C# SQLite高级功能示例

发表于 2025-9-6 11:29:44

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有账号？立即注册

×

目次
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高级特性：

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

2 程序布局和流程

初始化和环境设置：
- 界说数据库文件名和连接字符串
- 检查数据库文件是否存在，不存在则创建
- 调用SetupExampleData()方法创建示例数据
依次演示各个高级功能：
- 调用各个演示方法：UseTransactions(), UseIndexes(), UseViews(), UseTriggers(), UseFTS(), UseWindowFunctions(), UseForeignKeys()
每个演示方法的通用布局：
- 打印功能标题
- 创建数据库连接
- 执行相关SQL命令
- 显示执行效果
- 捕捉和处理大概的异常
辅助方法：
- 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')");
}
}

复制代码

剖析：

连接管理：使用using语句确保连接被正确关闭和资源释放，这是C#中处理IDisposable资源的最佳实践。
启用外键束缚：PRAGMA foreign_keys = ON; - SQLite默认不启用外键束缚，这行代码显式启用它，答应表之间建立引用完备性关系。
创建表布局：
- Departments表：有DeptId（主键）和Name字段
- Employees表：有EmpId（主键）、Name、DeptId（外键）、Salary和HireDate字段
- FOREIGN KEY束缚：确保Employees表中的DeptId引用Departments表中存在的DeptId
清空表数据：DELETE FROM语句确保每次运行程序时表是空的，避免重复数据。从有外键束缚的Employees表先删除是必要的，以避免违背引用完备性。
插入示例数据：向两个表添加示例数据。留意：先插入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");
}
}

复制代码

剖析：

事务管理：connection.BeginTransaction()创建一个SQLite事务，也用using语句确保适当关闭。
错误处理：try...catch块捕捉任何大概发生的异常，如果有错误发生，会回滚事务。
执行事务操作：在事务中执行两个操作：
- 插入新部分"人力资源部"
- 给全部研发部(DeptId=1)的员工加薪10%
提交或回滚：根据条件决定提交或回滚事务。在这个示例中，shouldCommit设为true，以是事务会被提交。在实际应用中，这大概基于业务逻辑或验证效果。
事务的好处：
- 原子性：多个操作要么全部执行，要么全部不执行
- 同等性：数据库从一个同等状态转换到另一个同等状态
- 隔离性：事务操作相互隔离
- 持久性：一旦提交，更改永久生存

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");
}
}

复制代码

剖析：

创建索引：
- idx_employees_dept：在Employees表的DeptId列上创建索引，优化按部分查询
- idx_employees_salary：在Employees表的Salary列上创建降序索引，优化薪资排序和范围查询
查询筹分别析：EXPLAIN QUERY PLAN显示SQLite将怎样执行查询，包括是否使用索引、使用哪些索引、按什么顺序扫描表等信息。这对于优化查询性能非常有效。
执行索引优化查询：执行一个同时使用DeptId和Salary条件的查询，应该能从创建的索引中受益。
索引优化的好处：
- 加快数据检索操作
- 减少磁盘I/O
- 资助执行ORDER BY操作
- 加快JOIN操作
留意事项：实际应用中需要均衡读写性能，因为索引会减慢写入速度。

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");
}
}

复制代码

剖析：

创建视图：CREATE VIEW语句创建了一个名为EmployeeDetails的视图，这个视图连接Employees和Departments表，并选择特定的列。视图提供了一个预界说的查询，简化了复杂查询的使用。
视图功能：视图将Employees和Departments表联接在一起，显示员工ID、姓名、薪资、所属部分名称和入职日期。
查询视图：通过简朴的SELECT * FROM EmployeeDetails查询视图，就像查询平凡表一样，但实际上视图封装了复杂的JOIN查询。
视图的好处：
- 简化复杂查询
- 提供抽象和封装
- 限定对某些列的访问，增强安全性
- 确保数据查询的同等性
留意事项：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");
}
}

复制代码

剖析：

创建审计日志表：AuditLog表用于记录数据变更操作，包罗字段：LogId、TableName（被修改的表）、Operation（执行的操作）和Timestamp（时间戳）。
创建触发器：
- after_employee_insert：在Employees表插入操作后触发，记录到审计日志
- after_employee_update：在Employees表更新操作后触发，记录到审计日志
触发操作：
- 向Employees表插入新员工，触发after_employee_insert触发器
- 更新新员工的薪资，触发after_employee_update触发器
查察效果：查询AuditLog表，显示记录的操作日志。
触发器的应用场景：
- 审计跟踪
- 业务规则验证
- 自动维护派生数据
- 级联更新或删除
- 逼迫数据同等性
留意事项：触发器会增加数据库复杂性，大概影响性能，特别是在大量数据操作时。

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编译时启用该扩展。");
}
}
}

复制代码

剖析：

