DDL, DML, DCL и TCL в языке SQL

Рассказываем, что такое группы операторов в языке SQL и зачем они нужны.

В статье обсудим категории команд в языке SQL и выясним, что означает и что в себя включает DDL, DML, DCL и TCL. Объясним термины, приведем примеры команд и изучим базовые концепции Structured Query Language.

Коротко о языке структурированных запросов SQL

Сейчас SQL де-факто — стандарт среди языков запросов к реляционным базам данных. Компании прикладывают много сил, чтобы их хранилища данных были SQL-совместимыми, например Amazon Redshift, Snowflake, Firebolt и другие. Но еще полвека назад разработчики активно думали над тем, какую модель данных и язык выбрать для своей базы данных.

До реляционной модели использовали два основных варианта:

network data model — обладала высокой сложностью запросов при большой вероятности испортить данные;
hierarchy data model — отличалась дублированием данных и отсутствием независимости языка запросов от модели данных.

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

Главные преимущества реляционной модели:

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

Кодд предложил использовать язык реляционной алгебры для доступа к данным. Идея реляционной модели понравилась сообществу — в семидесятые разработали первые реляционные БД: Ingres, System R и Oracle. Вместе с System R появился язык запросов SEQUEL, позже переименованный в SQL.

Structured Query Language — язык программирования, который широко используется в современных системах управления базами данных для сохранения, обработки и изменения данных. SQL относится к семейству декларативных языков. Ключевая цель в том, чтобы сказать системе, какой ответ необходим, то есть описать ожидаемый результат, а не процесс его получения. Это означает, что некоторые СУБД ответственны за эффективное исполнение запросов. Такие системы имеют сложные оптимизаторы запросов, которые способны переписывать запросы и искать оптимальные стратегии исполнения.

Команды SQL — это операторы, которые используются для коммуникации с базой данных, выполнения определенных задач и функций. Все команды делятся на четыре категории: DDL, DML, DCL, и TCL.

Протестировать команды поможет сервис DB Fiddle. Используемый код совместим с PostgreSQL v14.

Что значит DDL (Data Definition Language)

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

Наиболее известные команды SQL DDL — CREATE, ALTER, DROP. Рассмотрим их подробнее.

CREATE

Этот DDL-оператор создает объекты базы данных, например таблицы или представления.

CREATE TABLE IF NOT EXISTS table_name (

user_id serial PRIMARY KEY,

username VARCHAR ( 50 ) NOT NULL,

last_login TIMESTAMP

);

ALTER

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

ALTER TABLE old_table_name RENAME TO new_table_name;

DROP

Команду используют для удаления объектов из базы данных: таблицы, представления или индекса. Пример удаляет таблицу с именем my_table.

Пример для PostgreSQL v14:

DROP TABLE my_table;

Команду DROP нельзя отменить, поэтому будьте аккуратны, когда используете ее.

Что такое DML (Data Manipulation Language)

https://sky.pro/media/svyazi-mezhdu-tabliczami-sql/

DML, или Data Manipulation Language — это группа операторов, которые позволяют получать и изменять записи, присутствующие в таблице. Разберем отдельные DML-команды.

SELECT

Эта инструкция используется для получения кортежей из таблицы.

SELECT user_id, username FROM table_name;

INSERT INTO

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

INSERT INTO table_name(user_id, username, last_login) VALUES(1, 'Ivan Petrov', NULL)

DELETE

DML-команда позволяет удалить одну или несколько записей.

DELETE FROM table_name WHERE username = 'nick';

UPDATE

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

UPDATE table_name SET username = 'newnick' WHERE user_id = 1;

Значение DCL (Data Control Language)

DCL, или Data Control Language — это команды SQL, которые используют для предоставления и отзыва привилегий пользователя базы данных. При этом пользователь не может откатить изменения. Рассмотрим наиболее известные команды: GRANT и REVOKE.

GRANT

Используется для предоставления пользователям прав доступа к базе данных. Например, команда разрешает пользователю `user` добавлять записи в таблицу `my_table`.

GRANT INSERT ON my_table TO user;

REVOKE

Команда, которая позволяет отозвать ранее выданные права доступа. Например, команда отзывает право на вставку в таблицу `my_table` у пользователя `user`.

REVOKE INSERT ON my_table TO user;

Команды TCL (Transaction Control Language)

TCL, или Transaction Control Language — одни из наиболее популярных команд SQL. Их используют для обеспечения согласованности базы данных и для управления транзакциями.

Транзакция — это набор SQL-запросов, выполняемых над данными, которые объединены в атомарную секцию. Это значит, что промежуточные результаты операции не видны для других конкурирующих транзакций — и вся секция будет либо выполнена, либо полностью отменена в случае ошибки.

Примеры команд: BEGIN/COMMIT, ROLLBACK.

BEGIN/COMMIT

Команда, которая применяется для объявления транзакции. Команда иллюстрирует пример банковской транзакции: пользователь с `user_id`, равным 10, переводит 100 условных единиц на баланс пользователя с `user_id`, равным 20. Конструкция BEGIN/COMMIT гарантирует, что баланс изменится сразу у двух пользователей — либо ни у одного.

BEGIN;

UPDATE my_table SET balance = balance - 100 WHERE used_id = 10;

UPDATE my_table SET balance = balance + 100 WHERE used_id = 20;

COMMIT;

ROLLBACK

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

BEGIN;

UPDATE my_table SET balance = balance - 100 WHERE used_id = 10;

UPDATE my_table SET balance = balance + 100 WHERE used_id = 20;

ROLLBACK

COMMIT;

Краткие итоги

Команды групп DDL, DML, DCL и TCL помогают с различными функциями, включая запись, обновление, редактирование, удаление данных и управление транзакциями. Знать основные команды SQL важно, чтобы понимать принципы взаимодействия с базами данных.