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

Следующийшаг – дать столбцам имена и назначить типы данных. В табл. 2.6 показан первый вариант.Таблица 2.6. Таблица Person (человек), первое приближениеСтолбецТипNameVarchar(40)GenderChar(1)Birth_dateDateAddressVarchar(100)Favorite_foodsVarchar(200)Допустимые значенияM, FСтолбцы name, address и favorite_foods типа varchar позволяют записывать информацию в свободной форме. В столбце gender (пол) допускается только один символ, M (М) или F (Ж).

Столбцу birth_date (дата рождения) назначен тип date, поскольку точное время не требуется.Шаг 2: уточнениеВ главе 1 была представлена концепция нормализации, что являетсяпроцессом обеспечения отсутствия в БД дублирующихся (кроме внешних ключей) или составных столбцов. При повторном анализе столбцов таблицы возникают следующие соображения:• Столбец name на самом деле является составным объектом, включающим имя и фамилию.• Поскольку несколько человек могут иметь одинаковые имя, пол,дату рождения и т. д., в таблице person нет столбцов, гарантирующих уникальность.• Столбец address – тоже составной объект, включающий улицу, город, штат/область, страну и почтовый индекс.• Столбец favorite_foods – это список, содержащий 0, 1 или более независимых элементов. Было бы лучше вынести эти данные в отдельную таблицу, включающую внешний ключ к таблице person, чтобыобозначить человека, к которому приписано конкретное блюдо.В табл.

2.7 можно увидеть нормализованный вариант таблицы personпосле учета всех этих замечаний.Теперь, когда у таблицы person есть первичный ключ (person_id), гарантирующий уникальность, следующим шагом будет построение таблицы favorite_food, включающей внешний ключ к таблице person. Результат показан в табл. 2.8.Столбцы person_id и food (блюдо) образуют первичный ключ таблицыfavorite_food. Столбец person_id также является внешним ключомк таблице person.38Глава 2. Создание и заполнение базы данныхТаблица 2.7. Таблица Person, второе приближениеСтолбецТипPerson_idSmallint (unsigned)First_nameVarchar(20)Last_nameVarchar(20)GenderChar(1)Birth_dateDateStreetVarchar(30)CityVarchar(20)StateVarchar(20)CountryVarchar(20)Postal_codeVarchar(20)Допустимые значенияM, FТаблица 2.8. Таблица Favorite_food (любимое блюдо)СтолбецТипPerson_idSmallint (unsigned)FoodVarchar(20)Шаг 3: построение SQLвыраженийуправления схемой данныхТеперь, по завершении проектирования двух таблиц для размещенияперсональной информации, следующим шагом является формирование SQLвыражений для создания таблиц в БД.

Вот выражение длясоздания таблицы person:CREATE TABLE person(person_id SMALLINT UNSIGNED,fname VARCHAR(20),lname VARCHAR(20),gender CHAR(1),birth_date DATE,address VARCHAR(30),city VARCHAR(20),state VARCHAR(20),country VARCHAR(20),postal_code VARCHAR(20),CONSTRAINT pk_person PRIMARY KEY (person_id));В этом выражении должно быть понятно все, кроме последнего элемента. При описании таблицы необходимо сообщить серверу БД, какой столбец или столбцы будут играть роль первичного ключа таблиСоздание таблиц39цы.

Осуществляется это путем создания ограничения (constraint) длятаблицы. В описание таблицы можно добавить ограничение одного изнескольких типов. Данное ограничение является ограничением первичного ключа (primarykey constraint). Оно накладывается на столбецperson_id и получает имя pk_person. Обычно я начинаю имена ограничений первичного ключа с приставки pk_, а затем указываю имя таблицы, чтобы при просмотре списка таких ограничений было ясно, чемкаждое из них является.Говоря об ограничении, упомянем еще один тип, который мог бы бытьполезным для таблицы person. В табл.

2.7 был добавлен третий столбецдля допустимых значений определенных столбцов (например 'M' и 'F'для столбца gender). Это другой тип ограничения – проверочное ограничение (check constraint), ограничивающее допустимые значения конкретного столбца. MySQL позволяет вводить в описание столбца проверочное ограничение:gender CHAR(1) CHECK (gender IN ('M','F')),На большинстве серверов БД проверочные ограничения работают соответствующим образом, а сервер MySQL допускает описание проверочных ограничений, но не выполняет их проверку.

Но MySQL предоставляет другой символьный тип данных – enum (перечисление), который вводит проверочное ограничение в описание типа. Вот как это выглядело бы для описания столбца gender:gender ENUM('M','F'),Вот как выглядит создание таблицы person с введением типов данныхenum для столбца gender:CREATE TABLE person(person_id SMALLINT UNSIGNED,fname VARCHAR(20),lname VARCHAR(20),gender ENUM('M','F'),birth_date DATE,address VARCHAR(30),city VARCHAR(20),state VARCHAR(20),country VARCHAR(20),postal_code VARCHAR(20),CONSTRAINT pk_person PRIMARY KEY (person_id));Позже в данной главе будет показано, что происходит при попытке добавить в столбец данные, не соответствующие проверочному ограничению (или, в случае MySQL, значениям перечисления).Теперь все готово для выполнения выражения create table с помощьюинструмента командной строки mysql.

