Создание таблиц с помощью SQL

Итак, у нас появилось хранилище, в котором мы можем создавать таблицы. Мы можем перейти в него, вызвав команду

use League

чтобы выполнять команды внутри базы данных.

Как мы уже знаем, таблицы состоят из колонок, и каждая колонка может иметь тип. Мы уже говорили о типах данных в общих словах – число или строка, а теперь давайте познакомимся с ними более конкретно. Я не буду называть все, только самые основные на мой взгляд:

char(x) – набор символов или другими словами строка длинной X символов. Причем это фиксированная строка. Если указать тип char(100) и в такую колонку поместить строку с одним символом, на диске будет все равно выделено 100, просто после нашего единственного символа добавят 99 пробелов. Этот тип можно использовать в тех случаях, когда мы точно уверены, что длинна данных фиксирован – индекс в почтовом адресе, пол, и т. д. Когда база данных знает. Четкий размер она может (но не обязательно реально делает это) эффективнее рассчитывать пространство.

varchar(x) – строка переменной длинны, но с максимумом в x символов. База будет выделять именно столько, сколько вы и попытались сохранить.

int – число. На диске поле такого типа будет занимать 4 байта и этого достаточно для хранения числа со знаком в пределах от -2,147,483,648 до 2,147,483,648.

int unsigned – если вы уверены, что вы будете хранить только положительные числа, то можно добавить к слову int еще unsigned (без знака) и возможное значение будет от 0 до в два раза больше, чем int.

bigint – если вам недостаточно размерности int, то bigint позволяет хранить 8 байт. Это примерно от -нефигасебекакмного до +нефигасебекакмного.

bool – логическое значение, которое может хранить 1 (правда) или 0 (обман или ложь) и на диске обычно занимает 1 байт. Не думаю, что какие-то базы данных делают это по другому, скорей всего все выделяют один байт.

decimal(x, y) – число с плавающей точкой и со знаком, где x – общее количество цифр, а y – это количество символов после запятой. Это значит, что decimal(10, 4) означает, что всего чисел может быть 10, из которых 4 после запятой, что соответствует формату XXXXXX.YYYY.

float и double – тоже числа с плавающей точкой, но мы не может указать точность, она фиксирована. Первое число занимает 4 байта, второе 8 байт.

DATE и TIME и DATETIME – три типа данных для хранения даты и времени. Первый для даты, второй для времени, третий – это и то и другое в одном флаконе.

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

CREATE TABLE имя (
   Имя_колонки1 тип,
   Имя_колонки2 тип
   . . . 
   . . . 
)

Итак, мы строим футбольную базу и нам понадобиться таблица, где будут храниться команды. Начнем с таблицы футболистов, потому что в ней наверно будут наиболее интересные данные.

Нам понадобится знать имя и фамилию игрока. Я уже говорил, что не стоит хранить делимые данные в одной колонке. Имя и фамилия вполне делимы и нужно создать две колонки – Firstname и Lastname. Обе эти колонки по своей природе явно должны хранить строки и могут быть разной длины. Явный кандидат тут varchar с указанием просто максимальной длины, я думаю 20 для каждой из них хватит.

CREATE TABLE player (
 firstname varchar(20),
 lastname varchar(20)
)

Если среди этих колонок какая-то, которая точно и уникально скажет нам, какого игрока мы имеем в виду? Тески по фамилии имени существуют, поэтому нам явно нужна колонка, которая будет гарантировать уникальность, и она будет играть роль первичного ключа. Чтобы не заморачиваться очень часто программисты создают какую-то колонку числового типа int, помечают ее как первичный ключ и делают ее автоматически увеличиваемой.

Имена колонок, которые обычно играют роль первичного ключа часто называют idИмя или имяId, бывает добавляют подчеркивание между id и именем, и я нормально отношусь к любому из этих вариантов. Главное, чтобы вы выбрали один и следовали ему всегда. В качестве имени обычно выступает имя таблицы. Нашу таблицу я назвал Player, а значит первичный ключ может быть playerid или idPlayer. Сегодня я выберу первый вариант:

CREATE TABLE player (
  playerid int,
  firstname varchar(20),
  lastname varchar(20)
)

Отлично, у нас теперь есть первая числовая колонка, но она еще не стала первичным ключом. Чтобы это произошло есть несколько вариантов – добавить после имени и типа колонки два магических слова primary key:

CREATE TABLE player (
  playerid int primary key,
  firstname varchar(20),
  lastname varchar(20)
)

Второй вариант работает не во всех базах данных, но все же работает:

CREATE TABLE player (
  playerid int,
  firstname varchar(20),
  lastname varchar(20),
  PRIMARY KEY (playerid)
)

Второй вариант не сильно отличается от первого и если честно, я им не пользуюсь, потому что иметь идентификатор после имени колонки мне удобнее. Но второй вариант можно расширить и указать имя первичного ключа в формате:

CONSTRAINT имя PRIMARY KEY (колонка)

В нашем случае результат будет выглядеть так:

CREATE TABLE player (
  playerid int,
  firstname varchar(20),
  lastname varchar(20),
  CONSTRAINT pk_playerid PRIMARY KEY (playerid)
)

Первичный ключ – приводит к тому, что в базе создается что-то типа объекта, который имеет свое имя и в этом объекте сохраняется информация об ограничении. В первые два раза я не указывал имени, поэтому базы данных могут работать на свое усмотрение, и кто-то может выбрать что-то простое. Это просто эстетический вопрос – давать имена или нет. Я в дальнейшем не буду только для того, чтобы сэкономить время.

Последний штрих, который мы должны сделать – сделать первичный ключ автоматически увеличиваемым, для этого после имени колонки playerid нужно где-нибудь добавить слово auto_increment:

CREATE TABLE player (
  playerid int auto_increment primary key,
  firstname varchar(20),
  lastname varchar(20)
)

Не имеет значения, в каком месте вы его укажите, я сделал это сразу же после типа данных, можно это сделать после указания primary key.

Создайте такую таблицу и давайте попробуем вставить в нее данные.

insert into Player (firstname) values('Ronaldo');

В зависимости от базы данных этот запрос может завершиться удачно и неудачно. Допустим, что у вас он закончился неудачно, тогда мы можем посмотреть на результат. Получаем данные:

Select * from player;
+----------+-----------+----------+
| playerid | firstname | lastname |
+----------+-----------+----------+
|        1 | Ronaldo   | NULL     |
+----------+-----------+----------+

Если запрос на вставку данных завершился неудачно, какая может быть причина? Дело в том, что мы не предоставили данных для фамилии, а когда мы не предоставляем данные, то сервер не вставит в это поле значения и там будет NULL. Но мы не сказали, а можно вообще вставлять NULL! Это настраиваемо для каждого поля отдельно. Может ли отсутствовать фамилия? Я даже не знаю такого. Бывает, что игроки играют чисто под псевдонимом, но кажется фамилия есть всегда, поэтому теоретически мы можем сказать, что фамилия не должна быть нулевой.

После имени поля мы можем указать разрешены NULL или нет разрешены NOT NULL. В следующем примере я указал, что фамилия и имя не могут быть нулевыми, а имя может быть:

CREATE TABLE player (
  playerid int auto_increment primary key,
  firstname varchar(20) NOT NULL,
  lastname varchar(20) NOT NULL,
  middlename varchar(20) NULL
)

У нас уже таблица существует и еще раз мы ее создать не можем, поэтому чтобы проверить этот запрос нужно сначала удалить существующую таблицу с помощью

DROP TABLE player;

Кстати, по умолчанию таблица и строковые поля используют набор символов и кодировку как у базы данных. Если вы хотите изменить это, то после скобок CREATE TABLE можно добавить настройки символов точно так же, как мы делали это для базы:

