БД:Теория:Книга

Глава 1

(Эта глава основана на материалах Лекции 1: "ИСБД. Введение" и частично Лекции 3: "Нормализация", чтобы заложить фундамент перед созданием БД)

Часть 1: Зачем нужны Базы Данных?

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

Самый простой вариант — использовать обычные файлы, например, текстовые файлы или таблицы Excel.

// Пример хранения в текстовом файле student_data.txt
1;Иванов;Иван;2004-05-15
2;Петров;Петр;2004-03-10
3;Сидорова;Анна;2003-11-20

// Пример хранения в Excel (упрощенно)
| ID | Фамилия  | Имя  | Дата рождения |
|----|----------|------|---------------|
| 1  | Иванов   | Иван | 2004-05-15    |
| 2  | Петров   | Петр | 2004-03-10    |
| 3  | Сидорова | Анна | 2003-11-20    |

На первый взгляд, это работает. Но что если:

1. Структура данных изменится? Допустим, нужно добавить номер группы. Вам придется вручную изменить все файлы и всепрограммы, которые эти файлы читают. Это негибко.
2. Несколько человек захотят одновременно изменить данные? Например, два куратора одновременно захотят обновить оценки. Как понять, чьи изменения правильные? Как избежать потери данных? Работать многопользовательски с простыми файлами сложно и опасно.
3. Данные повторяются? Если у вас есть отдельный файл для оценок, вам придется дублировать имена студентов. А если студент сменит фамилию? Придется менять ее во всех файлах, где она есть. Это избыточность и риск несогласованностиданных (в одном файле фамилия старая, в другом — новая).

Чтобы решить эти проблемы, придумали Базы Данных (БД).

Решение: Хранить данные (информацию о студентах, оценках) и метаданные (описание структуры данных: какие есть поля, какие у них типы) вместе.

Часть 2: Что такое База Данных и СУБД?

• База Данных (БД) — это, по сути, набор файлов, которые хранят данные, но также содержат описание структуры этих данных (метаданные). Эти файлы управляются специальным программным обеспечением.
• Система Управления Базами Данных (СУБД) — это то самое программное обеспечение, которое управляет базой данных. СУБД отвечает за:
  • Хранение данных.
  • Предоставление доступа к данным.
  • Обеспечение целостности и согласованности данных.
  • Поддержку языка для работы с данными (например, SQL).
  • Управление одновременным доступом нескольких пользователей.
  • Резервное копирование и восстановление.

Примеры СУБД: PostgreSQL (с которой мы будем работать), MySQL, Oracle Database, Microsoft SQL Server, SQLite.

Часть 3: Классификация СУБД

СУБД бывают разными. Их можно классифицировать по нескольким признакам:

1. По степени распределенности:
   • Локальные: Все данные и сама СУБД находятся на одном компьютере.
   • Распределенные: Данные и/или СУБД могут быть разнесены по нескольким компьютерам, объединенным в сеть.
2. По способу доступа к БД:
   • Файл-серверные: Данные лежат на сервере в виде файлов, а сама СУБД (логика обработки) работает на компьютере пользователя (клиенте). Клиент загружает нужные файлы для обработки. Примеры: MS Access (в некоторых режимах), dBase, FoxPro. Это не очень эффективно для больших объемов данных и многих пользователей.
   • Клиент-серверные: Данные и основная логика СУБД находятся на сервере. Клиент отправляет запросы на сервер, сервер их обрабатывает и возвращает только результат. Это самая распространенная архитектура для большинства современных СУБД. Примеры: PostgreSQL, Oracle, MS SQL Server.
   • Встраиваемые: СУБД является частью другого приложения, как библиотека. Она хранит данные только этого приложения и не требует отдельной установки. Примеры: SQLite, BerkeleyDB.
3. По модели данных: (Способ организации данных)
   • Иерархические: Данные организованы в виде дерева. (Сейчас почти не используются).
   • Сетевые: Более сложная структура, похожая на граф. Частный случай - графовые СУБД (Neo4j, OrientDB).
   • Объектно-ориентированные: Данные хранятся как объекты (в смысле ООП). Пример: InterSystems Caché.
   • Реляционные (RDBMS): Данные хранятся в виде таблиц (отношений). Это самая популярная модель. Примеры: PostgreSQL, MySQL, Oracle.
   • Объектно-реляционные (ORDBMS): Реляционная модель с добавлением объектных возможностей. Пример: PostgreSQL часто относят и к этому типу.
   • NoSQL: ("Not Only SQL") - Различные модели, оптимизированные для конкретных задач (ключ-значение, документо-ориентированные, колоночные). Примеры: Redis, MongoDB, Cassandra. (Мы их подробно рассматривать не будем).

Мы с вами будем фокусироваться на клиент-серверных реляционных (и объектно-реляционных) СУБД, используя PostgreSQL.

Часть 4: Реляционная модель данных

Основоположником реляционной модели считается Эдгар Кодд (сотрудник IBM, 1960-е - 1970-е). Его идея: любые данные можно представить в виде совокупности отношений.

• Отношение (Relation) — это формальное название для таблицы особого вида в реляционной модели.
• У отношения есть атрибуты (Attributes) — это столбцы таблицы.
• У отношения есть кортежи (Tuples) — это строки таблицы.
• У каждого атрибута есть имя (уникальное в пределах отношения).
• Каждый атрибут определяется доменом.
  • Домен (Domain) — это множество допустимых значений для атрибута (например, домен "целые числа", домен "строки", домен "даты"). Смысл домена в том, что значения из одного домена можно сравнивать между собой.

Основные правила (свойства) отношений:

1. Отношение состоит из заголовка (набор атрибутов) и тела (набор кортежей). Заголовок фиксирован, тело меняется со временем.
2. Каждый кортеж — это набор пар "атрибут-значение", по одной паре для каждого атрибута из заголовка.
3. Значение для каждой пары "атрибут-значение" берется из домена, связанного с этим атрибутом.
4. Нет двух одинаковых кортежей (строк) в отношении. Каждая строка уникальна.
5. Порядок кортежей (строк) не имеет значения.
6. Порядок атрибутов (столбцов) не имеет значения. (Хотя на практике при отображении мы их видим в определенном порядке).
7. Все значения атрибутов атомарны. Это значит, что на пересечении строки и столбца должно быть ровно одно значение из домена (или специальное значение NULL). Не может быть списков или других сложных структур внутри одной ячейки (это основа Первой Нормальной Формы, о которой мы поговорим позже).

Пример таблицы (Отношения) STUDENT:

ID (integer)	Surname (text)	Name (text)	Birthday (date)	Location (text)
1	Иванов	Василий	1980-12-01	г. Москва
2	Георгиев	Сергей	1992-03-12	г. Санкт-Петербург
3	Васильев	Андрей	1987-10-14	г. Оренбург
7	Романов	Кирилл	1991-12-01	NULL

Терминология:

• Переменная отношения / Имя таблицы: STUDENT
• Атрибут / Столбец / Поле: ID, Surname, Name, Birthday, Location
• Кортеж / Строка / Запись: Строка с ID=1, строка с ID=2, и т.д.
• Заголовок: Набор имен атрибутов (ID, Surname, Name, Birthday, Location) и их доменов.
• Тело: Набор кортежей (все строки таблицы).
• Степень отношения (Arity/Degree): Число атрибутов (в примере = 5).
• Кардинальное число (Cardinality): Число кортежей (в примере = 4).
• Домен: Для ID - целые числа, для Surname/Name/Location - текст, для Birthday - даты.
• Значение NULL: Означает "значение неизвестно" или "неприменимо". В примере у Кирилла Романова местоположение неизвестно.

Часть 5: Ключи и Целостность

Ключи:

• Потенциальный ключ (Candidate Key): Минимальный набор атрибутов, значения которых уникально определяют кортеж в отношении. Минимальный — значит, что нельзя убрать ни один атрибут из набора без потери уникальности. В таблице STUDENT потенциальным ключом является {ID}. Возможно, {Surname, Name, Birthday} тоже мог бы быть потенциальным ключом, если мы уверены в его уникальности.
• Первичный ключ (Primary Key, PK): Один из потенциальных ключей, выбранный в качестве основного идентификатора кортежей в таблице. Обычно выбирают самый простой и стабильный ключ. В таблице STUDENT первичным ключом логично выбрать {ID}. Первичный ключ не может содержать NULL-значений.
• Внешний ключ (Foreign Key, FK): Набор атрибутов в одном отношении, значения которых должны соответствовать значениям первичного ключа в другом (или этом же) отношении. Используется для организации связей между таблицами.

Пример с Внешним Ключом:

Таблица GROUP

ID (integer, PK)	Name (text)	class_leader (integer, FK -> STUDENT.ID)
34	P3100	3
37	P3112	2
354	R4230	NULL

Таблица STUDENT (добавим gr_id)

ID (integer, PK)	name (text)	surname (text)	gr_id (integer, FK -> GROUP.ID)
1	Григорий	Иванов	34
2	Григорий	Иванов	34
3	Иван	Сидоров	37

Здесь gr_id в таблице STUDENT — внешний ключ, ссылающийся на ID в таблице GROUP. class_leader в GROUP — внешний ключ, ссылающийся на ID в STUDENT.

Целостность данных (Data Integrity):

Целостность — это корректность и согласованность данных в любой момент времени. Выделяют три группы правил:

1. Целостность сущностей (Entity Integrity): Ни один атрибут, входящий в состав первичного ключа, не может принимать значение NULL. Это гарантирует, что каждая строка имеет уникальный идентификатор.
2. Целостность по ссылкам (Referential Integrity): Значение внешнего ключа должно либо:
   • Соответствовать значению существующего первичного ключа в связанной таблице.
   • Быть полностью NULL (если это разрешено для данного внешнего ключа). Это гарантирует, что ссылки между таблицами не ведут "в никуда". Например, нельзя студенту присвоить gr_id = 100, если группы с ID = 100 не существует в таблице GROUP.
3. Целостность, определяемая пользователем (User-defined Integrity): Любые другие бизнес-правила, специфичные для предметной области, которые не покрываются первыми двумя типами. Примеры:
   • Уникальность каких-либо атрибутов (не являющихся первичным ключом).
   • Ограничение на диапазон значений (оценка от 2 до 5).
   • Принадлежность значения определенному набору (пол "М" или "Ж").

Эти правила целостности реализуются в СУБД с помощью ограничений (constraints), о которых мы подробно поговорим при создании таблиц.

Пример нарушения целостности (из слайда с ошибками):

Таблица STUDENT

id	name	surname	gr_id	Проблема
1	Григорий	Иванов	34	ОК
2	Иван	Сидоров	34	ОК
2	Петр	Петров	37	Нарушение целостности сущностей (PK): id=2 повторяется
2	Иван	Сидоров	34	Нарушение целостности сущностей (PK): id=2 повторяется; Дубликат строки:Полностью совпадает со второй строкой (тоже нарушение)

Таблица GROUP

id	name	class_leader	Проблема
'Vasya'	P3100	3	Нарушение домена/типа: id должен быть числом (PK), а не строкой
37	P3112	2	ОК
34	R4230	4	Нарушение ссылочной целостности (FK): Студента с ID=4 нет в STUDENT

Часть 6: Язык SQL (Введение)

SQL (Structured Query Language) — структурированный язык запросов. Это декларативный язык, то есть мы описываем, что хотим получить или сделать, а не как это сделать. СУБД сама определяет оптимальный способ выполнения нашего запроса.

SQL используется для:

• Определения данных (Data Definition Language, DDL): Создание, изменение, удаление структуры БД (таблиц, индексов, представлений и т.д.).
  • CREATE: Создать объект.
  • ALTER: Изменить объект.
  • DROP: Удалить объект.
• Манипулирования данными (Data Manipulation Language, DML): Работа с данными внутри таблиц.
  • SELECT: Выборка (чтение) данных.
  • INSERT: Вставка (добавление) новых данных.
  • UPDATE: Обновление (изменение) существующих данных.
  • DELETE: Удаление данных.
• Управления доступом к данным (Data Control Language, DCL): Предоставление и отзыв прав доступа пользователям.
  • GRANT: Предоставить права.
  • REVOKE: Отозвать права.

Мы начнем с DDL для создания таблиц, а затем перейдем к DML для работы с данными.

Глава 2

(Эта глава основана на материалах Лекции 2: “Создание Базы Данных”)

Часть 1: Этапы Построения БД

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

1. Определение Предметной Области:
   • Предметная область — это та часть реального мира, данные о которой мы хотим хранить и обрабатывать в БД. Примеры: учебный процесс в университете, работа интернет-магазина, учет товаров на складе.
   • На этом этапе мы собираем требования, общаемся с будущими пользователями системы, анализируем существующие процессы.
2. Уровни Представления Данных:
   • Пользовательский уровень: Как разные пользователи видят и взаимодействуют с данными. Обычно пользователь работает только с частью системы и имеет неполное представление о ней в целом. Пользователю не важно, как данные физически хранятся, как оптимизируются запросы или как происходит поиск. Информация собирается в виде текстов, диаграмм, интервью.
   • Инфологический уровень: Создание обобщенной, неформальной модели предметной области, которая не зависит от конкретной СУБД или модели данных.
     • Собирается на основе анализа пользовательских представлений.
     • Не зависит от “физического” хранилища.
     • Использует стандартные средства описания, например, ER-диаграммы (Entity-Relationship).
     • Цель – описать сущности, их атрибуты и связи между ними.
   • Даталогический уровень: Представление инфологической модели с учетом конкретной модели данных (в нашем случае — реляционной) и особенностей конкретной СУБД.
     • Здесь появляются детали, специфичные для СУБД (например, конкретные типы данных: integer, text в PostgreSQL).
     • ER-модель преобразуется в набор таблиц (отношений).
   • Физический уровень: Реализация даталогической модели средствами конкретной СУБД.
     • Зависит от особенностей СУБД.
     • Описывается на языке, поддерживаемом СУБД (в нашем случае — SQL).
     • Пример: SQL-код для создания таблиц (CREATE TABLE STUDENT (...)).
3. Проектирование “Сверху-Вниз” (Top-Down Approach): Мы движемся от общего к частному:
   1. Анализ предметной области (Пользовательский уровень).
   2. Построение инфологической модели (ER-диаграмма).
   3. Преобразование в даталогическую модель (схема таблиц с типами данных).
   4. Физическая реализация (SQL-код для создания таблиц и других объектов).

Часть 2: Инфологическая Модель (ER-модель)

Один из самых популярных инструментов для инфологического моделирования — ER-диаграммы (Entity-Relationship), предложенные Питером Ченом (IBM, 1976).

Основные элементы ER-модели:

• Сущность (Entity): Класс реальных или абстрактных объектов, информация о которых должна храниться в БД (например, СТУДЕНТ, ГРУППА, ЭКЗАМЕН, ПРЕПОДАВАТЕЛЬ). На диаграммах обычно изображается прямоугольником.
• Экземпляр сущности (Entity Instance): Конкретный объект данного класса (например, студент “Иван Иванов”, группа “P3100”).
• Атрибут (Attribute): Характеристика (свойство) сущности, имеющая имя и тип данных (например, у сущности СТУДЕНТ могут быть атрибуты: ID_Студента, Имя, Фамилия, Дата_Рождения). На диаграммах обычно изображается овалом, связанным с сущностью.
• Связь (Relationship): Ассоциация между двумя или более сущностями, отражающая их взаимодействие (например, студент принадлежит группе, студент сдает экзамен). На диаграммах обычно изображается ромбом, связанным с сущностями, которые он соединяет.

Изображение элементов (Нотация Чена - упрощенно):

• Степень связи (Мощность, Кардинальность): Указывает, сколько экземпляров одной сущности может быть связано с одним экземпляром другой сущности. Обозначается как 1 (один) или M (много, иногда N или *).

Типы связей (по степени):

1. Один-к-одному (1:1): Каждому экземпляру первой сущности соответствует не более одного экземпляра второй, и наоборот.
   • Пример: ГРУППА —(1)— Староста —(1)— СТУДЕНТ (Предполагаем, что у группы ровно один староста, и студент может быть старостой только в одной группе).
2. Один-ко-многим (1:M): Одному экземпляру первой сущности может соответствовать ноль, один или несколько экземпляров второй, но одному экземпляру второй сущности соответствует не более одного экземпляра первой.
   • Пример: ГРУППА —(1)— Принадлежность —(M)— СТУДЕНТ (В одной группе много студентов, но каждый студент принадлежит только одной группе).
3. Многие-к-одному (M:1): Зеркальная связь 1:M. Одному экземпляру первой сущности соответствует не более одного экземпляра второй, но одному экземпляру второй может соответствовать ноль, один или несколько экземпляров первой.
   • Пример: СТУДЕНТ —(M)— Принадлежность —(1)— ГРУППА (Много студентов в одной группе).
4. Многие-ко-многим (M:M): Одному экземпляру первой сущности может соответствовать ноль, один или несколько экземпляров второй, и наоборот.
   • Пример: СТУДЕНТ —(M)— Процесс сдачи —(M)— ЭКЗАМЕН (Один студент может сдавать много экзаменов, и один экзамен могут сдавать много студентов).

Важно: Связи M:1 и 1:M — это одна и та же связь, просто рассмотренная с разных сторон. Поэтому часто выделяют только 3 типа связей: 1:1, 1:M, M:M.

Сложные и Тернарные связи:

• Между двумя сущностями может быть несколько связей разного типа (например, Группа-Студент связаны как “Принадлежность” (1:M) и “Староста” (1:1)).
• Тернарная связь: Связь, соединяющая три сущности.
  • Пример: СТУДЕНТ, ПРЕПОДАВАТЕЛЬ, ЭКЗАМЕН связаны через “Проведение экзамена”.

