SQL语句编写指南(规范).doc

1 目录1目录12规范的目的23SQL语句大小写规范24条件句式24.1原理介绍24.2主要原则34.2.1条件语句中，数据库字段放置在左边。34.2.2条件语句中，数据库字段不允许出现在函数或者运算表达式中。34.2.3条件语句中，依照字段数值变化频率，安排条件中字段顺序。44.2.4尽可能使用参数化SQL语句54.2.5LIKE 条件子句的使用规范。54.2.6日期型字段的条件编写规范65如何规避数据库死锁。75.1死锁产生的原理75.2死锁避免规范85.2.1入口资源法则85.2.2顺序法86 UNION查询使用规范92 规范的目的通过该规范，统一各个项目工程SQL语句的设计编写，达到1） 提高数据库系统的数据处理效率。2） 避免数据库资源死锁现象。3） 提高应用系统在不同数据库平台的可移植性。3 SQL语句大小写规范1) SQL 语句的关键字一律大写。2) 数据库系统函数一律大写。3) 字段名、表名、函数名、过程名除有特殊规定以外，一律小写。/* 说明: a) 此规定主要目的是为了增强数据库的可移植性。 b) 有些数据库系统对字段名、表名、函数名、过程名大小写敏感。因此特意约定一律小写，以便数据库移植。如Sybase。 c) 在移植过程中，只需要在表、视图等过程中，将其置为小写既可。 d) 有些项目，对数据库各类对象的命名会有特殊的规定，此时以其规定为约束。*/ 4 条件句式4.1 原理介绍 假设有数据表tab1( f1_vc vc(10) ,f2_vc vc(10) ,f3_date date ,f4_num number, f5_blob blob) 1) 数据库系统执行SQL语句前，将执行策略分析。策略分析的主要依据是是否存在数据库字段索引。2) SQL 语句分析后，分析结果将保存在缓存中，下一次相同SQL 语句执行时，可以不再需要作分析，从而节约时间。数据库系统将按照“最近使用法”清理SQL分析结果垃圾。3) 如果数据库系统没有作过数据分析，并且存在多个条件字段，同时分布在多个索引中；数据库系统将选取从左到右最早满足条件的索引，作为执行策略。举例：存在索引 i1( f1_vc , f2_vc) ; i2( f1_vc , f3_date ); SQL 语句： SELECT * FROM tab1 WHERE f1_vc = :f1_val AND f2_vc = :f2_val AND f3_date = :f3_val ; 此时，系统将选择索引 i1 作为执行策略。4) 如果数据库系统有作过数据分析，并且存在多个条件字段，同时分布在多个索引中；数据库系统将尝试选取数值变化最频繁的字段索引，作为查询执行策略。举例：存在索引 i1( f1_vc , f2_vc) ; i2( f1_vc , f3_date ); SQL 语句： SELECT * FROM tab1 WHERE f1_vc = :f1_val AND f2_vc = :f2_val AND f3_date = :f3_val ; 在策略分析时，索引i1 , i2 都可以使用，但在执行时，只能使用一个索引，此时，根据 f2_vc , f3_vc 的数据变化频率，决定选用的索引。该表中, f3_date 变化频率最大，系统将选择 i2 索引作为执行策略。4.2 主要原则4.2.1 条件语句中，数据库字段放置在左边。举例： 非法: SELECT * FROM tab1 WHERE 10 11 ; 合法： SELECT * FROM tab1 WHERE f1_vc10 AND f2_vc 11 ; 原因分析： 策略分析时，字段放置在左边，将有利于系统分析的执行。4.2.2 条件语句中，数据库字段不允许出现在函数或者运算表达式中。 规范要求如果需要运算，应在输入SQL语句前，应把比较数值先计算好，并放置在右边。举例1： 非法: SELECT * FROM tab1 WHERE SUBSTR( f1_vc, 1,2) =10 ; 合法： SELECT * FROM tab1 WHERE f1_vc LIKE 10%; 举例举例2： 非法: SELECT * FROM tab1 WHERE TO_DATE( f3_date, yyyymmdd) =20050101 ; 合法： SELECT * FROM tab1 WHERE f3_date = :begin_time and f2_date 20 ; 合法： SELECT * FROM tab1 WHERE f4_num :lower_number ; 使用参数化SQL语句，在执行SQL语句前 :lower_number 数值计算好，在传入到参数化SQL语句中。 4.2.3 条件语句中，依照字段数值变化频率，安排条件中字段顺序。 规范要求字段优先顺序依次为：关键字、unique 索引字段、其他索引字段。其他索引字段，一般而言，依据字段中数值变化频率的大小顺序，依次排列。4.2.4 尽可能使用参数化SQL语句 原理分析数据库系统将缓存执行策略，如果下一次出现相同的SQL语句时，将不需要重新分析SQL 语句，直接从缓存中提取执行策略给以执行。在SQL语句中，筛选条件中的数值，往往会根据不同的情况发生变化，从而导致相似的SQL语句，在不同的情况下就变得不一样。采用参数化的SQL语句，可以使得SQL语句保持不变的情况下，可以满足不同条件数值的查询。 规范要求除了LIKE 条件字句以外，尽可能使用参数化SQL语句 举例非法： SELECT * FROM DataTab WHERE Field1 = aaa AND FieldDate = TO_DATE( 20050101 , YYYYMMDD ) ; 合法： JAVA 、Delphi 等应用端的SQL语句。 SELECT * FROM DataTab WHERE Field1 = ? AND FieldDate = ? ; 存储过程端的SQL语句 CURSOR C_Tab( I_Field1 VARCHAR2, I_FieldDate DATE) SELECT * FROM DataTab WHERE Field1 = I_Field1 AND FieldDate = I_FieldDate ; 或者： SELECT * FROM DataTab WHERE Field1 = V_Field1 AND FieldDate = V_FieldDate ; V_Field1 、V_FieldDate 变量名称命名规则：V_字段名。 4.2.5 LIKE 条件子句的使用规范。LIKE 条件子句，一般用于字符型数据的通用符匹配查询。 原理分析n 当数据库系统策略分析 LIKE 条件子句时，如果第一个字符为通配符，将使用全表扫描，如果不是通配符，将使用索引。n 如果第一次查询时，发现第一个字符为通配符，将不采用索引，而采用全表扫描。此时该语句的分析结果将保存在缓存中。n 在第二次查询时，将按照第一次分析的策略执行，即使第一个字母不是通配符，也将采用全表扫描策略。 使用规范n 在LIKE 条件子句中，不得使用参数化条件。n 使用LIKE 条件字句，条件数值的第一个字母尽可能不要使用通配符。 举例非法： SELECT * FROM DataTab WHERE Field1 LIKE ? ; 合法 SELECT * FROM DataTab WHERE Field1 LIKE aa% ; 4.2.6 日期型字段的条件编写规范数据库的一个日期型字段中，除了日期以外，还存储时、分、秒等数值。对于时间段的条件表达式，应采用范围法。 举例： 筛选 2005年1月1日的所有数据非法1） SELECT * FROM T_Data1 WHERE ModiTime = TO_DATE( 2005/01/01 , YYYY/MM/DD ) ; 由于ModiTime 可能存放着时、分、秒等数据，该SQL 语句可能会无法筛选出任何数据。这条SQL语句只能找出发生在 2005年01月01日 0点0分0秒的数据。 该SQL 语句使用了Oracle 专用函数，只能适用于Oracle ，无法平台迁移。非法2） SELECT * FROM T_Data1 WHERE TO_CHAR( ModiTime , yyyy/mm/dd = 2005/01/01; 该SQL语句可以筛选出该天的数据。但由于数据字段出现在SQL 函数中，无法使用索引，该函数的执行将会导致全表扫描，大量消耗系统资源，可能导致整个系统的运行效率。该SQL 语句使用了Oracle 专用函数，只能适用于Oracle ，无法平台迁移。非法3） SELECT * FROM T_Data1 WHEREModiTime = TO_DATE( 2005/01/01 , yyyy/mm/dd ) AND ModiTime = :BEG_TIME AND ModiTime :END_TIME;采用参数化SQL ，:BEG_TIME = (new Date(2005/01/01); :END_TIME = (new Date(2005/01/02);该SQL 既可使用系统索引，也提高的平台的可移植性。5 如何规避数据库死锁。5.1 死锁产生的原理有资源A , B ，存在并发处理任务 t1 ， t2 ；如果t1 依序资源独占性处理 A ，B ； t2 同时依序资源独占性处理 B ，A ； 此时就会发生死锁行为。数据库操作中，可能会发生死锁的主要操作：UPDATE 、DELETEA) 多表修改或删除如果多表修改或删除顺序没有统一；不同的处理模块对多个数据表修改的顺序不一，就容易发生死锁行为。B) 单表修改或删除对同一个表的不同记录，如果不同程序、或不同任务中，修改顺序不一致，也可能会发生死锁行为。a) 用一条SQL语句，大批量修改同一张表的多条记录时，容易发生死锁。 这种修改方式，修改的记录以及记录顺序是由数据库系统和筛选条件决定的，决定记录顺序时，往往具有随机性。如果多个任务同时执行这样的修改任务时，就容易发生死锁行为。b) 使用游标筛选出同一张表的记录，一条记录一条记录的修改数据，在以下情况下，由于筛选出来的记录顺序不一；当多个任务同时执行修改任务时，也容易发生系统死锁。n 没有用排序字句。n 没有使用能够唯一确定顺序的排序字段（一般建议采用关键字或者唯一性索引）。n 不同的模块，采用的排序方式不一致(如模块1采用关键字，模块2采用唯一索引UniIdx1 ，模块3采用唯一性索引 UniIdx2 )。5.2 死锁避免规范5.2.1 入口资源法则概要的说，是关键表关键记录法。n 如果一次事务处理，需要修改或删除多个表、或单个表的多条记录；必须有一张表的一条记录，通过这条记录能够唯一的对应后续需要修改的各条记录。系统设计人员，必须在系统设计阶段确定关键数据表的关键记录。n 在实施多记录修改时，必须先锁定关键记录。方法1) Oracle 方法，不强烈推荐。SELECT * FROM Primary_Table WHERE Key_Field = :Key_Val FOR UPDATE ;方法2) 通用方法，可适用于各个平台，强烈推荐。 UPDATE Primary_Table SET Field1=Field1 WHERE Key_Field = :Key_Val ; n 如果修改的数据表，有关键表的关键记录，在可能的情况下，先修改关键表的关键记录。5.2.2 顺序法对于一组数据表，或者一张表的不同记录，各个模块均使用相同的顺序，6 UNION查询使用规范 原理分析u UNION 查询将把多条 SQL语句查询结果合并。u 在UNION 查询中，如果没有指定 ALL 关键字，数据库系统将检查每个查询结果集合，试图合并可能存在重复记录；不管实际情况是否存在重复记录，这一操作是必然执行。u 在执行记录集合的检查过程中，数据库系统将对结果的全部字段建立临时索引，以便判断。因此，这一过程是极其消耗系统资源的。u 在需要执行UNION 操作的SQL中，业务上一般是接收重复记录的。因此，如果不加入ALL 关键字，将