SQLite 语法
SQLite 语法大全（2025 最新版）
SQLite 支持 SQL-92 标准 的绝大部分语法，并扩展了 JSON、FTS、窗口函数、生成列 等现代特性。
一、核心语法结构
[命令] [对象] [条件/选项];| 命令 | 对象 | 示例 |
|---|---|---|
CREATE | TABLE, INDEX, VIEW, TRIGGER | CREATE TABLE users(...); |
SELECT | *, 列, 函数 | SELECT name, age FROM users; |
INSERT | INTO 表 | INSERT INTO users VALUES(...); |
UPDATE | 表 SET | UPDATE users SET age = 25; |
DELETE | FROM 表 | DELETE FROM users WHERE id = 1; |
ALTER | TABLE | ALTER TABLE users RENAME TO people; |
DROP | TABLE, INDEX | DROP TABLE users; |
二、数据类型（动态类型系统）
| 类型 | 说明 | 示例 |
|---|---|---|
INTEGER | 整型（1/2/3/4/6/8 字节） | id INTEGER PRIMARY KEY |
TEXT | 字符串（UTF-8） | name TEXT |
REAL | 浮点数（8字节） | price REAL |
BLOB | 二进制数据 | photo BLOB |
NUMERIC | 数值（支持小数） | score NUMERIC |
弱类型：允许
INTEGER列存'abc'（不推荐！）
类型亲和性（Affinity）：
INT → INTEGER
CHAR, TEXT → TEXT
BLOB → BLOB
REAL, FLOAT → REAL
NUMERIC, DECIMAL → NUMERIC三、CREATE TABLE 语法
CREATE [TEMP] TABLE [IF NOT EXISTS] 表名 (
列名 类型 [约束],
...
[表级约束]
);列约束
| 约束 | 说明 |
|---|---|
PRIMARY KEY | 主键 |
NOT NULL | 非空 |
UNIQUE | 唯一 |
CHECK(条件) | 检查 |
DEFAULT 值 | 默认值 |
COLLATE 排序规则 | 排序规则（如 NOCASE） |
表级约束
PRIMARY KEY(列1, 列2) -- 复合主键
UNIQUE(列1, 列2)
FOREIGN KEY(列) REFERENCES 表(列) [ON DELETE CASCADE]
CHECK(age >= 0)示例
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT UNIQUE,
age INTEGER CHECK(age >= 0),
status TEXT DEFAULT 'active',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(status) REFERENCES status(id)
);四、INSERT 语法
INSERT INTO 表名 [列列表] VALUES (值列表);
INSERT INTO 表名 [列列表] SELECT ...;示例
-- 单行
INSERT INTO users (username, email) VALUES ('alice', 'a@example.com');
-- 多行
INSERT INTO users (username, email) VALUES
('bob', 'b@example.com'),
('carol', 'c@example.com');
-- 从查询插入
INSERT INTO users_backup SELECT * FROM users WHERE age > 18;
AUTOINCREMENT确保主键递增（有性能开销）
五、SELECT 语法（最复杂）
SELECT [DISTINCT] 列/表达式
FROM 表/视图
[JOIN 表 ON 条件]
[WHERE 条件]
[GROUP BY 列]
[HAVING 条件]
[WINDOW 窗口名 AS (分区...)]
[ORDER BY 列 [ASC|DESC]]
[LIMIT 数量 [OFFSET 偏移]]示例
-- 基本查询
SELECT name, age FROM users WHERE age > 25 ORDER BY age DESC LIMIT 10;
-- 聚合 + 分组
SELECT department, AVG(salary) as avg_salary, COUNT(*) as count
FROM employees
GROUP BY department
HAVING count > 5;
-- 连接
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 窗口函数（3.25+）
SELECT name, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;六、UPDATE 语法
UPDATE 表名
SET 列 = 值 [, 列 = 值...]
[WHERE 条件];示例
UPDATE users SET age = age + 1 WHERE birthday LIKE '%-01-01';
UPDATE users SET status = 'inactive' WHERE last_login < '2024-01-01';七、DELETE 语法
DELETE FROM 表名 [WHERE 条件];示例
DELETE FROM users WHERE status = 'inactive';
DELETE FROM logs WHERE created_at < '2023-01-01';八、ALTER TABLE 语法（功能有限）
| 操作 | 语法 |
|---|---|
| 重命名表 | ALTER TABLE old RENAME TO new; |
| 添加列 | ALTER TABLE tbl ADD COLUMN col TYPE; |
| 重命名列（3.25+） | ALTER TABLE tbl RENAME COLUMN old TO new; |
| 删除列（3.35+） | ALTER TABLE tbl DROP COLUMN col; |
不能：修改列类型、删除列（旧版本）、修改约束
九、索引 INDEX
CREATE [UNIQUE] INDEX 索引名 ON 表名(列1, 列2) [WHERE 条件];示例
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_unique_username ON users(username);
CREATE INDEX idx_partial ON users(status) WHERE status = 'active'; -- 部分索引十、视图 VIEW
CREATE [TEMP] VIEW 视图名 AS SELECT ...;示例
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';
-- 查询视图
SELECT * FROM active_users;十一、事务 TRANSACTION
BEGIN [DEFERRED|IMMEDIATE|EXCLUSIVE];
-- SQL 语句
COMMIT;
-- 或
ROLLBACK;示例
BEGIN;
INSERT INTO accounts VALUES(1, 'Alice', 1000);
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;十二、常用函数
| 类别 | 函数 | 示例 |
|---|---|---|
| 字符串 | LENGTH(), UPPER(), LOWER(), SUBSTR(), TRIM() | UPPER(name) |
| 数学 | ROUND(), ABS(), RANDOM() | ROUND(price, 2) |
| 日期 | DATE(), TIME(), DATETIME(), STRFTIME() | STRFTIME('%Y', created_at) |
| 聚合 | COUNT(), SUM(), AVG(), MAX(), MIN() | COUNT(*) |
| JSON（3.38+） | json_extract(), json_array(), json_object() | json_extract(data, '$.name') |
十三、条件表达式
| 语法 | 说明 |
|---|---|
CASE WHEN 条件 THEN 结果 ELSE 结果 END | 条件判断 |
COALESCE(值1, 值2, ...) | 返回第一个非 NULL 值 |
NULLIF(值1, 值2) | 若相等返回 NULL |
SELECT name,
COALESCE(phone, email, '无联系方式') as contact
FROM users;
SELECT name,
CASE
WHEN age < 18 THEN '未成年'
WHEN age < 65 THEN '成年'
ELSE '老年'
END as age_group
FROM users;十四、高级特性
| 特性 | 版本 | 示例 |
|---|---|---|
| 窗口函数 | 3.25+ | ROW_NUMBER() OVER (...) |
| 生成列 | 3.31+ | total REAL GENERATED ALWAYS AS (price * qty) VIRTUAL |
| UPSERT | 3.24+ | INSERT ... ON CONFLICT DO UPDATE |
| 过滤聚合 | 3.30+ | COUNT(*) FILTER (WHERE age > 30) |
| RETURNING | 3.35+ | DELETE FROM users WHERE id=1 RETURNING *; |
-- UPSERT 示例
INSERT INTO users (id, name) VALUES (1, '新用户')
ON CONFLICT(id) DO UPDATE SET name = excluded.name;十五、PRAGMA 实用语法
PRAGMA foreign_keys = ON; -- 开启外键
PRAGMA journal_mode = WAL; -- WAL 模式
PRAGMA cache_size = -20000; -- 缓存 20MB
PRAGMA optimize; -- 优化查询十六、语法速查表（打印版）
-- 创建
CREATE TABLE t(id INT PRIMARY KEY, name TEXT NOT NULL);
-- 插入
INSERT INTO t VALUES(1, 'A'), (2, 'B');
-- 查询
SELECT * FROM t WHERE name LIKE 'A%' ORDER BY id DESC LIMIT 5;
-- 更新
UPDATE t SET name = 'New' WHERE id = 1;
-- 删除
DELETE FROM t WHERE id > 10;
-- 事务
BEGIN; ...; COMMIT;
-- 索引
CREATE INDEX idx_name ON t(name);
-- 视图
CREATE VIEW v AS SELECT * FROM t WHERE active = 1;十七、常见错误
| 错误 | 原因 | 解决 |
|---|---|---|
no such table | 表不存在 | 检查拼写、数据库路径 |
datatype mismatch | 类型不匹配 | 使用正确类型或转换 |
foreign key constraint failed | 外键违反 | 开启 foreign_keys = ON |
database is locked | 写冲突 | 使用 WAL 模式 |
十八、学习建议
- 背熟
CREATE TABLE约束 - 掌握
SELECT的 6 大子句顺序：
FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT- 用
EXPLAIN QUERY PLAN分析性能 - 开启
foreign_keys = ON防止数据不一致
下一步？ 回复：
SELECT 高级→ 窗口函数、CTE、JSON性能优化→ 索引、PRAGMA、EXPLAINPython 语法→sqlite3模块实战常见错误→ 排错指南
现在试试：
SELECT sqlite_version();看看你用的是什么版本！