Классификация сущностей (по Э. Кодду):

• Стержневая сущность (Kernel Entity): Независимая, базовая сущность, которая может существовать сама по себе (например, СТУДЕНТ, ГРУППА).
• Ассоциативная сущность (Associative Entity): Сущность, возникающая для представления связи M:M между двумя или более сущностями. Она “ассоциирует” экземпляры других сущностей. (Пример: таблица STUD_TO_EXAM для связи СТУДЕНТ-ЭКЗАМЕН).
• Характеристическая сущность (Characteristic Entity): Сущность, которая описывает или уточняет другую сущность и не может существовать без нее. Обычно связана с основной сущностью связью M:1 или 1:1. (Пример: если бы у студента было несколько телефонов, можно было бы вынести их в отдельную сущность ТЕЛЕФОН, которая была бы характеристической для СТУДЕНТА).

Ключи в инфологической модели:

• Ключ (Key): Минимальный набор атрибутов, уникально идентифицирующий экземпляр сущности.
• Суррогатный ключ (Surrogate Key): Искусственно добавленный атрибут (обычно числовой идентификатор, например, student_id), который используется как ключ для уникальной идентификации экземпляра. Часто используется вместо “естественных” ключей (как ФИО+ДатаРождения), так как он проще, стабильнее и эффективнее для связей.

Часть 3: Создание Реляционной БД на SQL

Переходим от инфологической модели к даталогической и физической. Наша цель — создать таблицы в PostgreSQL с помощью SQL.

Объекты Базы Данных:

В БД есть разные объекты: таблицы, представления (виртуальные таблицы), индексы, последовательности, функции, процедуры, триггеры и т.д. Основной объект, с которым мы начнем работать, — это таблица.

Язык SQL: Составляющие

Как и любой язык, SQL состоит из базовых элементов:

1. Предложения (Clauses/Statements): Команды, которые выполняют определенное действие (CREATE TABLE ...;, SELECT * FROM ...;). Заканчиваются точкой с запятой (;).
2. Идентификаторы (Identifiers): Имена объектов БД (таблиц, колонок, функций и т.д.). Могут быть системными или определенными пользователем. Используются для обращения к объектам (например, STUDENTS в SELECT * FROM STUDENTS;).
3. Ключевые слова (Keywords): Зарезервированные слова языка, имеющие специальное значение (SELECT, FROM, WHERE, CREATE, TABLE, INTEGER и т.д.). Обычно пишутся заглавными буквами для читаемости, хотя SQL часто нечувствителен к регистру для ключевых слов.
4. Константы (Constants/Literals): Фиксированные значения, не являющиеся идентификаторами или ключевыми словами.
   • Числовые: 123, 3.14, 5E6 (5 * 10^6)
   • Строковые: 'Пример строки' (в одинарных кавычках!)
   • Дата/Время: '2023-10-27', '2023-10-27 10:00:00' (формат зависит от СУБД и настроек, стандартный ISO 8601 предпочтителен)
   • Булевы: TRUE, FALSE (и специальное значение NULL)
5. Операторы (Operators): Символы, обозначающие действия над операндами (константами, значениями колонок).
   • Арифметические: +, -, *, /
   • Сравнения: =, >, <, >=, <=, <> или != (не равно)
   • Логические: AND, OR, NOT
   • Присваивания (в UPDATE): =
   • И другие (LIKE, IN, BETWEEN и т.д.)

Пример разбора предложения:

SELECT * FROM STUDENTS WHERE AGE > 19;

• SELECT, FROM, WHERE: Ключевые слова
• *: Специальный символ (означает “все колонки”)
• STUDENTS: Идентификатор (имя таблицы)
• AGE: Идентификатор (имя колонки)
• >: Оператор сравнения
• 19: Константа (числовая)
• ;: Завершение предложения

Часть 4: Работа с Таблицами (DDL)

Типы таблиц:

• Базовые таблицы: Реально существуют, хранятся на диске. Мы создаем их с помощью CREATE TABLE.
• Виртуальные таблицы: Не существуют постоянно, их содержимое вычисляется в момент запроса. Основной вид — представления (Views). Также сюда можно отнести результаты запросов, курсоры.

Создание базовой таблицы (CREATE TABLE)

Основная команда для создания структуры таблицы.

Синтаксис:

CREATE TABLE имя_таблицы (
    имя_колонки1 тип_данных1 [ограничения_колонки1],
    имя_колонки2 тип_данных2 [ограничения_колонки2],
    ...
    [ограничения_таблицы]
);

Пример:

-- Создаем таблицу студентов
CREATE TABLE students (
    stud_id    INTEGER,      -- ID студента (целое число)
    stud_name  TEXT,         -- Имя студента (текст произвольной длины)
    birth_date DATE          -- Дата рождения (дата)
);

Ввод/Вывод:

• Ввод (SQL команда):

  CREATE TABLE students (
      stud_id    INTEGER,
      stud_name  TEXT,
      birth_date DATE
  );
  

• Вывод (Сообщение от СУБД):

  CREATE TABLE

  (Это сообщение означает, что таблица успешно создана).

Удаление таблицы (DROP TABLE)

Удаляет таблицу и все данные в ней. Используйте с осторожностью!

Синтаксис:

DROP TABLE имя_таблицы;

Пример:

DROP TABLE students;

Ввод/Вывод:

• Ввод (SQL команда):

  DROP TABLE students;
  

• Вывод (Сообщение от СУБД):

  DROP TABLE

Изменение таблицы (ALTER TABLE)

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

Синтаксис (примеры):

-- Добавить колонку
ALTER TABLE имя_таблицы ADD COLUMN имя_новой_колонки тип_данных [ограничения];

-- Удалить колонку
ALTER TABLE имя_таблицы DROP COLUMN имя_колонки;

-- Изменить тип колонки (синтаксис может отличаться)
ALTER TABLE имя_таблицы ALTER COLUMN имя_колонки TYPE новый_тип_данных;

Примеры:

-- Добавляем колонку для номера группы в таблицу students
ALTER TABLE students ADD COLUMN group_name TEXT;

-- Удаляем колонку с датой рождения
ALTER TABLE students DROP COLUMN birth_date;

Ввод/Вывод:

• Ввод (SQL команда):

  ALTER TABLE students ADD COLUMN group_name TEXT;
  

• Вывод (Сообщение от СУБД):

  ALTER TABLE

• Ввод (SQL команда):

  ALTER TABLE students DROP COLUMN birth_date;
  

• Вывод (Сообщение от СУБД):

  ALTER TABLE

Часть 5: Типы Данных в PostgreSQL

Каждая колонка в таблице должна иметь тип данных. Тип данных:

• Определяет, какие значения могут храниться в колонке.
• Определяет “смысл” данных и какие операции с ними можно выполнять.
• Влияет на объем памяти, занимаемый данными.

Тип данных задается при создании таблицы (CREATE TABLE) или добавлении колонки (ALTER TABLE ADD COLUMN).

Основные типы данных в PostgreSQL:

1. Числовые типы:

   • SMALLINT: Целые числа, 2 байта (диапазон от -32768 до +32767).
   • INTEGER (или INT): Целые числа, 4 байта (около ±2 миллиардов). Самый распространенный целый тип.
   • BIGINT: Целые числа, 8 байт (очень большой диапазон).
   • NUMERIC(precision, scale) или DECIMAL(precision, scale): Числа с фиксированной точностью. precision - общее число знаков, scale - число знаков после запятой. Используется для финансовых расчетов, где важна точность. Пример: NUMERIC(10, 2) - до 10 цифр всего, 2 из них после запятой.
   • REAL: Числа с плавающей точкой, одинарная точность (4 байта).
   • DOUBLE PRECISION (или FLOAT8): Числа с плавающей точкой, двойная точность (8 байт).

2. Символьные (строковые) типы:

   • CHARACTER VARYING(n) или VARCHAR(n): Строка переменной длины с ограничением максимальной длины n.
   • CHARACTER(n) или CHAR(n): Строка фиксированной длины n. Если строка короче n, она дополняется пробелами до длины n. Используется редко.
   • TEXT: Строка переменной длины без явного ограничения (но есть технические ограничения). Самый удобный и часто используемый тип для текста в PostgreSQL.

   Пример использования CHAR(n):

   CREATE TABLE test_char (
       fixed_name CHAR(10)
   );
   
   INSERT INTO test_char (fixed_name) VALUES ('Valery');
   
   -- При выборке 'Valery' будет дополнено пробелами до 10 символов: 'Valery    '
   SELECT fixed_name, length(fixed_name) FROM test_char;
   

   Вывод:

    fixed_name | length
   ------------+--------
    Valery     |     10
   (1 row)

3. Логический тип:

   • BOOLEAN: Может принимать значения TRUE, FALSE или NULL.
   • В PostgreSQL можно задавать разными способами:
     • TRUE: TRUE, 'true', 't', 'yes', 'y', 'on', '1'
     • FALSE: FALSE, 'false', 'f', 'no', 'n', 'off', '0'
   • Рекомендуется использовать TRUE и FALSE.

4. Типы даты/времени:

   • DATE: Хранит только дату (год, месяц, день). Пример: '2023-10-27'.
   • TIME [ WITHOUT TIME ZONE ]: Хранит только время суток. Пример: '11:30:00'.
   • TIMESTAMP [ WITHOUT TIME ZONE ]: Хранит дату и время. Пример: '2023-10-27 11:30:00'.
   • TIMESTAMP WITH TIME ZONE (или TIMESTAMPTZ): Хранит дату и время с учетом часового пояса. Значение хранится в UTC, а при отображении конвертируется в текущий часовой пояс сессии. Это предпочтительный тип для хранения моментов времени. Пример: '2023-10-27 11:30:00+03'.
   • INTERVAL: Хранит временной интервал (разницу между двумя моментами времени). Пример: INTERVAL '2 days', INTERVAL '1 hour 30 minutes'.

Пример с типами:

CREATE TABLE exams (
    exam_id    INTEGER,    -- ID экзамена
    exam_name  TEXT,       -- Название экзамена
    exam_date  TIMESTAMPTZ -- Дата и время проведения с часовым поясом
);

Часть 6: NULL-значения

NULL — это специальное значение в SQL, которое означает отсутствие или неизвестность информации.

• Важно: NULL — это не пустая строка (), не ноль (0), не пробел (' '). Это именно “ничего”.
• Сравнение с NULL: Любое сравнение с NULL (кроме специальных операторов IS NULL и IS NOT NULL) дает результат NULL (что в контексте WHERE обычно интерпретируется как FALSE).
  • NULL = NULL дает NULL (не TRUE)
  • 5 = NULL дает NULL
  • 5 > NULL дает NULL
  • Чтобы проверить, является ли значение NULL, используйте:
    • column_name IS NULL
    • column_name IS NOT NULL
• Агрегатные функции и NULL: Большинство агрегатных функций (SUM, AVG, MIN, MAX, COUNT(column_name)) игнорируют NULL-значения при подсчете.
  • Исключение: COUNT(*) считает все строки, независимо от NULL.

Пример с COUNT и NULL:

Рассмотрим таблицу STUDENT из слайда 50:

id	name	surname	gr_id
1	Григорий	Иванов	34
2	Григорий	Петров	NULL
3	Иван	Сидоров	NULL

Запрос 1:

SELECT COUNT(gr_id) FROM STUDENT
WHERE gr_id IS NULL;

• Объяснение: Сначала WHERE gr_id IS NULL отбирает строки с id=2 и id=3. Затем COUNT(gr_id) пытается посчитать количество не-NULL значений в колонке gr_id для отобранных строк. Так как в обеих строках gr_id равен NULL, агрегатная функция их игнорирует.

• Вывод:

   count
  -------
       0
  (1 row)

Запрос 2:

SELECT COUNT(*) FROM STUDENT
WHERE gr_id IS NULL;

• Объяснение: Сначала WHERE gr_id IS NULL отбирает строки с id=2 и id=3. Затем COUNT(*) считает количество отобранных строк. Их две.

• Вывод:

   count
  -------
       2
  (1 row)

• NULL в DISTINCT, ORDER BY, GROUP BY: В этих операциях все NULL-значения считаются одинаковыми.

Часть 7: Ограничения целостности (Constraints)

Типы данных — это первый уровень ограничений, но их часто недостаточно. SQL позволяет определять более сложные правила для колонок и таблиц — ограничения целостности (constraints).

1. CHECK

   • Позволяет задать логическое выражение, которое должно быть истинным (TRUE) или неизвестным (NULL) для любого значения, вставляемого или обновляемого в колонке (или для строки, если это ограничение таблицы). Если выражение ложно (FALSE), операция не будет выполнена (вызовет ошибку).
   • Используется для реализации пользовательских правил (диапазоны значений, зависимости между колонками и т.д.).

   Пример (ограничение колонки): Количество несданных курсов не может быть отрицательным.

   CREATE TABLE students (
       st_id            INTEGER,
       st_name          TEXT,
       failed_courses   INTEGER CHECK (failed_courses >= 0) -- Ограничение CHECK
   );
   
   -- Попытка вставки некорректных данных
   INSERT INTO students (st_id, st_name, failed_courses) VALUES (1, 'Test', -1);
   

   Вывод при ошибке:

   ERROR:  new row for relation "students" violates check constraint "students_failed_courses_check"
   DETAIL:  Failing row contains (1, Test, -1).

   Пример (ограничение таблицы): Количество несданных курсов не должно превышать максимальное количество.

   CREATE TABLE students (
       st_id            INTEGER,
       failed_max       INTEGER,
       failed_courses   INTEGER,
       CONSTRAINT fcrs CHECK ( -- Задаем имя ограничению 'fcrs'
           failed_courses >= 0 AND failed_courses <= failed_max
       )
   );
   

   Задание имени (CONSTRAINT fcrs) полезно для управления ограничением в будущем (например, для его удаления) и для понимания сообщений об ошибках.

2. NOT NULL

   • Простое ограничение, запрещающее хранить NULL в колонке. Каждая строка должна иметь значение в этой колонке.
   • Логически эквивалентно CHECK (column_name IS NOT NULL).

   Пример: ID студента, максимальное и текущее число несданных курсов не могут быть NULL.

   CREATE TABLE students (
       st_id            INTEGER NOT NULL,
       failed_max       NUMERIC NOT NULL,
       failed_courses   INTEGER NOT NULL
   );
   

3. UNIQUE

   • Гарантирует, что все значения в колонке (или комбинации значений в группе колонок) уникальны в пределах таблицы.
   • Допускает наличие NULL-значений (и несколько строк могут иметь NULL, так как NULL не равен NULL).

   Пример (ограничение колонки): ID студента должен быть уникальным (и не NULL).

   CREATE TABLE students (
       st_id      INTEGER NOT NULL UNIQUE,
       st_name    TEXT NOT NULL,
       st_surname TEXT NOT NULL,
       st_birth   DATE NOT NULL
   );
   

   Пример (ограничение таблицы): Комбинация имени, фамилии и даты рождения должна быть уникальной.

   CREATE TABLE students (
       st_id      INTEGER NOT NULL UNIQUE, -- ID тоже уникален
       st_name    TEXT NOT NULL,
       st_surname TEXT NOT NULL,
       st_birth   DATE NOT NULL,
       UNIQUE (st_name, st_surname, st_birth) -- Уникальность для группы колонок
   );
   

4. PRIMARY KEY (Первичный ключ)

   • Обозначает одну или несколько колонок, которые уникально идентифицируют каждую строку в таблице.
   • Является комбинацией ограничений NOT NULL и UNIQUE.
   • В таблице может быть только один первичный ключ.
   • В PostgreSQL на колонку(и) первичного ключа автоматически создается UNIQUE B-Tree индекс для быстрого поиска.

   Пример:

   CREATE TABLE students (
       st_id      INTEGER PRIMARY KEY, -- Объявление первичного ключа для одной колонки
       st_name    TEXT NOT NULL,
       st_surname TEXT NOT NULL,
       st_birth   DATE NOT NULL,
       UNIQUE (st_name, st_surname, st_birth) -- Дополнительное ограничение уникальности
   );
   

   Если первичный ключ составной (из нескольких колонок), он объявляется как ограничение таблицы:

   CREATE TABLE student_exams (
       st_id   INTEGER,
       ex_id   INTEGER,
       grade   INTEGER,
       PRIMARY KEY (st_id, ex_id) -- Составной первичный ключ
   );
   

