🗄 Блок 06 — Базы данных Ozon

SQL · SELECT · JOIN · GROUP BY · Индексы · Транзакции · PostgreSQL

📋 1. SQL — основные запросы

CRUD-операции

Операция	SQL	Что делает
Create	INSERT INTO	Добавить запись
Read	SELECT	Прочитать данные
Update	UPDATE	Обновить запись
Delete	DELETE	Удалить запись

SELECT — основные конструкции

SELECT колонки
FROM таблица
WHERE условие
GROUP BY колонка
HAVING условие_после_группировки
ORDER BY колонка ASC/DESC
LIMIT количество
OFFSET пропуск

WHERE — фильтрация

SELECT * FROM users
WHERE age > 18
  AND city = 'Moscow'
  AND name LIKE 'А%'
  AND role IN ('admin', 'qa');

Оператор	Что делает	Пример
=	Равно	city = 'Moscow'
!= или <>	Не равно	status != 'deleted'
>	Больше	age > 18
LIKE	Поиск по шаблону	name LIKE 'А%' (начинается на А)
IN	В списке	role IN ('admin', 'qa')
BETWEEN	В диапазоне	age BETWEEN 18 AND 30
IS NULL	Пустое значение	email IS NULL

На собе: "Основные операторы: WHERE — фильтрация, GROUP BY — группировка, ORDER BY — сортировка, LIMIT — ограничение количества строк."

🔗 2. JOIN — объединение таблиц

JOIN — объединяет данные из двух таблиц по общему ключу.

Виды JOIN

Тип	Что берёт	Визуально
INNER JOIN	Только совпадения в обеих таблицах	∩ (пересечение)
LEFT JOIN	ВСЁ из левой + совпадения из правой	⊂ (левая целиком)
RIGHT JOIN	ВСЁ из правой + совпадения из левой	⊃ (правая целиком)
FULL JOIN	ВСЁ из обеих таблиц	∪ (всё)

Пример

-- Все пользователи и их заказы
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- u.id = o.user_id — ключ связи
-- LEFT — покажет ВСЕХ юзеров,
-- даже если заказов нет (NULL в o.total)

Когда какой JOIN

INNER — нужны только те, у кого есть данные в обеих таблицах
LEFT — нужны все из первой таблицы (даже без совпадений). Самый частый!
RIGHT — редко, обычно можно переписать как LEFT
FULL — нужны вообще все данные из обеих таблиц

На собе: "INNER JOIN — только совпадения. LEFT JOIN — все из левой таблицы + совпадения из правой (если нет — NULL). LEFT JOIN используется чаще всего."

📊 3. GROUP BY и агрегатные функции

GROUP BY — группирует строки по значению колонки. Используется с агрегатными функциями.

Агрегатные функции

Функция	Что делает	Пример
COUNT	Количество строк	COUNT(*) — всего, COUNT(email) — без NULL
SUM	Сумма	SUM(total)
AVG	Среднее	AVG(age)
MIN	Минимум	MIN(price)
MAX	Максимум	MAX(price)

Пример

-- Сколько заказов у каждого юзера
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 3; -- только те, у кого > 3 заказов

WHERE vs HAVING

	WHERE	HAVING
Когда	До группировки	После группировки
С чем работает	С отдельными строками	С агрегатными результатами
Пример	WHERE age > 18	HAVING COUNT(*) > 3

На собе: "GROUP BY группирует данные. С ним используют COUNT, SUM, AVG. WHERE фильтрует до группировки, HAVING — после. HAVING работает с агрегатными функциями."

🚀 4. Индексы

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

Что делает индекс

Без индекса: БД перебирает ВСЕ строки (Full Table Scan) — медленно
С индексом: БД сразу знает где нужная строка — быстро (O(log n) вместо O(n))

Когда нужен индекс

Колонка часто используется в WHERE
Колонка часто используется в JOIN ... ON
Частый поиск по колонке

Когда НЕ нужен

Мало данных (таблица на 100 строк — и без индекса быстро)
Колонка редко используется в запросах
Высокая частота INSERT/UPDATE — каждый индекс замедляет запись

Виды индексов

Тип	Когда
B-Tree (по умолчанию)	Равенство, диапазон, LIKE 'abc%'
Unique	Уникальные значения (email, id)
Composite (составной)	По нескольким колонкам сразу
Primary Key	Главный ключ — автоматически индексируется

На собе: "Индекс ускоряет поиск — вместо перебора всех строк БД сразу находит нужную. Нужен для колонок в WHERE и JOIN. Минус: замедляет INSERT/UPDATE, потому что индекс нужно пересчитывать."

🔒 5. Транзакции

Транзакция — группа операций, которая выполняется целиком или не выполняется вообще.

Пример

BEGIN; -- начали транзакцию

UPDATE accounts SET balance = balance - 100
WHERE user_id = 1; -- списали

UPDATE accounts SET balance = balance + 100
WHERE user_id = 2; -- зачислили

COMMIT; -- всё успешно → сохранили
-- или ROLLBACK; -- ошибка → отменили всё

ACID — свойства транзакции

Буква	Свойство	Что значит	Пример
A	Atomicity (Атомарность)	Все операции или ни одной	Перевод: списание + зачисление — вместе или никак
C	Consistency (Консистентность)	Данные всегда корректны	Баланс не может уйти в минус
I	Isolation (Изолированность)	Транзакции не мешают друг другу	Два перевода одновременно — не конфликтуют
D	Durability (Стойкость)	После COMMIT — данные сохранены навсегда	Даже если сервер упал — данные на месте

На собе: "Транзакция — группа операций «всё или ничего». COMMIT — сохранить, ROLLBACK — отменить. ACID: Atomicity (атомарность), Consistency (консистентность), Isolation (изолированность), Durability (стойкость)."

🐘 6. PostgreSQL — особенности

PostgreSQL — объектно-реляционная СУБД. Одна из самых популярных. Ты с ней уже работаешь!

Ключевые особенности

Фича	Что значит
JSONB	Хранит JSON, можно искать внутри
Массивы	Колонка может хранить массив: INTEGER[]
SERIAL / BIGSERIAL	Автоинкрементный ID
UUID	Уникальный идентификатор
UPSERT	INSERT ... ON CONFLICT — вставить или обновить

Полезные запросы для QA

-- Посмотреть структуру таблицы
\d table_name

-- Найти юзера по email
SELECT * FROM users WHERE email = 'test@mail.com';

-- Посчитать записи
SELECT COUNT(*) FROM orders WHERE status = 'pending';

-- Последние 10 заказов
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

-- Уникальные значения
SELECT DISTINCT status FROM orders;

На собе: "Работала с PostgreSQL — делала SELECT-запросы для проверки данных. Использовала WHERE, JOIN, ORDER BY, LIMIT. Читала данные для валидации результатов тестов."

🔑 7. Primary Key, Foreign Key, Normalization

Primary Key (PK)

Главный ключ — уникально идентифицирует строку
Не может быть NULL, не может повторяться
Обычно id (SERIAL / UUID)

Foreign Key (FK)

Внешний ключ — ссылка на Primary Key другой таблицы
Обеспечивает связь между таблицами
Не может ссылаться на несуществующую запись

users: orders:
id (PK) | name id (PK) | user_id (FK) | total
1 | Анна 1 | 1 | 500
2 | Иван 2 | 1 | 300
3 | 2 | 100

-- user_id в orders ссылается на id в users

Нормализация (зачем)

1NF — каждая ячейка содержит одно значение
2NF — все неключевые колонки зависят от всего ключа
3NF — нет транзитивных зависимостей
Суть: избежать дублирования данных

На собе: "Primary Key — уникальный идентификатор строки. Foreign Key — ссылка на PK другой таблицы, обеспечивает связь. Нормализация — чтобы не дублировать данные."

💬 Вопросы для самопроверки

❓ Чем INNER JOIN отличается от LEFT JOIN? INNER — только совпадения в обеих таблицах. LEFT — ВСЕ строки из левой + совпадения из правой (нет совпадения = NULL). LEFT используется чаще всего.

❓ В чём разница между WHERE и HAVING? WHERE — фильтрует строки ДО группировки. HAVING — фильтрует группы ПОСЛЕ GROUP BY. HAVING работает с агрегатными функциями (COUNT, SUM).

❓ Что такое индекс и когда он нужен? Структура для быстрого поиска. Нужен для колонок в WHERE и JOIN. Ускоряет SELECT, но замедляет INSERT/UPDATE.

❓ Что такое транзакция? Назови свойства ACID. Группа операций «всё или ничего». COMMIT — сохранить, ROLLBACK — отменить. ACID: Atomicity (атомарность), Consistency (консистентность), Isolation (изолированность), Durability (стойкость).

❓ Primary Key vs Foreign Key? PK — уникальный идентификатор строки в таблице. FK — ссылка на PK другой таблицы, обеспечивает связь между таблицами.

❓ COUNT(*) vs COUNT(column)? COUNT(*) — считает ВСЕ строки, включая NULL. COUNT(column) — считает только строки где column не NULL.