SQLite SQL编写规范

规范概述

SQL编写规范是确保数据库开发质量、提高代码可读性、维护性和性能的重要保障。统一的SQL编写规范有助于团队协作，减少错误，提高开发效率。本规范适用于所有使用SQLite进行数据库开发的场景。

语法规范

1. 关键字命名规范

关键字大写：SQL关键字（如SELECT、INSERT、UPDATE、DELETE、FROM、WHERE等）必须使用大写
函数名大写：SQL函数名（如COUNT、SUM、AVG、MAX、MIN等）必须使用大写
常量值：字符串常量使用单引号，数字常量直接书写
NULL值：NULL值必须大写

示例：

sql

-- 推荐
SELECT user_id, username, email FROM users WHERE status = 'active' AND created_at > '2023-01-01';

-- 不推荐
select user_id, username, email from users where status = 'active' and created_at > '2023-01-01';

2. 缩进与换行规范

缩进：使用4个空格进行缩进，不使用制表符
换行：每个关键字（如SELECT、FROM、WHERE、JOIN等）独占一行
逗号位置：逗号应放在字段名或表名的后面，紧跟在名称之后
条件换行：复杂条件语句应在AND/OR关键字前换行

示例：

sql

-- 推荐
SELECT 
    u.user_id,
    u.username,
    u.email,
    o.order_id,
    o.order_date,
    o.total_amount
FROM 
    users u
INNER JOIN 
    orders o ON u.user_id = o.user_id
WHERE 
    u.status = 'active'
    AND o.order_date > '2023-01-01'
    AND o.total_amount > 100
ORDER BY 
    o.order_date DESC;

-- 不推荐
SELECT u.user_id, u.username, u.email, o.order_id, o.order_date, o.total_amount FROM users u INNER JOIN orders o ON u.user_id = o.user_id WHERE u.status = 'active' AND o.order_date > '2023-01-01' AND o.total_amount > 100 ORDER BY o.order_date DESC;

3. 注释规范

单行注释：使用--进行单行注释，注释内容与--之间有一个空格
多行注释：使用/* */进行多行注释，适用于复杂逻辑的说明
注释位置：注释应放在被注释代码的上方或右侧
注释内容：注释应清晰、简洁，说明代码的功能和意图

示例：

sql

-- 查询活跃用户的订单信息
SELECT 
    u.user_id,
    u.username, -- 用户名称
    o.order_id,
    o.total_amount
FROM 
    users u
INNER JOIN 
    orders o ON u.user_id = o.user_id
WHERE 
    u.status = 'active';

/*
 * 计算用户订单统计信息
 * 包括：用户ID、用户名、订单总数、订单总金额、平均订单金额
 */
SELECT 
    user_id,
    username,
    COUNT(order_id) AS order_count,
    SUM(total_amount) AS total_amount,
    AVG(total_amount) AS avg_order_amount
FROM 
    user_orders
GROUP BY 
    user_id, username;

命名规范

1. 表名命名规范

命名格式：使用小写字母，单词之间用下划线分隔（snake_case）
命名规则：表名应简洁明了，反映表的内容或用途
避免使用保留字：不使用SQLite保留字作为表名
复数形式：表名通常使用复数形式（如users、orders等）

示例：

sql

-- 推荐
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    status TEXT DEFAULT 'inactive',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 不推荐
CREATE TABLE User (
    UserId INTEGER PRIMARY KEY AUTOINCREMENT,
    UserName TEXT NOT NULL UNIQUE,
    Email TEXT NOT NULL UNIQUE,
    PasswordHash TEXT NOT NULL,
    Status TEXT DEFAULT 'inactive',
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
);

2. 字段名命名规范

命名格式：使用小写字母，单词之间用下划线分隔（snake_case）
命名规则：字段名应简洁明了，反映字段的内容或用途
主键命名：主键通常命名为表名_id（如user_id、order_id等）
外键命名：外键通常命名为关联表名_id（如user_id、product_id等）
时间字段：创建时间通常命名为created_at，更新时间通常命名为updated_at

示例：

sql

-- 推荐
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    order_number TEXT NOT NULL UNIQUE,
    total_amount REAL NOT NULL,
    status TEXT DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 不推荐
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    userId INTEGER NOT NULL,
    orderNo TEXT NOT NULL UNIQUE,
    total REAL NOT NULL,
    stat TEXT DEFAULT 'pending',
    createTime DATETIME DEFAULT CURRENT_TIMESTAMP,
    updateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (userId) REFERENCES users(id)
);

3. 索引命名规范