5. FOREIGN KEY (Внешний ключ) и REFERENCES

   • Обеспечивает ссылочную целостность между двумя таблицами.
   • Указывает, что значения в колонке(ах) одной таблицы (дочерней) должны соответствовать значениям в колонке(ах) первичного (или уникального) ключа другой таблицы (родительской).
   • Позволяет реализовать связи (1:1, 1:M) между сущностями.

   Пример (связь 1:M между GROUP и STUDENT):

   -- Родительская таблица
   CREATE TABLE groups (
       gr_id   INTEGER PRIMARY KEY, -- Первичный ключ
       gr_name TEXT
   );
   
   -- Дочерняя таблица
   CREATE TABLE students (
       st_id   INTEGER PRIMARY KEY,
       st_name TEXT,
       gr_id   INTEGER REFERENCES groups (gr_id) -- Внешний ключ ссылается на groups.gr_id
                                                 -- Если имя колонки совпадает с PK родителя, (gr_id) можно опустить
                                                 -- ALTERNATIVE: gr_id INTEGER REFERENCES groups
   );
   

   Здесь students.gr_id ссылается на groups.gr_id. Это значит, что в students.gr_id можно вставить только те значения gr_id, которые уже существуют в таблице groups, либо NULL (если не запрещено NOT NULL).

   Пример с данными:

   INSERT INTO groups (gr_id, gr_name) VALUES (1, 'P3100');
   INSERT INTO groups (gr_id, gr_name) VALUES (2, 'P3101');
   
   -- Эта вставка успешна
   INSERT INTO students (st_id, st_name, gr_id) VALUES (101, 'Иванов', 1);
   -- Эта вставка вызовет ошибку, т.к. группы с gr_id=3 нет
   INSERT INTO students (st_id, st_name, gr_id) VALUES (102, 'Петров', 3);
   

   Вывод при ошибке:

   ERROR:  insert or update on table "students" violates foreign key constraint "students_gr_id_fkey"
   DETAIL:  Key (gr_id)=(3) is not present in table "groups".

   Действия при обновлении/удалении родительской записи (ON DELETE, ON UPDATE): Что должно произойти с дочерними записями (студентами), если родительская запись (группа) удаляется или ее первичный ключ изменяется?

   • RESTRICT (По умолчанию): Запретить операцию над родительской записью, если есть связанные дочерние.
   • NO ACTION: То же, что и RESTRICT, но проверка выполняется в конце транзакции (если ограничения отложенные).
   • CASCADE: Выполнить ту же операцию над дочерними записями (удалить/обновить связанные строки). Опасно!
   • SET NULL: Установить значение внешнего ключа в дочерних записях в NULL. (Колонка FK должна допускать NULL).
   • SET DEFAULT: Установить значение внешнего ключа в дочерних записях в значение по умолчанию (DEFAULT). (Для колонки FK должно быть задано значение по умолчанию).

   Пример с ON DELETE CASCADE:

   DROP TABLE students; -- Удалим старую таблицу
   
   CREATE TABLE students (
       st_id   INTEGER PRIMARY KEY,
       st_name TEXT,
       gr_id   INTEGER REFERENCES groups (gr_id)
                       ON DELETE CASCADE -- При удалении группы удалить и студентов
                       ON UPDATE CASCADE -- При изменении gr_id группы изменить и у студентов
   );
   
   INSERT INTO students (st_id, st_name, gr_id) VALUES (101, 'Иванов', 1);
   INSERT INTO students (st_id, st_name, gr_id) VALUES (102, 'Петров', 1);
   
   -- Эта команда удалит группу с ID=1 И студентов с ID=101, 102
   DELETE FROM groups WHERE gr_id = 1;
   

Часть 8: Отображение ER-диаграмм в Реляционную БД

Как преобразовать ER-модель в набор таблиц SQL:

1. Сущность → Таблица: Каждая стержневая или характеристическая сущность обычно становится таблицей.
2. Атрибут → Столбец: Каждый атрибут сущности становится столбцом в соответствующей таблице. Нужно выбрать подходящий тип данных PostgreSQL.
3. Ключ сущности → Первичный ключ: Потенциальный ключ (часто суррогатный ID) становится первичным ключом таблицы (PRIMARY KEY).
4. Связь → Внешний ключ или Отдельная таблица:
   • Связь 1:1: Добавляется внешний ключ (FOREIGN KEY REFERENCES ...) в одну из таблиц (обычно в “подчиненную” или “характеристическую”). На этот внешний ключ также накладывается ограничение UNIQUE.
   • Связь 1:M: Внешний ключ добавляется в таблицу на стороне “M” (много), ссылаясь на первичный ключ таблицы на стороне “1”.
   • Связь M:M: Создается отдельная связующая (associative) таблица. Эта таблица содержит как минимум два столбца — внешние ключи, ссылающиеся на первичные ключи связываемых таблиц. Комбинация этих двух внешних ключей обычно образует составной первичный ключ связующей таблицы. Если связь M:M сама имела атрибуты (как ассоциативная сущность), эти атрибуты становятся дополнительными столбцами в связующей таблице.

Пример реализации связи M:M (СТУДЕНТ - ЭКЗАМЕН):

• ER-модель: СТУДЕНТ – (M) — Сдает — (M) – ЭКЗАМЕН

• Таблицы:

  CREATE TABLE students (
      st_id   INTEGER PRIMARY KEY,
      st_name TEXT
  );
  
  CREATE TABLE exams (
      ex_id   INTEGER PRIMARY KEY,
      ex_name TEXT
  );
  
  -- Связующая таблица
  CREATE TABLE student_exams (
      st_id   INTEGER REFERENCES students (st_id) ON DELETE CASCADE, -- Внешний ключ 1
      ex_id   INTEGER REFERENCES exams (ex_id) ON DELETE CASCADE,    -- Внешний ключ 2
      grade   INTEGER CHECK (grade >= 2 AND grade <= 5),             -- Атрибут связи (оценка)
      PRIMARY KEY (st_id, ex_id) -- Составной первичный ключ
  );
  

  Здесь student_exams реализует связь M:M. Каждая строка означает, что конкретный студент (st_id) сдал конкретный экзамен (ex_id) с определенной оценкой (grade).

Часть 9: Дополнительные возможности

Создание таблицы на основе другой (LIKE)

Копирует структуру (колонки, типы, NOT NULL) из существующей таблицы. Опционально можно скопировать значения по умолчанию, ограничения и т.д.

-- Предположим, есть таблица абитуриентов
CREATE TABLE applicant (
    applicant_id INT PRIMARY KEY,
    name         TEXT NOT NULL,
    surname      TEXT NOT NULL
);

-- Создаем таблицу студентов, похожую на абитуриентов, но с доп. колонкой
CREATE TABLE student LIKE applicant INCLUDING ALL ( -- INCLUDING ALL копирует всё, что можно
    group_id INT -- Добавляем новую колонку
);

-- Удалим лишнюю колонку applicant_id, если она не нужна
ALTER TABLE student DROP COLUMN applicant_id;
-- Добавим свою student_id
ALTER TABLE student ADD COLUMN student_id INT PRIMARY KEY;

Последовательности (SEQUENCE)

Объекты БД, генерирующие последовательности чисел (обычно для автоинкрементных ID).

-- Создаем последовательность для ID студентов
CREATE SEQUENCE student_id_seq
    START WITH 1       -- Начать с 1
    INCREMENT BY 1     -- Увеличивать на 1
    NO MINVALUE        -- Нет минимального значения
    NO MAXVALUE        -- Нет максимального значения
    CACHE 1;           -- Не кэшировать значения (для гарантии отсутствия пропусков при сбоях)

-- Получить следующее значение
SELECT nextval('student_id_seq');
-- Получить текущее значение (в рамках текущей сессии, после вызова nextval)
SELECT currval('student_id_seq');
-- Установить значение
SELECT setval('student_id_seq', 100);
-- Установить значение и указать, что следующий nextval вернет 101 (а не 100+increment)
SELECT setval('student_id_seq', 100, true);
-- Узнать последнее значение, выданное nextval в текущей сессии
SELECT lastval();

Использование последовательности при вставке:

-- Вариант 1: Явный вызов nextval
INSERT INTO students (st_id, st_name, gr_id)
VALUES (nextval('student_id_seq'), 'Сидоров', 2);

-- Вариант 2: Значение по умолчанию в таблице
CREATE TABLE students (
    st_id   INTEGER PRIMARY KEY DEFAULT nextval('student_id_seq'), -- Устанавливаем default
    st_name TEXT,
    gr_id   INTEGER REFERENCES groups (gr_id)
);

-- Теперь st_id можно не указывать при вставке
INSERT INTO students (st_name, gr_id) VALUES ('Петров', 1); -- st_id заполнится автоматически

Типы SERIAL, SMALLSERIAL, BIGSERIAL

Это “синтаксический сахар” в PostgreSQL для создания автоинкрементных колонок. Они не являются настоящими типами.

• SMALLSERIAL: Создает колонку SMALLINT, последовательность и устанавливает DEFAULT nextval(...).
• SERIAL: Создает колонку INTEGER, последовательность и устанавливает DEFAULT nextval(...).
• BIGSERIAL: Создает колонку BIGINT, последовательность и устанавливает DEFAULT nextval(...).

Пример:

CREATE TABLE students (
    st_id   SERIAL PRIMARY KEY, -- Проще, чем создавать sequence вручную
    st_name TEXT,
    gr_id   INTEGER REFERENCES groups (gr_id)
);

-- При вставке st_id генерируется автоматически
INSERT INTO students (st_name, gr_id) VALUES ('Кузнецов', 2);

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

Глава 3

В предыдущей главе мы научились проектировать структуру базы данных с помощью ER-моделей и создавать таблицы с помощью SQL. Но как убедиться, что полученная структура таблиц (схема) является “хорошей”? Не приведет ли она к проблемам при работе с данными?

Именно для ответа на эти вопросы существует процесс нормализации.

Часть 1: Реляционное представление и Реляционная Алгебра (Краткое Повторение и Дополнение)

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

• Отношение (Таблица): Набор кортежей (строк).
• Атрибут (Столбец): Имеет имя и домен (тип данных).
• Кортеж (Строка): Набор значений атрибутов.
• Ключ: Атрибут(ы), уникально идентифицирующий кортеж.

Реляционная Алгебра:

Это набор операций над отношениями, результатом которых всегда является новое отношение. Нам понадобятся основные операции для понимания того, как СУБД может выполнять запросы и как устроена нормализация.

1. Выборка (Selection, σ): Выбирает кортежи (строки) из отношения, удовлетворяющие заданному условию (предикату $\phi$).
   • Обозначение: ${\sigma }_{\phi }(\text{R})$ - выбрать строки из отношения R, где условие $\phi$ истинно.
   • SQL аналог: Условие в секции WHERE.
   • Пример: Выбрать студентов группы ‘3100’, чей ID >= 150000.
     • SQL: SELECT * FROM STUDENTS WHERE STUDENTS.GROUP = '3100' AND STUDENTS.ID >= 150000;
     • Рел. Алгебра: ${\sigma }_{(\text{STUDENTS.GROUP='3100'})\wedge (\text{STUDENTS.ID}\ge 150000)}(\text{STUDENTS})$ (Здесь $\wedge$ означает логическое “И”)
2. Проекция (Projection, π): Выбирает указанные атрибуты (столбцы) из отношения, удаляя дубликаты строк в результирующем отношении.
   • Обозначение: ${\pi }_{\text{атрибуты}}(\text{R})$ - выбрать столбцы $\text{атрибуты}$ из отношения R.
   • SQL аналог: Список столбцов в секции SELECT. Удаление дубликатов соответствует SELECT DISTINCT.
   • Пример: Получить имена и группы всех студентов.
     • SQL: SELECT DISTINCT name, group FROM STUDENTS;
     • Рел. Алгебра: ${\pi }_{\text{name, group}}(\text{STUDENTS})$
3. Соединение (Join, ⋈): Комбинирует кортежи из двух отношений на основе некоторого условия ($\theta$, тета).
   • Тета-соединение (Theta Join): $\text{R}{\bowtie }_{\theta }\text{S}$. Общий случай, где $\theta$ - любое условие сравнения между атрибутами R и S. Логически эквивалентно: ${\sigma }_{\theta }(\text{R}\times \text{S})$ (выборка из декартова произведения).
   • Эквисоединение (Equijoin): Частный случай тета-соединения, где условие $\theta$ содержит только операции равенства ($=$).
   • Естественное соединение (Natural Join): Эквисоединение по всем атрибутам с одинаковыми именами, причем совпадающие столбцы включаются в результат только один раз.
   • SQL аналог: Операторы JOIN. INNER JOIN ON условие соответствует тета-соединению или эквисоединению. NATURAL JOIN соответствует естественному соединению.
   • Пример: Соединить студентов и их экзамены по ID студента.
     • SQL: SELECT * FROM STUDENTS JOIN EXAMS ON STUDENTS.ID = EXAMS.STUD_ID;
     • Рел. Алгебра: $\text{STUDENTS}{\bowtie }_{\theta }\text{EXAMS}$, где условие $\theta$ это $\text{STUDENTS.ID}=\text{EXAMS.STUD}\mathrm{\_}\text{ID}$

Законы Реляционной Алгебры (Примеры Эквивалентных Преобразований):

СУБД использует правила эквивалентности для преобразования запроса в различные планы выполнения, чтобы выбрать наиболее эффективный.

• Соединение коммутативно (для INNER JOIN): $\text{R}{\bowtie }_{\theta }\text{S}\equiv \text{S}{\bowtie }_{\theta }\text{R}$
• Соединение ассоциативно (для INNER JOIN): $\text{R}{\bowtie }_{\theta }(\text{S}{\bowtie }_{\phi }\text{T})\equiv (\text{R}{\bowtie }_{\theta }\text{S}){\bowtie }_{\phi }\text{T}$
• Каскад выборок: ${\sigma }_{\theta \wedge \phi }(\text{R})\equiv {\sigma }_{\theta }({\sigma }_{\phi }(\text{R}))$
• “Проталкивание” выборки через соединение: Если условие $\phi$ относится только к атрибутам $\text{R}$, то: ${\sigma }_{\phi }(\text{R}{\bowtie }_{\theta }\text{S})\equiv ({\sigma }_{\phi }(\text{R})){\bowtie }_{\theta }\text{S}$. Это важное правило оптимизации: выполнять выборку как можно раньше, чтобы уменьшить размер данных для соединения.
• “Проталкивание” проекции через соединение (сложнее): ${\pi }_{\text{A}}(\text{R}{\bowtie }_{\theta }\text{S})$ можно преобразовать, оставив в $\text{R}$ и $\text{S}$ только те атрибуты, которые нужны для результата ($\text{A}$) и для условия соединения ($\theta$). Тоже важное правило: отбрасывать ненужные столбцы как можно раньше.

План Выполнения Запроса:

Это последовательность операций реляционной алгебры (и конкретных алгоритмов их выполнения), которую СУБД строит для ответа на SQL-запрос. Часто представляется в виде дерева операций. Для одного SQL-запроса может существовать несколько эквивалентных планов (дающих одинаковый результат), но с разной стоимостью выполнения. Оптимизатор запросов выбирает план с наименьшей предполагаемой стоимостью.

Пример одного из возможных планов для запроса SELECT * FROM STUDENTS ST JOIN EXAMS EXAM ON ST.ID = EXAM.STUD_ID WHERE ST.GROUP = '3100' AND ST.ID >= 150000;

Часть 2: Проблемы Плохого Дизайна и Аномалии

Зачем нам вообще нужна нормализация, если мы можем просто создать одну большую таблицу со всеми нужными данными? Рассмотрим таблицу STUDENTS с информацией о студентах, их группах и кураторах (GrMentor - Куратор Группы).

Таблица STUDENTS (Ненормализованная)

StudID	StudName	Group	GrMentor
1	Ivan Petrov	P3100	Egor Kirov
3	Vasily Ivanov	P3101	Roman Ivov
34	Gleb Anisimov	P3100	Egor Kirov

Проблемы:

1. Избыточность Данных: Информация о кураторе (GrMentor) повторяется для каждого студента из одной и той же группы (Egor Kirov для группы P3100). Это ведет к неэффективному использованию памяти.

2. Аномалии Обновления (Update Anomalies): Если куратор группы P3100 сменится, нам придется обновить поле GrMentor во всех строках, где Group = 'P3100'. Если мы обновим не все строки, данные станут противоречивыми (у одной группы окажется несколько кураторов).

   -- Попытка сменить куратора только для одного студента
   UPDATE STUDENTS
   SET GrMentor = 'Eugene Lomov'
   WHERE StudName = 'Ivan Petrov';
   

   Результат (Несогласованность):

   StudID	StudName	Group	GrMentor
   1	Ivan Petrov	P3100	Eugene Lomov (Это новый куратор)
   3	Vasily Ivanov	P3101	Roman Ivov
   34	Gleb Anisimov	P3100	Egor Kirov (А тут остался старый)

3. Аномалии Вставки (Insertion Anomalies):

   • Мы не можем добавить информацию о новой группе и ее кураторе, пока в этой группе нет хотя бы одного студента (потому что StudID, вероятно, часть первичного ключа или просто идентификатор студента, который не может быть NULL для информации о группе).
   • При добавлении нового студента в существующую группу, мы должны правильно указать ее куратора. Ошибка при вводе имени куратора приведет к несогласованности (см. пример со слайда 26, где E.Kirov и Egor Lomov могут быть одним человеком).

   -- Попытка добавить студентов с разными написаниями куратора
   INSERT INTO STUDENTS VALUES(57, 'Nina Simonova', 'P3100', 'E. Kirov');
   INSERT INTO STUDENTS VALUES(58, 'Petr Uvarov', 'P3100', 'Egor Lomov');
   

4. Аномалии Удаления (Deletion Anomalies): Если мы удалим последнего студента из какой-либо группы (например, Василия Иванова из P3101), мы потеряем информацию о самой группе P3101 и ее кураторе Романе Ивове, даже если эта информация нам еще нужна.

   DELETE FROM STUDENTS
   WHERE StudName = 'Vasily Ivanov';
   

   После этого запроса информация о группе P3101 и ее кураторе исчезнет из таблицы.

Все эти проблемы возникают из-за того, что в одной таблице смешаны данные о разных сущностях (студенты и группы/кураторы) и существуют “неправильные” зависимости между атрибутами. Нормализация помогает выявить и устранить эти проблемы.

Часть 3: Функциональные Зависимости (ФЗ)

Ключевое понятие для нормализации.

• Функциональная зависимость (Functional Dependency, FD) описывает смысловую связь между атрибутами внутри одного отношения (таблицы).
• Говорят, что атрибут (или набор атрибутов) $\text{B}$ функционально зависит от атрибута (или набора атрибутов) $\text{A}$, если для каждого возможного значения $\text{A}$ существует ровно одно соответствующее значение $\text{B}$.
• Обозначение: $\text{A}\to \text{B}$ (читается “A функционально определяет B” или “B функционально зависит от A”).
• $\text{A}$ называется детерминантом.

