Предложение GROUP BY и агрегатные функции SQL

Оператор SELECT, включающий предложение GROUP BY, реализует реляционную операцию подведения итогов [2, п. 2.5.5]. Предложение GROUP BY определяет группы строк рабочего буфера (F - или W -таблицы), для которых должны быть вычислены значения агрегатных функций. После обработки предложения строки рабочего буфера оказываются собранными в группы с одинаковыми значениями столбцов, указанных после ключевого слова.

Например, если рабочий буфер содержит эквивалент таблицы SPJ, и система приняла предложение

GROUP BY Snum, Jnum

то после его обработки в буфере будет содержаться следующая G -таблица[18]:

Snum	Pnum	Jnum	Qt
S1	P1	J3
S1	P2	J3
S2	P9	J4
S1	P2	J5
S7	P6	J1
S3	P9	J9
S5	P3	J8
S6	P8	J9
S6	P5	J9
S7	P8	J2
S7	P3	J2
S8	P7	J6
S8	P1	J6
S8	P3	J6
S8	P9	J2
S8	P1	J1
S4	P4	J1
S2	P3	J7
S3	P4	J8

Замечание. При обработке все NULL -значения солбцов группирования считаются одинаковыми.

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

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

Стандарты SQL дают следующее определение синтаксиса агрегатных функций, упомянутых в [2, п. 2.5.5]:

агрегатная_функция::= { COUNT(*) } |

{

{ COUNT | SUM | AVG | MAX | MIN }

([DISTINCT | ALL] выражение)

}

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

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

Функция COUNT(*) возвращает полное число строк в группе, включая пустые и дубликаты.

При вычислении значений остальных пяти функций действуют следующие правила:

· перед вычислением значения функции все неопределённые значения аргумента исключаются;

· если определён параметр DISTINCT, то дубликаты значений аргумента в подсчёте значения функции не участвуют, в противном случае используются все значения аргумента (параметр ALL определён по умолчанию);

· если аргумент пуст, т.е. содержит только NULL -значения, то функция COUNT возвращает значение 0 (ноль), а все остальные функции – значение NULL;

· функция COUNT всегда возвращает целое число; другие функции наследуют типы аргументов;

· функци SUM и AVG работают только с аргументами числовых типов;

· функции COUNT, MAX и MIN могут использоваться с аргументами любых типов.

Пример 1. Получить общее число зарегистрированных поставок.

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

SELECT COUNT(*) AS Число_поставок

FROM SPJ;

Запрос возвратит следующую таблицу:

Число_поставок

В нашем частном случае это истина. Однако таблицы SQL – это не отношения. Представим себе, что не определён первичный ключ таблицы SPJ. Тогда в ней могут оказаться и частично или полностью пустые строки, и дубликаты строк, т.е. мусор, который нередко встречается в БД, созданных дилетантами. Наш оператор подсчитает все строки. Поэтому, если мы не уверены в том, что таблица не содержит мусора, нам следует сформулировать запрос так: «Подсчитать число строк таблицы SPJ, в которых все столбцы имеют определённые значения и наборы значений столбцов Snum, Pnum, Jnum не повторяются». Попытайтесь самостоятельно записать соответствующий оператор. Он будет значительно сложнее приведённого выше.

Пример 2. Подсчитать средний объём поставки детали P1.

SELECT AVG(Qt)

FROM SPJ

WHERE Pnum = ‘P1’;

Пример 3. Для каждого поставщика подсчитать количество поставляемых им видов деталей.

Это двусмысленная формулировка, но, скорее всего, именно так и будет сформулировано пожелание бухгалтера, для которого нужно создать программу подготовки отчётов. Что ему нужно на самом деле? Количество видов деталей, поставляемых каждым поставщиком, зарегистрированным в БД? Или только тем, кто реально выполнял поставки? Какие сведения о поставщике должны быть включены в выходную таблицу? Только номер? Номер и имя? И т.п.

