СУБД/Практические занятия №1(2 семестр)

Ссылки сюда (1) →

SQL. Создание таблиц и наполнение их информацией

Информация с сайта

• Создадим таблицы для форума. Сначала разберем их. И начнем с таблицы users (пользователи). В ней у нас 4 столбца:

id_user - целочисленные значения, значит будет тип int, ограничим его 10 символами - int (10).
name - строковое значение varchar, ограничим его 20 символами - varchar(20).
email - строковое значение varchar, ограничим его 50 символами - varchar(50).
password - строковое значение varchar, ограничим его 15 символами - varchar(15).

Все значения полей обязательны для заполнения, значит надо добавить тип NOT NULL.

id_user INT (10) NOT NULL
 name VARCHAR(20) NOT NULL
 email VARCHAR(50) NOT NULL
 password VARCHAR(15) NOT NULL

• Первый столбец, как вы помните из концептуальной модели нашей БД, является первичным ключом (т.е. его значения уникальны, и они однозначно идентифицируют запись). Следить за уникальностью самостоятельно можно, но не рационально. Для этого в SQL есть специальный атрибут - AUTO_INCREMENT, который при обращении к таблице на добавление данных высчитывает максимальное значение этого столбца, полученное значение увеличивает на 1 и заносит его в столбец. Таким образом, в этом столбце автоматически генерируется уникальный номер, а следовательно тип NOT NULL излишен. Итак, присвоим атрибут столбцу с первичным ключом:

id_user INT (10) AUTO_INCREMENT
 name VARCHAR(20) NOT NULL
 email VARCHAR(50) NOT NULL
 password VARCHAR(15) NOT NULL

• Теперь надо указать, что поле id_user является первичным ключом. Для этого в SQL используется ключевое слово PRIMARY KEY (), в скобочках указывается имя ключевого поля. Внесем изменения:

id_user INT (10) AUTO_INCREMENT
 name VARCHAR(20) NOT NULL
 email VARCHAR(50) NOT NULL
 password VARCHAR(15) NOT NULL
 PRIMARY KEY (id_user)

• Итак, таблица готова, и ее окончательный вариант выглядит так:

CREATE TABLE users (
 id_user INT (10) AUTO_INCREMENT,
 name VARCHAR(20) NOT NULL,
 email VARCHAR(50) NOT NULL,
 password VARCHAR(15) NOT NULL,
 PRIMARY KEY (id_user)
 );

• Теперь разберемся со второй таблицей - topics (темы). Рассуждая аналогично, имеем следующие поля:

id_topic INT (10) AUTO_INCREMENT
 topic_name VARCHAR(100) NOT NULL
 id_author INT (10) NOT NULL
 PRIMARY KEY (id_topic)

Но в модели нашей БД поле id_author является внешним ключом, т.е. оно может иметь только те значения, которые есть в поле id_user таблицы users. Для того, чтобы указать это в SQL есть ключевое слово FOREIGN KEY (), которое имеет следующий синтаксис:

FOREIGN KEY (имя_столбца_которое_является_внешним_ключом) REFERENCES имя_таблицы_родителя (имя_столбца_родителя);

• Укажем, что id_author - внешний ключ:

id_topic INT (10) AUTO_INCREMENT
 topic_name VARCHAR(100) NOT NULL
 id_author INT (10) NOT NULL
 PRIMARY KEY (id_topic)
 FOREIGN KEY (id_author) REFERENCES users (id_user)

• Таблица готова, и ее окончательный вариант выглядит так:

CREATE TABLE topics (
 id_topic INT (10) AUTO_INCREMENT,
 topic_name VARCHAR(100) NOT NULL,
 id_author INT (10) NOT NULL,
 PRIMARY KEY (id_topic),
 FOREIGN KEY (id_author) REFERENCES users (id_user)
 );

• Осталась последняя таблица - posts (сообщения). Здесь все аналогично, только два внешних ключа:

CREATE TABLE posts (
 id_post INT (10) AUTO_INCREMENT,
 message text NOT NULL,
 id_author INT (10) NOT NULL,
 id_topic INT (10) NOT NULL,
 PRIMARY KEY (id_post),
 FOREIGN KEY (id_author) REFERENCES users (id_user),
 FOREIGN KEY (id_topic) REFERENCES topics (id_topic)
 );

