Задание 7

SQL сервер. Использование представлений (View)

С помощью утилиты SQL Server Management Studio для БД Sales выполните следующие действия :

• подключитесь к БД Sales;

• создайте представления (View) на выборку из одной и объединения трех таблиц;

• создайте выборки с условиями, сортировкой;

• добавьте в таблицы данные;

• сохраните БД для дальнейшей работы.

Описание работы.

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

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

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

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

• Модификация данных с помощью представлений.

Использование представлений для разбиения таблиц

Когда пользователи запрашивают большие таблицы, им требуется видеть только их небольшой фрагмент или поднабор. Существует два метода возврата поднабора данных: приме­нение запроса SELECT с предложением WHERE и использование представления.

Метод запроса SELECT хорошо работает для редко выполняемых запросов (назы­ваемых нерегламентированными запросами), но он несколько сложен для пользователей, которые не понимают коды Transact-SQL. Например, если вы хотите просмотреть в таблице Customers только имена, фамилии и телефонные номера покупателей в коде зоны 495, то можете выполнить следующий запрос:

USE Sales

SELECT Fname, Lname, Phone from Customers

WHERE Phone LIKE '495%'

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

Чтобы понять суть представлений, мы создадим простое представление с помощью редактора View Assisted Editor.

Создание представления с помощью редактора View Assisted Editor

Вспомогательный редактор представляет собой новый термин для старой концеп­ции мастера. В то время, как мастер открывает несколько диалоговых окон, вспо­могательные редакторы имеют лишь одно окно и работают быстрее. В следующем наборе операций мы используем View Assisted Editor для создания представления, ко­торое будет отображать только те записи базы данных, телефонный номер в которых начинается с цифр 495.

1. Откройте SQL Server Management Studio и выполните подключение с исполь­зованием аутентификации SQL-.

2. В окне Object Explorer раскройте папки сервера Databases => Sales, щелкните правой кнопкой мыши на папке Views и выполните команду New View.

3. В диалоговом окне Add Table выберите таблицу Customers и щелкните
   на кнопке Add.

4. Чтобы вернуться в редактор View Assisted Editor, щелкните на кнопке Close.

5. В текстовый блок синтаксического редактора Transact-SQL введите следующие
   код (рис. 1):

SELECT Fname, Lname, Phone

FROM Customers

WHERE Phone LIKE '495%'

6. Выберите в меню пункт File=>Save View и сохраните представление под именем
dbo. View_1.

7. В диалоговом окне Choose Name введите имя Contact_in_495 и щелкните на
кнопке ОК.

Рис. 1. В окно редактора вводим код T-SQL

Теперь в папке Views базы данных Sales появится новое представле­ние с именем dbo.Contact_in_495. Вы можете протести­ровать представление, чтобы увидеть, как оно выполняет запросы.

1. Щелкните на кнопке New Query и выберите пункт New SQL Server Query.

2. Чтобы протестировать представление, введите и выполните следующий код

USE Sales

SELECT * FROM dbo.Contacts_in_495

3. Чтобы проверить, что представление извлекает точно такие же данные, что и запрос SELECT, введите и выполните запрос SELECT, на котором основано представление:

SELECT Fname, Lname, Phone

FROM Customers

WHERE Phone LIKE '495%'

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

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

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

1. В окне Object Explorer щелкните правой кнопкой мыши на представлении

dbo.Contacts_in_495 базы данных Sales и выберите команду Modify View.

2. В текстовом блоке синтаксиса T-SQL измените запрос SELECT (рис. 2):

SELECT State as [Регион], Lname as [Фамилия], Fname as [Имя],

Phone as [Номер телефона]

FROM dbo.Customers

WHERE phone LIKE '495%'

1. Щелкните на кнопке Save (значок в виде дискеты на панели инструментов), но
   не выходите из редактора.

2. Чтобы протестировать представление, перейдите в окно New Query и выполните следующий код (рис. 3).

USE Sales

SELECT * FROM dbo.Contact_in_495

Рис.2. Создание псевдонима

Рис. 3. Теперь имена столбцов стали понятнее

Организация результирующего набора данных

Возможно, вы уже обратили внимание на то, что в результирующих наборах данных, получаемых от представления, нет никакой организации — записи отображают­ся случайным образом. Результирующий набор трудно читать, так что вам нужно организовать его путем добавления предложения ORDER BY. Это предложение будет сортировать результаты представления согласно выбранному полю. В следующем наборе операций мы привнесем в представ­ление dboCustomers_in_495 некоторую организацию путем использования выражения ORDER BY по полю Lname.

1. В SQL Server Management Studio вернитесь к редактору View Assisted Editor
   который должен быть открыт после выполнения последнего набора действий.

