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

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

Для чего нужны представления? Самая важная причина — это обеспечение логической независимости данных. Если физическая независимость данных обеспечивает независимость от изменений физической структуры базы данных, то логическая независимость обеспечивает независимость пользовательских программ от изменения логической структуры базы данных при ее реструктуризации. Кроме того, представления дают возможность различным пользователям по-разному видеть одни и те же данные, возможно, даже в одно и то же время. Это особенно ценно при работе различных категорий пользователей с единой интегрированной базой данных. Пользователям предоставляют только интересующие их данные в наиболее удобной для них форме (окно в таблицу или в любое соединение любых таблиц).

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

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

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

Обобщенный синтаксис команды создания представления имеет следующий вид

CREATE VIEW <имя представления>

[<список имен столбцов представления^

AS <подзапрос>

[WITH CHECK OPTION];

Здесь подзапрос, следующий за AS, является определением данного представления, не исполняется, а просто сохраняется в каталоге.

Необязательная фраза WITH CHECK OPTION (с проверкой) указывает на то, что для операций INSERT и UPDATE над этим представлением должна осуществляться проверка, обеспечивающая удовлетворение WHERE фразы подзапроса.

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

  • а) когда хотя бы один из столбцов подзапроса не имеет имени (создается с помощью выражения, SQL-функции или константы);
  • б) два или более столбцов подзапроса имеют одно и то же имя.

Если же список отсутствует, то представление наследует имена

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

Как обычно, подзапрос не может включать ни команду UNION, ни фразу ORDER BY.

Инструкция SELECT, определяющая представление, не должна содержать каких-либо параметров.

Имя представления не должно совпадать с именем таблицы.

Если запрос, определенный инструкцией SELECT, является обновляемым, то представление также может обновляться. В противном случае оно предназначено только для чтения.

С учетом этих ограничений любая таблица, выборка которой может быть осуществлена с помощью предложения SELECT, может быть определена как представление. Рассмотрим конкретные варианты:

1. Представление может быть создано с вычислением арифметического выражения.

Пример 78. Сформировать как представление данные о годовых зарплатах сотрудников.

CREATE VIEW Годовая_зарплата_сотрудников ([табельный номер], фамилия, [годовая зарплата])

AS SELECT таб_номер, фамилия, зарплата* 12 FROM Сотрудник;

Представление «Годовая_зарплата_сотрудников»

табельный номер

фамилия

годовая зарплата

101

Прохоров П. П.

420000,00 р.

102

Семенов С. С.

300000,00 р.

105

Петров П. П.

300000,00 р.

153

Сидорова С. С.

198000,00 р.

201

Андреев А. А.

420000,00 р.

202

Борисов Б. Б.

300000,00 р.

241

Глухов Г. Г.

264000,00 р.

242

Чернов Ч.Ч.

198000,00 р.

301

Басов Б. Б.

420000,00 р.

302

Сергеева С. С.

300000,00 р.

401

Волков В. В.

420000,00 р.

402

Зайцев 3.3.

300000,00 р.

403

Смирнов С. С.

180000,00 р.

435

Лисин Л. Л.

264000,00 р.

501

Кузнецов К. К.

420000,00 р.

502

Романцев Р. Р.

300000,00 р.

503

Соловьев С. С.

300000,00 р.

601

Зверев 3.3.

420000,00 р.

602

Сорокина С. С.

300000,00 р.

614

Григорьев Г. Г.

264000,00 р.

В этом примере столбец представления «годовая зарплата» продуцируется на основе вычисления арифметического выражения.

2. Представление может формироваться на основе функции агрегации.

Пример 79. Сформировать как представление данные о месячном фонде зарплаты сотрудников.

CREATE VIEW Месячный_фонд_зарплаты_сотрудников ([фонд зарплаты])

AS SELECT Sum (зарплата)

FROM Сотрудник;

Представление «Месячный_фонд_зарплаты_сотрудников»

фонд зарплаты

524000,00 р.

фонд зарплаты

  • 524000,00~рГ
  • 3. Представление может формироваться как проекция базовой таблицы.

Пример 80. Сформировать как представление список шифров кафедр и должностей работающих на них сотрудников.

CREATE VIEW Должности_сотрудников (кафедра, должность)

AS SELECT DISTINCT шифр, должность FROM Сотрудник;

Представление «Должности_сотрудников»

кафедра

должность

вм

зав.кафедрой

вм

преподаватель

ис

зав.кафедрой

ис

инженер

ис

преподаватель

мм

зав.кафедрой

мм

преподаватель

оф

зав.кафедрой

оф

инженер

оф

преподаватель

пи

зав.кафедрой

пи

инженер

пи

преподаватель

эф

зав.кафедрой

эф

инженер

эф

преподаватель

В этом примере производится выборка столбцов таблицы «Сотрудник» без дублей строк. Имена столбцов представления «Должности_ сотрудников» «кафедра» и «должность» соответствуют столбцам выборки «шифр» и «должность» из базовой таблицы «Сотрудник».

Задание. Сформировать представление «Номера_студентов» как проекцию таблицы «Экзамен» на столбец «рег_номер». В списке должны быть представлены в единственном экземпляре регистрационные номера студентов, сдававших экзамены.

4. Представление может быть сформировано на основе простой операции селекции по условию.

Пример 81. Сформировать представление с именем «Инже- неры_вуза» на основе таблицы «Сотрудник» как выборку сотрудников, чья должность должна быть 'инженер'.

CREATE VIEW Инженеры_вуза ([табельный номер], кафедра, фамилия, должность, зарплата, начальник)

AS SELECT *

FROM Сотрудник

WHERE должность='инженер';

Представление «Инженеры_вуза»

табельный

номер

кафедра

фамилия

должность

зарплата

начальник

153

пи

