SQL (Structured Query Language) — это язык запросов, с помощью которого можно управлять данными в реляционных базах данных (БД). SQL-запросы состоят из операторов — специальных символов или ключевых слов, которые формируют команды. Разберемся, из чего состоят запросы SQL и как их писать.

Содержание скрыть
1 Базовые операторы
1.1 Операторы сравнения
1.2 Арифметические операторы
1.3 Логические операторы AND, OR и NOT
2 Структура SQL-запроса
3 Категории команд в SQL
3.1 Data Query Language (DQL) — язык запросов
3.2 SELECT и FROM
3.3 SQL-агрегатные функции (COUNT, SUM, AVG, MAX, MIN)
3.4 Оператор AS
3.5 WHERE
3.6 Логические операторы BETWEEN, LIKE, IN, IS NULL
3.7 GROUP BY
3.8 HAVING
3.9 ORDER BY
3.10 Ограничительные конструкции LIMIT и OFFSET
4 Data Definition Language (DDL) — язык определения данных
4.1 CREATE
4.2 DROP
4.3 ALTER
4.4 TRUNCATE
4.5 RENAME
4.6 COMMENT
5 Data Manipulation Language (DML) — язык манипулирования данными
5.1 INSERT
5.2 UPDATE
5.3 DELETE
6 Data Control Language (DCL) — язык управления данными
6.1 GRANT
6.2 REVOKE
7 Transaction Control Language (TCL) — язык управления транзакциями
7.1 COMMIT
7.2 ROLLBACK
7.3 SAVEPOINT
7.4 RELEASE SAVEPOINT
7.5 SET TRANSACTION
8 Внешние и внутренние запросы
8.1 Внутренний запрос (подзапрос)
8.2 Внешний запрос
8.3 Работа с внешними и внутренними запросами с использованием оператора EXISTS
9 Примеры использования команд SQL
9.1 Создание и удаление БД
9.2 Создание и управление таблицами
9.3 Ограничение целостности

Базовые операторы

Перед изучением структуры SQL-запроса и команд познакомимся с операторами сравнения, арифметическими и логическими операторами, которые понадобятся для работы с запросами.  

Операторы сравнения

Операторы
Назначение
= Проверяет равенство двух значений
<, > Проверяют, меньше или больше одно значение другого
<=, >= Проверяют, меньше или равно или больше или равно одно значение другому
!= или <> Проверяют, не равны ли два значения

Арифметические операторы

Операторы
Назначение
+ Сложение значений
— Вычитание одного значения из другого
* Умножение значений
/ Деление одного значения на другое
% Модуль. Возвращает остаток от деления одного значения на другое

Логические операторы AND, OR и NOT

AND возвращает TRUE, если оба условия истинны, иначе — FALSE. В некоторых реализациях SQL (например, PostgreSQL) можно использовать ||.

OR возвращает TRUE, если хотя бы одно из условий истинно, иначе — FALSE. В PostgreSQL допускается обозначение ~.

NOT — инвертирует значение условия (делает истинное значение ложным и наоборот).

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

Запрос должен быть правильно сформулирован, чтобы система управления базами данных (СУБД) смогла его обработать. 

Для этого используют структуру SQL-запроса, которая состоит из обязательных операторов — SELECT и FROM, а также опциональных: WHERE, GROUP BY, HAVING и ORDER BY. Эти операторы относятся к категории команд Data Query Language (DQL).

строение SQL запроса
Пример структуры запроса. Источник

Для запросов SQL не критично, написаны они в одну строку или в столбик. Главное, чтобы запрос был корректным. Однако для повышения читаемости длинные запросы целесообразно форматировать в столбик.

Категории команд в SQL

категории команд SQL
Источник — рисунок в Aseprite

Data Query Language (DQL) — язык запросов

Операторы этой категории используются для извлечения данных из БД, их сортировки и группировки. 

Если баз данных несколько, а для работы нужна конкретная БД, используется оператор USE:

USE database_name;

Запрос установит БД database_name в качестве активной. Все последующие запросы SQL будут выполнены для нее.

SELECT и FROM

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

где:

column1, column2, … — названия столбцов, которые нужно извлечь (можно использовать * для выбора всех столбцов);

