alan_beaulieu-learning_sql-ru (Учебник по SQL), страница 10

Если требуется ввести данные в обетаблицы, сначала следует создать строку в 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 строка не соответствует ожидаемому формату, будет сформирована другая безопасная ошибка.

Вот пример использования формата даты, не соответствующегоприменяемому по умолчанию «YYYYMMDD»:mysql> UPDATE person> SET birth_date = 'DEC211980'> 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 задает значение '00000000', как показано ниже:mysql> SELECT birth_date> FROM person> WHERE person_id = 1;49Банковская схема++| birth_date |++| 00000000 |++Опять же я бы предпочел предупреждению ошибку, поскольку сейчасв таблицу person внесены неверные данные (00000000).Банковская схемаДалее в книге используется группа таблиц, моделирующих банк, обслуживающий небольшой населенный пункт.

Среди этих таблиц можно назвать Employee (сотрудник), Branch (отделение), Account (счет), Customer (клиент), Product (услуга), Transaction (транзакция) и Loan (заем).Всю схему и пример данных следует создать после выполнения 13 шагов для загрузки сервера MySQL и формирования примера данных,приведенных в начале этой главы. Диаграмму с таблицами, их столбцами и связями можно увидеть в приложении А.В табл. 2.9 показаны все таблицы, используемые в банковской схеме,и даны их краткие описания.Таблица 2.9.

Описания банковской схемыТаблицаОписаниеAccountКонкретный счет, открытый для конкретного клиентаBusinessКлиентюридическое лицо (подтип таблицы Customer)CustomerФизическое или юридическое лицо, известные банкуDepartmentГруппа сотрудников банка, реализующая определенную банковскую функциюEmployeeЧеловек, работающий в банкеIndividualКлиентфизическое лицо (подтип таблицы Customer)OfficerЧеловек, которому разрешено вести дела от лица клиентаюридического лицаProductУслуга банка, предлагаемая клиентамProduct_typeГруппа функционально схожих услугTransactionИзменение баланса счетаНе бойтесь экспериментировать с таблицами, добавляйте собственныетаблицы, чтобы расширить бизнесфункцию банка. Чтобы получитьгарантированно нетронутый пример данных, всегда можно удалитьБД и восстановить ее из загруженного файла.Чтобы посмотреть доступные таблицы БД, можно использовать команду show tables: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.11ntType 'help;' or '\h' for help.

Type '\c' to clear the buffer.В данном случае ID соединения – 2. Эта информация может быть полезной администратору БД в случае какихлибо неполадок. Например, если требуется прервать плохо сформированный запрос, выполняющийся часами.После того как сервер открыл соединение, проверив достоверностьимени пользователя и пароля, можно выполнять запросы (и другие52Глава 3. Азбука запросовSQLвыражения). При каждом запросе перед выполнением выражения сервер проверяет следующее:• Есть ли у вас разрешение на выполнение выражения?• Есть ли у вас разрешение на доступ к необходимым данным?• Правилен ли синтаксис выражения?Если выражение проходит все три теста, оно передается оптимизатору запросов, работа которого заключается в определении наиболее эффективного способа выполнения запроса. Оптимизатор рассмотрит порядок соединения таблиц, перечисленных в запросе, и доступные индексы, а затем определит план выполнения, используемый серверомпри выполнении этого запроса.Многие из вас заинтересуются тем, как понять и воздействоватьна выбор сервером БД плана выполнения.

Читатели, использующие MySQL, могут посмотреть книгу «High Performance MySQL»(O’Reilly). Кроме прочего, вы научитесь генерировать индексы,анализировать планы выполнения, оказывать влияние на оптимизатор посредством подсказок запроса и настраивать параметры запуска сервера. Для пользователей Oracle Database или SQLServer есть десятки книг по этой тематике.По завершении выполнения запроса сервер возвращает в вызывающееприложение (опять же в инструмент 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. Блоки запросаБлокНазначениеSelectОпределяет столбцы, которые должны быть включены в результирующий набор запросаFromУказывает таблицы, из которых должны быть извлечены данные,и то, как эти таблицы должны быть соединеныWhereОграничивает число строк в окончательном результирующем набореGroup by Используется для группировки строк по одинаковым значениямстолбцовHavingОграничивает число строк в окончательном результирующем наборе с помощью группировки данныхOrder by Сортирует строки окончательного результирующего набора по одному или более столбцамВсе показанные в табл.

3.1 блоки включены в спецификацию ANSI.Кроме того, есть еще несколько блоков, используемых только в MySQL.Они будут рассмотрены в приложении В. В следующих разделах мыподробнее рассмотрим использование шести основных блоков запроса.54Глава 3. Азбука запросовБлок selectДаже несмотря на то, что блок select является первым в выраженииselect, сервер БД обрабатывает его одним из последних. Причина в том,что прежде чем можно будет определить, что включать в окончательный результирующий набор, необходимо знать все столбцы, которыемогли бы быть включены в этот набор.