views

1.

СОЗДАНИЕ
ПРЕДСТАВЛЕНИЙ
1

2.

Таблицы,
в
которых
хранятся
данные
называются базовыми таблицами. Однако
имеется другой вид таблиц: представления.
Представления - это виртуальные таблицы чье
содержимое выбирается или получается из
других таблиц. Они работают в запросах и
операторах DML точно также как и основные
таблицы, но не содержат никаких собственных
данных.
.
2

3.

В отличие от других объектов базы
данных представление не занимает
дисковой памяти за исключением
памяти, необходимой для хранения
определения самого представления.
Представления - подобны окнам,
через которые
просматривают
информацию, которая фактически
хранится в базовой таблице
3

4.

Преимущества представлений
4

5.

1. Представление является простым, но
эффективным
механизмом
для
управления
санкционированием
доступа.
5

6.

Механизм представлений позволяет скрыть
реальную структуру БД от некоторых
пользователей за счёт определения его
собственного представления БД.
Представление
даёт
возможность
пользователю работать только с теми
данными, которые ему нужны, и скрыть
служебные данные, например, шифры.
6

7.

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

8.

3. Представления позволяют упростить
структуру запросов за счет объединения
данных
из
нескольких
таблиц
в
единственную виртуальную таблицу. В
результате
многотабличные
запросы
сводятся к простым запросам к одному
представлению.
8

9.

Представление
–
это
сохраняемое в каталоге базы
данных выражение запроса,
обладающее
собственным
именем
и,
возможно,
собственными
именами
столбцов.
9

10.

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

11.

Представление - это фактически тот же
запрос, который выполняется всякий раз,
когда представление участвует в какой-либо
команде.
Когда СУБД встречает в команде ссылку на
представление,
она
отыскивает
его
определение, хранящееся в БД.
После этого происходит преобразование
пользовательской команды в ее эквивалент
к исходной таблице.
11

12.

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

13.

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

14.

ON COMMIT. В этом режиме при всякой
фиксации изменений данных в главных
таблицах
материализованное
представление
обновляется
автоматически, отражая эти изменения.
ON DEMAND. В этом режиме для
обновления
материализованного
представления потребуется выполнить
процедуру обновления.
По умолчанию принимается режим ON
DEMAND.

15.

CREATE MATERIALIZED VIEW

16.

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

17.

Индексированные представления.
Индексированным
называется
материализованное
представление. Это значит, что оно создано и сохранено.
Индексировать представление можно, создав для него
уникальный кластеризованный индекс.
Индексированные представления значительно повышают
производительность некоторых типов запросов.
Индексированные представления эффективнее всего
использовать в запросах, группирующих множество строк.
Они не очень хорошо подходят для часто обновляющихся
базовых наборов данных.
17

18.

Создание индексированного представления
Индексированное представление создается в два этапа:
1. Создается представление посредством инструкции
CREATE VIEW с предложением SCHEMABINDING.
2. Создается кластеризованный индекс для этого
представления.

19.

В то время как обычное представление хранит
инструкцию
SQL
SELECT
и
данные
не
материализуются до тех пор, пока представление не
будет вызвано, индексированное представление
хранит копию данных в кластеризованном индексе.
Обычное
создание
индексированных
представлений
без
полного
анализа
их
использования
запросами
скорее
навредит
производительности, чем улучшит ее.
19

20.

У СУБД есть две возможности реализации
представлений. Если его определение простое, то
система
формирует
каждую
запись
представления
по
мере
необходимости,
постепенно считывая исходные данные из
базовых таблиц.
В случае сложного определения СУБД приходится
сначала выполнить такую операцию, как
материализация представления, т.е. сохранить
информацию, из которой состоит представление,
во
временной
таблице.
Затем
система
приступает к выполнению пользовательской
команды и формированию ее результатов, после
чего временная таблица удаляется.
20

21.

