изучаем SQL. Она позволяет решать многошаговые задачи одним выражением

Название	Она позволяет решать многошаговые задачи одним выражением
Дата	09.02.2018
Размер	1.6 Mb.
Формат файла
Имя файла	изучаем SQL.pdf
Тип	Документы #36127
страница	20 из 31

1 ... 16 17 18 19 20 21 22 23 ... 31

181
FROM transaction t
WHERE t.account_id = a.account_id);
Подзапрос теперь включает условие фильтрации, связывающее ID сче та транзакции и ID счета из основного запроса. Изменился и блок se lect
– теперь вместо жестко запрограммированного значения 1 он пу тем конкатенации формирует предупреждение, включающее ID счета.
Оператор exists
Связанные подзапросы часто используются в условиях равенства и вхо ждения в диапазон, но самый распространенный оператор, применяе мый в условиях со связанными подзапросами, – это оператор exists (су ществует). Оператор exists применяется, если требуется показать, что связь есть, а количество связей при этом не имеет значения. Например,
следующий запрос находит все счета, для которых транзакция была выполнена в определенный день, без учета количества транзакций:
SELECT a.account_id, a.product_cd, a.cust_id, a.avail_balance
FROM account a
WHERE EXISTS (SELECT 1
FROM transaction t
WHERE t.account_id = a.account_id
AND t.txn_date = '2005 01 22');
При использовании оператора exists подзапрос может возвращать ни одной, одну или много строк, а условие просто проверяет, возвращены ли в результате выполнения подзапроса строки (все равно сколько).
Если взглянуть на блок select подзапроса, можно увидеть, что он со стоит из единственного литерала (1); для условия основного запроса имеет значение только число возвращенных строк, а что именно было возвращено подзапросом – не важно. Подзапрос может возвращать все,
что вам вздумается, как показывает следующий пример:
SELECT a.account_id, a.product_cd, a.cust_id, a.avail_balance
FROM account a
WHERE EXISTS (SELECT t.txn_id, 'hello', 3.1415927
FROM transaction t
WHERE t.account_id = a.account_id
AND t.txn_date = '2005 01 22');
Но все же при использовании exists принято задавать select 1 или select *
Для поиска подзапросов, не возвращающих строки, можно использо вать и оператор not exists:
mysql> SELECT a.account_id, a.product_cd, a.cust_id
> FROM account a
> WHERE NOT EXISTS (SELECT 1
> FROM business b
> WHERE b.cust_id = a.cust_id);

182
Глава 9. Подзапросы
+
+
+
+
| account_id | product_cd | cust_id |
+
+
+
+
| 1 | CHK | 1 |
| 2 | SAV | 1 |
| 3 | CD | 1 |
| 4 | CHK | 2 |
| 5 | SAV | 2 |
| 6 | CHK | 3 |
| 7 | MM | 3 |
| 8 | CHK | 4 |
| 9 | SAV | 4 |
| 10 | MM | 4 |
| 11 | CHK | 5 |
| 12 | CHK | 6 |
| 13 | CD | 6 |
| 14 | CD | 7 |
| 15 | CHK | 8 |
| 16 | SAV | 8 |
| 17 | CHK | 9 |
| 18 | MM | 9 |
| 19 | CD | 9 |
+
+
+
+
19 rows in set (0.04 sec)
Этот запрос выявляет всех клиентов, ID которых нет в таблице busi ness
, – окольный путь для поиска всех клиентов физических лиц.
Манипулирование данными
с помощью связанных подзапросов
До сих пор в этой главе в качестве примеров приводились только выра жения select, но это не значит, что в других SQL выражениях подза просы не используются. Они также широко задействуются в выраже ниях update, delete и insert, а связанные подзапросы часто применяют ся в выражениях update и delete. Вот пример связанного подзапроса,
с помощью которого изменяется столбец last_activity_date таблицы account
:
UPDATE account a
SET a.last_activity_date =
(SELECT MAX(t.txn_date)
FROM transaction t
WHERE t.account_id = a.account_id);
Это выражение корректирует все строки таблицы account (поскольку блока where нет), выбирая дату последней операции для каждого счета.
Хотя кажется разумным ожидать, что для каждого счета будет суще ствовать, по крайней мере, одна связанная с ним операция, но лучше проверить наличие такой операции, прежде чем пытаться обновить столбец last_activity_date. В противном случае в столбце появится

