Представления и табличные объекты. Что такое представления VIEWS в базах данных? И зачем они нужны

Представление – это виртуальная таблица. В действительности представление – всего лишь результат выполнения оператора SELECT, который хранится в структуре памяти, напоминающей SQL таблицу, например, Oracle . Для тех, кто работает с представлением, манипулирование его данными ничем не отличается от манипулирования данными таблицы. В некоторых случаях пользователь может вводить данные в представление, как если бы оно было таблицей. Работая с представлением нужно помнить, что:

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

Представление создается с помощью команды . После создания представления становятся частью схемы создавшего их пользователя. Переназначить их другому пользователю можно тогда, когда у него имеется системная привилегия CREATE ANY VIEW .

Синтаксис команды CREATE VIEW Oracle

Синтаксис команды Create View

Основные ключевые слова и параметры CREATE VIEW Oracle :
OR REPLACE, FORCE, NOFORCE, Sсhema, View , Alias, AS subquery, WITH CHECK OPTION, Constraint

OR REPLACE — пересоздает представление, если оно уже существует. Можно использовать эту опцию для изменения определения представления без того, чтобы удалять его, создавать заново и вновь назначать все объектные привилегии, которые были назначены по данному представлению;

FORCE — создает представление независимо от того, существуют ли базовые таблицы этого представления, и от того, имеет ли владелец схемы, содержащей представление, привилегии по этим таблицам. Необходимо чтобы оба названных условия были удовлетворены, прежде чем по данному представлению можно будет выдавать любые предложения SELECT , INSERT , UPDATE или DELETE . По умолчанию применяется параметр NOFORCE ;

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

Sсhema — схема, в которой создается представление. Если СХЕМА опущена, то ORACLE создает представление в схеме пользователя;

View — ключевое слово view это имя создаваемого представления;

Alias — специфицирует имена для выражений, выбираемых запросом представления. Число алиасов должно совпадать с количеством выражений, выбираемых подзапросом. Алиасы должны удовлетворять правилам именования объектов схем. Алиасы должны быть уникальны внутри представления. Если алиасы опускаются, то ORACLE определяет их по именам или алиасам столбцов в запросе представления. Поэтому использовать алиасы нужно, если запрос представления содержит, помимо имен столбцов, выражения;

AS subquery — идентифицирует столбцы и строки таблиц, на которых базируется представление. Запрос представления может быть любым предложением SELECT , не содержащим фраз ORDER BY или FOR UPDATE . Его список выборки может содержать до 254 выражений;

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

Constraint — имя, которое присваивается ограничению CHECK OPTION . Если этот идентификатор опущен, то ORACLE автоматически назначает этому ограничению имя следующего вида:

SYS_Cn , где n — целое, которое делает имя ограничения уникальным внутри базы данных.

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

Если команды обновления DML (INSERT , UPDATE , DELETE ) можно применить к представлению, то говорят, что представление является обновляемым (updatable); в противном случае оно является только читаемым (read-only). Представление является обновляемым, если при его создании учитывались следующие критерии:

• представление должно включать первичный ключ таблицы
• не должно содержать полей, полученных в результате применения функций агрегирования
• не должно содержать DISTINCT, GROUP BY, HAVING в своем определении
• может быть определено на другом представлении, но это представление должно быть обновляемым
• не может содержать константы, строки или выражения (например, comm*100) в списке выбираемых выходных полей

Пример 1.
CREATE VIEW Oracle .
Простое представление, которое создается из данных одной таблицы:

London_view AS SELECT * FROM Salespeople WHERE city = ‘London’;

Пример 2.
CREATE VIEW Oracle .
При создании представления можно можно задать новые имена полей:

Rating_view(rating,number) AS SELECT rating, COUNT (*) FROM Customers GROUP BY rating;

Пример 3.
CREATE VIEW Oracle .
Представления могут получать информацию из любого количества базовых таблиц:

Nameorders AS SELECT onum, amt,a.snum, sname, cname FROM Orders a, Customers b, Salespeople C WHERE a.cnum = b.cnum AND a.snum = c.snum;

Пример 4.
CREATE VIEW Oracle .
При создании представлений можно использовать подзапросы, включая и связанные подзапросы:

Sales_view AS SELECT b.odate, a.snum, a.sname, FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt) FROM Orders c WHERE c.odate = b.odate);

Пример 5.
CREATE VIEW Oracle .

