Удаление индекса (оператор drop index)

Если для таблицы базы данных был создан индекс, который впоследствии оказался ненужным, то его можно удалить с помощью оператора DROP INDEX. Этот оператор имеет следующий формат:

DROP INDEX IndexName

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

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

Представление является виртуальным отношением, которое не всегда реально существует в базе данных, но создается по запросу определенного пользователя в ходе выполнения этого запроса. С точки зрения пользователя базы данных представление выглядит как реальная таблица данных, содержащая набор поименованных столбцов и строк данных. Но в отличие от реальных таблиц представления не всегда существуют в базе как некоторый набор сохраняемых значений данных. В действительности доступные через представления строки и столбцы данных являются результатом выполнения запроса, заданного при определении представления. СУБД сохраняет определение представления в базе данных. Обнаружив ссылку на представление, СУБД применяет один из двух следующих подходов для формирования представления. При первом подходе СУБД отыскивает определение представления и преобразуют исходный запрос, лежащий в основе представления, в эквивалентный запрос к таблицам, использованным в определении представления, после чего модифицированный запрос выполняется. Этот процесс слияния запросов, называется заменой представления (под этим подразумевается замена представления оператором SQL, который обращается к базовым таблицам). При втором подходе, который называется материализацией представления, готовое представление хранится в базе данных в виде временной таблицы, а его актуальность постоянно поддерживается по мере обновления всех таблиц, лежащих в его основе. Теперь познакомимся с тем, как создаются и используются представления.

Создание представлений (оператор create view)

Оператор CREATE VIEW имеет следующий формат:

CREATE VIEW ViewName [ (newCoIumntfame [, . . . ] ) ]

AS subselect [WITH |CASCADED |LOCAL| CHECK OPTION]

Представление определяется с помощью подзапроса sutoselect, оформленного в виде оператора SELECT языка SQL. Существует необязательная возможность присвоения собственного имени каждому из столбцов представления. Если указывается список имен столбцов, то он должен иметь количество элементов, равное количеству столбцов в результирующей таблице запроса, заданного параметром subselect. Если список имен столбцов опущен, каждый столбец представления будет иметь имя соответствующего столбца результирующей таблицы запроса, заданного параметром subselect. Список имен столбцов должен обязательно задаваться в том случае, если в именах столбцов результирующей таблицы имеет место неоднозначность. Подобная ситуация возникает в тех случаях, когда подзапрос включает вычисляемые поля, а конструкция AS с именами столбцов результирующей таблицы не содержит для них имен или же когда результирующая таблица создается с помощью операции соединения и включает столбцы с одинаковыми именам.

Заданный параметром subseJect подзапрос принято называть определяющим запросом.

Если указана конструкция WITH CHECK OPTION, то гарантируется, что в тех случаях, когда строка данных не удовлетворяет условию, указанному в конструкции WHERE определяющего запроса представления, она не будет добавлена в его базовую таблицу. Следует отметить, что для успешного создания представления необходимо иметь привилегию SELECT для всех упоминаемых в определяющем запросе таблиц, а также привилегию USAGE для всех доменов, используемых в указанных в запросе столбцах. Хотя все представления создаются с помощью одного и того же метода, на практике для различных целей используются разные типы представлений. Назначение представлений различных типов мы продемонстрируем на нескольких конкретных примерах.

Пример 7.2.3. Создание горизонтального представления

Создайте представление, позволяющее преподавателю просматривать данные только тех студентов, у которых проходной бал равен '3.8'. Горизонтальное представление позволяет ограничить доступ пользователей только определенными строками, выбранными из одной или нескольких таблиц.

CREATE VIEW БаллСтудент

AS SELECT *

FROM Студент

WHERE СРБАЛЛ = '3,8';

В результате выполнения этого оператора будет создано представление БаллСтудент, включающее все столбцы таблицы Студент, но содержащее только те ее строки, в которых средний балл студентов равен '3,8'. (Строго говоря, в этом представлении столбец СРБАЛЛ не является необходимым и вполне может быть исключен из определения представления, поскольку он всегда будет содержать одно и то же значение – '3,8'.) Если теперь выполнить приведенный ниже оператор SQL, его результат будет иметь вид, показанный в табл. 7.2.3.

SELECT * FROM БаллСтудент;

Таблица 7.2.3. Данные, доступные в представлении БаллСтудент