2. В блоке синтаксиса T-SQL модифицируйте код (обратите внимание на предложение ТОР 100 PERCENT):

SELECT TOP 100 PERCENT State as [Регион], Lname as [Фамилия], Fname as [Имя], Phone as [Номер телефона]

FROM dbo.Customers

WHERE phone LIKE '495%'

ODER BY Lname

1. Щелкните на кнопке Save и закройте View Assisted Editor.

2. Чтобы протестировать изменения, перейдите в окно New Query и выполните
   следующий код:

USE Sales

SELECT * FROM dbo.Contacts_in_495

Теперь у нас имеется упорядоченное представление всех покупателей с номерами телефонов, начинающимися с 495. Чтобы получить такой же результат с помощью стандартного запроса SELЕСТ, вашим пользователям пришлось бы выполнить следующий код:

SELECT TOP 100 PERCENT State as [Регион], Lname as [Фамилия], Fname as [Имя], Phone as [Номер телефона]

FROM dbo.Customers

WHERE phone LIKE '495%'

ODER BY Lname

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

Использование представлений для объединения таблиц

Чтобы из­влечь все необходимые данные, может понадобиться проверить несколько таблиц с помощью запроса SELECT и выражения JOIN.

Объединение двух таблиц в представлении

В следующем наборе действий мы модифицируем представление dbo.Contact_in_495 для отображе­ния информации обо всех покупателях с номером телефона, начинающегося с 495, сделавших заказ, с целью отображе­ния даты заказа путем объединения таблиц dbo.Customers и dbo.Orders по общему столбцу CustID.

1. Откройте SQL Server management Studio и выполните подключение с использованием аутентификации Windows.

2. В Object Explorer расширьте папки сервера Databases=>Sales=>Views.

3. Щелкните правой кнопкой на представлении dbo.Contact_in_495 и выберите команду Modify View для открытия редактора View Assisted Editor.

4. Модифицируйте код в блоке синтаксиса T-SQL (рис. 4):

SELECT TOP 100 PERCENT Lname as [Фамилия], Fname as [Имя], Phone as [Номер телефона], o.OrdDate as [Дата]

FROM dbo.Customers с JOIN dbo.Orders o ON c.CustID = o.CustID

WHERE Phone LIKE '495%'

ORDER BY Lname

1. Щелкните на кнопке Save панели инструментов.

2. Чтобы протестировать представление, откройте новый запрос SQL Server и
   выполните следующий код:

USE Sales

SELECT * FROM dbo.Contact_in_495

Рис. 4. Объединение двух таблиц в представлении

Если вы сравните инструкцию SELECT из п. 4 с инструкцией SELECT из п. 6, то поймете, почему лучше использовать представление вместо нерегламентированного запроса. Если вы запишете инструкцию SELECT из п. 4 в интерфейсный код (который будет храниться на пользовательских машинах), то каждый раз при выполнении за­проса все строки кода будут пересылаться на сервер через сеть. Поскольку вы преобразовали этот запрос в представление, то по сети будут пересылаться только две строки кода из п. 6.

Объединение множества таблиц в представлении

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

Таблица Products содержит информацию обо всех продуктах, используемых в заказах, включая их идентификаторы. Чтобы извлечь идентификатор продукта, входящего в заказ, сделанный клиентом с телефонным номером, начинающимся с 495, нужно объединить таблицы Orders и Customers по столб­цу CustID, а затем объединить таблицы Orders и Products по столб­цу ProdID:

SELECT TOP 100 PERCENT Lname as [Фамилия], Fname as [Имя], Phone as [Номер телефона], o.OrdDate as [Дата], p.Description as [Продукт]

FROM dbo. Orders o

JOIN dbo.Customers c ON o.CustID = c.CustID

JOIN dbo.Products p ON o.ProdID = p.ProdID

WHERE Phone LIKE '495%'

ORDER BY Lname

Для отображения данных о продуктах вы можете модифицировать представление Contacts__in_495 сле­дующим образом:

Р
ис. 5. Модифицируем код запроса

1. Откройте SQL Server Management Studio и выполните подключение с использованием аутентификации SQL-сервер.

2. В окне Object Explorer раскройте папки Databases=>Sales=>Views.

3. Щелкните правой кнопкой мыши на представлении dbo. Contacts_in_495
   и с помощью команды Modify View, откройте редактор View Assisted Editor.

4. Модифицируйте свой код следующим образом (рис. 5):

SELECT TOP 100 PERCENT Lname as [Фамилия], Fname as [Имя], Phone as [Номер телефона], o.OrdDate as [Дата], p.Description as [Продукт]

FROM dbo. Orders o

JOIN dbo.Customers c ON o.CustID = c.CustID