Команды CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE VIEW

Введение

Работа с базой данных начинается с её структуры. Прежде чем хранить, изменять или анализировать данные, необходимо создать таблицы, в которых эти данные будут располагаться.

В SQL за создание и изменение структуры базы данных отвечают команды DDL (Data Definition Language) — языка определения данных.

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

Эти команды формируют каркас базы данных, на котором строится всё остальное.

Основные операции, выполняемые с помощью DDL:

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

Команды, рассматриваемые в этой лекции — CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE VIEW — являются базовыми инструментами администратора и разработчика базы данных.

Понимание их работы необходимо для грамотного проектирования и сопровождения любой информационной системы.

Команда CREATE TABLE

Команда CREATE TABLE используется для создания новой таблицы в базе данных.

Таблица является основной структурой хранения информации в реляционной базе данных, и каждая строка таблицы — это отдельная запись (объект), а каждый столбец — отдельное свойство (атрибут).

Общий синтаксис

CREATE TABLE имя_таблицы (
    имя_столбца тип_данных [ограничение],
    ...
);

имя_таблицы — задаёт название создаваемой таблицы;
имя_столбца — имя поля (колонки) в таблице;
тип_данных — определяет, какие значения можно хранить в этом столбце (число, текст, дата и т.д.);
ограничение (constraint) — задаёт дополнительные правила для данных (например, уникальность, обязательность, связь с другой таблицей).

Пример создания таблицы

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    birth_date DATE,
    group_id INT,
    email VARCHAR(100) UNIQUE
);

Разбор примера:

student_id — целочисленный идентификатор студента, обозначенный как PRIMARY KEY (уникальный ключ таблицы).
full_name — строка длиной до 100 символов, обязательное поле (NOT NULL).
birth_date — дата рождения.
group_id — числовое поле для связи с таблицей Groups.
email — уникальное поле (UNIQUE) для предотвращения повторов адресов.

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

Тип данных	Назначение	Пример значения
`INT`	Целое число	`123`, `-5`
`DECIMAL(p,s)`	Число с фиксированной точностью	`12.50`
`VARCHAR(n)`	Текст длиной до n символов	`'Иванов'`
`CHAR(n)`	Текст фиксированной длины	`'A123'`
`DATE`	Дата (год, месяц, день)	`'2024-10-26'`
`TIME`	Время	`'15:30:00'`
`DATETIME`	Дата и время	`'2024-10-26 15:30:00'`
`BOOLEAN`	Логическое значение (истина/ложь)	`TRUE`, `FALSE`

При выборе типов данных важно учитывать, какой объём информации хранится и как она будет использоваться.

Основные ограничения (Constraints)

Ограничение	Назначение	Пример
`PRIMARY KEY`	Уникальный идентификатор строки	`student_id INT PRIMARY KEY`
`NOT NULL`	Поле не может быть пустым	`full_name VARCHAR(50) NOT NULL`
`UNIQUE`	Все значения должны быть уникальными	`email VARCHAR(100) UNIQUE`
`DEFAULT`	Устанавливает значение по умолчанию	`status VARCHAR(10) DEFAULT 'active'`
`CHECK`	Проверяет условие для значений	`age INT CHECK (age >= 16)`
`FOREIGN KEY`	Создаёт связь с другой таблицей	`FOREIGN KEY (group_id) REFERENCES Groups(group_id)`

Пример с ограничениями и связями

CREATE TABLE Groups (
    group_id INT PRIMARY KEY,
    group_name VARCHAR(50) NOT NULL
);

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    group_id INT,
    FOREIGN KEY (group_id) REFERENCES Groups(group_id)
);

Здесь каждая запись в таблице Students связана с группой из таблицы Groups по полю group_id.

Если удалить группу, на которую ссылается студент, СУБД выдаст ошибку, что обеспечивает целостность данных.

Ошибки при создании таблиц

Использование одинаковых имён таблиц без удаления старых (table already exists).
Указание недопустимого типа данных.
Нарушение ограничений (duplicate key, not null constraint failed).
Попытка создать внешние ключи, если таблица-родитель ещё не существует.

Итоги

CREATE TABLE — основная команда для создания структуры хранения данных.
При создании таблицы нужно определить имена столбцов, типы данных и ограничения.
Правильное проектирование таблицы обеспечивает надёжность, целостность и удобство работы с базой данных.

