LabEx
ВходРегистрация

Подзапросы и вложенные операции в MySQL

MySQLBeginner
Практиковаться сейчас

Введение

В этой лабораторной работе вы изучите возможности подзапросов (subqueries) и вложенных операций в MySQL. Основное внимание уделяется использованию подзапросов в предложении WHERE для фильтрации данных на основе условий, полученных из других таблиц или той же таблицы.

Вы научитесь подключаться к серверу MySQL, создавать базу данных и таблицы (customers и orders), а затем конструировать SQL-запросы, использующие подзапросы, для выявления клиентов, разместивших заказы на общую сумму, превышающую определенное значение. Лабораторная работа также охватывает использование EXISTS с подзапросом, тестирование коррелированных подзапросов и сравнение производительности подзапросов.

Написание подзапроса в предложении WHERE

В этом шаге вы научитесь использовать подзапрос (subquery) в предложении WHERE SQL-запроса. Подзапрос — это запрос, вложенный в другой запрос. Это мощный инструмент для извлечения данных на основе условий, полученных из других таблиц или той же таблицы.

Понимание подзапросов

Подзапрос (или внутренний запрос) — это SQL-запрос, вложенный в более крупный запрос. Подзапрос выполняется первым, а его результат используется внешним запросом. Подзапросы могут встречаться в предложениях WHERE, SELECT, FROM и HAVING.

В предложении WHERE подзапрос обычно используется для фильтрации результатов внешнего запроса на основе условия. Подзапрос возвращает одно значение или набор значений, которые внешний запрос использует для сравнения.

Сценарий

Представьте, что у вас есть две таблицы: customers и orders. Таблица customers содержит информацию о клиентах (например, customer_id, name, city), а таблица orders содержит информацию о заказах (например, order_id, customer_id, order_date, total_amount).

Вы хотите найти всех клиентов, которые разместили хотя бы один заказ на сумму более 100 долларов.

Шаги

  1. Подключение к серверу MySQL:

    Откройте терминал и выполните следующую команду для подключения к серверу MySQL от имени пользователя root:

    sudo mysql -u root

    Вы должны увидеть приглашение MySQL: mysql>.

  2. Создание базы данных и таблиц:

    Если у вас еще нет базы данных и таблиц, создайте их сейчас. Давайте создадим базу данных с именем labdb и таблицы customers и orders. Выполните следующие SQL-команды в приглашении MySQL:

    CREATE DATABASE IF NOT EXISTS labdb;
USE labdb;

CREATE TABLE IF NOT EXISTS customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255),
    city VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

  3. Вставка образца данных:

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

    INSERT INTO customers (customer_id, name, city) VALUES
(1, 'Alice Smith', 'New York'),
(2, 'Bob Johnson', 'Los Angeles'),
(3, 'Charlie Brown', 'Chicago'),
(4, 'David Lee', 'Houston');

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2023-01-15', 120.00),
(2, '2023-02-20', 80.00),
(1, '2023-03-10', 150.00),
(3, '2023-04-05', 200.00),
(2, '2023-05-12', 110.00),
(4, '2023-06-18', 90.00);

  4. Написание подзапроса в предложении WHERE:

    Теперь напишите запрос для поиска клиентов, которые разместили заказы на сумму более 100 долларов. Выполните следующую SQL-команду в приглашении MySQL:

    SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE total_amount > 100);

    Объяснение:

    • Подзапрос (SELECT customer_id FROM orders WHERE total_amount > 100) выбирает customer_id из таблицы orders, где total_amount больше 100.
    • Внешний запрос SELECT * FROM customers WHERE customer_id IN (...) выбирает все столбцы из таблицы customers, где customer_id находится в наборе customer_id, возвращенных подзапросом.

  5. Наблюдение за выводом:

    Вы должны увидеть следующий вывод, показывающий клиентов, которые разместили заказы на сумму более 100 долларов:

    +-------------+-------------+-----------+
