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

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

Рис. 13.2. Пример битового индекса

Индексы
247
дой из 24 строк таблицы account. Таким образом, если серверу делается запрос на извлечение всех депозитных счетов денежного рынка (prod uct_cd
= 'MM'), он просто находит все значения 1 в битовой карте MM
и возвращает строки 7, 10 и 18. Если требуется найти несколько значе ний, сервер также может комбинировать битовые карты. Например,
если пользователь хочет получить все депозитные счета денежного рынка и сберегательные счета (product_cd = 'MM' или product_cd = 'SAV'),
сервер может осуществить для битовых карт MM и SAV операцию OR
(ИЛИ) и возвратит строки 2, 5, 7, 9, 10, 16 и 18.
Битовые индексы – милое компактное решение по индексации данных с малым кардинальным числом. Однако эта стратегия не годится, если число хранящихся в столбце значений слишком велико по сравнению с числом строк (в таком случае говорят о данных с большим кардиналь
ным числом
(high cardinality)), потому что серверу пришлось бы об служивать слишком много битовых карт. Например, не следует созда вать битовый индекс для столбца первичного ключа, поскольку он яв ляется примером максимально возможного количества элементов (но вое значение для каждой строки).
Пользователи Oracle могут формировать битовые индексы, просто до бавляя ключевое слово bitmap в выражение create index:
CREATE BITMAP INDEX acc_prod_idx ON account (product_cd);
Битовые индексы широко используются в информационных хранили щах, где обычно индексируются большие объемы данных для столбцов,
содержащих относительно небольшое количество значений (например,
квартальные отчеты, географические регионы, продукты, продавцы).
Текстовые индексы
Если БД используется для хранения документов, может потребовать ся обеспечить пользователям возможность выполнять поиск слов или фраз в документах. Конечно, не хочется, чтобы сервер открывал каж дый документ и просматривал его в поисках нужного текста при каж дом запросе. Традиционные стратегии индексации в данном случае не годятся. Чтобы справиться с этой ситуацией, MySQL и Oracle Database включают для документов специальные механизмы индексации и по иска. В SQL Server и MySQL есть так называемые индексы по всему
тексту
(full text) (для MySQL индексы по всему тексту доступны толь ко с механизмом хранения MyISAM). Oracle Database включает мощ ный инструментарий Oracle Text. Поиск по документам достаточно специализирован, поэтому не будем приводить здесь пример, но я хо тел хотя бы сообщить о такой возможности.
Использование индексов
Обычно сервер использует индексы для быстрого обнаружения место положения интересующих строк в конкретной таблице. После этого

248
Глава 13. Индексы и ограничения сервер просматривает ассоциированную таблицу и извлекает дополни тельную информацию, запрашиваемую пользователем. Рассмотрим следующий запрос:
mysql> SELECT emp_id, fname, lname
> FROM employee
> WHERE emp_id IN (1, 3, 9, 15, 22);
+
+
+
+
| emp_id | fname | lname |
+
+
+
+
| 1 | Michael | Smith |
| 3 | Robert | Tyler |
| 9 | Jane | Grossman |
| 15 | Frank | Portman |
+
+
+
+
4 rows in set (0.00 sec)
Для этого запроса сервер может использовать в качестве индекса пер вичный ключ для столбца emp_id, чтобы найти местоположение сотруд ников с ID 1, 3, 9, 15 и 22 в таблице employee, и затем просмотреть каж дую из пяти строк, чтобы извлечь значения столбцов имени и фамилии.
Однако если индекс содержит все, что необходимо для удовлетворения запроса, серверу не надо просматривать ассоциированную таблицу.
Для иллюстрации давайте посмотрим на то, как обращается оптимиза тор запросов с одним и тем же запросом при разных типах индексов.
Запрос, агрегирующий остатки на счетах для определенных клиентов,
выглядит так:
mysql> SELECT cust_id, SUM(avail_balance) tot_bal
> FROM account
> WHERE cust_id IN (1, 5, 9, 11)
> GROUP BY cust_id;
+
+
+
| cust_id | tot_bal |
+
+
+
| 1 | 4557.75 |
| 5 | 2237.97 |
| 9 | 10971.22 |
| 11 | 9345.55 |
+
+
+
4 rows in set (0.00 sec)
С помощью выражения explain (объяснить) рассмотрим, как оптими затор запросов MySQL принимает решение об обработке запроса. Сер вер не будет выполнять запрос, а покажет план его выполнения:
mysql> EXPLAIN SELECT cust_id, SUM(avail_balance) tot_bal
> FROM account
> WHERE cust_id IN (1, 5, 9, 11)
> GROUP BY cust_id \G
*************************** 1. row ***************************

Индексы
249
id: 1
select_type: SIMPLE
table: account type: index possible_keys: fk_a_cust_id key: fk_a_cust_id key_len: 4
ref: NULL
rows: 24
Extra: Using where
1 row in set (0.00 sec)
Каждый сервер БД включает инструменты, позволяющие уви деть, как оптимизатор запросов обрабатывает SQL выражение.
В SQL Server увидеть план выполнения перед выполнением
SQL выражения позволяет выражение set showplan_text on.
В Oracle Database есть выражение explain plan, записывающее план выполнения в специальную таблицу plan_table.
Если не вдаваться в детали, то план выполнения сообщает следующее:
•
Индекс fk_a_cust_id используется для поиска строк таблицы account,
которые удовлетворяют условию блока where.
•
После прочтения индекса ожидается, что сервер просмотрит все
24 строки таблицы account для сбора данных о доступных остатках,
поскольку он не знает, что могут существовать другие клиенты,
кроме клиентов с ID 1, 5, 9 и 11.
Индекс fk_a_cust_id – еще один индекс, автоматически сгенерирован ный сервером, но на этот раз из за ограничения внешнего ключа, а не ограничения первичного ключа (более подробно об этом позже в этой главе). Индекс fk_a_cust_id создан для столбца account.cust_id, таким образом, сервер использует его для определения местоположения кли ентов с ID 1, 5, 9 и 11 в таблице account, а затем посещает эти строки для извлечения и агрегирования данных по доступным остаткам.
Далее добавим новый индекс с названием acc_bal_idx для обоих столб цов cust_id и avail_balance:
mysql> ALTER TABLE account
> ADD INDEX acc_bal_idx (cust_id, avail_balance);
Query OK, 24 rows affected (0.03 sec)
Records: 24 Duplicates: 0 Warnings: 0
Теперь, имея этот индекс, посмотрим, как оптимизатор будет обраба тывать тот же запрос:
mysql> EXPLAIN SELECT cust_id, SUM(avail_balance) tot_bal
> FROM account
> WHERE cust_id IN (1, 5, 9, 11)
> GROUP BY cust_id \G
*************************** 1. row ***************************

