Язык SQL — Представления. Создание, изменение, удаление представлений

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

Таблицы, с которыми мы имели дело раньше, называются — базовыми таблицами. Эти таблицы определяют структуру базы данных и содержат данные, которые постоянно находятся на устройстве хранения информации.

Однако, в языке SQL имеется другой вид таблиц: — представления (VIEW, обзор, взгляд).

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

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

Представление – это хранимый запрос, создаваемый на основе команды SELECT.

При создании представление получает имя и его определение сохраняется в базе данных.

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

Запрос, определяющий представление, выполняется тогда, когда к представлению происходит обращение с другим запросом, например, SELECT, UPDATE и т.д.

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

Назначение представлений:

1. Хранение сложных запросов.
2. Представление данных в виде, удобном пользователю. Они показывают каждому пользователю структуру хранимых данных в наиболее подходящем для него виде.
3. С помощью представлений можно ограничить доступ к данным, разрешая пользователям видеть только некоторые из строк и столбцов таблицы.

2. Создание представлений

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

CREATE VIEW имя_представления [(столбец1 , столбец2, . . . ) ]

[WITH ENCRYPTION ]

AS 

команда SELECT

[WITH CHECK OPTION]

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

Рассмотрим параметры команды CREATE VIEW:

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

Имена можно указать в списке имён после имени представления, а можно указать псевдонимы для отдельных столбцов результата (например, select Naimen AS ‘Название товара’). Если не указывать имена столбцов, то в представлении они получат имена, которые указаны в команде SELECT.

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

AS — ключевое слово, показывающее начало определения представления.

Команда SELECT — команда, определяющая собственно представление.

WITH CHECK OPTION — предписывает серверу исполнять проверку при модификации данных на соответствие условиям на значения (ограничения), которые были определены для таблиц, лежащих в основе создаваемого представления. Включение в команду этого параметра гарантирует, что сделанные изменения будут отображены в представлении. Если пользователь пытается выполнить изменения, приводящие к исключению строки из представления, при заданном аргументе WITH CHECK OPTION сервер выдаст сообщение об ошибке и все изменения будут отклонены.

Рассмотрим примеры использования представлений.

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

Пример: создадим представление, которое позволит просматривать поставщиков из Владимирской обл.:

CREATE VIEW Поставщики_Владимира AS

SELECT *

FROM Поставщики

WHERE Регион = 'Владимир.обл.'

Теперь в базе данных хранится представление, называемое Поставщики_Владимира и к нему можно обращаться с помощью запросов так же, как и к любой другой таблице базы данных, например, выбрать из представления Поставщики_Владимира поставщиков, название которых начинается на букву ‘В’:

SELECT *

FROM Поставщики_Владимира

WHERE Название LIKE 'В%'

Результат выполнения запроса приведен на рис. 6.

Рис. 6. Результат запроса, источником данных которого является представление Поставщики_Владимира

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

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

CREATE VIEW Поставщики

AS

SELECT Название, Регион, Код_постав

FROM Поставщик

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

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

Групповые представления

Запрос, определяющий представление, может содержать предложение GROUP BY.

Пример: каждый день необходимо следить за количеством поставок, количеством поставщиков, средней суммой поставок, общей суммой поставок за каждый день. Чем конструировать каждый раз сложный запрос, можно просто создать следующее представление:

CREATE VIEW Итоги_за_день (Дата_поставки, Количество_ поставок, Количество_ поставщиков, Средняя_сумма, Общая_сумма)

AS

SELECT Дата, COUNT(Документ.Ном_док), COUNT(DISTINCT Код_постав), AVG(Цена*Количество), SUM(Цена*Количество)

FROM Документ, Приход

WHERE Документ.Ном_док = Приход.Ном_док

GROUP BY Дата

Теперь вы сможете получить данные из представления Итоги_за_день, относящиеся, например, к 17.05.2021, с помощью простого запроса:

SELECT *

FROM Итоги_за_день

WHERE Дата_поставки = '17.05.2021'

Результат выполнения запроса приведен на рис. 7.

Рис. 7. Результат запроса, источником данных которого является представление Итоги_за_день

Представления и соединения

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

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

CREATE VIEW Поступление AS

SELECT Приход.Ном_док, Тип, Наименование, Название

FROM Приход, Документ, Поставщик, Склад

WHERE Приход.Ном_док = Документ.Ном_док

