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

Синтаксис

• CREATE TABLE table_name (column_name1 data_type (size), column_name2 data_type (size), column_name3 data_type (size), ....); // Создание основной таблицы

• CREATE TABLE имя_таблицы [IF NOT EXISTS] (column_name1 data_type (size), column_name2 data_type (size), column_name3 data_type (size), ....); // Проверка существующих таблиц

• CREATE [TEMPORARY] TABLE table_name [IF NOT EXISTS] (column_name1 data_type (size), column_name2 data_type (size), column_name3 data_type (size), ....); // Создание временной таблицы

• CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl; // Создание таблицы из SELECT

замечания

CREATE TABLE table_name ( column_definitions ) ENGINE=engine;

Создание основной таблицы

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

CREATE TABLE Person (
    `PersonID`      INTEGER NOT NULL PRIMARY KEY,
    `LastName`      VARCHAR(80),
    `FirstName`     VARCHAR(80),
    `Address`       TEXT,
    `City`          VARCHAR(100)
) Engine=InnoDB;

Каждое полевое определение должно иметь:

1. Имя поля: Действительное поле. Не забудьте зафиксировать имена в `-chars. Это гарантирует, что вы можете использовать, например, пробелы в поле имя.
2. Тип данных [Длина]: если поле CHAR или VARCHAR , обязательно указать длину поля.
3. Атрибуты NULL | NOT NULL : Если задано NOT NULL , любая попытка сохранить значение NULL в этом поле не будет выполнена.
4. Подробнее о типах данных и их атрибутах см . Здесь .

Engine=... - необязательный параметр, используемый для указания механизма хранения таблицы. Если механизм хранения не указан, таблица будет создана с использованием механизма хранения таблиц по умолчанию сервера (обычно InnoDB или MyISAM).

Установка значений по умолчанию

Кроме того, если это имеет смысл, вы можете установить значение по умолчанию для каждого поля, используя DEFAULT :

CREATE TABLE Address (
    `AddressID`   INTEGER NOT NULL PRIMARY KEY,
    `Street`      VARCHAR(80),
    `City`        VARCHAR(80),
    `Country`     VARCHAR(80) DEFAULT "United States",
    `Active`      BOOLEAN DEFAULT 1,
) Engine=InnoDB;

Если во вставках ни одна Street не указана, это поле будет NULL при извлечении. Если ни одна Country не указана при вставке, она будет по умолчанию «Соединенными Штатами».

Вы можете установить значения по умолчанию для всех типов столбцов, за исключением полей BLOB , TEXT , GEOMETRY и JSON .

Создание таблицы с помощью основного ключа

CREATE TABLE Person (
    PersonID     INT UNSIGNED NOT NULL,
    LastName     VARCHAR(66) NOT NULL,
    FirstName    VARCHAR(66),
    Address      VARCHAR(255),
    City         VARCHAR(66),
    PRIMARY KEY (PersonID)
);

Первичный ключ - это NOT NULL одиночный или многоколоночный идентификатор, который однозначно идентифицирует строку таблицы. Создается индекс , и если он явно не объявлен как NOT NULL , MySQL будет объявлять их так тихо и неявно.

В таблице может быть только один PRIMARY KEY , и каждая таблица рекомендуется иметь. InnoDB автоматически создаст его в своем отсутствии (как видно из документации MySQL ), хотя это менее желательно.

Часто AUTO_INCREMENT INT также известный как «суррогатный ключ», используется для оптимизации тонких индексов и отношений с другими таблицами. Это значение будет (обычно) увеличиваться на 1 при добавлении новой записи, начиная с значения по умолчанию 1.

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

Значение INT автоматическим инкрементом не будет сброшено до его начального значения по умолчанию, если все строки в таблице будут удалены, если таблица не будет усечена с помощью инструкции TRUNCATE TABLE .

Определение одного столбца в качестве основного ключа (встроенное определение)

Если первичный ключ состоит из одного столбца, предложение PRIMARY KEY может быть вставлено в строку с определением столбца:

CREATE TABLE Person (
    PersonID     INT UNSIGNED NOT NULL PRIMARY KEY,
    LastName     VARCHAR(66) NOT NULL,
    FirstName    VARCHAR(66),
    Address      VARCHAR(255),
    City         VARCHAR(66)
);

Эта форма команды короче и легче читать.

Определение первичного ключа с несколькими столбцами

Также возможно определить первичный ключ, содержащий более одного столбца. Это можно сделать, например, на дочерней таблице отношения внешнего ключа. Первичный ключ с несколькими столбцами определяется путем перечисления участвующих столбцов в отдельном предложении PRIMARY KEY . Внутренний синтаксис здесь не разрешен, так как только один столбец может быть объявлен как PRIMARY KEY . Например:

CREATE TABLE invoice_line_items (
    LineNum      SMALLINT UNSIGNED NOT NULL,
    InvoiceNum   INT UNSIGNED NOT NULL,
    -- Other columns go here
    PRIMARY KEY (InvoiceNum, LineNum),
    FOREIGN KEY (InvoiceNum) REFERENCES -- references to an attribute of a table
);

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

Большим индексам требуется больше дискового пространства, памяти и ввода-вывода. Поэтому ключи должны быть как можно меньше (особенно в отношении составленных ключей). В InnoDB каждый «вторичный индекс» включает в себя копию столбцов PRIMARY KEY .

Создание таблицы с помощью внешнего ключа

CREATE TABLE Account (
    AccountID     INT UNSIGNED NOT NULL,
    AccountNo     INT UNSIGNED NOT NULL,
    PersonID    INT UNSIGNED,
    PRIMARY KEY (AccountID),
    FOREIGN KEY (PersonID) REFERENCES Person (PersonID)
) ENGINE=InnoDB;

Внешний ключ: внешний ключ ( FK ) представляет собой либо столбцы, либо столбцы из нескольких столбцов в таблице ссылок . Этот FK подтверждается, что существует в таблице, на которую делается ссылка . Настоятельно рекомендуется, чтобы ссылочный ключ таблицы, подтверждающий, что FK является основным ключом, но не применяется. Он используется как быстрый поиск в ссылке, где он не должен быть уникальным, и на самом деле может быть самым левым индексом.

Внешние отношения ключей включают родительскую таблицу, содержащую центральные значения данных, и дочернюю таблицу с одинаковыми значениями, указывающую на ее родительский элемент. Предложение FOREIGN KEY указано в дочерней таблице. В родительских и дочерних таблицах должен использоваться один и тот же механизм хранения. Они не должны быть ВРЕМЕННЫМИ таблицами.

Соответствующие столбцы внешнего ключа и ссылочного ключа должны иметь похожие типы данных. Размер и знак целочисленных типов должны быть одинаковыми. Длина типов строк не обязательно должна быть одинаковой. Для небинных (символьных) строковых столбцов набор символов и сопоставление должны быть одинаковыми.

Примечание. Ограничения внешнего ключа поддерживаются в системе хранения данных InnoDB (не MyISAM или MEMORY). БД-настройки с использованием других движков принимают этот оператор CREATE TABLE но не будут учитывать ограничения внешнего ключа. (Хотя новые версии MySQL по умолчанию InnoDB , но это хорошая практика, чтобы быть явным.)

Клонирование существующей таблицы

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

CREATE TABLE ClonedPersons LIKE Persons;

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

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

CREATE TABLE ClonedPersons SELECT * FROM Persons;

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

CREATE TABLE ModifiedPersons
SELECT PersonID, FirstName + LastName AS FullName FROM Persons
WHERE LastName IS NOT NULL;

Первичные ключи и индексы не сохраняются при создании таблиц из SELECT . Вы должны обновить их:

CREATE TABLE ModifiedPersons (PRIMARY KEY (PersonID))
SELECT PersonID, FirstName + LastName AS FullName FROM Persons
WHERE LastName IS NOT NULL;

CREATE TABLE FROM SELECT

Вы можете создать одну таблицу из другой, добавив SELECT в конец инструкции CREATE TABLE :

CREATE TABLE stack (
    id_user INT,
    username VARCHAR(30),
    password VARCHAR(30)
);

Создайте таблицу в той же базе данных:

-- create a table from another table in the same database with all attributes
CREATE TABLE stack2 AS SELECT * FROM stack;

-- create a table from another table in the same database with some attributes
CREATE TABLE stack3 AS SELECT username, password FROM stack;

Создание таблиц из разных баз данных:

-- create a table from another table from another database with all attributes
CREATE TABLE stack2 AS SELECT * FROM second_db.stack;

-- create a table from another table from another database with some attributes
CREATE TABLE stack3 AS SELECT username, password FROM second_db.stack;

NB

Чтобы создать таблицу, аналогичную другой таблице, которая существует в другой базе данных, вам необходимо указать имя базы данных следующим образом:

FROM NAME_DATABASE.name_table

Показать структуру таблицы

Если вы хотите увидеть информацию о схеме своей таблицы, вы можете использовать одно из следующих действий:

SHOW CREATE TABLE child; -- Option 1

CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullName` varchar(100) NOT NULL,
  `myParent` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `mommy_daddy` (`myParent`),
  CONSTRAINT `mommy_daddy` FOREIGN KEY (`myParent`) REFERENCES `parent` (`id`) 
      ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Если используется из командной строки mysql, это менее подробное:

SHOW CREATE TABLE child \G

Менее описательный способ отображения структуры таблицы:

mysql> CREATE TABLE Tab1(id int, name varchar(30));
Query OK, 0 rows affected (0.03 sec)

mysql> DESCRIBE Tab1; -- Option 2 

+-------+-------------+------+-----+---------+-------+ 
| Field | Type        | Null | Key | Default | Extra |  
+-------+-------------+------+-----+---------+-------+ 
| id    | int(11)     | YES  |     | NULL    |       | 
| name  | varchar(30) | YES  |     | NULL    |       |  
+-------+-------------+------+-----+---------+-------+ 

И DESCRIBE, и DESC дают тот же результат.

Чтобы увидеть, как DESCRIBE выполняется во всех таблицах в базе данных сразу, см. Этот пример .

Создание таблицы с помощью столбца TimeStamp для отображения последнего обновления

Столбец TIMESTAMP будет отображаться при последнем обновлении строки.

CREATE TABLE `TestLastUpdate` (
    `ID` INT NULL,
    `Name` VARCHAR(50) NULL,
    `Address` VARCHAR(50) NULL,
    `LastUpdate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
COMMENT='Last Update'
;