SQLite 视图（View）

在 SQLite 中，视图（View） 是一种 虚拟表，基于 SELECT 查询结果，不存储实际数据，仅保存定义。

优点：简化复杂查询、提供数据安全抽象、逻辑封装
缺点：不可索引，性能依赖底层表

---

一、创建视图

基本语法

CREATE [TEMP] VIEW [IF NOT EXISTS] 视图名 AS
SELECT ...;

选项	说明
TEMP	临时视图，连接关闭即消失
IF NOT EXISTS	避免重复创建报错

---

示例

-- 简单视图：用户基本信息
CREATE VIEW user_info AS
SELECT id, name, email
FROM users;

-- 复杂视图：带 JOIN 和计算
CREATE VIEW order_summary AS
SELECT 
    o.id,
    u.name AS customer,
    o.order_date,
    SUM(oi.quantity * oi.price) AS total
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id;

---

二、使用视图

完全像表一样使用：

-- 查询
SELECT * FROM user_info WHERE id = 1;

-- 嵌套
SELECT customer, COUNT(*) FROM order_summary GROUP BY customer;

-- JOIN
SELECT v.*, u.phone
FROM user_info v
JOIN users u ON v.id = u.id;

---

三、修改视图（不推荐直接改）

-- 替换定义
CREATE OR REPLACE VIEW user_info AS
SELECT id, name, email, status FROM users;

SQLite 不支持 ALTER VIEW，只能 DROP + CREATE

---

四、删除视图

DROP VIEW IF EXISTS 视图名;

---

五、视图 vs 表：关键区别

特性	视图	表
存储数据	❌ 虚拟	✅ 物理
可更新	⚠️ 限制多	✅
可建索引	❌	✅
性能	依赖底层查询	更快（+索引）
自动刷新	✅ 实时	✅

---

六、可更新视图（限制严格）

只有 简单视图 才能 INSERT/UPDATE/DELETE。

允许的条件（必须同时满足）：

1. 仅一个基表
2. 无 JOIN、GROUP BY、DISTINCT、聚合函数
3. 无子查询、UNION
4. 包含基表所有 NOT NULL 列（或有默认值）

---

示例：可更新视图

CREATE VIEW active_users AS
SELECT id, name, email, status
FROM users
WHERE status = 'active';

-- 可以更新！
UPDATE active_users SET name = 'Bob' WHERE id = 1;

---

使用 INSTEAD OF 触发器实现复杂更新

-- 不可更新视图（JOIN）
CREATE VIEW user_orders AS
SELECT u.id, u.name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 用触发器实现插入
CREATE TRIGGER insert_user_orders
INSTEAD OF INSERT ON user_orders
FOR EACH ROW
BEGIN
    INSERT INTO orders (user_id, order_date)
    VALUES (NEW.id, NEW.order_date);
END;

---

七、查看视图

-- 查看定义
SELECT sql FROM sqlite_master 
WHERE type = 'view' AND name = 'user_info';

-- 查看所有视图
SELECT name, sql FROM sqlite_master WHERE type = 'view';

---

八、递归视图（CTE 替代）

SQLite 支持递归 CTE，可替代递归视图：

-- 组织架构上下级
WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 0 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, o.level + 1
    FROM employees e
    JOIN org_chart o ON e.manager_id = o.id
)
SELECT * FROM org_chart;

---

九、最佳实践

场景	推荐
频繁使用的复杂查询	✅ 建视图
数据安全（隐藏列）	✅ 建视图 + 权限控制
需要写操作	⚠️ 用 INSTEAD OF 触发器
性能敏感	❌ 避免复杂视图，用临时表或索引
开发调试	✅ 临时视图 CREATE TEMP VIEW

---

十、完整示例：电商视图系统

-- 1. 用户摘要
CREATE VIEW customer_summary AS
SELECT 
    id,
    name,
    email,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- 2. 订单详情（JOIN）
CREATE VIEW order_details AS
SELECT 
    o.id,
    u.name AS customer,
    o.order_date,
    p.name AS product,
    oi.quantity,
    oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- 3. 不可更新？加触发器！
CREATE TRIGGER update_order_status
INSTEAD OF UPDATE ON order_details
FOR EACH ROW
WHEN OLD.id = NEW.id
BEGIN
    UPDATE orders SET status = NEW.status WHERE id = OLD.id;
END;

---

总结：视图核心要点

项目	内容
本质	保存的 SELECT 语句
存储	不存数据，实时计算
更新	简单视图可直接写，复杂用 INSTEAD OF
性能	无索引，依赖基表
替代	复杂逻辑建议用 CTE 或应用层

---

需要我帮你 把一个复杂 SQL 封装成视图？
或 为你的表设计一套视图层？把需求发我！