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

1.

Представления и
табличные объекты

2.

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

3.

Создание представления
CREATE VIEW название_представления [(столбец_1,
столбец_2, ....)]
AS выражение_SELECT
3

4.

Изменение представления
4

5.

Использование представления
Теперь используем
получения данных:
созданное
выше
представление
для
SELECT * FROM Заказ_товара
5

6.

Нюансы
Представления, как и таблицы, должны
уникальные имена в рамках той же базы данных.
иметь
Представления могут иметь не более 1024 столбцов и
могут обращаться не более чем к 256 таблицам.
Также можно создавать представления на основе других
представлений. Такие представления еще называют
вложенными (nested views). Однако уровень вложенности не
может быть больше 32-х.
6

7.

Нюансы
Команда SELECT, используемая в представлении, не может включать
выражения INTO или ORDER BY (за исключением тех случаев, когда также
применяется выражение TOP или OFFSET).
OFFSET (смещение)
Использование данной команды позволяет пропустить указанное
количество строк перед тем как выводить результаты запроса.
Допустим, у нас есть 100 записей в таблице и нужно пропустить первые 10
строк и вывести строки с 11 по 100.
Select * From <SomeTable>
Order by <SomeColumn>
Offset 10 Rows
Если же необходима сортировка данных в представлении, то выражение
ORDER BY применяется в команде SELECT, которая извлекает данные из
представления.
7

8.

При создании представления можно
определить набор его столбцов
8

9.

Изменение представления
ALTER VIEW название_представления [(столбец_1, столбец_2,
....)]
AS выражение_SELECT
9

10.

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

11.

Представление в среде MS SQL
Server Management Studio
11

12.

Обновляемое представление
Представления могут быть обновляемыми (updatable). В таких представлениях
мы можем изменить или удалить строки или добавить в них новые строки.
При создании подобных представлений есть множество ограничений. В
частности, команда SELECT при создании обновляемого представления не может
содержать:
TOP
DISTINCT
UNION
JOIN
агрегатные функции типа COUNT или MAX
GROUP BY и HAVING
подзапросы
производные столбцы или столбцы, которые вычисляются на основании
нескольких значений
обращения одновременно к нескольким таблицам
12

13.

Обновляемое представление
Создадим обновляемое представление и добавим в него данные
13

14.

Обновляемое представление
Стоит отметить, что при добавлении фактически будет добавлен объект в
таблицу Товары, которую использует представление Запрос_Товары.
И поэтому надо учитывать, что если в этой таблице есть какие-либо столбцы, в
которые представление не добавляет данные, но которые не допускают значение
NULL или не поддерживают значение по умолчанию, то добавление завершится с
ошибкой.
14

15.

Обновление строки в
представлении
Обновление и удаление также затрагивают ту таблицу, которую использует
представление.
15

16.

Удаление
строки
представлении
в
Обновление и удаление также затрагивают ту таблицу, которую использует
представление.
16

17.

Табличные переменные
Табличные переменные (table variable) позволяют сохранить
содержимое целой таблицы. Формальный синтаксис определения
подобной переменной во многом похож на создание таблицы:
DECLARE @табличная_переменная TABLE
(столбец_1 тип_данных [атрибуты_столбца],
столбец_2 тип_данных [атрибуты_столбца] ....)
[атрибуты_таблицы]
17

18.

Табличные переменные
В данном случае переменная @ABrends будет содержать два столбца.
В дальнейшем мы сможем работать с этой переменной как с обычной таблицей, то
есть добавлять в нее данные, изменять, удалять и извлекать их.
Однако следует учитывать, что такие переменные не полностью эквивалентны
таблицам. Они живут в пределах одного пакета, после завершения работы которого они
удаляются. То есть они носят временный характер, и физически их данные нигде не
хранятся на жестком диске.
18

19.

Временные таблицы
Временная таблица - это объект базы данных, который хранится и управляется
системой базы данных на временной основе. Такие таблицы могут быть полезны для
хранения табличных данных внутри сложного комплексного скрипта.
Временные таблицы могут быть локальными или глобальными. Локальные
временные таблицы представлены физически, т.е. они хранятся в системной базе
данных tempdb. Имена временных таблиц начинаются с префикса #, например
#table_name.
Локальная временная таблица принадлежит создавшему ее сеансу, и видима
только этому сеансу. Она удаляется по завершению создавшего ее сеанса. (Также
локальная временная таблица, определенная в хранимой процедуре, удаляется по
завершению выполнения этой процедуры.)
Глобальные временные таблицы видимы любому пользователю и любому
соединению и удаляются после отключения от сервера базы данных всех
обращающихся к ним пользователей. В отличие от локальных временных таблиц имена
глобальных временных таблиц начинаются с префикса ##.
19

20.

Временные таблицы
Если необходимо удалить таблицу до завершения сессии базы данных, то
для этой таблицы следует выполнить команду DROP TABLE.
20

21.

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

22.

Локальная временная таблица
Выведем во временную таблицу промежуточные данные из таблицы
Заказы:
22

23.

Локальная временная таблица
Здесь вначале извлекаются данные во временную таблицу
#СуммаЗаказа. Причем так как данные в нее извлекаются с помощью
выражения SELECT INTO, то предварительно таблицу не надо создавать.
Эта таблица будет содержать id товара, общее количество проданного
товара и на какую сумму был продан товар.
Затем эта таблица может использоваться в выражениях INNER JOIN.
23

24.

Глобальная временная таблица
24

25.

Обобщенные
выражения
табличные
Кроме временных таблиц MS SQL Server позволяет создавать обобщенные
табличные выражения (common table expression или CTE), которые являются
производными от обычного запроса и в плане производительности являются
более эффективным решением, чем временные таблицы. Обобщенное табличное
выражение задается с помощью ключевого слова WITH:
В
отличие
от
временных
таблиц
табличные
выражения
хранятся в оперативной
памяти
и
существуют
только во время первого
выполнения
запроса,
который представляет это
табличное выражение.
25

26.

Задания
1. В базе данных Товары создайте представление, использующее данные из
связанных таблиц.
2. Создайте обновляемое представление, которое позволяет вставить новый товар
в представление. Убедитесь, что товар попал и в исходную таблицу.
3. Измените данные в представлении. Убедитесь, что данные изменились и в
исходной таблице.
4. Выведите во временную локальную таблицу Стоимость товаров на складе,
учитывая их количество.
26