Как составить таблицу mysql

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

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

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 — имя таблицы, которую мы хотим «клонировать». 

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

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

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

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

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

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

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

Для вещественных чисел применяется 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, развернутыми в облаке cloud.timeweb.com.

    CREATE TABLE table_name 
(
    column_name_1 column_type_1,
    column_name_2 column_type_2,
    ...,
    column_name_N column_type_N,
);

    CREATE TABLE Staff 
(
    id INT,
    name VARCHAR(255) NOT NULL,
    position VARCHAR(30),
    birthday Date
);

    CREATE TABLE Staff

    CREATE TABLE IF NOT EXISTS Staff

    1050 Table 'Staff' already exists

    DROP TABLE table_name;

    CREATE TABLE Staff (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    position VARCHAR(30),
    birthday Date,
    has_children BOOLEAN
);

    CREATE TABLE Staff (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    position VARCHAR(30),
    birthday DATE NOT NULL,
    has_children BOOLEAN NOT NULL
);

    CREATE TABLE Staff (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    position VARCHAR(30),
    birthday DATE NOT NULL,
    has_children BOOLEAN DEFAULT(FALSE) NOT NULL
);

    CREATE TABLE Staff (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    position VARCHAR(30),
    birthday DATE NOT NULL,
    has_children BOOLEAN DEFAULT(FALSE) NOT NULL
);

    ALTER TABLE Staff AUTO_INCREMENT=10001;

    CREATE TABLE Staff (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    position VARCHAR(30),
    birthday DATE NOT NULL,
    has_child BOOLEAN DEFAULT(0) NOT NULL,
    phone VARCHAR(20) UNIQUE NOT NULL
);

    birthday DATE NOT NULL CHECK (birthday > ‘1900-01-01’),

    CHECK (birthday > ‘1900-01-01’),

    CREATE TABLE Staff (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    position VARCHAR(30),
    birthday DATE NOT NULL,
    has_child BOOLEAN DEFAULT(0) NOT NULL,
    phone VARCHAR(20) UNIQUE NOT NULL,
    CONSTRAINT staff_chk_birthday CHECK (birthday > '1900-01-01'),
    CONSTRAINT staff_chk_phone CHECK (phone REGEXP '[+]?[0-9]{1,3} ?\(?[0-9]{3}\)? ?[0-9]{2}[0-9 -]+[0-9]{2}')
);

    FOREIGN KEY (column_name1, column_name2) 
REFERENCES external_table_name(external_column_name1, external_column_name2)

    CREATE TABLE Positions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

    CREATE TABLE Staff (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    position_id int,
    birthday DATE NOT NULL,
    has_child BOOLEAN DEFAULT(0) NOT NULL,
    phone VARCHAR(20) UNIQUE NOT NULL,
    FOREIGN KEY (position_id) REFERENCES Positions (id)
);

    ALTER TABLE Staff
ADD FOREIGN KEY (position_id) REFERENCES Positions(id);

    ALTER TABLE Staff
ADD CONSTRAINT fk_position_id FOREIGN KEY (position_id) REFERENCES Positions(id);

    CREATE TABLE new_table LIKE source_table;

    CREATE TABLE new_table [AS] SELECT * FROM source_table;

    CREATE TABLE StaffData211015
SELECT s.Id,
       s.name as staff,
       p.name as position
FROM Staff s
JOIN Positions p ON s.position_id = p.id

    CREATE TEMPORARY TABLE table_name;

    RENAME TABLE old_table_name TO new_table_name;

    ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

    ALTER TABLE Staff
ADD email VARCHAR(50),
ADD age INT,
ADD has_auto BOOLEAN;

    ALTER TABLE Staff
DROP COLUMN age;

    ALTER TABLE Staff
ALTER COLUMN has_auto SET DEFAULT(FALSE);

    ALTER TABLE Staff
MODIFY COLUMN name VARCHAR(500) NOT NULL;

    ALTER TABLE Staff
ADD CONSTRAINT staff_chk_email CHECK (email REGEXP '^[^@]+@[^@]+\.[^@]{2,}$');
 

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

# Table creation with Primary Key

A primary key is a NOT NULL single or a multi-column identifier which uniquely identifies a row of a table. An index (opens new window) is created, and if not explicitly declared as NOT NULL, MySQL will declare them so silently and implicitly.

A table can have only one PRIMARY KEY, and each table is recommended to have one. InnoDB will automatically create one in its absence, (as seen in MySQL documentation (opens new window)) though this is less desirable.

