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

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

ORDER BY food;
+
+
| food |
+
+
| cookies |
| nachos |
| pizza |
+
+
3 rows in set (0.02 sec)
Блок order by указывает серверу, как сортировать данные, возвращае мые запросом. Без order by данные таблицы будут извлечены в произ вольном порядке.
Чтобы Вильям не скучал, можно выполнить еще одно выражение in sert и добавить в таблицу person Сьюзен Смит (Susan Smith):
mysql> INSERT INTO person
> (person_id, fname, lname, gender, birth_date,
> address, city, state, country, postal_code)
> VALUES (null, 'Susan','Smith', 'F', '1975 11 02',
> '23 Maple St.', 'Arlington', 'VA', 'USA', '20220');
Query OK, 1 row affected (0.01 sec)
Если снова запросить таблицу, мы увидим, что строке Сьюзен в каче стве первичного ключа было присвоено значение 2:
mysql> SELECT person_id, fname, lname, birth_date
> FROM person;
+
+
+
+
+
| person_id | fname | lname | birth_date |
+
+
+
+
+
| 1 | William | Turner | 1972 05 27 |
| 2 | Susan | Smith | 1975 11 02 |
+
+
+
+
+
2 rows in set (0.00 sec)
Обновление данных
При первичном вводе информации в таблицу о Вильяме Тернере в вы ражение insert не были включены данные для различных столбцов ад

46
Глава 2. Создание и заполнение базы данных реса. Следующее выражение показывает, как заполнить эти столбцы с помощью выражения update:
mysql> UPDATE person
> SET address = '1225 Tremont St.',
> city = 'Boston',
> state = 'MA',
> country = 'USA',
> postal_code = '02138'
> WHERE person_id = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Сервер ответил двустрочным сообщением: фраза «Rows matched: 1»
(Подходящих строк: 1) говорит о том, что условия блока where соответ ствуют только одной строке таблицы, а «Changed: 1» (Изменено: 1) оз начает, что была изменена одна строка таблицы. Поскольку в блоке where задан первичный ключ строки Вильяма, именно так и должно было произойти.
Как видите, одним выражением update можно изменять несколько столбцов. Одним выражением также можно изменять несколько строк в зависимости от условий блока where. Рассмотрим, к примеру, что произошло бы, если бы блок where выглядел следующим образом:
WHERE person_id < 10
Поскольку значение person_id и у Вильяма, и у Сьюзен меньше 10, из менениям подвергнуться обе строки. Если опустить блок where совсем,
выражение update обновит все строки таблицы.
Удаление данных
Похоже, Вильям и Сьюзен не вполне ладят друг с другом, поэтому один из них должен уйти. Поскольку Вильям был первым, Сьюзен бу дет вежливо «выставлена» выражением delete:
mysql> DELETE FROM person
> WHERE person_id = 2;
Query OK, 1 row affected (0.01 sec)
Опять же для выделения интересующей строки используется первич ный ключ, поэтому из таблицы удаляется всего одна строка. Как и в слу чае выражения update, можно удалить и несколько строк. Все зависит от условий, заданных в блоке where. Если блок where опущен, будут уда лены все строки.
Когда портятся хорошие выражения
До сих пор все SQL выражения для работы с данными, приведенные в этой главе, были правильными и играли по правилам. Однако, исхо

