Главная » SQL, Базы данных » ТАБЛИЧНЫЕ ВЫРАЖЕНИЯ SQL

0

Вначале рассмотрим приведенную ниже грамматику табличных выражений < table exp>

в форме Бэкуса-Наура.

<table exp>

::=  <with exp> \ <nonwith exp>

<with exp>

: : =  WITH [ RECURSIVE ]

<table name> [ ( <column name commalist> ) ] ] AS (

<table exp> ) <nonwith exp>

<nonwith ехр>

::= <join table exp> | <nonjoin table exp>

<join table exp>

::=  <table ref> I NATURAL ] JOIN <table ref>                                                                                 [ ON <bool exp>

| USING ( <column name conmalist> ) ] | <table ref> CROSS JOIN <table ref> | ( <join table exp> )

<table ref>

::=  <table name> [ [ AS ] <range var name>

[ ( <column name commalist> ) ] ] | ( <nonwi th exp> ) [ AS ]

<range var name>

<nonjoin table exp>

[ ( <column name commalist> ) ] | <join table exp>

::=  <nonjoin table term>

| <nonwith exp> UNION [ ALL | DISTINCT ]

[ CORRESPONDING [ BY ( <column name commalist> ) ] ]

<table term> <nonwith exp> EXCEPT [ ALL | DISTINCT ] [ CORRESPONDING [ BY ( <column name commalist> ) ] ]

<table term>

<nonjoin table term>

::=  <nonjoin table primary>

| <table term> INTERSECT [ ALL | DISTINCT ]

[ CORRESPONDING [ BY ( <column name commalist> ) ] ] <table primary>

<table term>

::=  <nonjoin table term> \ <join table exp>

<table primary>

::=  <nonjoin table primary> \ <join table exp>

<nonjoin table primary>

::=  TABLE <table name> | <table constructor> |

<select exp> | ( <nonjoin table exp> )

<table constructor

::=  VALUES <row constructor commalist>

<row constructor

::=  <scalar exp>

| ( <scalar exp commalist> ) I ( <table exp> )

<select exp>

::=  SELECT [ ALL | DISTINCT ] <select item commalist> FROM <table ref commalist> [ WHERE <bool exp> ]

[ GROUP BY <column name commalist> ] [ HAVING <bool

exp> ]

<select item>

::=                                         <scalar exp>  [   [  AS   ]   <column name>  ] | [   <range var name>.   ]   *

Теперь перейдем к конкретному случаю выражений выборки <select exp>, которые, безусловно, представляют наибольший интерес с  точки зрения практики. Выражения  <select ехр> можно неформально определить как табличные выражения <table exp>, в  которых не применяются операторы JOIN, UNION, EXCEPT или INTERSECT. В данном контексте слово "неформально" означает, что  такие  операторы,  разумеется,  могут  применяться  в  выражениях,  которые  вложены  в рассматриваемое выражение <select  exp>. Как показывает грамматическая структура выражения

<select exp>, в нее могут входить, кроме конструкции SELECT, конструкция FROM,  необязательная конструкция WHERE, необязательная конструкция GROUP BY И необязательная конструкция HAVING, В

указанной последовательности. Ниже каждая из этих конструкций рассматривается по очереди.

Конструкция SELECT

Конструкция SELECT принимает следующую форму.

SELECT   [   ALL   |   DISTINCT   ]   <select   item  commalist>

Пояснение

1.  Разделенный запятыми список элементов выборки <select  item  commalist> должен быть непустым’ (подробное описание опций с обозначением элемента выборки <select i tem> при ведено ниже).

2.  Если не заданы ни ключевое слово ALL, ни ключевое слово DISTINCT, по умолчанию применя ется  ключевое слово ALL.

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

4.  Допустим, что Т2 — таблица, полученная из таблицы Т1 путем вычисления указанного элемента выборки <select  item> применительно к Т1.

5.  Допустим, что ТЗ — таблица, которая получена из таблицы Т2 путем устранения избыточных дубликатов строк из Т2, если задано ключевое слово DISTINCT, или таблица, идентичная Т2, в противном случае.

6.  Таблица ТЗ представляет собой окончательный результат.

