Просмотр кода страницы БД:Теория:Глава 2

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

<span id="часть-1-этапы-построения-бд"></span>
=== Часть 1: Этапы Построения БД ===

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

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

<span id="часть-2-инфологическая-модель-er-модель"></span>
=== Часть 2: Инфологическая Модель (ER-модель) ===

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

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

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

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

{{#mermaid:graph LR
    subgraph Сущность 1
        A1_1(Атрибут 1)
        A1_2(Атрибут 2)
    end
    subgraph Сущность 2
        A2_1(Атрибут 1)
        A2_2(Атрибут 2)
        A2_3(Атрибут 3)
    end
    E1[Сущность 1] -- Атрибуты --&gt; A1_1
    E1 -- Атрибуты --&gt; A1_2
    E2[Сущность 2] -- Атрибуты --&gt; A2_1
    E2 -- Атрибуты --&gt; A2_2
    E2 -- Атрибуты --&gt; A2_3
    E1 -- 1/M --- R{Связь} -- 1/M --- E2
}}

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

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

<ol style="list-style-type: decimal;">
<li>'''Один-к-одному (1:1):''' Каждому экземпляру первой сущности соответствует не более одного экземпляра второй, и наоборот.
<ul>
<li>''Пример:'' ГРУППА —(1)— Староста —(1)— СТУДЕНТ (Предполагаем, что у группы ровно один староста, и студент может быть старостой только в одной группе).</li></ul>

{{#mermaid:graph LR
    Группа -- 1 --- Староста{Староста} -- 1 --- Студент}}
</li>
<li>'''Один-ко-многим (1:M):''' Одному экземпляру первой сущности может соответствовать ноль, один или несколько экземпляров второй, но одному экземпляру второй сущности соответствует не более одного экземпляра первой.
<ul>
<li>''Пример:'' ГРУППА —(1)— Принадлежность —(M)— СТУДЕНТ (В одной группе много студентов, но каждый студент принадлежит только одной группе).</li></ul>

{{#mermaid:graph LR
    Группа -- 1 --- Принадлежность{Принадлежность} -- M --- Студент}}</li>
<li>'''Многие-к-одному (M:1):''' Зеркальная связь 1:M. Одному экземпляру первой сущности соответствует не более одного экземпляра второй, но одному экземпляру второй может соответствовать ноль, один или несколько экземпляров первой.
<ul>
<li>''Пример:'' СТУДЕНТ —(M)— Принадлежность —(1)— ГРУППА (Много студентов в одной группе).</li></ul>
</li>
<li>'''Многие-ко-многим (M:M):''' Одному экземпляру первой сущности может соответствовать ноль, один или несколько экземпляров второй, и наоборот.
<ul>
<li>''Пример:'' СТУДЕНТ —(M)— Процесс сдачи —(M)— ЭКЗАМЕН (Один студент может сдавать много экзаменов, и один экзамен могут сдавать много студентов).</li></ul>

{{#mermaid:graph LR
    Студент -- M --- Процесс_сдачи{Процесс сдачи} -- M --- Экзамен}}</li></ol>

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

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

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

{{#mermaid:graph TD
     ПЭ{Проведение экзамена} -- M --- Студент
     ПЭ -- M --- Преподаватель
     ПЭ -- M --- Экзамен}}</li></ul>

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

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

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

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

<span id="часть-3-создание-реляционной-бд-на-sql"></span>
=== Часть 3: Создание Реляционной БД на SQL ===

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

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

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

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

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

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

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

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

<span id="часть-4-работа-с-таблицами-ddl"></span>
=== Часть 4: Работа с Таблицами (DDL) ===

'''Типы таблиц:'''

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

'''Создание базовой таблицы (<code>CREATE TABLE</code>)'''

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

'''Синтаксис:'''

<syntaxhighlight lang="sql">CREATE TABLE имя_таблицы (
    имя_колонки1 тип_данных1 [ограничения_колонки1],
    имя_колонки2 тип_данных2 [ограничения_колонки2],
    ...
    [ограничения_таблицы]
);</syntaxhighlight>
'''Пример:'''

<syntaxhighlight lang="sql">-- Создаем таблицу студентов
CREATE TABLE students (
    stud_id    INTEGER,      -- ID студента (целое число)
    stud_name  TEXT,         -- Имя студента (текст произвольной длины)
    birth_date DATE          -- Дата рождения (дата)
);</syntaxhighlight>
'''Ввод/Вывод:'''

