Перейти к содержимому
Лекции

Лекция 7. Основы баз данных и SQL

Курс: Разработка интернет-приложений (backend, Python/FastAPI) Раздел 3, лекция 7. Длительность пары — ~1 ч 30 мин.

План: зачем нужна БД → реляционная модель → SQL и NoSQL → DDL → DML (CRUD) → фильтрация, сортировка, агрегаты → связи и JOIN → нормализация.

1. Зачем веб-приложению база данных

Любое нетривиальное веб-приложение должно где-то хранить состояние между запросами: учётные записи, заказы, товары, настройки. Если держать данные в памяти процесса, они исчезнут при перезапуске, а при нескольких экземплярах приложения у каждого окажется своя копия.

База данных (БД) — это организованная коллекция структурированных данных на диске, управляемых отдельной программой — системой управления базами данных (СУБД). В backend-разработке БД нужна для:

  • постоянного хранения данных (они переживают перезапуск приложения и сервера);
  • целостности — СУБД не даст записать «битые» данные благодаря ограничениям и транзакциям;
  • конкурентного доступа — десятки запросов могут безопасно читать и писать одновременно;
  • эффективного поиска по большим объёмам данных через индексы и язык запросов.

Типичная схема: FastAPI-приложение принимает HTTP-запрос, обращается к СУБД (например, PostgreSQL), формирует ответ. Само приложение остаётся «без состояния» (stateless), а всё состояние живёт в БД — это и позволяет масштабировать backend горизонтально.

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

Реляционная модель — самый распространённый способ организации данных. Её ключевая идея проста: данные хранятся в таблицах (отношениях).

  • Таблица (table) — набор данных об однотипных сущностях, например users или orders.
  • Столбец (column / поле) — именованный атрибут с заданным типом данных, например email VARCHAR(255).
  • Строка (row / запись) — одна конкретная сущность: один пользователь, один заказ.
  • Схема (schema) — описание структуры таблиц: какие столбцы, каких типов, с какими ограничениями.

Важно: в реляционной БД схема строгая — прежде чем записывать данные, нужно описать таблицу, и каждая строка обязана соответствовать структуре столбцов.

Ключи

  • Первичный ключ (PRIMARY KEY) — столбец (или набор столбцов), однозначно идентифицирующий строку в таблице. Значения уникальны и не равны NULL. Чаще всего это id.
  • Внешний ключ (FOREIGN KEY) — столбец, ссылающийся на первичный ключ другой таблицы. Так в реляционной модели выражаются связи между таблицами.

Основные типы данных

ТипОписаниеПримеры
INTEGERЦелые числа1, -5, 1000
VARCHAR(n)Строки ограниченной длины'Иван'
TEXTДлинные тексты'Описание товара...'
BOOLEANЛогические значенияTRUE, FALSE
DECIMAL(p,s)Точные дробные числа99.99
TIMESTAMPДата и время'2026-01-15 14:30:00'

3. SQL и NoSQL: краткий обзор

Реляционные (SQL) СУБД

  • Принцип: данные в таблицах со строгой схемой, связи через внешние ключи.
  • Язык запросов: SQL (Structured Query Language) — единый стандарт.
  • Примеры: PostgreSQL, MySQL, SQLite, Oracle, SQL Server.
  • Сильные стороны: ACID-транзакции (надёжность), строгая схема, мощные запросы с JOIN, зрелость.

Нереляционные (NoSQL) СУБД

Объединяют несколько разных моделей хранения:

  • Документные (MongoDB) — хранят JSON-подобные документы с гибкой структурой.
  • Ключ-значение (Redis, DynamoDB) — простое и очень быстрое сопоставление ключа и значения.
  • Колоночные (Cassandra) — для аналитики на огромных объёмах.
  • Графовые (Neo4j) — для данных с множеством связей (соцсети, рекомендации).

Сильные стороны NoSQL: гибкая (или отсутствующая) схема, простое горизонтальное масштабирование, высокая производительность под конкретный сценарий.

Что выбрать

Для большинства веб-приложений с чёткой структурой данных и связями разумнее начать с реляционной СУБД. NoSQL берут под специфические задачи: кэш (Redis), слабоструктурированные документы, экстремальное масштабирование. В этом курсе мы работаем с SQL.

4. SQL: язык определения данных (DDL)

SQL делится на несколько частей. DDL (Data Definition Language) описывает и изменяет структуру БД: CREATE, ALTER, DROP.

CREATE TABLE — создание таблицы

Рассмотрим интернет-магазин: таблицы пользователей и их заказов.

CREATE TABLE users (
    id         SERIAL PRIMARY KEY,           -- автоинкрементный первичный ключ
    email      VARCHAR(255) UNIQUE NOT NULL, -- уникальный, обязательный
    name       VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    user_id    INTEGER NOT NULL,             -- внешний ключ на users
    amount     DECIMAL(10, 2) NOT NULL,
    status     VARCHAR(20) DEFAULT 'new',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Обратите внимание на ограничения (constraints):

  • PRIMARY KEY — первичный ключ;
  • NOT NULL — значение обязательно;
  • UNIQUE — значения не повторяются;
  • DEFAULT — значение по умолчанию;
  • FOREIGN KEY ... REFERENCES — внешний ключ;
  • ON DELETE CASCADE — при удалении пользователя его заказы удалятся автоматически.

ALTER TABLE — изменение таблицы

ALTER TABLE users ADD COLUMN phone VARCHAR(20);   -- добавить столбец
ALTER TABLE users ALTER COLUMN name SET NOT NULL; -- изменить ограничение
ALTER TABLE users DROP COLUMN phone;              -- удалить столбец

DROP — удаление объектов

DROP TABLE orders;   -- удалить таблицу целиком вместе с данными

DROP необратим, поэтому в реальных проектах структуру меняют через миграции (например, Alembic), а не вручную.

5. SQL: язык манипуляции данными (DML)

DML (Data Manipulation Language) работает с самими данными. Четыре операции образуют аббревиатуру CRUD: Create, Read, Update, Delete.

INSERT — добавление строк

-- Один пользователь
INSERT INTO users (email, name) VALUES ('ivan@example.com', 'Иван');


-- Несколько строк за один запрос
INSERT INTO users (email, name) VALUES
('anna@example.com', 'Анна'),
('petr@example.com', 'Пётр');


-- Заказ для пользователя с id = 1
INSERT INTO orders (user_id, amount, status) VALUES (1, 1499.00, 'paid');

SELECT — выборка строк

SELECT * FROM users;                                  -- все столбцы всех строк
SELECT email, name FROM users;                        -- только нужные столбцы
SELECT * FROM users WHERE email = 'ivan@example.com'; -- одна строка по условию

UPDATE — изменение строк

UPDATE users  SET name = 'Иван Петров'  WHERE id = 1;
UPDATE orders SET status = 'processing' WHERE status = 'new';

Важно: без WHERE команда UPDATE изменит все строки таблицы. То же относится к DELETE. Всегда проверяйте условие.

DELETE — удаление строк

DELETE FROM orders WHERE id = 42;
DELETE FROM users  WHERE email = 'petr@example.com'; -- заказы уйдут по ON DELETE CASCADE

6. Фильтрация, сортировка, агрегаты, группировка

WHERE — условия фильтрации

SELECT * FROM orders WHERE status = 'paid' AND amount >= 500;
SELECT * FROM users  WHERE name LIKE 'А%';          -- имя начинается с «А»
SELECT * FROM orders WHERE status IN ('paid', 'processing');
SELECT * FROM orders WHERE amount BETWEEN 100 AND 1000;
SELECT * FROM users  WHERE phone IS NULL;           -- телефон не указан

Операторы условий: =, <> (не равно), <, >, <=, >=, а также AND, OR, NOT, LIKE, IN, BETWEEN, IS NULL.

ORDER BY — сортировка

SELECT * FROM orders ORDER BY amount ASC;             -- по возрастанию (ASC — по умолчанию)
SELECT * FROM orders ORDER BY created_at DESC;        -- сначала новые
SELECT * FROM orders ORDER BY status ASC, amount DESC;-- несколько ключей

LIMIT — ограничение количества

SELECT * FROM orders ORDER BY amount DESC LIMIT 5;  -- 5 самых дорогих заказов

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

Агрегаты вычисляют одно значение по множеству строк:

  • COUNT(*) — количество строк;
  • SUM(col) — сумма;
  • AVG(col) — среднее;
  • MIN(col), MAX(col) — минимум и максимум.
SELECT COUNT(*)    AS total_orders FROM orders;
SELECT SUM(amount) AS revenue      FROM orders WHERE status = 'paid';

GROUP BY — группировка

GROUP BY разбивает строки на группы, и агрегат считается отдельно для каждой группы.

-- Количество заказов и сумма по каждому статусу
SELECT status, COUNT(*) AS orders_count, SUM(amount) AS total
FROM orders
GROUP BY status;

HAVING — фильтрация групп

WHERE фильтрует строки до группировки, HAVING — группы после агрегации.

-- Пользователи, потратившие больше 5000
SELECT user_id, SUM(amount) AS spent
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 5000;

7. Связи между таблицами и JOIN

Реляционные данные разнесены по таблицам, связанным через внешние ключи. Чтобы собрать их обратно в одну выборку, используют JOIN.

У нас orders.user_id ссылается на users.id. Объединим заказы с именами пользователей.

INNER JOIN

Возвращает только те строки, для которых нашлось соответствие в обеих таблицах.

SELECT u.name,
       o.id     AS order_id,
       o.amount
FROM orders o
INNER JOIN users u ON o.user_id = u.id
ORDER BY o.created_at DESC;

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

LEFT JOIN

Возвращает все строки левой таблицы; если соответствия в правой нет, её столбцы будут NULL.

-- Все пользователи, в том числе без заказов
SELECT u.name,
       o.id     AS order_id,
       o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

У пользователя без заказов order_id и amount окажутся NULL. Это удобно, например, чтобы найти неактивных клиентов или посчитать заказы каждого пользователя вместе с группировкой:

-- Клиенты без заказов вовсе
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;


-- Имя пользователя и число его заказов
SELECT u.name, COUNT(o.id) AS orders_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name
ORDER BY orders_count DESC;

Кроме INNER и LEFT есть RIGHT JOIN (зеркало LEFT) и FULL JOIN (все строки обеих таблиц), но на практике чаще хватает первых двух.

8. Нормализация (кратко)

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

Представим «плохую» таблицу, где имя и email пользователя продублированы в каждой строке заказа. При смене email придётся обновлять много строк (аномалия обновления), а данные легко рассинхронизировать. Решение — вынести пользователя в отдельную таблицу и ссылаться по user_id, что мы и сделали выше.

Первые три нормальные формы кратко:

  • 1НФ: каждое поле хранит одно атомарное значение (никаких списков «через запятую» в одной ячейке).
  • 2НФ: 1НФ + каждый неключевой столбец зависит от всего первичного ключа.
  • 3НФ: 2НФ + неключевые столбцы зависят только от ключа, а не друг от друга.

На практике стремятся к 3НФ как разумному балансу. Иногда ради скорости чтения данные намеренно денормализуют (дублируют), но это осознанный компромисс, а не отправная точка.

Краткие итоги

  • База данных хранит состояние веб-приложения отдельно от stateless-backend, обеспечивая постоянство, целостность и конкурентный доступ.
  • Реляционная модель организует данные в таблицы из строк и столбцов со строгой схемой; связи задаются первичными и внешними ключами.
  • SQL-СУБД (PostgreSQL, MySQL, SQLite) дают ACID и мощные запросы; NoSQL — гибкую схему и горизонтальное масштабирование под специфические задачи.
  • DDL (CREATE, ALTER, DROP) описывает структуру; DML (SELECT, INSERT, UPDATE, DELETE) работает с данными (CRUD).
  • WHERE фильтрует строки, ORDER BY сортирует, LIMIT ограничивает, агрегаты (COUNT, SUM, AVG) с GROUP BY/HAVING считают сводки.
  • JOIN собирает данные из связанных таблиц: INNER JOIN — только совпадения, LEFT JOIN — все строки левой таблицы.
  • Нормализация (до 3НФ) убирает дублирование и аномалии, храня каждый факт в одном месте.

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

  1. Почему данные веб-приложения хранят в БД, а не в памяти процесса? Какие гарантии даёт СУБД?
  2. Чем отличаются таблица, строка и столбец? Что такое схема таблицы?
  3. В чём разница между первичным и внешним ключом? Зачем нужен ON DELETE CASCADE?
  4. Когда стоит выбрать реляционную СУБД, а когда NoSQL? Приведите по примеру.
  5. К какой части SQL (DDL или DML) относятся CREATE TABLE, INSERT, ALTER, DELETE?
  6. Что произойдёт при выполнении UPDATE или DELETE без WHERE?
  7. Чем отличается фильтрация в WHERE от фильтрации в HAVING?
  8. Напишите запрос: сумма оплаченных заказов по каждому пользователю, отсортированная по убыванию.
  9. В чём разница между INNER JOIN и LEFT JOIN? Как с помощью LEFT JOIN найти пользователей без заказов?
  10. Что такое нормализация? Какую проблему решает вынесение данных пользователя в отдельную таблицу?