alan_beaulieu-learning_sql-ru (865932), страница 32

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 32)

И подзапрос, и основной запрос включают условие фильтрации account_id = 1. Таким образом, запрос в его теперешней формебудет проверять только по одному счету за раз. В следующем разделемы научимся создавать более общую форму запроса, которая будетпроверять все счета за одно выполнение.Связанные подзапросыВсе приведенные до сих пор запросы не зависели от своих содержащихвыражений, т. е. могли выполняться самостоятельно и представлятьсвои результаты для проверки. Связанный подзапрос (correlated subquery), напротив, зависит от содержащего выражения, из которого онссылается на один или более столбцов. В отличие от несвязанного подзапроса, который выполняется непосредственно перед выполнениемсодержащего выражения, связанный подзапрос выполняется по разудля каждой строкикандидата (это строки, которые предположительно могут быть включены в окончательные результаты).

Например,следующий запрос использует связанный подзапрос для подсчета количества счетов у каждого клиента. Затем основной запрос выбираеттех клиентов, у которых ровно по два счета:mysql> SELECT c.cust_id, c.cust_type_cd, c.city> FROM customer c> WHERE 2 = (SELECT COUNT(*)> FROM account a> WHERE a.cust_id = c.cust_id);++++| cust_id | cust_type_cd | city|++++|2 | I| Woburn ||3 | I| Quincy ||6 | I| Waltham ||8 | I| Salem ||10 | B| Salem |++++5 rows in set (0.01 sec)Ссылка на c.cust_id в самом конце подзапроса – это то, что делает этотподзапрос связанным. Чтобы подзапрос мог выполняться, основнойзапрос должен поставлять значения для c.cust_id. В данном случае основной запрос извлекает из таблицы customer все 13 строк и выполняетпо одному подзапросу для всех клиентов, передавая в него соответст180Глава 9.

Подзапросывующий ID клиента при каждом выполнении. Если подзапрос возвращает значение 2, условие фильтрации выполняется и строка добавляется в результирующий набор.Помимо условий равенства связанные подзапросы можно применятьв условиях других типов, таких как условие вхождения в диапазон,проиллюстрированное ниже:mysql> SELECT c.cust_id, c.cust_type_cd, c.city> FROM customer c> WHERE (SELECT SUM(a.avail_balance)>FROM account a>WHERE a.cust_id = c.cust_id)> BETWEEN 5000 AND 10000;++++| cust_id | cust_type_cd | city|++++|4 | I| Waltham||7 | I| Wilmington ||11 | B| Wilmington |++++3 rows in set (0.02 sec)Этот вариант приведенного ранее запроса находит всех клиентов, чейобщий доступный остаток по всем счетам находится в диапазоне от5000 до 10 000 долларов. И снова связанный подзапрос выполняется13 раз (по разу для каждой строки), и каждое выполнение подзапросавозвращает общий остаток по счетам данного клиента.В конце предыдущего раздела было продемонстрировано, как проверять доступный и отложенный остатки счета по транзакциям, зарегистрированным по данному счету, и я обещал показать, как изменитьпример для обработки всех счетов за одно выполнение.

Вот тот пример:SELECT 'ALERT! : Account #1 Has Incorrect Balance!'FROM accountWHERE (avail_balance, pending_balance) <>(SELECT SUM(<expression to generate available balance>),SUM(<expression to generate pending balance>)FROM transactionWHERE account_id = 1)AND account_id = 1;При использовании связанного подзапроса вместо несвязанного основной запрос может выполняться всего лишь один раз, а подзапрос будетвыполняться для каждого счета. Вот обновленная версия:SELECT CONCAT('ALERT! : Account #', a.account_id,' Has Incorrect Balance!')FROM account aWHERE (a.avail_balance, a.pending_balance) <>(SELECT SUM(<expression to generate available balance>),SUM(<expression to generate pending balance>)181Связанные подзапросыFROM transaction tWHERE t.account_id = a.account_id);Подзапрос теперь включает условие фильтрации, связывающее ID счета транзакции и ID счета из основного запроса. Изменился и блок select – теперь вместо жестко запрограммированного значения 1 он путем конкатенации формирует предупреждение, включающее ID счета.Оператор existsСвязанные подзапросы часто используются в условиях равенства и вхождения в диапазон, но самый распространенный оператор, применяемый в условиях со связанными подзапросами, – это оператор exists (существует).

