ПРЕДИСЛОВИЕ ............................................................................................................................ 30

Для кого предназначена эта книга? ......................................................................................... 31

Чего нет в этой книге? ............................................................................................................... 31

Платформы и версии SQL ............................................................................................................. 32

Таблицы, используемые в книге .................................................................................................. 32

Соглашения, используемые в книге ............................................................................................. 34

Типографские соглашения ......................................................................................................... 34

Соглашения по написанию кода ............................................................................................... 34

Благодарности за второе издание ............................................................................................ 35

Благодарности за первое издание ........................................................................................... 36

ГЛАВА 1. ИЗВЛЕЧЕНИЕ ЗАПИСЕЙ ............................................................................................... 39

1.1. Извлечение из таблицы всех строк и столбцов ............................................................... 39

1.2. Извлечение из таблицы подмножества строк ................................................................. 40

1.3. Возвращение строк по нескольким условиям ................................................................ .40

1.4. Извлечение из таблицы подмножества столбцов ........................................................... 41

1.5. Задание столбцам значимых имен .................................................................................... 42

1.6. Обращение к столбцу в предикате WHERE по его псевдониму ................................... 43

1.7. Конкатенация значений столбцов ..................................................................................... 44

1.8. Использование условной логики в операторе SELECT......................................................45

1.9. Ограничение числа возвращаемых строк ....................................................................46

1.10. Извлечение из таблицы произвольных записей ......................................................48

1.11. Поиск значений NULL...............................................................................................50

1.12. Преобразование значений NULL в реальные значения ........................................50

1.13. Поиск по шаблону.....................................................................................................51

1.14. Подведем итоги .......................................................................................................52

ГЛАВА 2. СОРТИРОВКА РЕЗУЛЬТАТОВ ЗАПРОСА .............................................................. 53

2.1. Возвращение результатов запроса в заданном порядке........................................53

2.2. Сортировка по нескольким столбцам .........................................................................54

2.3. Сортировка по подстрокам .......................................................................................... 55

2.4. Сортировка смешанных буквенно-цифровых данных ................................................. 56

2.5. Обработка значений NULL при сортировке ..... ...........................................................60

2.6. Сортировка по ключу, зависящему от данных.................................................................66

2.7. Подведем итоги ............................................................................................................. 67

ГЛАВА 3. РАБОТ А С НЕСКОЛЬКИМИ ТАБЛИЦАМИ ................................................................ 68

3.1. Размещение одного набора строк над другим ................................................................68

3.2. Объединение взаимосвязанных строк... ....................................................................... 70

3.3. Поиск строк с общими данными в двух таблицах ........................................................... 72

3.4. Извлечение из одной таблицы значений, отсутствующих в другой ............................... 74

3.5. Извлечение строк из таблицы, не соответствующих строкам

в другой таблице ................................................................................................................... 80

3.6. Добавление в запрос независимых объединений ............................................................ 81

3.7. Проверка двух таблиц на идентичность .......................................................................... 84

3.8. Выявление и устранение проблемы декартовых произведений .................................. 91

3.9. Выполнение объединений при использовании агрегатных функций ........................ 93

3.10. Выполнение внешних объединений при использовании агрегатных функций ........ 97

3.11. Возвращение отсутствующих данных из нескольких таблиц .................................. 101

3.12. Значения NULL в вычислениях и сравнениях ............................................................ 105

3. 13. Подведем итоги ................................................................................................................. 106

ГЛАВА 4. ВСТАВКА, ОБНОВЛЕНИЕ И УДАЛЕНИЕ ЗАПИСЕЙ ......................................................... 107

4.1. Вставка новой записи ........................................................................................................ 107

4.2. Вставка значений по умолчанию ... . ...... . ..... .. .............................................................. 108

4.3. Переопределение значения по умолчанию значением NULL ...................... . ... ... ... 110

4.4. Копирование строк одной таблицы в другую ...... ................................................... 11 О

4.5. Копирование определения таблицы .. ..................................... .......... ..... .........111

