На основе того, что было в курсе Bootcamp SQL , составил себе шпаргалку.
На основе того, что было в курсе Bootcamp SQL, составил себе шпаргалку по SQL (диалект — PostgreSQL). В Telegram я публиковал её частями, здесь собрал всё в одном месте.
1. Базовые операции DML (Data Manipulation Language)
| Оператор |
Назначение |
Пример |
SELECT |
Выборка данных из таблицы |
SELECT name, age FROM person |
INSERT |
Добавление новых строк |
INSERT INTO person (id, name) VALUES (1, 'Anna') |
UPDATE |
Изменение существующих данных |
UPDATE person SET age = 30 WHERE id = 1 |
DELETE |
Удаление строк |
DELETE FROM person WHERE id = 1 |
2. Условия и фильтрация
| Конструкция |
Назначение |
Пример |
WHERE |
Фильтрация строк по условию |
WHERE age > 18 |
AND / OR |
Логическое И / ИЛИ |
WHERE age > 18 AND gender = 'female' |
BETWEEN |
Проверка вхождения в диапазон |
WHERE price BETWEEN 800 AND 1000 |
LIKE |
Поиск по шаблону (% — любое кол-во символов) |
WHERE pizza_name LIKE '%mushroom%' |
IN |
Проверка принадлежности списку значений |
WHERE id IN (1, 2, 3) |
EXISTS |
Проверка наличия строк в подзапросе |
WHERE NOT EXISTS (SELECT 1 FROM ...) |
DISTINCT |
Удаление дубликатов из результата |
SELECT DISTINCT name FROM person |
ORDER BY |
Сортировка (ASC / DESC) |
ORDER BY name ASC, age DESC |
3. Подзапросы
Подзапросы требуют уложить в голове, что в каждой части основного запроса может создаваться другая таблица. Более того, таблицы можно создавать как множества и получать из них производные значения.
Эти механизмы позволяют не собирать JOIN-ами гигантскую таблицу, чтобы потом её фильтровать, а брать меньше данных и уже из них получать целевые значения.
| Тип |
Назначение |
Пример |
| Скалярный |
Возвращает одно значение (один столбец, одна строка) |
(SELECT name FROM person WHERE id = 1) |
| Коррелированный |
Ссылается на внешний запрос (выполняется для каждой строки) |
(SELECT 1 FROM person_order WHERE menu_id = menu.id) |
В FROM |
Используется как временная таблица (алиас обязателен) |
FROM (SELECT ...) AS t |
В WHERE / HAVING |
Используется для фильтрации |
WHERE id IN (SELECT person_id FROM person_order) |
4. Операции над множествами (SET-операции)
| Оператор |
Назначение |
Пример |
UNION |
Объединение двух запросов, дубли удаляются |
SELECT name FROM person UNION SELECT pizza_name FROM menu |
UNION ALL |
Объединение, дубли сохраняются |
SELECT name FROM person UNION ALL SELECT pizza_name FROM menu |
INTERSECT |
Пересечение — только общие строки |
(SELECT id FROM person) INTERSECT (SELECT person_id FROM person_order) |
EXCEPT |
Разность — строки из первого, отсутствующие во втором |
(SELECT id FROM person) EXCEPT (SELECT person_id FROM person_visits) |
EXCEPT ALL |
Разность с сохранением дублей |
SELECT person_id FROM person_order EXCEPT ALL SELECT person_id FROM person_visits |
5. JOIN (соединения)
| Тип |
Назначение |
Пример |
INNER JOIN |
Только совпадающие строки |
FROM person_order JOIN person ON person_order.person_id = person.id |
LEFT JOIN |
Все строки из левой + совпадения из правой |
FROM person LEFT JOIN person_visits ON person.id = person_visits.person_id |
RIGHT JOIN |
Все строки из правой + совпадения из левой |
FROM person_visits RIGHT JOIN pizzeria ON person_visits.pizzeria_id = pizzeria.id |
FULL JOIN |
Все строки из обеих таблиц |
FROM person FULL JOIN person_visits ON person.id = person_visits.person_id |
CROSS JOIN |
Декартово произведение (все комбинации) |
FROM person CROSS JOIN pizzeria |
NATURAL JOIN |
Автоматическое соединение по одинаковым именам столбцов |
FROM person_order NATURAL JOIN person |
| Самосоединение |
Таблица соединяется сама с собой (через алиасы) |
FROM person p1 JOIN person p2 ON p1.address = p2.address |
6. Группировка и агрегация
С группировкой я уже был знаком благодаря курсу от Yandex «Основы работы с базами данных и SQL»: сначала выполняешь операции со столбцами, а потом фильтруешь результат через HAVING.
| Функция / Конструкция |
Назначение |
Пример |
COUNT |
Количество строк |
SELECT COUNT(*) FROM person |
SUM |
Сумма значений |
SELECT SUM(price) FROM menu |
AVG |
Среднее значение |
SELECT AVG(rating) FROM pizzeria |
MAX / MIN |
Максимальное / минимальное значение |
SELECT MAX(age) FROM person |
GROUP BY |
Группировка строк по заданному столбцу |
GROUP BY pizzeria_id |
HAVING |
Фильтрация групп (после GROUP BY) |
HAVING COUNT(*) > 1 |
7. CTE (Common Table Expression)
CTE стали для меня новым. Повторяющиеся запросы и сложные конструкции можно вынести в отдельный блок и обращаться к нему несколько раз, в том числе рекурсивно.
| Конструкция |
Назначение |
Пример |
WITH ... AS |
Создаёт временное именованное множество (живёт в пределах запроса) |
WITH cte AS (SELECT ...) SELECT * FROM cte |
8. Генераторы и работа со строками
Неожиданно было увидеть внутри SELECT оператор CASE.
| Функция / Конструкция |
Назначение |
Пример |
generate_series() |
Генерирует последовательность чисел или дат |
generate_series('2022-01-01'::date, '2022-01-10'::date, '1 day') |
CONCAT() |
Склеивает строки |
CONCAT(name, ' age:', age) |
COALESCE() |
Заменяет NULL на заданное значение |
COALESCE(name, '-') |
CAST (::) |
Приводит значение к другому типу данных |
'2022-01-01'::date |
CASE WHEN |
Условное ветвление в запросе |
CASE WHEN age > 18 THEN 'adult' ELSE 'child' END |
Новые посты на почту
Разработка, инфраструктура и автоматизация — прямо в инбоксе.
Подписаться
