SQLite使用手册：轻量级数据库的全方位指南

一、SQLite概述：轻量级数据库的标杆

SQLite是一款开源的嵌入式关系型数据库，以其零配置、无服务器、单文件存储的特性，成为移动应用、桌面程序及小型Web服务的首选数据库。其核心优势包括：

1. 零依赖部署：无需单独服务器进程，数据库以单个文件（.db或.sqlite）形式存在，可直接嵌入到应用程序中。
2. 跨平台兼容：支持Windows、Linux、macOS、iOS、Android等主流操作系统，代码可移植性强。
3. 高性能：采用B+树索引结构，支持事务（ACID特性），读写效率接近内存数据库。
4. 标准兼容：支持大部分SQL92标准语法，包括事务、触发器、视图等高级功能。

典型应用场景包括：移动应用本地数据存储（如Flutter/React Native）、桌面工具数据管理（如Firefox浏览器配置）、物联网设备数据采集等。

二、安装与配置：快速上手

1. 安装方式

• Linux/macOS：通过包管理器安装（如sudo apt install sqlite3或brew install sqlite）。
• Windows：下载预编译二进制文件（SQLite官网下载页面），解压后配置环境变量。
• 编程语言集成：
  • Python：pip install pysqlite3或直接使用内置sqlite3模块。
  • Java：通过JDBC驱动（如sqlite-jdbc）。
  • C/C++：编译时链接sqlite3.c源文件。

2. 命令行工具使用

启动SQLite命令行界面：

sqlite3 test.db  # 创建或打开test.db数据库

常用命令：

• .tables：列出所有表。
• .schema 表名：查看表结构。
• .exit：退出命令行。
• .help：显示完整帮助文档。

三、基础操作：CRUD与表管理

1. 创建表与数据类型

SQLite支持的数据类型包括：

• INTEGER：整型（1,2,4,8字节）。
• REAL：浮点型。
• TEXT：文本字符串。
• BLOB：二进制数据。
• NULL：空值。

示例：创建用户表

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 插入数据

INSERT INTO users (name, age, email) 
VALUES ('Alice', 25, 'alice@example.com');

批量插入优化：

BEGIN TRANSACTION;
INSERT INTO users VALUES (NULL, 'Bob', 30, 'bob@example.com');
INSERT INTO users VALUES (NULL, 'Charlie', 28, 'charlie@example.com');
COMMIT;

3. 查询数据

基础查询：

SELECT * FROM users WHERE age > 20 ORDER BY name DESC;

高级查询：

• 分页：SELECT * FROM users LIMIT 10 OFFSET 20;
• 聚合函数：SELECT COUNT(*), AVG(age) FROM users;
• 连接查询（需通过外键关联）：

  CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
  );
  SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id;

4. 更新与删除

UPDATE users SET age = 26 WHERE name = 'Alice';
DELETE FROM users WHERE id = 3;

四、高级功能：事务、索引与扩展

1. 事务处理

SQLite默认启用事务，可通过显式语句控制：

BEGIN TRANSACTION;
-- 执行多条SQL
INSERT INTO logs VALUES (NULL, 'Operation started');
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- 或 ROLLBACK; 回滚

2. 索引优化

创建索引加速查询：

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_age_name ON users(age, name);  -- 复合索引

索引使用原则：

• 对高频查询的列创建索引。
• 避免过度索引（写入性能下降）。
• 复合索引需遵循最左前缀原则。

3. 扩展功能

• 全文搜索（FTS）：通过CREATE VIRTUAL TABLE实现：

  CREATE VIRTUAL TABLE docs USING fts5(title, content);
  INSERT INTO docs VALUES ('SQLite Guide', 'SQLite is a lightweight database');
  SELECT * FROM docs WHERE docs MATCH 'lightweight';

• JSON支持（SQLite 3.38+）：

  SELECT json_extract(config, '$.theme') FROM settings;

五、最佳实践与性能调优

1. 连接管理

• 避免频繁开关数据库连接（嵌入式场景无需显式关闭）。
• 多线程环境下使用sqlite3_open_v2的SQLITE_OPEN_FULLMUTEX标志。

2. 备份与恢复

• 在线备份API（C语言示例）：

  sqlite3_backup_init(dest_db, "main", src_db, "main");
  sqlite3_backup_step(backup, -1);  // -1表示复制全部
  sqlite3_backup_finish(backup);

• 命令行备份：

  sqlite3 mydb.db ".backup backup.db"

3. 性能优化技巧

• 批量操作：使用事务包裹批量插入/更新。
• PRAGMA配置：

  PRAGMA journal_mode = WAL;  -- 启用WAL模式提升并发
  PRAGMA cache_size = -2000;  -- 设置缓存页数（负值表示KB）
  PRAGMA synchronous = NORMAL;  -- 平衡安全性与性能

• 分析查询计划：

  EXPLAIN QUERY PLAN SELECT * FROM users WHERE age = 25;

六、常见问题与解决方案

1. 数据库锁定：

   • 原因：多线程/进程同时写入。
   • 解决：启用WAL模式或合理设计事务边界。

2. 外键约束失效：

   • 默认关闭外键，需显式启用：

     PRAGMA foreign_keys = ON;

3. 数据类型混淆：

   • SQLite采用动态类型系统，列可存储任意类型数据，但建议严格约束。

七、总结与资源推荐

SQLite凭借其轻量级、高性能和易用性，成为嵌入式数据库的首选。掌握其核心功能（事务、索引、扩展）和优化技巧（批量操作、PRAGMA配置），可显著提升开发效率。

推荐资源：

• SQLite官方文档
• 《SQLite权威指南》（电子书）
• SQLite Fiddle在线测试平台

通过本文的实践指导，开发者可快速构建稳定、高效的SQLite应用，满足从移动端到服务端的多样化需求。