<ul>
<li><p>'''Ввод (SQL команда):'''</p>
<syntaxhighlight lang="sql">CREATE TABLE students (
    stud_id    INTEGER,
    stud_name  TEXT,
    birth_date DATE
);</syntaxhighlight></li>
<li><p>'''Вывод (Сообщение от СУБД):'''</p>
<pre>CREATE TABLE</pre>
<p>(Это сообщение означает, что таблица успешно создана).</p></li></ul>

'''Удаление таблицы (<code>DROP TABLE</code>)'''

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

'''Синтаксис:'''

<syntaxhighlight lang="sql">DROP TABLE имя_таблицы;</syntaxhighlight>
'''Пример:'''

<syntaxhighlight lang="sql">DROP TABLE students;</syntaxhighlight>
'''Ввод/Вывод:'''

<ul>
<li><p>'''Ввод (SQL команда):'''</p>
<syntaxhighlight lang="sql">DROP TABLE students;</syntaxhighlight></li>
<li><p>'''Вывод (Сообщение от СУБД):'''</p>
<pre>DROP TABLE</pre></li></ul>

'''Изменение таблицы (<code>ALTER TABLE</code>)'''

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

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

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

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

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

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

-- Удаляем колонку с датой рождения
ALTER TABLE students DROP COLUMN birth_date;</syntaxhighlight>
'''Ввод/Вывод:'''

<ul>
<li><p>'''Ввод (SQL команда):'''</p>
<syntaxhighlight lang="sql">ALTER TABLE students ADD COLUMN group_name TEXT;</syntaxhighlight></li>
<li><p>'''Вывод (Сообщение от СУБД):'''</p>
<pre>ALTER TABLE</pre></li>
<li><p>'''Ввод (SQL команда):'''</p>
<syntaxhighlight lang="sql">ALTER TABLE students DROP COLUMN birth_date;</syntaxhighlight></li>
<li><p>'''Вывод (Сообщение от СУБД):'''</p>
<pre>ALTER TABLE</pre></li></ul>

<span id="часть-5-типы-данных-в-postgresql"></span>
=== Часть 5: Типы Данных в PostgreSQL ===

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

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

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

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

<ol style="list-style-type: decimal;">
<li><p>'''Числовые типы:'''</p>
<ul>
<li><code>SMALLINT</code>: Целые числа, 2 байта (диапазон от -32768 до +32767).</li>
<li><code>INTEGER</code> (или <code>INT</code>): Целые числа, 4 байта (около ±2 миллиардов). Самый распространенный целый тип.</li>
<li><code>BIGINT</code>: Целые числа, 8 байт (очень большой диапазон).</li>
<li><code>NUMERIC(precision, scale)</code> или <code>DECIMAL(precision, scale)</code>: Числа с фиксированной точностью. <code>precision</code> - общее число знаков, <code>scale</code> - число знаков после запятой. Используется для финансовых расчетов, где важна точность. Пример: <code>NUMERIC(10, 2)</code> - до 10 цифр всего, 2 из них после запятой.</li>
<li><code>REAL</code>: Числа с плавающей точкой, одинарная точность (4 байта).</li>
<li><code>DOUBLE PRECISION</code> (или <code>FLOAT8</code>): Числа с плавающей точкой, двойная точность (8 байт).</li></ul>
</li>
<li><p>'''Символьные (строковые) типы:'''</p>
<ul>
<li><code>CHARACTER VARYING(n)</code> или <code>VARCHAR(n)</code>: Строка переменной длины с ограничением максимальной длины <code>n</code>.</li>
<li><code>CHARACTER(n)</code> или <code>CHAR(n)</code>: Строка фиксированной длины <code>n</code>. Если строка короче <code>n</code>, она дополняется пробелами до длины <code>n</code>. Используется редко.</li>
<li><code>TEXT</code>: Строка переменной длины без явного ограничения (но есть технические ограничения). Самый удобный и часто используемый тип для текста в PostgreSQL.</li></ul>

<p>'''Пример использования <code>CHAR(n)</code>:'''</p>
<syntaxhighlight lang="sql">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;</syntaxhighlight>
<p>'''Вывод:'''</p>
<pre> fixed_name | length
------------+--------
 Valery     |     10