Использование подзапросов
183
значение null, поскольку по подзапросу не будет возвращено ни одной строки. Вот другой вариант выражения update, на этот раз использую щий блок where со вторым связанным подзапросом:
UPDATE account a
SET a.last_activity_date =
(SELECT MAX(t.txn_date)
FROM transaction t
WHERE t.account_id = a.account_id)
WHERE EXISTS (SELECT 1
FROM transaction t
WHERE t.account_id = a.account_id);
Эти два связанных подзапроса идентичны, за исключением блоков se lect
. Однако подзапрос блока set выполняется, только если условие блока where выражения update истинно (true) (т. е. для счета была най дена, по крайней мере, одна операция). Таким образом данные столбца last_activity_date защищены от перезаписи значением null.
Связанные подзапросы обычны и в выражениях delete. Например,
в конце каждого месяца запускается сценарий, уничтожающий не нужные данные. Этот сценарий может включать следующее выраже ние, которое удаляет из таблицы department данные, не имеющие до черних строк в таблице employee:
DELETE FROM department
WHERE NOT EXISTS (SELECT 1
FROM employee
WHERE employee.dept_id = department.dept_id);
При использовании связанных подзапросов в выражениях delete в MySQL необходимо помнить, что псевдонимы таблиц не допускаются ни в коем случае. Вот почему в этом подзапросе приходилось использо вать полное имя таблицы. Для большинства других серверов БД мож но было бы снабдить таблицы department и employee псевдонимами:
DELETE FROM department d
WHERE NOT EXISTS (SELECT 1
FROM employee e
WHERE e.dept_id = d.dept_id);
Использование подзапросов
Теперь, когда вы знакомы с разными типами подзапросов и разными операторами, с помощью которых можно взаимодействовать с табли цами, возвращенными подзапросами, пора рассмотреть множество способов использования подзапросов для построения мощных SQL вы ражений. В следующих трех разделах будет продемонстрировано, как подзапросы могут участвовать в построении специальных таблиц, соз дании условий и формировании столбцов значений в результирующих наборах.

184
Глава 9. Подзапросы
Подзапросы как источники данных
Ранее в главе 3 говорилось, что в блоке from выражения select указыва ются таблицы, которые будут использоваться запросом. Поскольку подзапрос формирует таблицу, содержащую строки и столбцы данных,
абсолютно допустимо включать подзапросы в блок from. Хотя на пер вый взгляд это может показаться любопытной возможностью без осо бых преимуществ, но использование подзапросов в качестве таблиц –
один из самых мощных инструментов, доступных при написании за просов. Вот простой пример:
mysql> SELECT d.dept_id, d.name, e_cnt.how_many num_employees
> FROM department d INNER JOIN
> (SELECT dept_id, COUNT(*) how_many
> FROM employee
> GROUP BY dept_id) e_cnt
> ON d.dept_id = e_cnt.dept_id;
+
+
+
+
| dept_id | name | num_employees |
+
+
+
+
| 1 | Operations | 14 |
| 2 | Loans | 1 |
| 3 | Administration | 3 |
+
+
+
+
3 rows in set (0.04 sec)
В этом примере подзапрос формирует список ID отделов с указанием количества сотрудников, приписанных к каждому отделу. Вот табли ца, сформированная подзапросом:
mysql> SELECT dept_id, COUNT(*) how_many
> FROM employee
> GROUP BY dept_id;
+
+
+
| dept_id | how_many |
+
+
+
| 1 | 14 |
| 2 | 1 |
| 3 | 3 |
+
+
+
3 rows in set (0.00 sec)
Подзапрос назван e_cnt и соединен с таблицей department по столбцу dept_id
. После этого основной запрос извлекает ID отдела и имя из таб лицы department, а также количество сотрудников из подзапроса e_cnt.
Подзапросы, используемые для формирования таблиц, должны быть несвязанными; они выполняются первыми, и полученные таблицы поддерживаются в памяти, пока основной запрос завершает выполне ние. Подзапросы предлагают необычайную гибкость при написании запросов. С их помощью можно выходить далеко за пределы набора доступных таблиц, создавать практически любое представление необ

