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

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

Выражение поиска Интерпретация
F%
Строки, начинающиеся с «F»
%t
Строки, заканчивающиеся на «t»
%bas%
Строки, содержащие подстроку «bas»
_ _t_
Строки, состоящие из четырех символов с «t» в третьей позиции
_ _ _ _ _ _ _ _ _
Строки из 11 символов, где четвертый и седьмой симво лы – дефисы

Типы условий
85
Символы маски хороши для простых выражений поиска. Если требу ется несколько более сложный поиск, можно использовать несколько выражений поиска, как показано в следующем примере:
mysql> SELECT emp_id, fname, lname
> FROM employee
> WHERE lname LIKE 'F%' OR lname LIKE 'G%';
+
+
+
+
| emp_id | fname | lname |
+
+
+
+
| 5 | John | Gooding |
| 6 | Helen | Fleming |
| 9 | Jane | Grossman |
| 17 | Beth | Fowler |
+
+
+
+
4 rows in set (0.00 sec)
Этот запрос находит всех сотрудников, фамилия которых начинается с «F» или «G».
Регулярные выражения
Если символы маски не обеспечивают достаточной гибкости, для по строения выражений поиска можно использовать регулярные выра жения. По существу, регулярные выражения – это мощнейшие выра жения поиска. Регулярные выражения хорошо знакомы разработчи кам на таких языках программирования, как Perl. Если вам не дово дилось использовать их, обратитесь к книге Джеффри Фридла (Jeffrey
Friedl) «Mastering Regular Expressions» (O’Reilly). Это слишком объ емная тема, чтобы пытаться охватить ее в данной книге.
Вот как выглядел бы предыдущий запрос (найти всех сотрудников с фа милиями, начинающимися с «F» или «G») с использованием реализа ции регулярных выражений MySQL:
mysql> SELECT emp_id, fname, lname
> FROM employee
> WHERE lname REGEXP '^[FG]';
+
+
+
+
| emp_id | fname | lname |
+
+
+
+
| 5 | John | Gooding |
| 6 | Helen | Fleming |
| 9 | Jane | Grossman |
| 17 | Beth | Fowler |
+
+
+
+
4 rows in set (0.00 sec)
Оператор regexp принимает регулярное выражение (в данном примере '^[FG]'
) и применяет его к выражению, находящемуся в левой части условия (столбец lname). Теперь, с регулярным выражением, запрос со держит всего одно условие, а не два, как это было при использовании символов маски.

86
Глава 4. Фильтрация
Oracle Database 10g и SQL Server 2000 тоже поддерживают регулярные выражения. При работе с Oracle Database 10g используется функция regexp_like
, а не оператор regexp, показанный в предыдущем примере.
А SQL Server допускает использование регулярных выражений с опе ратором like.
NULL: это слово из четырех букв…
Я, сколько мог, оттягивал этот момент, но он настал: пора обратиться к теме, которую обычно встречают с опаской, неуверенностью и трепе том, – значение null. Null – это отсутствие значения. Например, пока сотрудник не уволен, в его столбце end_date таблицы employee должно быть записано null. В данной ситуации нет значения, которое могло бы быть помещено в столбец end_date и имело бы смысл. Однако null –
коварный тип, поскольку имеет несколько оттенков:
Неприменимый
Например, столбец с ID сотрудника для транзакции, которая вы полняется с банкоматом.
Значение еще не известно
Например, если в момент создания строки клиента федеральный ID
неизвестен.
Значение не определено
Например, если создается счет для продукта, который еще не был добавлен в БД.
Некоторые теоретики считают, что для каждой из этих (и дру гих) ситуаций следовало бы использовать разные выражения,
но по мнению большинства практикующих специалистов при нескольких значениях null путаницы было бы гораздо больше.
При работе с null необходимо помнить:
•
Выражение может быть нулевым (null), но оно никогда не может быть равным нулю.
•
Два null никогда не равны друг другу.
Проверить выражение на значение null можно с помощью оператора is null, как показано в следующем примере:
mysql> SELECT emp_id, fname, lname, superior_emp_id
> FROM employee
> WHERE superior_emp_id IS NULL;
+
+
+
+
+
| emp_id | fname | lname | superior_emp_id |
+
+
+
+
+
| 1 | Michael | Smith | NULL |
+
+
+
+
+
1 row in set (0.00 sec)

