Лабораторная работа №5 «создание представлений»

Цель работы: Создание сложных запросов. Создание триггеров.

Постановка задачи

• Создать 5 представлений (views);

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

Методические указания

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

Представление (view) ‑ это виртуальная таблица, определяемая запросом, содержащим оператор SELECT. Эта виртуальная таблица состоит из данных одной или нескольких реальных таблиц, а для пользователей представление выглядит, как реальная таблица. С представлением можно работать, как с обычной таблицей. К представлению можно применять операции SELECT, INSERT, UPDATE и DELETE. На самом деле представление хранится просто как заранее определенный оператор SQL. При доступе к представлению оптимизатор запросов SQL Server объединяет текущий выполняемый оператор SQL с запросом, который был использован для определения данного представления.

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

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

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

1. С помощью Microsoft SQL Server Management Studio;

2. C помощью кода на T-SQL.

Создание представления с помощью Microsoft SQL Server Management Studio

Для пример будем использовать БД Northwind.

В окне Object Explorer раскройте папку Databases (Базы данных) на котором находится база данных Northwind. Найдите папку View (представление). Щелкните правой клавишей по View (представление) в появившемся контекстном меню выберите команду New View (Новое представление) (рисунок 5.1).

Рисунок 5.1 ‑ Пункт меню “Новое представление”

При создании представления система спрашивает, какие таблицы и созданные ранее представления будут использоваться при создании представления. (Рисунок 5.2)

Рисунок 5.2 – Выбор нужных таблиц

Созданное представление. (Рисунок 5.3)

Панель сетка Панель sql Панель результатов Панель схемы

Рисунок 5.3 – Окно созданного представления

Окно представление состоит из следующих четырех панелей:

• Панель схемы (Diagram panel). Показывает данные таблицы, которые используются для создания представления. В этой панели можно выбирать колонки. В этой панели отображаются таблицы и созданные ранее представления, которые участвуют в представлении.

• Панель сетка (Grid panel). Показывает колонки, выбранные из таблицы или базовых таблиц. В этой панели можно выбирать колонки (Column) таблицы, псевдоним колонки (Alias), таблицы (Table), отображать или нет данные (Output), тип сортировки (Sort Type), сортировать или нет (Sort Order), фильтры (Filter).

• Панель SQL (SQL panel). Показывает оператор SQL, используемый для определения данного представления. SQL Server генерирует этот оператор SQL, когда вы перетаскиваете элементы панели схемы и выбираете колонки в панели сетке.

• Панель результатов (Results panel). Показывает строки, считанные из представления. Эта информация позволяет вам понять, как выглядят данные.

Помимо этих четырех панелей есть кнопка группировки на панели инструментов . После нажатия на нее в панели сетка (Grid panel) появиться новая колонка Group By (Группировка).

Создадим представление выводящее работников дата рождения которых позднее 1930 года. Вывести фамилии работников и их дата рождения.

Этапы создания:

1. Добавим в представление нужные таблицы, а именно таблицу работники (Employees);

2. Выберем, что хотим вывести на экран;

3. Установим псевдонимы для колонок;

4. Отсортируем данные по фамилии (LastName);

5. Создадим фильтр по полю день рождения (BirthDate) > ’01.01.1930’.

Результат работы представлен на рисунке 5.4.

Рисунок 5.4 – Готовое представление

В Панель SQL видим сформированный sql-код. Содержание типичное для подобного запроса. Есть несколько моментов:

1. Строка TOP (100) PERCENT говорит, что все данные будут представлены.

2. Помимо указания таблицы указывается также схема данных.

3. Поле день рождения (BirthDate) имеет тип данных DataTime (т.е. сразу указывается дата и время в одном поле) и американский стиль представления даты (ГГГГ-ММ-ДД). Мы же к этому полю обратились указав фильтр > ’01.01.1930’ без преобразования. Все преобразования связанные с изменением типа и представления взял на себя SQL Server. Поэтому видим в строке в условия также код конвертации. (BirthDate > CONVERT(DATETIME, '1930-01-01 00:00:00', 102))

Можем использовать не одну таблицу, а сразу две и более, также можно использовать уже ранее созданное и сохраненное представление.

Создание представления с помощью кода на T-SQL

Синтаксис оператора CREATE VIEW:

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

[WITH ENCRYPTION]

AS

ваш оператор SELECT

[WITH CHECK OPTION]

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

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

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