Методы использования подзапросов в SQLite

Введение

В этой лабораторной работе вы изучите методы использования подзапросов 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.