NULL: это слово из четырех букв…
87
Этот запрос возвращает всех сотрудников, у которых нет начальника
(superior). Вот тот же запрос, но вместо is null используется = null:
mysql> SELECT emp_id, fname, lname, superior_emp_id
> FROM employee
> WHERE superior_emp_id = NULL;
Empty set (0.01 sec)
Как видите, запрос подвергается синтаксическому анализу и выполня ется, но не возвращает ни одной строки. Это общая ошибка неопытных
SQL программистов. Сервер БД не предупредит о ней, поэтому при соз дании условий проверки на null следует соблюдать осторожность.
Проверить наличие значения в столбце можно с помощью оператора is not null:
mysql> SELECT emp_id, fname, lname, superior_emp_id
> FROM employee
> WHERE superior_emp_id IS NOT NULL;
+
+
+
+
+
| emp_id | fname | lname | superior_emp_id |
+
+
+
+
+
| 2 | Susan | Barker | 1 |
| 3 | Robert | Tyler | 1 |
| 4 | Susan | Hawthorne | 3 |
| 5 | John | Gooding | 4 |
| 6 | Helen | Fleming | 4 |
| 7 | Chris | Tucker | 6 |
| 8 | Sarah | Parker | 6 |
| 9 | Jane | Grossman | 6 |
| 10 | Paula | Roberts | 4 |
| 11 | Thomas | Ziegler | 10 |
| 12 | Samantha | Jameson | 10 |
| 13 | John | Blake | 4 |
| 14 | Cindy | Mason | 13 |
| 15 | Frank | Portman | 13 |
| 16 | Theresa | Markham | 4 |
| 17 | Beth | Fowler | 16 |
| 18 | Rick | Tulman | 16 |
+
+
+
+
+
17 rows in set (0.01 sec)
Эта версия запроса возвращает остальных 17 сотрудников, у которых,
в отличие от Майкла Смита (Michael Smith), есть начальник.
Прежде чем на время отложить рассмотрение null, было бы полезным отметить еще одну потенциальную ловушку. Предположим, требуется идентифицировать всех сотрудников, не подчиняющихся Хелен Фле минг (Helen Fleming), ID которой равен 6. Наверняка первым порывом будет сделать следующее:
mysql> SELECT emp_id, fname, lname, superior_emp_id
> FROM employee
> WHERE superior_emp_id != 6;

88
Глава 4. Фильтрация
+
+
+
+
+
| emp_id | fname | lname | superior_emp_id |
+
+
+
+
+
| 2 | Susan | Barker | 1 |
| 3 | Robert | Tyler | 1 |
| 4 | Susan | Hawthorne | 3 |
| 5 | John | Gooding | 4 |
| 6 | Helen | Fleming | 4 |
| 10 | Paula | Roberts | 4 |
| 11 | Thomas | Ziegler | 10 |
| 12 | Samantha | Jameson | 10 |
| 13 | John | Blake | 4 |
| 14 | Cindy | Mason | 13 |
| 15 | Frank | Portman | 13 |
| 16 | Theresa | Markham | 4 |
| 17 | Beth | Fowler | 16 |
| 18 | Rick | Tulman | 16 |
+
+
+
+
+
14 rows in set (0.01 sec)
Действительно, эти 14 сотрудников не работают под руководством Хе лен Флеминг, но если внимательно посмотреть на данные, можно заме тить, что здесь пропущен один сотрудник, также не являющийся под чиненным Хелен. Это Майкл Смит, и в его столбце superior_emp_id стоит null
(потому что он «большая шишка»). Поэтому, чтобы правильно от ветить на вопрос, необходимо учитывать вероятность того, что для не которых строк столбец superior_emp_id может иметь значение null.
mysql> SELECT emp_id, fname, lname, superior_emp_id
> FROM employee
> WHERE superior_emp_id != 6 OR superior_emp_id IS NULL;
+
+
+
+
+
| emp_id | fname | lname | superior_emp_id |
+
+
+
+
+
| 1 | Michael | Smith | NULL |
| 2 | Susan | Barker | 1 |
| 3 | Robert | Tyler | 1 |
| 4 | Susan | Hawthorne | 3 |
| 5 | John | Gooding | 4 |
| 6 | Helen | Fleming | 4 |
| 10 | Paula | Roberts | 4 |
| 11 | Thomas | Ziegler | 10 |
| 12 | Samantha | Jameson | 10 |
| 13 | John | Blake | 4 |
| 14 | Cindy | Mason | 13 |
| 15 | Frank | Portman | 13 |
| 16 | Theresa | Markham | 4 |
| 17 | Beth | Fowler | 16 |
| 18 | Rick | Tulman | 16 |
+
+
+
+
+
15 rows in set (0.01 sec)

