Рецепты SQL-запросов на все случаи
и для всех баз данных  

Энтони Молинаро SQL. Сборник рецептов Издательство "Символ Плюс" (www.symbol.ru), СПб.-Москва; 2009; 672 стр.; 17x24 см; тир.2000; ISBN-13: 978-5-93286-125-7; ISBN-10: 5-93286-125-8; перевод с англ. Н.Шатохиной; научный редактор К.Козлинский Решения и методики построения запросов для разработчиков баз данных Аннотация от изд. "Символ Плюс" http://www.symbol.ru/alphabet/608269.html Оригинал: SQL Cookbook, by Anthony Molinaro. O’Relly Media Inc. 2006, ISBN 0-596-00976-2 (англ)

Категория: базы данных Уровень подготовки читателей: средний Включает решения для SQL Server, PostgreSQL, Oracle, MySQL и DB2

SQL «также неисчерпаем, как и атом»
Вл. Ильин «Материализм и эмпириокритицизм» (1909)

«Большинство разработчиков используют SQL
лишь формально и даже не подозревают о той мощи,
которая имеется в их распоряжении.»
Энтони Молинаро «SQL. Сборник рецептов» (2006)

Расхожее мнение: «Управлять государством и лечить людей от всех болезней – умеют все». Так и программировать на языке SQL умеют все. Кто-то разрабатывает программы на C++, кто-то на Delphi, на Java, на …, или даже на PL/SQL. И все так отлично знают обычный SQL, что даже как бы «стыдясь» его простоты, понятности и прозрачности, иногда городят такие громоздкие запросы, что процессы их выполнения зависают на много часов, пытаясь выяснить, какую же все-таки информацию разработчик хотел предоставить конечному пользователю. Для администраторов баз данных дело обстоит немного лучше. Существуют многие очень хорошие программные инструменты, которые буквально вскрывают базу данных, как банку с килькой, и высвечивают протекание процессов так прозрачно, как будто это изготовление яичницы :). Но надо отдавать себе отчет, что эти сильные действия стали возможными потому, что разработчики инструментария хорошо знали именно SQL. И еще. Каким бы продвинутым ни был применяемый инструментарий, пользователи, разработчики, АБД всегда находят вопросы к базе, которые не предусмотрены даже самым лучшим программным средством, ответы на которые может дать только непосредственный SQL-запрос. Из этого следует довольно простой и очевидный вывод, что язык SQL надо изучать и знать. А также надо знать, какие существуют книги, справочники, сборники рецептов (Cookbook), которые помогут, посоветуют, научат. Поэтому рекомендую познакомиться с книгой Энтони Молинаро «SQL. Сборник рецептов» .

Надо ли напоминать, что язык SQL (SQL:2003 — действующая в настоящее время пятая версия стандарта) является стандартом языка доступа для большинства реляционных баз данных. Это и Oracle (все релизы), и DB2, и SQL Server, и PostgreSQL, и MySQL, для которых непосредственно приведены рецепты (SQL-запросы) в рассматриваемой книге, но и многие квази- и пост- реляционные СУБД. SQL является также языком доступа к реляционным конструкциям нереляционных баз данных. Конечно, реализованная, встроенная в СУБД версия языка SQL одного вендора неизбежно, в том числе и по коммерческим причинам, отличается от SQL в СУБД других производителей, но все они – сестры, ну, может быть, двоюродные. Поэтому хорошее знание SQL для одной линии СУБД во много раз облегчает и ускоряет при необходимости переход на SQL другой линии. Это подтверждают примеры, приводимые в этой SQL Cookbook (сборнике рецептов, кулинарной книге). И наибольший интерес вызывают, конечно, различия в формулировании одного и того же запроса к различным базам данных. Таким образом, знакомясь с одной линией баз данных, становишься специалистом сразу по пяти воплощениям SQL.

