SQLite学习手册

一、SQLite基础入门

1. SQLite简介与优势

SQLite是一个轻量级的嵌入式关系型数据库，其核心特点包括零配置、无服务器、单文件存储和跨平台支持。与MySQL、PostgreSQL等传统数据库不同，SQLite将整个数据库存储在一个文件中，无需独立进程运行，特别适合移动应用、嵌入式设备或小型项目。其优势体现在：

• 极简部署：无需安装服务，直接通过API调用。
• 高性能：对简单查询的响应速度接近内存操作。
• ACID兼容：支持事务的原子性、一致性、隔离性和持久性。
• 零成本：开源免费，无商业授权限制。

2. 安装与环境配置

• 桌面端：下载预编译的二进制文件（如Windows的sqlite-tools-win32-*.zip），解压后包含sqlite3.exe命令行工具。
• 移动端：Android通过SQLiteDatabase类内置支持；iOS通过Core Data或直接调用SQLite C API。
• 编程语言集成：
  • Python：使用sqlite3模块（标准库）。

    import sqlite3
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    cursor.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)')

  • Java：通过JDBC驱动或Android的SQLiteOpenHelper。
  • C/C++：直接链接SQLite头文件和库文件。

二、核心操作详解

1. 数据库与表管理

• 创建数据库：连接时自动生成文件（如sqlite3 mydb.db）。
• 表设计原则：
  • 数据类型：SQLite采用动态类型系统，但建议明确指定（如INTEGER、TEXT、REAL、BLOB）。
  • 主键与索引：优先使用INTEGER PRIMARY KEY AUTOINCREMENT简化ID管理。

    CREATE TABLE products (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      price REAL CHECK(price > 0),
      stock INTEGER DEFAULT 0
    );

2. CRUD操作实战

• 插入数据：

  INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
  -- 批量插入
  INSERT INTO users (name, email) 
  SELECT 'Bob', 'bob@example.com' UNION ALL 
  SELECT 'Charlie', 'charlie@example.com';

• 查询优化：
  • 使用WHERE子句过滤数据，避免全表扫描。
  • 联合查询与排序：

    SELECT u.name, o.order_date 
    FROM users u 
    JOIN orders o ON u.id = o.user_id 
    WHERE o.total > 100 
    ORDER BY o.order_date DESC;

• 更新与删除：

  UPDATE products SET stock = stock - 1 WHERE id = 101;
  DELETE FROM users WHERE last_login < date('now', '-1 year');

3. 事务与并发控制

• 事务示例：

  try:
      conn.execute('BEGIN TRANSACTION')
      conn.execute('UPDATE accounts SET balance = balance - 100 WHERE id = 1')
      conn.execute('UPDATE accounts SET balance = balance + 100 WHERE id = 2')
      conn.execute('COMMIT')
  except:
      conn.execute('ROLLBACK')

• 锁机制：SQLite默认使用读者-写者锁，允许多个读操作但写操作独占。高并发场景需考虑WAL模式（Write-Ahead Logging）。

三、高级功能与优化

1. 索引与查询性能

• 索引创建：

  CREATE INDEX idx_user_email ON users(email);
  -- 复合索引
  CREATE INDEX idx_order_user_date ON orders(user_id, order_date);

• 性能分析：使用EXPLAIN QUERY PLAN查看执行计划：

  EXPLAIN QUERY PLAN SELECT * FROM users WHERE name LIKE 'A%';

2. 扩展功能

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

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

• JSON支持（SQLite 3.38+）：

  SELECT json_extract('{"name": "Alice"}', '$.name');

3. 备份与恢复

• 导出SQL脚本：

  sqlite3 mydb.db .dump > backup.sql

• 二进制备份：直接复制.db文件（需确保无写操作）。

四、常见问题与解决方案

1. 性能瓶颈

• 问题：复杂查询慢。
• 解决：
  • 添加适当索引。
  • 使用PRAGMA optimize定期整理数据库。
  • 对大数据表分表或分区。

2. 并发冲突

• 问题：多进程/线程同时写入。
• 解决：
  • 启用WAL模式：PRAGMA journal_mode=WAL。
  • 实现应用层锁机制。

3. 跨平台兼容性

• 问题：Windows/Linux/macOS文件路径差异。
• 解决：使用相对路径或统一配置文件管理路径。

五、最佳实践建议

1. 设计阶段：
   • 规范化表结构（至少达到3NF）。
   • 预估数据量，提前规划分表策略。
2. 开发阶段：
   • 使用参数化查询防止SQL注入：

     cursor.execute('SELECT * FROM users WHERE id = ?', (user_id,))

   • 定期执行VACUUM命令回收碎片空间。
3. 维护阶段：
   • 监控数据库文件大小，避免无限增长。
   • 版本控制SQL迁移脚本（如Flyway或Liquibase）。

六、总结与资源推荐

SQLite凭借其轻量级和高效性，已成为从移动应用到IoT设备的首选数据库。掌握其核心操作与优化技巧后，可进一步探索：

• 官方文档：SQLite官网
• 开源工具：DB Browser for SQLite（可视化工具）、SQLiteStudio。
• 进阶学习：研究SQLite源码（C语言实现），理解B-tree存储机制。

通过系统学习与实践，开发者能够充分发挥SQLite的潜力，构建稳定、高效的数据驱动应用。