| customer_id | name        | city      |
+-------------+-------------+-----------+
|           1 | Alice Smith | New York  |
|           2 | Bob Johnson | Los Angeles |
|           3 | Charlie Brown | Chicago   |
+-------------+-------------+-----------+
3 rows in set (0.00 sec)

Использование EXISTS с подзапросом

В этом шаге вы научитесь использовать оператор EXISTS с подзапросом в MySQL. Оператор EXISTS используется для проверки существования строк в подзапросе. Он возвращает TRUE, если подзапрос возвращает какие-либо строки, и FALSE в противном случае.

Понимание EXISTS

Оператор EXISTS часто используется в предложении WHERE SQL-запроса для фильтрации результатов на основе существования связанных данных в другой таблице. Это мощная альтернатива использованию операций IN или JOIN, и иногда может быть более эффективной, особенно при работе с большими наборами данных.

В отличие от IN, EXISTS фактически не извлекает данные из подзапроса. Он просто проверяет, возвращаются ли какие-либо строки. Это может сделать его быстрее, чем IN, когда вам нужно только знать, существует ли совпадение, а не сами значения.

Сценарий

Продолжая использовать таблицы customers и orders из предыдущего шага, найдем всех клиентов, которые разместили хотя бы один заказ.

Предварительные требования

Убедитесь, что вы выполнили предыдущий шаг ("Написание подзапроса в предложении WHERE") и что база данных labdb, таблица customers и таблица orders заполнены данными.

Шаги

  1. Написание запроса с использованием EXISTS:

    Напишите запрос для поиска клиентов, которые разместили хотя бы один заказ. Выполните следующую SQL-команду в приглашении MySQL:

    SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

    Объяснение:

    • Внешний запрос SELECT * FROM customers c выбирает все столбцы из таблицы customers с псевдонимом c.
    • Предложение WHERE EXISTS (...) проверяет, возвращает ли подзапрос какие-либо строки.
    • Подзапрос SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id выбирает значение 1 (это может быть любое константное значение) из таблицы orders с псевдонимом o, где customer_id в таблице orders совпадает с customer_id в таблице customers.
    • Оператор EXISTS возвращает TRUE, если подзапрос возвращает хотя бы одну строку, что указывает на то, что клиент разместил хотя бы один заказ.

  2. Наблюдение за выводом:

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

    +-------------+-------------+-----------+
| customer_id | name        | city      |
+-------------+-------------+-----------+
|           1 | Alice Smith | New York  |
|           2 | Bob Johnson | Los Angeles |
|           3 | Charlie Brown | Chicago   |
|           4 | David Lee   | Houston   |
+-------------+-------------+-----------+
4 rows in set (0.00 sec)

  3. Изменение запроса (необязательно):

    Изменим запрос, чтобы найти клиентов, которые не размещали никаких заказов. Вы можете сделать это, используя NOT EXISTS. Выполните следующую SQL-команду в приглашении MySQL:

    SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

  4. Наблюдение за выводом:

    Поскольку все клиенты в наших образцовых данных разместили заказы, этот запрос должен вернуть пустой набор результатов:

    Empty set (0.00 sec)

Тестирование коррелированного подзапроса

В этом шаге вы узнаете о коррелированных подзапросах (correlated subqueries) в MySQL. Коррелированный подзапрос — это подзапрос, который ссылается на столбец из внешнего запроса. Это означает, что подзапрос выполняется один раз для каждой строки во внешнем запросе.

Понимание коррелированных подзапросов

В отличие от простого подзапроса, который выполняется только один раз, коррелированный подзапрос зависит от внешнего запроса для получения своих значений. Подзапрос использует значения из текущей строки внешнего запроса для определения своего результата. Это делает коррелированные подзапросы более мощными для определенных типов запросов, но также потенциально менее эффективными, чем простые подзапросы, особенно для больших наборов данных.

Сценарий

Продолжая использовать таблицы customers и orders, найдем всех клиентов, которые разместили заказ на сумму, превышающую среднюю сумму заказа по всем заказам.

Предварительные требования

