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

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

1 ... 8 9 10 11 12 13 14 15 ... 31

103
торого псевдонимы таблиц являются обязательными. В противном случае сервер не сможет определить, на кого делается ссылка – на со трудника или его начальника.
Хотя в таблице employee 18 строк, по запросу было возвращено только 17.
У президента банка, Майкла Смита (Michael Smith), нет начальника
(его столбец superior_emp_id имеет значение null), поэтому для данной строки соединение не сработало. Чтобы включить Майкла Смита в ре зультирующий набор, необходимо использовать внешнее соединение,
которое будет рассмотрено в главе 10.
Сравнение эквисоединений
с неэквисоединениями
Все запросы к нескольким таблицам, показанные до сих пор, исполь зовали эквисоединения (equi joins). Это означает, что для обеспечения успешности соединения значения двух таблиц должны совпадать. Эк висоединение всегда использует знак равенства, например:
ON e.assigned_branch_id = b.branch_id
Подавляющее большинство запросов использует эквисоединения, но можно также соединять таблицы посредством диапазонов значений,
называемых неэквисоединениями (non equi joins). Вот пример запроса,
осуществляющего соединение по диапазону значений:
SELECT e.emp_id, e.fname, e.lname, e.start_date
FROM employee e INNER JOIN product p
ON e.start_date >= p.date_offered
AND e.start_date <= p.date_retired
WHERE p.name = 'no fee checking';
Этот запрос соединяет две таблицы, между которыми нет взаимосвя зей по внешним ключам. Задача – найти всех сотрудников, принятых в банк в то время, когда предлагалась услуга беспроцентного текущего вклада. Таким образом, дата начала работы сотрудника должна нахо диться между датами начала и конца этой акции.
Также может понадобиться рекурсивное неэквисоединение (self non
equi join
), которое означает, что таблица соединяется сама с собой с ис пользованием неэквисоединения. Например, управляющий операция ми решил провести шахматный турнир между всеми операциониста ми банка. Требуется создать список всех пар игроков. Можно попробо вать получить список всех операционистов (title = 'Teller'), соединив таблицу employee с самой собой, и выбрать из него все строки с разными значениями emp_id (поскольку игрок не может составить пару с самим собой):
mysql> SELECT e1.fname, e1.lname, 'VS' vs, e2.fname, e2.lname
> FROM employee e1 INNER JOIN employee e2

106
Глава 5. Запрос к нескольким таблицам mysql> SELECT a.account_id, a.product_cd, c.fed_id
> FROM account a INNER JOIN customer c
> ON a.cust_id = c.cust_id
> WHERE c.cust_type_cd = 'B';
+
+
+
+
| account_id | product_cd | fed_id |
+
+
+
+
| 20 | CHK | 04 1111111 |
| 21 | BUS | 04 1111111 |
| 22 | BUS | 04 2222222 |
| 23 | CHK | 04 3333333 |
| 24 | SBL | 04 4444444 |
+
+
+
+
5 rows in set (0.08 sec)
Достаточно просто, но что произойдет, если по ошибке поместить усло вие фильтрации в подблок on, а не в блок where?
mysql> SELECT a.account_id, a.product_cd, c.fed_id
> FROM account a INNER JOIN customer c
> ON a.cust_id = c.cust_id
> AND c.cust_type_cd = 'B';
+
+
+
+
| account_id | product_cd | fed_id |
+
+
+
+
| 20 | CHK | 04 1111111 |
| 21 | BUS | 04 1111111 |
| 22 | BUS | 04 2222222 |
| 23 | CHK | 04 3333333 |
| 24 | SBL | 04 4444444 |
+
+
+
+
5 rows in set (0.00 sec)
Как видите, второй вариант, в котором оба условия находятся в подбло ке on и нет блока where, обеспечивает аналогичные результаты. А что ес ли оба условия помещены в блок where, но блок from по прежнему ис пользует ANSI синтаксис соединения?
mysql> SELECT a.account_id, a.product_cd, c.fed_id
> FROM account a INNER JOIN customer c
> WHERE a.cust_id = c.cust_id
> AND c.cust_type_cd = 'B';
+
+
+
+
| account_id | product_cd | fed_id |
+
+
+
+
| 20 | CHK | 04 1111111 |
| 21 | BUS | 04 1111111 |
| 22 | BUS | 04 2222222 |
| 23 | CHK | 04 3333333 |
| 24 | SBL | 04 4444444 |
+
+
+
+
5 rows in set (0.00 sec)