4.6. Вставка строк одновременно в несколько таблиц ......... ... ..... ....... ....................... 112

4.7. Блокировка вставки данных в определенные столбцы .............................................. 114

4.8. Изменение зап11сей в таблице ........................................................................................... 115

4.9. Обновление при условии нал11ч11я соответствующих строк . .................................... 117

4.10. Обновление значениями из другой таблицы ............................................................... 118

4.11. Слияние записей ...... . ..... . ..... . ............ . .. ... .......... . ....... . ... ... .... ... ............................ 122

4.12. Удаление всех записей таблицы .............................................. . ..... . .... . .................... ..123

4.13. Удаление определенных записей ................................... . ... . ..................................... . .. 124

4.14. Удаление одной записи ............................................................... . .............. . ............... ..124

4.15. Удаление строк, нарушающих ссылочную целостность ..................................... .. .. 125

4.16. Удаление дубликатов записей ................................. . ................................................... .126

4.17. Удаление записей, на которые есть ссылки из другой таблицы ............................. 128

4.18. Подведем итоги ................................................. . .............................................................. 129

ГЛАВА 5. ЗАПРОСЫ НА ПОЛУЧЕНИЕ МЕТАДАННЫХ ................................ 130

5.1. Создание списка таблиц схемы ................................................................................ ... ... 130

5.2. Создание списка столбцов таблицы ................................................................................ 131

5.3. Создание списка индексированных столбцов таблицы .............................................. 132

5.4. Создание списка ограничений таблицы ......................................................................... 134

5.5. Создание списка внешних ключей без соответствующих индексов ......................... 136

5.6. Генерирование кода SQL с помощью средств SQL ...................................................... 139

5.7. Описание представлений словаря данных в базе данных Oracle .............................. 141

5.8. Подведем итоги ................................................................................................................... 143

ГЛАВА 6. РАБОТ А СО СТРОКАМИ .................................................................... .144

6.1. Проход строки ..................................................................................................................... 144

6.2. Вставка кавычек в строковые константы ..................................................................... 147

6.3. Подсчет количества вхождений в строку определенного символа ........................... 148

6.4. Удаление символов из строки .......................................................................................... 149

6.5. Разделение цифровых и символьных данных ............................................................... 150

6.6. Определение, содержит ли строка только буквенно-цифровые символы ............... 155

6.7. Извлечение инициалов из имен ....................................................................................... 160

6.8. Сортировка по подстрокам ............................................................................................... 164

6.9. Сортировка по числу в строке ......................................................................................... 165

6.10. Создание из строк таблицы списка с разделителями ............... .... .. ... ....172

6.11. Преобразование данных с разделителями в многозначный список

оператора /N .. ................ .... ....................................... ... .. .... 175

6.12. Упорядочение строки по алфавиту...................... ... ...................181

6.13. Идентификация числовых подстрок в строке ... .. ... ... ............ .. . .. ........187

6.14. Излечение п-й подстроки из списка с разделителями .. . .. ............ ... ........194

6.15. Парсинг IР-адреса ................. . ................ . ... . ..... . ... . ......................................... . ............ 201

6.16. Сравнение строк по их звучанию .. ... . ... ..... ........ ..... .. ................. .. ...204

6.17. Обнаружение текста, не совпадающего с шаблоном .... .. ..... .. .... .. ....... .. .205

6.18. Подведем итоги ............ . ....................... . ........................................ . ................................ 208

ГЛАВА 7. ОПЕРАЦИИ С ЧИСЛАМИ ............................................................ . ...... 209

7 .1. Вычисление среднего ............ . ..................................... . .. . ........ . ................................ .. .. 209

7.2. Определение минимального и/или максимального значения столбца.. ... . .. .....Zll

7.3. Суммирование значений столбца........ .. ........ ...... ........ ...... .. .. ... .. .... .. ...213

7.4. Подсчет строк в таблице .......... .. ...... .. ....... .. ...... ... ..... .. . .. ... ..... ....215

