C# SQLite高级功能示例

目录

1 主要功能

2 程序结构和流程

3 详细实现说明

3.1 基础设置

3.2 事务演示

3.3 索引演示

3.4 视图演示

3.5 触发器演示

3.6 全文搜索演示

3.7 窗口函数演示

3.8 外键约束演示

 4 高级功能示例

5 单个方法详细介绍

5.1 SetupExampleData()方法

5.2 UseTransactions() 方法

5.3 UseIndexes() 方法

5.4 UseViews() 方法

5.5 UseTriggers() 方法

5.6 UseFTS() 方法

5.7 UseWindowFunctions() 方法

5.8 UseForeignKeys() 方法

5.9 辅助方法

6 使用注意事项

7 数据类型映射

---

本文给出的C#示例程序，展示了SQLite数据库的各种高级功能使用方法。通过命名空间SQLiteAdvancedDemo下的Program类实现。

1 主要功能

这个程序演示了以下SQLite高级特性：

1. 事务管理：展示如何开始、提交和回滚事务
2. 索引使用：创建和使用索引来优化查询性能
3. 视图：创建和使用SQL视图简化复杂查询
4. 触发器：实现数据变更时自动执行的操作
5. 全文搜索(FTS5)：演示SQLite的全文搜索功能
6. 窗口函数：使用高级SQL分析功能
7. 外键约束：展示如何正确处理表之间的关系

2 程序结构和流程

1. 初始化和环境设置：

   • 定义数据库文件名和连接字符串
   • 检查数据库文件是否存在，不存在则创建
   • 调用SetupExampleData()方法创建示例数据

2. 依次演示各个高级功能：

   • 调用各个演示方法：UseTransactions(), UseIndexes(), UseViews(), UseTriggers(), UseFTS(), UseWindowFunctions(), UseForeignKeys()

3. 每个演示方法的通用结构：

   • 打印功能标题
   • 创建数据库连接
   • 执行相关SQL命令
   • 显示执行结果
   • 捕获和处理可能的异常

4. 辅助方法：

   • ExecuteNonQuery: 执行非查询SQL语句
   • DisplayQueryResults: 格式化显示查询结果

3 详细实现说明

3.1 基础设置

• 在Main方法中创建数据库文件（如不存在）并处理可能的异常
• SetupExampleData方法中创建了两个表（部门和员工），并插入了示例数据
• 启用外键约束(PRAGMA foreign_keys = ON)

3.2 事务演示

• 展示如何使用事务包装多个SQL操作
• 演示提交和回滚机制，确保数据一致性

3.3 索引演示

• 创建索引优化查询
• 使用EXPLAIN QUERY PLAN展示查询计划
• 展示带索引的查询执行

3.4 视图演示

• 创建表连接的视图简化复杂查询
• 查询视图以获取员工详细信息

3.5 触发器演示

• 创建审计日志表
• 实现插入和更新触发器
• 通过操作员工表触发这些触发器
• 显示审计日志记录

3.6 全文搜索演示

• 创建FTS5虚拟表用于文本搜索
• 插入示例文档数据
• 演示基本搜索和带排名的搜索

3.7 窗口函数演示

• 使用窗口函数进行分析计算
• 展示分区排名、平均值计算等高级功能

3.8 外键约束演示

• 展示违反外键约束时的错误处理
• 演示正确的删除顺序

---

 4 高级功能示例

