Курс База данных для начинающих

Урок посвящен знакомству с основными инструментами, необходимыми для работы с реляционными базами данных. Мы разберем, как установить PostgreSQL, зададим пароль для пользователя postgres, а затем установим графический клиент DataGrip.

После установки мы создадим подключение к локальному серверу PostgreSQL и выполним небольшой SQL-скрипт. Он покажет сведения о сервере, текущей базе данных и пользователе, а также позволит проверить возможность создания, добавления и получения данных.

В конце урока у нас будет полностью подготовленное окружение для дальнейшего изучения SQL.

На втором занятии мы начнем создавать базу данных небольшого трекера задач. Сначала разберем, как устроены таблицы, что такое строки и столбцы. Затем создадим базу task_tracker и три таблицы: users для пользователей, projects для проектов и tasks для задач.

При создании таблиц познакомимся с основными типами данных PostgreSQL: INTEGER, VARCHAR, TEXT, BOOLEAN, DATE и TIMESTAMP. Обсудим, какой тип подходит для названия задачи, ее приоритета, признака выполнения и срока. После этого добавим по одной пробной записи в каждую таблицу и получим данные с помощью SELECT, чтобы проверить результат в DataGrip.

К концу урока у нас будет основа проекта, которую мы продолжим развивать на следующих занятиях.

На предыдущем занятии мы создали таблицы users, projects и tasks, выбрали типы столбцов и добавили пробные записи. Однако тип данных не решает всех проблем: например, целочисленный столбец priority позволяет записать не только приоритет 3, но и 100.

На третьем занятии познакомимся с ограничениями PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT и CHECK. Сделаем обязательными имена пользователей, названия проектов и задач; запретим повторяющиеся email; зададим значения по умолчанию для новых задач; ограничим приоритет диапазоном от 1 до 5.

После каждого изменения проверим результат на практике. Попробуем добавить задачу без названия, задачу с недопустимым приоритетом и пользователя с уже существующим email. Затем добавим корректную задачу и посмотрим, какие значения PostgreSQL подставил автоматически. На следующем уроке подробнее разберем добавление сразу нескольких записей.

В базе трекера задач уже есть таблицы users, projects и tasks, но для дальнейшей работы нам понадобится больше записей. На четвертом занятии подробно разберем команду INSERT INTO: научимся перечислять заполняемые столбцы, следить за порядком значений и добавлять несколько строк одним запросом.

Заполним таблицы пользователями, проектами и задачами. На примерах увидим, что происходит при пропуске столбца с DEFAULT и как явно указать отсутствие срока задачи с помощью NULL. После этого получим записи командой SELECT: сначала все столбцы, затем только выбранные. Используем AS, чтобы дать столбцам понятные названия в результате.

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

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

На пятом занятии научимся отбирать записи по условиям. Найдем задачи с определенным приоритетом, незавершенные задачи и задачи без срока. Затем сравним несколько способов задавать условия: операторы сравнения, AND и OR, IN, BETWEEN, LIKE и ILIKE.

После фильтрации разберем ORDER BY: выведем задачи по убыванию приоритета и по сроку выполнения. С помощью LIMIT и OFFSET получим только часть результата. В конце составим запрос для списка наиболее приоритетных незавершенных задач.

На предыдущих занятиях мы создавали задачи и получали нужные записи через SELECT. Теперь научимся поддерживать данные в актуальном состоянии: менять название и приоритет задачи, отмечать ее выполненной и удалять тестовые записи.

Сначала разберем команду UPDATE. Перед изменением будем находить нужную строку через SELECT, а после — проверять результат. Затем познакомимся с DELETE и увидим, почему запрос без WHERE может затронуть всю таблицу. PostgreSQL позволяет добавить к обеим командам RETURNING, чтобы сразу получить измененные или удаленные строки.

В конце урока еще раз воспользуемся ALTER TABLE: добавим столбец для времени изменения задачи и переименуем description в details. Это подготовит таблицу к дальнейшему развитию проекта.

На уроке свяжем задачи с пользователями, проектами и статусами. Разберем связь «один ко многим», внешние ключи и ссылочную целостность. Затем создадим таблицу комментариев и проверим, что PostgreSQL не позволяет ссылаться на несуществующие записи.

На седьмом занятии мы добавили в tasks внешние ключи: project_id, assignee_id и status_id. Эти столбцы указывают на записи в других таблицах, но при обычном SELECT показывают только числовые идентификаторы.

На восьмом занятии научимся объединять данные из нескольких таблиц в одном результате. Через INNER JOIN получим название проекта для задач с назначенным проектом. Затем применим LEFT JOIN, чтобы сохранить в результате и те задачи, у которых исполнитель или статус пока не назначен.

После этого соберем «карточку задачи»: ее название, проект, исполнителя и статус. Разберем псевдонимы таблиц и условие соединения ON, а в конце найдем задачи без исполнителя.

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

На девятом занятии познакомимся с агрегатными функциями, которые вычисляют результат по набору строк. Затем сгруппируем задачи по проектам и исполнителям с помощью GROUP BY. Сравним WHERE, который отбирает строки до группировки, и HAVING, который отбирает уже готовые группы.

В конце составим отчет по проектам. Используем LEFT JOIN, чтобы в него попали даже проекты, у которых пока нет задач.

Полное описание урока

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

На десятом занятии создадим отдельную таблицу tags и промежуточную таблицу task_tags. Каждая запись в task_tags будет означать назначение одного тега одной задаче. Составной первичный ключ запретит назначить одной задаче один и тот же тег дважды.

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

К этому моменту мы умеем искать задачи, соединять таблицы и строить отчеты. Но некоторые вопросы требуют объединить несколько действий. Например: «Какие задачи имеют приоритет выше среднего по всей базе?» Сначала нужно вычислить средний приоритет, а затем сравнить с ним каждую задачу.

На одиннадцатом занятии решим такие задачи с помощью подзапросов. Отдельно познакомимся с EXISTS, чтобы находить проекты, в которых есть хотя бы одна задача. Затем используем CTE — именованный промежуточный результат, существующий в рамках одного запроса, — и построим отчет по проектам в несколько понятных этапов.

В конце сохраним запрос для карточки задачи в виде представления VIEW. После этого карточку можно будет получать коротким SELECT, не переписывая каждый раз несколько JOIN.

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

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

После этого обсудим индексы. Они могут ускорять поиск по столбцам, которые часто встречаются в условиях запросов, но занимают место и требуют работы при изменении данных. Создадим несколько индексов и посмотрим план выполнения запроса с помощью EXPLAIN. В конце получим карточки задач и итоговый отчет по проектам.