CREATE TABLE player (
  playerid int auto_increment primary key,
  firstname varchar(20),
  lastname varchar(20),
  middlename varchar(20) NULL
) CHARACTER SET набор_символов COLLATE имя

Кодировку можно даже указать для отдельной колонки:

CREATE TABLE player (
  playerid int auto_increment primary key,
  firstname varchar(20) CHARACTER SET набор_символов COLLATE имя,
  lastname varchar(20),
  middlename varchar(20) NULL
) 

В MySQL есть очень удобная команда, которая позволяет вывести структуру таблицы:

DESCRIBE имя таблицы

Чтобы посмотреть, как выглядит сейчас наша таблица Player, мы должны выполнить команду:

DESCRIBE player

Результат будет таким:

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| playerid  | int(11)     | NO   | PRI | NULL    | auto_increment |
| firstname | varchar(20) | YES  |     | NULL    |                |
| lastname  | varchar(20) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

Иногда бывают такие поля, которые могут потребовать уникальности и база данных может помочь нам гарантировать, что мы никогда не вставим две строки с одним и тем же значением в колонке, которая гарантирует уникальность.

Как мы уже говорили – первичный ключ обеспечивает уникальность и его делать уникальным дополнительно ненужно. Попробуйте выполнить команду дважды:

insert into Player (playerid, firstname) values(2, 'Andre');

Если первый раз она выполниться успешно, то во второй раз произойдет ошибка:

ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

Второй раз цифру 2 в колонку playerid вставить не получиться.

Если есть какие-то другие колонки, которые не могут содержать повторяющих значений, то вы можете сами пытаться делать какие-то обходные пути и перед вставкой проверять – есть ли уже в базе значение. А можно возложить это на базу данных.

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

CREATE TABLE phone (
   phoneid int auto_increment primary key,
   firstname varchar(20),
   lastname varchar(20),
   phonenumber varchar(20) NULL unique
) 

Вы не сможете создать две записи с одним и тем же номером телефона. Попробуйте выполнить следующий запрос дважды:

insert into phone (firstname, phonenumber) values ('Misha', '123123');

Первый раз все будет отлично, а на второй произойдет ошибка:

ERROR 1062 (23000): Duplicate entry '123123' for key 'phonenumber'

Номер телефона уже существует и дубликат запрещен.

Уничтожим таблицу, потому что телефоны нам не нужны.

DROP TABLE phone;

При создании колонок можно сделать какую-то по умолчанию. Например, если справочник телефонов содержит колонку для хранения страны, то можно сделать так, чтобы она сохраняла по умолчанию 'RU':

CREATE TABLE phone (
   phoneid int auto_increment primary key,
   firstname varchar(20),
   lastname varchar(20),
   phonenumber varchar(20) NULL unique,
   country char(3) DEFAULT 'RU'
) 

Для хранения страны я выбрал три символа, потому что кажется тремя символами можно покрыть сокращения большинства стран, если не всех.

Теперь попробуем вставить какой-нибудь номер телефона:

insert into phone (firstname, phonenumber) 
values ('Misha', '123123');   

Я не указывал страну, поэтому будет выбрано значение по умолчанию RU:

select * from phone;
+---------+-----------+----------+-------------+---------+
| phoneid | firstname | lastname | phonenumber | country |
+---------+-----------+----------+-------------+---------+
|       1 | Misha     | NULL     | 123123      | RU      |
+---------+-----------+----------+-------------+---------+

Если же указать страну, то будет сохранено именно то значение, которое вы передали:

insert into phone (firstname, phonenumber, country) 
values ('Mike', '1492823', 'UA');

Проверяем:

select * from phone;
+---------+-----------+----------+-------------+---------+
| phoneid | firstname | lastname | phonenumber | country |
+---------+-----------+----------+-------------+---------+
|       1 | Misha     | NULL     | 123123      | RU      |
|       2 | Mike      | NULL     | 1492823     | UA      |
+---------+-----------+----------+-------------+---------+