Как создавать таблицы в MySQL

Данные начинаются с создания таблиц. С реляционных таблиц, которые должны отвечать определённым правилам. В MySQL для создания таблиц используются специальные запросы, в которых указывается из каких атрибутов (полей) состоит таблица, какие типы данных в каждом поле, при необходимости делаются описания полей. В этой статье мы научим вас работать с таблицами в MySQL, разберем синтаксис CREATE TABLE, расскажем, как создавать таблицы и правильно вносить данные.

Создание таблиц в MySQL

Для создания таблиц используется три основных способа.  

1. Если таблица создается с нуля, самыми первыми командами необходимо описать все поля. Для этого в MySQL используется инструкция CREATE TABLE со следующим синтаксисом: 

Обязательные параметры:

• table_name — имя создаваемой таблицы (должно быть уникальным, и для удобства название должно описывать, что из себя представляет таблица);
• column_name_1 — имя столбца;
• data_type_1 — определяет тип данных, которые будут храниться в столбце.

Необязательные параметры: 

TEMPORARY — если это слово применяется в инструкции, то создается временная таблица, которая существует только в текущем сеансе и исчезает при переподключении к базе данных. Временные таблицы обычно используются для хранения промежуточных данных при сложных выборках. Доступ к временной таблице имеет только пользователь, который ее создал.

IF NOT EXISTS — команда для того, чтобы не выводилась ошибка, если таблица с таким именем уже существует. Проверка того, соответствует ли структура существующей таблицы той, которую мы пытались создать, не проводится, проверяет только имя.

2. Второй способ создания таблицы — на основе запроса: 

Обязательные параметры:

query_expression — запрос SQL, на основе результатов которого создается таблица. Например, запрос может выглядеть следующим образом:

Необязательный параметр: 

IGNORE | REPLACE — указывает, как поступать со строками, которые дублируют уникальные значения ключа.

Полная инструкция запроса может выглядеть так:

3. Третий способ — когда новая таблица создаётся на основе структуры другой таблицы. Можно сказать, что мы копируем другую таблицу:

old_tbl_name — имя таблицы, которую мы хотим «клонировать». 

Этот метод создает таблицу с такой же структурой, как у исходной, но он не копирует данные, которые в ней хранятся. Мы получаем такую же таблицу, но пустую.

Типы данных в MySQL

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

Числовые типы данных

Числовые типы делятся на целочисленные и вещественные. 

Для хранения целых чисел используются типы данных TINYINT, SMALLINT, MEDIUMINT, INT или BIGINT. Каждый из них позволяет хранить значения в диапазоне от -2(N-1)  до 2(N-1)-1, где N – это количество битов, которое требуется для хранения. При необходимости есть возможность, используя атрибут UNSIGNED, запретить отрицательные значения. В этом случае можно будет перенести диапазон значений так, чтобы он начинался от 0, например, от 0 до 255 для TINYINT. Самым распространенным типом данных является INT.  

Таблица байтов для целочисленных типов:

Тип	Объем, требующийся для хранения (байты)	Минимальное значение (со знаком)	Минимальное значение (без знака)	Максимальное значение (со знаком)	Максимальное значение (без знака)
TINYINT	1	-128	0	127	255
SMALLINT	2	-32768	0	32767	65535
MEDIUMINT	3	-8388608	0	8388607	16777215
INT	4	-2147483648	0	2147483647	4294967295
BIGINT	8	-263	0	263-1	264-1

Для вещественных чисел применяется 3 типа данных:

• FLOAT (использует 4 байта) — хранит до 24 знаков после запятой;
• DOUBLE (использует 8 байт) — хранит до 54 знаков после запятой;
• DECIMAL (число байт зависит от выбранной точности).

Тип данных DECIMAL используется для хранения точных дробных чисел. Например, при хранении финансовых данных, когда нам нужны точные результаты при расчетах. Также можно использовать синонимы (псевдонимы) NUMERIC, DEC, FIXED. Для DECIMAL можно выбрать, какое количество значений мы хотим видеть до и после запятой. От этого и зависит, сколько он будет использовать байт. DECIMAL может принимать два параметра DECIMAL(precision, scale). В параметре precision указывается максимальное количество цифр, которые может хранить число. Это значение должно находиться в диапазоне от 1 до 65. В параметре scale указывается максимальное количество цифр, которые может содержать число после запятой. Это значение должно находиться в диапазоне от 0 до значения параметра precision. По умолчанию оно равно 0. Например, при параметрах DECIMAL(5,2) мы сможем хранить число 69839.12 или число 71468.2.

