Как использовать вложенные запросы в SQL

Введение

Язык структурированных запросов (SQL) используется для управления данными в системе управления реляционными базами данных (RDBMS). Полезной функцией в SQL является создание запроса внутри другого запроса, также известного как подзапрос или вложенный запрос. Вложенный запрос представляет собой оператор SELECT, обычно заключенный в скобки и вложенный в основную операцию SELECT, INSERT или DELETE.

В этом руководстве вы будете использовать вложенные запросы с операторами SELECT, INSERT и DELETE. Вы также будете использовать агрегатные функции внутри вложенного запроса для сравнения значений данных с отсортированными значениями данных, указанными в WHERE и LIKE выражениях.

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

Для выполнения этого руководства вам понадобится компьютер, на котором установлена некоторая система управления реляционными базами данных (RDBMS), использующая SQL. Инструкции и примеры в этом руководстве были проверены в следующей среде:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL установлен и защищен на сервере. Следуйте нашему руководству Как установить MySQL на Ubuntu 20.04, чтобы настроить это. В этом руководстве предполагается, что вы также настроили неадминистративного пользователя MySQL, как описано в Шаге 3 этого руководства.

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

Подключение к MySQL и настройка образцовой базы данных

Если ваша база данных SQL работает на удаленном сервере, выполните вход в свой сервер через SSH с вашего локального компьютера:

ssh sammy@your_server_ip

Затем откройте командную строку MySQL, заменив sammy на информацию о вашей учетной записи пользователя MySQL:

mysql -u sammy -p

Создайте базу данных с именем zooDB:

CREATE DATABASE zooDB;

Если база данных была успешно создана, вы получите следующий вывод:

Output
Query OK, 1 row affected (0.01 sec)

Чтобы выбрать базу данных zooDB, выполните следующее выражение USE:

USE zooDB;

Output
Database changed

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

• guest_id: хранит значения для гостей, посещающих зоопарк, и использует тип данных int. Это также служит в качестве первичного ключа таблицы, что означает, что каждое значение в этом столбце будет функционировать как уникальный идентификатор для соответствующей строки.
• first_name: содержит имя каждого гостя с использованием типа данных varchar с максимальной длиной в 30 символов.
• last_name: использует тип данных varchar, также с максимальной длиной в 30 символов, для хранения фамилии каждого гостя.
• guest_type: содержит тип гостя (взрослый или ребенок) для каждого гостя с использованием типа данных varchar с максимальной длиной в 15 символов.
• membership_type: представляет тип членства, который у каждого гостя, с использованием типа данных varchar для хранения максимум 30 символов.
• membership_cost: хранит стоимость различных типов членства. Этот столбец использует тип данных decimal с точностью пяти и масштабом два, что означает, что значения в этом столбце могут иметь пять цифр и две цифры справа от десятичной точки.
• total_visits: использует тип данных int для записи общего количества посещений от каждого гостя.

Создайте таблицу с именем guests, содержащую каждый из этих столбцов, запустив следующую команду CREATE TABLE:

CREATE TABLE guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
guest_type varchar(15),
membership_type varchar(30),
membership_cost decimal(5,2), 
total_visits int,
PRIMARY KEY (guest_id)
); 

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

INSERT INTO guests
(guest_id, first_name, last_name, guest_type, membership_type, membership_cost, total_visits)
VALUES
(1, 'Judy', 'Hopps', 'Adult', 'Resident Premium Pass', 110.0, 168),
(2, 'Nick', 'Wilde', 'Adult', 'Day Pass', 62.0, 1),
(3, 'Duke', 'Weaselton', 'Adult', 'Resident Pass', 85.0, 4),
(4, 'Tommy', 'Yax', 'Child', 'Youth Pass', 67.0, 30),
(5, 'Lizzie', 'Yax', 'Adult', 'Guardian Pass', 209.0, 30),
(6, 'Jenny', 'Bellwether', 'Adult', 'Resident Premium Pass', 110.0, 20),
(7, 'Idris', 'Bogo', 'Child', 'Youth Pass', 67.0, 79),
(8, 'Gideon', 'Grey', 'Child', 'Youth Pass', 67.0, 100),
(9, 'Nangi', 'Reddy', 'Adult', 'Guardian Champion', 400.0, 241),
(10, 'Octavia', 'Otterton', 'Adult', 'Resident Pass', 85.0, 11),
(11, 'Calvin', 'Roo', 'Adult', 'Resident Premium Pass', 110.0, 173), 
(12, 'Maurice', 'Big', 'Adult', 'Guardian Champion', 400.0, 2), 
(13, 'J.K.', 'Lionheart', 'Child', 'Day Pass', 52.0, 1), 
(14, 'Priscilla', 'Bell', 'Child', 'Day Pass', 104.0, 2),
(15, 'Tommy', 'Finnick', 'Adult', 'Day Pass', 62.0, 1);

