Практическая 5 DDL_

1. DDL (Data Definition Language) — определение данных

2.

Типы данных
Числовые типы
Числовые типы включают двух-, четырёх- и восьмибайтные целые, четырёх- и восьмибайтные числа с плавающей точкой, а также десятичные числа
с задаваемой точностью. Все эти типы перечислены в Таблице
Имя
Размер
Описание
Диапазон
smallint
Целочисленные типы
2 байта
целое в небольшом
-32768 .. +32767
диапазоне
integer
4 байта
типичный выбор для
-2147483648 ..
целых чисел
+2147483647
bigint
8 байт
целое в большом
-9223372036854775808 ..
диапазоне
9223372036854775807
Числа с произвольной точностью
Числа с плавающей точкой
Последовательные типы
2
decimal
переменный
вещественное число с
указанной точностью
numeric
переменный
вещественное число с
указанной точностью
real
4 байта
double precision
8 байт
smallserial
2 байта
serial
4 байта
bigserial
8 байт
вещественное число с
переменной точностью
вещественное число с
переменной точностью
небольшое целое с
автоувеличением
целое с
автоувеличением
большое целое с
автоувеличением
до 131072 цифр до
десятичной точки и до
16383 — после
до 131072 цифр до
десятичной точки и до
16383 — после
точность в пределах 6
десятичных цифр
точность в пределах 15
десятичных цифр
1 .. 32767
1 .. 2147483647
1 ..
9223372036854775807

3.