Очень важно обозначить целевую аудиторию читателей, которым предназначена книга, а также что есть и чего нет в ней по выбору автора. «SQL. Сборник рецептов» адресован автором (и рецензент с этим полностью согласен):

• новичкам в SQL;
• разработчикам не на SQL;
• рядовым специалистам SQL;
• экспертам SQL.

В «SQL. Сборник рецептов» отсутствуют (выбор автора):

• описание данных;
• XML;
• объектно-ориентированные расширения SQL;
• Теоретические моменты;
• политика производителей;
• политика ANSI;
• политика наследования.

Все остальное, как по мнению рецензента, так и автора аннотации к книге, в той-или-иной степени присутствует: «… как применять оконные функции и специальные операторы, а также расширенные методы работы с хранилищами данных: создание гистограмм, резюмирование данных, выполнение агрегации скользящего диапазона значений, формирование текущих сумм и подсумм. … как разворачивать строки в столбцы и наоборот, упрощать вычисления внутри строки и выполнять двойное разворачивание результирующего множества, выполнять обход строки, что позволяет использовать SQL для синтаксического разбора строки на символы, слова или элементы строки с разделителями. Приемы, предлагаемые автором, позволят оптимизировать код ваших приложений и откроют перед вами новые возможности языка SQL» !

Мой настоятельный совет: очень внимательно ознакомиться с оглавлением этой поваренной книги. В нем перечислены, конечно, не все, но очень многие рецепты. По названиям «кушаний» легко можно понять, насколько полезна эта книга для изучения и использования в повседневной жизни SQL-программиста и АБД Oracle, DB2, SQL Server, PostgreSQL, MySQL. Отмечу лишь несколько рецептов, которые должны заинтересовать любого SQL-программиста: «Возвращение n случайных записей таблицы», «Сортировка смешанных буквенно-цифровых данных», «Поиск одинаковых строк в двух таблицах», «Идентификация и устранение некорректного использования декартова произведения», «Выявление строк, которые могут быть интерпретированы как числа»,… Глава 9 «Работа с датами» в руссом переводе выложена на сервере изд. «Символ Плюс» по адресу http://www.books.ru/chapter?id=608269 . Я хочу привести отзыв Дж.Д. Херрингтона:
"Это замечательная книга для всех, кто пишет на SQL. Приобретите ее немедленно. О, вам нужна причина? Хорошо. Глава, рассказывающая о том, как эффективно работать с иерархическими данными (бич всех SQL-программистов), уже стоит потраченных денег. Ах да, все остальные главы тоже хоть куда".
Я с ним согласен.

В заключение этой заметки я хочу обратить внимание читателей на мнение специалистов компании "ФОРС", которые изучают и тестируют oracle-приложения пользователей на современном много серверном программно-аппаратном комплексе Exadata Database Machine. Кратко оно звучит примерно так: "Чем больше в программе SQL, тем успешнее применяются достоинства Exadata, тем лучше масштабируется приложение за счет распараллеливания SQL, тем быстрее и экономнее выполняется приложение".

Книга «SQL. Сборник рецептов» имеется в интернет-магазинах, а на сайте издательства по адресу http://www.books.ru/books/sql-sbornik-retseptov-fail-pdf-626980/?show=1 можно приобрести ее в pdf-формате.

На мой взгляд, она представляет значительный интерес для любого SQL-специалиста – от студента до профессионала высокого уровня.

Анатолий Бачин,
интернет-журнал “FORS Magazine”
главный редактор

Приложение 1.

Полное оглавление книги Энтони Молинаро «SQL. Сборник рецептов»

Предисловие

Глава 1. Извлечение записей

• Извлечение всех строк и столбцов из таблицы
• Извлечение подмножества строк из таблицы
• Выбор строк по нескольким условиям
• Извлечение подмножества столбцов из таблицы
• Как задать столбцам значимые имена
• Обращение к столбцу в предикате WHERE по псевдониму
• Конкатенация значений столбцов
• Использование условной логики в выражении SELECT
• Ограничение числа возвращаемых строк
• Возвращение n случайных записей таблицы
• Поиск значений NULL
• Преобразование значений NULL в не-NULL значения
• Поиск по шаблону

