sqlite数据库视图原理：

视图特点：
├── 虚拟表：不存储实际数据
├── 动态生成：查询时计算
├── 只读：普通视图不可更新
├── 安全：隐藏底层表结构
└── 简化：封装复杂查询

视图类型：
├── 简单视图：单表查询
├── 复杂视图：多表关联
├── 聚合视图：包含聚合函数
└── 可更新视图：满足特定条件

视图优势：
├── 简化复杂查询
├── 提供数据安全
├── 保持数据一致性
├── 屏蔽表结构变化
└── 提高开发效率

视图限制：
├── 不能使用ORDER BY（除非有LIMIT）
├── 不能包含子查询的SELECT
├── 不能使用临时表
└── 更新有严格限制

视图语法：
CREATE VIEW view_name AS SELECT …;
DROP VIEW IF EXISTS view_name;

sqlite数据库触发器机制：

触发器特点：
├── 自动执行：满足条件时触发
├── 事件驱动：INSERT/UPDATE/DELETE
├── 时间点：BEFORE/AFTER/INSTEAD OF
├── 行级触发：FOR EACH ROW
└── 支持条件：WHEN子句

触发器类型：
├── BEFORE触发器：操作前执行
├── AFTER触发器：操作后执行
└── INSTEAD OF触发器：替代操作（视图）

触发器应用场景：
├── 数据验证
├── 审计日志
├── 数据同步
├── 级联更新
├── 计算字段
└── 业务规则

触发器语法：
CREATE TRIGGER trigger_name
{BEFORE|AFTER|INSTEAD OF}
{INSERT|UPDATE|DELETE}
ON table_name
[FOR EACH ROW] [WHEN condition]
BEGIN
— SQL statements
END;

特殊变量：
├── NEW：新行数据（INSERT/UPDATE）
├── OLD：旧行数据（UPDATE/DELETE）
└── 可在触发器体中引用

sqlite数据库CTE与高级SQL：

CTE特点：
├── 临时命名结果集
├── 只在当前查询有效
├── 可定义多个CTE
├── 支持递归查询
└── 提高可读性

CTE语法：
WITH cte_name AS (
SELECT …
)
SELECT … FROM cte_name;

递归CTE：
WITH RECURSIVE cte_name AS (
— 初始查询
SELECT …
UNION ALL
— 递归查询
SELECT … FROM cte_name WHERE …
)
SELECT … FROM cte_name;

高级SQL特性：
├── 窗口函数：OVER、PARTITION BY
├── 分析函数：ROW_NUMBER、RANK
├── 递归查询：层次结构处理
├── MERGE语句：UPSERT
└── 生成列：计算字段

窗口函数示例：
SELECT
name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as rank,
SUM(salary) OVER (PARTITION BY dept) as dept_total
FROM employees;

sqlite数据库视图应用规划：

1. 视图命名规范
├── v_前缀：v_user_info
├── 描述性名称
└── 避免与表名冲突

2. 视图使用场景
├── 报表查询
├── 数据权限控制
├── 复杂查询封装
└── 接口数据提供

3. 视图管理
├── 文档化视图用途
├── 定期审查视图
├── 避免嵌套过深
└── 监控视图性能

4. 性能考虑
├── 视图不缓存结果
├── 每次查询重新计算
├── 复杂视图影响性能
└── 考虑物化视图

视图创建示例：
— 创建基础表
CREATE TABLE fgedu_employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept_id INTEGER,
salary REAL,
hire_date DATE
);

CREATE TABLE fgedu_departments (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
manager_id INTEGER
);

— 创建简单视图
CREATE VIEW v_employee_list AS
SELECT
e.id,
e.name,
d.name as dept_name,
e.salary
FROM fgedu_employees e
LEFT JOIN fgedu_departments d ON e.dept_id = d.id;

— 创建聚合视图
CREATE VIEW v_dept_stats AS
SELECT
d.id as dept_id,
d.name as dept_name,
COUNT(e.id) as employee_count,
AVG(e.salary) as avg_salary,
MAX(e.salary) as max_salary,
MIN(e.salary) as min_salary
FROM fgedu_departments d
LEFT JOIN fgedu_employees e ON d.id = e.dept_id
GROUP BY d.id;

— 查询视图
sqlite> SELECT * FROM v_dept_stats;
1|技术部|10|15000.0|25000.0|8000.0
2|市场部|5|12000.0|18000.0|6000.0
3|人事部|3|10000.0|15000.0|5000.0

— 创建可更新视图
CREATE VIEW v_active_employees AS
SELECT id, name, dept_id, salary
FROM fgedu_employees
WHERE salary > 0;

— 创建INSTEAD OF触发器支持视图更新
CREATE TRIGGER tr_update_active_emp
INSTEAD OF UPDATE ON v_active_employees
BEGIN
UPDATE fgedu_employees
SET name = NEW.name,
dept_id = NEW.dept_id,
salary = NEW.salary
WHERE id = NEW.id;
END;

触发器创建示例：
— 创建审计表
CREATE TABLE fgedu_audit_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
table_name TEXT,
operation TEXT,
record_id INTEGER,
old_data TEXT,
new_data TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

— 创建INSERT触发器
CREATE TRIGGER tr_user_insert
AFTER INSERT ON fgedu_employees
BEGIN
INSERT INTO fgedu_audit_log (table_name, operation, record_id, new_data)
VALUES (‘fgedu_employees’, ‘INSERT’, NEW.id,
json_object(‘name’, NEW.name, ‘salary’, NEW.salary));
END;