Числовые типы данных
Целочисленные типы
Типы smallint, integer и bigint хранят целые числа, то есть числа без дробной части, имеющие разные допустимые диапазоны. Попытка сохранить значение,
выходящее за рамки диапазона, приведёт к ошибке.
Чаще всего используется тип integer, как наиболее сбалансированный выбор ширины диапазона, размера и быстродействия. Тип smallint обычно применяется,
только когда крайне важно уменьшить размер данных на диске. Тип bigint предназначен для тех случаев, когда числа не умещаются в диапазон типа integer.
В SQL определены только типы integer (или int), smallint и bigint. Имена типов int2, int4 и int8 выходят за рамки стандарта, хотя могут работать и в некоторых
других СУБД.
Числа с произвольной точностью
Типы decimal и numeric равнозначны. Оба эти типа описаны в стандарте SQL.
Числа фиксированной точности представлены двумя типами — numeric и decimal.
Однако они являются идентичными по своим возможностям. Поэтому мы будем проводить изложение на примере типа numeric.
Для задания значения этого типа используются два базовых понятия: масштаб (scale) и точность (precision). Масштаб
показывает число значащих цифр, стоящих справа от десятичной точки (запятой).
Точность указывает общее число цифр как до десятичной точки, так и после нее. Например, у числа 12.3456 точность составляет 6 цифр, а масштаб — 4 цифры.
Параметры этого типа данных указываются в круглых скобках после имени типа: numeric(точность, масштаб). Например, numeric(6, 2).
Его главное достоинство — это обеспечение точных результатов при выполнении вычислений, когда это, конечно, возможно в принципе. Это оказывается
возможным при выполнении сложения, вычитания и умножения. Числа типа numeric могут хранить очень большое количество цифр: 131 072 цифры — до
десятичной точки (запятой), 16 383 — после точки. Однако нужно учитывать, что такая точность достигается
за счет замедления вычислений по сравнению с целочисленными типами и типами с плавающей точкой. При этом для хранения числа затрачивается больше
памяти, чем в случае целых чисел. Данный тип следует выбирать для хранения денежных сумм, а также в других случаях, когда требуется гарантировать
точность вычислений.
Сферы применения:
-Финансовые расчеты: Учет денег в банках и магазинах, где ошибка даже в одну копейку из-за двоичного округления недопустима.
- Криптография: Алгоритмы шифрования (работающие с гигантскими простыми числами в сотни и тысячи бит.
- Научные и математические расчеты: Вычисление знаков числа Пи, моделирование физических процессов высокой точности или астрономия, где стандартной
точности не хватает.
3

4.

Числовые типы данных
Числа с плавающей точкой
Типы данных real и double precision хранят приближённые числовые значения с переменной точностью и применяются там, где важны огромный
диапазон значений и высокая скорость вычислений при допустимости небольшой погрешности округления.
Где они применяются:
- Научные и инженерные расчёты: физическое моделирование, метеорология, астрономия, где числа могут быть как гигантскими (масса планеты),
так и микроскопическими (заряд электрона).
- Графика и геймдев (3D/2D): координаты объектов, векторы, матрицы трансформаций, расчеты физики движения и освещения в играх.
- Машинное обучение и анализ данных: веса нейросетей, статистический анализ, матричные вычисления.
- Геоинформационные системы (ГИС): хранение географических координат (широта и долгота с высокой точностью).
- Сенсорные данные и телеметрия: обработка сигналов с датчиков, показаний приборов, логов с плавающей шкалой.
Где их применять нельзя:
- В финансах и бухгалтерии: из-за двоичной системы исчисления операции с десятичными дробями дают микро-погрешности. Для денег
используют типы decimal или numeric.
- Для точных счетчиков и ID: где важна строгая целочисленность и порядок
Последовательные типы
4
Типы данных smallserial, serial и bigserial не являются настоящими типами, а представляют собой просто удобное средство для создания столбцов с
уникальными идентификаторами, это удобное сокращение для автоматического создания уникальных возрастающих номеров (автоинкремента),
которые обычно применяются для первичных ключей. По сути, они не являются отдельными самостоятельными типами данных, а представляют
собой комбинацию целочисленного типа, создания объекта последовательности и назначения её генератора по умолчанию.

5.

Денежные типы
Тип money хранит денежную сумму с фиксированной дробной частью;
Тип данных money в базах данных (например, в PostgreSQL) используется для хранения денежных сумм с фиксированной
точностью. Он автоматически учитывает локальные настройки форматирования валюты.
Характеристики типа money
- Фиксированная точность: дробная часть отделяется фиксированным числом знаков.
- Зависимость от локали: вывод знака валюты определяется параметрами системы.
- Ограничения: для сложных расчетов часто рекомендуют numeric из-за риска ошибок округления
5
Имя
Размер
Описание
Диапазон
money
8 байт
денежная сумма
92233720368547758.0
8 ..
+92233720368547758.
07

6.

Символьные типы
Стандартные представители строковых типов — это типы character varying(n)
и character(n), где параметр указывает максимальное число символов в строке, которую можно сохранить в столбце такого типа.
При работе с многобайтовыми кодировками символов, например UTF-8, нужно учитывать, что речь идет о символах, а не о байтах.
Если сохраняемая строка символов будет короче, чем указано в определении типа, то значение типа character будет дополнено
пробелами до требуемой длины, а значение типа character varying будет сохранено так, как есть.
Типы character varying(n) и character(n) имеют псевдонимы varchar(n) и char(n) соответственно. На практике, как правило,
используют именно эти краткие псевдонимы.
PostgreSQL дополнительно предлагает еще один символьный тип — text. В столбец этого типа можно ввести сколь угодно большое
значение, конечно, в пределах, установленных при компиляции исходных текстов СУБД.
6
Имя
Описание
Для чего используется
Как хранит данные
character varying(n), varchar(n)
строка ограниченной
переменной длины
Строки разной длины с
ограничением (имена, почта)
Строки разной длины с
ограничением (имена, почта)
character(n), char(n), bpchar(n)
строка фиксированной длины,
дополненная пробелами
Строки строго фиксированной
длины (коды стран, хэши)
text
строка неограниченной
переменной длины
Огромные тексты без четких
лимитов (статьи, отзывы, логи)
Если строка короче n, база
данных принудительно
допишет пробелы в конец.
Хранит любые объемы
данных, но может работать
медленнее при поиске и
индексации.

7.

Двоичные типы данных
В PostgreSQL для хранения двоичных данных (последовательностей байтов) используется единственный встроенный тип данных
— bytea.
Его главное преимущество в том, что он позволяет сохранять любые байтовые последовательности, включая нулевые байты и
другие непечатаемые символы, которые недопустимы в текстовых типах, таких как text или varchar.
Это самое распространенное применение. bytea позволяет хранить содержимое файлов непосредственно в базе данных,
обеспечивая его целостность и согласованность с другими данными.
Вы можете хранить небольшие графические файлы, миниатюры, аудиозаписи или короткие видеоклипы напрямую в столбце bytea.
Это удобно, когда нужно, чтобы медиафайл был неразрывно связан с записью в БД (например, аватар пользователя).
Файлы форматов .doc, .xls, .pdf и другие также можно хранить в bytea, если их размер не превышает лимит в 1 ГБ на одно значение.
Bytea — идеальный тип для хранения результатов криптографических функций, так как они возвращают бинарные данные. (Хеши
паролей и данных, Зашифрованные данные, Бинарные ключи и токены)
7

8.

Типы даты/времени
В PostgreSQL типы данных TIMESTAMPTZ, TIMESTAMP, DATE, TIME и INTERVAL составляют основу для работы со
временем. Они тесно связаны между собой, и база данных позволяет легко конвертировать их друг в друга и проводить между ними
математические операции.
8
Тип данных
Что хранит
Пример значения
Размер
DATE
Только дату (год, месяц, день)
2026-09-22
4 байта
TIME
Только время (часы, минуты, секунды) 21:04:00
8 байт
TIMESTAMP
Дата + Время в одном поле без
часового пояса
2026-09-22 21:04:00
8 байт
TIMESTAMPTZ
Дата + Время в одном поле с часовым
поясом
2026-09-22 21:04:00 Z
8 байт
INTERVAL
Промежуток / количество времени
3 days 02:30:00
16 байт

9.

Логический тип данных
В PostgreSQL для хранения логических значений используется стандартный тип данных BOOLEAN.
Главное отличие PostgreSQL от многих других СУБД (например, MySQL или Oracle) заключается в том, что BOOLEAN здесь — это
полноценный тип данных, а не просто маскировка под числа 1 и 0.
Логический тип в PostgreSQL может принимать три состояния:
TRUE (истина)
FALSE (ложь)
NULL (неизвестно / отсутствие значения)
Типы перечислений
Типы перечислений — это специальные типы данных, которые принимают значения только из заранее определенного фиксированного
списка строк.
Типы перечислений (enum) определяют статический упорядоченный набор значений, так же как и типы enum, существующие в ряде
языков программирования. В качестве перечисления можно привести дни недели или набор состояний.
CREATE TYPE day_of_week AS ENUM ( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday' );
9

10.

Геометрические типы данных
Геометрические типы данных в PostgreSQL служат для хранения двумерных пространственных объектов (point, box, circle и др.) и выполнения
математических расчетов на плоскости без подключения сторонних расширений.
Геометрические типы данных представляют объекты в двумерном пространстве. Все существующие в PostgreSQL геометрические типы
перечислены в таблице.
10
Имя
Размер
Описание
Представление
point
16 байт
Точка на плоскости
(x,y)
line
24 байта
Бесконечная прямая
{A,B,C}
lseg
32 байта
Отрезок
[(x1,y1),(x2,y2)]
box
32 байта
Прямоугольник
(x1,y1),(x2,y2)
path
16+16n байт
Закрытый путь (подобный
многоугольнику)
((x1,y1),...)
path
16+16n байт
Открытый путь
[(x1,y1),...]
polyg
on
40+16n байт
Многоугольник (подобный закрытому
пути)
((x1,y1),...)
circle
24 байта
Окружность
<(x,y),r> (центр окружности и
радиус)
Где и для чего они используются:
Простые геозоны (LBS и доставка) Определение попадания точки (например, координат курьера или пользователя) внутрь определенной зоны или здания.
Игры и симуляции Расчет пересечений объектов на плоской 2D-карте, проверка столкновений персонажей или зон поражения.
Чертежи, САПР (CAD) и схемы Хранение координат элементов на чертежах помещений, деталей или схем посадочных мест в кинотеатрах/самолетах.
Пространственный поиск на плоскости Быстрый поиск ближайших объектов с помощью встроенных операторов расстояния или проверки вхождения

11.

Типы, описывающие сетевые адреса
В PostgreSQL для хранения сетевых адресов используются специальные типы данных: inet, cidr, macaddr и macaddr8.
Для хранения сетевых адресов лучше использовать эти типы, а не простые текстовые строки, так как PostgreSQL
проверяет вводимые значения данных типов и предоставляет специализированные операторы и функции для работы
с ними.
Основные типы сетевых адресов
- inet: хранит хосты и сети IPv4 или IPv6 (занимает 7 или 19 байт). Принимает любые биты после маски (например,
192.168.1.5/24).
- cidr: хранит спецификации сетей IPv4 или IPv6 (занимает 7 или 19 байт). Автоматически сбрасывает в нули биты,
идущие справа от маски (ввод 192.168.1.5/24 преобразуется в 192.168.1.0/24).
- macaddr: хранит MAC-адреса устройств (6 байт).
- macaddr8: хранит MAC-адреса в формате EUI-64 (8 байт)
11

12.

Битовые строки
Битовые строки используются для хранения битовых масок, то есть последовательностей из нулей и единиц (0 и 1). Они особенно полезны для
оптимизации хранения флагов, работы с битовыми масками, настройки прав доступа или кодирования состояний.
Основные типы данных
bit(n) — битовая строка фиксированной длины n.
bit varying(n) — битовая строка переменной длины, но с максимальным пределом n. Без указания длины размер строки не ограничен.
Битовые строки применяются в специфических задачах для экономии места и ускорения побитовых операций:
Компактное хранение флагов.
Управление правами доступа.
Сжатие данных и маскирование сетей.
Побитовая фильтрация.
Типы, предназначенные для текстового поиска
Текстовым поиском называется операция анализа набора документов с текстом на естественном языке, в результате которой находятся фрагменты, наиболее
соответствующие запросу.
Тип tsvector представляет документ в виде, оптимизированном для текстового поиска, а tsquery представляет запрос текстового поиска в подобном виде.
12
tsvector (текстовый вектор) — это документ, подготовленный и оптимизированный для поиска. Он представляет собой отсортированный список
уникальных лексем (слов, приведенных к нормальной форме) с указанием позиций в тексте и весов (важности).
tsquery (поисковый запрос) — тип данных для поискового запроса, содержащий слова для поиска, соединенные логическими операторами

13.

Тип UUID
Тип данных uuid сохраняет универсальные уникальные идентификаторы (Universally Unique Identifiers, UUID), определённые в RFC 9562,
ISO/IEC 9834-8:2005 и связанных стандартах.
Этот идентификатор представляет собой 128-битное значение, генерируемое специальным алгоритмом, практически гарантирующим, что этим
же алгоритмом оно не будет получено больше нигде в мире. Таким образом, эти идентификаторы будут уникальными и в распределённых
системах, а не только в единственной базе данных, как значения генераторов последовательностей.
Тип данных XML
Тип данных XML предназначен для хранения и проверки XML-документов или фрагментов. Его главное преимущество перед обычным типом text
— встроенная проверка вводимых данных на соответствие синтаксису XML.
Типы JSON
Типы JSON предназначены для хранения данных JSON (JavaScript Object Notation, Запись объекта JavaScript) согласно стандарту RFC 7159. Такие
данные можно хранить и в типе text, но типы JSON лучше тем, что проверяют, соответствует ли вводимое значение формату JSON. Для работы с
ними есть также несколько специальных функций и операторов.
Массивы
Массивы— это тип данных, позволяющий хранить упорядоченные наборы элементов одного базового или пользовательского типа в рамках одного
столбца.
PostgreSQL позволяет определять столбцы таблицы как многомерные массивы переменной длины. Элементами массивов могут быть любые
встроенные или определённые пользователями базовые типы, перечисления, составные типы, типы-диапазоны или домены.
13

14.

Составные типы
Составной тип представляет структуру табличной строки или записи; по сути это просто список имён полей и соответствующих типов
данных. PostgreSQL позволяет использовать составные типы во многом так же, как и простые типы. Например, в определении таблицы
можно объявить столбец составного типа.
Диапазонные типы
Диапазонные типы— это специальные типы данных, которые позволяют хранить наборы значений в виде одного интервала (например,
диапазон чисел, дат или времени).
Типы доменов
Домен— это пользовательский тип данных, который создается на основе другого базового типа и может содержать дополнительные
ограничения.
Идентификаторы объектов
Идентификатор объекта (OID, Object Identifier) в базах данных — это специальное четырехбайтное число, которое работает как
внутренний уникальный ключ для системных таблиц и строк. В современных версиях баз данных тип oid и его псевдонимы (regclass,
regtype и др.) нужны в основном для внутренних задач системы и работы с метаданными.
Тип pg_lsn
Тип данных pg_lsn хранит номер позиции (Log Sequence Number) в журнале предзаписи (WAL), представляющий собой 64-битное
байтовое смещение. Значение выводится как два шестнадцатеричных числа до 8 знаков через слэш.
Псевдотипы
14
В систему типов PostgreSQL включены несколько специальных элементов, которые в совокупности называются псевдотипами.
Псевдотип нельзя использовать в качестве типа данных столбца, но можно объявить функцию с аргументом или результатом такого типа.
Каждый из существующих псевдотипов полезен в ситуациях, когда характер функции не позволяет просто получить или вернуть
определённый тип данных SQL.

15.

ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДАННЫХ DDL
Перейдем к изучению первой большой группы операторов языка SQL – операторов определения данных
данных (Data Definition Language, DDL). Иногда эту группу операторов называют подъязыком DDL. Эти
операторы позволяют создавать, редактировать и удалять основные объекты БД, такие как схемы,
домены, таблицы и т. д. За создание, изменение и удаление объекта БД отвечают три команды CREATE,
ALTER и DROP соответственно.
Основные операторы DDL
В группу DDL-операторов входят три главные команды:
15
CREATE — создает новый объект базы данных с нуля (например, CREATE TABLE создает новую таблицу, а CREATE
DATABASE — базу данных).
ALTER — меняет структуру уже существующего объекта (например, добавляет новый столбец в таблицу или изменяет тип
данных).
DROP — полностью удаляет объект вместе со всей содержащейся в нем информацией (например, DROP TABLE стирает
таблицу из базы данных).
RENAME - переименовывает существующий объект базы данных.

16.

ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДАННЫХ DDL
Создание базы данных
Для создания базы данных используется команда CREATE DATABASE. Она имеет следующий синтаксис:
CREATE DATABASE имя_базы_данных
[ WITH ] [ OWNER [=] имя_пользователя ]
[ TEMPLATE [=] шаблон ]
[ ENCODING [=] кодировка ]
[ LC_COLLATE [=] правило_сортировки ]
[ LC_CTYPE [=] классификация_символов ]
[ TABLESPACE [=] имя_табличного_пространства ]
[ CONNECTION LIMIT [=] лимит_соединений ];
Простое создание базы данных с настройками по умолчанию:
CREATE DATABASE [IF NOT EXISTS] <имя_базы_даных>;
IF NOT EXISTS — необязательный параметр создает новую базу данных, только если она еще не существует, и не выдает ошибку, если
она уже есть.
Для удаления базы данных применяется команда DROP DATABASE, которая имеет следующий синтаксис:
DROP DATABASE [IF EXISTS] <имя_базы_даных>;
16

17.

ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДАННЫХ DDL
Создание таблиц
Для создания таблиц используется команда CREATE TABLE. Эта команды применяет ряд операторов, которые определяют столбцы таблицы и их
атрибуты.
Базовый синтаксис:
CREATE TABLE имя_таблицы (
колонка1 тип_данных PRIMARY KEY,
колонка2 тип_данных UNIQUE,
колонка3 тип_данных NOT NULL,
колонка_связи тип_данных,
CONSTRAINT имя_ограничения FOREIGN KEY (колонка_связи)
REFERENCES имя главной_таблицы (целевая_колонка)
ON DELETE CASCADE
);
С помощью атрибутов можно настроить поведение столбцов. Могут использоваться атрибуты, представленные в таблице
Ключ
NOT NULL
UNIQUE
AUTO_INCREMENT
Описание
Запрет на вставку в столбец неопределенного значения NULL.
Значение столбца должно быть уникальным.
Значение столбца будет автоматически увеличиваться при добавлении новой строки.
DEFAULT
PRIMARY KEY
Определяет значение по умолчанию для столбца.
Признак первичного ключа. Значение поля должно быть уникальным, оно не может содержать NULL, в таблице
это ограничение может использоваться только один раз.
CHECK
Ограничение-проверка на допустимое значение. В скобках за оператором CHECK указывается предикат,
проверяющий допустимость значения.
Ограничение внешнего ключа для таблицы. Ограничения внешнего ключа требуют, чтобы все значения,
присутствующие во внешнем ключе, соответствовали значениям родительского ключа (обеспечение
ссылочной целостности)
FOREIGN KEY
17

18.

ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДАННЫХ DDL
Создание таблиц
После команды CREATE TABLE идет название таблицы. Имя таблицы выполняет роль ее идентификатора в базе данных, поэтому оно
должно быть уникальным. Затем в скобках перечисляются названия столбцов, их типы данных и атрибуты. В самом конце можно
определить атрибуты для всей таблицы. Атрибуты столбцов, а также атрибуты таблицы указывать необязательно. В качестве примера
рассмотрим скрипт создания простейшей таблицы:
CREATE DATABASE productsdb;
CREATE TABLE customers (
id INT,
age INT,
first_name VARCHAR(20),
last_name VARCHAR(20)
);
Т.к. таблица не может существовать сама по себе, а должна быть создана в определенной базе данных, в скрипте сначала создается база
productsdb.
Далее собственно идет команда создания таблицы клиентов с названием customers.
В таблице определено четыре столбца: id, age, first_name, last_name. Первые два столбца представляют собой идентификатор клиента и его
возраст и имеют тип INT, то есть будут хранить целочисленные значения.
Следующие столбцы хранят имя и фамилию клиента и имеют тип VARCHAR(20), то есть представляют собой строку длиной не более 20
символов.
В данном случае для каждого столбца определены имя и тип данных, при этом атрибуты столбцов и таблицы в целом отсутствуют.
18

19.

ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДАННЫХ DDL
Создание таблиц
Рассмотрим пример создания таблицы с использованием дополнительных атрибутов.
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
age INT DEFAULT 18 CHECK(age > 0 AND age < 100),
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
email VARCHAR(30),
position VARCHAR(50) NOT NULL
CHECK (position IN ('Консультант', 'Старший консультант', 'Менеджер', 'Старший менеджер')),
phone VARCHAR(20) UNIQUE
);
В примере создается таблица customers с указанием, что столбец id является первичным ключом. Кроме того, этот столбец является
автоинкрементным, т.е. явно задавать значение идентификатора не требуется, значение столбца id после каждой новой добавленной строки
будет увеличиваться на единицу. Для столбца age указан атрибут DEFAULT, т.е. если явно не указать значение этого столбца при
добавлении новых данных, по умолчанию будет добавлено значение 18. Кроме того, у столбца age указан атрибут CHECK, после которого
добавлено условие, что возраст клиентов должен быть больше 0 и меньше 100. Далее указано, что имя и фамилия клиента не могут
содержать неопределенные значения NULL. Столбец phone, который представляет телефон клиента, может хранить только уникальные
значения. Т.е. будет нельзя добавить в таблицу две строки, у которых значения в этом столбце будут совпадать.
19

20.

ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДАННЫХ DDL
Создание таблиц
Рассмотрим еще один пример с заданием первичного ключа таблицы. Первичный ключ уникально идентифицирует строку в таблице.
Первичный ключ может быть установлен как на уровне столбца, так и на уровне таблицы. Первичный ключ также может быть составным:
такой ключ будет использовать сразу несколько столбцов, чтобы уникально идентифицировать строку в таблице. В следующем примере
составной ключ создается для таблицы заказов orders.
CREATE TABLE orders (
order_id INT,
product_id INT,
quantity INT,
price DECIMAL(10, 2),
PRIMARY KEY (order_id, product_id)
);
В таблице поля order_id и product_id вместе выступают как составной первичный ключ. То есть в таблице orders не может быть двух строк,
где для обоих из этих полей одновременно были бы одни и те же значения. Таким же образов на уровне таблицы можно указывать
атрибуты CHECK и UNIQUE.
20

21.

ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДАННЫХ DDL
Внешние ключи
Внешние ключи (FOREIGN KEY) позволяют установить связи между таблицами.
Внешний ключ устанавливается для столбцов из зависимой, дочерней таблицы, и указывает на один из столбцов главной или родительской таблицы. Как
правило, внешний ключ указывает на первичный ключ из связанной главной таблицы. Общий синтаксис установки внешнего ключа на уровне таблицы
имеет следующий вид:
[CONSTRAINT <имя_ограничения>]
FOREIGN KEY (<столбец1>, <столбец2>, ..., <столбецN>)
REFERENCES <главная_таблица> (<столбец1>, <столбец2>, ..., <столбецN>)
[ON DELETE <действие>]
[ON UPDATE <действие>]
Для создания ограничения внешнего ключа после FOREIGN KEY указывается столбец таблицы, который будет представлять внешний ключ. После
ключевого слова REFERENCES указывается имя связанной таблицы, а затем в скобках имя связанного столбца, на который будет указывать внешний
ключ. После выражения REFERENCES идут выражения ON DELETE и ON UPDATE, которые задают действие при удалении и обновлении строки из
главной таблицы соответственно.
При создании внешнего ключа с помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняются соответственно при
удалении и изменении связанной строки из главной таблицы. В качестве действия могут использоваться опции, перечисленные в таблице. Например,
каскадное удаление позволяет при удалении строки из главной таблицы автоматически удалить все связанные строки из зависимой таблицы.
Опции ON DELETE / ON UPDATE
Ключ
CASCADE
SET NULL
RESTRICT
NO ACTION
2 1 SET DEFAULT
Описание
Изменение значения первичного ключа приводит к автоматическому изменению соответствующих значений внешнего
ключа.
При изменении значения или удалении первичного ключа все значения в связанных с внешним ключом колонках
устанавливаются в NULL. В результате в дочерней таблице появляются «брошенные» строки, утерявшие связь с
соответствующей записью из главной таблицы.
Отклоняет удаление или изменение строк в главной таблице при наличии связанных строк в зависимой таблице.
То же самое, что и RESTRICT.
При удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию,
которое задается с помощью атрибуты DEFAULT.

22.

ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДАННЫХ DDL
Рассмотрим создание внешнего ключа на примере.
Определим таблицы customers и orders. Таблица customers является главной и представляет клиента.
Таблица orders является зависимой и представляет заказ, сделанный клиентом.
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
age INT, first_name VARCHAR(20),
last_name VARCHAR(20) )
;
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT,
created_at DATE,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
Столбец customer_id в таблице orders является внешним ключом, который указывает на столбец customer_id из таблицы customers, который
является первичным ключом. При создании внешнего ключа с помощью выражений ON DELETE и ON UPDATE можно установить
действия, которые выполняются соответственно при удалении и изменении связанной строки из главной таблицы. В качестве действия
могут использоваться опции. Например, каскадное удаление позволяет при удалении строки из главной таблицы автоматически удалить
все связанные строки из зависимой таблицы.
22