(1 row)</pre></li>
<li><p>'''Логический тип:'''</p>
<ul>
<li><code>BOOLEAN</code>: Может принимать значения <code>TRUE</code>, <code>FALSE</code> или <code>NULL</code>.</li>
<li>В PostgreSQL можно задавать разными способами:
<ul>
<li><code>TRUE</code>: <code>TRUE</code>, <code>'true'</code>, <code>'t'</code>, <code>'yes'</code>, <code>'y'</code>, <code>'on'</code>, <code>'1'</code></li>
<li><code>FALSE</code>: <code>FALSE</code>, <code>'false'</code>, <code>'f'</code>, <code>'no'</code>, <code>'n'</code>, <code>'off'</code>, <code>'0'</code></li></ul>
</li>
<li>Рекомендуется использовать <code>TRUE</code> и <code>FALSE</code>.</li></ul>
</li>
<li><p>'''Типы даты/времени:'''</p>
<ul>
<li><code>DATE</code>: Хранит только дату (год, месяц, день). Пример: <code>'2023-10-27'</code>.</li>
<li><code>TIME [ WITHOUT TIME ZONE ]</code>: Хранит только время суток. Пример: <code>'11:30:00'</code>.</li>
<li><code>TIMESTAMP [ WITHOUT TIME ZONE ]</code>: Хранит дату и время. Пример: <code>'2023-10-27 11:30:00'</code>.</li>
<li><code>TIMESTAMP WITH TIME ZONE</code> (или <code>TIMESTAMPTZ</code>): Хранит дату и время с учетом часового пояса. Значение хранится в UTC, а при отображении конвертируется в текущий часовой пояс сессии. Это предпочтительный тип для хранения моментов времени. Пример: <code>'2023-10-27 11:30:00+03'</code>.</li>
<li><code>INTERVAL</code>: Хранит временной интервал (разницу между двумя моментами времени). Пример: <code>INTERVAL '2 days'</code>, <code>INTERVAL '1 hour 30 minutes'</code>.</li></ul>
</li></ol>

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

<syntaxhighlight lang="sql">CREATE TABLE exams (
    exam_id    INTEGER,    -- ID экзамена
    exam_name  TEXT,       -- Название экзамена
    exam_date  TIMESTAMPTZ -- Дата и время проведения с часовым поясом
);</syntaxhighlight>
<span id="часть-6-null-значения"></span>
=== Часть 6: NULL-значения ===

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

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

'''Пример с <code>COUNT</code> и <code>NULL</code>:'''

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

{| class="wikitable"
|-
! style="text-align: left;"| id
! style="text-align: left;"| name
! style="text-align: left;"| surname
! style="text-align: left;"| gr_id
|-
| style="text-align: left;"| 1
| style="text-align: left;"| Григорий
| style="text-align: left;"| Иванов
| style="text-align: left;"| 34
|-
| style="text-align: left;"| 2
| style="text-align: left;"| Григорий
| style="text-align: left;"| Петров
| style="text-align: left;"| <code>NULL</code>
|-
| style="text-align: left;"| 3
| style="text-align: left;"| Иван
| style="text-align: left;"| Сидоров
| style="text-align: left;"| <code>NULL</code>
|}

'''Запрос 1:'''

<syntaxhighlight lang="sql">SELECT COUNT(gr_id) FROM STUDENT
WHERE gr_id IS NULL;</syntaxhighlight>
<ul>
<li><p>'''Объяснение:''' Сначала <code>WHERE gr_id IS NULL</code> отбирает строки с <code>id=2</code> и <code>id=3</code>. Затем <code>COUNT(gr_id)</code> пытается посчитать количество ''не-NULL'' значений в колонке <code>gr_id</code> для отобранных строк. Так как в обеих строках <code>gr_id</code> равен <code>NULL</code>, агрегатная функция их игнорирует.</p></li>
<li><p>'''Вывод:'''</p>
<pre> count
-------
     0
(1 row)</pre></li></ul>

'''Запрос 2:'''

<syntaxhighlight lang="sql">SELECT COUNT(*) FROM STUDENT
WHERE gr_id IS NULL;</syntaxhighlight>
<ul>
<li><p>'''Объяснение:''' Сначала <code>WHERE gr_id IS NULL</code> отбирает строки с <code>id=2</code> и <code>id=3</code>. Затем <code>COUNT(*)</code> считает количество ''отобранных строк''. Их две.</p></li>
<li><p>'''Вывод:'''</p>
<pre> count
-------
     2
(1 row)</pre></li>
<li><p>'''<code>NULL</code> в <code>DISTINCT</code>, <code>ORDER BY</code>, <code>GROUP BY</code>:''' В этих операциях все <code>NULL</code>-значения считаются '''одинаковыми'''.</p></li></ul>

