SQLite视图 #

-- 视图是存储的查询，表现为虚拟表
-- 视图不存储数据，每次查询时动态生成

-- 视图的特点：
-- 1. 简化复杂查询
-- 2. 提供数据抽象层
-- 3. 实现数据安全
-- 4. 不占用存储空间（只存储定义）

SQLite 视图类型：
├── 普通视图
├── 临时视图（TEMP VIEW）
└── 可更新视图（有限支持）

-- CREATE VIEW 基本语法
CREATE VIEW view_name AS
SELECT statement;

-- 示例表
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    department_id INTEGER,
    salary REAL
);

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

-- 创建简单视图
CREATE VIEW user_emails AS
SELECT name, email FROM users;

-- 使用视图
SELECT * FROM user_emails;

-- 创建带JOIN的视图
CREATE VIEW user_departments AS
SELECT 
    u.name AS user_name,
    u.email,
    d.name AS department_name
FROM users u
LEFT JOIN departments d ON u.department_id = d.id;

-- 创建带聚合的视图
CREATE VIEW department_stats AS
SELECT 
    d.name AS department_name,
    COUNT(u.id) AS employee_count,
    AVG(u.salary) AS avg_salary,
    MAX(u.salary) AS max_salary,
    MIN(u.salary) AS min_salary
FROM departments d
LEFT JOIN users u ON d.id = u.department_id
GROUP BY d.id;

-- 临时视图只在当前连接存在
CREATE TEMP VIEW temp_results AS
SELECT * FROM users WHERE status = 1;

-- 或
CREATE TEMPORARY VIEW temp_results AS
SELECT * FROM users WHERE status = 1;

-- 连接关闭后自动删除

-- 避免重复创建
CREATE VIEW IF NOT EXISTS user_emails AS
SELECT name, email FROM users;

-- 像表一样查询视图
SELECT * FROM user_emails;

SELECT * FROM user_departments WHERE department_name = 'Engineering';

-- 在视图中使用WHERE
SELECT user_name, email 
FROM user_departments 
WHERE department_name = 'Engineering';

-- 在视图中使用ORDER BY
SELECT * FROM department_stats ORDER BY employee_count DESC;

-- 在另一个视图中使用视图
CREATE VIEW high_salary_employees AS
SELECT user_name, salary
FROM user_departments ud
JOIN users u ON ud.email = u.email
WHERE u.salary > 100000;

-- 或直接基于视图创建
CREATE VIEW engineering_employees AS
SELECT * FROM user_departments WHERE department_name = 'Engineering';

-- 视图可以参与JOIN
SELECT 
    ds.department_name,
    ds.employee_count,
    e.project_count
FROM department_stats ds
LEFT JOIN (
    SELECT department_id, COUNT(*) AS project_count
    FROM projects
    GROUP BY department_id
) e ON ds.department_id = e.department_id;

-- SQLite 不支持 ALTER VIEW
-- 需要删除并重建

DROP VIEW IF EXISTS user_emails;
CREATE VIEW user_emails AS
SELECT name, email, department_id FROM users;

-- 删除视图
DROP VIEW user_emails;

-- 使用 IF EXISTS
DROP VIEW IF EXISTS user_emails;

-- SQLite 对可更新视图有限支持
-- 条件：
-- 1. 视图基于单个表
-- 2. 没有 DISTINCT、GROUP BY、HAVING
-- 3. 没有聚合函数
-- 4. 没有 UNION、EXCEPT、INTERSECT
-- 5. 没有子查询

-- 可更新视图示例
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE status = 1;

-- 通过视图插入
INSERT INTO active_users (name, email) VALUES ('Test', 'test@example.com');

-- 通过视图更新
UPDATE active_users SET name = 'Updated' WHERE id = 1;

-- 通过视图删除
DELETE FROM active_users WHERE id = 1;

-- 对于复杂视图，使用INSTEAD OF触发器实现更新

-- 创建复杂视图
CREATE VIEW user_department_view AS
SELECT 
    u.id,
    u.name,
    u.email,
    d.name AS department_name
FROM users u
LEFT JOIN departments d ON u.department_id = d.id;

-- 创建INSERT触发器
CREATE TRIGGER trg_user_dept_insert
INSTEAD OF INSERT ON user_department_view
BEGIN
    INSERT OR IGNORE INTO departments (name) VALUES (NEW.department_name);
    INSERT INTO users (name, email, department_id)
    VALUES (
        NEW.name,
        NEW.email,
        (SELECT id FROM departments WHERE name = NEW.department_name)
    );
END;

-- 创建UPDATE触发器
CREATE TRIGGER trg_user_dept_update
INSTEAD OF UPDATE ON user_department_view
BEGIN
    UPDATE users 
    SET name = NEW.name, email = NEW.email
    WHERE id = OLD.id;
    
    UPDATE departments
    SET name = NEW.department_name
    WHERE id = (SELECT department_id FROM users WHERE id = OLD.id);
END;

-- 创建DELETE触发器
CREATE TRIGGER trg_user_dept_delete
INSTEAD OF DELETE ON user_department_view
BEGIN
    DELETE FROM users WHERE id = OLD.id;
END;

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

-- 查看特定视图
SELECT sql FROM sqlite_master WHERE type = 'view' AND name = 'user_emails';

-- 使用命令行
.schema user_emails

-- 列出所有视图
SELECT name FROM sqlite_master WHERE type = 'view';

-- 列出所有表和视图
SELECT name, type FROM sqlite_master WHERE type IN ('table', 'view');

-- 复杂查询封装为视图
CREATE VIEW order_summary AS
SELECT 
    o.id AS order_id,
    u.name AS customer_name,
    o.order_date,
    COUNT(oi.id) AS item_count,
    SUM(oi.quantity * oi.price) AS total_amount
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 order_summary WHERE total_amount > 1000;

-- 限制敏感数据访问
CREATE VIEW public_users AS
SELECT id, name FROM users;  -- 不暴露email和salary

-- 应用程序使用视图而不是基表
SELECT * FROM public_users;

-- 隐藏表结构变化
CREATE VIEW customer_info AS
SELECT 
    id,
    first_name || ' ' || last_name AS full_name,
    email
FROM customers;

-- 即使customers表结构变化，视图可以保持不变

-- 创建报表专用视图
CREATE VIEW monthly_sales_report AS
SELECT 
    strftime('%Y-%m', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total) AS total_sales,
    AVG(total) AS avg_order_value
FROM orders
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;

-- 生成报表
SELECT * FROM monthly_sales_report;

-- 视图不存储数据，每次查询都执行底层查询
-- 复杂视图可能影响性能

-- 对于频繁使用的复杂视图，考虑：
-- 1. 物化视图（使用表存储结果）
-- 2. 缓存结果
-- 3. 优化底层查询

-- 创建物化视图（使用表）
CREATE TABLE mv_monthly_sales AS
SELECT 
    strftime('%Y-%m', order_date) AS month,
    SUM(total) AS total_sales
FROM orders
GROUP BY strftime('%Y-%m', order_date);

-- 定期更新
DELETE FROM mv_monthly_sales;
INSERT INTO mv_monthly_sales
SELECT strftime('%Y-%m', order_date), SUM(total)
FROM orders GROUP BY strftime('%Y-%m', order_date);

-- 分析视图查询计划
EXPLAIN QUERY PLAN SELECT * FROM user_departments;

-- 检查是否使用了索引