23.

ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДАННЫХ DDL
Модификация таблиц
Для изменения таблицы применяется команда ALTER TABLE. Ее сокращенный формальный синтаксис имеет следующий вид:
ALTER TABLE <название_таблицы>
{
ADD <название_столбца> <тип_данных> [<атрибуты>]
|
DROP COLUMN <название> RESTRICT | CASCADE |
MODIFY COLUMN <название> <тип_данных> [<атрибуты>]|
ALTER COLUMN <название> SET DEFAULT <значение> |
ADD [CONSTRAINT] <определение_ограничения> |
DROP [CONSTRAINT] <имя> RESTRICT | CASCADE
}
С помощью этой команды можно добавить, изменить или удалить столбцы таблицы, а также добавить к таблице новое или удалить уже существующее
ограничение.
Ключ
Описание
ADD [COLUMN]
Добавляет в таблицу новый столбец. Новый столбец определяется так же, как в операторе CREATE TABLE.
ALTER [COLUMN]
Используется для создания или отмены значения по умолчанию для столбца.
DROP [COLUMN]
Удаляет из таблицы столбец. При использовании параметра RESTRICT перед удалением столбца СУБД проверит наличие
ссылок на него из других таблиц и представлений. Если таковые имеются, то столбец не будет удален. Наоборот, при
использовании параметра CASCADE вместе со столбцом будут удалены все объекты, ссылающиеся на него.
ADD CONSTRAINT
Позволяет добавить к таблице новое ограничение.
DROP CONSTRAINT
Удаляет уже существующие ограничения. Если в предложении задан параметр RESTRICT, то в этот момент столбец не
должен использоваться как родительский ключ для внешнего ключа другой таблицы. Если передается параметр CASCADE,
то внешние ключи, имеющие ссылки или ограничения FOREIGN KEY, уничтожаются.
Для полного удаления данных, т.е. очистки таблицы, применяется команда TRUNCATE TABLE:
TRUNCATE TABLE <имя_таблицы>;
Для удаления таблицы из базы данных применяется команда DROP TABLE, после которой указывается название удаляемой таблицы:
2 3 DROP TABLE <имя_таблицы>;

