Работа с MySQL, MS SQL Server и Oracle в примерах: практическое пособие для программистов и тестировщиков. 2 изд

Предисловие . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Раздел 1: МОДЕЛЬ, ГЕНЕРАЦИЯ И НАПОЛНЕНИЕ БАЗЫ ДАННЫХ . . . . . . . . . . . . . 7
1.1. ОБЩЕЕ ОПИСАНИЕ МОДЕЛИ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
1.2. МОДЕЛЬ ДЛЯ MYSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.3. МОДЕЛЬ ДЛЯ MS SQL SERVER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
1.4. МОДЕЛЬ ДЛЯ ORACLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.5. ГЕНЕРАЦИЯ И НАПОЛНЕНИЕ БАЗЫ ДАННЫХ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Раздел 2: ЗАПРОСЫ НА ВЫБОРКУ И МОДИФИКАЦИЮ ДАННЫХ . . . . . . . . . . . . . 18
2.1. ВЫБОРКА ИЗ ОДНОЙ ТАБЛИЦЫ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
2.1.1. Пример 1: выборка всех данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
2.1.2. Пример 2: выборка данных без повторения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .19
2.1.3. Пример 3: использование функции COUNT и оценка её производительности . . .22
2.1.4. Пример 4: использование функции COUNT в запросе с условием . . . . . . . . . . . . . . .29
2.1.5. Пример 5: использование функций SUM, MIN, MAX, AVG . . . . . . . . . . . . . . . . . . . . .31
2.1.6. Пример 6: упорядочивание выборки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35
2.1.7. Пример 7: использование составных условий . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .38
2.1.8. Пример 8: поиск множества минимальных и максимальных значений . . . . . . . . . .42
2.1.9. Пример 9: вычисление среднего значения агрегированных данных . . . . . . . . . . . . .54
2.1.10. Пример 10: использование группировки данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .60
2.2. ВЫБОРКА ИЗ НЕСКОЛЬКИХ ТАБЛИЦ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
2.2.1. Пример 11: запросы на объединение как способ получения
человекочитаемых данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65
2.2.2. Пример 12: запросы на объединение и преобразование столбцов в строки . . . . . .69
2.2.3. Пример 13: запросы на объединение и подзапросы с условием IN . . . . . . . . . . . . . .80
2.2.4. Пример 14: нетривиальные случаи использования условия IN
и запросов на объединение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .90
2.2.5. Пример 15: двойное использование условия IN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .95
2.2.6. Пример 16: запросы на объединение и функция COUNT . . . . . . . . . . . . . . . . . . . . . 101
2.2.7. Пример 17: запросы на объединение, функция COUNT
и агрегирующие функции . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
2.2.8. Пример 18: учёт вариантов и комбинаций признаков . . . . . . . . . . . . . . . . . . . . . . . . 129
2.2.9. Пример 19: запросы на объединение и поиск минимума, максимума,
диапазонов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
2.2.10. Пример 20: все разновидности запросов на объединение в трёх СУБД . . . . . . . . 154
2.3. МОДИФИКАЦИЯ ДАННЫХ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
2.3.1. Пример 21: вставка данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
2.3.2. Пример 22: обновление данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
2.3.3. Пример 23: удаление данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
2.3.4. Пример 24: слияние данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
2.3.5. Пример 25: использование условий при модификации данных . . . . . . . . . . . . . . . 209
Раздел 3: ИСПОЛЬЗОВАНИЕ ПРЕДСТАВЛЕНИЙ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
3.1. ВЫБОРКА ДАННЫХ С ИСПОЛЬЗОВАНИЕМ ПРЕДСТАВЛЕНИЙ . . . . . . . . . . . . . 220
3.1.1. Пример 26: выборка данных с использованием
некэширующих представлений . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
3.1.2. Пример 27: выборка данных с использованием
кэширующих представлений и таблиц . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
3.1.3. Пример 28: использование представлений для сокрытия
значений и структур данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
3.2. МОДИФИКАЦИЯ ДАННЫХ С ИСПОЛЬЗОВАНИЕМ ПРЕДСТАВЛЕНИЙ . . . . . 258
3.2.1. Пример 29: модификация данных с использованием
«прозрачных» представлений . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
4
3.2.2. Пример 30: модификация данных с использованием
триггеров на представлениях . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Раздел 4: ИСПОЛЬЗОВАНИЕ ТРИГГЕРОВ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288
4.1. АГРЕГАЦИЯ ДАННЫХ С ИСПОЛЬЗОВАНИЕМ ТРИГГЕРОВ . . . . . . . . . . . . . . . . . . 288
4.1.1. Пример 31: обновление кэширующих таблиц и полей . . . . . . . . . . . . . . . . . . . . . . . . 288
4.1.2. Пример 32: обеспечение консистентности данных . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
4.2. КОНТРОЛЬ ОПЕРАЦИЙ С ДАННЫМИ С ИСПОЛЬЗОВАНИЕМ ТРИГГЕРОВ . 335
4.2.1. Пример 33: контроль операций модификации данных . . . . . . . . . . . . . . . . . . . . . . . 335
4.2.2. Пример 34: контроль формата и значений данных . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
4.2.3. Пример 35: прозрачное исправление ошибок в данных . . . . . . . . . . . . . . . . . . . . . . 366
Раздел 5: ИСПОЛЬЗОВАНИЕ ХРАНИМЫХ ФУНКЦИЙ И ПРОЦЕДУР . . . . . . . . . . 374
5.1. ИСПОЛЬЗОВАНИЕ ХРАНИМЫХ ФУНКЦИЙ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374
5.1.1. Пример 36: выборка и модификация данных
с использованием хранимых функций . . . . . . . . . . . . . . . . . . . . . . . . . . . 374
5.1.2. Пример 37: контроль операций с данными
с использованием хранимых функций . . . . . . . . . . . . . . . . . . . . . . . . . . . 394
5.2. ИСПОЛЬЗОВАНИЕ ХРАНИМЫХ ПРОЦЕДУР . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399
5.2.1. Пример 38: выполнение динамических запросов
с помощью хранимых процедур . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399
5.2.2. Пример 39: оптимизация производительности
с помощью хранимых процедур . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
5.2.3. Пример 40: управление структурами базы данных
с помощью хранимых процедур . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 425
Раздел 6: ИСПОЛЬЗОВАНИЕ ТРАНЗАКЦИЙ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 434
6.1. УПРАВЛЕНИЕ НЕЯВНЫМИ И ЯВНЫМИ ТРАНЗАКЦИЯМИ . . . . . . . . . . . . . . . . . . 434
6.1.1. Пример 41: управление неявными транзакциями . . . . . . . . . . . . . . . . . . . . . . . . . . . . 434
6.1.2. Пример 42: управление явными транзакциями . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445
6.2. КОНКУРИРУЮЩИЕ ТРАНЗАКЦИИ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454
6.2.1. Пример 43: управление уровнем изолированности транзакций . . . . . . . . . . . . . . . 454
6.2.2. Пример 44: взаимодействие конкурирующих транзакций . . . . . . . . . . . . . . . . . . . . 461
6.2.3. Пример 45: управление транзакциями в триггерах,
хранимых функциях и процедурах . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491
Раздел 7: РЕШЕНИЕ ТИПИЧНЫХ ЗАДАЧ
И ВЫПОЛНЕНИЕ ТИПИЧНЫХ ОПЕРАЦИЙ . . . . . . . . . . . . . . . . . . . . . . . . . . . 500
7.1. РАБОТА С ИЕРАРХИЧЕСКИМИ И СВЯЗАННЫМИ СТРУКТУРАМИ . . . . . . . . . . 500
7.1.1. Пример 46: формирование и анализ иерархических структур . . . . . . . . . . . . . . . . 500
7.1.2. Пример 47: формирование и анализ связанных структур . . . . . . . . . . . . . . . . . . . . 518
7.2. ОПЕРАЦИИ С БАЗАМИ ДАННЫХ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 544
7.2.1. Пример 48: резервное копирование и восстановление базы данных . . . . . . . . . . 544
7.3. ОПЕРАЦИИ С СУБД . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 550
7.3.1. Пример 49: управление пользователями, запуск и остановка СУБД . . . . . . . . . . . 550
7.3.2. Пример 50: определение и изменение кодировок . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553
7.4. ПОЛЕЗНЫЕ ОПЕРАЦИИ С ДАННЫМИ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 556
7.4.1. Пример 51: вычисление дат . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 556
7.4.2. Пример 52: получение комбинации неповторяющихся идентификаторов . . . . . 564
7.4.3. Пример 53: работа с форматом JSON . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 570
7.4.4. Пример 54: работа со сводными таблицами . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 582
7.4.5. Пример 55: предложите свой пример . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 594
Раздел 8: КРАТКОЕ СРАВНЕНИЕ MYSQL, MS SQL SERVER, ORACLE . . . . . . . . . 595
Раздел 9: ЛИЦЕНЗИЯ И РАСПРОСТРАНЕНИЕ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599

 Три СУБД, 50 примеров, 129 задач, более 500 запросов с пояснениями и комментариями. От SELECT * до поиска кратчайшего пути в ориентированном графе; никакой теории, только схемы и код, много кода. Книга будет полезна тем, кто: когда-то изучал базы данных, но многое забыл; имеет опыт работы с одной СУБД, но хочет быстро переключиться на другую; хочет в предельно сжатые сроки научиться писать типичные SQL-запросы.
 Эта книга посвящена практике использования SQL для решения типичных задач. Здесь не рассматривается теория реляционных баз данных (предполагается, что вы с ней знакомы либо способны найти недостающую информацию), но приведено более 500 SQL-запросов: от элементарных выборок до использования представлений, триггеров, хранимых процедур и функций. Все примеры представлены в виде постановки задачи и её решения с использованием MySQL, MS SQL Server и Oracle, а также снабжены пояснениями и разбором типичных ошибок.
 

Этот материал в первую очередь будет полезен тем, кто:

• когда-то изучал базы данных, но многое забыл;
• имеет опыт работы с одной СУБД, но хочет переключиться на другую;
• хочет в предельно сжатые сроки научиться писать типичные SQL-запросы.