SQLite学习手册

摘要

本文为SQLite初学者及进阶开发者提供系统性学习指南，涵盖SQLite的核心特性、安装配置、SQL语法精要、性能优化技巧及实际项目中的应用场景。通过理论解析与代码示例结合，帮助读者快速掌握SQLite开发技能，提升数据库应用能力。

一、SQLite基础入门

1.1 SQLite概述

SQLite是一款轻量级嵌入式数据库引擎，具有零配置、无服务器、单文件存储等特性。与MySQL、PostgreSQL等传统数据库相比，SQLite无需独立进程运行，所有数据存储在单个磁盘文件中，适合移动端、桌面应用及小型Web项目。其优势包括：

• 跨平台兼容性：支持Windows、Linux、macOS及移动端（iOS/Android）
• 极低资源占用：内存消耗通常小于500KB
• ACID事务支持：确保数据操作的原子性、一致性、隔离性与持久性
• 零管理成本：无需安装、配置或维护数据库服务器

典型应用场景：浏览器缓存、移动应用本地存储、物联网设备数据记录、小型网站内容管理。

1.2 安装与配置

1.2.1 命令行工具安装

• Linux/macOS：通过包管理器安装（如sudo apt install sqlite3）
• Windows：下载预编译二进制文件（SQLite官网下载）
• 移动端集成：Android通过Gradle依赖androidx.sqlite2.3.1，iOS通过CocoaPods集成FMDB或直接使用系统API

1.2.2 图形化工具推荐

• DB Browser for SQLite：开源可视化工具，支持表结构编辑、SQL查询执行及数据导出
• SQLiteStudio：跨平台GUI工具，提供代码补全与数据可视化功能
• VS Code插件：安装SQLite扩展实现内嵌数据库操作

1.3 创建与管理数据库

-- 创建或打开数据库（文件不存在时自动创建）
sqlite3 mydb.db
-- 查看数据库信息
.databases
.schema
-- 退出命令行
.exit

二、SQL语法核心精要

2.1 数据类型与表设计

SQLite采用动态类型系统，列可存储任意类型数据，但建议遵循类型亲和性：

• NUMERIC：整数、浮点数、布尔值
• TEXT：字符串（UTF-8/UTF-16）
• BLOB：二进制数据
• INTEGER：带符号整数
• REAL：浮点数

表创建示例：

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT CHECK(email LIKE '%@%.%'),
    age INTEGER DEFAULT 18,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2.2 核心CRUD操作

2.2.1 插入数据

-- 单条插入
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
-- 批量插入
INSERT INTO users (username, email) 
VALUES ('bob', 'bob@example.com'), ('charlie', 'charlie@example.com');

2.2.2 查询优化

-- 基础查询
SELECT * FROM users WHERE age > 20 ORDER BY created_at DESC LIMIT 10;
-- 索引加速
CREATE INDEX idx_users_email ON users(email);
-- 复合查询
SELECT u.username, o.order_date 
FROM users u JOIN orders o ON u.id = o.user_id 
WHERE o.total > 100;

2.2.3 事务处理

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 或 ROLLBACK 回滚

三、进阶技巧与性能优化

3.1 索引策略

• 单列索引：加速等值查询（如WHERE email = 'x'）
• 复合索引：优化多条件查询（如CREATE INDEX idx_name_age ON users(name, age)）
• 部分索引：仅对满足条件的行创建索引

  CREATE INDEX idx_active_users ON users(email) WHERE is_active = 1;

3.2 查询优化实践

• 避免SELECT *：仅查询所需列
• 使用EXISTS替代IN：处理大数据集时效率更高
• 分页优化：结合OFFSET与LIMIT，或使用键集分页

  -- 键集分页示例
  SELECT * FROM users WHERE id > last_seen_id ORDER BY id LIMIT 10;

3.3 WAL模式与并发

启用WAL（Write-Ahead Logging）模式提升并发性能：

PRAGMA journal_mode=WAL;

• 优势：读写操作可并行执行
• 限制：单个连接最多同时存在1个写事务

四、实战案例解析

4.1 移动应用数据持久化

场景：Android应用存储用户偏好设置

// Kotlin示例（使用Room库）
@Database(entities = [User::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
}
@Dao
interface UserDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertUser(user: User)
    @Query("SELECT * FROM users WHERE id = :userId")
    suspend fun getUser(userId: Long): User?
}

4.2 物联网设备数据采集

场景：Raspberry Pi存储传感器数据

# Python示例
import sqlite3
import time
conn = sqlite3.connect('sensor_data.db')
c = conn.cursor()
c.execute('''CREATE TABLE IF NOT EXISTS readings
             (timestamp DATETIME, temp REAL, humidity REAL)''')
while True:
    temp = read_temperature()  # 假设的传感器读取函数
    humidity = read_humidity()
    c.execute("INSERT INTO readings VALUES (datetime('now'), ?, ?)", 
              (temp, humidity))
    conn.commit()
    time.sleep(60)

4.3 全文检索实现

场景：构建本地文档搜索引擎

-- 启用FTS扩展
CREATE VIRTUAL TABLE documents_fts USING fts5(title, content);
-- 插入文档
INSERT INTO documents_fts(title, content) VALUES ('SQLite指南', 'SQLite是轻量级数据库...');
-- 全文搜索
SELECT * FROM documents_fts WHERE documents_fts MATCH '数据库 轻量级';

五、常见问题解决方案

5.1 数据库锁定问题

现象：database is locked错误
解决方案：

1. 确保所有事务正确提交或回滚
2. 缩短事务执行时间
3. 在多线程环境中使用连接池

5.2 性能瓶颈分析

工具：

• EXPLAIN QUERY PLAN分析查询执行计划

  EXPLAIN QUERY PLAN SELECT * FROM users WHERE age > 30;

• 使用.timer on统计查询耗时

5.3 数据备份与恢复

# 导出数据库
sqlite3 mydb.db .dump > backup.sql
# 恢复数据库
sqlite3 newdb.db < backup.sql

六、学习资源推荐

1. 官方文档：SQLite Documentation
2. 进阶书籍：
   • 《The Definitive Guide to SQLite》
   • 《SQLite High Performance》
3. 开源项目：
   • SQLite源码解析
   • FMDB（iOS）

通过系统学习与实践，开发者可充分利用SQLite的轻量级特性，在资源受限环境中构建高效可靠的数据存储方案。建议从简单CRUD操作入手，逐步掌握索引优化、事务处理等高级技巧，最终实现复杂业务场景的数据库设计。