【SQL编程规范】提升SQL代码质量：打造高效、可维护的数据库应用程序（最佳实践）

SQL编程规范

1. 引言

1. SQL的重要性

• SQL作为访问和操作关系型数据库的标准语言，是现代软件开发不可或缺的一部分。
• 数据库是几乎所有企业级应用的核心，而SQL是与之交互的主要方式。
• 了解如何编写高效、可维护的SQL代码对于开发者来说至关重要。

2. 为什么要制定SQL编程规范

• SQL编程规范帮助团队达成共识，确保代码的一致性和可读性。
• 规范化的SQL代码更容易维护，减少错误发生的可能性。
• 当团队成员需要阅读或修改他人的代码时，规范化的代码能显著提高效率。

3. 规范的目标

• 本指南旨在提供一套实用的SQL编程规范，帮助开发者编写高质量的SQL代码。
• 我们将涵盖SQL语法的最佳实践、命名约定、以及一些应避免的常见错误。

---

2. 一般原则

1. 使用一致的、描述性的名称

• 一个好的命名应该简洁但又足够描述性，以便于其他开发者理解其含义。
• 避免使用缩写，除非它们非常普遍且易于理解。

2. 合理使用空格和缩进来增强可读性

• 空格和缩进有助于区分SQL关键字和操作符，使代码更加清晰。
• 示例：

SELECT first_name, last_name
  FROM staff
 WHERE department = 'Engineering';

3. 存储符合ISO-8601标准的日期格式

• 使用ISO-8601格式的日期可以确保数据的便携性和一致性。
• 示例：

INSERT INTO log (entry_date)
VALUES ('2024-08-19T15:57:00.00000');

4. 使用标准SQL函数而非特定供应商的函数

• 标准SQL函数使得代码更加便携，不受特定数据库系统的限制。
• 示例：

SELECT COUNT(*) AS total_staff
  FROM staff;

5. 保持代码简洁明了

• 避免不必要的引号或括号，简化WHERE子句。
• 示例：

SELECT file_hash
  FROM file_system
 WHERE file_name = '.vimrc';

6. 注释的重要性

• 注释可以帮助解释代码的目的和功能，特别是对于复杂的查询或存储过程。
• 示例：

/* Updating the file record after writing to the file */
UPDATE file_system
   SET file_modified_date = '1980-02-22 13:19:01.00000',
       file_size = 209732
 WHERE file_name = '.vimrc';

---

3. 应避免的事情

1. 驼峰命名法

• 驼峰命名法虽然在编程语言中很常见，但在SQL中并不适用。
• 示例：

-- 不好的命名
SELECT FirstName, LastName
  FROM Staff;

2. 描述性前缀或匈牙利命名法

• 这类命名法增加了不必要的复杂性，使得代码难以阅读。
• 示例：

-- 不好的命名
SELECT sp_FirstName, sp_LastName
  FROM tbl_Staff;

3. 复数形式

• 使用更自然的集合术语而非复数形式。
• 示例：

-- 不好的命名
SELECT * FROM Employees;
-- 好的命名
SELECT * FROM staff;

4. 被引号包裹的标识符

• 在大多数情况下，避免使用引号包裹标识符。
• 示例：

-- 不好的命名
SELECT "FirstName"
  FROM "Staff";

5. 面向对象编程原则的应用

• SQL和关系型数据库有自己的最佳实践，不应盲目套用面向对象的概念。
• 示例：

-- 不好的设计
CREATE TABLE Staff (
  Id INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Address VARCHAR(200),
  PhoneNumber VARCHAR(20)
);
-- 好的设计
CREATE TABLE people (
  person_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);
CREATE TABLE addresses (
  address_id INT PRIMARY KEY,
  person_id INT,
  street_address VARCHAR(200),
  phone_number VARCHAR(20),
  FOREIGN KEY (person_id) REFERENCES people(person_id)
);

---

4. 命名惯例

4.1 一般原则

1. 保证名字独一无二且不是保留字

• 例如，避免使用 select、from、where 等作为表名或列名。
• 示例代码:

-- 错误的命名
CREATE TABLE select (col1 INT);

-- 正确的命名
CREATE TABLE selection (col1 INT);

2. 保证名字长度不超过 30 个字节

• 如果不使用多字节字符集，则长度限制为 30 个字符。
• 示例代码:

