Основы баз данных: таблицы, ключи и запросы SQL

Таблицы клиентов и заказов

Возьмём вымышленный учёт. В таблице клиентов одна строка описывает клиента: идентификатор и название. В таблице заказов строка описывает заказ: собственный номер, идентификатор клиента и сумма. Один клиент может иметь несколько заказов, поэтому его описание хранится отдельно от покупок.

Клиент 1 — Север, клиент 2 — Юг. У первого заказы 100 и 200 рублей, у второго — 300 рублей. Для связи используем идентификатор, для отображения — название. Если название изменится, связь по ключу может сохраниться; не нужно искать заказы по текстовому совпадению имени.

Различайте ключ и обычный столбец

Первичный ключ однозначно идентифицирует строку: его значение или сочетание значений уникально и не допускает NULL. Внешний ключ проверяет ссылку на допустимую запись — в другой или той же таблице. В документации PostgreSQL показано, как ограничение отклоняет ссылку на несуществующую запись.

У заказа хранится идентификатор клиента. Проверка такой ссылки не доказывает правильность цены или имени: это другой уровень контроля. Отдельно определяют, обязательна ли связь, как обрабатывают неизвестное значение и что происходит при изменении связанных записей. Для введения достаточно записать вопросы к схеме, не проектируя промышленную систему.

Простой запрос SELECT

SELECT задаёт возвращаемые поля, FROM — источник, WHERE — условие, ORDER BY — порядок результата. В нашем примере запрос «SELECT id, amount FROM orders WHERE amount > 150 ORDER BY id» отберёт заказы 200 и 300 рублей. Названия таблицы и столбцов относятся к вымышленному набору.

Проверьте условие на границе: заказ 150 рублей не пройдёт «>150», но пройдёт «>= 150». Добавление такого случая помогает увидеть разницу. Сначала объясните результат простой выборки вручную, затем добавляйте новые условия и соединения.

Проверьте смысл соединения

JOIN сопоставляет строки по заданному условию. INNER JOIN оставляет совпавшие пары; LEFT JOIN сохраняет строки левой таблицы и при отсутствии пары возвращает NULL для полей справа. Связь один ко многим может дать несколько строк для одного клиента.

Соединение двух клиентов и трёх заказов даёт три совпавшие строки, а не две. Сумма заказов каждого клиента равна 300 рублей. Добавим клиента 3 без заказа: внутреннее соединение его не покажет, левое покажет с отсутствующими полями заказа. Количество клиентов и количество строк результата теперь особенно важно различать.

Как проверить число строк и сумму

Перед усложнением запроса посчитайте контрольные итоги вручную. В нашем наборе у Севера два заказа на 100 и 200 рублей, у Юга один на 300: общий итог — 600 рублей, клиентов — два, заказов — три. После добавления третьего клиента без заказа INNER JOIN даёт три строки, LEFT JOIN — четыре, а сумма существующих заказов остаётся 600 рублей. Сравнивайте отдельно число клиентов, число заказов и денежный итог.

Для суммы по клиентам нужен групповой расчёт по соответствующему ключу. Отдельно проверьте кратность соединения: лишняя связь может размножить строки и увеличить итог. При проектировании реальной базы дополнительно нужны правила доступа, изменения и восстановления; простая схема помогает освоить саму логику данных.

Вопросы по теме