Often, an AUTO_INCREMENT INT also known as «surrogate key», is used for thin index optimization and relations with other tables. This value will (normally) increase by 1 whenever a new record is added, starting from a default value of 1.

However, despite its name, it is not its purpose to guarantee that values are incremental, merely that they are sequential and unique.

An auto-increment INT value will not reset to its default start value if all rows in the table are deleted, unless the table is truncated using TRUNCATE TABLE (opens new window) statement.

# Defining one column as Primary Key (inline definition)

If the primary key consists of a single column, the PRIMARY KEY clause can be placed inline with the column definition:

This form of the command is shorter and easier to read.

# Defining a multiple-column Primary Key

It is also possible to define a primary key comprising more than one column. This might be done e.g. on the child table of a foreign-key relationship. A multi-column primary key is defined by listing the participating columns in a separate PRIMARY KEY clause. Inline syntax is not permitted here, as only one column may be declared PRIMARY KEY inline. For example:

Note that the columns of the primary key should be specified in logical sort order, which may be different from the order in which the columns were defined, as in the example above.

Larger indexes require more disk space, memory, and I/O. Therefore keys should be as small as possible (especially regarding composed keys). In InnoDB, every ‘secondary index’ includes a copy of the columns of the PRIMARY KEY.

# Basic table creation

The CREATE TABLE statement is used to create a table in a MySQL database.

Every field definition must have:

1. Field name: A valid field Name. Make sure to encolse the names in `-chars. This ensures that you can use eg space-chars in the fieldname.
2. Data type [Length]: If the field is CHAR or VARCHAR, it is mandatory to specify a field length.
3. Attributes NULL | NOT NULL: If NOT NULL is specified, then any attempt to store a NULL value in that field will fail.
4. See more on data types and their attributes here (opens new window).

Engine=... is an optional parameter used to specify the table’s storage engine.
If no storage engine is specified, the table will be created using the server’s default table storage engine (usually InnoDB or MyISAM).

# Setting defaults

Additionally, where it makes sense you can set a default value for each field by using DEFAULT:

If during inserts no Street is specified, that field will be NULL when retrieved. When no Country is specified upon insert, it will default to «United States».

You can set default values for all column types, except (opens new window) for BLOB, TEXT, GEOMETRY, and JSON fields.

# Table creation with Foreign Key

Foreign key: A Foreign Key (FK) is either a single column, or multi-column composite of columns, in a referencing table. This FK is confirmed to exist in the referenced table. It is highly recommended that the referenced table key confirming the FK be a Primary Key, but that is not enforced. It is used as a fast-lookup into the referenced where it does not need to be unique, and in fact can be a left-most index there.

Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table. The parent and child tables must use the same storage engine. They must not be TEMPORARY (opens new window) tables.

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

Note: foreign-key constraints are supported under the InnoDB storage engine (not MyISAM or MEMORY). DB set-ups using other engines will accept this CREATE TABLE statement but will not respect foreign-key constraints. (Although newer MySQL versions default to InnoDB, but it is good practice to be explicit.)

# Show Table Structure

If you want to see the schema information of your table, you can use one of the following:

If used from the mysql commandline tool, this is less verbose:

A less descriptive way of showing the table structure:

Both DESCRIBE and DESC gives the same result.

To see DESCRIBE performed on all tables in a database at once, see this Example (opens new window).

# Cloning an existing table

A table can be replicated as follows:

The new table will have exactly the same structure as the original table, including indexes and column attributes.

As well as manually creating a table, it is also possible to create table by selecting data from another table:

You can use any of the normal features of a SELECT statement to modify the data as you go:

Primary keys and indexes will not be preserved when creating tables from SELECT. You must redeclare them:

# CREATE TABLE FROM SELECT

You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:

Create a table in the same database:

Create tables from different databases:

N.B

To create a table same of another table that exist in another database, you need to specifies the name of the database like this:

# Table Create With TimeStamp Column To Show Last Update

The TIMESTAMP column will show when the row was last updated.

# Syntax

The CREATE TABLE statement should end with an ENGINE specification:

Some options are:

• InnoDB: (Default since version 5.5.5) It’s a transation-safe (ACID compliant) engine. It has transaction commit and roll-back, and crash-recovery capabilities and row-level locking.
• MyISAM: (Default before version 5.5.5) It’s a plain-fast engine. It doesn’t support transactions, nor foreign keys, but it’s useful for data-warehousing.
• Memory: Stores all data in RAM for extremely fast operations but table date will be lost on database restart.

More engine options here (opens new window).