Объединение таблиц в SQLite

Введение

В этой лабораторной работе вы узнаете, как объединять данные из нескольких таблиц в SQLite, используя методы объединения таблиц (table joining techniques). Мы рассмотрим INNER JOIN, LEFT JOIN, объединение нескольких таблиц и фильтрацию объединенных результатов. К концу этой лабораторной работы вы сможете эффективно извлекать и объединять данные с помощью SQLite.

Создание и заполнение таблиц

В этом шаге вы создадите две таблицы: employees (сотрудники) и departments (отделы), и заполните их примерами данных. Эти таблицы будут использоваться в последующих шагах для демонстрации методов объединения таблиц (table joining techniques).

Сначала откройте оболочку SQLite, выполнив следующую команду в терминале:

sqlite3 /home/labex/project/company.db

Эта команда открывает оболочку SQLite и подключается к файлу базы данных с именем company.db. Если файл не существует, SQLite создаст его.

Теперь создайте таблицу employees с помощью следующей SQL-команды:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department_id INTEGER
);

Эта команда создает таблицу с именем employees с тремя столбцами: id, name и department_id. Столбец id является первичным ключом (primary key) и будет однозначно идентифицировать каждого сотрудника.

Далее создайте таблицу departments с помощью следующей SQL-команды:

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT
);

Эта команда создает таблицу с именем departments с двумя столбцами: id и name. Столбец id является первичным ключом и будет однозначно идентифицировать каждый отдел.

Теперь вставьте несколько примеров данных в таблицу employees:

INSERT INTO employees (name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3),
('Eve', NULL);

Эта команда вставляет пять строк в таблицу employees. Каждая строка представляет сотрудника и соответствующий идентификатор отдела (department ID).

Наконец, вставьте примеры данных в таблицу departments:

INSERT INTO departments (name) VALUES
('Sales'),
('Marketing'),
('Engineering');

Эта команда вставляет три строки в таблицу departments. Каждая строка представляет отдел и его название.

Чтобы убедиться, что таблицы были созданы и заполнены правильно, вы можете выполнить следующую SQL-команду:

SELECT * FROM employees;

Ожидаемый вывод:

1|Alice|1
2|Bob|2
3|Charlie|1
4|David|3
5|Eve|

И:

SELECT * FROM departments;

Ожидаемый вывод:

1|Sales
2|Marketing
3|Engineering

Выполнение запросов INNER JOIN

В этом шаге вы узнаете, как использовать предложение INNER JOIN в SQLite. INNER JOIN возвращает строки, когда есть соответствие в обеих таблицах на основе указанного условия.

Основной синтаксис для INNER JOIN:

SELECT column1, column2 FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

В нашем случае мы хотим получить имена сотрудников вместе с названиями их отделов. Для этого выполните следующую SQL-команду:

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

Эта команда объединяет таблицы employees и departments на основе соответствия столбца department_id в employees столбцу id в departments. Затем она выбирает имя сотрудника из таблицы employees и название отдела из таблицы departments.

Ожидаемый вывод:

Alice|Sales
Bob|Marketing
Charlie|Sales
David|Engineering

Этот вывод показывает имя сотрудника и название соответствующего отдела. Обратите внимание, что Eve не включена в результат, потому что ее department_id имеет значение NULL, и нет соответствующего отдела. INNER JOIN возвращает только совпадающие строки.

Использование LEFT JOIN для необязательных данных

В этом шаге вы узнаете, как использовать LEFT JOIN в SQLite. LEFT JOIN (или LEFT OUTER JOIN) возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Если в правой таблице нет соответствия, для столбцов правой таблицы возвращаются значения NULL. Это полезно, когда вы хотите получить все записи из одной таблицы и включить связанные данные из другой таблицы, если они существуют.

Основной синтаксис для LEFT JOIN:

SELECT column1, column2 FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Чтобы получить всех сотрудников и названия их отделов, даже если сотруднику не назначен отдел, выполните следующую SQL-команду:

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