Упражнения
89
Теперь результирующий набор включает всех 15 сотрудников, не под чиняющихся Хелен. При работе с малознакомой базой данных не по мешает выяснить, какие столбцы таблицы могут содержать null; это поможет вам создавать правильные условия фильтрации, чтобы дан ные не смогли утекать сквозь пальцы.
Упражнения
Следующие упражнения проверят ваше понимание условий фильтра ции. Решения ищите в приложении С.
В первых двух упражнениях используются следующие данные о тран закциях:
4.1
Какие ID транзакций возвращают следующие условия фильтрации?
txn_date < '2005 02 26' AND (txn_type_cd = 'DBT' OR amount > 100)
4.2
Какие ID транзакций возвращают следующие условия фильтрации?
account_id IN (101,103) AND NOT (txn_type_cd = 'DBT' OR amount > 100)
4.3
Создайте запрос, выбирающий все счета, открытые в 2002 году.
4.4
Создайте запрос, выбирающий всех клиентов физических лиц, второй буквой фамилии которых является буква 'a' и есть 'e' в любой пози ции после 'a'.
Txn_id
Txn_date
Account_id
Txn_type_cd
Amount
1 2005 02 22 101
CDT
1000.00 2
2005 02 23 102
CDT
525.75 3
2005 02 24 101
DBT
100.00 4
2005 02 24 103
CDT
55 5
2005 02 25 101
DBT
50 6
2005 02 25 103
DBT
25 7
2005 02 25 102
CDT
125.37 8
2005 02 26 103
DBT
10 9
2005 02 27 101
CDT
75

Запрос к нескольким таблицам
Поскольку реляционные БД предполагают расположение независи мых сущностей в разных таблицах, необходим механизм сведения не скольких таблиц воедино в одном запросе. Этот механизм известен как
соединение
(join), и данная глава посвящена самому простому и наибо лее распространенному соединению – внутреннему соединению (inner
join
). Все разнообразные типы соединений представлены в главе 10.
Что такое соединение?
Запросы к одной таблице, конечно, не редкость, но большинство за просов обращены к двум, трем или даже более таблицам. Для иллюст рации давайте рассмотрим описания таблиц employee и department и затем определим запрос, извлекающий данные из обеих:
mysql> DESC employee;
+
+
+
+
+
+
| Field | Type | Null | Key | Default |
+
+
+
+
+
+
| emp_id | smallint(5) unsigned | | PRI | NULL |
| fname | varchar(20) | | | |
| lname | varchar(20) | | | |
| start_date | date | | | 0000 00 00 |
| end_date | date | YES | | NULL |
| superior_emp_id | smallint(5) unsigned | YES | MUL | NULL |
| dept_id | smallint(5) unsigned | YES | MUL | NULL |
| title | varchar(20) | YES | | NULL |
| assigned_branch_id | smallint(5) unsigned | YES | MUL | NULL |
+
+
+
+
+
+
9 rows in set (0.11 sec)
mysql> DESC department;
+
+
+
+
+
+

Что такое соединение?
91
| Field | Type | Null | Key | Default |
+
+
+
+
+
+
| dept_id | smallint(5) unsigned | | PRI | NULL |
| name | varchar(20) | | | |
+
+
+
+
+
+
2 rows in set (0.03 sec)
Скажем, требуется выбрать имя и фамилию каждого сотрудника, а так же название отдела, в котором он работает. Поэтому запрос должен бу дет извлекать столбцы employee.fname, employee.lname и department.name.
Но как можно получить данные двух таблиц одним запросом? Ответ кроется в столбце employee.dept_id, в котором хранится ID отдела каж дого сотрудника (более формально, столбец employee.dept_id является
внешним ключом
, ссылающимся на таблицу department). Запрос, кото рый вскоре будет представлен, указывает серверу использовать стол бец employee.dept_id как мост между таблицами employee и department,
обеспечивая таким образом возможность включения столбцов обеих таблиц в результирующий набор запроса. Такой тип операции называ ется соединением.
Декартово произведение
Начнем с самого простого: поместим таблицы employee и department в блок from запроса и посмотрим, что произойдет. Вот запрос, выбираю щий имена и фамилии сотрудников, а также название отдела. Здесь в блоке from указаны обе таблицы, разделенные ключевым словом join:
mysql> SELECT e.fname, e.lname, d.name
> FROM employee e JOIN department d;
+
+
+
+
| fname | lname | name |
+
+
+
+
| Michael | Smith | Operations |
| Susan | Barker | Operations |
| Robert | Tyler | Operations |
| Susan | Hawthorne | Operations |
| John | Gooding | Operations |
| Helen | Fleming | Operations |
| Chris | Tucker | Operations |
| Sarah | Parker | Operations |
| Jane | Grossman | Operations |
| Paula | Roberts | Operations |
| Thomas | Ziegler | Operations |
| Samantha | Jameson | Operations |
| John | Blake | Operations |
| Cindy | Mason | Operations |
| Frank | Portman | Operations |
| Theresa | Markham | Operations |
| Beth | Fowler | Operations |
| Rick | Tulman | Operations |