Символьные (строковые) типы данных 

• CHAR имеет фиксированную длину до 255 символов. Полезен, когда необходимо сохранить короткие строки или, например, когда все данные примерно одинаковой длины. Так как длина фиксирована, то и объем выделяемого места тоже фиксирован. Если при создании таблицы указан CHAR(10) (10 — это длина строки, которую будем хранить), то все строки будут уметь длину 10. Если мы вводим меньшее количество символов, то оставшееся место заполнится пробелами. Получится, что место в базе данных выделено под пустоту.
• VARCHAR хранит символьные строки динамической длины от 0 до 65535 символов, при условии, что версия MySQL выше чем 5.0.3. Удобен, когда мы не знаем, какой длины текст будем хранить. Использует ровно столько места, сколько необходимо под наши данные.
• TEXT предназначен для хранения большого объема символьных данных. Существует 4 типа TEXT: TINYTEXT, TEXT, MEDIUMTEXT и LONGTEXT. Они отличаются максимальной длиной строки, которую могут содержать. Это особый тип (из-за размеров), поэтому часто он хранится отдельно, по-другому сортируется и не индексируется по полной длине. Похожие особенности имеет семейство BLOB. Эти типы данных могут хранить информацию больших объемов в двоичном виде. Семейство BLOB очень похоже на TEXT. К семейству BLOB относятся TINYBLOB, BLOB, MEDIUMBLOB и LONGBLOB.
• ENUM хранит какой-то предопределенный набор строковых значений, который задается при создании столбца. Подходит, если в каком-то поле часто повторяются определенные значения, которых должно быть немного и список с которыми не приходится часто дополнять. Например, тип кузова автомобиля или планеты солнечной системы — их ограниченное количество, и появляются новые нечасто.  Все эти значения хранятся в виде целых чисел и занимают меньше места, чем строковые. Занимает 1-2 байта.
• SET — это строковый объект, который может иметь 0 и более значений, каждое из которых выбрано из предопределённого списка, который задается при создании таблицы (максимальный размер — 64 элемента). Например, для хранения в столбце с типом SET жанра книги. SET хранит значения в виде целых чисел. Занимает 1-8 байт.

Типы данных для даты и времени

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

• DATE хранит только дату в формате 'YYYY-MM-DD', занимает 3 байта. 
• TIME хранит только время в формате 'hh:mm:ss', занимает 3 байта, диапазон значений от '-838:59:59.00' до '838:59:59.00'. 
• YEAR хранит год, диапазон значений 1901-2155, занимает 1 байт.

В случае, когда необходимо использовать и дату, и время понадобятся сразу 2 типа: DATETIME и TIMESTAP.

• DATETIME — занимает 8 байт. Позволяет хранить диапазон значений с 1001 по 9999 год с точностью до 1 секунды в формате: 'YYYY-MM-DD hh:mm:ss'. Не зависит от часового пояса.
• TIMESTAP — использует 4 байта, поэтому диапазон дат у него гораздо меньше: от '1970-01-01 00:00:01' до '2038-01-19 03:14:07'. Хранится в виде количества секунд, прошедших с начала эпохи Unix (1 января 1970 года по Гринвичу (GMT)). Отображаемое значение зависит от часового пояса. В более поздник версиях появилась поддержка хранения времени в микросекундах.

Ограничения в MySQL

Для обеспечения целостности базы данных в MySQL используются определенные ограничения (CONSTRAINT). Их можно разделить на два типа: ограничения уровня столбца и уровня таблицы (применяются либо к конкретному столбцу, либо ко всей таблице). Ограничения объявляются во время создания таблицы. К ним относят:

• NOT NULL — указывает, что столбец не может содержать значение NULL.
• UNIQUE — не позволяет вставлять повторяющиеся значения (все значения в столбце должны быть уникальны).
• PRIMARY KEY — в столбце могут храниться только уникальные не NULL значения. Такой столбец может быть только один в таблице.
• FOREIGN KEY — создает связь между двумя таблицами по конкретному столбцу. 
• CHEK — контролирует значения в столбце, проверяет допустимо оно или нет.
• DEFAULT — устанавливает значение по умолчанию для столбца. Если при внесении записи в таблицу это поле пропустили, то будет вставлено значение по умолчанию.

Типы индексов в MySQL 

Индекс — это структура, которая хранит в себе значение столбца таблицы (или нескольких) и ссылки на строки, где эти значения расположены. Создание индексов помогает увеличить эффективность работы MySQL, значительно повышая скорость запросов. Большая часть индексов имеет древовидную структуру данных (является B-tree индексом). Индекс занимает место в памяти, поэтому индексируются обычно только те поля, по которым происходит выборка данных. Когда индекса нет, при запросе поиск идет по всем записям таблицы, что может отнимать много времени и вычислительных мощностей. 

В MySQL индексы ускоряют операции:

• Поиск строк, соответствующих запросу WHERE; 
• Извлечение строк при выполнении объединений; 
• Для поиска минимальных MIN() и максимальных MAX() значений определенного индексированного столбца;
• Сортировки или группировки таблиц при условии, что операция выполняется по крайнему левому префиксу используемого индекса.

В MySQL могут быть следующие типы индексов: 

• Primary key (первичный ключ) Это столбец, который идентифицирует каждую строку в таблице однозначно. Обычно указывается при создании таблицы. Если этого не сделать самостоятельно, MySQL всё равно создаст скрытый ключ. Primary key содержит уникальные значения. Если он состоит из нескольких столбцов, то комбинация значений в них должна быть уникальной. В Primary key не может быть значений NULL. Таблица может иметь только один первичный ключ.
• Уникальный индекс. Обеспечивает уникальность значений в одном или нескольких столбцах. В отличии от первичного ключа можно сделать много уникальных индексов. Может иметь значение NULL.
• Составной индекс. Это индекс по нескольким столбцам. MySQL позволяет создавать составные индексы, содержащие до 16 столбцов. Обычно используется для ускорения запросов, в которых необходимо произвести выборку по нескольким полям.

Не рекомендуется использовать индексы для небольших таблиц. Улучшения от использования индексов не будет заметно. Создавать индексы следует в первую очередь для медленных запросов или для самых часто используемых. Для этого следует собрать статистику выполнения запросов и провести оценку. Создавать индексы для всего подряд не лучшая идея.

Итак, узнав основы теории создания таблиц в MySQL, перейдём к примерам.

Пример 1

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

В таблице создаются следующие столбцы:

• id — уникальный идентификатор фильма
• title — название фильма
• year — год выхода
• running_time_min — длительность фильма в минутах
• storyline — сюжетная линия, небольшое описание фильма

Все поля имеют ограничение NOT NULL. Первичный ключ PRIMARY KEY – поле id. 

Пока в таблице имеются только столбцы для которых заданы типы данных. Теперь внесём в таблицу сами данные. Для этого используются команды INSERT и VALUES. 

Получаем такую таблицу: 

Пример 2

Теперь создадим таблицу на основе запроса. Возьмём все фильмы, которые вышли после 1999 года:

Результат запроса:

Пример 3

Создадим таблицу на основе структуры другой таблицы: 

Таблица получится пустая. Структура такая же, как у таблицы movies, но данных в новой таблице нет. Результат:

Foreign keys (внешние ключи)

Когда в базе данных есть несколько таблиц, в какой-то момент нам понадобится их связать между собой. Для этого в MySQL используются внешние ключи. 

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

Создание ключа:

Обязательные параметры:

• FOREIGN KEY [index_name] (col_name, ...) — указывается, какое поле будет внешним ключом.
• index_name — имя индекса.
• col_name — имя столбца.
• REFERENCES tbl_name (col_name,...) — указание столбца родительской таблицы, с которым будет связан наш внешний ключ.
• tbl_name — имя таблицы.
• col_name — имя столбца.

Необязательные параметры:

CONSTRAINT symbol — используется для создания и удаления ограничений.

ON DELETE/ ON UPDATE — определяет, что делать при удалении или обновлении родительской таблицы. Здесь есть несколько опций:

• CASCADE — при удалении или обновлении записи в родительской таблице, в дочерней они автоматически тоже удаляются или обновляются.
• SET NULL — если в родительской таблице данные удаляются или обновляются, то в дочерней эти значения заменяются на NULL.
• RESTRICT — запрет удаления или обновления данных, если они использованы в дочерней таблице.
• NO ACTION — пришло из стандарта SQL. Работает аналогично RESTRICT.

Пример 4

Создадим таблицу с жанрами фильмов:

Оба столбца не должны содержать значения NULL, для столбца genre указано дополнительно ограничение UNIQUE, все значения должны быть уникальны.

Заполним ее:

Получим результат с id для каждого жанра.

А вот пример создания в MySQL новой таблицы с использованием FOREIGN KEY: 

Получаем такую табличку:

Чтоб получилось совсем красиво, можно с помощью LEFT JOIN и таблицы genres вывести не цифры, а сами жанры.

Манипуляции с таблицами

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

Переименование делается с помощью команды RENAME TABLE. Возможны разные особенности применения.

Вариант 1. Используется, если мы изначально указали в какой базе данных работаем:

Вариант 2. Применяется в случае, если не указали базу данных:

Также с помощью команды RENAME TABLE можно перенести таблицу из одной базы в другую:

Для добавления нового столбца нам понадобится команда ADD.

Чтобы удалить столбец в MySQL используется ALTER TABLE с DROP COLUMN:

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

• CHANGE — позволяет переименовать столбец и изменить его определение, или сделать и то, и другое.

     Изменим тип столбца year на INT и имя столбца на date:

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

• MODIFY — даёт возможность изменить определение столбца, но не его имя.

     Изменим тип столбца title на VARCHAR(100):

• RENAME COLUMN — изменяет имя столбца.

Операции с удалением 

Если надо очистить таблицу от данных используется команда TRUNCATE TABLE:  

С помощью DROP TABLE можно полностью удалить таблицу из базы данных:

Для добавления и удаления внешнего ключа воспользуемся уже знакомой нам командой ADD:

Разверните MySQL в облаке за минуту

Cloud DB 1/1/8

447 ₽/мес

Процессор

1 x 3.3 ГГц

Память

1 ГБ

Диск NVMe

8 ГБ

Приватный IP

Есть

Резервные копии

Есть

Заказать

Cloud DB 1/2/20

711 ₽/мес

Процессор

1 x 3.3 ГГц

Память

2 ГБ

Диск NVMe

20 ГБ

Приватный IP

Есть

Резервные копии

Есть

Заказать

Показать еще 6 тарифов

Таблица тарифов

Сравнение тарифов	Cloud DB 1/1/8 496	Cloud DB 1/2/20 790	Cloud DB 2/2/30 1160	Cloud DB 2/4/40 1580	Cloud DB 4/8/80 3160	Cloud DB 4/12/120 4240	Cloud DB 6/12/180 5460	Cloud DB 8/16/220 7040
Процессор	1 x 3.3 ГГц	1 x 3.3 ГГц	2 x 3.3 ГГц	2 x 3.3 ГГц	4 x 3.3 ГГц	4 x 3.3 ГГц	6 x 3.3 ГГц	8 x 3.3 ГГц
Память	1 ГБ	2 ГБ	2 ГБ	4 ГБ	8 ГБ	12 ГБ	12 ГБ	16 ГБ
Диск NVMe	8 ГБ	20 ГБ	30 ГБ	40 ГБ	80 ГБ	120 ГБ	180 ГБ	220 ГБ
Приватный IP	Есть	Есть	Есть	Есть	Есть	Есть	Есть	Есть
Резервные копии	Есть	Есть	Есть	Есть	Есть	Есть	Есть	Есть

Заключение

Приведённые операторы и примеры охватывают базовую работу с таблицами, но изучив эти основные команды MySQL, вы уже сможете делать много полезных вещей. Применять эти навыки можно, в том числе, для работы с базами MySQL, развернутыми в облаке timeweb.cloud.