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

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

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

CREATE VIEW . Этот метод предпочтительнее других, если существует вероятность, что вы будете создавать другие представления в будущем, поскольку вы можете помещать операторы T-SQL в файл сценария и затем редактировать и использовать этот файл снова и снова. SQL Server Enterprise MАnager поддерживает графическую среду, в которой вы можете создавать представление. И наконец, вы можете использовать мастер создания представлений Create View Wizard, когда вам требуется помощь, чтобы пройти через процесс создания представления, что может оказаться полезным как для новичка, так и специалиста.

Использование T-SQL для создания представления

Создание представлений с помощью T-SQL – достаточно простой процесс: вы запускаете оператор CREATE VIEW для создания представления с помощью ISQL, OSQL или Query Аnalyzer. Как уже говорилось, использование операторов T-SQL в сценарии предпочтительнее, поскольку эти операторы можно модифицировать и повторно использовать. (Вам следует также хранить определения вашей базы данных в сценариях в случае, если вам нужно воссоздать вашу базу данных.)

Оператор CREATE VIEW имеет следующий синтаксис:

CREATE VIEW имя_представления [(колонка, колонка...)] AS ваш оператор SELECT

Создавая представление, вы можете активизировать два средства, которые изменяют поведение представления. Для активизирования этих средств нужно включить в оператор T-SQL ключевые слова WITH ENCRYPTION и/или WITH CHECK OPTION . Рассмотрим эти средства более подробно.

Ключевое слово WITH ENCRYPTION указывает, что определение представления (оператор SELECT , определяющий представление) должно шифроваться. SQL Server использует для шифрования операторов SQL тот же метод, что и для паролей. Этот метод обеспечения безопасности может оказаться полезным, если вы не хотите, чтобы определенные классы пользователей знали, к каким таблицам осуществляется доступ.

Ключевое слово WITH CHECK OPTION указывает, что операции модифицирования данных, применяемые к представлению, должны отвечать критериям, содержащимся в операторе SELECT . Например, можно запретить операцию модифицирования данных, применяемую к представлению для создания строки таблицы, которая не видна внутри этого представления. Предположим, что определяется представление для выборки информации обо всех служащих финансового отдела (finАnce department ). Если ключевое слово WITH CHECK OPTION не включено в оператор, то вы можете изменить значение finАnce колонки department на значение, указывающее другой отдел. Но если это ключевое слово указано, то данное изменение не будет допускаться, поскольку изменение значения колонки department в какой-либо строке сделает эту строку недоступной из данного представления. Ключевое слово WITH CHECK OPTION указывает, что вы не можете сделать какую-либо строку недоступной из представления, внося какое-либо изменение внутри этого представления.

Оператор SELECT можно изменять для создания любого нужного вам представления. Его можно использовать для выборки подмножества колонок или подмножества строк либо для выполнения какой-либо операции связывания (join). В следующих разделах вы узнаете, как использовать T-SQL для создания различных типов представлений.

Подмножество колонок

Представление, содержащее подмножество колонок, может оказаться полезным, если вам требуется обеспечить безопасность таблицы, которая должна быть доступна пользователям лишь частично. Рассмотрим один пример. Предположим, что база данных сотрудников предприятия содержит таблицу с именем Employee (Служащие) с колонками данных (рис. 18.1).

Рис. 18.1.

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

Чтобы создать представление по таблице Employee, в котором имеется доступ только к колонкам name (имя), phone (телефон) и office (комната), используйте следующий оператор T-SQL:

CREATE VIEW emp_vw AS SELECT name, phone, office FROM Employee

Результирующее представление будет содержать колонки (рис. 18.2). Хотя эти колонки также существуют в базовой таблице , пользователи, имеющие доступ к данным через это представление, могут видеть эти колонки только в этом представлении. А поскольку представление может иметь уровень безопасности, отличный от базовой таблицы представления, это представление можно предоставлять для доступа любому пользователю, в то время как образующая таблица останется защищенной. Иными словами, вы можете ограничить доступ к таблице Employee, разрешив его, например, только отделу кадров, и можете предоставить всем пользователям доступ к этому представлению.

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

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

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

Удаление

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

Изменение

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

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

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