Главная » SQL, Базы данных » ВЫЧИСЛИТЕЛЬНЫЕ ВОЗМОЖНОСТИ

0

Несмотря на то, что ранее об этом не упоминалось, в определенном нами реляционном исчислении уже есть аналоги алгебраических операторов EXTEND и SUMMARIZE ПО перечисленными ниже причинам.

■     Одной из допустимых форм кортежа-прототипа является параметр < tuple se lector inv>  ("вызов селектора кортежа"), компонентами которого могут быть произвольные выражения.

■     В параметре <Ъоо1   ехр> сравниваемыми элементами могут быть произвольные выражения.

■     Как было показано в главе 7, первым или единственным параметром <agg op inv> ("вызов агрегирующего оператора")    является реляционное выражение

<relation exp>.

Мы считаем, что здесь не следует приводить все возможные синтаксические и семантические сведения; достаточно лишь рассмотреть несколько типичных примеров (сами эти примеры также несколько упрощены).

8.5.1.    Определить номера и вес в граммах всех типов деталей, вес которых превышает 10 000 г

{ РХ.Р#, РХ.WEIGHT * 454 AS GMWT }

WHERE PX.WEIGHT * 454 > WEIGHT ( 10000.0 )

Обратите внимание, что спецификация AS в кортеже-прототипе (как и в примере 8.3.2) дает имя соответствующему атрибуту результата. Поэтому такое имя недоступно для использования в конструкции WHERE и выражение РХ. WEIGHT * 454 должно быть указано в двух местах.

8.5.2.    Выбрать сведения обо всех поставщиках и обозначить каждого из них литеральным значением "Supplier"

{   SX,     ‘Supplier’  AS    TAG  }

8.5.3.    Получить полные сведения о каждой поставке, включая общий вес поставки

{ SPX, PX.WEIGHT * SPX.QTY AS SHIPWT } WHERE PX.P# = SPX.P#

8.5.4.    Для каждой детали получить номер детали и общий объем поставки в штуках

{   РХ.Р#,    SIM   (   SPX  WHERE   SPX.P#   =   PX.P#,    QTY   )    AS   TOTQTY   }

8.5.5.    Определить общее количество поставляемых деталей

SUM  (  SPX,   QTY  )  AS  GRANDTOTAL

8.5.6.    Для каждого поставщика получить номер поставщика и общий объем поставки в штуках

{ SX.S#, COUNT ( SPX WHERE SPX.S# = SX.S# ) AS #_OF_PARTS }

8.5.7.    Указать названия таких городов, в которых хранятся детали, что в них находится больше пяти деталей красного цвета

RANGEVAR PY RANGES OVER P ;

PX.CITY WHERE COUNT ( PY WHERE PY.CITY = PX.CITY

AND PY.COLOR = COLOR (‘Red’) ) > 5

8.6. СРЕДСТВА ЯЗЫКА SQL

Как уже говорилось в разделе 8.4, реляционный язык может быть основан как на реляционной алгебре, так и на реляционном исчислении. Что же лежит в основе языка SQL? К сожалению, ответом будет "частично и то, и другое, а частично ни то, ни другое…".

Когда язык SQL только разрабатывался, предполагалось что он будет отличаться как от реляционной алгебры, так и от реляционного исчисления [4.8]. Действительно, именно этим мотивировалось введение в язык конструкции IN <subquery> (см. пример 8.6.10, приведенный ниже). Однако со временем выяснилось, что язык SQL нуждается в определенных средствах как реляционной алгебры, так и исчисления, поэтому он был расширен для включения этих функций4. На сегодняшний день ситуация складывается таким образом, что язык SQL в чем-то похож на реляционную алгебру, в чем-то на реляционное исчисление,  а  в чем-то отличается от них обоих. Таким положением дел объясняется, почему в главе 7 мы отложили обсуждение средств обработки данных языка SQL до настоящей главы. (Мы предоставляем читателю в качестве упражнения определить, какая часть языка SQL основана на алгебре, какая на исчислении, а какая ни на  том, ни на другом.)

Запросы в языке SQL формулируются в виде табличных выражений < table  exp>, которые в принципе могут иметь очень высокую степень сложности. Здесь мы не будем углубляться во все эти нюансы, а просто рассмотрим несколько примеров, раскрывающих наиболее важные моменты. В качестве основы для  примеров взяты определения таблиц SQL для базы данных поставщиков и деталей, представленные в главе 4 (см. рис. 4.1).

8.6.1.Указать цвета деталей и названия городов для деталей, которые имеют вес свыше

10 фунтов и хранятся в городах, отличных от Парижа

SELECT PX.COLOR, PX.CITY FROM  P AS PX

WHERE PX.CITY <> ‘Paris’

AND   PX.WEIGHT > WEIGHT ( 10.0 ) ;

Необходимо отметить следующее.

1.          Как было указано в главе 5, в языке SQL в качестве оператора проверки на нера венство используется символ "о". Операторы "меньше или равно" и "больше или равно", соответственно, записываются как"< = " и ">=".

2.          Спецификация Р AS PX в конструкции FROM по сути представляет собой опреде ление (в стиле исчисления кортежей) переменной области значений с именем РХ, областью значений которой является текущее значение таблицы Р. Само имя (а не переменную!) РХ принято называть именем корреляции, а областью его определе ния, неформально говоря, является табличное выражение, в котором появляется его определение, исключая любое внутреннее выражение, в котором определена другая переменная области значений с тем же именем (см. пример 8.6.12).

4  Вследствие этого, как отмечается в аннотации к [4.19], конструкцию IN <subquery> можно  полностью удалить из языка без потери его функциональности! В этом есть некоторая  ирония, поскольку благодаря именно указанной конструкции в названии данного языка, в  переводе означающего "язык структурированных  запросов"  (Structured  Query  Language),   появилось  слово  "структурированный" (Structured). В действительности именно эта конструкция способствовала тому, что в первую очередь для работы с базами данных был применен язык  SQL,  и лишь затем реляционная алгебра и реляционное исчисление.

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

SELECT P.COLOR, P.CITY FROM  P

WHERE P.CITY <> ‘Paris’

AND   P.WEIGHT > WEIGHT { 10.0 ) ;

Основная идея состоит в том, что должно быть разрешено использовать имя  таблицы  для  обозначения  неявно  заданной  переменной  области  значений,  которая принимает значения из рассматриваемой таблицы (разумеется, при том условии, что результаты  не  допускают  неоднозначного  толкования).  Например,  конструкцию FROM   P   в   данном   примере   можно   рассматривать   как   сокращенную   запись конструкция  FROM  P  AS  P.  Другими  словами,  необходимо  четко  понимать,  что уточняющее имя Р  (например) в выражении P.COLOR в  конструкциях  WHERE  и SELECT  обозначает не саму таблицу Р, а переменную области значений Р, которая принимает свои значения из одноименной таблицы.

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

SELECT COLOR, CITY FROM P

WHERE CITY <> ‘Paris’

AND WEIGHT > WEIGHT ( 10.0 ) ;

Согласно общему правилу языка SQL, неутонченные имена допускаются во  всех случаях, когда это не вызывает неоднозначности. Однако в наших  примерах спецификаторы будут обычно (но не всегда!) использоваться и в  тех случаях, когда формально они излишни. К сожалению, в определенных контекстах явно требуется, чтобы имена столбцов были не уточнены! Например, это требуется5  в конструкции ORDER BY (см. следующий пример).

5.     В интерактивных запросах SQL может также использоваться конструкция ORDER BY, уже упоминавшаяся в главе 4 в связи с объявлением DECLARE CURSOR, как показано ниже.

SELECT P.COLOR, P.CITY FROM  Р

WHERE P.CITY <> ‘Paris’

AND   P.WEIGHT > WEIGHT ( 10.0 )

ORDER BY CITY DESC ; /* Следует о

6.           Напоминаем, что допускается использование сокращения SELECT   *, о котором также упоминалось в главе 4.

SELECT * FROM   P

WHERE P.CITY <> ‘Paris’

AND   P.WEIGHT > WEIGHT ( 10.0 )

5 За исключением тех случаев, которые указаны в разделе 4.6 главы 4.

Символ "*" в выражении SELECT * представляет собой сокращенное обозначение разделенного запятыми списка имен всех столбцов  таблицы  (или таблиц), указанной в конструкции FROM. В этом списке имена столбцов находятся в том же  порядке,  в  котором  они   расположены  в  соответствующей  таблице  (или таблицах). Следует  отметить, что такую сокращенную запись особенно удобно использовать   в   интерактивных  запросах,  поскольку  при  этом  уменьшается количество нажатий клавиш. Однако при использовании этой  конструкции во внедренных  операторах  SQL  (т.е.  в  операторах  языка   SQL,  внедренных  в программу  на  другом  языке)  существует  скрытая   опасность,  поскольку  в подобных   случаях   символ  "*"  может   приобрести  совсем   другое  значение (например, после добавления в  таблицу столбца или его удаления с помощью оператора ALTER TABLE).

7.     {Более важная информация по сравнению с приведенной в предыдущих пунктах!) Обратите внимание, что для используемого нами в примерах набора данных этот запрос будет возвращать четыре строки, а не две, несмотря на то, что три из них будут совершенно идентичны. В языке SQL не предусмотрено удаление излишних дублирующихся строк из результата оператора SELECT, пока пользователь явно не потребует этого с помощью ключевого слова DISTINCT, как показано ниже.

SELECT DISTINCT P.COLOR, P.CITY FROM  P

WHERE P.CITY <> ‘Paris’

AND   P.WEIGHT > WEIGHT ( 10.0 ) ;

Данный вариант запроса будет возвращать уже две строки, а не четыре.

Из всего вышесказанного следует (как уже было фактически указано в главе 6), что фундаментальным объектом данных в языке SQL является не  отношение, а скорее  таблица,  и  таблицы  SQL  содержат  (вообще  говоря)  не  множества,  a мультимножества    строк    (в    мультимножествах    допускаются    повторения элементов). Таким образом, в языке SQL  нарушается информационный принцип. Одно из следствий этого факта  состоит  в  том, что основные  операторы SQL являются  не   реляционными   операторами  в  полном  смысле  этого  слова,  а аналогами    реляционных    операторов,    предназначенных    для     работы     с мультимножествами. Другим следствием является то, что теоремы и их побочные результаты, которые являются справедливыми в реляционной модели (например, о преобразовании выражений, [6.6]), не обязательно выполняются в языке SQL.

8.6.2. Для всех деталей указать номер детали и вес в граммах (упрощенная версия примера 8.5.1)

SELECT Р.Р#, P.WEIGHT * 454 AS GMWT FROM  P ;

Спецификация AS GMWT вводит соответствующее имя столбца результата для  "вычисленного столбца". Таким образом, два столбца результирующей таблицы будут называться р# и GMWT. Если бы спецификация AS GMWT была опущена, то соответствующий ; столбец остался бы фактически безымянным. Отметим, что  хотя в подобных случаях правила языка SQL в действительности не требуют от пользователя указания имени результирующего столбца, в наших примерах будем их задавать всегда.

8.6.3. Получить все комбинации данных о поставщиках и деталях,

находящихся в одном городе

В языке SQL существует несколько способов формулирования этого запроса. Приведем три самых простых.

1.  SELECT S.*, P.P#, P.PNAME, P.COLOR, P.WEIGHT FROM  S, Р

WHERE S.CITY = P.CITY ;

2.  S JOIN P USING CITY ;

3.  S NATURAL JOIN P ;

Результатом в каждом случае6 будет естественное соединение таблиц S и Р (по атрибуту города CITY).

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

■     Во-первых, после выполнения конструкция FROM МЫ получаем декартово произведение S TIMES P. (Строго говоря, перед вычислением произведения следовало бы позаботиться о переименовании столбцов. Для простоты мы этот вопрос не рас сматриваем. Напоминаем также, что, как следует из сказанного в разделе 7.7, де картовым произведением единственной таблицы t является сама таблица t.)

■     Во-вторых, после выполнения конструкция WHERE МЫ получаем сокращение этого произведения, в котором два значения атрибута CITY В каждой строке равны (иначе говоря, выполнено соединение таблиц поставщиков и деталей по эквивалентности атрибутов с обозначением города).

■     В-третьих, после выполнения конструкция SELECT мы получаем проекцию выбор ки по столбцам, указанным в конструкции SELECT. Конечным результатом становится естественное соединение указанных таблиц.

Следовательно, неформально говоря, в языке SQL конструкция FROM соответствует декартову произведению, конструкция WHERE — операции сокращения, а конструкции SELECT-FROM-WHERE, вместе взятые, — проекции сокращения произведения (но, как было указано выше, рассматриваемая "проекция" не обязательно устраняет дубликаты).

8.6.4.   Найти все пары названий городов, таких что поставщик, находящийся в первом городе, поставляет деталь, хранящуюся во втором городе

SELECT DISTINCT S.CITY AS SCITY, P.CITY AS PCITY FROM  S JOIN SP USING S# JOIN P USING P# ;

Обратите   внимание,   что   приведенный   ниже  оператор  является  неправильным

(объясните, почему).

6 В стандарте SQL:2003, по-видимому, будет предусмотрено требование, чтобы вторая и третья формулировки включали префикс "SELECT * FROM".

SELECT DISTINCT S.CITY AS SCITY, P.CITY AS PCITY FROM   S NATURAL JOIN SP NATURAL JOIN P ;

Ответ. Поскольку во втором соединении он включает столбец CITY как столбец, по которому выполняется соединение.

8.6.5.Получить все пары номеров поставщиков, таких что оба поставщика в каждой паре находятся в одном городе (см. пример 8.3.2)

SELECT A.S# AS SA, B.S# AS SB FROM  S AS A, S AS В WHERE  A.CITY = B.CITY AND A.S# < B.S# ;

В этом примере требуется явно указывать переменные области значений. Также следует отметить, что вводимые имена столбцов SA и SB относятся к столбцам результирующей таблицы, и потому не могут использоваться в конструкции WНЕRЕ.

8.6.6.Определить общее количество поставщиков

SELECT S.S# FROM   S

WHERE S.STATUS <

( SELECT MAX (

S.STATUS ) FROM  S )

;

В этом примере используются две отдельные неявные переменные области значений,

обозначенные тем же именем S и принимающие значения из той же таблицы S.

8.6.13.Определить имена поставщиков детали с номером Р2

Примечание. Этот пример повторяет пример 8.6.10. Ниже приведено другое решение,

позволяющее представить еще одно средство языка SQL.

SELECT DISTINCT S.SNAME FROM   S WHERE  EXISTS

( SELECT * FROM   SP

WHERE     SP.S#   =   S . S #

AND                                        SP.P#   =   P#     ( ‘ P 2 ‘ )     ) ;

Пояснение. Выражение EXISTS (SELECT … FROM …) на языке SQL принимает  значение TRUE тогда и только тогда, когда результат вычисления выражения  SELECT … FROM … будет непустым. Другими словами, в языке SQL функция EXISTS соответствует квантору существования реляционного исчисления (в большей или меньшей степени; см. [19.6]).

Примечание. В спецификации SQL ссылки на подзапрос, аналогичные показанным в данном примере, называются ссылками на коррелированный подзапрос (correlated subquery), поскольку в данном подзапросе содержится ссылка на переменную области значений (а именно — на неявную переменную области значений S), которая определена во внешнем

запросе. Еще одним примером коррелированного подзапроса может служить  пример 8.6.8.

8.6.14.     Определить имена поставщиков, которые не поставляют деталь с номером Р2 (пример 8.3.7)

SELECT DISTINCT S.SNAME FROM  S

WHERE NOT EXISTS

( SELECT * FROM   SP

WHERE SP.S# = S.S# AND

SP.P# = P# (‘P2′) ) ;

Этот же запрос можно также представить в альтернативной формулировке.

SELECT DISTINCT S.SNAME FROM   S WHERE S.S# NOT IN

( SELECT SP.S#

FROM  SP                                        WHERE SP.P#

= P# ( ‘ P2 ‘ ) ) ;

8.6.15.Определить имена поставщиков, которые поставляют детали  всех типов (см. пример 8.3.6)

SELECT DISTINCT S.SNAME FROM   S

WHERE NOT EXISTS   ( SELECT * FROM  P WHERE  NOT EXISTS

( SELECT *   FROM  SP WHERE SP.S# = S.S#    AND SP.P# = P.P# ) ) ;

Язык SQL не включает какой-либо непосредственной поддержки квантора  всеобщности FORALL; следовательно, запросы "с кванторами FORALL" обычно  выражаются с помощью квантора существования и двойного отрицания, как и в этом примере.

Следует отметить, что выражения, подобные показанному выше, хотя на  первый взгляд и кажутся довольно устрашающими, легко формируются пользователями, знакомыми с реляционным исчислением, как отмечается в [8.4]. В ином случае, если подобные примеры все еще кажутся слишком сложными,  существует несколько "обходных" путей, позволяющих избежать использования отрицаемых кванторов. В данном случае, например, можно записать запрос следующим образом.

SELECT DISTINCT S.SNAME FROM  S

WHERE ( SELECT COUNT ( SP.P# ) FROM  SP

WHERE SP.S# = S.S# )

= ( SELECT COUNT ( P.P#

)

FROM  P ) ;

(Расшифровка: "Получить имена поставщиков, для которых количество  поставляемых деталей равно количеству всех деталей".) Однако следует отметить, что в последней формулировке (в отличие от формулировки с выражением NOT EXISTS) используется тот факт, что номер каждой поставляемой детали является номером существующей детали. Другими словами, эти две формулировки эквивалентны, но вторая является правильной только благодаря поддержке некоторого ограничения целостности (подробности приводятся в следующей главе).

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

SELECT DISTINCT S.SNAME /* Предостережение! Недопустимый запрос! */

FROM   S

WHERE { SELECT SP.P# FROM   SP

WHERE  SP.S# = S.S#

) = ( SELECT P.P# FROM   P ) ;

Но в языке SQL непосредственно не поддерживается операция сравнения таблиц, поэтому приходится прибегать к уловке, используя сравнение кардинальностей таблиц вместо сравнения таблиц (опираясь на практический опыт, который свидетельствует о том, что если кардинальности таблиц равны, то и таблицы одинаковы, по крайней мере, в обсуждаемом случае). Дополнительный материал по данной теме приведен в упр. 8.11.

8.6.16.Определить номера деталей, которые либо весят более 16 фунтов, либо поставляются поставщиком с номером S2, либо соответствуют и тому, и другому условию (см. пример 8.3.9)

SELECTP.P# FROM  P

WHERE P.WEIGHT > WEIGHT ( 16.0 )

UNION

SELECT SP.P# FROM   SP

WHERE SP.S# = S# (‘S2′) ;

Лишние повторяющиеся строки всегда исключаются из результата  выполнения  неуточненных операторов UNION, INTERSECT И EXCEPT (В языке  SQL оператор EXCEPT служит аналогом операции MINUS реляционной алгебры). Однако язык SQL также поддерживает  уточненные  варианты  этих  операторов  (UNION  ALL, INTERSECT  ALL  И EXCEPT ALL), при которых  повторяющиеся строки (если они есть) сохраняются. Примеры с этими вариантами операторов умышленно не показаны.

8.6.17.Определить номер детали и вес в граммах для каждой детали с весом

> 10 000 г (см. пример 8.5.1)

SELECT P.P#, P.WEIGHT * 454 AS GMWT FROM  P

WHERE P.WEIGHT * 454 > WEIGHT ( 10000.0 ) ;

Теперь необходимо вспомнить определение конструкция WITH, которая была впервые представлена в главе 5 и использовалась при описании реляционной алгебры9  в главе 7. Неформально  выражаясь,  конструкция  WITH  предназначена  для  присваивания  имен выражениям. В языке SQL также имеется конструкция WITH, но ее применение ограничивается только выражениями с таблицами. В данном примере с помощью этой конструкции можно избежать необходимости  дважды записывать выражение P.WEIGHT * 454, как показано ниже.

WITH Tl AS ( SELECT P.P#, P.WEIGHT * 454 AS GMWT FROM  P )

SELECT T1.P#, Tl.GMWT FROM   Tl WHERE Tl.GMWT > WEIGHT ( 10000.0

) ;

Кстати, следует отметить, что записи в конструкции WITH (которые в предыдущей главе именовались как <name intro> — определение имени) в языке SQL принимают вид<name> AS (<ехр>), а в языке Tutorial D имеют форму  <ехр> AS <name>. Необходимо также учитывать, что конструкция WITH  приобретает важное значение, когда требуется сформулировать на языке SQL  аналог алгебраического оператора TCLOSE. Дополнительные сведения здесь не  представлены, но соответствующий пример можно найти в ответе на упр. 4.6, который представлен в приложении Д.

На этом список примеров применения языка SQL для выборки данных завершается. Хотя этот список был достаточно большим, о многих возможностях языка SQL здесь даже не упоминалось. Язык SQL в действительности является  чрезвычайно избыточным [4.19] в том смысле, что почти всегда существует множество способов представления одного и того же запроса, и нам не хватит места, чтобы описать все возможные формулировки и все возможные опции даже для сравнительно небольшого числа примеров, которые рассматривались в этой главе. (Дополнительные сведения на эту тему приведены в приложении Б.)

9 Безусловно, эта конструкция может также использоваться в реляционном исчислении.

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

По теме:

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