SQL. Сборник рецептов

668 Pages·2009·3.533 MB·Russian

by  Молинаро Э.

д
Решения и методики построения запросов ля Вк
S л
для разработчиков баз данных OrQL юч
SQL. Сборник рецептов acle, SMeyrSveаr,е тP oрешен
Звоадзмачоаж: нВоыс зтниа ентее в о пснолонвыой я змыекрае .з аВпыр хоосотив тSеQ нLа, нучои птоьнсия мпареитме,е чнтяот ьи сSпQоLл дьзлуяе ртее шегео- S QL и DstgreSия
ния более широкого спектра задач – от использования внутри баз данных, до Q B2QL
передачи данных по сети в приложения. И вам хотелось бы повысить уровень ,
своих SQL-навыков.
Решение: Откройте эту книгу. Прочитайте рецепты, посвященные интересующему вас типу
L
задач. Изучите и применяйте описанные методики.
SQL – обманчиво простой язык. Можно начинать с простого выражения: SELECT <столбцы>
FROM <таблица> WHERE <условия>. Многие не идут дальше этого, а ведь этот язык предо- .
ставляет значительно более широкие возможности. В своей книге «SQL. Сборник рецептов»
С
опытный разработчик Энтони Молинаро демонстрирует возможности SQL и делится свои-
ми приемами работы. Вы научитесь:
б
• Использовать оконные функции – вероятно, самое значимое нововведение в SQL за
о
последние годы (вы многое теряете, если их не применяете!).
• Использовать преимущества мощных специальных возможностей баз данных, таких р
как операторы PIVOT и UNPIVOT в SQL Server, оператор MODEL в Oracle и функция
н
GENERATE_SERIES в PostgreSQL.
• Разворачивать строки в столбцы и наоборот, упрощать вычисления внутри строки и вы- и
полнять двойное разворачивание результирующего множества.
к
• Создавать гистограммы, резюмировать данные в блоки, выполнять агрегацию скользя-
щего диапазона значений, формировать текущие суммы и подсуммы и использовать
р
другие расширенные методы работы с хранилищами данных.
е
• Выполнять обход строки, что позволяет использовать SQL для синтаксического разбора
строки на символы, слова или элементы строки с разделителями. ц
Написанная в популярном формате O’Reilly «Задача/Решение/Обсуждение», книга, несо-
е
мненно, будет вам полезна. Автор бережет время читателя, – понятным даже неопытному
SQL
пользователю языком, он быстро укажет вам кратчайший путь к решению задачи. Жиз- п
ненное кредо Энтони: «Мы все ходим на работу, зарабатывая на оплату счетов, и хотим воз-
вращаться домой вовремя и наслаждаться досугом». т
о
«Как только вы обнаружите, от скольких строк запутанного кода можете избавиться
в своем приложении, благодаря SQL, эта книга больше не покинет ваш стол.» в
Оливер Помел (Olivier Pomel), технический директор, Wireless Generation, Inc.
Сборник рецептов
М
Категория: базы данных о
л Э
Уровень подготовки читателей: средний и н
н т
а о
Издательство «Символ-Плюс» р н
о и
ISBN-13 978-5-93286-125-7 [phone], [phone]

