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 AS
                    SELECT e.EmpId, e.Name, e.Salary, d.Name AS Department, e.HireDate
                    FROM Employees e
                    JOIN 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_insert
                    AFTER INSERT ON Employees
                    BEGIN
                        INSERT INTO AuditLog (TableName, Operation)
                        VALUES ('Employees', 'INSERT');
                    END");
                
                ExecuteNonQuery(connection, @"
                    CREATE TRIGGER IF NOT EXISTS after_employee_update
                    AFTER UPDATE ON Employees
                    BEGIN
                        INSERT 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, rank
                        FROM DocumentsFTS
                        WHERE 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 DiffFromAvg
                        FROM Employees e
                        JOIN Departments d ON e.DeptId = d.DeptId
                        ORDER 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 AS
            SELECT e.EmpId, e.Name, e.Salary, d.Name AS Department, e.HireDate
            FROM Employees e
            JOIN 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_insert
            AFTER INSERT ON Employees
            BEGIN
                INSERT INTO AuditLog (TableName, Operation)
                VALUES ('Employees', 'INSERT');
            END");
        
        ExecuteNonQuery(connection, @"
            CREATE TRIGGER IF NOT EXISTS after_employee_update
            AFTER UPDATE ON Employees
            BEGIN
                INSERT 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, rank
                FROM DocumentsFTS
                WHERE 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 DiffFromAvg
                FROM Employees e
                JOIN Departments d ON e.DeptId = d.DeptId
                ORDER 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类型之间的映射：

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

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