Пусть после уточнения требования пользователя оказались следующими: «Для каждого существующего поставщика получить его номер и количество поставляемых им видов деталей». В текущем состоянии БД с некоторыми поставщиками могут быть не связаны какие-либо поставки. Поэтому в качестве источника данных следует использовать левое внешнее соединение таблиц S и SPJ.

SELECT S.Snum AS Поставщик,

COUNT(DISTINCT Pnum) AS Видов

FROM S LEFT JOIN SPJ

GROUP BY S.Snum;

Поставщик	Видов
S1
S7
S3
S9
S5
S6
S4
S8
S2

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

Предложение HAVING

Предложение HAVING определяет условие, которому должны удовлетворять группы строк, созданные предложением GROUP BY. Предикат предложения HAVING может содержать только выражения для вычисления значений, принимающие единственное значение на группе строк. Обычно используются агрегатные функции и ссылки на столбцы группирования. В выходную таблицу запроса попадают только строки, соответствующие группам, на которых предикат принял значение TRUE. Если HAVING используется без GROUP BY, то группой строк, на которой оценивается его предикат, является всё множество строк таблицы предыдущего уровня.

Пример 1. Получить значения номеров поставщиков, номеров деталей и превосходящих 500 штук суммарных объёмов поставок этих деталей, выполненных этими поставщиками.

SELECT Snum AS Поставщик,

Pnum AS Деталь,

SUM(Qt) AS Объём

FROM SPJ

GROUP BY Snum, Pnum

HAVING SUM(Qt) > 500;

Этот оператор произведёт следующую таблицу:

Поставщик	Деталь	Объём
S1	P2
S3	P9
S6	P5
S7	P3
S7	P6
S7	P8
S8	P1

Пример 2. Получить значения номеров деталей, суммарные объёмы поставок каждой из которых, выполненные поставщиком S7, превосходят 500 штук.

SELECT Pnum Деталь, SUM(Qt) Объём

FROM SPJ

WHERE Snum = 'S7'

GROUP BY Pnum

HAVING SUM(Qt) > 500;

Деталь	Объём
P3
P6
P8

Другая, может быть, менее очевидная формула такова:

SELECT PNUM Деталь, SUM(QT) Объём

FROM SPJ

GROUP BY SNUM, PNUM

HAVING SNUM = 'S7' AND SUM(QT) > 500;

Вложение запросов

Оператор SELECT производит неименованную таблицу. На неё нельзя ссылаться из другого оператора. Однако какой-то механизм управления работой операторов в зависимости от результатов выборки необходим. В современном SQL это механизм вложения запросов.

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

Подзапросы обычно используются в предикатах. Это означает, что подзапрос может быть частью другого подзапроса.[19] Стандарт SQL1 допускает только такое применение. Стандартом же SQL2 разрешается, кроме того, использование подзапросов в выражениях для вычисления значений, в предложении FROM оператора выборки данных и в конструкторах значений строк. Познакомимся с некоторыми деталями механизма вложения запросов на примере оператора выборки данных.

Подзапрос в предложении FROM определяет часть источника данных для включающего оператора выборки. В процессе обработки включающего запроса он исполняется один раз – при формировании F -таблицы. Подзапрос может ссылаться только на столбцы таблиц, указанных в его собственном предложении FROM. Выходная таблица подзапроса отражает текущее состояние его источника данных. Она получает имя, действительное только внутри включающего запроса. На это имя можно ссылаться во всех предложениях включающего запроса как на имя таблицы. Один пример использования подзапроса в предложении FROM мы уже видели (см. п. 6.2.9, пример 1, вариант 3).

Пример 1. Получить все сведения о поставляемых деталях и общие объёмы поставок этих деталей.

Может показаться, что требуемый результат выдаст оператор

SELECT P.*, SUM(Qt)

FROM P JOIN SPJ

GROUP BY P.Pnum;

Однако он будет отвергнут системой, поскольку целевой список содержит недопустимые элементы.