-- 过长的名字
CREATE TABLE this_is_an_extremely_long_table_name (col1 INT);

-- 适当的长度
CREATE TABLE short_table_name (col1 INT);

3. 名字要以字母开头，不能以下划线结尾

• 示例代码:

-- 不好的命名
CREATE TABLE _table (col1 INT);

-- 好的命名
CREATE TABLE table_ (col1 INT);

4. 只在名字中使用字母、数字和下划线

• 示例代码:

-- 不好的命名
CREATE TABLE table-name (col1 INT);

-- 好的命名
CREATE TABLE table_name (col1 INT);

5. 不要在名字中出现连续下划线

• 示例代码:

-- 不好的命名
CREATE TABLE table__name (col1 INT);

-- 好的命名
CREATE TABLE table_name (col1 INT);

6. 在名字中需要空格的地方用下划线代替

• 示例代码:

-- 不好的命名
CREATE TABLE first name (col1 INT);

-- 好的命名
CREATE TABLE first_name (col1 INT);

7. 尽量避免使用缩写词

• 示例代码:

-- 不好的命名
CREATE TABLE dept (col1 INT);

-- 好的命名
CREATE TABLE department (col1 INT);

8. 使用时确定这个缩写简明易懂

• 示例代码:

-- 好的命名
CREATE TABLE dept (col1 INT);
-- 注意: "dept" 是一个广泛接受的缩写，因此在这种情况下是可以接受的。

4.2 表名

1. 使用集合名称

• 示例代码:

-- 好的命名
CREATE TABLE staff (col1 INT);

-- 不好的命名
CREATE TABLE employees (col1 INT);

2. 使用复数形式

• 示例代码:

-- 好的命名
CREATE TABLE staff (col1 INT);

-- 不好的命名
CREATE TABLE staff_member (col1 INT);

3. 不要使用描述性的前缀

• 示例代码:

-- 不好的命名
CREATE TABLE tbl_staff (col1 INT);

-- 好的命名
CREATE TABLE staff (col1 INT);

4. 表不应该同它的列同名

• 示例代码:

-- 不好的命名
CREATE TABLE staff (staff INT);

-- 好的命名
CREATE TABLE staff (staff_id INT);

5. 尽量避免连接两个表的名字作为关系表的名字

• 示例代码:

-- 不好的命名
CREATE TABLE cars_mechanics (car_id INT, mechanic_id INT);

-- 好的命名
CREATE TABLE services (car_id INT, mechanic_id INT);

4.3 列名

1. 总是使用单数形式

• 示例代码:

-- 好的命名
CREATE TABLE staff (first_name VARCHAR(50));

-- 不好的命名
CREATE TABLE staff (first_names VARCHAR(50));

2. 避免直接使用 id 做表的主标识符

• 示例代码:

-- 不好的命名
CREATE TABLE staff (id INT PRIMARY KEY);

-- 好的命名
CREATE TABLE staff (staff_id INT PRIMARY KEY);

3. 避免列名和表名同名

• 示例代码:

-- 不好的命名
CREATE TABLE staff (staff VARCHAR(50));

-- 好的命名
CREATE TABLE staff (staff_name VARCHAR(50));

4. 总是使用小写字母

• 示例代码:

-- 好的命名
CREATE TABLE staff (first_name VARCHAR(50));

-- 不好的命名
CREATE TABLE staff (FirstName VARCHAR(50));

4.4 别名与关联名

1. 别名应该与它们所指的对象或表达式相关联

• 示例代码:

SELECT first_name AS fn
  FROM staff AS s1
  JOIN students AS s2
    ON s2.mentor_id = s1.staff_num;

2. 关联名应该由对象名中每一个单词的首字母组成

• 示例代码:

SELECT first_name AS fn
  FROM staff AS s
  JOIN students AS st
    ON st.mentor_id = s.staff_num;

3. 如果已经有相同的关联名了，那么在关联名后加一个数字

• 示例代码:

SELECT first_name AS fn
  FROM staff AS s1
  JOIN students AS s2
    ON s2.mentor_id = s1.staff_num;

• 4. 总是加上 AS 关键字
  • 示例代码:

SELECT first_name AS fn
  FROM staff AS s;

5. 为计算出的数据（SUM() 或 AVG()）命名时，用一个将这条数据存在表中时会使用的列名

• 示例代码:

SELECT SUM(s.monitor_tally) AS monitor_total
  FROM staff AS s;

4.5 存储过程名

1. 名字一定要包含动词

• 示例代码:

-- 好的命名
CREATE PROCEDURE update_staff_salary (IN staff_id INT, IN new_salary DECIMAL(10,2));

-- 不好的命名
CREATE PROCEDURE sp_update_staff_salary (IN staff_id INT, IN new_salary DECIMAL(10,2));

2. 不要附加 sp_ 或任何其他描述性的前缀或使用匈牙利表示法

• 示例代码:

-- 不好的命名
CREATE PROCEDURE sp_update_staff_salary (IN staff_id INT, IN new_salary DECIMAL(10,2));

-- 好的命名
CREATE PROCEDURE update_staff_salary (IN staff_id INT, IN new_salary DECIMAL(10,2));

5. 统一的后缀

• _id —— 独一无二的标识符，如主键。
• _status —— 标志值或任何表示状态的值，比如 publication_status。
• _total —— 总和或某些值的和。
• _num —— 表示该字段包含数值。
• _name —— 表示名字，例如 first_name。
• _seq —— 包含一系列值。
• _date —— 表示该列包含日期。
• _tally —— 计数值。
• _size —— 大小，如文件大小或服装大小。
• _addr —— 地址，有形的或无形的，如 ip_addr

示例代码:

-- 使用统一的后缀
CREATE TABLE staff (
    staff_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10,2),
    position_status VARCHAR(10)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_total DECIMAL(10,2),
    order_date DATE
);

CREATE TABLE payments (
    payment_id INT PRIMARY KEY,
    amount_paid DECIMAL(10,2),
    payment_date DATE,
    payment_method VARCHAR(50)
);

---

6. 查询语句

6.1 保留字

1. 使用标准 SQL 保留字

• 示例代码:

SELECT * FROM staff WHERE first_name = 'John';

2. 保留字应全部小写

• 示例代码:

SELECT * FROM staff WHERE first_name = 'John';
-- 而非
SELECT * FROM STAFF WHERE FIRST_NAME = 'John';

6.2 空白字符

1. 在操作符前后使用空白字符

• 示例代码:

SELECT * FROM staff WHERE first_name = 'John';
-- 而非
SELECT * FROM staff WHERE first_name='John';

2. 在逗号后使用空白字符

• 示例代码:

SELECT first_name, last_name FROM staff;
-- 而非
SELECT first_name,last_name FROM staff;

3. 在函数参数之间使用空白字符

• 示例代码:

SELECT COUNT(*) FROM staff;
-- 而非
SELECT COUNT(*)FROM staff;

6.3 换行

** 每个子句放在新的一行**

• 示例代码:

SELECT first_name, last_name
FROM staff
WHERE first_name = 'John'
AND last_name = 'Doe';

6.4 缩进

使用缩进来组织复杂的查询

• 示例代码:

SELECT first_name, last_name
FROM staff
  WHERE first_name = 'John'
    AND last_name = 'Doe';

6.5 Join语句

每个 join 子句放在新的一行，并使用缩进

• 示例代码:

SELECT s.first_name, s.last_name, d.department_name
FROM staff s
INNER JOIN departments d
  ON s.department_id = d.department_id;

6.6 子查询

子查询放在括号内并单独一行

• 示例代码:

SELECT first_name, last_name
FROM staff
WHERE department_id IN (
  SELECT department_id
  FROM departments
  WHERE department_name = 'Sales'
);

6.7 推荐的形式

• 清晰的缩进和换行
  • 示例代码:

SELECT s.first_name, s.last_name, d.department_name
FROM staff s
INNER JOIN departments d
  ON s.department_id = d.department_id
WHERE s.first_name = 'John'
  AND d.department_name = 'Sales';

7. 创建语句

7.1 选择数据类型

1. 使用合适的数据类型

• 示例代码:

CREATE TABLE staff (
  staff_id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  hire_date DATE,
  salary DECIMAL(10,2),
  department_id INT
);

2. 避免使用通用类型

• 示例代码:

CREATE TABLE staff (
  staff_id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50),  -- 不使用 CHAR 或 VARCHAR
  last_name VARCHAR(50),
  hire_date DATE,          -- 不使用 DATETIME
  salary DECIMAL(10,2),    -- 不使用 FLOAT
  department_id INT
);

7.2 指定默认类型

