SQL书写规范与最佳实践

编写高质量的SQL代码不仅是技术问题，更是专业素养的体现。良好的SQL书写规范能够提升代码可读性、减少错误、提高团队协作效率。本文总结了一套全面的SQL书写规范与最佳实践，帮助开发者编写更专业、更规范的SQL代码。

一、关键字规范

1.1 关键字大写

所有SQL关键字应使用大写形式，包括：

• DML关键字：SELECT, INSERT, UPDATE, DELETE, MERGE
• DDL关键字：CREATE, ALTER, DROP, TRUNCATE
• 子句关键字：FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
• 逻辑运算符：AND, OR, NOT, IN, BETWEEN, LIKE, EXISTS
• 函数关键字：COUNT, SUM, AVG, MAX, MIN, COALESCE, CASE
• JOIN关键字：JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN

正确示例：

SELECT
    employee_id,
    first_name,
    last_name,
    department,
    salary
FROM employees
WHERE department IN ('Engineering', 'Marketing')
    AND salary > 50000
    AND hire_date >= '2020-01-01'
ORDER BY salary DESC, last_name ASC;

错误示例：

select employee_id, first_name, last_name, department, salary
from employees
where department in ('Engineering', 'Marketing')
and salary > 50000
and hire_date >= '2020-01-01'
order by salary desc, last_name asc;

1.2 函数名规范

SQL内置函数名同样应该大写：

SELECT
    COALESCE(nickname, first_name, '匿名用户') AS display_name,
    UPPER(country) AS country_code,
    DATE_FORMAT(created_at, '%Y-%m-%d') AS reg_date,
    COUNT(*) AS total_records
FROM users
WHERE created_at IS NOT NULL
GROUP BY display_name, country_code, reg_date;

二、缩进与换行规范

2.1 基本缩进规则

使用4个空格作为标准缩进单位（避免使用Tab字符）：

SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name,
    e.salary,
    e.hire_date
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id
WHERE e.status = 'active'
    AND e.hire_date >= '2020-01-01'
    AND e.salary BETWEEN 30000 AND 150000
ORDER BY
    d.department_name ASC,
    e.salary DESC;

2.2 SELECT字段换行

每个查询字段独占一行，逗号放在字段名之后：

-- 推荐：每行一个字段
SELECT
    employee_id,
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    job_id,
    salary,
    department_id
FROM employees;

-- 不推荐：多个字段挤在同一行
SELECT employee_id, first_name, last_name, email,
    phone_number, hire_date, job_id, salary, department_id
FROM employees;

2.3 WHERE条件换行

每个WHERE条件独占一行，逻辑运算符置于行首：

SELECT *
FROM orders
WHERE status = 'pending'
    AND customer_id IS NOT NULL
    AND total_amount > 100
    AND order_date >= '2024-01-01'
    AND (region = '华东' OR region = '华南')
    AND EXISTS (
        SELECT 1
        FROM order_items oi
        WHERE oi.order_id = orders.order_id
    );

2.4 CASE语句格式化

CASE语句应有清晰的缩进结构：

SELECT
    employee_id,
    first_name,
    salary,
    CASE
        WHEN salary >= 100000 THEN '高薪'
        WHEN salary >= 50000  THEN '中薪'
        WHEN salary >= 30000  THEN '标准'
        ELSE '低薪'
    END AS salary_level,
    CASE department_id
        WHEN 1 THEN '研发部'
        WHEN 2 THEN '市场部'
        WHEN 3 THEN '销售部'
        WHEN 4 THEN '人事部'
        ELSE '其他部门'
    END AS department_name
FROM employees;

三、别名使用规范

3.1 表别名

• 使用有意义的缩写作为表别名
• 避免使用无意义的单字母别名（a, b, c）
• 多表查询时必须使用表别名

-- 推荐：有意义的别名
SELECT
    emp.employee_id,
    emp.first_name,
    dept.department_name,
    addr.city,
    addr.country
FROM employees emp
INNER JOIN departments dept
    ON emp.department_id = dept.department_id
LEFT JOIN addresses addr
    ON emp.address_id = addr.address_id;