Работа с множествами
Хотя можно работать и с отдельными строками данных, реляционные
БД на самом деле приспособлены для работы с наборами (множества ми). Вы уже видели, как можно создавать таблицы посредством запро сов или подзапросов, делать их постоянными с помощью выражений insert и сводить вместе через соединения. В данной главе будут иссле дованы комбинации нескольких таблиц с использованием различных операторов работы с множествами.
Основы теории множеств
Во многих странах основы теории множеств включены в программы начального курса математики. Возможно, кое что на рис. 6.1 покажет ся вам знакомым.
A
B
= A union B
Рис. 6.1. Операция объединения

Основы теории множеств
109
Заштрихованные области на рис. 6.1 представляют объединение (union)
множеств А и В, которое является комбинацией двух множеств (при этом все пересекающиеся области включены только один раз). Что то припоминаете? Если да, то наконец появился шанс применить эти зна ния на практике. Если нет, не волнуйтесь, потому что без труда пойме те все, взглянув на пару диаграмм.
Представим множества (А и В) в виде кругов; область перекрытия представляет подмножество данных, общих для обоих множеств
(рис. 6.1). Поскольку без перекрытий множеств данных теория мно жеств совершенно неинтересна, я буду использовать такую же диа грамму для иллюстрации всех операций с множествами. Есть другая операция, результат которой – только перекрытие двух множеств данных. Эту операцию называют пересечением (intersection) (рис. 6.2).
Множество данных, получаемое в результате пересечения множеств А
и В, – это собственно область перекрытия между двумя множествами.
Если два множества не перекрываются, операция пересечения дает пустое множество.
Третья и последняя операция с множествами (рис. 6.3) известна как операция разности (except). На рис. 6.3 показан результат операции
A
except B, который представляет собой множество А минус все пересе чения с множеством В. Если два множества не пересекаются, в резуль тате операции A except B будет получено полное множество А.
Применяя эти три операции или их сочетания, можно получать любые нужные результаты. Например, представим, что требуется создать множество, показанное на рис. 6.4.
Искомое множество включает множества А и В без области пересече ния. Такое множество не может быть получено в результате ни одной из трех представленных ранее операций. Понадобится сначала создать
A
B
= A intersect B
Рис. 6.2. Операция пересечения

110
Глава 6. Работа с множествами множество данных, объединяющее множества А и В целиком, а затем применить вторую операцию, чтобы удалить область пересечения. Ес ли составное множество описать как A union B, а область пересечения –
как A intersect B, операция, необходимая для формирования представ ленного на рис. 6.4 множества, выглядела бы так:
(A union B) except (A intersect B)
Конечно, часто есть несколько способов получения одного и того же результата. Аналогичное множество можно было бы получить с помо щью следующей операции:
(A except B) union (B except A)
A
B
= A except B
Рис. 6.3. Операция разности
A
B
= ????
Рис. 6.4. Загадочное множество данных

112
Глава 6. Работа с множествами
Поэтому при применении операций с множествами к реальным табли цам необходимо соблюдать такие правила:
•
В обеих таблицах должно быть одинаковое число столбцов.
•
Типы данных столбцов двух таблиц должны быть одинаковыми
(или сервер должен уметь преобразовывать один тип в другой).
Эти правила позволяют уяснить, что представляет собой «перекрытие данных» на практике. Чтобы комбинируемые строки двух таблиц счи тались одинаковыми, каждая пара столбцов комбинируемых таблиц должна содержать одинаковые строки, числа или даты.
Операции с множествами осуществляются путем помещения операто
ра работы с множествами
(set operator) между двух выражений se lect
, как показано ниже:
mysql> SELECT 1 num, 'abc' str
> UNION
> SELECT 9 num, 'xyz' str;
+
+
+
| num | str |
+
+
+
| 1 | abc |
| 9 | xyz |
+
+
+
2 rows in set (0.02 sec)
Каждый запрос формирует таблицу, состоящую из единственной стро ки с числовым и строковым столбцами. Оператор работы с множества ми, в данном случае union, указывает серверу БД объединить все строки двух таблиц. Таким образом, конечная таблица включает две строки и два столбца. Такой запрос называют составным запросом (compound
query
), потому что он объединяет несколько независимых запросов.
Как будет показано позже, если для получения окончательного ре зультата требуется выполнить несколько операций с множествами, со ставные запросы могут включать больше двух запросов.
Операторы работы с множествами
Язык SQL включает три оператора работы с множествами, позволяю щие осуществлять всевозможные операции над множествами, уже упо мянутые в этой главе. Кроме того, у каждого из этих операторов есть две разновидности: первая включает дублирующие данные, а вторая удаляет их (но необязательно все). В следующих разделах даны опреде ления всех операторов и показано их применение.

1 ... 8 9 10 11 12 13 14 15 ... 31