使用 DEFAULT 关键字指定默认值

• 示例代码:

CREATE TABLE staff (
  staff_id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  hire_date DATE DEFAULT CURRENT_DATE,
  salary DECIMAL(10,2) DEFAULT 0.00,
  department_id INT DEFAULT 1
);

7.3 约束和键

1. 定义主键

• 示例代码:

  CREATE TABLE staff (
    staff_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
  );
  

2. 定义外键

• 示例代码:

  CREATE TABLE staff (
    staff_id INT AUTO_INCREMENT PRIMARY KEY,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
  );
  

3. 定义唯一约束

• 示例代码:

  CREATE TABLE staff (
    staff_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
  );
  

4. 定义检查约束

• 示例代码:

  CREATE TABLE staff (
    staff_id INT AUTO_INCREMENT PRIMARY KEY,
    age INT CHECK (age >= 18)
  );
  

8. 应该避免的设计

8.1 示例代码

1. 避免冗余数据

• 示例代码:

-- 不好的设计: 存储重复的信息
CREATE TABLE staff (
 staff_id INT AUTO_INCREMENT PRIMARY KEY,
 first_name VARCHAR(50),
 last_name VARCHAR(50),
 full_name VARCHAR(100) -- 冗余信息
);

更好的设计:

CREATE TABLE staff (
 staff_id INT AUTO_INCREMENT PRIMARY KEY,
 first_name VARCHAR(50),
 last_name VARCHAR(50)
);

2. 避免使用过时的数据类型

• 示例代码:

-- 不好的设计: 使用过时的类型
CREATE TABLE staff (
  staff_id TINYINT AUTO_INCREMENT PRIMARY KEY,
  first_name CHAR(50),
  last_name CHAR(50),
  hire_date DATETIME
);

更好的设计:

CREATE TABLE staff (
  staff_id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  hire_date DATE
);

3. 避免使用过于宽泛的类型

• 示例代码:

-- 不好的设计: 使用过于宽泛的类型
CREATE TABLE staff (
  staff_id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  hire_date DATETIME
);

更好的设计:

CREATE TABLE staff (
  staff_id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  hire_date DATE
);

4. 避免使用不合适的默认值

• 示例代码:

-- 不好的设计: 使用不合适的默认值
CREATE TABLE staff (
  staff_id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50) DEFAULT '',
  last_name VARCHAR(50) DEFAULT ''
);

更好的设计:

CREATE TABLE staff (
  staff_id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL
);

5. 避免在查询中使用模糊的连接

• 示例代码:

-- 不好的设计: 使用模糊的连接
SELECT *
FROM staff, departments
WHERE staff.department_id = departments.department_id;

更好的设计:

SELECT *
FROM staff
INNER JOIN departments
  ON staff.department_id = departments.department_id;

9. 列的数据类型

9.1 示例代码

• VARCHAR - 可变长度字符串

CREATE TABLE staff (
  first_name VARCHAR(50)
);

• INT - 整数

CREATE TABLE staff (
  staff_id INT
);

• DATE - 日期

CREATE TABLE staff (
  hire_date DATE
);

• DECIMAL - 固定点数

CREATE TABLE staff (
  salary DECIMAL(10, 2)
);

• BOOLEAN - 布尔值

CREATE TABLE staff (
  is_active BOOLEAN
);

• TEXT - 较大的文本块

CREATE TABLE staff (
  bio TEXT
);

• TIMESTAMP - 时间戳

CREATE TABLE staff (
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• BLOB - 二进制大对象

CREATE TABLE staff (
  profile_picture BLOB
);

• ENUM - 枚举类型

CREATE TABLE staff (
  gender ENUM('Male', 'Female', 'Other')
);

• SET - 集合类型

CREATE TABLE staff (
  skills SET('Programming', 'Design', 'Management')
);

10. 结论

1. 重申SQL编程规范的重要性

• SQL 规范化和标准化对于维护数据库的完整性、提高查询效率以及简化代码管理至关重要。
• 一致的命名约定、合理的数据类型选择、恰当的约束使用等都是良好数据库设计的基础。

2. 提醒持续改进的重要性

• 数据库设计是一个不断迭代的过程，随着业务需求的变化和技术的进步，原有的设计可能需要调整。
• 定期回顾和更新 SQL 编码规范，以确保其符合最新的最佳实践和业务需求。