SQLite使用全面教程：轻量级数据库的终极指南

sudo apt install sqlite3

brew install sqlite

sqlite3 --version

3.46.1 2024-08-13 09:16:08 c9c2ab54ba1f5f46360f1b4f35d849cd3f080e6fc2b6c60e91b16c63f69aalt1 (64-bit)

# 正确：点命令直接敲进 CLI
sqlite3 mydatabase.db

sqlite> CREATE TABLE t(id INTEGER PRIMARY KEY, name TEXT);
sqlite> .tables
t
sqlite> .quit

# 文件不存在会自动创建，所以"连接"和"建库"是同一个动作
sqlite3 mydatabase.db

# 内存库：进程退出数据即消失，适合测试
sqlite3 :memory:

CREATE TABLE affinity_demo (
  a INTEGER,
  b TEXT,
  c REAL,
  d NUMERIC,
  e BLOB
);

-- 推荐：普通自增，性能好，ID 可能被重用
CREATE TABLE t_plain (id INTEGER PRIMARY KEY, name TEXT);

-- 只在"主键绝不能重用"时用（比如 ID 会暴露给外部系统）
CREATE TABLE t_autoinc (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);

CREATE TABLE users (
  id         INTEGER PRIMARY KEY,                       -- rowid 别名，自增
  name       TEXT    NOT NULL,
  email      TEXT    UNIQUE,                            -- 唯一约束
  age        INTEGER CHECK (age >= 0),
  created_at TEXT    DEFAULT (datetime('now'))          -- 默认值
);

ALTER TABLE users ADD COLUMN phone TEXT;              -- 加列（最常用）
ALTER TABLE users RENAME TO app_users;                -- 改名
ALTER TABLE users RENAME COLUMN phone TO mobile;      -- 改列名（3.25+）
ALTER TABLE users DROP COLUMN mobile;                 -- 删列（3.35+）

INSERT INTO users (name, email) VALUES
  ('张三', 'z@example.com'),
  ('李四', 'l@example.com');

SELECT * FROM users;                          -- 全部列
SELECT id, name FROM users WHERE age > 18;    -- 指定列 + 条件
SELECT DISTINCT name FROM users;              -- 去重
SELECT * FROM users ORDER BY name ASC LIMIT 10 OFFSET 20;  -- 排序分页

UPDATE users SET name = '张三丰' WHERE id = 1;
DELETE FROM users WHERE id = 3;

-- 主键冲突时更新
INSERT INTO users (id, name, email) VALUES (1, '张三', 'z@example.com')
ON CONFLICT(id) DO UPDATE SET email = excluded.email;

-- 邮箱冲突时不动作
INSERT INTO users (id, name, email) VALUES (1, '张三', 'z@example.com')
ON CONFLICT(email) DO NOTHING;

INSERT OR IGNORE INTO users (id, name) VALUES (1, '张三');   -- 冲突则跳过
INSERT OR REPLACE INTO users (id, name) VALUES (1, '张三');  -- 冲突则删除旧行再插入

INSERT INTO users (name, email) VALUES ('王五', 'w@example.com')
RETURNING id, name;

3|王五

sqlite3 demo.db "PRAGMA foreign_keys;"

0

PRAGMA foreign_keys = ON;

CREATE TABLE orders (
  id      INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  product TEXT,
  amount  REAL
);

SELECT date('now');                                   -- 2026-09-22
SELECT datetime('now');                               -- 2026-09-22 10:45:15（UTC）
SELECT strftime('%Y-%m-%d %H:%M:%S', 'now');          -- 同上，格式自定
SELECT unixepoch('now');                              -- 1790073915（秒级时间戳，3.38+）
SELECT date('now', '+1 month', 'start of month');     -- 2026-10-01（可串联修饰符）

SELECT datetime('now', 'localtime');

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- 出错时用 ROLLBACK;

sqlite3 demo.db "PRAGMA journal_mode=WAL;"
sqlite3 demo.db "PRAGMA journal_mode;"

wal

PRAGMA busy_timeout = 5000;   -- 忙时等待 5 秒再放弃，而不是立刻报错

CREATE INDEX idx_users_email ON users(email);            -- 单列
CREATE INDEX idx_orders_user_amount ON orders(user_id, amount);  -- 复合
CREATE UNIQUE INDEX idx_users_name ON users(name);       -- 唯一索引
DROP INDEX idx_users_name;                               -- 删（注意：没有 ON 子句）

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