Представим себе, что в нашем распоряжении есть таблица, в которой для каждой поставляемой детали указан общий объём поставок. Тогда мы могли бы получить требуемый результат как её естественное соединение с таблицей P. Нужную таблицу можно получить непосредственно при обработке предложения FROM.

SELECT P.*, SUMQ.SQT

FROM P JOIN (

SELECT Pnum, SUM(Qt) AS SQT

FROM SPJ

GROUP BY Pnum

) AS SUMQ;

Из нашей учебной БД будут извлечены следующие данные:

Pnum	Pnam	We	Co	Ci	SQT
P1	корпус		белый	Томск
P7	кнопка		оранжевый	Асино
P4	корпус		синий	Лесото
P9	лампочка		красный	Томск
P5	панель		серый	Асино
P6	кинескоп		черный	Яя
P2	разъём		чёрный	Яя
P8	панель		розовый	Асино
P3	кнопка		красный	Томск

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

Подзапросы в предикатах предложений WHERE и HAVING используются для управления процессом выборки данных. Далее в настоящем разделе мы будем для определённости говорить о предикатах предложения WHERE. Однако всё нижесказанное в равной мере относится и к предикатам предложения HAVING.

Значения данных из произведённой подзапросом таблицы никогда не попадают в выходную таблицу включающего запроса. От них зависит истинностное значение предиката, а значит и решение о том, включать или не включать конкретную строку в соответствующую временную таблицу.

Стандарт допускает использование подзапросов в любых предикатах. Однако существуют некоторые ограничения. Укажем важнейшие из них, имея в виду приведённое нами определение предиката SQL1.[20]

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

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

· В предикате EXISTS можно использовать табличный подзапрос, возвращающий таблицу из любого количества столбцов и строк.

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

Простой подзапрос обрабатывается один раз. Возвращённая подзапросом таблица используется для вычисления значений предиката на каждой строке F -таблицы.

Пример 2. Вернёмся к примеру 1 из п. 6.2.9 и запишем формулу оператора выборки, основанную на следующем словесном представлении:

«Получить значения атрибута Jnam из таких кортежей отношения J, в которых значения атрибута J.Jnum принадлежат множеству значений атрибута SPJ.Jnum из таких кортежей отношения SPJ, в которых SPJ.Snum = 'S1'».

В соответствии с этим представлением достаточно однажды построить нужное множество значений SPJ.Jnum, а затем для каждой строки таблицы J выполнить проверку принадлежности значения J.Jnum этому множеству. В переводе на SQL это выглядит так:

SELECT Jnam

FROM J

WHERE Jnum IN (

SELECT DISTINCT Jnum

FROM SPJ

WHERE Snum = 'S1'

);

Обрабатывая предложение WHERE включающего запроса, система прежде всего исполнит подзапрос в предикате IN. Он возвратит значения номеров изделий, для которых поставщик S1 выполнял поставки деталей:

Jnum

J3

J5

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

Замечание 1. Параметр DISTINCT в подзапросе можно было бы не использовать. Тогда результат содержал бы дубликат первой строки. Однако это никак не повлияло бы на результаты проверки.

Замечание 2. Имя Jnum во включающем и вложенном запросах относится к столбцам разных таблиц, но мы не использовали префиксы для уточнения имён. В этом нет необходимости, т.к. обрабатывая подзапрос, система движется по кратчайшему ссылочному пути. Ссылка трактуется как внешняя, только если ссылочный столбец не обнаружен в источнике данных подзапроса.

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

Пример 3. Рассмотрим ещё одну возможную словесную формулировку запроса о наименованиях изделий: «Получить значения атрибута Jnam из таких кортежей отношения J, для которых в отношении SPJ существуют кортежи со значениями SPJ.Jnum, совпадающими с текущим значением J.Jnum и значениями SPJ.Snum, равными S1».

Эта формулировка предполагает проверку факта существования соответствующих строк в таблице SPJ для каждой строки таблицы J. Такую проверку может выполнить предикат EXISTS.

SELECT Jnam

FROM J