<span id="часть-7-ограничения-целостности-constraints"></span>
=== Часть 7: Ограничения целостности (Constraints) ===

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

<ol style="list-style-type: decimal;">
<li><p>'''<code>CHECK</code>'''</p>
<ul>
<li>Позволяет задать логическое выражение, которое должно быть истинным (<code>TRUE</code>) или неизвестным (<code>NULL</code>) для любого значения, вставляемого или обновляемого в колонке (или для строки, если это ограничение таблицы). Если выражение ложно (<code>FALSE</code>), операция не будет выполнена (вызовет ошибку).</li>
<li>Используется для реализации пользовательских правил (диапазоны значений, зависимости между колонками и т.д.).</li></ul>

<p>'''Пример (ограничение колонки):''' Количество несданных курсов не может быть отрицательным.</p>
<syntaxhighlight lang="sql">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);</syntaxhighlight>
<p>'''Вывод при ошибке:'''</p>
<pre>ERROR:  new row for relation &quot;students&quot; violates check constraint &quot;students_failed_courses_check&quot;
DETAIL:  Failing row contains (1, Test, -1).</pre>
<p>'''Пример (ограничение таблицы):''' Количество несданных курсов не должно превышать максимальное количество.</p>
<syntaxhighlight lang="sql">CREATE TABLE students (
    st_id            INTEGER,
    failed_max       INTEGER,
    failed_courses   INTEGER,
    CONSTRAINT fcrs CHECK ( -- Задаем имя ограничению 'fcrs'
        failed_courses >= 0 AND failed_courses <= failed_max
    )
);</syntaxhighlight>
<p>''Задание имени (<code>CONSTRAINT fcrs</code>) полезно для управления ограничением в будущем (например, для его удаления) и для понимания сообщений об ошибках.''</p></li>
<li><p>'''<code>NOT NULL</code>'''</p>
<ul>
<li>Простое ограничение, запрещающее хранить <code>NULL</code> в колонке. Каждая строка ''должна'' иметь значение в этой колонке.</li>
<li>Логически эквивалентно <code>CHECK (column_name IS NOT NULL)</code>.</li></ul>

<p>'''Пример:''' ID студента, максимальное и текущее число несданных курсов не могут быть <code>NULL</code>.</p>
<syntaxhighlight lang="sql">CREATE TABLE students (
    st_id            INTEGER NOT NULL,
    failed_max       NUMERIC NOT NULL,
    failed_courses   INTEGER NOT NULL
);</syntaxhighlight></li>
<li><p>'''<code>UNIQUE</code>'''</p>
<ul>
<li>Гарантирует, что все значения в колонке (или комбинации значений в группе колонок) уникальны в пределах таблицы.</li>
<li>Допускает наличие <code>NULL</code>-значений (и несколько строк могут иметь <code>NULL</code>, так как <code>NULL</code> не равен <code>NULL</code>).</li></ul>

<p>'''Пример (ограничение колонки):''' ID студента должен быть уникальным (и не <code>NULL</code>).</p>
<syntaxhighlight lang="sql">CREATE TABLE students (
    st_id      INTEGER NOT NULL UNIQUE,
    st_name    TEXT NOT NULL,
    st_surname TEXT NOT NULL,
    st_birth   DATE NOT NULL
);</syntaxhighlight>
<p>'''Пример (ограничение таблицы):''' Комбинация имени, фамилии и даты рождения должна быть уникальной.</p>
<syntaxhighlight lang="sql">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) -- Уникальность для группы колонок
);</syntaxhighlight></li>
<li><p>'''<code>PRIMARY KEY</code> (Первичный ключ)'''</p>
<ul>
<li>Обозначает одну или несколько колонок, которые уникально идентифицируют каждую строку в таблице.</li>
<li>Является комбинацией ограничений <code>NOT NULL</code> и <code>UNIQUE</code>.</li>
<li>В таблице может быть '''только один''' первичный ключ.</li>
<li>В PostgreSQL на колонку(и) первичного ключа автоматически создается <code>UNIQUE</code> B-Tree индекс для быстрого поиска.</li></ul>

