Использование ключевых слов ANY и ALL

Ключевые слова ANY и ALL могут использоваться с подзапросами, возвращающими один столбец чисел.

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

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

Если в результате выполнения подзапроса получено пустое значение, то для ключевого слова ALL условие сравнения будет считаться выполненным, а для ключевого слова ANY — невыполненным. Ключевое слово SOME является синонимом слова ANY.

Пример 7.14. Определить клиентов, совершивших сделки с максимальным количеством товара (эквивалентно запросу 7.3.)

SELECT Клиент.Фамилия, Сдeлка.Koличecтвo

FROM Клиент INNER JOIN Сделка

ON Kлиeнт.KoдKлиeнта = Сделка.КодКлиента

WHERE Сдeлка.Koличecтвo> =ALL (SELECT Количество FROM Сделка)

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

Пример 7.15. Найти фирму, купившую товаров на сумму, превышающую 10000 грн.

SELECT Клиент.Фирма,

Sum(Тoвар.Цeна*Сдeлка.Koличecтвo) AS Общ_Стоимоеть

FROM Товар INNER JOIN

(клиент INNER JOIN Сделка

ON Клиент.КодКлиента = Сделка.КодКлиента)

ON Товар.КодТовара = Сделка.КодТовара

GROUP BY Kлиeнт.Фмрма

HAVING Sum(Тoвар.Цeна*Сдeлка.Koличecтвo)> 10000

Добавим в запрос подзапрос.

Пример 7.16. Найти фирму, которая приобрела товаров на самую большую сумму.

SELECT Киент.Фирма,

Sum(Тoвар.Цeна*Сдeлка.Koличecтвo) AS Общ_cтoммocть

FROM Товар INNER JOIN

(клиент INNER JOIN Сделка

ON Клиент.КодКлиента = Сделка.КодКлиента)

ON Тoвар.KoдТoвара = Сдeлка.KoдТoвара

GROUP BY Клиент.Фирма

HAVING Sum(Тoвар.Цeна*Сдeлка.Koлмчecтвo)> =

ALL(SELECT Sum(Тoвар.Цeна*Сдeлка.Koлмчecтвo)

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара = Сделка.КодТовара

GROUP BY Сдeлка.KoдKлмeнта)

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

Пример 7.17. Найти фирмы, в сделках которых количество товара превышает такой же показатель хотя бы в одной сделке клиентов из Донецка.

SELECT Клиент.Фирма, Сдeлка.Koличecтвo

FROM Клиент INNER JOIN Сделка

ON Клиент.КодКиента = Сделка.КодКлиента

WHERE Сдeлка.Koличecтвo> ANY(SELECT Сделка.Количество

FROM Клиент INNER JOIN

Сделка ON Клиент.КодКлиента = Сделка.КодКлиента

WHERE Kлиeнт.ГoрoдKлиeнта =" Донецк" )

Использование операций EXISTS и NOT EXISTS

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

Пример 7.18. Определить список имеющихся на складе товаров (запрос эквивалентен примеру 7.7).

SELECT Название

FROM Товар

WHERE EXISTS (SELECT КодТовара FROM Склад

WHERE Товар.КодТовара = Склад.КодТовара)

Пример 7.19. Определить список отсутствующих на складе товаров (запрос эквивалентен примеру 7.8).

SELECT Название

FROM Товар

WHERE NOT EXISTS (SELECT КодТовара FROM Склад

WHERE Товар.КодТовара = Склад.КодТовара)

Лекция 8 «Представления»

План

1 Определение представления

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

Определение представления

Представления, или просмотры (VIEW) представляют собой временные, производные (иначе – виртуальные) таблицы и являются объектами базы данных, информация в которых не хранится постоянно, как в базовых таблицах, а формируется динамически при обращении к ним. Обычные таблицы относятся к базовым, т.е. содержащим данные и постоянно находящимся на устройстве хранения информации. Представление не может существовать само по себе, а определяется только в терминах одной или нескольких таблиц. Применение представлений позволяет разработчику базы данных обеспечить каждому пользователю или группе пользователей наиболее подходящие способы работы с данными, что решает проблему простоты их использования и безопасности. Содержимое представлений выбирается из других таблиц с помощью выполнения запроса, причем при изменении значений в таблицах данные в представлении автоматически меняются. Представление — это фактически тот же запрос, который выполняется всякий раз при участии в какой-либо команде. Результат выполнения этого запроса в каждый момент времени становится содержанием представления. У пользователя создается впечатление, что он работает с настоящей, реально существующей таблицей.