— 创建UPDATE触发器
CREATE TRIGGER tr_user_update
AFTER UPDATE ON fgedu_employees
WHEN OLD.salary != NEW.salary
BEGIN
INSERT INTO fgedu_audit_log (table_name, operation, record_id, old_data, new_data)
VALUES (‘fgedu_employees’, ‘UPDATE’, NEW.id,
json_object(‘salary’, OLD.salary),
json_object(‘salary’, NEW.salary));
END;

— 创建DELETE触发器
CREATE TRIGGER tr_user_delete
BEFORE DELETE ON fgedu_employees
BEGIN
INSERT INTO fgedu_audit_log (table_name, operation, record_id, old_data)
VALUES (‘fgedu_employees’, ‘DELETE’, OLD.id,
json_object(‘name’, OLD.name, ‘salary’, OLD.salary));
END;

— 测试触发器
sqlite> INSERT INTO fgedu_employees (name, dept_id, salary) VALUES (‘风哥’, 1, 20000);
sqlite> SELECT * FROM fgedu_audit_log;
1|fgedu_employees|INSERT|1||{“name”:”风哥”,”salary”:20000}|2024-04-08 12:00:00

sqlite> UPDATE fgedu_employees SET salary = 25000 WHERE id = 1;
sqlite> SELECT * FROM fgedu_audit_log WHERE operation = ‘UPDATE’;
2|fgedu_employees|UPDATE|1|{“salary”:20000}|{“salary”:25000}|2024-04-08 12:01:00

CTE使用示例：
— 基础CTE
WITH dept_avg AS (
SELECT dept_id, AVG(salary) as avg_salary
FROM fgedu_employees
GROUP BY dept_id
)
SELECT
e.name,
e.salary,
d.avg_salary,
e.salary – d.avg_salary as diff
FROM fgedu_employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary;

— 多个CTE
WITH
high_salary AS (
SELECT * FROM fgedu_employees WHERE salary > 20000
),
low_salary AS (
SELECT * FROM fgedu_employees WHERE salary < 10000 ) SELECT 'high' as type, COUNT(*) as count FROM high_salary UNION ALL SELECT 'low' as type, COUNT(*) as count FROM low_salary; -- 递归CTE：组织层级 CREATE TABLE fgedu_org ( id INTEGER PRIMARY KEY, name TEXT, parent_id INTEGER ); INSERT INTO fgedu_org VALUES (1, 'CEO', NULL), (2, 'CTO', 1), (3, 'CFO', 1), (4, '研发经理', 2), (5, '测试经理', 2), (6, '开发工程师', 4); WITH RECURSIVE org_tree AS ( -- 初始查询：顶级节点 SELECT id, name, parent_id, 0 as level, name as path FROM fgedu_org WHERE parent_id IS NULL UNION ALL -- 递归查询：子节点 SELECT o.id, o.name, o.parent_id, t.level + 1, t.path || ' > ‘ || o.name
FROM fgedu_org o
JOIN org_tree t ON o.parent_id = t.id
)
SELECT id, name, level, path FROM org_tree ORDER BY path;

执行结果：
1|CEO|0|CEO
3|CFO|1|CEO > CFO
2|CTO|1|CEO > CTO
4|研发经理|2|CEO > CTO > 研发经理
5|测试经理|2|CEO > CTO > 测试经理
6|开发工程师|3|CEO > CTO > 研发经理 > 开发工程师

— 窗口函数
SELECT
name,
dept_id,
salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) as dept_rank,
RANK() OVER (ORDER BY salary DESC) as overall_rank,
SUM(salary) OVER (PARTITION BY dept_id) as dept_total
FROM fgedu_employees;

递归CTE应用案例：
— 计算数字序列
WITH RECURSIVE numbers(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10 ) SELECT * FROM numbers; 执行结果： 1 2 3 4 5 6 7 8 9 10 -- 日期序列生成 WITH RECURSIVE dates(date) AS ( SELECT date('2024-01-01') UNION ALL SELECT date(date, '+1 day') FROM dates WHERE date < '2024-01-10' ) SELECT date FROM dates; -- 物料清单展开（BOM） CREATE TABLE fgedu_bom ( id INTEGER PRIMARY KEY, parent_id INTEGER, component TEXT, quantity INTEGER ); WITH RECURSIVE bom_tree AS ( SELECT id, parent_id, component, quantity, 0 as level, component as path FROM fgedu_bom WHERE parent_id IS NULL UNION ALL SELECT b.id, b.parent_id, b.component, b.quantity, t.level + 1, t.path || ' > ‘ || b.component
FROM fgedu_bom b
JOIN bom_tree t ON b.parent_id = t.id
)
SELECT component, level, path FROM bom_tree;

sqlite数据库高级特性最佳实践：

1. 视图使用建议
├── 封装复杂查询
├── 提供数据抽象层
├── 避免嵌套过深
└── 定期审查优化

2. 触发器使用建议
├── 保持触发器简单
├── 避免递归触发
├── 注意性能影响
└── 做好错误处理

3. CTE使用建议
├── 提高可读性
├── 递归查询注意终止条件
├── 复杂查询优先CTE
└── 避免过度使用

4. 窗口函数建议
├── 排名计算
├── 累计统计
├── 移动平均
└── 分组聚合