Введение
В этой лабораторной работе вы узнаете, как объединять данные из нескольких таблиц в 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.
