DDL – Data Definition Language

Data Definition Language (DDL) – это группа операторов определения данных. Другими словами, с помощью операторов, входящих в эту группы, мы определяем структуру базы данных и работаем с объектами этой базы, т.е. создаем, изменяем и удаляем их.

В эту группу входят следующие операторы:

CREATE – используется для создания объектов базы данных;
ALTER – используется для изменения объектов базы данных;
DROP – используется для удаления объектов базы данных.

В MySQL регистронезависимый синтаксис, ключевые слова дальше написаны КАПСОМ только для читабельности.

CREATE DATABASE - создание базы данных

CREATE DATABASE [IF NOT EXISTS] db_name

Оператор CREATE DATABASE создает базу данных с указанным именем. Если база данных уже существует и не указан ключевой параметр IF NOT EXISTS, то возникает ошибка выполнения команды.

Если в названии базы данных присутствуют пробелы или название совпадает с ключевым словом SQL, то название можно экранировать символами обратной кавычки (подобное экранирование применяется и к названиям других сущностей):

CREATE DATABASE `database`

DROP DATABASE - удаление базы данных

DROP DATABASE [IF EXISTS] db_name

Оператор DROP DATABASE удаляет все таблицы в указанной базе данных (строго говоря не только таблицы, а все имеющиеся объекты) и саму базу.

CREATE TABLE - создание таблицы

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options]

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

Имя таблицы может быть указано как db_name.tbl_name. Эта форма записи работает независимо от того, является ли указанная база данных текущей.
(create_definition, ...) описывает набор создаваемых сущностей (столбцы, индексы и т.д.):
- колонка (столбец)
  
  синтаксис:
```
col_name col_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
    [PRIMARY KEY]
```
  - col_name - название;
  - col_type - тип данных;
    - TINYINT[(length)] [UNSIGNED] [ZEROFILL]
    - INT[(length)] [UNSIGNED] [ZEROFILL]
    - INTEGER[(length)] [UNSIGNED] [ZEROFILL]
    - DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
    - FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
    - DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
    - CHAR(length) [BINARY]
    - VARCHAR(length) [BINARY]
    - DATE
    - TIME
    - TIMESTAMP
    - DATETIME
    - TEXT
    - ENUM(value1,value2,value3,...)
    - SET(value1,value2,value3,...)
  - NOT NULL | NULL - обязательность (по-умолчанию NULL);
  - DEFAULT default_value - значение по-умолчанию;
  - AUTO_INCREMENT - целочисленный столбец может иметь дополнительный атрибут AUTO_INCREMENT. При записи величины NULL (рекомендуется) или 0 в столбец AUTO_INCREMENT данный столбец устанавливается в значение value+1, где value представляет собой наибольшее для этого столбца значение в таблице на момент записи. Последовательность AUTO_INCREMENT начинается с 1.
  - PRIMARY KEY - представляет собой уникальный ключ KEY (KEY является синонимом для INDEX). Столбец с данным ключом должен быть определен как NOT NULL. В MySQL этот ключ называется PRIMARY (первичный). Таблица может иметь только один первичный ключ PRIMARY KEY (То есть если мы хотим создать составной первичный ключ, то надо создавать его отдельной сущностью).
  например:
```
CREATE TABLE `test` (
    `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
)
```
- первичный ключ (подразумевается составной, хотя никто не мешает использовать и одну колонку)
  
  синтаксис:
```
PRIMARY KEY (index_col_name,...)
```
  Например, аналог предыдущего запроса
```
CREATE TABLE `test` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
)
```
- индекс
  
  синтаксис:
```
KEY [index_name] (index_col_name,...)
```
  или
```
INDEX [index_name] (index_col_name,...)
```
  или
```
UNIQUE [INDEX] [index_name] (index_col_name,...)
```
  - index_name - название индекса
  - index_col_name - название колонки, входящей в ключ индекса: col_name [(length)]. Содержимое колонки может быть обрезано по длине length
  Ключ UNIQUE может иметь только различающиеся значения. При попытке добавить новую строку с ключом, совпадающим с существующей строкой, возникает ошибка выполнения команды.
- внешний ключ
  
  синтаксис:
```
[CONSTRAINT index_name] FOREIGN KEY (index_col_name,...)
    REFERENCES главная_таблица (столбец_главной_таблицы,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]
