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

Введение

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

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

Skills Graph

Создание таблиц: 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 соответственно.

Создание простого представления (Simple 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.

Создание сложного представления (Complex 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) с агрегатными функциями (Aggregate Functions)

На этом шаге вы создадите представление под названием 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, показывая среднюю зарплату для каждого отдела.

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

На этом шаге вы обновите данные в таблице employees через представление employee_info.

Не все представления являются обновляемыми (updatable). Представление обычно является обновляемым, если оно выбирает данные из одной таблицы и включает первичный ключ (primary key) этой таблицы.

Сначала давайте удалим и воссоздадим представление employee_info, чтобы убедиться, что оно включает столбец id, который является первичным ключом таблицы employees:

DROP VIEW IF EXISTS employee_info;

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

Теперь обновите отдел сотрудника с id = 1 на 'HR':

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

Этот SQL-запрос обновляет столбец department в таблице employees для сотрудника с id = 1 через представление employee_info.

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

SELECT * FROM employees WHERE id = 1;

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

Удаление устаревших представлений (Obsolete Views)

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

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

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

DROP VIEW IF EXISTS employee_info;

Предложение IF EXISTS предотвращает ошибку, если представление не существует.

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

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

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

SELECT name FROM sqlite_master WHERE type='view';

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

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

.exit

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

Итог (Summary)

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