SQLite简介 #

-- SQLite 的核心特点
-- 1. 无服务器架构
-- 2. 单文件存储
-- 3. 零配置
-- 4. 事务支持

┌─────────────────────────────────────────┐
│           应用程序                       │
├─────────────────────────────────────────┤
│           SQL接口                        │
│   (sqlite3_prepare, sqlite3_exec...)    │
├─────────────────────────────────────────┤
│           SQL编译器                      │
│   (词法分析、语法分析、代码生成)          │
├─────────────────────────────────────────┤
│           虚拟机                         │
│   (VDBE - 虚拟数据库引擎)                │
├─────────────────────────────────────────┤
│           B-Tree存储引擎                 │
├─────────────────────────────────────────┤
│           页面缓存                       │
├─────────────────────────────────────────┤
│           操作系统接口                   │
│   (文件系统、内存管理)                   │
└─────────────────────────────────────────┘

-- 传统数据库架构
┌──────────┐     ┌──────────┐     ┌──────────┐
│  客户端   │────→│  服务器   │────→│  数据库   │
└──────────┘     └──────────┘     └──────────┘

-- SQLite架构
┌──────────┐     ┌──────────┐
│  应用程序 │────→│ 数据库文件│
└──────────┘     └──────────┘

-- SQLite数据库就是一个文件
myapp.db    -- 整个数据库存储在这个文件中

-- 包含内容：
-- 1. 表结构定义
-- 2. 数据记录
-- 3. 索引
-- 4. 触发器
-- 5. 视图

# 无需配置文件
# 无需启动服务
# 无需创建用户

# 直接使用
sqlite3 myapp.db

-- SQLite 支持完整的 ACID 事务
BEGIN TRANSACTION;

INSERT INTO accounts (user_id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

COMMIT;
-- 或 ROLLBACK;

-- SQLite 库大小
-- 完整版：约 500KB - 1MB
-- 最小版：约 250KB

-- 对比其他数据库
-- MySQL：约 200MB+
-- PostgreSQL：约 300MB+

-- SQLite 经过严格测试
-- 100% 分支测试覆盖率
-- 数十亿次测试用例执行

-- 数据库文件格式稳定
-- 向后兼容性保证

-- 对于小型到中型数据集
-- SQLite 通常比客户端/服务器数据库更快

-- 原因：
-- 1. 无网络开销
-- 2. 无进程间通信
-- 3. 无权限检查开销

-- SQLite 是公有领域软件
-- 无版权限制
-- 可用于任何目的
-- 无需支付许可费用
-- 无需开源代码

-- iOS 应用
-- CoreData 底层使用 SQLite
-- 直接使用 SQLite

-- Android 应用
-- 内置 SQLite 支持
-- Room 底层使用 SQLite

-- 示例：本地数据存储
CREATE TABLE user_settings (
    key TEXT PRIMARY KEY,
    value TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 应用程序配置存储
CREATE TABLE app_config (
    id INTEGER PRIMARY KEY,
    section TEXT,
    key TEXT,
    value TEXT,
    UNIQUE(section, key)
);

-- 本地缓存
CREATE TABLE cache (
    key TEXT PRIMARY KEY,
    value BLOB,
    expires_at DATETIME
);

-- IoT 设备数据存储
CREATE TABLE sensor_data (
    id INTEGER PRIMARY KEY,
    sensor_id TEXT,
    value REAL,
    recorded_at DATETIME
);

-- 创建索引优化查询
CREATE INDEX idx_sensor_time ON sensor_data(sensor_id, recorded_at);

-- 小型网站
-- 日流量 < 100,000 的网站

-- 静态网站生成器
-- 内容存储在 SQLite

-- 开发和测试环境
-- 快速原型开发

-- 本地数据分析
-- CSV 导入 SQLite
.mode csv
.import data.csv mytable

-- 使用 SQL 分析数据
SELECT 
    category,
    COUNT(*) as count,
    AVG(value) as avg_value
FROM mytable
GROUP BY category;

-- SQLite 支持多读者，但只支持单写者

-- 读并发：多个进程可同时读取
-- 写并发：同一时间只能有一个写入者

-- 解决方案：
-- 1. 使用 WAL 模式提高并发
PRAGMA journal_mode = WAL;

-- 2. 短事务减少锁持有时间
BEGIN IMMEDIATE;
-- 快速操作
COMMIT;

-- SQLite 不支持直接网络访问
-- 数据库文件必须在本地文件系统

-- 变通方案：
-- 1. 网络文件系统（不推荐）
-- 2. 定期同步
-- 3. 使用 SQLite 作为本地缓存

-- 理论最大：140TB
-- 实际建议：< 1TB

-- 大文件考虑：
-- 1. 备份时间
-- 2. 文件系统限制
-- 3. 查询性能

-- SQLite 没有内置用户管理系统
-- 没有用户名/密码
-- 没有权限控制

-- 安全依赖：
-- 1. 文件系统权限
-- 2. 应用层控制
-- 3. 加密扩展（SQLCipher）

选择 SQLite 的场景：
├── 移动应用
├── 桌面应用
├── 嵌入式设备
├── 小型网站
├── 开发测试
└── 本地缓存

选择 MySQL/PostgreSQL 的场景：
├── 大型Web应用
├── 高并发写入
├── 需要用户权限管理
├── 需要存储过程
└── 分布式系统

-- SQLite 3.0 (2004)
-- UTF-8 和 UTF-16 支持
-- BLOB 支持
-- 新的文件格式

-- SQLite 3.7 (2010)
-- WAL 模式
-- 外键约束

-- SQLite 3.25 (2018)
-- 窗口函数

-- SQLite 3.35 (2021)
-- 内置 JSON 函数
-- RETURNING 子句

-- SQLite 3.39 (2022)
-- RIGHT JOIN 和 FULL JOIN

-- SQLite 3.45 (2024)
-- 增强的 JSON 函数

-- SQL 方式
SELECT sqlite_version();

-- 命令行方式
sqlite3 --version