Файл: Лабораторная работа Вложенные запросы Задание.pdf

Лабораторная работа № 5. Вложенные запросы
Задание.
1. Изучить правила создания подзапросов (вложенных запросов).
2. Создать вложенные запросы к таблицам своей базы данных.
Вложенный запрос – это запрос, который находится внутри другого SQL запроса и встроен внутри условного оператора
WHERE.
Данный вид запросов используется для возвращения данных, которые будут использоваться в основном запросе, как условие для ограничения получаемых данных.
Вложенные запросы должны следовать следующим правилам.
Вложенный запрос должен быть заключён в родительский запрос.
Вложенный запрос может содержать только одну колонку в операторе
SELECT.
Оператор ORDER BY не может быть использован во вложенном запросе. Для обеспечения функционала ORDER BY, во вложенном запросе может быть использован GROUP BY.
Вложенные запросы, возвращающие более одной записи могут использоваться с операторами нескольких значений, как оператор IN.
Вложенный запрос не может заканчиваться в функции.
SELECT не может включать никаких ссылок на значения BLOB,
ARRAY, CLOB и NCLOB.
Оператор BETWEEN не может быть использован вместе с вложенным запросом.
Пример структуры вложенного запроса
SELECT поля_таблиц
FROM список_таблиц
WHERE конкретное_поле IN (
SELECT поле_таблицы FROM таблица
)
Здесь, SELECT поля_таблиц
FROM список_таблиц
WHERE конкретное_поле IN (...) — внешний запрос, а SELECT поле_таблицы FROM таблица — вложенный (внутренний) запрос.
Каждый вложенный запрос, в свою очередь, может содержать один или несколько вложенных запросов. Количество вложенных запросов в инструкции не ограничено.
Подзапрос может содержать все стандартные инструкции, разрешённые для использования в обычном
SQL-запросе: DISTINCT, GROUP
BY, LIMIT, ORDER BY, объединения таблиц, запросов и т.д.
Подзапрос может возвращать скаляр (одно значение), одну строку, один столбец или таблицу (одну или несколько строк из одного или нескольких столбцов). Они называются скалярными, столбцовыми, строковыми и табличными подзапросами.

Подзапрос как скалярный операнд
Скалярный подзапрос — запрос, возвращающий единственное скалярное значение (строку, число и т.д.).
Следующий простейший запрос демонстрирует вывод единственного значения (названия компании). В таком виде он не имеет большого смысла, однако ваши запросы могут быть намного сложнее.
SELECT (SELECT name FROM company LIMIT 1);
Таким же образом можно использовать скалярные подзапросы для фильтрации строк с помощью WHERE, используя операторы сравнения.
SELECT *
FROM FamilyMembers
WHERE birthday = (SELECT MAX(birthday) FROM FamilyMembers);
С помощью данного запроса возможно получить самого младшего члена семьи. Здесь используется подзапрос для получения максимальной даты рождения, которая затем используется для фильтрации строк.
Подзапросы с ANY, IN, ALL
ANY — ключевое слово, которое должно следовать за операцией сравнения (>, <, <>, = и т.д.), возвращающее TRUE, если хотя бы одно из значений столбца подзапроса удовлетворяет обозначенному условию.
SELECT поля_таблицы_1
FROM таблица_1
WHERE поле_таблицы_1 <= ANY (SELECT поле_таблицы_2 FROM таблица_2);
ALL — ключевое слово, которое должно следовать за операцией сравнения, возвращающее TRUE, если все значения столбца подзапроса удовлетворяет обозначенному условию.
SELECT поля_таблицы_1
FROM таблица_1
WHERE поле_таблицы_1 > ALL (SELECT поле_таблицы_2 FROM таблица_2);
IN — ключевое слово, являющееся псевдонимом ключевому слову ANY с оператором сравнения = (эквивалентность), либо <>
ALL для NOT IN. Например, следующие запросы равнозначны:
WHERE поле_таблицы_1 = ANY (SELECT поле_таблицы_2 FROM таблица_2);
WHERE поле_таблицы_1 IN (SELECT поле_таблицы_2 FROM таблица_2);
Строковые подзапросы
Строковый подзапрос — это подзапрос, возвращающий единственную строку с более чем одной колонкой. Например, следующий запрос получает в подзапросе единственную строку, после чего по порядку попарно сравнивает полученные значения со значениями во внешнем запросе.
SELECT поля_таблицы_1

