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

Введение

В этой лабораторной работе мы изучим создание представлений (view) SQLite, сосредоточившись на построении сложных представлений, запросах к ним, обновлении данных через обновляемые представления и удалении устаревших представлений.

Мы начнем с понимания представлений как виртуальных таблиц, основанных на результирующих наборах SQL-запросов, полезных для упрощения запросов и контроля доступа к данным. Лабораторная работа проведет вас через создание простых представлений из отдельных таблиц, таких как таблица employees (сотрудники), а затем перейдет к более сложным представлениям, включающим объединения (joins) между таблицами, например, объединение таблиц employees и departments (отделы). Вы узнаете, как запрашивать эти представления, как если бы они были обычными таблицами, и изучите возможности обновления данных через обновляемые представления, а также то, как правильно удалять представления, когда они больше не нужны.

Создание таблиц: сотрудники (employees) и отделы (departments)

На этом шаге вы создадите две таблицы: employees (сотрудники) и departments (отделы), и вставите в них некоторые примерные данные. Эти таблицы будут использоваться для создания представлений (views) и запросов к ним на следующих шагах.

Сначала откройте оболочку SQLite, выполнив следующую команду в терминале:

sqlite3 /home/labex/project/employees.db

Эта команда открывает оболочку SQLite и подключается к базе данных employees.db. Если файл базы данных не существует, SQLite создаст его.

Теперь создайте таблицу employees с помощью следующего SQL-запроса:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    department TEXT,
    salary REAL
);

Этот SQL-запрос создает таблицу с именем employees с пятью столбцами: id, first_name (имя), last_name (фамилия), department (отдел) и salary (зарплата). Столбец id является первичным ключом (primary key) для таблицы.

Далее вставьте некоторые примерные данные в таблицу employees:

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 50000.00),
('Jane', 'Smith', 'Marketing', 60000.00),
('Peter', 'Jones', 'Sales', 55000.00),
('Mary', 'Brown', 'IT', 70000.00);

Этот SQL-запрос вставляет четыре строки в таблицу employees.

Теперь создайте таблицу departments с помощью следующего SQL-запроса:

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT,
    location TEXT
);

Этот SQL-запрос создает таблицу с именем departments с тремя столбцами: id, name (название) и location (местоположение). Столбец id является первичным ключом для таблицы.

Далее вставьте некоторые примерные данные в таблицу departments:

INSERT INTO departments (name, location) VALUES
('Sales', 'New York'),
('Marketing', 'London'),
('IT', 'San Francisco');

Этот SQL-запрос вставляет три строки в таблицу departments.

Вы можете проверить создание таблицы и вставку данных, запросив таблицы:

SELECT * FROM employees;
SELECT * FROM departments;

Эти команды отобразят содержимое таблиц employees и departments соответственно.

Создание простого представления (View)

На этом шаге вы создадите простое представление под названием employee_info (информация о сотрудниках), которое выбирает определенные столбцы из таблицы employees.

Представление (view) — это виртуальная таблица, основанная на результирующем наборе SQL-запроса. Оно упрощает сложные запросы и обеспечивает уровень абстракции.

Чтобы создать представление employee_info, выполните следующий SQL-запрос в оболочке SQLite:

CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;

Этот SQL-запрос создает представление с именем employee_info, которое выбирает столбцы id, first_name (имя), last_name (фамилия) и department (отдел) из таблицы employees.

Теперь вы можете запрашивать представление, как если бы это была таблица:

SELECT * FROM employee_info;

Эта команда отобразит содержимое представления employee_info, которое является подмножеством столбцов в таблице employees.

Создание сложного представления (View) с объединениями (Joins)

На этом шаге вы создадите более сложное представление под названием employee_department_info (информация о сотрудниках и отделах), которое объединяет таблицы employees и departments.

Объединение таблиц (joining tables) позволяет объединять данные из нескольких таблиц на основе связанного столбца.

Чтобы создать представление employee_department_info, выполните следующий SQL-запрос в оболочке SQLite:

CREATE VIEW employee_department_info AS
SELECT
    e.first_name,
    e.last_name,
    e.department,
    d.location
FROM
    employees e
JOIN
    departments d ON e.department = d.name;

Этот SQL-запрос создает представление с именем employee_department_info, которое объединяет таблицы employees и departments по столбцу department. Он выбирает имя сотрудника (first name), фамилию сотрудника (last name), отдел (department) и местоположение отдела (location).

Теперь вы можете запросить представление, чтобы увидеть объединенные данные:

SELECT * FROM employee_department_info;

Эта команда отобразит содержимое представления employee_department_info, показывая информацию о сотрудниках вместе с местоположением их отдела.