У СУБД есть две возможности реализации представлений. Если его определение простое, то система формирует каждую запись представления по мере необходимости, постепенно считывая исходные данные из базовых таблиц. В случае сложного определения СУБД приходится сначала выполнить такую операцию, как материализация представления, т.е. сохранить информацию, из которой состоит представление, во временной таблице. Затем система приступает к выполнению пользовательской команды и формированию ее результатов, после чего временная таблица удаляется.

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

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

Создания и изменения представлений представлены следующей командой:

{ CREATE | ALTER}

VIEW имя_просмотра [(имя_столбца [,...n])]

[WITH ENCRYPTION]

AS SELECT_onepaTop

[WITH CHECK OPTION]

Рассмотрим назначение основных параметров.

По умолчанию имена столбцов в представлении соответствуют именам столбцов в исходных таблицах. Явное указание имени столбца требуется для вычисляемых столбцов или при объединении нескольких таблиц, имеющих столбцы с одинаковыми именами. Имена столбцов перечисляются через запятую, в соответствии с порядком их следования в представлении.

Параметр WITH ENCRYPTION предписывает серверу шифровать SQL-код запроса, что гарантирует невозможность его несанкционированного просмотра и использования. Если при определении представления необходимо скрыть имена исходных таблиц и столбцов, а также алгоритм объединения данных, необходимо применить этот аргумент.

Параметр WITH CHECK OPTION предписывает серверу исполнять проверку изменений, производимых через представление, на соответствие критериям, определенным в операторе SELECT. Это означает, что не допускается выполнение изменений, которые приведут к исчезновению строки из представления. Такое случается, если для представления установлен горизонтальный фильтр и изменение данных приводит к несоответствию строки установленным фильтрам. Использование аргумента WITH CHECK OPTION гарантирует, что сделанные изменения будут отображены в представлении. Если пользователь пытается выполнить изменения, приводящие к исключению строки из представления, при заданном аргументе WITH CHECK OPTION сервер выдаст сообщение об ошибке и все изменения будут отклонены.

Пример 8.1. Показать в представлении клиентов из Москвы.

Создание представления:

CREATE VIEW viewl

SELECT КодКлиента, Фамилия, ГородКлиента

FROM Клиент

WHERE ГородКлиента=" Москва"

Выборка данных из представления:

SELECT * FROM viewl

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

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

Так, в примере 8.1 представление просто ограничивает доступ пользователя к данным таблицы Клиент, позволяя видеть только часть значений.

Выполним команду:

INSERT INTO viewl VALUES (12, 'Петров', 'Самара')

Это допустимая команда в представлении, и строка будет добавлена с помощью представления viewl в таблицу Клиент. Однако, когда информация будет добавлена, строка исчезнет из представления, поскольку название города отлично от Москвы. Иногда такой подход может стать проблемой, т.к. данные уже находятся в таблице, но пользователь их не видит и не в состоянии выполнить их удаление или модификацию. Для исключения подобных моментов служит WITH CHECK OPTION в определении представления. Фраза размещается в определении представления, и все команды модификации будут подвергаться проверке.

Пример 8.2. Создание представления с проверкой команд модификации.

ALTER VIEW viewl

SELECT КодКлиента, Фамилия, ГородКлиента

FROM Клиенет

WHERE ГородКлиента='Москва' WITH CHECK OPTION

Для такого представления вышеупомянутая вставка значений будет отклонена системой.

Таким образом, представление может изменяться командами модификации DML, но фактически модификация воздействует не на само представление, а на базовую таблицу.

Представление удаляется командой:

DROP VIEW имя_просмотра [,...n]

⇐ Предыдущая 1 2 34