命名格式：使用小写字母，单词之间用下划线分隔（snake_case）
命名规则：idx_表名_字段名（单字段索引）或idx_表名_字段1_字段2（多字段索引）
唯一索引：唯一索引命名为idx_表名_字段名_unique
前缀索引：前缀索引命名为idx_表名_字段名_prefix

示例：

sql

-- 推荐
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_username_unique ON users(username);
CREATE INDEX idx_orders_user_id_created_at ON orders(user_id, created_at);

-- 不推荐
CREATE INDEX email_idx ON users(email);
CREATE UNIQUE INDEX username_unique ON users(username);
CREATE INDEX user_created_idx ON orders(user_id, created_at);

4. 视图命名规范

命名格式：使用小写字母，单词之间用下划线分隔（snake_case）
命名规则：v_视图名或view_视图名
视图名：视图名应反映视图的内容或用途

示例：

sql

-- 推荐
CREATE VIEW v_active_users AS
SELECT user_id, username, email, created_at
FROM users
WHERE status = 'active';

-- 不推荐
CREATE VIEW ActiveUsers AS
SELECT user_id, username, email, created_at
FROM users
WHERE status = 'active';

性能规范

1. 查询性能规范

**避免SELECT ***：只查询需要的字段，不使用SELECT *
使用索引：在WHERE、JOIN、ORDER BY和GROUP BY字段上创建适当的索引
避免在WHERE子句中使用函数：这会导致索引失效
使用LIMIT限制结果集：对大表查询使用LIMIT限制返回记录数
避免在循环中执行SQL：批量处理数据，减少SQL执行次数

示例：

sql

-- 推荐
SELECT user_id, username, email FROM users WHERE status = 'active' LIMIT 100;

-- 不推荐
SELECT * FROM users WHERE DATE(created_at) = '2023-01-01';

2. 插入性能规范

批量插入：使用事务批量插入数据，减少磁盘I/O
避免频繁提交事务：事务提交会触发磁盘写入，频繁提交会影响性能
使用参数化查询：减少SQL解析开销，提高安全性

示例：

sql

-- 推荐：批量插入
BEGIN TRANSACTION;
INSERT INTO users (username, email, password_hash) VALUES ('user1', 'user1@example.com', 'hash1');
INSERT INTO users (username, email, password_hash) VALUES ('user2', 'user2@example.com', 'hash2');
INSERT INTO users (username, email, password_hash) VALUES ('user3', 'user3@example.com', 'hash3');
COMMIT;

-- 不推荐：频繁提交
INSERT INTO users (username, email, password_hash) VALUES ('user1', 'user1@example.com', 'hash1');
INSERT INTO users (username, email, password_hash) VALUES ('user2', 'user2@example.com', 'hash2');
INSERT INTO users (username, email, password_hash) VALUES ('user3', 'user3@example.com', 'hash3');

3. 更新和删除性能规范

使用索引：在WHERE子句字段上创建索引，提高更新和删除速度
避免全表更新/删除：始终指定WHERE条件，避免全表操作
批量更新/删除：使用事务批量处理，减少磁盘I/O

示例：

sql

-- 推荐
BEGIN TRANSACTION;
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';
DELETE FROM orders WHERE order_date < '2022-01-01';
COMMIT;

-- 不推荐
UPDATE users SET status = 'inactive'; -- 全表更新
DELETE FROM orders; -- 全表删除

安全规范

1. 防止SQL注入

使用参数化查询：始终使用参数化查询，不拼接SQL语句
验证用户输入：对用户输入进行验证和过滤，确保符合预期格式
使用预处理语句：使用SQLite预处理语句，提高安全性和性能

示例：

sql

-- 推荐：参数化查询
SELECT * FROM users WHERE username = ? AND password_hash = ?;

-- 不推荐：SQL拼接
SELECT * FROM users WHERE username = '" + username + "' AND password_hash = '" + password + "';

2. 数据安全

加密敏感数据：对敏感数据（如密码、银行卡号等）进行加密存储
限制数据访问：根据最小权限原则，限制用户对数据的访问权限
定期备份数据：定期备份数据库，确保数据安全
使用事务：对关键操作使用事务，确保数据一致性

版本差异

SQLite 3.8.0 及以上

支持窗口函数
支持Common Table Expressions (CTE)
支持UPSERT语法

示例：

sql

-- 窗口函数（SQLite 3.8.0+）
SELECT 
    user_id,
    username,
    order_date,
    total_amount,
    SUM(total_amount) OVER (PARTITION BY user_id ORDER BY order_date) AS cumulative_amount
FROM 
    user_orders;