Эта команда объединяет таблицы employees и departments на основе соответствия столбца department_id в employees столбцу id в departments. Она выбирает имя сотрудника из таблицы employees и название отдела из таблицы departments. Поскольку это LEFT JOIN, будут перечислены все сотрудники.

Ожидаемый вывод:

Alice|Sales
Bob|Marketing
Charlie|Sales
David|Engineering
Eve|

Обратите внимание, что Eve указана, но название отдела пустое (NULL), потому что ее department_id имеет значение NULL, и нет соответствующего отдела. Это демонстрирует, как LEFT JOIN включает все строки из левой таблицы (employees), даже если в правой таблице (departments) нет соответствия.

Объединение нескольких таблиц

В этом шаге вы узнаете, как объединять несколько таблиц в SQLite. Объединение более двух таблиц включает в себя объединение данных из нескольких связанных таблиц с использованием нескольких предложений JOIN.

Сначала давайте добавим новую таблицу под названием locations для хранения местоположений отделов. Выполните следующую SQL-команду, чтобы создать таблицу locations:

CREATE TABLE locations (
    id INTEGER PRIMARY KEY,
    department_id INTEGER,
    city TEXT
);

Эта команда создает таблицу с именем locations с тремя столбцами: id, department_id и city.

Далее, вставьте некоторые примеры данных в таблицу locations:

INSERT INTO locations (department_id, city) VALUES
(1, 'New York'),
(2, 'Los Angeles'),
(3, 'Chicago');

Теперь давайте объединим все три таблицы, чтобы получить имена сотрудников, названия отделов и местоположения отделов. Выполните следующую SQL-команду:

SELECT employees.name, departments.name, locations.city
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN locations ON departments.id = locations.department_id;

Этот запрос сначала объединяет employees и departments на основе employees.department_id = departments.id. Затем он объединяет результат с таблицей locations на основе departments.id = locations.department_id. Это связывает сотрудников с их отделами, а затем с местоположением этих отделов.

Ожидаемый вывод:

Alice|Sales|New York
Bob|Marketing|Los Angeles
Charlie|Sales|New York
David|Engineering|Chicago

Это показывает имя каждого сотрудника, название его отдела и город, в котором расположен его отдел. Eve не включена, потому что ей не назначен отдел.

Фильтрация объединенных результатов

В этом шаге вы узнаете, как фильтровать результаты объединенных таблиц с помощью предложения WHERE в SQLite. Фильтрация позволяет извлекать только те строки, которые соответствуют определенным критериям после операции объединения (join operation).

Чтобы получить имена сотрудников, работающих в отделе продаж (Sales department), выполните следующую SQL-команду:

SELECT employees.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
WHERE departments.name = 'Sales';

Этот запрос объединяет таблицы employees и departments, а затем фильтрует результаты, чтобы включить только сотрудников, у которых название отдела — 'Sales'.

Ожидаемый вывод:

Alice
Charlie

Это показывает, что Alice и Charlie работают в отделе продаж.

Теперь давайте получим имена сотрудников, работающих в отделах, расположенных в Нью-Йорке (New York). Выполните следующую SQL-команду:

SELECT employees.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN locations ON departments.id = locations.department_id
WHERE locations.city = 'New York';

Этот запрос объединяет все три таблицы, а затем фильтрует результаты, чтобы включить только сотрудников, у которых местоположение отдела — Нью-Йорк.

Ожидаемый вывод:

Alice
Charlie

Опять же, Alice и Charlie — единственные сотрудники в отделах, расположенных в Нью-Йорке.

Резюме

В этой лабораторной работе вы научились объединять данные из нескольких таблиц в SQLite, используя методы объединения таблиц (table joining techniques). Вы изучили INNER JOIN, который возвращает строки, когда есть совпадение в обеих таблицах, и LEFT JOIN, который возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Вы также узнали, как объединять несколько таблиц и фильтровать объединенные результаты с помощью предложения WHERE. Эти навыки позволят вам эффективно извлекать и объединять данные с помощью SQLite.