WHERE EXISTS (

SELECT *

FROM SPJ

WHERE SPJ.Jnum = J.Jnum

AND Snum = 'S1'

);

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

Обычно в целевом списке подзапроса предиката EXISTS используется спецификатор *. Хотя стандарт не запрещает явное указание имён столбцов, это не имеет смысла, т.к. значения столбцов не используются при вычислении значения предиката. Оно зависит лишь от наличия или отсутствия строк в выходной таблице подзапроса.

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

Пример 4. Получить номера, наименования и города размещения деталей, суммарные объёмы поставок которых максимальны.[21]

Требуемые данные можно получить только из таблицы P, но для выбора нужных строк придётся использовать данные из таблицы SPJ. Выполняя запрос средствами «бумажной» технологии, мы, скорее всего, использовали бы такой план.

– Создать вспомогательную таблицу с заголовком (номер детали, суммарный объём поставок).

– Для каждого значения номера детали из таблицы SPJ вычислить суммарный объём поставок и заполнить вспомогательную таблицу.

– Выбрать значения номеров деталей из тех строк вспомогательной таблицы, в которых значение суммарного объёма поставок максимально.

– С выбранными значениями номеров деталей войти в таблицу P и получить значения столбцов Pnum, Pnam, Ci.

Приведённая ниже формула SQL точно описывает логику этой процедуры.

SELECT P.Pnum AS Номер,

P.Pnam AS Наименование,

P.Ci AS Город

FROM P

WHERE P.Pnum IN

(SELECT Pnum

FROM (SELECT Pnum, SUM(Qt) AS Sumqt

FROM SPJ

GROUP BY Pnum

) AS TQt

WHERE

NOT EXISTS

(SELECT *

FROM TQt AS Y

WHERE Y.Sumqt > TQt. Sumqt

)

);

Вспомогательная таблица будет построена подзапросом в предложении FROM подзапроса предиката IN. Ей будет присвоено локальное имя TQt.

TQt
	Pnum	Sumqt
	P1
	P7
	P2
	P9
	P5
	P6
	P8
	P4
	P3

Затем на каждой строке этой таблицы будет обрабатываться подзапрос в предикате NOT EXISTS предложения WHERE подзапроса предиката IN. В выходную таблицу подзапроса первого уровня вложения попадут значения Pnum из тех строк таблицы TQt, в которых значение суммарного объёма поставок Sumqt максимально. Вот результат, произведённый этим подзапросом:

Pnum

P5

P8

P6

Наконец, начнётся обработка запроса верхнего уровня. Полученное множество номеров деталей будет использовано для выбора строк из таблицы P.

Результат этих манипуляций таков:

Номер	Наименование	Город
P5	панель	Асино
P8	панель	Асино
P6	кинескоп	Яя

Другая формула запроса приведена ниже.

SELECT P.Pnum AS Номер,

P.Pnam AS Наименование,

P.Ci AS Город

FROM P

WHERE (

SELECT SUM(Qt)

FROM SPJ AS X

WHERE P.Pnum = X.Pnum

)

>= ALL

(

SELECT SUM(Qt)

FROM SPJ AS Y

GROUP BY Y.Pnum

);

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

Коррелированный подзапрос в левой части предиката для текущей строки таблицы Pвычисляет значение суммарного объёма поставок соответствующей детали. Это значение сравнивается с каждым значением множества. Если оказывается, что оно не меньше любого из них, то данные из текущей строки таблицы P заносятся в выходную таблицу запроса. Затем выбирается следующая строка таблицы и вновь исполняется коррелированный подзапрос.

Приведём ещё одну, возможно, наиболее наглядную формулу запроса:

SELECT P.Pnum AS Номер,

P.Pnam AS Наименование,

P.Ci AS Город

FROM P,

(

SELECT Pnum, SUM(Qt) AS Sumqt

FROM SPJ

GROUP BY Pnum

) AS TQt

WHERE P.Pnum = TQT.Pnum