24.

ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДАННЫХ DDL
Например, добавить столбец можно так:
ALTER TABLE customers ADD COLUMN phone VARCHAR(30);
Новый столбец заполняется заданным для него значением по умолчанию (или значением NULL, если вы не добавите указание DEFAULT)
Удалить столбец можно так:
ALTER TABLE products DROP COLUMN description;
Данные, которые были в этом столбце, исчезают. Вместе со столбцом удаляются и включающие его ограничения таблицы. Однако если на столбец
ссылается ограничение внешнего ключа другой таблицы, PostgreSQL не удалит это ограничение неявно. Разрешить удаление всех зависящих от
этого столбца объектов можно, добавив указание CASCADE:
ALTER TABLE products DROP COLUMN description CASCADE;
Для добавления ограничения используется синтаксис ограничения таблицы.
Например:
ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
Чтобы добавить ограничение NOT NULL, которое обычно не записывается в виде ограничения таблицы, используется специальный синтаксис:
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
Эта команда ничего не делает, если для столбца уже задано ограничение NOT NULL.
24
Ограничение проходит проверку автоматически и будет добавлено, только если ему удовлетворяют данные таблицы.

25.

ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДАННЫХ DDL
Для удаления ограничения вы должны знать его имя.
ALTER TABLE products DROP CONSTRAINT some_name;
Как и при удалении столбца, если вы хотите удалить ограничение с зависимыми объектами, добавьте указание CASCADE. Примером такой зависимости может
быть ограничение внешнего ключа, связанное со столбцами ограничения первичного ключа.
Для удаления ограничения NOT NULL предусмотрен упрощённый синтаксис:
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
Этот синтаксис соответствует синтаксису SET NOT NULL для добавления такого ограничения. Если для столбца нет ограничения NOT NULL, команда не будет
выполнять никаких действий. (Напомним, что у столбца может быть не более одного ограничения NOT NULL, поэтому команда всегда однозначно определяет, к
какому ограничению она применяется)
Назначить столбцу новое значение по умолчанию можно так:
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
Заметьте, что это никак не влияет на существующие строки таблицы, а просто задаёт значение по умолчанию для последующих команд INSERT.
Чтобы удалить значение по умолчанию, выполните:
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
При этом по сути значению по умолчанию просто присваивается NULL. Как следствие, ошибки не будет, если вы попытаетесь удалить значение по умолчанию, не
определённое явно, так как неявно оно существует и равно NULL.
25