Использование подзапросов
185
ходимых данных и затем соединять эту таблицу с другими таблицами или таблицами, сформированными подзапросами. Теперь создать от чет или сформировать набор данных для внешних систем можно с по мощью единственного запроса. Раньше для этого требовалось несколь ко запросов или использование процедурного языка.
Формирование таблиц
С помощью подзапросов можно как резюмировать имеющиеся данные,
так и формировать данные, которых в БД нет ни в какой форме. На пример, требуется сгруппировать клиентов по денежным суммам, раз мещенным на депозитных счетах, но использовать для этого описания групп, не хранящиеся в БД. Скажем, надо разбить клиентов на сле дующие группы:
Чтобы сформировать эти группы в рамках одного запроса, потребуется способ определения этих трех групп. Первый шаг – создать запрос,
формирующий описания групп:
mysql> SELECT 'Small Fry' name, 0 low_limit, 4999.99 high_limit
> UNION ALL
> SELECT 'Average Joes' name, 5000 low_limit, 9999.99 high_limit
> UNION ALL
> SELECT 'Heavy Hitters' name, 10000 low_limit, 9999999.99 high_limit;
+
+
+
+
| name | low_limit | high_limit |
+
+
+
+
| Small Fry | 0 | 4999.99 |
| Average Joes | 5000 | 9999.99 |
| Heavy Hitters | 10000 | 9999999.99 |
+
+
+
+
3 rows in set (0.00 sec)
Здесь с помощью оператора для работы с наборами union all (объеди нить все) результаты трех отдельных запросов сводятся в один резуль тирующий набор. Каждый запрос получает три литерала. Результаты этих трех запросов объединяются для формирования таблицы, состоя щей из трех строк и трех столбцов. Теперь у нас есть запрос для форми рования необходимых групп. Его можно поместить в блок from другого запроса для формирования групп клиентов:
mysql> SELECT groups.name, COUNT(*) num_customers
> FROM
> (SELECT SUM(a.avail_balance) cust_balance
Группа
Нижний предел
(долларов)
Верхний предел
(долларов)
Small Fry
(мелкота)
0 4 999,99
Average Joes
(середняки)
5 000 9 999,99
Heavy Hitters
(тяжеловесы)
10 000 9 999 999,99

186
Глава 9. Подзапросы
> FROM account a INNER JOIN product p
> ON a.product_cd = p.product_cd
> WHERE p.product_type_cd = 'ACCOUNT'
> GROUP BY a.cust_id) cust_rollup INNER JOIN
> (SELECT 'Small Fry' name, 0 low_limit, 4999.99 high_limit
> UNION ALL
> SELECT 'Average Joes' name, 5000 low_limit,
> 9999.99 high_limit
> UNION ALL
> SELECT 'Heavy Hitters' name, 10000 low_limit,
> 9999999.99 high_limit) groups
> ON cust_rollup.cust_balance
> BETWEEN groups.low_limit AND groups.high_limit
> GROUP BY groups.name;
+
+
+
| name | num_customers |
+
+
+
| Average Joes | 2 |
| Heavy Hitters | 4 |
| Small Fry | 5 |
+
+
+
3 rows in set (0.01 sec)
В блоке from имеется два подзапроса: первый подзапрос, cust_rollup,
возвращает общий остаток по депозитным счетам для каждого клиента,
а второй подзапрос, groups, формирует таблицу, содержащую три груп пы клиентов. Вот таблица, сгенерированная подзапросом cust_rollup:
mysql> SELECT SUM(a.avail_balance) cust_balance
> FROM account a INNER JOIN product p
> ON a.product_cd = p.product_cd
> WHERE p.product_type_cd = 'ACCOUNT'
> GROUP BY a.cust_id;
+
+
| cust_balance |
+
+
| 4557.75 |
| 2458.02 |
| 3270.25 |
| 6788.98 |
| 2237.97 |
| 10122.37 |
| 5000.00 |
| 3875.18 |
| 10971.22 |
| 23575.12 |
| 38552.05 |
+
+
11 rows in set (0.05 sec)
Затем таблица, сгенерированная подзапросом cust_rollup, соединяется с таблицей groups посредством условия вхождения в диапазон (cust_rol

Использование подзапросов
187
lup.cust_balance
BETWEEN groups.low_limit AND groups.high_limit). Нако нец, соединенные данные группируются и подсчитывается число кли ентов в каждой группе для формирования окончательного результиру ющего набора.
Конечно, можно было бы не использовать подзапрос, а просто создать постоянную таблицу для хранения описаний групп. При таком подхо де через некоторое время БД изобиловала бы небольшими специаль ными таблицами, причины появления которых мало кто помнил бы.
Мне приходилось работать в средах, где пользователям БД позволя лось создавать собственные таблицы для специальных целей. Резуль таты были просто губительными (таблицы, не включенные в резерв ные копии; таблицы, потерянные при обновлениях сервера; простои сервера из за проблем распределения памяти и т. д.). Однако, воору жившись запросами, можно придерживаться политики, при которой таблицы добавляются в БД, только если есть очевидная необходимость хранения новых данных.
Подзапросы, ориентированные на задачи
В системах, используемых для создания отчетов или наборов данных,
часто встречаются следующие запросы:
mysql> SELECT p.name product, b.name branch,
> CONCAT(e.fname, ' ', e.lname) name,
> SUM(a.avail_balance) tot_deposits
> FROM account a INNER JOIN employee e
> ON a.open_emp_id = e.emp_id
> INNER JOIN branch b
> ON a.open_branch_id = b.branch_id
> INNER JOIN product p
> ON a.product_cd = p.product_cd
> WHERE p.product_type_cd = 'ACCOUNT'
> GROUP BY p.name, b.name, e.fname, e.lname;
+
+
+
+
+
| product | branch | name | tot_deposits |
+
+
+
+
+
| certificate of deposit | Headquarters | Michael Smith | 11500.00 |
| certificate of deposit | Woburn Branch | Paula Roberts | 8000.00 |
| checking account | Headquarters | Michael Smith | 782.16 |
| checking account | Quincy Branch | John Blake | 1057.75 |
| checking account | So. NH Branch | Theresa Markham | 67852.33 |
| checking account | Woburn Branch | Paula Roberts | 3315.77 |
| money market account | Headquarters | Michael Smith | 14832.64 |
| money market account | Quincy Branch | John Blake | 2212.50 |
| savings account | Headquarters | Michael Smith | 767.77 |
| savings account | So. NH Branch | Theresa Markham | 387.99 |
| savings account | Woburn Branch | Paula Roberts | 700.00 |
+
+
+
+
+
11 rows in set (0.02 sec)