```
  - (index_col_name,...) - список полей, являющихся частью составного ключа (чаще всего просто одно поле). Должен соответствовать списку полей главной таблицы в выражении REFERENCES.
  - главная_таблица - название таблицы, с которой устанавливается связь
  - (столбец_главной_таблицы,...) - поля главной таблицы, по которым устанавливается связь
  Внешние ключи позволяют установить связи между таблицами. Внешний ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и указывает на один из столбцов из главной таблицы. Как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы.
  
  Для создания ограничения внешнего ключа после FOREIGN KEY указывается столбец таблицы, который будет представляет внешний ключ. А после ключевого слова REFERENCES указывается имя связанной таблицы, а затем в скобках имя связанного столбца, на который будет указывать внешний ключ.
  
  С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняются соответственно при удалении и изменении связанной строки из главной таблицы. В качестве действия могут использоваться следующие опции:
  - CASCADE: автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.
  - SET NULL: при удалении или обновлении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL. (В этом случае столбец внешнего ключа должен поддерживать установку NULL)
  - RESTRICT: отклоняет удаление или изменение строк в главной таблице при наличии связанных строк в зависимой таблице.
  - NO ACTION: то же самое, что и RESTRICT - действие по-умолчанию.
  - SET DEFAULT: при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Несмотря на то, что данная опция в принципе доступна, однако движок InnoDB не поддерживает данное выражение.
  Например:
```
-- таблица "склад"
CREATE TABLE `warehouse` (
    `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `title` varchar(100) DEFAULT NULL,
    `quantity` int(11) DEFAULT NULL,
)

-- таблица "продажи"
CREATE TABLE `sales` (
    `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `warehouse_id` int(11) DEFAULT NULL,
    `quantity` int(11) DEFAULT NULL,
    CONSTRAINT `sales_FK` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouse` (`id`)
)
```
table_options - тут можно настроить тип таблицы, кодировку символов, параметры автоинкремента. Но обычно эти параметры не трогают и они наследуются от аналогичных параметров базы данных.

ALTER TABLE - изменение структуры таблицы

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

Оператор ALTER TABLE обеспечивает возможность изменять структуру существующей таблицы. Например, можно добавлять или удалять столбцы, создавать или уничтожать индексы или переименовывать столбцы либо саму таблицу.

Оператор ALTER TABLE во время работы создает временную копию исходной таблицы. Требуемое изменение выполняется на копии, затем исходная таблица удаляется, а новая переименовывается. Так делается для того, чтобы в новую таблицу автоматически попадали все обновления кроме неудавшихся.

Опция IGNORE управляет работой ALTER TABLE при наличии дубликатов уникальных ключей в новой таблице. Если опция IGNORE не задана, то для данной копии процесс прерывается и происходит откат назад. Если IGNORE указывается, тогда для строк с дубликатами уникальных ключей остается только первая строка, а остальные удаляются.
alter_spec - что меняется
- добавление новой колонки
```
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
```
  В create_definition используется тот же синтаксис, что и при создании таблицы.
  
  По-умолчанию столбец добавляется в конец списка столбцов, но с помощью модификаторов FIRST и AFTER можно разместить столбец в начале списка или после какого-то имеющегося столбца.
- добавление первичного ключа
```
ADD PRIMARY KEY (index_col_name,...)
```
- добавление внешнего ключа
```
ADD [CONSTRAINT symbol] FOREIGN KEY (index_col_name,...)
    REFERENCES главная_таблица (столбец_главной_таблицы,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]    
```
- изменение существующей колонки
```
ALTER [COLUMN] col_name [create_definition] {SET DEFAULT literal | DROP DEFAULT}    
```
  В целом параметры те же что и при создании, единствено отличается синтаксис добавления или удаления значения по-умолчанию.
- удаление колонки
```
DROP [COLUMN] col_name
```
- удаление первичного ключа
```
DROP PRIMARY KEY
```
- удаление индекса (в том числе внешнего ключа)
```
DROP INDEX index_name
```

Задание

Написать скрипт, создающий базу данных по ERD вашего курсового проекта.

ddl.md 16 KB History Raw