介绍
在这个实验（Lab）中，我们将探索 SQLite 视图的创建，重点是构建复杂的视图、通过它们进行查询、通过可更新视图进行更新，以及移除过时的视图。
我们将首先了解视图，将其视为基于 SQL 语句结果集的虚拟表，这对于简化查询和控制数据访问非常有用。本实验（Lab）将指导你从单个表（例如 employees 表）创建简单的视图，然后逐步创建涉及表之间连接的更复杂的视图，例如连接 employees 和 departments 表。你将学习如何像查询常规表一样查询这些视图，并探索通过可更新视图更新数据的可能性，以及如何在不再需要视图时正确地移除它们。
创建表：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 表中列的子集。
创建一个包含连接的复杂视图
在此步骤中，你将创建一个更复杂的视图，名为 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 视图的内容，显示员工信息以及他们所在部门的所在地。
创建一个包含聚合函数的视图
在此步骤中，你将创建一个名为 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 视图的内容，显示每个部门的平均工资。
通过可更新视图更新数据
在这一步中，你将了解 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'。
移除过时的视图和触发器
在这一步，你将移除在之前的步骤中创建的视图和触发器。
随着你的数据库不断发展，一些视图和触发器可能会变得过时或不必要。移除这些对象对于维护一个干净且高效的数据库模式至关重要。
首先，移除 INSTEAD OF 触发器：
DROP TRIGGER IF EXISTS update_employee_info;
然后移除视图。要移除一个视图，使用 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 中创建、查询、更新和删除视图（view）。你首先创建了基于单个表的简单视图，然后逐步过渡到涉及连接（join）和聚合函数（aggregate function）的更复杂的视图。你发现 SQLite 视图默认是只读的，但学习了如何使用 INSTEAD OF 触发器（trigger）创建真正可更新的视图。你还学习了如何正确地删除过时的视图和触发器，以维护一个干净的数据库模式（schema）。这些技能对于简化复杂查询、控制数据访问以及有效地管理你的 SQLite 数据库至关重要。