Важно: ФЗ определяются смыслом данных (семантикой) предметной области, а не текущими данными в таблице!

Примеры ФЗ для таблицы STUDENTS (StudID, StudName, Group, GrMentor):

• $\text{StudID}\to \text{StudName}$ (По ID студента однозначно определяется его имя).
• $\text{StudID}\to \text{Group}$ (По ID студента однозначно определяется его группа).
• $\text{StudID}\to \text{GrMentor}$ (По ID студента можно узнать его группу, а по группе - куратора, т.е. косвенно ID определяет куратора).
• $\text{Group}\to \text{GrMentor}$ (По номеру группы однозначно определяется ее куратор. Предполагаем, что у группы только один куратор).
• $\text{StudID, Group}\to \text{GrMentor}$ (Тоже верно, но избыточно, т.к. $\text{Group}$ уже зависит от $\text{StudID}$).

Примеры НЕ ФЗ:

• $\text{Group}\to \text{StudID}$ (Неверно, т.к. в одной группе много студентов).
• $\text{StudName}\to \text{StudID}$ (Неверно, т.к. могут быть тезки).

Типы ФЗ:

• Тривиальная ФЗ: Зависимость вида $\text{A}\to \text{B}$, где $\text{B}$ является подмножеством $\text{A}$. Например, $\{\text{StudID},\text{StudName}\}\to \text{StudName}$. Такие зависимости выполняются всегда и не несут полезной информации для нормализации. Обычно рассматривают только нетривиальные ФЗ.
• Полная ФЗ: Зависимость $\text{A}\to \text{B}$, где $\text{A}$ – составной детерминант (несколько атрибутов), и $\text{B}$ не зависит ни от какого подмножества $\text{A}$. Мы уже обсуждали это в контексте 2НФ.
• Частичная ФЗ: Зависимость $\text{A}\to \text{B}$, где $\text{A}$ – составной детерминант, и $\text{B}$ зависит от части $\text{A}$. (Пример: $\{\text{StudID},\text{ExamID}\}\to \text{StudName}$, но при этом $\text{StudID}\to \text{StudName}$. Здесь $\text{StudName}$ частично зависит от ключа).
• Транзитивная ФЗ: Зависимость $\text{A}\to \text{C}$, которая существует только через промежуточный атрибут $\text{B}$, такой что $\text{A}\to \text{B}$ и $\text{B}\to \text{C}$, при этом $\text{B}$ не зависит от $\text{A}$ ($\text{B}\to \text{A}$ неверно) и $\text{B}$ не является частью ключа $\text{A}$. (Пример: $\text{StudID}\to \text{Group}$ и $\text{Group}\to \text{GrMentor}$, следовательно, $\text{StudID}\to \text{GrMentor}$ транзитивно через $\text{Group}$).

Аксиомы Армстронга: Формальные правила для вывода новых ФЗ из существующих:

1. Рефлексивность: Если $\text{B}\subseteq \text{A}$, то $\text{A}\to \text{B}$. (Тривиальная зависимость).
2. Дополнение (Augmentation): Если $\text{A}\to \text{B}$, то $\text{A, C}\to \text{B, C}$. (Добавление атрибута $\text{C}$ к обеим частям).
3. Транзитивность: Если $\text{A}\to \text{B}$ и $\text{B}\to \text{C}$, то $\text{A}\to \text{C}$.

Эти аксиомы позволяют формально вывести все возможные ФЗ для отношения, зная некоторый начальный набор.

Часть 4: Нормальные Формы

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

Ненормализованная Форма (UNF / 0NF): Таблица содержит неатомарные значения (повторяющиеся группы, списки в ячейках). Это нарушает основные принципы реляционной модели.

Пример (из слайда 38):

StudID	StudName	ExamID	ExamName	ExDate	ProfID	ProfName
123	Ivan Ivanov	34	OPD	14.01.19	55	Rebrov A.
		78	DBMS	29.12.20	789	Uvarov S.
345	Egor Kirov	34	OPD	14.01.19	55	Rebrov A.
		87	History	25.01.19	342	Serov G.

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

Первая Нормальная Форма (1НФ):

• Правило: Отношение находится в 1НФ, если все его атрибуты содержат только атомарные (неделимые) значения. На пересечении строки и столбца - ровно одно значение из домена.

Как достичь: 1. (Плохой способ) “Расплющить” таблицу: Создать отдельную строку для каждого значения из повторяющейся группы. Это приводит к сильной избыточности. Пример (из слайда 40):

StudID	StudName	ExamID	ExamName	ExDate	ProfID	ProfName
123	Ivan Ivanov	34	OPD	14.01.19	55	Rebrov A.
123	Ivan Ivanov	78	DBMS	29.12.20	789	Uvarov S.
345	Egor Kirov	34	OPD	14.01.19	55	Rebrov A.
345	Egor Kirov	87	History	25.01.19	342	Serov G.

Теперь значения атомарны, но информация о студенте Иванове и Кирове дублируется.

2. (Хороший способ) Декомпозиция: Разделить таблицу на несколько, вынеся повторяющиеся группы в отдельную таблицу со связью через внешний ключ. Пример (из слайда 41):

Таблица STUDENTS

StudID (PK)	StudName
123	Ivan Ivanov
345	Egor Kirov

Таблица EXAMS

StudID (FK)	ExamID	ExamName	ExDate	ProfID	ProfName
123	34	OPD	14.01.19	55	Rebrov A.
123	78	DBMS	29.12.20	789	Uvarov S.
345	34	OPD	14.01.19	55	Rebrov A.
345	87	History	25.01.19	342	Serov G.

Теперь обе таблицы в 1НФ.

Вторая Нормальная Форма (2НФ):

• Правило: Отношение находится в 2НФ, если оно находится в 1НФ и все неключевые атрибуты полностью функционально зависят от каждого потенциального ключа. (Нет частичных зависимостей от ключа).
• Актуально для таблиц с составными первичными ключами.
• Цель: Устранить избыточность, возникающую из-за того, что неключевой атрибут зависит только от части составного ключа.
• Как достичь: Вынести частично зависимые атрибуты и ту часть ключа, от которой они зависят, в отдельную таблицу.

Пример (используем “расплющенную” таблицу из 1НФ):

• Первичный ключ: $\{\text{StudID},\text{ExamID}\}$.
• Неключевые атрибуты: $\text{StudName}$, $\text{ExamName}$, $\text{ExDate}$, $\text{ProfID}$, $\text{ProfName}$.
• Частичные зависимости:
  • $\text{StudID}\to \text{StudName}$ (Зависит только от части ключа - $\text{StudID}$).
  • $\text{ExamID}\to \text{ExamName}$ (Зависит только от части ключа - $\text{ExamID}$).
• Полные зависимости:
  • $\{\text{StudID},\text{ExamID}\}\to \text{ExDate}$ (Предполагаем, что дата зависит от студента и экзамена).
  • $\{\text{StudID},\text{ExamID}\}\to \text{ProfID}$ (Предполагаем, что преподаватель зависит от студента и экзамена).
• Другие зависимости:
  • $\text{ProfID}\to \text{ProfName}$ (Имя преподавателя зависит от его ID).

Таблица не в 2НФ из-за частичных зависимостей $\text{StudName}$ и $\text{ExamName}$.

Декомпозиция для 2НФ:

1. Выносим $\text{StudID}\to \text{StudName}$:
   • Новая таблица $\text{STUDENTS}$: $\{\text{StudID}(\text{PK}),\text{StudName}\}$.
   • Старая таблица (переименуем в $\text{EXAMS}\mathrm{\_}\text{PARTICIPATION}$): $\{\text{StudID}(\text{FK}),\text{ExamID}(\text{PK}),\text{ExamName},\text{ExDate},\text{ProfID},\text{ProfName}\}$. ($\text{StudName}$ удален).
2. Выносим $\text{ExamID}\to \text{ExamName}$ из $\text{EXAMS}\mathrm{\_}\text{PARTICIPATION}$:
   • Новая таблица $\text{EXAMS}$: $\{\text{ExamID}(\text{PK}),\text{ExamName}\}$.
   • Таблица $\text{EXAMS}\mathrm{\_}\text{PARTICIPATION}$: $\{\text{StudID}(\text{FK}),\text{ExamID}(\text{FK}),\text{ExDate},\text{ProfID},\text{ProfName}\}$. ($\text{ExamName}$ удален). Первичный ключ теперь $\{\text{StudID},\text{ExamID}\}$.

Результат (все таблицы в 2НФ):

Таблица STUDENTS

StudID (PK)	StudName
123	Ivan Ivanov
345	Egor Kirov

Таблица EXAMS

ExamID (PK)	ExamName
34	OPD
78	DBMS
87	History

Таблица EXAMS_PARTICIPATION

StudID (FK, PK)	ExamID (FK, PK)	ExDate	ProfID	ProfName
123	34	14.01.19	55	Rebrov A.
123	78	29.12.20	789	Uvarov S.
345	34	14.01.19	55	Rebrov A.
345	87	25.01.19	342	Serov G.

Третья Нормальная Форма (3НФ):

• Правило: Отношение находится в 3НФ, если оно находится в 2НФ и все неключевые атрибуты нетранзитивно зависят от каждого потенциального ключа. (Нет транзитивных зависимостей неключевых атрибутов от ключа через другие неключевые атрибуты).
• Цель: Устранить избыточность, возникающую из-за того, что неключевой атрибут зависит от другого неключевого атрибута.
• Как достичь: Вынести транзитивно зависимые атрибуты и их непосредственный детерминант в отдельную таблицу.

Пример (используем таблицы из 2НФ):

• В таблицах $\text{STUDENTS}$ и $\text{EXAMS}$ нет транзитивных зависимостей (там просто ключ и один неключевой атрибут).
• Рассмотрим $\text{EXAMS}\mathrm{\_}\text{PARTICIPATION}$:
  • Первичный ключ: $\{\text{StudID},\text{ExamID}\}$.
  • Неключевые атрибуты: $\text{ExDate}$, $\text{ProfID}$, $\text{ProfName}$.
  • Зависимости:
    • $\{\text{StudID},\text{ExamID}\}\to \text{ExDate}$ (Полная)
    • $\{\text{StudID},\text{ExamID}\}\to \text{ProfID}$ (Полная)
    • $\text{ProfID}\to \text{ProfName}$ (Зависимость между неключевыми атрибутами!)
  • Транзитивная зависимость: $\{\text{StudID},\text{ExamID}\}\to \text{ProfID}$ и $\text{ProfID}\to \text{ProfName}$, следовательно, $\text{ProfName}$ транзитивно зависит от первичного ключа через $\text{ProfID}$.

Таблица $\text{EXAMS}\mathrm{\_}\text{PARTICIPATION}$ не в 3НФ.

Декомпозиция для 3НФ:

1. Выносим $\text{ProfID}\to \text{ProfName}$ из $\text{EXAMS}\mathrm{\_}\text{PARTICIPATION}$:
   • Новая таблица $\text{PROFS}$: $\{\text{ProfID}(\text{PK}),\text{ProfName}\}$.
   • Таблица $\text{EXAMS}\mathrm{\_}\text{PARTICIPATION}$: $\{\text{StudID}(\text{FK, PK}),\text{ExamID}(\text{FK, PK}),\text{ExDate},\text{ProfID}(\text{FK})\}$. ($\text{ProfName}$ удален, $\text{ProfID}$ остался как внешний ключ к $\text{PROFS}$).

Результат (все таблицы в 3НФ):

Таблица STUDENTS (без изменений) Таблица EXAMS (без изменений)

Таблица PROFS

ProfID (PK)	ProfName
55	Rebrov A.
789	Uvarov S.
342	Serov G.

Таблица EXAMS_PARTICIPATION

StudID (FK, PK)	ExamID (FK, PK)	ExDate	ProfID (FK)
123	34	14.01.19	55
123	78	29.12.20	789
345	34	14.01.19	55
345	87	25.01.19	342

Нормальная Форма Бойса-Кодда (НФБК / BCNF):

• Более строгая версия 3НФ.
• Правило: Отношение находится в НФБК, если для каждой нетривиальной функциональной зависимости $\text{A}\to \text{B}$, детерминант $\text{A}$ является суперключом (т.е. содержит в себе потенциальный ключ).
• Большинство таблиц в 3НФ также находятся и в НФБК. Проблемы могут возникнуть при наличии нескольких перекрывающихся потенциальных ключей.
• Часто является конечной целью нормализации на практике.

Высшие нормальные формы (4НФ, 5НФ): Существуют и другие нормальные формы (4НФ, 5НФ, DKNF), которые решают более тонкие проблемы, связанные с многозначными зависимостями и зависимостями соединения. На практике они используются редко. Обычно достаточно достичь 3НФ или НФБК.

Часть 5: Денормализация

Иногда, после приведения базы данных к высокой нормальной форме (например, 3НФ или НФБК), оказывается, что для выполнения частых запросов требуется слишком много операций соединения (JOIN) между таблицами. Это может снижать производительность.

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

• Прием: Объединение нескольких таблиц в одну, добавление избыточных данных.
• Плюсы:
  • Уменьшение количества соединений в запросах.
  • Потенциальное ускорение выполнения частых запросов на чтение.
• Минусы:
  • Увеличение избыточности данных (занимает больше места).
  • Повышенный риск аномалий (вставки, обновления, удаления).
  • Требуется больше усилий для поддержания целостности данных (например, с помощью триггеров или на уровне приложения).

Денормализацию следует применять осторожно, только после тщательного анализа производительности и понимания всех рисков. Обычно она является оправданной в системах с преобладанием операций чтения (например, в хранилищах данных для аналитики), где скорость выборки критически важна.

Глава 4

В предыдущих главах мы рассмотрели, как спроектировать и создать структуру базы данных с помощью ER-моделей, нормализации и DDL-команд SQL (CREATE TABLE, ALTER TABLE и т.д.). Теперь перейдем к самой частой задаче при работе с БД — извлечению данных с помощью DML-команды SELECT.

Часть 1: Структура Запроса SELECT

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

Логический порядок выполнения (как СУБД “думает” о запросе):

1. FROM: Определяет таблицы-источники данных. Если таблиц несколько, на этом этапе (логически) формируется их комбинация (например, декартово произведение или результат JOIN).
2. WHERE: Фильтрует строки, полученные на шаге FROM, оставляя только те, которые удовлетворяют заданному условию.
3. GROUP BY: Группирует строки, прошедшие фильтрацию WHERE, по одинаковым значениям в указанных столбцах. Все строки с одинаковыми значениями в этих столбцах “схлопываются” в одну строку группы.
4. Агрегатные функции: Вычисляются для каждой группы, созданной на шаге GROUP BY (или для всей таблицы, если GROUP BY отсутствует).
5. HAVING: Фильтрует группы, созданные на шаге GROUP BY, оставляя только те группы, которые удовлетворяют условию в HAVING (условие часто включает агрегатные функции).
6. SELECT: Выбирает столбцы (или вычисляет выражения), которые нужно вернуть в результате. Если использовался GROUP BY, здесь можно указывать только столбцы, по которым шла группировка, агрегатные функции или константы.
7. DISTINCT: (Если указано) Удаляет дублирующиеся строки из результирующего набора.
8. ORDER BY: Сортирует финальный результирующий набор строк по указанным столбцам.
9. LIMIT / OFFSET: (Нестандартные, но есть в PostgreSQL и многих других СУБД) Ограничивают количество возвращаемых строк и/или пропускают начальные строки.

Важно: Физический порядок выполнения СУБД может отличаться! Оптимизатор может переставлять операции (например, применять условия WHERE до полного формирования JOIN) для повышения эффективности, но логический результат должен оставаться таким же.

Пример полной структуры:

SELECT -- 6. Выбираем столбцы/выражения
    column1,
    AGG_FUNC(column2) AS aggregate_result
FROM -- 1. Указываем таблицу(ы)
    my_table
WHERE -- 2. Фильтруем строки
    column3 > 10
GROUP BY -- 3. Группируем строки
    column1
HAVING -- 5. Фильтруем группы
    AGG_FUNC(column2) > 100
ORDER BY -- 8. Сортируем результат
    aggregate_result DESC
LIMIT 10; -- 9. Ограничиваем вывод

Часть 2: Операторы и Предикаты в WHERE и HAVING

Условия в WHERE (фильтрация строк) и HAVING (фильтрация групп) строятся с использованием операторов и предикатов.

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

• AND: Логическое И.
• OR: Логическое ИЛИ.
• NOT: Логическое НЕ.

Работа с NULL: Вспомним таблицы истинности (из Лекции 5 / Начала Лекции 6):

• TRUE AND NULL -> NULL
• FALSE AND NULL -> FALSE
• TRUE OR NULL -> TRUE
• FALSE OR NULL -> NULL
• NOT NULL -> NULL
• NULL AND NULL -> NULL
• NULL OR NULL -> NULL

Помните, что в WHERE и HAVING условие, дающее NULL или FALSE, приводит к тому, что строка/группа не включается в результат.

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

• =, >, <, >=, <=, <> (или !=)
• При сравнении с NULL результат всегда NULL.

Предикаты сравнения:

• expression BETWEEN x AND y: Проверяет, находится ли значение expression в диапазоне [x, y] (включая границы). Эквивалентно expression >= x AND expression <= y.
• expression NOT BETWEEN x AND y: Обратная проверка.
• expression IS NULL: Проверяет, равно ли значение NULL. Правильный способ проверки на NULL!
• expression IS NOT NULL: Проверяет, не равно ли значение NULL.
• expression IS DISTINCT FROM value: Сравнивает expression и value. Равно FALSE, если оба равны или оба NULL. Равно TRUE в противном случае. Похоже на <>, но корректно работает с NULL.
• expression IS NOT DISTINCT FROM value: Сравнивает expression и value. Равно TRUE, если оба равны или оба NULL. Равно FALSE в противном случае. Похоже на =, но корректно работает с NULL.

