Практика 10. Практическая работа 10. SQL: запросы к базе данных

Цель

Научиться создавать схему БД на языке DDL (таблицы, ключи, ограничения).
Наполнять таблицы данными командой INSERT.
Писать запросы SELECT с фильтрацией (WHERE), сортировкой (ORDER BY) и ограничением (LIMIT).
Применять агрегатные функции и группировку (GROUP BY / HAVING).
Объединять таблицы через JOIN, изменять и удалять данные (UPDATE / DELETE).
Создавать индекс и читать план выполнения запроса (EXPLAIN).

Работаем с SQLite — встроенной СУБД, которая хранит всю базу в одном файле и не требует отдельного сервера. Это идеально для учебной практики (см. лекции 7 и 8).

Теория (кратко)

DDL (Data Definition Language) — описывает структуру: CREATE TABLE, ALTER TABLE, DROP.
DML (Data Manipulation Language) — работает с данными: INSERT, SELECT, UPDATE, DELETE (CRUD).
Ключи: PRIMARY KEY однозначно определяет строку; FOREIGN KEY ссылается на первичный ключ другой таблицы и обеспечивает целостность связей.
Агрегаты (COUNT, SUM, AVG, MIN, MAX) считают одно значение по множеству строк; GROUP BY разбивает строки на группы, HAVING фильтрует уже готовые группы.
JOIN соединяет строки разных таблиц по условию. INNER JOIN — только совпадения; LEFT JOIN — все строки левой таблицы.
Индекс — структура (обычно B-tree), ускоряющая поиск по столбцу ценой замедления записи и доп. места.
EXPLAIN показывает, как СУБД выполнит запрос (использует индекс или сканирует таблицу целиком), не выдавая сами данные.

Особенности SQLite относительно PostgreSQL из лекции: вместо SERIAL используют INTEGER PRIMARY KEY AUTOINCREMENT, вместо EXPLAIN ANALYZE — EXPLAIN QUERY PLAN. Внешние ключи нужно явно включить: PRAGMA foreign_keys = ON;.

Задание

Шаг 0. Подготовка

Установка SQLite не требуется — CLI обычно идёт с системой, а модуль sqlite3 входит в стандартную библиотеку Python. Проверьте:

sqlite3 --version
python --version

Запустите интерактивную оболочку с файлом базы (создастся автоматически):

sqlite3 shop.db

Внутри оболочки включите удобный вывод и поддержку внешних ключей:

.headers on
.mode column
PRAGMA foreign_keys = ON;

Выйти из оболочки — команда .quit.

Шаг 1. Создание схемы (DDL)

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