Теперь перейдем к описанию элементов выборки <select item>. Для этого необходимо  рассмотреть два случая, причем второй из них относится просто к форме, в которой  применяется сокращенный вариант разделенного запятыми списка <select item> из  первой формы; таким образом, первый случай действительно является более фундаментальным.

Случай 1. Опция <select i ten» принимает следующую форму.

<scalar ехр>   [   [  AS   ]   <column name>   ]

Скалярное выражение <scalar exp> обычно (но не обязательно) включает один или несколько столбцов таблицы  Т1  (см.  п.  3).  Для  каждой  строки  Т1  вычисление  выражения  <scalar  exp> приводит

1  Фактически, все простые списки и разделенные запятыми списки, рассматриваемые в данном при ложении, должны быть непустыми.

2  Иными словами, в контексте оператора SELECT применяемым по умолчанию значением является ALL. В отличие от этого, в контексте UNION, INTERSECT или EXCEPT применяемым по умолчанию зна чением является DISTINCT.

к получению скалярного результата. Разделенный запятыми список таких результатов (соответствующий вычислению всех элементов выборки <select item> в конструкции SELECT применительно к отдельной строке Т1) составляет одну строку таблицы Т2 (см. п. 4). Если элемент <select item> включает конструкцию AS, то неуточненное имя столбца <column пате> из этой конструкции назначается как имя соответствующего столбца Т2 (необязательное ключевое слово AS предусмотрено просто для удобства и может быть опущено; в результате этого смысл конструкции не изменится). Если элемент <select i ten» не включает конструкцию AS, могут рассматриваться следующие два варианта: во-первых, если этот элемент состоит просто из имени столбца <column пате> (возможно, уточненного), то имя <column пате> присваивается в качестве имени соответствующему столбцу таблицы Т2; во-вторых, в противном случае соответствующий столбец Т2, по сути, остается  безымянным  (но фактически этому столбцу присваивается внутреннее имя, зависящее от реализации). Из этого следуют приведенные ниже выводы.

■     Поскольку имя, введенное с помощью конструкции AS, безусловно, относится к столбцу таблицы Т2, а не таблицы Т1, то это имя не может использоваться в конструкциях WHERE, GROUP BY и HAVING, непосредственно участвующих в формировании таблицы Т1 (если они имеются). Однако на него можно ссылаться в соответствующей конструкции ORDER BY, если она имеется, и  также  во  "внешнем" выражении <table   exp>,  которое  содержит вложенное в  него рассматриваемое выражение <select exp>.

■     Если элемент <select   item> включает  вызов агрегирующего оператора,  а  выражение

<select  exp> не включает конструкцию GROUP BY (см. ниже), то ни один элемент <select i tem> в конструкции SELECT не может включать каких-либо ссылок на столбец Т1, если только

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

Случай 2. Опция <select i tem> принимает следующую форму.

[   <range var name>.   ]   *

Если спецификатор опущен (т.е. опция <select item> представляет собой просто звездочку и не имеет уточнения), то данная опция <select item> должна быть единственной  опцией <select item> в конструкции  SELECT. Такая форма является сокращенным  обозначением разделенного запятыми списка всех опций <column пате> для таблицы Т1 в последовательности расположения столбцов слева направо. Если же спецификатор включен (т.е. опция <select item> представляет собой звездочку, уточненную именем переменной  области значений R, например, "R.*"), то опция

<select item> представляет собой разделенный запятыми список имен столбцов <со1итп  пате>,

который включает все столбцы таблицы, связанные с переменной области значений R в последовательности расположения столбцов слева направо. (Напомним, что в разделе 8.6 было отмечено, что в качестве неявно заданной переменной области значений может использоваться и часто используется имя таблицы. Поэтому опция <select i tem> чаще применяется в форме "Т. *", а не "R. *".)

Конструкция FROM

Конструкция FROM принимает следующую форму.

FROM <table ref commalist>

Допустим, что заданные выражения со ссылками на таблицу <table ref> после  вычисления принимают, соответственно, значения таблиц А, В, . . ., Z. Тогда  результатом вычисления конструкции FROM является таблица, равная декартову произведению (в стиле SQL) таблиц А, В, . . . , Z.

Конструкция WHERE

