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

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

Оператор 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 строк поступают из таблицы in dividual
(физические лица), а остальные 4 – из таблицы business (юри дические лица). Таблица business включает всего один столбец с назва нием компании, а в таблице individual присутствуют два столбца: имя и фамилия физического лица. В данном случае из таблицы individual берется только фамилия.
Проверим, что оператор union all не удаляет дублирующие значения.
Для этого приведем такой же запрос, как в предыдущем примере, но с дополнительным запросом к таблице business:
mysql> SELECT cust_id, lname name
> FROM individual
> UNION ALL
> SELECT cust_id, name
> FROM business
> UNION ALL
> SELECT cust_id, name
> FROM 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, два из которых идентичны. Как видно по результатам, четыре строки из таблицы bu siness включены дважды (ID клиентов 10, 11, 12 и 13).
Поскольку вряд ли вы когда нибудь дважды включите один и тот же запрос в составной запрос, вот другой пример составного запроса, по которому возвращаются дублирующие данные:
mysql> SELECT emp_id
> FROM employee
> WHERE assigned_branch_id = 2
> AND (title = 'Teller' OR title = 'Head Teller')
> UNION ALL
> SELECT DISTINCT open_emp_id
> FROM account
> WHERE open_branch_id = 2;
+
+
| emp_id |
+
+
| 10 |
| 11 |
| 12 |
| 10 |
+
+
4 rows in set (0.01 sec)
Первый запрос составного выражения выбирает всех операционистов отделения Woburn, а второй возвращает другое множество – операцио нистов, открывавших счета в отделении Woburn. Из четырех строк ре

Операторы работы с множествами
115
зультирующего набора одна дублируется (ID сотрудника – 10). Если бы потребовалось исключить дублирующие строки из составной таб лицы, вместо оператора union all надо было бы использовать оператор union
:
mysql> SELECT emp_id
> FROM employee
> WHERE assigned_branch_id = 2
> AND (title = 'Teller' OR title = 'Head Teller')
> UNION
> SELECT DISTINCT open_emp_id
> FROM account
> WHERE open_branch_id = 2;
+
+
| emp_id |
+
+
| 10 |
| 11 |
| 12 |
+
+
3 rows in set (0.01 sec)
Для данной версии запроса с применением оператора union all в резуль тирующий набор включаются только три разные строки, а не четыре
(три разные, одна дублирующаяся).
Оператор intersect
Спецификация SQL ANSI включает оператор intersect (пересечение),
предназначенный для выполнения пересечений. К сожалению, MySQL
версии 4.1 не реализует оператор intersect. Oracle (но не SQL Server) по зволяет использовать intersect. Однако поскольку для всех примеров данной книги используется MySQL, результирующие наборы для при меров запросов в данном разделе являются вымышленными и не могут быть получены в MySQL до версии 5.0 включительно. Здесь не показа но приглашение MySQL (mysql>), потому что эти выражения не выпол няются сервером MySQL.
Если два запроса составного запроса возвращают неперекрывающиеся таблицы, пересечением будет пустое множество. Рассмотрим следую щий запрос:
SELECT emp_id, fname, lname
FROM employee
INTERSECT
SELECT cust_id, fname, lname
FROM individual;
Empty set (0.04 sec)
Первый запрос возвращает ID и имя каждого сотрудника, а второй – ID
и имя каждого клиента. Это абсолютно неперекрывающиеся множест ва, поэтому пересечение двух этих множеств и дает пустое множество.