26.

ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДАННЫХ DDL
Чтобы преобразовать столбец в другой тип данных, используйте команду:
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
Она будет успешна, только если все существующие значения в столбце могут быть неявно приведены к новому типу.
Чтобы переименовать столбец, выполните:
ALTER TABLE имя_таблицы RENAME COLUMN старое_имя TO новое_имя;
ALTER TABLE products RENAME COLUMN product_no TO product_number;
Таблицу можно переименовать так:
ALTER TABLE старое_имя RENAME TO новое_имя;
ALTER TABLE products RENAME TO items;
26

27.

ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДАННЫХ DDL
Чтобы преобразовать столбец в другой тип данных, используйте команду:
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
Она будет успешна, только если все существующие значения в столбце могут быть неявно приведены к новому типу.
Чтобы переименовать столбец, выполните:
ALTER TABLE имя_таблицы RENAME COLUMN старое_имя TO новое_имя;
ALTER TABLE products RENAME COLUMN product_no TO product_number;
Таблицу можно переименовать так:
ALTER TABLE старое_имя RENAME TO новое_имя;
ALTER TABLE products RENAME TO items;
27

28.

Панель запросов (Query Tool)
pgAdmin4 — это программный продукт для администрирования и разработки баз данных PostgreSQL. pgAdmin4 позволяет выполнять
задачи мониторинга, обслуживания, конфигурирования сервера PostgreSQL, а также создавать и выполнять SQL-запросы.
Query Tool — это ключевой инструмент в pgAdmin 4 для работы с SQL, это мощная графическая среда, которая позволяет выполнять
произвольные SQL-команды и анализировать результаты.
Основная задача Query Tool — предоставить удобное место для написания, выполнения и анализа SQL-запросов. Вы можете выполнять
как отдельные запросы, так и целые скрипты, а также просматривать планы выполнения для оптимизации.
Как открыть:
Через меню: В верхнем меню выберите Tools → Query Tool.
Через контекстное меню: В левой панели (Object Explorer) щелкните правой кнопкой мыши по нужной базе данных или таблице и
выберите Query Tool.
Горячие клавиши: Shift + Alt + Q
28