250
Глава 13. Индексы и ограничения id: 1
select_type: SIMPLE
table: account type: range possible_keys: acc_bal_idx key: acc_bal_idx key_len: 4
ref: NULL
rows: 8
Extra: Using where; Using index
1 row in set (0.01 sec)
Сравнение двух планов выполнения дает следующие различия:
•
Вместо индекса fk_a_cust_id оптимизатор использует новый индекс acc_bal_idx
•
Оптимизатор ускоряет свою работу, поскольку теперь ему надо об работать только восемь строк вместо 24.
•
Для обеспечения результатов запроса таблица account не нужна
(обозначена через Using index в столбце Extra).
Следовательно, сервер может определить с помощью индексов место положение строк в ассоциированной таблице или использовать индекс как таблицу, если он содержит все столбцы, необходимые для удовле творения запроса.
Только что рассмотренный процесс – это пример оптимизации запроса. Оптимизация включает изучение SQL выражения и ус тановление доступных серверу ресурсов, необходимых для вы полнения этого выражения. Чтобы обеспечить более эффектив ное выполнение выражения, можно изменить SQL выражение или настроить ресурсы БД, или сделать и то, и другое. Оптими зация – тонкий вопрос, поэтому настоятельно рекомендую про читать руководство по оптимизации для используемого сервера или найти хорошую книгу по этой теме, чтобы увидеть все воз можные подходы к оптимизации для конкретного сервера.
Обратная сторона индексации
Если индексы – это так замечательно, почему бы не индексировать все подряд? Чтобы понять, почему большее количество индексов не всегда хорошо, надо помнить, что каждый индекс – это таблица (особого ти па, но все равно таблица). Поэтому при каждом добавлении или удале нии строки из таблицы все ее индексы должны быть модифицирова ны. При обновлении строки все задействованные индексы столбца или столбцов тоже должны изменяться. Следовательно, чем больше индек сов, тем больше работы приходится выполнять серверу для обновле ния всех объектов схемы. А это очень замедляет процесс.
Индексам требуется некоторое количество памяти, а также некоторое внимание администраторов, поэтому наилучшая стратегия – добавлять

Ограничения
251
индекс только при возникновении вполне определенной необходимо сти. Если индекс нужен только для конкретной цели, например для выполнения программы по ежемесячному обслуживанию, всегда мож но добавить индекс, выполнить программу и удалить индекс до того момента, пока он не понадобится вновь. Для хранилищ данных, где индексы очень нужны в рабочие часы (поскольку пользователи состав ляют отчеты и выполняют случайные запросы), но становятся пробле мой при загрузке данных в хранилище в ночное время, общепринятой практикой является уничтожение индексов перед загрузкой данных и их повторное создание перед открытием хранилищ для работы.
Ограничения
Ограничение – это просто некоторое ограничивающее условие, нала гаемое на один или более столбцов таблицы. Есть несколько разных типов ограничений, включая:
Ограничения первичного ключа (Primary key constraints)
Идентифицируют столбец или столбцы, гарантирующие уникаль ность в рамках таблицы.
Ограничения внешнего ключа (Foreign key constraints)
На один или более столбцов накладывается такое ограничение: они могут содержать только значения, содержащиеся в столбцах первич ного ключа другой таблицы. Также могут ограничиваться допусти мые значения других таблиц, если установлены правила update cas cade
(каскадное обновление) или delete cascade (каскадное удаление).
Ограничения уникальности (Unique constraints)
На один или более столбцов накладывается ограничение: они могут содержать только уникальные в рамках таблицы значения.
Проверочные ограничения целостности (Check constraints)
Ограничивают допустимые значения столбца.
Без ограничений под сомнением может оказаться непротиворечивость базы данных. Например, если сервер допускает изменение ID клиента в таблице customer без изменения этого же ID клиента в таблице ac count
, все может закончиться тем, что счета больше не будут указывать на соответствующие записи клиентов (это явление известно как «оси ротевшие» строки (orphaned rows)). Но если заданы ограничения пер вичного и внешнего ключей, сервер или сформирует ошибку в случае попытки изменения или удаления данных, на которые ссылаются дру гие таблицы, или распространит изменения на другие таблицы (более подробно об этом чуть позже).
Если при работе с сервером MySQL требуются ограничения внеш него ключа, в таблицах должен использоваться механизм хра нения InnoDB.

252
Глава 13. Индексы и ограничения
Создание ограничений
Обычно ограничения создают одновременно с ассоциированной табли цей посредством выражения create table. Для иллюстрации приведем пример из сценария формирования схемы для БД, используемой в ка честве примера к этой книге:
CREATE TABLE product
(product_cd VARCHAR(10) NOT NULL,
name VARCHAR(50) NOT NULL,
product_type_cd VARCHAR (10) NOT NULL,
date_offered DATE,
date_retired DATE,
CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)
REFERENCES product_type (product_type_cd),
CONSTRAINT pk_product PRIMARY KEY (product_cd)
);
Таблица product включает два ограничения: первое определяет столбец product_cd как первичный ключ таблицы, а второе – столбец product_
type_cd как внешний ключ к таблице product_type. Альтернативный ва риант: таблицу product можно было создать без ограничений, а ограни чения первичного и внешнего ключей добавить позже посредством вы ражений alter table:
ALTER TABLE product
ADD CONSTRAINT pk_product PRIMARY KEY (product_cd);
ALTER TABLE product
ADD CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)
REFERENCES product_type (product_type_cd);
Если требуется убрать ограничения первичного или внешнего ключей,
можно опять воспользоваться выражением alter table, только в этом случае задается drop, а не add:
ALTER TABLE product
DROP PRIMARY KEY;
ALTER TABLE product
DROP FOREIGN KEY fk_product_type_cd;
Ограничение первичного ключа обычно не удаляется, а ограничения внешнего ключа иногда отменяются во время определенных операций обслуживания, а потом устанавливаются вновь.
Ограничения и индексы
Как было показано в этой главе ранее, иногда создание ограничений включает автоматическое формирование индекса. Однако серверы БД
ведут себя по разному в зависимости от связи между ограничениями и индексами. В табл. 13.1 показаны связи между ограничениями и ин дексами в MySQL, SQL Server и Oracle Database.

