SQL запросы в примерах
Сегодня покажу основные SQL запросы, которые могут пригодиться. Начнем с самого начала, то есть с создания базы данных и пользователя. Далее создадим структуры и таблицы с исходными данными. И последним блоком данной статьи будут запросы, начиная от простых выборок данных и заканчивая усложненными.
Содержание
Исходные данные
Таблица sample_users_table в базе данных sample_db.
| id | full_name | age | date_of_birth | |
| 1 | Иванов Иван | 25 | 12-02-1995 | |
| 2 | Иванов Петр | 37 | 15-06-1983 | |
| 3 | Левин Иван | 25 | 12-02-1995 | |
| 4 | Левин Петр | 29 | 07-04-1991 | |
| 5 | Смирнов Петр | 19 | 26-08-2001 | |
| 6 | Сидоров Илья | 33 | 10-04-1987 | |
| 7 | Андреев Игорь | 30 | 16-01-1990 |
Таблица sample_paid_table в базе данных sample_db.
| id | user_id | paid_date | paid_total | |
| 1 | 1 | 18-02-2020 | 5000 | |
| 2 | 1 | 14-02-2020 | 10000 | |
| 3 | 4 | 09-02-2020 | 1000 | |
| 4 | 3 | 15-02-2020 | 6000 | |
| 5 | 1 | 11-02-2020 | 3000 | |
| 6 | 4 | 08-02-2020 | 4000 | |
| 7 | 1 | 12-02-2020 | 9000 | |
| 8 | 4 | 13-01-2020 | 25000 | |
Создание MySQL базы данных и пользователей
Для создания базы данных я использую подключение к серверу по ssh с последующим входом в консоль управления mysql.
Создать базу данных sample_db в MySQL (MariaDB):
mysql> CREATE DATABASE sample_db;
Query OK, 1 row affected (0.02 sec)Создать пользователя sample_user с паролем sample_pass с возможностью подключения с любого IP по сети:
mysql> CREATE USER 'sample_user'@'%' IDENTIFIED BY 'Sample1_pass';
Query OK, 0 rows affected (0.03 sec)Добавим полные права пользователю sample_user на базу данных sample_db:
mysql> GRANT ALL PRIVILEGES ON sample_db.* TO 'sample_user'@'%';
Query OK, 0 rows affected (0.16 sec)Подготовка окончена. Теперь можно воспользоваться программой для работы с базой данных, например DBeaver или MySQL Workbench. Я использую MySQL Workbench.
Создание таблиц с исходными данными
Для начала нужно создать структуру таблиц, то есть определить имена и типы столбцов. Создаем таблицы sample_users_table и sample_paid_table в базе данных sample_db:
-- удалить таблицу если существует
DROP TABLE IF EXISTS sample_db.sample_users_table;
-- создать таблицу со структурой
CREATE TABLE sample_db.sample_users_table(
id INT,
full_name VARCHAR(100),
age SMALLINT,
date_of_birth DATE
);
DROP TABLE IF EXISTS sample_db.sample_paid_table;
CREATE TABLE sample_db.sample_paid_table(
id INT,
user_id INT,
paid_date DATE,
paid_total INT
);Результат:
0 row(s) affected, 1 warning(s): 1051 Unknown table 'sample_db.sample_users_table'
0 row(s) affected
0 row(s) affected, 1 warning(s): 1051 Unknown table 'sample_db.sample_paid_table'
0 row(s) affectedПосле создания таблицы можно в нее записать данные. Таблица sample_users_table:
-- вставить записи в таблицу
INSERT INTO sample_db.sample_users_table(id, full_name, age, date_of_birth)
VALUES
(1,'Иванов Иван',25,'1995-02-12'),
(2,'Иванов Петр',37,'1983-06-15'),
(3,'Левин Иван',25,'1995-02-12'),
(4,'Левин Петр',29,'1991-04-07'),
(5,'Смирнов Петр',19,'2001-08-26'),
(6,'Сидоров Илья',33,'1987-04-10'),
(7,'Андреев Игорь',30,'1990-01-16');7 row(s) affected Records: 7 Duplicates: 0 Warnings: 0Таблица sample_paid_table в базе данных sample_db:
INSERT INTO sample_db.sample_paid_table(id, user_id, paid_date, paid_total)
VALUES
(1,1,'2020-02-18',5000),
(2,1,'2020-02-14',10000),
(3,4,'2020-02-09',1000),
(4,3,'2020-02-15',6000),
(5,1,'2020-02-11',3000),
(6,4,'2020-02-08',4000),
(7,1,'2020-02-12',9000),
(8,4,'2020-01-13',25000);8 row(s) affected Records: 8 Duplicates: 0 Warnings: 0Подготовка исходных данных завершена.
Простые SQL запросы
Выбрать все данные из таблицы sample_users_table:
use sample_db;
select * from sample_users_table;
-- равноценно --
select * from sample_db.sample_users_table;Выбрать только столбцы id и full_name из таблицы sample_users_table:
use sample_db;
select id,full_name from sample_users_table;
-- равноценно --
select id,full_name from sample_db.sample_users_table;Получить все данные из таблицы sample_users_table для пользователей, чей возраст более 25 лет. Вывод отсортировать по возрастанию возраста:
use sample_db;
select *
from sample_users_table
where age > 25
order by age asc;
-- равноценно --
select *
from sample_db.sample_users_table
where age > 25
order by age asc;Вывести только уникальных пользователей из таблицы sample_users_table.
За это отвечает оператор GROUP BY. Он отберет только уникальные записи по столбцу sut.id. В данном запросе это бессмысленно, так как id подразумевают уникальность. Это пригодится чуть дальше. Также в блоке FROM добавлен оператор AS для создания ссылки, то есть, что бы не писать sample_users_table, можно обратиться через sut. В случае, если запрос написан для одной таблицы, в этом нет необходимости, но когда в одном запросе несколько таблиц, обработчик должен понимать, в какой таблице ему искать данные. Также пригодится чуть дальше:
use sample_db;
SELECT
sut.id,
sut.full_name
FROM sample_users_table AS sut
GROUP BY sut.id,sut.full_name
ORDER BY sut.id ASC;| id | full_name | |
| 1 | Иванов Иван | |
| 2 | Иванов Петр | |
| 3 | Левин Иван | |
| 4 | Левин Петр | |
| 5 | Смирнов Петр | |
| 6 | Сидоров Илья | |
| 7 | Андреев Игорь |
Сложные SQL запросы
Выбрать общую сумму выплат для каждого пользователя
В данном запросе будут участвовать две таблицы. Пользователи берутся из sample_users_table, а выплаты из sample_paid_table. За создание связи между таблицами отвечает JOIN.
Для начала нужно вывести только уникальных пользователей из таблицы sample_users_table. Как это сделать описано чуть выше. Теперь к полученным данным необходимо пристыковать данные из таблицы sample_paid_table таким образом, чтобы данные из столбца paid_total суммировались для каждого пользователя:
use sample_db;
SELECT
sut.id,
sut.full_name,
SUM(spt.paid_total) AS total_paid_all
FROM sample_users_table AS sut
LEFT JOIN sample_paid_table AS spt ON spt.user_id=sut.id
GROUP BY sut.id, sut.full_name
ORDER BY sut.id ASC;| id | full_name | total_paid_all | |
| 1 | Иванов Иван | 27000 | |
| 2 | Иванов Петр | ||
| 3 | Левин Иван | 6000 | |
| 4 | Левин Петр | 30000 | |
| 5 | Смирнов Петр | ||
| 6 | Сидоров Илья | ||
| 7 | Андреев Игорь |
От предыдущего данный запрос отличается всего двумя строками.
LEFT JOIN sample_paid_table AS spt ON spt.user_id=sut.idДобавляет связь между таблицами sample_paid_table (spt) и sample_users_table (sut). LEFT JOIN говорит, что основная таблица — это таблица, указанная в FROM, и к ее данным будут пристыковываться данные из spt. Поля для выстраивания связей в sut это id, то есть уникальный идентификатор пользователя, и в spt user_id, что также соответствует id пользователя.
SUM(spt.paid_total) AS total_paid_allСуммирует столбец paid_total. За это отвечает функция SUM. Обратите внимание, что для суммирования необходим групповой оператор, в данном случае GROUP BY sut.id. Иначе обработчик не поймет по какому принципу определить данные, то есть какие строки нужно суммировать. В данном случае обработчик, проходя по записям, если будет встречать уже существующий user_id, просуммирует к нему поле paid_total.
Кстати, функция SUM является агрегирующей, то есть объединяет несколько строк. Так как sut.full_name не обернут агрегирующей функцией, его нужно явно добавить в GROUP BY, иначе Mysql сгенерирует ошибку:
Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘sample_db.sut.full_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.
Из sql_mode параметр ONLY_FULL_GROUP_BY убирать нежелательно, так как это может замедлить выполнение групповых запросов.
Получить id пользователя, сумму и дату первой выплаты
С виду задача кажется простой: группируем по user_id, сортируем по дате и выбираем минимальную. Но не все так просто, так как поле paid_total в этом случае выведется по той строке, которую оператор GROUP BY встретил первой.
Лучше эту задачу разбить на две части. Сначала напишем запрос, который для каждого пользователя выберет минимальную дату выплаты (то есть дату первого платежа):
use sample_db;
SELECT
user_id,
MIN(paid_date) AS MinDate
FROM sample_paid_table
GROUP BY user_id;| id | paid_date | |
| 1 | 2020-02-11 | |
| 3 | 2020-02-15 | |
| 4 | 2020-01-13 |
После этого с помощью INNER JOIN этот запрос пристыкуем к целевой таблице sample_paid_table. Таким образом, будут выбраны строки целиком из таблицы sample_paid_table, но только те, которые были найдены в первом запросе:
use sample_db;
SELECT
spt.user_id,
spt.paid_date,
spt.paid_total
FROM sample_paid_table AS spt
INNER JOIN
(SELECT
user_id,
MIN(paid_date) AS MinDate
FROM sample_paid_table
GROUP BY user_id) group_diff
ON spt.user_id = group_diff.user_id
AND spt.paid_date = group_diff.MinDate;| user_id | paid_date | paid_total | |
| 3 | 2020-02-15 | 6000 | |
| 1 | 2020-02-11 | 3000 | |
| 4 | 2020-01-13 | 25000 |
Получить сумму выплат на каждый день начиная с первой выплаты
Начать нужно с того, что сгенерировать список дат. Немного поискав в интернете, наткнулся на решение:
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
Данный скрипт сгенерирует даты начиная от сегодняшнего числа и заканчивая 9999 днями назад. Т.е. диапазон с 1994-01-17 по 2021-06-03. Источник скрипта: sql — generate days from date range — Stack Overflow
Далее сгенерируем диапазоны дат, в которые изменялись суммы:
SELECT
spt.user_id,
spt.paid_date as startDate,
(select tspt.paid_date
from sample_paid_table tspt
where tspt.paid_date > spt.paid_date AND tspt.user_id = spt.user_id
order by tspt.paid_date asc
limit 1
) endDate,
spt.paid_total,
(select sum(tspt.paid_total)
from sample_paid_table tspt
where tspt.paid_date <= spt.paid_date AND tspt.user_id = spt.user_id
) sum_paid_total
FROM sample_paid_table spt
ORDER BY spt.user_id, startDate ASC
| user_id | startDate | endDate | paid_total | sum_paid_total | |
| 1 | 2020-02-11 | 2020-02-12 | 3000 | 3000 | |
| 1 | 2020-02-12 | 2020-02-14 | 9000 | 12000 | |
| 1 | 2020-02-14 | 2020-02-18 | 10000 | 22000 | |
| 1 | 2020-02-18 | null | 5000 | 27000 | |
| 3 | 2020-02-15 | null | 6000 | 6000 | |
| 4 | 2020-01-13 | 2020-02-08 | 25000 | 25000 | |
| 4 | 2020-02-08 | 2020-02-09 | 4000 | 29000 | |
| 4 | 2020-02-09 | null | 1000 | 30000 |
Последним этапом объединим эти два запроса и получим желаемую таблицу:
select
rspt.user_id,
rspt.startDate,
rspt.endDate,
dr.Date,
rspt.sum_paid_total
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) as dr
JOIN (
SELECT
spt.user_id,
spt.paid_date as startDate,
(select tspt.paid_date
from sample_paid_table tspt
where tspt.paid_date > spt.paid_date AND tspt.user_id = spt.user_id
order by tspt.paid_date asc
limit 1
) endDate,
spt.paid_total,
(select sum(tspt.paid_total)
from sample_paid_table tspt
where tspt.paid_date <= spt.paid_date AND tspt.user_id = spt.user_id
) sum_paid_total
FROM sample_paid_table spt
ORDER BY spt.user_id, startDate ASC
) as rspt on dr.Date >= DATE(rspt.startDate) and (dr.Date < DATE(rspt.endDate) or rspt.endDate is null)
ORDER BY rspt.user_id, dr.Date ASC
| user_id | date | sum_paid_total | |
| 1 | 11.02.2020 | 3000 | |
| 1 | 12.02.2020 | 12000 | |
| 1 | 13.02.2020 | 12000 | |
| 1 | 14.02.2020 | 22000 | |
| 1 | 15.02.2020 | 22000 | |
| 1 | 16.02.2020 | 22000 | |
| 1 | 17.02.2020 | 22000 | |
| 1 | 18.02.2020 | 27000 | |
| 1 | 19.02.2020 | 27000 | |
| … | … | … | |
| 4 | 06.02.2020 | 25000 | |
| 4 | 07.02.2020 | 25000 | |
| 4 | 08.02.2020 | 29000 | |
| 4 | 09.02.2020 | 30000 | |
| 4 | 10.02.2020 | 30000 |
Заключение
В данной статье рассмотрел часто встречающиеся SQL запросы и тонкие моменты при работе с GROUP BY и поиском минимальных значений. В свое время потратил достаточно много времени на попытки понять, что не так и как это решить.