AND Sumqt = (SELECT MAX(Sumqt) FROM TQT);

В логике этой формулы читатель разберётся самостоятельно. Заметим, что она единственная может быть легко модифицирована для такого запроса: «Получить номера, наименования и города размещения деталей, суммарные объёмы поставок которых максимальны, а также значение максимального объёма». Как? Почему так же нельзя изменить предыдущие формулы?

Как видно из приведённых примеров, механизм вложения запросов – это мощное выразительное средство. С его помощью можно представить запрос любой сложности в виде одного оператора SELECT. Все запросы, выразимые в терминах соединений, могут быть выражены и в терминах подзапросов. Обратное неверно. Например, только что обсуждавшийся запрос без использования подзапросов сформулировать невозможно. Почему?

6.2.13 Предложение ORDER BY

Предложение ORDER BY используется для сортировки выходной таблицы запроса. Оно всегда обрабатывается последним.

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

6.2.14 Операторы UNION, INTERSECT и EXCEPT

Эти операторы реализуют соответственно операции объединения, пересечения и разности РА [5, с. 73]. Они используют в качестве аргументов операторы SELECT без концевого ограничителя ‘; ’ и предложения ORDER BY в соответствии со следующим синтаксисом:

запрос_А { UNION | INTERSECT | EXCEPT }

[ ALL ] [ CORRESPONDING [ BY (имя_столбца.,..) ] ]

запрос_В;

Если не указан параметр CORRESPONDING, то запросы-операнды должны иметь эквивалентные целевые списки, то есть количество столбцов должно быть одинаковым, и столбцы с одинаковыми порядковыми номерами должны быть сравнимы. Совпадения имён соответственных столбцов не требуется.

Параметр CORRESPONDING используется, если операнды содержат одноимённые сравнимые столбцы. Их порядковые номера могут не совпадать, и каждый из операндов может содержать и другие столбцы. В результат операции в любом случае войдут только одноимённые сравнимые столбцы. Таким образом, CORRESPONDING выполняет SQL-проекцию операндов на эти столбцы перед выполнением соответствующей бинарной операции.

Если не указан параметр BY, то в результат операции войдут все одноимённые столбцы операндов в том порядке, в каком они расположены в первом. В противном случае результат будет содержать только перечисленные в списке столбцы в порядке следования имён.

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

Если m – число дубликатов некоторой строки S в таблице A, и n – число дубликатов той же строки в таблице B, то количество дубликатов в результате операции определяется следующими правилами:

Операция	Число дубликатов
TABLE АUNION ALL TABLE В[22]	m + n
TABLE АINTERSECT ALL TABLE В	MIN (m, n)
TABLE АEXCEPT ALL TABLE В	если m > n, то m – n, иначе 0

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

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

TABLE А UNION ALL TABLE В UNION TABLE C;

Если так:

(TABLE А UNION ALL TABLE В) UNION TABLE C;

то результат не будет содержать дубликатов строк, а если так:

TABLE А UNION ALL (TABLE В UNION TABLE C);

то в результат войдут все экземпляры строк, входящих в А. Если такие же строки есть в объединении B и C (не содержащем дубликатов!), то и они будут представлены.

Если результат табличного выражения, содержащего операторы UNION, INTERSECT и EXCEPT, должен быть упорядочен, то предложение ORDER BY следует указать непосредственно перед концевым ограничителем ‘; ’. Будет отсортирована таблица, произведённая выражением, а не последним оператором SELECT.

Пример 1. С помощью операции объединения можно реализовать внешнее соединение. Вернёмся к примеру 2 из п. 6.2.9. Следующее выражение эквивалентно левому внешнему соединению:

SELECT Snam, Jnam

FROM S, SPJ, J

WHERE S.Snum = SPJ.Snum

AND J.Jnum = SPJ.Jnum

UNION

SELECT Snam, NULL

FROM S

WHERE NOT EXISTS (

SELECT *

FROM SPJ

WHERE S.Snum = SPJ.Snum

);