Пример использования IS NULL:

-- Найти студентов без указанной группы
SELECT st_name FROM students WHERE gr_id IS NULL;

Поиск по шаблону:

• string LIKE pattern: Проверяет, соответствует ли строка string шаблону pattern.
  • %: Соответствует любой последовательности символов (включая пустую).
  • _: Соответствует ровно одному любому символу.
  • ESCAPE 'char': Позволяет экранировать символы % и _, если их нужно искать как обычные символы.
  • Пример: st_name LIKE 'Иван%' (имена, начинающиеся на “Иван”).
  • Пример: st_name LIKE '_ва%' (имена, где второй символ “в”).
• string ILIKE pattern: То же, что LIKE, но без учета регистра (I - Insensitive). Это расширение PostgreSQL.
• string SIMILAR TO pattern: Более мощный стандартный SQL-вариант для поиска по шаблону, использующий синтаксис, похожий на регулярные выражения SQL (отличается от POSIX). Редко используется на практике по сравнению с LIKE или POSIX regex.
• POSIX Регулярные выражения (PostgreSQL): Предоставляют наиболее мощные средства поиска по шаблонам.
  • string ~ pattern: Соответствует регулярному выражению POSIX (с учетом регистра).
  • string ~* pattern: Соответствует регулярному выражению POSIX (без учета регистра).
  • string !~ pattern: Не соответствует (с учетом регистра).
  • string !~* pattern: Не соответствует (без учета регистра).
  • Пример: st_name ~* '^иван' (имена, начинающиеся на “иван” без учета регистра).
  • Пример: email ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$' (проверка формата email).

Условные выражения CASE:

Позволяют выполнять ветвление внутри SQL-запроса. Аналог if/then/else или switch.

Синтаксис 1 (Простой):

CASE выражение
    WHEN значение1 THEN результат1
    WHEN значение2 THEN результат2
    ...
    [ ELSE результат_по_умолчанию ]
END

Сравнивает выражение с значение1, значение2 и т.д.

Синтаксис 2 (Общий):

CASE
    WHEN условие1 THEN результат1
    WHEN условие2 THEN результат2
    ...
    [ ELSE результат_по_умолчанию ]
END

Проверяет условие1, условие2 и т.д. по порядку и возвращает результат для первого истинного условия.

• Если ни одно условие не истинно и нет ELSE, возвращается NULL.
• CASE можно использовать везде, где допустимо выражение (в SELECT, WHERE, ORDER BY и т.д.).

Пример (из Лекции 5, слайд 14): Преобразование оценки из баллов в текстовое представление.

-- Исходные данные (предположим)
SELECT Result FROM EXAM;
 Result
------
     76
     95
     55

-- Запрос с CASE
SELECT Result,
    CASE
        WHEN Result >= 91 THEN 'Отл.'
        WHEN Result >= 75 THEN 'Хор.'
        WHEN Result >= 60 THEN 'Удовл.'
        ELSE 'Неуд.' -- Добавим вариант для < 60
    END AS Description
FROM EXAM;

Вывод:

 result | description
--------+-------------
     76 | Хор.
     95 | Отл.
     55 | Неуд.
(3 rows)

Преобразование типов:

Иногда нужно явно преобразовать значение из одного типа в другой.

• CAST ( expression AS type ): Стандартный SQL синтаксис.
• expression::type: Синтаксис PostgreSQL (более короткий и часто используемый).
• typename ( expression ): Исторический синтаксис через функцию (менее предпочтителен).

-- Преобразовать целое число 42 в число с плавающей точкой двойной точности
SELECT CAST(42 AS DOUBLE PRECISION);
SELECT 42::DOUBLE PRECISION;
SELECT float8(42); -- float8 это имя типа double precision в PostgreSQL

Вывод (для всех трех вариантов):

 float8
--------
     42
(1 row)

Преобразование возможно не всегда и зависит от совместимости типов.

Оператор IN (со списком значений):

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

expression IN (value1, value2, ...)

Эквивалентно: (expression = value1) OR (expression = value2) OR ...

-- Найти студентов из групп P3100 или P3101
SELECT st_name FROM students WHERE group_name IN ('P3100', 'P3101');

Часть 3: Запросы с использованием Нескольких Таблиц (Соединения - JOIN)

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

Декартово Произведение (Cartesian Product):

• Комбинация каждой строки одной таблицы с каждой строкой другой таблицы.
• Число строк в результате = (число строк в табл. 1) * (число строк в табл. 2).
• В SQL получается, если перечислить таблицы в FROM через запятую без условия соединения в WHERE или использовать CROSS JOIN.
• Обычно не является желаемым результатом, так как создает много “бессмысленных” комбинаций строк и очень ресурсоемко.

-- Таблица lt
 id | name
----+------
  1 | aaa
  2 | bbb
  3 | ccc

-- Таблица rt
 id | value
----+-------
  1 | xxx
  3 | yyy
  7 | zzz

-- Декартово произведение
SELECT * FROM lt, rt;
-- или
SELECT * FROM lt CROSS JOIN rt;

Вывод (Декартово произведение):

 id | name | id | value
----+------+----+-------
  1 | aaa  |  1 | xxx
  1 | aaa  |  3 | yyy
  1 | aaa  |  7 | zzz
  2 | bbb  |  1 | xxx
  2 | bbb  |  3 | yyy
  2 | bbb  |  7 | zzz
  3 | ccc  |  1 | xxx
  3 | ccc  |  3 | yyy
  3 | ccc  |  7 | zzz
(9 rows)

Соединение (JOIN):

Правильный способ комбинировать строки из связанных таблиц на основе условия соединения.

Старый стиль (через WHERE) - НЕ РЕКОМЕНДУЕТСЯ:

-- Найти студентов из России (пример из Лекции 4)
SELECT s.Name, s.City
FROM STUDENT s, CITIES c -- Таблицы через запятую
WHERE c.CityName = s.City -- Условие соединения
  AND c.Country = 'Россия'; -- Условие фильтрации

Этот стиль смешивает условия соединения и условия фильтрации в WHERE, что ухудшает читаемость и может приводить к ошибкам (если забыть условие соединения, получится декартово произведение).

Современный стиль (явный JOIN) - РЕКОМЕНДУЕТСЯ:

Используются ключевые слова JOIN, ON, USING, NATURAL JOIN. Условия соединения отделены от условий фильтрации (WHERE).

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

SELECT ...
FROM table1
[ join_type ] JOIN table2
  ON join_condition -- Условие для связи строк table1 и table2
[ WHERE filter_condition ]
...

Типы JOIN:

1. INNER JOIN (или просто JOIN):

   • Возвращает только те строки, для которых найдено соответствие в обеих таблицах согласно условию ON. Строки, не имеющие пары в другой таблице, в результат не попадают.
   • Это самый частый тип соединения.
   • Коммутативен (A JOIN B эквивалентно B JOIN A).
   • Ассоциативен ((A JOIN B) JOIN C эквивалентно A JOIN (B JOIN C)).

   Пример: Получить имена студентов и названия их групп.

   SELECT s.st_name, g.gr_name
   FROM students s
   INNER JOIN groups g ON s.gr_id = g.gr_id; -- Условие соединения по внешнему ключу
   

   Студенты, у которых gr_id равен NULL или ссылается на несуществующую группу, в результат не попадут.

2. LEFT OUTER JOIN (или LEFT JOIN):

   • Возвращает все строки из левой таблицы (table1).
   • Для каждой строки из левой таблицы ищется соответствие в правой таблице (table2) по условию ON.
   • Если соответствие найдено, возвращается комбинированная строка.
   • Если соответствие не найдено, все равно возвращается строка из левой таблицы, а столбцы из правой таблицы заполняются значениями NULL.
   • Не коммутативен! A LEFT JOIN B не эквивалентно B LEFT JOIN A.

   Пример: Получить всех студентов и названия их групп (даже если группа не указана).

   SELECT s.st_name, g.gr_name
   FROM students s
   LEFT JOIN groups g ON s.gr_id = g.gr_id;
   

   В результате будут все студенты. Если у студента gr_id равен NULL или ссылается на несуществующую группу, в колонке gr_name для него будет NULL.

3. RIGHT OUTER JOIN (или RIGHT JOIN):

   • Симметричен LEFT JOIN. Возвращает все строки из правой таблицы (table2).
   • Если для строки из правой таблицы не найдено соответствие в левой, столбцы из левой таблицы заполняются NULL.
   • Не коммутативен.

   Пример: Получить все группы и имена студентов в них (даже если в группе нет студентов).

   SELECT s.st_name, g.gr_name
   FROM students s
   RIGHT JOIN groups g ON s.gr_id = g.gr_id;
   

   В результате будут все группы. Если в группе нет студентов, в колонке st_name для такой группы будет NULL.

4. FULL OUTER JOIN (или FULL JOIN):

   • Комбинация LEFT JOIN и RIGHT JOIN. Возвращает все строки из обеих таблиц.
   • Если для строки из одной таблицы нет соответствия в другой, недостающие столбцы заполняются NULL.
   • Коммутативен.

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

   SELECT s.st_name, g.gr_name
   FROM students s
   FULL OUTER JOIN groups g ON s.gr_id = g.gr_id;
   

   Результат будет содержать: студентов с их группами; студентов без групп (с NULL в gr_name); группы без студентов (с NULL в st_name).

5. CROSS JOIN:

   • Возвращает декартово произведение двух таблиц. Условие ON не используется.
   • Используется редко, в основном для генерации комбинаций.

   Пример: Сгенерировать все возможные пары студент-группа.

   SELECT s.st_name, g.gr_name
   FROM students s
   CROSS JOIN groups g;
   

Способы указания условия соединения:

• ON join_condition: Самый гибкий способ, позволяет указать любое логическое условие для соединения строк. Используется чаще всего.

  FROM students s JOIN groups g ON s.gr_id = g.gr_id
  

• USING (column_list): Упрощенная запись для эквисоединения, когда столбцы, по которым идет соединение, имеют одинаковые имена в обеих таблицах. Столбцы из списка column_list включаются в результат только один раз.

  -- Предполагая, что в обеих таблицах есть колонка gr_id
  FROM students JOIN groups USING (gr_id)
  

  Эквивалентно ON students.gr_id = groups.gr_id, но в SELECT будет только одна колонка gr_id.

• NATURAL JOIN: Еще более короткая запись. Автоматически соединяет таблицы по всем столбцам с одинаковыми именами. Совпадающие столбцы включаются в результат только один раз.

  -- Предполагая, что соединение нужно делать только по gr_id, и других колонок с одинаковыми именами нет
  FROM students NATURAL JOIN groups
  

  Опасно! Используйте NATURAL JOIN с осторожностью, так как случайное совпадение имен колонок может привести к неверному условию соединения или к CROSS JOIN, если совпадающих имен нет.

Часть 4: Вложенные Подзапросы (Subqueries)

Подзапрос — это SELECT-запрос, вложенный внутрь другого SQL-запроса (SELECT, INSERT, UPDATE, DELETE).

Типы подзапросов:

1. Простые (Некоррелированные):

   • Внутренний запрос не зависит от внешнего.

   • Может быть выполнен один раз независимо от внешнего запроса.

   • Его результат (одно значение, список значений, таблица) подставляется во внешний запрос.

   • Пример: Найти студентов из городов России (как в Лекции 4, слайд 44).

     SELECT Surname
     FROM STUDENT
     WHERE CityName IN (
         SELECT City -- Этот подзапрос выполняется один раз
         FROM CITIES
         WHERE Country = 'Россия'
     );
     

     Сначала выполняется внутренний запрос, возвращает список городов ('Москва', 'Санкт-Петербург', ...). Затем внешний запрос ищет студентов, у которых CityName есть в этом списке.

2. Коррелированные:

   • Внутренний запрос зависит от данных из внешнего запроса (ссылается на его таблицы/колонки).

   • Внутренний запрос перевыполняется для каждой строки, обрабатываемой внешним запросом.

   • Часто бывают менее эффективны, чем некоррелированные запросы или соединения.

   • Пример: Найти студентов, участвовавших в олимпиадах (как в Лекции 4, слайд 48).

     SELECT Surname
     FROM STUDENT s -- Внешний запрос ссылается на таблицу STUDENT как 's'
     WHERE EXISTS ( -- Оператор для подзапроса
         SELECT 1
         FROM STUDENT_OLYMPIAD so
         WHERE so.StID = s.StudentID -- Внутренний запрос ссылается на s.StudentID из внешнего
     );
     

     Для каждой строки s из STUDENT выполняется внутренний SELECT. Если он находит хотя бы одну строку в STUDENT_OLYMPIAD с соответствующим StID, EXISTS возвращает TRUE, и фамилия студента s попадает в результат.

Сравнение Подзапросов и Соединений:

• Многие задачи можно решить и с помощью подзапросов, и с помощью соединений.
• Соединения (JOIN) часто более читаемы и лучше оптимизируются СУБД, особенно для эквисоединений.
• Коррелированные подзапросы часто можно переписать с использованием JOIN, что обычно повышает производительность.
• Подзапросы бывают удобны и необходимы в некоторых случаях, особенно с операторами IN, ANY, ALL, EXISTS, или когда нужно получить результат агрегации для использования во внешнем запросе.

Операторы для работы с Подзапросами:

• IN (подзапрос):
  • Проверяет, равно ли выражение значению хотя бы одной строки, возвращаемой подзапросом.
  • Подзапрос должен возвращать один столбец.
  • expression IN (SELECT column FROM ...) эквивалентно expression = ANY (SELECT column FROM ...)
• NOT IN (подзапрос):
  • Проверяет, что выражение не равно ни одному значению из подзапроса.
  • Осторожно с NULL! Если подзапрос возвращает хотя бы одно NULL-значение, NOT IN всегда вернет FALSE или NULL, но никогда TRUE, что часто приводит к неожиданным пустым результатам.
• EXISTS (подзапрос):
  • Возвращает TRUE, если подзапрос возвращает хотя бы одну строку, иначе FALSE.
  • Содержимое строк подзапроса не имеет значения (часто пишут SELECT 1 или SELECT *).
  • Часто используется с коррелированными подзапросами для проверки наличия связанных данных.
• ANY / SOME (подзапрос): (SOME — синоним ANY)
  • Сравнивает выражение с каждым значением, возвращаемым подзапросом (который должен вернуть один столбец), используя заданный оператор (=, >, <, и т.д.).
  • Возвращает TRUE, если сравнение истинно хотя бы для одного значения из подзапроса.
  • Пример: expression > ANY (SELECT column FROM ...) - истинно, если выражение больше хотя бы одного значения из подзапроса.
• ALL (подзапрос):
  • Сравнивает выражение с каждым значением, возвращаемым подзапросом (который должен вернуть один столбец), используя заданный оператор.
  • Возвращает TRUE, если сравнение истинно для всех значений из подзапроса (включая случай, когда подзапрос не вернул строк).
  • Пример: expression > ALL (SELECT column FROM ...) - истинно, если выражение больше всех значений из подзапроса (т.е. больше максимального).

Часть 5: Агрегатные Функции, Группировка и Фильтрация Групп

Часто нужно получить не сырые данные, а агрегированную информацию (итоги, средние значения, количество и т.д.).

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

Выполняют вычисления над набором строк и возвращают одно значение.

• COUNT(*): Количество строк в группе (или во всей таблице).
• COUNT(column): Количество не-NULL значений в указанном столбце в группе.
• COUNT(DISTINCT column): Количество уникальных не-NULL значений в столбце.
• SUM(column): Сумма значений в столбце (для числовых типов). Игнорирует NULL.
• AVG(column): Среднее значение в столбце (для числовых типов). Игнорирует NULL.
• MIN(column): Минимальное значение в столбце. Игнорирует NULL.
• MAX(column): Максимальное значение в столбце. Игнорирует NULL.
• И другие (STDDEV, VARIANCE и т.д.).

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

• Используется для разделения строк таблицы на группы на основе одинаковых значений в одной или нескольких колонках.
• Агрегатные функции затем применяются к каждой группе отдельно.
• Важное правило: Если используется GROUP BY, то в секции SELECT можно указывать только:
  1. Столбцы, перечисленные в GROUP BY.
  2. Агрегатные функции.
  3. Константы или выражения, не зависящие от конкретных строк внутри группы.

Пример: Посчитать количество студентов в каждой группе.

SELECT
    gr_id, -- Столбец из GROUP BY
    COUNT(*) AS student_count -- Агрегатная функция
FROM students
WHERE gr_id IS NOT NULL -- Исключим студентов без группы (опционально)
GROUP BY gr_id -- Группируем по ID группы
ORDER BY gr_id;

Вывод (примерный):

 gr_id | student_count
-------+---------------
    34 |             2
    37 |             1
(2 rows)

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

• Применяется после группировки и вычисления агрегатных функций.
• Фильтрует результаты группировки (целые группы), а не отдельные строки (как WHERE).
• В условии HAVING можно использовать агрегатные функции.

Пример: Найти группы, в которых более одного студента.

SELECT
    gr_id,
    COUNT(*) AS student_count
FROM students
WHERE gr_id IS NOT NULL
GROUP BY gr_id
HAVING COUNT(*) > 1 -- Фильтруем группы по результату агрегатной функции
ORDER BY gr_id;

Вывод (примерный):

 gr_id | student_count
-------+---------------
    34 |             2 -- Группа 37 отфильтрована, т.к. там 1 студент
