SQLite 常用命令与核心语法全能速查表 (SQLite CheatSheet)

SQLite 作为全球安装量最大的嵌入式轻量级关系型数据库，广泛应用于移动端（iOS / Android）、桌面客户端（Electron）、边缘计算设备及轻量级服务端开发。

由于其零配置（Serverless）、单文件存储以及独特的动态类型机制，SQLite 在使用体验上与传统数据库存在诸多差异。本文为您整理了一份案头必备的 SQLite 常用技能全景速查。

一、 sqlite3 命令行终端核心“点命令” (Dot Commands)

在终端运行 sqlite3 mydatabase.db 进入交互式环境后，所有以点（.）开头的指令均属于命令行专用元命令：

点命令	功能与用法说明	实用技巧 / 示例
`.help`	查看所有可用的点命令清单	随时查阅帮助
`.databases`	查看当前已附加（ATTACH）的数据库文件与路径	检查多库挂载
`.tables` 或 `.tables pattern`	列出当前数据库的所有表名	`.tables user*` 模糊匹配
`.schema table_name`	查看建表 DDL 语句与索引定义	查看表结构与约束
`.mode format`	切换输出呈现格式	`.mode box`（表格框，推荐） `.mode csv`（CSV 格式） `.mode json`（JSON 数组） `.mode column`（对齐列）
`.headers on/off`	开启或关闭查询结果的表头字段名显示	建议默认 `.headers on`
`.import file table`	将 CSV 或文本文件直接导入到指定数据表	`.import --csv data.csv users`
`.dump [table]`	将整库或指定表导出为标准 SQL 文本文件	`.dump > backup.sql`
`.read filename.sql`	读取并执行外部 SQL 脚本文件	`.read /tmp/init.sql`
`.open filename.db`	关闭当前库并打开新的数据库文件	随时切换数据库
`.quit` 或 `.exit`	退出 sqlite3 控制台	快捷键 `Ctrl + D`

[!TIP] 推荐在个人根目录创建 ~/.sqliterc 文件，写入以下两行，每次打开 sqlite3 即可默认享受美观排版：
.headers on
.mode box

二、生产必备：核心 PRAGMA 性能与安全调优

SQLite 默认的开箱配置更倾向于极致的极端断电安全（保守写入），通过调整几条关键的 PRAGMA 指令，能够让 SQLite 的写入吞吐量暴增 5~10 倍！

1. 开启 WAL 预写日志模式（大幅提升并发读写）

-- 强烈推荐！开启 Write-Ahead Logging 模式，实现读写互不阻塞
PRAGMA journal_mode = WAL;

默认模式下（Rollback Journal），只要有写事务，所有读操作就会被锁住。
开启 WAL 后，读操作读取主数据库文件，写操作追加写入 -wal 文件，支持多个读者和一个写者完全并发并发！

2. 调整同步刷盘级别 (synchronous)

-- 在 WAL 模式下推荐设置为 NORMAL，极大提升写入性能且依然保持断电 ACID 安全
PRAGMA synchronous = NORMAL;

3. 必须显式开启外键约束检查

-- 重要：SQLite 出于向下兼容，默认不强制校验外键！每个连接需显式开启：
PRAGMA foreign_keys = ON;

4. 调整内存缓存大小与临时存储

-- 将缓存扩大到 64MB (默认仅 2MB)
PRAGMA cache_size = -64000;

-- 临时表和索引放在内存中处理，提升复杂聚合与 ORDER BY 速度
PRAGMA temp_store = MEMORY;

三、表定义与现代 SQL 语法特性

1. 经典建表范例（包含自增主键与 JSON 约束）

CREATE TABLE IF NOT EXISTS articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,  -- 必须为 INTEGER PRIMARY KEY
    title TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,
    content TEXT DEFAULT '',
    tags JSON DEFAULT '[]',                 -- SQLite 原生支持 JSON 字段
    view_count INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2. UPSERT 语法（存在则更新，不存在则插入）

SQLite 3.24.0+ 原生支持标准 ON CONFLICT 语法：

INSERT INTO articles (slug, title, view_count) 
VALUES ('mysql-tutorial', 'MySQL 入门教程', 1)
ON CONFLICT(slug) DO UPDATE SET 
    view_count = articles.view_count + 1,
    title = excluded.title;

四、常用内置函数速查

1. 字符串与聚合函数

函数	功能说明	语法示例
`LENGTH(str)`	字符数（如果是 BLOB 则返回字节数）	`SELECT LENGTH('数据库'); -- 3`
`LOWER(str)` / `UPPER(str)`	大小写转换	`SELECT LOWER('SQLite');`
`SUBSTR(str, pos, len)`	字符串截取（起始索引为 1）	`SELECT SUBSTR('Hello World', 1, 5); -- Hello`
`REPLACE(str, from, to)`	字符串全局替换	`SELECT REPLACE('abc_def', '_', '-');`
`TRIM(str)` / `LTRIM` / `RTRIM`	剔除两端或单端空白	`SELECT TRIM(' test ');`
`INSTR(str, substr)`	查找子串首次出现位置	`SELECT INSTR('database', 'base'); -- 5`
`GROUP_CONCAT(expr, sep)`	组内字符串拼接	`SELECT dept, GROUP_CONCAT(name, ', ') FROM emp GROUP BY dept;`
`COALESCE(v1, v2, ...)`	返回首个非 NULL 的值	`SELECT COALESCE(nickname, username, '匿名');`

2. 日期与时间函数（SQLite 独特时间处理）

SQLite 内部没有独立的日期数据类型，通常以 TEXT（ISO-8601 字符串）或 INTEGER（Unix 时间戳）存储：

-- 1. 获取当前 UTC 日期时间与本地时间
SELECT datetime('now');                    -- UTC 时间: 2026-10-31 06:00:00
SELECT datetime('now', 'localtime');       -- 本地时区时间

-- 2. 日期加减与区间运算
SELECT date('now', '+7 days');             -- 7 天后
SELECT datetime('now', '-1 month');        -- 1 个月前
SELECT datetime('now', 'start of month');  -- 本月第 1 天 0 点

-- 3. 格式化输出 (STRFTIME)
SELECT strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime');

3. 原生 JSON 函数 (JSON1 模块)

-- 提取 JSON 字段内容
SELECT json_extract('{"name": "Alice", "skills": ["SQL", "Go"]}', '$.name'); -- "Alice"

-- 判断 JSON 是否包含有效结构
SELECT json_valid('{"valid": true}'); -- 返回 1

-- 在查询结果中直接生成 JSON 对象
SELECT json_object('id', id, 'title', title) FROM articles;

五、主流编程语言极速连接代码片段

Python (标准内置库，无需安装依赖)

import sqlite3

# 连接数据库（若文件不存在会自动创建）
conn = sqlite3.connect("database.db")
cursor = conn.cursor()

# 开启 WAL 模式提升吞吐量
cursor.execute("PRAGMA journal_mode = WAL;")

cursor.execute("SELECT id, title FROM articles WHERE view_count > ?", (100,))
rows = cursor.fetchall()
for row in rows:
    print(row)

conn.close()

Node.js (better-sqlite3 极致性能库)

const Database = require('better-sqlite3');
const db = new Database('database.db');

// 开启 WAL 模式
db.pragma('journal_mode = WAL');

const stmt = db.prepare('SELECT id, title FROM articles WHERE id = ?');
const article = stmt.get(1);
console.log(article);

结语

本速查表涵盖了 SQLite 日常交互、底层调优与高频内置函数的关键用法。关于 SQLite 每一个函数的详细运行参数与返回结果示例，请参阅本站的 SQLite 函数参考手册。