Виртуальные таблицы в SQL

Представление (VIEW) – это объект базы данных (БД), который хранит в себе запрос SELECT. При обращении к данному объекту будет возвращен результирующий набор данных (результат выполнения запроса).

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

Ссылка на статью с разбором системных представлений

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

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

Преимущества представлений

Гибкая настройка прав доступа

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

Предотвращение дублирования запросов

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

Сокрытие реализации

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

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

Синтаксис создания представления следующий:

CREATE VIEW view_name AS SELECT columns FROM tables;

Для создания представления используется оператор CREATE VIEW. view_name – это название представления. После оператора AS следует сам запрос.

Обновление определения представления

Синтаксис данной операции следующий:

CREATE OR REPLACE VIEW view_name AS SELECT columns FROM tables;

Обновление применимо, например, в том случае, если вам необходимо что-то изменить в запросе.

Если представления не существует, то оно будет создано.

Удаление представления

Синтаксис удаления представления следующий:

DROP VIEW view_name;

Материализованные представления

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

Синтаксис создания такого представления представлен ниже:

CREATE MATERIALIZED VIEW view_name AS SELECT columns FROM tables;

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

Для этого применяется следующая команда:

REFRESH MATERIALIZED VIEW view_name;

Материализованные представления не являются частью стандарта ANSI SQL и поддерживаются не всеми системами управления базами данных (СУБД). PostgreSQL и Oracle Database поддерживают данный тип представлений.

Практическая часть

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

Схема БД представлена ниже.

Весь код далее был написан в pgAdmin. Соответственно в качестве СУБД использовалась PostgreSQL.

Код создание таблиц приведён ниже.

-- Таблица "Страховой агент" CREATE TABLE insurance_agents ( id SERIAL PRIMARY KEY, last_name CHARACTER VARYING(30) NOT NULL CHECK(last_name != ''), first_name CHARACTER VARYING(30) NOT NULL CHECK(first_name != ''), middle_name CHARACTER VARYING(30) NOT NULL CHECK(middle_name != ''), phone CHARACTER VARYING(18) NOT NULL UNIQUE CHECK(phone != ''), email CHARACTER VARYING(30) NOT NULL CHECK(email != '') UNIQUE, insurance_percent REAL CHECK((insurance_percent >= 0.2) AND (insurance_percent <= 1)) NOT NULL ); -- Таблица "Страхователь" CREATE TABLE insurants ( id SERIAL PRIMARY KEY, last_name CHARACTER VARYING(30) NOT NULL CHECK(last_name != ''), first_name CHARACTER VARYING(30) NOT NULL CHECK(first_name != ''), middle_name CHARACTER VARYING(30) NOT NULL CHECK(middle_name != ''), phone CHARACTER VARYING(18) NOT NULL UNIQUE CHECK(phone != ''), email CHARACTER VARYING(30) NOT NULL CHECK(email != '') UNIQUE ); -- Таблица "Выгодоприобретатель" CREATE TABLE beneficiaries ( id SERIAL PRIMARY KEY, last_name CHARACTER VARYING(30) NOT NULL CHECK(last_name != ''), first_name CHARACTER VARYING(30) NOT NULL CHECK(first_name != ''), middle_name CHARACTER VARYING(30) NOT NULL CHECK(middle_name != ''), phone CHARACTER VARYING(18) NOT NULL UNIQUE CHECK(phone != ''), email CHARACTER VARYING(30) NOT NULL CHECK(email != '') UNIQUE ); -- Таблица "Страховой полис" CREATE TABLE insuranсe_policies ( id SERIAL PRIMARY KEY, series_and_number CHARACTER VARYING(10) NOT NULL UNIQUE CHECK(series_and_number != ''), object_insurance CHARACTER VARYING(30) NOT NULL CHECK(object_insurance != ''), insurance_amount NUMERIC(8,2) NOT NULL, insurance_premium NUMERIC(8,2) NOT NULL, date_in DATE NOT NULL, date_out DATE NOT NULL, id_agent INTEGER, id_insurant INTEGER, id_beneficiary INTEGER, FOREIGN KEY (id_agent) REFERENCES insurance_agents (id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (id_insurant) REFERENCES insurants (id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (id_beneficiary) REFERENCES beneficiaries (id) ON DELETE CASCADE ON UPDATE CASCADE );

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

Сделаем тестовый запрос к БД.

-- Страховая премия по объектам страхования SELECT object_insurance AS "Объект страхования", Sum(insurance_premium) AS "Общая страховая премия", Round(Avg(insurance_premium), 2), Min(insurance_premium), Max(insurance_premium) FROM insuranсe_policies GROUP BY object_insurance ORDER BY Sum(insurance_premium) DESC

Результат выполнения запроса:

Всё в порядке, всё работает. Можем приступать к работе с представлениями.

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

-- Продажи по сотрудникам CREATE VIEW sales_agents AS SELECT last_name AS "Фамилия", first_name AS "Имя", middle_name AS "Отчество", Count(*) AS "Кол-во сделок", Sum(insurance_amount + insurance_premium) AS "Сумма сделок" FROM insurance_agents INNER JOIN insuranсe_policies ON insurance_agents.id = insuranсe_policies.id_agent GROUP BY last_name, first_name, middle_name ORDER BY last_name

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

SELECT * FROM sales_agents

Результат:

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

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

Теперь, изменим представление – добавим почты агентов. Удалять столбцы из представления нельзя.

-- Продажи по сотрудникам CREATE OR REPLACE VIEW sales_agents AS SELECT last_name AS "Фамилия", first_name AS "Имя", middle_name AS "Отчество", Count(*) AS "Кол-во сделок", Sum(insurance_amount + insurance_premium) AS "Сумма сделок", email AS "Почта" FROM insurance_agents INNER JOIN insuranсe_policies ON insurance_agents.id = insuranсe_policies.id_agent GROUP BY last_name, first_name, middle_name, email ORDER BY last_name

Результат:

Удалим представление и создадим материализованное представление.

Удаление:

Создание материализованного представления:

-- Страховая сумма и премия по годам CREATE MATERIALIZED VIEW by_year AS SELECT CAST(Extract(year FROM date_in) AS INT) AS "Год", Sum(insurance_amount) AS "Страховая сумма", Sum(insurance_premium) AS "Страховая премия" FROM insuranсe_policies GROUP BY Extract(year FROM date_in) ORDER BY Extract(year FROM date_in)

Обратимся к представлению как к таблице:

SELECT * FROM by_year

Результат:

Попробуем изменить данные и вновь вызовем представление. Удалим запись о договоре страхования с серией и номером 0567412354.

Информация о договоре:

Удаление:

Вызов обращения:

Выполним отдельно запрос из представления, чтобы сверить результаты.

По скриншотам видно, что данные в материализованном представлении не обновились (данные за 2015 год).

Однако скорость выполнения операций отличается.

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

Запрос:

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

В этом посте я рассмотрел тему представлений в SQL. Что они из себя представляют, какие бывают, для чего и как используются. Если остались вопросы, задавайте их в комментариях.

Надеюсь материал был полезен. Удачи в использовании представлений!