(1 row)

Глава 5

В этой главе мы углубимся в команды для изменения данных (DML), рассмотрим мощный инструмент SQL — представления (Views), научимся создавать собственные функции и процедуры с использованием процедурного расширения PL/pgSQL, разберемся с триггерами для реализации сложной логики и автоматизации, а также подробно рассмотрим механизм транзакций для обеспечения целостности данных. Эта глава основана на материалах Лекции 5: “PL/pgSQL” и частично затрагивает DML из Лекции 4/5.

Часть 1: Манипулирование Данными (DML)

DML (Data Manipulation Language) — это часть SQL, отвечающая за добавление, изменение и удаление данных внутри таблиц.

1. INSERT: Добавляет одну или несколько новых строк в таблицу.

   • Синтаксис 1 (Указание столбцов):

     INSERT INTO имя_таблицы (столбец1, столбец2, ...)
     VALUES (значение1, значение2, ...);
     

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

   • Синтаксис 2 (Без указания столбцов):

     INSERT INTO имя_таблицы VALUES (значение1, значение2, ...);
     

     Значения должны быть перечислены в том же порядке, в каком столбцы определены в таблице. Менее надежный способ, т.к. зависит от порядка столбцов.

   • Синтаксис 3 (Вставка из SELECT):

     INSERT INTO имя_таблицы (столбец1, столбец2, ...)
     SELECT выражение1, выражение2, ...
     FROM другая_таблица
     WHERE условие;
     

     Позволяет вставить в таблицу результат другого запроса. Количество и типы столбцов в INSERT и SELECT должны совпадать.

   Пример (из слайдов):

   -- Пример 1 и 2: Добавление студента
   INSERT INTO student VALUES (123, 'Vasya', 345);
   INSERT INTO student (StudID, GroupID, Name) VALUES (123, 345, 'Vasya');
   
   -- Пример 3: Добавление студентов из временной таблицы по условию
   INSERT INTO student (StudID, Name, GroupID) -- Указываем порядок для INSERT
   SELECT ID, Name, 345 -- Указываем порядок в SELECT
   FROM tmp_person
   WHERE Exam > 70;
   

2. UPDATE: Изменяет существующие строки в таблице.

   • Синтаксис:

     UPDATE имя_таблицы
     SET столбец1 = новое_значение1,
         столбец2 = новое_значение2,
         ...
     [ WHERE условие ]; -- Если WHERE отсутствует, обновятся ВСЕ строки!
     

     Условие WHERE определяет, какие строки нужно обновить.

   Пример (из слайдов):

   -- Установить всем студентам GroupID = 578
   UPDATE student SET GroupID = 578;
   
   -- Установить GroupID = 34 для студента с именем 'Ivan'
   UPDATE student SET GroupID = 34 WHERE Name = 'Ivan';
   

3. DELETE: Удаляет существующие строки из таблицы.

   • Синтаксис:

     DELETE FROM имя_таблицы
     [ WHERE условие ]; -- Если WHERE отсутствует, удалятся ВСЕ строки!
     

     Условие WHERE определяет, какие строки нужно удалить.

   Пример (из слайдов):

   -- Удалить студентов с GroupID = 34
   DELETE FROM student WHERE GroupID = 34;
   
   -- Удалить ВСЕХ студентов
   DELETE FROM student;
   

4. TRUNCATE: Быстро удаляет все строки из таблицы (или нескольких таблиц).

   • Синтаксис:

     TRUNCATE [TABLE] имя_таблицы1 [, имя_таблицы2 ...];
     

   • Отличия от DELETE без WHERE:

     • Обычно намного быстрее для больших таблиц, так как не сканирует таблицу построчно и не записывает удаление каждой строки в лог транзакций так же подробно.
     • Не запускает триггеры DELETE на уровне строк (FOR EACH ROW).
     • Сбрасывает счетчики последовательностей (SERIAL и т.п.), связанных с таблицей (в PostgreSQL, если указано RESTART IDENTITY).
     • Не возвращает количество удаленных строк.

   • Используйте TRUNCATE, когда нужно быстро и полностью очистить таблицу.

   Пример (из слайдов):

   -- Быстро очистить таблицу student
   TRUNCATE TABLE student;
   
   -- Быстро очистить несколько таблиц
   TRUNCATE TABLE student, groups, exams;
   

Часть 2: Выполнение Скриптов

Часто SQL-код (особенно DDL для создания схемы или DML для наполнения данными) хранится в файлах (.sql). Такие файлы называются скриптами. Выполнить скрипт в PostgreSQL можно несколькими способами:

1. Через утилиту psql при запуске:

   psql -U имя_пользователя -d имя_базы -f путь/к/файлу/script.sql
   

   • -U: Имя пользователя PostgreSQL.
   • -d: Имя базы данных, к которой подключаемся.
   • -f: Путь к файлу скрипта.

2. Внутри интерактивной сессии psql: Используются мета-команды (начинаются с \):

   -- Подключаемся к базе
   psql -U имя_пользователя -d имя_базы
   
   -- Выполняем скрипт из файла
   \i путь/к/файлу/script.sql
   -- или эквивалентная команда
   \include путь/к/файлу/script.sql
   

   Это удобно для выполнения небольших скриптов или частей кода во время работы с БД.

Часть 3: Представления (Views)

Мы уже кратко упоминали виртуальные таблицы. Основной их вид — это представления.

• Представление (View) — это сохраненный в базе данных именованный SQL-запрос (SELECT).
• С представлением можно работать почти так же, как с обычной таблицей (выполнять SELECT, иногда INSERT, UPDATE, DELETE, если представление обновляемое).
• Представление не хранит данные само по себе (за исключением материализованных представлений). Каждый раз при обращении к представлению СУБД выполняет лежащий в его основе SELECT-запрос к базовым таблицам.

Зачем нужны представления?

1. Упрощение сложных запросов: Длинный и сложный запрос можно “спрятать” за простым именем представления.
2. Сокрытие структуры данных: Можно предоставить пользователям доступ только к представлению, которое показывает лишь необходимые им столбцы и строки, скрывая сложность или конфиденциальность базовых таблиц.
3. Логическая независимость данных: Если структура базовых таблиц меняется, можно изменить только определение представления, а запросы пользователей, обращающиеся к представлению, останутся прежними.
4. Обеспечение обратной совместимости: При рефакторинге схемы можно создать представления, имитирующие старую структуру.

Создание Представления (CREATE VIEW)

CREATE [ OR REPLACE ] VIEW имя_представления [ (имя_колонки1, имя_колонки2, ...) ]
AS
SELECT_запрос; -- Запрос, определяющий представление

• OR REPLACE: Если представление с таким именем уже существует, оно будет заменено.
• [ (имя_колонки1, ...) ]: Необязательный список имен для столбцов представления. Если опущен, используются имена столбцов из SELECT_запрос.

Пример (из слайдов): Создать представление, показывающее студентов факультета ПИКТ (предполагаем, что это группы, начинающиеся с ‘P3’).

-- Создаем представление
CREATE VIEW PICTStudents AS
SELECT * -- Выбираем все колонки из student
FROM student
WHERE GroupID IN ( -- Выбираем студентов, чья группа в списке групп ПИКТ
    SELECT GroupID
    FROM groups
    WHERE GroupName LIKE 'P3%' -- Находим группы, чье имя начинается с 'P3'
);

-- Используем представление как обычную таблицу
SELECT st_name, GroupID FROM PICTStudents WHERE st_id > 100;

Пример (с переименованием колонок):

CREATE VIEW PICTStudents2 (PICTId, pSurname) AS -- Задаем свои имена колонок
SELECT StudentID, Surname -- Выбираем нужные колонки из student
FROM student
WHERE GroupID IN (
    SELECT GroupID FROM groups WHERE GroupName LIKE 'P3%'
);

-- Используем представление с новыми именами
SELECT PICTId FROM PICTStudents2 WHERE pSurname LIKE 'Иван%';

Материализованные Представления (MATERIALIZED VIEW)

• В отличие от обычных представлений, материализованное представление хранит результат своего запроса физически в базе данных (как кэш).
• Обращение к материализованному представлению происходит очень быстро, так как не требует выполнения основного запроса каждый раз.
• Проблема: Данные в материализованном представлении могут устареть, если данные в базовых таблицах изменились.
• Обновление: Данные нужно обновлять вручную (или по расписанию) с помощью команды REFRESH MATERIALIZED VIEW.

Создание и Обновление:

-- Создаем материализованное представление
CREATE MATERIALIZED VIEW PICTStudents_MV (PICTId, pSurname) AS -- Добавляем MATERIALIZED
SELECT StudentID, Surname
FROM student
WHERE GroupID IN (
    SELECT GroupID FROM groups WHERE GroupName LIKE 'P3%'
);

-- Используем (быстро, читает сохраненные данные)
SELECT * FROM PICTStudents_MV;

-- Обновляем данные (выполняет основной SELECT и перезаписывает сохраненные данные)
REFRESH MATERIALIZED VIEW PICTStudents_MV;

Материализованные представления полезны для сложных, ресурсоемких запросов, результаты которых нужны часто, а актуальность данных с точностью до секунды не критична (например, для отчетов).

Часть 4: Введение в PL/pgSQL

SQL — декларативный язык. Иногда нам нужно выполнить последовательность действий, использовать циклы, условия, переменные — то есть написать процедурный код. Для этого в PostgreSQL (и других СУБД) существуют процедурные расширения языка.

• PL/pgSQL (Procedural Language / PostgreSQL SQL) — стандартный, блочно-структурированный процедурный язык для PostgreSQL. Его синтаксис во многом основан на языке Ada.

Зачем нужен PL/pgSQL?

• Создание пользовательских функций (UDF) и хранимых процедур.
• Реализация сложной бизнес-логики непосредственно в базе данных.
• Создание триггеров для автоматизации действий при изменении данных.
• Повышение производительности за счет уменьшения обмена данными между приложением и СУБД (логика выполняется на сервере БД).

Типы Пользовательских Функций:

В PostgreSQL можно создавать функции на разных языках:

1. SQL-функции: Тело функции состоит из одного или нескольких SQL-запросов. Выполняются быстро, но возможности ограничены самим SQL.
2. PL/pgSQL-функции: Тело функции написано на языке PL/pgSQL, позволяет использовать переменные, циклы, условия и т.д. Самый распространенный вариант для сложной логики.
3. Функции на других языках (C, Python, Perl, Tcl и др.): Требуют установки соответствующих расширений (CREATE EXTENSION plpython3u;). Позволяют использовать возможности и библиотеки этих языков внутри БД.

Часть 5: Пользовательские Функции и Процедуры

Создание Функции (CREATE FUNCTION)

CREATE [ OR REPLACE ] FUNCTION имя_функции ( [ [имя_арг1] тип_арг1, [имя_арг2] тип_арг2, ...] )
RETURNS тип_возвращаемого_значения -- Или RETURNS TABLE(...) для возврата таблицы, или VOID если ничего не возвращает
AS $$ -- Или AS '...' - тело функции в $$ или одинарных кавычках
    -- Тело функции (SQL или PL/pgSQL код)
$$ LANGUAGE язык; -- язык: sql, plpgsql, plpython3u и т.д.

• OR REPLACE: Заменяет существующую функцию с тем же именем и типами аргументов.
• Аргументы:
  • В старых версиях и в SQL-функциях часто используются позиционные параметры ($1, $2, …).
  • В PL/pgSQL и современных SQL-функциях можно (и рекомендуется) использовать именованные аргументы.
• RETURNS: Указывает тип возвращаемого значения. VOID означает, что функция ничего не возвращает (похоже на процедуру, но это все еще функция).
• AS $$ ... $$: Тело функции. Использование $$ (долларовое квотирование) предпочтительнее одинарных кавычек ('...'), так как позволяет легко использовать одинарные кавычки внутри тела функции без экранирования ().
• LANGUAGE: Язык, на котором написано тело функции.

Пример SQL-функции (позиционные параметры):

-- Функция для изменения группы студента
CREATE FUNCTION updateStudentGroup_v1(int, int) -- Аргументы: stud_id, group_id
RETURNS void -- Ничего не возвращает
AS '
    UPDATE student
    SET GroupID = $2 -- $2 - второй аргумент (group_id)
    WHERE StudID = $1; -- $1 - первый аргумент (stud_id)
' LANGUAGE sql;

-- Вызов функции
SELECT updateStudentGroup_v1(101, 2); -- Обновить группу для студента 101 на группу 2

Пример SQL-функции (именованные параметры, PostgreSQL 9.2+):

CREATE FUNCTION updateStudentGroup_v2(
    p_stud_id INT, -- Имя аргумента p_stud_id, тип INT
    p_group_id INT -- Имя аргумента p_group_id, тип INT
)
RETURNS void
AS $$
    UPDATE student
    SET GroupID = p_group_id -- Используем имя параметра
    WHERE StudID = p_stud_id; -- Используем имя параметра
$$ LANGUAGE sql;

-- Вызов функции (можно по именам или по позиции)
SELECT updateStudentGroup_v2(p_stud_id := 102, p_group_id := 1);
SELECT updateStudentGroup_v2(103, 1);

Процедуры (CREATE PROCEDURE, PostgreSQL 11+)

Процедуры похожи на функции, возвращающие VOID, но имеют ключевое отличие: внутри процедур можно управлять транзакциями (COMMIT, ROLLBACK), а внутри функций — нельзя.

CREATE [ OR REPLACE ] PROCEDURE имя_процедуры ( [ [имя_арг1] тип_арг1, ...] )
AS $$
    -- Тело процедуры (обычно PL/pgSQL)
$$ LANGUAGE язык;

-- Вызов процедуры
CALL имя_процедуры(значение1, ...);

Пример:

CREATE PROCEDURE transfer_money(sender_acc INT, receiver_acc INT, amount NUMERIC)
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE acc_id = sender_acc;
    UPDATE accounts SET balance = balance + amount WHERE acc_id = receiver_acc;
    -- Здесь можно было бы добавить COMMIT или ROLLBACK, если бы это не было частью бОльшей транзакции
END;
$$ LANGUAGE plpgsql;

-- Вызов
CALL transfer_money(1, 2, 100.00);

Часть 6: Основы PL/pgSQL

PL/pgSQL — блочно-структурированный язык. Основной элемент — блок кода.

Структура блока:

[ <<метка_блока>> ] -- Необязательная метка
[ DECLARE
    -- Объявление переменных
    имя_переменной тип_данных [ := начальное_значение ];
    ...
]
BEGIN
    -- Исполняемые операторы (SQL-запросы, присваивания, циклы, условия и т.д.)
    ...
    [ RETURN значение; ] -- Только для функций, возвращающих значение
END [ метка_блока ]; -- Метка конца блока (необязательно)

• Секция DECLARE необязательна, если переменные не нужны.
• BEGIN/END обязательны.
• Блоки могут быть вложенными.
• Метки используются для разрешения имен переменных во вложенных блоках (обращение к переменной внешнего блока: метка_блока.имя_переменной).
• Присваивание значения: переменная := выражение;.
• Выполнение SQL-запросов: Просто пишете SQL-запрос. Чтобы сохранить результат SELECT в переменную, используется SELECT ... INTO переменная ....
• Вывод отладочной информации: RAISE NOTICE 'Сообщение: %', переменная; (% - место для подстановки значения переменной).

Пример блока (из слайда 33):

CREATE FUNCTION somefunc() RETURNS integer AS $$ -- SQL command
<< outerblock >> -- Метка внешнего блока (pl/pgSQL)
DECLARE
    quantity integer := 30; -- Переменная внешнего блока
BEGIN
    -- Создаем вложенный блок
    DECLARE
        quantity integer := 80; -- Переменная внутреннего блока (то же имя!)
    BEGIN
        RAISE NOTICE 'Inner quantity = %', quantity; -- Выведет 80 (переменная внутреннего блока)
        RAISE NOTICE 'Outer quantity = %', outerblock.quantity; -- Выведет 30 (обращение к переменной внешнего блока по метке)
    END; -- Конец вложенного блока

    RAISE NOTICE 'Сейчас quantity = %', quantity; -- Выведет 30 (переменная внешнего блока, т.к. вышли из вложенного)
    RETURN quantity; -- Вернет 30
END;
$$ LANGUAGE plpgsql;

-- Вызов функции
SELECT somefunc();

Вывод в NOTICE при вызове:

NOTICE:  Inner quantity = 80
NOTICE:  Outer quantity = 30
NOTICE:  Сейчас quantity = 30

Результат SELECT:

 somefunc
----------
       30
(1 row)

Анонимные блоки (DO)

Позволяют выполнить блок PL/pgSQL кода без создания функции или процедуры. Удобно для одноразовых задач или скриптов.

DO $$
[ DECLARE ...]
BEGIN
    -- PL/pgSQL код
END;
$$ LANGUAGE plpgsql; -- Язык можно опустить, если используется plpgsql по умолчанию

Пример (из слайда 34): Посчитать и вывести количество студентов.

DO $$
<<studentBlock>>
DECLARE
    studCount integer := 0;
BEGIN
    SELECT COUNT(*)
    INTO studCount -- Сохраняем результат COUNT(*) в переменную studCount
    FROM student;

    RAISE NOTICE 'Students: %', studCount; -- Выводим результат
END studentBlock $$;

Вывод в NOTICE: (Зависит от количества студентов в таблице)

NOTICE:  Students: 15
DO

Часть 7: Триггеры

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

Зачем нужны триггеры?

