Основы SQL

SQL (Structured Query Language) — декларативный язык для работы с реляционными базами данных. Он используется для определения структуры, чтения и изменения данных, задания ограничений и управления транзакциями.

Примеры близки к стандартному SQL и PostgreSQL. Автоинкремент, функции дат, ALTER TABLE, индексы и планы выполнения могут отличаться в MySQL, SQL Server, Oracle и SQLite.

SELECT id, name
FROM customers
WHERE active = TRUE
ORDER BY name;

SELECT — возвращаемые столбцы.
FROM — источник данных.
WHERE — фильтр строк.
ORDER BY — сортировка.

-- Однострочный комментарий
/* Многострочный комментарий */

Содержание

Реляционная модель
Нормализация: 1NF–3NF
DDL: `CREATE`, `ALTER`, `DROP`
DML: `SELECT`, `INSERT`, `UPDATE`, `DELETE`
`JOIN`: `INNER`, `LEFT`, `RIGHT`, `FULL`
Агрегаты, `GROUP BY`, `HAVING`
Подзапросы и CTE
Ограничения: PK, FK, `UNIQUE`, `CHECK`
Индексы и оптимизация
Транзакции и ACID
Проектирование схемы и ER-диаграммы
Общий пример схемы
Частые ошибки
Краткая памятка

Реляционная модель

Реляционная модель представляет данные в виде отношений, на практике — таблиц.

Понятие	Практический смысл
Отношение	Таблица
Кортеж	Строка
Атрибут	Столбец
Домен	Допустимое множество значений
Ключ	Атрибуты, идентифицирующие строку

CREATE TABLE customers (
  customer_id BIGINT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(200) NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE
);

Кандидатный ключ — минимальный набор столбцов, однозначно определяющий строку. Один кандидатный ключ выбирают первичным. Естественный ключ имеет предметный смысл, например ISBN. Суррогатный ключ создаётся специально: числовой ID или UUID.

Составной ключ:

CREATE TABLE order_items (
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Основные связи:

1:1 — одной строке соответствует не более одной строки другой таблицы;
1:N — одной строке соответствует множество строк;
M:N — множеству строк соответствует множество строк.

Связь 1:N реализуется внешним ключом на стороне «многие». Для M:N нужна таблица связи:

CREATE TABLE order_items (
  order_id BIGINT NOT NULL REFERENCES orders(order_id),
  product_id BIGINT NOT NULL REFERENCES products(product_id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

Нормализация: 1NF–3NF

Нормализация уменьшает дублирование и предотвращает аномалии вставки, обновления и удаления.

Первая нормальная форма — 1NF

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

Нарушение:

orders(order_id, customer_id, product_ids="8,11,27")

Нормализованный вариант:

orders(order_id, customer_id)
order_items(order_id, product_id)

JSON и массивы допустимы, но если элементы нужно независимо связывать, проверять и агрегировать, отдельная таблица обычно лучше.

Вторая нормальная форма — 2NF

Таблица находится в 2NF, если она в 1NF и каждый неключевой атрибут зависит от всего кандидатного ключа.

order_items(order_id, product_id, order_date, product_name, quantity)

При ключе (order_id, product_id) поле order_date зависит только от order_id, а product_name — только от product_id.

orders(order_id, order_date)
products(product_id, product_name)
order_items(order_id, product_id, quantity)

Третья нормальная форма — 3NF

Таблица находится в 3NF, если она в 2NF и неключевые атрибуты не зависят транзитивно друг от друга.

employees(employee_id, department_id, department_name)
employee_id -> department_id -> department_name

CREATE TABLE departments (
  department_id BIGINT PRIMARY KEY,
  department_name VARCHAR(200) NOT NULL UNIQUE
);

CREATE TABLE employees (
  employee_id BIGINT PRIMARY KEY,
  department_id BIGINT NOT NULL REFERENCES departments(department_id),
  full_name VARCHAR(200) NOT NULL
);

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

Денормализация допустима после измерений, например для сохранённого итога или аналитического агрегата. Следует определить источник истины и способ синхронизации.

DDL: `CREATE`, `ALTER`, `DROP`

DDL определяет структуру объектов базы данных.

`CREATE TABLE`

CREATE TABLE products (
  product_id BIGINT GENERATED ALWAYS AS IDENTITY,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(12, 2) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT pk_products PRIMARY KEY (product_id),
  CONSTRAINT uq_products_name UNIQUE (name),
  CONSTRAINT chk_products_price CHECK (price >= 0),
  CONSTRAINT chk_products_status CHECK (status IN ('active', 'archived'))
);

GENERATED ... AS IDENTITY — стандартный способ генерировать числовой ID. В СУБД встречаются также AUTO_INCREMENT, SERIAL и IDENTITY. Для денег используйте DECIMAL/NUMERIC, а не FLOAT.

`ALTER TABLE`

ALTER TABLE products ADD COLUMN stock INTEGER DEFAULT 0;
ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0);
ALTER TABLE products RENAME COLUMN name TO product_name;
ALTER TABLE products DROP COLUMN stock;

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

`DROP`

DROP TABLE products;
DROP TABLE IF EXISTS products;

DROP удаляет объект и данные. CASCADE может удалить зависимые объекты.

Оператор	Результат	`WHERE`	Структура остаётся
`DELETE`	Удаляет строки	Да	Да
`TRUNCATE`	Очищает таблицу	Нет	Да
`DROP`	Удаляет объект	Нет	Нет

Транзакционность DDL и TRUNCATE зависит от СУБД.

DML: `SELECT`, `INSERT`, `UPDATE`, `DELETE`

`SELECT`

SELECT DISTINCT customer_id, full_name AS customer_name
FROM customers
WHERE active = TRUE
ORDER BY full_name, customer_id
LIMIT 20 OFFSET 0;

Без ORDER BY порядок не гарантирован. Для стабильной пагинации сортировка должна однозначно упорядочивать строки.

WHERE price >= 1000 AND price < 5000
WHERE status IN ('new', 'paid')
WHERE price BETWEEN 100 AND 500
WHERE name LIKE 'SQL%'
WHERE deleted_at IS NULL

В LIKE знак % означает любое количество символов, _ — один символ.

`NULL`

NULL — неизвестное или отсутствующее значение, не равное нулю или пустой строке.

-- Неверно
WHERE phone = NULL

-- Верно
WHERE phone IS NULL

SELECT COALESCE(phone, 'не указан') AS phone
FROM customers;

`INSERT`

INSERT INTO customers (email, full_name)
VALUES ('anna@example.com', 'Анна Смирнова');

INSERT INTO products (name, price)
VALUES ('Клавиатура', 4500.00), ('Мышь', 2100.00);

Из запроса:

INSERT INTO archived_orders (order_id, customer_id)
SELECT order_id, customer_id
FROM orders
WHERE created_at < DATE '2025-01-01';

`UPDATE`

UPDATE products
SET price = price * 1.05,
    updated_at = CURRENT_TIMESTAMP
WHERE category_id = 10;

`DELETE`

DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;

Без WHERE команды UPDATE и DELETE затронут все строки. Перед массовой операцией выполните SELECT с тем же условием и используйте транзакцию.

Логический порядок `SELECT`

FROM и JOIN.
WHERE.
GROUP BY.
Агрегаты.
HAVING.
SELECT.
DISTINCT.
ORDER BY.
LIMIT/OFFSET.

Псевдоним из SELECT обычно нельзя использовать в WHERE того же уровня.

`JOIN`: `INNER`, `LEFT`, `RIGHT`, `FULL`

`INNER JOIN`

Только строки с совпадением с обеих сторон:

SELECT c.full_name, o.order_id
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.customer_id;

`LEFT JOIN`

Все строки слева и совпадения справа. При отсутствии совпадения справа будут NULL:

SELECT c.full_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id;

Клиенты без заказов:

SELECT c.customer_id, c.full_name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

`RIGHT JOIN`

Все строки справа и совпадения слева:

SELECT c.full_name, o.order_id
FROM customers AS c
RIGHT JOIN orders AS o ON o.customer_id = c.customer_id;

Его часто переписывают как LEFT JOIN, поменяв таблицы местами.

`FULL OUTER JOIN`

Совпадения и несовпавшие строки обеих таблиц:

SELECT c.customer_id, o.order_id
FROM customers AS c
FULL OUTER JOIN orders AS o ON o.customer_id = c.customer_id;

Некоторые СУБД не поддерживают FULL OUTER JOIN напрямую.

`ON` и `WHERE`

Сохраняет всех клиентов, присоединяя только оплаченные заказы:

SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id AND o.status = 'paid';

Если перенести условие статуса в WHERE, строки без заказа исчезнут. Перед соединением определяйте кардинальность 1:1, 1:N или M:N. Не скрывайте ошибочное умножение строк через DISTINCT.

Агрегаты, `GROUP BY`, `HAVING`

Функция	Назначение
`COUNT(*)`	Число строк
`COUNT(column)`	Число не-`NULL` значений
`SUM(column)`	Сумма
`AVG(column)`	Среднее
`MIN(column)`	Минимум
`MAX(column)`	Максимум

SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_spent,
  AVG(total_amount) AS average_order
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 5
ORDER BY total_spent DESC;

WHERE фильтрует строки до группировки, HAVING — группы после агрегирования. Большинство агрегатов игнорирует NULL; COUNT(*) считает все строки.

Условная агрегация:

SELECT customer_id,
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders
FROM orders
GROUP BY customer_id;

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

Скалярный подзапрос возвращает одну строку и один столбец:

SELECT name, price,
       (SELECT AVG(price) FROM products) AS average_price
FROM products;

Проверка существования:

SELECT c.customer_id, c.full_name
FROM customers AS c
WHERE EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = c.customer_id AND o.status = 'paid'
);

Для поиска отсутствия связи используйте NOT EXISTS. NOT IN может дать неожиданный результат, если подзапрос возвращает NULL.

CTE — именованный результат внутри одной инструкции:

WITH paid_orders AS (
  SELECT customer_id, total_amount
  FROM orders
  WHERE status = 'paid'
),
customer_totals AS (
  SELECT customer_id, SUM(total_amount) AS total_spent
  FROM paid_orders
  GROUP BY customer_id
)
SELECT c.full_name, ct.total_spent
FROM customer_totals AS ct
JOIN customers AS c ON c.customer_id = ct.customer_id;

CTE улучшает читаемость, но не обязательно ускоряет запрос: оптимизатор может встроить или материализовать его.

Рекурсивный CTE для дерева:

WITH RECURSIVE tree AS (
  SELECT department_id, parent_id, name, 0 AS level
  FROM departments WHERE parent_id IS NULL
  UNION ALL
  SELECT d.department_id, d.parent_id, d.name, t.level + 1
  FROM departments AS d
  JOIN tree AS t ON d.parent_id = t.department_id
)
SELECT * FROM tree;

Для графов учитывайте циклы и глубину обхода.

Ограничения: PK, FK, `UNIQUE`, `CHECK`

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

CONSTRAINT pk_orders PRIMARY KEY (order_id),
CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id)
  REFERENCES customers(customer_id)
  ON UPDATE CASCADE
  ON DELETE RESTRICT,
CONSTRAINT uq_customer_email UNIQUE (email),
CONSTRAINT chk_total CHECK (total_amount >= 0)

PRIMARY KEY однозначно идентифицирует строку и запрещает NULL.
FOREIGN KEY обеспечивает ссылочную целостность.
UNIQUE запрещает повтор значения или комбинации.
CHECK проверяет логическое условие.
NOT NULL требует значение.
DEFAULT задаёт значение, если столбец не указан, но не заменяет явный NULL.

Действия FK:

RESTRICT/NO ACTION — запретить изменение родителя при наличии ссылок;
CASCADE — изменить или удалить зависимые строки;
SET NULL — установить NULL;
SET DEFAULT — установить значение по умолчанию.

Внешний ключ не во всех СУБД автоматически создаёт индекс на дочернем столбце. Поведение нескольких NULL в UNIQUE также зависит от СУБД.

Индексы и оптимизация

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

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

Составной индекс подходит, например, для:

SELECT order_id, created_at, total_amount
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Порядок столбцов важен. B-tree (customer_id, created_at) обычно полезен для поиска по customer_id и по паре, но не обязательно только по created_at.

Селективность показывает, насколько условие сокращает результат. Индекс по уникальному email селективен, индекс по логическому полю часто малополезен. Наличие индекса не гарантирует его использование.

PostgreSQL поддерживает частичные индексы и индексы по выражению:

CREATE INDEX idx_orders_unpaid
ON orders (created_at) WHERE status = 'unpaid';

CREATE INDEX idx_customers_lower_email
ON customers (LOWER(email));

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

EXPLAIN
SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN ANALYZE реально выполняет запрос. Проверяйте тип сканирования, оценочное и фактическое число строк, соединения, сортировки и дорогие узлы.

Практические правила:

выбирайте нужные столбцы вместо безусловного SELECT *;
индексируйте частые селективные фильтры, соединения и сортировки;
сравнивайте совместимые типы;
не применяйте функцию к индексируемому столбцу без индекса по выражению;
для существования используйте EXISTS;
избегайте больших OFFSET, применяйте пагинацию по ключу;
проверяйте N+1-запросы приложения;
измеряйте на реалистичных данных.

Диапазон часто лучше функции над столбцом:

WHERE created_at >= TIMESTAMP '2026-08-01 00:00:00'
  AND created_at <  TIMESTAMP '2026-08-02 00:00:00'

Не создавайте индекс на каждый столбец: индексы занимают место и замедляют INSERT, UPDATE, DELETE.

Транзакции и ACID

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;
COMMIT;

При ошибке выполняется ROLLBACK.

Atomicity: выполняются все операции либо ни одной.
Consistency: сохраняются ограничения и бизнес-инварианты.
Isolation: параллельные транзакции не создают недопустимых эффектов.
Durability: после COMMIT изменения сохраняются.

Уровень	Общая идея
`READ UNCOMMITTED`	Минимальная изоляция
`READ COMMITTED`	Видны зафиксированные данные
`REPEATABLE READ`	Повторное чтение стабильно
`SERIALIZABLE`	Эффект последовательного выполнения

Точное поведение зависит от СУБД. Возможны грязное и неповторяемое чтение, фантомы, потерянное обновление и write skew.

BEGIN;
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;
-- Изменение
COMMIT;

Атомарное уменьшение остатка:

UPDATE products
SET stock = stock - 1
WHERE product_id = 100 AND stock > 0;

Транзакции должны быть короткими. Изменяйте ресурсы в одинаковом порядке, обрабатывайте deadlock и повторяйте всю транзакцию при конфликте. Для частичного отката применяйте SAVEPOINT и ROLLBACK TO SAVEPOINT.

Проектирование схемы и ER-диаграммы

Порядок проектирования:

Собрать требования и сценарии запросов.
Выделить сущности, атрибуты и жизненный цикл.
Определить кардинальности 0..1, 1..1, 0..N, 1..N.
Выбрать кандидатные и первичные ключи.
Нормализовать модель.
Задать ограничения и правила удаления.
Добавить индексы под реальные запросы.
Проверить граничные случаи и оформить миграции.

Связь 1:1 через общий PK/FK:

CREATE TABLE user_profiles (
  user_id BIGINT PRIMARY KEY REFERENCES users(user_id),
  bio TEXT
);

Исторические данные проектируйте отдельно. Например, order_items.unit_price хранит цену на момент заказа, потому что текущая цена товара может измениться.

ER-диаграмма показывает сущности, атрибуты и связи:

CUSTOMERS 1 ─────< N ORDERS
ORDERS    1 ─────< N ORDER_ITEMS N >───── 1 PRODUCTS
PRODUCTS  N >───── 1 CATEGORIES

Mermaid-вариант:

erDiagram CUSTOMERS ||--o{ ORDERS : places ORDERS ||--|{ ORDER_ITEMS : contains PRODUCTS ||--o{ ORDER_ITEMS : included_in CATEGORIES ||--o{ PRODUCTS : classifies

|| — ровно один, o| — ноль или один, |{ — один или много, o{ — ноль или много. ER-диаграмма не заменяет DDL: типы, проверки и индексы фиксируются в миграциях.

Общий пример схемы

CREATE TABLE customers (
  customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(200) NOT NULL
);

CREATE TABLE products (
  product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(12, 2) NOT NULL CHECK (price >= 0),
  stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE orders (
  order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
  status VARCHAR(20) NOT NULL DEFAULT 'new',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (status IN ('new', 'paid', 'shipped', 'cancelled'))
);

CREATE TABLE order_items (
  order_id BIGINT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(product_id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(12, 2) NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (order_id, product_id)
);

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

CREATE INDEX idx_order_items_product
ON order_items (product_id);

Частые ошибки

UPDATE или DELETE без WHERE.
Сравнение с NULL через =.
Условие правой таблицы в WHERE после LEFT JOIN.
Хранение списков независимых сущностей в одной строке.
Отсутствие PK, FK, UNIQUE, CHECK и NOT NULL.
Использование FLOAT для точных денежных расчётов.
Расчёт истории по текущим изменяемым данным.
Индексирование каждого столбца без анализа.
Ожидание порядка строк без ORDER BY.
Слишком длинные транзакции.

Краткая памятка

SELECT columns FROM table_name WHERE condition;
INSERT INTO table_name (column1) VALUES (value1);
UPDATE table_name SET column1 = value1 WHERE condition;
DELETE FROM table_name WHERE condition;

SELECT group_column, COUNT(*)
FROM table_name
WHERE row_condition
GROUP BY group_column
HAVING COUNT(*) > 1;

WITH data AS (SELECT * FROM table_name)
SELECT * FROM data;

BEGIN;
-- операции
COMMIT; -- либо ROLLBACK

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

Основы SQL

Содержание

Реляционная модель

Нормализация: 1NF–3NF

Первая нормальная форма — 1NF

Вторая нормальная форма — 2NF

Третья нормальная форма — 3NF

DDL: CREATE, ALTER, DROP

CREATE TABLE

ALTER TABLE

DROP

DML: SELECT, INSERT, UPDATE, DELETE

SELECT

NULL

INSERT

UPDATE

DELETE

Логический порядок SELECT

JOIN: INNER, LEFT, RIGHT, FULL

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

ON и WHERE

Агрегаты, GROUP BY, HAVING