SQLite

SQLite 是全球部署量最大的嵌入式关系型数据库引擎，由 D. Richard Hipp 于 2000 年创建。它是自包含、无服务器、零配置的，整个数据库就是一个单一文件（.db），无需独立进程。SQLite 被嵌入在每一个 Android/iOS 手机、Chrome/Firefox 浏览器、Python 标准库中，是移动开发、边缘计算、IoT、桌面应用和原型开发的理想选择。支持 ACID 事务、大多数 SQL 标准，以"小、快、可靠"闻名。

1. 环境准备

• 操作系统：所有主流 OS（SQLite 已内置于大多数系统）
• 运行时：无需独立服务器进程，SQLite 是库文件
• 依赖：无外部依赖（单个 C 源文件约 750KB）
• 工具：推荐安装 sqlite3 CLI 工具（通常系统自带）

2. 安装命令

验证是否已安装

sqlite3 --version
# 输出类似：3.42.0 2023-05-16...

大多数 Linux/macOS 已自带。若无：

Ubuntu/Debian

sudo apt install sqlite3 libsqlite3-dev

macOS

brew install sqlite

Windows

choco install sqlite
# 或下载预编译二进制：https://www.sqlite.org/download.html

Python（标准库自带）

import sqlite3  # 无需安装，Python 内置！
print(sqlite3.sqlite_version)

Node.js

npm install better-sqlite3

3. 常见安装问题

Q: sqlite3 命令 not found

which sqlite3
# 无输出则手动安装，或直接用 Python 的 import sqlite3

Q: Python 版本过低导致 sqlite3 版本旧

# 升级 Python 即可。Python 3.8+ 自带 SQLite 3.30+
python3 --version

Q: 并发写入冲突
SQLite 默认只支持单写入者。高并发写入场景考虑 PostgreSQL/MySQL。
解决方案：使用 WAL 模式缓解并发读：

PRAGMA journal_mode=WAL;

目标

通过 sqlite3 CLI 和 Python 标准库，完成 SQLite 数据库创建、表操作、CRUD，体验 SQLite "零配置"的优势。

环境准备

sqlite3 --version
python3 -c "import sqlite3; print('OK')"  # 确认自带

第一步：CLI 方式（最简）

# 创建数据库文件并进入交互模式
sqlite3 hello.db

-- 创建表
CREATE TABLE todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    task TEXT NOT NULL,
    done BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 插入
INSERT INTO todos (task) VALUES
    ('完成数据库作业'),
    ('复习SQL语法'),
    ('写实验报告');

-- 查询
SELECT * FROM todos;
.mode column          -- 格式化输出
.headers on
SELECT * FROM todos WHERE done = 0;

-- 更新
UPDATE todos SET done = 1 WHERE id = 1;

-- 删除
DELETE FROM todos WHERE id = 3;

SELECT * FROM todos;
.quit

第二步：Python 方式

import sqlite3

# 连接数据库（不存在则自动创建）
conn = sqlite3.connect("hello.db")
cursor = conn.cursor()

# 创建表
cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        score REAL,
        grade TEXT GENERATED ALWAYS AS (
            CASE
                WHEN score >= 90 THEN 'A'
                WHEN score >= 80 THEN 'B'
                WHEN score >= 70 THEN 'C'
                ELSE 'F'
            END
        ) VIRTUAL
    )
