DDL в SQL: CREATE TABLE, ALTER TABLE, DROP и TRUNCATE - Вопросы
Всего: 6 вопросов
1. Что такое DDL в SQL, какие команды в него входят и чем он отличается от DML?
Что такое 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?
Как создать базу данных и таблицу в 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, 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?
Как посмотреть структуру таблицы в 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 имя действие [, действие ...]; меняет структуру без потери данных; в одной команде можно перечислить несколько действий.
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?
Чем 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.