Когда портятся хорошие выражения
47
дя из описаний таблиц person и favorite_food, у вас есть много возмож ностей наделать ошибок при вставке или изменении данных. В этом разделе приведены некоторые из распространенных ошибок и показа но, как сервер MySQL будет на них реагировать.
Неуникальный первичный ключ
Поскольку описания таблиц включают создание ограничений первич ного ключа, MySQL проверит, чтобы в таблицы не вводились дублиру ющие значения. Следующее выражение делает попытку обойти свой ство автоприращения столбца person_id и создать в таблице person еще одну строку со значением person_id, равным 1:
mysql> INSERT INTO person
> (person_id, fname, lname, gender, birth_date)
> VALUES (1, 'Charles','Fulton', 'M', '1968 01 15');
ERROR 1062 (23000): Duplicate entry '1' for key 1
Ничто не мешает (по крайней мере, в текущей схеме) создать две стро ки с идентичными именами, адресами, датами рождения и т. д., если в столбце person_id у них разные значения.
Несуществующий внешний ключ
Описание таблицы favorite_food включает создание ограничения внешнего ключа для столбца person_id. Это ограничение гарантирует,
что все значения person_id, введенные в таблицу favorite_food, имеют ся в таблице person. Вот что произошло бы при попытке создания стро ки, нарушающей это ограничение:
mysql> INSERT INTO favorite_food (person_id, food)
> VALUES (999, 'lasagna');
ERROR 1216 (23000): Cannot add or update a child row:
a foreign key constraint fails
В этом случае таблица favorite_food считается дочерней (child), а таб лица person – родителем (parent), поскольку таблица favorite_food за висит от данных таблицы person. Если требуется ввести данные в обе таблицы, сначала следует создать строку в parent, а затем уже можно будет ввести данные в favorite_food.
Ограничения внешнего ключа выполняются, только если табли цы создаются с использованием механизма хранения InnoDB.
Механизмы хранения MySQL обсуждаются в главе 12.
Применение недопустимых значений
Столбец gender таблицы person может иметь только два значения: 'M'
для мужчин и 'F' для женщин. Если по ошибке делается попытка за дать любое другое значение, будет получен следующий ответ:

48
Глава 2. Создание и заполнение базы данных mysql> UPDATE person
> SET gender = 'Z'
> WHERE person_id = 1;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
Выражение update не дало сбой, но было сформировано предупрежде ние. Чтобы увидеть описание предупреждения, можно выполнить ко манду show warnings (показать предупреждения):
mysql> SHOW WARNINGS;
+
+
+
+
| Level | Code | Message |
+
+
+
+
| Warning | 1265 | Data truncated for column 'gender' at row 1 |
+
+
+
+
1 row in set (0.00 sec)
Это я назвал бы безопасной ошибкой (soft error), поскольку сервер MySQL
не забраковал выражение, но также и не произвел ожидаемых результа тов. Чтобы решить эту проблему, сервер MySQL заполняет столбец gender пустой строкой ('') – определенно не тем, что предполагалось получить.
Лично я предпочел бы, чтобы выражение было отвергнуто с сообщением об ошибке, что и сделали бы большинство других серверов БД.
Недействительные преобразования дат
Если предлагаемая для заполнения столбца date строка не соответству ет ожидаемому формату, будет сформирована другая безопасная ошиб ка. Вот пример использования формата даты, не соответствующего применяемому по умолчанию «YYYY MM DD»:
mysql> UPDATE person
> SET birth_date = 'DEC 21 1980'
> WHERE person_id = 1;
Query OK, 1 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
Команда show warnings выдает следующее:
mysql> SHOW WARNINGS;
+
+
+
+
| Level | Code | Message |
+
+
+
+
| Warning | 1265 | Data truncated for column 'birth_date' at row 1 |
+
+
+
+
Поскольку это столбец типа date, birth_date не может быть пустой стро кой, поэтому MySQL задает значение '0000 00 00', как показано ниже:
mysql> SELECT birth_date
> FROM person
> WHERE person_id = 1;

Банковская схема
49
+
+
| birth_date |
+
+
| 0000 00 00 |
+
+
Опять же я бы предпочел предупреждению ошибку, поскольку сейчас в таблицу person внесены неверные данные (0000 00 00).
Банковская схема
Далее в книге используется группа таблиц, моделирующих банк, об служивающий небольшой населенный пункт. Среди этих таблиц мож но назвать Employee (сотрудник), Branch (отделение), Account (счет), Cus tomer
(клиент), Product (услуга), Transaction (транзакция) и Loan (заем).
Всю схему и пример данных следует создать после выполнения 13 ша гов для загрузки сервера MySQL и формирования примера данных,
приведенных в начале этой главы. Диаграмму с таблицами, их столб цами и связями можно увидеть в приложении А.
В табл. 2.9 показаны все таблицы, используемые в банковской схеме,
и даны их краткие описания.
Таблица 2.9. Описания банковской схемы
Не бойтесь экспериментировать с таблицами, добавляйте собственные таблицы, чтобы расширить бизнес функцию банка. Чтобы получить гарантированно нетронутый пример данных, всегда можно удалить
БД и восстановить ее из загруженного файла.
Чтобы посмотреть доступные таблицы БД, можно использовать ко манду show tables:
Таблица
Описание
Account
Конкретный счет, открытый для конкретного клиента
Business
Клиент юридическое лицо (подтип таблицы Customer)
Customer
Физическое или юридическое лицо, известные банку
Department
Группа сотрудников банка, реализующая определенную бан ковскую функцию
Employee
Человек, работающий в банке
Individual
Клиент физическое лицо (подтип таблицы Customer)
Officer
Человек, которому разрешено вести дела от лица клиента юри дического лица
Product
Услуга банка, предлагаемая клиентам
Product_type
Группа функционально схожих услуг
Transaction
Изменение баланса счета

