- •5. Сложные запросы
- •5.1. Теоретико-множественные операции
- •5.1.1. Декартово произведение наборов записей
- •5.1.2. Объединение наборов записей
- •5.1.3. Пересечение наборов записей
- •5.1.4. Вычитание наборов записей
- •5.2. Внешние соединения
- •5.2.1. Левое внешнее соединение
- •5.2.2. Правое внешнее соединение
- •5.2.3. Полное внешнее соединение
- •5.3. Подзапросы
- •5.3.1. Простые подзапросы
- •5.3.2. Связанные подзапросы
- •5.4. Представления
- •Лабораторная работа 5
5.4. Представления
При работе с SQL нередко возникают ситуации, когда один и тот же запрос приходится использовать повторно. В подобных ситуациях обычно используются представления (views). Представления могут строиться на основе как простых и стандартных запросов к одной таблице, так и чрезвычайно сложных запросов, в которых задействовано несколько таблиц.
Представление можно рассматривать как хранимый запрос, на основе которого создается объект базы данных. Этот объект очень похож на таблицу, но в его содержимом динамически отражается состояние только тех записей, которые были заданы при создании представления. Представления не являются физическими объектами хранения данных. Данные в представлениях, подобно в ответах на запрос SELECT, просто выбираются из таблиц базы данных, т.е. представляются в том или ином виде. В действительности за представлением стоит скрытый SQL-запрос. Работать с представлением можно как с обычной таблицей. Однако любой запрос к представлению в действительности инициирует скрытый запрос, который комбинируется с пользовательским.
Представления, будучи созданы, могут быть доступны многим пользователям и существуют в базе данных до тех пор, пока не будут принудительно удалены.
Представления создаются командой
CREATE VIEW имяПредставления AS
запросSELECT;
Как и обычной таблице базы данных, представлению присваивается имя, которое не должно совпадать ни с одним именем таблиц. За ключевым словом AS следует SQL-выражение запроса на выборку данных.
Пусть требуется получить для каждого наименования книги количество ее покупок и дату последней покупки:
SELECT title, count(*) AS num_shipped,
max(ship_date) AS last_date
FROM shipments JOIN editions USING (isbn)
JOIN books ON (book_id = books.id)
GROUP BY title
ORDER BY num_shipped DESC;
Запрос получается слишком громоздким, и часто вводить его вручную нежелательно. Создадим на базе этого запроса представление:
CREATE VIEW recent_shipments AS
SELECT title, count(*) AS num_shipped,
max(ship_date) AS last_date
FROM shipments JOIN editions USING (isbn)
JOIN books ON (book_id = books.id)
GROUP BY title
ORDER BY num_shipped DESC;
Представления значительно упрощают получение нужных данных. Вместо того, чтобы вводить длинный запрос, достаточно ввести простую команду SELECT:
SELECT *
FROM recent_shipments;
Получим названия книг, которые были куплены в наибольшем количестве:
SELECT title
FROM recent_shipments
WHERE num_shipped = (SELECT max(num_shipped)
FROM recent_shipments)
ORDER BY title;
Некоторые современные СУБД поддерживают операции изменения содержимого представлений – вставку, изменение и удаление записей. Однако это возможно лишь в том достаточно редком случае, когда имеется однозначное соответствие между столбцами представления и столбцами таблицы базы данных. Для этого в операторе SELECT представления не должно использоваться более одной таблицы, вычисляемых выражений, группировки, ключевого слова DISTINCT и т.п.
В PostgreSQL при попытке вызова команд INSERT, UPDATE или DELETE для представления происходит ошибка.
Представления удаляются из базы данных командой
DROP VIEW имяПредставления;
При удалении представления данные сохраняются без изменения. Теряется только запрос, на котором основано представление.