Команда ALTER TABLE

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

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

Общий синтаксис

ALTER TABLE имя_таблицы
[Действие];

Команда выполняет одно действие за раз, например:

добавить новый столбец;
изменить тип данных столбца;
удалить столбец;
добавить или удалить ограничение (constraint);
изменить имя таблицы.

Добавление нового столбца

Чтобы добавить в таблицу новый столбец, используется оператор ADD COLUMN.

Пример:

ALTER TABLE Students
ADD COLUMN phone VARCHAR(15);

Теперь в таблице Students появится новое поле phone, в котором можно хранить номер телефона.

Можно добавить несколько столбцов сразу:

ALTER TABLE Students
ADD COLUMN address VARCHAR(100),
ADD COLUMN email VARCHAR(100);

Изменение типа данных

Если необходимо изменить тип данных существующего столбца, используется оператор ALTER COLUMN.

Пример:

ALTER TABLE Students
ALTER COLUMN phone TYPE VARCHAR(20);

Такая операция полезна, если формат данных нужно расширить (например, увеличить длину текста).

Переименование столбца

Некоторые СУБД (например, PostgreSQL) позволяют изменить имя столбца:

ALTER TABLE Students
RENAME COLUMN phone TO contact_phone;

В MySQL для этого используется синтаксис CHANGE COLUMN.

Удаление столбца

Чтобы удалить столбец, который больше не используется:

ALTER TABLE Students
DROP COLUMN address;

После выполнения команды поле будет полностью удалено из таблицы вместе со всеми данными в этом столбце.

Добавление ограничений (Constraints)

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

Пример: добавление внешнего ключа:

ALTER TABLE Students
ADD CONSTRAINT fk_group
FOREIGN KEY (group_id) REFERENCES Groups(group_id);

Пример: добавление проверки условия:

ALTER TABLE Students
ADD CONSTRAINT chk_age CHECK (birth_date > '2000-01-01');

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

Удаление ограничений

Чтобы удалить ранее установленное ограничение, используется команда DROP CONSTRAINT:

ALTER TABLE Students
DROP CONSTRAINT fk_group;

Переименование таблицы

Если требуется изменить имя таблицы целиком:

ALTER TABLE Students
RENAME TO Learners;

Такое действие не изменяет данные и структуру, но обновляет название таблицы в схеме базы данных.

Пример комплексного изменения

ALTER TABLE Students
ADD COLUMN email VARCHAR(100) UNIQUE,
ALTER COLUMN full_name TYPE VARCHAR(150),
DROP COLUMN birth_date;

В этом примере добавляется новый столбец email, увеличивается длина имени, а поле birth_date удаляется.

Основные действия ALTER TABLE

Действие	Ключевые слова	Пример
Добавить столбец	`ADD COLUMN`	`ALTER TABLE Students ADD COLUMN phone VARCHAR(15);`
Изменить тип данных	`ALTER COLUMN ... TYPE`	`ALTER TABLE Students ALTER COLUMN age TYPE SMALLINT;`
Удалить столбец	`DROP COLUMN`	`ALTER TABLE Students DROP COLUMN address;`
Добавить ограничение	`ADD CONSTRAINT`	`ALTER TABLE Students ADD CONSTRAINT fk_group FOREIGN KEY (group_id) REFERENCES Groups(group_id);`
Удалить ограничение	`DROP CONSTRAINT`	`ALTER TABLE Students DROP CONSTRAINT fk_group;`
Переименовать столбец	`RENAME COLUMN`	`ALTER TABLE Students RENAME COLUMN phone TO contact_phone;`
Переименовать таблицу	`RENAME TO`	`ALTER TABLE Students RENAME TO Learners;`

Итоги

ALTER TABLE — мощный инструмент для изменения структуры таблицы без её удаления.
Команда поддерживает добавление, удаление и модификацию столбцов и ограничений.
Следует быть осторожным: изменения структуры могут повлиять на существующие данные.
Перед выполнением серьёзных изменений рекомендуется создавать резервную копию таблицы.

Команда DROP TABLE

Команда DROP TABLE используется для удаления таблицы из базы данных вместе со всеми её данными, структурой и ограничениями.

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

