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

В предыдущих статьях инструкции DDL и DML рассматривались применительно к базовым таблицам. Данные базовой таблицы хранятся на диске. В отличие от базовых таблиц, представления по умолчанию не существуют физически, т.е. их содержимое не сохраняется на диске. Это не относится к так называемым индексированным представлениям, которые рассматриваются позже. Представления (views) - это объекты базы данных, которые всегда создаются на основе одной или более базовых таблиц (или других представлений), используя информацию метаданных. Эта информация (включая имя представления и способ получения строк из базовых таблиц) - все, что сохраняется физически для представления. По этой причине представления также называются виртуальными таблицами.

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

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

CREATE VIEW view_name [(column_list)] AS select_statement Соглашения по синтаксису

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

Параметр view_name задает имя определяемого представления, а в параметре column_list указывается список имен, которые будут использоваться в качестве имен столбцов представления. Если этот необязательный параметр опущен, то используются имена столбцов таблиц, по которым создается представление. Параметр select_statement задает инструкция SELECT, которая извлекает строки и столбцы из таблиц (или других представлений). Параметр WITH ENCRYPTION задает шифрование инструкции SELECT, повышая таким образом уровень безопасности системы баз данных.

Предложение SCHEMABINDING привязывает представление к схеме таблицы, по которой оно создается. Когда это предложение указывается, имена объектов баз данных в инструкции SELECT должны состоять из двух частей, т.е. в виде schema.db_object, где schema - владелец, а db_object может быть таблицей, представлением или определяемой пользователем функцией.

Любая попытка модифицировать структуру представлений или таблиц, на которые ссылается созданное таким образом представление, будет неудачной. Чтобы такие таблицы или представления можно было модифицировать (инструкцией ALTER) или удалять (инструкцией DROP), нужно удалить это представление или убрать из него предложение SCHEMABINDING.

Когда при создании представления указывается параметр VIEW_METADATA , все его столбцы можно обновлять (за исключением столбцов с типом данных timestamp), если представление имеет триггеры INSERT или UPDATE INSTEAD OF.

Инструкция SELECT в представлении не может содержать предложение ORDER BY или параметр INTO. Кроме этого, по временным таблицам нельзя выполнять запросы.

Представления можно использовать для разных целей:

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

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

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

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

Запрос в этом примере выбирает из таблицы Works_on строки, удовлетворяющие условию Job="Консультант". Представление view_Consultant определяется строками и столбцами, возвращаемыми этим запросом. На рисунке ниже отображена таблица Works_on, в которой строки, выбранные в представлении view_Consultant, выделены красным цветом:

Запрос в этом примере задает выборку строк, т.е. он создает горизонтальное подмножество базовой таблицы Works_on. Возможно также создание представления с ограничениями на включаемые в него столбцы и строки. Создание такого представления показано в примере ниже:

USE SampleDb; GO CREATE VIEW view_WithoutBudget AS SELECT Number, ProjectName FROM Project;

Запрос в этом примере выбирает для включения в представление view_WithoutBudget все столбцы таблицы Project, за исключением столбца Budget.

Как уже упоминалось ранее, в общем формате инструкции CREATE VIEW не обязательно указывать имена столбцов представления. Однако, с другой стороны, в приведенных далее двух случаях обязательно требуется явно указывать имена столбцов:

    если столбец представления создается из выражения или агрегатной функции;

    если два или больше столбцов представления имеют одинаковое имя в базовой таблице.

В примере ниже показано создание представления, для которого явно указываются имена столбцов:

USE SampleDb; GO CREATE VIEW view_Count(projectNumber, countProject) AS SELECT ProjectNumber, COUNT(*) FROM Works_on GROUP BY ProjectNumber;

Здесь имена столбцов представления view_Count должны быть указаны явно по той причине, что инструкция SELECT содержит агрегатную функцию count(*), которая требует, чтобы все столбцы представления были именованы.