QUERY PLAN
`--SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)

PRAGMA cache_size = -8000;   -- 8000 KiB ≈ 8 MB 页缓存

PRAGMA synchronous = NORMAL;  -- WAL 模式下的推荐值，兼顾安全与速度
PRAGMA mmap_size = 268435456; -- 256 MB 内存映射，可加速读

sqlite3 demo.db ".backup '/path/to/backup.db'"

sqlite3 demo.db .dump > dump.sql

/* WARNING: Script requires that SQLITE_DBCONFIG_DEFENSIVE be disabled */
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
...

sqlite3 new.db < dump.sql

sqlite3 corrupt.db .dump > recovery.sql   # 尽量导出
sqlite3 new.db < recovery.sql             # 导入新库

sqlite3 corrupt.db .recover > recovery.sql

sqlite3 demo.db

sqlite> .headers on
sqlite> .mode csv
sqlite> .output users.csv
sqlite> SELECT * FROM users;
sqlite> .output stdout

id,name,email
1,"张三",z@example.com
2,"李四",l@example.com

sqlite3 -header -csv demo.db "SELECT id, name, email FROM users;" > users.csv

sqlite3 demo.db

sqlite> CREATE TABLE temp_import(name TEXT, email TEXT);
sqlite> .mode csv
sqlite> .import /path/to/new_users.csv temp_import

INSERT INTO users (name, email) SELECT name, email FROM temp_import;
DROP TABLE temp_import;

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

INSERT INTO docs VALUES
  ('SQLite Guide', 'Comprehensive guide'),
  ('Python Tutorial', 'Learn Python');

SELECT title FROM docs WHERE docs MATCH 'guide OR python';

SQLite Guide
Python Tutorial

import sqlite3
from datetime import datetime

conn = sqlite3.connect('mydatabase.db')
conn.execute('PRAGMA foreign_keys = ON')   # 别忘了这句
cur = conn.cursor()

cur.execute('''
    CREATE TABLE IF NOT EXISTS orders (
        id         INTEGER PRIMARY KEY,
        user_id    INTEGER NOT NULL REFERENCES users(id),
        product    TEXT,
        amount     REAL,
        order_date TEXT
    )
''')

rows = [
    (1, 'Laptop', 1200.50, datetime.now().isoformat()),
    (1, 'Mouse', 25.99, datetime.now().isoformat()),
]
cur.executemany(
    "INSERT INTO orders (user_id, product, amount, order_date) VALUES (?, ?, ?, ?)",
    rows,
)

# 查询：注意按列下标取值
cur.execute('''
    SELECT users.name, orders.product, orders.amount
    FROM users JOIN orders ON users.id = orders.user_id
''')
for name, product, amount in cur.fetchall():
    print(f"{name} 买了 {product}，花了 ${amount:.2f}")

conn.commit()
conn.close()

with sqlite3.connect('mydatabase.db') as conn:
    conn.execute("INSERT INTO users (name) VALUES (?)", ('赵六',))
    # 出异常自动回滚，正常结束自动提交

# 看看是谁占着文件
lsof mydatabase.db

ATTACH DATABASE 'another.db' AS other;
SELECT * FROM main.users UNION ALL SELECT * FROM other.users;
DETACH DATABASE other;

# —— 连接 ——
sqlite3 db.db                    # 打开/创建
sqlite3 :memory:                 # 内存库
sqlite3 -header -csv db.db "SQL" # 一行出 CSV

# —— CLI 点命令 ——
.tables   .schema t   .indexes t   .databases
.mode column   .headers on   .width 10 20
.output f   .import f.csv t   .dump   .read f.sql
.backup f   .recover   .quit

# —— 必开的 PRAGMA ——
PRAGMA foreign_keys = ON;        # 外键（默认关！）
PRAGMA journal_mode = WAL;       # 提升并发读
PRAGMA busy_timeout = 5000;      # 忙等待
PRAGMA cache_size = -8000;       # 8MB 缓存（负值单位为 KiB）

-- —— 常用语句 ——（每行为独立示意，勿当作连续脚本执行）
INSERT INTO t(a,b) VALUES (1,2);
INSERT INTO t(a,b) VALUES (1,2) ON CONFLICT(a) DO UPDATE SET b=excluded.b;
INSERT INTO t(a,b) VALUES (1,2) RETURNING a;
SELECT * FROM t WHERE a > 10 ORDER BY b LIMIT 5;
EXPLAIN QUERY PLAN SELECT * FROM t WHERE a = 1;
CREATE INDEX idx ON t(a);
VACUUM;                          -- 回收空间、整理碎片

// 片段示意，需在 Activity/Context 中调用
SQLiteDatabase db = openOrCreateDatabase("app_data.db", MODE_PRIVATE, null);
db.execSQL("CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT)");
ContentValues values = new ContentValues();
values.put("key", "theme");
values.put("value", "dark");
db.insert("settings", null, values);

import sqlite3

conn = sqlite3.connect('weblog.db')
conn.execute('''CREATE TABLE IF NOT EXISTS visits (
    id        INTEGER PRIMARY KEY,
    ip        TEXT,
    url       TEXT,
    visited_at TEXT DEFAULT (datetime('now'))
)''')

conn.execute("INSERT INTO visits (ip, url) VALUES (?, ?)", ('192.168.1.1', '/homepage'))
conn.commit()

# 统计每个 URL 的访问量
for url, cnt in conn.execute(
    "SELECT url, COUNT(*) FROM visits GROUP BY url ORDER BY COUNT(*) DESC"
):
    print(f"{cnt:>6}  {url}")
conn.close()