Создание и использование представлений (VIEW) в SQL

1. Введение: Что такое представление (VIEW)?

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

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

Зачем использовать представления (VIEW)?

  • Простота: Представления могут скрывать сложность структуры базовых таблиц и замысловатые операции соединения (join). Пользователь может запрашивать данные из простого представления, не зная сложный запрос, который его определяет. Это особенно полезно для часто выполняемых сложных запросов.
  • Безопасность: Представления могут ограничивать доступ к данным. Можно создать представление, которое предоставляет определенным пользователям доступ только к конкретным столбцам или строкам из таблицы (или комбинации таблиц), скрывая конфиденциальную информацию, присутствующую в базовых таблицах.
  • Согласованность (Консистентность): Представления обеспечивают согласованную, стабильную структуру для пользователей и приложений, даже если базовые таблицы подвергаются реструктуризации. Пока определение представления остается действительным, запросы к нему могут продолжать работать без изменений.
  • Абстракция данных: Представления предоставляют слой абстракции над схемой базы данных.

2. Создание представлений (VIEW) в PostgreSQL

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

CREATE [ OR REPLACE ] VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE [condition];
-- Or any other valid SELECT statement, including JOINs, GROUP BY, functions, etc.
  • CREATE VIEW view_name: Эта фраза начинает процесс создания представления и присваивает ему имя (view_name). Имя должно быть уникальным в пределах схемы.
  • OR REPLACE: Эта необязательная фраза позволяет изменить определение существующего представления без необходимости предварительно его удалять. Если представление view_name уже существует, его определение заменяется; в противном случае создается новое представление.
  • AS: Это ключевое слово отделяет определение представления от инструкции SELECT, которая его определяет.
  • SELECT ...: Это ядро представления — стандартный SQL-запрос SELECT. То, что возвращает этот запрос, определяет структуру (столбцы) и содержимое (строки) представления в момент выполнения запроса к нему.

Предварительные условия: Убедитесь, что у вас есть соответствующие привилегии для создания представлений (обычно привилегия CREATE на схему).

Данные для примера:

Предположим, у нас есть следующие упрощенные таблицы из наших предыдущих занятий (P2-P11):

-- Departments Table
CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

-- Employees Table
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary NUMERIC(10, 2),
    department_id INT REFERENCES departments(department_id)
);

-- Sample Data Insertion (You should have similar tables from previous work)
INSERT INTO departments (department_name, location) VALUES
('Technology', 'Building A'),
('Human Resources', 'Building B'),
('Sales', 'Building C');

INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id) VALUES
('Alice', 'Smith', 'alice.s@example.com', '2022-01-15', 75000, 1),
('Bob', 'Johnson', 'bob.j@example.com', '2021-11-01', 68000, 1),
('Charlie', 'Davis', 'charlie.d@example.com', '2023-03-10', 82000, 3),
('Diana', 'Miller', 'diana.m@example.com', '2022-07-20', 60000, 2),
('Eve', 'Wilson', 'eve.w@example.com', '2023-01-05', 90000, 1);

Пример 1: Простое представление

Создайте представление, показывающее только имена и адреса электронной почты сотрудников.

CREATE VIEW employee_contact_list AS
SELECT
    first_name,
    last_name,
    email
FROM
    employees;

Пример 2: Представление с фильтрацией и сортировкой

Создайте представление, показывающее сотрудников из отдела ‘Technology’, принятых на работу после 2022 года, отсортированных по дате найма. Примечание: Хотя ORDER BY можно включать в определение представления, часто более гибко применять сортировку при запросе самого представления, если только сортировка не является фундаментальной для цели представления.

CREATE VIEW recent_tech_employees AS
SELECT
    employee_id,
    first_name,
    last_name,
    hire_date
FROM
    employees
WHERE
    department_id = (SELECT department_id FROM departments WHERE department_name = 'Technology')
    AND hire_date >= '2022-01-01'
ORDER BY
    hire_date DESC;

Пример 3: Представление с объединением таблиц (JOINs) (используя концепции из P8/P11)

Создайте представление, которое объединяет информацию о сотрудниках с названием их отдела.

CREATE VIEW employee_department_details AS
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.email,
    d.department_name,
    d.location
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id;

Пример 4: Представление с агрегацией

Создайте представление, показывающее количество сотрудников и среднюю зарплату по каждому отделу.

CREATE VIEW department_stats AS
SELECT
    d.department_name,
    COUNT(e.employee_id) AS number_of_employees,
    AVG(e.salary)::NUMERIC(10, 2) AS average_salary -- Casting to NUMERIC for better formatting -- Приведение к NUMERIC для лучшего форматирования
FROM
    departments d
LEFT JOIN -- Use LEFT JOIN to include departments with no employees -- Используем LEFT JOIN, чтобы включить отделы без сотрудников
    employees e ON d.department_id = e.department_id
GROUP BY
    d.department_name;

3. Использование представлений (VIEW)

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

-- Query the simple contact list view -- Запрос к простому представлению со списком контактов
SELECT * FROM employee_contact_list;

-- Query the view with filtering applied *to the view* -- Запрос к представлению с применением фильтра *к самому представлению*
SELECT * FROM employee_contact_list WHERE last_name = 'Smith';

-- Query the joined view -- Запрос к представлению с объединением таблиц
SELECT first_name, last_name, department_name
FROM employee_department_details
WHERE location = 'Building A';

-- Query the aggregation view -- Запрос к представлению с агрегацией
SELECT * FROM department_stats
WHERE number_of_employees > 1
ORDER BY average_salary DESC;

-- Query the view that already had ordering and filtering -- Запрос к представлению, которое уже содержит сортировку и фильтрацию
SELECT * FROM recent_tech_employees;

Обратите внимание, как запрос к employee_department_details становится намного проще, чем написание полного запроса с JOIN каждый раз.

4. Изменение представлений (VIEW)

Если вам нужно изменить лежащий в основе запроса представления, вы можете использовать синтаксис CREATE OR REPLACE VIEW. Этот способ обычно предпочтительнее удаления и повторного создания, особенно если другие объекты базы данных или приложения зависят от этого представления.

Пример: Измените employee_contact_list, чтобы включить employee_id.

CREATE OR REPLACE VIEW employee_contact_list AS
SELECT
    first_name,
    last_name,
    email,
    employee_id -- Added column -- Добавлен столбец
FROM
    employees;

Теперь при запросе к employee_contact_list также будет возвращаться employee_id.

5. Удаление представлений (VIEW)

Если представление больше не требуется, его можно удалить с помощью инструкции DROP VIEW.

DROP VIEW view_name;
  • CASCADE: Можно добавить CASCADE (DROP VIEW view_name CASCADE;) для автоматического удаления объектов, которые зависят от представления (используйте с осторожностью!).
  • RESTRICT: Это поведение по умолчанию. Если какие-либо другие объекты зависят от представления, инструкция DROP VIEW завершится с ошибкой.

Пример:

-- Drop the simple contact list view -- Удалить простое представление со списком контактов
DROP VIEW employee_contact_list;

-- Attempt to drop the department details view (might fail if other objects depend on it) -- Попытка удалить представление с деталями отделов (может завершиться с ошибкой, если от него зависят другие объекты)
DROP VIEW employee_department_details; -- Defaults to RESTRICT -- По умолчанию RESTRICT