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

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

В этой статье разберем, как работает каждая из этих категорий.

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

SQL — это стандартная система, которая помогает работать с реляционными базами данных. Крупные компании делают всё, чтобы их системы «понимали» SQL. Но так было не всегда. Раньше разработчики ломали голову, как правильно хранить и работать с данными.

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

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

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

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

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

SQL-команды делятся на четыре группы:

• DDL — описывает структуры данных.
• DML — работает с самими данными.
• DCL — управляет доступом.
• TCL — управляет транзакциями.

Попробовать команды SQL можно прямо в браузере через DB Fiddle, где есть PostgreSQL v14.

Пройдите курс «Аналитик данных» в онлайн-университете Skypro. Освоите SQL, Excel, Python, Java, научитесь рассчитывать лайфтайм, LTR и LTV и многое другое. Весь курс построен на практике — сразу начнете работать над реальными проектами. Учеба проходит онлайн — удобно совмещать с работой и личной жизнью. Занимайтесь, когда вам удобно: утром, ночью, на выходных.

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

С помощью DDL создают и меняют структуру базы данных: таблицы, схемы, индексы и другие объекты.

Самые известные команды 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 -- время последнего входа
);

Команду CREATE используют каждый раз, когда нужно задать структуру базы данных с нуля.

ALTER

С помощью ALTER меняют существующую таблицу: добавляют или убирают столбцы, изменяют тип данных, задают или убирают ограничения, например NOT NULL, переименовывают таблицу или столбец.

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

ALTER TABLE old_table_name RENAME TO new_table_name;

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

Добавить новый столбец:

ALTER TABLE users ADD email VARCHAR(100);

Удалить столбец:

ALTER TABLE users DROP COLUMN email;

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

DROP

DROP удаляет объекты из базы данных — таблицу, представление или индекс.

Например, удалить таблицу с именем my_table в PostgreSQL:

DROP TABLE my_table;

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

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

DML — это набор команд, с помощью которых получают и меняют данные в таблице.

Рассмотрим основные команды DML.

SELECT

С помощью SELECT можно «попросить» базу данных показать нужные данные: всю таблицу или столбцы и строки с определенным условием. Пример:

SELECT user_id, username FROM table_name;

В результате получите таблицу с двумя колонками (user_id и username) и всеми строками, которые есть в table_name.

Если хотите получить все столбцы таблицы, поставьте звездочку *:

SELECT*FROM table_name;

Еще можно добавить условия — так увидите только те записи, которые нужны:

SELECT*FROM users WHERE username = 'Ivan';

Команда вернет пользователей с именем Ivan.

INSERT INTO

INSERT INTO добавляет новые записи в таблицу. Например, нового пользователя, товар или заказ:

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

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

INSERT INTO table_name VALUES(1, 'Ivan Petrov', NULL);

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

DELETE

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

DELETE FROM table_name WHERE username = 'nick';

Если не указать WHERE, вы удалите все записи из таблицы, поэтому будьте осторожны и всегда проверяйте условия:

DELETE FROM table_name; -- удалит всё!

Важно: удаленные строки восстановить нельзя. Лучше заранее сделайте копию.

UPDATE

UPDATE меняет данные в таблице:

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

Всегда используйте условие WHERE — так вы измените только нужные записи. Если опустить WHERE, то поменяете все строки таблицы.

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

DCL — это команды SQL, которые дают или забирают доступ у пользователей базы данных.

GRANT

С помощью GRANT администратор базы данных разрешает пользователям читать данные, добавлять новые записи, менять или удалять информацию:

GRANT INSERT ON my_table TO user;

С GRANT легко контролировать, кто и что делает с данными.

REVOKE

REVOKE забирает доступ у пользователя:

REVOKE INSERT ON my_table TO user;

REVOKE — важная команда для тех, кто следит за безопасностью базы.

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

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

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

Основные команды TCL: BEGIN — начать транзакцию, COMMIT — сохранить изменения, ROLLBACK — отменить изменения, если произошла ошибка.

BEGIN/COMMIT

BEGIN/COMMIT объединяют несколько SQL-запросов в одну транзакцию.

BEGIN начинает, а команда COMMIT завершает транзакцию и сохраняет все изменения.

Например, банковский перевод:

BEGIN;
UPDATE my_table SET balance = balance - 100 WHERE user_id = 10;
UPDATE my_table SET balance = balance + 100 WHERE user_id = 20;
COMMIT;

Если в процессе появится ошибка, например вторая строка не выполняется, и нет COMMIT, то изменения не сохранятся. Это защищает от ситуации, когда деньги списались, но не пришли получателю. Всё либо проходит полностью, либо не проходит совсем.

ROLLBACK

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

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

BEGIN;
UPDATE my_table SET balance = balance - 100 WHERE user_id = 10;
UPDATE my_table SET balance = balance + 100 WHERE user_id = 20;
COMMIT;

После ROLLBACK оба обновления не сохранятся, как будто ничего не произошло.

Все эти команды вы изучите на курсе «Аналитик данных». Опытные преподаватели расскажут обо всём подробно и простыми словами. Если что-то не поймете, сможете задать вопрос в общем чате или лично преподавателю. Вы не останетесь один на один со сложными терминами или заданием. С вами на связи всегда будет куратор, который поддержит и поможет не бросить всё на полпути. После учебы в Skypro получите диплом. Специалисты центра карьеры подскажут, как составить резюме и успешно пройти собеседование.

Пример использования SQL-запросов

В базе данных интернет-магазина есть три таблицы:

• Products — товары.
• Customers — покупатели.
• Orders — заказы.

Вот какие запросы можно применить:

• Посмотреть все товары в магазине.
  Команда SELECT * FROM:

  SELECT * FROM products;</li>
  

  Показывает весь список товаров: названия, цены, описание.

• Найти товары дороже 1000 ₽.
  Команда SELECT ... WHERE — выбирает только те строки, которые соответствуют условию:

  SELECT name, price FROM products
  WHERE price > 1000;
  

  Показывает товары, у которых цена больше 1000 ₽.

• Добавить нового покупателя.
  Команда INSERT INTO — добавляет новую запись в таблицу:

  INSERT INTO customers (name, email)
  VALUES ('Мария Иванова', 'maria@example.com');
  

  Добавляет покупателя с именем и email.

• Изменить email покупателя.
  Команда UPDATE ... SET ... WHERE — обновляет данные в таблице:

   	
  UPDATE customers
  SET email = 'masha2024@example.com'
  WHERE name = 'Мария Иванова';
  

  Обновит email Марии Ивановой.

• Сделать заказ.
  Снова команда INSERT INTO — добавляет новые строки (записи):

   
   	<li>INSERT INTO orders (customer_id, product_id, quantity)
  VALUES (1, 3, 2);
  

  Покупатель customer_id = 1 заказал 2 штуки товара product_id = 3.

• Удалить товар, которого больше не будет в продаже.
  Команда DELETE FROM ... WHERE — удаляет строки из таблицы по условию:

   
  DELETE FROM products
  WHERE name = 'Старый смартфон X1';
  

  Удаляет из таблицы товар с определенным названием.

• Посмотреть все заказы клиента.
  Команда SELECT ... WHERE — фильтрует заказы клиентов:

   
  SELECT * FROM orders
  WHERE customer_id = 1;
  

  Показывает все заказы, которые сделал покупатель с ID 1.

• Посчитать заказы.
  Команда SELECT COUNT(*) — считает количество строк в таблице:

   
  SELECT product_id, COUNT(*) AS total_orders
  FROM orders
  GROUP BY product_id;
  

  Покажет, сколько раз заказали каждый товар.

• Посмотреть, кто и что заказывал.
  Команда JOIN — соединяет данные из нескольких таблиц:

   
  SELECT c.name AS customer_name, p.name AS product_name
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
  JOIN products p ON o.product_id = p.id;
  

  Показывает, кто из клиентов какие товары заказывал.

Главное о DDL, DML, DCL и TCL в языке SQL

• DDL, DML, DCL и TCL — четыре группы SQL-запросов.
• DDL — создает и меняет структуру базы данных.
• DML — меняет данные в таблице.
• DCL — дает или забирает доступ к базе данных.
• TCL — управляет транзакциями.
• Если знаете основные SQL-команды, быстрее разберетесь, как работать с базой данных.