Ограничения
253
Таблица 13.1. Формирование ограничений
Следовательно, MySQL формирует новый индекс, чтобы реализовать ограничения первичного ключа, внешнего ключа и уникальности. SQL
Server формирует новый индекс для ограничений первичного ключа и уникальности, а для ограничений внешнего ключа – нет. Oracle Da tabase использует такой же подход, как и SQL Server, за тем исключе нием, что Oracle для введения в действие ограничений первичного клю ча и уникальности будет использовать существующий индекс (если есть подходящий). Хотя ни SQL Server, ни Oracle Database не формиру ют индекс для ограничения внешнего ключа, документация обоих сер веров рекомендует создавать индекс для каждого внешнего ключа.
Каскадные ограничения
Если при наличии ограничений внешнего ключа пользователь пытает ся вставить новую строку или изменить существующую и при этом по лучается, что столбец внешнего ключа не имеет соответствующего зна чения в родительской таблице, сервер формирует ошибку. Для иллю страции рассмотрим данные таблиц product и product_type:
mysql> SELECT product_type_cd, name
> FROM product_type;
+
+
+
| product_type_cd | name |
+
+
+
| ACCOUNT | Customer Accounts |
| INSURANCE | Insurance Offerings |
| LOAN | Individual and Business Loans |
+
+
+
3 rows in set (0.00 sec)
mysql> SELECT product_type_cd, product_cd, name
> FROM product
> ORDER BY product_type_cd;
+
+
+
+
| product_type_cd | product_cd | name |
+
+
+
+
| ACCOUNT | CD | certificate of deposit |
| ACCOUNT | CHK | checking account |
Тип ограничения
MySQL
SQL Server
Oracle Database
Ограничение первичного ключа
Формирует уни кальный индекс
Формирует уни кальный индекс
Использует имею щийся индекс или создает новый
Ограничение внешнего ключа
Формирует индекс Не формирует индекс
Не формирует индекс
Ограничение уникальности
Формирует уни кальный индекс
Формирует уни кальный индекс
Использует имею щийся индекс или создает новый

254
Глава 13. Индексы и ограничения
| ACCOUNT | MM | money market account |
| ACCOUNT | SAV | savings account |
| LOAN | AUT | auto loan |
| LOAN | BUS | business line of credit |
| LOAN | MRT | home mortgage |
| LOAN | SBL | small business loan |
+
+
+
+
8 rows in set (0.01 sec)
В таблице product_type имеется три разных значения для столбца prod uct_type_cd
(ACCOUNT, INSURANCE и LOAN). Два из этих трех значений (AC
COUNT
и LOAN) упоминаются в столбце product_type_cd таблицы product.
Следующее выражение делает попытку изменить значение столбца product_type_cd таблицы product на значение, которого нет в таблице product_type
:
mysql> UPDATE product
> SET product_type_cd = 'XYZ'
> WHERE product_type_cd = 'LOAN';
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
Из за ограничения внешнего ключа на столбец product.product_type_cd сервер не разрешает провести такое обновление, поскольку в таблице product_type в столбце product_type_cd нет строки со значением 'XYZ'. Та ким образом, ограничение внешнего ключа не позволяет изменить до чернюю строку, если в родительской нет соответствующего значения.
Однако что произошло бы, попробуй мы изменить значение родитель
ской
строки таблицы product_type на 'XYZ'? Вот выражение update, реа лизующее попытку изменить тип счета LOAN на XYZ:
mysql> UPDATE product_type
> SET product_type_cd = 'XYZ'
> WHERE product_type_cd = 'LOAN';
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Опять формируется ошибка. На этот раз потому, что в таблице product есть дочерние строки, столбец product_type_cd которых содержит зна чение 'LOAN'. Это поведение ограничений внешнего ключа по умолча нию, но оно не единственное возможное. Можно указать серверу рас пространять это изменение на все дочерние строки, сохраняя, таким образом, целостность данных. Эта разновидность ограничения внеш него ключа, известная как каскадное обновление (cascading update),
может быть установлена путем удаления существующего внешнего ключа и добавления нового, включающего блок on update cascade:
mysql> ALTER TABLE product
> DROP FOREIGN KEY fk_product_type_cd;
Query OK, 8 rows affected (0.02 sec)

Ограничения
255
Records: 8 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE product
> ADD CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)
> REFERENCES product_type (product_type_cd)
> ON UPDATE CASCADE;
Query OK, 8 rows affected (0.03 sec)
Records: 8 Duplicates: 0 Warnings: 0
Изменив ограничение таким образом, посмотрим, что произойдет, ес ли попытаться выполнить выражение update снова:
mysql> UPDATE product_type
> SET product_type_cd = 'XYZ'
> WHERE product_type_cd = 'LOAN';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
На этот раз выражение выполняется успешно. Для проверки того, что изменения были распространены на таблицу product, еще раз посмот рим на данные в обеих таблицах:
mysql> SELECT product_type_cd, name
> FROM product_type;
+
+
+
| product_type_cd | name |
+
+
+
| ACCOUNT | Customer Accounts |
| INSURANCE | Insurance Offerings |
| XYZ | Individual and Business Loans |
+
+
+
3 rows in set (0.02 sec)
mysql> SELECT product_type_cd, product_cd, name
> FROM product
> ORDER BY product_type_cd;
+
+
+
+
| product_type_cd | product_cd | name |
+
+
+
+
| ACCOUNT | CD | certificate of deposit |
| ACCOUNT | CHK | checking account |
| ACCOUNT | MM | money market account |
| ACCOUNT | SAV | savings account |
| XYZ | AUT | auto loan |
| XYZ | BUS | business line of credit |
| XYZ | MRT | home mortgage |
| XYZ | SBL | small business loan |
+
+
+
+
8 rows in set (0.01 sec)
Как видите, изменения таблицы product_type распространились и на таб лицу product. Кроме каскадных обновлений можно задавать каскад
ные удаления
(cascading deletes). При каскадном удалении, если стро

