🗄️ SQL

Полный справочник: типы, DDL и ограничения, DML и upsert, SELECT, JOIN, группировки, подзапросы, CTE, оконные функции, функции дат и строк, JSON, индексы, EXPLAIN, транзакции, нормализация и типовые задачи.

Шпаргалки · Данные · #sql #database #postgres #mysql #sqlite

Диалекты

SQL стандартизирован, но у каждой СУБД свои отличия: PostgreSQL, MySQL / MariaDB, SQLite, SQL Server (T-SQL), Oracle (PL/SQL). Здесь основа общая, различия помечены.

Задача	PostgreSQL	MySQL	SQL Server	SQLite
Ограничить строки	`LIMIT n OFFSET m`	`LIMIT n OFFSET m`	`TOP n` или `OFFSET m ROWS FETCH NEXT n ROWS ONLY`	`LIMIT n OFFSET m`
Авто-ключ	`GENERATED ALWAYS AS IDENTITY`, `SERIAL`	`AUTO_INCREMENT`	`IDENTITY(1,1)`	`INTEGER PRIMARY KEY`
Текущее время	`now()`	`NOW()`	`GETDATE()`, `SYSDATETIME()`	`datetime('now')`
Склейка	`a \|\| b`, `concat()`	`CONCAT(a, b)`	`a + b`, `CONCAT`	`a \|\| b`
Кавычки имён	`"name"`	`name`	`[name]`	`"name"`
Upsert	`ON CONFLICT DO UPDATE`	`ON DUPLICATE KEY UPDATE`	`MERGE`	`ON CONFLICT DO UPDATE`

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

FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. Поэтому псевдоним из SELECT нельзя использовать в WHERE (но можно в ORDER BY).

Типы данных

Группа	Типы (PostgreSQL)
Целые	`smallint`, `integer`, `bigint`
Точные числа	`numeric(12,2)` (деньги), `decimal`
Приближённые	`real`, `double precision`
Текст	`text`, `varchar(n)`, `char(n)`
Дата и время	`date`, `time`, `timestamp`, `timestamptz` (с часовым поясом), `interval`
Логический	`boolean`
Идентификаторы	`uuid`
Структуры	`json`, `jsonb`, `array`, `enum`
Двоичные	`bytea`

Деньги храните в numeric, не в float. Время лучше в UTC (timestamptz).

DDL: схема

CREATE TABLE users (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       VARCHAR(255) NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  age         INT CHECK (age >= 0),
  role        TEXT NOT NULL DEFAULT 'user',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id   BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total     NUMERIC(12,2) NOT NULL CHECK (total >= 0),
  status    TEXT NOT NULL DEFAULT 'new',
  UNIQUE (user_id, id)
);

ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
TRUNCATE TABLE logs;           -- быстро очищает таблицу
DROP TABLE IF EXISTS tmp CASCADE;
CREATE TEMP TABLE t AS SELECT * FROM users WHERE age > 30;

Ограничение	Смысл
`PRIMARY KEY`	уникальный идентификатор строки, не NULL
`FOREIGN KEY`	ссылка на другую таблицу; `ON DELETE CASCADE / SET NULL / RESTRICT`
`UNIQUE`	значения не повторяются (NULL допускается)
`NOT NULL`	значение обязательно
`CHECK`	произвольное условие
`DEFAULT`	значение по умолчанию

DML: изменение данных

INSERT INTO users (email, name) VALUES ('a@x.ru', 'Аня'), ('b@x.ru', 'Борис');
INSERT INTO archive SELECT * FROM users WHERE created_at < '2020-01-01';
UPDATE users SET name = 'Анна', age = age + 1 WHERE id = 1;
UPDATE o SET status = 'paid' FROM payments p WHERE p.order_id = o.id;      -- PostgreSQL: обновление по связи
DELETE FROM users WHERE id = 1;
INSERT INTO users (email, name) VALUES ('a@x.ru', 'Аня')
  ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;                  -- upsert (PostgreSQL, SQLite)
INSERT INTO users (id, name) VALUES (1, 'Аня') ON DUPLICATE KEY UPDATE name = VALUES(name);   -- MySQL
INSERT ... RETURNING id;   UPDATE ... RETURNING *;                          -- PostgreSQL: вернуть строки

Внимание: UPDATE и DELETE без WHERE затронут все строки. Сначала выполните SELECT с тем же условием, работайте внутри транзакции.

SELECT

SELECT DISTINCT role, COUNT(*) AS n
FROM users u
WHERE age BETWEEN 18 AND 65
  AND role IN ('user', 'admin')
  AND name LIKE 'А%'          -- % любая строка, _ один символ; ILIKE — без регистра (PostgreSQL)
  AND phone IS NOT NULL
GROUP BY role
HAVING COUNT(*) > 5
ORDER BY n DESC, role
LIMIT 10 OFFSET 20;

Операторы: = <> != < > <= >=, AND OR NOT, BETWEEN, IN, LIKE, IS NULL, EXISTS, ANY / ALL. Сравнение с NULL через = NULL ничего не даёт: пишите IS NULL. Выражения: CASE WHEN age < 18 THEN 'minor' ELSE 'adult' END, COALESCE(a, b, 0), NULLIF(a, 0), CAST(x AS INT), x::int (PostgreSQL).

JOIN

JOIN	Результат
`INNER JOIN`	только пары с совпадением
`LEFT [OUTER] JOIN`	все строки слева + совпадения справа (иначе NULL)
`RIGHT JOIN`	зеркально LEFT
`FULL OUTER JOIN`	все строки обеих таблиц
`CROSS JOIN`	все пары (декартово произведение)
`SELF JOIN`	таблица с самой собой (иерархии)

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid'    -- условие по правой таблице держите в ON
WHERE o.id IS NULL;                                              -- пользователи без оплаченных заказов
SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON m.id = e.manager_id;

Условие по правой таблице в WHERE превращает LEFT JOIN в INNER. USING (col) сокращает ON a.col = b.col.

Объединение результатов

SELECT id FROM a UNION SELECT id FROM b;           -- уникальные
SELECT id FROM a UNION ALL SELECT id FROM b;       -- с дублями (быстрее)
SELECT id FROM a INTERSECT SELECT id FROM b;       -- пересечение
SELECT id FROM a EXCEPT SELECT id FROM b;          -- разность (в Oracle MINUS)

Число и типы столбцов должны совпадать.

Агрегаты и группировки

SELECT status, COUNT(*) AS cnt, SUM(total) AS sum, AVG(total) AS avg, MIN(total), MAX(total),
       COUNT(DISTINCT user_id) AS buyers,
       COUNT(*) FILTER (WHERE total > 1000) AS big                 -- PostgreSQL; в других: SUM(CASE WHEN ... THEN 1 END)
FROM orders GROUP BY status;
SELECT region, product, SUM(sales) FROM s GROUP BY ROLLUP (region, product);       -- подитоги; также CUBE, GROUPING SETS
SELECT string_agg(name, ', ' ORDER BY name) FROM users;             -- склейка: GROUP_CONCAT (MySQL), STRING_AGG (SQL Server)

COUNT(*) считает строки, COUNT(col) только не-NULL. WHERE фильтрует до группировки, HAVING после.

Подзапросы и CTE

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);   -- быстрее и безопаснее с NULL, чем IN
SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS n FROM users u;

WITH paid AS (
  SELECT user_id, SUM(total) AS total FROM orders WHERE status = 'paid' GROUP BY user_id
), top AS (
  SELECT * FROM paid ORDER BY total DESC LIMIT 10
)
SELECT u.name, t.total FROM top t JOIN users u ON u.id = t.user_id;

