Лекция № 7
Тема: "Работа с представлениями"
План
1. Понятие представления
2. Создание и использование представлений
3. Изменение и удаление представлений
4. Работа с представлениями в среде SQL Server Management Studio
5. Работа с представлениями в среде SQL Manager for MySQL
1. Понятие представления
Часто при работе с БД пользователю приходится часто вводить тексты одних и тех же запросов для выборки данных. Было бы удобно иметь какой-то способ сохранения таких запросов для будущего использования.
Подобный подход реализован в Microsoft Access, где каждый запрос сохраняется как отдельный объект базы данных, и для повторного выборки достаточно открыть этот объект как обычную таблицу.Такой же подход можно реализовать в любой СУБД с помощью представлений.
Представление – это виртуальная таблица, которая физически отсутствует в базе данных и строится на основе текста запроса. Такая таблица может отобразить только нужные столбцы, скрыв от пользователя не нужные или секретные, можно отобразить данные из нескольких связанных таблиц, выполнить расчеты, группировки и т.п. Причем представление будет храниться в базе данных как отдельный объект, который можно открыть как обычную таблицу, без необходимости вводить текст запроса.
Представление имеет ряд преимуществ:
- легкое многократное выполнение запросов любой сложности без необходимости вводить сам текст запроса;
- возможность использования имени представления в других запросах, что фактически означает использование подзапроса, но с более короткой записью (вместо самого текста подзапроса можно указать имя представления);
- повышение защищенности базы данных. Можно организовать работу таким образом, что пользователь не будет иметь доступа к таблицам, а сможет работать только через представления.
Представление имеет ряд ограничений:
- в запросе, на основе которого создается представление, нельзя задавать сортировку данных. Параметры сортировки нужно указывать при отборе данных с помощью запроса;
- если в представлении отображаются данные из нескольких связанных таблиц, то такое представление не является редактируемым. Его можно только просматривать;
- представление не может включать в себя запросы, объединенные операцией UNION;
- представления в MySQL не могут содержать подзапросы, но могут вместо подзапросов использовать другие представления.
2. Создание и использование представлений
Для создания представления используют команду вида:
Сreate View имя_представления
As
текст_запроса
[Where условие
[With Check Option]]
Пример. Создать представление, которое выведет ФИО всех студентов в алфавитном порядке
Create View stud_list
As
Select fam, imya, otch
From students
После выполнения такой команды в базе данных будет создан новый объект с именем "stud_list", но сами данные выведены на экран не будут.
Пример. Используя созданное представление, вывести список студентов на экран с сортировкой по фамилии. Если рассматривать представление как таблицу, то вывести из нее данные можно так:
Select *
From stud_list
Order by fam
Пример. Для каждой группы учебного заведения рассчитать количество студентов.
Create View kol_stud_grup
As
Select grupa, count(students.id_stud) as kol
From grupy inner join students
on grupy.id_grup=students.id_grup
Group by grupa
Пример. Выведем количество студентов по группам.
Select *
From kol_stud_grup
Если в запросе представления указан раздел задания условий Where, то вместе с ним можно задать параметр . Данный параметр указывает, что если при редактировании или добавлении данных в представление вводить данные, не удовлетворяющие ограничениям Where, то такие данные не сохраняются. Если же задано ограничение Where, но не указан параметр With Check Option, то при редактировании или добавлении данных в представление никаких онраничений на ввод не существует.
Пример. Пусть имеется представление, которое отбирает студентов мужского пола.
Create View list_stud_men
As
Select *
From students
Where pol='м'
With Check Option
Если в даное представление попытаться ввести данные с полом, отличным от 'м', то такие данные не сохраняются.
Использование вложенных представлений
Как говорилось выше в представлении нельзя использовать подзапросы, но можно использовать другие представления.
Пример. Для каждого студента вывести размер стипендии, рассчитываемый по формуле:
- 1500, средний балл 5;
- 1300, средний балл от 4 и выше;
- 0, в противном случае.
Стипендию получают только студенты, обучающиеся на бюджете.
Вначале создадим представление, в котором отберем средний балл каждого студента.
Create View stud_srbal
As
Select id_stud, avg(ocenka) as srbal
From ocenki
Group by id_stud
Имея представление, свяжем его как таблицу с таблицей "students" и рассчитаем стипендию для каждого студента.
Create View stip_list
As
Select fam, imya, case when srbal=5 then 600
when srbal>=4 then 550
else 0 end as stip
From students inner join stud_srbal
on students.id_stud=stud_srbal.id_stud
Where finance='Бюджет'
Order by fam
Выведем список стипендиатов.
Select *
From stip_list
3. Изменение и удаление представлений
Для внесения изменений в представление использую команду вида:
Alter View имя_представления
As
текст_запроса
[Where условие
[With Check Option]]
Указывается имя существующего представления, а затем новый запрос с новыми параметрами.
Для удаления представления используется команда:
Drop View имя_представления
Необходимо указать имя существующего представления.
4. Работа с представлениями в среде SQL Server Management Studio
При работе в "Среде SQL Server Management Studio" список представлений, имеющихся в базе данных, находится в папке Представления. Для создания нового представления достаточно выбрать в контекстном меню папки команду "Создать представление". В ответ откроется новое окно, в котором нужно выбрать таблицы, по которым будет строиться представление.
Окно конструктора представлений похоже на окно конструктора запросов в Access.
В верхней части окна выберите поля, которые вы хотите поместить в представление.
В следующем разделе для каждого выбранного поля можно задать параметры: псевдоним для поля, тип сортировки и условия для отбора по полю (фильтр).
Ниже расположено текстовое поле, в котором можно увидеть текст запроса, который автоматически генерируется при задании пользователем нужных параметров.
С помощью кнопки "Выполнить код SQL" можно проверить результат работы сформированного запроса, который отображается в нижней части окна конструктора.
После задания всех параметров сохраните представление для использования в будущем.
Изменение представления
Для редактирования представления следует открыть папку Представления, найти имя нужного представления и в его контекстном меню выбрать команду Проект. В ответ откроется окно конструтора для задания новых параметров.
Просмотр зависимостей
Для просмотра зависимостей между представлением и другими объектами базы данных следует в контекстном меню представления выбрать команду "Просмотреть зависимости". В окне с помощью переключателей можно просмотреть объекты, который зависят от представления, или объекты, от которых зависит само представление.
Открытие представления
Для просмотра и редактирования данных в контекстном меню нужного представления выберите команду "Изменить первые 200 строк". Обратите внимание, что изменять данные можно только через те представления, которые построены по одной таблице и не имеют агрегатных функций расчета и группировок данных.
Удаление представления
В контекстном меню представления выберите команду Удалить. Если в БД нет объектов, зависящих от удаляемого, то операция будет выполнена успешно. В противном случае нужно предварительно удалить все зависимые объекты.
5. Работа с представлениями в среде SQL Manager for MySQL
При работе в "Среде SQL Manager for MySQL" список представлений, имеющихся в базе данных, находится в папке Представления. Для создания нового представления достаточно выбрать в контекстном меню папки команду "Новый объект: Представление". В ответ откроется новое окно, в котором нужно ввести имя представления, а ниже текст запроса. После задания всех параметров щелкните по команде Compile. В результате в БД будет создано представление, а в верхней части окна задания параметров представления появится закладка Данные, на которой можно просмотреть данные представления. Измекнить данные через представление в данном окне нельзя.
В дальнейщем для открытия, просмотра или изменения представления достаточно открыть его двойным щелчком.
Удаление представления
В контекстном меню представления выберите команду Удалить представление.
Вопросы для самоконтроля
1. Для чего используют представления? Какие у них преимущества и ограничения?
2. Опишите команду создания представления. Как открыть представление для просмотра?
3. Что означает параметр With Check Option в команде создания представления?
4. Как изменить или удалить представление?