SQL для повседневной работы

SQL: задать вопрос данным.

Основы переносимы между PostgreSQL, MySQL и SQLite; функции дат, JSON и некоторые типы отличаются. Примеры используют таблицы users, orders и order_items.

Читать данные

SELECT * FROM users;

Все столбцы и строки. В прикладном коде обычно лучше перечислить нужные столбцы.

SELECT id, email FROM users LIMIT 50;

Выбрать только нужные поля и ограничить размер ответа.

WHERE status = 'active'

Отфильтровать строки по условию.

WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'

Строки за последнюю неделю; синтаксис интервала — PostgreSQL.

ORDER BY created_at DESC

Отсортировать от новых к старым.

LIMIT 50 OFFSET 100

Пагинация; на больших таблицах для последовательного просмотра лучше keyset-пагинация.

Условия

WHERE id IN (1, 2, 3)

Значение входит в список.

WHERE name LIKE 'Ann%'

Строка начинается с Ann; _ означает один любой символ.

WHERE deleted_at IS NULL

Проверка NULL. Сравнение = NULL не работает.

WHERE price BETWEEN 10 AND 100

Диапазон с включёнными границами.

WHERE a = 1 AND (b = 2 OR b = 3)

Скобки явно задают порядок логики.

Агрегировать

SELECT COUNT(*) FROM orders;

Количество строк.

SELECT status, COUNT(*) FROM orders GROUP BY status;

Количество заказов в каждом статусе.

SELECT user_id, SUM(total) FROM orders GROUP BY user_id;

Сумма заказов по пользователям.

HAVING COUNT(*) > 3

Фильтр уже сгруппированных результатов.

COUNT(DISTINCT user_id)

Количество уникальных пользователей.

Соединять таблицы

... FROM orders o JOIN users u ON u.id = o.user_id

INNER JOIN: только заказы, у которых есть пользователь.

... FROM users u LEFT JOIN orders o ON o.user_id = u.id

Все пользователи, даже без заказов; поля o будут NULL.

WHERE o.id IS NULL

После LEFT JOIN найти пользователей без заказов.

JOIN order_items i ON i.order_id = o.id

Присоединить строки заказа; учитывайте, что они размножают строки заказа.

Изменять данные

INSERT INTO users (email, name) VALUES (?, ?);

Добавить строку. Используйте параметры драйвера, а не склейку SQL-строк.

UPDATE users SET status = 'active' WHERE id = ?;

Обновить ровно нужную запись.

DELETE FROM sessions WHERE expires_at < NOW();

Удалить просроченные сессии.

UPDATE ... RETURNING id, status;

PostgreSQL: вернуть обновлённые поля одним запросом.

Перед UPDATE или DELETE сначала выполните тот же WHERE как SELECT; не запускайте их без WHERE

CTE и окна

WITH recent AS (SELECT * FROM orders WHERE created_at > CURRENT_DATE - INTERVAL '30 days') SELECT * FROM recent;

Разбить сложный запрос на именованные части.

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)

Нумерация строк внутри каждого пользователя.

SUM(total) OVER (ORDER BY created_at)

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

LAG(total) OVER (ORDER BY created_at)

Значение предыдущей строки — для сравнения периодов.

Транзакции и скорость

BEGIN; ... COMMIT;

Выполнить набор изменений атомарно.

ROLLBACK;

Отменить незавершённую транзакцию при ошибке.

EXPLAIN ANALYZE SELECT ...

PostgreSQL: фактический план и время запроса. На изменяющих запросах выполняет сам запрос.

CREATE INDEX idx_orders_user_id ON orders(user_id);

Индекс для частых фильтров и соединений по user_id; измеряйте эффект через EXPLAIN.

SELECT * FROM pg_stat_activity;

PostgreSQL: активные соединения и запросы. Требуются соответствующие права.