-- 不推荐：无意义的别名
SELECT
    a.employee_id,
    a.first_name,
    b.department_name,
    c.city,
    c.country
FROM employees a
INNER JOIN departments b
    ON a.department_id = b.department_id
LEFT JOIN addresses c
    ON a.address_id = c.address_id;

3.2 字段别名

对计算字段和表达式使用AS关键字指定别名：

SELECT
    e.first_name || ' ' || e.last_name AS full_name,
    e.salary * 12 AS annual_salary,
    e.salary * 12 * (1 + e.bonus_rate) AS total_compensation,
    DATEDIFF(CURRENT_DATE, e.hire_date) / 365 AS years_of_service,
    CASE
        WHEN DATEDIFF(CURRENT_DATE, e.hire_date) / 365 >= 10 THEN '资深员工'
        WHEN DATEDIFF(CURRENT_DATE, e.hire_date) / 365 >= 5  THEN '骨干员工'
        ELSE '普通员工'
    END AS employee_tier
FROM employees e;

3.3 别名命名规则汇总

场景	推荐做法	避免做法
表别名	使用表名缩写（emp, dept）	使用a, b, c
字段别名	使用有意义的名称	使用col1, col2
子查询别名	描述查询内容（active_orders）	使用sub1, t1
聚合别名	描述统计含义（total_revenue）	使用cnt, sum
连接别名	保持与表名一致	随意缩写

四、JOIN格式化规范

4.1 标准JOIN格式

SELECT
    c.customer_name,
    o.order_id,
    o.order_date,
    oi.product_name,
    oi.quantity,
    oi.unit_price
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id
INNER JOIN order_items oi
    ON o.order_id = oi.order_id
LEFT JOIN products p
    ON oi.product_id = p.product_id
    AND p.is_active = 1
WHERE o.order_date >= '2024-01-01'
ORDER BY
    c.customer_name,
    o.order_date DESC;

4.2 多条件JOIN

当JOIN包含多个关联条件时，每个条件独占一行：

SELECT
    e.employee_id,
    e.first_name,
    p.project_name,
    pa.role,
    pa.assigned_date
FROM employees e
INNER JOIN project_assignments pa
    ON e.employee_id = pa.employee_id
    AND pa.is_active = 1
    AND pa.assigned_date >= '2024-01-01'
INNER JOIN projects p
    ON pa.project_id = p.project_id
    AND p.status = 'in_progress'
LEFT JOIN departments d
    ON e.department_id = d.department_id;

4.3 子查询中的JOIN

SELECT
    d.department_name,
    dept_stats.avg_salary,
    dept_stats.employee_count
FROM departments d
INNER JOIN (
    SELECT
        department_id,
        AVG(salary) AS avg_salary,
        COUNT(*) AS employee_count
    FROM employees
    WHERE status = 'active'
    GROUP BY department_id
    HAVING COUNT(*) > 5
) dept_stats
    ON d.department_id = dept_stats.department_id
ORDER BY dept_stats.avg_salary DESC;

五、INSERT语句规范

5.1 标准INSERT格式

INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    job_id,
    salary,
    department_id
)
VALUES (
    1001,
    '张三',
    '张',
    'zhangsan@example.com',
    '13800138000',
    '2024-03-15',
    'DEV_ENGINEER',
    25000.00,
    1
);

5.2 INSERT SELECT格式

INSERT INTO employee_archive (
    employee_id,
    first_name,
    last_name,
    email,
    department_id,
    archive_date
)
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.email,
    e.department_id,
    CURRENT_DATE
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id
WHERE e.status = 'inactive'
    AND e.termination_date < DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR);

六、UPDATE和DELETE规范

6.1 UPDATE语句格式

UPDATE employees
SET
    salary = salary * 1.10,
    last_review_date = CURRENT_DATE,
    review_notes = '年度调薪10%'
WHERE department_id = 1
    AND status = 'active'
    AND hire_date < DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
    AND performance_rating >= 4;

6.2 DELETE语句格式

DELETE FROM order_items
WHERE order_id IN (
    SELECT o.order_id
    FROM orders o
    WHERE o.status = 'cancelled'
        AND o.cancel_date < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
);