116
Глава 6. Работа с множествами
Второй шаг – выявить два запроса, действительно имеющих пере крывающиеся данные, и затем применить оператор intersect. Для это го используем тот же запрос, что и для демонстрации разницы между union и union all, только на этот раз возьмем оператор intersect:
SELECT emp_id
FROM employee
WHERE assigned_branch_id = 2
AND (title = 'Teller' OR title = 'Head Teller')
INTERSECT
SELECT DISTINCT open_emp_id
FROM account
WHERE open_branch_id = 2;
+
+
| emp_id |
+
+
| 10 |
+
+
1 row in set (0.01 sec)
Пересечение этих двух запросов дает сотрудника с ID равным 10, что является единственным значением, имеющимся в результирующих наборах обоих запросов.
Наряду с оператором intersect, удаляющим все дублирующие строки области перекрытия, спецификация SQL ANSI предлагает оператор in tersect all, не удаляющий дубликаты. Единственный сервер БД, в на стоящее время реализующий оператор intersect all, – DB2 Universal
Server компании IBM.
Оператор except
Спецификация SQL ANSI включает оператор except (разность), пред назначенный для выполнения операции разности. Опять же, к сожа лению, MySQL версии 4.1 не реализует оператор except, поэтому в дан ном разделе действуют те же соглашения, что и в предыдущем.
При работе с Oracle Database вам понадобится использовать опе ратор minus (минус), не совместимый со спецификацией ANSI.
Операция except возвращает первую таблицу за вычетом всех перекры тий со второй таблицей. Вот пример из предыдущего раздела, но с опе ратором except вместо intersect:
SELECT emp_id
FROM employee
WHERE assigned_branch_id = 2
AND (title = 'Teller' OR title = 'Head Teller')
EXCEPT
SELECT DISTINCT open_emp_id
FROM account

Операторы работы с множествами
117
WHERE open_branch_id = 2;
+
+
| emp_id |
+
+
| 11 |
| 12 |
+
+
2 rows in set (0.01 sec)
В этом варианте запроса результирующий набор включает три строки из результирующего набора первого запроса минус сотрудник с ID,
равным 10, который присутствует в результирующих наборах обоих запросов. В спецификации SQL ANSI также описан оператор except all,
но опять же он реализован только в DB2 Universal Server IBM.
В операторе except all есть небольшая хитрость. Вот пример, показы вающий, как обрабатываются дублирующие данные. Скажем, есть два множества данных, имеющих следующий вид:
Множество A
+
+
| emp_id |
+
+
| 10 |
| 11 |
| 12 |
| 10 |
| 10 |
+
+
Множество B
+
+
| emp_id |
+
+
| 10 |
| 10 |
+
+
В результате операции A except B получаем следующее:
+
+
| emp_id |
+
+
| 11 |
| 12 |
+
+
Если изменить операцию и применить A except all B, увидим следующее:
+
+
| emp_id |
+
+
| 10 |
| 11 |

118
Глава 6. Работа с множествами
| 12 |
+
+
Следовательно, разница между этими двумя операциями в том, что ex cept удаляет все экземпляры дублирующихся данных из множества А,
тогда как except all удаляет из множества А только один экземпляр дуб ликата данных для каждого экземпляра дубликата данных множества В.
Правила операций с множествами
В следующих разделах обозначены некоторые правила, которых необ ходимо придерживаться при работе с составными запросами.
Результаты сортирующего составного запроса
Если требуется сортировать результаты составного запроса, после по следнего входящего в него запроса можно добавить блок order by. В бло ке order by указываются имена столбцов из первого запроса составного запроса. До сих пор в каждом примере главы имена столбцов в обоих запросах составного запроса совпадали, но так делать не обязательно,
что и показывает следующий пример:
mysql> SELECT emp_id, assigned_branch_id
> FROM employee
> WHERE title = 'Teller'
> UNION
> SELECT open_emp_id, open_branch_id
> FROM account
> WHERE product_cd = 'SAV'
> ORDER BY emp_id;
+
+
+
| emp_id | assigned_branch_id |
+
+
+
| 1 | 1 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 10 | 2 |
| 11 | 2 |
| 12 | 2 |
| 14 | 3 |
| 15 | 3 |
| 16 | 4 |
| 17 | 4 |
| 18 | 4 |
+
+
+
12 rows in set (0.04 sec)
В этом примере в двух запросах заданы разные имена столбцов. Если в блоке order by указать имя столбца из второго запроса, будет получе на следующая ошибка:

Правила операций с множествами
119
mysql> SELECT emp_id, assigned_branch_id
> FROM employee
> WHERE title = 'Teller'
> UNION
> SELECT open_emp_id, open_branch_id
> FROM account
> WHERE product_cd = 'SAV'
> ORDER BY open_emp_id;
ERROR 1054 (42S22): Unknown column 'open_emp_id' in 'order clause'
Чтобы избежать этой проблемы, рекомендуется в обоих запросах да вать столбцам одинаковые псевдонимы.
Старшинство операций с множествами
Если в составном запросе больше двух запросов, использующих разные операторы работы с множествами, то для обеспечения желаемых ре зультатов следует продумать порядок расположения этих запросов в со ставном выражении. Рассмотрим следующее составное выражение из трех запросов:
mysql> SELECT cust_id
> FROM account
> WHERE product_cd IN ('SAV', 'MM')
> UNION ALL
> SELECT a.cust_id
> FROM account a INNER JOIN branch b
> ON a.open_branch_id = b.branch_id
> WHERE b.name = 'Woburn Branch'
> UNION
> SELECT cust_id
> FROM account
> WHERE avail_balance BETWEEN 500 AND 2500;
+
+
| cust_id |
+
+
| 1 |
| 2 |
| 3 |
| 4 |
| 8 |
| 9 |
| 7 |
| 11 |
| 5 |
+
+
9 rows in set (0.00 sec)
Этот составной запрос включает три запроса, возвращающих набор не уникальных ID клиентов. Первые два запроса разделены оператором union all, а второй и третий – оператором union. Может показаться, что расположение операторов union и union all не играет роли, но на самом

120
Глава 6. Работа с множествами деле разница есть. Вот тот же составной запрос, в котором эти операто ры поменялись местами:
mysql> SELECT cust_id
> FROM account
> WHERE product_cd IN ('SAV', 'MM')
> UNION
> SELECT a.cust_id
> FROM account a INNER JOIN branch b
> ON a.open_branch_id = b.branch_id
> WHERE b.name = 'Woburn Branch'
> UNION ALL
> SELECT cust_id
> FROM account
> WHERE avail_balance BETWEEN 500 AND 2500;
+
+
| cust_id |
+
+
| 1 |
| 2 |
| 3 |
| 4 |
| 8 |
| 9 |
| 7 |
| 11 |
| 1 |
| 1 |
| 2 |
| 3 |
| 3 |
| 4 |
| 4 |
| 5 |
| 9 |
+
+
17 rows in set (0.00 sec)
При виде результатов становится очевидным, что порядок разных опе раторов работы с множествами в составном запросе действительно
имеет значение. В общем, составные запросы из трех или больше за просов оцениваются в порядке сверху вниз, но с учетом следующих по яснений:
•
По спецификации SQL ANSI из всех операторов работы с множест вами первым выполняется оператор intersect.
•
Порядок сочетания запросов можно задавать с помощью скобок.
Но поскольку в MySQL еще не реализованы ни оператор intersect, ни скобки в составных запросах, для получения нужного результата при дется аккуратно расставлять запросы, образующие составной запрос.
При использовании другого сервера БД, для переопределения порядка

Упражнения
121
обработки составных запросов по умолчанию (сверху вниз) запросы,
расположенные рядом, можно заключить в скобки:
(SELECT cust_id
FROM account
WHERE product_cd IN ('SAV', 'MM')
UNION ALL
SELECT a.cust_id
FROM account a INNER JOIN branch b
ON a.open_branch_id = b.branch_id
WHERE b.name = 'Woburn Branch')
INTERSECT
(SELECT cust_id
FROM account
WHERE avail_balance BETWEEN 500 AND 2500
EXCEPT
SELECT cust_id
FROM account
WHERE product_cd = 'CD'
AND avail_balance < 1000);
Для этого составного запроса первый и второй запросы комбинируют ся оператором union all, затем третий и четвертый запросы – операто ром except, и, наконец, для формирования окончательного результи рующего набора результаты этих двух операций комбинируются с по мощью оператора intersect.
Упражнения
Следующие упражнения призваны протестировать понимание опера ций с множествами. Ответы на эти упражнения приведены в приложе нии С.
6.1
Имеются множество A = {L M N O P} и множество B = {P Q R S T}. Какие мно жества будут получены в результате следующих операций:
•
A
union B
•
A
union all B
•
A
intersect B
•
A
except B
6.2
Напишите составной запрос для выбора имен и фамилий всех клиен тов физических лиц, а также имен и фамилий всех сотрудников.
6.3
Отсортируйте результаты упражнения 6.2 по столбцу lname.

