1. SQLite表数据管理基础

SQLite作为一款轻量级的关系型数据库，在移动端和小型应用中广受欢迎。相比其他数据库系统，SQLite的数据管理有着自己独特的特点和实现方式。我们先从最基础的增删改查操作开始讲起。

1.1 插入数据技巧

在SQLite中插入数据看似简单，但有几个关键细节需要注意：

-- 基本插入语法 INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2); -- 当主键是INTEGER PRIMARY KEY时的特殊处理 INSERT INTO users (id, name) VALUES (NULL, '张三'); -- id会自动生成

这里有个重要特性：当主键定义为INTEGER PRIMARY KEY时，插入NULL值会让SQLite自动生成一个唯一的整数值。这个值通常是当前表中最大ID值+1。如果表为空，则从1开始。

注意：这种自动递增行为只适用于INTEGER PRIMARY KEY，其他类型的主键不会自动递增。如果定义为PRIMARY KEY但没有指定INTEGER，也不会自动递增。

1.2 删除数据操作

SQLite的删除操作与其他数据库有些差异：

-- 基本删除语法 DELETE FROM 表名 WHERE 条件; -- 删除所有数据（相当于TRUNCATE） DELETE FROM 表名;

SQLite不支持标准的TRUNCATE TABLE语法，但DELETE FROM 表名不加WHERE条件可以达到同样效果。两者的区别在于：

• TRUNCATE在其他数据库中通常不记录日志，速度更快
• DELETE会记录日志，可以回滚
• 在SQLite中，DELETE FROM 表名后，自增计数器不会重置。如果需要重置，需要额外执行：

DELETE FROM sqlite_sequence WHERE name='表名';

1.3 更新数据要点

更新操作需要注意原子性和条件准确性：

UPDATE 表名 SET 列1=值1, 列2=值2 WHERE 条件;

实际开发中常见问题：

1. WHERE条件缺失导致全表更新
2. 更新大量数据时未使用事务，导致性能问题
3. 并发更新时的锁竞争问题

2. SQLite查询进阶

2.1 连接查询详解

SQLite支持多种连接方式，但与其他数据库有些差异：

模拟右外连接的技巧：

-- 用LEFT JOIN模拟RIGHT JOIN SELECT * FROM 表2 LEFT JOIN 表1 ON 表1.id=表2.id;

2.2 系统表的使用

SQLite有几个重要的系统表，掌握它们对数据库管理很有帮助。

2.2.1 sqlite_master表

这是SQLite中最重要的系统表，存储了所有数据库对象的定义信息：

-- 查询所有用户表 SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'; -- 查询特定表的创建语句 SELECT sql FROM sqlite_master WHERE type='table' AND name='users'; -- 查询所有索引 SELECT name, tbl_name FROM sqlite_master WHERE type='index';

2.2.2 sqlite_sequence表

这个表记录了自增主键的当前值：

-- 查看所有表的自增序列值 SELECT * FROM sqlite_sequence; -- 重置某个表的自增计数器 UPDATE sqlite_sequence SET seq=0 WHERE name='表名';

注意：直接修改sqlite_sequence表可能会破坏数据完整性，应谨慎操作。

3. 高级查询技术

3.1 窗口函数应用

SQLite从3.25.0版本开始支持窗口函数，这是一个强大的分析工具：

-- 基本窗口函数语法 SELECT 列名, window_function() OVER ( PARTITION BY 分组列 ORDER BY 排序列 ROWS/RANGE BETWEEN 边界规则 ) FROM 表名; -- 实际示例：计算每个部门的薪资排名 SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank FROM employees;

窗口函数使用时需要注意：

1. 只能在SELECT列表和ORDER BY子句中使用
2. 不能与DISTINCT同时使用
3. 帧类型(ROWS/RANGE)的选择会影响计算结果

3.2 公共表表达式(CTE)

CTE可以大大简化复杂查询：

-- 基本CTE语法 WITH 临时表名 AS ( SELECT 查询 ) SELECT * FROM 临时表名; -- 递归CTE示例：生成1-10的数字序列 WITH RECURSIVE numbers(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM numbers WHERE n<10 ) SELECT * FROM numbers;

递归CTE的深度默认有限制(通常1000)，可以通过编译时选项调整。

4. 事务与锁机制

4.1 事务控制

SQLite的事务控制命令简单但功能强大：

BEGIN; -- 开始事务 -- 执行多个操作 COMMIT; -- 提交事务 -- 或者 BEGIN; -- 执行操作 ROLLBACK; -- 回滚事务

SQLite支持三种事务类型：

1. DEFERRED(默认)：第一次访问数据库时才获取锁
2. IMMEDIATE：立即获取保留锁，防止其他写操作
3. EXCLUSIVE：获取排他锁，阻止所有其他访问

4.2 锁机制详解

SQLite的锁状态转换流程如下：

UNLOCKED → SHARED → RESERVED → PENDING → EXCLUSIVE → UNLOCKED

实际开发中的锁优化建议：

1. 读多写少场景使用DEFERRED事务
2. 写操作使用IMMEDIATE或EXCLUSIVE事务
3. 避免长时间持有事务
4. 合理设置事务超时时间

4.3 常见锁问题解决

问题1：database is locked

解决方案：

• 检查是否有未提交的事务
• 减少事务持有时间
• 使用适当的重试机制

问题2：死锁

预防措施：

• 按照固定顺序访问表和行
• 使用合适的事务隔离级别
• 设置合理的锁超时时间

5. 性能优化实践

5.1 索引优化

-- 创建索引 CREATE INDEX idx_name ON users(name); -- 查看查询计划 EXPLAIN QUERY PLAN SELECT * FROM users WHERE name='张三';

索引使用注意事项：

1. 不要过度索引，索引会降低写入性能
2. 复合索引要注意列顺序
3. 索引不适合小表或频繁更新的列

5.2 批量操作优化

-- 使用事务批量插入 BEGIN; INSERT INTO users(name) VALUES ('张三'); INSERT INTO users(name) VALUES ('李四'); -- ...更多插入 COMMIT;

批量操作可以显著提高性能，因为：

1. 减少了事务开销
2. 减少了磁盘I/O次数
3. 减少了锁竞争

5.3 内存数据库使用

SQLite支持纯内存数据库：

-- 创建内存数据库 Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:"); -- 从内存数据库备份到文件 .backup main file.db

内存数据库适合：

1. 临时数据处理
2. 单元测试
3. 高性能计算场景

6. 实际应用经验分享

在我多年的SQLite使用经验中，总结出以下几点心得：

1. 连接池管理：SQLite对并发写入支持有限，应用层需要做好连接管理，避免同时多个写入操作。

2. 迁移策略：当数据量增长到百万级别时，考虑迁移到MySQL等更强大的数据库系统。可以使用.dump命令导出数据。

3. 备份方案：SQLite数据库就是一个文件，可以直接复制备份。但要注意：

   • 备份时确保没有写入操作
   • 或者使用.backup命令进行在线备份

4. 版本兼容性：不同版本的SQLite功能差异较大，特别是窗口函数等高级特性。���署时要确保运行环境版本符合要求。

5. 调试技巧：遇到问题时可以：

   • 使用PRAGMA命令查看各种设置
   • 开启SQLite的日志功能
   • 使用EXPLAIN分析查询计划

最后，SQLite虽然轻量，但功能强大。合理使用可以满足大多数中小型应用的数据存储需求，关键是理解它的特性和限制，做出适合的设计决策。