Эта операция относится к языку DDL (Data Definition Language) и применяется, когда таблица больше не нужна или её необходимо пересоздать заново.

Общий синтаксис

DROP TABLE имя_таблицы;

После выполнения этой команды:

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

Пример

DROP TABLE Students;

После выполнения этой команды таблица Students исчезнет из базы данных, и все записи о студентах будут потеряны.

Безопасное удаление

Чтобы избежать ошибки при удалении таблицы, которая уже не существует, в некоторых СУБД (например, MySQL и PostgreSQL) можно использовать модификатор IF EXISTS:

DROP TABLE IF EXISTS Students;

Эта команда проверит наличие таблицы перед удалением.

Если таблица отсутствует — СУБД просто выдаст уведомление, а не ошибку.

Удаление нескольких таблиц одновременно

Можно удалить сразу несколько таблиц одной командой:

DROP TABLE IF EXISTS Students, Groups, Subjects;

При этом все перечисленные таблицы будут удалены из базы данных.

Ограничения при удалении таблицы

Связанные таблицы.

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

Пример ошибки:

ERROR: cannot drop table "Groups" because other objects depend on it DETAIL: constraint fk_group on table Students depends on table Groups
Решение проблемы:
- сначала удалить зависимую таблицу (Students),
- или удалить ограничение внешнего ключа:
  
  sql ALTER TABLE Students DROP CONSTRAINT fk_group; DROP TABLE Groups;

Временные таблицы

Временные таблицы (TEMP или TEMPORARY) существуют только в рамках одного сеанса работы с базой.

Их можно удалять командой DROP TABLE, но они также автоматически исчезают при завершении сеанса:

DROP TABLE IF EXISTS temp_results;

Это удобно для временных расчётов, отчётов и тестов.

DROP TABLE с параметром CASCADE

Некоторые СУБД (например, PostgreSQL) позволяют удалить таблицу вместе со всеми зависимыми объектами (внешними ключами, представлениями и т.д.) с помощью параметра CASCADE:

DROP TABLE Groups CASCADE;

Использовать CASCADE нужно очень осторожно, так как он может удалить больше объектов, чем ожидалось.

Пример комплексного сценария

-- Удаляем зависимость
ALTER TABLE Students DROP CONSTRAINT fk_group;

-- Удаляем таблицы
DROP TABLE IF EXISTS Students;
DROP TABLE IF EXISTS Groups;

Такой порядок гарантирует, что удаление произойдёт корректно, без ошибок целостности.

Основные параметры DROP TABLE

Ключевое слово	Назначение	Пример
`IF EXISTS`	Удаляет таблицу, если она существует	`DROP TABLE IF EXISTS Students;`
`CASCADE`	Удаляет таблицу и все объекты, которые от неё зависят	`DROP TABLE Groups CASCADE;`
`RESTRICT`	Запрещает удаление, если есть зависимости (по умолчанию)	`DROP TABLE Groups RESTRICT;`

Важно помнить

DROP TABLE — необратимая операция, данные будут потеряны навсегда.
Перед удалением таблицы нужно убедиться, что данные действительно не нужны.
Для тестовых таблиц можно использовать IF EXISTS, чтобы избежать ошибок.
Для рабочих баз данных рекомендуется сначала делать резервную копию.

Итоги

Команда DROP TABLE полностью удаляет таблицу из базы данных.
Возможности IF EXISTS и CASCADE делают удаление более гибким, но требуют осторожности.
Перед удалением связанных таблиц необходимо снять внешние зависимости.
Команда применяется только на этапе администрирования или реструктуризации базы данных.

Команда CREATE VIEW

Команда CREATE VIEW используется для создания представления (view) — виртуальной таблицы, которая формируется на основе результата SQL-запроса.

Представление не хранит данные физически, а отображает их из одной или нескольких таблиц в удобной и структурированной форме.

Что такое представление?

Представление (view) — это логический слой между пользователем и базой данных.

Оно позволяет обращаться к данным так, как будто это отдельная таблица, хотя на самом деле информация берётся из других таблиц.

Представления особенно полезны, когда:

необходимо упростить сложные запросы;
нужно ограничить доступ к определённым столбцам или строкам;
требуется обеспечить единый формат данных для пользователей или отчётов.