29.

Интерфейс:
Окно Query Tool разделено три части:
Панель инструментов.
Верхняя панель (SQL Editor): Это рабочая область для ввода и редактирования вашего SQL-кода. Здесь есть подсветка синтаксиса и
автодополнение (по нажатию Ctrl + Space). Также в верхней панели находятся вкладки History (история выполненных запросов) и
Scratch Pad (блокнот для временных заметок).
Вместо ручного ввода или автозаполнения вы можете перетащить нужный объект (таблицу, функцию) из дерева объектов
(Object Explorer) прямо в редактор SQL. Имя объекта вставится в запрос уже с полной квалификацией (со схемой).
Два дефиса (--) в SQL используются для создания однострочного комментария. Всё, что написано после этих символов до конца
текущей строки, полностью игнорируется интерпретатором базы данных.
Нижняя панель (Data Output): Здесь отображаются результаты. На разных вкладках можно увидеть:
Data Output: Сами данные, возвращенные запросом.
Messages: Служебные сообщения от сервера (например, «Successfully run» или текст ошибки).
Notifications: Уведомления.
29

30.

На панели инструментов расположены кнопки управления запросом. Основные группы:
Файловые операции: Открыть (Ctrl+O), Сохранить (Ctrl+S).
Выполнение:
o
Execute script (F5) — выполнить весь текст в редакторе.
o
Execute query (Alt+F5) — выполнить только запрос под курсором или выделенный фрагмент.
o
Explain (F7) — показать план выполнения без выполнения.
o
Explain Analyze (Shift+F7) — выполнить запрос и показать реальный план.
Транзакции: Commit (Shift+Ctrl+M), Rollback (Shift+Ctrl+R).
Редактирование: Форматировать SQL (Ctrl+K), Комментировать/раскомментировать (Ctrl+/), Отступы, Поиск/Замена (Ctrl+F).
Очистка: Очистить редактор.
Выбор подключения: В верхней части может отображаться текущая база данных, пользователь и хост. В новых версиях pgAdmin 4
есть выпадающий список для переключения между базами данных без открытия нового окна.
Панель инструментов:
30