Создание, преобразование
и работа с данными
Как говорилось в предисловии, цель данной книги – показать универ сальные методы SQL, применяемые на разных серверах БД. Однако эта глава посвящена созданию, преобразованию и работе со строковы ми, числовыми и временными данными, а язык SQL не включает ко манды, обеспечивающие эти функциональные возможности. Вернее,
все эти операции осуществляются встроенными функциями. К тому же, хотя стандарт SQL и описывает некоторые функции, производите ли БД часто отступают от их спецификаций.
Поэтому в данной главе сначала показаны некоторые общие приемы ра боты с данными в SQL выражениях, а потом продемонстрированы от дельные встроенные функции, реализованные в Microsoft SQL Server,
Oracle Database и MySQL. Кроме материала, представленного в этой главе, настоятельно рекомендуется приобрести справочное руководст во с описанием всех функций, реализованных сервером, с которым вы работаете. Если вы работаете с несколькими серверами, есть ряд спра вочников, охватывающих несколько серверов, например «SQL in a Nut shell» или «SQL Pocket Guide», оба от издательства O’Reilly.
Строковые данные
При работе со строковыми данными используется один из следующих символьных типов данных:
CHAR
Предназначен для хранения строк фиксированной длины, допол ненных пробелами. MySQL допускает значения типа CHAR длиной до 255 символов, Oracle Database – до 2000 символов, а SQL Server –
до 8000 символов.

Строковые данные
123
varchar
Предназначен для хранения строк переменной длины. MySQL до пускает в столбце типа varchar до 255 символов (65 535 символов для версии 5.0 и выше), Oracle Database (с помощью типа varchar2) –
до 4000 символов, а SQL Server – до 8000 символов.
text
(MySQL и SQL Server) или CLOB (Character Large Object; Oracle Da
tabase
)
Позволяют хранить очень большие строки переменной длины (обыч но в этом контексте их называют документами). В MySQL есть не сколько текстовых типов (tinytext, text, mediumtext и longtext) для документов размером до 4 Гбайт. В SQL Server всего один тип text для документов размером до 2 Гбайт. Oracle Database включает тип данных CLOB, позволяющий хранить колоссальные документы до 128 Тбайт.
В некоторых примерах данного раздела, иллюстрирующих примене ние этих различных типов, я использую такую таблицу:
CREATE TABLE string_tbl
(char_fld CHAR(30),
vchar_fld VARCHAR(30),
text_fld TEXT
);
В следующих двух разделах показано, как создавать строковые дан ные и работать с ними.
Создание строк
Самый простой способ заполнить символьный столбец – заключить строку в кавычки:
mysql> INSERT INTO string_tbl (char_fld, vchar_fld, text_fld)
> VALUES ('This is char data',
> 'This is varchar data',
> 'This is text data');
Query OK, 1 row affected (0.00 sec)
При вставке строковых данных в таблицу не забывайте, что если длина строки превышает максимальный размер символьного столбца (или за данный, или допустимый максимум типа данных), сервер или сформи рует исключение (Oracle Database), или, в случае MySQL или SQL Ser ver, без лишнего шума усечет строку (MySQL генерирует предупреж дение). Чтобы показать, как MySQL поведет себя в такой ситуации, сле дующее выражение update пытается обновить строкой из 46 символов столбец vchar_fld, для которого задан максимальный размер в 30 сим волов:
mysql>