1. Реализация сложных ограничений целостности: Правила, которые сложно или невозможно выразить стандартными CHECK, FOREIGN KEY (например, проверка баланса перед списанием, сложные зависимости между таблицами).
2. Аудит изменений: Автоматическая запись информации о том, кто, когда и какие данные изменил, в отдельную таблицу логов.
3. Автоматическое обновление связанных данных: Например, пересчет итоговых сумм при изменении деталей заказа.
4. Репликация данных (в некоторых случаях).

Как работает триггер?

1. Событие: Происходит операция DML (INSERT, UPDATE, DELETE) или DDL (для событийных триггеров) с таблицей, для которой определен триггер.
2. Срабатывание: СУБД проверяет, есть ли триггеры, связанные с этим событием и таблицей.
3. Выполнение: Если триггер найден, выполняется связанная с ним триггерная функция.

Создание триггера в PostgreSQL:

Процесс состоит из двух шагов:

1. Создание триггерной функции: Это обычная функция PL/pgSQL (или на другом языке), но со специфическими особенностями:

   • Она не принимает аргументов.
   • Она должна возвращать специальный тип TRIGGER (для DML триггеров) или EVENT_TRIGGER (для DDL триггеров).
   • Внутри функции доступны специальные переменные (NEW, OLD, TG_OP, TG_WHEN и т.д.), содержащие информацию о событии и изменяемых данных.
   • Возвращаемое значение функции имеет значение (особенно для BEFORE ROW триггеров):
     • Возврат NEW (или измененной строки NEW): Операция продолжается с этой (возможно измененной) строкой.
     • Возврат OLD (для UPDATE/DELETE): Операция продолжается со старой строкой (редко используется).
     • Возврат NULL: Операция для данной строки отменяется, последующие триггеры для этой строки не срабатывают. Позволяет “запретить” изменение.
     • Для AFTER триггеров возвращаемое значение игнорируется (операция уже произошла), но рекомендуется возвращать NULL или ту же запись (NEW или OLD).

2. Создание самого триггера: Связывает триггерную функцию с конкретной таблицей и событием.

   Синтаксис CREATE TRIGGER:

   CREATE TRIGGER имя_триггера
       { BEFORE | AFTER | INSTEAD OF } -- Когда срабатывать
       { event [ OR ... ] } -- На какое событие(я) (INSERT, UPDATE, DELETE, TRUNCATE)
       ON имя_таблицы
       [ FOR [ EACH ] { ROW | STATEMENT } ] -- Уровень срабатывания
       [ WHEN ( условие ) ] -- Дополнительное условие срабатывания
       EXECUTE PROCEDURE имя_триггерной_функции(); -- Какую функцию вызвать
   

   • BEFORE | AFTER | INSTEAD OF:
     • BEFORE: Функция выполняется перед выполнением операции DML и перед проверкой ограничений. Позволяет изменить данные (NEW) или отменить операцию (вернув NULL).
     • AFTER: Функция выполняется после выполнения операции DML и проверки ограничений. Не может изменить данные (операция уже прошла) или отменить ее. Используется для аудита, обновления связанных данных.
     • INSTEAD OF: Специальный тип для представлений (Views). Функция выполняется вместо операции DML над представлением, позволяя реализовать логику обновления базовых таблиц.
   • event: INSERT, UPDATE [ OF column1, ... ], DELETE, TRUNCATE. Можно указать несколько через OR. UPDATE OF срабатывает только при изменении указанных колонок.
   • FOR EACH ROW | STATEMENT:
     • ROW: Функция выполняется для каждой строки, затронутой операцией DML. Внутри доступны переменные NEW (для INSERT/UPDATE) и OLD (для UPDATE/DELETE).
     • STATEMENT (По умолчанию): Функция выполняется один раз на всю операцию DML, независимо от количества затронутых строк. Переменные NEW и OLD недоступны.
   • WHEN (условие): Дополнительное условие (использующее значения NEW/OLD), которое проверяется перед вызовом функции (только для ROW триггеров). Если условие ложно, функция не вызывается.

Пример: Аудит таблицы EMPLOYEE (из слайдов)

Задача: При добавлении нового сотрудника в таблицу EMPLOYEE автоматически записывать ID сотрудника и время добавления в таблицу AUDIT.

1. Таблицы:

CREATE TABLE employee (
    id     INT PRIMARY KEY,
    name   TEXT NOT NULL,
    addr   CHAR(50),
    salary REAL
);

CREATE TABLE audit (
    emp_id     INT NOT NULL,
    entry_date TEXT NOT NULL -- Лучше использовать TIMESTAMPTZ
);

2. Триггерная функция:

CREATE OR REPLACE FUNCTION auditfunc()
RETURNS TRIGGER AS $$
BEGIN
    -- Вставляем запись в таблицу аудита
    -- NEW.id - это ID из строки, которая сейчас вставляется в employee
    -- current_timestamp - встроенная функция PostgreSQL, возвращает текущее время
    INSERT INTO audit(emp_id, entry_date) VALUES (NEW.id, current_timestamp);

    -- Возвращаем NEW, чтобы операция INSERT продолжилась успешно
    -- Для AFTER триггера возвращаемое значение игнорируется, но хорошей практикой
    -- является возврат соответствующей строки или NULL.
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. Создание триггера:

CREATE TRIGGER employee_audit_insert
    AFTER INSERT ON employee -- Срабатывать ПОСЛЕ вставки
    FOR EACH ROW -- Для каждой вставляемой строки
    EXECUTE PROCEDURE auditfunc(); -- Вызвать нашу функцию

Проверка:

INSERT INTO employee (id, name, salary) VALUES (1, 'Иван', 1000);
-- После этой команды в таблице audit появится запись: (1, <текущее время>)

INSERT INTO employee (id, name, salary) VALUES (2, 'Петр', 1500);
-- После этой команды в таблице audit появится вторая запись: (2, <текущее время>)

Событийные триггеры (Event Triggers):

• Срабатывают на DDL-команды (CREATE TABLE, ALTER TABLE, DROP TABLE и т.д.).
• Используются для аудита изменений схемы, запрета определенных DDL-операций и т.п.
• Триггерная функция должна возвращать EVENT_TRIGGER.
• Доступны специальные переменные (например, tg_event, tg_tag).

Пример (из слайдов): Логгирование DDL команд.

-- Функция
CREATE OR REPLACE FUNCTION eventtest()
RETURNS event_trigger AS $$
BEGIN
    RAISE NOTICE 'DDL Event: %, Command Tag: %', tg_event, tg_tag; -- tg_tag содержит текст команды
END;
$$ LANGUAGE plpgsql;

-- Триггер
CREATE EVENT TRIGGER eventtest_trigger
    ON ddl_command_start -- Срабатывать перед началом любой DDL команды
    EXECUTE PROCEDURE eventtest();

-- Проверка
CREATE TABLE some_table (id int);

Вывод в NOTICE:

NOTICE:  DDL Event: ddl_command_start, Command Tag: CREATE TABLE
CREATE TABLE

Удаление триггера:

DROP TRIGGER имя_триггера ON имя_таблицы;

Часть 8: Транзакции

Транзакция — это логическая единица работы, состоящая из одной или нескольких операций SQL, которая должна быть выполнена атомарно.

Свойства ACID:

Транзакции в реляционных СУБД обычно гарантируют свойства ACID:

1. Atomicity (Атомарность): Либо все операции внутри транзакции успешно выполняются и фиксируются, либо ни одна из них не оказывает влияния на базу данных (все изменения отменяются). “Все или ничего”.
2. Consistency (Согласованность): Транзакция переводит базу данных из одного согласованного (целостного) состояния в другое согласованное состояние. Во время выполнения транзакции целостность может временно нарушаться, но к моменту фиксации она должна быть восстановлена.
3. Isolation (Изолированность): Параллельно выполняющиеся транзакции не должны мешать друг другу. Каждая транзакция должна выполняться так, как будто других транзакций в системе нет. (На практике существуют разные уровни изоляции, которые допускают те или иные аномалии для повышения производительности).
4. Durability (Долговечность/Устойчивость): Если транзакция успешно завершена (зафиксирована), ее результаты должны быть сохранены постоянно и не должны быть потеряны даже в случае сбоев системы (например, отключения питания). Обычно достигается за счет записи изменений в журналы транзакций (WAL - Write-Ahead Log) перед применением их к основным файлам данных.

Управление транзакциями в SQL:

• BEGIN или START TRANSACTION: Начинает новую транзакцию. В PostgreSQL многие команды DDL (как CREATE TABLE) не могут выполняться внутри явного блока BEGIN...COMMIT, они сами по себе являются транзакциями. DML команды (INSERT, UPDATE, DELETE) могут быть сгруппированы. Если BEGIN не вызван явно, каждая отдельная DML команда часто выполняется в своей собственной неявной транзакции (режим autocommit, зависит от настроек клиента/СУБД).
• COMMIT: Успешно завершает текущую транзакцию, делая все ее изменения видимыми для других транзакций и постоянными.
• ROLLBACK: Отменяет все изменения, сделанные в текущей транзакции с момента ее начала (или с последней точки сохранения), и завершает транзакцию.

Пример (Банковский перевод):

BEGIN; -- Начать транзакцию

-- Снять деньги со счета Алекса
UPDATE account SET balance = balance - 50.00 WHERE name = 'Alex';

-- Добавить деньги на счет Ивана
UPDATE account SET balance = balance + 50.00 WHERE name = 'Ivan';

-- Если обе операции прошли успешно:
COMMIT; -- Зафиксировать изменения

-- Если на каком-то этапе произошла ошибка (например, недостаточно средств),
-- нужно выполнить ROLLBACK вместо COMMIT:
-- ROLLBACK; -- Отменить все изменения с момента BEGIN

Точки сохранения (SAVEPOINT):

Позволяют установить “закладку” внутри транзакции, к которой можно будет откатиться, не отменяя всю транзакцию.

• SAVEPOINT имя_точки;: Устанавливает точку сохранения.
• ROLLBACK TO SAVEPOINT имя_точки;: Отменяет все изменения, сделанные после указанной точки сохранения. Сама точка сохранения остается активной.
• RELEASE SAVEPOINT имя_точки;: Удаляет точку сохранения, но не отменяет изменения, сделанные после нее.

Пример с SAVEPOINT:

BEGIN;

UPDATE account SET balance = balance - 50.00 WHERE name = 'Alex';

SAVEPOINT savepoint1; -- Установить точку сохранения

UPDATE account SET balance = balance + 50.00 WHERE name = 'Ivan';

-- Предположим, здесь возникла проблема или нужно отменить перевод Ивану
ROLLBACK TO SAVEPOINT savepoint1; -- Откат к состоянию после списания у Алекса

-- Теперь можно попробовать перевести деньги кому-то другому
UPDATE account SET balance = balance + 50.00 WHERE name = 'Ivan2';

COMMIT; -- Фиксируем результат (списание у Алекса и зачисление Ivan2)

При откате к точке сохранения (ROLLBACK TO), все точки, созданные после* нее, автоматически удаляются.*

Глава 6: Индексы и Выполнение Запросов

В предыдущих главах мы научились создавать таблицы, наполнять их данными и извлекать эти данные с помощью SQL-запросов. Теперь разберемся, как СУБД выполняет эти запросы “под капотом” и как можно ускорить их выполнение с помощью индексов. Эта глава основана на материалах Лекций 6: “Индексы. Выполнение запросов” и Лекции 7: “Выполнение запросов”.

Часть 1: Повышение Производительности Запросов

Когда данных в таблицах становится много (тысячи, миллионы строк), даже простые запросы могут выполняться долго. Почему? Потому что в самом простом случае СУБД приходится последовательно просматривать всю таблицу, чтобы найти нужные строки (это называется полное сканирование таблицы или Sequential Scan).

Способы повышения производительности:

1. Грамотное проектирование модели данных: Это основа. Если модель данных изначально плохо спроектирована (например, ненормализована, что приводит к избыточности, или наоборот, излишне нормализована, что требует множества сложных соединений для простых задач), то оптимизировать запросы будет очень сложно.
2. Использование индексов: Создание дополнительных структур данных, которые позволяют СУБД быстро находить нужные строки по значениям в определенных столбцах, не просматривая всю таблицу. (Основная тема этой главы).
3. Оптимизация запросов: Переписывание SQL-запросов так, чтобы они были более понятны оптимизатору СУБД и могли быть выполнены более эффективно (например, избегая ненужных соединений или используя EXISTS вместо IN в некоторых случаях).
4. Настройка физических параметров СУБД: Оптимизация конфигурации сервера PostgreSQL (выделение памяти, настройка параллелизма, параметры ввода-вывода и т.д.).

Мы сосредоточимся на индексах и понимании выполнения запросов.

Часть 2: Индексы

Что такое Индекс?

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

Индекс SQL работает похожим образом:

• Это отдельная структура данных (обычно хранящаяся в отдельном файле), связанная с таблицей.
• Он содержит копии значений из одного или нескольких столбцов таблицы.
• Эти значения в индексе упорядочены определенным образом (например, по алфавиту для строк, по возрастанию для чисел, или организованы в специфическую структуру данных типа хеш-таблицы).
• Каждое значение в индексе имеет указатель (ссылку, адрес, TID - Tuple Identifier) на те строки в основной таблице, где это значение встречается.

Как индекс помогает?

Рассмотрим таблицу STUDENT:

StudID	GroupID	Name
14	51	Ivan Petrov
23	53	Vladimir Ivanov
18	54	Eugene Serov
13	55	Gennady Surikov
25	56	Nikolay Petrov
25	58	Svetlana Ivanova
25	59	Antony Fedorov
18	63	Petr Menchikov
27	67	Gennady Klokov
23	69	Serge Vidov
18	72	Roman Klever
13	73	Pavel Borisov

И запрос:

SELECT * FROM STUDENT WHERE StudID = 18;

• Без индекса: СУБД читает каждую строку таблицы STUDENT и сравнивает значение в колонке StudID с числом 18. Если таблица большая (миллионы строк), это займет много времени, так как СУБД должна прочитать все данные с диска в память и проверить каждую строку.
• С индексом по StudID:
  1. СУБД обращается к структуре индекса по StudID.
  2. Если индекс, например, B-дерево (значения отсортированы), СУБД быстро находит значение 18 (например, используя бинарный поиск или навигацию по дереву).
  3. Из индекса СУБД получает указатели (TID) на строки в основной таблице, где StudID = 18.
  4. СУБД читает только эти конкретные строки из основной таблицы, используя полученные указатели. Это значительно быстрее, чем читать всю таблицу.

Пример (слайды 10-17, упрощенная иллюстрация):

Допустим, у нас есть упорядоченный список значений StudID (это и есть наш “игрушечный” индекс) и стрелки, указывающие на строки в основной таблице.

Индекс (упорядоченный StudID)	Таблица STUDENT (StudID, GroupID, Name)
13 →	14, 51, Ivan Petrov
13 →	23, 53, Vladimir Ivanov
14 →	18, 54, Eugene Serov <– Нужная строка
18 →	13, 55, Gennady Surikov
18 →	25, 56, Nikolay Petrov
18 →	25, 58, Svetlana Ivanova
23 →	25, 59, Antony Fedorov
23 →	18, 63, Petr Menchikov <– Нужная строка
25 →	27, 67, Gennady Klokov
25 →	23, 69, Serge Vidov
25 →	18, 72, Roman Klever <– Нужная строка
27 →	13, 73, Pavel Borisov

1. Запрос: SELECT * FROM STUDENT WHERE StudID = 18;
2. СУБД смотрит в индекс.
3. Поскольку индекс отсортирован (в нашем примере это простой список, но представьте более сложную структуру), СУБД быстро находит первое вхождение 18.
4. Берет указатель и читает строку 18, 54, Eugene Serov.
5. Переходит к следующему значению 18 в индексе.
6. Берет указатель и читает строку 18, 63, Petr Menchikov.
7. Переходит к следующему значению 18 в индексе.
8. Берет указатель и читает строку 18, 72, Roman Klever.
9. Следующее значение в индексе уже 23 (или больше 18). Поиск завершен.

Вместо сканирования всех 12 строк таблицы, мы быстро нашли 3 нужные строки через индекс.

Важные моменты:

• Индексы работают неявно: Вам не нужно указывать в SELECT-запросе, какой индекс использовать. Оптимизатор СУБД сам решает, использовать ли индекс и какой именно, на основе статистики данных и стоимости выполнения разных планов.
• Иногда СУБД может не использовать индекс, даже если он есть. Например, если запрос выбирает очень большую часть таблицы (проще прочитать всю таблицу), или если условия в WHERE таковы, что индекс неприменим (например, функция над индексированным столбцом WHERE upper(StudName) = 'ИВАНОВ').

Когда Индексы Полезны?

Индексы ускоряют операции, включающие:

• Поиск строк по условиям (WHERE): Особенно для условий равенства (=), диапазонных запросов (>, <, BETWEEN) по индексированному столбцу.
• Соединения таблиц (JOIN): Индексы по столбцам, участвующим в условии ON, значительно ускоряют поиск совпадающих строк в соединяемых таблицах. Первичные и уникальные ключи автоматически индексируются в PostgreSQL, что ускоряет соединения по ним.
• Поиск минимального/максимального значения (MIN(), MAX()): Если столбец индексирован (например, B-деревом), СУБД может просто взять первое/последнее значение из индекса, не сканируя таблицу.
• Сортировку (ORDER BY): Если порядок сортировки совпадает с порядком индекса (или обратен ему, и индекс это поддерживает), СУБД может избежать фактической сортировки данных, просто прочитав строки в порядке индекса.
• Группировку (GROUP BY): Индексы могут помочь СУБД быстрее найти строки, относящиеся к одной группе.

Недостатки Индексов:

1. Занимают место на диске и в памяти: Индекс — это дополнительная структура данных. Чем больше индексов, тем больше места они занимают.
2. Замедляют операции записи (INSERT, UPDATE, DELETE): При изменении данных в индексированном столбце или добавлении/удалении строки СУБД должна обновить не только саму таблицу, но и все связанные с ней индексы. Это добавляет накладные расходы. Чем больше индексов на таблице, тем медленнее будут операции записи.
   • Пример (слайд 23-24): DELETE FROM STUDENT WHERE GroupID = 54; Если по GroupID есть индекс, то СУБД должна не только удалить строку из таблицы STUDENT, но и удалить соответствующие записи из индекса по GroupID, а также, возможно, из индекса по StudID (если StudID был ключом этого индекса и строка удаляется).
3. Неэффективны на маленьких таблицах: Если таблица настолько мала, что целиком помещается в несколько блоков памяти, СУБД прочитает ее полностью быстрее, чем будет обращаться к индексу.
4. Неэффективны при выборке больших объемов данных: Если запрос выбирает значительную часть таблицы (например, WHERE column > 0, где почти все значения больше нуля), СУБД, скорее всего, решит, что проще прочитать всю таблицу последовательно (Seq Scan), чем много раз обращаться к индексу и затем к разным частям таблицы за строками (Index Scan + много случайных чтений с диска, что медленнее последовательного).

Стратегии Применения Индексов:

• Анализируйте запросы: Какие столбцы часто используются в WHERE и JOIN? Какие операции преобладают — чтение или запись?
• Индексируйте столбцы в WHERE: Особенно те, по которым идет поиск на равенство или по диапазону и которые обладают хорошей селективностью (т.е. по значению в этом столбце можно отобрать небольшую долю строк таблицы). Индексировать столбец “пол” (М/Ж), где всего два значения, обычно бессмысленно, так как каждое значение выбирает примерно половину таблицы.
• Индексируйте столбцы внешних ключей: Это критически важно для ускорения JOIN. PostgreSQL не создает их автоматически для внешних ключей (в отличие от первичных/уникальных), поэтому это нужно делать вручную.
• Индексируйте столбцы в ORDER BY и GROUP BY: Если часто нужна сортировка или группировка по определенному столбцу.
• Используйте составные индексы: Если часто ищете по комбинации столбцов (WHERE col1 = ? AND col2 = ?), создайте индекс по (col1, col2). Порядок столбцов в составном индексе важен! Индекс по (col1, col2) может использоваться для запросов по col1 и для запросов по col1 И col2, но не для запросов только по col2.
• Не создавайте лишних индексов: Каждый индекс замедляет запись. Регулярно проверяйте и удаляйте неиспользуемые индексы.
• Первичные и уникальные ключи индексируются автоматически в PostgreSQL (с помощью B-Tree индекса).

Создание Индексов (CREATE INDEX)

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ IF NOT EXISTS ] имя_индекса
ON имя_таблицы [ USING метод ]
( { имя_колонки | ( выражение ) } [ COLLATE имя_сортировки ] [ класс_операторов ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( параметр_хранения = значение [, ... ] ) ]
[ TABLESPACE имя_табличного_пространства ]
[ WHERE предикат ]; -- Частичный индекс

Полный синтаксис сложнее, здесь приведены основные части.

• UNIQUE: Создает уникальный индекс.
• CONCURRENTLY: (PostgreSQL) Позволяет создать индекс без длительной блокировки операций записи в таблицу.
• IF NOT EXISTS: Не выдавать ошибку, если индекс с таким именем уже существует.
• имя_индекса: Имя для вашего индекса.
• имя_таблицы: Таблица, для которой создается индекс.
• USING метод: Указывает тип индекса (btree, hash, gist, gin и др.). По умолчанию в PostgreSQL это btree.
• ( имя_колонки | ( выражение ) ... ): Список столбцов или выражений, по которым строится индекс.
• ASC | DESC: Порядок сортировки (для B-Tree).
• NULLS FIRST | NULLS LAST: Где размещать NULL-значения.
• WHERE предикат: Создает частичный индекс. Индексируются только строки, удовлетворяющие предикату. Полезно, если часто ищутся строки с определенным значением в одном столбце, а по другому нужно индексировать только подмножество.

Примеры:

-- Простой B-Tree индекс по одной колонке
CREATE INDEX idx_student_groupid ON student (GroupID);

-- Составной B-Tree индекс по двум колонкам
CREATE INDEX idx_student_surname_name ON student (Surname, Name);

-- Индекс по выражению (по фамилии в нижнем регистре)
CREATE INDEX idx_student_surname_lower ON student (lower(Surname));

-- Хеш-индекс (только для оператора равенства '=')
CREATE INDEX idx_student_name_hash ON student USING hash (Name);

-- Пример из слайда 29 (B-Tree по двум колонкам)
CREATE INDEX idx_example ON table_name USING btree (column1, column2);

Часть 3: Типы Индексов

PostgreSQL поддерживает несколько типов индексов, оптимизированных для разных задач.

1. B-дерево (B-Tree):
   • Используется по умолчанию (USING btree).
   • Структура: Сбалансированное, многоуровневое дерево.
     • Листовые узлы: Содержат индексируемые значения (ключи) и указатели (TID - Tuple Identifier, адрес строки на диске) на строки таблицы. Ключи в листовых узлах отсортированы. Листовые узлы связаны между собой для быстрого последовательного прохода (полезно для диапазонных запросов).
     • Внутренние узлы (включая корневой): Содержат “разделители” (ключи) и ссылки на дочерние узлы. Помогают быстро сузить область поиска.
   • Преимущества: Очень универсальный. Эффективен для:
     • Поиска по равенству (=).
     • Диапазонных запросов (>, <, BETWEEN, LIKE 'abc%' (поиск по префиксу)).
     • Сортировки (ORDER BY).
     • Поиска NULL (IS NULL, IS NOT NULL).
     • Работы с MIN() и MAX().
   • Пример поиска (StudID = 85, слайды 36-41):
     1. Запрос: SELECT * FROM STUDENT WHERE StudID = 85;
     2. Начинаем с корневого узла (например, [3, 30, 63]).
     3. 85 > 63, идем по правой ветке к узлу (например, [63, 76, 88]).
     4. 76 < 85 <= 88, идем по ветке, соответствующей диапазону (88), к листовому узлу (например, содержащему ключи [76(tid), 85(tid)]).
     5. В листовом узле находим ключ 85 и соответствующий tid.
     6. По tid читаем строку из таблицы.
   • Пример диапазонного поиска (StudID >= 85, слайды 42-43):
     1. Аналогично находим первый ключ 85.
     2. Далее, благодаря связям между листовыми узлами, последовательно читаем следующие листовые узлы ([88(tid), 95(tid)] и т.д.), пока условие выполняется.
2. Хеш-индекс (Hash):
   • Структура: Основан на хеш-таблице. Для индексируемого значения вычисляется хеш-код, который используется как индекс в массиве “корзин” (buckets). Каждая корзина содержит указатели на строки таблицы, чьи индексируемые значения дали этот хеш-код.
   • Пример (слайды 46-49):
     • Значения: (23, 2), (34, 2), (34543, 4), (234, 4), (2355, 5)
     • Хеш-функция Hash_Function(val1, val2) дает, например:
       • Hash_Function(23, 2) -> ...2345 (хэш значение)
       • Hash_Function(34, 2) -> ...3423
       • Hash_Function(34543, 4) -> ...2345 (коллизия с первым!)
     • Хэш-индекс:
       • Корзина 2345: tid1 (для (23,2)), tid3 (для (34543,4))
       • Корзина 3423: tid2 (для (34,2))
     • При поиске, например, WHERE StudID=23 AND GroupID=2:
       1. Вычисляется Hash_Function(23, 2) -> ...2345.
       2. Смотрим в корзину 2345.
       3. Для каждой строки (tid1, tid3) в этой корзине проверяем точное совпадение исходных значений StudID и GroupID (т.к. в корзине могут быть коллизии).
   • Преимущества: Очень быстрый для операций поиска по точному равенству (=).
   • Недостатки:
     • Бесполезен для диапазонных запросов, сортировки, поиска по префиксу.
     • Менее эффективен при большом количестве коллизий (когда много разных значений попадают в одну корзину).
   • Когда использовать: Только если запросы к столбцу — это исключительно проверки на точное равенство (WHERE attr = 1), и другие операции не нужны. B-Tree обычно является более гибким выбором.
3. GiST (Generalized Search Tree - Обобщенное дерево поиска):
   • Гибкая инфраструктура для создания индексов для сложных типов данных (геометрические данные, диапазоны, некоторые виды текстового поиска).
   • Позволяет индексировать операции типа “пересекается”, “содержит”.
   • Используется расширениями, такими как PostGIS (для геоданных) и pg_trgm (для поиска по похожести строк - триграммам).
4. GIN (Generalized Inverted Index - Обобщенный инвертированный индекс):
   • Оптимизирован для индексации составных значений, где одно значение в столбце может содержать несколько “ключей” (например, массив слов в документе, элементы массива jsonb, элементы hstore).
   • Хранит карту “ключ из составного значения -> список строк, где этот ключ встречается”.
   • Преимущества: Очень эффективен для поиска строк, содержащих определенные значения внутри составного типа (например, найти все документы, содержащие слово ‘postgresql’).
   • Используется для полнотекстового поиска, индексации jsonb (операторы ?, ?|, ?&), hstore, массивов (операторы @>, <@, &&).

Часть 4: Выполнение Запросов и Оптимизация

(Этот раздел повторяет и немного расширяет информацию из предыдущей версии методички, основываясь на слайдах Лекции 7).

Декларативность SQL: Пользователь указывает, что он хочет получить, а не как это сделать. СУБД сама решает, как выполнить запрос.

Этапы выполнения запроса в PostgreSQL:

1. Разбор запроса (Parser): SQL-текст преобразуется во внутреннее представление — дерево разбора (parse tree). Проверяется синтаксис.
2. Преобразование запроса (Rewriter/Analyzer): Дерево разбора анализируется, преобразуется с учетом правил и представлений (например, запрос к представлению заменяется на запрос к базовым таблицам). Семантическая проверка. Результат — дерево запроса (query tree).
3. Планировщик/Оптимизатор (Planner/Optimizer):
   • Генерирует множество возможных планов выполнения для дерева запроса, используя:
     • Законы реляционной алгебры для эквивалентных преобразований.
     • Информацию об имеющихся индексах.
     • Доступные алгоритмы выполнения операций (Seq Scan, Index Scan, Nested Loop Join, Hash Join, Merge Join, Sort, Aggregate и т.д.).
   • Оценивает стоимость каждого плана на основе статистики таблиц (количество строк, распределение значений, размер таблицы, кардинальность и т.д.) и стоимости операций (I/O, CPU).
   • Выбирает план с минимальной оцененной стоимостью.
   • Результат — план выполнения (execution plan).
4. Выполнение плана (Executor): План выполнения передается исполнителю, который шаг за шагом выполняет операции плана и возвращает результат.

План выполнения запроса:

• Это, по сути, программа, которую СУБД строит для ответа на SQL-запрос.
• Для одного SQL-запроса может существовать несколько эквивалентных планов, но с разной стоимостью выполнения.
• Критерий выбора плана: Оценочная стоимость (число обменов с диском, время обмена, загрузка CPU).

Оптимизация на основе Реляционной Алгебры:

Оптимизатор использует законы реляционной алгебры (коммутативность, ассоциативность соединений, проталкивание выборок и проекций и т.д.) для генерации различных, но семантически эквивалентных планов.

• Пример (из слайдов 16-22 Лекции 7): sql SELECT * FROM STUDENTS ST JOIN EXAMS EXAM ON ST.ID = EXAM.STUD_ID WHERE ST.GROUP = '3100' AND ST.ID >= 150000;
  • План 1 (сначала JOIN, потом WHERE): σ(ST.GROUP='3100' ∧ ST.ID>=150000) (STUDENTS ⋈(ST.ID=EXAM.STUD_ID) EXAMS) Представление в виде дерева: сначала соединение, потом две операции выборки.
  • План 2 (сначала WHERE для STUDENTS, потом JOIN): (σ(ST.GROUP='3100' ∧ ST.ID>=150000) (STUDENTS)) ⋈(ST.ID=EXAM.STUD_ID) EXAMS Представление в виде дерева: сначала две операции выборки для STUDENTS, потом результат соединяется с EXAMS. Этот план обычно эффективнее, так как соединяется меньший объем данных.

Материализация vs Конвейерная Обработка (Pipelining):

• Материализация: Результат промежуточной операции полностью вычисляется и сохраняется во временную структуру, затем передается следующей операции.
• Конвейерная обработка: Как только первая операция производит первую строку результата, эта строка немедленно передается на вход следующей операции. Экономит ресурсы и время. Оптимизатор старается использовать конвейер. Левосторонние деревья планов хорошо подходят для конвейерной обработки (результат предыдущего соединения сразу идет на вход следующего).

Советы по построению (оптимизации) планов (что пытается делать оптимизатор):

• Использовать конвейерную обработку (левосторонние планы, избегать блокирующих операций типа полной сортировки без необходимости).
• Делать выборку как можно раньше.
• Делать проекцию как можно раньше.
• Грамотно планировать соединения (порядок, алгоритмы).
• Цель: Уменьшение размеров промежуточных данных => уменьшение числа операций чтения/записи во внешнюю память.

Влияние индексов на планы: Индексы могут кардинально изменить выбранный план, позволяя заменить полное сканирование таблицы (Seq Scan) на более эффективное индексное сканирование (Index Scan) или улучшить производительность соединений (Index Nested Loop Join).

Стоимость плана: Складывается из стоимости чтения входных/промежуточных таблиц, записи промежуточных данных (при материализации), сортировки и других операций.

Часть 5: Алгоритмы Выполнения Соединений (JOIN)

(Детальное описание алгоритмов было в предыдущей версии, здесь краткое повторение с акцентом на примеры из Лекции 7)

1. Nested Loop Join (Соединение вложенными циклами):
   • R ⋈_condition S (R - внешнее, S - внутреннее отношение)
   • Простой: Для каждой строки r из R, просматриваем все строки s из S. Если condition(r, s) истинно, добавляем r + s в результат.
     • Пример (слайды 54-70 Лекции 7): Таблицы Groups и Students. Для первой группы из Groups перебираются все студенты из Students. Потом для второй группы из Groups снова перебираются все студенты из Students, и так далее.
   • Block Nested Loop: Внешнее отношение R читается блоками (несколько страниц). Для каждого блока R, сканируется вся внутренняя таблица S. Уменьшает число полных сканирований S.
     • Пример (слайды 88-91 Лекции 7): Если в буфере помещается блок из 4 страниц Groups, то для этого блока один раз сканируется вся таблица Students. Затем загружается следующий блок Groups и снова сканируется вся Students. Эффективнее, если внешняя таблица меньше внутренней.
   • Index Nested Loop: Если по столбцу соединения во внутренней таблице S есть индекс. Для каждой строки r из R выполняется быстрый поиск по индексу в S.
2. Sort-Merge Join (Соединение слиянием):
   • Обе таблицы (R и S) сортируются по атрибутам соединения.
   • Затем отсортированные таблицы “сливаются” (как при merge sort), одновременно проходя по обеим и комбинируя строки с совпадающими значениями атрибутов соединения.
   • Пример (слайды 93-99 Лекции 7): Таблицы Groups и Students (предполагается, что они уже отсортированы по GroupID). Указатели одновременно движутся по обеим таблицам.

Часть 6: Просмотр Плана Выполнения в PostgreSQL

Команда EXPLAIN показывает план, который СУБД выбрала для выполнения запроса.

• EXPLAIN <SQL_ЗАПРОС>;: Показывает план выполнения, но не выполняет сам запрос.
  • Вывод (слайд 103-104): QUERY PLAN --------------------------------------------------------------------- Seq Scan on Students (cost=0.00..309.00 rows=900 width=170)
    • Seq Scan on Students: Операция — последовательное сканирование таблицы Students.
    • cost=0.00..309.00: Оценочная стоимость (начальная..общая).
    • rows=900: Оценочное количество строк, которое вернет эта операция.
    • width=170: Оценочный средний размер строки.
  • Вывод с индексом (слайд 105): QUERY PLAN ---------------------------------------------------------------------------------------- Index Scan using students_studid_pkey on students (cost=0.32..8.34 rows=1 width=150) Index Cond: (StudId = 942)
    • Index Scan using students_studid_pkey on students: Используется индекс students_studid_pkey.
    • Index Cond: (StudId = 942): Условие, применяемое к индексу.
• EXPLAIN ANALYZE <SQL_ЗАПРОС>;: Выполняет запрос и показывает реальный план выполнения с фактическим временем выполнения каждой операции и количеством строк.

  • Вывод (примерный, на основе слайда 106):

    EXPLAIN ANALYZE SELECT * FROM students WHERE StudID = 942;
    
    QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------
     Index Scan using students_studid_pkey on students  (cost=0.29..8.30 rows=1 width=153) (actual time=0.020..0.021 rows=1 loops=1)
       Index Cond: (studid = 942)
     Planning Time: 0.075 ms
     Execution Time: 0.035 ms
    (4 rows)
    

    • (actual time=0.020..0.021 rows=1 loops=1): Фактическое время (начало..конец), фактическое число строк, сколько раз узел выполнялся.
    • Planning Time: Время, затраченное на планирование/оптимизацию.
    • Execution Time: Общее время выполнения запроса (включая планирование, если не используется SET anl_separate_planning_info = on; или аналогичная опция).

Анализ вывода EXPLAIN (особенно EXPLAIN ANALYZE) помогает понять, почему запрос выполняется медленно, используются ли индексы, корректны ли оценки оптимизатора, и какие части запроса являются “бутылочным горлышком”.