256
Глава 13. Индексы и ограничения ка удаляется в родительской таблице, соответствующие ей строки уда ляются и в дочерней таблице. Для задания каскадного удаления ис пользуется блок on delete cascade:
ALTER TABLE product
ADD CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)
REFERENCES product_type (product_type_cd)
ON UPDATE CASCADE
ON DELETE CASCADE;
Теперь при таком варианте ограничения сервер будет обновлять дочер ние строки таблицы product при обновлении строки в таблице pro duct_type
, а также удалять дочерние строки таблицы product при удале нии строки таблицы product_type.
Каскадные ограничения – один из случаев, когда ограничения непо
средственно
влияют на код, который вы пишете. Чтобы полностью представлять эффект применения выражений update и delete, необхо димо знать, для каких ограничений базы данных заданы каскадные обновления и/или удаления.

ER диаграмма примера базы данных
На рис. А.1 представлена диаграмма сущностей и связей (entity relati onship, ER) базы данных, используемой в этой книге в качестве приме ра. Как следует из названия, диаграмма отображает сущности, или таблицы, базы данных и связи внешнего ключа между таблицами. Вот несколько подсказок, которые помогут понять условные обозначения:
•
Каждый прямоугольник представляет таблицу. Имя таблицы ука зано в верхнем левом углу прямоугольника. Столбец (или столбцы)
первичного ключа указан первым и отделен от обычных столбцов линией. Обычные столбцы перечислены под линией, столбцы внеш него ключа отмечены как «(FK)».
•
Линиями между таблицами представлены связи внешнего ключа.
Отметки на концах линий показывают допустимую кратность свя зи, которая может иметь значения нуль (0), один (1) или много ( ).
Например, взглянув на связь между таблицами account и product,
можно сказать, что счет должен относиться только к одной услуге,
но для одной услуги может быть нуль, один или много счетов.
Более подробно моделирование баз данных и соответствующие инстру менты рассмотрены в приложении D.

258
Приложение A. ER диаграмма примера базы данных branch branch_id:smallint unsigned name:varchar(20)
address:varchar(30)
city:varchar(20)
state:varchar(2)
zip:varchar(12)
department dept_id:smallint unsigned name:varchar(20)
product_type product_type_cd:varchar(10)
name:varchar(50)
officer officer_id:smallint unsigned cust_id:integer unsigned (FK)
fname:varchar(30)
lname:varchar(30)
title:varchar(20)
start_date:date end_date:date product product_cd:varchar(10)
name:varchar(50)
product_type_cd:varchar(10) (FK)
date_offered:date date_retired:date employee emp_id:smallint unsigned fname:varchar(20)
lname:varchar(20)
start_date:date end_date:date superior_emp_id:smallint unsigned (FK)
dept_id:smallint unsigned (FK)
title: varchar(20)
assigned_branch_id:smallint unsigned (FK)
account account_id:integer unsigned product_cd:varchar(10) (FK)
cust_id:integer unsigned (FK)
open_date:date close_date:date last_activity_date:date status:varchar(10)
open_branch_id:smallint unsigned (FK)
open_emp_id:smallint unsigned (FK)
avail_balance:float(10,2)
pending_balance:float(10,2)
transaction txn_id: integer unsigned txn_date:datetime account_id:integer unsigned (FK)
txn_type_cd:varchar(10)
amount:double(10,2)
teller_emp_id:smallint unsigned (FK)
execution_branch_id:smallint unsigned (FK)
funds_avail_date:datetime customer cust_id:integer unsigned fed_id:varchar(12)
cust_type_cd:char(2)
address:varchar(30)
city:varchar(20)
state:varchar(20)
postal_code:varchar(10)
business cust_id:integer unsigned (FK)
name:varchar(40)
state_id:varchar(10)
incorp_date:date individual cust_id:integer unsigned (FK)
fname:varchar(30)
lname:varchar(30)
birth_date:date
Рис. A.1. ER диаграмма

MySQL расширения языка SQL
Поскольку для всех примеров в данной книге используется сервер
MySQL, я подумал, что для читателей, планирующих продолжать ра ботать с MySQL, будет полезным включить приложение, посвященное
MySQL расширениям языка SQL. Это приложение рассматривает не которые MySQL расширения выражений select, insert, update и delete,
очень полезные в определенных ситуациях.
Расширения выражения Select
Реализация выражения select в MySQL включает два дополнительных блока, обсуждаемых в следующих разделах.
Блок limit
В некоторых ситуациях нас не интересуют все строки, возвращаемые запросом. Например, можно создать запрос, выбирающий всех опера ционистов банка и все номера счетов, открытых каждым из них. Если цель запроса – выявить трех лучших операционистов для вручения награды от банка, необязательно знать, кто будет четвертым, пятым и т. д. Для разрешения подобных ситуаций выражение select MySQL
включает блок limit, позволяющий ограничить число возвращаемых запросом строк.
Чтобы продемонстрировать использование блока limit, начнем с по строения запроса, показывающего количество счетов, открытых каж дым операционистом банка:
mysql> SELECT open_emp_id, COUNT(*) how_many
> FROM account
> GROUP BY open_emp_id;
+
+
+

260
Приложение B
| open_emp_id | how_many |
+
+
+
| 1 | 8 |
| 10 | 7 |
| 13 | 3 |
| 16 | 6 |
+
+
+
4 rows in set (0.31 sec)
Результат показал, что счета открывали четверо разных сотрудников.
Если требуется ограничить результирующий набор только тремя запи сями, можно добавить блок limit. Он определяет, что должны быть возвращены только три записи:
mysql> SELECT open_emp_id, COUNT(*) how_many
> FROM account
> GROUP BY open_emp_id
> LIMIT 3;
+
+
+
| open_emp_id | how_many |
+
+
+
| 1 | 8 |
| 10 | 7 |
| 13 | 3 |
+
+
+
3 rows in set (0.06 sec)
Теперь благодаря блоку limit (четвертая строка запроса) результирую щий набор включает только три строки. Четвертый операционист
(служащий с ID 16) удален из результирующего набора.
Сочетание блока limit с блоком order by
Предыдущий запрос возвращает три записи, но есть небольшая про блема: запрос не описывает, какие три записи из четырех нас интере суют. Если требуется выбрать конкретные три записи, например трех операционистов, открывших больше всего счетов, придется использо вать блок limit вместе с блоком order by:
mysql> SELECT open_emp_id, COUNT(*) how_many
> FROM account
> GROUP BY open_emp_id
> ORDER BY how_many DESC
> LIMIT 3;
+
+
+
| open_emp_id | how_many |
+
+
+
| 1 | 8 |
| 10 | 7 |
| 16 | 6 |
+
+
+
3 rows in set (0.03 sec)

