Группы операторов в SQL: DDL, DML, DCL и TCL

17 фев 2025

Группы операторов в SQL: DDL, DML, DCL и TCL

15 мин

709

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

Язык SQL (Structured Query Language)

SQL , или Structured Query Language, язык структурированных запросов — это декларативный язык программирования, который применяется для создания, управления и модификации данных в реляционной базе данных. SQL является важным инструментом для работы с реляционными системами управления базами данных (СУБД, database management systems), например, MySQL, PostgreSQL и другими.

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

Преимущества SQL:

так как SQL — это декларативный язык, пользователю достаточно описать, что он хочет сделать, не указывая, как конкретно это сделать;
SQL позволяет совершать широкий спектр операций — создавать объекты (database objects), менять их структуру, добавлять, модифицировать и удалять данные, управлять транзакциями, настраивать права доступа и так далее;
SQL поддерживается многими реляционными СУБД, что делает его универсальным инструментом;
SQL разработан таким образом, что даже при работе с большими объемами данных сохраняется высокая производительность.

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

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

DDL (Data Definition Language) — это группа операторов SQL, с помощью которых можно управлять объектами базы данных (таблицами, представлениями, индексами и так далее), а именно: создавать, модифицировать и удалять объекты. К основным DDL-операторам относятся: CREATE, ALTER, DROP.

Команда CREATE

DDL-оператор CREATE используется для создания новых объектов базы данных: таблиц, представлений, индексов и других. Например, такой запрос можно применить для создания таблицы с данными об учениках:

CREATE TABLE students (

  student_id int PRIMARY KEY,

  first_name varchar(50) NOT NULL,

  last_name varchar(50) NOT NULL,

  birth_date date

);

sql

В результате применения CREATE будет создана таблица из четырех столбцов (student_ID, first_name, last_name и birth_date). student_ID — это первичный ключ (PRIMARY KEY), в данном случае использован для того, чтобы ID каждого ученика был уникальным. После имени каждого столбца указывается его тип данных: int — целое число, varchar — символьные данные переменной длины, в скобках указывается максимальная длина; date — дата. NOT NULL означает, что столбец не может содержать null-значения.

Для того чтобы получить информацию о структуре таблицы в базе данных (имена столбцов, типы данных, возможность содержания null-значения и так далее) можно применить DESCRIBE или DESC:

DESCRIBE students;

sql

Команда ALTER

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

Добавим в таблицу учеников новый столбец, в котором будут храниться адреса их электронной почты:

ALTER TABLE students

ADD email varchar(100) NOT NULL;

sql

Чтобы изменить тип данных столбца, можно применить следующий запрос:

ALTER TABLE students

ALTER COLUMN last_name nvarchar(100);

sql

Удаление столбца (в данном случае last_name) может выглядеть так:

ALTER TABLE students

DROP COLUMN last_name

sql

Команда DROP

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

Пример с удалением таблицы, содержащей данные об учениках:

DROP TABLE students

sql

Этот запрос удалит саму таблицу students и все данные, которые она содержит.

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

DML (Data Manipulation Language) — это группа операторов SQL, которые применяются для работы с данными, которые уже хранятся в базе данных. DML-операторы позволяют извлекать, вставлять, обновлять и удалять данные. Основные операторы: SELECT, INSERT INTO, DELETE, UPDATE.

Команда SELECT

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

Для выбора конкретных столбцов (здесь — first_name и last_name) можно использовать следующий запрос:

SELECT first_name, last_name FROM students;

sql

Если же нужно извлечь сразу всю таблицу, то применяется такой запрос:

SELECT * FROM students;

sql

Дополнительно можно применить оператор WHERE — это позволить отфильтровать данные по заданным условиям, например:

SELECT first_name, last_name FROM students WHERE birth_date > '2004-01-01';

sql

Таким образом выбраны будут только те студенты, которые родились после 1 января 2004 года.

Также можно добавить оператор ORDER BY, который отсортирует результаты по заданному столбцу:

SELECT first_name, last_name FROM students ORDER BY last_name ASC;

sql

В результате фамилии будут отсортированы по алфавиту в порядке возрастания.

Команда INSERT INTO

DML-оператор INSERT INTO применяется для добавления новых строк в таблицу.

Для вставки новой записи в таблицу students можно применить следующий запрос:

INSERT INTO students (student_id, first_name, last_name, birth_date)

VALUES (15, 'Ivan', 'Ivanov', '2010-12-05');

sql

Таким образом в таблицу будет добавлен новый ученик, указанные значения (ID, имя, фамилия, дата рождения) будут записаны в соответствующие столбцы.

Можно вставить и сразу несколько записей, например, так:

INSERT INTO students (student_id, first_name, last_name, birth_date)

VALUES (10, 'Ivan', 'Ivanov', '2010-12-05'),

               (11, 'Ivan', 'Koshkin', '2010-10-09'),

               (12, 'Ivan', 'Andreev', '2010-04-11');

sql

Чтобы импортировать данные из одной таблицы в другую, можно совместить операторы SELECT и INSERT INTO:

INSERT INTO students (student_id, first_name, last_name, birth_date)

SELECT student_id, first_name, last_name, birth_date

FROM students_1

WHERE birth_date > '2004-01-01';

sql

В результате этого запроса из таблицы students _1 извлекаются те строки, где значение в столбце birth_date больше даты 2004-01-01 (то есть ученики родились после 1 января 2004 года). Эти строки импортируются (вставляются) в таблицу students.

Команда DELETE

DML-оператор DELETE применяется в SQL для удаления из таблицы тех записей, которые соответствуют заданному с помощью WHERE условию.

Например, чтобы удалить ученика с ID равным 1, можно применить следующий запрос:

DELETE FROM students WHERE student_id = 1;

sql

Если условие с WHERE отсутствует, то удалены будут все записи данной таблицы.

К оператору DELETE можно добавить JOIN, тогда запрос будет выглядеть так:

DELETE students

FROM students

JOIN students_1 ON students.student_id = students_1.student_id

WHERE students.student_id = 1;

sql

Этот запрос удалит из таблицы students строку, где student_id равно 1, но только если существует соответствующая строка в таблице students_1 с тем же student_id равным 1.

Команда UPDATE

DML-оператор UPDATE в SQL нужен для модификации существующих в таблице данных.

Например, если нужно изменить имя ученика, то можно применить такой запрос:

UPDATE students SET first_name = 'Ivan' WHERE student_id = 1;

sql

В результате имя ученика с ID равным 1 будет изменено на значение Ivan.

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

Допускается использование и других условий, например:

UPDATE students SET first_name = 'Ivan' WHERE student_id > 10;

sql

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

Что такое DCL (Data Control Language)

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

Команда GRANT

DCL-оператор GRANT используется для предоставления пользователям определенных прав на объекты базы данных.

Чтобы предоставить пользователю право на выполнение SELECT-команд, можно использовать следующий запрос:

GRANT SELECT ON students TO user_name;

sql

Пользователь с именем user_name получит право на выполнение SELECT-команд в таблице students.

Если нужно, чтобы пользователь мог вносить записи в таблицу, то применяется оператор INSERT:

GRANT INSERT ON students TO user_name;

sql

Для того чтобы дать разрешение на изменение любого столбца, применяется оператор UPDATE:

GRANT UPDATE ON students TO user_name;

sql

Разрешение на изменение определенных столбцов:

GRANT UPDATE (first_name) ON students TO user_name;

sql

Также можно составлять списки привилегий и пользователей, тогда они разделяются запятой:

GRANT SELECT, INSERT ON students TO user_name_1, user_name_2;

sql

Если нужно предоставить все права на какой-то объект, то применяется ALL PRIVILEGES:

GRANT ALL PRIVILEGES ON students TO user_name;

sql

WITH GRANT OPTION позволяет пользователю, которому передали эту привилегию, тоже передавать привилегии:

GRANT ALL PRIVILEGES ON students TO user_name WITH GRANT OPTION;

sql

Команда REVOKE

DCL-оператор REVOKE в SQL нужен, наоборот, для того, чтобы отозвать ранее предоставленные привилегии.

Отозвать право на выполнение оператора SELECT в таблице student можно так:

REVOKE SELECT ON students FROM user_name;

sql

Так же, как в случае с GRANT допускается формирование списков привилегий и пользователей:

REVOKE UPDATE, SELECT ON students FROM user_name;

sql

Если необходимо отозвать все привилегии, применяется следующий запрос:

REVOKE ALL PRIVILEGES ON students TO user_name;

sql

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

Что такое TCL (Transaction Control Language)

TCL (Transaction Control Language) — это набор операторов SQL, которые применяются для управления транзакциями: их начала, фиксации и отката. Основные команды: BEGIN/COMMIT и ROLLBACK.

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

Команда BEGIN/COMMIT

TCL-операторы BEGIN/COMMIT применяются в SQL для объявления транзакции: ее начала и окончания соответственно. Пример применения BEGIN/COMMIT:

BEGIN; 

UPDATE students 

SET first_name = 'Ivan' 

WHERE student_id = 1; 

INSERT INTO students (student_id, first_name, last_name, birth_date) 

VALUES (2, 'Andrew', 'Andreev', '2004-01-10'); 

COMMIT;

sql

В процессе данной транзакции для ученика с ID равным 1 значение столбца first_name будет изменено на Ivan, также в таблицу будет добавлена новая запись.

Любая ошибка внутри транзакции отменяет все изменения, которые были совершены после BEGIN.

Команда ROLLBACK

TCL-оператор ROLLBACK в SQL применяется для отката транзакции: отмены сделанных в рамках транзакции изменений.

Выглядеть применение ROLLBACK может следующим образом:

BEGIN; 

UPDATE students 

SET first_name = 'Ivan' 

WHERE student_id = 1; 

INSERT INTO students (student_id, first_name, last_name, birth_date) 

VALUES (2, 'Andrew', 'Andreev', '2004-01-10'); 

ROLLBACK;

sql