7.5. Подсчет значений столбца .... ... ... ..... .................... . .... . .. .. ...... . .. ... ... . .. . ... .......217

7.6. Вычисление текущей суммы ... ... ............ ... ............. .... .. . ........ ... ......... .. .. .. ....... ..218

7.7. Вычисление текущего произведения.. ..... . ..... .. ... . ..... ... ...... . ..... . .. ..... ................. 219

7.8. Сглаживание последовательности значений ................................................................ 220

7.9. Вычисление моды ................................................................. . ................. . ......................... 222

7.10. Вычисление медианы ......................................................... . .................................... . ...... 225

7.11. Вычисление процентной доли от целого. ... .. ........ . ...... .. ....... ....... ......... .......227

7.12. Агрегация столбцов, содержащих значения NULL ... ....... ..... ..... .............230

7.13. Вычисление среднего без учета крайних значений .................................................... 231

7.14. Преобразование буквенно-цифровых строк в числа ................................................. 233

7.15. Изменение значений в текущей сумме ......................................................................... 235

7.16. Находим выбросы, используя среднее абсолютное отклонение .............................. 237

7.17. Обнаруживаем аномальные значения, используя закон Бенфорда ........................ 241

7.18. Подведем итоги ................................................................................................................. 243

ГЛАВА 8. АРИФМЕТИЧЕСКИЕ ОПЕРАЦИИ С ДАТАМИ .............................. 244

8.1. Сложение и вычитание дней, месяцев и лет .................................................................. 244

8.2. Вычисление количества дней между двумя датами .................................................... 247

8.3. Вычисление количества рабочих дней между двумя датами ..................................... 249

8.4. Вычисление количества месяцев или лет между двумя датами ................................ 254

8.5. Вычисление количества секунд, минут и часов между двумя датами ..................... 257

8.6. Вычисление повторений каждого дня недели в году ................................................... 259

8.7. Вычисление разницы в днях между датами двух записей .......................................... 272

8.8. Подведем итоги ................................................................................................................... 277

ГЛАВА 9. РАБОТА С ДАТАМИ ....................... . ........ . ........... . ................. ... ......... 278

9.1. Определение високосного года ......................................................................... . .............. 278

9.2. Определение количества дней в году... .. ............ .. .......... ..... .......... ... ..285

9.3. Извлечение из даты единиц времени ..................... . .... . ................................................. 288

9.4. Вычисление первого и последнего дней месяца .. .... ............................291

9.5. Вычисление дат определенного дня недели для всего года ........................................ 293

9.6. Вычисление дат первого и последнего вхождения заданного дня недели

9.7. Создание календаря ........................................................................................................... 307

9.8. Создание списка начальных и конечных дат кварталов года ................................... 321

9.9. Определение начальной и конечной дат для заданного квартала ............................ 326

9.10. Дополнение недостающих дат ........................................................................................ 333

9.11. Поиск по заданным единицам времени ........................................................................ 342

9.12. Сравнение записей по определенным частям даты ................................................... 343

9.13. Выявление наложений диапазонов дат ........................................................................ 347

9.14. Подведем итоги ................................................................................................................. 352

ГЛАВА 10. РАБОТА С ДИАПАЗОНАМИ ЗНАЧЕНИЙ ...................................... 353

10.1. Поиск диапазона последовательных значений ........................................................... 353

10.2. Вычисление разницы между значениями строк одной группы или сегмента ......... 356

10.3. Определение границ диапазона последовательных значений ................................. 363

10.4. Вставка пропущенных значений диапазона ................................................................ 366

10.5. Генерирование последовательных числовых значений ............................................ 370

10.6. Подведем итоги ................................................................................................................. 374

ГЛАВА 11. РАСШИРЕННЫЙ поиск. .. ... ...... ... .. . ........ . ........... . .. ...... ... ..375

11.1. Разбивка результирующего множества на страницы ................................................ 375