Глава 2. Сортировка результатов запроса

• Возвращение результатов запроса в заданном порядке
• Сортировка по нескольким полям
• Сортировка по подстрокам
• Сортировка смешанных буквенно-цифровых данных
• Обработка значений NULL при сортировке
• Сортировка по зависящему от данных ключу

Глава 3. Работа с несколькими таблицами

• Размещение одного набора строк под другим
• Объединение взаимосвязанных строк
• Поиск одинаковых строк в двух таблицах
• Извлечение из одной таблицы значений, которых нет в другой таблице
• Извлечение из таблицы строк, для которых нет соответствия в другой таблице
• Независимое добавление объединений в запрос
• Выявление одинаковых данных в двух таблицах
• Идентификация и устранение некорректного использования декартова произведения
• Осуществление объединений при использовании агрегатных функций
• Внешнее объединение при использовании агрегатных функций
• Возвращение отсутствующих данных из нескольких таблиц
• Значения NULL в операциях и сравнениях

Глава 4. Вставка, обновление, удаление

• Вставка новой записи
• Вставка значений по умолчанию
• Переопределение значения по умолчанию значением NULL
• Копирование строк из одной таблицы в другую
• Копирование описания таблицы
• Вставка в несколько таблиц одновременно
• Блокировка вставки в определенные столбцы
• Изменение записей в таблице
• Обновление в случае существования соответствующих строк в другой таблице
• Обновление значениями из другой таблицы
• Слияние записей
• Удаление всех записей из таблицы
• Удаление определенных записей
• Удаление одной записи
• Удаление записей, которые нарушают ссылочную целостность
• Уничтожение дублирующихся записей
• Удаление записей, на которые есть ссылки в другой таблице

Глава 5. Запросы на получение метаданных

• Как получить список таблиц схемы
• Как получить список столбцов таблицы
• Как получить список индексированных столбцов таблицы
• Как получить список ограничений, наложенных на таблицу
• Как получить список внешних ключей без соответствующих индексов
• Использование SQL для генерирования SQL
• Описание представлений словаря данных в базе данных Oracle

Глава 6. Работа со строками

• Проход строки
• Как вставить кавычки в строковые литералы
• Как подсчитать, сколько раз символ встречается в строке
• Удаление из строки ненужных символов
• Разделение числовых и символьных данных
• Как определить, содержит ли строка только буквенно-цифровые данные
• Извлечение инициалов из имени
• Упорядочивание по частям строки
• Упорядочивание по числу в строке
• Создание списка с разделителями из строк таблицы
• Преобразование данных с разделителями в список оператора IN со множеством значений
• Упорядочение строки в алфавитном порядке
• Выявление строк, которые могут быть интерпретированы как числа
• Извлечение n-ной подстроки с разделителями
• Синтаксический разбор IP-адреса

Глава 7. Работа с числами

• Вычисление среднего
• Поиск минимального/максимального значения столбца
• Вычисление суммы значений столбца
• Подсчет строк в таблице
• Подсчет значений в столбце
• Вычисление текущей суммы
• Вычисление текущего произведения
• Вычисление текущей разности
• Вычисление моды
• Вычисление медианы
• Вычисление доли от целого в процентном выражении
• Агрегация столбцов, которые могут содержать NULL-значения
• Вычисление среднего без учета наибольшего и наименьшего значений
• Преобразование буквенно-цифровых строк в числа
• Изменение значений в текущей сумме

Глава 8. Арифметика дат

• Добавление и вычитание дней, месяцев и лет
• Определение количества дней между двумя датами
• Определение количества рабочих дней между двумя датами
• Определение количества месяцев или лет между двумя датами
• Определение количества секунд, минут или часов между двумя датами
• Как подсчитать, сколько раз в году повторяется каждый день недели
• Определение интервала времени в днях между текущей и следующей записями

Глава 9. Работа с датами

• Как определить, является ли год високосным
• Как определить количество дней в году
• Извлечение единиц времени из даты
• Определение первого и последнего дней месяца
• Выбор всех дат года, выпадающих на определенный день недели
• Определение дат первого и последнего появления заданного дня недели
• Создание календаря
• Получение дат начала и конца кварталов года
• Определение дат начала и окончания заданного квартала
• Дополнение отсутствующих дат
• Поиск по заданным единицам времени
• Сравнение строк по определенной части даты
• Выявление наложений диапазонов дат

Глава 10. Работа с диапазонами данных

• Поиск диапазона последовательных значений
• Вычисление разности между значениями строк одной группы или сегмента
• Определение начала и конца диапазона последовательных значений
• Вставка пропущенных значений диапазона
• Формирование последовательности числовых значений

Глава 11. Расширенный поиск

• Разбиение результирующего множества на страницы
• Как пропустить n строк таблицы
• Использование логики OR во внешних объединениях
• Выявление строк со взаимообратными значениями
• Как выбрать записи с n-ым количеством наивысших значений
• Как найти записи с наибольшим и наименьшим значениями
• Сбор информации из последующих строк
• Смещение значений строк
• Ранжирование результатов
• Исключение дубликатов
• Ход конем
• Формирование простых прогнозов

Глава 12. Составление отчетов и управление хранилищами данных

• Разворачивание результирующего множества в одну строку
• Разворачивание результирующего множества в несколько строк
• Обратное разворачивание результирующего множества
• Обратное разворачивание результирующего множества в один столбец
• Исключение повторяющихся значений из результирующего множества
• Разворачивание результирующего множества для упрощения вычислений
• Создание блоков данных фиксированного размера
• Создание заданного количества блоков
• Создание горизонтальных гистограмм
• Создание вертикальных гистограмм
• Как возвратить столбцы, не перечисленные в операторе GROUP BY
• Вычисление простых подсумм
• Вычисление подсумм для всех возможных сочетаний
• Как выявить строки, в которых представлены не подсуммы
• Использование выражений CASE для маркировки строк
• Создание разреженной матрицы
• Группировка строк по интервалам времени
• Агрегация разных групп/сегментов одновременно
• Агрегация скользящего множества значений
• Разворачивание результирующего множества, содержащего подсуммы

Глава 13. Иерархические запросы

• Представление отношений родитель-потомок
• Представление отношений потомок-родитель-прародитель
• Создание иерархического представления таблицы
• Выбор всех дочерних строк для заданной строки
• Определение узлов: ветвления, концевого, корневого

Глава 14. Всякая всячина

• Создание отчетов с перекрестными ссылками с помощью оператора SQL Server PIVOT
• Обратное разворачивание отчета с помощью оператора SQL Server UNPIVOT
• Транспонирование результирующего множества с помощью оператора Oracle MODEL
• Извлечение элементов строки, положение которых в строке неизвестно
• Как определить количество дней в году (альтернативное решение для Oracle)
• Поиск смешанных буквенно-цифровых строк
• Преобразование целых чисел в их двоичное представление с использованием Oracle
• Разворачивание ранжированного результирующего множества
• Как добавить заголовок столбца в дважды развернутое результирующее множество
• Преобразование скалярного подзапроса в составной подзапрос (Oracle)
• Синтаксический разбор сериализованных данных в строки таблицы
• Определение доли от целого в процентном выражении
• Создание списка разделенных запятыми значений в Oracle
• Выбор текста, не соответствующего шаблону (Oracle)
• Преобразование данных с помощью вложенного запроса
• Проверка существования значения в группе

Приложение A. Оконные функции, краткий обзор
Приложение B. Вспоминаем Розенштейна
Алфавитный указатель