<p>'''Пример:'''</p>
<syntaxhighlight lang="sql">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) -- Дополнительное ограничение уникальности
);</syntaxhighlight>
<p>''Если первичный ключ составной (из нескольких колонок), он объявляется как ограничение таблицы:''</p>
<syntaxhighlight lang="sql">CREATE TABLE student_exams (
    st_id   INTEGER,
    ex_id   INTEGER,
    grade   INTEGER,
    PRIMARY KEY (st_id, ex_id) -- Составной первичный ключ
);</syntaxhighlight></li>
<li><p>'''<code>FOREIGN KEY</code> (Внешний ключ) и <code>REFERENCES</code>'''</p>
<ul>
<li>Обеспечивает '''ссылочную целостность''' между двумя таблицами.</li>
<li>Указывает, что значения в колонке(ах) одной таблицы (дочерней) должны соответствовать значениям в колонке(ах) первичного (или уникального) ключа другой таблицы (родительской).</li>
<li>Позволяет реализовать связи (1:1, 1:M) между сущностями.</li></ul>

<p>'''Пример (связь 1:M между GROUP и STUDENT):'''</p>
<syntaxhighlight lang="sql">-- Родительская таблица
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
);</syntaxhighlight>
<p>''Здесь <code>students.gr_id</code> ссылается на <code>groups.gr_id</code>. Это значит, что в <code>students.gr_id</code> можно вставить только те значения <code>gr_id</code>, которые уже существуют в таблице <code>groups</code>, либо <code>NULL</code> (если не запрещено <code>NOT NULL</code>).''</p>
<p>'''Пример с данными:'''</p>
<syntaxhighlight lang="sql">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);</syntaxhighlight>
<p>'''Вывод при ошибке:'''</p>
<pre>ERROR:  insert or update on table &quot;students&quot; violates foreign key constraint &quot;students_gr_id_fkey&quot;
DETAIL:  Key (gr_id)=(3) is not present in table &quot;groups&quot;.</pre>
<p>'''Действия при обновлении/удалении родительской записи (<code>ON DELETE</code>, <code>ON UPDATE</code>):''' Что должно произойти с дочерними записями (студентами), если родительская запись (группа) удаляется или ее первичный ключ изменяется?</p>
<ul>
<li><code>RESTRICT</code> (По умолчанию): Запретить операцию над родительской записью, если есть связанные дочерние.</li>
<li><code>NO ACTION</code>: То же, что и <code>RESTRICT</code>, но проверка выполняется в конце транзакции (если ограничения отложенные).</li>
<li><code>CASCADE</code>: Выполнить ту же операцию над дочерними записями (удалить/обновить связанные строки). '''Опасно!'''</li>
<li><code>SET NULL</code>: Установить значение внешнего ключа в дочерних записях в <code>NULL</code>. (Колонка FK должна допускать <code>NULL</code>).</li>
<li><code>SET DEFAULT</code>: Установить значение внешнего ключа в дочерних записях в значение по умолчанию (<code>DEFAULT</code>). (Для колонки FK должно быть задано значение по умолчанию).</li></ul>

<p>'''Пример с <code>ON DELETE CASCADE</code>:'''</p>
<syntaxhighlight lang="sql">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;</syntaxhighlight></li></ol>

<span id="часть-8-отображение-er-диаграмм-в-реляционную-бд"></span>
=== Часть 8: Отображение ER-диаграмм в Реляционную БД ===

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

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

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

<ul>
<li><p>'''ER-модель:''' СТУДЕНТ – (M) — Сдает — (M) – ЭКЗАМЕН</p></li>
<li><p>'''Таблицы:'''</p>
<syntaxhighlight lang="sql">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) -- Составной первичный ключ
);</syntaxhighlight>
<p>''Здесь <code>student_exams</code> реализует связь M:M. Каждая строка означает, что конкретный студент (<code>st_id</code>) сдал конкретный экзамен (<code>ex_id</code>) с определенной оценкой (<code>grade</code>).''</p></li></ul>

<span id="часть-9-дополнительные-возможности"></span>
=== Часть 9: Дополнительные возможности ===

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

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

<syntaxhighlight lang="sql">-- Предположим, есть таблица абитуриентов
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;
</syntaxhighlight>
'''Последовательности (<code>SEQUENCE</code>)'''

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

<syntaxhighlight lang="sql">-- Создаем последовательность для 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();</syntaxhighlight>
'''Использование последовательности при вставке:'''

<syntaxhighlight lang="sql">-- Вариант 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 заполнится автоматически</syntaxhighlight>
'''Типы <code>SERIAL</code>, <code>SMALLSERIAL</code>, <code>BIGSERIAL</code>'''

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

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

'''Пример:'''

<syntaxhighlight lang="sql">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);</syntaxhighlight>
''Это предпочтительный способ создания автоинкрементных первичных ключей в PostgreSQL.''