Просмотр исходного текста страницы СУБД/Практические занятия №1(2 семестр)

==SQL. Создание таблиц и наполнение их информацией==
{{Карандаш|24px}} [http://www.site-do.ru/db/db.php#2 Информация с сайта]
* Создадим таблицы для форума. Сначала разберем их. И начнем с таблицы 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.
<source lang="sql">id_user int (10) NOT NULL
 name varchar(20) NOT NULL
 email varchar(50) NOT NULL
 password varchar(15) NOT NULL</source>

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

<source lang="sql">id_user int (10) AUTO_INCREMENT
 name varchar(20) NOT NULL
 email varchar(50) NOT NULL
 password varchar(15) NOT NULL</source>

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

<source lang="sql">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)</source>

* {{V|24px}}Итак, таблица готова, и ее окончательный вариант выглядит так:
<source lang="sql">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)
 );</source> 

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

<source lang="sql">id_topic int (10) AUTO_INCREMENT
 topic_name varchar(100) NOT NULL
 id_author int (10) NOT NULL
 PRIMARY KEY (id_topic)</source>

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

<source lang="sql">FOREIGN KEY (имя_столбца_которое_является_внешним_ключом) REFERENCES имя_таблицы_родителя (имя_столбца_родителя);</source> 
* Укажем, что id_author - внешний ключ: 

<source lang="sql">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)</source>

* {{V|24px}}Таблица готова, и ее окончательный вариант выглядит так:

<source lang="sql">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)
 );</source> 

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

<source lang="sql">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)
 );</source> 

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

{{ЖЛампа|24px}}Помните, если вы сделали что-то не так, всегда можно удалить таблицу или всю БД с помощью оператора DROP. 
* Итак, таблицы созданы, чтобы убедиться в этом вспомним о команде show tables:

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

<source lang="sql">married enum ('да', 'нет') NOT NULL default('да')</source> 

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

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

Для этого используется оператор INSERT. Синтаксис можно использовать двух видов. Первый вариант используется для внесения данных во все поля таблицы:
<source lang="sql">INSERT INTO имя_таблицы VALUES ('значение_первого_столбца','значение_второго_столбца', ..., 'значение_последнего_столбца');</source> 
Давайте попробуем внести в нашу таблицу users следующие значения:
<source lang="sql">INSERT INTO users VALUES ('1','sergey', 'sergey@mail.ru', '1111');</source> 

Второй вариант используется для внесения данных в некоторые поля таблицы:
<source lang="sql">INSERT INTO имя_таблицы ('имя_столбца', 'имя_столбца') VALUES ('значение_первого_столбца','значение_второго_столбца');</source> 

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

<source lang="sql">INSERT INTO users (name, email, password) VALUES ('valera', 'valera@mail.ru', '2222');</source> 

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

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

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

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

*{{X|24px}} Сервер выдает ошибку и говорит, что не может внести такую строку, т.к. в поле, являющемся внешним ключом, стоит значение, отсутствующее в связанной таблице users.

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

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