ISBN-10 5-93286-125-8
www.symbol.ru Энтони Молинаро
9 785932 861257
По договору между издательством «Символ(cid:19)Плюс» и Интернет(cid:19)мага(cid:19)
зином «Books.Ru – Книги России» единственный легальный способ
получения данного файла с книгой ISBN 5(cid:19)93286(cid:19)125(cid:19)8, название
«SQL. Сборник рецептов» – покупка в Интернет(cid:19)магазине «Books.Ru –
Книги России». Если Вы получили данный файл каким(cid:19)либо другим
образом, Вы нарушили международное законодательство и законода(cid:19)
тельство Российской Федерации об охране авторского права. Вам необ(cid:19)
ходимо удалить данный файл, а также сообщить издательству «Сим(cid:19)
вол(cid:19)Плюс» ([email protected]), где именно Вы получили данный
файл.
SQL
Cookbook
Anthony Molinaro
SQL
Сборник рецептов
Энтони Молинаро
Санкт(cid:6)Петербург–Москва
2009
Энтони Молинаро
SQL. Сборник рецептов
Перевод Н.Шатохиной
Главный редактор А.Галунов
Зав. редакцией Н.Макарова
Выпускающий редактор П. Щеголев
Научный редактор К.Козинский
Редактор О.Меркулова
Корректор C.Минин
Верстка Д.Орлова
МолинароЭ.
SQL. Сборник рецептов.– Пер. с англ. – СПб: Символ!Плюс, 2009.– 672с., ил.
ISBN!13: 978!5!93286!125!7
ISBN!10: 5!93286!125!8
Книга Энтони Молинаро «SQL. Сборник рецептов» предназначена тем, кто уже
знаком с основами языка запросов SQL и хочет повысить свой профессиональ!
ный уровень. Она будет полезна и экспертам SQL, поскольку автор предлагает
варианты решения задач для разных СУБД: DB2, Oracle, PostgreSQL, MySQL
и SQL Server. Если вы постоянно работаете с SQL на одной платформе, то, воз!
можно, найдете в рецептах более эффективное решение на другой. Вы научи!
тесь использовать SQL для решения более широкого спектра задач – от опера!
ций внутри баз данных до передачи данных по сети в приложения. Для этого
достаточно открыть книгу на странице с интересующим вас рецептом.
Вы узнаете, как применять оконные функции и специальные операторы, а так!
же расширенные методы работы с хранилищами данных: создание гисто!
грамм, резюмирование данных в блоки, выполнение агрегации скользящего
диапазона значений, формирование текущих сумм и подсумм. Вы сможете раз!
ворачивать строки в столбцы и наоборот, упрощать вычисления внутри строки
и выполнять двойное разворачивание результирующего множества, выполнять
обход строки, что позволяет использовать SQL для синтаксического разбора
строки на символы, слова или элементы строки с разделителями. Приемы,
предлагаемые автором, позволят оптимизировать код ваших приложений и от!
кроют перед вами новые возможности языка SQL.
ISBN(cid:1)13: 978(cid:1)5(cid:1)93286(cid:1)125(cid:1)7
ISBN(cid:1)10: 5(cid:1)93286(cid:1)125(cid:1)8
ISBN 0(cid:1)596(cid:1)00976(cid:1)3 (англ)
© Издательство Символ!Плюс, 2009
Authorized translation of the English edition © 2006 O’Reilly Media, Inc. This trans!
lation is published and sold by permission of O’Reilly Media, Inc., the owner of all
rights to publish and sell the same.
Все права на данное издание защищены Законодательством РФ, включая право на полное или час!
тичное воспроизведение в любой форме. Все товарные знаки или зарегистрированные товарные зна!
ки, упоминаемые в настоящем издании, являются собственностью соответствующих фирм.
Издательство«Символ!Плюс».199034,Санкт!Петербург,16линия,7,
тел.(812) 324!5353, www.symbol.ru. Лицензия ЛП N 000054 от 25.12.98.
Подписано в печать 29.09.2008. Формат 70×1001 /16. Печать офсетная.
Объем 42 печ.л. Тираж 2500 экз. Заказ №
Отпечатано с готовых диапозитивов в ГУП «Типография «Наука»
199034, Санкт!Петербург, 9 линия, 12.
Моей маме:
Ты лучшая! Спасибо тебе за все.

