Анализ данных • 01 июля 2024 • 5 мин чтения

SQL-запросы: основные команды для управления базами данных

Язык программирования SQL — Structured Query Language предназначен для управления базами данных. Чтобы обрабатывать данные из базы, нужно использовать SQL-запросы. Рассказываем, какие они бывают.

Виды SQL-запросов

SQL используют для управления реляционными базами данных (БД) — в них данные представлены в виде взаимосвязанных таблиц. Существует 4 вида SQL-запросов, которые также называют подмножествами языка SQL. Каждый из них выполняет определённые действия с БД или данными, и в каждом есть свой набор запросов:

● DDL — Data Definition Language. Язык определения и управления структурой БД. С помощью таких запросов можно создать БД с нуля, описать её структуру и настроить правила расположения информации в ней. Изменения, совершённые с помощью DDL-запросов, обычно постоянны и фиксируются автоматически — auto-commit.

● DML — Data Manipulation Language. Язык для проведения операций с данными в БД. SQL-запросы этого подмножества помогают изменять информацию в базе — добавить, обновить, выбрать, извлечь или удалить данные. DML-запросы манипулируют данными, но при этом не изменяют структуру таблиц в БД.

● DCL — Data Control Language. Язык управления доступом к объектам БД. Такие запросы позволяют предоставить или отозвать права доступа для определённых пользователей. С помощью DCL-запросов обеспечивают безопасность информации, которая хранится в БД.

● TCL — Transaction Control Language. Язык управления транзакциями в БД. Транзакциями называют последовательность нескольких DML-запросов, которые выполняются как единое целое и не выполняются, если одна из операций не совершена. Например, с помощью транзакций можно изменить сразу несколько объектов в БД. TCL-запросы обеспечивают целостность и согласованность данных при выполнении нескольких операций, позволяя откатить изменения в случае ошибок.

Простые и сложные запросы SQL

В зависимости от структуры и функциональности SQL-запросы можно поделить на простые и сложные:

1. Простые запросы SQL выполняют одну основную функцию и обычно затрагивают только одну таблицу с данными в БД. У них простая структура с минимальным количеством условий и операций без вложенных подзапросов и объединений. Эти свойства дают высокую производительность — на выполнение простых SQL-запросов уходит минимум времени.

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

Этот простой запрос с помощью команды SELECT извлечёт все столбцы — это обозначается символом * — из таблицы с названием goods

2. Сложные запросы SQL выполняют несколько операций и могут иметь сложную структуру с объединениями — JOIN, вложенными подзапросами и другими конструкциями. Такие запросы могут обращаться сразу к нескольким таблицам в БД и использовать сложные условия. Из-за более сложной структуры производительность операций снижается и выполняются они дольше, чем простые SQL-запросы.

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

Этот сложный запрос возвращает названия товаров — name, и их цены — price, продающихся в отделе одежды — Cloth, сортируя их по цене в порядке убывания с помощью команды ORDER BY и модификатора DESC

Разобраться в простых и сложных SQL-запросах поможет курс «SQL для работы с данными и аналитики». После обучения на курсе вы сможете уверенно использовать SQL в работе с базами данных и получите навыки работы с PostgreSQL, ClickHouse и другими инструментами.

Структура запроса в SQL

Слова и символы в SQL-запросах записываются последовательно в определённом порядке. Если запрос включает в себя все ключевые команды, их стандартный порядок будет таким:

1. SELECT — указывает столбцы таблицы, которые нужно выбрать,
2. FROM — указывает название таблицы, в которой находятся эти столбцы,
3. JOIN — объединяет несколько таблиц,
4. WHERE — фильтрует строки до выполнения группировки,
5. GROUP BY — группирует строки по указанным столбцам,
6. HAVING — фильтрует получившиеся после группировки группы,
7. ORDER BY — сортирует результат запроса,
8. LIMIT — ограничивает количество возвращаемых строк.

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

Так выглядит шаблон стандартного SQL-запроса

В шаблоне стандартного SQL-запроса содержатся:

● column1, column2, ... — названия столбцов,
● table1, table2 — названия используемых таблиц,
● condition — условие для выполнения команды фильтрации строк WHERE и команды фильтрации групп HAVING,
● ASC|DESC — один из модификаторов команды группировки ORDER BY. DESC — в порядке убывания, ASC — в порядке возрастания,
● number — предельное число возвращаемых командой LIMIT строк.

Для удобства чтения SQL-запросы можно структурировать по строкам, как в примере, а можно записывать в одну строку, сохраняя стандартную последовательность.

Агрегатные функции

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

Основные агрегатные функции:

1. COUNT(*) — возвращает количество строк в наборе данных,
2. SUM(column) — возвращает сумму значений указанного столбца,
3. AVG(column) — возвращает среднее значение в указанном столбце,
4. MAX(column) — возвращает максимальное значение в указанном столбце,
5. MIN(column) — возвращает минимальное значение в указанном столбце.

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

Например:

SELECT department, AVG(price) AS average_price
FROM goods
GROUP BY department;

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

Слово AS используется для присвоения псевдонимов — alias — столбцам и таблицам в запросах. В примере возвращаемый столбец получает псевдоним average_price — в нём будет содержаться среднее значение данных из столбца department, сгруппированных по отделам. Псевдонимы упрощают работу с результатами запросов и их чтение, но использовать их не обязательно.

Вложенные подзапросы

Вложенные подзапросы в SQL — это запросы, которые находятся внутри другого запроса. Их также называют субзапросами. Они используются для выполнения операций, которые зависят от результатов другого запроса. Подзапросы могут быть включены в разные части SQL-запросов.

● Подзапросы в SELECT: используются для вычисления значений, которые будут возвращены как часть результирующего набора.
● Подзапросы в FROM: используются для создания временной таблицы, которая может быть использована в основном запросе.
● Подзапросы в WHERE: используются для фильтрации строк на основе условий, зависящих от другого запроса.
● Подзапросы в HAVING: используются для фильтрации сгруппированных данных.
● Подзапросы в JOIN: используются для создания виртуальных таблиц, которые затем объединяются с другими таблицами.
● Подзапросы в INSERT: используются для вставки данных, полученных из другого запроса.
● Подзапросы в UPDATE: используются для изменения данных на основе результатов другого запроса.
● Подзапросы в DELETE: используются для удаления данных на основе условий, зависящих от другого запроса.

Пример:

SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.goods_id = goods.id) AS order_count
FROM goods;

Этот запрос возвращает названия товаров и количество их заказов. Вложенным подзапросом является код — SELECT COUNT(*) FROM orders WHERE orders.goods_id = goods.id

Примеры SQL-запросов

Представим таблицу goods с информацией о товарах в онлайн-магазине и создадим несколько примеров SQL-запросов. В таблице есть столбцы:

● name — название товара,
● price — цена товара,
● department — отдел,
● orders — количество заказов.

1. Извлечение данных из таблицы goods:

SELECT * FROM goods;

2. Извлечение столбцов с названиями и ценами товара:

SELECT name, price
FROM goods

3. Извлечение товаров с условием, что они продаются в отделе одежды (Cloth):

SELECT name, price
FROM goods
WHERE department = 'Cloth';

4. Извлечение товаров, отсортированных по цене в порядке убывания:

SELECT name, price
FROM goods
ORDER BY price DESC;

5. Подсчёт количества товаров в каждом отделе с помощью агрегатной функции COUNT:

SELECT department, COUNT(*) AS total_goods FROM goods GROUP BY department;

6. Извлечение товаров, цена которых выше средней цены всех товаров, с помощью подзапроса:

SELECT name, price
FROM goods
WHERE price > (SELECT AVG(price) FROM goods);

7. Извлечение товаров, у которых общее количество заказов больше 100:

SELECT name, orders
FROM goods
WHERE orders > 100;

8. Добавление нового товара с названием shirt с ценой 19,99 из отдела Cloth в таблицу goods:

INSERT INTO goods (name, price, department, orders)
VALUES ('Shirt', 19.99, 'Cloth', 0);

9. Обновление цены товара с названием skirt до 20.00 в таблице goods:

UPDATE goods
SET price = 20.00
WHERE name = 'skirt;

10. Удаление товара с названием skirt из таблицы goods:

DELETE FROM goods
WHERE name = 'skirt';

Заключение

SQL-запросы помогают управлять реляционными базами данных, то есть теми, которые представлены в виде таблиц. Существует четыре типа запросов, которые называют подмножествами языка SQL. С их помощью можно создать базу данных, проводить операции над значениями в ней, управлять доступом к БД и транзакциями. Простые запросы используют одну таблицу и базовые команды, а сложные могут использовать агрегатные функции, вложенные подзапросы и другие условия.

Статью подготовили: