PostgreSQL с нуля: практический курс для подготовки к экзамену

Основы реляционных баз данных и установка PostgreSQL

Представьте, что вы — владелец небольшого интернет-магазина. У вас есть список клиентов, каталог товаров, заказы и история платежей. Сначала вы держите всё в Excel-таблице, но со временем файл разрастается до сотен строк, появляются дубли, ссылки между листами ломаются, а поиск нужного клиента занимает минуты. Именно для решения таких проблем и были придуманы базы данных — системы, которые хранят, структурируют и обрабатывают данные быстро, надёжно и без хаоса.

Что такое реляционная база данных

Реляционная база данных — это организованное хранилище данных, где информация располагается в связанных между собой таблицах. Слово «реляционная» происходит от английского relation (отношение), и в контексте баз данных это означает именно «таблица». Каждая таблица состоит из строк (записей) и столбцов (полей), а между таблицами устанавливаются связи — например, заказ «ссылается» на клиента, который его оформил.

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

| Свойство | Описание | Пример из жизни | |----------|----------|-----------------| | Таблица | Структурированный набор данных | Список учеников в журнале | | Строка (запись) | Один конкретный элемент данных | Конкретный ученик Иванов | | Столбец (поле) | Характеристика элемента | Имя, фамилия, дата рождения | | Первичный ключ | Уникальный идентификатор строки | Номер в журнале | | Связь (foreign key) | Ссылка между таблицами | Оценка ссылается на номер ученика |

Зачем нужен PostgreSQL

PostgreSQL — это мощная объектно-реляционная система управления базами данных (СУБД) с открытым исходным кодом. Она существует с 1996 года, стабильно развивается и используется такими компаниями, как Apple, Instagram, Spotify и «Яндекс». PostgreSQL поддерживает сложные запросы, хранимые процедуры, полнотекстовый поиск, JSON-данные и десятки других возможностей.

Для подготовки к экзамену PostgreSQL подходит идеально: его SQL-синтаксис максимально близок к стандарту, а бесплатная лицензия позволяет установить его на любой компьютер без ограничений.

Установка PostgreSQL

Windows

macOS

Откройте терминал и используйте Homebrew:

Linux (Ubuntu/Debian)

После установки PostgreSQL автоматически запускается как системный сервис.

Подключение к серверу

Для работы с PostgreSQL используется утилита psql — консольный клиент, который позволяет выполнять SQL-запросы прямо в терминале.

Флаг -U postgres указывает, что вы подключаетесь под пользователем postgres. Система запросит пароль — введите тот, что вы задали при установке.

После успешного подключения вы увидите приглашение:

Это означает, что сервер готов принимать команды. Теперь можно проверить версию:

Вы увидите строку вида PostgreSQL 15.x on x86_64-pc-linux-gnu... — подтверждение, что всё работает.

Первая база данных и первая таблица

Чтобы закрепить теорию практикой, создадим простейшую базу данных для нашего воображаемого магазина.

Создание базы данных:

Подключение к ней:

Команда \c — это метакоманда psql, она не является частью SQL, а служит для управления клиентом. Теперь приглашение изменится на shop=#.

Создадим первую таблицу:

Разберём, что здесь происходит:

Добавим запись:

Проверим, что данные сохранились:

Результат:

Структура SQL-запросов

Все SQL-запросы делятся на несколько категорий, и для экзамена важно их различать:

| Категория | Расшифровка | Примеры команд | |-----------|-------------|----------------| | DDL | Data Definition Language — определение структуры | CREATE, ALTER, DROP | | DML | Data Manipulation Language — работа с данными | INSERT, UPDATE, DELETE, SELECT | | DCL | Data Control Language — управление доступом | GRANT, REVOKE | | TCL | Transaction Control Language — управление транзакциями | BEGIN, COMMIT, ROLLBACK |

На экзамене чаще всего встречаются DDL и DML — именно ими мы займёмся в следующих статьях.

Типичные ошибки новичков

> Запятая после последнего столбца в CREATE TABLE — ошибка синтаксиса. PostgreSQL ожидает следующий столбец или закрывающую скобку, а не запятую.

> Имена таблиц и столбцов чувствительны к регистру только в кавычках. CREATE TABLE Customers и CREATE TABLE customers создадут одну и ту же таблицу, но CREATE TABLE "Customers" — другую.

> Не забывайте ставить точку с запятой в конце каждого SQL-запроса в psql. Без неё запрос не выполнится, а будет ожидать продолжения.

Метакоманды psql

Помимо SQL-запросов, в psql полезно знать служебные команды:

Эти команды не являются частью SQL-стандарта — они уникальны для клиента psql и значительно ускоряют навигацию по базе данных.

Создание таблиц и управление структурой данных

Когда вы проектируете базу данных, первое и самое важное решение — как организовать таблицы. Неправильная структура приведёт к дублированию данных, сложным запросам и ошибкам при обновлении. Представьте, что вы храните адрес клиента прямо в таблице заказов: если клиент сменил адрес, придётся обновлять каждую его запись. А если забыть хотя бы одну — данные станут противоречивыми. Именно поэтому структура таблиц — это фундамент, от которого зависит всё остальное.

Создание таблиц: полный синтаксис

В предыдущей статье мы создали простую таблицу customers. Теперь разберём полный синтаксис CREATE TABLE со всеми возможностями, которые пригодятся на экзамене.

Ключевой элемент — тип данных. PostgreSQL предлагает десятки типов, но для экзамена достаточно знать основные.

Типы данных PostgreSQL

| Категория | Типы | Когда использовать | |-----------|------|---------------------| | Целые числа | SMALLINT, INTEGER, BIGINT | Количество, возраст, счётчики | | Дробные числа | REAL, DOUBLE PRECISION, NUMERIC(p,s) | Цены, проценты, координаты | | Текстовые | CHAR(n), VARCHAR(n), TEXT | Имена, описания, комментарии | | Дата и время | DATE, TIME, TIMESTAMP | Даты рождения, время событий | | Логический | BOOLEAN | Флаги: активен/неактивен, оплачен/не оплачен | | Перечисление | ENUM | Фиксированный набор значений |

Тип NUMERIC(p,s) deserves отдельного пояснения: p — общее количество цифр, s — количество знаков после запятой. Например, NUMERIC(10,2) хранит числа вида 12345678.90. Для цен и финансовых данных это предпочтительнее, чем REAL или DOUBLE PRECISION, потому что NUMERIC не теряет точность при округлении.

Ограничения (constraints)

Ограничения — это правила, которые гарантируют целостность данных. PostgreSQL не позволит вставить запись, нарушающую ограничение, и вернёт ошибку.

PRIMARY KEY

Первичный ключ однозначно идентифицирует каждую строку. Он автоматически создаёт индекс для быстрого поиска и гарантирует уникальность и ненулевость.

Первичный ключ может быть составным — из нескольких столбцов:

NOT NULL

Запрещает пустые значения. Если попытаться вставить NULL в столбец с этим ограничением, PostgreSQL вернёт ошибку.

UNIQUE

Гарантирует, что все значения в столбце различны. В отличие от PRIMARY KEY, допускает один NULL (в PostgreSQL).

DEFAULT

Задаёт значение по умолчанию, если при вставке столбец не указан:

CHECK

Проверяет условие перед вставкой или обновлением:

Если попытаться вставить товар с отрицательной ценой, PostgreSQL отклонит запрос.

FOREIGN KEY

Внешний ключ создаёт связь между таблицами. Он гарантирует, что значение в одном столбце существует в другом.

Теперь невозможно создать заказ для несуществующего клиента — PostgreSQL проверит наличие id в таблице customers.

Дополнительные опции ON DELETE определяют поведение при удалении связанной записи:

Изменение структуры таблицы

Реальные проекты редко создаются с идеальной структурой с первого раза. Команда ALTER TABLE позволяет модифицировать существующие таблицы.

Добавление столбца

Удаление столбца

Изменение типа столбца

Добавление ограничения

Удаление ограничения

Переименование столбца

Удаление таблиц и баз данных

Без CASCADE PostgreSQL откажется удалить таблицу, на которую ссылаются внешние ключи. С CASCADE — удалит и саму таблицу, и все зависимые объекты.

Удалить базу данных можно только вне её — предварительно подключитесь к другой базе через \c postgres.

Проектирование связей: практический пример

Разработаем схему для интернет-магазина с тремя таблицами:

Эта схема демонстрирует три типа связей:

Просмотр структуры таблицы

В psql команда \d имя_таблицы выводит полное описание: столбцы, их типы, ограничения и индексы.

Результат покажет все столбцы, ограничения CHECK, внешние ключи и последовательность для SERIAL. Это быстрый способ проверить, что структура создана корректно, не прибегая к сложным системным запросам.

Базовые операции с данными: INSERT, UPDATE, DELETE

Таблицы без данных — это пустые полки в магазине. Чтобы база данных стала полезной, в неё нужно добавлять, изменять и удалять записи. Эти три операции — INSERT, UPDATE и DELETE — составляют основу языка манипуляции данными (DML) и встречаются на каждом экзамене по SQL. При этом каждая операция скрывает нюансы, которые отличают уверенного пользователя от новичка.

INSERT: добавление данных

Вставка одной записи

Базовый синтаксис указывает таблицу, список столбцов и значения:

Перечисление столбцов необязательно, но настоятельно рекомендуется. Без него порядок значений должен точно соответствовать порядку столбцов в таблице, и любое изменение структуры сломает запрос.

Вставка нескольких записей

PostgreSQL позволяет вставить несколько строк за один вызов — это быстрее, чем выполнять отдельные INSERT для каждой записи:

Вставка с возвратом данных

Команда RETURNING возвращает значения вставленных строк — полезно, когда нужно узнать сгенерированный SERIAL-ключ:

Результат:

Вставка из другого запроса

Данные можно вставлять не только вручную, но и из результатов SELECT:

Это копирует всех клиентов с id меньше 100 в архивную таблицу без необходимости перечислять их вручную.

UPDATE: изменение данных

Базовый синтаксис

Команда UPDATE изменяет значения в существующих строках. Клауза WHERE определяет, какие строки затронуть.

Опасность забытого WHERE

> Если опустить WHERE, UPDATE изменит все строки в таблице. Это одна из самых распространённых ошибок новичков. Всегда проверяйте, что условие WHERE присутствует, прежде чем выполнять запрос на реальных данных.

Обновление нескольких столбцов

Столбцы перечисляются через запятую:

Этот запрос увеличивает цену на 10% и добавляет пометку «(акция)» ко всем товарам из категории 3. Оператор || — это конкатенация строк в PostgreSQL.

Обновление с использованием подзапроса

Запрос пересчитывает сумму заказа на основе цены товара и количества, но только для заказов, где сумма ещё не указана.

DELETE: удаление данных

Базовый синтаксис

Удаляет строку с id = 5. Как и в UPDATE, отсутствие WHERE приведёт к удалению всех строк.

Удаление с возвратом

RETURNING * вернёт все удалённые строки — удобно для проверки, что удалилось именно то, что планировалось.

TRUNCATE: быстрая очистка

Если нужно удалить все данные из таблицы, TRUNCATE работает быстрее, чем DELETE без WHERE:

| Операция | Удаляет данные | Удаляет структуру | Сбрасывает счётчик SERIAL | Можно откатить | |----------|---------------|-------------------|--------------------------|----------------| | DELETE FROM table | Да | Нет | Нет | Да | | TRUNCATE TABLE | Да | Нет | Да | Да | | DROP TABLE | Да | Да | — | Нет |

TRUNCATE не проходит по каждой строке, а освобождает страницы данных целиком, поэтому на больших таблицах он в десятки раз быстрее DELETE.

Транзакции: безопасность операций

Транзакция — это группа операций, которая выполняется как единое целое. Если хотя бы одна операция завершается ошибкой, все изменения откатываются.

Если между BEGIN и COMMIT произойдёт ошибка (например, недостаточно средств на счете 1), можно выполнить ROLLBACK — и оба UPDATE отменятся. Без транзакции первый UPDATE уже применился бы, а второй — нет, и данные стали бы некорректными.

Для экзамена важно помнить три команды управления транзакциями:

Возврат к последней точке: SAVEPOINT

Сейвпоинт позволяет откатить не всю транзакцию, а только её часть:

Это полезно при пакетной обработке, когда часть данных может быть некорректной, но не должна ломать всю операцию.

Практический сценарий: управление складом

Закрепим все три операции на задаче управления складом.

Результат:

Выборки, фильтрация, сортировка и объединение таблиц

Данные в базе бесполезны, если их нельзя извлечь. Команда SELECT — это инструмент, с помощью которого вы задаёте вопрос базе данных и получаете ответ. «Покажи всех клиентов из Москвы», «Найди товары дороже 5000 рублей», «Посчитай общую сумму заказов за месяц» — всё это выражается через SELECT. Именно эта команда составляет львиную долю запросов в реальных проектах и занимает центральное место на экзамене.

Базовый SELECT

Минимальный запрос извлекает все данные из таблицы:

Звёздочка * означает «все столбцы». На практике лучше явно перечислять нужные столбцы — это ускоряет запрос и делает код понятнее:

Фильтрация: WHERE

Клауза WHERE задаёт условие, которому должна соответствовать строка. Только строки, прошедшие проверку, попадают в результат.

Операторы сравнения

Оператор IS NULL — единственный корректный способ проверить пустое значение. Выражение email = NULL никогда не вернёт true, потому что NULL не равен ничему, даже самому себе.

Логические операторы

Условия комбинируются через AND, OR, NOT:

BETWEEN включает граничные значения (эквивалентно price >= 500 AND price <= 2000). IN проверяет принадлежность множеству.

Шаблоны: LIKE и ILIKE

Для поиска по частичному совпадению используется LIKE:

Символ % заменяет любую последовательность символов, _ — ровно один символ. ILIKE работает так же, но без учёта регистра.

| Шаблон | Описание | Пример совпадения | |--------|----------|-------------------| | 'Иван%' | Начинается с «Иван» | Иванов, Иван Петров | | '%ов' | Заканчивается на «ов» | Петров, Сидоров | | '%сер%' | Содержит «сер» | Козлов (нет), Сергеев (да) | | '_ергей' | Любая буква + «ергей» | Сергей |

Сортировка: ORDER BY

ORDER BY упорядочивает результат. По умолчанию — по возрастанию (ASC), для убывания — DESC:

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

Ограничение количества строк: LIMIT и OFFSET

Возвращает пять самых дорогих товаров. OFFSET пропускает строки — полезно для пагинации:

Пропускает первые 20 товаров и возвращает следующие 10.

Агрегатные функции

Агрегатные функции вычисляют одно значение по набору строк:

| Функция | Описание | |---------|----------| | COUNT(*) | Количество строк | | COUNT(column) | Количество непустых значений | | SUM(column) | Сумма | | AVG(column) | Среднее | | MIN(column) | Минимум | | MAX(column) | Максимум |

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

Группировка: GROUP BY

GROUP BY разбивает строки на группы, и агрегатная функция вычисляется отдельно для каждой:

Для каждой категории выводится количество товаров и средняя цена.

Фильтрация групп: HAVING

WHERE фильтрует строки до группировки, HAVING — после:

Показывает только те категории, в которых больше 10 товаров.

Объединение таблиц: JOIN

JOIN — это операция, которая соединяет строки из двух или более таблиц на основе связанных столбцов. Именно здесь проявляется сила реляционных баз данных.

INNER JOIN

Возвращает только строки, у которых есть соответствие в обеих таблицах:

Если товар не привязан к категории (category_id = NULL), он не попадёт в результат. Псевдонимы p и c сокращают запись — вместо products.name пишется p.name.

LEFT JOIN

Возвращает все строки из левой таблицы, даже если нет соответствия в правой. Незаполненные столбцы правой таблицы содержат NULL:

Товары без категории всё равно появятся в результате, но в столбце category будет NULL.

RIGHT JOIN и FULL JOIN

RIGHT JOIN — зеркальная версия LEFT JOIN: все строки из правой таблицы. FULL JOIN объединяет оба подхода — возвращает все строки из обеих таблиц.

CROSS JOIN

Декартово произведение: каждая строка первой таблицы соединяется с каждой строкой второй. Если в первой таблице 10 строк, а во второй — 5, результат содержит 50 строк. На практике используется редко.

Множественные JOIN

Таблицы можно соединять последовательно:

Запрос показывает все заказы 2024 года с именами клиентов и товаров, отсортированные от новых к старым.

Подзапросы

Подзапрос — это SELECT, вложенный в другой запрос. Он может出现在 WHERE, FROM или SELECT:

Находит товары, цена которых выше средней. Подзапрос в скобках сначала вычисляет среднюю цену, а внешний запрос использует результат для фильтрации.

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

UNION: объединение результатов

UNION склеивает результаты двух запросов в одну таблицу. Столбцы должны совпадать по количеству и типам:

UNION автоматически удаляет дубликаты. UNION ALL сохраняет все строки, включая повторы — и работает быстрее.

Практические задачи и подготовка к экзамену

Теория без практики забывается за неделю. Эта статья — сборник реальных задач, которые встречаются на экзаменах по SQL и PostgreSQL. Каждая задача разобрана по шагам: от постановки до финального запроса. Здесь нет абстрактных упражнений — только конкретные сценарии, которые проверяют умение комбинировать знания из предыдущих статей.

Подготовка рабочей среды

Перед решением задач создадим учебную базу данных. Выполните этот блок целиком в psql:

Задача: Простая выборка с фильтрацией

Условие: Вывести имена студентов, поступивших в 2022 году, отсортированных по алфавиту.

Анализ: Нужна одна таблица students, фильтр по году поступления и сортировка.

Результат: Иванов Алексей, Петрова Мария, Волков Сергей.

Задача: Агрегация с группировкой

Условие: Для каждого курса вывести количество сданных экзаменов и средний балл. Считать только курсы, где средний балл выше 3.5.

Анализ: Нужна таблица grades, группировка по course_id, агрегатные функции COUNT и AVG, фильтрация через HAVING.

Задача: JOIN с условием

Условие: Вывести названия курсов и количество студентов, получивших оценку 5.

Анализ: Нужно соединить таблицы courses и grades, отфильтровать по оценке и сгруппировать по курсу.

Задача: LEFT JOIN для поиска отсутствующих данных

Условие: Найти студентов, которые не сдавали ни одного экзамена.

Анализ: Используем LEFT JOIN из students в grades и ищем строки, где grades.id IS NULL.

Результат: Белова Ольга из группы ИТ-103 — единственный студент без оценок.

Задача: Подзапрос в WHERE

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

Анализ: Внутренний подзапрос вычисляет средний балл, внешний фильтрует студентов.

DISTINCT убирает дубли — студент с несколькими оценками выше среднего появится в списке один раз.

Задача: Составной запрос с несколькими JOIN

Условие: Вывести полную информацию: имя студента, название курса, оценку и дату экзамена. Только для студентов группы ИТ-101.

Задача: Модификация данных в рамках транзакции

Условие: Перевести студента Иванова Алексея в группу ИТ-103 и обновить все его оценки по курсу «Базы данных» до 5, используя транзакцию.

Задача: Создание таблицы с ограничениями

Условие: Создать таблицу attendance для учёта посещаемости с внешними ключами на students и courses, столбцом attended (BOOLEAN, по умолчанию true) и запретом дублирования пар «студент — курс — дата».

Ограничение UNIQUE на комбинацию трёх столбцов гарантирует, что один студент не может быть отмечен на одном курсе дважды за один день.

Частые ошибки на экзамене

Смешивание WHERE и HAVING. WHERE фильтрует строки до группировки, HAVING — после. Попытка использовать агрегатную функцию в WHERE вызовет ошибку:

NULL в логических операциях. Любая операция с NULL возвращает NULL, а не true или false. Условие WHERE grade != 5 не вернёт строки, где grade IS NULL, хотя NULL технически не равен 5.

Забытый GROUP BY. Если в SELECT есть агрегатная функция и обычный столбец, PostgreSQL потребует GROUP BY для этого столбца. Без него — ошибка.

Чек-лист перед экзаменом