31.

Как создать базу данных в Query Tool
CREATE DATABASE нельзя выполнять вместе с другими командами в одном скрипте.
PostgreSQL не разрешает эту команду внутри транзакции, а несколько операторов, отправленных одним запросом, выполняются как одна
транзакция.
Как создать базу данных в Query Tool:
- Откройте Query Tool для любой существующей базы
В дереве объектов выберите, например, базу postgres.
Правой кнопкой → Query Tool (или Shift+Alt+Q).
- Выполните только одну команду
Например,
CREATE DATABASE shop;
Нажмите F5 (Execute script).
- Обновите дерево объектов
Правой кнопкой по узлу Databases → Refresh.
Появится новая база.
Как переключиться на новую базу
В PostgreSQL нет команды USE. Чтобы работать с таблицами в новой базе:
Закройте Query Tool для postgres.
В дереве объектов выберите базу shop.
Правой кнопкой → Query Tool (откроется новое окно, уже подключённое к shop).
Теперь можно выполнять CREATE TABLE и другие запросы.
(Например, таблицы создаются уже в новом окне Query Tool, подключённом к этой базе)
31

32.

Практическая работа
Цель работы: научиться создавать, изменять и удалять объекты базы данных с помощью DDL-команд (CREATE, ALTER, DROP,
TRUNCATE), а также использовать ограничения целостности.
1. Создать и выполнить следующие SQL- запросы:
1.1 Создать БД praktikal;
1.2 Создать следующие таблицы БД:
Таблица Клиенты (Clients)
Описание
Поле
32
id_клиента
Первичный ключ
ФИО
ФИО клиента
телефон
Телефон
паспорт
Паспортные данные, серия и номер паспорта
email
Электронная почта