188
Глава 9. Подзапросы
Этот запрос суммирует все остатки депозитных счетов по типу счета,
сотруднику, открывшему счета, и отделениям, в которых были откры ты счета. Если внимательнее посмотреть на запрос, увидим, что табли цы product, branch и employee нужны только в целях отображения и что все необходимое для группировки (product_cd, open_branch_id, open_emp_id и avail_balance) есть в таблице account. Поэтому задачу по формирова нию групп можно было бы выделить в подзапрос, а затем для получе ния нужного результата соединить остальные три таблицы с табли цей, сгенерированной подзапросом. Вот подзапрос группировки:
mysql> SELECT product_cd, open_branch_id branch_id, open_emp_id emp_id,
> SUM(avail_balance) tot_deposits
> FROM account
> GROUP BY product_cd, open_branch_id, open_emp_id;
+
+
+
+
+
| product_cd | branch_id | emp_id | tot_deposits |
+
+
+
+
+
| BUS | 2 | 10 | 9345.55 |
| BUS | 4 | 16 | 0.00 |
| CD | 1 | 1 | 11500.00 |
| CD | 2 | 10 | 8000.00 |
| CHK | 1 | 1 | 782.16 |
| CHK | 2 | 10 | 3315.77 |
| CHK | 3 | 13 | 1057.75 |
| CHK | 4 | 16 | 67852.33 |
| MM | 1 | 1 | 14832.64 |
| MM | 3 | 13 | 2212.50 |
| SAV | 1 | 1 | 767.77 |
| SAV | 2 | 10 | 700.00 |
| SAV | 4 | 16 | 387.99 |
| SBL | 3 | 13 | 50000.00 |
+
+
+
+
+
14 rows in set (0.01 sec)
Это – сердце запроса; все остальные таблицы нужны только для того,
чтобы обеспечить осмысленные строки для шапки таблицы вместо имен столбцов внешних ключей product_cd, open_branch_id и open_emp_id. Сле дующий запрос включает запрос к таблице account в качестве подзапроса и соединяет результирующую таблицу с тремя остальными таблицами:
mysql> SELECT p.name product, b.name branch,
> CONCAT(e.fname, ' ', e.lname) name,
> account_groups.tot_deposits
> FROM
> (SELECT product_cd, open_branch_id branch_id,
> open_emp_id emp_id,
> SUM(avail_balance) tot_deposits
> FROM account
> GROUP BY product_cd, open_branch_id, open_emp_id) account_groups
> INNER JOIN employee e ON e.emp_id = account_groups.emp_id
> INNER JOIN branch b ON b.branch_id = account_groups.branch_id

1 ... 16 17 18 19 20 21 22 23 ... 31