Общий синтаксис

CREATE VIEW имя_представления AS
SELECT столбцы
FROM таблицы
WHERE условия;

Простой пример

CREATE VIEW ActiveStudents AS
SELECT student_id, full_name, group_id
FROM Students
WHERE birth_date > '2005-01-01';

Теперь можно обращаться к представлению как к обычной таблице:

SELECT * FROM ActiveStudents;

Результат запроса будет показывать только студентов, рождённых после 1 января 2005 года.

При этом никакие новые данные не создаются — представление просто отображает нужную часть таблицы Students.

Пример представления с несколькими таблицами

CREATE VIEW StudentsInfo AS
SELECT s.student_id, s.full_name, g.group_name
FROM Students s
JOIN Groups g ON s.group_id = g.group_id;

Представление StudentsInfo объединяет данные из таблиц Students и Groups, чтобы не писать длинный JOIN-запрос каждый раз.

Использование представлений

После создания представления можно:

выполнять к нему запросы:

sql SELECT * FROM StudentsInfo WHERE group_name = 'ИС-21';
использовать его в других представлениях или запросах;
предоставить доступ пользователям только к этому представлению, скрыв исходные таблицы.

Обновляемые и не обновляемые представления

Тип представления	Характеристика	Пример
Обновляемое	Позволяет изменять данные в исходной таблице через представление (если оно построено из одной таблицы и без сложных операций).	`CREATE VIEW SimpleView AS SELECT name, age FROM Students;`
Не обновляемое	Только для чтения, не позволяет вносить изменения (если содержит JOIN, агрегаты, DISTINCT, GROUP BY и т.п.).	`CREATE VIEW Report AS SELECT group_id, COUNT(*) FROM Students GROUP BY group_id;`

Если попытаться изменить данные через не обновляемое представление, СУБД выдаст ошибку.

Изменение и удаление представлений

Изменение существующего представления:

sql CREATE OR REPLACE VIEW ActiveStudents AS SELECT full_name, group_id FROM Students WHERE birth_date > '2006-01-01';
Удаление представления:

sql DROP VIEW ActiveStudents;

В некоторых СУБД можно использовать IF EXISTS:

DROP VIEW IF EXISTS ActiveStudents;

Преимущества использования представлений

Преимущество	Описание
Упрощение работы	Сложные запросы можно заменить коротким обращением к представлению
Безопасность	Можно предоставить доступ к представлению без разрешения на исходные таблицы
Гибкость	Представления можно изменять без изменения реальных данных
Повторное использование	Один раз созданное представление можно использовать в разных запросах
Логическая независимость	Изменения в структуре таблиц не требуют изменения приложений, если они работают с представлениями

Пример комплексного использования

-- Создание представления для отчёта по группам
CREATE VIEW GroupReport AS
SELECT g.group_name, COUNT(s.student_id) AS student_count
FROM Groups g
LEFT JOIN Students s ON g.group_id = s.group_id
GROUP BY g.group_name;

-- Запрос к представлению
SELECT * FROM GroupReport;

Представление GroupReport автоматически подсчитывает количество студентов в каждой группе и может использоваться для отчетности.

Итоги

CREATE VIEW — создаёт виртуальную таблицу на основе SQL-запроса.
Представления не хранят данные, а отражают их из других таблиц.
Используются для упрощения работы, аналитики и ограничения доступа.
Могут быть обновляемыми или только для чтения, в зависимости от сложности запроса.
Изменяются через CREATE OR REPLACE VIEW, удаляются командой DROP VIEW.

Итоги

Команда	Назначение	Основные действия	Пример
CREATE TABLE	Создаёт новую таблицу	Определяет поля, типы данных, ключи и связи	`CREATE TABLE Students (...);`
ALTER TABLE	Изменяет структуру таблицы	Добавление/удаление столбцов, изменение типа данных, добавление ограничений	`ALTER TABLE Students ADD email VARCHAR(100);`
DROP TABLE	Удаляет таблицу и её данные	Полное удаление таблицы из базы	`DROP TABLE Students;`
CREATE VIEW	Создаёт виртуальную таблицу (представление)	Определяет запрос, который отображает данные из одной или нескольких таблиц	`CREATE VIEW StudentsInfo AS SELECT ...;`