33.

Практическая работа
Таблица Туры (Tours)
Описание
Поле
id_тура
Первичный ключ
город
Город
дата_выезда
Дата выезда
дата_возврата
Дата возврата
стоимость
Стоимость путёвки
тип_питания
Всё включено / Полный пансион / Полупансион / Завтраки/ Без питания
Таблица Сотрудники (Employees)
Поле
Описание
id_сотрудника
Первичный ключ
ФИО
ФИО
должность
Должность
3 3 телефон
Рабочий телефон

34.

Практическая работа
34
Таблица Заказы (Orders)
Поле
Описание
id_заказа
Первичный ключ
id_клиента
→ Клиенты
id_тура
→ Туры
id_сотрудника
→ Сотрудники
дата_заказа
Дата оформления
количество_человек
Кол-во туристов (количество_человек > 0)

35.

35
1.3 Выполнить индивидуальное задание в соответствии со своим вариантом:
Вариант 1
- изменить тип данных в любом из столбцов таблицы Клиенты
- переименовать любой не ключевой столбец таблицы Туры
- установить значение по умолчанию в столбце Должность
- удалить любой не ключевой столбец таблицы Сотрудники
- удалить таблицу Сотрудники и внешний ключ в таблице Заказы
Вариант 2
- изменить тип данных в любом из столбцов таблицы Туры
- переименовать любой не ключевой столбец таблицы Сотрудники
- установить значение по умолчанию в столбце Должность
- удалить любой не ключевой столбец таблицы Туры
- удалить таблицу Сотрудники и внешний ключ в таблице Заказы
Вариант 3
- изменить тип данных в любом из столбцов таблицы Сотрудники
- переименовать любой не ключевой столбец таблицы Клиенты
- установить значение по умолчанию в столбце Должность
- удалить любой не ключевой столбец таблицы Клиенты
- удалить таблицу Сотрудники и внешний ключ в таблице Заказы
Вариант 4
- изменить тип данных в любом из столбцов таблицы Клиенты
- переименовать любой не ключевой столбец таблицы Сотрудники
- установить значение по умолчанию в столбце Должность
- удалить любой не ключевой столбец таблицы Сотрудники
- удалить таблицу Сотрудники и внешний ключ в таблице Заказы
Вариант 5
- изменить тип данных в любом из столбцов таблицы Сотрудники
- переименовать любой не ключевой столбец таблицы Туры
- установить значение по умолчанию в столбце Должность
- удалить любой не ключевой столбец таблицы Туры
- удалить таблицу Сотрудники и внешний ключ в таблице Заказы
2. Загрузка SQL кода запроса в ЭИОС и
защита (Safe File ->Safe As ->*.sql).

36.

Вариант практической работы определяется по номеру зачетной книжки
25-БИ1
номер по
списку
1
номер зачетной книжки
381147
2
582592
2
3
465177
3
4
677344
4
5
629232
5
6
228837
1
7
563142
2
8
375921
3
9
699484
4
10
156187
5
11
375644
1
12
892931
2
13
182222
3
вариант
1
25-БИ2
36
номер по
списку
1
номер зачетной книжки
759428
2
919749
2
3
661898
3
4
413262
4
5
637473
5
6
796199
1
7
563878
2
8
569497
3
9
875779
4
10
648459
5
11
969388
1
12
975293
2
13
837156
3
14
985947
4
вариант
1
25-ОЗБИ
номер
номер по зачетной
списку книжки
1
455212
2
917314
3
612113
4
445989
5
523494
6
973797
7
616561
8
622332
9
298662
10
115797
11
785622
12
946442
13
458185
вариант
1
2
3
4
5
1
2
3
4
5
1
2
3

37. Спасибо