Представление создается командой CREATE
VIEW, после которой указывается имя
представления, а затем следует запрос,
формирующий тело представления.
21

22.

Синтаксис :
CREATE VIEW view_name [(column_list)]
[WITH {ENCRYPTION | SCHEMABINDING ]
AS select_statement
[WITH CHECK OPTION]
Инструкция CREATE VIEW должна быть единственной
инструкцией пакета. (Это означает, что эту
инструкцию следует отделять от других инструкций
посредством инструкции GO.)
22

23.

Параметр view_name задает имя определяемого
представления, а в параметре column_list указывается
список имен, которые будут использоваться в качестве
имен столбцов представления. Если этот необязательный
параметр опущен, то используются имена столбцов
таблиц, по которым создается представление.
Параметр select_statement задает инструкция SELECT,
которая извлекает строки и столбцы из таблиц (или других
представлений).
23

24.

Параметр
WITH
ENCRYPTION
задает шифрование инструкции
SELECT, повышая таким образом
уровень безопасности системы
баз данных.
24

25.

Предложение
WITH
CHECK
OPTION
определяет, что вставки
и обновления,
выполняемые посредством представления,
не могут создавать строки, которые
представление не может выбрать.
Это позволяет ограничениям целостности и
проверкам допустимости осуществляться на
вставляемых или обновленных данных.
25

26.

Предложение SCHEMABINDING привязывает представление к
схеме таблицы, по которой оно создается.
Любая попытка модифицировать структуру представлений или
таблиц, на которые ссылается созданное таким образом
представление, будет неудачной. Чтобы такие таблицы или
представления можно было модифицировать (инструкцией
ALTER) или удалять (инструкцией DROP), нужно удалить это
представление
или
убрать
из
него
предложение
SCHEMABINDING.
Когда это предложение указывается, имена объектов баз
данных в инструкции SELECT должны состоять из двух частей,
т.е. в виде schema.db_object, где schema - владелец, а db_object
может быть таблицей, представлением или определяемой
пользователем функцией.
26

27.

Инструкция SELECT в представлении
не может содержать предложение
ORDER BY или параметр INTO
27

28.

Создание представлений рассматривается на примере
БД, содержащей следующие таблицы:
STUDENTS (NOM_ZACH, SFAM, SNAME,
STIP)
USP (NOM_ZACH, PKOD, TNUM,UDATE,
MARK)
PREDM (PKOD, PNAME, TNUM, HOURS,
COURS)
PREP (TNUM,TFAM,TNAME, TSIRNAME)
28

29.

Например, для создания представления о
студентах, получивших стипендию в
размере выше 100 рублей можно
использовать следующую команду:
CREATE VIEW OTLSTUD
AS SELECT *
FROM STUDENTS
WHERE STIP > 100;
29

30.

В БД теперь существует представление с
именем
OTLSTUD,
которое
можно
использовать также как любую таблицу.
Это
представление
является
горизонтальным, так как оно позволяет
ограничить доступ пользователей к
определенным строкам.
30

31.

Преимущество
представления
по
сравнению с запросами к БД заключается
в том, что оно будет модифицировано
автоматически всякий раз, когда таблица,
лежащая в его основе изменяется.
Например, если появится еще один
студент со стипендией выше 100, он
автоматически
отобразиться
в
представлении.
31

32.

Вертикальные
представления
позволяют
ограничить доступ пользователей к нескольким
столбцам таблицы, исключив некоторые поля.
Например, для того, чтобы скрыть данные о
стипендии, надо отобрать в таблицу все поля,
исключая поле STIP.
CREATE VIEW STIPOFF
AS SELECT
NOM_ZACH, SFAM,
SNAME
FROM STUDENTS;
32

33.

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

34.

Имена, которые необходимо присвоить
полям, записываются в круглых скобках
после имени представления. Они могут
не указываться, если совпадают с
именами полей запрашиваемой таблицы
34

35.

Представление можно создать из другого
представления, как показано в примере:
CREATE VIEW OTLSTUD_А
AS SELECT *
FROM OTLSTUD
WHERE SFAM LIKE ’A %’;
Представление OTLSTUD_A в примере создается
из представления OTLSTUD. Все запросы,
использующие
представление
OTLSTUD_А,
преобразовываются в эквивалентные запросы
к базовой таблице STUDENTS.
35

36.

Когда делается запрос к представлению, то на самом
деле система обращается к базовым таблицам.
Например, при выполнении команды
SELECT *
FROM OTLSTUD
WHERE NOM_ZACH = ‘201201’;
СУБД фактически осуществляет следующий запрос:
SELECT *
FROM STUDENTS
WHERE STIP >100 and NOM_ZACH=‘201201’;
36

37.

Иногда возникают ситуации, когда
появляются проблемы с запросами к
представлениям в результате комбинации
из допустимых предикатов, которые не
будут работать.
37

38.

Создадим представление, которое
содержит данные о количестве
студентов, получающих ту или иную
стипендию:
STUDENTS (NOM_ZACH, SFAM, SNAME, STIP)
SELECT STIP, COUNT (*)
FROM STUDENTS
GROUP BY STIP
38

39.

CREATE VIEW STIPCOUNT(STIP,KOL)
AS SELECT STIP, COUNT (*)
FROM STUDENTS
GROUP BY STIP;
Теперь каждый раз, когда требуется
определить
количество
студентов,
получающих ту или иную стипендию,
достаточно просто выбрать все записи
рассматриваемого представления вместо
того, чтобы создавать достаточно сложный
запрос.
39

40.

Теперь обратимся к этому представлению, чтобы
выяснить, есть ли такой размер стипендии, который
получен менее чем 10 студентами:
SELECT *
FROM STIPCOUNT
WHERE KOL <10;
Если запрос к представлению преобразовать к запросу к
исходной таблице, то, скорее всего, будет получено
SELECT STIP, COUNT(*)
FROM STUDENTS
WHERE COUNT(*)<10
GROUP BY STIP;
40

41.

Последний запрос недопустим, так как
агрегатные
функции
не
могут
использоваться в предложении WHERE.
Возникает закономерное предположение о
том, что СУБД в некоторых случаях не
может правильно сформулировать запрос к
базовым таблицам, а значит выполнить
его.
41

42.

Все зависит от того, каким образом и по каким
алгоритмам система интерпретирует пользовательские
команды. Правильным способом формирования
приведенного выше запроса будет такой:
SELECT STIP, COUNT(*)
FROM STUDENTS
GROUP BY STIP
HAVING COUNT(*)<10;
Однако SQL может не выполнить такого превращения. В
этом случае следует проверить, справляется ли
используемая СУБД с аналогичными запросами, и в
соответствии с этим формировать запросы к
42
представлениям.

43.

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

44.

STUDENTS (NOM_ZACH, SFAM,
SNAME, STIP)
USP (NOM_ZACH, PKOD, TNUM,
UDATE, MARK)
PREDM (PKOD, PNAME, TNUM,
HOURS, COURSE)
44

45.

CREATE VIEW STUDOCEN
AS SELECT STUDENTS.SFAM,
PREDM.PNAME, USP.MARK
FROM STUDENTS, PREDM, USP
WHERE
STUDENTS.NOM_ZACH=USP.NOM_ZACH
AND USP.PKOD= PREDM.PKOD;
45

46.

С помощью такого представления можно
выводить оценки для любого студента по его
фамилии, например,
SELECT *
FROM STUDOCEN
WHERE SFAM=”Иванов”;
46

47.

Допускается
соединение
представлений с другими базовыми
таблицами или представлениями.
47

48.

В представлениях могут использоваться подзапросы.
Например, если необходимо вывести в представление
все оценки со значениями выше средней оценки по
каждой дисциплине:
CREATE VIEW USP1
AS SELECT *
FROM USP A
WHERE MARK > (SELECT AVG(MARK)
FROM USP B
WHERE A .PCOD=B.PCOD);
48

49.

Извлечение
данных
осуществляется запросом:
SELECT * FROM USP1
49

50.

Запросы на модификацию
представлений
50

51.

Представление
можно
изменять
командами модификации DML, но
фактически,
команды
будут
перенаправлены к базовой таблице.
Например, выполнение команды
UPDATE STIPOTL
SET SNAME=”Степан”
WHERE NOM_ZACH= ‘201201’;
аналогично выполнению той же команды для
таблицы STUDENTS
51

52.

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

53.

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

54.

Если
команды
модификации
могут
выполняться в представлении, то оно
считается модифицируемым: в противном
случае оно предназначено только для чтения.
Критерии, по которым определяют, является
ли представление модифицируемым в SQL:
54

55.

• Представление должно основываться только
на одной базовой таблице.
• Оно должно содержать первичный ключ этой
таблицы.
• Оно не должно содержать никаких полей,
которые
являлись
бы
агрегатными
функциями
или
вычисляемыми
выражениями.
• Представление не должно содержать GROUP
BY или HAVING в своем определении.
• Желательно, чтобы оно не использовало в
своем определении подзапросы.
55

56.

Таким
образом,
модифицируемые
представления
фактически
подобны
фрагментам базовых таблиц, отображая
определенную часть их содержимого.
Цели,
для
которых
создают
модифицируемые и не модифицируемые
представления
различны:
модифицируемые
представления,
в
основном используются точно также как
базовые таблицы.
56

57.

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

58.

Немодифицируемые представления
позволяют получать информацию
более рационально, выполнять
достаточно
сложные
запросы,
которые
можно
получать
многократно, не используя сложных
предикатов.
58

59.

Примеры
1. Модифицируемое представление.
Создать представление для вывода успеваемости по
математике:
CREATE VIEW MATEMUSP
AS SELECT *
FROM USP
WHERE PKOD=2003:
59

60.

2. Представление Только для чтения (возможно удаление):
CREATE VIEW
IDXSTIP(NOM_ZACH,SFAM,NEWSTIP)
AS SELECT NOM_ZACH, SFAM,STIP*2
FROM STUDENTS
WHERE STIP=25;
Представление
является
не
(исключая DELETE), так как
присутствует выражение STIP*2.
модифицируемым
в определении
60

61.

Представление, выводящее информацию о студентах,
получивших оценки в определенный день, будет только для
чтения:
CREATE VIEW DATEOC
AS SELECT *
FROM STUDENTS
WHERE NOM_ZACH IN
(SELECT NOM_ZACH
FROM USP
WHERE UDATE=08.01.2001);
61

62.

Следующее представление является модифицируемым:
CREATE VIEW DATEOC
AS SELECT *
FROM USP
WHERE UDATE IN(08.01.2011, 09.01.2011);
62

63.

Рассмотрим
использование
представлений с тремя инструкциями
модификации данных DML: INSERT,
UPDATE и DELETE.
Для представления, используемого для
вставки, модифицирования и удаления
данных из таблицы, на основе которой
оно создано, существуют некоторые
ограничения.
63

64.

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

65.

В примере создается представление STIPOFF,
которое содержит первые два столбца таблицы
STUDENTS. Последующая инструкция INSERT
вставляет строку в таблицу, связанную с
представлением. При этом столбцам, которые не
вошли в представление STIPOFF, присваивается
значение NULL:
CREATE VIEW STIPOFF
AS SELECT NOM_ZACH, SFAM
FROM STUDENTS;
GO
INSERT INTO STIPOFF
VALUES(‘2016000', 'Петров');
65

66.

В
некоторых
реализациях
SQL
представление
может
допускать
добавление данных (INSERT),
только
если все поля таблицы-источника, не
присутствующие
в
представлении,
имеют
значения
по
умолчанию.
66

67.

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

68.

Например, создадим представление
CREATE VIEW OTLSTUD
AS SELECT *
FROM STUDENTS
WHERE STIP> 200
и вставим строку со значением поля стипендия 100
INSERT INTO OTLSTUD (NOM_Zach, STIP)
VALUES (‘2012000', 100)
Строка будет вставлена в базовую таблицу и команда
SELECT * FROM STUDENTS
вернет строку со значением стипендии равным 100, а команда
SELECT * FROM OTLSTUD
не вернет эту строку, поскольку она не удовлетворяет условию WHERE
представления OTLSTUD
68

69.

Чтобы ограничить вставку в представление только
строками,
которые
удовлетворяют
условиям
представления, применяется предложение WITH CHECK
OPTION.
Если представление OTLSTUD будет создано с включением предложения
WITH CHECK OPTION то попытка вставки в представление командой
INSERT INTO OTLSTUD1 (NOM_Zach,STIP VALUES (‘2012000',100)
вызовет сообщение об ошибке.
При использовании предложения WITH CHECK OPTION компонент Database
Engine проверяет каждую вставляемую строку на удовлетворение условий
предложения WHERE. запроса, создающего представление.
Если это предложение отсутствует, такая проверка не выполняется,
вследствие чего каждая вставляемая в представление строка также
вставляется в таблицу, на которой оно основано. Это может вызвать
путаницу, когда строка вставляется в представление, но впоследствии не
возвращается из этого представления инструкцией SELECT, т.к. для нее
выполняются условия предложения WHERE.
69

70.

Вставку строк в таблицу, на которой основано
представление, нельзя выполнить, если это
представление содержит одну из следующих
возможностей:
• предложение FROM в определении представления
содержит более чем одну таблицу, и список
столбцов содержит столбцы более чем из одной
таблицы;
• столбец в представлении создается из агрегатной
функции;
• инструкция SELECT в представлении содержит
предложение GROUP BY или параметр DISTINCT;
• столбец в представлении создается с помошью
выражения.
70

71.

Обновление данных с помощью представления
Инструкцию UPDATE можно применять с представлением, как
будто бы это базовая таблица. При модифицировании строк
представления
модифицируется
содержимое
таблицы,
лежащей в его основе.
Предложение WITH CHECK OPTION действует также как при
выполнении инструкции INSERT, то есть компонент Database
Engine проверяет, будет ли измененное значение столбца
давать значение заданное в условии предложения WHERE
инструкции
SELECT.
Попытка
изменения
значения
завершается неудачей, если условие не удовлетворяется. (т.е.
выполняется попытка изменить
значение стипендия на
значение < 200, если в представлении отбираются значения
>200).
71

72.

CREATE VIEW OTLSTUD
AS SELECT *
FROM STUDENTS
WHERE STIP> 200
72

73.

В некоторых реализациях SQL через
представление, основанное на нескольких
таблицах, можно обновлять таблицу, но
только одну за запрос, т.е. конструкция SET
оператора UPDATE должна перечислять
столбцы только одной таблицы из
определения представления.
Кроме того, чтобы представление, основанное на
нескольких таблицах, было обновляемым,
таблицы в его определении должны быть
объединены только с помощью INNER JOIN, а не
OUTER JOIN или UNION.
73

74.

Удаление данных с помощью
представления
С помощью представления можно
удалять строки из таблицы, на которой
оно основано.
В отличие от инструкций INSERT и
UPDATE, инструкция DELETE допускает
значения, получаемые из констант или
выражений, в столбце представления,
используемого для удаления строк из
таблицы, на которой оно основано.
74

75.

Удаление
данных
из
представлений,
основанных
более чем на одной таблице, не
поддерживается.
75

76.

Синтаксис удаления представления
из БД подобен удалению базовых
таблиц:
DROP VIEW <имя
представления>
76