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

В выражении insert три основных компонента:•Имя таблицы, в которую должны быть добавлены данные.•Имена тех столбцов таблицы, которые должны быть заполнены.•Значения, которыми должны быть заполнены столбцы.Таким образом, не обязательно предоставлять данные для всех столбцов таблицы (если только все столбцы таблицы не были определеныкак not null). В некоторых случаях столбцы, не включенные в исходное выражение insert, будут заполнены позже с помощью выраженийupdate. Бывает, что столбец в какойто строке вообще никогда не заполняется данными (например, если заказ покупателя отменяетсядо поставки, столбец ship_date остается незаполненным).Формирование числовых ключейПрежде чем заполнить таблицу person данными, полезно обсудить процесс формирования значений числовых первичных ключей.

Кромевыбора числа «от фонаря» есть два варианта:•Найти в таблице самое большое на данный момент значение первичного ключа и прибавить 1.•Позволить серверу БД предоставить значение.Хотя первый вариант и кажется допустимым, он становится проблематичным в многопользовательской среде, поскольку два пользователямогут одновременно работать с таблицей и сгенерировать одно и то жезначение первичного ключа. Напротив, все серверы БД, присутствующие сегодня на рынке, обеспечивают более надежный, более устойчиЗаполнение и изменение таблиц43вый к ошибкам метод формирования числовых ключей. Иногда, например в Oracle Database, используется отдельный объект схемы (называемый последовательностью (sequence)). Однако в случае с MySQL надопросто включить для столбца первичного ключа свойство autoincrement (автоприращение). Обычно это делается при создании таблицы, номы занимаемся этим сейчас, чтобы изучить еще одно SQLвыражениеуправления схемой, которое меняет описание существующей таблицы:ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;Это выражение, по существу, переопределяет столбец person_id таблицы person.

Теперь команда describe для этой таблицы отобразит атрибут автоприращения в столбце Extra для person_id:mysql> DESC person;+++++++| Field| Type| Null | Key | Default | Extra|+++++++| person_id | smallint(5) unsigned || PRI | NULL| auto_increment || .||||||| .||||||| .||||||При вводе данных в таблицу person просто задайте значение null длястолбца person_id, и MySQL заполнит столбец следующим доступнымчислом (для столбцов с автоприращением MySQL по умолчанию начинает отсчет с 1).Выражение insertТеперь, когда все расставлено по местам, пора добавить коекакие данные.

Следующее выражение создает в таблице person строку для Вильяма Тернера (William Turner):mysql> INSERT INTO person> (person_id, fname, lname, gender, birth_date)> VALUES (null, 'William','Turner', 'M', '19720527');Query OK, 1 row affected (0.01 sec)Обратная связь («Query OK, 1 row affected») сообщает, что синтаксисвыражения правильный и что в базу данных была добавлена однастрока (поскольку это было выражение insert). С помощью выражения select можно увидеть только что добавленные в таблицу данные:mysql> SELECT person_id, fname, lname, birth_date> FROM person;+++++| person_id | fname | lname | birth_date |+++++|1 | William | Turner | 19720527 |+++++1 row in set (0.06 sec)44Глава 2. Создание и заполнение базы данныхКак видите, сервер MySQL генерирует для первичного ключа значение 1.

Поскольку в таблице person всего одна строка, я не стал указывать, какая именно строка меня интересует, и попросту извлек всестроки таблицы. Если бы строк было несколько, можно было бы добавить блок where и указать, что требуется извлечь данные для строки,значение person_id которой равно единице:mysql> SELECT person_id, fname, lname, birth_date> FROM person> WHERE person_id = 1;+++++| person_id | fname | lname | birth_date |+++++|1 | William | Turner | 19720527 |+++++1 row in set (0.00 sec)В данном запросе задается конкретное значение первичного ключа.

Нодля поиска строк может использоваться любой столбец таблицы, о чемсвидетельствует следующий запрос, выбирающий все строки, столбцыlname которых содержат значение 'Turner':mysql> SELECT person_id, fname, lname, birth_date> FROM person> WHERE lname = 'Turner';+++++| person_id | fname | lname | birth_date |+++++|1 | William | Turner | 19720527 |+++++1 row in set (0.00 sec)Прежде чем двигаться дальше, сделаю несколько замечаний относительно предыдущего выражения insert:• Не заданы значения ни для одного столбца адреса. Это нормально,поскольку для них допускается значение null.• Для столбца birth_date было предоставлено строковое значение. Поскольку строка соответствует формату, приведенному в табл. 2.4,MySQL преобразует ее для вас в дату.• Количество и типы столбцов и предоставляемых значений должнысовпадать. Если указывается семь столбцов и предоставляется только шесть значений, или если предоставленные значения не могутбыть преобразованы в соответствующий тип данных для соответствующего столбца, вы получите ошибку.Вильям также предоставил информацию о своих любимых блюдах.Вот три выражения вставки, позволяющих записать его кулинарныепредпочтения:mysql> INSERT INTO favorite_food (person_id, food)> VALUES (1, 'pizza');Query OK, 1 row affected (0.01 sec)Заполнение и изменение таблиц45mysql> INSERT INTO favorite_food (person_id, food)> VALUES (1, 'cookies');Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO favorite_food (person_id, food)> VALUES (1, 'nachos');Query OK, 1 row affected (0.01 sec)А вот запрос, извлекающий любимые блюда Вильяма в алфавитномпорядке с помощью блока order by (упорядочить по):mysql> SELECT food> FROM favorite_food> WHERE person_id = 1> ORDER BY food;++| food|++| cookies || nachos || pizza |++3 rows in set (0.02 sec)Блок order by указывает серверу, как сортировать данные, возвращаемые запросом.

Без order by данные таблицы будут извлечены в произвольном порядке.Чтобы Вильям не скучал, можно выполнить еще одно выражение insert и добавить в таблицу 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', '19751102',> '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 | 19720527 ||2 | Susan | Smith | 19751102 |+++++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', '19680115');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.