🗊Презентация Реализация представлений. (Лекция 8)

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

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

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

CREATE VIEW <Name of view > [(name of attributes),…] AS <SELECT …>; Пример 12.1. Создать представление о студентах, получающих стипендию в размере 25.50. CREATE VIEW STIP25_50 AS SELECT * FROM STUDENTS WHERE STIP=25.50;

Теперь в БД существует представление STIP25_50. Это такая же таблица, как и остальные. С ней можно выполнять запросы, изменения, вставки как с другими таблицами. При выполнении запроса к ней: Теперь в БД существует представление STIP25_50. Это такая же таблица, как и остальные. С ней можно выполнять запросы, изменения, вставки как с другими таблицами. При выполнении запроса к ней: SELECT * FROM STIP25_50; будет получена таблица.

При создании представлений можно часть информации скрыть. Пример 12.2. Создать представление о студентах без указания стипендии. CREATE VIEW STIPOFF AS SELECT SNUM, SFAM, SNAME, SFATH FROM STUDENTS;

При выполнении запроса к ней, будет получена таблица : При выполнении запроса к ней, будет получена таблица :

Представление теперь может изменяться также как и таблица, фактически же команда направлена к таблице STUDENTS. Пример 12.3. Изменить у студента с номером студенческого билета 3415 имя. UPDATE STIPOFF SET SNAME =’ Василий’ WHERE SNUM=3415; UPDATE STUDENTS SET SNAME =’ Василий’ WHERE SNUM=3415;

Но команда Но команда UPDATE STIPOFF SET STIP =100 WHERE SNUM=3415; будет отвергнута, так как поле STIP в представлении STIPOFF отсутствует.

Существуют ограничения на модификацию представлений. Чаще всего в представлении используются те же имена столбцов, что и в базовых таблицах. При использовании объединения может быть конфликт имен. Допустимо указание других имен в представлении в скобках после имени. Пример 12.4. CREATE VIEW STIPCOUNT(STIP, NUM) AS SELECT STIP, COUNT(*) FROM STUDENTS GROUP BY STIP;

Сделаем запрос к представлению: показать все данные о стипендиях, которые получают 2 и более человек. Сделаем запрос к представлению: показать все данные о стипендиях, которые получают 2 и более человек. SELECT * FROM STIPCOUNT WHERE NUM>=2; Но не допускается функция в предикате WHERE: SELECT STIP, COUNT(*) FROM STUDENTS WHERE COUNT(*) >= 2 GROUP BY STIP; Правильная команда: SELECT STIP, COUNT(*) as NUM FROM STUDENTS GROUP BY STIP HAVING COUNT(*)>=2;

Представления могут основываться на двух и более таблицах. Пример 12.5.Создать представление о студентах и их оценках. CREATE VIEW STUDMARK AS SELECT C.UNUM, A.SFAM, B.PNAME, C.MARK FROM STUDENTS A, PREDMET B, USP C WHERE A.SNUM=C.SNUM AND B.PNUM=C.PNUM;

После этого легче ориентироваться в оценках: После этого легче ориентироваться в оценках: SELECT * FROM STUDMARK;

Допускается соединение представления с базовыми таблицами: Допускается соединение представления с базовыми таблицами: SELECT SFAM, PNAME, MARK, UDATE FROM STUDMARK A, USP B WHERE A.SFAM= ‘Поляков’ AND A.UNUM=B.UNUM;

Представления допускают соотнесенные подзапросы. Пример12.6.Пусть в таблице USP

Создадим представление об оценках превышающих среднюю. Создадим представление об оценках превышающих среднюю. 1)CREATE VIEW AVGMARK AS SELECT * FROM USP A WHERE MARK>(SELECT AVG(MARK) FROM USP B WHERE B.PNUM= A.PNUM); 2)SELECT * FROM AVGMARK;

Существуют ограничения на построения представлений: Существуют ограничения на построения представлений: 1) в них не допускаются объединения UNION запросов; 2)  не допустимо упорядочение ORDER BY, так как в базовых таблицах записи не упорядочены.

Для удаления представлений используется команда DROP VIEW <Name of view>; Для удаления представления не требуется удалять все данные, потому что реально они в нем не содержатся. DROP VIEW AVGMARK;

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

Рассмотрим критерии, по которым мы определяем, является ли представление модифицируемым: Рассмотрим критерии, по которым мы определяем, является ли представление модифицируемым: 1)   представление должно основываться только на одной таблице; 2)  оно должно содержать первичный ключ этой таблицы; 3)   представление  не должно иметь полей - агрегатных функций;

4)  представление  не должно использовать DISTINCT; 4)  представление  не должно использовать DISTINCT; 5)  представление  не должно использовать GROUP BY, HAVING; 6)     представление  не должно использовать подзапросы; 7)     представление  не должно использовать константы, строки, выражения среди полей вывода; 8)   для команды INSERT оно может содержать любые поля базовой таблицы, для которой имеются ограничения NOT NULL, если другое значение по умолчанию не определено.