table_name — имя таблицы, из которой нужно извлечь данные.

  • Запрос для извлечения всех данных из таблицы:

  • Запрос для извлечения определенных столбцов из таблицы:

SQL-агрегатные функции (COUNT, SUM, AVG, MAX, MIN)

Используются для выполнения вычислений над наборами значений и возвращения единственного результирующего значения.

COUNT вычисляет количество строк в результирующем наборе данных.

  • Вернуть количество строк в таблице:

SUM вычисляет сумму значений в указанном столбце.

  • Вернуть общую сумму зарплат всех сотрудников:

AVG вычисляет среднее значение из указанного столбца.

  • Вернуть средний возраст работников:
SELECT AVG(age)
FROM employees;

MAX возвращает максимальное значение из указанного столбца.

  • Вернуть максимальную цену из всех продуктов:
SELECT MAX(price)
FROM products;

MIN возвращает минимальное значение из указанного столбца.

  • Вернуть минимальное количество товаров в наличии:
SELECT MIN(stock)
FROM inventory;

Оператор AS

Задает более читабельный псевдоним столбцу или таблице.

  • Присваиваем псевдоним для результата функции:
SELECT SUM(salary) AS total_salary
FROM employees;

где:

SUM(salary) — агрегатная функция, которая вычисляет общую сумму значений в столбце salary;

AS total_salary задает псевдоним total_salary результату функции SUM.

WHERE

Этот оператор определяет, над какими данными будут производиться операции. Условия выбора целевых данных должны быть прописаны в предикатах — выражениях, которые оценивают значения как TRUE, FALSE или UNKNOWN.

SELECT column1, column2, …
FROM table_nameWHERE condition;

где:

condition — условие (предикат), которому должны соответствовать данные.

  • Например, так можно реализовать запрос на выборку сотрудников с зарплатой выше 50 000:
 
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;

Логические операторы BETWEEN, LIKE, IN, IS NULL

BETWEEN

Нужен для выбора значений в пределах заданного диапазона.

  • Выборка всех сотрудников с зарплатой в диапазоне от 50 000 до 80 000:
SELECT first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 80000;

LIKE

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

  • Выборка всех клиентов, у которых имя начинается с буквы «A»:
SELECT first_name, last_name
FROM customers
WHERE first_name LIKE ‘A%’;

IN

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

  • Выборка всех продуктов из категорий «Электроника» и «Одежда»:
SELECT product_name, category
FROM products
WHERE category IN (‘Electronics’, ‘Clothing’);

IS NULL

Нужен для выбора строк, в которых отсутствует значение столбца (является NULL).

  • Выборка всех клиентов, у которых не заполнен номер телефона:
SELECT first_name, last_name
FROM customers
WHERE phone_number IS NULL;

GROUP BY

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

SELECT column1, column2, …, aggregate_function(column)
FROM table_name
GROUP BY column1, column2, …;

где:

column1, column2, … — столбцы, по которым нужно сгруппировать данные;

aggregate_function(column) — агрегатная функция, применяемая к столбцам для каждой группы.

  • Найти общее количество продуктов в каждой категории:
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;
  • Вычислить общую сумму продаж для каждого месяца:
SELECT MONTH(sale_date) AS month, SUM(sale_amount) AS total_sales
FROM sales
GROUP BY MONTH(sale_date);

HAVING

Применяют для фильтрации результатов запроса, которые были сгруппированы с использованием оператора GROUP BY.

SELECT column1, column2, …, aggregate_function(column)
FROM table_nameGROUP BY column1, column2, …
HAVING condition;

где:

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

  • Выборка категорий продуктов, среднее количество которых превышает 50:
SELECT category, AVG(quantity) AS avg_quantity
FROM products
GROUP BY category
HAVING AVG(quantity) > 50;
  • Выборка клиентов, у которых стоимость среднего заказа выше 1000:
SELECT customer_id, AVG(order_amount) AS avg_order_amount
FROM orders
GROUP BY customer_id
HAVING AVG(order_amount) > 1000;

ORDER BY

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

SELECT column1, column2, …
FROM table_name
ORDER BY column1 [ASC], column2 [ASC], …;

где:

ASC (или DESC) — необязательное ключевое слово, которое определяет порядок сортировки. По умолчанию используется ASC (порядок возрастания), но можно указать DESC (порядок убывания).

  • Сортировка заказов по дате в порядке убывания:
SELECT order_id, order_date, customer_id
FROM orders
ORDER BY order_date DESC;

Ограничительные конструкции LIMIT и OFFSET

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

LIMIT

Определяет количество строк, которые нужно вернуть. Если оно равно нулю, запрос возвращает пустой набор результатов.

SELECT column1, column2, …
FROM table_nameLIMIT number_of_rows;

где:

number_of_rows — количество строк, которые нужно вернуть. Если значение равно нулю, запрос вернет пустой набор результатов.

OFFSET

Указывает, сколько строк нужно пропустить перед возвратом результата. Если не определять OFFSET, запрос возвращает данные, начиная с первой строки, указанной в SELECT.

SELECT column1, column2, …
FROM table_name
OFFSET number_of_rows_to_skip;

где:

number_of_rows_to_skip — количество строк, которые нужно пропустить перед возвратом результата.

Операторы LIMIT и OFFSET лучше всего использовать вместе с ORDER BY. Это упорядочит возвращаемый результат.

Data Definition Language (DDL) — язык определения данных

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

CREATE 

Используется для создания БД и ее объектов.

CREATE DATABASE — создание БД.

CREATE DATABASE my_database;

CREATE TABLE — создание таблицы.

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE
);

где:

name VARCHAR(50) создает столбец с именем name, который будет содержать строковые значения (VARCHAR) длиной не более 50 символов;

date DATE создает столбец с именем date, который будет содержать даты.

CREATE INDEX — создание индекса.

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

CREATE INDEX idx_last_name ON employees(last_name);

Оператор ON указывает на то, что индекс будет создан на столбце last_name таблицы last_name.

CREATE VIEW — создание представления.

Представление (view) — это виртуальная таблица, основанная на результате запроса. Представления не хранят данные самостоятельно, они определяются SQL-запросами, которые извлекают данные из одной или нескольких таблиц.

CREATE VIEW employee_view AS
SELECT first_name, last_name
FROM employees;

CREATE SCHEMA — создание схемы.

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

CREATE SCHEMA my_schema;

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

CREATE TABLE my_schema.my_table

CREATE TRIGGER — создание триггера.

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

CREATE TRIGGER trigger_name
BEFORE INSERT
ON table_name
FOR EACH ROW
—тело триггера (код, который будет выполнен после срабатывания триггера)

где:

BEFORE (или AFTER) — указание времени срабатывания триггера (до или после выполнения операции);

INSERT (или UPDATE, DELETE) — определение операции, до или после которой сработает триггер;

ON table_name — указание таблицы, к которой привязан триггер;

FOR EACH ROW (или STATEMENT) — условие, будет ли триггер выполняться для каждой строки (FOR EACH ROW) или для каждого оператора (FOR EACH STATEMENT). Эта часть синтаксиса может отсутствовать в некоторых СУБД.

CREATE PROCEDURE

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

Создание процедуры

CREATE PROCEDURE get_employee_count()
BEGIN
SELECT COUNT(*)
FROM employees;
END;

Оператор BEGIN обозначает начало транзакции (совокупности операций), а END — ее завершение.

Вызов процедуры

CALL get_employee_count();

CALL вызывает набор инструкций из процедуры или функции.

DROP 

Используется для удаления объектов.

DROP TABLE my_table;

Аналогично DROP можно применять и с другими объектами, в том числе и с БД.

ALTER 

Используется для изменения структуры существующих объектов БД.

ALTER TABLE my_table
ADD COLUMN age INT;

Добавляем столбец (COLUMN) с именем age и форматом данных INT.

TRUNCATE 

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

TRUNCATE TABLE my_table;

RENAME 

Оператор для переименования объектов БД.

RENAME TABLE old_table TO new_table;

Оператор TO указывает на новое значение (новое имя или местоположение).

COMMENT

Используется для добавления комментариев к объектам БД.

COMMENT ON TABLE employees IS ‘Таблица для хранения информации о сотрудниках’

Оператор IS указывает на объект команды. В данном случае — на текст, который будет являться комментарием к таблице.

Data Manipulation Language (DML) — язык манипулирования данными

Используется для работы с данными внутри таблиц. К DML относятся операторы INSERT, UPDATE и DELETE. Сюда можно также отнести SELECT и FROM, но они являются частью DQL.

INSERT

