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

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

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

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

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

CREATE TEMPORARY  TABLE IF NOT EXISTS table_name
( 
column_name_1 data_type_1,
column_name_2 data_type_2, 
...,
column_name_N data_type_N,
) ;

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

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

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

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

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

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

CREATE TEMPORARY  TABLE IF NOT EXISTS table_name
( 
column_name_1 data_type_1,
column_name_2 data_type_2, 
...,
column_name_N data_type_N,
) 
IGNORE | REPLACE
AS query_expression;

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

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

SELECT id, column_1 FROM table WHERE id > 15

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

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

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

CREATE TABLE movies_copy 
(id INT, title CHAR(50) UNIQUE, year YEAR, summary TEXT) 
IGNORE 
SELECT id, title, year, storyline as summary FROM movies;

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

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
LIKE old_tbl_name;

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

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

dbaas

Типы данных в 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

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

CREATE TABLE movies (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
title CHAR(100) NOT NULL,
year YEAR NOT NULL,
running_time_min INT NOT NULL,
storyline TEXT
);

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

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

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

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

INSERT movies3(title, year, running_time_min, storyline) 
VALUES ('Harry Potter and the Philosophers Stone', 2001, 152, "An orphaned boy enrolls in a school of wizardry, where he learns the truth about himself, his family and the terrible evil that haunts the magical world."),
('Harry Potter and the Chamber of Secrets', 2002, 162,"An ancient prophecy seems to be coming true when a mysterious presence begins stalking the corridors of a school of magic and leaving its victims paralyzed."),
('The Green Mile', 1999, 188,'Death Row guards at a penitentiary, in the 1930s, have a moral dilemma with their job when they discover one of their prisoners, a convicted murderer, has a special gift.'),
('Forrest Gump', 1994, 142,"The presidencies of Kennedy and Johnson, the Vietnam War, the Watergate scandal and other historical events unfold from the perspective of an Alabama man with an IQ of 75, whose only desire is to be reunited with his childhood sweetheart."),
('Cast Away', 2000, 143,"A FedEx executive undergoes a physical and emotional transformation after crash landing on a deserted island.");

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

Пример 2

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

CREATE TABLE movies_query 
AS
SELECT id, title, year, running_time_min 
FROM movies 
WHERE year > 1999;

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

Пример 3

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

CREATE TABLE movies_copy 
LIKE movies;

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

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

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

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

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

CONSTRAINT symbol FOREIGN KEY
index_name (col_name, ...)
REFERENCES tbl_name (col_name,...)
ON DELETE reference_option
ON UPDATE reference_option

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

• 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

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

CREATE TABLE genres (
id INT AUTO_INCREMENT PRIMARY KEY,
genre VARCHAR(200) UNIQUE NOT NULL
);

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

Заполним ее:

INSERT genres(genre) 
VALUES ('drama'),
 ('fantasy'),
 ('sci-fi'),
 ('cartoon');

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

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

CREATE TABLE movies2 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
title CHAR(100) NOT NULL,
year YEAR NOT NULL,
running_time_min INT NOT NULL,
genre_id INT,
FOREIGN KEY (genre_id) REFERENCES genres (id)
);

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

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

SELECT movies2.id, title, year, genre from movies2 
LEFT JOIN genres on genres.id = genre_id;

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

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

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

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

USE movies_db;
RENAME TABLE movies2 TO cinema;

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

RENAME TABLE movies_db.movies2 TO movies_db.cinema;

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

RENAME TABLE movies_db.movies2 TO cinema_db.cinema;

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

ALTER TABLE cinema
ADD Language VARCHAR(50) NULL;

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

ALTER TABLE cinema
DROP COLUMN Language;

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

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

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

ALTER TABLE cinema CHANGE year date INT NOT NULL;

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

ALTER TABLE cinema CHANGE year year INT NOT NULL;

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

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

ALTER TABLE cinema
MODIFY COLUMN title VARCHAR(100;

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

ALTER TABLE cinema RENAME COLUMN running_time_min TO running_time;

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

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

TRUNCATE TABLE cinema;

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

DROP TABLE cinema;

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

ALTER TABLE cinema
ADD FOREIGN KEY(producer_id) REFERENCES producer(Id);

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

Заключение

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