Конструкция WHERE принимает следующую форму.

WHERE <bool  exp>

Допустим, что т — результат вычисления конструкции FROM, непосредственно  предшествующей этой конструкции WHERE. Тогда результат вычисления конструкции WHERE представляет собой таблицу, полученную из  Т  путем удаления всех  строк,  для  которых  логическое  выражение <Ьоо1 ехр> не принимает значения TRUE. Если же конструкция WHERE опущена, то результатом становится просто Т.

Конструкция GROUP BY

Конструкция GROUP BY принимает следующую форму.

GROUP  BY  <column name  commalist>

Допустим, что Т — результат вычисления непосредственно предшествующей конструкции  FROM и конструкции  WHERE  (если  она  имеется).  Каждое  имя  столбца  <column  nаmе>,  упомянутое  в конструкции  GROUP BY, должно представлять собой имя столбца таблицы Т с  необязательным уточнителем. Результатом вычисления конструкции  GROUP BY  является  сгруппированная таблица (иными словами, множество групп строк, полученное из таблицы Т  путем концептуального переупорядочения строк с разбивкой на минимальное количество групп, таких что в каждой отдельной группе все строки имеют  одинаковое  значение  для  комбинации  столбцов,  обозначенной  конструкцией  GROUP  BY). Поэтому  заслуживает  особого  внимания  то,  что  этот  результат  фактически  не  представляет  собой "настоящую таблицу" (еще раз  повторяем, что эта таблица состоит из групп строк, а не из отдельных строк). Но конструкция GROUP BY никогда не применяется без соответствующей конструкции SELECT, в результате  действия которой из таблицы групп создается настоящая таблица, поэтому из-за указанного временного  отклонения  от  инфраструктуры  "настоящей  таблицы"  никаких  нарушений  в  работе  не возникает.

Если выражение выборки  <select ехр> включает  конструкцию  GROUP BY,  то  каждый  элемент выборки  <select  item>  в  конструкции  SELECT  (включая  все  те  элементы,  которые  становятся следствием применения сокращенного обозначения в виде звездочки),  должен быть однозначным в расчете на каждую группу.

Конструкция HAVING

Конструкция HAVING принимает следующую форму.

HAVING  <Ъоо1   ехр>

Допустим, что G — сгруппированная таблица, полученная в результате вычисления  непосредственно предшествующих конструкций: конструкции FROM, конструкции WHERE (если она имеется) и конструкции GROUP BY (если она имеется). Если конструкция GROUP BY отсутствует, то таблица G рассматривается как результат вычисления одних только конструкций FROM и WHERE, поэтому считается сгруппированной таблицей, содержащей одну и только одну группу;3   иными словами, в данном случае применяется неявная концептуальная конструкция GROUP BY,  которая вообще не задает ни одного группирующего столбца. Результатом применения конструкции HAVING является сгруппированная таблица, полученная из таблицы G путем удаления всех групп, для которых логическое выражение <bool ехр> не принимает значения TRUE. Из этого вытекают приведенные ниже выводы.

■     Если конструкция HAVING опущена, а конструкция GROUP   BY включена, то результатом вычисления конструкции HAVING является просто таблица G. Если опущены обе конструкции, и HAVING, и GROUP  BY, то результатом становится просто "настоящая" (т.е. несгруппированная) таблица Т, полученная в результате применения конструкций FROM и WHERE.

■     Любое скалярное выражение <scalar exp> в конструкции HAVING должно быть однозначным в расчете на каждую группу (как и выражения <scalar  exp> в конструкции SELECT, если имеется конструкция GROUP BY, как было описано в предыдущем подразделе).

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

Всеобъемлющий пример

В  завершение этого  описания выражений выборки  <select  exp>  приведем довольно  сложный пример,  который  иллюстрирует  несколько  особенностей  этих  выражений, описанных  а  предыдущих подразделах (но, разумеется, не все). Соответствующий запрос приведен ниже.

Для всех деталей красного и синего цвета, таких что общий объем их поставки превышает 350 (но из итогов исключены все поставки, для которых это количество меньше  или равно 200), получить номера деталей, вес в граммах, цвет и максимальное поставляемое количество этих деталей.