FROM таблица_1
WHERE (первое_поле_таблицы_1, второе_поле_таблицы_1) =
(
SELECT первое_поле_таблицы_2, второе_поле_таблицы_2
FROM таблица_2
WHERE id = 10
);
Данную конструкцию удобно использовать для замены логических операторов. Так, следующие два запроса полностью эквивалентны:
SELECT поля_таблицы_1 FROM таблица_1 WHERE
(первое_поле_таблицы_1, второе_поле_таблицы_1) = (1, 1);
SELECT поля_таблицы_1 FROM таблица_1 WHERE первое_поле_таблицы_1 = 1 AND второе_поле_таблицы_1 = 1;
Связанные подзапросы
Связанным подзапросом является подзапрос, который содержит ссылку на таблицу, которая была объявлена во внешнем запросе. Здесь вложенный запрос ссылается на внешюю таблицу "таблица_1":
SELECT поля_таблицы_1 FROM таблица_1
WHERE поле_таблицы_1 IN
(
SELECT поле_таблицы_2 FROM таблица_2
WHERE таблица_2.поле_таблицы_2 = таблица_1.поле_таблицы_1
);
Подзапросы как производные таблицы
Производная таблица — выражение, которое генерирует временную таблицу в предложении FROM, которая работает так же, как и обычные таблицы, которые вы указываете через запятую. Так выглядит общий синтаксис запроса с использованием производных таблиц:
SELECT поля_таблицы_1 FROM (подзапрос) [AS] псевдоним_производной_таблицы
Обратите внимание на то, что для производной таблицы обязательно должен указываться её псевдоним, для того, чтобы имелась возможность обратиться к ней в других частях запроса.
Обработка вложенных запросов
Вложенные подзапросы обрабатываются «снизу вверх». То есть сначала обрабатывается вложенный запрос самого нижнего уровня. Далее значения, полученные по результату его выполнения, передаются и используются при реализации подзапроса более высокого уровня и т.д.
Примеры выполнения:
Предположим, что у нас есть таблица developers, которая содержит следующие записи:

+----+-------------------+-----------+------------+--------+
| ID | NAME | SPECIALTY | EXPERIENCE | SALARY |
+----+-------------------+-----------+------------+--------+
| 1 | Eugene Suleimanov | Java | 2 | 2500 |
| 2 | Peter Romanenko | Java | 3 | 3500 |
| 3 | Andrei Komarov | C++ | 3 | 2500 |
| 4 | Konstantin Geiko | C# | 2 | 2000 |
| 5 | Asya Suleimanova | UI/UX | 2 | 1800 |
| 6 | Ludmila Geiko | UI/UX | 2 | 1800 |
| 7 | Ivan Ivanov | C# | 1 | 900 |
+----+-------------------+-----------+------------+--------+
Попробуем выполнить следующий вложенный запрос:
SELECT * FROM developers
WHERE ID IN (SELECT ID
FROM developers
WHERE SALARY > 2000);
Предположим, что у нас есть клон таблицы developers, который имеет имя developers_clone и имеет следующую структуру:
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| ID | int(11) | NO | PRI | NULL | |
| NAME | varchar(100) | NO | | NULL | |
| SPECIALTY | varchar(100) | YES | | NULL | |
| EXPERIENCE | int(11) | NO | | NULL | |
| SALARY | int(11) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
И не содержит данных: mysql> SELECT * FROM developers_clone;
Empty set (0.00 sec)
Теперь попробуем выполнить для этой же таблицы следующий запрос: mysql> INSERT INTO developers_clone
SELECT * FROM developers
WHERE ID IN (SELECT ID
FROM developers);
В результате выполнения данного запроса таблица developers_clone будет содержать следующие данные:
+----+-------------------+-----------+------------+--------+
| ID | NAME | SPECIALTY | EXPERIENCE | SALARY |
+----+-------------------+-----------+------------+--------+
| 1 | Eugene Suleimanov | Java | 2 | 2500 |