Создание представления (View) с агрегатными функциями

На этом шаге вы создадите представление под названием department_salary_stats (статистика зарплат по отделам), которое использует агрегатные функции для вычисления средней зарплаты для каждого отдела.

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

Чтобы создать представление department_salary_stats, выполните следующий SQL-запрос в оболочке SQLite:

CREATE VIEW department_salary_stats AS
SELECT
    department,
    AVG(salary) AS average_salary
FROM
    employees
GROUP BY
    department;

Этот SQL-запрос создает представление с именем department_salary_stats, которое вычисляет среднюю зарплату для каждого отдела, используя функцию AVG (среднее значение) и группирует результаты по отделам, используя предложение GROUP BY.

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

SELECT * FROM department_salary_stats;

Эта команда отобразит содержимое представления department_salary_stats, показывая среднюю зарплату для каждого отдела.

Обновление данных через обновляемое представление в SQLite

На этом шаге вы узнаете об ограничениях представлений SQLite и о том, как создавать по-настоящему обновляемые представления, используя триггеры INSTEAD OF.

Важно: По умолчанию представления SQLite доступны только для чтения. Вы не можете напрямую выполнять операции UPDATE, INSERT или DELETE данных через представление. Однако вы можете создавать обновляемые представления, используя триггеры INSTEAD OF.

Сначала давайте разберемся с ограничением, попытавшись выполнить прямое обновление существующего представления:

UPDATE employee_info
SET department = 'HR'
WHERE id = 1;

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

Чтобы создать по-настоящему обновляемое представление, нам нужно использовать триггеры INSTEAD OF. Давайте пересоздадим представление employee_info и добавим триггер INSTEAD OF для обновлений:

DROP VIEW IF EXISTS employee_info;

CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;

Теперь создайте триггер INSTEAD OF для обработки операций UPDATE:

CREATE TRIGGER update_employee_info
INSTEAD OF UPDATE ON employee_info
BEGIN
    UPDATE employees
    SET first_name = NEW.first_name,
        last_name = NEW.last_name,
        department = NEW.department
    WHERE id = OLD.id;
END;

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

UPDATE employee_info
SET department = 'HR'
WHERE id = 1;

Этот оператор UPDATE теперь будет работать, потому что триггер INSTEAD OF перенаправляет обновление в базовую таблицу employees.

Вы можете проверить обновление, напрямую запросив таблицу employees:

SELECT * FROM employees WHERE id = 1;

Эта команда отобразит строку в таблице employees с id = 1, и вы должны увидеть, что столбец department был обновлен до 'HR'.

Удаление устаревших представлений (views) и триггеров (triggers)

На этом шаге вы удалите представления (views) и триггеры (triggers), которые вы создали на предыдущих шагах.

По мере развития вашей базы данных некоторые представления (views) и триггеры (triggers) могут устареть или стать ненужными. Важно удалять эти объекты для поддержания чистой и эффективной схемы базы данных.

Сначала удалите триггер (trigger) INSTEAD OF:

DROP TRIGGER IF EXISTS update_employee_info;

Затем удалите представления (views). Чтобы удалить представление (view), используйте оператор DROP VIEW. Например, чтобы удалить представление (view) employee_info, выполните следующую команду:

DROP VIEW IF EXISTS employee_info;

Клауза (clause) IF EXISTS предотвращает ошибку, если представление (view) не существует.

Удалите и другие представления (views):

DROP VIEW IF EXISTS employee_department_info;
DROP VIEW IF EXISTS department_salary_stats;

Вы можете проверить, были ли удалены представления (views), запросив таблицу sqlite_master:

SELECT name FROM sqlite_master WHERE type='view';

Эта команда должна вернуть пустой результат, указывающий на отсутствие представлений (views) в базе данных.

Наконец, выйдите из оболочки SQLite:

.exit

Эта команда закрывает соединение с базой данных employees.db и возвращает вас в терминал Linux.

Резюме

В этой лабораторной работе вы узнали, как создавать, запрашивать, обновлять и удалять представления (views) в SQLite. Вы начали с создания простых представлений на основе одной таблицы, а затем перешли к более сложным представлениям, включающим объединения (joins) и агрегатные функции. Вы обнаружили, что представления SQLite по умолчанию доступны только для чтения, но узнали, как создавать действительно обновляемые представления, используя триггеры INSTEAD OF. Вы также узнали, как правильно удалять устаревшие представления и триггеры для поддержания чистой схемы базы данных. Эти навыки необходимы для упрощения сложных запросов, контроля доступа к данным и эффективного управления вашими базами данных SQLite.