WITH RECURSIVE tree AS (                                   -- иерархия
  SELECT id, parent_id, name, 1 AS lvl FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.lvl + 1 FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;
SELECT * FROM users u, LATERAL (SELECT * FROM orders o WHERE o.user_id = u.id ORDER BY id DESC LIMIT 3) x;   -- «последние N на каждого»

Оконные функции

SELECT name, dept, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
       RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,       -- с пропусками при равенстве
       DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS drnk,
       SUM(salary)  OVER (PARTITION BY dept) AS dept_total,
       AVG(salary)  OVER () AS avg_all,
       LAG(salary)  OVER (ORDER BY id) AS prev,   LEAD(salary) OVER (ORDER BY id) AS next,
       SUM(salary)  OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running,
       NTILE(4)     OVER (ORDER BY salary) AS quartile,
       FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_earner
FROM staff;

Окно не схлопывает строки, в отличие от GROUP BY. Фильтровать по оконной функции нужно через подзапрос или CTE.

Функции строк, чисел, дат

-- строки (PostgreSQL / MySQL)
LENGTH(s), UPPER(s), LOWER(s), TRIM(s), SUBSTRING(s FROM 2 FOR 3), REPLACE(s, 'a', 'b'), POSITION('x' IN s),
LEFT(s, 3), RIGHT(s, 3), LPAD(s, 5, '0'), SPLIT_PART(s, ',', 2), REGEXP_REPLACE(s, '\s+', ' ', 'g'), s ~ '^\d+$'
-- числа
ROUND(x, 2), CEIL(x), FLOOR(x), ABS(x), MOD(a, b), POWER(a, 2), SQRT(x), RANDOM()
-- даты (PostgreSQL)
now(), CURRENT_DATE, DATE_TRUNC('month', ts), EXTRACT(YEAR FROM ts), ts + INTERVAL '7 days', AGE(a, b),
TO_CHAR(ts, 'YYYY-MM-DD HH24:MI'), TO_DATE('2026-01-31', 'YYYY-MM-DD'), generate_series('2026-01-01'::date, '2026-01-31', '1 day')
-- MySQL: DATE_ADD(d, INTERVAL 7 DAY), DATEDIFF(a, b), DATE_FORMAT(d, '%Y-%m')
-- SQL Server: DATEADD(day, 7, d), DATEDIFF(day, a, b), FORMAT(d, 'yyyy-MM')

JSON и массивы (PostgreSQL)

SELECT data->>'name' AS name, data->'tags' AS tags, data #>> '{address,city}' AS city FROM events;
SELECT * FROM events WHERE data @> '{"type":"click"}';
SELECT jsonb_agg(row_to_json(u)) FROM users u;     SELECT jsonb_each_text(data) FROM events;
SELECT * FROM t WHERE 'a' = ANY(tags);             SELECT unnest(tags) FROM t;

MySQL: JSON_EXTRACT(col, '$.name'), col->>'$.name'. SQL Server: JSON_VALUE, OPENJSON.

Индексы

CREATE INDEX idx_orders_user ON orders (user_id);
CREATE UNIQUE INDEX uq_users_email ON users (lower(email));
CREATE INDEX idx_orders_user_status ON orders (user_id, status);             -- составной: порядок важен
CREATE INDEX idx_paid ON orders (created_at) WHERE status = 'paid';           -- частичный (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_x ON big (col);                                  -- без блокировки записи (PostgreSQL)
CREATE INDEX idx_gin ON events USING gin (data);                               -- JSON, массивы, полнотекстовый поиск
DROP INDEX idx_orders_user;

Правила: индексируйте столбцы из WHERE, JOIN, ORDER BY; составной индекс (a, b) помогает запросам по a и по a, b, но не только по b; функция над столбцом (WHERE lower(email) = ..., WHERE col + 1 = 5) отключает обычный индекс; LIKE '%x' его не использует; индексы замедляют запись и занимают место. Типы: B-tree (по умолчанию), Hash, GIN, GiST, BRIN.

План выполнения

EXPLAIN SELECT * FROM orders WHERE user_id = 5;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;          -- реальное выполнение (PostgreSQL)

Что искать: Seq Scan на больших таблицах (нужен индекс), большая разница оценки и факта строк (обновите статистику ANALYZE), Nested Loop на больших объёмах, сортировка на диске. VACUUM (ANALYZE) в PostgreSQL.

Транзакции

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                  -- или ROLLBACK;
SAVEPOINT sp1; ROLLBACK TO sp1;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;                 -- блокировка строки
SELECT * FROM jobs WHERE status = 'new' LIMIT 1 FOR UPDATE SKIP LOCKED;      -- очередь задач

ACID: атомарность, согласованность, изолированность, долговечность.

Уровень изоляции	Защищает от
`READ UNCOMMITTED`	почти ни от чего (грязное чтение)
`READ COMMITTED`	грязного чтения (по умолчанию в PostgreSQL, Oracle, SQL Server)
`REPEATABLE READ`	неповторяемого чтения (по умолчанию в MySQL InnoDB)
`SERIALIZABLE`	всех аномалий, включая фантомы

Взаимная блокировка (deadlock): обновляйте строки в одном порядке, держите транзакции короткими.

Представления, функции, триггеры

CREATE VIEW active_users AS SELECT * FROM users WHERE active;
CREATE MATERIALIZED VIEW daily_sales AS SELECT date, SUM(total) FROM orders GROUP BY date;
REFRESH MATERIALIZED VIEW daily_sales;

CREATE FUNCTION add(a int, b int) RETURNS int LANGUAGE sql AS $$ SELECT a + b $$;
CREATE TRIGGER trg_updated BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Права

CREATE ROLE app LOGIN PASSWORD '...';   GRANT SELECT, INSERT, UPDATE ON users TO app;   REVOKE DELETE ON users FROM app;
GRANT USAGE ON SCHEMA public TO app;    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

Нормализация

Форма	Правило
1НФ	значения атомарны, нет повторяющихся групп
2НФ	нет зависимости от части составного ключа
3НФ	нет зависимости неключевых полей друг от друга

Нормализуйте для целостности, денормализуйте осознанно для скорости чтения. Связи: один-к-одному, один-ко-многим (внешний ключ), многие-ко-многим (связующая таблица).

Пагинация

OFFSET на больших значениях медленный. Курсорная (keyset): WHERE (created_at, id) < (:last_ts, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20.

Классические задачи

-- вторая по величине зарплата
SELECT DISTINCT salary FROM staff ORDER BY salary DESC OFFSET 1 LIMIT 1;
-- дубликаты
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
-- удалить дубликаты, оставив минимальный id
DELETE FROM users a USING users b WHERE a.email = b.email AND a.id > b.id;
-- топ-N в каждой группе
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM staff) t WHERE rn <= 3;
-- накопительный итог
SELECT date, SUM(amount) OVER (ORDER BY date) AS running FROM payments;
-- разрывы в последовательности
SELECT id + 1 AS gap_start FROM t a WHERE NOT EXISTS (SELECT 1 FROM t b WHERE b.id = a.id + 1);
-- пользователи без заказов
SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL;
-- процент от общего
SELECT dept, SUM(salary) * 100.0 / SUM(SUM(salary)) OVER () AS pct FROM staff GROUP BY dept;

Антипаттерны

SELECT * в приложениях: перечисляйте столбцы.
Склейка SQL из ввода пользователя: используйте параметры ($1, ?, :name).
Функции над индексируемыми столбцами в WHERE.
NOT IN с подзапросом, где возможен NULL: NOT EXISTS.
Запросы в цикле (N+1): JOIN или IN.
Хранение денег в float, дат в строках, списков в одном поле.
Отсутствие индексов на внешних ключах.

Инструменты

psql, DBeaver, DataGrip, pgAdmin, MySQL Workbench, SSMS, Azure Data Studio; миграции Flyway, Liquibase, Alembic, Prisma Migrate; анализ запросов pg_stat_statements, EXPLAIN, Percona Toolkit.