Добавляет новые строки данных в таблицу.

INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, …);

где:
INTO указывает место, куда нужно поместить данные;

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

UPDATE

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

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

где:

SET — оператор для присвоения значения переменной (в данном случае столбцам).

DELETE

Удаляет строки данных из таблицы.

DELETE FROM table_nameWHERE condition;

Data Control Language (DCL) — язык управления данными

Используется для управления правами доступа к данным и контролем над БД.

GRANT

Предоставляет пользователю или роли определенные привилегии на объект БД.

Роль можно создать с помощью команды CREATE ROLE role_name. Вместо того чтобы назначать привилегии отдельным пользователям, их можно назначать ролям.

GRANT SELECT, INSERT
ON employees
TO user1;

Пользователь user1 получает привилегии SELECT и INSERT на таблицу employees.

REVOKE

Отменяет определенные привилегии у пользователя или роли на объект БД.

REVOKE SELECT, INSERT
ON employees
FROM user1;

У пользователя user1 отзываются привилегии SELECT и INSERT на таблицу employees.

Transaction Control Language (TCL) — язык управления транзакциями

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

COMMIT

Фиксирует все изменения, сделанные в рамках текущей транзакции. После выполнения команды COMMIT все изменения становятся видимыми для других пользователей.

ROLLBACK

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

SAVEPOINT

Создает точку сохранения внутри транзакции, к которой можно откатиться без отката всей транзакции. 

RELEASE SAVEPOINT

Удаляет ранее созданную точку сохранения. После удаления точки сохранения к ней больше нельзя откатиться.

SET TRANSACTION

Устанавливает характеристики транзакции.

Здесь устанавливается уровень изоляции (ISOLATION LEVEL) самого высокого уровня — SERIALIZABLE. Уровни изоляции влияют на возможность других транзакций вносить изменения в те же данные.

Внешние и внутренние запросы

Внешние (основные) и внутренние запросы (подзапросы) позволяют выполнять один запрос внутри другого. Подзапрос выполняется первым, а его результат используется основным запросом.

SELECT name
FROM employees
WHERE department_id = (
SELECT id
FROM departments
WHERE name = ‘Sales’
);

Внутренний запрос (подзапрос)

SELECT id
FROM departments
WHERE name = ‘Sales’;

Этот запрос выполняется первым. Он находит id отдела, где name равно Sales. Допустим, результатом будет id = 3.

Внешний запрос

SELECT name
FROM employees
WHERE department_id = 3;

Внешний запрос использует результат подзапроса (id = 3) для фильтрации данных в таблице employees.

Он выбирает имена сотрудников, у которых department_id = 3.

Работа с внешними и внутренними запросами с использованием оператора EXISTS

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

SELECT name
FROM customers
WHERE EXISTS (
SELECT *
FROM orders
WHERE orders.customer_id = customers.id
);

Внешний запрос выбирает имена клиентов из таблицы customers.

Подзапрос проверяет, существует ли хотя бы один заказ для каждого клиента в таблице orders, используя условие orders.customer_id = customers.id.

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

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

Создание и удаление БД

Создание и управление таблицами

Создаем таблицу employees:

 

Добавляем столбец email:

ALTER TABLE employees ADD COLUMN email VARCHAR(100);

Изменяем тип столбца (MODIFY COLUMN) на INT:

ALTER TABLE employees MODIFY COLUMN age INT UNSIGNED;

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

Ограничение целостности

Операции ограничения целостности применяются для обеспечения точности и надежности данных в таблице.

Создаем структуру таблицы для хранения информации о заказах в БД.

 

Создает столбец id типа INT, который будет автоматически увеличиваться для каждой новой записи. Он также определяется как первичный ключ (PRIMARY KEY), что гарантирует уникальность каждой записи в таблице.

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

Создает столбец quantity типа INT, который будет содержать количество продуктов в заказе.

Устанавливает ограничение внешнего ключа (FOREIGN KEY) на столбец product_id, который ссылается на столбец id в таблице products. Это гарантирует целостность данных: значение product_id в таблице orders будет соответствовать существующему id в таблице products.

Устанавливает условие проверки (CHECK), которое гарантирует, что значение в столбце quantity всегда будет больше нуля. Это запретит добавление записей с некорректными значениями количества продуктов.

 

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *