项目一部数据库开发规范注：对该文件内容增加、删除或修改均需填写此变更记录，详细记载变更信息，以保证其可追溯性。

1.命名规范1.1.对象新建的表，存储过程，包等要遵循以下规则1.2.表表名不得超过30个字母，全部采用大写字母，表的命名可以如下：模块缩写名_表的名称，如RES_NODE代表资源模块的节点; 值班： DUTY_CALENDAR。

1.3.字段名称字段名不得超过30个字母，必须以英文单词构成，每个单词之间以下划线隔开，全部采用大写字母。

对复杂的大型应用系统而言，必须建立表名和字段名的数据字典，并附于开发规范附录中，在命名时必须严格遵守数据字典。

2.数据库对象管理2.1.一般规定数据库所有对象，包括表、视图、主键、索引、序列、存储过程、包等必须在数据库建模工具中进行管理并保持与数据库完全同步。

2.2.大小写在数据库模型、数据库脚本中，所有对象，包括表、视图、主键、索引、序列、存储过程、包等名称必须大写。

3.语句书写规范3.1.尽量不使用某种数据库的特有功能为了保持可移植性，尽量不使用某种数据库的特有功能，如SQL Server专用的Unique ID, Oracle专用的Sequence的功能；3.2.查询sql语句尽量使用绑定变量3.3.尽最大可能不使用通配符在SQL语句中，LIKE关键字支持通配符匹配，但这种匹配特别耗费时间。

如：SELECT A FROM ABC WHERE A LIKE 'M%' 。

在A字段上建立了索引。

把语句改为SELECT A FROM ABC WHERE A >'M' AND A <'N'，在执行查询时会利用索引以提高响应速度。

使用*通配符必须事先征得项目开发负责人同意。

3.3.1Distinct使用distinct会增加查询和I/O的操作次数。

应当避免使用distinct关键字。

3.3.2嵌套查询SELECT A FROM CMS_USER WHERE USER_NAME IN ( SELECTUSER_NAME FROM CMS_DEPARTMENT WHERE DEPARTMENT=’电子办’)如果我们用连接来代替，且表关联放在条件语句的最后部。

即：SELECT A FROM CMS_USER，CMS_DEPARTMENT WHERECMS_DEPARTMENT .DEPARTMENT=’电子办’ ANDCMS_DEPARTMENT .USER_NAME = CMS_ER_NAME 将提高一定的效率。

查询嵌套层次越多，效率越低。

应当尽量避免子查询。

如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。

3.3.3排序利用索引自动以适当的次序输出时，可以避免对表中数据排序，当以下的情况发生时，排序就不能省略：➢索引中不包括一个或几个待排序的列；➢group by或order by子句中列的次序与索引的次序不一样；➢排序的列来自不同的表。

正确地增建索引、合理地合并数据库表，可以避免不必要的排序。

如果排序不可避免，那么应当试图简化它，如缩小排序列的范围等。

3.3.4UNION如果不过滤多表中的重复数据，请使用UNION ALL；如果过滤多表中的重复数据，请使用UNION。

3.3.5长语句避免使用很长、很复杂的查询语句，如果有特殊需求必须书写较长的SQL语句，应该把语句分解成若干部分，每一部分形成一个存储过程或函数。

3.3.6大表尽可能使用分区大表分区：超过1G的表尽可能使用分区，分区的原则和尽可能和维护该表的机制结合起来。

比如：保留10天数据，每天删除10天前的一天数据，在删除数据的时候，可以采用采用alter table table_name truncate partition partition_name,而后alter table table_name drop partition partition_name;这里不直接使用drop的原因是减小数据库的开销。

Truncate 是在秒的级别中完成。

3.3.7其他注意事项1)在条件语句中，如 Where a.s=b.c 中将记录少的表放在等号的前部。

表关联条件放在语句的最后部。

2)不使用容易与系统关键字重复的单词来命名，如ID，DATE等，但可以使用如NODEID，BUYDATE等可以来命名；3)SQL语句用大写字母（字段中的内容除外）；4)编写ddl和dml时，每个语句后面必须加上分号；5)对于定期增加性的性能数据表，必须建立有效的索引；6)查询语句的Where语句必须落在索引上。

4高效的sql语句4.1Sql优化方法RBO(rule-based optimizer)CBO4.1.1驱动表1）2张行数不一致的表连接表TAB1行数：16,384行表TAB2行数：1行×SELECT COUNT(*) FROM TAB2, TAB1;○SELECT COUNT(*) FROM TAB1, TAB2;2）3张表连接×SELECT *FROM EMP E,LOC L,CAT CWHERE E.emp_no BETWEEN 1000 AND 2000AND E.cat_no = C.cat_noAND E.locn = L.locn;○SELECT *FROM LOC L,CAT C,EMP EWHERE E.emp_no BETWEEN 1000 AND 2000AND E.cat_no = C.cat_noAND E.locn = L.locn;4.1.2Where语句顺序的效率1)使用索引引起的where语句效率使用AND语句时行数多的放在前面×SELECT *FROM emp EWHERE emp_sal > 50000AND emp_type = 'MANAGER'AND 25 < (SELECT COUNT(*) FROM EMP WHERE emp_mgr= E.emp_no);○SELECT *FROM emp EWHERE 25 < (SELECT COUNT(*) FROM EMP WHEREemp_mgr = E.emp_no)AND emp_sal > 50000AND emp_type = 'MANAGER';使用OR语句时，行数多的放在后面×SELECT *FROM emp EWHERE 25 < (SELECT COUNT(*) FROM EMP WHEREemp_mgr = E.emp_no)OR (emp_sal > 50000AND emp_type = 'MANAGER');○SELECT *FROM emp EWHERE (emp_sal > 50000AND emp_type = 'MANAGER')OR 25 < (SELECT COUNT(*) FROM EMP WHERE emp_mgr =E.emp_no);2）ROWID的使用使用ROWID的WHERE语句效率最高。