Первый оператор SELECT произведёт следующую таблицу:

Snam	Jnam
Иван	дисплей
Иван	дисплей
Иван	процессор
Николай	мышь
Николай	принтер
Григорий	джойстик
Григорий	винчестер
Петр	процессор
Константин	джойстик
Иван	винчестер
Иван	винчестер
Сергей	клавиатура
Сергей	процессор
Сергей	клавиатура
Владимир	процессор
Владимир	клавиатура
Владимир	клавиатура
Владимир	клавиатура
Владимир	клавиатура

Второй оператор создаст таблицу, состоящую также из двух столбцов. Первый столбец будет содержать значения имён поставщиков, не выполнивших ни одной поставки, а второй (безымянный) будет заполнен значениями NULL во всех строках.[23]

Snam
Егор	NULL

Результат объединения содержит в единственном экземпляре каждую строку первой таблицы и единственную строку второй:

Владимир	клавиатура
Владимир	процессор
Григорий	винчестер
Григорий	джойстик
Егор	NULL
Иван	винчестер
Иван	процессор
Константин	джойстик
Николай	мышь
Николай	принтер
Петр	процессор
Иван	дисплей
Сергей	клавиатура
Сергей	процессор

Пример 2. Получить номера поставщиков, выполняющих поставки для изделий, производимых в Томске и Яе. Результат упорядочить по именам.

SELECT S.Snum, Snam

FROM S JOIN (J JOIN SPJ) USING (Snum)

WHERE J.Ci = 'Томск'

INTERSECT

SELECT S.Snum, Snam

FROM S JOIN (J JOIN SPJ) USING (Snum)

WHERE J.Ci = 'Яя'

ORDER BY 2;

S1	Иван
S8	Владимир

Попытайтесь сформулировать этот запрос без оператора INTERSECT.

Этим мы завершим обзор оператора SELECT. В заключение сделаем два замечания.

Во-первых, мы обсудили здесь возможности оператора очень бегло. Для того чтобы получить о нём более полное представление, необходимо решить задачи, предлагаемые в приложении Б. Для каждого запроса постарайтесь записать несколько различных формул.

Во-вторых, реальные СУБД поддерживают не все определения стандарта. Например, диалект SQL MS Access не допускает использования подзапросов в предложении FROM, поддерживает единственный вид встроенной операции соединения – соединение по условию – и только типы INNER, LEFT и RIGHT, не поддерживает операции INTERSECT и EXCEPT, использует определение предиката LIKE, отличное от стандартного, и имеет ещё ряд особенностей. Это следует иметь в виду, проверяя правильность своих формулировок в конкретной операционной среде.

Операторы обновления данных

Оператор SELECT, как мы видели, может использовать в качестве источника данных любую совокупность именованных таблиц. Существует единственное ограничение: ID, издавший оператор, должен иметь привилегии на просмотр всех таблиц источника. В отличие от этого операторы обновления данных всегда используют в качестве приёмника данных одну именованную таблицу – базовую или представление. Если приёмником является представление, то оно должно быть обновляемым (см. п. 6.4.3). В любом случае реально обновляются только базовые таблицы.

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

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

Рассмотрим теперь синтаксис операторов обновления.

Оператор INSERT в общем случае добавляет в указанную таблицу группу строк. Группа может быть либо результатом запроса, либо явно заданным списком строк – конструктором значений таблицы.[24] Результат запроса или строки конструктора могут содержать значения части столбцов обновляемой таблицы. В этом случае имена этих столбцов должны быть указаны списком имён столбцов.

Вот синтаксическая диаграмма оператора, соответствующая определению SQL2:

INSERT INTO имя_таблицы [ (имя_столбца.,..) ]

запрос

| конструктор_значений_таблицы

| { DEFAULT VALUES };

