Ткачев Олег - PostgreSQL: SQL + PL/pgSQL для тех, кто хочет стать профессионалом [2024, PDF, RUS]

Примеры страниц (скриншоты)

Оглавление

ВВЕДЕНИЕ........................................................................................................... 13
ГЛАВА 1. ВИЗУАЛЬНАЯ СРЕДА РАЗРАБОТКИ DBEAVER.............................................. 19
1.1. Главное окно DBeaver......................................................................................21
1.1.1. Меню..........................................................................................................21
1.1.2. Панель инструментов ...................................................................................21
1.1.3. Рабочая область.......................................................................................... 22
1.1.4. Окно проектов.............................................................................................23
1.2. Подключение к базе данных......................................................................... 23
1.2.1. Настройка соединения...............................................................................24
1.2.2. Загрузка схемы базы данных из резервной копии....................................... 26
1.3. Создание пользователей и предоставление им прав для
работы с базой данных........................................................................................28
1.3.1. Создание роли.............................................................................................29
1.3.2. Предоставление привилегий.................................................................... 31
1.3.3. Отзыв привилегий..................................................................................... 34
1.3.4. Предоставление и отзыв привилегий в графическом режиме............................35
1.3.5. Пример создания новой базы данных и предоставления
привилегий.............................................................................................................36
1.4. Навигатор базы данных..................................................................................38
1.4.1. Режимы просмотра объектов базы данных....................................................39
1.4.2. Свойства объектов..................................................................................... 40
1.4.3. Копирование таблиц..................................................................................43
1.5. Редактор SQL............................................................................................. 45
1.5.1. Окно редактора SQL..................................................................................46
1.5.2. SQL-терминал.............................................................................................48
1.5.3. Панель результатов..................................................................................... 49
1.5.4. Управление скриптами...............................................................................51
1.5.5. План выполнения запроса......................................................................... 53
1.6. Диаграммы сущность - связь.........................................................................55
1.6.1. Диаграммы таблиц и схем......................................................................... 55
1.6.2. Пользовательские диаграммы..................................................................57
1.6.3. Описание используемой схемы базы данных.......................................60
ГЛАВА 2. СТРУКТУРА ОПЕРАТОРА SELECT И
ФОРМИРОВАНИЕ УСЛОВИЙ ВЫБОРА............................................................. 63
2.1. Структура оператора SELECT...................................................................65
2.2. Условия выбора......................................................................................68
2.3. Выражения выбора............................................................................... 70
2.3.1. Выражение LIKE..........................................................................................71
2.3.2. Выражение BETWEEN...............................................................................72
2.3.3. Выражение IN..............................................................................................75
2.3.4. Выражение IS NULL............................................................................ 77
2.4. Вычисляемые столбцы........................................................................78
2.5. Операция конкатенации......................................................................80
2.6. Условные выражения........................................................................... 81
2.6.1. Выражение CASE с параметром..............................................................82
2.6.2. Выражение CASE с условием.................................................................. 85
2.7. Сортировка............................................................................................85
2.8. Ограничение количества строк в результате выполнения
запроса..............................................................................................................87
Задачи для самостоятельного решения.............................................................89
ГЛАВА 3 ТИПЫ ДАННЫХ И ВСТРОЕННЫЕ ФУНКЦИИ .........................................91
3.1. Числовые типы........................................... ........................................ 92
3.2. Символьные типы.............................................................................. 98
3.3. Типы даты и времени........................................................................ 104
3.3.1. Тип INTERVAL..........................................................................................107
3.4. Логический тип......................................... ........................................112
3.5. Функции преобразования типов данных................................................. 114
3.5.1. Преобразование чисел в строку символов.......................................... 115
3.5.2. Преобразование типов даты и времени в строку символов............116
3.5.3. Преобразование строки символов в число......................................... 119
3.5.4. Преобразование строки символов к типам даты и времени...........120
3.5.5. Преобразование значений NULL........................................................... 121
3.5.6. Функция COALESCE ............................................................................. 122
Задачи для самостоятельного решения.............................................. 125
ГЛАВА 4 АГРЕГАТНЫЕ ФУНКЦИИ И ГРУППИРОВКА
ДАННЫХ................................................................................................ 127
4.1. Агрегатные функции........................................................................ 128
4.2. Группировка......................................................................................... 131
4.2.1. Группировка по нескольким столбцам.................................................133
4.2.2. Использование условий на группу.............................................................. 134
4.3. Использование специальных операторов группировки...............................137
4.3.1. Оператор GROUP BY ROLLUP............................................................. 137
4.3.2. Оператор GROUP BY CUBE................................................................ 140
4.3.3. Оператор GROUP BY GROUPING SETS............................................ 141
Задачи для самостоятельного решения.................................................... 143
ГЛАВА 5. МНОГОТАБЛИЧНЫЕ ЗАПРОСЫ.....................................................145
5.1. Условия соединения таблиц в предложении WHERE..............................147
5.2. Условия соединения таблиц в предложении FROM.................................150
5.2.1. Внутренние соединения...........................................................................150
5.2.2. Внешние соединения................................................................................ 154
Левое внешнее соединение..................................................................... 154
Правое внешнее соединение................................................................... 156
Полное внешнее соединение.................................................................. 157
5.3. Декартово произведение таблиц.................................................... 158
5.4. Самосоединение таблицы................................................................. 159
Задачи для самостоятельного решения...............................................161
ГЛАВА 6. ПОДЗАПРОСЫ (ВЛОЖЕННЫЕ ЗАПРОСЫ)....................................163
6.1. Простые подзапросы..........................................................................164
6.1.1. Подзапросы и значение Null................................................................... 166
6.1.2. Использование выражения IN................................................................ 167
6.1.3. Использование выражений ALL и ANY...............................................169
6.1.4. Выражения ALL, ANY и значение NULL.............................................170
6.1.5. Выражения ALL, ANY и пустые подзапросы..................................... 172
6.1.6. Многостолбцовые подзапросы............................................................... 173
6.2. Коррелированные подзапросы....................................................... 177
6.2.1. Выражение EXISTS.................................................................................. 179
6.3. Использование оператора WITH................................................... 181
6.4. Составные запросы............................................................................ 183
Задачи для самостоятельного решения...............................................187
ГЛАВА 7. ОПЕРАТОРЫ МОДИФИКАЦИИ ДАННЫХ.......................................189
7.1. Оператор INSERT...............................................................................191
7.1.1. Вставка значений, заданных по умолчанию....................................... 192
7.1.2. Вставка нескольких строк.......................................................................193
7.2. Оператор UPDATE..............................................................................195
7.2.1. Присвоение значений, заданных по умолчанию ...............................196
7.2.2. Обновление строк с использованием коррелированного подзапроса.. 197
7.3. Оператор MERGE...............................................................................198
7.4. Оператор DELETE............................................................................. 204
7.5. Ошибки нарушения ограничения ссылочной целостности
при выполнении операторов изменения данных...................................206
Задачи для самостоятельного решения.............................................. 209
ГЛАВА 8. ОПЕРАТОРЫ ОПРЕДЕЛЕНИЯ ДАННЫХ.......................................211
8.1. Создание таблиц базы данных.......................................................214
8.1.1. Значения по умолчанию..........................................................................215
8.2. Ограничения........................................................................................ 215
8.2.1. Ограничение NOT NULL........................................................................217
8.2.2. Ограничение UNIQUE.............................................................................217
8.2.3. Ограничение EXCLUDE.......................................................................... 218
8.2.4. Ограничение CHECK..............................................................................219
8.2.5. Ограничение первичного ключа (PRIMARY KEY)...........................220
8.2.6. Ограничение внешнего ключа (FOREIGN KEY)............................... 221
8.3. Экспорт данных..................................................................................224
8.4. Резервное копирование базы данных..........................................228
8.5. Редактирование таблиц....................................................................230
8.5.1. Добавление и удаление столбцов......................................................... 231
8.5.2. Изменение столбцов.................................................................................231
8.5.3. Изменение ограничений.......................................................................... 232
8.5.4. Удаление таблицы..................................................................................... 233
8.5.5. Получение информации о таблицах базы данных............................. 234
8.6. Представления.....................................................................................235
8.7. Последовательности...........................................................................240
8.7.1. Функции для работы с последовательностями...................................242
8.7.2. Использование последовательностей...................................................243
8.7.3. Изменение последовательности............................................................ 244
8.7.4. Получение информации о последовательностях................................245
8.8. Индексы.................................................................................................. 245
8.8.1. Создание индексов на базе функций.....................................................247
8.8.2. Перестроение индекса.............................................................................248
8.8.3. Переименование индекса........................................................................ 249
8.8.4. Удаление индекса..................................................................................... 249
8.8.5. Получение информации об индексах....................................................249
Задачи для самостоятельного решения...............................................251
ГЛАВА 9. СТРУКТУРА ПРОГРАММ PL/PGSQL...........................................253
9.1. Структура блока PL/pgSQL.............................................................255
9.2. Анонимные блоки............................................................................... 256
9.3. Переменные, константы и тины данных...............................................259
9.3.1. Использование переменных числового типа......................................260
9.3.2. Использование переменных символьного типа..................................261
9.3.3. Использование переменных типа Date ................................................263
9.3.4. Неявное объявление типа переменной.................................................264
9.3.5. Область действия переменных...............................................................266
9.4. Операторы SQL в PL/SQL........................................................................269
9.4.1. Использование оператора SELECT...................................................... 270
9.4.2. Использование оператора INSERT........................................................273
9.4.3. Использование оператора UPDATE......................................................275
9.4.4. Использование оператора DELETE..................................................... 279
9.4.5. Использование оператора MERGE...................................................... 280
Задачи для самостоятельного решения.............................................. 282
ГЛАВА 10. ОПЕРАТОРЫ УПРАВЛЕНИЯ....................................................285
ЮЛ. Условные операторы.......................................................................286
10.1.1. Использование инструкции ELSIF......................................................290
10.1.2. Вложенные операторы IF..................................................................... 292
10.2. Использование команд и выражений CASE..................................................293
10.2.1. Команда CASE....................................................................................... 294
10.2.2. Выражение CASE.................................................................................. 298
10.3. Операторы цикла............................................................................. 301
10.3.1. Простые циклы LOOP...........................................................................301
10.3.2. Циклы WHILE........................................................................................307
10.3.3. Циклы FOR.............................................................................................311
10.3.4. Вложенные циклы.................................................................................. 314
Задачи для самостоятельного решения...........................................................317
ГЛАВА 11. КУРСОРЫ..................................................................................319
11.1. Использование курсоров................................................................ 320
11.1.1. Объявление курсора...............................................................................320
11.1.2. Открытие курсора и получение данных............................................322
11.1.3. Закрытие курсора................................................................................... 323
11.2. Циклы для курсоров........................................................................328
11.3. Использование курсоров для изменения данных................................332
11.4. Курсорные переменные.................................................................. 335
Задачи для самостоятельного решения.............................................. 337
ГЛАВА 12. МАССИВЫ.......................................................................... 339
12.1. Объявление масси ва.........................................................................340
12.2. Функции для работы с массивами..............................................342
12.3. Циклы для массивов........................................................................344
12.4. Многоуровневые массивы.............................................................347
12.5. Использование массивов в столбцах таблицы...................... 350
12.6. Использование массивов и курсоров для повышения
эффективности обработки данных....................................................... 357
Задачи для самостоятельного решения...............................................363
ГЛАВА 13. ОБРАБОТКА ОШИБОК................................................365
13.1. Раздел обработки ошибок...............................................................366
13.2. Обработка ошибок, определяемых программистом.............374
13.3. Инициирование ошибок, определяемых сервером...............379
13.4. Распространение ошибок...............................................................381
Задачи для самостоятельного решения.............................................. 385
ГЛАВА 14. ХРАНИМЫЕ ПРОЦЕДУРЫ И ФУНКЦИИ...................................... 387
14.1. Хранимые процедуры......................................................................388
14.2. Хранимые функции..........................................................................396
14.3. Использование массивов в качестве параметров процедур и
функций....................................................................................................... 400
14.4. Хранимые функции, возвращающие таблицу....................... 404
Задачи для самостоятельного решения..............................................409
ГЛАВА 15. ТРИГГЕРЫ........................................................................ 411
15.1. DML-триггеры...................................................................................413
Порядок активизации DML-триггеров ..........................................................414
15.2. Триггерные функции......................................................................414
15.3. Примеры использования DML-триггеров........................................416
15.4. Триггеры для оператора MERGE..................................................... 423
15.5. Триггеры с моментом срабатывания INSTEAD OF.............................. 428
15.5. Триггеры событий........................................................................... 430
15.6. Управление триггерами..................................................................435
Задачи для самостоятельного решения.............................................. 437
ГЛАВА 16. ВСТРОЕННЫЙ ДИНАМИЧЕСКИЙ SQL..................................439
16.1. Выполнение динамических операторов SELECT.................. 440
16.2. Использование динамических DML-операторов..................445
16.3. Формирование имени вызываемой процедуры или функции. 450
16.4. Использование динамических DDL-операторов................... 453
Задачи для самостоятельного решения..............................................456
PostgreSQL: SQL + PL/pgSQL Ф PostgreSQL
ГЛАВА 17. УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ .............................................457
17.1. Требования к транзакциям............................................................458
17.2. Команды управления транзакциями......................................... 461
17.2.1. Команда COMMIT .................................................................................462
17.2.2. Команда SAVEPOINT............................................................................ 462
17.2.3. Команда ROLLBACK ТО SAVEPOINT............................................. 462
17.2.4. Команда ROLLBACK............................................................................ 463
17.3. Примеры использования команд управления транзакциями..464
17.4. Использование команд COMMIT и ROLLBACK в блоках
PL/pgSQL.......................................................................................................467
17.5. Управление транзакциями в DBeaver.......................................472
17.5.1. Режим интеллектуальной фиксации (Smart Commit Mode)..........473
17.5.2. Журнал транзакций.................................................................................474
Задачи для самостоятельного решения...............................................476
Список использованных источников...................................................477