MySQL расширения языка SQL
261
Разница между этим и предыдущим запросами в том, что теперь блок limit применяется к упорядоченному набору. В итоге в конечном ре зультирующем наборе имеем трех сотрудников, открывших наиболь шее число счетов. Если требуется не произвольная выборка записей,
обычно блок limit используется в паре с блоком order by.
Блок limit применяется после всех фильтров, группировок и рас становок, поэтому он никогда не изменит результат выражения select
, только ограничит число возвращаемых им записей.
Необязательный второй параметр блока limit
Допустим, теперь вместо поиска трех лучших операционистов постав лена задача выбрать всех, кроме двух лучших (вместо награждения лучших исполнителей банк пошлет несколько самых непроизводи тельных операционистов на тренинг по повышению самооценки). Для подобных ситуаций блок limit предоставляет необязательный второй параметр. Если используются оба параметра, первый указывает, с ка кой строки добавлять записи в конечный результирующий набор,
а второй – сколько строк включить. Обозначая записи порядковыми номерами, помните, что в MySQL первой записью является запись под номером 0. Следовательно, если стоит задача найти третьего лучшего работника, можно сделать следующее:
mysql> SELECT open_emp_id, COUNT(*) how_many
> FROM account
> GROUP BY open_emp_id
> ORDER BY how_many DESC
> LIMIT 2, 1;
+
+
+
| open_emp_id | how_many |
+
+
+
| 16 | 6 |
+
+
+
1 row in set (0.00 sec)
В этом примере нулевая и первая записи отбрасываются; включаются записи, начиная со второй. Поскольку второй параметр блока limit ра вен 1, получаем только одну запись.
Если требуется начать со второй позиции и включить все оставшиеся записи, можно сделать второй аргумент блока limit достаточно боль шим, чтобы все оставшиеся записи гарантированно вошли в результи рующий набор. Если неизвестно, сколько операционистов открывали новые счета, для выбора всех работников, кроме двух лучших, можно было бы сделать примерно следующее:
mysql> SELECT open_emp_id, COUNT(*) how_many
> FROM account
> GROUP BY open_emp_id
> ORDER BY how_many DESC

262
Приложение B
> LIMIT 2, 999999999;
+
+
+
| open_emp_id | how_many |
+
+
+
| 16 | 6 |
| 13 | 3 |
+
+
+
2 rows in set (0.00 sec)
В этом варианте запроса отбрасываются нулевая и первая записи, а в ре зультат включаются все записи вплоть до 999 999 999, начиная со вто рой (в данном случае таких записей всего две, но лучше немного пере усердствовать, чем потерять нужные записи, недооценив их количество).
Ранжирующие запросы
Запросы, включающие блок limit в сочетании с блоком order by, можно назвать ранжирующими запросами (ranking queries), потому что они позволяют ранжировать данные. Я уже продемонстрировал пример ранжирования банковских сотрудников по числу открытых счетов.
Но ранжирующие запросы используются для решения многих при кладных задач, таких как поиск:
•
Пяти лучших продавцов 2005 года
•
Третьего по числу круговых пробежек игрока в истории бейсбола
•
98 бестселлеров всех времен и народов, кроме Библии и цитатника
Мао
•
Двух самых непопулярных видов мороженого
Уже было рассмотрено, как найти трех лучших операционистов, тре тьего лучшего и всех, кроме двух лучших. Если я хочу сделать что то аналогичное для четвертого примера (например, найти худших со трудников), требуется просто изменить порядок сортировки на обрат ный, так чтобы результаты располагались, начиная с наименьшего числа открытых счетов и до наибольшего:
mysql> SELECT open_emp_id, COUNT(*) how_many
> FROM account
> GROUP BY open_emp_id
> ORDER BY how_many ASC
> LIMIT 2;
+
+
+
| open_emp_id | how_many |
+
+
+
| 13 | 3 |
| 16 | 6 |
+
+
+
2 rows in set (0.24 sec)
За счет простого изменения порядка сортировки (с ORDER BY how_many
DESC
на ORDER BY how_many ASC) теперь запрос возвращает двух наихудших операционистов. Таким образом, с помощью блока с возрастающим

MySQL расширения языка SQL
263
или убывающим порядком сортировки можно создавать ранжирую щие запросы для решения большинства типовых прикладных задач.
Блок into outfile
Если требуется записать результат запроса в файл, можно выделить его, скопировать в буфер обмена и вставить в свой любимый редактор.
Однако если результирующий набор запроса достаточно велик или ес ли запрос выполняется из сценария, необходим способ записывать ре зультаты в файл без участия пользователя. Для помощи в таких ситуа циях MySQL включает блок into outfile (в выходной файл), в котором можно задать имя файла для записи результатов. Вот пример записи результатов запроса в каталог c:\temp:
mysql> SELECT emp_id, fname, lname, start_date
> INTO OUTFILE 'C:\\TEMP\\emp_list.txt'
> FROM employee;
Query OK, 18 rows affected (0.20 sec)
Как говорилось в главе 7, обратный слэш используется для эк ранирования символов в строке. Поэтому в Windows для зада ния пути потребуется ставить по два обратных слэша подряд.
Результаты запроса не выводятся на экран, а записываются в файл
emp_list.txt
и выглядят так:
1 Michael Smith 2001 06 22 2 Susan Barker 2002 09 12 3 Robert Tyler 2000 02 09 4 Susan Hawthorne 2002 04 24 16 Theresa Markham 2001 03 15 17 Beth Fowler 2002 06 29 18 Rick Tulman 2002 12 12
Формат по умолчанию использует символ табуляции ('\t') между столбцами и символ новой строки ('\n') после каждой записи. Если требуется дополнительное форматирование данных, можно включить в блок into outfile несколько подблоков. Например, если надо предста вить данные в формате, называемом форматом с разделителем «|»
(pipe delimited format), то в подблоке fields (поля) можно в качестве символа разделителя столбцов задать символ '|':
mysql> SELECT emp_id, fname, lname, start_date
> INTO OUTFILE 'C:\\TEMP\\emp_list_delim.txt'
> FIELDS TERMINATED BY '|'
> FROM employee;
Query OK, 18 rows affected (0.02 sec)
MySQL не позволяет перезаписывать существующий файл с по мощью into outfile, поэтому если один и тот же запрос выпол

