alan_beaulieu-learning_sql-ru (865932), страница 8
Текст из файла (страница 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.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
