12. Операторы DML
Для заполнения базы данных пользовательскими данными, изменения и удаления существующих данных используются операторы SQL подраздела DML (Data Manipulation Language). Операторы задают, что должно быть сделано с данными базы данных, не указывая, как именно это должно быть сделано.
Оператор SELECT — один из самых сложных и самых мощных операторов SQL в СУБД РЕД База Данных. Он позволяет выбирать данные из одной или нескольких таблиц на основании условий
в предложении WHERE, условий объединения (оператор SELECT в предложении UNION) и
условий соединения (предложение ON), если используется объединение (UNION) или соединение
нескольких таблиц (JOIN).
Оператор выбирает данные из одной или нескольких таблиц, представлений или из хранимой процедуры выбора.
Результатом выборки является выходной набор данных — множество строк одинаковой структуры, состав
которых задан в списке выбора оператора SELECT.
Для добавления новых строк в таблицы или в представления базы данных используется оператор INSERT.
Для изменения данных в таблицах базы данных применяется оператор UPDATE.
Для изменения существующих строк в таблицах базы данных или для добавления новых данных,
если такие строки еще не существуют, применяется оператор UPDATE OR INSERT.
Для удаления строк таблиц используется оператор DELETE.
Есть также оператор EXECUTE BLOCK, который позволяет в декларативной части SQL использовать
некоторые императивные средства, применяемые в языке хранимых процедур и триггеров (PSQL).
Все действия по изменению данных выполняются в контексте (под управлением) какой-либо транзакции.
Это может быть предварительно запущенная в клиенте оператором SET TRANSACTION транзакция с
необходимыми характеристиками или транзакция по умолчанию, запускаемая системой автоматически
при выполнении любых операций с данными и метаданными базы данных. О транзакциях см. в главе 6.
12.1. SELECT
Синтаксис оператора достаточно сложный. Он представлен в листинге .
Далее в подразделе дается краткое описание структуры оператора SELECT, о полном описании всех
предложении рассказывается в следующих подразделах этой главы.
Оператор (команда) SELECT извлекает данные из базы данных и передаёт их в приложение или в
вызывающую SQL-команду. Данные возвращаются в виде набора строк,
каждая строка содержит один или несколько столбцов и полей. Совокупность возвращаемых строк является
результирующим набором данных команды.
Листинг 12.1 Синтаксис оператора выборки данных SELECT
[WITH [RECURSIVE] <CTE> [, <CTE> ...]]
SELECT
[FIRST <значение>] [SKIP <значение>]
[DISTINCT | ALL]
<выходное поле> [, <выходное поле>]
FROM {<источники> | <соединения (joins)>}
[WHERE <условие выборки>]
[GROUP BY <условие группирования> [, <условие группирования> ...]
[HAVING <условие выборки>]]
[WINDOW <спецификация окна> [, <спецификация окна> ...]]
[PLAN <выражение для плана поиска>]
[UNION [DISTINCT | ALL] <другой набор данных>]
[ORDER BY <выражение сортировки> [, <выражение сортировки> ...]]
[{ ROWS <m> [TO <n>]
| [OFFSET <n> {ROW | ROWS}]
[FETCH {FIRST | NEXT}
[<m>] {ROW | ROWS} ONLY]
}]
[FOR UPDATE [OF <имя столбца> [, <имя столбца>]...]]
[WITH LOCK [SKIP LOCKED]]
[OPTIMIZE FOR {FIRST | ALL} ROWS]
[INTO [:]<переменная> [,[:]<переменная> ... ]]
Примечание
Приведенный выше синтаксис не является полным синтаксисом SELECT.
Он упрощен для документации и будет раскрыт в следующих разделах.
Полный синтаксис SELECT можно найти ниже, в разделе Полный синтаксис SELECT.
Обязательными в команде SELECT являются список полей, запрашиваемых из базы данных, и ключевое
слово FROM, за которым следует объект выборки (например, таблица).
В простейшей форме SELECT извлекает ряд полей из единственной таблицы, например:
SELECT id, name, address FROM contacts;
Или, для того чтобы извлечь все поля таблицы:
SELECT * FROM contacts;
Предложение WITH позволяет задать общее табличное выражение (CTE, Common Table Expression).
Оно может быть рекурсивным (ключевое слово RECURSIVE) и обычным, не рекурсивным (значение по умолчанию).
Сразу после ключевого слова SELECT могут следовать предложения FIRST и SKIP,
позволяющие определить количество строк, помещаемых в результирующий набор данных, выбранных
на основании условий выборки (предложения ON, UNION и WHERE).
Ключевое слово DISTINCT указывает, что в выходной набор данных не должны входить дубликаты строк.
Далее идет сам список выбора, указывающий, какие столбцы из каких таблиц, участвующих в
операции выборки, помещаются в выходной набор данных. Здесь могут располагаться константы,
контекстные переменные и операторы SELECT, выбирающие из произвольных таблиц одно значение одного столбца.
Предложение FROM содержит список таблиц, из которых осуществляется выбор
данных. В этом предложении может содержаться описание соединения (JOIN)
нескольких таблиц для получения выходного набора данных.
Необязательное предложение WHERE задает условия выборки данных — те
условия, которым должны удовлетворять строки исходной таблицы (исходных
таблиц), для того, чтобы они попали в результирующий набор данных.
Подробное описание условия выборки см. в разделе этой главы.
Предложения GROUP BY и HAVING позволяют сгруппировать выбранные данные,
если в списке выбора присутствуют агрегатные функции, обобщающие данные из нескольких строк исходной таблицы.
Предложение WINDOW предназначено для задания именованных окон, которые используются оконными функциями.
Предложение UNION дает возможность объединить в выходном наборе данных
несколько таблиц с одинаковой структурой.
В предложении PLAN можно задать свой план для выполнения запроса,
который позволит ускорить процесс выбора данных.
Предложение ORDER BY задает упорядоченность выходного набора данных.
Здесь также можно указать количество строк, которое должно быть
помещено в результирующий набор данных (предложения ROWS, OFFSET, FETCH).
Предложение OPTIMIZE FOR позволяет задать необходимую стратегию оптимизации запросов для
ускорения процесса выборки данных.
Необязательное предложение WITH LOCK запрещает параллельным процессам, транзакциям выполнять
какие-либо изменения в данной таблице запроса. Подробности см. в главе 6.
Предложение SKIP LOCKED позволяет пропускать записи, заблокированные другими транзакциями,
вместо того чтобы ждать их разблокирования или выдавать ошибку конфликта обновления.
С помощью предложения INTO в PSQL (хранимых процедурах, триггерах и др.) результаты выборки команды SELECT
могут быть построчно загружены в локальные переменные (число, порядок и типы локальных переменных должны
соответствовать полям SELECT).
WITH RECURSIVE
Предложение WITH позволяет задать общее табличное выражение (CTE, Common Table Expression).
CTE описаны как виртуальные таблицы или представления, определённые в преамбуле основного запроса,
которые участвуют в основном запросе. Основной запрос может ссылаться на любое CTE из определённых
в преамбуле, как и при выборке данных из обычных таблиц или представлений. Оно может быть рекурсивным
(ключевое слово RECURSIVE), то есть ссылающимся само на себя и обычным, не рекурсивным (значение по умолчанию).
Синтаксис предложения представлен в листинге :
Листинг 12.2 Синтаксис предложения WITH RECURSIVE
WITH [RECURSIVE] <CTE> [, <CTE> ...]
SELECT ...
FROM ...
<CTE> ::= <псевдоним CTE> [(<список столбцов CTE>)] AS (<оператор SELECT или UNION>)
<список столбцов CTE> ::= <псевдоним столбца CTE> [, <псевдоним столбца CTE> ...]
Примечания по использованию CTE:
Операторы
WITHне могут быть вложенными;CTEмогут использовать друг друга, но ссылки не должны иметь циклов;CTEмогут быть использованы в любой части главного запроса или другого табличного выражения и сколько угодно раз;Основной запрос может ссылаться на
CTEнесколько раз, но с разными алиасами;CTEмогут быть использованы в операторахINSERT, UPDATEиDELETEкак подзапросы;Если
CTEобъявлен, то он должен быть обязательно использован;CTEмогут быть использованы и в PSQL (FOR WITH ... SELECT ... INTO ...)
Пример не рекурсивного использования WITH:
WITH DEPT_YEAR_BUDGET AS (
SELECT
FISCAL_YEAR,
DEPT_NO,
SUM(PROJECTED_BUDGET) AS BUDGET
FROM PROJ_DEPT_BUDGET
GROUP BY FISCAL_YEAR, DEPT_NO )
SELECT
D.DEPT_NO,
D.DEPARTMENT,
B_1993.BUDGET AS B_1993, B_1994.BUDGET AS B_1994,
B_1995.BUDGET AS B_1995, B_1996.BUDGET AS B_1996
FROM DEPARTMENT D
LEFT JOIN DEPT_YEAR_BUDGET B_1993
ON D.DEPT_NO = B_1993.DEPT_NO
AND B_1993.FISCAL_YEAR = 1993
LEFT JOIN DEPT_YEAR_BUDGET B_1994
ON D.DEPT_NO = B_1994.DEPT_NO
AND B_1994.FISCAL_YEAR = 1994
LEFT JOIN DEPT_YEAR_BUDGET B_1995
ON D.DEPT_NO = B_1995.DEPT_NO
AND B_1995.FISCAL_YEAR = 1995
LEFT JOIN DEPT_YEAR_BUDGET B_1996
ON D.DEPT_NO = B_1996.DEPT_NO
AND B_1996.FISCAL_YEAR = 1996
WHERE EXISTS (SELECT * FROM PROJ_DEPT_BUDGET B
WHERE D.DEPT_NO = B.DEPT_NO);
Рекурсивное (ссылающееся само на себя) CTE это объединение, у которого должен
быть, по крайней мере, один не рекурсивный элемент, к которому привязываются остальные
элементы объединения. Не рекурсивный элемент помещается в CTE первым. Рекурсивные
члены отделяются от не рекурсивных и друг от друга с помощью UNION ALL. Объединение не
рекурсивных элементов может быть любого типа.
Рекурсивное CTE требует наличия ключевого слова RECURSIVE справа от WITH. Каждый
рекурсивный член объединения может сослаться на себя только один раз и это должно быть
сделано в предложении FROM.
Главным преимуществом рекурсивных CTE является то, что они используют гораздо меньше
памяти и процессорного времени, чем эквивалентные рекурсивные хранимые процедуры.
Пример рекурсивного использования WITH:
WITH RECURSIVE DEPT_YEAR_BUDGET AS (
SELECT
FISCAL_YEAR,
DEPT_NO,
SUM(PROJECTED_BUDGET) AS BUDGET
FROM PROJ_DEPT_BUDGET
GROUP BY FISCAL_YEAR, DEPT_NO ),
DEPT_TREE AS (
SELECT
DEPT_NO,
HEAD_DEPT,
DEPARTMENT,
CAST('' AS VARCHAR(255)) AS INDENT
FROM DEPARTMENT
WHERE HEAD_DEPT IS NULL
UNION ALL
SELECT
D.DEPT_NO,
D.HEAD_DEPT,
D.DEPARTMENT,
H.INDENT || ' '
FROM DEPARTMENT D
JOIN DEPT_TREE H ON D.HEAD_DEPT = H.DEPT_NO )
SELECT
D.DEPT_NO,
D.INDENT || D.DEPARTMENT AS DEPARTMENT,
B_1993.BUDGET AS B_1993, B_1994.BUDGET AS B_1994,
B_1995.BUDGET AS B_1995, B_1996.BUDGET AS B_1996
FROM DEPT_TREE D
LEFT JOIN DEPT_YEAR_BUDGET B_1993
ON D.DEPT_NO = B_1993.DEPT_NO
AND B_1993.FISCAL_YEAR = 1993
LEFT JOIN DEPT_YEAR_BUDGET B_1994
ON D.DEPT_NO = B_1994.DEPT_NO
AND B_1994.FISCAL_YEAR = 1994
LEFT JOIN DEPT_YEAR_BUDGET B_1995
ON D.DEPT_NO = B_1995.DEPT_NO
AND B_1995.FISCAL_YEAR = 1995
LEFT JOIN DEPT_YEAR_BUDGET B_1996
ON D.DEPT_NO = B_1996.DEPT_NO
AND B_1996.FISCAL_YEAR = 1996;
Примечания для рекурсивного CTE:
В рекурсивных членах объединения не разрешается использовать агрегаты (
DISTINCT, GROUP BY, HAVING) и агрегатные функции (SUM, COUNT, MAXи т.п.);Рекурсивная ссылка не может быть участником внешнего объединения
OUTER JOIN;Максимальная глубина рекурсии составляет 1024;
Рекурсивный член не может быть представлен в виде производной таблицы.
Список выбора
После ключевого слова SELECT может следовать какие столбцы исходных таблиц должны присутствовать
в выходном наборе данных.
Листинг 12.3 Синтаксис списка выбора в операторе SELECT
SELECT ...
[{ ALL | DISTINCT }] <список выбора>
FROM ...
<список выбора> ::= * | <подсписок> [, <подсписок> ...]
<подсписок> ::=
<спецификатор>.*
| <выражение> [[AS] <столбец>]
<выражение> ::=
[спецификатор.]<столбец таблицы (представления) или псевдоним>
| [спецификатор.]<выходной параметр селективной хранимой процедуры>
| <литерал>
| <контекстная переменная>
| <вызов функции>
| <подзапрос SELECT, возвращающий единственное скалярное значение>
| <конструкция CASE>
| NEXT VALUE FOR <имя генератора>
| NULL
| <любое выражение, возвращающее единственное значение>
<вызов функции> ::=
<скалярная функция>
| <агрегатная функция>
| <оконная функция>
<спецификатор> ::= имя таблицы (представления) или псевдоним таблицы (представления, хранимой процедуры, производной таблицы)
Символ "*" означает, что в результирующем наборе данных должны присутствовать все столбцы исходной таблицы (исходных таблиц).
Если в запросе используется несколько таблиц, у которых имена столбцов могут совпадать,
то слева от имени нужного столбца прибавляется <спецификатор> — имя таблицы или псевдоним таблицы и точка.
Такая конструкция называется уточненным именем столбца:
select COUNTRY.CODCOUNTRY
from country;
Для любого столбца в списке выбора можно задать псевдоним после ключевого слова AS. Псевдонимом может быть
любой правильный идентификатор. Допустимо также использование имен с ограничителями. Например,
select COUNTRY.CODCOUNTRY AS "Код страны"
from country;
Если для отдельных столбцов были заданы псевдонимы, то при использовании для отображения строк
таблицы утилиты isql (или менеджера баз данных с графическим интерфейсом) именно псевдонимы столбцов,
а не их имена в таблицах базы данных будут присутствовать в заголовках столбцов. Кроме того,
псевдонимы столбцов могут быть использованы в предложениях ORDER BY и GROUP BY. Если
для столбцов были заданы псевдонимы, то в большинстве конструкций оператора SELECT могут
быть использованы как имена столбцов, так и их псевдонимы.
Предупреждение
Алиасы (псевдонимы) заменяют оригинальное имя таблицы/представления/хранимой процедуры: как только определён алиас для соответствующего отношения, использовать оригинальное имя нельзя.
Перед списком столбцов можно указать необязательное ключевое слово DISTINCT или ALL:
DISTINCTисключает дубликаты строк. То есть, если несколько строк имеют одинаковые значения в каждом столбце, только одна из них будет включена в результирующий набор;ALLиспользуется по умолчанию: возвращаются все строки, включая дубликаты.
Выражение COLLATE не изменяет содержимое поля, однако, если указать COLLATE для определённого поля,
то это может изменить чувствительность к регистру символов или к акцентам (accent sensitivity),
что, в свою очередь, может повлиять на:
Порядок сортировки, если это поле указано в выражении
ORDER BY;Группировку, если это поле указано в выражении
GROUP BY;Количество возвращаемых строк, если используется
DISTINCT.
DISTINCT, ALL
Перед списком полей могут быть добавлены ключевые слова DISTINCT или ALL.
Листинг 12.4 Синтаксис предложения DISTINCT, ALL
SELECT
[DISTINCT | ALL] <выходное поле> [, <выходное поле> ...]
...
FROM ...
Ключевое слово DISTINCT исключает дубликаты строк из результирующего набора.
То есть, если несколько строк имеют одинаковые значения в каждом столбце, только одна из них будет включена в результирующий набор.
Ключевое слово ALL, используемое по умолчанию, означает, что в результирующий набор данных
попадут все строки исходных таблиц, которые соответствуют условиям выборки (предложение ON
в соединении таблиц и предложение WHERE), в том числе и дубликаты.
FIRST и SKIP
После ключевого слова SELECT может следовать указание, какое количество полученных при выполнении
этого оператора строк исходной таблицы (исходных таблиц) должно помещаться в выходной набор данных.
Листинг 12.5 Синтаксис предложения FIRST, SKIP
SELECT
[FIRST <значение>] [SKIP <значение>]
FROM ...
...
<значение> ::=
<целочисленный литерал>
| <параметр запроса>
| (<выражение>)
В качестве <значение> может выступать целочисленный литерал, параметр запроса (? — в DSQL и :paramname — в PSQL)
или выражение, возвращающее целочисленное значение. Если возвращается дробное число, то десятичные знаки просто
отбрасываются без округления. Любой аргумент FIRST и SKIP, не являющийся целочисленным литералом или параметром SQL,
должен быть заключен в круглые скобки.
Это означает, что выражение подзапроса должно быть заключено в две пары круглых скобок.
Отрицательные значения в SKIP и FIRST приводят к ошибке.
Примечание
Предложения FIRST и SKIP не входят в стандарт SQL.
Используйте стандартные предложения OFFSET и FETCH везде, где это возможно.
Предложение FIRST m ограничивает вывод запроса первыми m строками. Нумерация строк начинается с 1.
Если вывод запроса содержит меньше m строк, то в результирующий набор войдут все вернувшиеся строки,
без каких-либо сообщений. FIRST 0 допускается и возвращает пустой набор.
Предложение SKIP n пропускает первые n строк в выводе запроса, исключая их из результирующего набора.
Если вывод запроса содержит меньше n строк, то результирующий набор будет пустым.
SKIP 0 допускается, но не имеет смысла.
В одном операторе SELECT можно указать сразу и FIRST, и SKIP. При совместном использовании,
например, FIRST m SKIP n, n первых строк вывода отбрасываются и возвращаются первые m строк оставшейся части.
Например, если задать
SELECT FIRST 10 ...
то в набор данных будут помещены первые 10 выбранных строк.
Если в операторе указать
SELECT SKIP 9 ...
то в набор данных будут помещены строки, начиная с десятой.
Если же задать
SELECT FIRST 10 SKIP 9 ...
то в набор данных будет помещены 10 строк, начиная с 10-ой.
Например, если нужно выбрать только первую половину строк из таблицы FIRM, то можно задать
предложение FIRST в виде следующего выражения:
SELECT FIRST ((SELECT COUNT (*) FROM COUNTRY) / 2) ...
Использование ключевых слов FIRST и/или SKIP не требует обязательного
присутствия в операторе SELECT предложения ORDER BY, как в случае
использования предложения ROWS.
Ключевые слова FIRST и SKIP не могут присутствовать в операторе SELECT,
где указано предложение ROWS.
Количество помещаемых в результирующий набор данных строк может также задаваться (корректироваться)
при использовании предложений ORDER BY и ROWS, а также предложений OFFSET, FETCH.
FROM
Предложение FROM задает источники, из которых осуществляется выборка данных. В качестве источников
могут выступать таблицы, представления, хранимые процедуры выбора, получающие данные из одной
или нескольких таблиц, или общее табличное выражение (CTE). Эти таблицы, представления, процедуры могут
комбинироваться с использованием разнообразных видов соединений (JOIN), но об этом будет рассказано в другом разделе.
Листинг 12.6 Синтаксис предложения FROM в операторе SELECT
SELECT
...
FROM <источник> [, <источник> ...]
[...]
<источник> ::= <табличный источник> | <соединение>
<табличный источник> ::=
<источник или CTE> [[AS] псевдоним]
| [LATERAL] <производная таблица> [ [AS] <псевдоним> [(<список столбцов>)]]
| <родительская соединённая таблица>
<источник или CTE> ::=
<таблица или представление>
| CTE
| [имя пакета.]<имя процедуры> [(<аргументы процедуры>)]
<аргументы процедуры> ::= <значение> [, <значение> ...]
<производная таблица> ::=
(<SELECT запрос>) [[AS] <псевдоним производной таблицы>]
[(<псевдоним столбца> [, <псевдоним столбца>])]
<список столбцов> ::= <столбец> [, <столбец> ...]
<столбец> ::=
<таблица или псевдоним>
| <представление>
| <CTE>
| <производная таблица>
Предложение FROM позволяет указать несколько таблиц (представлений, хранимых
процедур выбора), разделенных запятыми, где каждая задает соединяемую таблицу. Это
соответствует неявному внутреннему соединению (INNER JOIN). Здесь условие соединения таблиц задается
не предложением ON, как при явном соединении, а присутствует в предложении WHERE. Не рекомендуется
использовать неявное соединение таблиц. Следует явно задавать соединяемые таблицы
(в том числе, полученные из представлений или из хранимых процедур выбора) с использованием ключевого
слова JOIN и условия соединения, указанного в предложении ON. Подробнее о соединении таблиц
см. в разделе .
Предупреждение
Если в предложении FROM для таблицы (представления или хранимой процедуры) указан псевдоним, то во
всех конструкциях оператора SELECT должен обязательно использоваться именно этот псевдоним.
Использование имени таблицы в этом случае недопустимо.
Например, следующий оператор не будет выполнен (вы получите сообщение об ошибке):
SELECT COUNTRY.CODCOUNTRY
FROM COUNTRY C;
Выборка из таблицы или представления
При выборке из таблицы или представления предложение FROM не требует ничего, кроме имени.
Псевдоним (алиас) может быть полезен или даже необходим, если есть подзапросы,
которые ссылаются на основной оператор select.
Такие подзапросы называются коррелированными подзапросами.
Предупреждение
Если для таблицы или представления задан псевдоним,
необходимо всегда использовать этот псевдоним вместо имени таблицы при запросах
(и везде, где есть ссылки на столбцы, например, в предложениях ORDER BY, GROUP BY и WHERE).
SELECT id, name, sex, age
FROM actors
WHERE state = 'Ohio';
Выборка из селективной хранимой процедуры
Селективная хранимая процедура (т.е. с возможностью выборки) должна
содержать хотя бы один выходной параметр и использовать ключевое слово SUSPEND,
чтобы вызывающая сторона могла получать выходные строки по одной, как при выборе из таблицы или представления.
Выходные параметры селективной хранимой процедуры с точки зрения команды SELECT соответствуют
полям обычной таблицы.
Выборка из хранимой процедуры без входных параметров осуществляется точно так же, как обычная выборка из таблицы. Если хранимая процедура требует входные параметры, то они должны быть указаны в скобках после имени процедуры:
SELECT name, az, alt
FROM visible_stars('Brugge' , current_date, '22:30');
Значения для необязательных параметров (то есть, параметров, для которых определены значения по умолчанию) могут быть указаны или опущены. Однако если параметры задаются частично, то пропущенные параметры должны быть в конце перечисления внутри скобок.
Выборка из производной таблицы
В настоящей версии РЕД Базы Данных в предложении FROM можно создавать производные таблицы
(derived tables), которые могут использоваться и в других предложениях того же оператора SELECT.
Синтаксис создания производной таблицы в предложении FROM показан в
листинге .
Листинг 12.7 Синтаксис производной таблицы
<производная таблица> ::= (<оператор SELECT>) [[AS] <псевдоним таблицы>]
[(<псевдоним столбца> [, <псевдоним столбца>...])]
Возвращаемый набор данных такого оператора представляет собой виртуальную таблицу, к которой можно составлять запросы так, как будто это обычная таблица.
Сам оператор SELECT, который создает производную таблицу, заключается в
круглые скобки. Это может быть оператор любой сложности. После него
идет ключевое слово AS, за которым следует имя псевдонима производной
таблицы. По этому имени можно обращаться к производной таблице из
любого оператора, как если бы это была таблица, хранящаяся в базе
данных, или представление, использующее одну или несколько таблиц базы
данных, или хранимая процедура выбора.
После псевдонима таблицы в круглых скобках можно указать список
псевдонимов столбцов, которые были указаны в списке выбора этого
оператора SELECT, создающего (описывающего) производную таблицу.
Количество столбцов в списке выбора оператора SELECT и количество
псевдонимов столбцов должно быть одинаковым. Дальнейшее обращение к
именам столбцов должно выполняться только с использованием имен
псевдонимов, если они были указаны.
Ключевое слово LATERAL обозначает таблицу как боковую производную таблицу.
Боковые производные таблицы могут ссылаться на таблицы (включая другие производные таблицы),
которые встречаются ранее в предложении FROM.
Следующий оператор выбирает список имен и внутренних идентификаторов
всех несистемных таблиц из системной таблицы RDB$RELATIONS. Пример использования производной таблицы:
SELECT *
FROM (SELECT RDB$RELATION_NAME,
RDB$RELATION_ID
FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME NOT STARTING WITH 'RDB$')
AS R ("Таблица" , "Идентификатор");
Оператор отбирает только те таблицы, имена которых не начинаются с
символов 'RDB$', то есть таблицы, созданные пользователем, а не системой. Полученному набору данных
присваивается имя R. Это имя может в дальнейшем использоваться в данном
операторе как имя таблицы базы данных. Двум выбираемым в операторе
столбцам присваиваются псевдонимы "Таблица" и "Идентификатор". Только
по заданным псевдонимам можно будет в этом операторе обращаться к этим столбцам.
Производные таблицы могут быть вложенными.
Производные таблицы могут быть объединениями и использоваться в объединениях. Они могут содержать агрегатные функции, подзапросы и соединения, и сами по себе могут быть использованы в агрегатных функциях, подзапросах и соединениях. Они также могут быть хранимыми процедурами или запросами из них. Они могут иметь предложения
WHERE, ORDER BYиGROUP BY, указанияFIRST, SKIPилиROWSи т.д.Каждый столбец в производной таблице должен иметь имя. Если этого нет по своей природе (например, потому что это — константа), то надо в обычном порядке присвоить псевдоним или добавить список псевдонимов столбцов в спецификации производной таблицы.
Список псевдонимов столбцов опциональный, но если он присутствует, то должен быть полным (т.е. он должен содержать псевдоним для каждого столбца производной таблицы).
Оптимизатор может обрабатывать производные таблицы очень эффективно. Однако если производная таблица включена во внутреннее соединение и содержит подзапрос, то никакой порядок соединения не может быть использован оптимизатором.
Выборка из общих табличных выражений
Общие табличные выражения являются более сложной и более мощной вариацией
производных таблиц. CTE состоят из преамбулы, начинающейся с ключевого слова WITH,
которая определяет одно или более общих табличных выражений (каждое из которых может
иметь список алиасов полей). Основной запрос, который следует за преамбулой, может
обращаться к CTE так, как будто это обычные таблицы. CTE доступны только в рамках основного
запроса.
Подробно CTE описываются в разделе .
Предположим, что у нас есть таблица COEFFS, которая содержит коэффициенты для ряда квадратных
уравнений, которые мы собираемся решить. В зависимости от значений коэффициентов a, b и c,
каждое уравнение может иметь ноль, одно или два решения. Можно найти эти решения с помощью CTE.
WITH vars (b, D, denom) AS
(SELECT b, b*b - 4*a*c, 2*a
FROM coeffs ),
vars2 (b, D, denom, sqrtD) AS
(SELECT b, D, denom, IIF (D >= 0, sqrt(D), NULL)
FROM vars )
SELECT
IIF (D >= 0, (-b - sqrtD) / denom, NULL) AS sol_1,
IIF (D > 0, (-b + sqrtD) / denom, NULL) AS sol_2
FROM vars2;
JOIN
Соединения объединяют данные из двух источников в один набор.
Объединение выполняется по каждой строке и обычно включает проверку условия объединения,
чтобы определить, какие строки должны быть объединены и включены в результирующий набор данных.
Существует несколько типов (INNER, OUTER) и классов (квалифицированные, естественные и т. д.) соединений,
каждый из которых имеет свой собственный синтаксис и правила.
Поскольку соединения можно объединять в цепочки, наборы данных, участвующие в соединении, сами могут быть соединенными наборами.
Синтаксис предложения JOIN:
Листинг 12.8 Синтаксис предложения JOIN
SELECT
...
FROM <источник> [, <источник> ...]
[...]
<источник> ::= <табличный источник> | <соединение>
<табличный источник> ::=
<источник или CTE> [[AS] псевдоним]
| [LATERAL] <производная таблица> [ [AS] <псевдоним> [(<список столбцов>)]]
| <родительская соединённая таблица>
<источник или CTE> ::=
<таблица или представление>
| CTE
| [имя пакета.]<имя процедуры> [(<аргументы процедуры>)]
<аргументы процедуры> ::= <значение> [, <значение> ...]
<производная таблица> ::=
(<SELECT запрос>) [[AS] <псевдоним производной таблицы>]
[(<псевдоним столбца> [, <псевдоним столбца>])]
<список столбцов> ::= <столбец> [, <столбец> ...]
<столбец> ::=
<таблица или псевдоним>
| <представление>
| <CTE>
| <производная таблица>
<родительская соединённая таблица> ::=
(<родительская соединённая таблица>)
| (<соединение>)
<соединение> ::=
<перекрёстное соединение>
| <естественное соединение>
| <квалифицированное соединение>
<перекрёстное соединение> ::=
<источник> CROSS JOIN <табличный источник>
<естественное соединение> ::=
<источник> NATURAL [<тип соединения>] JOIN <табличный источник>
<тип соединения> ::= INNER | { LEFT | RIGHT | FULL } [OUTER]
<квалифицированное соединение> ::=
<источник> [<тип соединения>] JOIN <табличный источник>
{ ON <условие соединения>
| USING (<список столбцов>) }
Если задается предложение USING, то предложение ON должно отсутствовать.
В предложении USING перечисляются имена столбцов, которые должны
присутствовать в обеих соединяемых таблицах. В этом случае условием
соединения является равенство значений указанных столбцов в таблицах.
Неявное соединение
Неявное соединение дает декартово произведение двух множеств строк обеих соединяемых таблиц.
Каждая строка левой таблицы соединяется с каждой строкой правой таблицы. Но если в предложении
WHERE добавить условие соединения, то результат эквивалентен операции INNER JOIN с таким же условием.
Листинг 12.9 Синтаксис неявного соединения
SELECT ...
FROM <источник> , <источник> [, <источник>...]
[WHERE <условие>]
Предупреждение
В настоящее время синтаксис неявных соединений не рекомендуется к использованию.
Внутреннее (INNER) соединение
Для внутреннего (INNER JOIN) соединения тип соединения можно не указывать — соединение является внутренним по умолчанию.
Результат соединения логически формируется следующим образом: каждая строка одной таблицы сопоставляется
с каждой строкой второй таблицы, после чего для полученной "соединённой" строки проверяется условие
соединения (заданное в предложении ON). Если условие истинно, в таблицу-результат добавляется соответствующая "соединённая" строка.
Порядок таблиц для оператора неважен, поскольку оператор является коммутативным.
Предположим, у нас есть таблица PEOPLE:
COD |
NAME |
SEX |
|---|---|---|
34 |
Ivan |
0 |
56 |
Ruslan |
0 |
109 |
Ann |
1 |
и таблица STAFF:
CODPEOPLE |
CODORG |
DUTIES |
|---|---|---|
56 |
12346 |
accountant |
78 |
35456 |
programmer |
109 |
46743 |
manager |
Следующий запрос соединит таблицы:
SELECT *
FROM PEOPLE
JOIN STAFF ON PEOPLE.COD = STAFF.CODPEOPLE;
COD NAME SEX CODPEOPLE CODORG DUTIES
=== ====== === ========= ====== ==========
56 Ruslan 0 56 12346 accountant
109 Ann 1 109 46743 manager
Можно указать слово INNER, но обычно оно опускается.
Точно такой же результат можно получить, выполнив следующий оператор SELECT:
SELECT *
FROM PEOPLE, STAFF
WHERE PEOPLE.COD = STAFF.CODPEOPLE;
Это неявное внутреннее соединение. Условие соединения в этом случае задается в предложении WHERE.
Внешние (OUTER) соединения
В результат внешнего соединения обязательно входят все строки либо одной, либо обеих таблиц.
Внешние соединения (OUTER JOIN) бывают левыми (LEFT), правыми (RIGHT) и полными (FULL).
При указании слов LEFT, RIGHT, FULL слово OUTER можно опустить.
Результат левого внешнего соединения (LEFT OUTER JOIN) логически формируется следующим образом:
в результат включается внутреннее соединение (INNER JOIN) левой и правой таблиц по условию
(заданному в предложении ON). Затем в результат добавляются те строки левой таблицы, которые
не вошли во внутреннее соединение. Для таких строк столбцы, соответствующие правой таблице, заполняются значениями NULL.
Порядок таблиц для оператора важен, поскольку оператор не является коммутативным.
Пусть требуется для каждого сотрудника таблицы STAFF по коду получить его имя из
таблицы PEOPLE. Следующий оператор выполняет необходимую выборку:
SELECT
PEOPLE.NAME AS "Сотрудник",
SEX AS "Пол",
DUTIES AS "Должность"
FROM STAFF
LEFT OUTER JOIN PEOPLE
ON STAFF.CODPEOPLE = PEOPLE.COD;
Сотрудник Пол Должность
========= ====== ==========
Ruslan 0 accountant
<null> <null> programmer
Ann 1 manager
Слово OUTER можно опустить.
Правое внешнее соединение (RIGHT OUTER JOIN) отличается от левого
внешнего соединения только порядком выполнения соединения таблиц. При
левом внешнем соединении действия выполняются слева направо, при правом
же внешнем соединении наоборот — справа налево.
Результат правого внешнего соединения логически формируется следующим образом: в результат включается
внутреннее соединение (INNER JOIN) левой и правой таблиц по условию (заданному в предложении ON).
Затем в результат добавляются те строки правой таблицы, которые не вошли во внутреннее соединение.
Для таких строк столбцы, соответствующие левой таблице, заполняются значениями NULL.
Порядок таблиц для оператора важен, поскольку оператор не является коммутативным.
Пусть требуется для каждого человека таблицы PEOPLE по коду получить его должность из
таблицы STAFF. Следующий оператор выполняет необходимую выборку:
SELECT
PEOPLE.NAME AS "Сотрудник",
SEX AS "Пол",
DUTIES AS "Должность"
FROM STAFF
RIGHT JOIN PEOPLE
ON STAFF.CODPEOPLE = PEOPLE.COD;
Сотрудник Пол Должность
========= ====== ==========
Ivan 0 <null>
Ruslan 0 accountant
Ann 1 manager
Результат полного внешнего соединения (FULL OUTER JOIN) логически формируется следующим образом:
в результат включается внутреннее соединение (INNER JOIN) левой и правой таблиц по условию
(заданному в предложении ON). Затем добавляются те строки первой таблицы, которые не вошли во
внутреннее соединение. Для таких строк столбцы, соответствующие второй таблице, заполняются
значениями NULL. И потом добавляются те строки второй таблицы, которые не вошли во внутреннее
соединение на шаге 1. Для таких строк столбцы, соответствующие первой таблице, заполняются значениями NULL.
Порядок таблиц для оператора неважен, поскольку оператор является коммутативным.
Пусть требуется для каждого человека таблицы PEOPLE по коду получить его должность
из таблицы STAFF и для каждого сотрудника таблицы STAFF по коду получить его имя из таблицы
PEOPLE. Следующий оператор выполняет необходимую выборку:
SELECT
PEOPLE.NAME AS "Сотрудник",
SEX AS "Пол",
DUTIES AS "Должность"
FROM STAFF
FULL OUTER JOIN PEOPLE
ON STAFF.CODPEOPLE = PEOPLE.COD;
Сотрудник Пол Должность
========= ====== ==========
Ivan 0 <null>
Ruslan 0 accountant
Ann 1 manager
<null> <null> programmer
Перекрестное (CROSS) соединение
Перекрестное соединение (CROSS JOIN) дает декартово произведение двух множеств строк обеих
соединяемых таблиц. Каждая строка левой таблицы соединяется с каждой строкой правой таблицы.
Листинг 12.10 Синтаксис перекрестного соединения
SELECT ...
FROM <источник> CROSS JOIN <источник>
Порядок таблиц для оператора неважен, поскольку оператор является коммутативным.
Следующие три конструкции эквивалентны.
FROM <таблица1> CROSS JOIN <таблица2>
FROM <таблица1>, <таблица2>
FROM <таблица1> INNER JOIN <таблица2> ON <всегда истинное условие>
SELECT
PEOPLE.NAME AS "Сотрудник",
SEX AS "Пол",
DUTIES AS "Должность"
FROM STAFF
CROSS JOIN PEOPLE;
Сотрудник Пол Должность
========= ====== ==========
Ivan 0 accountant
Ruslan 0 accountant
Ann 1 accountant
Ivan 0 programmer
Ruslan 0 programmer
Ann 1 programmer
Ivan 0 manager
Ruslan 0 manager
Ann 1 manager
Квалифицированные соединения
Квалифицированные соединения задают условия для объединения строк.
Это происходит либо в явном виде в предложении ON, либо в неявном виде в предложении USING.
<квалифицированные соединения> ::=
<источник> [<тип соединения>] JOIN <табличный источник>
{ ON <условие соединения>
| USING (<список столбцов>) }
<тип соединения> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]
Соединения именованными столбцами
В синтаксисе явного соединения есть предложение ON с условием соединения, в котором
может быть указано любое логическое выражение, но, как правило, оно содержит условие
сравнения между двумя участвующими источниками. Довольно часто, это условие — проверка на равенство.
Такие соединения называются эквисоединениями.
Эквисоединения часто сравнивают столбцы, которые имеют одно и то же имя в обеих таблицах.
Для таких соединений мы можем использовать второй тип явных соединений, называемый
соединением именованными столбцами (Named Columns Joins). Соединение именованными
столбцами осуществляются с помощью предложения USING, в котором перечисляются только
имена столбцов.
Соединения именованными столбцами доступны только в диалекте 3.
Листинг 12.11 Синтаксис соединения именованными столбцами
SELECT ...
FROM <источник> [<тип соединения>] JOIN <источник> USING (<список столбцов>)
<тип соединения> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]
Следующий запрос:
SELECT *
FROM flotsam f
JOIN jetsam j
ON f.sea = j.sea AND f.ship = j.ship;
можно переписать, используя предложение USING:
SELECT *
FROM flotsam
JOIN jetsam USING (sea, ship);
Результирующий набор в этом примере будет различаться. Используя предложение ON, выборка будет содержать
каждый из столбцов SEA и SHIP дважды: один раз для таблицы FLOTSAM и один раз для таблицы JETSAM.
Очевидно, что они будут иметь они и те же значения. Результат соединения именованными столбцами, с помощью
предложения USING, будет содержать эти столбцы один раз.
Для внешних (OUTER) соединений именованными столбцами существуют дополнительные
нюансы при использовании SELECT * или неполного имени столбца. Если столбец строки из
одного источника не имеет совпадений со столбцом строки из другого источника, но все равно
должен быть включён результат из-за инструкций LEFT, RIGHT или FULL, то объединяемый
столбец получит не NULL значение. Это достаточно справедливо, но теперь вы не можете
сказать из какого набора левого, правого или обоих пришло это значение. Это особенно
обманывает, когда значения пришли из правой части набора данных, потому что "*" всегда
отображает для комбинированных столбцов значения из левой части набора данных, даже
если используется RIGHT соединение. Лучше избегать "*" в серьёзных запросах и перечислять все имена
столбцов для соединяемых множеств.
Соединения с явным условием
В синтаксисе явного соединения есть предложение ON, с условием соединения,
в котором может быть указано любое логическое выражение, но, как правило,
оно содержит условие сравнения между двумя участвующими источниками.
Довольно часто, это условие - проверка на равенство
(или ряд проверок на равенство объединённых оператором AND) использующая оператор "=".
Такие соединения называются эквисоединениями.
Примеры соединений с явными условиями:
/*
* Выборка всех заказчиков из города Детройт, которые
* сделали покупку.
*/
SELECT *
FROM customers c
JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'
/*
* Тоже самое, но включает в выборку заказчиков, которые
* не совершали покупки.
*/
SELECT *
FROM customers c
LEFT JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'
Естественное соединение
Естественное соединение выполняет эквисоединение по всем одноименным столбцам правой и левой таблицы. Типы данных этих столбцов должны быть совместимыми. Естественные соединения доступны только в диалекте 3.
Листинг 12.12 Синтаксис естественного соединения
SELECT ...
FROM <источник> NATURAL [<тип соединения>] JOIN <источник>
<тип соединения> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]
Создадим две таблицы с некоторыми одноименными столбцами:
CREATE TABLE Table1 (
a BIGINT,
s VARCHAR(12),
ins_date DATE );
CREATE TABLE Table2 (
a BIGINT,
b VARCHAR(12),
x FLOAT,
ins_date DATE );
Естественное соединение таблиц будет происходить по столбцам a и ins_date и два
следующих оператора дадут один и тот же результат:
SELECT *
FROM Table1
NATURAL JOIN Table2;
SELECT *
FROM Table1
JOIN Table2 USING (a, ins_date);
Как и все соединения, естественные соединения являются внутренними соединениями по
умолчанию, но можно превратить их во внешние соединения, указав LEFT, RIGHT или FULL
перед ключевым словом JOIN.
Примечание
Если в двух исходных таблицах не будут найдены одноименные столбцы, то будет выполнен CROSS JOIN.
Смешивание явного и неявного соединения
Смешивание явных и неявных соединений не рекомендуется, но позволяется. Некоторые виды смешивания запрещены в РЕД Базе Данных.
Например, такой запрос вызовет ошибку "Column unknown -TA.COL1":
SELECT *
FROM TA, TB
JOIN TC ON TA.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2;
Это происходит потому, что явный JOIN не может видеть таблицу TA. Однако следующий запрос
будет выполнен без ошибок, поскольку изоляция не нарушена.
SELECT *
FROM TA, TB
JOIN TC ON TB.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2;
Примечание о равенстве
Примечание
Это примечание об операторах равенства и неравенства применимо везде в языке SQL РЕД Базы Данных, а не только в условиях JOIN.
Оператор «=», который явно используется во многих условных соединениях и неявно в соединениях по именованным столбцам и естественных соединениях, сопоставляет только значения со значениями. Согласно стандарту SQL, NULL не является значением, и, следовательно, два NULL не равны и не неравны друг другу. Если нужно, чтобы NULL совпадали друг с другом в объединении, используйте оператор IS NOT DISTINCT FROM. Этот оператор возвращает true, если операнды имеют одинаковое значение или если они оба NULL.
select *
from A join B
on A.id is not distinct from B.code;
Аналогично, если требуется объединить по признаку неравенства, используйте IS DISTINCT FROM,
а не «<>», если нужно, чтобы NULL считался отличающимся от любого значения, а два NULL считались равными:
select *
from A join B
on A.id is distinct from B.code;
Неоднозначные имена полей в соединениях
РЕД База Данных отвергает неполные имена полей в запросе, если эти имена полей существуют в более чем одном наборе данных,
участвующих в объединении.
Это также верно для внутренних эквисоединений, в которых имена полей фигурируют в предложении ON:
SELECT a, b, c
FROM TA
JOIN TB ON TA.a = TB.a
Существует одно исключение из этого правила: соединения по именованным столбцам и естественные соединения,
которые используют неполное имя поля в процессе подбора,
могут использоваться законно. Это же относится и к одноименным объединяемым столбцам.
Для соединений по именованным столбцам эти столбцы должны быть перечислены в предложении USING.
Для естественных соединений это столбцы, имена которых присутствуют в обеих таблицах.
Особенно во внешних соединениях, плоское имя colname является не всегда тем же самым что left.colname или right.colname.
Типы данных могут отличаться, и один из полных столбцов может иметь значение NULL, в то время как другой нет.
В этом случае значение в объединённом, неполном столбце может замаскировать тот факт, что одно из исходных значений отсутствует.
Соединения с хранимыми процедурами
Если соединение происходит с хранимой процедурой, которая не коррелирована с другими потоками данных через входные параметры, то нет никаких особенностей. В противном случае есть одна особенность: потоки, используемые во входных параметрах, должны быть описаны раньше соединения с хранимой процедурой:
SELECT *
FROM MY_TAB
JOIN MY_PROC(MY_TAB.F) ON 1 = 1
Запрос, написанный следующим образом вызовет ошибку:
SELECT *
FROM MY_PROC(MY_TAB.F)
JOIN MY_TAB ON 1 = 1
Соединение с производными таблицами
Производная таблица, определенная с помощью ключевого слова LATERAL,
называется латеральной производной таблицей.
Если производная таблица определена как латеральная, то разрешается ссылаться на другие таблицы в том же предложении FROM,
но только на те, которые были объявлены раньше в предложении FROM.
Без LATERAL каждый подзапрос выполняется независимо и поэтому не может обращаться к другим элементам FROM.
Элемент LATERAL может находиться на верхнем уровне списка FROM или в дереве JOIN.
В последнем случае он может также ссылаться на любые элементы в левой части JOIN,
справа от которого он находится.
Когда элемент FROM содержит ссылки LATERAL, то запрос выполняется следующим образом:
сначала вычисляется значения всех столбцов о которых зависит производная таблица с ключевым словом LATERAL,
затем вычисляется сама производная таблица с LATERAL для каждой полученной записи.
Результирующие строки полученные из производной таблицы с LATERAL соединяются со строками из которых они получены.
В качестве соединений допускается следующие CROSS JOIN и LEFT OUTER JOIN.
Внутреннее соединение также допустимо,
но не рекомендуется, поскольку могут возникнуть проблемы при вычислении условия соединения потоков.
Примеры соединений с боковыми производными таблицами:
select c.name, ox.order_date as last_order, ox.number
from customer c
left join LATERAL (select first 1 o.order_date, o.number
from orders o
where o.id_customer = c.id
order by o.ORDER_DATE desc
) as ox on true;
select dt.population, dt.city_name, c.country_name
from (select distinct country_name from cities) AS c
cross join LATERAL (select first 1 city_name, population
from cities
where cities.country_name = c.country_name
order by population desc
) AS dt;
select salespeople.name, max_sale.amount, customer_of_max_sale.customer_name
from salespeople,
LATERAL (select max(amount) as amount
from all_sales
where all_sales.salesperson_id = salespeople.id
) as max_sale,
LATERAL (select customer_name
from all_sales
where all_sales.salesperson_id = salespeople.id
and all_sales.amount = max_sale.amount
) as customer_of_max_sale;
WHERE
Предложение WHERE ограничивает возвращаемые строки только теми, которые соответствуют указанному условию.
Условие, следующее за ключевым словом WHERE, может быть как простой проверкой (например, «AMOUNT = 3»),
так и многоуровневым сложным выражением, содержащим подвыборки, предикаты, вызовы функций,
математические и логические операторы, контекстные переменные и многое другое.
Листинг 12.13 Синтаксис предложения WHERE
SELECT ...
FROM ...
[WHERE <условие выборки>]
Условие выборки — это логическое выражение возвращающее TRUE, FALSE и возможно UNKNOWN.
Только те строки, для которых условие поиска истинно будут включены в результирующий набор.
Будьте осторожны с возможными получаемыми значениями NULL: если вы отрицаете выражение, дающее
NULL с помощью NOT, то результат такого выражения все равно будет NULL и строка не пройдёт.
SELECT name
FROM wrestlers
WHERE region = 'Europe'
AND weight > ALL (SELECT weight FROM shot_putters
WHERE region = 'Africa');
SELECT SUM(population)
FROM towns
WHERE name LIKE '%dam'
AND province CONTAINING 'land';
В DSQL и ESQL выражение поиска может содержать параметры. Это полезно, если запрос должен
быть повторен несколько раз с разными значениями входных параметров. В строке SQL-запроса,
передаваемого на сервер, вопросительные знаки используются как обозначения для параметров.
Эти вопросительные знаки называются позиционными параметрами, поскольку их можно различить только по их положению в строке.
Библиотеки доступа часто поддерживают именованные параметры в виде
:id, :amount, :a и т.д. Это более удобно для пользователя, библиотека заботится о преобразовании
именованных параметров в позиционные параметры, прежде чем передать запрос на сервер.
Условие поиска может также содержать имена локальных (PSQL) или хостовых (ESQL) переменных, перед которыми ставится двоеточие.
SELECT name, address, phone
FROM stores
WHERE city = ? AND class = ?
SELECT *
FROM pants
WHERE model = :model AND size = :size AND color = :col;
Обратите внимание, что запросы, подобные этим, не могут быть выполнены немедленно, они должны быть предварительно подготовлены. После того как параметризованный запрос был подготовлен, пользователь (или вызывающий код) может подставить значения параметров и выполнять его многократно, подставляя перед каждым вызовом новые значения параметров.
GROUP BY
Предложение GROUP BY позволяет сгруппировать полученные в результате выполнения оператора SELECT
строки. Оно соединяет записи, имеющие одинаковую комбинацию значений полей, указанных в его списке,
в одну запись. Агрегатные функции в списке выбора применяются к каждой группе индивидуально, а не
для всего набора в целом. Предложение может быть использовано в том случае, если в списке выбора
присутствуют как имена столбцов, так и агрегатные функции AVG, COUNT, SUM, MIN, MAX и др..
При этом все столбцы, не являющиеся параметрами агрегатных функций, обязательно должны присутствовать в
предложении GROUP BY. Это основное правило группирования.
Листинг 12.14 Синтаксис предложения GROUP BY
SELECT
<список выборки>
FROM ...
GROUP BY <список группировки> [, <список группировки>];
[HAVING <условие выборки>]
<список группировки> ::=
<неагрегирующее выражение в select>
| <неагрегирующее выражение>
<неагрегирующее выражение> ::=
<копия выражения>
| <псевдоним столбца>
| <позиция столбца>
Здесь в качестве <списка группировки> может выступать:
Дословная копия выражения из списка выбора, не содержащего агрегатной функции.
Псевдоним выражения (столбца) из списка выбора, не содержащего агрегатной функции.
Номер позиции выражения (столбца) из списка выбора, не содержащего агрегатной функции. Позиция представляет собой целое число от 1 до до количества столбцов в списке
SELECT.Столбцы исходной таблицы, которые не включены в список выборки
SELECT, или неагрегатные выражения, основанные на таких столбцах. Добавление таких столбцов может дополнительно разбить группы. Но так как эти столбцы не в списке выборкиSELECT, вы не можете сказать, какому значению столбца соответствует значение агрегированной строки. Таким образом, если вы заинтересованы в этой информации, вы так же должны включить этот столбец или выражение в список выборкиSELECT, что возвращает вас к правилу "каждый не агрегированный столбце в списке выборкиSELECTдолжен быть включён в список группировкиGROUP BY";Выражения, которые не зависят от данных из основного набора, т.е. константы, контекстные переменные, некоррелированные подзапросы, возвращающие единственное значение и т.д. Это упоминается только для полноты картины, т.к. добавление этих элементов является абсолютно бессмысленным, поскольку они не повлияют на группировку вообще. "Безвредные, но бесполезные" элементы так же могут фигурировать в списке выбора
SELECTбез их копирования в список группировкиGROUP BY.
Предупреждение
Если вы группируете по позиции столбца или алиасу, то выражение соответствующее этой позиции (алиасу)
будет скопировано из списка выборки SELECT. Это касается и подзапросов, таким образом, подзапрос
будет выполняться, по крайней мере, два раза.
Если список выборки содержит только агрегатные столбцы или столбцы, значения которых не зависит от
отдельных строк основного множества, то предложение GROUP BY необязательно. Когда предложение
GROUP BY опущено, результирующее множество будет состоять из одной строки (при условии, что хотя
бы один агрегатный столбец присутствует).
SELECT COUNT(*), AVG(age), current_date
FROM students
WHERE sex = 'M';
COUNT AVG CURRENT_DATE
===== === ============
157 11 11.03.2032
Если в списке выборки содержатся как агрегатные столбцы, так и столбцы, чьи значения зависит
от выбираемых строк, то предложение GROUP BY становится обязательным.
SELECT
class,
sex,
boarding_type,
COUNT(*) AS anumber,
AVG(age) AS avg_age
FROM students
GROUP BY class, sex, boarding_type;
CLASS SEX BOARDING_TYPE ANUMBER AVG_AGE
===== === ============= ======= ======
8A F BOARDING 2 12.0
8A F DAY 1 13.0
8A M DAY 3 14.0
8B F BOARDING 2 13.0
8B M BOARDING 1 14.0
8B M DAY 1 14.0
Если при наличии предложения GROUP BY, в предложении SELECT отсутствуют агрегатные функции,
то запрос просто вернет по одной строке из каждой группы.
HAVING
Необязательное предложение HAVING определяет дополнительные условия поиска
(условия выборки строк таблицы/таблиц) для использования в GROUP BY. Предложение HAVING может
использоваться только вместе с предложением GROUP BY.
Предложение HAVING вместе с предложением WHERE, предложениями FIRST,
SKIP, ORDER BY и ROWS сокращает количество отобранных строк в результирующем выходном наборе данных.
Условие выборки в предложении HAVING может ссылаться на:
Любой агрегированный столбец в списке выбора
SELECT. Это наиболее широко используемый вариант.SELECT class, COUNT(*) AS anumber, AVG(age) AS avg_age FROM students WHERE sex = 'M' GROUP BY class HAVING COUNT(*) >= 2;
Любое агрегированное выражение, которое не находится в списке выбора
SELECT, но допустимо в контексте запроса.SELECT class, COUNT(*) AS anumber, AVG(age) AS avg_age FROM students WHERE sex = 'M' GROUP BY class HAVING MAX(age) - MIN(age) > 0.9;
Любой столбец в списке
GROUP BY. Однако более эффективно фильтровать неагрегированные данные на более ранней стадии в предложенииWHERE.SELECT class, COUNT(*) AS anumber, AVG(age) AS avg_age FROM students WHERE sex = 'M' GROUP BY class HAVING class STARTING WITH '8';
SELECT class, COUNT(*) AS anumber, AVG(age) AS avg_age FROM students WHERE sex = 'M' AND class STARTING WITH '8' GROUP BY class;
Любое выражение, значение которого не зависит от содержимого набора данных (например, константа или контекстная переменная). Это допустимо, но совершенно бессмысленно, потому что условие, не имеющее никакого отношения к самому набору данных, либо подавит весь набор, либо оставит его не тронутым.
Предложение HAVING не может содержать:
Не агрегированные выражения столбца, которые не находятся в списке
GROUP BY.Позицию столбца. Целое число в предложении
HAVING– просто целое число.Псевдонимы столбца – даже, если они появляются в предложении
GROUP BY.
WINDOW
Предложение WINDOW предназначено для определения именованных окон, которые используются оконными
функциями. Поскольку выражение окна может быть довольно сложным и может использоваться многократно,
такая функциональность бывает полезной.
Листинг 12.15 Синтаксис предложения WINDOW
SELECT ...
FROM ...
...
WINDOW <спецификация окна> [, <спецификация окна>] ...
<спецификация окна> ::= <имя окна> AS ([<имя окна>] [<выражение секционирования>]
[<выражение сортировки>] [<рамка окна>])
<выражение секционирования> ::= PARTITION BY <выражение> [, <выражение> ...]
<выражение сортировки> ::= ORDER BY <выражение> [ASC | DESC] [NULLS {FIRST | LAST}]
[, <выражение> [ASC | DESC] [NULLS {FIRST | LAST}]...]
<рамка окна> ::= {ROWS | RANGE} { <начало окна> | <окно между значениями>}
<начало окна> ::= UNBOUNDED PRECEDING | <выражение> PRECEDING | CURRENT ROW
<окно между значениями> ::= BETWEEN { UNBOUNDED PRECEDING | <выражение> PRECEDING |
<выражение> FOLLOWING | CURRENT ROW }
AND { UNBOUNDED FOLLOWING | <выражение> PRECEDING |
<выражение > FOLLOWING | CURRENT ROW }
Имя окна может быть использовано в предложении OVER для ссылки на определение окна,
кроме того оно может быть использовано в качестве базового окна для другого именованного
или встроенного (в предложении OVER) окна. Окна с рамкой (с предложениями RANGE и
ROWS) не могут быть использованы в качестве базового окна (но могут быть использованы в
предложении OVER <имя окна>). Окно, которое использует ссылку на базовое окно, не может
иметь предложение PARTITION BY и не может переопределять сортировку с помощью предложения ORDER BY.
Приведен пример использования именованных окон (см. таблицу employee из тестовой БД employee.fdb).
SELECT
emp_no,
dept_no,
salary,
count(*) OVER w1,
first_value(salary) OVER w2,
last_value(salary) OVER w2,
sum(salary) over (w2 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s
FROM employee
WINDOW w1 AS (PARTITION BY DEPT_NO),
w2 AS (w1 ORDER BY salary)
ORDER BY dept_no, salary;
UNION
Предложение UNION позволяет объединить выбранный основным оператором SELECT набор данных
с другим набором данных, имеющим точно такую же структуру, тем самым увеличивая общее количество
строк, но не столбцов. Наборы данных, принимающие участие в UNION, должны иметь одинаковое
количество столбцов. Однако столбцы в соответствующих позициях не обязаны иметь один и тот же
тип данных, они могут быть абсолютно не связанными.
Не следует путать объединение таблиц, UNION, с соединением таблиц, JOIN.
Синтаксис предложения объединения UNION следующий :
Листинг 12.16 Синтаксис предложения UNION
SELECT ...
FROM ...
...
UNION [DISTINCT | ALL] <выборка SELECT>
[UNION [DISTINCT | ALL] <выборка SELECT> ...]
Ключевое слово ALL означает, что в выходном наборе данных могут присутствовать дубликаты строк.
При отсутствии этого ключевого слова или при задании DISTINCT дубликаты строк не помещаются
в выходной набор данных. Отсутствие дубликатов строк в выходном наборе данных предполагается по умолчанию.
Объединения получают имена столбцов из первого запроса на выборку. Если вы хотите дать псевдонимы объединяемым столбцам, то делайте это для списка столбцов в самом верхнем запросе на выборку. Псевдонимы в других участвующих в объединении выборках разрешены и могут быть даже полезными, но они не будут распространяться на уровне объединения.
Если объединение имеет предложение ORDER BY, то единственно возможными элементами сортировки
являются целочисленные литералы, указывающие на позиции столбцов, необязательно сопровождаемые
ASC/DESC и/или NULLS FIRST/LAST директивами. Это так же означает, что вы не можете
упорядочить объединение ничем, что не является столбцом объединения. Однако вы можете завернуть
его в производную таблицу, которая даст вам все обычные параметры сортировки.
Объединения позволены в подзапросах любого вида и могут сами содержать подзапросы.
Они также могут содержать соединения (joins) и могут принимать участие в соединениях,
если завёрнуты в производную таблицу.
Этот запрос представляет информацию из различных музыкальных коллекций в одном наборе данных с помощью объединений:
SELECT id, title, artist, len, 'CD' AS medium
FROM cds
UNION
SELECT id, title, artist, len, 'LP'
FROM records
UNION
SELECT id, title, artist, len, 'MC'
FROM cassettes
ORDER BY 3, 2; -- artist, title
Если id, title, artist и length – единственные поля во всех участвующих таблицах, то запрос может быть записан так:
SELECT c.*, 'CD' AS medium
FROM cds c
UNION
SELECT r.*, 'LP'
FROM records r
UNION
SELECT c.*, 'MC'
FROM cassettes c
ORDER BY 3, 2; -- artist, title
Квалификация "звёзд" необходима здесь, потому что они не являются единственным элементом в
списке столбцов. Заметьте, что псевдонимы "c" в первой и третьей выборке не кусают друг
друга. Они не имеют контекста объединения, а лишь применяются к отдельным запросам на выборку.
Следующий запрос получает имена и телефонные номера переводчиков и корректоров.
Те переводчики, которые также работают корректорами, будут отображены только один раз в
результирующем наборе, если имена и номера их телефонов одинаковые в обеих таблицах. Тот же результат
может быть получен без ключевого слова DISTINCT. Если вместо ключевого слова DISTINCT,
будет указано ключевое слово ALL, эти люди будут отображены дважды.
SELECT name, phone
FROM translators
UNION DISTINCT
SELECT name, telephone
FROM proofreaders;
Пример использования UNION в подзапросе:
SELECT name, phone, hourly_rate
FROM clowns
WHERE hourly_rate < ALL
(SELECT hourly_rate FROM jugglers
UNION
SELECT hourly_rate FROM acrobats)
ORDER BY hourly_rate;
PLAN
При обработке оператора выборки данных SELECT оптимизатор сервера базы данных строит план,
который определяет, в каком порядке и с использованием каких механизмов (при помощи индексов или при
последовательном переборе с возможным дополнительным упорядочиванием) будут выбираться данные из таблиц
для получения выходного набора данных. Как правило, такой план является не худшим решением. При этом у
вас есть возможность задать свой план поиска, основываясь на каких-то характеристиках базы данных,
например, на размерах различных индексов, количестве строк в таблицах и др. В некоторых случаях грамотно
составленный собственный план может повысить производительность системы при выборке данных.
Утилита ISQL имеет возможность отобразить пользователю план извлечения данных с помощью
команды SET PLAN ON. Для того, чтобы просто изучить план запроса (без выполнения самого запроса)
необходимо ввести команду SET PLANONLY ON. Более подробный план можно получить при включении
расширенного плана с помощью команды SET EXPLAIN ON.
Синтаксис для задания плана выборки представлен в листинге .
Листинг 12.17 Синтаксис предложения PLAN
SELECT ...
FROM ...
...
PLAN <выражение для плана поиска>
<выражение для плана поиска> ::=
(<элемент плана> [,<элемент плана> ...])
| SORT (<элемент плана>)
| JOIN (<элемент плана> [,<элемент плана> ...])
| [SORT] MERGE (<элемент плана> [,<элемент плана> ...])
| HASH (<элемент плана> [,<элемент плана> ...])
<элемент плана> ::= <основной элемент> | <выражение для плана поиска>
<основной элемент> ::=
{ <имя/псевдоним таблицы> | <имя представления> }
{ NATURAL
| INDEX (<имя индекса> [, <имя индекса> ...])
| ORDER <имя индекса> [ INDEX (<имя индекса> [, <имя индекса> ...]) ] }
В этом синтаксисе можно видеть, что выражение для плана поиска допускает вложенные конструкции. Синтаксис для плана получается довольно богатым по своим возможностям.
Если выполняется запрос к нескольким таблицам, то на основании плана осуществляется выборка
данных из каждой таблицы. Результатом одной такой выборки является промежуточный набор данных,
который иногда называют потоком (stream). На следующем этапе выполняется соединение (JOIN)
или объединение, или же слияние (MERGE) промежуточных наборов данных (потоков) в один
результирующий набор данных, который и является результатом выполнения оператора SELECT.
В выражении плана в самом начале может присутствовать тип соединения. Используются типы
соединения JOIN и MERGE.
JOIN — тип соединения по умолчанию. В этом случае с левым промежуточным набором данных
соединяются строки правого набора данных.
MERGE означает, что сливаются, объединяются два промежуточных набора данных — к левому
промежуточному набору данных присоединяются строки правого набора данных. Ключевое слово SORT
требует предварительной сортировки обоих наборов данных.
В элементе плана присутствует имя или псевдоним таблицы. Если для соответствующей таблицы был задан псевдоним, то и в элементе плана может присутствовать только псевдоним, но не имя таблицы.
После имени или псевдонима таблицы задаются ключевые слова NATURAL, INDEX или ORDER.
NATURAL (значение по умолчанию) означает, что все строки таблицы просматриваются последовательно
страница за страницей вне какого-нибудь порядка и без использования каких-либо индексов.
Ключевое слово INDEX и следующий за ним в скобках список имен индексов данной таблицы
задают использование указанных индексов для проверки условий соединения в запросе.
Ключевое слово ORDER указывает, что строки промежуточного набора данных должны быть
упорядочены с использованием заданного индекса или соответствующего ограничения
первичного, уникального или внешнего ключа.
Реализация действий по поиску данных на основании плана выполняется слева направо. При этом действия в круглых скобках выполняются, как обычно, в первую очередь.
В сложных запросах используются вложенные выражения для плана поиска. Соответствующие примеры планов будут рассмотрены далее.
Примеры простых планов
Здесь будут рассмотрены планы, которые строит оптимизатор запросов при обработке
оператора выборки данных SELECT.
Далее в листингах будут приведены примеры запросов и сразу под ними планы в обычной и EXPLAIN форме
(см. таблицы тестовой базы данных employee).
Выбираются все столбцы всех строк таблицы JOB:
SELECT * FROM JOB;
PLAN (JOB NATURAL)
Select Expression
-> Table "JOB" Full Scan
Если есть предложение WHERE, то указывается индекс, который будет
использоваться при нахождении совпадений (если такой существует):
SELECT * FROM JOB
WHERE JOB_COUNTRY = 'JOB_COUNTRY';
PLAN (JOB INDEX (RDB$FOREIGN3))
Select Expression
-> Filter
-> Table "JOB" Access By ID
-> Bitmap
-> Index "RDB$FOREIGN3" Range Scan (full match)
В следующем запросе задается упорядоченность по столбцу, являющемуся первичным ключом таблицы.
SELECT * FROM JOB
ORDER BY JOB_CODE;
PLAN (JOB ORDER RDB$PRIMARY2)
Select Expression
-> Table "JOB" Access By ID
-> Index "RDB$PRIMARY2" Full Scan
В элементе плана задается упорядочение строк промежуточного набора данных с использованием
индекса RDB$PRIMARY2, построенного системой автоматически для поддержания значений первичного
ключа таблицы JOB.
Эквивалентным этому запросу будет запрос, содержащий такой план:
SELECT *
FROM JOB
PLAN SORT (JOB INDEX (RDB$PRIMARY2))
ORDER BY JOB_CODE;
Для запроса, содержащего упорядочение по столбцу, не являющемуся ключевым и
не входящему в состав какого-нибудь индекса будет построен план, в котором сразу после
ключевого слова PLAN указывается упорядочение полученного набора данных.
SELECT * FROM JOB
ORDER BY JOB_TITLE;
PLAN SORT (JOB NATURAL)
Select Expression
-> Sort (record length: 126, key length: 32)
-> Table "JOB" Full Scan
В отличие от предыдущего примера, где из базы данных сразу выбирался упорядоченный набор данных,
здесь выборка данных будет происходить в два этапа. В начале в результате последовательного
(NATURAL) перебора строк таблицы будет сформирован промежуточный набор данных. Затем этот
набор данных будет отсортирован в соответствии с заданными условиями упорядочения, указанными
в предложении ORDER BY.
В следующем запросе выбираются строки из таблицы JOB. Результат упорядочивается
по столбцу "Страна работы", который входит в состав первичного ключа таблицы и в то же время является
внешним ключом, ссылающимся на первичный ключ родительской таблицы — справочника стран COUNTRY. План задает
выборку строк в порядке, указанном в индексе, который был автоматически создан системой для
внешнего ключа (RDB$FOREIGN3).
SELECT * FROM JOB
ORDER BY job_country;
PLAN (JOB ORDER RDB$FOREIGN3)
Select Expression
-> Table "JOB" Access By ID
-> Index "RDB$FOREIGN3" Full Scan
Оптимизатор, естественно, выбирает упорядочение отыскиваемых строк на основании внешнего ключа.
Если в операторе SELECT при выборке вакансий в предложении ORDER BY указать
столбцы, входящие в состав первичного ключа (код вакансии, должность и название страны) в правильном порядке,
то оптимизатор выберет упорядочение строк набора данных по первичному ключу.
SELECT * FROM JOB
ORDER BY job_code, job_grade, job_country;
PLAN (JOB ORDER RDB$PRIMARY2)
Select Expression
-> Table "JOB" Access By ID
-> Index "RDB$PRIMARY2" Full Scan
Однако если в списке упорядочения переставить местами столбцы или изменить направление упорядочения на убывающее, то в такой таблице не найдется подходящего индекса.
SELECT * FROM JOB
ORDER BY job_grade, job_code, job_country;
PLAN SORT (JOB NATURAL)
Select Expression
-> Sort (record length: 136, key length: 44)
-> Table "JOB" Full Scan
Здесь последовательно будут выбраны соответствующие строки, а затем на втором этапе промежуточный набор данных упорядочивается нужным образом.
При создании первичного ключа для таблицы вакансий можно указать предложение USING,
в котором задать упорядочение индекса по убыванию значений столбцов, входящих в его состав.
В этом случае выборка данных с использованием предыдущего оператора будет проходить много быстрее.
В следующем запросе данные выбираются также из таблицы вакансий, однако здесь
присутствует предложение WHERE, задающее выборку не всех строк, а только тех, которые
удовлетворяют указанному условию. В условии выборки используется столбец, являющийся внешним
ключом таблицы. При этом никакая упорядоченность результата не задается. Оптимизатор построит
план, в котором используется индекс, автоматически построенный системой для ограничения внешнего ключа.
SELECT * FROM JOB
WHERE job_country = 'USA';
PLAN (JOB INDEX (RDB$FOREIGN3))
Select Expression
-> Filter
-> Table "JOB" Access By ID
-> Bitmap
-> Index "RDB$FOREIGN3" Range Scan (full match)
Использование индекса внешнего ключа в данном случае позволяет резко ускорить выборку релевантных данных.
В предыдущий запрос можно ввести требование упорядоченности результирующего
набора данных по столбцам, входящим в состав первичного ключа. Созданный оптимизатором план
теперь будет включать использование индекса первичного ключа для упорядочения строк набора
данных на основании предложения ORDER BY.
SELECT * FROM JOB
WHERE job_code = 'SRep'
ORDER BY job_code, job_grade, job_country;
PLAN (JOB ORDER RDB$PRIMARY2)
Select Expression
-> Filter
-> Table "JOB" Access By ID
-> Index "RDB$PRIMARY2" Range Scan (partial match: 1/3)
Примеры составных планов
При выполнении более сложного запроса, включающего объединения (UNION) трех таблиц,
оптимизатор создаст три плана для каждого оператора SELECT.
SELECT
COD AS "Внутренний код",
CODPEOPLE AS "Код человека",
DATEADMIN AS "Дата",
CODADMIN AS "Код нарушения/награды",
'Нарушение' AS "Вид"
FROM PEOPLEADMIN
UNION
SELECT
COD AS "Внутренний код",
CODPEOPLE AS "Код человека",
DATESPEC AS "Дата",
CODREW AS "Код нарушения/награды",
'Награда' AS "Вид"
FROM PEOPLEREW
UNION
SELECT
COD AS "Внутренний код",
CODPEOPLE AS "Код человека",
DATEADMIN AS "Дата",
CODADMIN AS "Код нарушения/награды",
'Нарушение' AS "Вид"
FROM PEOPLEADMIN
ORDER BY 1;
PLAN SORT (PEOPLEADMIN NATURAL, PEOPLEREW NATURAL, PEOPLEADMIN NATURAL)
Select Expression
-> Unique Sort (record length: 102, key length: 56)
-> Union
-> Table "PEOPLEADMIN" Full Scan
-> Table "PEOPLEREW" Full Scan
-> Table "PEOPLEADMIN" Full Scan
Для каждого из оператора выборки, заданных в предложении UNION, планы предусматривают
последовательный перебор всех записей таблиц. Затем промежуточные наборы данных объединяются
в один набор данных, строки которого сортируются по первому столбцу полученного набора
данных (в планах не отражено).
SELECT выполняет двойное левое внешнее соединение таблицы с этой же таблицей.SELECT
PG.FULLNAME AS "Фамилия, имя, отчество",
PM.NAME3 AS "Мать",
PF.NAME3 AS "Отец"
FROM PEOPLE PG /* Главная таблица */
LEFT OUTER JOIN PEOPLE PM /* Мать */
ON PG.CODMOTHER = PM.COD
LEFT OUTER JOIN PEOPLE PF /* Отец */
ON PG.CODFATHER = PF.COD
ORDER BY PG.FULLNAME;
PLAN JOIN (SORT (JOIN (PG NATURAL, PM INDEX (RDB$PRIMARY1))), PF INDEX (RDB$PRIMARY1))
Select Expression
-> Nested Loop Join (outer)
-> Sort (record length: 270, key length: 108)
-> Nested Loop Join (outer)
-> Table "PEOPLE" as "PG" Full Scan
-> Filter
-> Table "PEOPLE" as "PM" Access By ID
-> Bitmap
-> Index "RDB$PRIMARY1" Unique Scan
-> Filter
-> Table "PEOPLE" as "PF" Access By ID
-> Bitmap
-> Index "RDB$PRIMARY1" Unique Scan
По этому плану видно, что строки главной таблицы выбираются последовательным перебором страниц базы данных. Для выборки соответствующих строк обеих соединяемых таблиц (это та же самая таблица) используется индекс, автоматически построенный системой для первичного ключа. Вначале к набору данных присоединяются данные из первой соединяемой таблицы (сведения о матери), затем к полученному набору данных присоединяются при использовании того же индекса данные из второй соединяемой таблицы (сведения об отце). После этого выполняется сортировка полученного набора данных.
Здесь в предложении ORDER BY для упорядочения результирующего набора данных используется вычисляемый
столбец, для которого в базе данных не существует никакого индекса. Если же выполнить упорядочение набора
данных по первичному ключу COD (в операторе SELECT возможно упорядочение и по столбцам, которые
не присутствуют в списке выбора оператора), то план будет выглядеть несколько иначе.
SELECT
PG.FULLNAME AS "Фамилия, имя, отчество",
PM.NAME3 AS "Мать",
PF.NAME3 AS "Отец"
FROM PEOPLE PG /* Главная таблица */
LEFT OUTER JOIN PEOPLE PM /* Мать */
ON PG.CODMOTHER = PM.COD
LEFT OUTER JOIN PEOPLE PF /* Отец */
ON PG.CODFATHER = PF.COD
ORDER BY PG.COD;
PLAN JOIN (JOIN (PG ORDER RDB$PRIMARY1, PM INDEX (RDB$PRIMARY1)), PF INDEX (RDB$PRIMARY1))
Select Expression
-> Nested Loop Join (outer)
-> Nested Loop Join (outer)
-> Table "PEOPLE" as "PG" Access By ID
-> Index "RDB$PRIMARY1" Full Scan
-> Filter
-> Table "PEOPLE" as "PM" Access By ID
-> Bitmap
-> Index "RDB$PRIMARY1" Unique Scan
-> Filter
-> Table "PEOPLE" as "PF" Access By ID
-> Bitmap
-> Index "RDB$PRIMARY1" Unique Scan
В этом случае не требуется выполнять дополнительную сортировку результирующего набора данных.
Строки из главной таблицы выбираются уже в упорядоченном виде, поскольку используется индекс
первичного ключа, по которому требуется отсортировать результирующий набор данных, как задано
в предложении ORDER BY. Выборка соответствующих строк из соединяемых таблиц осуществляется
точно так же, как и в предыдущем запросе.
Выполнение довольно сложного запроса, который содержит как объединения (UNION),
так и соединения (JOIN) нескольких таблиц, приведет к созданию также довольно сложного плана.
Оптимизатор для этого запроса создаст три плана, определяющие порядок выборки данных из главных
таблиц правонарушений и наград, которые указаны в предложениях FROM в операторах SELECT,
и условия их соединения с таблицей людей (PEOPLE) в предложениях ON. Строки из всех таблиц
выбираются последовательным перебором и для каждой строится HASH-таблица. Связь между главными и
соединяемыми таблицам осуществляется через построенные HASH-таблицы..
SELECT *
FROM (SELECT
P.FULLNAME AS "Сотрудник",
DATEADMIN AS "Дата",
CODADMIN AS "Код нарушения/награды"
FROM PEOPLEADMIN
INNER JOIN PEOPLE P
ON PEOPLEADMIN.CODPEOPLE = P.COD
UNION
SELECT
P.FULLNAME AS "Сотрудник",
DATESPEC AS "Дата",
CODREW AS "Код нарушения/награды"
FROM PEOPLEREW
INNER JOIN PEOPLE P
ON PEOPLEREW.CODPEOPLE = P.COD
UNION ALL
SELECT
P.FULLNAME AS "Сотрудник",
DATEADMIN AS "Дата",
CODADMIN AS "Код нарушения/награды"
FROM PEOPLEADMIN
INNER JOIN PEOPLE P
ON PEOPLEADMIN.CODPEOPLE = P.COD)
ORDER BY "Сотрудник";
PLAN SORT (SORT (JOIN (P NATURAL, PEOPLEADMIN INDEX (RDB$FOREIGN3)), JOIN (P NATURAL, PEOPLEREW INDEX (RDB$FOREIGN5))), JOIN (P NATURAL, PEOPLEADMIN INDEX (RDB$FOREIGN3)))
Select Expression
-> Sort (record length: 194, key length: 108)
-> Union
-> Unique Sort (record length: 186, key length: 124)
-> Union
-> Nested Loop Join (inner)
-> Table "PEOPLE" as "P" Full Scan
-> Filter
-> Table "PEOPLEADMIN" Access By ID
-> Bitmap
-> Index "RDB$FOREIGN3" Range Scan (full match)
-> Nested Loop Join (inner)
-> Table "PEOPLE" as "P" Full Scan
-> Filter
-> Table "PEOPLEREW" Access By ID
-> Bitmap
-> Index "RDB$FOREIGN5" Range Scan (full match)
-> Nested Loop Join (inner)
-> Table "PEOPLE" as "P" Full Scan
-> Filter
-> Table "PEOPLEADMIN" Access By ID
-> Bitmap
-> Index "RDB$FOREIGN3" Range Scan (full match)
Действия по выборке данных выполняются в соответствии с этими планами. В результате будет получено три промежуточных набора данных, которые затем объединяются в результирующий набор данных и сортируются по первому столбцу (упорядочивание результата в плане не отражено).
В следующем запросе присутствуют агрегатные функции, выполняется полное внешнее соединение и группировка результата.
SELECT
AVG(SALARY) AS "Среднее",
MAX(SALARY) AS "Максимум",
MIN(SALARY) AS "Минимум",
COUNT(*) AS "Количество",
O.NAME AS "Организация"
FROM STAFF S
FULL OUTER JOIN FIRM O
ON O.COD = S.CODORG
GROUP BY "Организация"
ORDER BY 5 COLLATE NONE;
PLAN SORT (SORT (JOIN (JOIN (O NATURAL, S INDEX (RDB$FOREIGN3)), JOIN (S NATURAL, O INDEX (RDB$PRIMARY1)))))
Select Expression
-> Sort (record length: 278, key length: 108)
-> Aggregate
-> Sort (record length: 166, key length: 108)
-> Full Outer Join
-> Nested Loop Join (outer)
-> Table "FIRM" as "O" Full Scan
-> Filter
-> Table "STAFF" as "S" Access By ID
-> Bitmap
-> Index "RDB$FOREIGN3" Range Scan (full match)
-> Nested Loop Join (outer)
-> Table "STAFF" as "S" Full Scan
-> Filter
-> Table "FIRM" as "O" Access By ID
-> Bitmap
-> Index "RDB$PRIMARY1" Unique Scan
ORDER BY
Результат выборки данных при выполнении оператора SELECT по умолчанию никак не упорядочивается
(фактически происходит упорядочение в хронологическом порядке помещения строк в таблицу операторами
INSERT). Предложение ORDER BY позволяет задать при выборке данных из таблицы необходимый
порядок. Синтаксис предложения представлен в листинге :
Листинг 12.18 Синтаксис предложения ORDER BY
SELECT ...
FROM ...
ORDER BY <определение сортировки> [, <определение сортировки> ... ]
<определение сортировки> ::=
<выражение> [<определение порядка>] [<поведение для null>]
<определение порядка> ::=
ASC | ASCENDING
| DESC | DESCENDING
<поведение для null> ::=
NULLS FIRST
| NULLS LAST
ORDER BY состоит из разделенного запятыми списка столбцов или выражений, по которым должен быть отсортирован полученный набор данных.
Порядок сортировки может быть задан именем столбца,
но только если этому столбцу ранее не был присвоен псевдоним в списке столбцов SELECT.
Псевдоним должен быть использован, если он использовался в списке выбора.
Порядковый номер столбца в списке столбцов SELECT, псевдоним,
присвоенный столбцу в списке SELECT с помощью ключевого слова AS,
или номер столбца в списке SELECT могут использоваться без ограничений.
Все три формы выражения столбцов для порядка сортировки можно смешивать в одном предложении ORDER BY.
Например, один столбец в списке может быть указан по имени, а другой - по номеру.
Предупреждение
Если вы сортируете по позиции столбца или его алиасу, то выражение соответствующее этой
позиции (алиасу) будет скопировано из списка выборки SELECT. Это касается и подзапросов,
таким образом, подзапрос будет выполняться, по крайней мере, два раза.
Если используется позиция столбца для указания порядка сортировки в запросах формата SELECT *,
сервер расширяет звездочку до полного списка столбцов, чтобы определить столбцы для сортировки.
Однако такая организация сортировки не считается хорошей практикой.
Ключевое слово ASCENDING задает упорядочение по возрастанию значений. Допустимо
сокращение ASC. Применяется по умолчанию.
Ключевое слово DESCENDING задает упорядочение по убыванию значений. Допустимо
сокращение DESC. В одном предложении упорядочение по одному столбцу может идти
по возрастанию значений, а по другому — по убыванию.
Ключевое слово COLLATE в <выражении> определяет порядок сортировки, который следует применить к строковому столбцу,
если нужен порядок, отличный от того, который был установлен для этого столбца
(явно при описании столбца или по умолчанию, принятому для соответствующего набора символов).
Ключевое слово NULLS определяет, где в сортированном списке будут находиться пустые
значения соответствующего столбца — в начале списка (FIRST) или в конце (LAST).
По умолчанию принимается NULLS FIRST.
Сортировка по псевдонимам столбцов:
SELECT
RDB$CHARACTER_SET_ID AS CHARSET_ID,
RDB$COLLATION_ID AS COLL_ID,
RDB$COLLATION_NAME AS NAME
FROM RDB$COLLATIONS
ORDER BY CHARSET_ID, COLL_ID;
Сортировка частей UNION
Части выборок SELECT, участвующих в объединении UNION, не могут быть отсортированы с использованием
предложения ORDER BY. Однако вы можете достичь желаемого результата с использованием производных
таблиц или общих табличных выражений. Предложение ORDER BY, записанное последним в объединении, будет
применено ко всей выборке в целом, а не к последней его части. Для объединений, единственно возможными
элементами сортировки являются целочисленные литералы, указывающие на позиции столбцов, необязательно
сопровождаемые ASC / DESC и/или NULLS FIRST / LAST директивами.
Сортировка выборки полученной объединением выборок из двух запросов. Выборка сортируется
по убыванию значений второго столбца с размещением NULL значений в конце списка и возрастанием
значений первого столбца с размещением NULL значений в начале списка.
SELECT
DOC_NUMBER, DOC_DATE
FROM PAYORDER
UNION ALL
SELECT
DOC_NUMBER, DOC_DATE
FROM BUDGORDER
ORDER BY 2 DESC NULLS LAST, 1 ASC NULLS FIRST;
OPTIMIZE FOR
Предложение OPTIMIZE FOR позволяет задать необходимую стратегию оптимизации запросов,
тем самым ускорить процесс выборки данных. Синтаксис предложения:
SELECT ...
[WITH LOCK [SKIP LOCKED]]
OPTIMIZE FOR {FIRST | ALL} ROWS
FIRST- для запросов выбирается такой план доступа, который позволяет максимально быстро получить первые записи в выборке;ALL- для запросов выбирается такой план доступа, который позволяет максимально быстро получить все записи в выборке.
В отсутсвии предложения OPTIMIZE FOR при выполнении оператора SELECT будет использоваться стратегия
оптимизации запросов, указанная в параметре конфигурационного файла OptimizeForFirstRows.
Значением по умолчанию является default. Если при умолчательном значении параметра OptimizeForFirstRows
в запросе присутствуют ключевые слова FIRST и/или SKIP или же предложение ROWS, то будет использована
стратегия оптимизации FIRST ROWS, несмотря на настройки файла конфигурации. Если же в конфигурационном файле
указана стратегия ALL ROWS, то данные предложения не будут влиять на оптимизацию запросов. При стратегии
ALL ROWS всегда применяется явный выбор данных и их сортировка.
Для примера рассмотрим запрос:
SELECT * FROM COUNTRY ORDER BY COUNTRY;
При стратегии FIRST ROWS для запроса, содержащего упорядочение по столбцу, будет построен следующий план:
PLAN (COUNTRY ORDER RDB$PRIMARY1)
Так выглядит план в расширенном варианте:
Select Expression
-> Table "COUNTRY" Access By ID
-> Index "RDB$PRIMARY1" Full Scan
А при стратегии ALL ROWS для этого же запроса оптимизатор построит другой план, в котором
применяется упорядочивание данных полученного набора:
PLAN (COUNTRY ORDER RDB$PRIMARY1)
В расширенном варианте план выглядит так:
Select Expression
-> Table "COUNTRY" Access By ID
-> Index "RDB$PRIMARY1" Full Scan
ROWS
Предложение ROWS задает диапазон строк, которые попадут в результирующий набор данных из полученного
в результате выполнения запроса набора данных. Синтаксис предложения:
Листинг 12.19 Синтаксис предложения ROWS
SELECT ...
FROM ...
ORDER BY ...
ROWS <m> [TO <n>]
Нумерация записей в наборе данных начинается с 1.
Предложение ROWS имеет смысл использовать, только если задано и предложение ORDER BY.
ROWS может быть использовано без выражения ORDER BY, хотя это редко имеет смысл, за
исключением случая, когда необходимо быстро взглянуть на данные таблицы – получаемые строки
при этом будут чаще всего в случайном порядке.
В отличие от FIRST и SKIP, выражение ROWS принимает все типы целочисленных выражений
в качестве аргумента – без скобок! Конечно, скобки могут требоваться для правильных вычислений
внутри выражения, и вложенный запрос также должен быть обернут в скобки.
Вызов
ROWS <m>приведёт к возвращению первых<m>записей из набора данных.Если
<m>больше общего числа записей в возвращаемом наборе данных, то будет возвращён весь набор данных.Если
<m> = 0, то будет возвращён пустой набор данных.Если
<m> < 0, выдаётся ошибка
Следующий запрос вернёт первые 10 имён из таблицы.
SELECT id, name FROM People
ORDER BY name ASC
ROWS 10;
Вызов
ROWS <m> TO <n>приведёт к возвращению записей с<m>по<n>из набора данных.Если
<m>больше общего количества строк в наборе данных и<n> >= <m>, то будет возвращён пустой набор данных.Если число
<m>не превышает общего количества строк в наборе данных, а<n>превышает, то выборка ограничивается строками, начиная с<m>до конца набора данных.Если
<m> < 1и<n> < 1, то операторSELECTвыдаст ошибку.Если
<n> = <m> -1, то будет возвращён пустой набор данных.Если
<n> < <m> -1, то операторSELECTвыдаст ошибку.
Этот запрос вернёт последние 10 записей.
SELECT id, name FROM People
ORDER BY name ASC
ROWS (SELECT COUNT(*) - 9 FROM People)
TO (SELECT COUNT(*) FROM People);
Соотношения между ключевыми словами FIRST и SKIP и предложением ROWS
Нельзя использовать ROWS вместе с FIRST/SKIP в одном и том же операторе SELECT,
но можно использовать разный синтаксис в разных подзапросах. В сущности, ROWS заменяет собой
нестандартные выражения FIRST и SKIP. Но не существует предложения ROWS, соответствующего
тому случаю, когда заданы и ключевое слово FIRST, и ключевое слово SKIP.
Использование ROWS в UNION
При использовании ROWS с выражением UNION, он будет применяться к объединённому набору
данных, и должен быть помещён после последнего SELECT.
При необходимости ограничить возвращаемые наборы данных одного или нескольких операторов
SELECT внутри UNION, можно воспользоваться следующими вариантами:
Использовать
FIRST/SKIPв этих операторахSELECT. Необходимо помнить, что нельзя локально использовать выражениеORDER BYвSELECTвнутриUNION– только глобально, ко всему суммарному набору данных.Преобразовать
SELECTв производные таблицы с выражениямиROWS.
FETCH, OFFSET
Предложения FETCH и OFFSET являются SQL:2008 совместимым эквивалентом предложениям FIRST/SKIP
и альтернативой предложению ROWS. Предложение OFFSET указывает, какое количество строк необходимо
пропустить. Предложение FETCH указывает, какое количество строк необходимо получить. Синтаксис предложения:
Листинг 12.20 Синтаксис предложения OFFSET/FETCH
SELECT ...
FROM ...
[ORDER BY ...]
[OFFSET <значение1> {ROW | ROWS}]
[FETCH {FIRST | NEXT} [<значение2>] {ROW | ROWS} ONLY]
Здесь аргументами могут быть числовые литералы, SQL параметры (?) или PSQL параметры (:param).
Предложения OFFSET и FETCH могут применяться независимо уровня вложенности выражений запросов.
Предложения FETCH, OFFSET имеет смысл использовать, только если задано предложение ORDER BY.
Предложения OFFSET и/или FETCH не могут быть объединены с предложениями ROWS или FIRST/SKIP в одном выражении запроса.
В отличие от предложения ROWS, предложения OFFSET и FETCH допустимы только в операторе SELECT.
Следующий запрос возвращает все строки кроме первых 10, упорядоченных по столбцу COL1:
SELECT * FROM T1
ORDER BY COL1
OFFSET 10 ROWS;
В этом примере возвращается первые 10 строк, упорядоченных по столбцу COL1:
SELECT * FROM T1
ORDER BY COL1
FETCH FIRST 10 ROWS ONLY;
WITH LOCK
Опция WITH LOCK, обеспечивает возможность ограниченной явной пессимистической блокировки для
осторожного использования в затронутых наборах строк крайне малой выборки (в идеале из одной строки)
и при контроле из приложения.
Предупреждение
Требуется хорошее знание различных уровней изоляции и других параметров транзакций прежде чем использовать явную блокировку в вашем приложении.
Листинг 12.21 Синтаксис предложения WITH LOCK
SELECT ...
FROM ...
WITH LOCK [SKIP LOCKED]
При успешном выполнении предложения WITH LOCK будут заблокированы выбранные строки данных
и таким образом запрещён доступ на их изменение в рамках других транзакций до момента
завершения вашей транзакции.
Предложение WITH LOCK доступно только для выборки данных (SELECT) из одной таблицы.
Предложение WITH LOCK нельзя использовать:
в подзапросах;
в запросах с объединением нескольких таблиц (
JOIN);с оператором
DISTINCT, предложениемGROUP BYи при использовании любых агрегатных функций;при работе с представлениями;
при выборке данных из селективных хранимых процедур;
при работе с внешними таблицами.
Если предложение FOR UPDATE предшествует предложению WITH LOCK, то буферизация выборки
не используется. Таким образом, блокировка применяется к каждой строке, одна за другой, по
мере извлечения записей. Это делает возможным ситуацию, в которой успешная блокировка данных
перестаёт работать при достижении в выборке строки, заблокированной другой транзакцией.
Сервер, в свою очередь, для каждой записи, подпадающей под явную блокировку, возвращает версию записи, которая является в настоящее время подтверждённой (актуальной), независимо от состояния базы данных, когда был выполнен оператор выборки данных, или исключение при попытке обновления заблокированной записи.
Ожидаемое поведение и сообщения о конфликте зависят от параметров транзакции, определённых в TPB (Transaction Parameters Block):
Режим TPB |
Поведение |
|---|---|
isc_tpb_consistency |
Явные блокировки переопределяются неявными или явными блокировками табличного уровня и игнорируются. |
isc_tpb_concurrency + isc_tpb_nowait |
При подтверждении изменения записи в транзакции, стартовавшей после транзакции, запустившей явную блокировку, немедленно возникает исключение конфликта обновления. |
isc_tpb_concurrency + isc_tpb_wait |
При подтверждении изменения записи в транзакции, стартовавшей после транзакции, запустившей явную блокировку, немедленно возникает исключение конфликта обновления. Если в активной транзакции идёт редактирование записи (с использованием явной блокировки или нормальной оптимистической блокировкой записи), то транзакция, делающая попытку явной блокировки, ожидает окончания транзакции блокирования и, после её завершения, снова пытается получить блокировку записи. Это означает, что при изменении версии записи и подтверждении транзакции с блокировкой возникает исключение конфликта обновления. |
isc_tpb_read_committed + isc_tpb_nowait |
Если есть активная транзакция, редактирующая запись (с использованием явной блокировки или нормальной оптимистической блокировкой записи), то сразу же возникает исключение конфликта обновления. |
isc_tpb_read_committed +isc_tpb_wait |
Если в активной транзакции идёт редактирование записи (с использованием явной блокировки или нормальной оптимистической блокировкой записи), то транзакция, делающая попытку явной блокировки, ожидает окончания транзакции блокирования и, после её завершения, снова пытается получить блокировку записи. Для этого режима TPB никогда не возникает конфликта обновления |
Как сервер работает с WITH LOCK
Попытка редактирования записи с помощью оператора UPDATE, заблокированной другой транзакцией,
приводит к вызову исключения конфликта обновления или ожиданию завершения блокирующей транзакции –
в зависимости от режима TPB. Поведение сервера здесь такое же, как если бы эта запись уже была
изменена блокирующей транзакцией.
Нет никаких специальных кодов gdscode, возвращаемых для конфликтов обновления, связанных с
пессимистической блокировкой.
Сервер гарантирует, что все записи, возвращённые явным оператором блокировки, фактически
заблокированы и действительно соответствуют условиям поиска, заданным в операторе WHERE,
если эти условия не зависят ни от каких других таблиц, не имеется операторов соединения,
подзапросов и т.п. Это также гарантирует то, что строки, не попадающие под условия поиска,
не будут заблокированы. Это не даёт гарантии, что нет строк, которые попадают под условия
поиска, и не заблокированы.
Сервер блокирует строки по мере их выборки. Это имеет важные последствия, если блокируются сразу несколько строк. Многие методы доступа к базам данных по умолчанию используют для выборки данных пакеты из нескольких сотен строк (так называемый "буфер выборки"). Большинство компонентов доступа к данным не выделяют строки, содержащиеся в последнем принятом пакете, и для которых произошёл конфликт обновления.
Предостережения при использовании WITH LOCK
Откат неявной или явной точки сохранения отменяет блокировку записей, которые изменялись в рамках её действий, но ожидающие окончания блокировки транзакции при этом не уведомляются. Приложения не должны зависеть от такого поведения, поскольку в будущем оно может быть изменено.
Хотя явные блокировки могут использоваться для предотвращения и/или обработки необычных ошибок конфликтов обновления, объем ошибок обновления (
deadlock) вырастет, если вы тщательно не разработаете свою стратегию блокировки и не будете ей строго управлять.Большинство приложений не требуют явной блокировки записей. Основными целями явной блокировки являются:
предотвращение дорогостоящей обработки ошибок конфликта обновления в сильно загруженных приложениях
для поддержания целостности объектов, отображаемых из реляционной базы данных в кластеризируемой среде.
Если использование вами явной блокировки не подпадает под одну из этих двух категорий, то это является неправильным способом решения задач.
Явная блокировка — это расширенная функция; не злоупотребляйте её использованием! В то время как явная блокировка может быть очень важной для веб-сайтов, обрабатывающих тысячи параллельных пишущих транзакций или для систем типа
ERP/CRM, работающих в крупных корпорациях, большинство прикладных программ не требуют её использования.
INTO
В PSQL (хранимых процедурах, триггерах и др.) результаты выборки команды SELECT могут быть
построчно загружены в локальные переменные (число, порядок и типы локальных переменных должны
соответствовать полям SELECT). Часто такая загрузка – единственный способ что-то сделать
с возвращаемыми значениями.
Листинг 12.22 Синтаксис предложения INTO
SELECT ...
FROM ...
INTO [:] <psql переменная> [, [:] <psql переменная> ...]
В PSQL двоеточие перед именами переменных является опциональным.
Простой оператор SELECT может быть использован в PSQL, только если он возвращает единственную строку.
Для запросов, возвращающих несколько строк, PSQL предлагает использовать оператор FOR SELECT.
Также, PSQL поддерживает оператор DECLARE CURSOR, который связывает именованный курсор с
определенной командой SELECT — и этот курсор впоследствии может быть использован для
навигации по возвращаемому набору данных.
В PSQL выражение INTO должно появляться в самом конце команды SELECT.
В PSQL, можно присвоить значения min_amt, avg_amt и max_amt заранее объявленным
переменным или выходным параметрам:
CREATE PROCEDURE GetOrderStats
RETURNS (min_amt DECIMAL(10, 2), avg_amt FLOAT, max_amt DECIMAL(10, 2))
AS
BEGIN
SELECT
MIN(amount),
AVG( CAST(amount AS float) ),
MAX(amount)
FROM orders
WHERE artno = 372218
INTO min_amt, avg_amt, max_amt;
END!
Полный синтаксис SELECT
В предыдущих разделах использовались неполные или упрощенные фрагменты синтаксиса SELECT.
Ниже приводится полный синтаксис.
Приведенный ниже синтаксис не включает синтаксис PSQL SELECT ... INTO,
который по сути является: <спецификацией курсора> INTO <список переменных>.
Листинг 12.23 Полный синтаксис оператора SELECT
<спецификация курсора> ::=
<запрос> [<предложение update>] [<предложение lock>]
<запрос> ::=
[<предложение with>] <тело запроса> [<предложение order by>]
[{ <предложение rows>
| [<предложение offset>] [<предложение fetch>] }]
<предложение with> ::=
WITH [RECURSIVE] <CTE> [, <CTE> ...]
<CTE> ::= <псевдоним CTE> [(<список столбцов CTE>)] AS (<оператор SELECT или UNION>)
<список столбцов> ::= <столбец> [, <столбец> ...]
<тело запроса> ::=
<основа запроса>
| <тело запроса> UNION [{ DISTINCT | ALL }] <другой набор данных>
<основа запроса> ::=
<определение запроса>
| (<тело запроса> [<предложение order by>]
[<предложение offset>] [<предложение fetch>])
<определение запроса> ::=
SELECT <ограничение> [{ ALL | DISTINCT }] <список выбора>
FROM <источник> [, <источник> ...]
[WHERE <условие поиска>]
[GROUP BY <значение> [, <значение> ...]]
[HAVING <условие поиска>]
[WINDOW <определение окна> [, <определение окна> ...]]
[PLAN <выражение>]
<ограничение> ::= [FIRST <выражение ограничения>] [SKIP <выражение ограничения>]
<выражение ограничения> ::=
<целочисленный литерал>
| <параметр запроса>
| (<выражение>)
<список выбора> ::= * | <подсписок> [, <подсписок> ...]
<подсписок> ::=
<спецификатор>.*
| <выражение> [[AS] <столбец>]
<источник> ::= <табличный источник> | <соединение>
<табличный источник> ::=
<источник или CTE> [[AS] псевдоним]
| [LATERAL] <производная таблица> [ [AS] <псевдоним> [(<список столбцов>)]]
| <родительская соединённая таблица>
<источник или CTE> ::=
<таблица или представление>
| CTE
| [имя пакета.]<имя процедуры> [(<аргументы процедуры>)]
<аргументы процедуры> ::= <значение> [, <значение> ...]
<производная таблица> ::=
(<SELECT запрос>) [[AS] <псевдоним производной таблицы>]
[(<псевдоним столбца> [, <псевдоним столбца>])]
<список столбцов> ::= <столбец> [, <столбец> ...]
<столбец> ::=
<таблица или псевдоним>
| <представление>
| <CTE>
| <производная таблица>
<родительская соединённая таблица> ::=
(<родительская соединённая таблица>)
| (<соединение>)
<соединение> ::=
<перекрёстное соединение>
| <естественное соединение>
| <квалифицированное соединение>
<перекрёстное соединение> ::=
<источник> CROSS JOIN <табличный источник>
<естественное соединение> ::=
<источник> NATURAL [<тип соединения>] JOIN <табличный источник>
<тип соединения> ::= INNER | { LEFT | RIGHT | FULL } [OUTER]
<квалифицированное соединение> ::=
<источник> [<тип соединения>] JOIN <табличный источник>
{ ON <условие соединения>
| USING (<список столбцов>) }
<предложение order by> ::=
ORDER BY <определение сортировки> [, <определение сортировки> ...]
<определение сортировки> ::=
<выражение> [<определение порядка>] [<поведение для null>]
<определение порядка> ::=
ASC | ASCENDING
| DESC | DESCENDING
<поведение для null> ::=
NULLS FIRST
| NULLS LAST
<окно> ::=
<имя окна> AS (<определение окна>)
<определение окна> ::=
[имя существующего окна]
[<выражение секционирования>]
[<предложение order by>]
[<определение рамки>]
<выражение секционирования> ::=
PARTITION BY <выражение> [, <выражение> ...]
<определение рамки> ::= { RANGE | ROWS } <границы рамки>
<границы рамки> ::=
<начало рамки>
| <рамка между>
<начало рамки> ::=
UNBOUNDED PRECEDING
| <выражение> PRECEDING
| CURRENT ROW
<рамка между> ::=
BETWEEN { UNBOUNDED PRECEDING | <выражение> PRECEDING
| CURRENT ROW | <выражение> FOLLOWING }
AND { <выражение> PRECEDING | CURRENT ROW
| <выражение> FOLLOWING | UNBOUNDED FOLLOWING }
<предложение rows> ::= ROWS <значение от> [TO <значение до>]
<предложение offset> :: =
OFFSET <выражение выборки> { ROW | ROWS }
<выражение выборки> ::=
<целочисленный литерал>
| <параметр запроса>
<предложение fetch> ::=
[FETCH { FIRST | NEXT }
[<выражение выборки>] { ROW | ROWS } ONLY]
<предложение WITH LOCK> ::= WITH LOCK [SKIP LOCKED]
12.2. INSERT
Добавление новых строк в обычную таблицу или в таблицу, лежащую в основе представления, осуществляется
с помощью оператора INSERT. Его синтаксис представлен в листинге .
Листинг 12.24 Синтаксис оператора добавления данных INSERT
INSERT INTO {<имя таблицы> | <имя представления>}
[OVERRIDE {SYSTEM | USER} VALUE]
{ DEFAULT VALUES | [(<список столбцов>)] <источник значений>}
[RETURNING { <список возвращаемых значений> | *} [INTO <переменные>]]
<источник значений> ::= VALUES (<значение>| DEFAULT [,<значение>| DEFAULT...])
| <поиск многих>
<список столбцов> ::= <имя столбца> [, <имя столбца> ...]
<список возвращаемых значений> ::= <имя столбца> [[AS] <алиас>] [, <имя столбца>
[[AS] <алиас>]...]
<переменные> ::= [:]<имя переменной> [, [:]<имя переменной> ...]
<значение> ::= <литерал>
| <выражение>
| <встроенная функция>
| <UDF> [(<параметр> [, <параметр> ...])]
| <обращение к хранимой процедуре> [(<параметр> [, <параметр>]...)]
| NEXT VALUE FOR <имя генератора>
| (<выбор одного>) }
Примечание
Поиск многих — оператор SELECT, возвращающий ноль или произвольное количество значений нескольких столбцов.
Добавлять данные в таблицу может ее владелец, пользователь SYSDBA,
пользователь операционной системы root (Linux), trusted user (Windows),
а также пользователь, которому предоставлено право добавлять данные в
таблицу (в таблицы, базовые для представления) оператором GRANT INSERT
— см. документ "Руководство администратора".
Можно добавить строку, для которой указаны конкретные значения столбцов; также строку,
которая будет во всех столбцах содержать значения по умолчанию (предложение DEFAULT VALUES);
или значения столбцов также могут быть получены из оператора SELECT, в этом случае может
быть вставлено от нуля и более строк.
После имени таблицы или имени представления в скобках могут быть указаны имена столбцов, в которые будут помещаться значения. Если список столбцов не указан, то данные будут помещаться в том порядке, в котором столбцы присутствуют в таблице или в порядке описания столбцов базовой таблицы в представлении. Рекомендуется всегда указывать список столбцов. Во-первых, это избавит от ошибок в случае изменения порядка столбцов в таблице или в представлении. Во-вторых, это некоторый элемент документирования. Это особенно важно, если таблица или представление содержит достаточно большое количество столбцов.
INSERT INTO cars (make, model, byyear)
VALUES ('Ford', 'T', 1908);
INSERT INTO cars
VALUES ('Ford', 'T', 1908, 'USA', 850);
Если в списке столбцов оператора INSERT не указан какой-либо столбец, то
ему будет присвоено значение по умолчанию (предложение DEFAULT в
определении столбца). Если для такого столбца не задано значение по
умолчанию, то ему будет присвоено пустое значение NULL. Для столбца,
входящего в состав первичного ключа, пустые значения недопустимы.
Столбцы, для которых запрещено пустое значение, и которые не имеют
значения по умолчанию, отличного от NULL, обязательно должны быть
указаны в операторе INSERT с конкретным значением.
VALUES
В случае использования ключевого слова VALUES в списке, следующем за этим ключевым словом,
должны быть представлены значения столбцов, помещаемых в строку таблицы. Весь список заключается
в скобки, значения в списке отделяются друг от друга запятыми. Количество значений должно в
точности совпадать с количеством имен столбцов, перечисленных в списке столбцов оператора, или
всех существующих в таблице столбцов при отсутствии такого списка.
В списке VALUES вместо значения столбца можно использовать ключевое слово DEFAULT.
В этом случае столбец получит значение по умолчанию, указанное при определении целевой таблицы.
Если значение по умолчанию для столбца отсутствует, то столбец получит значение NULL.
CREATE TABLE cars (
ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
BYYEAR SMALLINT DEFAULT 1990 NOT NULL,
NAME VARCHAR(45),
CONSTRAINT pk_cars PRIMARY KEY (ID) );
INSERT INTO cars (byyear, name)
VALUES (DEFAULT, 'Ford Focus' );
-- в столбец BYYEAR попадёт значение 1990
INSERT INTO cars (id, byyear, name)
VALUES (DEFAULT, 1996, 'Ford Mondeo');
-- в столбец id попадёт значение 2, как будто мы не указывали значение для id
Если ключевое слово DEFAULT указано для столбца, определенного как GENERATED BY DEFAULT AS IDENTITY,
то столбец получит следующее значение идентификации, так как будто этот столбец не был указан в запросе вовсе.
Поиск многих
Поиск многих — оператор SELECT, возвращающий ноль или произвольное количество значений нескольких столбцов.
Данные для вставки в таблицу или представление можно получить из сколь угодно сложного оператора SELECT
из какой-либо таблицы или группы таблиц, представления или хранимой процедуры выбора. Поиск многих позволяет
выполнить именно "пакетное" добавление данных. В этом варианте в таблицу помещается столько строк, сколько
вернет внутренний оператор SELECT. Только в этом случае оператор SELECT можно в операторе добавления
данных не заключать в круглые скобки.
Для использования оператора SELECT в операторе добавления данных пользователь должен иметь
соответствующие полномочия по выборке этих данных — быть владельцем всех таблиц, из которых
осуществляется выборка данных, пользователем SYSDBA, пользователем root операционной
системы (Linux), trusted user (Windows) или быть пользователем, которому предоставлено
право выборки данных из всех этих таблиц оператором GRANT SELECT — см. документ "Руководство администратора".
Количество столбцов, возвращаемых оператором SELECT, должно в точности соответствовать количеству
столбцов добавляемой строки. По этой причине имеет смысл в операторе SELECT явно задавать список
выбора в виде списка имен столбцов, а не использовать символ "*", который определяет выбор всех столбцов
таблицы. Список выбора может содержать не только имена столбцов, но и литералы.
INSERT INTO cars (make, model, byyear)
SELECT make, model, byyear
FROM new_cars;
Следует быть особенно осторожными при добавлении в таблицу строк, получаемых из той же самой таблицы,
что присутствует в операторе SELECT, так как в подобных случаях можно получить бесконечно
выполняемый оператор — когда вновь добавленные строки опять возвращаются в оператор добавления
в результате выполнения оператора SELECT.
Этот пример не является совсем правильным, потому что есть более простые
способы выполнить соответствующие действия проще и за меньшее количество серверного
времени и используемых ресурсов. Пусть, например, нужно переписать одни ошибочно записанные
регионы страны Россия в другую страну, например, США. Можно выполнить следующий оператор
добавления в таблицу регионов REGION:
INSERT INTO REGION (CODCOUNTRY, CODREGION, ...)
SELECT 'USA', CODREGION, ...
FROM REGION
WHERE (CODCOUNTRY = 'RUS' AND CODREGION = 'TX');
Здесь в списке выбора оператора SELECT первым указан литерал 'USA'.
Значение этого литерала будет присвоено столбцу CODCOUNTRY для всех добавляемых строк.
После выполнения этого оператора следует удалить все переписанные строки из страны Россия, выполнив следующий оператор:
DELETE FROM REGION
WHERE (CODCOUNTRY = 'RUS' AND CODREGION = 'TX');
Подробнее об операторе удаления строк таблицы см. в конце этой главы. Более эффективный вариант этого действия см. в следующем разделе.
Примечание
Подобное решение следует рассматривать только как иллюстрацию конкретного оператора.
Если таблица регионов содержит подчиненные таблицы (таблицы, чьи внешние ключи ссылаются
на данную таблицу, а их может быть более одной), то такая форма перемещения данных в
некоторых случаях может привести к нарушениям целостности данных базы данных или
к невыполнению соответствующих перемещений для подчиненных данных. Лучшим вариантом
будет использование оператора UPDATE, правда только в том случае, если в описании
внешних ключей всех дочерних таблиц использовались варианты ON DELETE CASCADE и
ON UPDATE CASCADE или в базе данных существуют триггеры, которые выполняют соответствующие
действия. См. главу 10, главу 21.
DEFAULT VALUES
Предложение DEFAULT VALUES позволяет вставлять записи без указания значений вообще.
Это возможно, только если для каждого NOT NULL поля и полей, на которые наложены другие
ограничения, или имеются допустимые объявленные значения по умолчанию, или эти значения
устанавливаются в BEFORE INSERT триггере.
INSERT INTO journal
DEFAULT VALUES;
OVERRIDING
Значения столбцов идентификации (GENERATED BY DEFAULT AS IDENTITY) могут быть переопределены
в операторах INSERT, UPDATE OR INSERT, MERGE. Для этого просто достаточно указать значение
столбца в списке значений. Однако для столбцов определённых как GENERATED ALWAYS это недопустимо.
Директива OVERRIDING SYSTEM VALUE позволяет заменить сгенерированное системой значение на значение
указанное пользователем. Директива OVERRIDING SYSTEM VALUE вызовет ошибку, если в таблице нет
столбцов идентификации или если они определены как GENERATED BY DEFAULT AS IDENTITY.
CREATE TABLE objects (
id INT GENERATED ALWAYS AS IDENTITY,
name CHAR(50));
INSERT INTO objects (id, name)
OVERRIDING SYSTEM VALUE
VALUES (11, 'Laptop' ); -- будет вставлено значение с кодом 11
Директива OVERRIDE USER VALUE выполняет обратную задачу, т.е. заменяет значение указанное
пользователем на значение сгенерированное системой, если столбец идентификации определён как
GENERATED BY DEFAULT AS IDENTITY. Директива OVERRIDING USER VALUE вызовет ошибку, если
в таблице нет столбцов идентификации или если они определены как GENERATED ALWAYS AS IDENTITY.
CREATE TABLE objects (
id INT GENERATED BY DEFAULT AS IDENTITY,
name CHAR(50));
INSERT INTO objects (id, name)
OVERRIDING USER VALUE
VALUES (12, 'Laptop' ); -- значение 12 будет проигнорировано
RETURNING
Необязательное предложение RETURNING указывает, что оператор возвращает значения заданных
столбцов одной добавляемой строки. Список столбцов возвращаемых значений не заключается в
скобки. Если оператор помещает более одной строки в таблицу, то в этом случае возникнет ошибка
базы данных. Ключевое слово INTO позволяет сохранить возвращенные значения во внутренних
переменных триггера или хранимой процедуры. Здесь список также не заключается в скобки.
Вариант INTO может использоваться только в PSQL.
В DSQL, оператор с RETURNING всегда возвращает только одну строку. Если указано предложение
RETURNING, и обнаружено более одной совпадающей строки, выдаётся сообщение об ошибке.
Вместо списка столбцов вы можете указать звёздочку (*) для возврата всех значений столбцов
таблицы. Возвращаемые значения содержат все изменения, произведённые в триггерах BEFORE.
Замечания:
Предложение
RETURNINGподдерживается только дляINSERT .. VALUESи одиночныхINSERT .. SELECT.В DSQL оператор с использованием
RETURNINGвсегда возвращает ровно одну строку. Если запись не была вставлена на самом деле, то все поля в возвращаемой строке будут меть значенияNULL.В PSQL, если ни одна строка не вставлена, то ничего не возвращается и все целевые переменные сохраняют свои прежние значения.
12.3. UPDATE
Для изменения данных в столбцах существующих строк в обычной таблице или в таблице, являющейся
базовой для изменяемого представления, используется оператор UPDATE. Оператор за одно
обращение позволяет изменить данные во всех строках или только в части строк в таблице или
в представлении. Его синтаксис представлен в листинге .
Листинг 12.25 Синтаксис оператора изменения данных UPDATE
UPDATE {<имя таблицы> | <имя представления>} [[AS] <псевдоним>]
SET <имя столбца> = <значение>|DEFAULT [,<имя столбца> = <значение>|DEFAULT...]
[WHERE { <условие поиска> | CURRENT OF <имя курсора>}]
[PLAN <план>]
[ORDER BY <упорядочиваемый элемент> [, <упорядочиваемый элемент> ... ]]
[ROWS <m> [TO <n>]]
[SKIP LOCKED]
[RETURNING { <возвращаемые значения> | [{OLD. | NEW.}]* }
[INTO [:]<имя переменной> [, [:]<имя переменной> ...]] ];
<значение> ::= { <литерал>
| <выражение>
| <встроенная функция>
| <UDF> [(<параметр> [, <параметр> ...])]
| NEXT VALUE FOR <имя генератора>
| (<выбор одного>) }
<упорядочиваемый элемент> ::=
{<имя столбца>|<псевдоним столбца>|<номер столбца>|<произвольное выражение>}
[COLLATE <порядок сортировки>]
[ASC[ENDING] | DESC[ENDING]]
[NULLS {FIRST | LAST}]
<возвращаемые значения> ::= <имя столбца> [[AS] <алиас>] [,<имя столбца> [[AS] <алиас>]]...
Изменять данные в таблице может ее владелец, пользователь SYSDBA, пользователь операционной
системы root (Linux), trusted user (Windows), а также пользователь, которому предоставлено
право изменять отдельные (указанные в операторе) столбцы таблицы оператором GRANT UPDATE —
см. документ "Руководство администратора". Если в данном операторе
изменение ключевых столбцов (столбцов, входящих в состав первичного или
уникального ключа) таблицы влечет автоматическое внесение изменений в
строки дочерних таблиц, то и к этим таблицам пользователь должен иметь
полномочия UPDATE.
Примечание
Если вы назначаете псевдоним таблице или представлению, вы обязаны использовать псевдоним для уточнения столбцов таблицы.
SET
Предложение SET задает список выполняемых изменений: указывается имя
изменяемого столбца и после знака равенства новое значение столбца.
Разрешено использовать имена столбцов в качестве значения. При этом использоваться будет всегда старое
значение столбца, даже если присваивание этому столбцу уже произошло ранее в перечислении SET.
Один столбец может быть использован только один раз в конструкции SET.
Пусть имеются следующие данные в таблице SETTBL:
A B
============ ============
1 0
2 0
После выполнения оператора:
UPDATE SETTBL
SET A = 5, B = A;
получим следующую таблицу:
A B
============ ============
5 1
5 2
Обратите внимание, что старые значения (1 и 2) используются для обновления столбца B,
даже после того как столбцу A были назначено новое значение (5).
В предложении SET вместо значения столбца можно использовать ключевое слово DEFAULT.
В этом случае столбец получит значение по умолчанию, указанное при определении целевой таблицы.
Если значение по умолчанию для столбца отсутствует, то столбец получит значение NULL.
UPDATE cars
SET BYYEAR = DEFAULT
WHERE ID = 1;
WHERE
Предложение WHERE ограничивает набор обновляемых записей заданным условием или текущей строкой
именованного курсора, если указано предложение WHERE CURRENT OF (только в PSQL). Если это предложение
не указано, то будут изменены все строки таблицы в том случае, если не было указано также предложение
ORDER BY вместе с предложением ROWS. Для того чтобы иметь возможность использовать предложение
WHERE в операторе UPDATE, пользователь должен также иметь полномочия выборки данных из этой же
таблицы — GRANT SELECT.
UPDATE employees
SET salary = 2.5 * salary
WHERE title = 'CEO';
Подробнее об условиях поиска в предложении WHERE см. в разделе 12.1.
PLAN
В операторе изменения данных может быть использовано ключевое слово PLAN, задающее план выборки данных.
Подробнее о планах выборки см. в разделе . Основным назначением этого
предложения является определение порядка выборки строк из таблицы, для которых будут выполняться
изменения. Такое предложение может ускорить (или замедлить при неправильном его применении) выборку
подлежащих для корректировки записей. Как правило, сервер базы данных выбирает наиболее подходящий
план для поиска записей. При достаточном опыте работы с базой данных вы можете задавать свой собственный
план, позволяющий ускорить работу системы.
ORDER BY и ROWS
Предложение ORDER BY позволяет задать порядок обновления записей. Это может иметь значение в некоторых случаях.
Листинг 12.26 Синтаксис предложения упорядочения данных ORDER BY
UPDATE ...
SET ...
ORDER BY <упорядочиваемый элемент> [, <упорядочиваемый элемент> ... ]
[ROWS <m> [TO <n>]]
<упорядочиваемый элемент> ::=
{<имя столбца>|<псевдоним столбца>|<номер столбца>|<произвольное выражение>}
[COLLATE <порядок сортировки>]
[ASC[ENDING] | DESC[ENDING]]
[NULLS {FIRST | LAST}]
В этом предложении перечисляются столбцы, по которым нужно упорядочить строки набора данных перед выполнением изменений. Здесь можно задавать только имена столбцов, а не их номера.
Ключевое слово ASCENDING задает упорядочение по возрастанию значений.
Допустимо сокращение ASC. Применяется по умолчанию.
Ключевое слово DESCENDING задает упорядочение по убыванию значений.
Допустимо сокращение DESC. В одном предложении упорядочение для одного
столбца может идти по возрастанию значений, а для другого — по убыванию.
Ключевое слово COLLATE позволяет задать порядок сортировки строкового
столбца, если нужен порядок, отличный от того, который был установлен
для этого столбца (явно или по умолчанию). Допустимые порядки
сортировки для различных наборов символов см. в 33.
Ключевое слово NULLS определяет, где в сортированном списке будут
находиться пустые значения соответствующего столбца — в начале всего
списка (FIRST) или в конце (LAST). По умолчанию принимается NULLS FIRST.
Для возможности использования предложения ORDER BY в операторе UPDATE пользователь
должен также иметь полномочия выборки данных из соответствующей таблицы — GRANT SELECT.
Предложение ROWS имеет смысл только вместе с предложением ORDER BY. Однако его можно использовать отдельно.
При одном аргументе m, ROWS ограничивает обновление первыми m строками. Особенности:
Если
m > количества обрабатываемых строк, то обновляется весь набор строк;Если
m = 0, ни одна строка не обновляется;Если
m < 0, выдаётся ошибка.
При двух аргументах m и n, ROWS ограничивает обновление до строк от m до n
включительно. Оба аргумента – целочисленные, и начинаются с 1. Особенности:
Если
m > количества обрабатываемых строк, ни одна строка не обновляется;Если
nбольше количества строк, то обновляются строки отmдо конца набора;Если
m < 1илиn < 1, выдаётся ошибка;Если
n = m - 1, ни одна строка не обновляется;Если
n < m - 1, выдаётся ошибка.
Надбавка 20-ти сотрудникам с наименьшей зарплатой.
UPDATE employees
SET salary = salary + 50
ORDER BY salary ASC
ROWS 20;
В одном операторе могут быть указаны и предложение WHERE, и предложение ROWS. В этом случае
сначала отбираются строки, соответствующие условию в предложении WHERE, затем они упорядочиваются
на основании предложения ORDER BY, и, наконец, выполняются заданные изменения для тех строк,
которые указаны в предложении ROWS.
RETURNING
Необязательное предложение RETURNING указывает, что оператор возвращает значения заданных столбцов
обновляемой строки. В RETURNING могут включаться любые строки, необязательно только те, которые
обновляются. Если оператор изменяет более одной строки таблицы, то в этом случае возникнет ошибка.
Возвращаемые значения содержат изменения, произведённые в триггерах BEFORE, но не в
триггерах AFTER. OLD.fieldname и NEW.fieldname могут быть использованы в качестве имён
столбцов. Если OLD. или NEW. не указано, возвращаются новые значения столбцов (NEW.).
Вместо списка столбцов вы можете указать звёздочку (*). В этом случае будут возвращены все значения
столбцов таблицы. Звёздочку можно применять со спецификаторами NEW или OLD.
UPDATE Scholars
SET first_name = 'Hugh' , last_name = 'Pickering'
WHERE first_name = 'Henry' AND last_name = 'Higgins'
RETURNING id, old.last_name, new.last_name;
Ключевое слово INTO позволяет сохранить эти возвращенные значения во внутренних переменных
триггера или хранимой процедуры (только в PSQL). Если записи не было обновлены, ничего не возвращается,
и переменные, указанные в RETURNING, сохранят свои прежние значения.
12.4. UPDATE OR INSERT
Оператор UPDATE OR INSERT позволяет изменить существующие данные или добавить новые, если
в таблице нет строк, соответствующих некоторому условию.
Синтаксис оператора представлен в листинге .
Листинг 12.27 Синтаксис оператора изменения или добавления данных UPDATE OR INSERT
UPDATE OR INSERT INTO
{<имя таблицы> | <имя представления>} [(<имя столбца> [, <имя столбца> ...] )]
VALUES (<значение> | DEFAULT [, <значение> | DEFAULT ...] )
[MATCHING (<имя столбца> [, <имя столбца>] ...)]
[RETURNING { <возвращаемые значения> | [{OLD. | NEW.}]* }
[INTO [:]<имя переменной> [, [:]<имя переменной> ...]] ];
<возвращаемые значения> ::= <имя столбца> [[AS] <алиас>] [, <имя столбца> [[AS] <алиас>]]...
Для выполнения оператора UPDATE OR INSERT пользователь должен иметь привилегии и UPDATE,
и INSERT к таблице (представлению).
Совпадением считается полное совпадение значений столбцов MATCHING или PK. Совпадение проверяется
с использованием IS NOT DISTINCT, поэтому NULL совпадает с NULL.
Синтаксис оператора немного похож на синтаксис оператора INSERT. После названия оператора и ключевого
слова INTO помещается имя таблицы или представления, к которому применяется оператор. Затем в скобках
следует необязательный список имен столбцов таблицы (представления). Ключевое слово VALUES является
обязательным. После него в скобках следует список значений, присваиваемых соответствующим столбцам.
В списке VALUES вместо значения столбца можно использовать ключевое слово DEFAULT. В этом
случае столбец получит значение по умолчанию, указанное при определении целевой таблицы. Если
значение по умолчанию для столбца отсутствует, то столбец получит значение NULL.
Примечание
Столбец для которого вместо значения использовано ключевое слово DEFAULT не может быть использован предложении MATCHING.
Оператор позволяет изменить значения отдельных столбцов в существующей строке или нескольких строках,
если найдено соответствие, или добавить одну новую строку, если соответствия не найдено. В случае
добавления новой строки в операторе должны быть заданы значения всех столбцов, входящих в состав
первичного ключа, а также столбцов, описанных с характеристикой NOT NULL.
Если не задано ключевое слово MATCHING, то в списке столбцов обязательно должны присутствовать
все столбцы, входящие в состав первичного ключа. Соответствующая строка будет найдена, если в таблице
существует запись с тем же значением первичного ключа, что задано в операторе. В этом случае в строке
произойдет изменение значений остальных указанных в операторе столбцов. Здесь изменяются только столбцы
одной строки. Если строки с указанным в операторе значением первичного ключа не найдено, то в таблицу
добавляется новая строка.
Если в операторе указано ключевое слово MATCHING и после него список имен столбцов, то поиск
соответствующих строк осуществляется по этим столбцам (значения всех этих столбцов должны присутствовать
в предложении VALUES). Если найдены соответствующие строки, то для них выполняется изменение значений
указанных столбцов. Таких строк может быть более одной. Если не найдено ни одной соответствующей строки,
то в таблицу добавляется одна новая строка. Если у таблицы нет первичного ключа, то указание MATCHING
считается обязательным.
Предложение RETURNING позволяет вернуть значения указанных столбцов измененной или добавленной
строки. Если изменяется или добавляется более одной строки, то наличие данного предложения вызовет
исключение базы данных.
Возвращаемые значения содержат все изменения, произведённые в триггерах BEFORE, но не в
триггерах AFTER. OLD.fieldname и NEW.fieldname могут быть использованы в качестве
возвращаемых значений. Для обычных имён столбцов возвращаются новые значения.
Вместо списка столбцов вы можете указать звёздочку (*). В этом случае будут возвращены все значения
столбцов таблицы. Звёздочку можно применять со спецификаторами NEW или OLD.
Ключевое слово INTO позволяет сохранить возвращенные значения во внутренних переменных триггера
или хранимой процедуры. Здесь список также не заключается в скобки. Вариант INTO может использоваться только в PSQL.
12.5. DELETE
Для удаления существующих строк из таблиц базы данных используется оператор DELETE.
Оператор позволяет удалить все или группу строк таблицы. Его синтаксис показан
в листинге .
Листинг 12.28 Синтаксис оператора удаления данных из таблицы или представления DELETE
DELETE FROM {<имя таблицы> | <имя представления>} [[AS] <псевдоним>]
[WHERE { <условие поиска> | CURRENT OF <имя курсора>}]
[PLAN <план>]
[ORDER BY <упорядочиваемый элемент> [, <упорядочиваемый элемент> ... ]]
[ROWS <m> [TO <n>]]
[SKIP LOCKED]
[RETURNING {<список возвращаемых значений> | *} [INTO <переменные>]]
<список возвращаемых значений> ::= <имя столбца> [[AS] <алиас>] [, <имя столбца> [[AS] <алиас>]...]
<переменные> ::= [:]<имя переменной> [, [:]<имя переменной> ...];
Удалять данные из таблицы (таблиц, лежащих в основе представления) может ее владелец, пользователь
SYSDBA, пользователь операционной системы root (Linux), trusted user (Windows), а также
пользователь, которому предоставлено право на удаление строк таблицы (таблиц) оператором
GRANT DELETE — см. документ "Руководство администратора". Если удаление строки таблицы влечет и
удаление подчиненных строк из дочерних таблиц, то и к этим таблицам пользователь также должен иметь
соответствующие полномочия.
Оператор удаляет из таблицы строки в соответствии с условием в предложении WHERE и заданными
значениями в предложениях ORDER BY и ROWS.
Предложение FROM задает имя таблицы или изменяемого представления, в которой удаляются строки.
WHERE
Предложение WHERE определяет множество строк, которые будут удалены. Удаляются только те строки,
которые удовлетворяют условию поиска, или только текущей строке именованного курсора (только в PSQL).
Если это предложение не указано, будут удалены все существующие строки таблицы в том случае, если не
указано также предложение ORDER BY и предложение ROWS.
Для возможности использования предложения WHERE в операторе DELETE пользователь должен также
иметь полномочия выборки данных из таблицы — GRANT SELECT.
Подробнее об условиях поиска в предложении WHERE см. в разделе 12.1.
DELETE FROM REGION
WHERE (CODCOUNTRY = 'RUS' AND CODREGION = 'TX');
PLAN
Может быть использовано ключевое слово PLAN, задающее план выборки данных для удаления.
Подробнее о плане выборки см. в разделе . Основным назначение этого
предложения является определение порядка (алгоритма) выборки строк из исходной таблицы для выполнения
удаления. Такое предложение может ускорить (или замедлить при неправильном применении) выборку
предназначенных для удаления записей.
ORDER BY и ROWS
Предложение ORDER BY используется для упорядочения результатов выборки перед его удалением.
В нем указывается список столбцов, по которым происходит упорядочение, направление сортировки
для каждого столбца (по возрастанию или по убыванию) и порядок сортировки (COLLATE) для строкового
столбца. Синтаксис предложения ORDER BY и его использование подробно описано в
разделе 12.3 этой главы.
MERGE INTO customers c
USING (select * from customers_delta where id > 10) cd
ON (c.id = cd.id)
WHEN MATCHED THEN
UPDATE SET name = cd.name
WHEN NOT MATCHED THEN
INSERT (id, name) VALUES (cd.id, cd.name);
Предложение ROWS позволяет ограничить количество удаляемых строк. Имеет смысл только в
комбинации с предложением ORDER BY, но допустимо и без него.
В качестве m и n могут выступать любые целочисленные выражения.
При одном аргументе m, удаляются первые m строк. Порядок строк без ORDER BY не определён (случаен).
Замечания:
Если
mбольше общего числа строк в наборе, то весь набор удаляется;Если
m = 0, то удаление не происходит;Если
m < 0, то выдаётся сообщение об ошибке.
Если указаны аргументы m и n, удаление ограничено количеством строк от m до n,
включительно. Нумерация строк начинается с 1.
Замечания по использованию двух аргументов:
Если
m > общего числа строк в наборе, ни одна строка не удаляется;Если
m > 0и<= числа строк в наборе, аnвне этих значений, то удаляются строки отmдо конца набора;Если
m < 1илиn < 1, выдаётся сообщение об ошибке;Если
n = m – 1, ни одна строка не удаляется;Если
n < m – 1, выдаётся сообщение об ошибке.
RETURNING
Предложение RETURNING позволяет вернуть вызвавшей программе значения указанных столбцов
удаленной строки. Если происходит удаление более чем одной строки, то выдается сообщение об ошибке.
В RETURNING могут быть указаны любые столбцы, не обязательно все, а также другие столбцы и
выражения. Вместо списка столбцов может быть указана звёздочка (*), в этом случае будут возвращены
все столбцы удалённой записи.
Ключевое слово INTO позволяет сохранить возвращенные значения во внутренних переменных
триггера или хранимой процедуры. Вариант INTO может использоваться только в PSQL — см. главу 18.
12.6. MERGE
Объединяет данные в таблицу или представление.
Листинг 12.29 Синтаксис оператора MERGE
MERGE INTO <имя таблицы | имя представления> [[AS] <алиас>]
USING <таблица|представление|хранимая процедура|производная таблица> [AS <алиас>]
ON <условие соединения>
<предложение WHEN> [<предложение WHEN> ...]
[PLAN <выражение для построения плана>]
[ORDER BY <выражение для упорядочивания выборки>]
[RETURNING <список возвращаемых выражений>|[{OLD. | NEW.}]*
[INTO <список переменных>]]
<предложение WHEN> ::= <предложение WHEN MATCHED>
| <предложение WHEN NOT MATCHED BY TARGET>
| <предложение WHEN NOT MATCHED BY SOURCE>
<предложение WHEN MATCHED> ::=
WHEN MATCHED [ AND <доп.условие> ]
THEN {UPDATE SET <список назначений> | DELETE}
<предложение WHEN NOT MATCHED BY TARGET> ::=
WHEN NOT MATCHED [ BY TARGET ] [ AND <доп.условие> ]
THEN INSERT [(<столбцы>)] VALUES (<значения>)
<предложение WHEN NOT MATCHED BY SOURCE> ::=
WHEN NOT MATCHED BY SOURCE [ AND <доп.условие> ]
THEN { UPDATE SET <список назначений> | DELETE }
<список назначений> ::= <имя столбца>={<значение>|DEFAULT} [, <имя столбца>= {<значение>|DEFAULT} ...]
<столбцы> ::= <имя столбца> [, <имя столбца> ...]
<значения> ::= { <значение> | DEFAULT} [, { <значение> | DEFAULT} ...]
<список возвращаемых выражений> ::= <выражение> [[AS] <псевдоним>] [, <выражение> [[AS] <псевдоним>] ...]
<список переменных> ::= [:] <имя переменной> [, [:] <имя переменной> ...]
Оператор MERGE производит слияние записей источника в целевую таблицу (или обновляемое представление).
Источником данных может быть таблица, представление, хранимая процедура или производная таблица, т.е.
заключенный в скобки оператор SELECT. Каждая запись источника используется для обновления
(предложение UPDATE) или удаления (предложение DELETE) одной или более записей цели,
или вставки (предложение INSERT) записи в целевую таблицу, или ни для того, ни для другого.
Условие обычно содержит сравнение столбцов в таблицах источника и цели.
В списке VALUES предложения INSERT и списке SET предложения UPDATE вместо значения
столбца можно использовать ключевое слово DEFAULT. В этом случае столбец получит значение
по умолчанию, указанное при определении целевой таблицы. Если значение по умолчанию для столбца
отсутствует, то столбец получит значение NULL.
Допускается указывать несколько предложений WHEN MATCHED и WHEN NOT MATCHED.
Предложение WHEN NOT MATCHED BY TARGET вызывается, когда исходная запись не совпадает с ни с
одной записью в целевой таблице. INSERT изменит целевую таблицу.
Предложение WHEN NOT MATCHED BY SOURCE вызывается, когда целевая запись не совпадает ни с
одной записью в источнике. UPDATE или DELETE изменяют целевую таблицу.
Предложения WHEN проверяются в указанном порядке. Если условие в предложении WHEN
не выполняется, то мы пропускаем его и переходим к следующему предложению. Так будет происходить
до тех пор, пока условие для одного из предложений WHEN не будет выполнено. В этом случае
выполняется действие, связанное с предложением WHEN, и осуществляется переход на следующую
строку источника. Для каждой строки источника выполняется только одно действие.
Примечание
Предложение WHEN NOT MATCHED берёт за основу записи из источника данных, указанного в
предложении USING. Это значит, что если у исходной записи нет соответствия в целевой
таблице, то выполняется оператор INSERT. С другой стороны записи в целевой таблице,
не имеющие соответствия в источнике данных, не вызывают никаких действий.
Примечание
Если условие WHEN MATCHED присутствует, и несколько записей совпадают с записями
в целевой таблице, UPDATE выполнится для всех совпадающих записей источника, и
каждое последующее обновление перезапишет предыдущее. Это нестандартное поведение:
стандарт SQL-2003 требует, чтобы в этой ситуации выдавалось исключение (ошибка).
Оператор MERGE, затрагивающий не более одной строки, может содержать конструкцию RETURNING
для возвращения значений добавленной, модифицируемой или удаляемой строки. В RETURNING
могут быть указаны любые столбцы из целевой таблицы (обновляемого представления), не обязательно
все, а также другие столбцы и выражения.
Возвращаемые значения содержат изменения, произведённые в триггерах BEFORE.
Имена столбцов могут быть уточнены с помощью префиксов NEW и OLD для определения,
какое именно значение вы хотите столбца вы хотите получить до модификации или после.
Для предложений WHEN MATCHED UPDATE и MERGE WHEN NOT MATCHED неуточненные имена
столбцов или уточнённые именами таблиц или их псевдонимами понимаются как столбцы с префиксом
NEW, для предложений MERGE WHEN MATCHED DELETE – с префиксом OLD.
MERGE INTO customers c
USING (select * from customers_delta where id > 10) cd
ON (c.id = cd.id)
WHEN MATCHED THEN
UPDATE SET name = cd.name
WHEN NOT MATCHED THEN
INSERT (id, name) VALUES (cd.id, cd.name);
Предупреждение
Если присутствует предложение WHEN MATCHED и нескольким записям из источника данных
соответствуют записи в целевой таблице, то операция обновления выполняется многократно;
при этом каждое обновление перезаписывает предыдущее. Это поведение не соответствует стандарту:
SQL-2003 определяет, что в таком случае должно быть вызвано исключение .
12.7. EXECUTE PROCEDURE
В DSQL, в языке хранимых процедур и триггеров и при использовании утилиты isql можно вызвать
выполняемую хранимую процедуру, используя оператор EXECUTE PROCEDURE. Его синтаксис:
Листинг 12.30 Синтаксис оператора EXECUTE PROCEDURE
EXECUTE PROCEDURE <имя процедуры> [(<параметр> [, <параметр>] ...)]
[RETURNING_VALUES [:]<параметр> [, [:]<параметр>] ...];
При вызове хранимой процедуры можно после имени процедуры указать список
входных параметров для процедуры. Если процедура получает параметры, то
список входных параметров в операторе EXECUTE PROCEDURE является
обязательным. При этом требуется полное соответствие количества
параметров и их типов данных.
Оператор EXECUTE PROCEDURE является наиболее часто используемым стилем вызова хранимой
процедуры, которая написана для модификации некоторых данных, код которой не содержит оператора
SUSPEND. Такие хранимые процедуры могут возвратить набор данных, состоящий не более чем из
одной строки. Этот набор может быть передан в переменные другой (вызывающей) процедуры с помощью
предложения RETURNING_VALUES. Клиентские интерфейсы, как правило, имеют обертку API, которые
могут извлекать выходные значения в однострочный буфер при вызове процедуры через EXECUTE PROCEDURE в DSQL.
При вызове с помощью EXECUTE PROCEDURE процедур другого типа (селективных процедур) будет
возвращена только первая запись из результирующего набора, несмотря на то, что эта процедура
скорее всего должна возвращать многострочный результат. "Селективные" хранимые процедуры должны
вызываться с помощью оператора SELECT, в этом случае они ведут себя как виртуальные таблицы.
Если этот оператор вызывается из isql, то нельзя использовать предложение RETURNING_VALUES.
EXECUTE PROCEDURE MakeFullName
('Mr./Mrs.' || First_Name, Middle_Name, upper(Last_Name))
RETURNING_VALUES FullName;
12.8. EXECUTE BLOCK
Для выполнения из декларативного SQL (DSQL) некоторых императивных действий используются анонимные (безымянные) PSQL блоки. Заголовок анонимного PSQL блока опциально может содержать входные и выходные параметры. Тело анонимного PSQL блока может содержать объявление локальных переменных, курсоров и блок PSQL операторов. Анонимный PSQL блок не определяется и сохраняется как объект метаданных, в отличии от хранимых процедур и триггеров. Он не может обращаться сам к себе.
Как и хранимые процедуры анонимные PSQL блоки могут использоваться для обработки данных или для осуществления выборки из базы данных.
Синтаксис оператора представлен в листинге .
Листинг 12.31 Синтаксис оператора EXECUTE BLOCK
EXECUTE BLOCK
[(<список входных параметров>)]
[RETURNS (<список выходных параметров>)]
AS
[<объявление> [<объявление> ...] ]
BEGIN
<блок операторов>
END;
<список входных параметров>::=<описание параметра>=? [,<описание параметра>=?...]
<список выходных параметров> ::= <описание параметра> [, <описание параметра>]
<описание параметра> ::= <имя параметра> <тип> [NOT NULL]
[COLLATE <порядок сортировки>]
<тип> ::= <тип данных SQL>
| [TYPE OF] <имя домена>
| TYPE OF COLUMN <имя таблицы/представления>.<имя столбца>
<объявление> ::= объявление локальной переменной
| объявление курсора
| <объявление подпрограммы (процедуры или функции)>
| <реализация подпрограммы (процедуры или функции)>
Оператор выполняет блок PSQL кода, так как будто это хранимая процедура, возможно с входными и выходными параметрами и локальными переменными. Это позволяет пользователю выполнять "на лету" PSQL в контексте DSQL.
Выполнение блока без входных параметров должно быть возможным с любым клиентом РЕД Базы Данных,
который позволяет пользователю вводить свои собственные DSQL операторы. Если есть входные параметры,
все становится сложнее: эти параметры должны получить свои значения после подготовки оператора,
но перед его выполнением. Это требует специальных возможностей, которыми располагает не каждое
клиентское приложение (isql такой возможности не предлагает).
Сервер принимает только вопросительные знаки ("?") в качестве заполнителей для входных значений,
а не ":xxx" или литеральные значения. Клиентское программное обеспечение может поддерживать форму
":ххх", в этом случае будет произведена предварительная обработка запроса перед отправкой его на сервер.
Вычисляет среднее геометрическое двух чисел и возвращает его пользователю:
EXECUTE BLOCK (
x DOUBLE PRECISION = ?,
y DOUBLE PRECISION = ?)
RETURNS (gmean DOUBLE PRECISION)
AS
BEGIN
gmean = sqrt(x*y);
SUSPEND;
END
Поскольку этот блок имеет входные параметры, он должен быть предварительно подготовлен. После чего можно установить параметры и выполнить блок.
Если блок имеет выходные параметры, нужно использовать SUSPEND, иначе ничего не будет возвращено.
Выходные данные всегда возвращаются в виде набора данных, так же как и в случае с оператором SELECT.
Не получится использовать RETURNING_VALUES или выполнить блок, вернув значения в некоторые переменные,
используя INTO, даже если возвращается всего одна строка.
Типом данных для внутренней переменной (входного, выходного параметра или локальной переменной) может быть любой тип данных, используемый в SQL.
Вместо типа данных можно указать имя домена. В этом случае внутренней переменной присваиваются
все характеристики домена — запрет на пустое значение (NOT NULL), значение по умолчанию
(DEFAULT) и условие (CHECK), которому должно удовлетворять значение, помещаемое в
переменную. В случае задания в операторе предложения TYPE OF для этой переменной создается
лишь тип данных, заданный в домене.
Внутренние можно объявлять, используя тип данных столбцов существующих таблиц и представлений.
Для этого используется предложение TYPE OF COLUMN, после которого указывается имя таблиц
или представления и через точку имя столбца. При использовании TYPE OF COLUMN наследуется
только тип данных, а в случае строковых типов ещё набор символов и порядок сортировки.
Ограничения и значения по умолчанию столбца никогда не используются.
Для внутренних переменных можно указать ограничение NOT NULL, тем самым запретив
передавать в него значение NULL.
Для строковых типов данных, заданных явно или при ссылке на домен, можно указывать
предложение COLLATE, определяющее порядок сортировки.
Локальной переменной можно устанавливать инициализирующее (начальное) значение.
Это значение устанавливается с помощью предложения DEFAULT или оператора "=".
В качестве значения по умолчанию может быть использовано значение NULL, литерал и любая
контекстная переменная совместимая по типу данных.
Некоторые клиенты, особенно те, что позволяет пользователю отослать несколько операторов
сразу, могут потребовать, окружить оператор EXECUTE BLOCK строками SET TERM.
В следующем примере выполняется вычисление факториала числа, заданного входным параметром.
EXECUTE BLOCK
(N BIGINT = ?)
RETURNS (RESULT BIGINT)
AS
DECLARE VARIABLE I BIGINT;
BEGIN
RESULT = 1;
I = 1;
WHILE (I <= N) DO
BEGIN
RESULT = RESULT * I;
I = I + 1;
END
SUSPEND;
END
После выполнения цикла по вычислению факториала заданного числа выдается оператор SUSPEND,
который позволяет отобразить полученный результат в программе графического интерфейса.
Подробное описание параметров, локальных переменных и операторов языка хранимых процедур и триггеров см. в главе 18. Там же приведен пример хранимой процедуры, выполняющей вычисление факториала заданного числа.
12.9. SET OPTIMIZE
Оператор определяет, что нужно оптимизировать: получение первых строк или всех.
Листинг 12.32 Синтаксис предложения SET OPTIMIZE
SET OPTIMIZE <режим оптимизации>
<режим оптимизации> ::= FOR {FIRST | ALL} ROWS
| TO DEFAULT
Оператор SET OPTIMIZE позволяет изменить стратегию оптимизатора на уровне текущей сессии.
Существует две стратегии оптимизации запросов:
FIRST ROWS— оптимизатор строит план запроса так, чтобы быстрее извлечь только первые строки запроса;ALL ROWS— оптимизатор строит план запроса так, чтобы быстрее извлечь все строки запроса
По умолчанию используется стратегия оптимизации указанная в параметре OptimizeForFirstRows
конфигурационного файла firebird.conf или database.conf.
Стратегия оптимизации может быть переопределена на уровне оператора с помощью предложения OPTIMIZE FOR.