alan_beaulieu-learning_sql-ru (865932), страница 20
Текст из файла (страница 20)
NH Branch ||17 | Beth| Fowler| So. NH Branch ||18 | Rick| Tulman| So. NH Branch |+++++18 rows in set (0.03 sec)5.2Напишите запрос, по которому для каждого клиента физического ли ца (customer.cust_type_cd = 'I') возвращаются ID счета, федеральный ID(customer.fed_id) и тип созданного счета (product.name).5.3Создайте запрос для выбора всех сотрудников, начальник которых при писан к другому отделу. Извлеките ID, имя и фамилию сотрудника.Работа с множествамиХотя можно работать и с отдельными строками данных, реляционныеБД на самом деле приспособлены для работы с наборами (множества ми).
Вы уже видели, как можно создавать таблицы посредством запро сов или подзапросов, делать их постоянными с помощью выраженийinsert и сводить вместе через соединения. В данной главе будут иссле дованы комбинации нескольких таблиц с использованием различныхоператоров работы с множествами.Основы теории множествВо многих странах основы теории множеств включены в программыначального курса математики. Возможно, кое что на рис. 6.1 покажет ся вам знакомым.AB= 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.Искомое множество включает множества А и В без области пересече ния. Такое множество не может быть получено в результате ни однойиз трех представленных ранее операций. Понадобится сначала создатьAB= A intersect BРис.
6.2. Операция пересечения110Глава 6. Работа с множествамиAB= A except BРис. 6.3. Операция разностиAB= ????Рис. 6.4. Загадочное множество данныхмножество данных, объединяющее множества А и В целиком, а затемприменить вторую операцию, чтобы удалить область пересечения. Ес ли составное множество описать как A union B, а область пересечения –как A intersect B, операция, необходимая для формирования представ ленного на рис. 6.4 множества, выглядела бы так:(A union B) except (A intersect B)Конечно, часто есть несколько способов получения одного и того жерезультата.
Аналогичное множество можно было бы получить с помо щью следующей операции:(A except B) union (B except A)Теория множеств на практике111Эти концепции, наглядно представленные диаграммами, достаточнопросты для понимания. В следующих разделах будет показано, какэти идеи реализуются в реляционных СУБД с помощью SQL операто ров работы с множествами.Теория множеств на практикеКруги, представляющие множества данных на диаграммах предыду щего раздела, никак не отражают содержимое множеств. Однако приработе с реальными данными необходимо описывать структуру таб лиц, если предполагается их комбинирование. Представим, например,что произошло бы при попытке сгенерировать объединение описанныхниже таблиц product и customer:mysql> DESC product;+++++++| Field| Type| Null | Key | Default | Extra |+++++++| product_cd| varchar(10) || PRI |||| name| varchar(50) |||||| product_type_cd | varchar(10) || MUL |||| date_offered| date| YES || NULL||| date_retired| date| YES || NULL||+++++++5 rows in set (0.23 sec)mysql> DESC customer;+++++++| Field| Type| Null | Key | Default | Extra|+++++++| cust_id| int(10) unsigned || PRI | NULL| auto_increment || fed_id| varchar(12)|||||| cust_type_cd | enum('I','B')||| I||| address| varchar(30)| YES || NULL||| city| varchar(20)| YES || NULL||| state| varchar(20)| YES || NULL||| postal_code | varchar(10)| YES || NULL||+++++++7 rows in set (0.04 sec)После комбинирования первый столбец результирующей таблицы былбы комбинацией столбцов product.product_cd и customer.cust_id, второй –комбинацией столбцов product.name и customer.fed_id и т.
д. Хотя неко торые пары столбцов сочетаются без труда (т. е. два столбца числовоготипа), неясно, как должны объединяться пары столбцов разного типа,такие как числовой со строковым или строковый с датой. Кроме того,в шестом и седьмом столбцах комбинированной таблицы будут толькоданные шестого и седьмого столбцов таблицы customer, поскольку в таб лице product всего пять столбцов. Очевидно, что таблицы, подлежащиеобъединению, должны обладать некоторой общностью.112Глава 6. Работа с множествамиПоэтому при применении операций с множествами к реальным табли цам необходимо соблюдать такие правила:• В обеих таблицах должно быть одинаковое число столбцов.• Типы данных столбцов двух таблиц должны быть одинаковыми(или сервер должен уметь преобразовывать один тип в другой).Эти правила позволяют уяснить, что представляет собой «перекрытиеданных» на практике.
Чтобы комбинируемые строки двух таблиц счи тались одинаковыми, каждая пара столбцов комбинируемых таблицдолжна содержать одинаковые строки, числа или даты.Операции с множествами осуществляются путем помещения оператора работы с множествами (set operator) между двух выражений select, как показано ниже: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, указывает серверу БД объединить все строкидвух таблиц. Таким образом, конечная таблица включает две строкии два столбца. Такой запрос называют составным запросом (compoundquery), потому что он объединяет несколько независимых запросов.Как будет показано позже, если для получения окончательного ре зультата требуется выполнить несколько операций с множествами, со ставные запросы могут включать больше двух запросов.Операторы работы с множествамиЯзык SQL включает три оператора работы с множествами, позволяю щие осуществлять всевозможные операции над множествами, уже упо мянутые в этой главе.
Кроме того, у каждого из этих операторов естьдве разновидности: первая включает дублирующие данные, а втораяудаляет их (но необязательно все). В следующих разделах даны опреде ления всех операторов и показано их применение.Оператор unionОператоры union (объединить) и union all (объединить все) позволяюткомбинировать несколько таблиц. Разница в том, что если требуетсяобъединить две таблицы, включая в окончательный результат все ихОператоры работы с множествами113строки, даже дублирующие значения, нужно использовать операторunion all. Благодаря оператору union all в конечной таблице всегда бу дет столько строк, сколько во всех исходных таблицах в сумме. Эта опе рация – самая простая из всех операций работы с множествами (с точкизрения сервера), поскольку серверу не приходится проверять перекры вающиеся данные.
Следующий пример демонстрирует применение опе ратора union all для формирования полного множества данных клиен тов из двух таблиц подтипов клиентов:mysql> SELECT cust_id, lname name> FROM individual> UNION ALL> SELECT cust_id, name> FROM business;+++| cust_id | name|+++|1 | Hadley||2 | Tingley||3 | Tucker||4 | Hayward||5 | Frasier||6 | Spencer||7 | Young||8 | Blake||9 | Farley||10 | Chilton Engineering||11 | Northeast Cooling Inc. ||12 | Superior Auto Body||13 | AAA Insurance Inc.|+++13 rows in set (0.04 sec)Запрос возвращает все 13 клиентов: 9 строк поступают из таблицы individual (физические лица), а остальные 4 – из таблицы business (юри дические лица). Таблица business включает всего один столбец с назва нием компании, а в таблице individual присутствуют два столбца: имяи фамилия физического лица.
В данном случае из таблицы individualберется только фамилия.Проверим, что оператор union all не удаляет дублирующие значения.Для этого приведем такой же запрос, как в предыдущем примере, нос дополнительным запросом к таблице business:mysql>>>>>>>>SELECT cust_id, lname nameFROM individualUNION ALLSELECT cust_id, nameFROM businessUNION ALLSELECT cust_id, nameFROM business;114Глава 6. Работа с множествами+++| cust_id | name|+++|1 | Hadley||2 | Tingley||3 | Tucker||4 | Hayward||5 | Frasier||6 | Spencer||7 | Young||8 | Blake||9 | Farley||10 | Chilton Engineering||11 | Northeast Cooling Inc. ||12 | Superior Auto Body||13 | AAA Insurance Inc.||10 | Chilton Engineering||11 | Northeast Cooling Inc. ||12 | Superior Auto Body||13 | AAA Insurance Inc.|+++17 rows in set (0.01 sec)Этот составной запрос включает три выражения select, два из которыхидентичны.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
