Книга: Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ

Предложения в операторе SELECT

Предложения в операторе SELECT

В следующих подразделах мы предварительно рассмотрим каждое доступное в операторе SELECT предложение. Большинство предложений является необязательным, при этом важно их использование в правильном порядке.

За ключевым словом SELECT может следовать режим выборки для управления включением строк в выходной набор, когда они соответствуют всем другим условиям.

Это значение по умолчанию режима выборки для выходного списка и обычно опускается. Оно возвращает все строки, соответствующие условиям в спецификации.

Этот режим выборки подавляет вывод всех дубликатов строк в выходной набор. Например, таблица EMPLOYEE_PROJECT хранит пересекающиеся записи для каждой комбинации служащего (EMP_NO) и проекта (PROJ_ID), поддерживая отношение многие-ко- многим. Конкатенация EMP NO + PROJ_ID образует первичный ключ. Оператор

SELECT DISTINCT EMP_NO, PROJ_ID FROM EMPLOYEE_PROJECT;

вернет 28 строк - т. е. все строки, это является тем же самым, что и SELECT [ALL], потому что каждое появление (EMP_NO + PROJ_ID) является по своей природе уникальным, отличным от других.

Операторы

SELECT DISTINCT EMP_NO FROM EMPLOYEE_PROJECT;

и

SELECT DISTINCT PROJ_ID FROM EMPLOYEE_PROJECT;

вернут, соответственно, 22 и 5 строк.

Вычисление отличий применяется ко всем выходным столбцам, что делает оператор DISTINCT полезным в некоторых запросах, которые используют соединения для получения ненормализованного набора. Тщательно тестируйте этот режим для проверки, что он создает тот результат, который вы ожидаете[68].

Необязательные ключевые слова FIRST(m) и/или SKIP(n), если присутствуют, предшествуют всем другим спецификациям. Они задают режим выбора первых m строк в упорядоченном наборе и игнорирования первых n строк в упорядоченном наборе, соответственно. Не имеет смысла использовать эту конструкцию в неупорядоченном наборе[69]. Очевидно, нужно предложение ORDER BY для использования условия упорядочения, которое сделает осмысленным выбор кандидатов строк.

Эти два ключевых слова могут быть использованы вместе или индивидуально. Аргументы m и n должны быть целыми или выражениями, дающими целые числа. Круглые скобки вокруг значений тип требуются для выражений и не обязательны для простых целых аргументов.

Поскольку FIRST и SKIP выполняются над набором, полученным на основании остальной части спецификации, не следует ожидать, что они сделают выполнение запроса более быстрым. Преимущества в производительности получаются от сокращения сетевого трафика[70].

Следующий пример вернет пять строк, начиная со строки 101 упорядоченного набора:

SELECT FIRST 5 SKIP 100 MEMBER_ID, MEMBERSHIP_TYPE, JOIN_DATE

FROM MEMBERS

ORDER BY JOIN DATE;

! ! !

СОВЕТ. Для получения n строк с самыми большими значениями столбцов в соответствии с условиями упорядочения установите порядок сортировки DESC[ENDING].

. ! .

Предложение SELECT определяет список столбцов, которые будут помещаться в выходной набор. Список должен содержать, по меньшей мере, один столбец, который не обязательно должен быть столбцом, присутствующим в таблице. Эта фраза не является столь странной, как она звучит. Список столбцов действительно является выходной спецификацией и относится к языку манипулирования данными (DML). Выходные спецификации могут включать следующее:

* идентификатор столбца, который хранится в таблице, задан в просмотре или объявлен как выходной аргумент хранимой процедуры. При некоторых условиях идентификатор столбца должен содержать уточнение в виде имени или алиаса содержащей его таблицы;

* простое или сложное выражение, сопровождаемое идентификатором времени выполнения;

* константное значение, сопровождаемое идентификатором времени выполнения;

* контекстная переменная сервера, сопровождаемая идентификатором времени выполнения;

* символ *, часто называемый "звездочкой выбора", который задает все столбцы. Хотя SELECT * не исключает выбор одного или более столбцов из той же таблицы индивидуально, вообще-то в этом нет смысла. Чтобы включить дубликаты столбцов для специальных целей, применяйте для него ключевое слово AS и алиас, возвращая его как вычисляемое (только для чтения) поле.

Все следующие спецификации SELECT правильны.

Простой список столбцов:

SELECT COLUMN1, COLUMN2 ...

Уточненные имена столбцов, требуемые для спецификаций со многими таблицами:

SELECT

TABLEA.ID,

TABLEA.BOOK_TITLE,

TABLEB.CHAPTER_TITLE,

CURRENT_TIMESTAMP AS RETRIEVE_DATE . . .

Выражение (агрегирующее):

SELECT MAX (COST * QUANTITY) AS BEST_SALE ...

Выражение (преобразующее):

SELECT 'EASTER' || CAST (EXTRACT (YEAR FROM CURRENT_DATE) AS CHAR(4)) AS SEASON ...

Переменные и константы:

SELECT

ACOLUMN,

BCOLUMN,

CURRENT_USER, /* контекстная переменная */

'Jaybird' AS NICKNAME ...

/* константа времени выполнения */

Все столбцы таблицы:

SELECT * ...

Режимы выборки:

SELECT FIRST 5 SKIP 100 ACOLUMN, BCOLUMN ...

/* это не будет иметь смысла при отсутствии в дальнейшем предложения ORDER BY */

Константы или выражения - которые могут включать, а могут и не включать имя столбца- могут возвращаться только как неизменяемые, вычисляемые в момент выполнения поля. Такому столбцу должно назначаться имя, уникальное среди имен

выходного набора. Имена для столбцов времени выполнения называются алиасами столбцов. Для большей ясности алиас столбца необязательно может быть отмечен ключевым словом AS.

Возьмем предыдущий пример:

SELECT 'EASTER' || CAST (EXTRACT (YEAR FROM CURRENT_DATE) AS CHAR(4)) AS SEASON ...

В 2004 году этот алиас столбца будет возвращен для каждой строки набора в виде:

SEASON

======

EASTER2004

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

! ! !

ПРИМЕЧАНИЕ. Константы типа BLOB и массивы не могут быть использованы для выходных полей времени выполнения.

. ! .

О выражениях и функциях читайте в следующей главе.

Предложение FROM задает источник данных, который может быть таблицей, просмотром или хранимой процедурой, имеющей выходные аргументы. Если оператор включает соединение двух или более структур, то предложение FROM задает структуру, находящуюся в левой части. Другие таблицы добавляются в спецификацию при использовании последующих предложений ON (СМ. разд. "JOIN <спецификация> ").

В следующих примерах предложения FROM добавляются к спецификациям SELECT предыдущих примеров:

SELECT COLUMN1, COLUMN2 FROM ATABLE . . .

SELECT

TABLEA.ID,

TABLEA.BOOK_TITLE,

TABLEB.CHAPTER_TITLE,

CURRENT_TIMESTAMP AS RETRIEVE_DATE

FROM TABLEA . . .

SELECT MAX(COST * QUANTITY) AS BEST_SALE

FROM SALES ...

SELECT 'EASTER'||CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHAR(2))

AS SEASON

FROM RDB$DATABASE ;

SELECT ACOLUMN, BCOLUMN, CURRENT_USER, 'Jaybird' AS NICKNAME

FROM MYTABLE ...

Синтаксис внутреннего соединения SQL-89

Firebird обеспечивает поддержку устаревшего синтаксиса неявного внутреннего соединения SQL-89. Например:

SELECT

TABLEA.ID,

TABLEA.BOOK_TITLE,

TABLEB.CHAPTER_TITLE,

CURRENT_TIMESTAMP AS RETRIEVE_DATE

FROM TABLEA, TABLEB ...

По разным причинам вам не следует использовать этот синтаксис в новых приложениях (см. главу 22).

RDB$DATABASE является системной таблицей, содержащей одну и только одну строку, которая хранит сведения заголовочной информации о базе данных. Что там находится, неважно для пользователей Firebird. Тот факт, что там всегда содержится одна строка - ни больше, ни меньше - делает эту таблицу удобной, когда мы хотим найти значение на сервере, которое не хранится ни в таблице, ни в просмотре, ни в хранимой процедуре.

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

SELECT GEN_ID(MyGenerator, 1) FROM RDB$DATABASE;

В РСУБД Oracle для аналогичных целей используется таблица DUAL.

Используйте это предложение, чтобы добавить имена и условия соединения для второго и каждого последующего потока данных (таблица, просмотр или хранимая процедура выбора), который объединяется в многотабличном операторе SELECT - одно предложение JOIN ... ON для каждого исходного набора. Синтаксис и использование JOIN подробно обсуждается в главе 22. Следующий оператор иллюстрирует простое внутреннее соединение между двумя таблицами из предыдущего примера:

SELECT

TABLEA.ID,

TABLEA.BOOK_TITLE,

TABLEB.CHAPTER_TITLE,

CURRENT_TIMESTAMP AS RETRIEVE_DATE

FROM TABLEA

JOIN TABLEB

ON TABLEA.ID = TABLEB.ID_В ...

В том же фрагменте оператора идентификаторы таблиц могут быть заменены на алиасы таблиц, например:

SELECT

T1.ID,

T1.BOOK_TITLE,

T2.CHAPTER_TITLE,

CURRENT_TIMESTAMP AS RETRIEVE_DATE

FROM TABLEA T1

JOIN TABLEB T2

ON T1.ID = T2.ID_B ...

Использование алиасов таблиц в многотабличных запросах подробно рассматривается в главе 22.

Условия поиска, ограничивающие выводимые строки, размещаются в приложении WHERE. Условия поиска могут изменяться от условия простого соответствия для одного столбца до сложных условий, включающих выражения, предикаты AND, OR и NOT, преобразование типов, условия наборов символов и последовательностей сортировки и многое другое.

Предложение WHERE является фильтрующим предложением, определяющим, какие строки являются кандидатами для включения в выходной набор. Те строки, которые не были исключены условиями поиска в предложении WHERE, могут быть готовы для передачи инициатору запроса или они могут передаваться для последующей обработки, упорядочения на основании предложения ORDER BY С объединением или без объединения на основании предложения GROUP BY.

Следующие простые примеры иллюстрируют некоторые предложения WHERE, использующие условия выборки для ограничения отыскиваемых строк:

SELECT COLUMN 1, C0LUMN2 FROM ATABLE

WHERE ADATE BETWEEN '2002-12-25' AND '2004-12-24'...

/**/

SELECT TL.ID, T2.TITLE, CURRENT_TIMESTAMP AS RETRIEVE_DATE

FROM TABLEA

JOIN TABLEB

ON TABLEA.ID = TABLEB.ID_B

WHERE TABLEA.ID = 99 ;

/**/

SELECT MAX(COST * QUANTITY) AS BEST_SALE

FROM SALES

WHERE SALES_DATE > '31.12.2003'...

Глава 21 посвящена выражениям и предикатам, используемым в условиях поиска.

Интерфейсы доступа к данным, реализованные в API Firebird, имеют возможность обрабатывать константы в условиях поиска как заменяемые параметры. Следовательно, приложение может создавать оператор с динамическими условиями поиска в предложении WHERE, значения которых можно устанавливать непосредственно перед выполнением запроса. Такая возможность иногда называется динамическим связыванием.

Подробности см. в одном из следующих разд. "Использование параметров".

Выход оператора SELECT может быть разделен на одну или более вложенных групп, которые обобщают (суммируют) наборы данных, возвращенных с каждого вложенного уровня. Такое группирование часто использует агрегирующие выражения, т. е. выражения, содержащие функции, которые работают с множеством значений, таких как итоги, средние значения, счетчики строк, минимальные/максимальные значения.

Следующий простой пример иллюстрирует группирующий запрос. Агрегатная SQL - функция SUMO используется для вычисления общего количества продаж для каждого типа продукции:

SELECT PRODUCT_TYPE, SUM(NUMBER_SOLD) AS SUM_SALES

FROM TABLEA

WHERE SERIAL_NO BETWEEN 'A' AND 'K'

GROUP BY PRODUCT_TYPE;

Результат может быть похожим на следующий:

PRODUCT TYPE SUM SALES

Gadgets 174

Whatsits 25

Widgets 117

Firebird предоставляет широкий диапазон возможностей группирования с весьма ограниченными правилами управления их логикой.

! ! !

ВНИМАНИЕ! Если вы конвертируете базу данных из InterBase в Firebird, то это одна из областей, где вам нужно помнить о различиях. Firebird является менее терпимым к нелогичным спецификациям группирования, чем его предшественник. Это позволяет исключить возможность выполнения запросов, возвращающих некорректные результаты.

. ! .

Обобщающие выражения обсуждаются в главе 21. Подробную информацию о предложении GROUP BY см. в главе 23.

Необязательное предложение HAVING может быть использовано вместе со спецификацией группирования для включения или исключения строк или групп, как это делает предложение WHERE, ограничивая набор строк. Часто в группирующих запросах предложение HAVING может заменить предложение WHERE. При этом, поскольку HAVING оперирует с промежуточным набором, созданным в качестве входа для спецификации GROUP BY, может оказаться более экономичным использование условия WHERE для ограничения количества строк и условия HAVING для ограничения количества групп.

Изменим предыдущий пример, добавив предложение HAVING для получения только тех PRODUCT_TYPE, которые имели количество продаж больше 100:

SELECT PRODUCT_TYPE, SUM(NUMBER_SOLD) AS SUM_SALES

FROM TABLEA

WHERE SERIAL_NO BETWEEN 'A' AND 'K'

AND PRODUCT_TYPE = 'WIDGETS'

GROUP BY PRODUCT_TYPE

HAVING SUM(NUMBER_SOLD) > 100;

Вывод будет таким:

PRODUCT TYPE SUM SALES

Widgets 117

Наборы UNION формируются объединением двух или более спецификаций запросов, которые могут использовать различные таблицы, в один выходной набор. Единственное ограничение - выходные столбцы в каждой выходной спецификации должны соответствовать по степени, типу и размеру. Это означает, что в каждом выходе должно быть то же количество столбцов в том же порядке слева направо, и каждый столбец должен быть совместим по типу данных и размеру.

По умолчанию UNION убирает дубликаты в финальном выходном наборе. Для сохранения всех дубликатов добавьте ключевое слово ALL[71].

Наборы UNION подробно обсуждаются в главе 23.

Предложение PLAN позволяет включить план запроса в спецификацию запроса. План является инструкцией оптимизатору по использованию отдельных индексов, порядка соединения и методов доступа для запроса. Оптимизатор создает свой собственный план при подготовке оператора запроса. Вы можете просматривать план в isql и многих других доступных утилитах графического интерфейса. Обычно "оптимизатор знает лучше", но у вас может быть опыт использования различных планов оптимизатора, когда запрос работал медленно.

Планы запросов и синтаксис выражений плана обсуждаются в разд. "Тема оптимизации" главы 22.

Используйте это предложение, когда вам нужно сортировать выходной набор. Например, следующий оператор дает список имен, отсортированный в алфавитном порядке по фамилии и имени:

SELECT EMP_NO, LAST_NAME, FIRST_NAME FROM EMPLOYEE

ORDER BY LAST_NAME, FIRST_NAME;

В отличие от столбцов GROUP BY столбцы в ORDER BY не обязательно должны присутствовать в выходной спецификации (предложение SELECT). Идентификатор любого упорядочиваемого столбца, который также появляется в выходной спецификации, может быть заменен на его порядковый номер в выходной спецификации при подсчете слева направо:

SELECT EMP_NO, LAST_NAME, FIRST_NAME FROM EMPLOYEE

ORDER BY 2, 3;

Обратите особое внимание на индексы для столбцов, которые будут использованы для сортировки (см. главу 18). Подробнее о синтаксисе и проблемах см. в главе 23.

Его синтаксис:

[FOR UPDATE [OF col1 [,col2..]] [WITH LOCK]]

Вообще говоря, предложение FOR UPDATE имеет смысл только в контексте оператора SELECT, который используется для задания именованных курсоров. Оно указывает серверу, чтобы тот ждал вызова FETCH, читал бы одну строку в курсор для операции "текущей строки" и затем ожидал бы следующий вызов FETCH. После того как все записи будут прочитаны, они становятся доступными для операций изменения.

Необязательное внутреннее предложение OF <список-столбцов> может быть использовано для задания списка присутствующих в курсоре полей, которые могут быть изменены.

* В приложениях ESQL оператор DECLARE CURSOR используется для объявления именованного курсора. Подробную информацию см. в документации по InterBase 6.0 "Embedded SQL".

¦- Приложения интерфейса DSQL должны использовать функцию isc_dsqi_set_ cursor name для получения именованного курсора и осмысленно использовать FOR UPDATE. Более подробную информацию см. в InterBase API Guide.

Поскольку в DSQL отсутствует FETCH как элемент языка, приложения реализуют его с помощью вызова функции API С именем isc_dsql_fetch.

API "знает" порядок и формат выходных полей, потому что динамическое приложение должно передавать ему описательную структуру- называемую расширенной областью дескрипторов SQL (Extended SQL Descriptor Area, XSQLDA). Одна структура XSQLDA содержит массив описателей сложных переменных, называемых SQLVAR, по одному на каждое выходное поле.

Клиентское приложение использует isc_dsqi_fetch для запроса строки, которая только что заполнила XSQLDA. Обычное поведение большинства современных клиентских приложений- выполнение в цикле обращений к isc_dsqi_fetch для получения выходных строк в пакете и буферизация их в структурах клиентской стороны, которые называются наборами записей, наборами данных или результирующими наборами.

Некоторые приложения API реализуют именованные курсоры и используют поведение TOR UPDATE, однако большинство этого не делают.

Firebird 1.5 вводит необязательное расширение WITH LOCK, используемое с/без предложения FOR UPDATE, для поддержки ограниченного уровня явной пессимистической блокировки (pessimistic locking) на уровне строки. Пессимистическая блокировка является прямой противоположностью архитектуры транзакций в Firebird и добавляет запутанность. Ее использование рекомендуется только тем разработчикам, которые хорошо понимают, как параллельная работа многих пользователей реализована в Firebird. Пессимистическая блокировка обсуждается в главе 27.

Оглавление книги