Убедитесь, что вы выполнили предыдущие шаги и что база данных labdb, таблица customers и таблица orders заполнены данными.

Шаги

  1. Написание коррелированного подзапроса:

    Напишите запрос для поиска клиентов, которые разместили заказ на сумму, превышающую среднюю сумму заказа. Выполните следующую SQL-команду в приглашении MySQL:

    SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.total_amount > (SELECT AVG(total_amount) FROM orders)
);

    Объяснение:

    • Внешний запрос SELECT c.customer_id, c.name FROM customers c выбирает customer_id и name из таблицы customers с псевдонимом c.
    • Предложение WHERE EXISTS (...) проверяет, возвращает ли подзапрос какие-либо строки.
    • Коррелированный подзапрос SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.total_amount > (SELECT AVG(total_amount) FROM orders) выбирает значение 1 из таблицы orders с псевдонимом o, где:
      • o.customer_id = c.customer_id: Это корреляция. Подзапрос ссылается на customer_id из таблицы customers внешнего запроса.
      • o.total_amount > (SELECT AVG(total_amount) FROM orders): Это условие проверяет, превышает ли сумма заказа среднюю сумму заказа по всем заказам. Подзапрос AVG(total_amount) является некоррелированным подзапросом, который выполняется только один раз для получения средней суммы заказа.

  2. Наблюдение за выводом:

    Вы должны увидеть следующий вывод, показывающий клиентов, которые разместили заказы на сумму, превышающую среднюю сумму заказа:

    +-------------+-------------+
| customer_id | name        |
+-------------+-------------+
|           1 | Alice Smith |
|           3 | Charlie Brown |
+-------------+-------------+
2 rows in set (0.00 sec)

  3. Другой пример: Найти самую высокую сумму заказа для каждого клиента

    Выполните следующую SQL-команду в приглашении MySQL, чтобы получить идентификатор, имя каждого клиента и их самую высокую сумму заказа. Коррелированный подзапрос вычисляет максимальную сумму заказа для каждого клиента индивидуально.

    SELECT c.customer_id, c.name, (
    SELECT MAX(o.total_amount)
    FROM orders o
    WHERE o.customer_id = c.customer_id
) AS highest_order_amount
FROM customers c;

  4. Наблюдение за выводом:

    Вы должны увидеть следующий вывод:

    +-------------+-------------+-----------------------+
| customer_id | name        | highest_order_amount  |
+-------------+-------------+-----------------------+
|           1 | Alice Smith |                150.00 |
|           2 | Bob Johnson |                110.00 |
|           3 | Charlie Brown |                200.00 |
|           4 | David Lee   |                 90.00 |
+-------------+-------------+-----------------------+
4 rows in set (0.00 sec)

Сравнение производительности подзапросов

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

Понимание факторов производительности

На производительность подзапроса могут влиять несколько факторов, в том числе:

  • Размер данных: Размер таблиц, участвующих в запросе.
  • Тип подзапроса: Является ли подзапрос коррелированным или некоррелированным.
  • Индексирование: Наличие и эффективность индексов в таблицах.
  • Версия MySQL: Конкретная используемая версия MySQL, поскольку методы оптимизации запросов могут различаться.

Сценарий

Продолжая использовать таблицы customers и orders, сравним производительность подзапроса с использованием IN и подзапроса с использованием EXISTS для поиска всех клиентов, которые разместили хотя бы один заказ.

Предварительные требования

Убедитесь, что вы выполнили предыдущие шаги и что база данных labdb, таблица customers и таблица orders заполнены данными. Чтобы сделать сравнение производительности более осмысленным, мы добавим больше данных в таблицу orders.

Шаги

  1. Добавление большего количества данных в таблицу orders:

    Чтобы сделать сравнение производительности более реалистичным, добавим значительный объем данных в таблицу orders. Мы вставим 1000 заказов для каждого клиента с помощью хранимой процедуры. Выполните следующие SQL-команды в приглашении MySQL:

    DELIMITER //