Модификация представлений подобна фрагментации базовых таблиц. Модификация представлений подобна фрагментации базовых таблиц. Пример 12.7. CREATE VIEW PRCOUNT (UDATE, COL) AS SELECT UDATE, COUNT(*) FROM USP GROUP BY UDATE; Это представление не модифицируемо - GROUP BY.

Пример 12.8. Пример 12.8. CREATE VIEW MATEMUSP AS SELECT * FROM USP WHERE PNUM = 2003; Это представление – модифицируемо.

Другой результат достигается на представлении: Другой результат достигается на представлении: 1)CREATE VIEW ONLY5 AS SELECT SNUM, MARK FROM USP WHERE MARK = 5; 2)INSERT INTO ONLY5 VALUES (3415, 4); Это допустимая команда, в таблицу эти значения будут вставлены, но на экране не появятся.

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

Если его добавить к команде: Если его добавить к команде: CREATE VIEW ONLY5 AS SELECT SNUM, MARK FROM USP WHERE MARK = 5 WITH CHECK OPTION; То любое значение отличное от указанных будет отключено.

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

CREATE VIEW DATEMARK CREATE VIEW DATEMARK AS SELECT SNUM, SFAM FROM STUDENTS WHERE SNUM IN ( SELECT SNUM FROM USP WHERE UDATE = 10.06.2002); Это представление для чтения – имеется подзапрос.

Что будет если пользователь решит добавить запись: Что будет если пользователь решит добавить запись: INSERT INTO DATEMARK VALUES (3415,’Котенко’); Часть данных будет заполнена как NULL. Проблема не решится если применить WITH CHECK OPTION, так как представление станет модифицируемым и удаляемым, но без вставки.

Пример 12.9. Пример 12.9. CREATE VIEW STIPSTUD AS SELECT SNUM, SFAM, STIP FROM STUDENTS WHERE STIP>0 WITH CHECK OPTION; То вставка будет не удачна. INSERT INTO STIPSTUD VALUES (3417, Решетник, 0.00); 3)однако в NEW1 она возможна. CREATE VIEW NEW1 AS SELECT * FROM STIPSTUD;

Вставка выполнится.Это означает, что любое корректное представление модифицируемо. Даже если: Вставка выполнится.Это означает, что любое корректное представление модифицируемо. Даже если: CREATE VIEW NEW1 AS SELECT * FROM STIPSTUD WITH CHECK OPTION;

Лекция № 11 Views – Представления Представления. Представление VIEW – это именованная виртуальная таблица, содержание которой выбирается из других таблиц с помощью запросов. При изменении значений в таблицах автоматически меняются значения представления. Наличие имени у такой таблицы позволяет пользователю выполнять с ней операции аналогичные операциям с базовыми таблицами.

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

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

CREATE VIEW <Name of view > [(name of attributes),…] AS <SELECT …>; Пример 12.1. Создать представление о студентах, получающих стипендию в размере 25.50. CREATE VIEW STIP25_50 AS SELECT * FROM STUDENTS WHERE STIP=25.50;

Теперь в БД существует представление STIP25_50. Это такая же таблица, как и остальные. С ней можно выполнять запросы, изменения, вставки как с другими таблицами. При выполнении запроса к ней: Теперь в БД существует представление STIP25_50. Это такая же таблица, как и остальные. С ней можно выполнять запросы, изменения, вставки как с другими таблицами. При выполнении запроса к ней: SELECT * FROM STIP25_50; будет получена таблица.

При создании представлений можно часть информации скрыть. Пример 12.2. Создать представление о студентах без указания стипендии. CREATE VIEW STIPOFF AS SELECT SNUM, SFAM, SNAME, SFATH FROM STUDENTS;

При выполнении запроса к ней, будет получена таблица : При выполнении запроса к ней, будет получена таблица :

Представление теперь может изменяться также как и таблица, фактически же команда направлена к таблице STUDENTS. Пример 12.3. Изменить у студента с номером студенческого билета 3415 имя. UPDATE STIPOFF SET SNAME =’ Василий’ WHERE SNUM=3415; UPDATE STUDENTS SET SNAME =’ Василий’ WHERE SNUM=3415;

Но команда Но команда UPDATE STIPOFF SET STIP =100 WHERE SNUM=3415; будет отвергнута, так как поле STIP в представлении STIPOFF отсутствует.

Существуют ограничения на модификацию представлений. Чаще всего в представлении используются те же имена столбцов, что и в базовых таблицах. При использовании объединения может быть конфликт имен. Допустимо указание других имен в представлении в скобках после имени. Пример 12.4. CREATE VIEW STIPCOUNT(STIP, NUM) AS SELECT STIP, COUNT(*) FROM STUDENTS GROUP BY STIP;