SELECT ROWID, ...INTO :emp_rowid, ...FROM empWHERE emp.emp_no = 56722FOR UPDATE;UPDATE empSET = ... ,WHERE ROWID = :emp_rowid;3）减少访问次数×SELECT emp_name, sal, gradeFROM empWHERE emp_no = 0342;SELECT emp_name, sal, gradeFROM empWHERE emp_no = 0291;○SELECT A.emp_name, A.sal, A.grade,B.emp_name, B.sal, B.gradeFROM emp A, emp BWHERE A.emp_no = 0342AND B.emp_no = 0291;4）Where语句的索引的使用（1）SUBSTR×SELECT acc_name, trans_date, amountFROM transactionWHERE SUBSTR(account_name,1,7) = 'CAPITAL';○SELECT acc_name, trans_date, amountFROM transactionWHERE account_name LIKE 'CAPITAL%';（2）！=×SELECT acc_name, trans_date, amountFROM transactionWHERE amount != 0;FROM transactionWHERE amount > 0;（3）TRUNC×SELECT acc_name, trans_date, amountFROM transactionWHERE TRUNC(trans_date) = TRUNC(SYSDATE);○SELECT acc_name, trans_date, amountFROM transactionWHERE trans_dateBETWEEN TRUNC(SYSDATE)AND TRUNC(SYSDATE) + .99999;（4）||×SELECT acc_name, trans_date, amountFROM transactionWHERE acc_name || acc_type = 'AMEXA';○SELECT acc_name, trans_date, amountFROM transactionWHERE acc_name = 'AMEX'AND acc_type = 'A';（5）运算×SELECT acc_name, trans_date, amountFROM transactionWHERE amount + 3000 < 5000;○SELECT acc_name, trans_date, amountFROM transactionWHERE amount < 5000 + 3000;4.2SQL命令的使用秘诀4.2.1DECODE的使用×SELECT COUNT(*), SUM(salary)FROM empWHERE dept_no = 0020AND emp_name LIKE 'SMITH%';SELECT COUNT(*), SUM(salary)FROM empWHERE dept_no = 0030AND emp_name LIKE 'SMITH%';○SELECT COUNT（DECODE(dept_no, 0020, 'X', NULL))D0020_count,COUNT (DECODE(dept_no, 0030, 'X', NULL))D0030_count,SUM (DECODE(dept, 0020, salary, NULL)) D0020_sal,SUM (DECODE(dept, 0030, salary, NULL)) D0030_salFROM empWHERE emp_name LIKE 'SMITH%';4.2.2表的别名的使用× SELECT E.emp_no, name, tax_no, p_code, comp_nameFROM company C, emp EWHERE p_code = p_code;○ SELECT E.emp_no, , E.tax_no, p_code, p_nameFROM company C, emp EWHERE p_code = p_code;4.2.3去掉重复行DELETE FROM emp EWHERE E.rowid > ( SELECT MIN(X.rowid)FROM emp XWHERE X.emp_no = E.emp_no );4.2.4表的行计数SELECT COUNT(有索引的列) FROM TRANS;SELECT COUNT(*) FROM TRANS;SELECT COUNT(1) FROM TRANS;4.2.5用WHERE语句替换HAVING语句的使用×SELECT region, AVG(loc_size)FROM locationGROUP BY regionHAVING region != 'SYDNEY'AND region != 'PERTH';○SELECT region, AVG(loc_size)FROM locationWHERE region != 'SYDNEY'AND region != 'PERTH';GROUP BY region4.2.6使用表连接替代EXISTS使用×SELECT emp_nameFROM empWHERE (emp_cat, sal_range) =(SELECT MAX(category), MAX(sal_range) FROM emp_categories) AND emp_dept = 0020;○SELECT emp_nameFROM empWHERE emp_cat = (SELECT MAX(category) FROM emp_categories)AND sal_range = (SELECT MAX(sal_range) FROM emp_categories)AND emp_dept = 0020;4.2.7使用EXISTS替代表连接×SELECT ...FROM dept D, emp EWHERE E.dept_no = D.dept_noAND E.emp_type = 'MANAGER'AND D.dept_cat = 'A';○SELECT ...FROM emp EWHERE EXISTS ( SELECT 'X' FROM deptWHERE dept_no = E.dept_noAND dept_cat = 'A')AND E.emp_type = 'MANAGER';×SELECT ...FROM dept D, emp EWHERE E.dept_no = D.dept_noAND (E.emp_type = 'MANAGER'OR D.dept_cat = 'A');○SELECT ...FROM emp EWHERE E.emp_type = 'MANAGER';OR EXISTS ( SELECT 'X'FROM deptWHERE dept_no = E.dept_noAND dept_cat = 'A');4.2.8使用EXISTS代替DISTINCT语句×SELECT DISTINCT dept_no, dept_nameFROM dept D, emp EWHERE D.dept_no = E.dept_no;○SELECT dept_no, dept_nameFROM dept DWHERE EXISTS ( SELECT 'X'FROM emp EWHERE E.dept_no = D.dept_no);4.2.9使用NOT EXISTS代替NOT IN语句× SELECT ...FROM empWHERE dept_no NOT IN ( SELECT dept_noFROM deptWHERE dept_cat = 'A');○SELECT ...FROM emp EWHERE NOT EXISTS ( SELECT 'X'FROM deptWHERE dept_no = E.dept_noAND dept_cat = 'A');4.2.10使用union all 代替union 语句Union -------- 进行排序Union All -------- 不排序4.2.11使用Union和IN代替OR语句Loc_no, region上有索引× SELECT ...FROM locationWHERE loc_id = 10OR region = 'MELBOURNE';○ SELECT ...FROM locationWHERE loc_id = 10UNION ALLSELECT ...FROM locationWHERE region = 'MELBOURNE';×SELECT ...FROM locationWHERE loc_id = 10OR loc_id = 20OR loc_id = 30;5高效索引5.1索引的使用使用索引时，要考虑以下因素：1）索引列的计算2）索引列的增加3）索引列不要用NOT4）索引中空值的使用IS NULL, IS NOT NULL5）索引列的数据类型的变换EMP_TYPE为varchar2类型，下列语句使用索引SELECT ...FROM empWHERE emp_type = 123;SELECT ...FROM empWHERE TO_NUMBER(emp_type) = 123;SELECT ...FROM empWHERE emp_type = '123';5.2增加索引增加索引要考虑多种因素，要考虑对更新、插入的影响等。