CREATE PROCEDURE insert_many_orders()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 1000 DO
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1, CURDATE(), 50.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (2, CURDATE(), 75.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (3, CURDATE(), 100.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (4, CURDATE(), 125.00);
    SET i = i + 1;
  END WHILE;
END//
DELIMITER ;
CALL insert_many_orders();
DROP PROCEDURE insert_many_orders;

    Объяснение:

    • Этот SQL-скрипт создает хранимую процедуру с именем insert_many_orders.
    • Процедура вставляет 1000 заказов для каждого из четырех клиентов в таблицу orders.
    • После вставки данных процедура удаляется.

  2. Запрос с использованием IN:

    Выполните следующий запрос с использованием IN для поиска всех клиентов, которые разместили хотя бы один заказ:

    SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

  3. Анализ плана выполнения запроса с помощью EXPLAIN:

    Перед выполнением запроса используйте команду EXPLAIN для анализа плана выполнения запроса. Это даст вам представление о том, как MySQL планирует выполнить запрос, и поможет выявить потенциальные узкие места в производительности. Выполните следующую SQL-команду в приглашении MySQL:

    EXPLAIN SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

    Вывод EXPLAIN покажет вам доступные таблицы, используемые индексы (если таковые имеются) и порядок выполнения операций. Обратите внимание на столбец type, который указывает тип используемого соединения или метода доступа.

  4. Запрос с использованием EXISTS:

    Выполните следующий запрос с использованием EXISTS для поиска всех клиентов, которые разместили хотя бы один заказ:

    SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

  5. Анализ плана выполнения запроса с помощью EXPLAIN:

    Используйте команду EXPLAIN для анализа плана выполнения запроса EXISTS. Выполните следующую SQL-команду в приглашении MySQL:

    EXPLAIN SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

    Сравните план выполнения с планом запроса IN. Есть ли какие-либо различия в доступных таблицах, используемых индексах или методах доступа?

  6. Наблюдения:

    В целом, EXISTS обычно работает лучше, чем IN, когда подзапрос возвращает большое количество строк. Это связано с тем, что IN должен сравнивать значения внешнего запроса со всеми значениями, возвращаемыми подзапросом, в то время как EXISTS останавливается, как только находит совпадение. Однако фактическая производительность может варьироваться в зависимости от конкретного запроса, данных и системы управления базами данных. Вы можете использовать функцию BENCHMARK() (как показано в исходном документе), чтобы получить более точное измерение времени выполнения, но для этой лабораторной работы анализ вывода EXPLAIN дает достаточное представление о планах запросов.

  7. Очистка (необязательно):

    Если вы хотите очистить базу данных и таблицы, вы можете выполнить следующие команды в приглашении MySQL:

    DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP DATABASE IF EXISTS labdb;

    Когда вы закончите все шаги, вы можете выйти из клиента MySQL, набрав:

    exit

Резюме

В этой лабораторной работе вы научились использовать подзапросы в предложении WHERE SQL-запроса для фильтрации данных на основе условий, полученных из других таблиц или той же таблицы. Вы практиковались в подключении к серверу MySQL, создании базы данных и таблиц, а также вставке образцов данных.

Вы изучили использование оператора IN с подзапросом для поиска клиентов на основе связанных данных в таблице orders. Вы также узнали об операторе EXISTS как альтернативе IN и практиковались в его использовании для проверки существования связанных строк.

Кроме того, вы познакомились с коррелированными подзапросами, которые ссылаются на столбцы из внешнего запроса, и использовали один из них для поиска клиентов с заказами, превышающими среднюю сумму заказа. Наконец, вы сравнили производительность подзапросов IN и EXISTS, анализируя их планы выполнения с помощью команды EXPLAIN, получив представление о том, как MySQL обрабатывает эти запросы.

Связанные MySQL Курсы
MySQL для начинающих

MySQL для начинающих

databasemysql
Практические лабораторные работы по продвинутому MySQL

Практические лабораторные работы по продвинутому MySQL

databasemysql
Управление правами пользователей MySQL

Управление правами пользователей MySQL

databasemysql