Provision
—— XP

Практика: запросы к учебной базе

практика~20 минут+450 XP

В прошлом уроке ты видел запросы со стороны. Теперь наберёшь их сам на базе, которую завёл в практике с Compose. Все ответы в уроке настоящие: данные у всех одинаковые, поэтому числа должны совпасть до единицы.

Работай в интерактивном psql и выходи из него командой \q: только тогда psql сохранит историю запросов, по которой проверяется сдача.

Шаг 1. Подключиться

Перейди в каталог стенда и открой psql. Флаг -U — имя пользователя. Базу можно не называть: psql по умолчанию подключается к базе с тем же именем, что у пользователя, а у нас обе называются shop.

ubuntu@stand:~$
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. Сколько покупателей

Самый короткий полезный запрос — подсчёт. С него удобно начинать знакомство с любой таблицей: сразу видно, маленькая она или огромная.

ubuntu@stand:~$
shop=# SELECT count(*) FROM customers;
 count
-------
     5
(1 row)

Пять строк — можно смело смотреть все. Будь там пять миллионов, следующий шаг был бы только с LIMIT.

Шаг 3. Покупатели из одного города

Условие ставится после WHERE, текст — в одинарных кавычках.

ubuntu@stand:~$
shop=# SELECT name FROM customers WHERE city = 'Казань';
 name
-------
 Борис
 Дина
(2 rows)

Теперь нарочно ошибись — поставь двойные кавычки:

ubuntu@stand:~$
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, нужен, чтобы при одинаковых суммах порядок был предсказуемым.

ubuntu@stand:~$
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. Заказы по статусам

Группировка раскладывает строки по кучкам и считает каждую.

ubuntu@stand:~$
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 их раскладывает.

ubuntu@stand:~$
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 заказа.

ubuntu@stand:~$
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.

Если получилось, ответ будет таким:

ubuntu@stand:~$
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(*) их считает.

ubuntu@stand:~$
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. Именно при выходе он записывает историю набранных запросов в файл внутри контейнера.

ubuntu@stand:~$
shop=# \q
$

Стрелка вверх в следующий раз вернёт любой из сегодняшних запросов. История живёт в контейнере, поэтому после docker compose down она пропадёт, а данные в томе останутся.

Шаг 11. Запрос одной строкой из терминала

Иногда в psql заходить не нужно: достаточно одной команды и её ответа. Для этого есть флаг -c — выполнить запрос и сразу выйти.

ubuntu@stand:~$
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 и транзакция.

Практика 02+450 XP

Отвечать на вопрос «что в базе» коротким запросом: отбор, сортировка, ограничение, подсчёт и соединение двух таблиц.

  • Отбор по условию
  • Ограничение ответа
  • Подсчёт по группам
  • Соединение таблиц
  • Данные не испорчены
Команда сдачи — выполни на своей машине
команда
cd ~/infra/db 2>/dev/null || echo NODB; H=/root/.psql_history; for w in WHERE LIMIT GROUP JOIN; do echo $w=$(docker compose exec -T db grep -ci $w $H); done; echo ANNA=$(docker compose exec -T db psql -U shop -Atc "SELECT count(*) FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.name = 'Анна'")

Нашёл ошибку или непонятное место? Наведи на абзац — слева появится «+», нажми и напиши, что не так. Замечание увидит автор курса.Нашёл ошибку или непонятное место? Нажми «+» справа от заголовка раздела и напиши, что не так. Замечание увидит автор курса.