Вот как это выглядит:mysql> CREATE TABLE person> (person_id SMALLINT UNSIGNED,40Глава 2. Создание и заполнение базы данных> fname VARCHAR(20),> lname VARCHAR(20),> gender ENUM('M','F'),> birth_date DATE,> address VARCHAR(30),> city VARCHAR(20),> state VARCHAR(20),> country VARCHAR(20),> postal_code VARCHAR(20),> CONSTRAINT pk_person PRIMARY KEY (person_id)> );Query OK, 0 rows affected (0.27 sec)После обработки выражения create table сервер MySQL возвращает сообщение «Query OK, 0 rows affected» (Запрос выполнен без ошибок,0 строк подверглось обработке), что говорит об отсутствии синтаксических ошибок в выражении. Если требуется убедиться, что таблица person действительно существует, можно использовать команду describe(описать) (или desc для краткости) и посмотреть описание таблицы:mysql> DESC person;+++++++| Field| Type| Null | Key | Default | Extra |+++++++| person_id | smallint(5) unsigned || PRI | 0||| fname| varchar(20)| YES || NULL||| lname| varchar(20)| YES || NULL||| gender| enum('M','F')| YES || NULL||| birth_date | date| YES || NULL||| address| varchar(30)| YES || NULL||| city| varchar(20)| YES || NULL||| state| varchar(20)| YES || NULL||| country| varchar(20)| YES || NULL||| postal_code | varchar(20)| YES || NULL||+++++++10 rows in set (0.06 sec)Что такое Null?В некоторых случаях невозможно предоставить значение определенного столбца таблицы.

Например, при добавлении данныхо новом заказе покупателя значение столбца ship_date (дата доставки) еще не может быть определено. В этом случае говорят,что столбец является нулевым (null) (обратите внимание, я несказал, равен нулю), что указывает на отсутствие значения.При проектировании таблицы можно определить, какие столбцы могут быть нулевыми (по умолчанию), а какие – нет (это обозначается путем добавления ключевых слов not null (ненулевой)после описания типа).41Создание таблицСмысл столбцов 1 и 2 результата выполнения выражения describe очевиден. Столбец 3 показывает, можно ли пропустить тот или иной столбец при вводе данных в таблицу. Я намеренно пока не включил эту темув обсуждение (краткие рассуждения по этому вопросу можно найти воврезке «Что такое Null?»), она будет полностью рассмотрена в главе 4.Четвертый столбец показывает, участвует ли столбец в формированиикакоголибо ключа (первичного или внешнего).

В данном случае столбец person_id отмечен как первичный ключ. Столбец 5 показывает, будет ли определенный столбец заполнен значением по умолчанию в случае, если он пропущен при вводе данных в таблицу. Для столбца person_id значением по умолчанию является 0, хотя оно будет использовано только один раз, поскольку каждая строка таблицы person должнасодержать в данном столбце уникальное значение (это первичныйключ). Шестой столбец (названный Extra (дополнительно)) содержитлюбую другую информацию, относящуюся к столбцу.Теперь, после создания таблицы person, следующий шаг – создать таблицу favorite_food:mysql> CREATE TABLE favorite_food> (person_id SMALLINT UNSIGNED,> food VARCHAR(20),> CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),> CONSTRAINT fk_person_id FOREIGN KEY (person_id)>REFERENCES person (person_id)> );Query OK, 0 rows affected (0.10 sec)Это очень похоже на выражение create table для таблицы person, за несколькими исключениями:• Поскольку у человека может быть несколько любимых блюд (чтои стало причиной создания данной таблицы), одного столбца person_id для обеспечения уникальности в таблице недостаточно.

Поэтому первичный ключ данной таблицы состоит из двух столбцов:person_id и food.• Таблица favorite_food содержит другой тип ограничения – ограничение внешнего ключа (foreignkey constraint). Оно ограничивает значения столбца person_id таблицы favorite_food, позволяя ему включать только те значения, которые есть в таблице person.

При такомограничении не получится включить в таблицу favorite_food строку,показывающую, что person_id 27 любит пиццу, если в таблице personнет строки со значением 27 для person_id.Если при создании таблицы ограничение внешнего ключа небыло указано, его можно добавить позже с помощью оператораalter table (изменить таблицу).После выполнения выражения create table по команде describe будетвыведено следующее:42Глава 2. Создание и заполнение базы данныхmysql> DESC favorite_food;+++++++| Field| Type| Null | Key | Default | Extra |+++++++| person_id| smallint(5) unsigned || PRI | 0||| food| varchar(20)|| PRI |||+++++++Теперь, когда есть таблица, следующим логичным шагом будет добавить в нее коекакие данные.Заполнение и изменение таблицИмея таблицы person и favorite_food, можно приступить к изучениючетырех SQLвыражений для работы с данными: insert, update, deleteи select.Вставка данныхПоскольку пока что в наших таблицах person и favorite_food нет данных, из четырех SQLвыражений для работы с данными первым рассмотрим insert.