Оператор exists применяется, если требуется показать, чтосвязь есть, а количество связей при этом не имеет значения. Например,следующий запрос находит все счета, для которых транзакция былавыполнена в определенный день, без учета количества транзакций:SELECT a.account_id,FROM account aWHERE EXISTS (SELECTFROM transaction tWHERE t.account_idAND t.txn_date =a.product_cd, a.cust_id, a.avail_balance1= a.account_id'20050122');При использовании оператора exists подзапрос может возвращать ниодной, одну или много строк, а условие просто проверяет, возвращеныли в результате выполнения подзапроса строки (все равно сколько).Если взглянуть на блок select подзапроса, можно увидеть, что он состоит из единственного литерала (1); для условия основного запросаимеет значение только число возвращенных строк, а что именно быловозвращено подзапросом – не важно.

Подзапрос может возвращать все,что вам вздумается, как показывает следующий пример:SELECT a.account_id,FROM account aWHERE EXISTS (SELECTFROM transaction tWHERE t.account_idAND t.txn_date =a.product_cd, a.cust_id, a.avail_balancet.txn_id, 'hello', 3.1415927= a.account_id'20050122');Но все же при использовании 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 которых нет в таблице business, – окольный путь для поиска всех клиентовфизических лиц.Манипулирование даннымис помощью связанных подзапросовДо сих пор в этой главе в качестве примеров приводились только выражения select, но это не значит, что в других SQLвыражениях подзапросы не используются.

Они также широко задействуются в выражениях update, delete и insert, а связанные подзапросы часто применяются в выражениях update и delete. Вот пример связанного подзапроса,с помощью которого изменяется столбец last_activity_date таблицыaccount:UPDATE account aSET a.last_activity_date =(SELECT MAX(t.txn_date)FROM transaction tWHERE t.account_id = a.account_id);Это выражение корректирует все строки таблицы account (посколькублока where нет), выбирая дату последней операции для каждого счета.Хотя кажется разумным ожидать, что для каждого счета будет существовать, по крайней мере, одна связанная с ним операция, но лучшепроверить наличие такой операции, прежде чем пытаться обновитьстолбец last_activity_date.

В противном случае в столбце появитсяИспользование подзапросов183значение null, поскольку по подзапросу не будет возвращено ни однойстроки. Вот другой вариант выражения update, на этот раз использующий блок where со вторым связанным подзапросом:UPDATE account aSET a.last_activity_date =(SELECT MAX(t.txn_date)FROM transaction tWHERE t.account_id = a.account_id)WHERE EXISTS (SELECT 1FROM transaction tWHERE t.account_id = a.account_id);Эти два связанных подзапроса идентичны, за исключением блоков select. Однако подзапрос блока set выполняется, только если условиеблока where выражения update истинно (true) (т.

е. для счета была найдена, по крайней мере, одна операция). Таким образом данные столбцаlast_activity_date защищены от перезаписи значением null.Связанные подзапросы обычны и в выражениях delete. Например,в конце каждого месяца запускается сценарий, уничтожающий ненужные данные. Этот сценарий может включать следующее выражение, которое удаляет из таблицы department данные, не имеющие дочерних строк в таблице employee:DELETE FROM departmentWHERE NOT EXISTS (SELECT 1FROM employeeWHERE employee.dept_id = department.dept_id);При использовании связанных подзапросов в выражениях deleteв MySQL необходимо помнить, что псевдонимы таблиц не допускаютсяни в коем случае.

Вот почему в этом подзапросе приходилось использовать полное имя таблицы. Для большинства других серверов БД можно было бы снабдить таблицы department и employee псевдонимами:DELETE FROM department dWHERE NOT EXISTS (SELECT 1FROM employee eWHERE 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 отделов с указаниемколичества сотрудников, приписанных к каждому отделу.

Характеристики

Список файлов ответов (шпаргалок)