В прошлом уроке ты видел запросы со стороны. Теперь наберёшь их сам на базе, которую завёл в практике с Compose. Все ответы в уроке настоящие: данные у всех одинаковые, поэтому числа должны совпасть до единицы.
Работай в интерактивном psql и выходи из него командой \q: только тогда psql сохранит историю запросов, по которой проверяется сдача.
Шаг 1. Подключиться
Перейди в каталог стенда и открой psql. Флаг -U — имя пользователя. Базу можно не называть: psql по умолчанию подключается к базе с тем же именем, что у пользователя, а у нас обе называются shop.
cd ~/infra/db docker compose exec db psql -U shop psql (17.11) Type "help" for help. shop=#
Если вместо приглашения пришло service "db" is not running — база остановлена, подними её docker compose up -d.
Шаг 2. Сколько покупателей
Самый короткий полезный запрос — подсчёт. С него удобно начинать знакомство с любой таблицей: сразу видно, маленькая она или огромная.
shop=# SELECT count(*) FROM customers; count ------- 5 (1 row)
Пять строк — можно смело смотреть все. Будь там пять миллионов, следующий шаг был бы только с LIMIT.
Шаг 3. Покупатели из одного города
Условие ставится после WHERE, текст — в одинарных кавычках.
shop=# SELECT name FROM customers WHERE city = 'Казань'; name ------- Борис Дина (2 rows)
Теперь нарочно ошибись — поставь двойные кавычки:
shop=# SELECT name FROM customers WHERE city = "Казань"; ERROR: column "Казань" does not exist LINE 1: SELECT name FROM customers WHERE city = "Казань"; ^
База решила, что "Казань" — имя столбца, и не нашла его. Галочка ^ под строкой показывает, где именно она споткнулась. Запомни, как выглядит эта ошибка: в чужих скриптах она встречается постоянно.
Шаг 4. Пять самых дорогих заказов
Сортировка по убыванию — ORDER BY … DESC, ограничение — LIMIT. Второй столбец в сортировке, id, нужен, чтобы при одинаковых суммах порядок был предсказуемым.
shop=# SELECT id, amount FROM orders ORDER BY amount DESC, id LIMIT 5; id | amount -------+-------- 2027 | 5099 7027 | 5099 12027 | 5099 17027 | 5099 22027 | 5099 (5 rows)
Максимальная сумма — 5099, и таких заказов много. Без LIMIT psql вывел бы все двести тысяч строк и включил постраничный просмотр; выйти из него можно клавишей q.
Шаг 5. Заказы по статусам
Группировка раскладывает строки по кучкам и считает каждую.
shop=# SELECT status, count(*) FROM orders GROUP BY status; status | count --------+------- done | 66667 paid | 66667 new | 66666 (3 rows)
Порядок строк здесь может отличаться от твоего: мы не просили сортировку. Если хочешь стабильный порядок — допиши ORDER BY status.
Шаг 6. Статусы заказов одного покупателя
Условие и группировка работают вместе: сначала WHERE отбирает строки, потом GROUP BY их раскладывает.
shop=# SELECT status, count(*) FROM orders WHERE customer_id = 1 GROUP BY status; status | count --------+------- done | 13334 new | 13333 paid | 13333 (3 rows)
Покупатель номер 1 — это Анна. Но в заказе видно только число, и чтобы работать с именами, нужен следующий шаг.
Шаг 7. Имена рядом с суммами
JOIN приставляет к каждому заказу строку покупателя, у которого id совпадает с customer_id заказа.
shop=# SELECT c.name, o.amount FROM orders o JOIN customers c ON c.id = o.customer_id ORDER BY o.id LIMIT 3; name | amount -------+-------- Борис | 137 Вера | 174 Глеб | 211 (3 rows)
Буквы o и c — прозвища таблиц, они объявлены прямо после имён в FROM и JOIN.
Шаг 8. Задача: сколько заказов у Анны
Теперь сам. Нужно посчитать заказы покупателя по имени, а не по номеру. Подсказка: возьми запрос из шага 7, замени столбцы на count(*), убери сортировку и ограничение и добавь условие на c.name.
Если получилось, ответ будет таким:
shop=# SELECT count(*) FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.name = 'Анна'; count ------- 40000 (1 row)
Сорок тысяч — каждый пятый заказ из двухсот тысяч, как мы и задумывали при наполнении.
Шаг 9. Выручка по городам
Последний новый кусок — функция sum(). Она складывает значения столбца в каждой группе так же, как count(*) их считает.
shop=# SELECT c.city, sum(o.amount) FROM orders o JOIN customers c ON c.id = o.customer_id GROUP BY c.city ORDER BY sum(o.amount) DESC; city | sum --------+----------- Казань | 208000000 Москва | 207960000 Пермь | 103940000 (3 rows)
Рядом с sum живут avg (среднее), min и max. Работают точно так же, их хватает на большинство быстрых проверок.
Шаг 10. Выйти и сохранить историю
Выйди из psql. Именно при выходе он записывает историю набранных запросов в файл внутри контейнера.
shop=# \q $
Стрелка вверх в следующий раз вернёт любой из сегодняшних запросов. История живёт в контейнере, поэтому после docker compose down она пропадёт, а данные в томе останутся.
Шаг 11. Запрос одной строкой из терминала
Иногда в psql заходить не нужно: достаточно одной команды и её ответа. Для этого есть флаг -c — выполнить запрос и сразу выйти.
docker compose exec db psql -U shop -c '\dt' Schema | Name | Type | Owner --------+-----------+-------+------- public | customers | table | shop public | orders | table | shop
Так же запускают и обычный SQL. Запрос берут в одинарные кавычки, чтобы оболочка не тронула звёздочки и пробелы.
Что дальше
Читать ты научился. Дальше — как данные менять так, чтобы ошибку можно было отменить: INSERT, UPDATE, DELETE и транзакция.