SQL规范

SQL编写规范1　范围本规范规定了SQL DQL和DML语言的编写总则，从书写格式和性能优化两方面归纳了SQL 书写的具体要求，并给出SQL语句示例。本规范适用于所有开发人员。2　规范性引用文件下列文件中的条款通过本规范的引用而成为本规范的条款。凡是注日期的引用文件，其随后所有的修改单（不包括勘误的内容）或修订版均不适用于本规范，然而，鼓励根据本规范达成协议的各方研究是否可使用这些文件的最新版本。凡是不注日期的引用文件，其最新版本适用于本规范。3　术语和定义下列术语和定义适用于本规范。3.1　 驱动表：ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表将被最先处理。这个最先处理的表就是驱动表。3.2　 交叉表：被其他表引用的表。4　SQL编写总则4.1　SQL语句应正确、规范、高效和最优。4.2　同一项目的SQL书写格式应该统一。4.3　应避免写非常复杂的SQL语句。4.4　SQL语句不应在客户端组织，而应在服务器端组织。4.5　SQL语句的语法应与所使用的数据库相适应。4.6　应确保变量和参数的类型和大小与数据库中表数据列相匹配。4.7　使用SELECT语句时，应指出列名，不应使用列的序号或者用“*”替代所有列名。4.8　使用INSERT语句时，应指定插入的字段名，不应不指定字段名直接插入VALUES。4.9　如果 SQL 语句连接多表时，应使用表的别名来引用列。 //正确的示例SELECT     list.manifest_no,     list.bill_no,    stat.list_statFROMmft_list list,list_stat statWHERElist.manifest_no = stat.manifest_noAND list.bill_no = stat.list_no;4.10　SQL语句应避免对大表的全表扫描操作，对大表的操作应尽量使用索引。4.11　SQL语句应避免不必要的排序。4.12　SQL语句应避免删除全表的操作。4.13　应使用变量绑定实现SQL语句共享，避免使用硬编码。/红色字体表示书写错误；蓝色字体表示书写正确SELECT emp_nameFROM empWHERE emp_no = :B1;    //Bind value: 123 SELECT emp_nameFROM empWHERE emp_no = :B1;    //Bind value: 987 SELECT emp_name FROM empWHERE emp_no = 123;SELECT emp_name FROM empWHERE emp_no = 987;4.14　在含有子查询的SQL语句中，应减少对表的查询。 //红色字体表示书写错误；蓝色字体表示书写正确UPDATE empSETemp_cat =  (SELECT MAX(category)FROM emp_categories),sal_range =  (SELECT MAX(sal_range)            FROM emp_categories)WHERE emp_dept = 0020; UPDATE empSET (emp_cat, sal_range) = (SELECT MAX(category), MAX(sal_range)                        FROM emp_categories)                        FROM emp_categories)WHERE emp_dept = 0020;4.15　SQL语句尽可能避免多表联合复杂查询。4.16　应将SQL语句中的数据库函数、计算表达式等放置在等号右边。  4.17　应按照业务需要使用事务，同时应保持事务简短，避免大事务。4.18　在事务完整性的基础上，SQL语句应在程序中显式使用 COMMIT，ROLLBACK，尽快提交事务，释放系统资源。4.19　SQL语句应避免频繁引起数据库事务回滚。5　PL/SQL书写规则5.1　SQL语句的大小写5.1.1　SQL语句中出现的所有表名、表别名、字段名、序列等数据库对象都应小写。//红色字体表示书写错误；蓝色字体表示书写正确SELECT     list.manifest_no,     list.bill_no,     STAT.list_statFROMmft_list list,list_stat STATWHERElist.manifest_no = STAT.manifest_noAND list.BILL_NO = STAT.list_no; ALTER SEQUENCE MANIFEST_NO CACHE 200;SELECT manifest_no.nextval FROM DUAL;5.1.2　SQL 语句中出现的系统保留字、内置函数名、SQL保留字、绑定变量等都应大写。 //红色字体表示书写错误；蓝色字体表示书写正确SELECTlist.manifest_no,list.list_no,stat.list_statfrommft_list list,list_stat statwherelist.manifest_no = stat.manifest_noAND list.bill_no = stat.list_no; INSERT INTO employees(employee_name, 　 date,employee_age)VALUES(employees_seq.nextval,'John', to_date (SYSDATE)，30）；5.2　SQL语句的命名5.2.1　SQL语句中出现的变量参数应采用Camel语法命名，并反映变量的实际意义。。5.2.2　SQL语句中的表别名应简短明了，宜反映表名的实际意义。 //红色字体表示书写错误；蓝色字体表示书写正确SELECT      list.manifest_no,      list.list_no,      s.list_statFROMmft_list list,list_stat sWHERElist.manifest_no = s.manifest_noAND list.bill_no = s.list_no;5.3　SQL语句的缩进5.3.1　如果一行有多列并超过80个字符，基于列对齐原则，应采用下行缩进。5.3.2　缩进应为1个Tab或者4个字符。5.3.3　同层次的SQL语句缩进应保持一致（纵向对齐）。 //存储过程SQL文书写格式的正确示例SELECTresult.dealerCode,ROUND (SUM (result.submitsubletamountdlr + result.submitpartsamountdlr+ result.submitlaboramountdlr) / COUNT (*), 2) as avg,DECODE (null, 'x', 'xx', 'CNY')FROM   (SELECTtwc.dealerCode,twc.submitsubletamountdlr,twc.submitpartsamountdlr,twc.submitlaboramountdlrFROM  srv_twc_f twcWHERE(twc.origsubmittime >= TO_DATE ('Date Range(start)', 'yyyy/mm/dd')AND twc.origsubmittime <= TO_DATE ('Date Range(end)', 'yyyy/mm/dd')AND NVL (twc.deleteflag, '0') <> '1')UNION ALLSELECThistory.dealercode,history.submitsubletamountdlr,history.submitpartsamountdlr,history.submitlaboramountdlrFROM  srv_twchistory_f historyWHERE(history.origsubmittime >= TO_DATE ('Date Range(start)', 'yyyy/mm/dd')AND history.origsubmittime <= TO_DATE ('Date Range(end)','yyyy/mm/dd')AND NVL (history.deleteflag,'0') <> '1')) resultGROUP BY result.dealerCodeORDER BY avg DESC;）5.4　SQL语句的换行5.4.1　SELECT/FROM/WHERE/ORDER BY/GROUP BY等子句应独占一行。5.4.2　SELECT子句内容如果只有一项，应与 SELECT 同占一行。5.4.3　SELECT子句内容如果多于一项，每一项都应独占一行，并在对应 SELECT的基础上向右缩进2个Tab或者8个字符。5.4.4　FROM子句内容如果只有一项，应与 FROM同占一行。5.4.5　FROM子句内容如果多于一项，每一项都应独占一行，并在对应FROM的基础上向右缩进1个Tab或者4个字符。5.4.6　WHERE子句内容如果只有一项，应与 WHERE同占一行。5.4.7　WHERE子句的条件如果有多项，每一个条件应独占一行，并以AND开头，并在对应WHERE的基础上向右缩进1个Tab或者4个字符。 //SELECT语句书写的正确示例SELECT bill_no,FROM  mft_listWHERE manifest_no =‘000000000000000007’; SELECTlist.manifest_no,list.list_no,stat.list_statFROMmft_list list,list_stat statWHERE list.manifest_no = stat.manifest_noAND stat.stat != 2;5.4.8　（UPDATE）SET子句内容如果有一项，应与 SET同占一行。5.4.9　（UPDATE）SET子句内容如果有多项，每一项应独占一行，并在对应SET的基础上向右缩进1个Tab或者4个字符。 //SELECT语句书写的正确示例UPDATE  list_statSETlist_stat = '2',parent = '0'WHERE list_no = 'bill010';  5.4.10　INSERT 子句左/右括号以及每个表字段应独占一行，其中括号无缩进，表字段在对应括号的基础上向右缩进1个Tab或者4个字符；5.4.11　VALUES子句左/右括号以及每一项的值应独占一行，其中括号无缩进，每一项的值在对应括号的基础上向右缩进1个Tab或者4个字符；。 //INSERT语句书写的正确示例INSERT INTO list_stat(list_no, 　 list_stat, 　 parent, 　 manifest_no,div_flag)VALUES('bill020','1','0', 　 '000000000000007807', '0');5.4.12　SQL 文中不应出现空行。5.5　SQL 书写应遵循以下空格规则。5.5.1　SQL语句内的算术运算符、逻辑运算符（AND、OR、NOT）、 比较运算符（=、<=、>=、>、<、<>、BETWEENAND）、IN、LIKE等运算符前后都应加一空格。5.5.2　SQL 语句中逗号后应加一空格。//红色字体表示书写错误；蓝色字体表示书写正确SELECT     list.manifest_no,     list.list_no,     stat.list_statFROMmft_list list,list_stat statWHERElist.manifest_no = stat.manifest_noAND list.bill_no= stat.list_no; SELECTCOUNT(DECODE(dept_no, '0020','X',NULL))　d0020_count,　  COUNT(DECODE(dept_no, '0030','X',NULL))　d0030_count,    SUM(DECODE(dept_no, 0020, salary, NULL)) d0020_sal,    SUM(DECODE(dept_no, 0030, salary, NULL)) d0030_salFROM empWHERE emp_name LIKE 'SMITH%';5.6　不等于应统一使用符号“<>”。 //红色字体表示书写错误；蓝色字体表示书写正确SELECT     list.manifest_no,     list.list_no,    stat.list_statFROMmft_list list,list_stat statWHERElist.manifest_no = stat.manifest_noAND list.bill_no = stat.list_noAND stat.stat <> ‘2’AND parent != ‘0’;5.7　SQL 语句的注释。5.7.1　对较为复杂的 SQL 语句应注释，并说明算法和功能。5.7.2　注释应单独成行，并放在语句前面。5.7.3　应对不易理解的分支条件表达式加注释。5.7.4　对重要的计算应说明其功能。5.7.5　过长的函数实现，应将其语句按实现的功能分段加以概括性说明。5.7.6　对常量及变量注释时，应注释被保存值的含义，宜包括合法取值的范围。5.7.7　应可采用多行注释。（/* */ 方式）。 BEGIN   ...   /* Compute a 15% bonus for top-rated employees. */   IF rating > 90 THEN      bonus := salary * 0.15   /* bonus is based on salary */   ELSE      bonus := 0;   END IF;   ...   /* The following line computes the area of a      circle using pi, which is the ratio between      the circumference and diameter. */   area := pi * radius**2;END;6　嵌入式SQL书写规则6.1　应遵循PL/SQL的书写规则。6.2　SQL语句中出现的变量参数命名应遵循各语言编码规范的要求。7　SQL字符串书写规则7.1　SQL语句的大小写7.1.1　SQL语句中出现的所有表名、表别名、字段名、序列等数据库对象都应小写。7.1.2　SQL 语句中出现的系统保留字、内置函数名、SQL保留字、绑定变量等都应大写。7.2　SQL语句的命名7.2.1　SQL语句中出现的变量参数应遵循各语言编码规范的要求。7.2.2　SQL语句中的表别名应简短明了，宜反映表名的实际意义。7.3　SQL语句的缩进和换行7.3.1　应遵循各语言的编码规范的要求。7.3.2　单引号应与所属的 SQL子句位于同一行。7.4　SQL书写应遵循以下空格规则。7.4.1　SQL语句内的算术运算符、逻辑运算符（AND、OR、NOT）、 比较运算符（=、<=、>=、>、<、<>、BETWEENAND）、IN、LIKE等运算符前后都应加一空格。7.4.2　SQL语句中逗号后应加一空格。7.5　不等于应统一使用符号“<>”。7.6　拼装SQL语句时宜使用StringBulider，不宜使用String + String的方式。7.7　SQL 语句的注释。7.7.1　应遵循各语言编码规范的代码注释要求。7.7.2　对较为复杂的 SQL语句应注释，并说明算法和功能。7.7.3　对重要的计算应说明其功能。7.7.4　对常量及变量注释时，应注释被保存值的含义，宜包括合法取值的范围。 //SQL拼串语句书写的正确示例string tmpsql = "SELECT i_e_flag, i_e_date, ship_id FROM mft_head WHERE manifest_no =:MANIFEST_NO";StringBuilder sqlStr  = "SELECT manifest_no, i_e_flag, i_e_date, ship_id  FROM mft_head "+ "WHERE  manifest_no = '"+ manifestNo + "'"+ "AND ship_id = '"+ shipId + "'"; 8　SQL 优化规则(仅适用于Oracle)8.1　SQL 语句中应避免使用不必要的 PL/SQL，JAVA，C。8.1.1　应用逻辑简单时，应使用 SQL。8.1.2　应用逻辑复杂时，使用 SQL 实现困难，可使用 PL/SQL，JAVA，C。8.2　多表联查时驱动表的选择应遵循以下规则：8.2.1　如果两张表联查，应选择记录少的表做为驱动表。8.2.2　如果三张表联查，应选择交叉表（定义请参见3.2　）做为驱动表。8.3　WHERE子句的连接顺序应遵循以下规则：8.3.1　表之间的连接必须写在其他WHERE条件之前。8.3.2　应按符合指定条件的记录范围由小到大进行排列,过滤掉最大数量记录的条件必须写在WHERE子句的末尾。8.4　使用ROWID的WHERE子句效率最高。8.5　WHERE子句中应避免常量比较，应使用主机变量。8.6　应尽量避免在SQL语句中使用多表连接，特别是表之间的嵌套连接。8.7　应尽量加少对数据库的访问次数。8.7.1　应使用 DECODE函数避免重复扫描相同记录或者重复连接相同的表。 //一般情况下，效率较低。SELECT         COUNT (*),         SUM (salary)FROM empWHEREdept_no = '0020'AND emp_name LIKE 'SMITH%'; SELECTCOUNT (*),SUM (salary)FROM empWHEREdept_no = '0030'AND emp_name LIKE 'SMITH%'; //一般情况下，效率较高。SELECTCOUNT(DECODE(dept_no, '0020', 'X', NULL))　d0020_count,COUNT(DECODE(dept_no, '0030', 'X', NULL))　d0030_count,    SUM(DECODE(dept_no, '0020', salary, NULL)) d0020_sal,    SUM(DECODE(dept_no, '0030', salary, NULL)) d0030_salFROM empWHERE emp_name LIKE 'SMITH%'; 其中：X表示任何字符或字符串。类似的，DECODE 还可用于GROUP BY 和 ORDER BY 子句中。 8.7.2　整合简单的、无关联的数据库访问。 //3个简单的查询。SELECT NAMEFROM EMPWHERE EMP_NO = 1234; SELECT NAMEFROM DPTWHERE DPT_NO = 10 ; SELECT NAMEFROM CATWHERE CAT_TYPE = ‘RD’;　　//上面的3个查询可以被合并成一个。SELECTE.NAME ,D.NAME ,C.NAMEFROMCAT C ,DPT D ,EMP E,DUAL XWHERENVL(‘X’,X.DUMMY) = NVL(‘X’,E.ROWID(+))　　AND NVL(‘X’,X.DUMMY) = NVL(‘X’,D.ROWID(+))　　AND NVL(‘X’,X.DUMMY) = NVL(‘X’,C.ROWID(+))　　AND E.EMP_NO(+) = 1234　　AND D.DEPT_NO(+) = 10　　AND C.CAT_TYPE(+) =‘RD’; 8.8　使用 WHERE 子句替代 HAVING 子句在有些情况下会提高性能。具体根据实际测试而定。 //一般情况下，效率较低。SELECTregion,AVG (log_size)FROM locationGROUP BY regionHAVING region <> 'SYDNEY'AND region <> 'PERTH' //一般情况下，效率较高。SELECTregion,AVG (log_size)FROM locationWHEREregion <> 'SYDNEY'AND region <> 'PERTH'GROUP BY region 8.9　使用EXIST代替IN可能会提高性能，但并非所有情况都适用。具体要依据测试结果而定。 //一般情况下，效率较低。SELECT …FROM empWHERE empno > 0AND deptno IN ( SELECT deptno                FROM dept                where loc = 'MELB') //一般情况下，效率较高。SELECT …FROM empWHERE empno > 0AND EXISTS ( SELECT 'X'             FROM dept             WHERE dept.deptno = emp.deptno             AND loc = 'MELB') 8.10　使用NOT EXIST或外连接代替NOT IN可能会提高性能，但并非所有情况都适用。具体要依据测试结果而定。 //一般情况下，效率较低。SELECT …FROM empWHERE dept_no NOT IN ( SELECT dept_no                       FROM dept                       WHERE dept_cat = 'A'); //一般情况下，效率较高。SELECT …FROM employee emp, department deptWHERE emp.dept_no = dept.dept_no(+)AND dept.dept_no IS NULLAND dept.dept_cat(+) = 'A' //一般情况下最高效。SELECT …FROM employee empWHERE NOT EXISTS ( SELECT 'X'                   FROM department dept                   WHERE emp.dept_no = dept.dept_no) 8.11　一般情况下，使用表连接替代 EXIST 子句可提高性能。 //一般情况下，效率较低。SELECT enameFROM employee empWHERE EXISTS ( SELECT 'X'               FROM department dept                WHERE dept_no = emp.dept_no               AND dept_cat = 'A'); //一般情况下，效率较高。SELECT enameFROM department dept, employee empWHERE dept.dept_no = emp.dept_noAND dept.dept_cat = 'A'; 一般情况下，使用EXIST 子句替代 DISTINCT 子句可提高性能。 //一般情况下，效率较低。SELECTDISTINCT dept.dept_no,dept.dept_nameFROMdepartement dept,employee empWHERE dept.dept_no = emp.dept_no //一般情况下，效率较高。SELECTdept_no,dept_nameFROM department deptWHERE EXISTS ( SELECT 'X'               FROM employee emp               WHERE emp.dept_no = dept.dept_no); 8.12　使用索引应遵循以下规则：8.12.1　索引的建立应慎重考虑，不是越多越好。索引可以提高相应的select的效率，但同时也降低了INSERT及 UPDATE 的效率。8.12.2　被查询列有大量重复数据时，如状态标志，可考虑建立位图索引。位图索引只对基于COST优化时有效。8.12.3　查询列、排序列应与索引列次序保持一致。8.12.4　应避免在WHERE子句中使用计算后的索引列。8.12.5　应避免在 WHERE 子句索引列上使用函数或者表达式。如果确需使用，应建立对应的函数索引。8.12.6　应避免在 WHERE 子句中对索引列使用 IS NULL，IS NOT NULL。8.12.7　索引列的比较应避免使用<>，NOT。8.12.8　应避免在 WHERE 子句中对索引列使用 LIKE ’%xxx%’,’%xxx’。8.12.9　可在WHERE子句中对索引列使用LIKE ’xxx%’。8.12.10　应避免对索引列值进行隐式/显式转换。8.12.11　应尽量使用与索引列数据类型保持一致的比较值。8.12.12　在 WHERE子句中应注意比较值与索引列数据类型的一致性，应显式转换比较值使其与索引列数据类型保持一致。8.12.13　应避免比较同一张表中的列。8.12.14　IN、OR子句常会使用工作表，导致索引无效。如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应包含索引。8.12.15　对于复合索引，WHERE子句中必须包含索引的第一列才一定能够使用到索引。8.13　为保证SQL的执行效率，应避免使用UNION、 OR 子句，可考虑在应用中对结果集进行处理。 // WHERE语句中使用索引的优化。红色的表示没有成功使用索引，蓝色的表示成功使用索引。SELECTacc_name,trans_date,amountFROM transactionWHERE SUBSTR(account_name,1,7) = 'CAPITAL'; SELECTacc_name,trans_date,amountFROM transactionWHERE account_name LIKE 'CAPITAL%'; SELECTacc_name,trans_date,amountFROM transactionWHERE amount <> 0; SELECTacc_name,trans_date,amountFROM transactionWHERE amount > 0; SELECTacc_name,trans_date,amountFROM transactionWHERE TRUNC(trans_date) = TRUNC(SYSDATE); SELECTacc_name,trans_date,amountFROM transactionWHERE trans_date  BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + .99999; SELECTacc_name,trans_date,amountFROM transactionWHERE acc_name || acc_type = 'AMEXA';        SELECTacc_name,trans_date,amountFROM transactionWHEREacc_name = 'AMEX'AND acc_type = 'A'; SELECTacc_name,trans_date,amountFROM transactionWHERE amount + 3000 < 5000;        SELECTacc_name,trans_date,amountFROM transactionWHERE amount < 5000 - 3000; 8.14　表扫描应遵循以下规则：8.14.1　数据量小的表可使用全表扫描。8.14.2　对于大表应通过索引加快数据查询。8.14.3　如果查询返回数据量超过表总数据的20%时可使用全表扫描。 8.15　应尽量减少ORDER BY和GROUP BY等排序操作。 8.16　如果使用排序操作应遵循以下规则：8.16.1　排序应尽量建立在索引列上。8.16.2　如果业务规则允许结果集不需要唯一确定，应使用 UNION ALL 替代 UNION。 8.17　避免死锁应遵循以下规则：8.17.1　业务逻辑允许情况下，两个或多个事务按照同一顺序访问数据对象。8.17.2　为了减少死锁的发生，在执行update语句前可先执行select … for update nowait。如果此时其它会话已经锁定本会话需要update的记录，oracle会立即返回一个错误代码ora-00054，而不会一直等待其它会话释放锁。8.18　数据中心的Oracle数据库中使用的是基于代价的数据库优化规则（CB0），出于实用的角度考虑，只介绍基于CBO的优化规则。在基于代价的优化器模式下(CBO)，表连接时，SQL语句的执行计划与表的书写顺序无关，与WHERE子句中各查询条件的顺序也无关，但对于有复合索引的情况下，建议WHERE子句中各查询条件的顺序尽量与复合索引建立的顺序一致，并且只有在WHERE子句中使用了复合索引的第一列才一定能够使用到索引。 
    对任何列的操作都会导致表扫描。     要求返回表中数据的大部分或者全部。Oracle通过一个内部参数_small_table_threshold来定义大表和小表的界限。缺省的该参数等于2%的Buffer数量，如果表的大小小于该参数定义，Oracle认为该表为小表，否则Oracle认为该表为大表。      大量的排序影响性能。      注意UNIONALL不会去除结果集中的重复记录，而UNION会去重。