七、存储过程与函数规范

7.1 存储过程格式

CREATE PROCEDURE sp_update_employee_salary(
    IN p_employee_id INT,
    IN p_new_salary DECIMAL(10, 2),
    IN p_reason VARCHAR(255)
)
BEGIN
    DECLARE v_old_salary DECIMAL(10, 2);
    DECLARE v_employee_name VARCHAR(100);

    -- 获取当前薪资
    SELECT salary, CONCAT(first_name, ' ', last_name)
    INTO v_old_salary, v_employee_name
    FROM employees
    WHERE employee_id = p_employee_id;

    -- 验证输入
    IF p_new_salary <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '薪资必须大于0';
    END IF;

    -- 更新薪资
    UPDATE employees
    SET
        salary = p_new_salary,
        updated_at = CURRENT_TIMESTAMP
    WHERE employee_id = p_employee_id;

    -- 记录变更日志
    INSERT INTO salary_change_log (
        employee_id,
        old_salary,
        new_salary,
        change_reason,
        changed_at
    )
    VALUES (
        p_employee_id,
        v_old_salary,
        p_new_salary,
        p_reason,
        CURRENT_TIMESTAMP
    );
END;

八、注释规范

8.1 注释类型与使用场景

-- ============================================
-- 文件说明：员工月度报表查询
-- 作者：开发团队
-- 创建日期：2024-01-15
-- 修改记录：
--   2024-03-01 添加部门筛选条件
--   2024-06-15 优化JOIN性能
-- ============================================

-- 查询各部门月度薪资统计
SELECT
    d.department_name,
    COUNT(e.employee_id) AS headcount,
    SUM(e.salary) AS total_salary,
    AVG(e.salary) AS avg_salary,
    MAX(e.salary) AS max_salary,
    MIN(e.salary) AS min_salary
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id
WHERE e.status = 'active'          -- 只统计在职员工
    AND e.hire_date <= '2024-06-30' -- 统计截止日之前的员工
GROUP BY d.department_name
HAVING COUNT(e.employee_id) > 0
ORDER BY total_salary DESC;

8.2 注释的最佳实践

注释类型	使用场景	示例
文件头注释	说明SQL脚本的整体用途	包含作者、日期、修改记录
段落注释	分隔不同逻辑段落	用--分隔SELECT/WHERE/JOIN逻辑块
行尾注释	解释特定字段的含义	salary * 1.1 AS adjusted -- 含10%调薪
TODO注释	标记待优化项	-- TODO: 添加分区支持
注意注释	标记需要注意的逻辑	-- WARNING: 此条件影响全表扫描

九、完整实战示例

以下是一个综合了所有书写规范的完整SQL查询：

-- ============================================
-- 销售分析报表：2024年度客户消费排行
-- 功能：统计年度消费TOP100客户及其明细
-- ============================================

SELECT
    c.customer_id,
    c.customer_name,
    c.customer_level,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    AVG(o.total_amount) AS avg_order_amount,
    MAX(o.order_date) AS last_order_date,
    DATEDIFF(CURRENT_DATE, MAX(o.order_date)) AS days_since_last_order
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id
INNER JOIN order_items oi
    ON o.order_id = oi.order_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
    AND o.status IN ('completed', 'shipped')
    AND c.is_active = 1
GROUP BY
    c.customer_id,
    c.customer_name,
    c.customer_level
HAVING COUNT(DISTINCT o.order_id) >= 3
ORDER BY
    total_spent DESC,
    order_count DESC
LIMIT 100;

十、总结

良好的SQL书写规范不仅能提升代码质量，还能促进团队协作、降低维护成本。记住以下核心原则：

1. 关键字大写：所有SQL关键字统一使用大写
2. 合理缩进：使用4空格缩进，保持层次清晰
3. 每行一个元素：字段、条件、分组项各占一行
4. 有意义的别名：使用能描述含义的别名
5. 规范注释：为复杂逻辑添加清晰的注释
6. 一致性：在整个项目中保持风格统一

遵循这些规范，你的SQL代码将更加专业、易读、易维护。