AND Документ.Код_постав = Поставщик.Код_постав

AND Приход.Ном_ном = Склад.Ном_ном

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

Например, чтобы увидеть все поставки Комаровой Н.В., требуется ввести следующий запрос:

SELECT *

FROM Поступление

WHERE Название = 'Комарова Н.В.'

Результат выполнения запроса приведен на рис. 8.

Рис. 8. Результат запроса, источником данных которого является представление Поступление

Представления и подзапросы

Представления могут также использовать и подзапросы. Предположим, компания даст премию поставщикам, поставляющим самые дорогие товары в 2020г. Вы можете проследить эту информацию с помощью представления:

CREATE VIEW Премия AS

SELECT A.Код_постав, Дата, Цена, Название FROM Документ A, Приход B, Поставщик C

WHERE A.Код_постав = C.Код_постав

AND A.Ном_док = B.Ном_док

AND Цена = (SELECT MAX(Цена)

FROM Приход, Документ WHERE YEAR (Дата) = 2020

AND Документ.Ном_док = Приход.Ном_док)

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

SELECT *

FROM Премия

Результат выполнения запроса приведен на рис. 9.

Рис. 9. Результат запроса, источником данных которого является представление Премия

3. Изменение значений базовой таблицы с помощью представлений

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

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

Представление является модифицируемым, если оно удовлетворяет следующим требованиям:

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

Перечисленные требования базируются на принципе, запомнить который, пожалуй, легче, чем сами требования: представление разрешается обновлять в том случае, если система управления базами данных может для каждой строки представления найти исходную строку в исходной таблице, а для каждого обновляемого столбца представления — исходный столбец в исходной таблице. Если представление соответствует этим требованиям, то к нему могут применяться команды INSERT, UPDATE, DELETE.

Чтобы лучше понять смысл этих ограничений, рассмотрим примеры модифицируемых представлений и представлений только для чтение:

CREATE VIEW Дата_заказа (Дата, Количество_заказов)

AS

SELECT Дата, COUNT (*)

FROM Заказы

GROUP BY Дата

Это представление только для чтения из-за присутствия в нем агрегатной функции и GROUP BY.

CREATE VIEW Москва

AS

SELECT *

FROM Поставщик

WHERE Регион = 'Моск.обл.'

А это — представление модифицируемое.

CREATE VIEW Влад ( Код, Название_товара, Цена_с_НДС)

AS

SELECT Ном_ном, Наименование, Цена * 1.2

FROM Склад

Это представление только для чтение из-за выражения Цена * 1.2.

Модифицируемые и немодифицируемые представления создаются для различных целей.

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

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

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

Добавление строк

Рассмотрим пример использования представления в команде INSERT для добавления строк в таблицу, на которой это представление определено.

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

CREATE VIEW N1

AS

SELECT *

FROM Поставщик

WHERE Рейтинг = 80

Это представление модифицируемое. Оно просто ограничивает доступ к определенным строкам в таблице.

Вставим новую строку:

INSERT INTO N1

VALUES (40,'Маслов','Владимир.обл.', 50)

Это допустимая команда INSERT. Строка будет вставлена с помощью представления N1 в таблицу Поставщик (рис. 10).

Рис. 10. Результат вставки строки в таблицу Поставщик

Однако когда она появится там, она исчезнет из представления N1, поскольку значение рейтинга не равно 80 (рис. 11).

Рис. 11. Данные представления N1

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

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

Если в представление N1 добавить опцию WITH CHECK OPTION, то определение представления будет выглядеть следующим образом:

CREATE VIEW N2

AS

SELECT *

FROM Поставщик

WHERE Рейтинг = 50

WITH CHECK OPTION

Теперь, если выполнить запрос на вставку новой записи

INSERT INTO N3

VALUES (45,'Кокорин','Московск. обл.', 70), будет выдано сообщение (рис. 12).

Рис. 12. Сообщение о невозможности выполнить операцию вставки

4. Удаление представлений

Для удаления представлений из базы данных можно воспользоваться командой DROP VIEW языка Transact-SQL. Синтаксис команды DROP VIEW:

DROP VIEW имя__представления

В приведенном ниже примере удаляется представление с именем view1:

DROP VIEW view1

Удаление представления не влияет на таблицы, лежащие в его основе. Определение представления просто удаляется из базы данных.