一、SQLite 基础概念

1. 什么是 SQLite

SQLite 是一个开源的、零配置的、自包含的、基于文件的轻量级关系型数据库管理系统。

核心特点：

• 嵌入式数据库：直接集成到应用程序中，无需单独的数据库服务器
• 单文件数据库：整个数据库存储在一个普通文件中
• 零配置：无需安装、配置或管理
• 跨平台：支持 Windows、Linux、macOS、iOS、Android 等
• 事务性：支持 ACID 事务（原子性、一致性、隔离性、持久性）
• 小型化：核心库仅约 600KB

SQLite 与其他数据库对比：

特性	SQLite	MySQL	PostgreSQL
架构	嵌入式	客户端-服务器	客户端-服务器
配置	零配置	需要配置	需要配置
并发	低（单写多读）	高	高
数据库大小	适合小到中型	适合中到大型	适合大型
内存使用	低	中	高
适用场景	移动应用、小型应用	Web 应用、中型系统	企业级应用

2. 适用场景

适合使用 SQLite 的场景：

• 移动应用（Android、iOS）的本地数据存储
• 桌面应用程序的配置和数据存储
• 小型网站或 Web 应用的后端
• 原型开发和测试
• 嵌入式系统和 IoT 设备
• 数据分析和临时数据处理

不适合使用 SQLite 的场景：

• 高并发写入场景（>100 写操作/秒）
• 需要复杂用户权限管理的系统
• 需要存储 TB 级数据的场景
• 需要复杂存储过程和触发器的系统

提示：SQLite 是世界上使用最广泛的数据库引擎，嵌入在几乎所有智能手机、电脑和浏览器中。它不是"简化版"数据库，而是为特定场景优化的完整 SQL 数据库。

二、安装与基本使用

1. 安装 SQLite

大多数系统已预装：

• macOS 和 Linux：通常已预装，可通过终端输入 sqlite3 验证
• Windows：需要单独安装

Windows 安装步骤：

1. 从 SQLite 官网 下载预编译二进制文件
2. 解压 sqlite-tools-win32-x86-*.zip
3. 将 sqlite3.exe 添加到系统 PATH

验证安装：

sqlite3 --version
# 应该输出类似：3.39.4 2022-09-29 11:34:22 1a0d0802c6c0ea5d585f6c624e6d3a9e6b01c1a140e5c7d7b3e7d8c3e5c7

2. 创建和连接数据库

创建新数据库：

sqlite3 mydatabase.db

• 如果文件不存在，会自动创建
• 如果文件已存在，会打开现有数据库

基本命令：

-- 显示帮助
.help

-- 列出所有表
.tables

-- 查看表结构
.schema table_name

-- 退出
.exit

常用元命令：

命令	说明
.tables	列出所有表
.schema [table]	显示表结构
.databases	显示数据库文件
.dump [table]	导出 SQL 语句
.read file.sql	执行 SQL 文件
.output file.txt	重定向输出到文件
.mode column	设置列模式显示
.headers on	显示列标题

提示：在 SQLite 命令行中，以点（.）开头的是 SQLite 特有的命令，而非 SQL 语句。

三、常用 SQL 语句

1. 数据定义语言 (DDL)

创建表：

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

修改表结构：

-- 添加列
ALTER TABLE users ADD COLUMN phone TEXT;

-- 重命名表
ALTER TABLE users RENAME TO customers;

删除表：

DROP TABLE IF EXISTS users;

2. 数据操作语言 (DML)

插入数据：

-- 单行插入
INSERT INTO users (name, email, age) 
VALUES ('John Doe', 'john@example.com', 30);

-- 多行插入
INSERT INTO users (name, email, age) VALUES
('Jane Smith', 'jane@example.com', 25),
('Mike Johnson', 'mike@example.com', 40);

查询数据：

-- 基本查询
SELECT * FROM users;

-- 指定列查询
SELECT id, name, email FROM users;

-- 带条件查询
SELECT * FROM users WHERE age > 25;

-- 排序
SELECT * FROM users ORDER BY created_at DESC;

-- 限制结果
SELECT * FROM users LIMIT 10;

更新数据：

UPDATE users 
SET email = 'john_new@example.com', age = 31 
WHERE id = 1;

删除数据：

DELETE FROM users WHERE id = 1;

3. 条件查询和高级操作

WHERE 子句：

-- 比较操作
SELECT * FROM users WHERE age > 25 AND age < 40;

-- IN 操作符
SELECT * FROM users WHERE id IN (1, 3, 5);

-- LIKE 操作符
SELECT * FROM users WHERE name LIKE 'J%';

-- NULL 处理
SELECT * FROM users WHERE phone IS NULL;

聚合函数：

-- 计数
SELECT COUNT(*) FROM users;

-- 平均值
SELECT AVG(age) FROM users;

-- 分组统计
SELECT age, COUNT(*) FROM users GROUP BY age;

-- 分组过滤
SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;

连接查询：

-- 内连接
SELECT users.name, orders.amount 
FROM users 
INNER JOIN orders ON users.id = orders.user_id;

-- 左连接
SELECT users.name, orders.amount 
FROM users 
LEFT JOIN orders ON users.id = orders.user_id;

4. 常用函数

日期时间函数：

-- 当前时间
SELECT datetime('now');

-- 日期加减
SELECT datetime('now', '+1 day');
SELECT datetime('now', '-1 hour');

-- 格式化日期
SELECT strftime('%Y-%m-%d', 'now');

字符串函数：

-- 字符串连接
SELECT name || ' (' || email || ')' FROM users;

-- 子字符串
SELECT substr(name, 1, 5) FROM users;

-- 大小写转换
SELECT upper(name), lower(email) FROM users;

数值函数：

-- 四舍五入
SELECT round(3.14159, 2);

-- 最大值/最小值
SELECT max(age), min(age) FROM users;

提示：SQLite 的 SQL 语法与标准 SQL 非常接近，但有少量差异。掌握这些基本语句足以应对 90% 的日常查询需求。

四、数据类型和约束

1. SQLite 的数据类型

动态类型系统：SQLite 使用"类型亲和性"而非严格的类型系统。

主要数据类型：

类型	描述	常用别名
INTEGER	有符号整数	INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8
REAL	浮点数	FLOAT, DOUBLE, DOUBLE PRECISION
TEXT	文本字符串	CHAR, VARCHAR, TEXT, CLOB
BLOB	二进制数据	BLOB
NULL	空值	NULL

类型亲和性规则：

• 列声明类型包含 "INT" → INTEGER 亲和性
• 列声明类型包含 "CHAR"、"CLOB" 或 "TEXT" → TEXT 亲和性
• 列声明类型包含 "BLOB" → 无亲和性
• 列声明类型包含 "REAL"、"FLOA" 或 "DOUB" → REAL 亲和性
• 其他情况 → NUMERIC 亲和性

示例：

-- 这些声明都创建具有 INTEGER 亲和性的列
CREATE TABLE example (
    id1 INTEGER,
    id2 INT,
    id3 TINYINT,
    id4 SMALLINT
);

2. 约束

主键约束：

-- 单列主键
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

-- 复合主键
CREATE TABLE orders (
    user_id INTEGER,
    product_id INTEGER,
    PRIMARY KEY (user_id, product_id)
);

唯一约束：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);

非空约束：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

外键约束：

-- 需要先启用外键支持
PRAGMA foreign_keys = ON;

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY(user_id) REFERENCES users(id)
);

检查约束：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    age INTEGER CHECK (age > 0 AND age < 120)
);

提示：SQLite 的外键约束默认是禁用的，需要在连接数据库后执行 PRAGMA foreign_keys = ON; 才能启用。

五、索引和性能优化

1. 创建和使用索引

创建索引：

-- 普通索引
CREATE INDEX idx_users_email ON users(email);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 复合索引
CREATE INDEX idx_users_name_age ON users(name, age);

何时使用索引：

• 经常用于 WHERE 条件的列
• 经常用于 JOIN 条件的列
• 经常用于 ORDER BY 和 GROUP BY 的列
• 高选择性的列（唯一值多的列）

索引注意事项：

• 索引会增加写操作的开销
• 不要过度索引（通常每个表 2-5 个索引足够）
• 复合索引的顺序很重要（最常过滤的列放在前面）

2. 性能优化技巧

使用 EXPLAIN QUERY PLAN：

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'john@example.com';

• 查看查询执行计划
• 确认是否使用了正确的索引

批量操作优化：

-- 不推荐：多次单独插入
INSERT INTO users VALUES ('Alice');
INSERT INTO users VALUES ('Bob');

-- 推荐：批量插入
BEGIN TRANSACTION;
INSERT INTO users VALUES ('Alice');
INSERT INTO users VALUES ('Bob');
COMMIT;

WAL 模式：

-- 启用 WAL 模式（提高并发性能）
PRAGMA journal_mode = WAL;

-- 设置检查点
PRAGMA wal_checkpoint;

• 提高读写并发性能
• 减少写操作的锁争用

其他优化技巧：

-- 禁用同步（提高写入速度，但降低可靠性）
PRAGMA synchronous = OFF;

-- 增加缓存大小
PRAGMA cache_size = 10000;

-- 临时禁用索引更新
PRAGMA temp_store = MEMORY;
PRAGMA journal_mode = MEMORY;

提示：大多数性能问题源于缺少适当的索引或不当的查询设计。使用 EXPLAIN QUERY PLAN 是诊断查询性能问题的首要步骤。

六、事务和锁机制

1. 事务管理

基本事务：

BEGIN TRANSACTION;
INSERT INTO users (name) VALUES ('John');
INSERT INTO users (name) VALUES ('Jane');
COMMIT;

回滚事务：

BEGIN TRANSACTION;
INSERT INTO users (name) VALUES ('Mike');
-- 出现错误
ROLLBACK;

自动提交模式：

• 默认情况下，SQLite 处于自动提交模式
• 每个单独的 SQL 语句都是一个事务
• 显式使用 BEGIN 后进入手动事务模式

2. 锁机制

锁的生命周期：

1. UNLOCKED：无锁状态
2. SHARED：读操作获取
3. RESERVED：写操作准备
4. PENDING：准备获取 EXCLUSIVE 锁
5. EXCLUSIVE：写操作完成

锁冲突处理：

• 读操作不会阻塞其他读操作
• 写操作会阻塞所有其他操作
• 默认超时：5 秒（可通过 PRAGMA busy_timeout 调整）

WAL 模式的优势：

• 读操作不会阻塞写操作
• 写操作不会阻塞读操作
• 支持更高的并发

设置超时：

-- 设置忙等待超时（毫秒）
PRAGMA busy_timeout = 10000;  -- 10秒

提示：在应用程序中，应始终处理可能的 SQLITE_BUSY 错误，并实现适当的重试逻辑。

七、实用技巧与常见问题

1. 常用场景解决方案

移动应用本地存储：

• 每个应用有自己的数据库文件
• 使用 ORM 库（如 Room for Android，Core Data for iOS）
• 定期备份重要数据

数据迁移策略：

-- 创建新表
CREATE TABLE users_new (...);

-- 复制数据
INSERT INTO users_new SELECT ... FROM users;

-- 重命名表
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

大数据量处理：

-- 分批处理
BEGIN;
-- 处理1000条记录
DELETE FROM logs WHERE id < 1000;
COMMIT;

-- 重复直到完成

2. 常见问题排查

数据库锁定问题：

• 现象：database is locked 错误
• 解决方法：
  • 增加 busy_timeout：PRAGMA busy_timeout = 5000;
  • 检查未完成的事务
  • 考虑使用 WAL 模式
  • 确保正确关闭数据库连接

性能问题：

• 现象：查询变慢
• 诊断步骤：
  1. 使用 EXPLAIN QUERY PLAN
  2. 检查是否缺少索引
  3. 分析查询语句是否合理
  4. 考虑增加缓存大小

数据丢失问题：

• 现象：重启后数据消失
• 原因：可能使用了内存数据库或临时表
• 解决：确保使用的是文件数据库

3. 最佳实践

数据库设计：

• 规范化数据结构，但避免过度规范化
• 为经常查询的列创建索引
• 使用 INTEGER PRIMARY KEY AUTOINCREMENT 作为主键
• 为时间戳添加 DEFAULT CURRENT_TIMESTAMP

备份策略：

# 简单备份（需确保无写操作）
cp mydatabase.db mydatabase_backup.db

# 使用命令行备份
sqlite3 mydatabase.db ".backup mydatabase_backup.db"

# 在应用中备份
PRAGMA wal_checkpoint;  -- 确保WAL内容合并
-- 然后复制文件

版本迁移：

• 使用版本表跟踪数据库模式
• 按需执行迁移脚本
• 测试迁移过程

-- 创建版本表
CREATE TABLE db_version (version INTEGER);

-- 插入初始版本
INSERT INTO db_version VALUES (1);

-- 应用启动时检查并迁移
BEGIN;
SELECT version FROM db_version;
-- 如果版本低，执行迁移
UPDATE db_version SET version = 2;
COMMIT;

八、常用工具

1. 命令行工具

sqlite3 命令：

# 执行单条查询
sqlite3 mydb.db "SELECT * FROM users;"

# 执行SQL文件
sqlite3 mydb.db < script.sql

# 导出为SQL
sqlite3 mydb.db ".dump" > backup.sql

# 从SQL恢复
sqlite3 mydb.db < backup.sql

2. GUI 工具

DB Browser for SQLite：

• 免费开源的跨平台工具
• 功能：创建/浏览表、执行查询、导入/导出数据
• 官网：https://sqlitebrowser.org/

其他常用工具：

• SQLite Studio：功能更丰富的开源工具
• DBeaver：支持多种数据库的通用工具
• TablePlus：商业工具，界面美观

3. 编程语言集成

Python：

import sqlite3

# 连接数据库
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

# 执行查询
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

# 参数化查询（防止SQL注入）
cursor.execute("SELECT * FROM users WHERE name = ?", ("John",))

# 提交更改
conn.commit()
conn.close()

JavaScript (Node.js)：

const sqlite3 = require('sqlite3').verbose();

// 打开数据库
let db = new sqlite3.Database('mydatabase.db');

// 执行查询
db.all("SELECT * FROM users", [], (err, rows) => {
  if (err) {
    throw err;
  }
  console.log(rows);
});

// 参数化查询
db.run(`INSERT INTO users(name, email) VALUES(?, ?)`, 
  ['John', 'john@example.com'],
  function(err) {
    if (err) {
      return console.log(err.message);
    }
    console.log(`A row has been inserted with rowid ${this.lastID}`);
  });

// 关闭连接
db.close();

Java：

import java.sql.*;

public class SQLiteJDBC {
  public static void main(String args[]) {
    Connection c = null;
    try {
      Class.forName("org.sqlite.JDBC");
      c = DriverManager.getConnection("jdbc:sqlite:mydatabase.db");
      
      Statement stmt = c.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * FROM users;");
      while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        System.out.println(id + ": " + name);
      }
      rs.close();
      stmt.close();
      c.close();
    } catch (Exception e) {
      System.err.println(e.getClass().getName() + ": " + e.getMessage());
      System.exit(0);
    }
  }
}

九、实用技巧速查表

操作	命令/代码	说明
创建数据库	sqlite3 dbname.db	创建或打开数据库
创建表	CREATE TABLE tbl(...)	创建新表
插入数据	INSERT INTO tbl VALUES(...)	插入新记录
查询数据	SELECT * FROM tbl WHERE...	检索数据
更新数据	UPDATE tbl SET... WHERE...	修改现有记录
删除数据	DELETE FROM tbl WHERE...	删除记录
创建索引	CREATE INDEX idx_name ON tbl(col)	提高查询速度
开始事务	BEGIN TRANSACTION	手动事务开始
提交事务	COMMIT	保存更改
回滚事务	ROLLBACK	撤销更改
查看表结构	.schema tbl	命令行查看表定义
导出数据库	.dump > backup.sql	导出为SQL文件
导入数据库	sqlite3 db < backup.sql	从SQL文件恢复