264
Приложение B
няется больше одного раза, перед каждым выполнением следует удалить имеющийся файл.
Содержимое файла emp_list_delim.txt выглядит так:
1|Michael|Smith|2001 06 22 2|Susan|Barker|2002 09 12 3|Robert|Tyler|2000 02 09 4|Susan|Hawthorne|2002 04 24 16|Theresa|Markham|2001 03 15 17|Beth|Fowler|2002 06 29 18|Rick|Tulman|2002 12 12
Кроме формата с разделителями «|» можно форматировать данные за пятыми (формат с разделителями запятыми, comma delimited for
mat
). В этом случае следует задать fields terminated by ','. Однако если записываемые в файл данные включают строки, применение запятых в качестве разделителей полей может вызвать проблемы. Запятые встречаются в строках намного чаще, чем символ «|». Рассмотрим сле дующий запрос, записывающий число и две строки, разделенные за пятыми, в файл comma1.txt:
mysql> SELECT data.num, data.str1, data.str2
> INTO OUTFILE 'C:\\TEMP\\comma1.txt'
> FIELDS TERMINATED BY ','
> FROM
> (SELECT 1 num, 'This string has no commas' str1,
> 'This string, however, has two commas' str2) data;
Query OK, 1 row affected (0.04 sec)
Поскольку третий столбец выходного файла (str2) – строка, содержа щая запятые, можно предположить, что у приложения, считывающе го файл comma1.txt, возникнут проблемы при синтаксическом разборе строк и распределении их по столбцам. Но сервер MySQL предпринял специальные меры предосторожности для подобных ситуаций. Вот со держимое файла comma1.txt:
1,This string has no commas,This string\, however\, has two commas
Как видите, запятые в третьем столбце экранированы обратным слэ шем, размещенным перед ними. Если выполнить этот же запрос, но использовать формат с разделителями «|», запятые не будут экраниро ваны. Если хочется использовать в выходном файле другой символ эк ранирования, например еще одну запятую, его можно задать с помо щью подблока fields escaped by.
Кроме разделителей столбцов можно задавать символ, используемый для разделения разных записей в файле данных. Если требуется, что бы каждая запись в выходном файле была отделена не символом новой строки, а каким то другим, можно воспользоваться подблоком lines:
mysql> SELECT emp_id, fname, lname, start_date

MySQL расширения языка SQL
265
> INTO OUTFILE 'C:\\TEMP\\emp_list_atsign.txt'
> FIELDS TERMINATED BY '|'
> LINES TERMINATED BY '@'
> FROM employee;
Query OK, 18 rows affected (0.03 sec)
Поскольку в файле emp_list_atsign.txt для разделения записей не ис пользуется символ новой строки, весь файл выглядит как одна длин ная текстовая строка, в которой каждая запись отделена символом '@':
1|Michael|Smith|2001 06 22@2|Susan|Barker|2002 09 12@3|Robert|Tyler|
2000 02 09@4|Susan|Hawthorne|2002 04 24@5|John|Gooding|2003 11 14@6|Helen|
Fleming|2004 03 17@7|Chris|Tucker|2004 09 15@8|Sarah|Parker|2002 12 02@9|
Jane|Grossman|2002 05 03@10|Paula|Roberts|2002 07 27@11|Thomas|Ziegler|
2000 10 23@12|Samantha|Jameson|2003 01 08@13|John|Blake|2000 05 11@14|
Cindy|Mason|2002 08 09@15|Frank|Portman|2003 04 01@16|Theresa|Markham|
2001 03 15@17|Beth|Fowler|2002 06 29@18|Rick|Tulman|2002 12 12@
Если понадобится сгенерировать файл данных для загрузки в элек тронную таблицу или рассылки в/за пределы организации, блок into outfile обеспечит достаточную гибкость для создания файла любого необходимого формата.
Сочетание выражений insert/update
Допустим, требуется создать таблицу для сбора информации о посеще нии клиентами отделений банка. Таблица должна содержать ID кли ента, ID отделения и столбец datetime с датой и временем последнего посещения отделения клиентом. Строки в таблицу добавляются вся кий раз, когда клиент посещает определенное отделение. Но если кли ент уже был в этом отделении, следует просто обновить столбец da tetime существующей строки. Вот описание таблицы:
CREATE TABLE branch_usage
(branch_id SMALLINT UNSIGNED NOT NULL,
cust_id INTEGER UNSIGNED NOT NULL,
last_visited_on DATETIME,
CONSTRAINT pk_branch_usage PRIMARY KEY (branch_id, cust_id)
);
Кроме трех столбцов таблица branch_usage определяет ограничение пер вичного ключа для столбцов branch_id и cust_id. Следовательно, сервер отклонит любую добавляемую в таблицу строку, пара значений отде ление/клиент которой уже есть в таблице.
Скажем, таблица создана, и клиент с ID 5 посещает главное отделение
(отделение с ID 1) за первую неделю три раза. После первого визита в таблицу branch_usage можно вставить запись, поскольку для клиента с ID 5 и отделения с ID 1 записи еще нет:
mysql> INSERT INTO branch_usage (branch_id, cust_id, last_visited_on)
> VALUES (1, 5, CURRENT_TIMESTAMP( ));
Query OK, 1 row affected (0.02 sec)

266
Приложение B
Однако при следующем посещении клиентом того же отделения потре буется обновить существующую запись, а не вставлять новую. В про тивном случае будет получена следующая ошибка:
ERROR 1062 (23000): Duplicate entry '1 5' for key 1
Чтобы избежать этой ошибки, можно запросить таблицу branch_usage и посмотреть, имеется ли данная пара значений клиент/отделение,
а затем уже вставить запись, если таковой не найдено, или обновить имеющуюся строку, если она уже существует. Однако чтобы избавить пользователей от хлопот, разработчики MySQL расширили выраже ние insert и обеспечили возможность определять необходимость изме нения одного или нескольких столбцов, если выражение insert дает сбой из за дублирования ключей. Следующее выражение предписыва ет серверу изменять столбец last_visited_on, если данные клиент и от деление уже есть в таблице branch_usage:
mysql> INSERT INTO branch_usage (branch_id, cust_id, last_visited_on)
> VALUES (1, 5, CURRENT_TIMESTAMP( ))
> ON DUPLICATE KEY UPDATE last_visited_on = CURRENT_TIMESTAMP( );
Query OK, 2 rows affected (0.02 sec)
Блок on duplicate key (при дублировании ключа) позволяет выполнять одно и то же выражение при каждом появлении клиента с ID 5 в отде лении с ID 1. Если выражение выполняется 100 раз, в результате пер вого прогона в таблицу добавляется одна строка. Следующие 99 выпол нений обеспечивают изменение столбца last_visited_on соответственно
Замещение команды replace
До версии 4.1 сервера MySQL операции с возможностью обновле ния и вставки осуществлялись с помощью команды replace (за местить). Это собственное выражение, которое сначала удаляет существующую строку, если такое значение первичного ключа уже существует, а потом уже вставляет новую строку в таблицу.
Выполняя операции с возможностью обновления и вставки при работе с версией 4.1 и более поздними, можно выбирать между командами replace и insert...on duplicate key.
Однако команда replace выполняет операцию удаления при встрече дублирующихся значений ключей, что может обусло вить цепную реакцию, если используется механизм хранения
InnoDB и наложены ограничения внешнего ключа. Если ограни чения созданы посредством опции on delete cascade, при удале нии строки целевой таблицы команда replace может автоматиче ски удалить и строки других таблиц. Поэтому обычно более без опасным считается использование блока on duplicate key выраже ния insert, а не более старой команды replace.

MySQL расширения языка SQL
267
текущему времени. Такой тип операций часто называют операциями
с возможностью обновления и вставки
(upsert), т. е. сочетанием выра жений update и insert.
Упорядоченные обновления и удаления
Ранее здесь было показано, как с помощью сочетания блоков limit и order by можно писать запросы, формирующие ранжированную вы борку (например, три лучших сотрудника по количеству открытых счетов). MySQL тоже позволяет использовать блоки limit и order by в выражениях update и delete, обеспечивая таким образом возмож ность изменять или удалять определенные строки таблицы на основа нии их ранга. Предположим, например, что требуется удалить строки таблицы, используемой для отслеживания регистраций пользовате лей в онлайновой банковской системе. Вот таблица, отслеживающая
ID клиента и дату/время регистрации:
CREATE TABLE login_history
(cust_id INTEGER UNSIGNED NOT NULL,
login_date DATETIME,
CONSTRAINT pk_login_history PRIMARY KEY (cust_id, login_date)
);
Следующее выражение заполняет таблицу login_history некоторыми данными путем формирования перекрестного соединения между таб лицами account и customer и формирования дат регистрации на основа нии значений столбца open_date таблицы account:
mysql> INSERT INTO login_history (cust_id, login_date)
> SELECT c.cust_id,
> ADDDATE(a.open_date, INTERVAL a.account_id * c.cust_id HOUR)
> FROM customer c CROSS JOIN account a;
Query OK, 312 rows affected (0.03 sec)
Records: 312 Duplicates: 0 Warnings: 0
Теперь таблица заполнена 312 строками относительно случайных дан ных. Ваша задача – раз в месяц просмотреть данные таблицы login_his tory
, составить для руководства отчет о тех, кто использует онлайновую банковскую систему, и затем удалить все записи кроме 50 последних.
Один из возможных подходов – написать запрос с использованием бло ков order by и limit для поиска 50 самых свежих регистраций:
mysql> SELECT login_date
> FROM login_history
> ORDER BY login_date DESC
> LIMIT 49,1;
+
+
| login_date |
+
+
| 2004 07 02 09:00:00 |
+
+
1 row in set (0.00 sec)

268
Приложение B
Вооружившись этой информацией, уже можно создать выражение delete
, удаляющее все строки, значение столбца login_date которых меньше даты, возвращенной запросом:
mysql> DELETE FROM login_history
> WHERE login_date < '2004 07 02 09:00:00';
Query OK, 262 rows affected (0.02 sec)
Теперь таблица содержит 50 последних регистраций. Однако MySQL
расширения позволяют достичь тех же результатов с помощью единст венного выражения delete с блоками limit и order by. Возвратив исход ные 312 строк в таблицу login_history, можно выполнить следующее выражение:
mysql> DELETE FROM login_history
> ORDER BY login_date ASC
> LIMIT 262;
Query OK, 262 rows affected (0.05 sec)
Это выражение сортирует строки по возрастанию значений столбца login_date
, затем первые 262 строки удаляются, а 50 самых свежих строк остаются.
В этом примере для построения блока limit необходимо было знать число строк в таблице (312 исходных строк – 50 остав шихся строк = 262 удалений). Лучше было бы отсортировать строки в убывающем порядке, указать серверу пропустить пер вые 50 строк и затем удалить оставшиеся строки:
DELETE FROM login_history
ORDER BY login_date DESC
LIMIT 49, 9999999;
Однако MySQL не обеспечивает возможности применения вто рого необязательного параметра при использовании блока limit в выражениях delete или update.
С помощью блоков limit и order by можно не только удалять, но и об новлять данные. Например, если банк решает для удержания лояль ных клиентов добавить по 100 долларов на каждый из десяти самых старых счетов, можно сделать следующее:
mysql> UPDATE account
> SET avail_balance = avail_balance + 100
> WHERE product_cd IN ('CHK', 'SAV', 'MM')
> ORDER BY open_date ASC
> LIMIT 10;
Query OK, 10 rows affected (0.06 sec)
Rows matched: 10 Changed: 10 Warnings: 0
Это выражение сортирует счета в возрастающем порядке по дате от крытия и затем изменяет первые десять записей, которыми в данном случае являются десять самых старых счетов.