using System;
using System.Data.SQLite;
using System.IO;
using System.Collections.Generic;namespace SQLiteAdvancedDemo
{class Program{private static string dbFile = "advanced_demo.db";private static string connectionString = $"Data Source={dbFile};Version=3;";static void Main(string[] args){try{// 确保数据库文件存在if (!File.Exists(dbFile)){SQLiteConnection.CreateFile(dbFile);}// 创建示例数据SetupExampleData();// 演示高级功能UseTransactions();UseIndexes();UseViews();UseTriggers();UseFTS();UseWindowFunctions();UseForeignKeys();Console.WriteLine("所有高级功能演示已完成！");}catch (Exception ex){Console.WriteLine($"发生错误: {ex.Message}");}Console.ReadKey();}// 设置示例数据static void SetupExampleData(){using (var connection = new SQLiteConnection(connectionString)){connection.Open();// 启用外键约束ExecuteNonQuery(connection, "PRAGMA foreign_keys = ON;");// 创建示例表ExecuteNonQuery(connection, @"CREATE TABLE IF NOT EXISTS Departments (DeptId INTEGER PRIMARY KEY,Name TEXT NOT NULL)");ExecuteNonQuery(connection, @"CREATE TABLE IF NOT EXISTS Employees (EmpId INTEGER PRIMARY KEY,Name TEXT NOT NULL,DeptId INTEGER,Salary REAL,HireDate TEXT,FOREIGN KEY (DeptId) REFERENCES Departments(DeptId))");// 清空表，以便重复运行ExecuteNonQuery(connection, "DELETE FROM Employees");ExecuteNonQuery(connection, "DELETE FROM Departments");// 添加部门数据ExecuteNonQuery(connection, @"INSERT INTO Departments (DeptId, Name) VALUES(1, '研发部'),(2, '市场部'),(3, '财务部')");// 添加员工数据ExecuteNonQuery(connection, @"INSERT INTO Employees (Name, DeptId, Salary, HireDate) VALUES('张三', 1, 15000, '2020-01-15'),('李四', 1, 12000, '2020-03-20'),('王五', 2, 10000, '2021-05-10'),('赵六', 2, 9500, '2021-06-01'),('钱七', 3, 11000, '2022-02-15')");}}// 使用事务static void UseTransactions(){Console.WriteLine("\n=== 事务演示 ===");using (var connection = new SQLiteConnection(connectionString)){connection.Open();// 开始事务using (var transaction = connection.BeginTransaction()){try{// 执行多个操作ExecuteNonQuery(connection, "INSERT INTO Departments (DeptId, Name) VALUES (4, '人力资源部')", transaction);ExecuteNonQuery(connection, "UPDATE Employees SET Salary = Salary * 1.1 WHERE DeptId = 1", transaction);// 假设这里有条件判断是否提交bool shouldCommit = true;if (shouldCommit){transaction.Commit();Console.WriteLine("事务已提交。");}else{transaction.Rollback();Console.WriteLine("事务已回滚。");}}catch (Exception ex){transaction.Rollback();Console.WriteLine($"发生错误，事务已回滚: {ex.Message}");}}// 显示结果Console.WriteLine("部门列表:");DisplayQueryResults(connection, "SELECT * FROM Departments");}}// 使用索引static void UseIndexes(){Console.WriteLine("\n=== 索引演示 ===");using (var connection = new SQLiteConnection(connectionString)){connection.Open();// 创建索引ExecuteNonQuery(connection, "CREATE INDEX IF NOT EXISTS idx_employees_dept ON Employees(DeptId)");ExecuteNonQuery(connection, "CREATE INDEX IF NOT EXISTS idx_employees_salary ON Employees(Salary DESC)");// 使用EXPLAIN QUERY PLAN查看查询计划Console.WriteLine("查询计划分析:");DisplayQueryResults(connection, "EXPLAIN QUERY PLAN SELECT * FROM Employees WHERE DeptId = 1 AND Salary > 10000");// 使用索引的查询Console.WriteLine("\n使用索引的查询结果:");DisplayQueryResults(connection, "SELECT * FROM Employees WHERE DeptId = 1 AND Salary > 10000");}}// 使用视图static void UseViews(){Console.WriteLine("\n=== 视图演示 ===");using (var connection = new SQLiteConnection(connectionString)){connection.Open();// 创建视图ExecuteNonQuery(connection, @"CREATE VIEW IF NOT EXISTS EmployeeDetails ASSELECT e.EmpId, e.Name, e.Salary, d.Name AS Department, e.HireDateFROM Employees eJOIN Departments d ON e.DeptId = d.DeptId");// 使用视图Console.WriteLine("视图查询结果:");DisplayQueryResults(connection, "SELECT * FROM EmployeeDetails ORDER BY Salary DESC");}}// 使用触发器static void UseTriggers(){Console.WriteLine("\n=== 触发器演示 ===");using (var connection = new SQLiteConnection(connectionString)){connection.Open();// 创建审计日志表ExecuteNonQuery(connection, @"CREATE TABLE IF NOT EXISTS AuditLog (LogId INTEGER PRIMARY KEY,TableName TEXT,Operation TEXT,Timestamp TEXT DEFAULT CURRENT_TIMESTAMP)");// 创建触发器ExecuteNonQuery(connection, @"CREATE TRIGGER IF NOT EXISTS after_employee_insertAFTER INSERT ON EmployeesBEGININSERT INTO AuditLog (TableName, Operation)VALUES ('Employees', 'INSERT');END");ExecuteNonQuery(connection, @"CREATE TRIGGER IF NOT EXISTS after_employee_updateAFTER UPDATE ON EmployeesBEGININSERT INTO AuditLog (TableName, Operation)VALUES ('Employees', 'UPDATE');END");// 触发触发器ExecuteNonQuery(connection, "INSERT INTO Employees (Name, DeptId, Salary) VALUES ('新员工', 2, 8000)");ExecuteNonQuery(connection, "UPDATE Employees SET Salary = 8500 WHERE Name = '新员工'");// 显示审计日志Console.WriteLine("审计日志:");DisplayQueryResults(connection, "SELECT * FROM AuditLog");}}// 使用全文搜索static void UseFTS(){Console.WriteLine("\n=== 全文搜索(FTS5)演示 ===");using (var connection = new SQLiteConnection(connectionString)){connection.Open();try{// 创建FTS5虚拟表ExecuteNonQuery(connection, @"CREATE VIRTUAL TABLE IF NOT EXISTS DocumentsFTS USING fts5(Title, Content, Author,tokenize = 'porter unicode61')");// 插入示例文档ExecuteNonQuery(connection, @"INSERT INTO DocumentsFTS (Title, Content, Author) VALUES('SQLite教程', '这是一份关于SQLite数据库的完整教程，包括基本操作和高级功能。', '张三'),('C#编程入门', 'C#是微软开发的面向对象编程语言，用于.NET平台开发。', '李四'),('数据库设计', '良好的数据库设计遵循范式和性能优化原则。SQLite是轻量级数据库的首选。', '王五')");// 执行全文搜索Console.WriteLine("搜索'SQLite'的结果:");DisplayQueryResults(connection, "SELECT * FROM DocumentsFTS WHERE DocumentsFTS MATCH 'sqlite'");// 带排名的搜索Console.WriteLine("\n带排名的搜索结果:");DisplayQueryResults(connection, @"SELECT Title, Author, rankFROM DocumentsFTSWHERE DocumentsFTS MATCH 'sqlite OR 数据库'ORDER BY rank");}catch (SQLiteException ex){Console.WriteLine($"FTS5可能未启用: {ex.Message}");Console.WriteLine("注意: 使用FTS5需要SQLite编译时启用该扩展。");}}}// 使用窗口函数static void UseWindowFunctions(){Console.WriteLine("\n=== 窗口函数演示 ===");using (var connection = new SQLiteConnection(connectionString)){connection.Open();try{// 使用窗口函数string windowQuery = @"SELECT e.Name,d.Name AS Department,e.Salary,RANK() OVER (PARTITION BY e.DeptId ORDER BY e.Salary DESC) AS DeptRank,AVG(e.Salary) OVER (PARTITION BY e.DeptId) AS AvgDeptSalary,e.Salary - AVG(e.Salary) OVER (PARTITION BY e.DeptId) AS DiffFromAvgFROM Employees eJOIN Departments d ON e.DeptId = d.DeptIdORDER BY d.Name, DeptRank";Console.WriteLine("窗口函数查询结果:");DisplayQueryResults(connection, windowQuery);}catch (SQLiteException ex){Console.WriteLine($"窗口函数可能未支持: {ex.Message}");Console.WriteLine("注意: 窗口函数需要SQLite 3.25+版本。");}}}// 使用外键约束static void UseForeignKeys(){Console.WriteLine("\n=== 外键约束演示 ===");using (var connection = new SQLiteConnection(connectionString)){connection.Open();// 确保外键约束已开启ExecuteNonQuery(connection, "PRAGMA foreign_keys = ON;");// 尝试删除有关联记录的部门try{ExecuteNonQuery(connection, "DELETE FROM Departments WHERE DeptId = 1");Console.WriteLine("删除成功"); // 不应该执行到此处}catch (SQLiteException ex){Console.WriteLine($"预期的外键约束错误: {ex.Message}");}// 正确的删除顺序 - 先删除关联记录Console.WriteLine("\n按正确顺序删除:");ExecuteNonQuery(connection, "BEGIN TRANSACTION");ExecuteNonQuery(connection, "DELETE FROM Employees WHERE DeptId = 3");ExecuteNonQuery(connection, "DELETE FROM Departments WHERE DeptId = 3");ExecuteNonQuery(connection, "COMMIT");Console.WriteLine("部门列表:");DisplayQueryResults(connection, "SELECT * FROM Departments");}}// 执行非查询语句的辅助方法static void ExecuteNonQuery(SQLiteConnection connection, string sql, SQLiteTransaction transaction = null){using (var command = new SQLiteCommand(sql, connection, transaction)){command.ExecuteNonQuery();}}// 显示查询结果的辅助方法static void DisplayQueryResults(SQLiteConnection connection, string sql){using (var command = new SQLiteCommand(sql, connection)){using (var reader = command.ExecuteReader()){// 获取列名List<string> columns = new List<string>();for (int i = 0; i < reader.FieldCount; i++){columns.Add(reader.GetName(i));}// 输出列名Console.WriteLine(string.Join(" | ", columns));Console.WriteLine(new string('-', columns.Count * 15));// 输出行while (reader.Read()){List<string> values = new List<string>();for (int i = 0; i < reader.FieldCount; i++){values.Add(reader[i]?.ToString() ?? "NULL");}Console.WriteLine(string.Join(" | ", values));}}}}}
}

5 单个方法详细介绍

5.1 SetupExampleData()方法

static void SetupExampleData()
{using (var connection = new SQLiteConnection(connectionString)){connection.Open();// 启用外键约束ExecuteNonQuery(connection, "PRAGMA foreign_keys = ON;");// 创建示例表ExecuteNonQuery(connection, @"CREATE TABLE IF NOT EXISTS Departments (DeptId INTEGER PRIMARY KEY,Name TEXT NOT NULL)");ExecuteNonQuery(connection, @"CREATE TABLE IF NOT EXISTS Employees (EmpId INTEGER PRIMARY KEY,Name TEXT NOT NULL,DeptId INTEGER,Salary REAL,HireDate TEXT,FOREIGN KEY (DeptId) REFERENCES Departments(DeptId))");// 清空表，以便重复运行ExecuteNonQuery(connection, "DELETE FROM Employees");ExecuteNonQuery(connection, "DELETE FROM Departments");// 添加部门数据ExecuteNonQuery(connection, @"INSERT INTO Departments (DeptId, Name) VALUES(1, '研发部'),(2, '市场部'),(3, '财务部')");// 添加员工数据ExecuteNonQuery(connection, @"INSERT INTO Employees (Name, DeptId, Salary, HireDate) VALUES('张三', 1, 15000, '2020-01-15'),('李四', 1, 12000, '2020-03-20'),('王五', 2, 10000, '2021-05-10'),('赵六', 2, 9500, '2021-06-01'),('钱七', 3, 11000, '2022-02-15')");}
}

解析：

1. 连接管理：使用using语句确保连接被正确关闭和资源释放，这是C#中处理IDisposable资源的最佳实践。

2. 启用外键约束：PRAGMA foreign_keys = ON; - SQLite默认不启用外键约束，这行代码显式启用它，允许表之间建立引用完整性关系。

3. 创建表结构：

   • Departments表：有DeptId（主键）和Name字段

   • Employees表：有EmpId（主键）、Name、DeptId（外键）、Salary和HireDate字段

   • FOREIGN KEY约束：确保Employees表中的DeptId引用Departments表中存在的DeptId

4. 清空表数据：DELETE FROM语句确保每次运行程序时表是空的，避免重复数据。从有外键约束的Employees表先删除是必要的，以避免违反引用完整性。

5. 插入示例数据：向两个表添加示例数据。注意：先插入Departments数据，再插入Employees数据，这是因为Employees表的外键引用了Departments表。

5.2 UseTransactions() 方法

static void UseTransactions()
{Console.WriteLine("\n=== 事务演示 ===");using (var connection = new SQLiteConnection(connectionString)){connection.Open();// 开始事务using (var transaction = connection.BeginTransaction()){try{// 执行多个操作ExecuteNonQuery(connection, "INSERT INTO Departments (DeptId, Name) VALUES (4, '人力资源部')", transaction);ExecuteNonQuery(connection, "UPDATE Employees SET Salary = Salary * 1.1 WHERE DeptId = 1", transaction);// 假设这里有条件判断是否提交bool shouldCommit = true;if (shouldCommit){transaction.Commit();Console.WriteLine("事务已提交。");}else{transaction.Rollback();Console.WriteLine("事务已回滚。");}}catch (Exception ex){transaction.Rollback();Console.WriteLine($"发生错误，事务已回滚: {ex.Message}");}}// 显示结果Console.WriteLine("部门列表:");DisplayQueryResults(connection, "SELECT * FROM Departments");}
}

解析：

1. 事务管理：connection.BeginTransaction()创建一个SQLite事务，也用using语句确保适当关闭。

2. 错误处理：try...catch块捕获任何可能发生的异常，如果有错误发生，会回滚事务。

3. 执行事务操作：在事务中执行两个操作：

   • 插入新部门"人力资源部"

   • 给所有研发部(DeptId=1)的员工加薪10%

4. 提交或回滚：根据条件决定提交或回滚事务。在这个示例中，shouldCommit设为true，所以事务会被提交。在实际应用中，这可能基于业务逻辑或验证结果。

5. 事务的好处：

   • 原子性：多个操作要么全部执行，要么全部不执行

   • 一致性：数据库从一个一致状态转换到另一个一致状态

   • 隔离性：事务操作相互隔离

   • 持久性：一旦提交，更改永久保存

5.3 UseIndexes() 方法

static void UseIndexes()
{Console.WriteLine("\n=== 索引演示 ===");using (var connection = new SQLiteConnection(connectionString)){connection.Open();// 创建索引ExecuteNonQuery(connection, "CREATE INDEX IF NOT EXISTS idx_employees_dept ON Employees(DeptId)");ExecuteNonQuery(connection, "CREATE INDEX IF NOT EXISTS idx_employees_salary ON Employees(Salary DESC)");// 使用EXPLAIN QUERY PLAN查看查询计划Console.WriteLine("查询计划分析:");DisplayQueryResults(connection, "EXPLAIN QUERY PLAN SELECT * FROM Employees WHERE DeptId = 1 AND Salary > 10000");// 使用索引的查询Console.WriteLine("\n使用索引的查询结果:");DisplayQueryResults(connection, "SELECT * FROM Employees WHERE DeptId = 1 AND Salary > 10000");}
}

解析：

1. 创建索引：

   • idx_employees_dept：在Employees表的DeptId列上创建索引，优化按部门查询

   • idx_employees_salary：在Employees表的Salary列上创建降序索引，优化薪资排序和范围查询

2. 查询计划分析：EXPLAIN QUERY PLAN显示SQLite将如何执行查询，包括是否使用索引、使用哪些索引、按什么顺序扫描表等信息。这对于优化查询性能非常有用。

3. 执行索引优化查询：执行一个同时使用DeptId和Salary条件的查询，应该能从创建的索引中受益。

4. 索引优化的好处：

   • 加速数据检索操作

   • 减少磁盘I/O

   • 帮助执行ORDER BY操作

   • 加速JOIN操作

5. 注意事项：实际应用中需要平衡读写性能，因为索引会减慢写入速度。 

5.4 UseViews() 方法

static void UseViews()
{Console.WriteLine("\n=== 视图演示 ===");using (var connection = new SQLiteConnection(connectionString)){connection.Open();// 创建视图ExecuteNonQuery(connection, @"CREATE VIEW IF NOT EXISTS EmployeeDetails ASSELECT e.EmpId, e.Name, e.Salary, d.Name AS Department, e.HireDateFROM Employees eJOIN Departments d ON e.DeptId = d.DeptId");// 使用视图Console.WriteLine("视图查询结果:");DisplayQueryResults(connection, "SELECT * FROM EmployeeDetails ORDER BY Salary DESC");}
}

解析：

1. 创建视图：CREATE VIEW语句创建了一个名为EmployeeDetails的视图，这个视图连接Employees和Departments表，并选择特定的列。视图提供了一个预定义的查询，简化了复杂查询的使用。

2. 视图功能：视图将Employees和Departments表联接在一起，显示员工ID、姓名、薪资、所属部门名称和入职日期。

3. 查询视图：通过简单的SELECT * FROM EmployeeDetails查询视图，就像查询普通表一样，但实际上视图封装了复杂的JOIN查询。

4. 视图的好处：

   • 简化复杂查询

   • 提供抽象和封装

   • 限制对某些列的访问，增强安全性

   • 确保数据查询的一致性

5. 注意事项：SQLite视图是只读的，不支持可更新视图。

5.5 UseTriggers() 方法

static void UseTriggers()
{Console.WriteLine("\n=== 触发器演示 ===");using (var connection = new SQLiteConnection(connectionString)){connection.Open();// 创建审计日志表ExecuteNonQuery(connection, @"CREATE TABLE IF NOT EXISTS AuditLog (LogId INTEGER PRIMARY KEY,TableName TEXT,Operation TEXT,Timestamp TEXT DEFAULT CURRENT_TIMESTAMP)");// 创建触发器ExecuteNonQuery(connection, @"CREATE TRIGGER IF NOT EXISTS after_employee_insertAFTER INSERT ON EmployeesBEGININSERT INTO AuditLog (TableName, Operation)VALUES ('Employees', 'INSERT');END");ExecuteNonQuery(connection, @"CREATE TRIGGER IF NOT EXISTS after_employee_updateAFTER UPDATE ON EmployeesBEGININSERT INTO AuditLog (TableName, Operation)VALUES ('Employees', 'UPDATE');END");// 触发触发器ExecuteNonQuery(connection, "INSERT INTO Employees (Name, DeptId, Salary) VALUES ('新员工', 2, 8000)");ExecuteNonQuery(connection, "UPDATE Employees SET Salary = 8500 WHERE Name = '新员工'");// 显示审计日志Console.WriteLine("审计日志:");DisplayQueryResults(connection, "SELECT * FROM AuditLog");}
}

解析：

1. 创建审计日志表：AuditLog表用于记录数据变更操作，包含字段：LogId、TableName（被修改的表）、Operation（执行的操作）和Timestamp（时间戳）。

2. 创建触发器：

   • after_employee_insert：在Employees表插入操作后触发，记录到审计日志

   • after_employee_update：在Employees表更新操作后触发，记录到审计日志

3. 触发操作：

   • 向Employees表插入新员工，触发after_employee_insert触发器

   • 更新新员工的薪资，触发after_employee_update触发器

4. 查看结果：查询AuditLog表，显示记录的操作日志。

5. 触发器的应用场景：

   • 审计跟踪

   • 业务规则验证

   • 自动维护派生数据

   • 级联更新或删除

   • 强制数据一致性

6. 注意事项：触发器会增加数据库复杂性，可能影响性能，特别是在大量数据操作时。

5.6 UseFTS() 方法

static void UseFTS()
{Console.WriteLine("\n=== 全文搜索(FTS5)演示 ===");using (var connection = new SQLiteConnection(connectionString)){connection.Open();try{// 创建FTS5虚拟表ExecuteNonQuery(connection, @"CREATE VIRTUAL TABLE IF NOT EXISTS DocumentsFTS USING fts5(Title, Content, Author,tokenize = 'porter unicode61')");// 插入示例文档ExecuteNonQuery(connection, @"INSERT INTO DocumentsFTS (Title, Content, Author) VALUES('SQLite教程', '这是一份关于SQLite数据库的完整教程，包括基本操作和高级功能。', '张三'),('C#编程入门', 'C#是微软开发的面向对象编程语言，用于.NET平台开发。', '李四'),('数据库设计', '良好的数据库设计遵循范式和性能优化原则。SQLite是轻量级数据库的首选。', '王五')");// 执行全文搜索Console.WriteLine("搜索'SQLite'的结果:");DisplayQueryResults(connection, "SELECT * FROM DocumentsFTS WHERE DocumentsFTS MATCH 'sqlite'");// 带排名的搜索Console.WriteLine("\n带排名的搜索结果:");DisplayQueryResults(connection, @"SELECT Title, Author, rankFROM DocumentsFTSWHERE DocumentsFTS MATCH 'sqlite OR 数据库'ORDER BY rank");}catch (SQLiteException ex){Console.WriteLine($"FTS5可能未启用: {ex.Message}");Console.WriteLine("注意: 使用FTS5需要SQLite编译时启用该扩展。");}}
}

解析：

1. 创建FTS5虚拟表：CREATE VIRTUAL TABLE ... USING fts5创建全文搜索表，FTS5是SQLite中最新的全文搜索引擎。

   • tokenize = 'porter unicode61'：指定使用Porter词干提取算法和unicode61标记器，以支持中文等非ASCII字符。

2. 插入示例文档：向DocumentsFTS表中插入三条文档记录，每条记录有标题、内容和作者。

3. 执行基本全文搜索：使用MATCH操作符搜索含有"sqlite"的文档，FTS支持不区分大小写的搜索。

4. 带排名的搜索：使用FTS的rank函数对搜索结果进行排序，包含更多搜索词或出现频率更高的文档排名更高。

5. 错误处理：捕获可能的异常，因为FTS5是一个扩展，不是所有SQLite编译版本都默认启用。

6. FTS的优势：

   • 快速文本搜索

   • 支持复杂检索语法（短语、AND/OR、接近度等）

   • 支持结果排名

   • 适用于全文内容检索应用

7. 应用场景：文档搜索、应用内搜索、日志分析等。

5.7 UseWindowFunctions() 方法

static void UseWindowFunctions()
{Console.WriteLine("\n=== 窗口函数演示 ===");using (var connection = new SQLiteConnection(connectionString)){connection.Open();try{// 使用窗口函数string windowQuery = @"SELECT e.Name,d.Name AS Department,e.Salary,RANK() OVER (PARTITION BY e.DeptId ORDER BY e.Salary DESC) AS DeptRank,AVG(e.Salary) OVER (PARTITION BY e.DeptId) AS AvgDeptSalary,e.Salary - AVG(e.Salary) OVER (PARTITION BY e.DeptId) AS DiffFromAvgFROM Employees eJOIN Departments d ON e.DeptId = d.DeptIdORDER BY d.Name, DeptRank";Console.WriteLine("窗口函数查询结果:");DisplayQueryResults(connection, windowQuery);}catch (SQLiteException ex){Console.WriteLine($"窗口函数可能未支持: {ex.Message}");Console.WriteLine("注意: 窗口函数需要SQLite 3.25+版本。");}}
}

解析：

1. 窗口函数查询：这个查询使用多种窗口函数来计算员工数据的聚合和排名：

   • RANK() OVER (PARTITION BY e.DeptId ORDER BY e.Salary DESC) AS DeptRank：计算每个员工在其部门内按薪资排名

   • AVG(e.Salary) OVER (PARTITION BY e.DeptId) AS AvgDeptSalary：计算每个部门的平均薪资

   • e.Salary - AVG(e.Salary) OVER (PARTITION BY e.DeptId) AS DiffFromAvg：计算每个员工薪资与部门平均值的差异

2. PARTITION BY：按部门分组计算，每个部门单独计算聚合值。

3. ORDER BY：在窗口函数中指定排序方式，这里按薪资降序排列。

4. 错误处理：捕获可能的异常，因为窗口函数需要SQLite 3.25+版本支持。

5. 窗口函数的优势：

   • 在保持原始行的情况下执行聚合计算

   • 避免复杂的自连接或子查询

   • 实现排名、移动平均、累计和等复杂分析功能

   • 提高查询可读性和性能

6. 应用场景：

   • 排名计算

   • 移动平均和累计计算

   • 同比环比分析

   • 分组内排序和比较

5.8 UseForeignKeys() 方法

static void UseForeignKeys()
{Console.WriteLine("\n=== 外键约束演示 ===");using (var connection = new SQLiteConnection(connectionString)){connection.Open();// 确保外键约束已开启ExecuteNonQuery(connection, "PRAGMA foreign_keys = ON;");// 尝试删除有关联记录的部门try{ExecuteNonQuery(connection, "DELETE FROM Departments WHERE DeptId = 1");Console.WriteLine("删除成功"); // 不应该执行到此处}catch (SQLiteException ex){Console.WriteLine($"预期的外键约束错误: {ex.Message}");}// 正确的删除顺序 - 先删除关联记录Console.WriteLine("\n按正确顺序删除:");ExecuteNonQuery(connection, "BEGIN TRANSACTION");ExecuteNonQuery(connection, "DELETE FROM Employees WHERE DeptId = 3");ExecuteNonQuery(connection, "DELETE FROM Departments WHERE DeptId = 3");ExecuteNonQuery(connection, "COMMIT");Console.WriteLine("部门列表:");DisplayQueryResults(connection, "SELECT * FROM Departments");}
}

解析：

1. 启用外键约束：SQLite默认不强制执行外键约束，需要显式启用PRAGMA foreign_keys = ON;。

2. 错误测试：尝试删除有员工关联的部门（DeptId=1），这应该会触发外键约束错误，因为有员工引用这个部门。

3. 异常处理：捕获外键约束违反异常，这是预期的行为，确保数据完整性。

4. 正确的删除顺序：

   • 开始事务BEGIN TRANSACTION

   • 先删除引用记录：DELETE FROM Employees WHERE DeptId = 3

   • 再删除被引用记录：DELETE FROM Departments WHERE DeptId = 3

   • 提交事务COMMIT

5. 查看结果：显示部门表数据，确认删除成功。

6. 外键约束的好处：

   • 确保数据一致性和引用完整性

   • 防止孤立记录

   • 可配置级联动作（CASCADE、SET NULL等）

   • 提供数据库级别的数据验证

7. 注意事项：

   • SQLite外键约束需要显式启用

   • 多表操作需要考虑正确的顺序

   • 事务可以确保多步操作的原子性

5.9 辅助方法

// 执行非查询语句的辅助方法
static void ExecuteNonQuery(SQLiteConnection connection, string sql, SQLiteTransaction transaction = null)
{using (var command = new SQLiteCommand(sql, connection, transaction)){command.ExecuteNonQuery();}
}// 显示查询结果的辅助方法
static void DisplayQueryResults(SQLiteConnection connection, string sql)
{using (var command = new SQLiteCommand(sql, connection)){using (var reader = command.ExecuteReader()){// 获取列名List<string> columns = new List<string>();for (int i = 0; i < reader.FieldCount; i++){columns.Add(reader.GetName(i));}// 输出列名Console.WriteLine(string.Join(" | ", columns));Console.WriteLine(new string('-', columns.Count * 15));// 输出行while (reader.Read()){List<string> values = new List<string>();for (int i = 0; i < reader.FieldCount; i++){values.Add(reader[i]?.ToString() ?? "NULL");}Console.WriteLine(string.Join(" | ", values));}}}
}

解析：

1. ExecuteNonQuery方法：

   • 用于执行不返回结果集的SQL命令（INSERT、UPDATE、DELETE、CREATE等）

   • 参数：数据库连接、SQL命令文本、可选的事务对象

   • 使用using语句确保命令对象被正确释放

2. DisplayQueryResults方法：

   • 用于执行查询并漂亮地显示结果

   • 步骤：

     • 创建SQL命令并执行查询获取读取器

     • 获取所有列名并显示表头

     • 循环读取每一行数据

     • 将每行格式化为表格形式并输出

3. 数据读取技巧：

   • 使用reader.FieldCount获取结果集列数

   • 使用reader.GetName(i)获取列名

   • 使用空合并运算符??处理NULL值

   • 使用连接将结果格式化为表格

4. 方法复用：这些辅助方法体现了代码复用的好实践，避免在多个地方重复相似代码。

6 使用注意事项

1. 连接管理：

   • 总是使用using语句确保连接正确关闭

   • 避免长时间保持连接打开

   • 对于多线程应用，考虑使用连接池

2. 参数化查询：

   • 始终使用参数化查询防止SQL注入

   • 不要通过字符串拼接构建SQL语句

3. 事务使用：

   • 批量操作使用事务提高性能

   • 保持事务尽可能短小

   • 正确处理事务的异常情况

4. 性能考虑：

   • 为频繁查询的列创建索引

   • 使用EXPLAIN QUERY PLAN分析查询性能

   • 大量数据操作时考虑批处理

5. 版本兼容性：

   • 确认目标SQLite版本是否支持需要的功能

   • 窗口函数需要SQLite 3.25+

   • FTS5需要特殊编译支持 

7 数据类型映射

C#类型和SQLite类型之间的映射：

C#类型	SQLite类型
int, long	INTEGER
double, float	REAL
string	TEXT
byte[]	BLOB
DateTime	TEXT/INTEGER
bool	INTEGER

在读取时需要注意类型转换，SQLite的动态类型系统可能需要额外的类型转换处理。

以上就是SQLite高级功能的学习资源，在里面已经详细介绍了SQLite高级功能的使用以及注意事项等问题！！