Не требуется явно указывать список столбцов в инструкции CREATE VIEW, если применить заголовки столбцов, как это показано в примере ниже:

USE SampleDb; GO CREATE VIEW view_Count1 AS SELECT ProjectNumber, COUNT(*) countProject FROM Works_on GROUP BY ProjectNumber;

Представление можно создать из другого представления, как показано в примере:

Представление view_project_p2 в примере ниже создается из представления view_Consultant. Все запросы, использующие представление view_project_p2, преобразовываются в эквивалентные запросы к базовой таблице Works_on.

Представления можно также создавать посредством среды Management Studio. Для этого выберите в обозревателе объектов базу данных, в которой требуется создать представление, щелкните в ней правой кнопкой мыши узел Views и в открывшемся контекстном меню выберите пункт New View. Откроется редактор представлений, в котором можно выполнять следующие действия:

    выбрать базовые таблицы и строки в этих таблицах для создания представления;

    присвоить представлению имя и определить условия в предложении WHERE соответствующего запроса.

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

Для изменения определения представления в языке Transact-SQL применяется инструкция ALTER VIEW . Синтаксис этой инструкции аналогичен синтаксису инструкции CREATE VIEW, применяющейся для создания представления.

Использование инструкции ALTER VIEW позволяет избежать переназначения существующих разрешений для представления. Кроме этого, изменение представления посредством этой инструкции не влияет на объекты базы данных, зависящие от этого представления. Если же модифицировать представление, сначала удалив его (инструкция DROP VIEW), а затем создав новое представление с требуемыми свойствами (инструкция CREATE VIEW), то все объекты базы данных, которые ссылаются на это представление, не будут работать должным образом, по крайней мере, в период времени между удалением представления и его воссоздания.

Использование инструкции ALTER VIEW показано в примере ниже:

В этом примере инструкция ALTER VIEW расширяет инструкцию SELECT в представлении view_WithoutBudget новым условием в предложении WHERE.

Инструкция DROP VIEW удаляет из системных таблиц определение указанного в ней представления. Применение этой инструкции показано в примере ниже:

USE SampleDb; GO DROP VIEW view_Count;

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

USE SampleDb; GO DROP VIEW view_Consultant;

Здесь инструкция DROP VIEW явно удаляет представление view_Consultant, при этом неявно удаляя представление view_project_p2, основанное на представлении view_Consultant. Теперь попытка выполнить запрос по представлению view_project_p2 возвратит сообщение об ошибке.

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

1) Понятие представления
Представления (View) – это объекты БД, которые не содержат собственных таблиц, но их содержимое берется из других таблиц или представлений посредством выполнения запроса.

2) Создание представлений
CREATE VIEW
[()]
AS
CHECK OPTION]

Примечания:
В SQL Server текст представления можно зашифровать с помощью опции WITH ENCRYPTION, указав её после имени представления.

3) Удаление представлений
DROP VIEW CASDADE|RESTRICT

Примечание:
RESTRICT – не должно существовать никаких ссылок на удаляемое представление в представлении и ограничениях, иначе в удалении будет отказано.
CASADE – означает удаление всех объектов, ссылающихся на данное представление.

4) Ключевые слова
a) RECURSIVE
Создается представление, которое получает значения из себя самого.
b) WITH CHECK OPTION
Запрещает обновление таблиц, на основе представлений, если изменяемые или добавляемые данные не отражаются в представлении.
Запрет действует только на значения, не подпадающие под условия, указанные в разделе WHERE .
c) LOCAL
Контролирует, чтобы изменения в базовых таблицах отражались только в текущем представлении.
d) CASCADED
Контролирует отражение изменений во всех представлениях, определенных на данном представлении.

5) Ограничения и особенности
1. Имена столбцов обычно указываются тогда, когда некоторые столбцы являются вычисляемыми и, следовательно, не поименованы, а также тогда, когда два или более столбца имеют одинаковые имена в соответствующих таблицах в запросе. В InterBase всегда.
2. В ряде СУБД нельзя использовать раздел ORDER BY, обеспечивающий сортировку.
3. Представления можно соединять как с базовыми таблицами, так и с другими представлениями с помощью запросов к обоим объектам.

6) Критерии обновляемости представлений
1. Оно должно базироваться только на одной таблице. Желательно, чтобы оно включало первичный ключ таблицы.
2. Оно не должно содержать столбцов, полученных в результате применения функций агрегирования.
3. Оно не может содержать спецификацию DISTINCT в своем определении.
4. Оно не может использовать GROUP BY или HAVING в своем определении.
5. Оно не должно содержать подзапросов.
6. Если оно определено на другом представлении, то и оно должно быть обновляемым.
7. Оно не может включать константы, строки или выражения в списке выходных полей. Перестановка и переименование полей не допустима.
8. Для оператора INSERT оно должно включать любые поля из лежащей в основе представлений базовой таблицы, которые имеют ограничения NOT NULL, однако в качестве значения по умолчанию может быть указано другое значение.

7) Примеры

1. CREATE VIEW LondonStaff
AS SELECT * FROM SalesPeople WHERE City=’London’

2. CREATE VIEW SalesOwn
AS SELECT SNum, SName, City FROM SalesPeople

3. CREATE VIEW NameOrders
AS SELECT ONum, Amt, A.SNum, SName, CName
FROM Orders A, Customer B, SalesPeople C
WHERE A.CNum=B.CNum AND A.SNUM=C.SNum

Примеры на запрет обновления:

1. CREATE VIEW HighRating AS SELECT CNum, Rating

2. Добавляем строку, которую представление не видит:
INSERT INTO HighRating VALUES(2018, 200)
3. Запрещаем добавлять строки вне видимости:
CREATE VIEW HighRating AS SELECT CNum, Rating
FROM Customer WHERE Rating=300
WITH CHECK OPTION
4. Создаем новое, которое разрешает вновь добавлять:
CREATE VIEW MyRating AS SELECT * FROM HighRating

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

Удаление

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

Изменение

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

Для изменения существующей процедуры используется оператор 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

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

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

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

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

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

    1. Sql индексы

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

    1. Триггеры

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

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

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

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

    1. Хранимые процедуры

Хранимая процедура - это последовательность компилированных операторов Transact-SQL, хранящихся в системной базе данных SQL Server. Хранимые процедуры предварительно откомпилированы, поэтому эффективность их выполнения выше, чем у обычных запросов. Хранимые процедуры работают непосредственно на сервере и хорошо укладываются в модель клиент - сервер.

Существует два вида хранимых процедур: системные и пользовательские.

Системные хранимые процедуры предназначены для получения информации из системных таблиц и выполнения различных служебных операций и особенно полезны при администрировании базы данных. Их имена начинаются с sp_ (stored procedure).

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

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

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

    Компоненты, ссылающиеся на объекты БД (таблицы, индексы, представления и т. п.), сопоставляются с этими объектами с предварительной проверкой их существования. Этот процесс носит название раз решение ссылок .

    В системной таблице syscomments сохраняется исходный текст процедуры, а в таблице sysobjects - ее название.

    Создается предварительный план выполнения запроса. Этот предварительный план называется нормализованным планом или деревом запроса и хранится в системной таблице sysprocedures.

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

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

Использование хранимых процедур имеют еще ряд дополнительных преимуществ.

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

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

3. Хранимые процедуры могут принимать аргументы при запуске и возвращать значения (в виде результирующих наборов данных).

4. Хранимые процедуры могут запускаться по расписанию (в режиме автоматического выполнения), задаваемому при запуске SQL Server.

5. Хранимые процедуры используются для извлечения или изменения данных в любое время.

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

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

Последнее обновление: 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

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