| 2 | Peter Romanenko | Java | 3 | 3500 |
| 3 | Andrei Komarov | C++ | 3 | 2500 |
| 4 | Konstantin Geiko | C# | 2 | 2000 |
| 5 | Asya Suleimanova | UI/UX | 2 | 1800 |
| 6 | Ludmila Geiko | UI/UX | 2 | 1800 |
| 7 | Ivan Ivanov | C# | 1 | 900 |
+----+-------------------+-----------+------------+--------+
Другими словами, мы скопировали все данные из таблицы developers в таблицу developers_clone.
Теперь мы изменим данные в таблице developers воспользовавшись данными из таблицы developers_clone с помощью следующего запроса: mysql> UPDATE developers
SET SALARY = SALARY * 1.25
WHERE EXPERIENCE IN (SELECT EXPERIENCE
FROM developers_clone
WHERE EXPERIENCE >=2);
В результате этого наша таблица содержащая изначальные данные:
+----+-------------------+-----------+------------+--------+
| ID | NAME | SPECIALTY | EXPERIENCE | SALARY |
+----+-------------------+-----------+------------+--------+
| 1 | Eugene Suleimanov | Java | 2 | 2500 |
| 2 | Peter Romanenko | Java | 3 | 3500 |
| 3 | Andrei Komarov | C++ | 3 | 2500 |
| 4 | Konstantin Geiko | C# | 2 | 2000 |
| 5 | Asya Suleimanova | UI/UX | 2 | 1800 |
| 6 | Ludmila Geiko | UI/UX | 2 | 1800 |
| 7 | Ivan Ivanov | C# | 1 | 900 |
+----+-------------------+-----------+------------+--------+
Будет хранить следующие данные:
+----+-------------------+-----------+------------+--------+
| ID | NAME | SPECIALTY | EXPERIENCE | SALARY |
+----+-------------------+-----------+------------+--------+
| 1 | Eugene Suleimanov | Java | 2 | 3125 |
| 2 | Peter Romanenko | Java | 3 | 4375 |
| 3 | Andrei Komarov | C++ | 3 | 3125 |
| 4 | Konstantin Geiko | C# | 2 | 2500 |
| 5 | Asya Suleimanova | UI/UX | 2 | 2250 |
| 6 | Ludmila Geiko | UI/UX | 2 | 2250 |
| 7 | Ivan Ivanov | C# | 1 | 900 |
+----+-------------------+-----------+------------+--------+
И наконец, попробуем выполнить удаление данных из таблицы с помощью вложенного запроса: mysql> DELETE FROM developers

WHERE EXPERIENCE IN (SELECT EXPERIENCE FROM developers_clone
WHERE EXPERIENCE >= 2);
В результате таблица developers содерит следующие записи:
+----+-------------+-----------+------------+--------+
| ID | NAME | SPECIALTY | EXPERIENCE | SALARY |
+----+-------------+-----------+------------+--------+
| 7 | Ivan Ivanov | C# | 1 | 900 |
+----+-------------+-----------+------------+--------+
Очистим таблицу developers: mysql> TRUNCATE developers;
Таблица пуста: mysql> SELECT * FROM developers;
Empty set (0.00 sec)
Теперь восстановим данные таблицы developers, с помощью резервной таблицы developers_clone используя следующий запрос: mysql> INSERT INTO developers
SELECT * FROM developers_clone
WHERE ID IN (SELECT ID
FROM developers_clone);
Наша таблица developers имеет исходный вид: mysql> SELECT * FROM developers;
+----+-------------------+-----------+------------+--------+
| ID | NAME | SPECIALTY | EXPERIENCE | SALARY |
+----+-------------------+-----------+------------+--------+
| 1 | Eugene Suleimanov | Java | 2 | 2500 |
| 2 | Peter Romanenko | Java | 3 | 3500 |
| 3 | Andrei Komarov | C++ | 3 | 2500 |
| 4 | Konstantin Geiko | C# | 2 | 2000 |
| 5 | Asya Suleimanova | UI/UX | 2 | 1800 |
| 6 | Ludmila Geiko | UI/UX | 2 | 1800 |
| 7 | Ivan Ivanov | C# | 1 | 900 |
+----+-------------------+-----------+------------+--------+
7 rows in set (0.00 sec)