11.2. Пропускаем п строк таблицы ......................................................................................... 377

11.3. Использование логики ИЛИ во внешних объединениях ......................379

11.4. Определение строк со взаимообратными значениями......... .. ..... .. ..... .. ..381

11.5. Выборка первых п записей с наибольшими значениями .....................383

11.6. Выявление строк с наибольшим и наименьшим значениями..................384

11.7. Проверка значений из следующих строк...................................385

11.8. Смещение значений строк .................................................................................. 388

11.9. Ранжирование результатов....................................................390

11.10. Исключение дубликатов ...................................................391

11.11. Ход конем ......................................................... .. ...394

11.12. Создание простых прогнозов..............................................400

11.13. Подведем итоги ..........................................................408

ГЛАВА 12. СОСТАВЛЕНИЕ ОТЧЕТОВ И ФОРМАТИРОВАНИЕ

РЕЗУЛЬТИРУЮЩИХ МНОЖЕСТВ ........ ... .. ...... .. ... .. .... ... . ..... . ... .. ... ..409

12.1. Транспонирование результирующего множества в одну строку ..............409

12.2. Транспонирование результирующего множества в несколько строк..........412

12.3. Обратное транспонирование результирующего множества ...................417

12.4. Обратное транспонирование результирующего множества в один столбец.....419

12.5. Исключение повторяющихся значений из результирующего множества.......423

12.6. Транспонирование результирующего множества для облегчения вычислений

с несколькими строками .......................................................................................... . ..... 425

12.7. Создание блоков данных фиксированного размера...........................427

12.8. Создание предопределенного количества блоков данных......................429

12.9. Создание горизонтальных гистограмм ...................... .. ...............430

12.10. Создание вертикальных гистограмм.........................................432

12.11. Возвращение столбцов, не указанных в опера

12.12. Вычисление простых подсумм ..................................................................................... 438

12.13. Вычисление подсумм для всех возможных сочетаний ........................................... .441

12.14. Выделение строк, не содержащих подсумм ................................ . ............................. 452

12.15. Маркировка строк с помощью выражений CASE ................................................... 454

12.16. Создание разреженной матрицы ................................................................................. 456

12.17. Группирование строк по интервалам времени ......................................................... 457

12.18. Одновременная агрегация разных групп/сегментов ............................................... 462

12.19. Агрегирования скользящего диапазона значений ................................................... 463

12.20. Транспонирование результирующего множества, содержащего подсуммы ....... 471

12.21. Подведем итоги ............................................................................................................... 477

ГЛАВА 13. ИЕРАРХИЧЕСКИЕ ЗАПРОСЫ.............................478

13.1. Выражение отношений родитель-потомок ............................................................... 479

13.2. Выражение отношений потомок-родитель-прародитель .................................... 483

13.3. Создание иерархического представления таблицы ................................................... 488

13.4. Выборка всех дочерних строк заданной родительской строки ............................... 492

13.5. Определение концевых, неконцевых и корневых узлов .......................................... .494

13.6. Подведем итоги ................................................................................................................. 502

ГЛАВА 14. НЕСТАНДАРТНЫЕ подходы. .. . .............. .. ................................ 503

14.1. Создание кросс-табличных отчетов с помощью оператора SQL Server PIVOT ...... 503

14.2. Обратное транспонирование кросс-табличных отчетов с помощью оператора

SQL Server UNPIVOT ................................................................... ... ............................... 505

14.3. Транспонирование результирующего множества с помощью оператора Oracle

MODEL ............................................................................................................................... 507

14.4. Извлечение элементов строки, положение которых неизвестно ............................. 512

14.5. Вычисление количества дней в году (альтернативное решение для Oracle) ........ 515

14.6. Поиск смешанных буквенно-цифровых строк ........................................................... 516

14.7. Преобразование десятичных чисел в двоичные в Oracle ......................................... 519

14.8. Транспонирование ранжированного результирующего множества ....................... 522

14.9. Добавление заголовка столбца в дважды развернутое результирующее

множество .......................................................................................................................... 526

14.10. Преобразование скалярного подзапроса в составной подзапрос в Oracle ........... 539

14.11. Парсинг сериалюова11ных данных в строки ............................................................ 541

14.12. Вычисление процентной доли от целого .................................................................... 546

14.13. Проверка на наличие в группе значений определенного значения ............ . ..... ..548

14.14. Подведем итоги .. . ............ . .... . ............ .. ..... . ... . ........................................................... 552

ПРИЛОЖЕНИЕ 1. КРАТКИЙ ОБЗОР ОКОННЫХ ФУНКЦИЙ .............................................................. 553

Группировка ................................................................................................................................. 553

Определение группы в SQL ........................................................................................................ 554

Группы не могут быть пустыми .............................................................................................. 555

Группы уникальны ................................................................................................................... 555

Значение COUNT никогда не равно нулю ............................................................................ 558

Парадоксы .................................................................................................................................... 558

Взаимосвязь между SELECTи CJROUP ВУ ............................................................................... 562

Оконные функции ....................................................................................................................... 565

Простой пример ........................................................................................................................ 565

Порядок обработки ................................................................................................................... 566

Сегменты ................................................................................................................................... 567

Воздействие значений NULL ................................................................................................... 569

Когда порядок имеет ·1начение ................................................................................................ 570

Оператор кадрирования ........................................................................................................... 572

Текущая сумма RUN_TOTAU ................................................................................... 573

Текущая сумма RUN_TOTALЗ ................................................................................... 574

Текущая сумма RUN_TOTAL.:/ ................................................................................... 574

Завершаем рассмотрение вопроса кадрирования .................................................................. 574

Столбец MIN 1 ............................................................................................................. 575

Столбец МАХ! ............................................................................................................. 575

Столбцы MIN2 и МАХ2 .............................................................................................. 575

Столбцы М!NЗ и МАХЗ .............................................................................................. 576

Столбец МАХ.:/ ............................................................................................................. 576

Удобочитаемость+ производительность= мощь .................................................................. 576

Запросы «В стиле отчетов» ...................................................................................................... 578

ПРИЛОЖЕНИЕ 2. ПОДЗАПРОСЫ И ОБОБЩЕННЫЕ

ТАБЛИЧНЫЕ ВЫРАЖЕНИЯ .. ...... .............................. ...... .... ............ .. 581

Подзапросы .................................................................................................................................. 581

Обобщенные табличные выражения ......................................................................................... 581

Подведем итоги ............................................................................................................................ 583

ПРЕДМЕТНЫЙ УКАЗАТЕЛЬ...... .......... . ........ ..... .. ........... ..... . ..... . ... ... 585

 Рассмотрены готовые рецепты для решения практических задач при работе с СУБД Oracle, DB2, SQL Server, MySQL и PostgreSQL. Описаны извлечение записей из таблиц, сортировка результатов запросов, принципы работы с несколькими таблицами, обработка запросов с метаданными. Рассказывается о способах поиска данных средствами SQL,о составлении отчетов и форматировании результирующих множеств, работе с иерархическими запросами. Рассматривается использование оконных функций, обобщенных табличных выражений (ОТВ), сбор данных в блоки, формирование гистограмм, текущих сумм и подсумм, агрегация скользящего диапазона значений. Описан обход строки и ее синтаксический разбор на символы, приведены способы упрощения вычислений внутри строки.

 Во втором издании учтены все изменения в синтаксисе и архитектуре актуальных реализаций SQL.

  Для программистов, разработчиков и администраторов баз данных

 Второе издание включает:

• Полностью обновленные рецепты, учитывающие более широкое использование оконных функций в SQL-приложениях

• Дополнительные примеры, показывающие обширное использование обобщенных табличных выражений (ОТВ) для создания более удобочитаемых и простых решений

• Новые решения, которые делают SQL более полезным для пользователей, не являющихся экспертами в области СУБД, включая специалистов по работе с данными