Кроме столбцов, значения которых считываются непосредственно из базы данных, SQL-запрос на чтение может содержать вычисляемые столбцы, значения которых определяются на основании значений, хранящихся в базе данных. Чтобы получить вычисляемый столбец, в списке возвращаемых столбцов необходимо указать выражение. Выражения могут включать в себя операции сложения, вычитания, умножения и деления. В выражениях можно также использовать скобки. Конечно, столбцы, участвующие в арифметическом выражении, должны содержать числовые данные. При попытке сложить, вычесть, умножить или разделить столбцы, содержащие текстовые данные, будет выдано сообщение об ошибке.
Во многих СУБД реализованы дополнительные арифметические операции, операции над строками символов и встроенные функции, которые можно применять в выражениях SQL. Их также можно использовать в выражениях в списке возвращаемых столбцов, как в следующем примере для DB2:
Кроме того, в списке возвращаемых столбцов можно использовать константы SQL. Это может пригодиться для создания результатов запроса, которые более удобны для восприятия, как в следующем примере:
Чтение всех столбцов (оператор SELECT *)
Иногда требуется получить содержимое всех столбцов таблицы. На практике такая ситуация может возникнуть, когда вы впервые сталкиваетесь с новой базой данных и необходимо быстро получить представление о ее структуре и хранимых в ней данных. С учетом этого в SQL разрешается использовать вместо списка возвращаемых столбцов символ звездочки (*), который означает, что требуется прочитать все столбцы:
В стандарте ANSI/ISO сказано, что в предложении select можно использовать либо символ чтения всех столбцов, либо список возвращаемых столбцов, но не оба одновременно. Однако во многих реализациях SQL символ звездочки (*) считается просто одним из возвращаемых столбцов. Таким образом, запрос
SELECT *, (SALES - TARGET) FROM OFFICES
допустим в большинстве коммерческих диалектов SQL (в частности, в DB2, Oracle и SQL Server), однако не разрешен стандартом ANSI/ISO.
Символ чтения всех столбцов очень удобно использовать в интерактивном SQL. Однако следует избегать использования его в программном SQL, поскольку изменения в структуре базы данных могут привести к краху приложения. Предположим, например, что таблица offices была удалена из базы данных, а затем создана вновь, при этом был изменен порядок столбцов и добавлен новый, седьмой столбец. Если программа ожидает, что запрос select * from offices возвратит результат, содержащий шесть столбцов определенных типов, она почти наверняка перестанет работать после изменения порядка столбцов и добавления нового столбца.
Этих сложностей можно избежать, если в программах запрашивать требуемые столбцы по именам. Например, следующий запрос возвращает те же результаты, что и select * from offices. Он не восприимчив к изменениям структуры базы данных, пока в таблице offices существуют столбцы с указанными именами.
SELECT OFFICE, CITY, REGION, MGR, TARGET, SALES FROM OFFICES
Повторяющиеся строки (ключевое слово DISTINCT)
Если в списке возвращаемых столбцов запроса на чтение указать первичный ключ таблицы, то каждая строка результатов запроса будет уникальной (из-за того, что значения первичного ключа во всех строках разные). Если первичный ключ не указан, результаты запроса могут содержать повторяющиеся строки. Предположим, например, что был выполнен следующий запрос:
Повторяющиеся строки из таблицы результатов запроса можно удалить, если в операторе select перед списком возвращаемых столбцов указать ключевое слово distinct. Ниже приведен вариант предыдущего запроса, возвращающий те результаты, которых вы ожидали.
Если ключевое слово distinct не указано, повторяющиеся строки не удаляются. Можно также указать ключевое слово all, явно показывая, что повторяющиеся строки следует оставить, однако делать это не обязательно, поскольку ключевое слово all используется по умолчанию.
Отбор строк (предложение WHERE)
SQL-запросы, считывающие из таблицы все строки, полезны при просмотре базы данных и создании отчетов, однако редко применяются для чего-нибудь еще. Обычно требуется выбрать из таблицы несколько строк и включить в результаты запроса только их. Чтобы указать, какие строки требуется отобрать, следует использовать предложение where. Ниже показано несколько запросов, в которых используется это предложение.
Предложение where состоит из ключевого слова where, за которым следует условие поиска, определяющее, какие именно строки требуется прочитать. Если в условии поиска встречается имя столбца (как, например, имя manager), то используется значение этого столбца из текущей строки. Для каждой из строк условие поиска может иметь одно из трех перечисленных ниже значений.
• Если условие поиска имеет значение true, строка будет включена в результаты запроса.
• Если условие поиска имеет значение false, то строка исключается из результатов запроса.
• Если условие поиска имеет значение null, то строка исключается из результатов запроса.
Условие поиска служит фильтром для строк таблицы. Строки, удовлетворяющие условию поиска, проходят через фильтр и становятся частью результатов запроса. Строки, не удовлетворяющие условию поиска, отфильтровываются и исключаются из результатов запроса.
В SQL используется множество условий поиска, позволяющих эффективно и естественным образом создавать различные типы запросов. Ниже рассматриваются пять основных условий поиска (в стандарте ANSI/ISO они называются предикатами).
• Сравнение. Значение одного выражения сравнивается со значением другого выражения. Например, такое условие поиска используется для выбора всех офисов, находящихся в восточном регионе, или всех служащих, фактические объемы продаж которых превышают плановые.
• Проверкана принадлежность диапазону значений. Проверяется, попадает ли указанное значение в определенный диапазон значений. Например, такое условие поиска используется для нахождения служащих, фактические объемы продаж которых превышают $100000, но меньше $500000.
• Проверка на членство в множестве. Проверяется, совпадает ли значение выражения с одним из значений из заданного множества. Например, такое условие поиска используется для выбора офисов, расположенных в Нью-Йорке, Чикаго или Лос-Анджелесе.
• Проверка на соответствие шаблону. Проверяется, соответствует ли строковое значение, содержащееся в столбце, определенному шаблону. Например, такое условие поиска используется для выбора клиентов, имена которых начинаются с буквы "Е".
• Проверка на равенство значению NULL. Проверяется, содержится ли в столбце значение null. Например, такое условие поиска используется для нахождения всех служащих, которым еще не был назначен менеджер.
СРАВНЕНИЕ (=, О, <, <=, >, >=)
Наиболее распространенным условием поиска в SQL является сравнение.При сравнении SQL вычисляет и сравнивает значения двух выражений для каждой строки данных. Выражения могут быть как очень простыми, например, содержать одно имя столбца или константу, так и более сложными — арифметическими — выражениями. В SQL имеется шесть различных способов сравнения двух выражений. Когда SQL сравнивает значения двух выражений, могут получиться три результата:
· если сравнение истинно, результат проверки имеет значение TRUE;
· если сравнение ложно, результат проверки имеет значениеFALSE;
· еслихотя бы одно из двух выражений имеет значениеNULL, то результатом проверки будет значение NULL.
Использование значений NULL в запросах может привести к некоторым неожиданным результатам.
ПРОВЕРКА НА ПРИНАДЛЕЖНОСТЬ ДИАПАЗОНУ ЗНАЧЕНИЙ (BETWEEN)
Задается предложением BETWEEN нижняя_граница AND верхняя_граница
При проверке на принадлежность диапазону верхний и нижний пределы считаются частью диапазона.
Инвертированная версия проверки на принадлежность диапазону позволяет выбрать значения, лежащие за пределами диапазона.
ПРОВЕРКА НА ЧЛЕНСТВО В МНОЖЕСТВЕ (IN)
Еще одним условием поиска является проверка на членство в множестве (IN).В этом случае проверяется, соответствует ли значение данных какому-либо значению из заданного списка.
С помощью формы not in можно убедиться в том, что значение данных не является членом заданного множества. Проверяемое выражение в проверке in может быть любым допустимым выражением, однако обычно оно представляет собой короткое имя столбца, как в предыдущих примерах. Если результатом проверяемого выражения является значение null, то проверка IN также возвращает значение null. Все значения в списке заданных значений должны иметь один и тот же тип данных, который должен быть сравним с типом данных проверяемого выражения.
Как и проверка between, проверка in не добавляет в возможности SQL ничего нового, поскольку условие поиска
Х IN (А, В, С)
полностью эквивалентно условиям
(X = A) OR (X = В) OR (X = С)
Однако проверка in предлагает гораздо более эффективный способ выражения условия поиска, особенно если множество содержит большое число элементов. В стандарте ANSI/ISO не определено максимальное количество элементов множества, и в большинстве коммерческих реализаций не задано явного верхнего предела. По соображениям переносимости лучше избегать множеств, содержащих один элемент, наподобие такого:
CITY IN ('New York')
Их следует заменять на следующее простое сравнение:
CITY = 'New York'
Проверка на соответствие шаблону (LIKE)
Для чтения строк, в которых содержимое некоторого текстового столбца совпадает с заданным текстом, можно использовать простое сравнение/
Поверка на соответствие шаблону (ключевое слово like) позволяет определить, соответствует ли значение данных в столбце некоторому шаблону. Шаблон представляет собой строку, в которую может входить один или более подстановочных знаков. Эти знаки интерпретируются особым образом.
ПОДСТАНОВОЧНЫЕ ЗНАКИ
Подстановочный знак % совпадает с любой последовательностью из нуля или более символов.
Подстановочный знак "_" (символ подчеркивания) совпадает с любым отдельным символом.
Подстановочные знаки можно помещать в любое место строки шаблона, и в одной строке может содержаться несколько подстановочных знаков.
С помощью формы not like можно находить строки, которые несоответствуют шаблону. Проверку like можно применять только к столбцам, имеющим строковый тип данных. Если в столбце содержится значение null, то результатом проверки like будет значение null.
СИМВОЛЫ ПРОПУСКА *
При проверке строк на соответствие шаблону может оказаться, что подстановочные знаки входят в строку символов в качестве литералов. Например, нельзя проверить, содержится ли знак процента в строке, просто включив его в шаблон, поскольку SQL будет считать этот знак подстановочным. Как правило, это не вызывает серьезных проблем, поскольку подстановочные знаки довольно редко встречаются в именах, названиях товаров и других текстовых данных, которые обычно хранятся в базе данных.
В стандарте ANSI/ISO определен способ проверки наличия в строке литералов, использующихся в качестве подстановочных знаков. Для этого применяются символы пропуска. Когда в шаблоне встречается такой символ, то символ, следующий непосредственно за ним, считается не подстановочным знаком, а литералом. (Происходит пропусксимвола.) Непосредственно за символом пропуска может следовать либо один из двух подстановочных знаков, либо сам символ пропуска, поскольку он тоже приобретает в шаблоне особое значение.
Символ пропуска определяется в виде строки, состоящей из одного символа, и предложения escape
Первый символ процента в шаблоне, следующий за символом пропуска, считается литералом; второй считается подстановочным знаком.
Символы пропуска часто используются при проверке на соответствие шаблону; именно поэтому они были включены в стандарт ANSI/ISO. Однако они не входили в первые реализации SQLи поэтому не очень распространены. Для обеспечения переносимости приложений предложения escape использовать не следует.
ПРОВЕРКА НА РАВЕНСТВО ЗНАЧЕНИЮ NULL (IS NULL)
Значения null обеспечивают возможность применения трехзначной логики в условиях поиска. Для любой заданной строки результат применения условия поиска может быть true, false или null (в случае, когда водном из столбцов содержится значение null). Иногда бывает необходимо явно проверять значения столбцов на равенство null и непосредственно обрабатывать их. Для этого в SQL имеется специальная проверка на равенство значению null (is null).
Инвертированная форма проверки на null (is not null) позволяет отыскать строки, которые не содержат значения null:
В отличие от условий поиска, описанных выше, проверка на null не может возвратить значение null в качестве результата. Она всегда возвращает true или FALSE.
Может показаться странным, что нельзя проверить значение на равенство null с помощью операции сравнения, например:
Ключевое слово null здесь нельзя использовать, поскольку на самом деле это не настоящее значение; это просто сигнал о том, что значение неизвестно. Даже если бы сравнение было возможно, правила обработки значений null в сравнениях привели бы к тому, что оно вело бы себя не так, как ожидается. Когда SQL встречает строку, в которой столбец rep_office содержит значение null, выполняется следующая проверка:
NULL = NULL
Что будет результатом этого сравнения: true илиfalse? Так как значения по обе стороны знака равенства неизвестны, то, в соответствии с правилами логики SQL, условие поиска должно вернуть значение null. Поскольку условие поиска возвращает результат, отличный от true, строка исключается из таблицы результатов запроса — это совсем не то, к чему вы стремились! Из-за правил обработки значений null в SQL необходимо использовать проверку is null.
СОСТАВНЫЕ УСЛОВИЯ ПОИСКА (AND, OR И NOT)
Простые условия поиска, описанные в предыдущих параграфах, после применения к некоторой стуже возвращают значения true, false или null. С помощью правил логики эти простые условия можно объединять в более сложные. Обратите внимание на то, что условия поиска, объединенные с помощью ключевых слов and, or и not, сами могут быть составными.
Ключевое слово or используется для объединения двух условий поиска, из которых как минимум одно должно быть истинным:
Для объединения двух условий поиска, оба из которых должны быть истинными, можно также использовать ключевое слово and:
И, наконец, можно использовать ключевое слово not, чтобы выбрать строки, в которых условие поиска ложно:
СОРТИРОВКА РЕЗУЛЬТАТОВ ЗАПРОСА (ПРЕДЛОЖЕНИЕ ORDER BY)
Строки результатов запроса, как и строки таблицы базы данных, не имеют определенного порядка. Включив в оператор select предложение order by, можно отсортировать результаты запроса. Это предложение состоит из ключевых слов order by, за которыми следует список имен или порядковых номеров столбцов, разделенных запятыми. Первый столбец является главным ключом сортировки; столбцы, следующие за ним, являются все более второстепеннымиключами. Можно сортировать результаты запроса по любому элементу списка возвращаемых столбцов запроса.
В предложении order by можно выбрать возрастающий или убывающий порядок сортировки. По умолчанию, данные сортируются в порядке возрастания. Чтобы сортировать их по убыванию, следует включить в предложение сортировки ключевое слово desc.
Чтобы определить порядок сортировки по возрастанию, необходимо использовать ключевое слово asc, однако из-за того, что такой порядок принят по умолчанию, это ключевое слово обычно не указывают.
Если столбец результатов запроса, используемый для сортировки, является вычисляемым, то у него нет имени, которое можно указать в предложении сортировки. В таком случае вместо имени столбца необходимо указать его порядковый номер:
Стандарт SQL2 позволяет управлять порядком сортировки отдельно по каждому ключу. Это может оказаться важным при работе с локализованными наборами символов или для обеспечения переносимости между системами с таблицами кодировок ASCII и EBCDIC. Однако эта часть спецификации SQL2 является довольно сложной, и в большинстве реализации SQL либо вообще игнорируются вопросы последовательности сортировки, либо используются собственные схемы управления этой последовательностью.
ПРАВИЛА ВЫПОЛНЕНИЯ ОДНОТАБЛИЧНЫХ ЗАПРОСОВ
Результаты запроса, возвращенные оператором select, получаются в результате поочередного применения входящих в оператор предложений. Вначале применяется предложение from (оно выбирает таблицу, содержащую требуемые данные). Затем применяется предложение where (которое по определенному критерию отбирает из таблицы строки). Далее применяется предложение select (которое создает указанные столбцы результатов запроса и при необходимости удаляет повторяющиеся строки), и, наконец, для сортировки результатов запроса применяется предложение order by.
ОБЪЕДИНЕНИЕ РЕЗУЛЬТАТОВ НЕСКОЛЬКИХ ЗАПРОСОВ (UNION)*
Иногда появляется необходимость объединения результатов двух или более запросов в одну таблицу. SQL поддерживает такую возможность с помощью оператора Union.
Оператор uNion создает одну таблицу результатов запроса, в которой содержатся строки результатов как первого, так и второго запросов. В запросах оператор union используется следующим образом:
Чтобы таблицы результатов запроса можно было объединить с помощью оператора union, они должны соответствовать следующим требованиям:
• две таблицы должны содержать одинаковое число столбцов;
• тип данных каждого столбца первой таблицы должен совпадать с типом данных соответствующего столбца во второй таблице;
• ни одна из двух таблиц не может быть отсортирована с помощью предложения order by, однако объединенные результаты запроса можно отсортировать.
Имена столбцов в двух запросах, объединенных с помощью оператора union, не обязательно должны быть одинаковыми. Поскольку столбцы в двух таблицах могут иметь различные имена, столбцы результатов запроса, возвращенные оператором union, имен не имеют. Стандарт ANSI/ISO накладывает дополнительные ограничения на операторы select, участвующие в операторе union. Он разрешает использовать в списке возвращаемых столбцов только имена столбцов или указатель на все столбцы (select *) и запрещает использовать выражения. В большинстве коммерческих реализации SQL, тем не менее, это ограничение ослаблено, и в списке возвращаемых столбцов разрешено использовать простые выражения. Однако во многих реализациях SQL не разрешается включать в операторы select предложения group by или having, а в некоторых не разрешается использовать в списке возвращаемых столбцовагрегатные функции (т.е. нельзя использовать итоговые запросы).
ЗАПРОС НА ОБЪЕДИНЕНИЕ И ПОВТОРЯЮЩИЕСЯ СТРОКИ *
Поскольку оператор union объединяет строки из двух результатов запросов, вполне вероятно, что в результатах, возвращенных этим оператором, будут содержаться повторяющиеся строки.
Если в результатах оператора union необходимо сохранить повторяющиеся строки, сразу за ключевым словом union следует указать ключевое слово all. Эта форма запроса вернет результаты с повторяющимися строками:
Обработка повторяющихся строк для операторов union и select осуществляется по разному. Оператор select по умолчанию оставляет повторяющиеся строки (select all). Чтобы удалить повторяющиеся строки, необходимо явно указать ключевое слово distinct. Оператор union по умолчанию удаляет повторяющиеся строки. Чтобы оставить повторяющиеся строки, необходимо явно указать ключевое слово ALL.
Специалисты по работе с базами данных критиковали обработку повторяющихся строк в SQL и указывали на эту "непоследовательность" языка как на одну из проблем. Причина такой "непоследовательности" заключается в том, что в SQL в качестве установок по умолчанию выбираются наиболее часто используемые варианты:
• на практике большинство простых операторов select не возвращают повторяющиеся строки, поэтому по умолчанию принято не удалять их;
• на практике большинство операторов union возвращают повторяющиеся строки, что нежелательно, поэтому по умолчанию такие строки удаляются.
Удаление повторяющихся строк из таблицы результатов запроса занимает много времени, особенно если результаты запроса содержат большое количество строк. Если известно, что оператор union не возвратит повторяющиеся строки, необходимо явно указать ключевое слово all, тогда запрос будет выполняться быстрее.
ЗАПРОС НА ОБЪЕДИНЕНИЕ И СОРТИРОВКА *
Предложение order by нельзя использовать ни в одномиз операторов select, объединенных оператором union. Нет смысла выполнять сортировку результатов таких запросов, поскольку пользователь все равно не увидит их в чистом виде. Однако объединенные результаты запросов, возвращенные оператором union, можно отсортировать с помощью предложения order by, следующего за вторым оператором select. Поскольку столбцы таблицы результатов запроса на объединение не имеют имен, в этом предложении следует указывать номера столбцов.
Скобки в запросе показывают, какой оператор union должен выполняться первым. Независимо от того, удаляют ли все операторы union повторяющиеся строки или сохраняют их, порядок выполнения операторов не имеет значения. Следующие выражения полностью эквивалентны
A UNION (В UNION С)
(A UNION В) UNION С
(A UNION С) UNION В
и возвращают семь строк результатов запроса. Подобным образом три следующих выражения полностью эквивалентны и возвращают двенадцать строк результатов запроса, поскольку повторяющиеся строки сохраняются:
A UNION ALL (В UNION ALL С)
(A UNION ALL В) UNION ALL С
(A UNION ALL C) UNION ALL В
Однако если в запросы на объединения входят как операторы union, так и операторы union all, то порядок следования этих операторов имеет значение. Если выражение
A UNION ALL В UNION С
проинтерпретировать как
A UNION ALL (В UNION С)
то оно вернет десять строк (шесть из внутреннего оператора плюс четыре строки из таблицы А). Однако если его проинтерпретировать как
(A UNION ALL В) UNION С
то оно вернет только четыре строки, поскольку внешний оператор union удалит все повторяющиеся строки.
По этой причине всегда необходимо использовать круглые скобки, чтобы указать последовательность выполнения в запросах на объединение, содержащих три или более операторов union.
27. МНОГОТАБЛИЧНЫЕ ЗАПРОСЫ НА ЧТЕНИЕ (ОБЪЕДИНЕНИЯ)
На практике многие запросы считывают данные сразу из нескольких таблиц базы данных.
SQL позволяет получить ответы на эти запросы посредством многотабличных запросов, которые объединяют данные из нескольких таблиц. В настоящей главе рассматриваются такие запросы и имеющиеся в SQL средства объединения.
ПРОСТОЕ ОБЪЕДИНЕНИЕ ТАБЛИЦ (ОБЪЕДИНЕНИЕ ПО РАВЕНСТВУ)
Процесс формирования пар строк путем сравнения содержимого соответствующих столбцов называетсяобъединением таблиц. Таблица, получающаяся в результате формирования пар строк и содержащая данные из обеих исходных таблиц, называется объединением двух таблиц. (Объединение на основе точного равенства между двумя столбцами более правильно называется объединением по равенству).
Объединения представляют собой основу многотабличных запросов в SQL. В реляционной базе данных вся информация хранится в виде явных значений данных в столбцах, так что все возможные отношения между таблицами можно сформировать, сопоставляя содержимое соответствующих столбцов. Таким образом, объединения являются мощным средством использования отношений, существующих между данными.
Так как в SQL многотабличные запросы выполняются путем сопоставления столбцов, неудивительно, что оператор select для многотабличного запроса должен содержать условие поиска, которое определяет соответствие столбцов.
