Представлениями можно управлять в редакторе запросов, выполняя сценарии 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 может применять и удалять комментарии во всех выделенных строках. Для этого нужно выбрать соответствующие команды в меню Правка или на панели инструментов.

В предыдущих статьях инструкции 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

Пример создания представления (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 .

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

Удаление

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

Изменение

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

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

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