创建FTS5捏造表：CREATE VIRTUAL TABLE ... USING fts5创建全文搜索表，FTS5是SQLite中最新的全文搜索引擎。
- tokenize = 'porter unicode61'：指定使用Porter词干提取算法和unicode61标志器，以支持中文等非ASCII字符。
插入示例文档：向DocumentsFTS表中插入三条文档记录，每条记录有标题、内容和作者。
执行基本全文搜索：使用MATCH操作符搜索含有"sqlite"的文档，FTS支持不区分大小写的搜索。
带排名的搜索：使用FTS的rank函数对搜索效果进行排序，包罗更多搜索词或出现频率更高的文档排名更高。
错误处理：捕捉大概的异常，因为FTS5是一个扩展，不是全部SQLite编译版本都默认启用。
FTS的上风：
- 快速文本搜索
- 支持复杂检索语法（短语、AND/OR、靠近度等）
- 支持效果排名
- 实用于全文内容检索应用
应用场景：文档搜索、应用内搜索、日志分析等。

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+版本。");
}
}
}

复制代码

剖析：

窗口函数查询：这个查询使用多种窗口函数来盘算员工数据的聚合和排名：
- 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：盘算每个员工薪资与部分均匀值的差别
PARTITION BY：按部分分组盘算，每个部分单独盘算聚合值。
ORDER BY：在窗口函数中指定排序方式，这里按薪资降序分列。
错误处理：捕捉大概的异常，因为窗口函数需要SQLite 3.25+版本支持。
窗口函数的上风：
- 在保持原始行的环境下执行聚合盘算
- 避免复杂的自连接或子查询
- 实现排名、移动均匀、累计和等复杂分析功能
- 进步查询可读性和性能
应用场景：
- 排名盘算
- 移动均匀和累计盘算
- 同比环比分析
- 分组内排序和比力

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");
}
}

复制代码

剖析：

启用外键束缚：SQLite默认不逼迫执行外键束缚，需要显式启用PRAGMA foreign_keys = ON;。
错误测试：尝试删除有员工关联的部分（DeptId=1），这应该会触发外键束缚错误，因为有员工引用这个部分。
异常处理：捕捉外键束缚违背异常，这是预期的行为，确保数据完备性。
正确的删除顺序：
- 开始事务BEGIN TRANSACTION
- 先删除引用记录：DELETE FROM Employees WHERE DeptId = 3
- 再删除被引用记录：DELETE FROM Departments WHERE DeptId = 3
- 提交事务COMMIT
查察效果：显示部分表数据，确认删除成功。
外键束缚的好处：
- 确保数据同等性和引用完备性
- 防止孤立记录
- 可配置级联动作（CASCADE、SET NULL等）
- 提供数据库级别的数据验证
留意事项：
- 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));
}
}
}
}

复制代码

剖析：

ExecuteNonQuery方法：
- 用于执行不返回效果集的SQL命令（INSERT、UPDATE、DELETE、CREATE等）
- 参数：数据库连接、SQL命令文本、可选的事务对象
- 使用using语句确保命令对象被正确释放
DisplayQueryResults方法：
- 用于执行查询并漂亮地显示效果
- 步调：
  - 创建SQL命令并执行查询获取读取器
  - 获取全部列名并显示表头
  - 循环读取每一行数据
  - 将每行格式化为表格情势并输出
数据读取本领：
- 使用reader.FieldCount获取效果集列数
- 使用reader.GetName(i)获取列名
- 使用空合并运算符??处理NULL值
- 使用连接将效果格式化为表格
方法复用：这些辅助方法表现了代码复用的好实践，避免在多个地方重复相似代码。

6 使用留意事项

连接管理：
- 总是使用using语句确保连接正确关闭
- 避免长时间保持连接打开
- 对于多线程应用，考虑使用连接池
参数化查询：
- 始终使用参数化查询防止SQL注入
- 不要通过字符串拼接构建SQL语句
事务使用：
- 批量操作使用事务进步性能
- 保持事务尽大概短小
- 正确处理事务的异常环境
性能考虑：
- 为频繁查询的列创建索引
- 使用EXPLAIN QUERY PLAN分析查询性能
- 大量数据操作时考虑批处理
版本兼容性：
- 确认目标SQLite版本是否支持需要的功能
- 窗口函数需要SQLite 3.25+
- FTS5需要特别编译支持

7 数据类型映射

C#类型和SQLite类型之间的映射：
C#类型SQLite类型int, longINTEGERdouble, floatREALstringTEXTbyte[]BLOBDateTimeTEXT/INTEGERboolINTEGER 在读取时需要留意类型转换，SQLite的动态类型系统大概需要额外的类型转换处理。
以上就是SQLite高级功能的学习资源，在里面已经详细先容了SQLite高级功能的使用以及留意事项等标题！！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

继续阅读请点击广告

C# SQLite高级功能示例

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

种地