-- UPSERT（SQLite 3.24.0+）
INSERT INTO users (username, email, password_hash)
VALUES ('user1', 'user1@example.com', 'hash1')
ON CONFLICT (username) DO UPDATE SET
    email = excluded.email,
    password_hash = excluded.password_hash,
    updated_at = CURRENT_TIMESTAMP;

SQLite 3.25.0 及以上

支持JSON函数
支持生成列（Generated Columns）

示例：

sql

-- JSON函数（SQLite 3.38.0+）
SELECT 
    user_id,
    username,
    JSON_EXTRACT(settings, '$.theme') AS theme,
    JSON_EXTRACT(settings, '$.language') AS language
FROM 
    users;

-- 生成列（SQLite 3.31.0+）
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    tax_rate REAL DEFAULT 0.1,
    total_price REAL GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);

常见问题（FAQ）

Q: 为什么要使用snake_case命名风格？

A: snake_case命名风格在SQL中更为常用，具有以下优点：

可读性好，单词之间用下划线分隔，易于区分
与SQL关键字大小写规则兼容
在不同操作系统和数据库系统中表现一致
符合多数数据库开发团队的习惯

Q: 为什么要避免使用SELECT *？

A: 避免使用SELECT *的原因包括：

增加网络传输开销，查询不需要的字段会浪费带宽
增加数据库负担，需要读取更多的数据
可能导致应用程序错误，如果表结构发生变化
无法利用覆盖索引（Covering Index）优化查询性能

Q: 如何选择合适的索引？

A: 选择合适的索引需要考虑以下因素：

查询的WHERE子句字段
JOIN操作的关联字段
ORDER BY和GROUP BY字段
数据的分布情况
查询的频率和重要性
索引的维护成本

Q: 什么情况下需要使用事务？

A: 在以下情况下需要使用事务：

执行多个相关操作，确保要么全部成功，要么全部失败
保证数据一致性和完整性
提高批量操作的性能
防止并发操作导致的数据不一致

Q: 如何防止SQL注入？

A: 防止SQL注入的主要方法包括：

使用参数化查询或预处理语句
对用户输入进行验证和过滤
限制数据库用户的权限
使用ORM框架，自动处理SQL注入防护
避免直接拼接SQL语句

最佳实践

始终使用参数化查询，防止SQL注入
只查询需要的字段，避免使用SELECT *
在WHERE、JOIN、ORDER BY字段上创建适当的索引
使用事务处理批量操作，提高性能和数据一致性
定期分析和优化查询，使用EXPLAIN分析执行计划
遵循命名规范，提高代码可读性和维护性
对敏感数据进行加密存储，确保数据安全
定期备份数据库，防止数据丢失
使用注释说明复杂逻辑，提高代码可维护性
遵循最小权限原则，限制用户对数据的访问权限

通过遵循以上SQL编写规范，可以提高数据库开发质量，减少错误，提高性能和安全性，便于团队协作和代码维护。

SQLite SQL编写规范 ​

规范概述 ​

语法规范 ​

1. 关键字命名规范 ​

2. 缩进与换行规范 ​

3. 注释规范 ​

命名规范 ​

1. 表名命名规范 ​

2. 字段名命名规范 ​

3. 索引命名规范 ​

4. 视图命名规范 ​

性能规范 ​

1. 查询性能规范 ​

2. 插入性能规范 ​

3. 更新和删除性能规范 ​

安全规范 ​

1. 防止SQL注入 ​

2. 数据安全 ​

版本差异 ​

SQLite 3.8.0 及以上 ​

SQLite 3.25.0 及以上 ​

常见问题（FAQ） ​

Q: 为什么要使用snake_case命名风格？ ​

Q: 为什么要避免使用SELECT *？ ​

Q: 如何选择合适的索引？ ​

Q: 什么情况下需要使用事务？ ​

Q: 如何防止SQL注入？ ​

最佳实践 ​

SQLite SQL编写规范

规范概述

语法规范

1. 关键字命名规范

2. 缩进与换行规范

3. 注释规范

命名规范

1. 表名命名规范

2. 字段名命名规范

3. 索引命名规范

4. 视图命名规范

性能规范

1. 查询性能规范

2. 插入性能规范

3. 更新和删除性能规范

安全规范

1. 防止SQL注入

2. 数据安全

版本差异

SQLite 3.8.0 及以上

SQLite 3.25.0 及以上

常见问题（FAQ）

Q: 为什么要使用snake_case命名风格？

Q: 为什么要避免使用SELECT *？

Q: 如何选择合适的索引？

Q: 什么情况下需要使用事务？

Q: 如何防止SQL注入？

最佳实践