Empl_v04 AS SELECT e.eid, e.sname, e.fname, e.otch, p.pname, d.dname FROM posts p, departments d, employees e WHERE e.did = d.did AND e.pid = p.pid;

Пример создания представления (View) в базе данных типа MS SQL Server средствами MS Visual Studio. Создание вычисляемого поля

В данной теме описывается пошаговый детальный процесс создания представления (view) на примере базы данных Education.mdf , которая размещается в локальном файле. База данных предназначена для работы под управлением СУБД Microsoft SQL Server .

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

Условие задачи

Пусть дана база данных, которая размещается в файле Education.mdf . База данных содержит две, связанные между собой, таблицы Student и Session .

Таблицы связаны между собой по полю ID_Book .

Используя средства Microsoft Visual Studio создать представление (View ) с именем View1 , которое будет иметь следующую структуру:

Название поля	Таблица
Num_Book	Student
Name	Student
Mathematics	Session
Informatics	Session
Philosophy	Session
Average	Вычисляемое поле

Выполнение (пошаговая инструкция)

1. Загрузить Microsoft Visual Studio

2. Подключить базу данных Education.mdf к перечню баз данных утилиты Server Explorer

Чтобы не тратить время на разработку и связывание таблиц базы данных Education.mdf, готовую базу данных можно загрузить . После загрузки и сохранения в некоторой папке, базу данных нужно подключить к перечню баз данных утилиты Server Explorer .

Подключение базы данных реализуется одним из нескольких стандартных способов:

• выбором команды «Connect to Database…» из меню Tools;
• выбором кнопки (команды) «Connect to Database…» из утилиты Server Explorer .

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

Рис. 1. Способы добавления/подключения базы данных

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

Подробное описание того, как осуществляется подключение базы данных типа Microsoft SQL Server в Microsoft Visual Studio, приведено в теме:

После подключения, окно утилиты Server Explorer будет иметь вид как показано на рисунке 2.

Рис. 2. Утилита Server Explorer с подключенной базой данных Education.mdf

3. Добавление нового представления (View ). Команда «Add New View»

Система Microsoft Visual Studio позволяет создавать представление данных (views). Сами представления только отображают данные таблиц в удобном для чтения формате. Они не являются непосредственно данными таблиц (Tables ). В нашем случае нужно создать представление в соответствии с условием задачи.

Просмотр создается с помощью команды «Add New View» , которая вызывается из контекстного меню (рисунок 3).

Рис. 3. Команда «Add New View…»

В результате откроется окно «Add Table» , в котором нужно выбрать таблицы, данные из которых будут использоваться в представлении (рисунок 4).

Рис. 4. Выбор таблиц, которые будут использоваться в запросе

Для нашего случая нужно выбрать обе таблицы.

В результате, окно Microsoft Visual Studio примет вид, как показано на рисунке 5.

Рис. 5. Окно Microsoft Visual Studio после создания представления

В таблицах нужно выделить поля, которые будут использоваться в представлении. Порядок выбора полей может отвечать отображению их в запросе в соответствии с условием задачи. Это означает, что сначала выбираются поля таблицы Student (NumBook , Name ), а потом выбираются поля таблицы Session (Mathematics , Informatics , Philosophy ).

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

Рис. 6. Выбор полей для представления

Как видно из рисунка 6, в нижней части окна отображается представление на языке SQL , сформированное системой

SELECT dbo.Student.Num_Book, dbo.Student.Name, dbo.Session.Mathematics, dbo.Session.Informatics, dbo.Session.Philosophy FROM

4. Добавление вычисляемого поля Average

Чтобы создать вычисляемое поле Average , нужно в нижней части изменить текст SQL -запроса для представления (см. рисунок 6, нижняя часть). Например:

SELECT dbo.Student.Num_Book, dbo.Student.Name, dbo.Session.Mathematics, dbo.Session.Informatics, dbo.Session.Philosophy, (dbo.Session.Mathematics + dbo.Session.Informatics + dbo.Session.Philosophy)/3.0 AS Average FROM dbo.Session INNER JOIN dbo.Student ON dbo.Session.ID_Book = dbo.Student.ID_Book

Добавляется вычисляемое поле Average , которое есть средним арифметическим (рисунок 7).

Рис. 7. Добавление вычисляемого поля Average

5. Запуск SQL-запроса для отображения представления