""")

# 批量插入
students = [
    ("Alice", 95.5),
    ("Bob", 82.0),
    ("Charlie", 76.5),
]
cursor.executemany(
    "INSERT INTO students (name, score) VALUES (?, ?)",
    students
)
conn.commit()

# 查询
cursor.execute("SELECT * FROM students ORDER BY score DESC")
for row in cursor.fetchall():
    print(f"ID={row[0]} | {row[1]} | {row[2]}分 | {row[3]}")

# 统计
cursor.execute("SELECT grade, COUNT(*) FROM students GROUP BY grade")
for grade, count in cursor.fetchall():
    print(f"Grade {grade}: {count}人")

cursor.close()
conn.close()

第三步：Python 上下文管理器（推荐）

import sqlite3

with sqlite3.connect("hello.db") as conn:
    conn.row_factory = sqlite3.Row  # 字典式访问
    rows = conn.execute("SELECT * FROM students").fetchall()
    for r in rows:
        print(f"{r['name']}: {r['score']}分 → {r['grade']}")

预期输出

# CLI
id  task        done  created_at
--  ----------  ----  -------------------
1   完成数据库作业  1    2025-01-01 10:00:00
2   复习SQL语法     0    2025-01-01 10:00:00

# Python
ID=1 | Alice | 95.5分 | A
ID=2 | Bob | 82.0分 | B
ID=3 | Charlie | 76.5分 | C
Grade A: 1人
Grade B: 1人
Grade C: 1人

1. 什么是 SQLite？

SQLite 是"无服务器"的关系型数据库引擎。整个数据库就是一个 .db 文件，你把它放在哪都可以。它被嵌入在你的应用中，不需要安装、配置或管理。全球有超过 1 万亿个 SQLite 数据库在运行——你的手机里就有几十个。

关键特性

• 零配置：无需 CREATE DATABASE，打开文件即数据库
• 单文件：一个 .db 文件包含全部内容，可随意复制
• ACID 兼容：事务支持与大型数据库相同
• 大小：库文件 ~750KB，适合嵌入任何应用

2. SQLite vs 其他数据库

场景	SQLite	MySQL/PostgreSQL
移动 App 本地存储	✅ 完美	❌ 过重
桌面应用	✅ 完美	⚠️ 可能
Web 网站（低并发）	✅ 可用	✅ 推荐
Web 网站（高并发写入）	❌ 不适合	✅ 推荐
数据分析	⚠️ 中等数据集	✅ 大数据集
IoT / 嵌入式设备	✅ 完美	❌ 资源不足
原型开发	✅ 完美	⚠️ 需配置

3. 实战：Todo App 数据库设计

CREATE TABLE projects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    color TEXT DEFAULT '#3498db',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id INTEGER,
    title TEXT NOT NULL,
    description TEXT,
    priority INTEGER CHECK(priority BETWEEN 1 AND 5) DEFAULT 3,
    due_date DATE,
    completed BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- 复合索引
CREATE INDEX idx_tasks_project_priority ON tasks(project_id, priority);
CREATE INDEX idx_tasks_due ON tasks(due_date);

4. SQLite 独有技巧

日期时间处理

-- SQLite 没有 DATETIME 类型，用 TEXT/INTEGER 存储
SELECT
    date('now') AS today,
    datetime('now', '+7 days') AS next_week,
    strftime('%Y-%m-%d', due_date) AS formatted_date
FROM tasks;

全文搜索 (FTS5)

CREATE VIRTUAL TABLE tasks_fts USING fts5(title, description);

-- 与主表同步
CREATE TRIGGER tasks_fts_insert AFTER INSERT ON tasks BEGIN
    INSERT INTO tasks_fts (rowid, title, description)
    VALUES (new.id, new.title, new.description);
END;

-- 搜索
SELECT * FROM tasks_fts WHERE tasks_fts MATCH 'database OR SQL';

UPSERT（插入或更新）

INSERT INTO projects (name, color) VALUES ('学习', '#e74c3c')
ON CONFLICT(name) DO UPDATE SET color = excluded.color;

5. Python 实战：完整 Todo CLI

import sqlite3
import sys

DB = "todos.db"

def init():
    with sqlite3.connect(DB) as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS todos (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                task TEXT NOT NULL,
                done BOOLEAN DEFAULT 0
            )
        """)

def add(task):
    with sqlite3.connect(DB) as conn:
        conn.execute("INSERT INTO todos (task) VALUES (?)", (task,))

def list_todos():
    with sqlite3.connect(DB) as conn:
        rows = conn.execute(
            "SELECT id, task, done FROM todos ORDER BY id"
        ).fetchall()
    for r in rows:
        status = "✅" if r[2] else "⬜"
        print(f"{status} [{r[0]}] {r[1]}")

def done(tid):
    with sqlite3.connect(DB) as conn:
        conn.execute("UPDATE todos SET done=1 WHERE id=?", (tid,))

if __name__ == "__main__":
    init()
    cmd = sys.argv[1] if len(sys.argv) > 1 else "list"
    if cmd == "add":
        add(" ".join(sys.argv[2:]))
    elif cmd == "done":
        done(int(sys.argv[2]))
    list_todos()

思考题

1. SQLite 的 VACUUM 什么时候需要执行？会带来什么问题？
2. 为什么 SQLite 不适合高并发 Web 应用？WAL 模式能缓解到何种程度？
3. 把 SQLite .db 文件放到 Git 里作为"数据库即代码"的方案，你是否赞成？

必知命令

sqlite3 db.sqlite .dump >; backup.sql    # 导出
sqlite3 db.sqlite <; backup.sql          # 恢复
sqlite3 db.sqlite "PRAGMA integrity_check"  # 检查损坏
sqlite3 db.sqlite VACUUM                # 压缩数据库