下面是一份 完整、实用的 SQLite 创建表（CREATE TABLE）指南，涵盖语法、数据类型、约束、常用技巧、示例代码（含 Python），帮助你快速上手。
一、基本语法
CREATE TABLE [IF NOT EXISTS] 表名 (
列名1 数据类型 [约束],
列名2 数据类型 [约束],
...
[表级约束]
);IF NOT EXISTS：表已存在时不报错（推荐加上）- 列定义之间用逗号分隔，最后一个列后不要逗号
二、SQLite 支持的常用数据类型
| 类型 | 说明 | 示例 |
|---|---|---|
INTEGER | 整数（1, 2, 4, 8 字节自动选择） | id INTEGER |
TEXT | 文本字符串（UTF-8/UTF-16） | name TEXT |
REAL | 浮点数（8字节） | price REAL |
BLOB | 二进制数据（如图片） | avatar BLOB |
NUMERIC | 数字（可存整数或小数） | score NUMERIC |
SQLite 是弱类型：你可以把字符串存进
INTEGER列，但建议遵守类型以保持语义清晰。
三、列约束（Column Constraints）
| 约束 | 作用 | 示例 |
|---|---|---|
PRIMARY KEY | 主键，唯一标识行 | id INTEGER PRIMARY KEY |
NOT NULL | 不能为空 | name TEXT NOT NULL |
UNIQUE | 唯一值 | email TEXT UNIQUE |
DEFAULT 值 | 默认值 | status INTEGER DEFAULT 1 |
CHECK(条件) | 检查条件 | age INTEGER CHECK(age >= 0) |
COLLATE 排序规则 | 字符串比较规则 | name TEXT COLLATE NOCASE |
四、特殊主键：INTEGER PRIMARY KEY
id INTEGER PRIMARY KEY等价于：
- 自增 ID（
AUTOINCREMENT可选） - 自动创建
ROWID别名 - 唯一且不为空
使用
AUTOINCREMENT会略微影响性能，除非你需要严格递增无空洞，否则不用加。
id INTEGER PRIMARY KEY AUTOINCREMENT五、表级约束（Table Constraints）
写在所有列定义之后：
CREATE TABLE orders (
order_id INTEGER,
user_id INTEGER,
total REAL,
PRIMARY KEY (order_id),
FOREIGN KEY (user_id) REFERENCES users(id),
UNIQUE (order_id, user_id)
);六、完整示例：用户 + 订单表
-- 用户表
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT UNIQUE,
password_hash TEXT NOT NULL,
created_at TEXT DEFAULT (datetime('now')),
is_active INTEGER DEFAULT 1 CHECK(is_active IN (0, 1))
);
-- 订单表
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
total_amount REAL DEFAULT 0.0 CHECK(total_amount >= 0),
status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'paid', 'shipped', 'cancelled')),
created_at TEXT DEFAULT (datetime('now')),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);七、临时表（Temporary Table）
CREATE TEMP TABLE temp_data (
key TEXT,
value REAL
);- 仅当前连接可见
- 连接关闭自动删除
- 适合中间计算、导入导出
八、创建索引提升查询性能
-- 单列索引
CREATE INDEX idx_users_email ON users(email);
-- 复合索引
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- 唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);九、Python 中创建表示例（sqlite3）
import sqlite3
# 连接数据库（不存在则创建）
conn = sqlite3.connect('mydatabase.db')
cur = conn.cursor()
# 创建用户表
cur.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE,
age INTEGER CHECK(age >= 0),
created_at TEXT DEFAULT (datetime('now'))
)
''')
# 插入数据
cur.execute("INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
('张三', 'zhang@example.com', 25))
# 提交事务
conn.commit()
# 查询
cur.execute("SELECT * FROM users")
print(cur.fetchall())
conn.close()十、常见错误与避免方法
| 错误 | 原因 | 解决 |
|---|---|---|
near ",": syntax error | 最后一列后加了逗号 | 删除最后一个逗号 |
no such table | 表未创建或连接了错文件 | 检查路径、IF NOT EXISTS |
datatype mismatch | 插入类型错误 | 确保类型匹配或使用参数化查询 |
UNIQUE constraint failed | 重复插入唯一字段 | 检查数据或用 INSERT OR REPLACE |
十一、高级技巧
1. 动态建表（Python 字典转表）
def create_table_from_dict(conn, table_name, data_dict):
columns = []
for key, value in data_dict.items():
col_type = {
int: 'INTEGER',
float: 'REAL',
str: 'TEXT',
bytes: 'BLOB'
}.get(type(value), 'TEXT')
columns.append(f"{key} {col_type}")
sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({', '.join(columns)})"
conn.execute(sql)2. 复制表结构（不复制数据）
CREATE TABLE backup_users AS SELECT * FROM users WHERE 0;
-- 或
CREATE TABLE backup_users LIKE users; -- SQLite 3.3+ 支持总结：建表 checklist
| 检查项 | 是否完成 |
|---|---|
使用 IF NOT EXISTS | Yes |
主键使用 INTEGER PRIMARY KEY | Yes |
必要字段加 NOT NULL | Yes |
唯一字段加 UNIQUE | Yes |
外键加 FOREIGN KEY ... | Yes |
默认值用 DEFAULT | Yes |
时间用 TEXT + datetime('now') | Yes |
| 创建索引加速查询 | Yes |
一句话总结：
SQLite 建表 = 表名 + 列名 + 类型 + 约束 + 索引，用
INTEGER PRIMARY KEY做自增 ID，配合IF NOT EXISTS安全建表。
需要我生成一个 完整的用户管理系统数据库脚本（含用户、角色、权限、登录日志）吗？可以一键运行！