Сделаем запрос к представлению: показать все данные о стипендиях, которые получают 2 и более человек. Сделаем запрос к представлению: показать все данные о стипендиях, которые получают 2 и более человек. SELECT * FROM STIPCOUNT WHERE NUM>=2; Но не допускается функция в предикате WHERE: SELECT STIP, COUNT(*) FROM STUDENTS WHERE COUNT(*) >= 2 GROUP BY STIP; Правильная команда: SELECT STIP, COUNT(*) as NUM FROM STUDENTS GROUP BY STIP HAVING COUNT(*)>=2;

Представления могут основываться на двух и более таблицах. Пример 12.5.Создать представление о студентах и их оценках. CREATE VIEW STUDMARK AS SELECT C.UNUM, A.SFAM, B.PNAME, C.MARK FROM STUDENTS A, PREDMET B, USP C WHERE A.SNUM=C.SNUM AND B.PNUM=C.PNUM;

После этого легче ориентироваться в оценках: После этого легче ориентироваться в оценках: SELECT * FROM STUDMARK;

Допускается соединение представления с базовыми таблицами: Допускается соединение представления с базовыми таблицами: SELECT SFAM, PNAME, MARK, UDATE FROM STUDMARK A, USP B WHERE A.SFAM= ‘Поляков’ AND A.UNUM=B.UNUM;

Представления допускают соотнесенные подзапросы. Пример12.6.Пусть в таблице USP

Создадим представление об оценках превышающих среднюю. Создадим представление об оценках превышающих среднюю. 1)CREATE VIEW AVGMARK AS SELECT * FROM USP A WHERE MARK>(SELECT AVG(MARK) FROM USP B WHERE B.PNUM= A.PNUM); 2)SELECT * FROM AVGMARK;

Существуют ограничения на построения представлений: Существуют ограничения на построения представлений: 1) в них не допускаются объединения UNION запросов; 2)  не допустимо упорядочение ORDER BY, так как в базовых таблицах записи не упорядочены.

Для удаления представлений используется команда DROP VIEW <Name of view>; Для удаления представления не требуется удалять все данные, потому что реально они в нем не содержатся. DROP VIEW AVGMARK;

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

Рассмотрим критерии, по которым мы определяем, является ли представление модифицируемым: Рассмотрим критерии, по которым мы определяем, является ли представление модифицируемым: 1)   представление должно основываться только на одной таблице; 2)  оно должно содержать первичный ключ этой таблицы; 3)   представление  не должно иметь полей - агрегатных функций;

4)  представление  не должно использовать DISTINCT; 4)  представление  не должно использовать DISTINCT; 5)  представление  не должно использовать GROUP BY, HAVING; 6)     представление  не должно использовать подзапросы; 7)     представление  не должно использовать константы, строки, выражения среди полей вывода; 8)   для команды INSERT оно может содержать любые поля базовой таблицы, для которой имеются ограничения NOT NULL, если другое значение по умолчанию не определено.

Модификация представлений подобна фрагментации базовых таблиц. Модификация представлений подобна фрагментации базовых таблиц. Пример 12.7. CREATE VIEW PRCOUNT (UDATE, COL) AS SELECT UDATE, COUNT(*) FROM USP GROUP BY UDATE; Это представление не модифицируемо - GROUP BY.

Пример 12.8. Пример 12.8. CREATE VIEW MATEMUSP AS SELECT * FROM USP WHERE PNUM = 2003; Это представление – модифицируемо.

Другой результат достигается на представлении: Другой результат достигается на представлении: 1)CREATE VIEW ONLY5 AS SELECT SNUM, MARK FROM USP WHERE MARK = 5; 2)INSERT INTO ONLY5 VALUES (3415, 4); Это допустимая команда, в таблицу эти значения будут вставлены, но на экране не появятся.

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

Если его добавить к команде: Если его добавить к команде: CREATE VIEW ONLY5 AS SELECT SNUM, MARK FROM USP WHERE MARK = 5 WITH CHECK OPTION; То любое значение отличное от указанных будет исключено.

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

CREATE VIEW DATEMARK CREATE VIEW DATEMARK AS SELECT SNUM, SFAM FROM STUDENTS WHERE SNUM IN ( SELECT SNUM FROM USP WHERE UDATE = 10.06.2002); Это представление для чтения – имеется подзапрос.

Что будет если пользователь решит добавить запись: Что будет если пользователь решит добавить запись: INSERT INTO DATEMARK VALUES (3415,’Котенко’); Часть данных будет заполнена как NULL. Проблема не решится если применить WITH CHECK OPTION, так как представление станет модифицируемым и удаляемым, но без вставки.

Пример 12.9. Пример 12.9. CREATE VIEW STIPSTUD AS SELECT SNUM, SFAM, STIP FROM STUDENTS WHERE STIP>0 WITH CHECK OPTION; То вставка будет не удачна. INSERT INTO STIPSTUD VALUES (3417, Решетник, 0.00); 3)однако в NEW1 она возможна. CREATE VIEW NEW1 AS SELECT * FROM STIPSTUD;