Output
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

После вставки данных вы готовы начать использовать вложенные запросы в SQL.

Использование вложенных запросов с SELECT

В SQL запрос – это операция, которая извлекает данные из таблицы в базе данных и всегда включает оператор SELECT. Вложенный запрос – это полный запрос, встроенный в другую операцию. Вложенный запрос может иметь все элементы, используемые в обычном запросе, и любой допустимый запрос может быть встроен в другую операцию, чтобы стать вложенным запросом. Например, вложенный запрос может быть встроен в операции INSERT и DELETE. В зависимости от операции вложенный запрос должен быть встроен, заключив оператор в правильное количество скобок, чтобы следовать определенному порядку операций. Вложенный запрос также полезен в ситуациях, когда вы хотите выполнить несколько команд в одном запросе, а не писать несколько запросов, чтобы получить желаемый результат(ы).

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

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > AVG(total_visits);

Однако запрос с использованием этого синтаксиса приведет к ошибке:

Output
ERROR 1111 (HY000): Invalid use of group function

Причина этой ошибки заключается в том, что агрегатные функции, такие как AVG(), не работают, если они не выполняются в пределах оператора SELECT.

Один из вариантов получения этой информации состоит в том, чтобы сначала выполнить запрос для нахождения среднего числа посещений гостями, а затем выполнить другой запрос для поиска результатов на основе этого значения, как в следующих двух примерах:

SELECT AVG(total_visits) FROM guests;

Output
+-----------------+
| avg(total_visits) |
+-----------------+
| 57.5333 |
+-----------------+
1 row in set (0.00 sec)

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 57.5333;

Output
+----------+---------+------------+
| first_name | last_name | total_visits |
+----------+---------+------------+
| Judy | Hopps | 168 |
| Idris | Bogo | 79 |
| Gideon | Grey | 100 |
| Nangi | Reddy | 241 |
| Calvin | Roo | 173 |
+----------+---------+------------+
5 rows in set (0.00 sec)

Однако вы можете получить этот же набор результатов с помощью одного запроса, вложив первый запрос (SELECT AVG(total_visits) FROM guests;) внутрь второго. Имейте в виду, что при использовании вложенных запросов необходимо использовать правильное количество скобок, чтобы завершить операцию, которую вы хотите выполнить. Это связано с тем, что вложенный запрос является первой операцией, которая выполняется:

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 
(SELECT AVG(total_visits) FROM guests);

Output
+------------+-----------+--------------+
| first_name | last_name | total_visits |
+------------+-----------+--------------+
| Judy       | Hopps     |          168 |
| Idris      | Bogo      |           79 |
| Gideon     | Grey      |          100 |
| Nangi      | Reddy     |          241 |
| Calvin     | Roo       |          173 |
+------------+-----------+--------------+
5 rows in set (0.00 sec)

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

Использование Вложенных Запросов с INSERT

С вложенным запросом вы не ограничены только вставкой его в другие SELECT операторы. Фактически, вы также можете использовать вложенные запросы для вставки данных в существующую таблицу, вложив ваш вложенный запрос в операцию INSERT.

Чтобы проиллюстрировать, предположим, что аффилированный зоопарк запрашивает некоторую информацию о ваших гостях, потому что они заинтересованы в предоставлении 15% скидки гостям, которые приобретают членство “Резидент” в их заведении. Для этого используйте CREATE TABLE, чтобы создать новую таблицу с названием upgrade_guests, которая будет содержать шесть столбцов. Обратите особое внимание на типы данных, такие как int и varchar, а также на максимальное количество символов, которые они могут содержать. Если они не соответствуют исходным типам данных из таблицы guests, созданной в разделе настройка образцовой базы данных, то при попытке вставки данных из таблицы guests с использованием вложенного запроса вы получите ошибку, и данные не будут переданы правильно. Создайте вашу таблицу со следующей информацией:

CREATE TABLE upgrade_guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
membership_type varchar(30),
membership_cost decimal(5,2),
total_visits int, 
PRIMARY KEY (guest_id)
); 

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

В этой операции напишите INSERT INTO и новую таблицу upgrade_guests, чтобы было ясно, куда вставляются данные. Затем напишите ваш вложенный запрос с оператором SELECT, чтобы извлечь соответствующие значения данных, и FROM, чтобы убедиться, что они берутся из таблицы guests.

Дополнительно примените 15% скидку к любым членам “Resident”, включив операцию умножения * на 0.85 внутри вложенного запроса (membership_cost * 0.85). Затем используйте оператор WHERE для сортировки значений в столбце membership_type. Вы можете сузить это еще больше, чтобы получить только результаты для членств “Resident”, используя LIKE clause и поместите знак процента % перед и после слова “Resident” в одинарные кавычки, чтобы выбрать любые членства, которые следуют тому же шаблону, или в данном случае тому же словесному описанию. Ваш запрос будет записан следующим образом:

INSERT INTO upgrade_guests
SELECT guest_id, first_name, last_name, membership_type, 
(membership_cost * 0.85), total_visits
FROM guests
WHERE membership_type LIKE '%resident%';

Output
Query OK, 5 rows affected, 5 warnings (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 5

Результаты указывают, что в новую таблицу upgrade_guests было добавлено пять записей. Чтобы подтвердить, что запрошенные вами данные успешно перенесены из таблицы guests в созданную вами пустую таблицу upgrade_guests, и с условиями, которые вы указали для вложенного запроса и оператора WHERE, выполните следующее:

SELECT * FROM upgrade_guests;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        1 | Judy       | Hopps      | Resident Premium Pass |           93.50 |          168 |
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|       11 | Calvin     | Roo        | Resident Premium Pass |           93.50 |          173 |
+----------+------------+------------+-----------------------+-----------------+--------------+
5 rows in set (0.01 sec)

Согласно этому выводу из вашей новой таблицы upgrade_guests, информация о гостевых членствах, связанных с “Resident”, из таблицы guest была вставлена правильно. Кроме того, новая стоимость членства membership_cost была пересчитана с применением 15% скидки. В результате данная операция помогла сегментировать и нацелить аудиторию, а также предоставила сниженные цены, готовые к предоставлению потенциальным новым членам.

Использование вложенных запросов с оператором DELETE

Для практики использования вложенного запроса с оператором DELETE, предположим, что вы хотите удалить всех гостей, которые часто посещают, потому что вы хотите сосредоточиться на продвижении скидки на обновленный премиум-пропуск для членов, которые в настоящее время не часто посещают зоопарк.

Начните эту операцию с оператора DELETE FROM, чтобы было ясно, откуда удаляются данные, в данном случае, из таблицы upgrade_guests. Затем используйте оператор WHERE, чтобы отсортировать любые total_visits, которые больше, чем количество, указанное во вложенном запросе. В вашем встроенном вложенном запросе используйте SELECT, чтобы найти среднее значение, AVG, total_visits, чтобы предшествующий оператор WHERE имел соответствующие значения данных для сравнения. Наконец, используйте FROM, чтобы извлечь эту информацию из таблицы guests. Полный оператор запроса будет следующим:

DELETE FROM upgrade_guests
WHERE total_visits >
(SELECT AVG(total_visits) FROM guests);

Output
Query OK, 2 rows affected (0.00 sec)

Подтвердите, что эти записи успешно удалены из таблицы upgrade_guests и используйте ORDER BY, чтобы организовать результаты по total_visits в числовом и по возрастанию порядке:

SELECT * FROM upgrade_guests ORDER BY total_visits;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
+----------+------------+------------+-----------------------+-----------------+--------------+
3 rows in set (0.00 sec)

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

Заключение

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