- •Часть 2. Реляционная модель.
- •Глава 4. Реляционные объекты данных: домены и отношения.
- •4.1. Вводный пример
- •4.2. Домены
- •4.3. Отношения
- •4.4. Виды отношений
- •4.5. Отношения и предикаты
- •4.6. Реляционные базы данных
- •4.7. Резюме
- •Глава 8.
- •8.1. Введение
- •8.2. Определение данных
- •8.3. Обработка данных: операции выборки
- •8.3.1. Получить цвета и города для деталей "не из Парижа" с весом, большим десяти
- •8.3.2. Для всех деталей получить номер детали и ее вес в граммах
- •8.3.3. Получить полную информацию обо всех поставщиках
- •8.3.4. Получить информацию обо всех парах поставщиков и деталей, совмещенных в одном городе
- •8.3.5. Получить все пары имен городов, таких что поставщик, находящийся в первом городе, поставляет деталь, хранящуюся во втором городе
- •8.3.6. Получить все пары номеров поставщиков, таких что оба поставщика в каждой паре размещаются в одном и том же городе
- •8.3.7. Получить общее число поставщиков
- •8.3.8. Получить максимальное и минимальное количество для детали р
- •8.3.9. Для каждой поставляемой детали получить номер детали и общее количество поставки
- •8.3.10. Получить номера для всех деталей, поставляемых более чем одним поставщиком
- •8.3.11. Получить имена поставщиков, поставляющих деталь р2
- •8.3.12. Получить имена поставщиков, поставляющих по крайней мере одну красную деталь
- •8.3.13. Получить номера поставщиков, статус которых меньше текущего максимального статуса в таблице s
- •8.3.14. Получить имена поставщиков, поставляющих деталь р2
- •8.3.15. Получить имена поставщиков, которые не поставляют деталь р2
- •8.3.16. Получить имена поставщиков, поставляющих все детали
- •8.3.17. Получить номера деталей, которые или весят более 16 фунтов, или поставляются поставщиком s2, или и то и другое
- •8.4. Обработка данных: операции обновления
- •Table-term
- •I join-table-expression
- •8.6. Условные выражения
- •8.7. Скалярные выражения
- •8.8. Встроенный sql
- •8.8.1. Единичный select. Получить статус и город для поставщика, чей номер поставки задан базовой переменной givens#
- •8.8.2. Insert. Вставить новую деталь в таблицу р (номер детали, ее назв. И вес определены базовыми переменными р#, pname, pwt соответственно, цвет и город неизвестны)
- •8.8.3, Update. Увеличить статус всех поставщиков из Лондона на значение, определенное базовой переменной raise
- •8.8.4. Delete. Удалить все поставки для поставщиков из города,
- •8.9. Резюме
Table-term
nonjoin- table-term
: := nonjoin-table-primary
| table-term INTERSECT [ ALL ]
[ CORRESPONDING [ BY ( column-commalist ) ] ]
table-primary
table-term
: := nonjoin-table-term
I join-table-expression
table -primary
: := nonjoin-table-primary
I join-table-expression
nonjoin-table -primary
: := TABLE table
| table-constructor
| select-expression
I ( nonjoin-table-expression )
table-constructor
: := VALUES row-constructor-commalist
row-constructor
: := scalar-expression
| ( scalar-expression-commalist )
I ( table-expression )
select-expression
: := SELECT [ ALL | DISTINCT ] select-item-commalist
FROM table-reference-commalist
[ WHERE conditional-expression ] [ GROUP BY column-commalist ]
[ HAVING conditional-expression ]
select-item
: := scalar-expression [ [ AS ] column ]
I [ range-variable . ] *
Рис. 8.1. BNF-грамматика для табличных выраженийSQL
• Если элемент выборки включает ссылку на итоговую функцию и выражение выборки не включает инструкцию group by (см. ниже), то ни один элемент выборки в инструкции select не может включать никаких ссылок на столбец таблицы T1, кроме такой, которая является ссылкой на аргумент (или часть аргумента) итоговой функции.
Случай 2. Элемент выборки имеет следующий вид:
[ range-variable . ] *
• Если уточнение (range-variable) опущено (т.е. элемент выборки представлен как неуточненный символ "*"), то этот элемент выборки должен быть только элементом выборки в инструкции select. Эта форма является сокращением для списка всех столбцов таблицы Т1 в порядке слева направо.
• Если уточнение включено (т.е. элемент выборки состоит из символа "*", уточненного именем переменной области значений R, записывается как "R.*" ), то этот элемент выборки представляет список всех столбцов таблицы, соответствующей переменной области значений R, в порядке слева направо. (Напомним, что имя таблицы может и часто будет использоваться как явная переменная области значений. Таким образом, элемент выборки часто будет иметь форму "T. *", а не "R. *".)
Инструкция FROM
Инструкция from записывается в виде
FROM tabIe-reference-commaIist
Список ссылок на таблицы (table-reference-commalist) не должен быть пустым. Пусть вычисление указанных табличных ссылок дает таблицы А, В,..., С. Тогда результат вычисления инструкции from будет таблицей, эквивалентной декартову произведению таблиц А, В,..., С.
Замечание. Декартово произведение отдельной таблицы Т определяется как эквивалентное Т, т.е. оно, конечно, допустимо для инструкции from, содержащей просто отдельную табличную ссылку.
Инструкция WHERE
Инструкция where записывается в виде
WHERE conditional-expression
Пусть Т— результат вычисления предыдущей инструкции from. Тогда результатом инструкции where будет таблица, производная от Т и исключающая все строки, для которых вычисление условного выражения (conditional-expression) не дает истину. Если инструкция where опущена, результатом будет просто Т.
Инструкция GROUP BY
Инструкция group by записывается в виде
GROUP BY column-commalist
Список столбцов (column-commalist) не должен быть пустым. Пусть Т— результат вычисления предыдущих инструкций from и where (если они использовались). Каждый столбец, указанный в списке инструкции group by, должен быть представлен (не обязательно) уточненным именем столбца таблицы Т. А результатом этой инструкции будет сгруппированная таблица, т.е. набор групп строк, производных от таблицы Т с помощью концептуальной перегруппировки таблицы Т в минимальное количество таких групп, что в пределах одной группы все строки имеют одинаковое значение для комбинации столбцов, указанных в инструкции group by. Поэтому заметьте, что результат — это не совсем таблица. Однако инструкция group by никогда не применяется без соответствующей инструкции select, которая преобразует этот промежуточный результат в правильную таблицу, поэтому нет ничего плохого в том, что временно мы отклонились от точной табличной структуры.
Если выражение выборки включает инструкцию group by, то есть ограничения на форму, которую может принимать инструкция select. Точнее, каждый элемент выборки в инструкции select (включая любой такой, который подразумевается по сокращению "*") должен быть однозначным в группе. Итак, каждый элемент выборки не должен включать никаких ссылок на какой-либо столбец таблицы T, не указанной в инструкции group by, кроме ссылок на аргументы или часть аргумента одной из итоговых функций — count, sum, avg, max или min, в результате выполнения которых некоторый набор скалярных значений группы сводится к единственному такому значению.
Инструкция HAVING
Инструкция having записывается в виде
HAVING conditional-expression
Пусть G (сгруппированная таблица) — результат выполнения предыдущей инструкции frcm, инструкции where (если она присутствует) и инструкции group by (если она есть). Если инструкции group by нет, то в качестве G берется результат выполнения предыдущей инструкции from или инструкции where и рассматривается как сгруппированная таблица, состоящая только из одной группы(///////); иначе говоря, в этом случае есть неявная концептуальная инструкция group by, которая указывает, что группируемых столбцов нет совсем. Результат инструкции having— это сгруппированная таблица, производная от G и исключающая все группы, для которых условное выражение не является истиной.
' Так сказано в стандарте SQL, хотя логичнее было бы сказать — не более одной группы, поскольку если с помощью инструкций FROM и WHERE будет вычислена пустая таблица, то не будет ни одной группы.
Замечание 1. Если инструкция having опущена, а инструкция group by присутствует, то результатом будет просто G. Если обе инструкции having и group by опущены, результатом будет "правильная", т.е. не сгруппированная, таблица T, вычисленная в соответствии с инструкциями where и from.
Замечание 2. Скалярные выражения инструкции having в каждой группе должны быть однозначными (так же как и скалярные выражения инструкции select, если присутствует инструкция group by, как обсуждалось выше).
Замечание 3. Стоит упомянуть, что инструкция having полностью излишняя, т.е. для любого выражения выборки, в котором используется эта инструкция, существует семантически эквивалентное выражение выборки, в котором она не используется (упражнение для читателя!).
Обширный пример
В заключение приведем довольно сложный пример, иллюстрирующий некоторые (но далеко не все) обсуждаемые выше вопросы. Рассмотрим запрос:
Для всех красных и голубых деталей, таких что поставляемое итоговое количество определенных деталей больше 350 (исключая из итога все поставки, в которых количество не больше 200), получить номер детали, вес в граммах, цвет и максимальное поставляемое количество этих деталей.
SELECT P.P#,
'Weight in grams = ' AS TEXT1,
P.WEIGHT * 454 AS GMWT,
P.COLOR,
'Max quantity = ' AS TEXT2,
MAX ( SP.OTY ) AS MQY FROM P, SP
WHERE P.P# = SP.P#
AND ( P.COLOR = 'Red' OR P.COLOR = 'Blue’ )
AND SP.OTY > 200
GROUP BY P.P#, P.WEIGHT, P.COLOR
HAVING SUM ( SP.QTY ) > 350 ;
Пояснения. Следует отметить, что (как уже пояснялось) инструкции выражения выборки концептуально выполняются в том порядке, в котором они написаны, с единственным исключением самой инструкции select, которая выполняется последней. Поэтому можно представить результат, построенный следующим образом:
1. FROM: вычисление инструкции from дает новую таблицу, которая является декартовым произведением таблиц Р и SP.
2. WHERE: результат шага 1 сокращается путем исключения всех строк, которые не удовлетворяют условию инструкции where. В данном примере исключаются строки, не удовлетворяющие условному выражению
P.P# = SP.P# AND
( P.COLOR = 'Red' OR P.COLOR = 'Blue' ) AND
SP.QTY > 200
3. GROUP BY: результат шага 2 группируется по значениям столбца или столбцов, перечисленных в инструкции group by. В данном примере это столбцы Р.Р#, P.WEIGHT и P.COLOR.
Замечание. Теоретически здесь было бы достаточно одного столбца Р.Р# в качестве группируемого, поскольку P.WEIGHT и P.COLOR однозначно определяются по номеру детали. Однако в SQL не учитывается этот момент, и если в инструкции group by опустить P.WEIGHT и P.COLOR, то, поскольку они упоминаются в инструкции select, будет сгенерирована ошибка.
4. HAVING: группы, не удовлетворяющие условию
SUM ( SP.QTY ) > 350,
из результата шага 3 исключаются.
5. SELECT: каждая группа в результате шага 4 генерирует отдельную строку результата следующим образом. Во-первых, из группы извлекаются номер детали, вес, цвет и максимальное количество поставки. Во-вторых, вес преобразуется в граммы. И, в-третьих, две литеральные строки "Weight in grams =" (вес в граммах) и "Max quantity ==" (максимальное количество) ставятся на соответствующее место в строке. Кстати, "соответствующее место в строке" предполагает, что столбцы таблицы в SQL расположены в порядке слева направо. Литеральные строки не имели бы смысла, если бы они не располагались в "соответствующих местах".
Окончательный результат выглядит следующим образом:
P# |
TEXT1 |
GMWT |
COLOR |
TEXT2 |
MQY |
P1 P2 P3 |
Weight in grams= Weight in grams= Weight in grams= |
5448 5448 7718 |
Red Blue Blue |
Max quantity= Max quantity= Max quantity= |
300 400 400 |
И, наконец, необходимо осознать, что описанный алгоритм представляет только концептуальное объяснение того, как вычисляется выражение select. Алгоритм, конечно, верный, в том смысле, что он обеспечивает правильный результат вычисления. Однако на практике он весьма неэффективен. Например, вычислять на первом шаге декартово произведение просто неуместно. Соображения, подобные этому, как раз и являются той причиной, по которой для реляционных систем требуется оптимизатор. Действительно, задача оптимизатора в системах SQL может быть охарактеризована как нахождение процедуры реализации, которая будет вырабатывать тот же результат, что и описанный выше концептуальный алгоритм, но более эффективно (об этом речь идет далее в книге).