MySQL расширения языка SQL
269
Многотабличные обновления и удаления
В определенных ситуациях для выполнения поставленной задачи мо жет понадобиться изменить или удалить данные из нескольких раз ных таблиц. Например, если обнаруживается, что в БД банка есть фик тивный клиент, выявленный в процессе аудита системы, вероятно, по надобится удалить данные из таблиц account, customer и individual.
Для этого раздела я создам набор клонов таблиц account, cus tomer и individual, назвав их account2, customer2 и individual2. Это позволит как защитить используемые в примере данные от из менений, так и избежать проблем с ограничениями внешнего ключа между таблицами (более подробно об этом в данном разде ле позже). Вот выражения create table для формирования трех таблиц клонов:
CREATE TABLE individual2 AS
SELECT * FROM individual;
CREATE TABLE customer2 AS
SELECT * FROM customer;
CREATE TABLE account2 AS
SELECT * FROM account;
Если бы ID фиктивного клиента был равен 1, можно было бы сгенери ровать три разных выражения delete для каждой из трех таблиц:
DELETE FROM account2
WHERE cust_id = 1;
DELETE FROM customer2
WHERE cust_id = 1;
DELETE FROM individual2
WHERE cust_id = 1;
Но в MySQL можно не писать отдельные выражения delete, а создать одно многотабличное выражение delete, которое в данном случае вы глядит так:
mysql> DELETE account2, customer2, individual2
> FROM account2 INNER JOIN customer2
> ON account2.cust_id = customer2.cust_id
> INNER JOIN individual2
> ON customer2.cust_id = individual2.cust_id
> WHERE individual2.cust_id = 1;
Query OK, 5 rows affected (0.02 sec)
Это выражение удаляет все пять строк, по одной из таблиц individual2
и customer2 и три из таблицы account2 (у клиента с ID = 1 три счета).
В этом выражении три отдельных блока:
delete
Указывает таблицы, строки которых предназначенны для удаления.

270
Приложение B
from
Указывает таблицы, позволяющие идентифицировать строки, ко торые должны быть удалены. Этот блок по форме и выполняемым функциям аналогичен блоку from в выражении select; в блок delete необязательно включать все перечисленные здесь таблицы.
where
Содержит условия фильтрации, используемые для идентификации строк, которые должны быть удалены.
Многотабличное выражение delete очень похоже на выражение select,
но с блоком delete вместо блока select. При удалении строк из одной таблицы с помощью многотабличного delete разница еще менее замет на. Например, вот выражение select, выбирающее ID всех счетов, при надлежащих Джону Хейварду (John Hayward):
mysql> SELECT account2.account_id
> FROM account2 INNER JOIN customer2
> ON account2.cust_id = customer2.cust_id
> INNER JOIN individual2
> ON individual2.cust_id = customer2.cust_id
> WHERE individual2.fname = 'John'
> AND individual2.lname = 'Hayward';
+
+
| account_id |
+
+
| 8 |
| 9 |
| 10 |
+
+
3 rows in set (0.01 sec)
Если просмотрев результаты, вы решите удалить из таблицы account2
все три счета Джона, потребуется только заменить в предыдущем за просе блок select блоком delete с указанием таблицы account2:
mysql> DELETE account2
> FROM account2 INNER JOIN customer2
> ON account2.cust_id = customer2.cust_id
> INNER JOIN individual2
> ON customer2.cust_id = individual2.cust_id
> WHERE individual2.fname = 'John'
> AND individual2.lname = 'Hayward';
Query OK, 3 rows affected (0.01 sec)
Надеюсь, это помогло лучше понять назначение блоков delete и from в многотабличном выражении delete. Оно функционально идентично следующему однотабличному выражению delete, определяющему ID
клиента Джона Хейварда с помощью подзапроса:
DELETE FROM account2
WHERE cust_id =

MySQL расширения языка SQL
271
(SELECT cust_id
FROM individual2
WHERE fname = 'John' AND lname = 'Hayward';
Применяя многотабличное выражение delete для удаления строк из одной таблицы, вы просто выбираете подобный запросу формат с со единением таблиц, а не традиционное выражение delete с подзапроса ми. Реальная мощь многотабличных выражений delete заключается в возможности удаления данных из нескольких таблиц одним выра жением, как показано в первом выражении этого раздела.
Кроме удаления строк из нескольких таблиц, MySQL также предостав ляет возможность изменять строки в нескольких таблицах с помощью
многотабличного обновления
(multitable update). Скажем, происходит слияние двух банков. В базах данных обоих банков есть перекрываю щиеся ID клиентов. Руководство одного из банков решает уладить про блему путем добавления 10 000 к каждому ID клиента своего банка,
чтобы можно было безопасно импортировать данные второго банка.
Следующий пример показывает, как с помощью одного выражения из менить ID клиента с ID 3 в таблицах individual2, customer2 и account2:
mysql> UPDATE individual2 INNER JOIN customer2
> ON individual2.cust_id = customer2.cust_id
> INNER JOIN account2
> ON customer2.cust_id = account2.cust_id
> SET individual2.cust_id = individual2.cust_id + 10000,
> customer2.cust_id = customer2.cust_id + 10000,
> account2.cust_id = account2.cust_id + 10000
> WHERE individual2.cust_id = 3;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 5 Changed: 4 Warnings: 0
Это выражение изменяет четыре строки – по одной в таблицах individ ual2
и customer2 и две в таблице account2. Синтаксис многотабличного выражения update очень похож на синтаксис однотабличного выраже ния update, за исключением того, что в блоке update указываются не сколько таблиц и соответствующие им условия соединения, а не про сто одна таблица. Как и однотабличное выражение update, многотаб личная версия включает блок set. Разница в том, что все упомянутые в блоке update таблицы можно изменить посредством блока set.
При использовании механизма хранения InnoDB, если задейст вованные таблицы имеют ограничения внешнего ключа, приме нять многотабличные выражения delete и update скорее всего не получится. Причина в том, что этот механизм не гарантирует проведение изменений в порядке, не нарушающем ограниче ния. Поэтому в такой ситуации следует использовать несколько однотабличных выражений в соответствующем порядке, так чтобы не нарушались ограничения внешнего ключа.

Решения к упражнениям