Сидорова С. С.

инженер

16500,00 р.

102

241

ис

Глухов Г. Г.

инженер

22000,00 р.

201

242

ис

Чернов Ч.Ч.

инженер

16500,00 р.

202

435

оф

Лисин Л. Л.

инженер

22000,00 р.

402

614

эф

Григорьев Г. Г.

инженер

22000,00 р.

602

Задание. Сформировать представление с именем «Препода- ватели_вуза» на основе таблицы «Сотрудник» как выборку сотрудников, чья должность может быть или 'преподаватель' или 'зав.кафедрой'.

5. Основанием для формирования представления может быть запрос на группировку данных.

Пример 82. Сформировать как представление с именем «Гуп- пировка_сотрудников» результат группировки сотрудников по кафе- драм с представлением агрегатных характиристик «средняя зарплата» и «количество сотрудников». Имен, которые могли бы наследоваться от второго и третьего столбцов подзапроса, нет. Поэтому имена соответствующих столбцов представления должны быть заданы явным образом.

CREATE VIEW Гуппировка_сотрудников (кафедра, [средняя зарплата], [количество сотрудников])

AS SELECT шифр, AVG (зарплата), Count (зарплата)

FROM Сотрудник GROUP BY шифр;

Представление «Гуппировка_сотрудников»

кафедра

средняя зарплата

количество сотрудников

вм

28333,33 р.

3

ис

24625,00 р.

4

мм

30000,00 р.

2

оф

24250,00 р.

4

пи

25375,00 р.

4

эф

27333,33 р.

3

В отличие от предыдущих примеров представление «Группиров- ка_сотрудников» не является просто подмножеством базовой таблицы «Сотрудник». Оно представляет собой некоторый результат статистической обработки базовой таблицы.

Задание. Сформировать представление «Группировка_студен- тов_по_специальностям» со столбцами « [номер специальности]», «[количество студентов]» на основе запроса на группировку строк таблицы «Студент» по столбцу «номер» с вычислением агрегатной функции Count (рег_номер).

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

Пример 83. Сформировать представление с именем «Инженеры» на основе естественного соединения базовых таблиц «Сотрудник» и «Инженер» по столбцам связи «таб_номер». Имена столбцам представления присвоить по предлагаемому в примере образцу.

CREATE VIEW Инженеры ([табельный номер], кафедра, фамилия, должность, специальность, зарплата, начальник)

AS SELECT Сотрудник.таб_номер, шифр, фамилия, должность, специальность, зарплата, шеф

FROM Сотрудник, Инженер

WHERE Сотрудник.таб_номер=Инженер.таб_номер;

Представление «Инженеры»

табельный

номер

кафедра

фамилия

должность

специальность

зарплата

начальник

614

эф

Григорьев Г. Г.

инженер

программист

22000,00 р.

602

153

пи

Сидорова С. С.

инженер

электроник

16500,00 р.

102

241

ис

Глухов Г. Г.

инженер

электроник

22000,00 р.

201

242

ис

Чернов Ч.Ч.

инженер

программист

16500,00 р.

202

435

оф

Лисин Л. Л.

инженер

электроник

22000,00 р.

402

Задание. Сформировать представление «Выпускающие_кафе- дры» на основе внутреннего соединения базовых таблиц «Кафедра» и «Специальность» по столбцу связи «шифр». Результатом соединения будут данные только о тех кафедрах, на которых открыты специальности (о выпускающих кафедрах). В списке имен столбцов представления предусмотреть столбцы «кафедра», «название», « [номер специальности]», «направление», соответствующие столбцам соединенных таблиц: «шифр», «название», «номер», «направление».

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

Пример 84. Сформировать представление с именем «Сотруд- ники_факультета» на основе запроса с простым подзапросом. В подзапросе на основе таблицы «Кафедра» по аббревиатуре факультета (столбец «Кафедра.факультет) сформировать список шифров кафедр факультета, заданного явно по столбцу «факультет»='ит'. В объемлющем запросе произвести выборку всех данных о сотрудниках, у которых «шифры» попадают в список «шифров» кафедр данного факультета. Список имен столбцов представления задать по образцу демонстрационного примера.

CREATE VIEW Сотрудники_факультета ([табельный номер], [место работы], фамилия, должность, зарплата, начальник)

AS SELECT *

FROM Сотрудник

WHERE шифр IN (SELECT шифр FROM Кафедра WHERE факультет='ит');

Представление «Сотрудники_факультета»

табельный номер

место

работы

фамилия

должность

зарплата

начальник

101

пи

Прохоров П. П.

зав.кафедрой

35000,00 р.

101

102

пи

Семенов С. С.

преподаватель

25000,00 р.

101

105

пи

Петров П. П.

преподаватель

25000,00 р.

101

153

пи

Сидорова С. С.

инженер

16500,00 р.

102

201

ис

Андреев А. А.

зав.кафедрой

35000,00 р.

201

202

ис

Борисов Б. Б.

преподаватель

25000,00 р.

201

241

ис

Глухов Г. Г.

инженер

22000,00 р.

201

242

ис

Чернов Ч.Ч.

инженер

16500,00 р.

202

Задание. Сформировать представление с именем «Студенты_ кафедры» на основе запроса с простым подзапросом. В подзапросе на основе таблицы «Специальность» по шифру кафедры (столбец «Специальность.шифр) сформировать список номеров специальностей кафедры, заданной явно по столбцу «шифр»= 'пи'. В объемлющем запросе произвести выборку всех данных о студентах, у которых «номера» попадают в список «номеров» специальностей данной кафедры. В списке имен представления задать столбцы: «регистрационный номер», «фамилия», «специальность».

 
Посмотреть оригинал
< Пред   СОДЕРЖАНИЕ   ОРИГИНАЛ   След >