В нижней части окна на рисунке 7 изображен результат выполнения SQL -запроса для представления. Выполнение запроса осуществляется вызовом команды «Execute SQL» из меню «Query Designer» или нажатием на кнопке ‘!’ , как изображено на рисунке 8).

Рис. 8. Вызов запуска SQL -запроса

6. Сохранение представления

После вызова команды

File->Save All

откроется окно «Choose Name» (рисунок 9), в котором нужно задать имя новосозданного представления. В нашем случае можно оставить имя (View1 ), которое предлагается системой по умолчанию.

Рис. 9. Окно задания имени для представления

7. Отображение представления в окне Server Explorer

После сохранения представления, окно утилиты Server Explorer будет иметь вид, как показано на рисунке 10. Как видно из рисунка, представление View1 отображается в окне Server Explorer .

Представлениями можно управлять в редакторе запросов, выполняя сценарии SQL, которые используют команды языка DDL: CREATE, ALTER и DROP. Основной синтаксис создания представления следующий:

CREATE VIEWимя_представления AS инструкция_SELECT

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

CREATE VIEW .

SELECT dbo.Customer.IdCust, dbo.Customer.FName, dbo.Customer.LName, dbo.City.CityName

FROM dbo.Customer INNER JOIN

dbo.City ON dbo.Customer.IdCity = dbo.City.IdCity

Попытка создать представление, которое уже существует, вызовет ошибку. Когда представление создано, инструкцию SELECT можно с легкостью отредактировать с помощью команды ALTER:

ALTER имя_представления AS измененная_инструкция_SELECT

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

Чтобы удалить представление из базы данных, используйте команду DROP:

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

Предложение order by и представления

Представления служат источником данных для других запросов и не поддерживают сортировку внутри себя. Например, следующий код извлекает данные из представления v_Customerи упорядочивает их по полямLNameиFName. Предложение ORDER BY не является частью представления v_Customer, а применяется к нему с помощью вызова инструкции SQL:

SELECT IdCust, FName, LName, CityName

FROM dbo.v_Customer

ORDER BY LName, FName

Выполнение представлений

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

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

Панель SQL отобразит представление в предложении FROM инструкции SELECT. Именно в такой форме на представление ссылаются пользователи:

SELECT * FROM v_Customer

Задание для самостоятельной работы: Создайте представление возвращающее список заказов с указанием имени клиента и количества товаров в каждом заказе. Таким образом, результат должен включать следующие атрибуты:IdOrd,OrdDate,IdCust,FName,LName, Количество видов товаров в заказе.

Лабораторная работа №7: Программирование на t-sql Синтаксис и соглашения t-sql

Правила формирования идентификаторов

Все объекты в SQLServerимеют имена (идентификаторы). Примерами объектов являются таблицы, представления, хранимые процедуры и т.д. Идентификаторы могут включать до 128 символов, в частности, буквы, символы _ @ $ # и цифры. Первый символ всегда должен быть буквенным. Для переменных и временных таблиц используются специальные схемы именования. Имя объекта не может содержать пробелов и совпадать с зарезервированным ключевым словомSQLServer, независимо от используемого регистра символов. Путем заключения идентификаторов в квадратные скобки, в именах объектов можно использовать запрещенные символы.

Завершение инструкции

Стандарт ANSISQLтребует помещения в конце каждой инструкции точки с запятой. В то же время при программировании на языкеT-SQLточка с запятой не обязательна.

Комментарии

Язык T-SQL допускает использование комментариев двух стилей: ANCI и языка С. Первый из них начинается с двух дефисов и заканчивается в конце строки:

Это однострочный комментарий стиля ANSI

Также комментарии стиля ANSI могут вставляться в конце строки инструкции:

SELECT CityName – извлекаемые столбцы

FROM City – исходная таблица

WHERE IdCity = 1; -- ограничение на строки

Редактор SQL может применять и удалять комментарии во всех выделенных строках. Для этого нужно выбрать соответствующие команды в меню Правка или на панели инструментов.

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

Удаление

Переименование

Изменение

Управление хранимыми процедурами

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

Для этого необходимо использовать специальную системную хранимую процедуру:

sp_rename ‘ИмяОбъекта’ ‘НовоеИмяОбъекта’.

Для удаления хранимой процедуры используется команда Transact‑SQL:

DROP PROC ИмяПроцедуры.

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

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

Представления обладают следующими преимуществами:

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

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

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

MS SQL Server предоставляет различные способы создания представлений: с помощью средств Transact-SQL и в утилите администрирования Management Studio .

Для создания представления используется команда CREATE VIEW, правом ее выполнения обладают члены ролей sysadmin, db_owner, db_dlladmin :

CREATE VIEW ИмяПредставления [(поле [,...n])]

ЗапросВыборки

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

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

Например, создадим представление, содержащее лишь такую информацию о сотрудниках компании AdventureWorks , как: должность и логин сотрудника, дата рождения.

CREATE VIEW InfoEmployees ([Номер], [Фамилия], [Дата рождения]) AS

SELECT BusinessEntityID, JobTitle + "(" + LoginID + ")",

CONVERT (char(10), BirthDate, 104)

FROM HumanResources.Employee

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

SELECT * FROM InfoEmployees

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

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

CREATE VIEW OnlineVendors

FROM Purchasing.Vendor

WHERE PurchasingWebServiceURL IS NOT NULL

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

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

CREATE VIEW InfoOrders

SELECT FirstName + " " + LastName as [Название компании],

SalesOrderHeader.SalesOrderID as [Номер заказа],

Convert (money, sum(UnitPrice*OrderQty*(1-UnitPriceDiscount)),0) as [Итог]

FROM (Person.Contact INNER JOIN Sales.SalesOrderHeader

ON Contact.ContactID=SalesOrderHeader.ContactID)

INNER JOIN Sales.SalesOrderDetail

ON SalesOrderHeader.SalesOrderID=SalesOrderDetail.SalesOrderID

GROUP BY SalesOrderHeader.SalesOrderID, FirstName + " " + LastName

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

Последнее обновление: 14.08.2017

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

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

Для создания представления используется команда CREATE VIEW , которая имеет следующую форму:

CREATE VIEW название_представления [(столбец_1, столбец_2, ....)] AS выражение_SELECT

Например, пусть у нас есть три связанных таблицы:

CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL); CREATE TABLE Customers (Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(30) NOT NULL); CREATE TABLE Orders (Id INT IDENTITY PRIMARY KEY, ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE, CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE, CreatedAt DATE NOT NULL, ProductCount INT DEFAULT 1, Price MONEY NOT NULL);

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

CREATE VIEW OrdersProductsCustomers AS SELECT Orders.CreatedAt AS OrderDate, Customers.FirstName AS Customer, Products.ProductName As Product FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id INNER JOIN Customers ON Orders.CustomerId = Customers.Id

То есть данное представление фактически будет возвращать сводные данные из трех таблиц. И после его создания мы сможем его увидеть в узле Views у выбранной базы данных в SQL Server Management Studio:

Теперь используем созданное выше представление для получения данных:

SELECT * FROM OrdersProductsCustomers

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

Представления могут иметь не более 1024 столбцов и могут обращать не более чем к 256 таблицам.

Также можно создавать представления на основе других представлений. Такие представления еще называют вложенными (nested views). Однако уровень вложенности не может быть больще 32-х.

Команда SELECT , используемая в представлении, не может включать выражения INTO или ORDER BY (за исключением тех случаев, когда также применяется выражение TOP или OFFSET ). Если же необходима сортировка данных в представлении, то выражение ORDER BY применяется в команде SELECT, которая извлекает данные из представления.

Также при создании представления можно определить набор его столбцов:

CREATE VIEW OrdersProductsCustomers2 (OrderDate, Customer,Product) AS SELECT Orders.CreatedAt, Customers.FirstName, Products.ProductName FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id INNER JOIN Customers ON Orders.CustomerId = Customers.Id

Изменение представления

Для изменения представления используется команда ALTER VIEW . Эта команда имеет практически тот же самый синтаксис, то и CREATE VIEW :

ALTER VIEW название_представления [(столбец_1, столбец_2, ....)] AS выражение_SELECT

Например, изменим выше созданное представление OrdersProductsCustomers:

ALTER VIEW OrdersProductsCustomers AS SELECT Orders.CreatedAt AS OrderDate, Customers.FirstName AS Customer, Products.ProductName AS Product, Products.Manufacturer AS Manufacturer FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id INNER JOIN Customers ON Orders.CustomerId = Customers.Id

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

Для удаления представления вызывается команда DROP VIEW :

DROP VIEW OrdersProductsCustomers

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