Номер студента в группе	Группа	ФИО	Год рождения	Адрес	Средний балл
12	ТМ-31	Бабич Ю.	1986	с.Жмеринка	3,8
19	ТЗ-31	Самойленко Ю.	1986	Бердычев	3,8

Для обеспечения того, чтобы преподаватель мог видеть только те строки таблицы Студент, доступ к которым ему разрешен, следует полностью запретить ему прямой доступ к этой таблице, но разрешить доступ к представлению БаллСтудент. В результате преподаватель будет иметь собственное представление данных таблицы Студент, содержащее сведения только о тех студентах, у которых средний балл равен 3,8.

Пример 7.2.4. Создание вертикального представления

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

CREATE VIEW БаллСтудентАдрес

AS SELECT НС, НГ, ФИО, ГОДР

FROM Студен

WHERE СРБАЛЛ = '3,8';

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

CREATE VIEW БаллСтудентАдрес

AS SELECT НС, НГ, ФИО, ГОДР

FROM БаллСтудент;

В любом случае будет создано представление БаллСтудентАдрес, содержащее все столбцы таблицы Студент, за исключением столбцов адрес и средний балл. Данные, содержащиеся в этом представлении, показаны в табл. 7.2.4.

Таблица 7.2.4. Данные, доступные в представлении БаллСтудентАдрес

Номер студента в группе	Группа	ФИО	Год рождения
12	ТМ-31	Бабич Ю.	1986
19	ТЗ-31	Самойленко Ю.	1986

Для того чтобы гарантировать, что только преподаватель будет иметь доступ к сведениям об адресе студентов, всем студентам, у которых проходной бал равен '3.8' должно быть предоставлено право доступа только к представлению БаллСтудент, но не к самой таблице Студент или представлению БаллСтудентАдрес.

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

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

Пример 7.2.5. Группирование по нескольким столбцам

Определите количество оценок, полученных каждым из студентов.

SELECT s.НГ, s.НС, COUNT(*) AS count

FROM Студент s, Успеваемость y

WHERE s.НС = у.НС AND s.НГ=у.НГ

GROUP BY s.НС, s.НГ

ORDER BY s.НС, s.НГ;

Чтобы составить требуемый отчет, прежде всего необходимо выяснить, кто из студентов института получал оценки. Эту задачу можно решить посредством соединения таблиц СТУДЕНТ и УСПЕВАЕМОСТЬ по столбцам НГ и НС в конструкциях FROM/WHERE. Затем необходимо сформировать группы, состоящие из номера группы и номеров ее студентов, для чего следует применить конструкцию GROUP BY. Наконец, результирующая таблица должна быть отсортирована с помощью задания конструкции ORDER BY. Результаты выполнения запроса представлены а табл. 7.2.5.

Таблица 7.2.5. Результат выполнения запроса из примера

Ном. студента	Номер группы	count
12	ТМ-31	1
15	ТЗ-32	1
19	ТЗ-31	1
20	ТИ-32	1
21	ТМ-33	1
23	ТИ-31	1

Пример 7.2.6. Представление, формируемое путем группирования и соединения

Создайте представление, содержащее данные о количестве оценок, полученных каждым студентом. Оно должно включать номер студента, номер группы и сведения о количестве оценок, которые он получил (см. предыдущий пример).

CREATE VIEW СтудентУспевCnt (НС, НГ, count)

AS SELECT s.НГ, s.НС, COUNT(*)

FROM Студент s, Успеваемость у

WHERE s.НС = у.НС

GROUP BY s.НС, у.НГ;

В результате будут получены данные, показанные в табл. 7.2.6. Этот пример иллюстрирует использование подзапроса, содержащего конструкцию GROUP BY (в результате чего создается представление, называемое сгруппированным представлением) и обращающегося к данным нескольких таблиц (подобные представления называют соединенными представлениями). Одной из важнейших причин использования представлений является стремление к упрощению многотабличных запросов. После определения представления с соединением нескольких таблиц можно будет использовать простейшие однотабличные запросы к этому представлению вместо сложных запросов с выполнением того же самого многотабличного соединения. Отметим, что в приведенное выше определение представления потребовалось включить список имен столбцов, поскольку в нем использована агрегирующая функция COUNT.

Таблица 7.2.6. Данные, доступные в представлении СтудентУспевCnt

Ном. студента	Номер группы	count
12	ТМ-31	1
15	ТЗ-32	1
19	ТЗ-31	1
20	ТИ-32	1
21	ТМ-33	1
23	ТИ-31	1