SQLite - 视图

视图（View）是基于 SQL 语句结果集的虚拟表。你可以把它想象成一个已存储的、命名的查询。它包含行和列，就像真实的表一样，但它本身不存储数据。数据在查询视图时动态生成。

视图是现代数据库设计中的强大工具，它允许你：

简化复杂性： 将复杂的多表连接和计算抽象为一个单一、易于查询的接口。
增强安全性： 通过仅向用户公开特定列或行来限制数据访问，隐藏敏感信息。
提供稳定的 API： 为应用程序维护一致的数据结构，即使底层基础表被重构。
汇总数据： 创建聚合视图以用于报告和分析目的。

创建视图

视图使用 CREATE VIEW 语句创建。可以使用 TEMP 或 TEMPORARY 关键字来创建一个只在当前数据库连接期间存在的视图。

基本语法：

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE [condition];

示例：为活跃员工创建视图

假设有一个包含多列的 EMPLOYEES 表。我们想创建一个简单的视图，只显示当前活跃员工的姓名和部门。

-- 首先，让我们定义基础表和一些数据
CREATE TABLE EMPLOYEES (
  ID INTEGER PRIMARY KEY,
  NAME TEXT NOT NULL,
  DEPARTMENT TEXT NOT NULL,
  SALARY REAL,
  STATUS TEXT DEFAULT 'Active' -- 'Active' or 'Terminated'
);

INSERT INTO EMPLOYEES (NAME, DEPARTMENT, SALARY, STATUS)
VALUES
('Alice', 'Engineering', 90000, 'Active'),
('Bob', 'Marketing', 65000, 'Terminated'),
('Charlie', 'Engineering', 95000, 'Active');

-- 现在，创建视图
CREATE VIEW v_active_engineers AS
SELECT NAME, DEPARTMENT
FROM EMPLOYEES
WHERE STATUS = 'Active' AND DEPARTMENT = 'Engineering';

你现在可以像查询常规表一样查询这个视图：

sqlite> SELECT * FROM v_active_engineers;

这将产生以下结果：

NAME      DEPARTMENT
--------  ----------
Alice     Engineering
Charlie   Engineering

可更新视图和 INSTEAD OF 触发器

默认情况下，只有简单的视图才是可更新的（允许 INSERT、UPDATE、DELETE）：它必须从单个表进行选择，并且不能使用聚合函数（如 GROUP BY、DISTINCT 等）。

对于复杂的、只读视图，你可以使用 INSTEAD OF 触发器为 INSERT、UPDATE 或 DELETE 操作定义自定义逻辑。此触发器将执行你的自定义逻辑，而不是尝试在视图上执行操作。

示例：允许从我们的视图中删除数据。

CREATE TRIGGER trg_delete_active_engineer
INSTEAD OF DELETE ON v_active_engineers
FOR EACH ROW
BEGIN
  -- 我们不是从视图中删除，而是更新基础表
  UPDATE EMPLOYEES
  SET STATUS = 'Terminated'
  WHERE NAME = OLD.NAME;
END;

-- 现在，这个操作将会成功
sqlite> DELETE FROM v_active_engineers WHERE NAME = 'Alice';

-- 验证基础表中的变化
sqlite> SELECT NAME, STATUS FROM EMPLOYEES WHERE NAME = 'Alice';
NAME      STATUS
--------  ----------
Alice     Terminated

删除视图

要删除视图，请使用 DROP VIEW 语句。你可以添加 IF EXISTS 以防止在视图不存在时发生错误。

sqlite> DROP VIEW IF EXISTS v_active_engineers;