• Обратите внимание, внешних ключей у таблицы может быть несколько, а первичный ключ в MySQL может быть только один.
• Обратите внимание, одну команду можно писать в несколько строк, и только после разделителя (точки с запятой) происходит к выполнение запроса.

Помните, если вы сделали что-то не так, всегда можно удалить таблицу или всю БД с помощью оператора DROP.

• Итак, таблицы созданы, чтобы убедиться в этом вспомним о команде show tables:

Теперь становятся понятны значения всех полей структуры, кроме поля DEFAULT. Это поле значений по умолчанию. Мы могли бы для какого-нибудь столбца (или для всех) указать значение по умолчанию. Например, если бы у нас было поле с названием "Женаты\Замужем" и типом ENUM ('да', 'нет'), то было бы разумно сделать одно из значений значением по умолчанию. Синтаксис был бы следующий:

married enum ('да', 'нет') NOT NULL DEFAULT('да')

Т.е. это ключевое слово пишется через пробел после указания типа данных, а в скобках указывается значение по умолчанию.

Но вернемся к нашим таблицам. Теперь нам необходимо внести данные в наши таблицы. На сайтах, вы обычно вводите информацию в какие-нибудь html-формы, затем сценарий на каком-либо языке (php, java...) извлекает эти данные из формы и заносит их в БД. Делает он это посредством SQL-запроса на внесение данных в базу. Писать сценарии на php мы пока не умеем, а вот отправлять SQL-запросы на внесение данных умеем.

Для этого используется оператор INSERT. Синтаксис можно использовать двух видов. Первый вариант используется для внесения данных во все поля таблицы:

INSERT INTO имя_таблицы VALUES ('значение_первого_столбца','значение_второго_столбца', ..., 'значение_последнего_столбца');

Давайте попробуем внести в нашу таблицу users следующие значения:

INSERT INTO users VALUES ('1','sergey', 'sergey@mail.ru', '1111');

Второй вариант используется для внесения данных в некоторые поля таблицы:

INSERT INTO имя_таблицы ('имя_столбца', 'имя_столбца') VALUES ('значение_первого_столбца','значение_второго_столбца');

В нашей таблице users все поля обязательны для заполнения, но наше первое поле имеет ключевое слово - AUTO_INCREMENT (т.е. оно заполняется автоматически), поэтому мы можем пропустить этот столбец:

INSERT INTO users (name, email, password) VALUES ('valera', 'valera@mail.ru', '2222');

Если бы у нас были поля с типом NULL, т.е. необязательные для заполнения, мы бы тоже могли их проигнорировать. А вот если попытаться оставить пустым поле со значением NOT NULL, то сервер выдаст сообщение об ошибке и не выполнит запрос. Кроме того, при внесении данных сервер проверяет связи между таблицами. Поэтому вам не удастся внести в поле, являющееся внешним ключом, значение, отсутствующее в связанной таблице. В этом вы убедитесь, внося данные в оставшиеся две таблицы.

• Но прежде внесем информацию еще о нескольких пользователях. Чтобы добавить сразу несколько строк, надо просто перечислять скобки со значениями через запятую:

• Теперь внесем данные во вторую таблицу - topics (темы). Все тоже самое, но надо помнить, что значения в поле id_author должны присутствовать в таблице users (пользователи).

• Теперь давайте попробуем внести еще одну тему, но с id_author, которого в таблице users нет (т.к. мы внесли в таблицу users только 5 пользователей, то id=6 не существует).

• Сервер выдает ошибку и говорит, что не может внести такую строку, т.к. в поле, являющемся внешним ключом, стоит значение, отсутствующее в связанной таблице users.

• Теперь внесем несколько строк в таблицу posts (сообщения), помня, что в ней у нас 2 внешних ключа, т.е. id_author и id_topic, которые мы будем вносить должны присутствовать в связанных с ними таблицах.

• Итак, у нас есть 3 таблицы, в которых есть данные.