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

Время прочтения: 7 мин.

Представление (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. Что они из себя представляют, какие бывают, для чего и как используются. Если остались вопросы, задавайте их в комментариях.

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

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

17.03.2022 Алексеев Дмитрий,г. Нижний Новгород