SQLite 视图创建

介绍

在这个实验（Lab）中，我们将探索 SQLite 视图的创建，重点是构建复杂的视图、通过它们进行查询、通过可更新视图进行更新，以及移除过时的视图。

我们将首先了解视图，将其视为基于 SQL 语句结果集的虚拟表，这对于简化查询和控制数据访问非常有用。本实验（Lab）将指导你从单个表（例如 employees 表）创建简单的视图，然后逐步创建涉及表之间连接的更复杂的视图，例如连接 employees 和 departments 表。你将学习如何像查询常规表一样查询这些视图，并探索通过可更新视图更新数据的可能性，以及如何在不再需要视图时正确地移除它们。

Skills Graph

创建表：employees 和 departments

在此步骤中，你将创建两个表，employees 和 departments，并插入一些示例数据。这些表将用于在后续步骤中创建和查询视图。

首先，通过在终端中运行以下命令来打开 SQLite shell：

sqlite3 /home/labex/project/employees.db

此命令打开 SQLite shell 并连接到 employees.db 数据库。如果数据库文件不存在，SQLite 将创建它。

现在，使用以下 SQL 语句创建 employees 表：

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 表中。

现在，使用以下 SQL 语句创建 departments 表：

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

此 SQL 语句创建一个名为 departments 的表，其中包含三列：id、name 和 location。 id 列是表的主键（primary key）。

接下来，将一些示例数据插入到 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 表的内容。

创建一个简单的视图

在此步骤中，你将创建一个名为 employee_info 的简单视图，该视图从 employees 表中选择特定的列。

视图是基于 SQL 语句的结果集（result-set）的虚拟表。它简化了复杂的查询，并提供了一定程度的抽象（abstraction）。

要创建 employee_info 视图，请在 SQLite shell 中运行以下 SQL 语句：

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

此 SQL 语句创建一个名为 employee_info 的视图，该视图从 employees 表中选择 id、first_name、last_name 和 department 列。

现在，你可以像查询表一样查询该视图：

SELECT * FROM employee_info;

此命令将显示 employee_info 视图的内容，该视图是 employees 表中列的子集。

创建一个带有连接（Join）的复杂视图

在此步骤中，你将创建一个更复杂的视图，名为 employee_department_info，它连接（join）了 employees 和 departments 表。

连接表允许你基于相关的列，将来自多个表的数据组合在一起。

要创建 employee_department_info 视图，请在 SQLite shell 中运行以下 SQL 语句：

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 的视图，该视图通过 department 列连接 employees 和 departments 表。它选择员工的名字（first name）、姓氏（last name）、部门（department）以及部门的所在地（location）。

现在，你可以查询该视图以查看组合后的数据：

SELECT * FROM employee_department_info;

此命令将显示 employee_department_info 视图的内容，显示员工信息以及他们所在部门的所在地。

创建一个带有聚合函数（Aggregate Functions）的视图

在此步骤中，你将创建一个名为 department_salary_stats 的视图，该视图使用聚合函数来计算每个部门的平均工资（average salary）。

聚合函数对一组值执行计算并返回单个结果。

要创建 department_salary_stats 视图，请在 SQLite shell 中运行以下 SQL 语句：

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）更新数据

在此步骤中，你将通过 employee_info 视图更新 employees 表中的数据。

并非所有视图都是可更新的。如果视图从单个表选择数据，并且包含该表的主键（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 的员工的部门（department）更新为 'HR'：

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

此 SQL 语句通过 employee_info 视图，更新 employees 表中 id = 1 的员工的 department 列。

你可以通过直接查询 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 shell：

.exit

此命令关闭与 employees.db 数据库的连接，并返回到 Linux 终端。

总结

在这个实验（lab）中，你学习了如何在 SQLite 中创建、查询、更新和移除视图（views）。你首先创建了基于单个表的简单视图，然后逐步创建了涉及连接（joins）和聚合函数（aggregate functions）的更复杂的视图。你还学习了如何通过可更新视图（updatable views）更新数据，以及如何移除过时的视图以维护干净的数据库模式。这些技能对于简化复杂查询、控制数据访问以及有效管理你的 SQLite 数据库至关重要。