50
Глава 2. Создание и заполнение базы данных mysql> SHOW TABLES;
+
+
| Tables_in_bank |
+
+
| account |
| branch |
| business |
| customer |
| department |
| employee |
| favorite_food |
| individual |
| officer |
| person |
| product |
| product_type |
| transaction |
+
+
13 rows in set (0.10 sec)
Вместе с 11 таблицами банковской схемы в список вошли две табли цы, созданные в этой главе – person и favorite_food. Эти таблицы не бу дут использоваться в последующих главах, поэтому их можно свобод но удалить с помощью следующих команд:
mysql> DROP TABLE favorite_food;
Query OK, 0 rows affected (0.56 sec)
mysql> DROP TABLE person;
Query OK, 0 rows affected (0.05 sec)
Посмотреть столбцы таблицы можно с помощью команды describe. Вот пример результата выполнения этой команды для таблицы customer:
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.03 sec)
Чем свободнее вы будете чувствовать себя с примером БД, тем понятнее будут примеры и, следовательно, концепции, представленные в сле дующих главах.

Азбука запросов
Первые две главы содержали несколько примеров запросов к базам данных (т. е. выражений select). Теперь пришло время поближе рас смотреть разные части выражения select и их взаимодействие.
Механика запроса
Прежде чем анализировать выражение select, любопытно узнать, как сервер MySQL (или, коли на то пошло, любой сервер БД) выполняет за просы. Если вы используете клиентскую программу командной строки
mysql
(что я предполагаю), то уже зарегистрировались на сервере
MySQL, предоставив свои имя пользователя и пароль (и, возможно,
имя хоста, если сервер MySQL выполняется на другом компьютере).
Как только сервер проверил правильность имени пользователя и пароля, для вас создается соединение с БД. Это соединение удержива ется запросившим его приложением (которым в данном случае являет ся инструмент mysql) до тех пор, пока приложение не высвободит соеди нение (например, в результате введения команды quit) или пока соеди нение не будет закрыто сервером (например, при выключении сервера).
Каждому соединению с сервером MySQL присваивается идентифика тор (ID), предоставляемый пользователю сразу после регистрации:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.11 nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
В данном случае ID соединения – 2. Эта информация может быть по лезной администратору БД в случае каких либо неполадок. Напри мер, если требуется прервать плохо сформированный запрос, выпол няющийся часами.
После того как сервер открыл соединение, проверив достоверность имени пользователя и пароля, можно выполнять запросы (и другие

52
Глава 3. Азбука запросов
SQL выражения). При каждом запросе перед выполнением выраже ния сервер проверяет следующее:
•
Есть ли у вас разрешение на выполнение выражения?
•
Есть ли у вас разрешение на доступ к необходимым данным?
•
Правилен ли синтаксис выражения?
Если выражение проходит все три теста, оно передается оптимизато
ру запросов
, работа которого заключается в определении наиболее эф фективного способа выполнения запроса. Оптимизатор рассмотрит по рядок соединения таблиц, перечисленных в запросе, и доступные ин дексы, а затем определит план выполнения, используемый сервером при выполнении этого запроса.
Многие из вас заинтересуются тем, как понять и воздействовать на выбор сервером БД плана выполнения. Читатели, использую щие MySQL, могут посмотреть книгу «High Performance MySQL»
(O’Reilly). Кроме прочего, вы научитесь генерировать индексы,
анализировать планы выполнения, оказывать влияние на опти мизатор посредством подсказок запроса и настраивать парамет ры запуска сервера. Для пользователей Oracle Database или SQL
Server есть десятки книг по этой тематике.
По завершении выполнения запроса сервер возвращает в вызывающее приложение (опять же в инструмент mysql) результирующий набор
(result set). Как было упомянуто в главе 1, результирующий набор –
это просто еще одна таблица со строками и столбцами. Если по запросу не удается найти никаких данных, инструмент mysql отобразит сооб щение, приведенное в конце следующего примера:
mysql> SELECT emp_id, fname, lname
> FROM employee
> WHERE lname = 'Bkadfl';
Empty set(0.00 sec)
Если запрос возвращает одну или более строк, программа форматиру ет результаты, добавляя заголовки столбцов и обводя столбцы рамкой из символов , | и +, как показано в следующем примере:
mysql> SELECT fname, lname
> FROM employee;
+
+
+
| fname | lname |
+
+
+
| Michael | Smith |
| Susan | Barker |
| Robert | Tyler |
| Susan | Hawthorne |
| John | Gooding |
| Helen | Fleming |
| Chris | Tucker |