CREATE TABLE users (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    email      TEXT    NOT NULL UNIQUE,
    name       TEXT    NOT NULL,
    city       TEXT,
    created_at TEXT    DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id    INTEGER NOT NULL,
    amount     REAL    NOT NULL,
    status     TEXT    DEFAULT 'new',
    created_at TEXT    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Проверьте структуру:

.tables
.schema orders

Шаг 2. Наполнение данными (INSERT)

INSERT INTO users (email, name, city) VALUES
('ivan@example.com',  'Иван',   'Москва'),
('anna@example.com',  'Анна',   'Казань'),
('petr@example.com',  'Пётр',   'Москва'),
('olga@example.com',  'Ольга',  'Сочи');

INSERT INTO orders (user_id, amount, status) VALUES
(1, 1499.00, 'paid'),
(1,  250.00, 'paid'),
(2, 3200.50, 'new'),
(2,  780.00, 'paid'),
(3,  990.00, 'processing'),
(1, 5400.00, 'paid');

Убедитесь, что данные на месте: SELECT COUNT(*) FROM users; SELECT COUNT(*) FROM orders;.

Шаг 3. SELECT с WHERE / ORDER BY / LIMIT

Задание 1. Выведите имя и email пользователей из Москвы, отсортированных по имени:

SELECT name, email
FROM users
WHERE city = 'Москва'
ORDER BY name ASC;

Задание 2. Покажите 3 самых дорогих оплаченных заказа (status = 'paid'):

SELECT id, user_id, amount
FROM orders
WHERE status = 'paid'
ORDER BY amount DESC
LIMIT 3;

Шаг 4. Агрегаты и GROUP BY

Задание 3. Для каждого пользователя посчитайте количество заказов и общую сумму:

SELECT user_id,
       COUNT(*)    AS orders_count,
       SUM(amount) AS total
FROM orders
GROUP BY user_id
ORDER BY total DESC;

Задание 4. Оставьте только пользователей, у которых сумма заказов больше 2000 (фильтрация групп через HAVING):

SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 2000;

Шаг 5. JOIN

Задание 5. Соедините таблицы и выведите имя пользователя рядом с каждым заказом (INNER JOIN):

SELECT u.name, o.amount, o.status
FROM orders o
INNER JOIN users u ON u.id = o.user_id
ORDER BY u.name;

Задание 6. Покажите всех пользователей и их суммарные траты, включая тех, у кого заказов нет (LEFT JOIN):

SELECT u.name, COALESCE(SUM(o.amount), 0) AS total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id
ORDER BY total DESC;

У Ольги заказов нет — в INNER JOIN её не будет, а в LEFT JOIN она появится с суммой 0.

Шаг 6. UPDATE и DELETE

Задание 7. Переведите все заказы со статусом new в processing, затем удалите один заказ по id:

UPDATE orders SET status = 'processing' WHERE status = 'new';

DELETE FROM orders WHERE id = 2;

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

Шаг 7. Индекс и EXPLAIN

Посмотрите план запроса по столбцу status до индекса (SCAN — полное сканирование таблицы):

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';

Создайте индекс и повторите анализ — план должен измениться на SEARCH ... USING INDEX:

CREATE INDEX idx_orders_status ON orders(status);

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';

Сравните оба вывода и сделайте вывод, зачем нужен индекс.

Шаг 8. (Необязательно) То же из Python

Те же запросы можно выполнить программно через модуль sqlite3:

import sqlite3

conn = sqlite3.connect("shop.db")
cur = conn.cursor()
cur.execute("""
    SELECT u.name, SUM(o.amount) AS total
    FROM users u
    JOIN orders o ON o.user_id = u.id
    GROUP BY u.id ORDER BY total DESC
""")
for name, total in cur.fetchall():
    print(name, total)
conn.close()

Критерии оценки

Что проверяется	Баллы
Схема создана корректно: таблицы, `PRIMARY KEY`, `FOREIGN KEY`, ограничения	15%
Данные добавлены через `INSERT` (минимум 4 пользователя и 6 заказов)	10%
Задания 1–2: `SELECT` с `WHERE`, `ORDER BY`, `LIMIT`	15%
Задания 3–4: агрегаты, `GROUP BY`, `HAVING`	20%
Задания 5–6: `INNER JOIN` и `LEFT JOIN`	20%
Задание 7: `UPDATE` и `DELETE` с условием	10%
Шаг 7: создан индекс, показан и объяснён `EXPLAIN QUERY PLAN`	10%

Итого: 100%. Работа засчитывается от 60%.

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

Чем отличается PRIMARY KEY от FOREIGN KEY? Что делает ON DELETE CASCADE?
В чём разница между WHERE и HAVING? Можно ли в WHERE использовать агрегатную функцию?
Что вернёт INNER JOIN, а что — LEFT JOIN, если у пользователя нет ни одного заказа?
Зачем нужен GROUP BY и какие столбцы можно перечислять в SELECT вместе с агрегатами?
Что произойдёт, если выполнить UPDATE или DELETE без WHERE?
Зачем нужен индекс и какова его «цена»? В каких случаях он не ускорит запрос?
Что показывает EXPLAIN QUERY PLAN и чем SCAN отличается от SEARCH ... USING INDEX?