SQL语句规范参考

140次阅读

共计 2628 个字符，预计需要花费 7 分钟才能阅读完成。

公司有 SQL 语句规范的参考，这里特别做个笔记。

书写风格

1. 语句关键字应全部使用小写。

2. 引用字符时应使用单引号。如：update testable set idcol=’abcd’。

3. 连接符或运算符 or、in、and、=、<=、>=，+，- 等前后宜加上一个空格。否则容易导致以下类似问题。例如在语句 select a–b from table 中，a，b 均为变量，拼写该语句时，如果 a = 6，b = -3，则语句变为 select 6–3 from table。– 被视为 SQL 的注释，结果语句报错。

4. 不得使用“select * from …”语法，必须标明字段名。即 select col1, col2,… from tablea where …

5. 严禁使用“insert into table_name values (?,?,……)”语法，统一使用“insert into table_name (col1,col2,……) values (?,?,……)”。

6. SQL 语句包含多表连接时，必须加上表的别名，对每个字段的使用都要带上表别名。即 select a.col1, a.col2, b.col3 from tablea a, tableb b where a.col4=b.col5

7. 应避免显式或隐含的类型转换。例如在 where 子句中 numeric 型和 int 型的列的比较。

8. 在子查询中前后必须加上括号。select col1, col2 from tablea where col3 in (select col4 from tableb where col4>0)

9. 执行 SQL 时一次应只执行一条，如果多条语句则应分开执行，但必须保持在一个事务中。不得一次执行通过分号等分开的多条语句，这样处理不清晰。

10. 如果能采用 or 代替，则不宜使用 in 语句。in 语句中的元素不得超过 500 个，如果超过，则应拆分为多条 SQL 语句。严禁使用 xx in(‘’,’’….) or xx in(‘’,’’,’’)。

11. or 连接条件不得超过 500，超过时应拆分为多条语句。

性能优化

1. 查询时应尽量减少多余数据的读取，通过使用 where 子句来减少返回的记录数。

2. 如果在语句中有 not in（in）操作，应尽量用 not exists（exists）来代替。特别对大数据量的两者检索速度有很明显的区别。

3. 不宜使用外连接。外连接效率低。

4. 一条 SQL 语句中不宜使用 3 层以上的嵌套查询。如果超过，则应在 Java 等应用服务器程序中处理。

5. 一条 SQL 语句中不得从 4 个及以上表中同时取数。仅作关联或过滤条件而不涉及取数的表不参与表个数计算；如果必须关联 4 个或 4 个以上表，应在 Java 等应用服务器程序中处理。

6. 应尽量避免使用 order by 和 group by 排序操作，如必须使用排序操作，尽量建立在有索引的列上。因为大量的排序操作影响系统性能。

7. 对索引列的比较，应尽量避免使用 not 或 !=，可拆分为几个条件。因为“not”和“!=”不会使用索引。如 col1 是索引列，条件 col1 !=0 可以拆分为 col1 >0 or col2 <0。

8. 应尽量将数据库函数、计算表达式写在逻辑操作符右边。因为这些对列的操作会将导致表扫描，影响性能。

9. 在 where 子句中，如果有多个过滤条件，应将索引列或过滤记录数最多的条件放在前面。

10. 能用连接方式实现的功能，不得用子查询。例如：select name from customer where customerId in (select customerId from order where money > 1000)。应该用如下语句代替：select name from customer inner join order on customer.customerId = order.customerId where order.money > 100。或 select name from customer where exists (select 1 from order where money > 1000 and customer.customerId = order.customerId) < 这里需要注意：使用 exists 的效率依赖于匹配度，inner join 效率比较稳定 >

11. 多表关联查询时，写法可遵循以下原则，这样做有利于建立索引，提高查询效率。格式如下：select sum (t1.je) from table1 t1, table2 t2, table3 t3 where (t1 的等值条件（=）) and (t1 的非等值条件) and (t2 与 t1 的关联条件) and (t2 的等值条件) and (t2 的非等值条件) and (t3 与 t2 的关联条件) and (t3 的等值条件) and (t3 的非等值条件)。

跨数据库支持

1. 对于跨数据库 Java 应用程序的 VO 映射数据库的数据格式建议：

1) 整型字段：字段设置保存为 Integer 或者 Long

2) 数字型字段：若需要使用小数 2 位以上的精确计算，读取、插入、更新使用 BigDecimal 类型

3) 字符型字段：读取为 String，并保存为 String，插入或者更新为 String

4) 时间字段：读取为 String，插入或者更新时的时间格式使用中间件统一处理。

2. 字符串连接应使用“||”符号，而不应使用“+”。“+”是 SQLServer 语法，Oracle 和 DB2 支持“||”，Hibernate 转化为 SQLServer 时，会自动将“||”转为“+”。

3. 通配符不能使用‘[a-c]%’这种形式。应写成如：select col1, col2 from table_name where col1 like‘[a]%’OR col1 like‘[b]%’OR col1 like‘[c]%’

4. 截取字符串长度函数应使用 substr，起始位置为 1 表示从头开始。因为 db2 中 substr 起点为 1，0 会报错；在 SqlServer 数据库中使用的是 substring 需要进行转换。

5. 不得通过 select percent n 和 select top n 限制查询结果集的记录数。

6. join 与 on 必须严格匹配，严禁出现没有 on 的 join。

7. join…on 后面不宜使用 or，如果使用则需将 or 的范围用 () 括起来。