Блоки запроса
53
| Sarah | Parker |
| Jane | Grossman |
| Paula | Roberts |
| Thomas | Ziegler |
| Samantha | Jameson |
| John | Blake |
| Cindy | Mason |
| Frank | Portman |
| Theresa | Markham |
| Beth | Fowler |
| Rick | Tulman |
+
+
+
18 rows in set (0.00 sec)
Этот запрос возвращает имена и фамилии всех сотрудников из табли цы employee. После отображения последней строки данных инструмент
mysql
выводит на экран сообщение, указывающее, сколько строк было возвращено, в данном случае – 18 строк.
Блоки запроса
Выражение select могут образовывать несколько компонентов, или
блоков
(clauses). Хотя при работе с MySQL обязательным является только один из них (блок select), обычно в запрос включаются, по крайней мере, два три из шести доступных блоков. В табл. 3.1 показа ны разные блоки и их назначение.
Таблица 3.1. Блоки запроса
Все показанные в табл. 3.1 блоки включены в спецификацию ANSI.
Кроме того, есть еще несколько блоков, используемых только в MySQL.
Они будут рассмотрены в приложении В. В следующих разделах мы подробнее рассмотрим использование шести основных блоков запроса.
Блок
Назначение
Select
Определяет столбцы, которые должны быть включены в результи рующий набор запроса
From
Указывает таблицы, из которых должны быть извлечены данные,
и то, как эти таблицы должны быть соединены
Where
Ограничивает число строк в окончательном результирующем наборе
Group by
Используется для группировки строк по одинаковым значениям столбцов
Having
Ограничивает число строк в окончательном результирующем набо ре с помощью группировки данных
Order by
Сортирует строки окончательного результирующего набора по одно му или более столбцам

54
Глава 3. Азбука запросов
Блок select
Даже несмотря на то, что блок select является первым в выражении select
, сервер БД обрабатывает его одним из последних. Причина в том,
что прежде чем можно будет определить, что включать в окончатель ный результирующий набор, необходимо знать все столбцы, которые
могли бы
быть включены в этот набор. Поэтому, чтобы полностью по нять роль блока select, надо немного разобраться с блоком from. Вот за прос для начала:
mysql> SELECT *
> FROM department;
+
+
+
| dept_id | name |
+
+
+
| 1 | Operations |
| 2 | Loans |
| 3 | Administration |
+
+
+
3 rows in set (0.04 sec)
В данном запросе в блоке from указана всего одна таблица (department),
и блок select показывает, что в результирующий набор должны быть включены все столбцы (обозначено символом «*») таблицы depart ment
. Этот запрос можно перевести на естественный язык следующим образом:
Покажи мне все столбцы таблицы department.
Выбрать все столбцы можно не только с помощью символа звездочки,
но и явно указав имена интересующих столбцов:
mysql> SELECT dept_id, name
> FROM department;
+
+
+
| dept_id | name |
+
+
+
| 1 | Operations |
| 2 | Loans |
| 3 | Administration |
+
+
+
3 rows in set (0.01 sec)
Результаты аналогичны первому запросу, поскольку в блоке select указаны все столбцы таблицы department (dept_id и name). А можно вы брать только некоторые из столбцов таблицы department: