SQL и sqlite3

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

Реляционная база хранит данные в таблицах: строки — записи, колонки — поля с типами. Таблицы связываются через ключи: первичный ключ (PRIMARY KEY) однозначно определяет строку, внешний (FOREIGN KEY) ссылается на строку другой таблицы.

customers orders ┌────┬───────┬────────┐ ┌────┬─────────────┬────────┐ │ id │ name │ city │ │ id │ customer_id │ amount │ ├────┼───────┼────────┤ ├────┼─────────────┼────────┤ │ 1 │ Аня │ Казань │◀──┐ │ 1 │ 1 │ 500 │ │ 2 │ Борис │ Москва │◀─┐└───│ 2 │ 1 │ 250 │ │ 3 │ Вера │ Омск │ └────│ 3 │ 2 │ 900 │ └────┴───────┴────────┘ └────┴─────────────┴────────┘ один клиент — много заказов (связь «один ко многим»)

SQLite — база в одном файле, встроенная в Python (import sqlite3), не требует сервера. Её используют в мобильных приложениях, браузерах, тестах и прототипах. В продакшене веб-сервисов чаще PostgreSQL, но SQL почти одинаковый. Все примеры урока выполняются в настоящей SQLite прямо в браузере.

Первые запросы

import sqlite3

conn = sqlite3.connect(":memory:")          # база в памяти; для файла — "shop.db"
conn.executescript("""
    CREATE TABLE customers (
        id   INTEGER PRIMARY KEY,           -- в SQLite это автоинкремент rowid
        name TEXT NOT NULL,
        city TEXT
    );
    CREATE TABLE orders (
        id          INTEGER PRIMARY KEY,
        customer_id INTEGER NOT NULL REFERENCES customers(id),
        amount      INTEGER NOT NULL CHECK (amount > 0),
        created     TEXT NOT NULL           -- даты в SQLite хранят строками ISO 8601
    );
    INSERT INTO customers (name, city) VALUES
        ('Аня', 'Казань'), ('Борис', 'Москва'), ('Вера', 'Москва'), ('Гоша', 'Омск');
    INSERT INTO orders (customer_id, amount, created) VALUES
        (1, 500, '2026-01-10'), (1, 250, '2026-02-03'), (2, 900, '2026-02-15'),
        (3, 120, '2026-03-01'), (3, 700, '2026-03-20'), (2, 300, '2026-03-28');
""")

def show(sql, params=()):
    cur = conn.execute(sql, params)
    names = [d[0] for d in cur.description]
    rows = cur.fetchall()
    widths = [max(len(str(x)) for x in [n, *[r[i] for r in rows]]) for i, n in enumerate(names)]
    print(" | ".join(n.ljust(w) for n, w in zip(names, widths)))
    print("-+-".join("-" * w for w in widths))
    for r in rows:
        print(" | ".join(str(x).ljust(w) for x, w in zip(r, widths)))
    print()

show("SELECT * FROM customers")
show("SELECT name, city FROM customers WHERE city = 'Москва' ORDER BY name DESC")
show("SELECT id, amount FROM orders WHERE amount BETWEEN 200 AND 600 ORDER BY amount DESC LIMIT 2")
show("SELECT name FROM customers WHERE name LIKE 'В%' OR city IN ('Омск', 'Сочи')")

Порядок записи запроса: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT. Порядок выполнения другой: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Поэтому псевдоним из SELECT нельзя использовать в WHERE, но можно в ORDER BY.

NULL

NULL — «значение неизвестно». Любое сравнение с ним даёт NULL (не истину), поэтому = NULL никогда не срабатывает.

import sqlite3
conn = sqlite3.connect(":memory:")
print(conn.execute("SELECT NULL = NULL, NULL IS NULL, 1 + NULL, COALESCE(NULL, 'по умолчанию')").fetchone())
conn.executescript("CREATE TABLE t (x); INSERT INTO t VALUES (1), (NULL), (3);")
print(conn.execute("SELECT count(*), count(x), sum(x), avg(x) FROM t").fetchone())   # агрегаты пропускают NULL
print(conn.execute("SELECT x FROM t WHERE x != 1").fetchall())                       # NULL не попал!

Агрегаты и GROUP BY

import sqlite3
conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT);
    CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER, created TEXT);
    INSERT INTO customers (name, city) VALUES ('Аня', 'Казань'), ('Борис', 'Москва'), ('Вера', 'Москва'), ('Гоша', 'Омск');
    INSERT INTO orders (customer_id, amount, created) VALUES (1, 500, '2026-01-10'), (1, 250, '2026-02-03'),
        (2, 900, '2026-02-15'), (3, 120, '2026-03-01'), (3, 700, '2026-03-20'), (2, 300, '2026-03-28');
""")

print(conn.execute("SELECT count(*), sum(amount), avg(amount), min(amount), max(amount) FROM orders").fetchone())

# по клиентам
for row in conn.execute("""
    SELECT customer_id, count(*) AS n, sum(amount) AS total
    FROM orders
    GROUP BY customer_id
    HAVING total > 800              -- фильтр по группам
    ORDER BY total DESC
"""):
    print(row)

# по месяцам
for row in conn.execute("SELECT substr(created, 1, 7) AS month, sum(amount) FROM orders GROUP BY month"):
    print(row)

WHERE против HAVING

WHERE отбирает строки до группировки, HAVING — группы после. Условие на агрегат (sum(amount) > 800) может быть только в HAVING.

JOIN

INNER JOIN — только пары, где есть совпадение LEFT JOIN — все строки левой таблицы; справа NULL, если пары нет RIGHT JOIN — наоборот (в SQLite с 3.39) FULL JOIN — все строки обеих таблиц CROSS JOIN — все со всеми (декартово произведение)

import sqlite3
conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT);
    CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
    INSERT INTO customers (name, city) VALUES ('Аня', 'Казань'), ('Борис', 'Москва'), ('Вера', 'Москва'), ('Гоша', 'Омск');
    INSERT INTO orders (customer_id, amount) VALUES (1, 500), (1, 250), (2, 900), (3, 120), (99, 50);
""")

print("INNER:", conn.execute("""
    SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id
""").fetchall())

print("LEFT: ", conn.execute("""
    SELECT c.name, o.amount FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
""").fetchall())

print("без заказов:", conn.execute("""
    SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL
""").fetchall())

print("FULL: ", conn.execute("""
    SELECT c.name, o.amount FROM customers c FULL JOIN orders o ON o.customer_id = c.id
""").fetchall())

print("итоги:", conn.execute("""
    SELECT c.name, count(o.id), coalesce(sum(o.amount), 0)
    FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id ORDER BY 3 DESC
""").fetchall())

count(*) против count(колонка)

В LEFT JOIN у клиента без заказов одна строка с NULL справа. count(*) посчитает её (1), а count(o.id) — нет (0).

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

import sqlite3
conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
    INSERT INTO orders (customer_id, amount) VALUES (1, 500), (1, 250), (2, 900), (3, 120), (3, 700), (2, 300);
""")

# заказы дороже среднего
print(conn.execute("SELECT id, amount FROM orders WHERE amount > (SELECT avg(amount) FROM orders)").fetchall())

# CTE (WITH) — именованный подзапрос, читается сверху вниз
print(conn.execute("""
    WITH totals AS (
        SELECT customer_id, sum(amount) AS total FROM orders GROUP BY customer_id
    )
    SELECT customer_id, total FROM totals WHERE total = (SELECT max(total) FROM totals)
""").fetchall())

# рекурсивный CTE: числа от 1 до 5 (так обходят деревья: категории, комментарии, оргструктуру)
print(conn.execute("""
    WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM n WHERE x < 5)
    SELECT group_concat(x, ',') FROM n
""").fetchone())

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

Агрегат по «окну» строк, не схлопывая их в одну. Любимая тема собеседований: «топ-N в каждой группе», «нарастающий итог», «разница с предыдущей строкой».

import sqlite3
conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE sales (manager TEXT, month TEXT, amount INTEGER);
    INSERT INTO sales VALUES
        ('Аня', '01', 100), ('Аня', '02', 150), ('Аня', '03', 90),
        ('Борис', '01', 200), ('Борис', '02', 120), ('Борис', '03', 210);
""")

for row in conn.execute("""
    SELECT manager, month, amount,
           sum(amount) OVER (PARTITION BY manager ORDER BY month)        AS running_total,
           amount - lag(amount) OVER (PARTITION BY manager ORDER BY month) AS diff,
           rank() OVER (ORDER BY amount DESC)                            AS place
    FROM sales
    ORDER BY manager, month
"""):
    print(row)

print("лучший месяц каждого менеджера:")
print(conn.execute("""
    SELECT manager, month, amount FROM (
        SELECT *, row_number() OVER (PARTITION BY manager ORDER BY amount DESC) AS rn FROM sales
    ) WHERE rn = 1
""").fetchall())

ROW_NUMBER нумерует подряд (1, 2, 3), RANK даёт одинаковым значениям одно место с пропуском (1, 1, 3), DENSE_RANK — без пропуска (1, 1, 2).

Изменение данных

import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE products (sku TEXT PRIMARY KEY, title TEXT, stock INTEGER DEFAULT 0)")

conn.executemany("INSERT INTO products (sku, title, stock) VALUES (?, ?, ?)",
                 [("A1", "Кружка", 10), ("B2", "Футболка", 0), ("C3", "Блокнот", 5)])
conn.execute("UPDATE products SET stock = stock - 1 WHERE sku = ?", ("A1",))
conn.execute("DELETE FROM products WHERE stock = 0")

# UPSERT: вставить или обновить при конфликте ключа
conn.execute("""
    INSERT INTO products (sku, title, stock) VALUES ('C3', 'Блокнот', 7)
    ON CONFLICT (sku) DO UPDATE SET stock = stock + excluded.stock
""")
print(conn.execute("SELECT * FROM products ORDER BY sku").fetchall())

row = conn.execute("INSERT INTO products (sku, title) VALUES ('D4', 'Ручка') RETURNING rowid, sku").fetchone()
print("вставлено:", row)

UPDATE и DELETE без WHERE

DELETE FROM products удалит всё. Перед опасным запросом выполните тот же SELECT … WHERE и посмотрите, что попадёт под удаление; работайте в транзакции.

Индексы

Без индекса поиск по условию — полный перебор таблицы. Индекс (обычно B-дерево) — отсортированная структура «значение → строки», поиск за O(log n).

import sqlite3, time
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER)")
conn.executemany("INSERT INTO orders (customer_id, amount) VALUES (?, ?)",
                 ((i % 5000, i % 997) for i in range(200_000)))

query = "SELECT count(*), sum(amount) FROM orders WHERE customer_id = 1234"

def measure():
    start = time.perf_counter()
    for _ in range(20):
        conn.execute(query).fetchone()
    return (time.perf_counter() - start) / 20 * 1000

print(conn.execute("EXPLAIN QUERY PLAN " + query).fetchall())
print(f"без индекса: {measure():.2f} мс")

conn.execute("CREATE INDEX ix_orders_customer ON orders (customer_id)")
print(conn.execute("EXPLAIN QUERY PLAN " + query).fetchall())
print(f"с индексом:  {measure():.3f} мс")

Что важно знать про индексы:

PRIMARY KEY и UNIQUE создают индекс автоматически; внешний ключ — нет (в PostgreSQL тоже), его индекс добавляют сами.
Составной индекс (a, b) помогает запросам по a и по a AND b, но не по одному b (правило левого префикса).
Индекс не поможет, если колонка обёрнута в функцию (WHERE lower(email) = …) или условие LIKE '%текст'.
Каждый индекс замедляет запись и занимает место. Создавайте их под реальные медленные запросы и проверяйте план (EXPLAIN / EXPLAIN ANALYZE в PostgreSQL).

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

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

Atomicity — атомарность: всё или ничего;
Consistency — согласованность: ограничения не нарушаются;
Isolation — изоляция: параллельные транзакции не видят «половинчатых» изменений друг друга;
Durability — долговечность: после COMMIT данные переживут сбой.

import sqlite3
conn = sqlite3.connect(":memory:")
conn.executescript("CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER CHECK (balance >= 0)); INSERT INTO accounts VALUES (1, 100), (2, 0);")

def transfer(amount):
    with conn:       # BEGIN … COMMIT, а при исключении — ROLLBACK
        conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = 2", (amount,))
        conn.execute("UPDATE accounts SET balance = balance - ? WHERE id = 1", (amount,))

transfer(30)
print(conn.execute("SELECT * FROM accounts").fetchall())
try:
    transfer(500)        # второе UPDATE нарушит CHECK — первое тоже откатится
except sqlite3.IntegrityError as e:
    print("ошибка:", e)
print(conn.execute("SELECT * FROM accounts").fetchall())

Уровни изоляции (SQL-стандарт, важны в PostgreSQL/MySQL) — компромисс между корректностью и параллельностью:

Уровень	Грязное чтение	Неповторяемое чтение	Фантомы
Read Uncommitted	возможно	возможно	возможны
Read Committed (по умолчанию в PostgreSQL)	нет	возможно	возможны
Repeatable Read	нет	нет	возможны*
Serializable	нет	нет	нет

* в PostgreSQL на Repeatable Read фантомов тоже нет. Классическая гонка «прочитал остаток → проверил → записал» решается атомарным UPDATE … WHERE balance >= ?, блокировкой SELECT … FOR UPDATE (PostgreSQL) или уровнем Serializable с повтором транзакции.

Модуль sqlite3: главное

import sqlite3

conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row            # строки как словари: row["name"]
conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)")
conn.executemany("INSERT INTO users (name, email) VALUES (:name, :email)", [
    {"name": "Аня", "email": "anya@example.com"},
    {"name": "Борис", "email": "boris@example.com"},
])
conn.commit()

row = conn.execute("SELECT * FROM users WHERE name = ?", ("Аня",)).fetchone()
print(row["id"], row["email"], dict(row))

for user in conn.execute("SELECT name FROM users ORDER BY name"):
    print(user["name"])

conn.close()

? или :имя — параметры: значения передаются отдельно от текста запроса.
fetchone() / fetchall() / итерация по курсору (лениво, для больших выборок).
with conn: — транзакция; само соединение он не закрывает.

SQL-инъекции

import sqlite3
conn = sqlite3.connect(":memory:")
conn.executescript("CREATE TABLE users (login TEXT, password_hash TEXT); INSERT INTO users VALUES ('admin', 'x'), ('anna', 'y');")

login = "' OR '1'='1"
unsafe = f"SELECT login FROM users WHERE login = '{login}'"
print(unsafe)
print("форматирование:", conn.execute(unsafe).fetchall())          # вернулись все!
print("параметры:     ", conn.execute("SELECT login FROM users WHERE login = ?", (login,)).fetchall())

Никогда не собирайте SQL из пользовательских данных через f-строки, % или +. Параметры нельзя использовать для имён таблиц и колонок — их выбирают из белого списка.

Проектирование: нормализация и связи

Один ко многим: внешний ключ на стороне «многих» (orders.customer_id).
Многие ко многим: промежуточная таблица (student_courses(student_id, course_id) с составным первичным ключом).
Нормализация: каждый факт хранится в одном месте. Не храните город клиента в каждом заказе — при переезде придётся обновлять все строки (аномалия обновления). Нормальные формы: 1НФ — атомарные значения, без списков в ячейке; 2НФ — нет зависимости от части составного ключа; 3НФ — нет зависимости неключевых полей друг от друга.
Денормализация — осознанное дублирование ради скорости чтения (счётчики, кеши отчётов).

PostgreSQL против SQLite

	SQLite	PostgreSQL
Запуск	файл, без сервера	отдельный сервер
Параллельная запись	один писатель за раз	много одновременных транзакций (MVCC)
Типы	гибкие (`TEXT`, `INTEGER`, `REAL`, `BLOB`)	строгие, богатые: `timestamptz`, `jsonb`, `uuid`, массивы
Где	тесты, мобильные и десктопные приложения, прототипы	веб-сервисы в продакшене

Из Python к PostgreSQL подключаются драйвером psycopg (или asyncpg), интерфейс похож на sqlite3 (DB-API), только параметры пишутся %s. Чаще — через ORM, о нём следующий урок.

Вопросы на собеседовании

Виды JOIN и чем LEFT отличается от INNER.
WHERE против HAVING; порядок выполнения запроса.
Что такое индекс, как он устроен, когда не помогает; составной индекс.
ACID, уровни изоляции, что такое грязное и неповторяемое чтение.
Как защититься от SQL-инъекций?
Задачи: второй по величине заказ, топ-3 в каждой категории (оконные функции), дубликаты (GROUP BY … HAVING count(*) > 1), клиенты без заказов (LEFT JOIN … IS NULL или NOT EXISTS).

Итог

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT; NULL сравнивается через IS.
JOIN соединяет таблицы по ключам; LEFT JOIN оставляет строки без пары.
Оконные функции — агрегаты без схлопывания строк.
Индексы ускоряют чтение ценой записи; проверяйте план.
Транзакции через with conn:; значения — только параметрами.

	INNER JOIN	LEFT JOIN (customers слева)	FULL OUTER JOIN
Клиент с заказами
Клиент без заказов
Заказ без клиента

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

Первые запросы

NULL

Агрегаты и GROUP BY

JOIN

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

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

Изменение данных

Индексы

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

Модуль sqlite3: главное

SQL-инъекции

Проектирование: нормализация и связи

PostgreSQL против SQLite

Вопросы на собеседовании

Итог

Практика решено 0 из 7