Введение
В этой лабораторной работе вы изучите методы использования подзапросов SQLite для расширения возможностей извлечения и фильтрации данных. Вы узнаете, как использовать подзапросы в предложении WHERE, встраивать их в оператор SELECT и создавать коррелированные подзапросы (correlated subqueries).
Создание таблиц и вставка данных
На этом первом шаге вы создадите две таблицы: departments (отделы) и employees (сотрудники), и вставите в них некоторые примеры данных. Это предоставит вам данные, необходимые для практики использования подзапросов на следующих шагах.
Сначала откройте свой терминал в LabEx VM (виртуальной машине). Ваш путь по умолчанию - /home/labex/project.
Для начала подключитесь к базе данных SQLite с именем company.db. Если база данных не существует, SQLite создаст ее для вас. Выполните следующую команду:
sqlite3 company.db
Эта команда открывает инструмент командной строки SQLite и подключается к базе данных company.db. Вы увидите приглашение sqlite>.
Теперь создайте таблицу departments с помощью следующей SQL-команды:
CREATE TABLE departments (
department_id INTEGER PRIMARY KEY,
department_name TEXT,
location TEXT
);
Эта команда создает таблицу с именем departments с тремя столбцами: department_id, department_name и location. Столбец department_id является первичным ключом (primary key) для этой таблицы.
Далее вставьте некоторые примеры данных в таблицу departments:
INSERT INTO departments (department_name, location) VALUES
('Sales', 'New York'),
('Marketing', 'Los Angeles'),
('Engineering', 'San Francisco');
Эта команда вставляет три строки в таблицу departments, представляющие три разных отдела и их местоположения.
Теперь создайте таблицу employees с помощью следующей SQL-команды:
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
employee_name TEXT,
department_id INTEGER,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
Эта команда создает таблицу с именем employees с тремя столбцами: employee_id, employee_name и department_id. Столбец department_id является внешним ключом (foreign key), который ссылается на столбец department_id в таблице departments.
Наконец, вставьте некоторые примеры данных в таблицу employees:
INSERT INTO employees (employee_name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3);
Эта команда вставляет четыре строки в таблицу employees, представляющие четырех разных сотрудников и их идентификаторы отделов (department IDs).
Использование подзапросов в предложении WHERE
На этом шаге вы узнаете, как использовать подзапросы в предложении WHERE для фильтрации результатов на основе выходных данных другого запроса.
Подзапрос (subquery) - это оператор SELECT, вложенный внутрь другого SQL-оператора. В этом случае вы будете использовать подзапрос для выбора значений department_id из таблицы departments, а затем использовать эти значения для фильтрации результатов запроса к таблице employees.
Давайте найдем всех сотрудников, которые работают в отделах, расположенных в 'New York'. Для этого вам сначала нужно найти department_id для отделов в 'New York', а затем найти всех сотрудников с этим department_id.
Введите следующую SQL-команду в приглашении sqlite>:
SELECT employee_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');
Эта команда выбирает employee_name из таблицы employees, где department_id находится в списке значений department_id, возвращаемых подзапросом. Подзапрос выбирает department_id из таблицы departments, где location - 'New York'.
После выполнения команды вы должны увидеть следующий вывод:
Alice
Charlie
Этот вывод показывает имена сотрудников, которые работают в отделе продаж (Sales), который находится в Нью-Йорке.
Внедрение подзапросов в предложение SELECT
На этом шаге вы узнаете, как встраивать подзапросы в предложение SELECT SQL-оператора для получения связанных данных.
Встраивание подзапроса в предложение SELECT позволяет получить одно значение для каждой строки во внешнем запросе. Это значение часто является вычисляемым значением или связанным значением из другой таблицы.
Давайте получим имя каждого сотрудника вместе с названием его отдела. Введите следующую SQL-команду в приглашении sqlite>:
SELECT employee_name, (SELECT department_name FROM departments WHERE department_id = employees.department_id) AS department_name FROM employees;
Эта команда выбирает employee_name из таблицы employees, а также включает подзапрос, который извлекает department_name из таблицы departments. Подзапрос использует department_id из таблицы employees для сопоставления с правильным отделом. Результат подзапроса получает псевдоним (alias) department_name.
После выполнения команды вы должны увидеть следующий вывод:
Alice|Sales
Bob|Marketing
Charlie|Sales
David|Engineering
Этот вывод показывает имя каждого сотрудника и название его соответствующего отдела.
Создание коррелированных подзапросов
На этом шаге вы узнаете, как создавать коррелированные подзапросы (correlated subqueries). Коррелированные подзапросы - это подзапросы, которые ссылаются на столбец из внешнего запроса. Это означает, что подзапрос выполняется один раз для каждой строки внешнего запроса.
В отличие от простых подзапросов, которые выполняются один раз, и их результат используется внешним запросом, коррелированные подзапросы зависят от внешнего запроса в отношении своих значений. Они используются, когда вам нужно сравнить значения внутри подзапроса со значениями в текущей строке внешнего запроса.
Давайте найдем всех сотрудников, которые работают в отделе, расположенном в том же городе, что и имя сотрудника. Чтобы это работало, сначала обновим имена сотрудников, чтобы они соответствовали названиям городов.
UPDATE employees SET employee_name = 'New York' WHERE employee_name = 'Alice';
UPDATE employees SET employee_name = 'Los Angeles' WHERE employee_name = 'Bob';
UPDATE employees SET employee_name = 'New York' WHERE employee_name = 'Charlie';
UPDATE employees SET employee_name = 'San Francisco' WHERE employee_name = 'David';
Эти команды обновляют столбец employee_name в таблице employees на названия городов.
Теперь давайте напишем коррелированный подзапрос:
SELECT e.employee_name FROM employees e WHERE e.department_id IN (SELECT d.department_id FROM departments d WHERE d.location = e.employee_name);
Эта команда выбирает employee_name из таблицы employees (с псевдонимом e), где department_id находится в списке значений department_id, возвращаемых подзапросом. Подзапрос выбирает department_id из таблицы departments (с псевдонимом d), где location соответствует employee_name из внешнего запроса.
После выполнения команды вы должны увидеть следующий вывод:
New York
Los Angeles
San Francisco
Этот вывод показывает имена сотрудников (теперь названия городов), которые работают в отделах, расположенных в том же городе.
Оценка эффективности подзапроса с помощью JOIN
На этом шаге вы узнаете, как оценить эффективность подзапросов и изучить альтернативные подходы с использованием операций JOIN для оптимизации.
Хотя подзапросы и являются мощным инструментом, они иногда могут приводить к снижению производительности (performance bottlenecks), особенно при работе с большими наборами данных. Во многих случаях вы можете переписать подзапросы, используя операции JOIN, которые могут быть более эффективными.
Давайте перепишем коррелированный подзапрос из предыдущего шага, используя JOIN:
SELECT e.employee_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.location = e.employee_name;
Эта команда выбирает employee_name из таблицы employees (с псевдонимом e) и объединяет (joins) ее с таблицей departments (с псевдонимом d) по столбцу department_id. Затем предложение WHERE фильтрует результаты, чтобы включить только те строки, где location в таблице departments соответствует employee_name в таблице employees.
Чтобы проверить результат, выполните команду. Вы должны увидеть тот же вывод, что и на предыдущем шаге:
New York
Los Angeles
San Francisco
Для оценки эффективности обычно используется EXPLAIN QUERY PLAN до и после изменения. Однако из-за ограничений в среде LabEx мы не можем полностью продемонстрировать команду EXPLAIN QUERY PLAN. Ключевой вывод заключается в том, что операции JOIN часто более эффективны, чем коррелированные подзапросы, особенно для больших наборов данных.
Наконец, выйдите из оболочки sqlite3:
.exit
Это вернет вас в командную строку bash.
Резюме
В этой лабораторной работе вы узнали, как использовать подзапросы SQLite для расширения возможностей извлечения и фильтрации данных. Вы попрактиковались в использовании подзапросов в предложении WHERE, во встраивании их в оператор SELECT и в создании коррелированных подзапросов (correlated subqueries). Вы также узнали, как переписать подзапросы, используя операции JOIN для повышения эффективности. Эти методы предоставляют вам мощные инструменты для работы с данными в SQLite.