Ниже приведена возможная формулировка этого запроса на языке SQL. SELECT   P . P # ,

‘Weight in grains =’ AS TEXT1, P.WEIGHT * 454 AS GMWT, P.COLOR,

‘Max quantity =’ AS TEXT2, MAX ( SP.QTY ) AS MXQTY FROM  P, SP WHERE P.P# = SP.P#

AND ( P.COLOR = COLOR (‘Red’) OR P.COLOR = COLOR (‘Blue’) ) AND SP.QTY > QTY ( 200 ) GROUP BY P.P#, P.WEIGHT, P.COLOR HAVING SUM ( SP.QTY ) > QTY ( 350 ) ;

Пояснение.  Прежде  всего  необходимо  отметить,  что  (как  указано  в  предыдущих   подразделах) конструкции  выражения  <select  exp>  концептуально  вычисляются  в  том  порядке,  в  каком  они записаны. Единственным исключением из этого правила является сама  конструкция SELECT, которая вычисляется в  последнюю очередь. Поэтому в  данном примере  можно  предположить, что  результат формируется, как описано ниже.

1.              Конструкция FROM. В приведенном выше предложении конструкция FROM вычисляется для по лучения новой таблицы, которая представляет собой декартово произведение таблиц Р и SP.

2.              Конструкция WHERE. К результату, полученному в шаге 1, применяется операция сокращения, выполняемая путем удаления всех строк, которые не удовлетворяют условию конструкции WHERE. Поэтому в данном примере удаляются строки, которые не соответствуют следующему логическому выражению <Ьоо1 ехр>.

Р . Р #   =  SP.P#

AND ( P.COLOR = COLOR (‘Red’) OR P.COLOR = COLOR (‘Blue’) ) AND SP.QTY > QTY ( 200 )

3.              Конструкция GROUP BY. Результат, полученный в шаге 2, группируется по значениям в столбце (столбцах), названном в конструкции GROUP BY. В данном примере такими столбцами являются Р. Р#, Р . WEIGHT И Р. COLOR.

4.              Конструкция HAVING. Из результата, полученного в шаге 3, удаляются группы, не удовлетво ряющие следующему логическому выражению <bool exp>: SUM  (  SP.QTY )    > QTY  ( 350 ).

5.              Конструкция SELECT. Каждая группа в результате шага 4 применяется для выработки одной строки окончательного результата следующим образом. Во-первых, из группы извлекаются дан ные о номере детали, весе, цвете и максимальном количестве. Во-вторых, вес преобразуется в граммы. В-третьих, в соответствующие места данной строки вставляются две символьные строки "Weight  in grams  =" и "Max quantity =".  Кстати, следует отметить, что здесь использует ся тот факт, что в языке SQL столбцы таблиц имеют упорядочение слева направо (о чем говорит фраза "соответствующие места данной строки"); если бы текстовые надписи появлялись не в этих "соответствующих местах", то они имели бы мало смысла.

Окончательный результат выглядит следующим образом.

Читатель должен учитывать, что приведенное выше описание алгоритма  выполнения запроса предназначено исключительно  в качестве концептуального  объяснения того, как происходит  вычисление  выражения  <select  exp>.  Этот  алгоритм,  безусловно,  является правильным в том смысле, что он гарантирует получение правильного результата. Но если бы он действительно выполнялся в соответствии с этим описанием, то оказался бы довольно неэффективным.  Например,  вряд  ли  было  бы  целесообразно  фактически  формировать декартово произведение в шаге 1. Именно такие соображения и являются той причиной, по которой в  реляционных системах требуется оптимизатор, как  было описано в главе 18. Безусловно,  задачу  оптимизатора  в  системе  SQL  можно  охарактеризовать  как  поиск процедуры  реализации,  позволяющей  получить  точно  такой  же   результат,   как   при использовании  только  что  (схематически)  описанного  концептуального  алгоритма,  но являющейся более эффективной, чем этот алгоритм.

Источник: Дейт К. Дж., Введение в системы баз данных, 8-е издание.: Пер. с англ. — М.: Издательский дом «Вильямс», 2005. — 1328 с.: ил. — Парал. тит. англ.

По теме:

  • Комментарии