Оглавление
Предисловие . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
1. Извлечение записей . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Извлечение всех строк и столбцов изтаблицы. . . . . . . . . . . . . . . . . . . . . 33
Извлечение подмножества строк из таблицы. . . . . . . . . . . . . . . . . . . . . . 34
Выбор строк по нескольким условиям . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Извлечение подмножества столбцов из таблицы. . . . . . . . . . . . . . . . . . . 35
Как задать столбцам значимые имена. . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Обращение к столбцу в предикате WHERE попсевдониму. . . . . . . . . . 37
Конкатенация значений столбцов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Использование условной логики в выражении SELECT . . . . . . . . . . . . 39
Ограничение числа возвращаемых строк . . . . . . . . . . . . . . . . . . . . . . . . . 40
Возвращение n случайных записей таблицы. . . . . . . . . . . . . . . . . . . . . . 42
Поиск значений NULL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Преобразование значений NULL в не!NULL значения. . . . . . . . . . . . . . 44
Поиск по шаблону. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
2. Сортировка результатов запроса . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Возвращение результатов запроса взаданномпорядке . . . . . . . . . . . . . 47
Сортировка по нескольким полям . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Сортировка по подстрокам . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Сортировка смешанных буквенно!цифровых данных . . . . . . . . . . . . . . 50
Обработка значений NULL при сортировке . . . . . . . . . . . . . . . . . . . . . . . 53
Сортировка по зависящему от данных ключу . . . . . . . . . . . . . . . . . . . . . 60
3. Работа с несколькими таблицами. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Размещение одного набора строк поддругим . . . . . . . . . . . . . . . . . . . . . 62
Объединение взаимосвязанных строк. . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Поиск одинаковых строк в двух таблицах . . . . . . . . . . . . . . . . . . . . . . . . 66
Извлечение из одной таблицы значений,
которых нет в другой таблице. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Извлечение из таблицы строк, для которых
нет соответствия в другой таблице. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
8 Оглавление
Независимое добавление объединений в запрос . . . . . . . . . . . . . . . . . . . 74
Выявление одинаковых данных в двух таблицах . . . . . . . . . . . . . . . . . . 76
Идентификация и устранение некорректного
использования декартова произведения. . . . . . . . . . . . . . . . . . . . . . . . . . 83
Осуществление объединений при использовании
агрегатных функций . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Внешнее объединение при использовании агрегатных функций. . . . . 90
Возвращение отсутствующих данных изнесколькихтаблиц. . . . . . . . 93
Значения NULL в операциях и сравнениях . . . . . . . . . . . . . . . . . . . . . . . 97
4. Вставка, обновление, удаление. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Вставка новой записи. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .100
Вставка значений по умолчанию . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .100
Переопределение значения по умолчанию значением NULL . . . . . . .102
Копирование строк из одной таблицы в другую . . . . . . . . . . . . . . . . . .103
Копирование описания таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .103
Вставка в несколько таблиц одновременно . . . . . . . . . . . . . . . . . . . . . .104
Блокировка вставки в определенные столбцы. . . . . . . . . . . . . . . . . . . .106
Изменение записей в таблице. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .107
Обновление в случае существования
соответствующих строк в другой таблице. . . . . . . . . . . . . . . . . . . . . . . .109
Обновление значениями из другой таблицы . . . . . . . . . . . . . . . . . . . . .110
Слияние записей . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .113
Удаление всех записей из таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . . .115
Удаление определенных записей . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .116
Удаление одной записи . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .116
Удаление записей, которые нарушают ссылочнуюцелостность . . . .117
Уничтожение дублирующихся записей . . . . . . . . . . . . . . . . . . . . . . . . .117
Удаление записей, на которые есть ссылки вдругойтаблице. . . . . . .119
5. Запросы на получение метаданных. . . . . . . . . . . . . . . . . . . . . . . . . .121
Как получить список таблиц схемы. . . . . . . . . . . . . . . . . . . . . . . . . . . . .121
Как получить список столбцов таблицы. . . . . . . . . . . . . . . . . . . . . . . . .122
Как получить список индексированных столбцовтаблицы . . . . . . . .123
Как получить список ограничений, наложенныхнатаблицу. . . . . . .125
Как получить список внешних ключей
без соответствующих индексов. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .126
Использование SQL для генерирования SQL . . . . . . . . . . . . . . . . . . . . .130
Описание представлений словаря данных вбазеданных Oracle. . . . .132
6. Работа со строками . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .134
Проход строки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .135
Как вставить кавычки в строковые литералы . . . . . . . . . . . . . . . . . . . .137
Как подсчитать, сколько раз символ встречаетсявстроке . . . . . . . . .138
Оглавление 9
Удаление из строки ненужных символов . . . . . . . . . . . . . . . . . . . . . . . .139
Разделение числовых и символьных данных. . . . . . . . . . . . . . . . . . . . .141
Как определить, содержит ли строка только
буквенно!цифровые данные . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .145
Извлечение инициалов из имени . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .150
Упорядочивание по частям строки . . . . . . . . . . . . . . . . . . . . . . . . . . . . .154
Упорядочивание по числу в строке . . . . . . . . . . . . . . . . . . . . . . . . . . . . .155
Создание списка с разделителями изстроктаблицы . . . . . . . . . . . . . .161
Преобразование данных с разделителями
всписокоператора IN со множеством значений . . . . . . . . . . . . . . . . . .168
Упорядочение строки в алфавитном порядке . . . . . . . . . . . . . . . . . . . .174
Выявление строк, которые могут быть
интерпретированы как числа . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .180
Извлечение n!ной подстроки с разделителями . . . . . . . . . . . . . . . . . . .187
Синтаксический разбор IP!адреса . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .194
7. Работа с числами . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .197
Вычисление среднего . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .197
Поиск минимального/максимального значениястолбца . . . . . . . . . .199
Вычисление суммы значений столбца. . . . . . . . . . . . . . . . . . . . . . . . . . .201
Подсчет строк в таблице. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .203
Подсчет значений в столбце . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .205
Вычисление текущей суммы. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .205
Вычисление текущего произведения. . . . . . . . . . . . . . . . . . . . . . . . . . . .209
Вычисление текущей разности. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .212
Вычисление моды . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .213
Вычисление медианы. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .216
Вычисление доли от целого в процентном выражении. . . . . . . . . . . . .220
Агрегация столбцов, которые могут содержатьNULL!значения. . . .223
Вычисление среднего без учета наибольшего
и наименьшего значений. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .224
Преобразование буквенно!цифровых строквчисла. . . . . . . . . . . . . . .226
Изменение значений в текущей сумме . . . . . . . . . . . . . . . . . . . . . . . . . .228
8. Арифметика дат. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .231
Добавление и вычитание дней, месяцев и лет . . . . . . . . . . . . . . . . . . . .231
Определение количества дней междудвумядатами. . . . . . . . . . . . . . .234
Определение количества рабочих дней междудвумя датами . . . . . . .236
Определение количества месяцев илилетмеждудвумя датами . . . .241
Определение количества секунд, минут
или часов между двумя датами. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .244
Как подсчитать, сколько раз в году
повторяетсякаждый день недели . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .246