SQLite学习手册

一、SQLite概述：轻量级数据库的典范

SQLite是一款开源的、零配置的、事务型的SQL数据库引擎，以其轻量级、嵌入式和跨平台特性闻名。与MySQL、PostgreSQL等传统数据库不同，SQLite将整个数据库存储在单个磁盘文件中，无需单独的服务器进程或系统配置，特别适合移动应用、嵌入式设备及小型Web项目。

核心优势

1. 零配置：无需安装或管理，解压即用。
2. 跨平台：支持Windows、Linux、macOS及移动端（Android/iOS）。
3. ACID兼容：保证事务的原子性、一致性、隔离性和持久性。
4. 单文件存储：数据库即文件，便于备份和迁移。

适用场景

• 移动应用（如Android/iOS本地存储）
• 嵌入式系统（如IoT设备）
• 桌面软件（如Firefox浏览器书签存储）
• 原型开发和小型项目

二、SQLite基础操作：从安装到CRUD

1. 安装与配置

SQLite无需安装，下载预编译的二进制文件（如sqlite3.exe或sqlite3命令行工具）即可使用。在Linux/macOS上可通过包管理器安装：

# Ubuntu/Debian
sudo apt-get install sqlite3
# macOS (Homebrew)
brew install sqlite

2. 命令行工具使用

启动SQLite命令行界面：

sqlite3 mydatabase.db

常用命令：

• .tables：列出所有表
• .schema 表名：查看表结构
• .exit：退出

3. 创建表与数据操作

示例：创建用户表并插入数据

-- 创建表
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER
);
-- 插入数据
INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 25);
INSERT INTO users (name, email, age) VALUES ('Bob', 'bob@example.com', 30);
-- 查询数据
SELECT * FROM users;

4. 条件查询与排序

-- 查询年龄大于25的用户
SELECT * FROM users WHERE age > 25;
-- 按年龄降序排列
SELECT * FROM users ORDER BY age DESC;

三、进阶功能：索引、事务与视图

1. 索引优化

索引可加速查询，但会降低写入性能。示例：为email字段创建索引

CREATE INDEX idx_email ON users (email);

适用场景：频繁查询但更新少的字段。

2. 事务处理

事务保证一组操作的原子性。示例：银行转账

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;
-- 若出错可执行 ROLLBACK;

3. 视图（View）

视图是虚拟表，简化复杂查询。示例：创建活跃用户视图

CREATE VIEW active_users AS
SELECT * FROM users WHERE age > 20;
-- 查询视图
SELECT * FROM active_users;

四、高级特性：触发器、全文搜索与扩展

1. 触发器（Trigger）

触发器在特定事件（如INSERT/UPDATE/DELETE）时自动执行。示例：日志记录

CREATE TRIGGER log_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, old_name, new_name)
    VALUES (OLD.id, OLD.name, NEW.name);
END;

2. 全文搜索（FTS）

SQLite支持全文搜索，适合文本内容检索。步骤：

1. 创建FTS虚拟表：

   CREATE VIRTUAL TABLE documents USING fts5(title, content);

2. 插入数据并查询：

   INSERT INTO documents (title, content) VALUES ('SQLite Guide', 'Learn SQLite database');
   SELECT * FROM documents WHERE documents MATCH 'SQLite';

3. 扩展功能

• JSON1扩展：支持JSON操作（如json_extract）。
• 空间数据：通过R*Tree模块存储地理数据。

五、实际应用：移动端与嵌入式开发

1. Android中的SQLite

Android通过SQLiteDatabase类封装操作。示例：插入数据

SQLiteDatabase db = openOrCreateDatabase("mydb.db", MODE_PRIVATE, null);
db.execSQL("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, content TEXT)");
db.execSQL("INSERT INTO notes (content) VALUES ('Hello SQLite')");

2. 嵌入式设备集成

在树莓派等设备上，SQLite可通过C API直接调用：

#include <sqlite3.h>
int main() {
    sqlite3 *db;
    sqlite3_open("test.db", &db);
    sqlite3_exec(db, "CREATE TABLE test (id INT)", NULL, NULL, NULL);
    sqlite3_close(db);
    return 0;
}

六、性能优化与最佳实践

1. 批量操作：使用事务批量插入数据，速度提升10倍以上。
2. 避免频繁查询：减少SELECT *，仅查询必要字段。
3. 定期维护：执行VACUUM命令回收碎片空间。
4. 连接池：在多线程环境中复用数据库连接。

七、学习资源推荐

1. 官方文档：SQLite官网
2. 书籍：《The Definitive Guide to SQLite》
3. 在线课程：Udemy《SQLite for Beginners》
4. 实践工具：DB Browser for SQLite（图形化工具）

结语

SQLite以其简单性和强大功能成为开发者必备工具。从基础CRUD到高级特性，掌握SQLite能显著提升数据存储效率。建议通过实际项目（如个人笔记应用）巩固知识，逐步探索其隐藏的潜力。