Работа с MySQL, MS SQL Server и Oracle в примерах

558 Pages·2016·66.483 MB·Russian

by  Куликов С.C.

Святослав Куликов
Работа с
MySQL, MS SQL Server
и Oracle
© ЕРАМ Systems, RD Dep.
СВЯТОСЛАВ КУЛИКОВ
РАБОТА С
MYSQL,
MS SQL SERVER
И ORACLE
В ПРИМЕРАХ
ПРАКТИЧЕСКОЕ ПОСОБИЕ
МИНСК
УП «БОФФ»
2016
УДК 004.4’6
ББК 32.973.26-018.2
К90
Куликов, С. С.
К90 Работа с MySQL, MS SQL Server и Oracle в примерах : практ. пособие. / С. С. Куликов. — Минск: БОФФ,
2016.— 556 с.
ISBN 978-985-430-054-1.
Три СУБД, 50 примеров, 129 задач, более 500 запросов с пояснениями и комментариями. От SELECT *
до поиска кратчайшего пути в ориентированном графе; никакой теории, только схемы и код, много кода.
Книга будет полезна тем, кто: когда-то изучал базы данных, но многое забыл; имеет опыт работы с одной СУБД,
но хочет быстро переключиться на другую; хочет в предельно сжатые сроки научиться писать типичные
SQL-запросы.
УДК 004.4’6
ББК 32.973.26-018.2
ISBN 978-985-430-054-1 © Куликов С. С., 2016
© Оформление. УП «БОФФ», 2016
СОДЕРЖАНИЕ
Предисловие..............................................................................................................................................5
Раздел 1: МОДЕЛЬ, ГЕНЕРАЦИЯ И НАПОЛНЕНИЕ БАЗЫ ДАННЫХ .......................6
1.1. ОБЩЕЕ ОПИСАНИЕ МОДЕЛИ....................................................................................................6
1.2. МОДЕЛЬ ДЛЯ MYSQL .....................................................................................................................8
1.3. МОДЕЛЬ ДЛЯ MS SQL SERVER ..................................................................................................9
1.4. МОД ЕЛЬ ДЛЯ ORACLE .............................................................................................................. 11
1.5. ГЕНЕРАЦИЯ И НАПОЛНЕНИЕ БАЗЫ ДАННЫХ .......................................................... 12
Раздел 2: ЗАПРОСЫ НА ВЫБОРКУ И МОДИФИКАЦИЮ ДАННЫХ.........................is
2.1. ВЫБОРКА ИЗ ОДНОЙ ТАБЛИЦЫ .........................................................................................18
2.1.1. Пример 1: выборка всех данных.......................................................................................18
2.1.2. Пример 2: выборка данных без повторения...................................................................19
2.1.3. Пример 3: использование функции COUNT и оценка её производительности .. 22
2.1.4. Пример 4: использование функции COUNT в запросе с условием.........................30
2.1.5. Пример 5: использование функций SUM, MIN, MAX, AVG.......................................32
2.1.6. Пример 6: упорядочивание выборки .............................................................................36
2.1.7. Пример 7: использование составных условий..............................................................40
2.1.8. Пример 8: поиск множества минимальных и максимальных значений ...............44
2.1.9. Пример 9: вычисление среднего значения агрегированных данных .....................56
2.1.10. Пример 10: использование группировки данных.........................................................63
2.2. ВЫБОРКА ИЗ НЕСКОЛЬКИХ ТАБЛИЦ................................................................................68
2.2.1. Пример 11: запросы на объединение как способ получения
человекочитаемых данных ...........................................................................68
2.2.2. Пример 12: запросы на объединение и преобразование столбцов в строки .........72
2.2.3. Пример 13: запросы на объединение и подзапросы с условием IN..........................82
2.2.4. Пример 14: нетривиальные случаи использования условия IN
и запросов на объединение...........................................................................93
2.2.5. Пример 15: двойное использование условия IN ..........................................................99
2.2.6. Пример 16: запросы на объединение и функция COUNT ..................................... 104
2.2.7. Пример 17: запросы на объединение, функция COUNT
и агрегирующие функции ....................................................................... 116
2.2.8. Пример 18: учёт вариантов и комбинаций признаков ........................................... 130
2.2.9. Пример 19: запросы на объединение и поиск минимума, максимума,
диапазонов .................................................................................................. 135
2.2.10. Пример 20: все разновидности запросов на объединение в трёх СУБД ............ 153
2.3. МОДИФИКАЦИЯ ДАННЫХ .................................................................................................. 188
2.3.1. Пример 21: вставка данных........................................................................................... 188
2.3.2. Пример 22: обновление данных................................................................................... 195
2.3.3. Пример 23: удаление данных......................................................................................... 198
2.3.4. Пример 24: слияние данных........................................................................................... 201
2.3.5. Пример 25: использование условий при модификации данных........................... 205
Раздел 3: ИСПОЛЬЗОВАНИЕ ПРЕДСТАВЛЕНИЙ 217
3.1. ВЫБОРКА ДАННЫХ С ИСПОЛЬЗОВАНИЕМ ПРЕДСТАВЛЕНИЙ ................... 217
3.1.1. Пример 26: выборка данных с использованием
некэширующих представлений.............................................................. 217
3.1.2. Пример 27: выборка данных с использованием
кэширующих представлений и таблиц ................................................ 222
3.1.3. Пример 28: использование представлений для сокрытия
значений и структур данных................................................................... 250
4
3.2. МОДИФИКАЦИЯ ДАННЫХ С ИСПОЛЬЗОВАНИЕМ ПРЕДСТАВЛЕНИЙ........ 254
3.2.1. Пример 29: модификация данных с использованием
«прозрачных» представлений.................................................................. 254
3.2.2. Пример 30: модификация данных с использованием
триггеров на представлениях.................................................................. 267
Раздел 4: ИСПОЛЬЗОВАНИЕ ТРИГГЕРОВ 284
4.1. АГРЕГАЦИЯ ДАННЫХ С ИСПОЛЬЗОВАНИЕМ ТРИГГЕРОВ................................. 284
4.1.1. Пример 31: обновление кэширующих таблиц и полей .......................................... 284
4.1.2. Пример 32: обеспечение консистентности данных.................................................. 305
4.2. КОНТРОЛЬ ОПЕРАЦИЙ С ДАННЫМИ
С ИСПОЛЬЗОВАНИЕМ ТРИГГЕРОВ................................................................................. 331
4.2.1. Пример 33: контроль операций модификации данных.......................................... 331
4.2.2. Пример 34: контроль формата и значений данных ................................................ 355
4.2.3. Пример 35: прозрачное исправление ошибок в данных........................................ 361
Раздел 5: ИСПОЛЬЗОВАНИЕ ХРАНИМЫХ ФУНКЦИЙ И ПРОЦЕДУР 369
5.1. ИСПОЛЬЗОВАНИЕ ХРАНИМЫХ ФУНКЦИЙ................................................................ 369
5.1.1. Пример 36: выборка и модификация данных
с использованием хранимых функций ................................................ 369
5.1.2. Пример 37: контроль операций с данными
с использованием хранимых функций ................................................ 389
5.2. ИСПОЛЬЗОВАНИЕ ХРАНИМЫХ ПРОЦЕДУР.............................................................. 394
5.2.1. Пример 38: выполнение динамических запросов
с помощью хранимых процедур ............................................................ 394
5.2.2. Пример 39: оптимизация производительности
с помощью хранимых процедур ............................................................ 408
5.2.3. Пример 40: управление структурами базы данных
с помощью хранимых процедур ............................................................ 420
Раздел 6: ИСПОЛЬЗОВАНИЕ ТРАНЗАКЦИЙ .................................................................... 429
6.1. УПРАВЛЕНИЕ НЕЯВНЫМИ И ЯВНЫМИ ТРАНЗАКЦИЯМИ................................. 429
6.1.1. Пример 41: управление неявными транзакциями .................................................. 429
6.1.2. Пример 42: управление явными транзакциями ...................................................... 440
6.2. КОНКУРИРУЮЩИЕ ТРАНЗАКЦИИ................................................................................. 449
6.2.1. Пример 43: управление уровнем изолированности транзакций......................... 449
6.2.2. Пример 44: взаимодействие конкурирующих транзакций ................................... 456
6.2.3. Пример 45: управление транзакциями в триггерах,
хранимых функциях и процедурах........................................................ 486
Раздел 7: РЕШЕНИЕ ТИПИЧНЫХ ЗАДАЧ
И ВЫПОЛНЕНИЕ ТИПИЧНЫХ ОПЕРАЦИЙ.................................................. 495
7.1. РАБОТА С ИЕРАРХИЧЕСКИМИ И СВЯЗАННЫМИ СТРУКТУРАМИ................. 495
7.1.1. Пример 46: формирование и анализ иерархических структур............................. 495
7.1.2. Пример 47: формирование и анализ связанных структур..................................... 512
7.2. ОПЕРАЦИИ С БАЗАМИ ДАННЫХ..................................................................................... 538
7.2.1. Пример 48: резервное копирование и восстановление базы данных................. 538
7.3. ОПЕРАЦИИ С СУБД.................................................................................................................. 544
7.3.1. Пример 49: управление пользователями, запуск и остановка СУБД ................. 544
7.3.2. Пример 50: определение и изменение кодировок.................................................... 547
Раздел 8: КРАТКОЕ СРАВНЕНИЕ MYSQL, MS SQL SERVER, ORACLE ............. 550
Раздел 9: ЛИЦЕНЗИЯ И РАСПРОСТРАНЕНИЕ ................................................................ 554
5
ПРЕДИСЛОВИЕ
Выражаю огромную благодарность коллегам из
ЕРАМ Systems за ценные замечания и рекомендации
в процессе подготовки материала.
Эта книга посвящена практике использования SQL для решения типичных задач. Здесь не
рассматривается теория реляционных баз данных (предполагается, что вы с ней знакомы либо
способны найти недостающую информацию), но приведено более 500 SQL-запросов: от элемен­
тарных выборок до использования представлений, триггеров, хранимых процедур и функций.
Все примеры представлены в виде постановки задачи и её решения с использованием MySQL,
MS SQL Server и Oracle, а также снабжены пояснениями и разбором типичных ошибок.
Этот материал в первую очередь будет полезен тем, кто:
• когда-то изучал базы данных, но многое забыл;
• имеет опыт работы с одной СУБД, но хочет быстро переключиться на другую;
• хочет в предельно сжатые сроки научиться писать типичные SQL-запросы.
Все решения выполнены на MySQL Community Server 5.6, Microsoft SQL Server Express 2012,
Oracle 1 lgR2 Express Edition и, скорее всего, успешно будут работать на более новых версиях этих
СУБД, но не на более старых.
В большинстве решений со сложной логикой алгоритм пояснён на примере MySQL, а для двух
других СУБД лишь приведён код с небольшими комментариями, потому желательно рассматри­
вать решения для всех трёх СУБД, даже если вам интересна только одна из них.
Исходные материалы (схемы, скрипты и т.д.) можно получить по этой ссылке: http://svyatoslav.
biz/database_book_download/src.zip
Условные обозначения, используемые в этой книге:
Постановка задачи. Сразу отметим, что почти в каждом рассмотренном примере при­
ведено несколько задач. Поскольку решение задачи в некоторых случаях будет разме­
щено далеко от её формулировки, рядом с номером задачи в фигурных скобках будет
приведена 1ссылка1 на решение.
Ожидаемый результат. Сначала мы будем показывать, что должно получаться при
правильном решении задачи, а потом — само решение.
Решение задачи. Решение всегда будет приведено для MySQL, MS SQL Server и Oracle.
Иногда решения будут похожими, иногда очень разными. Поскольку решение задачи
в некоторых случаях будет размещено далеко от её формулировки, рядом с номером
решения в фигурных скобках будет приведена 1ссылка1 на формулировку задачи.
Исследование. Это — тоже задача, но уже не на получение неких данных, а на проверку
того, как ведёт себя СУБД в некоторых условиях.
Предостережение. Мы будем рассматривать типичные ошибки и приводить поясне­
ния их причин и последствий.
Задание для самостоятельной проработки. Настоятельно рекомендуется выполнять
эти задания. Даже если вам кажется, что всё очень просто. Если, напротив, вам кажет­
ся, что всё очень сложно — сначала попробуйте самостоятельно решить уже разобран­
ные примеры, обращаясь к готовому решению лишь для самопроверки.
6
МОДЕЛЬ, ГЕНЕРАЦИЯ
И НАПОЛНЕНИЕ
БАЗЫ ДАННЫХ
ОБЩЕЕ ОПИСАНИЕ МОДЕЛИ* •
Для решения задач и рассмотрения примеров мы будем использовать три базы данных: «Биб­
лиотека», «Большая библиотека» и «Исследование». База данных «Исследование» будет состоять
из множества разрозненных таблиц, необходимых для демонстрации особенностей поведения
СУБД, и мы будем формировать её постепенно по мере проведения экспериментов. Также в ней
будет физически расположена база данных «Большая библиотека».
Модели баз данных «Библиотека» и «Большая библиотека» полностью идентичны (отличают­
ся эти базы данных только количеством записей). Здесь всего семь таблиц:
• genres — описывает литературные жанры:
° g_id — идентификатор жанра (число, первичный ключ);
° g_name — имя жанра (строка);
• books — описывает книги в библиотеке:
° b_id — идентификатор книги (число, первичный ключ);
° b_name — название книги (строка);
° Ь_уеаг — год издания (число);
0 b_quantity — количество экземпляров книги в библиотеке (число);
• authors — описывает авторов книг:
0 a_id — идентификатор автора (число, первичный ключ);
0 а_паше — имя автора (строка);
• subscribers — описывает читателей (подписчиков) библиотеки:
0 s_id — идентификатор читателя (число, первичный ключ);
0 s_name — имя читателя (строка);
• su bscription s — описывает факты выдачи/возврата книг (т.н. «подписки»):
0 sb_id — идентификатор подписки (число, первичный ключ);
0 sb_subscriber — идентификатор читателя (подписчика) (число, внешний ключ);
0 sb_book — идентификатор книги (число, внешний ключ);
0 sb _start — дата выдачи книги (дата);
■ ■ ■ Ill 1.1. ОБЩЕЕ ОПИСАНИЕ МОДЕЛИ 7
° sb_finish — запланированная дата возврата книги (дата);
° sb _is_active — признак активности подписки (содержит значение Y, если книга
ещё на руках у читателя, и N, если книга уже возвращена в библиотеку);
• m2m_books_genres — служебная таблица для организации связи «многие ко многим»
между таблицами books и genres:
° b_id — идентификатор книги (число, внешний ключ, часть составного первичного
ключа);
° g_id — идентификатор жанра (число, внешний ключ, часть составного первичного
ключа);
• m2m_books_authors — служебная таблица для организации связи «многие ко многим»
между таблицами books и authors:
0 b__id — идентификатор книги (число, внешний ключ, часть составного первичного
ключа);
0 a_id — идентификатор автора (число, внешний ключ, часть составного первичного
ключа).
В таблицах m2m_books_genres и m2m_books_authors оба внешних ключа входят в со­
ставной первичный ключ, чтобы исключить ситуацию, когда, например, принадлежность неко­
торой книги некоторому жанру будет указана более одного раза (такие ошибки приводят к не­
верной работе запросов вида «посчитать, к скольким жанрам относится книга»).
Общая схема базы данных представлена на рисунке І.а. Скрипты генерации баз данных для
каждой СУБД вы можете найти в исходном материале, ссылка на который приведена в преди­
словии^.
subscriptions [=! subscribers 1=1
books 1=1 «column» «column»
*PK sb id
genres «column» * sb_subscriber * s_name
*PK b id * sb_book
«column» * bname * sb_start
*РК gjd * byear * sb_finish
* a name * b quantity * sb_is_active
m2m_books_genres 1
authors
m2m books authors
«column»
*PK bjd «column» «column»
*PK gjd *PK bjd *PK ajd
* a name
*PK a id
Рисунок l.a Общая схема базы данных
8 Раздел 1: МОДЕЛЬ, ГЕНЕРАЦИЯ И НАПОЛНЕНИЕ БАЗЫ ДАННЫХ ІІІН Н
МОДЕЛЬ ДЛЯ MYSQL
Модель базы данных для MySQL представлена на рисунках І.Ь и І.с. Обратите внимание на
следующие важные моменты:
• Первичные ключи представлены беззнаковыми целыми числами для расширения макси­
мального диапазона значений.
• Строки представлены типом varchar длиной до 150 символов (чтобы гарантированно уло­
житься в ограничение 767 байт на длину индекса; 150* *4 = 600, MySQL выравнивает символы
UTF-строк на длину в четыре байта на символ при операциях сравнения).
• Поле sb_is_active представлено характерным для MySQL типом данных епшп (позво­
ляющим выбрать одно из указанных значений и очень удобным для хранения заранее из­
вестного предопределённого набора значений — YhNb нашем случае).
• Поле g_name сделано уникальным, т.к. существование одноимённых жанров недопустимо.
• Поля sb _ start и sb_finish представлены типом date (а не более полными, например,
datetim e), т.к. мы храним дату выдачи и возврата книги с точностью до дня.
Рисунок І.Ь — Модель базы данных для MySQL в Sparx ЕА
■ ■ ■ Ill 1.3. МОДЕЛЬ ДЛЯ MS SQL Server 9
genres books
gjd INT(IO) bjd INT(IO)
О g_name VARCHAR(150) О b_name VARCHAR(150) — ,
О b_year SMALLINT(5) l-h- __I subscribers ▼
subscriptions
sjd INT(IO)
PRIMARY О b_quantity SMALLINT(5) sbjd INT(IO)
О s_name VARCHAR(150)
UQ_genres_g_name Ф sb_subscriber INT(10)
Indexes
PRIMARY
♦ sb_book INT(10)
PRIMARY
Ф sb_start DATE
-l< Ф sb_finish DATE > i-J
О sbjs_active ENUM(Y,'N')
PRIMARY
A
FK_subscriptions_books
m2m_books_genres ▼ FK_subscriptions_subscribers
bjd INT(IO)
_l authors
t gjd INT(IO)
A ajd IMT(10)
m2m_books_authors ▼ О a_name VARCHAR(150)
PRIMARY
FK_m2m_books_genres_genres t bjd INT(IO) Indexes
PRIMARY
t ajd INT(IO)
PRIMARY
FK m2m books authors authors
Рисунок l.c — Модель базы данных для MySQL в MySQL Workbench
МОДЕЛЬ ДЛЯ MS SQL SERVER
Модель базы данных для MS SQL Server представлена на рисунках l.d и І.е. Обратите внима­
ние на следующие важные моменты:
• Первичные ключи представлены знаковыми целыми числами, т.к. в MS SQL Server нет воз­
можности сделать b igin t, in t и sm allin t беззнаковыми (a tin y in t, наоборот, бывает
только беззнаковым).
• Строки представлены типом nvarchar длиной до 150 символов (как из соображений ана­
логии с MySQL, так и чтобы гарантированно уложиться в ограничение 900 байт на длину
индекса; 150*2 = 300, MS SQL Server для хранения и сравнения символов в национальных
кодировках использует два байта на символ).
• Поле sb _is_active представлено типом char длиной в один символ (т.к. в MS SQL Server
нет типа данных епшп), а для соблюдения аналогии с MySQL на это поле наложено ограни­
чение check со значением [sb _is_active] IN (' Y' , 'N1) .
• Как и в MySQL, поля sb _start и sb_finish представлены типом date (а не более полны­
ми, например, datetim e), т.к. мы храним дату выдачи и возврата книги с точностью до дня.