92
Глава 5. Запрос к нескольким таблицам
| Michael | Smith | Loans |
| Susan | Barker | Loans |
| Robert | Tyler | Loans |
| Susan | Hawthorne | Loans |
| John | Gooding | Loans |
| Helen | Fleming | Loans |
| Chris | Tucker | Loans |
| Sarah | Parker | Loans |
| Jane | Grossman | Loans |
| Paula | Roberts | Loans |
| Thomas | Ziegler | Loans |
| Samantha | Jameson | Loans |
| John | Blake | Loans |
| Cindy | Mason | Loans |
| Frank | Portman | Loans |
| Theresa | Markham | Loans |
| Beth | Fowler | Loans |
| Rick | Tulman | Loans |
| Michael | Smith | Administration |
| Susan | Barker | Administration |
| Robert | Tyler | Administration |
| Susan | Hawthorne | Administration |
| John | Gooding | Administration |
| Helen | Fleming | Administration |
| Chris | Tucker | Administration |
| Sarah | Parker | Administration |
| Jane | Grossman | Administration |
| Paula | Roberts | Administration |
| Thomas | Ziegler | Administration |
| Samantha | Jameson | Administration |
| John | Blake | Administration |
| Cindy | Mason | Administration |
| Frank | Portman | Administration |
| Theresa | Markham | Administration |
| Beth | Fowler | Administration |
| Rick | Tulman | Administration |
+
+
+
+
54 rows in set (0.00 sec)
Хм… у нас только 18 сотрудников и 3 разных отдела. Но как же полу чилось, что в результирующем наборе оказалось 54 строки? Пригля девшись, можно заметить, что каждый из 18 сотрудников встречается трижды. При этом все его данные идентичны, кроме названия отдела.
Поскольку запрос не определил, как должны быть соединены эти две таблицы, сервер БД сгенерировал Декартово произведение, т. е. все воз
можные
перестановки двух таблиц (18 сотрудников умножить на 3 от дела получается 54 перестановки). Такой тип соединения называют пе
рекрестным соединением
(cross join). Его редко используют (намерен но, по крайней мере). Перекрестные соединения – один из типов соеди нений, которые будут изучаться в главе 10.

Что такое соединение?
93
Внутренние соединения
Чтобы изменить предыдущий запрос и получить результирующий на бор, включающий только 18 строк (по одной для каждого сотрудника),
понадобится описать взаимосвязь двух таблиц. Я уже показал, что связью между двумя таблицами служит столбец employee.dept_id, оста лось только добавить эту информацию в подблок on блока from:
mysql> SELECT e.fname, e.lname, d.name
> FROM employee e JOIN department d
> ON e.dept_id = d.dept_id;
+
+
+
+
| fname | lname | name |
+
+
+
+
| Susan | Hawthorne | Operations |
| Helen | Fleming | Operations |
| Chris | Tucker | Operations |
| Sarah | Parker | Operations |
| Jane | Grossman | Operations |
| Paula | Roberts | Operations |
| Thomas | Ziegler | Operations |
| Samantha | Jameson | Operations |
| John | Blake | Operations |
| Cindy | Mason | Operations |
| Frank | Portman | Operations |
| Theresa | Markham | Operations |
| Beth | Fowler | Operations |
| Rick | Tulman | Operations |
| John | Gooding | Loans |
| Michael | Smith | Administration |
| Susan | Barker | Administration |
| Robert | Tyler | Administration |
+
+
+
+
18 rows in set (0.00 sec)
Теперь благодаря добавлению подблока on, предписывающего серверу соединять таблицы employee и department, прослеживая связь от одной таблицы к другой по столбцу dept_id, имеем вместо 54 строк ожидае мые 18. Например, строка Сьюзен Хоторн (Susan Hawthorne) в табли це employee в столбце dept_id содержит 1 (в примере не показано). Сер вер использует это значение для поиска строки в таблице department,
столбец dept_id которой содержит 1, и извлекает значение 'Operations'
из столбца name этой строки.
Если определенное значение столбца dept_id присутствует в одной таб лице, но его нет в другой, соединение строк не происходит, и они не включаются в результирующий набор. Такой тип соединения называ ют внутренним соединением (inner join); это наиболее широко исполь зуемый тип соединения. Поясню: если в таблице department есть четвер тая строка для отдела маркетинга, но ни один сотрудник не приписан к нему, отдел маркетинга не попадет в результирующий набор. Анало

94
Глава 5. Запрос к нескольким таблицам гично, если некоторые сотрудники зарегистрированы в отделе с ID 99,
которого нет в таблице department, эти сотрудники не попадут в резуль тирующий набор. Если требуется включить все строки той или иной таблицы независимо от наличия соответствия, можно воспользоваться
внешним соединением
(outer join), но мы рассмотрим это в главе 10.
В предыдущем примере в блоке from я не указал тип используемого со единения. Однако если требуется соединить две таблицы путем внут реннего соединения, это следует явно указать в блоке from. Вот тот же пример с добавлением типа соединения (обратите внимание на ключе вое слово INNER (внутренний)):
mysql>