1. 基本SQL编写规范

(1)规则1:SQL中直接使用表名,不使用schema作为前缀,跨schema访问需要创建synonym(数据仓库,CRM数据库不适用)。

(2)规则2:必须使用绑定变量, 避免常量的直接引用。

说明:频繁的硬解析会影响数据库性能。

(3)规则3:避免在where子句中对索引字段施加函数,这样将导致索引失效。

示例:

错误的写法:

select  employee_id, first_name  from  employee  where  to_char(hire_date,'yyyymmdd')='20120601';

正确的写法:

select  employee_id, first_name  from  employee  where  hire_date=to_char('20120601','yyyymmdd');

(4)规则4:Like不允许使用全模糊,只允许使用右模糊查询。

说明:全模糊查询无法使用INDEX,可能造成性能问题。

示例:

全模糊查询:

select  employee_id, first_name  from  employee   where  name  like  '%joe%';

右模糊查询:

select  employee_id, first_name  from  employee   where  name  like  '%joe%';

(5)规则5:如果使用Oracle数据库,使用Oracle的外连接,而不是标准的ANSI 99外连接语法。

说明:ANSI 99外连接语法是指inner join、left join、right join、full outer join,Oracle使用(+)来表示外连接。

示例:

错误的写法:

Select  employee_id, first_name   from  employee  a  left join  on  department b  a.department_id = b.department_id;

正确的写法:

Select  employee_id, first_name   from  employee  a , department b where  a.department_id = b.department_id(+);

(6)规则6:Insert语句必须写上字段名。

(7)规则7:严格要求使用正确类型的变量,杜绝隐式类型转换。

书写SQL时,必须确定表中各个字段的数据类型,防止发生隐式类型转换。

示例:

ID是NUMBER型

错误的写法:

Select  employee_id, first_name   from  employee  where  employee_id = '123';--这里会造成oracle将id先转换成varchar类型,导致索引失效。

正确的写法:

Select  employee_id, first_name   from  employee  where  employee_id = 123;

反之亦然,字符型数据一定加上单引号。

时间类型的字段,必须使用TO_DATE进行赋值(当前时间可直接用sysdate表示)

示例:

错误的写法(使用date类型的变量):

select agtphone, name
from log_account_info
where accountno = #accountnot :varchar# and optime >= #dateBegin :date#

正确的写法:

select agtphone, name
from log_account_info
where accountno = #accountno:varchar# and optime >= to_date(#dateBegin :varchar#, 'yyyy-mm-dd hh24:mi:ss')

(8)规则8:MySQL,任何过滤字段值必须加上单引号。

2. select语句中*号的问题

(1)规则1:尽量不要使用select *。

(2)规则2:表连接时,不允许使用select * 。

(3)规则3:单表查询,允许使用select * ,但以下几种情况禁用:

①表中包含clob字段。

②表中包含长度较大的字段,比如varchar2(2000)以上的字段,但该SQL实际上并不需要取出该字段的值。

(4)规则4:MySQL中,当结果集中无需列出被查询表的所有字段,且查询中存在“order by”语句时。禁止使用“select *”取出所有字段。

5)规则5:MySQL中,连接查询中,禁止使用“select *”。

3. NULL的使用

说明: NULL的含义是"不确定",而不是"空"

(1)规则1: 查询时,使用is null或者is not null

(2)规则2: 更新时,使用等于号。

示例:

update  tablename  set  column_name  =  null ;

4. 聚合函数常见问题

(1)规则1:计数使用count(*),而不是count(1)或count(pk)。

说明: count(column_name)计算该列不为NULL的记录条数

count(distinct column_name)计算该列不为NULL的不重复值数量

count()函数不会返回NULL,但sum()函数可能返回NULL,可以使用nvl(sum(),0)来避免返回NULL

(2)规则2:用于排序的字段,如果有null值,请注意处理null值的顺序。如果null值排在前面,排序语句添加 nulls first关键字,如果null值排在后面,排序语句添加 nulls last关键字。