Базовый курс SQL. Представления

Forums:

[к оглавлению]

Что это такое

Представления - это виртуальные таблицы, строящиеся "на лету". Представления не содержат в себе данные, как обычные таблицы, а хранят запросы, которые эти данные извлекают.

Синтаксис создания представлений во всех основных СУБД одинаков, но есть особенности:

  • Синтаксис всех вложенных запросов должен соответствовать правилам вашей СУБД
  • Представления появились в MySQL, начиная с 5 версии, соответственно, если у вас более ранняя версия, код данного урока не сработает
  • В SQLite представления используются только для чтения, их содержимое не удастся обновить
  • MicrosoftAccess немного по-другому реализует представления, таким образом, данный урок также не подойдёт для этой СУБД

Давайте рассмотрим, что же такое представление на примере сложного объединения из главы 13:

SELECT student_surname
FROM Students, Groups, Faculties
WHERE Students.group_id =  Groups.group_id
       AND Groups.faculty_id = Faculties.faculty_id
       AND faculty_name = 'Компьютерных технологий';

Если поместить все объединения вместе с условиями в представление StudentsFaculties, то запрос будет выглядеть намного проще:

SELECT student_surname
FROM StudentsFaculties
WHERE faculty_name = 'Компьютерных технологий';

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

Представления - это самостоятельные сущности в базе данных, и для них работают специфические правила, которые следует запомнить:

  • Имя представления должно быть уникальным, не совпадать с именами других представлений и таблиц данной базы данных
  • Количество созданных представлений не ограничивается
  • Создание представлений может быть ограничено правами доступа пользователя
  • Можно создать представление внутри представления. Количество уровней вложенности зависит от конкретной СУБД. Однако производительность такого кода значительно снизится
  • Во многих СУБД невозможно отсортировать значения, получаемые в запросах к представлениям
  • Представления не индексируются, не содержат триггеров и значений по умолчанию
  • Некоторые СУБД запрещают вносить в представления такие изменения, которые повлекут исключение строки из представления. А есть и такие, что допускают только чтение данных

Это обобщённый список ограничений, для его уточнения обратитесь к документации своей СУБД.

Для чего нужны представления

Давайте разберёмся, в каких случаях следует использовать представления:

  • Переиспользование одинаковых запросов
  • Упрощение сложных конструкций
  • Частичный вывод данных таблицы
  • Ограничение доступа к данным для определённых пользователей, опять же через частичное получение данных
  • Форматирование и вычисление данных, вывод в виде, отличном от того, что хранится в таблице

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

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

Для создания представлений используется ключевое слово CREATE VIEW, затем указывается название, которое вы хотите дать представлению, за ним ключевое слово AS и далее идёт запрос, который будет храниться в представлении:

CREATE VIEW ViewName AS
/* текст запроса */

Удаление представления происходит ещё проще:

DROP VIEW ViewName;

А вот отредактировать представление нельзя - для этого придётся удалить его и создать новое.

Представления для упрощения сложных объединений

Это наиболее популярный случай использования представлений. И часто в таких запросах присутствуют объединения. Давайте реализуем рассмотренный выше пример:

CREATE VIEW StudentsFaculties AS
SELECT student_surname, faculty_name
FROM Students, Groups, Faculties
WHERE Students.group_id =  Groups.group_id
       AND Groups.faculty_id = Faculties.faculty_id;

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

SELECT student_surname
FROM StudentsFaculties
WHERE faculty_name = 'Компьютерных технологий';

Получим:

student_surname
-----------------------
Шульгина                                          
Колобков                                          
Распопов                                          
Адамченко                                         
Сейдинай                                          
Петрашевский                                      
Грошев                                            
Легран                                            
Егорова

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

Представления для изменения формата извлекаемых данных

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

SELECT RTRIM(student_surname) || ' ' || RTRIM(student_name) || ' (' || student_age || ')' AS student_line
FROM Students
ORDER BY student_surname;

Получим:

student_line
----------------------
Адамченко Пётр (21)
Грошев Вадим (20)
Егорова Зинаида (19)
Колобков Дмитрий (22)
Легран Базиль (24)
Петрашевский Аркадий (22)
Распопов Еагений (22)
Римский Григорий (21)
Сейдинай Эра (25)
Шульгина Ольга (23)

Обратите внимание на то, что оператор || используется для соединения строк, и в вашей СУБД он может отличаться.

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

CREATE VIEW StudentLine AS
SELECT RTRIM(student_surname) || ' ' || RTRIM(student_name) || ' (' || student_age || ')' AS student_line
FROM Students;

И следующие запросы будут выглядеть намного проще:

SELECT *
FROM StudentLine;

Представления для добавления стандартных условий

С помощью представлений можно сохранить запросы с постоянно используемыми условиями, и уже к ним добавлять те, что требуются в каждом конкретном случае. К примеру, это можно использовать, чтобы исключить из выборки отчисленных студентов, экзамены с прошедшей датой и т.п. Мы для примера выберем студентов, у которых есть e-mail:

CREATE VIEW StudentEmailList AS
SELECT student_surname, student_name, student_email
FROM Students
WHERE student_email IS NOT NULL;

Теперь запросив вывод данных представления мы получим список всех студентов, у которых поле email заполнено:

SELECT *
FROM StudentEmailList;

Также мы можем добавить в запрос свои условия WHERE, СУБД скомбинирует их с условиями представления автоматически.

Представления с вычисляемыми полями

Представления могут значительно упростить запросы с вычисляемыми полями. Ранее, на уроке 8 мы складывали баллы, полученные в конкурсах студентами:

SELECT student_id, 
    first_round_points, 
    second_round_points, 
    first_round_points + second_round_points AS final_points
FROM Participants
ORDER BY final_points DESC;

Давайте создадим представление:

CREATE VIEW ParticipantsPoints AS
SELECT student_id, 
    first_round_points, 
    second_round_points, 
    first_round_points + second_round_points AS final_points
FROM Participants;

Теперь мы можем переиспользовать наши данные и запросы с ними будут более наглядны:

SELECT *
FROM ParticipantsPoints
WHERE final_points > 200;

Получим:

student_id | first_round_points | second_round_points | final_points
---------------------------------------------------------------------
92540 	  |        148        	|        115       	  |  263
92522 	  |        95           |        124         |  219

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

Следующая страница. Хранимые процедуры.
Оглавление.