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

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

1 2 3 4 5 6 7 8 9 ... 31

Шаг 1: проектирование
Хорошо начать проектирование таблицы с небольшого мозгового штур ма – это позволит определить информацию, которую было бы полезно включить. Немного поразмыслив о данных, описывающих человека,
я получил вот что:
•
Имя, фамилия (name)
•
Пол (gender)
•
Дата рождения (birth date)
•
Адрес (address)

Создание таблиц
37
•
Любимое блюдо (favorite foods)
Разумеется, список не полный, но этого пока достаточно. Следующий шаг – дать столбцам имена и назначить типы данных. В табл. 2.6 по казан первый вариант.
Таблица 2.6. Таблица Person (человек), первое приближение
Столбцы 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.
Столбец
Тип
Допустимые значения
Name
Varchar(40)
Gender
Char(1)
M
, F
Birth_date
Date
Address
Varchar(100)
Favorite_foods
Varchar(200)

38
Глава 2. Создание и заполнение базы данных
Таблица 2.7. Таблица Person, второе приближение
Таблица 2.8. Таблица Favorite_food (любимое блюдо)
Шаг 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)
);
В этом выражении должно быть понятно все, кроме последнего эле мента. При описании таблицы необходимо сообщить серверу БД, ка кой столбец или столбцы будут играть роль первичного ключа табли
Столбец
Тип
Допустимые значения
Person_id
Smallint (unsigned)
First_name
Varchar(20)
Last_name
Varchar(20)
Gender
Char(1)
M
, F
Birth_date
Date
Street
Varchar(30)
City
Varchar(20)
State
Varchar(20)
Country
Varchar(20)
Postal_code
Varchar(20)
Столбец
Тип
Person_id
Smallint (unsigned)
Food
Varchar(20)

Создание таблиц
39
цы. Осуществляется это путем создания ограничения (constraint) для таблицы. В описание таблицы можно добавить ограничение одного из нескольких типов. Данное ограничение является ограничением пер
вичного ключа
(primary key 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,

Создание таблиц
41
Смысл столбцов 1 и 2 результата выполнения выражения describe оче виден. Столбец 3 показывает, можно ли пропустить тот или иной стол бец при вводе данных в таблицу. Я намеренно пока не включил эту тему в обсуждение (краткие рассуждения по этому вопросу можно найти во врезке «Что такое Null?»), она будет полностью рассмотрена в главе 4.
Четвертый столбец показывает, участвует ли столбец в формировании какого либо ключа (первичного или внешнего). В данном случае стол бец person_id отмечен как первичный ключ. Столбец 5 показывает, бу дет ли определенный столбец заполнен значением по умолчанию в слу чае, если он пропущен при вводе данных в таблицу. Для столбца per son_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, за не сколькими исключениями:
•
Поскольку у человека может быть несколько любимых блюд (что и стало причиной создания данной таблицы), одного столбца per son_id для обеспечения уникальности в таблице недостаточно. По этому первичный ключ данной таблицы состоит из двух столбцов:
person_id и food.
•
Таблица favorite_food содержит другой тип ограничения – ограниче
ние внешнего ключа
(foreign key 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. В выражении insert три основных компонента:
•
Имя таблицы, в которую должны быть добавлены данные.
•
Имена тех столбцов таблицы, которые должны быть заполнены.
•
Значения, которыми должны быть заполнены столбцы.
Таким образом, не обязательно предоставлять данные для всех столб цов таблицы (если только все столбцы таблицы не были определены как not null). В некоторых случаях столбцы, не включенные в исход ное выражение insert, будут заполнены позже с помощью выражений update
. Бывает, что столбец в какой то строке вообще никогда не за полняется данными (например, если заказ покупателя отменяется до поставки, столбец ship_date остается незаполненным).
Формирование числовых ключей
Прежде чем заполнить таблицу person данными, полезно обсудить про цесс формирования значений числовых первичных ключей. Кроме выбора числа «от фонаря» есть два варианта:
•
Найти в таблице самое большое на данный момент значение пер вичного ключа и прибавить 1.
•
Позволить серверу БД предоставить значение.
Хотя первый вариант и кажется допустимым, он становится проблема тичным в многопользовательской среде, поскольку два пользователя могут одновременно работать с таблицей и сгенерировать одно и то же значение первичного ключа. Напротив, все серверы БД, присутствую щие сегодня на рынке, обеспечивают более надежный, более устойчи

Заполнение и изменение таблиц
43
вый к ошибкам метод формирования числовых ключей. Иногда, напри мер в Oracle Database, используется отдельный объект схемы (называе мый последовательностью (sequence)). Однако в случае с MySQL надо просто включить для столбца первичного ключа свойство auto incre
ment
(автоприращение). Обычно это делается при создании таблицы, но мы занимаемся этим сейчас, чтобы изучить еще одно 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', '1972 05 27');
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 | 1972 05 27 |
+
+
+
+
+
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 | 1972 05 27 |
+
+
+
+
+
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 | 1972 05 27 |
+
+
+
+
+
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)

Заполнение и изменение таблиц
45
mysql> 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
>

1 2 3 4 5 6 7 8 9 ... 31