DDL в SQL: CREATE TABLE, ALTER TABLE, DROP и TRUNCATE - Вопросы

Всего: 6 вопросов

1. 

Что такое DDL в SQL, какие команды в него входят и чем он отличается от DML?

DDL (Data Definition Language, язык определения данных) — команды SQL, которые создают, изменяют и удаляют объекты базы данных: базы, таблицы, столбцы, индексы, ограничения. Основных команд пять:

  • CREATE — создаёт базу, таблицу, индекс, представление;
  • ALTER — меняет структуру: добавляет, изменяет и удаляет столбцы, ограничения, индексы;
  • RENAME — переименовывает таблицу;
  • DROP — удаляет объект вместе с данными;
  • TRUNCATE — удаляет все строки, сохраняя структуру таблицы.

DDL работает со структурой, а DML (INSERT, UPDATE, DELETE) — со строками. Важное отличие в MySQL: каждая команда DDL выполняет неявный COMMIT, поэтому её нельзя откатить через ROLLBACK; команды DML внутри транзакции откатываются. В PostgreSQL DDL выполняется внутри транзакции и откатывается.

2. 

Как создать базу данных и таблицу в MySQL и что делает IF NOT EXISTS?

База создаётся командой CREATE DATABASE и выбирается через USE. Таблица создаётся в текущей базе: в скобках через запятую перечисляют столбцы (имя, тип, ограничения), в конце — ограничения уровня таблицы.

CREATE DATABASE IF NOT EXISTS forum
    CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE forum;

CREATE TABLE topics (
    id         INT          NOT NULL AUTO_INCREMENT,
    title      VARCHAR(200) NOT NULL,
    author_id  INT          NOT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_topics_author FOREIGN KEY (author_id) REFERENCES users (id)
);

Если объект уже существует, команда без IF NOT EXISTS завершается ошибкой (ERROR 1007 для базы, ERROR 1050 для таблицы), а с IF NOT EXISTS ничего не делает и выдаёт только предупреждение. Такой вариант пишут в скриптах, которые запускают многократно. При этом IF NOT EXISTS проверяет только имя: если таблица есть, но с другими столбцами, её структура не изменится — для этого нужен ALTER TABLE. Родительская таблица (users) должна быть создана раньше дочерней (topics).

3. 

Что делают ограничения NOT NULL, DEFAULT и CHECK и чем ограничение у столбца отличается от ограничения у таблицы?

Ограничение — правило, которое база проверяет при каждой вставке и изменении строки; нарушение отклоняется с ошибкой.

  • NOT NULL — в столбце нет NULL (ERROR 1048); объявляется только у столбца.
  • DEFAULT значение — значение, если столбец не указан в INSERT; строго говоря, атрибут, а не проверка.
  • CHECK (условие) — значения удовлетворяют условию (ERROR 3819). Работает с MySQL 8.0.16, раньше разбирался и игнорировался. Строку с NULL пропускает: условие даёт «неизвестно», а не ложь.

Ограничение можно записать в описании столбца (name VARCHAR(50) NOT NULL UNIQUE) или отдельной строкой в конце CREATE TABLE. Второй способ позволяет дать имя через CONSTRAINT имя — его видно в тексте ошибки и по нему ограничение потом удаляют:

CREATE TABLE tags (
    id   INT         NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT uq_tags_name  UNIQUE (name),
    CONSTRAINT chk_tags_name CHECK (CHAR_LENGTH(name) >= 2)
);

Внешний ключ у столбца (author_id INT REFERENCES users (id)) MySQL 8.4 и более ранние версии молча игнорируют — его объявляют только строкой FOREIGN KEY (...) REFERENCES ....

4. 

Как посмотреть структуру таблицы в MySQL и чем DESCRIBE отличается от SHOW CREATE TABLE?

DESCRIBE таблица (короткая форма — DESC, то же выводит SHOW COLUMNS FROM таблица) показывает столбцы в виде таблицы: Field, Type, Null, Key, Default, Extra. В столбце Key PRI означает первичный ключ, MUL — неуникальный индекс (например, созданный для внешнего ключа).

SHOW CREATE TABLE таблица выводит полный текст CREATE TABLE: все ограничения с именами, движок и кодировку. Имена ограничений DESCRIBE не показывает, а они нужны для DROP FOREIGN KEY и DROP CHECK. Через SHOW CREATE TABLE также проверяют, что CHECK действительно сохранён, и сверяют определение столбца перед ALTER TABLE ... MODIFY. Список таблиц текущей базы выводит SHOW TABLES.

5. 

Как изменить существующую таблицу командой ALTER TABLE и как переименовать таблицу?

ALTER TABLE имя действие [, действие ...]; меняет структуру без потери данных; в одной команде можно перечислить несколько действий.

ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL AFTER email;     -- добавить столбец
ALTER TABLE topics MODIFY title VARCHAR(255) NOT NULL;               -- изменить тип и атрибуты
ALTER TABLE topics CHANGE title subject VARCHAR(200) NOT NULL;       -- переименовать и изменить
ALTER TABLE topics RENAME COLUMN title TO subject;                   -- только переименовать (MySQL 8.0+)
ALTER TABLE users DROP COLUMN phone;                                 -- удалить столбец
ALTER TABLE tags DROP CONSTRAINT chk_tags_name;                      -- удалить ограничение (8.0.19+)

Главная ловушка — MODIFY и CHANGE заменяют определение столбца целиком: что не указали (NOT NULL, DEFAULT, AUTO_INCREMENT), то молча пропадёт. Добавленный столбец без AFTER/FIRST встаёт последним, существующие строки получают DEFAULT или NULL. Внешний ключ на месте не меняют: его удаляют по имени (DROP FOREIGN KEY) и создают заново.

Таблицу переименовывают командой RENAME TABLE topics TO threads; или равнозначной ALTER TABLE topics RENAME TO threads;. Данные, индексы и ограничения сохраняются, внешние ключи других таблиц InnoDB обновляет сам. Несколько таблиц в одной RENAME TABLE переименовываются атомарно. Команды RENAME DATABASE в MySQL нет.

6. 

Чем TRUNCATE отличается от DELETE и DROP?

  • DELETE FROM t — команда DML: удаляет строки (все или по WHERE) по одной, счётчик AUTO_INCREMENT не сбрасывает, внутри транзакции откатывается, триггеры ON DELETE срабатывают. На миллионах строк медленно.
  • TRUNCATE TABLE t — в MySQL команда DDL: удаляет все строки (без WHERE), InnoDB пересоздаёт пустую таблицу, поэтому быстро. Структура, индексы и ограничения остаются, счётчик AUTO_INCREMENT сбрасывается к 1. Не откатывается (неявный COMMIT), триггеры не срабатывают. Для таблицы, на которую ссылается внешний ключ, — ERROR 1701, даже если дочерняя таблица пуста.
  • DROP TABLE t — команда DDL: удаляет таблицу целиком, со строками и структурой. Не откатывается. Для таблицы, на которую ссылается внешний ключ, — ERROR 3730. IF EXISTS превращает ошибку «таблицы нет» в предупреждение.

Итого: нужно удалить часть строк или иметь возможность отката — DELETE; очистить таблицу целиком и начать нумерацию заново — TRUNCATE; убрать таблицу из базы — DROP.

Страница 1 из 1