Здесь имя_столбца – имя столбца обновляемой таблицы. Список имён можно не указывать, если добавляемые строки содержат значения для всех столбцов таблицы. Столбцам, не вошедшим в список, в новых строках будут присвоены значения по умолчанию, если они определены, либо NULL -значения. Если для какого-либо столбца это невозможно (например, столбец определён как NOT NULL, а значение по умолчанию не задано), операция будет прервана.

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

Пример 1. Пусть в схеме определена временная таблица TQt, в которую приложение в процессе исполнения загружает данные об общих объёмах поставок деталей. Таблица содержит два столбца: Pnum – номер детали и Sumqt – суммарный объём поставок. Следующий оператор заполнит эту таблицу строками, содержащими обработанные данные из таблицы SPJ:

INSERT INTO TQt

SELECT Pnum, SUM(Qt)

FROM SPJ

GROUP BY Pnum;

Вводимые строки могут быть заданы явно с помощью следующей конструкции:

конструктор_значений_таблицы::=

VALUES конструктор_значений_строки., ..

конструктор_значений_строки::=

элемент_конструктора

| ( элемент_конструктора., .. )

| подзапрос_строки

элемент_конструктора::=

выражение_для вычисления_значения

| NULL | DEFAULT

Простейшим выражением является литерал. Однако можно использовать и скалярные подзапросы, и переменные, передаваемые приложениями или системные.

Пример 2. Добавить в таблицу S строку (‘S12’, ‘Алексей’, NULL, ‘Тайга’).

INSERT INTO S

VALUES (‘S12’, ‘Алексей’, NULL, ‘Тайга’);

Исполняя этот оператор, система проверит, существует ли значение ‘S12’ в текущем множестве значений столбца Snum и допустимо ли значение NULL для столбца St.

Точно такие же изменения в таблицу внесёт оператор

INSERT INTO S (Snam, Ci, Snum)

VALUES (‘Алексей’, ‘Тайга’, ‘S12’);

Обратите внимание на то, что порядок следования имён в списке не совпадает с порядком столбцов в схеме таблицы, но порядок следования значений в конструкторе значений строки совпадает с порядком следования имён в списке.

Пример 3. Внести в таблицу J строки:

('J11’, ‘принтер’, ‘Тайга’),

('J12’, ‘плоттер’, ‘Яя’),

('J13’, ‘сканер’, NULL)

INSERT INTO J

VALUES ('J12’, ‘плоттер’, ‘Яя’),

('J11’, ‘принтер’, ‘Тайга’),

('J13’, ‘сканер’, NULL);

Оператор UPDATE изменяет значения одного или нескольких столбцов в подмножестве строк базовой таблицы. Вот его синтаксическая диаграмма:

UPDATE имя_таблицы

SET { имя_столбца =

выражение_для_вычисления_значения

| NULL

| DEFAULT }}.,..

[ WHERE предикат ];

Здесь выражение может содержать литералы, имена переменных, ссылки на любые столбцы обновляемой таблицы, скалярные подзапросы. Тип выражения должен соответствовать типу обновляемого столбца. В качестве альтернативы выражению можно явно указывать значение NULL или «значение по умолчанию» DEFAULT, заданное при определении столбца (см. п. 6.3.5).

Если оператор не содержит предложения WHERE, то указанные в предложении SET значения присваиваются столбцам во всех строках таблицы. Предложение WHERE ограничивает действие оператора подмножеством строк, на которых предикат принимает значение TRUE.

Пример 4. Изменить единицу измерения веса деталей с грамма на килограмм.

UPDATE P

SET We=We/1000;

Будут изменены значения столбца We во всех строках таблицы P.

Пример 5. Изменить название производимого в Яе изделия ‘дисплей’ на ‘«Электрон»’.

UPDATE J

SET Jnam=‘«Электрон»’

WHERE Jnam=‘дисплей’ AND Ci = ‘Яя’;

Пример 6. Увеличить вдвое статусы тех поставщиков, чьи суммарные объёмы поставок детали P1 превосходят 2000.

UPDATE S

SET St=St*2

WHERE 2000 < SELECT SUM(Qt)

FROM SPJ

WHERE SP