DB2数据库SQL编码.docx

1、DB2数据库SQL编码Db2数据库SQL编码 摘要 本文主要是关于用SR做报表，编写SQL代码的心得，文章从select语句的执行顺序，临时表，自定义函数，索引，存储过程这几个方面的使用，来讨论如何优化SQL代码，加快速查询速度，增强代码的可读性。其中select语句的执行顺序和索引主要是为了加快SQL的查询速度。自定义函数，临时表，存储过程的使用是为了处理逻辑复杂的sql代码，让代码的可读性加强。关键字：SQL 查询速度 可读性1 SQL 执行顺序 根据SQL的执行顺序和表数据具体情况，在多表边接之前删除多余的数据，加快查询速度。详解如下：从sql的执行方式的来优化SQL代码的执行速度。首先

2、，看看where ,group by ,order by 等的执行顺序。Sql语句的执行顺序其实是如下的:Select A.id, COUNT(B.字段)AS num6FROM table1 AS A 1LEFT OUTER JOIN table2 AS B2ON A.id = A.id 3WHERE A.字段 = 条件4GROUP BY A.字段 5HAVING COUNT(B.字段) 37ORDER BY num 8首先，on 和where 两者的区别：上面的一段SQL是按标号的顺序执行的，SELECT的执行顺序是放在最后的，而FROM则是第一步，并且是两个表先做外连接，然后通过ON滤掉不

3、符合条件的结果。注意，这里写在on后面的过滤条件和where后面的过滤条件的是不同的，主要是根据连接的类型而定（例如:FROM TableA LEFT JOIN TableB ON TableA.DATE 1981-01-01当做完LEFT连接后不符合条件的TableA表中的记录是会添加进来的，因为这里使用的是LFET JOIN）。所以，因此在我们在写sql时应该在where中写最后的过滤条件，外连接的过滤条件不一定能得到我们想要的结果。鉴于上面的原理：当两表查询时，如果有一个表中的的数据特别大，另一个表较小，如（指标表和数据表）指标表一般只有几十条，而数据表有几千万条数据（记录每天的数据），

4、在这种情况的下，我们应该在on中添加一个时间的过滤条件，或添加一个子查询过滤掉不要的数据，再做连接。这样就变成了两个数据量比较小的表进行连接，再不是和一个几千万条数据进行连接后再用where语句去过滤掉大量不要的数据。其次，再说having 和where 两者的区别 ,查询过程中的聚合语句（sum ,min,max,avg,count）要比having子句优先执行，而where子句在查询过中的执行顺序要优先于聚合函数（sum ,min,max,avg,count）。Having语句是为了弥补where在分组数据判断的不足（where 执行级别要快于聚合语句）。不要使用select * from

5、 ，在select 后面，指明列表中要检索的列，不然DB2 会为被请求返回的每一列消耗附加资源。如果程序不需要数据，它就不会寻找它。即程序需要的每一列，最好根据 SQL 语句中的名称来显式地寻找每一列，以便增加清晰度和避免以前犯的错误。不要查找已经知道的东西 。如：SELECT EMPNO, LASTNAME, SALARYFROM EMPWHERE EMPNO = 000010; 已经知道EMPNO的值，就不要查询列表中加上EMPNO这一个字段了。因为即使在 WHERE 子句中列出了 EMPNO，DB2 还会尽职地检索该列。这会产生附加开销，从而降低性能。 2 临时表（with temp a

6、s ）使用临时表可使SQL结构清淅，同时加快查询速度。使用WITH AS 语句可以为一个子查询语句块定义一个名称，这样通过引用这个名称，我们可以在后面的SQL中多处使用到这个子查询。我们可以在任何一个顶层的SELECT 语句以及几乎所有类型的子查询语句前，使用子查询定义子句。被定义的子查询名称可以在主查询语句以及所有的子查询语句中引用，但未定义前不能引用。With 子句不能嵌套定义，但是with子句可以引用前面已经定义好的with子句。下面是with子句相关总结：1.当一个子查询在一段SQL中出现多次时，我们可以用with子句给其定义一个名字，方便在select查询模块中调用。With子句的定

7、义应在引用的select语句之前定义。2.with子句的返回结果存到用户的临时表空间中，只做一次查询，反复使用,提高sql的执行效率。3.在同级select前有多个查询定义的时候，第1个用with，后面的不用with，并且用逗号隔开。如下（注：temp后面的字段名可不要）：with temp(itemcode ,basecode ,isbase) as ( Select itemcode ,basecode ,isbase from I_BR_SREX),temp1(itemcode ,basecode ,isbase) as ( Select itemcode ,basecode ,isba

8、se from I_BR_SREX)Select * from temp union allSelect * from temp14.最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来，如上例。 5.前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句。如下例中的temp在temp1中被引用：with temp(itemcode ,basecode ,isbase) as ( Select itemcode ,basecode ,isbase from I_BR_SREX),temp1(i

9、temcode ,basecode ,isbase) as (Select itemcode ,basecode ,isbase from temp)Select * from temp union allSelect * from temp1 6.with查询的结果列有别名，引用的时候必须使用别名或*。7 with子句语法：With 别名1 as (select1),别名2 as (select2), 别名3 as (select n) Select .注：1)as和select中的括号都不能省略2)with只在最开始出现一次，多个临时表用逗号分割，同一个主查询同级别地方。3）最后定义的个临

10、时表与下面的实际查询之间没有逗号With as 使用的例子:1）.一般使用方式如查询销售部门员工的姓名：with a as(select id from s_dept where name=Sales order by id)select last_name,title from s_emp where dept_id in (select * from a);-使用select查询别名使用with 子句，可以在复杂的查询中预先定义好一个结果集，然后在查询中反复使用，不使用不会报错（在db2中不使用不会报错）。 2 ） 下面是一个with查询的例子。查询出部门的总薪水大于所有部门平均总薪水的部

11、门。部门表s_dept，员工表s_emp。分析：做这个查询，首先必须计算出所有部门的总薪水，然后计算出总薪水的平均薪水，再筛选出部门的总薪水大于所有部门总薪水平均薪水的部门。那么第1 步with 查询查出所有部门的总薪水，第2 步用with 从第1 步获得的结果表中查询出平均薪水，最后利用这两次的with 查询比较总薪水大于平均薪水的结果，如下：with-step1:查询出部门名和部门的总薪水dept_costs as(select a.name,sum(b.salary) dept_totalfroms_dept a,s_emp bwhere a.id=b.dept_idgroup by a

12、.name),-step2:利用上一个with查询的结果，计算部门的平均总薪水avg_costs as(select sum(dept_total)/count(*) dept_avgfrom dept_costs)-step3:从两个with查询中比较并且输出查询结果select name,dept_totalfrom dept_costswheredept_total(select dept_avgfromavg_costs)order by name;上面的例子中with的使用让SQL的逻辑更加清淅。下面说一下在做报表中用到的例子。 如上表样，每个部门前序号的大小是根据后面数值1，数值2

13、，数值3中任意一个的小计的值来决定的。我们可以把序号，部门，小计这三个字段放在一个临时表中，然后再后面左连接，连接（unoin all）就可以很容易得到上面的的表。总结临时表的优点如下：1. SQL可读性增强，让SQL的逻辑结构更加清淅。比如对于特定with子查询取个有意义的名字，增强代码的模块化。2. with子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能。3 索引 索引是表的一个或多个列的键值的有序列表。创建索引的原因有两个：一，确保一个或多个列中值的唯一性。二，提高对表进行的查询的性能。当执行查询想以更快的速度找到所需的列时，或要以索引的顺序显示查询结果时，DB2

14、优化器选择使用索引。如果表上不存在索引，那么必须对SQL查询中引用的每个表执行表扫描。表越大，表扫描所花的时间越长，因为表扫描需要顺序访问每个表行。虽然对于需要表中的大多数行的复杂查询来说，使用表扫描效率可能更高，但是对于只返回部分表行的查询而言，使用索引扫描可以更有效地访问表行。如果在SELECT语句中引用了索引列，并且优化器估计索引扫描比表扫描快，那么优化器选择索引扫描。索引文件一般较小，因此读取它所需的时间比读取整个表所需的时间要少，尤其在表增大时更是如此。索引的定义：是从表中抽取一个或多个列键值的有序列表。 一 ，索引的创建：1、 单列索引 单列索引是基于单个列所建立的索引。 sqlc

15、reate index 索引名 on 表名(列名); 2、 复合索引 复合索引是基于两列或是多列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同。 create index 索引名 on 表名(列名1，列名2); 二十、使用索引的原则 ： 1、在大表上建立索引才有意义。 2、在where子句或是连接条件上经常引用的列上建立索引。 3，一个表中如果建有大量索引会影响到insert,delete语句的性能，对于update，如果更新的列不是索引的列，刚不会有影响。因为表中的数据在更改时，所有的索引都要进行适当的修改。 所以，建索引会有利于数据的查询，但会引响数据的更新。不恰当的索引不但

16、没有起到它应有的作用，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。 比如对下列情况的表建索引： 1、很少或从不引用的字段。 2、逻辑型的字段，如男或女（是或否）等。综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立。 4 自定义函数 好的函数可以让我们的sql代码逻辑更加清淅，功能更加强大，当我们的报表要用查询环比数据时，db2的日期函数，是必须要用到。仅仅db2本身的函数有时可能不能滿足我们的需求，这时，我们可以自定义函数，满足业务需求。 Db2的本身自带的函数是相当多的，如下对于日期函数DAYNAME(2011-01-0

17、1) 返回一个大小写混合的字符串，对于参数的日部分，用星期表示这一天的名称（例如，SATURDAY）。 DAYOFWEEK 返回参数中的星期几，用范围在 1-7 的整数值表示，其中 1 代表星期日。 DAYOFYEAR 返回参数中一年中的第几天，用范围在 1-366 的整数值表示。 DAYS 返回日期的整数表示。 给定了日期、时间或时间戳记，则使用适当的函数可以单独抽取出（如果适用的话）年、月、日、时、分、秒及微秒各部分： YEAR (current timestamp) MONTH (current timestamp) DAY (current timestamp) HOUR (curre

18、nt timestamp) MINUTE (current timestamp) SECOND (current timestamp) MICROSECOND (current timestamp) 当在SQL中要查询环比时，我们可用如下函数：current date + 1 YEAR current date + 3 YEARS + 2 MONTHS + 15 DAYS current time + 5 HOURS - 3 MINUTES + 10 SECONDS日期类型数据我们的可以直接用date (2011-01-01)字符串转换为日期。Char(date)日期转换为字符串。但对于数据如

19、(20020202)这类格式的数据，db2就不能强转了。我们可以自定义函数：CREATE FUNCTION chartodate(yr char(8) RETURNS date RETURN date(substr(yr ,1,4)|-|substr(yr ,5,2)|-|substr(yr ,7,2)自定义函数有三种，分别是标量函数，行函数，表函数。这三种函数分别是根据函数返回值的类型而定的。创建一个函数的结构为：Create function functionName(参数)Return 返回值类型 SQL-function-body 函数的逻辑，核心。两个简单函数的例子如下：1 ，简单的

20、标量函数 CREATE FUNCTION chartodate(yr char(8) RETURNS date RETURN date(substr(yr ,1,4)|-|substr(yr ,5,2)|-|substr(yr ,7,2)2 表函数CREATE FUNCTION deptemployees1 (deptno CHAR(3) RETURNS TABLE (itemcode CHAR(6), basecode VARCHAR(15) ) RETURN SELECT itemcode, basecode FROM I_BR_SREX5 存诸过程有时由于查询条件的复杂，要涉及到多个表连

21、接，同时用一个Sql不能解决时，或者用一个SQL时查询时，给前台用户的体验很慢时，我们可以使用存诸过程。首先：我们须新建一个表，用于存放存储过程的数据。然后创建存储过程，通过调用存储过程，向表中插入数据。存储过程的结构如上：CREATE PROCEDURE 名字(参数1，参数2) -存储过程可以设定输入参数和输出参数 LANGUAGE SQL -DB2可以用多种语言编写存储过程，这里用的是纯SQLBEGIN -开始 DECLARE var varchar(8); -定义变量的数据类型; FOR V AS SELECT 表字段 FROM 表名where 字段=参数DO -循环体开始 SET va

22、r=表字段; -对var赋值INSERT INTO 表名VALUES(var ); -往事先建好的表中插入数据END FOR; -循环体结束 END -存储过程结束下面是建一个存储过程的具休操作：1 创建一个表RMBCKFL_TABLE，用于存放存储过程插入的数据：2 创建存储过程：create PROCEDURE db2iltbb.rmbckfl_proc(in mydate varchar(20)LANGUAGE SQLbeginDECLARE DATADATE varchar(20);DECLARE one varchar(20);DECLARE two varchar(20);DECL

23、ARE val DECIMAL(20,2);DECLARE type varchar(10);FOR V AS select aa.name1 as one , aa.chdeptname as two ,sum (bb.val) as val ,bb.type from (select d.name1 , d.chdeptcode ,d.chdeptname ,a.chauditorcode ,a.chauditorname from (select a.name1 ,b.code2 as chdeptcode ,b.name2 as chdeptname from (select chde

24、ptcode as code1 ,chdeptname as name1 from department where chparentdept =2611999 )a left join (select chdeptcode as code2 ,chdeptname as name2 ,chparentdept as parentcode from department where chparentdept in (select chdeptcode from department where chparentdept =2611999) order by code2)b on b.paren

25、tcode =a.code1) as d ,auditor as a where a.chdeptcode =d.chdeptcode order by chdeptcode )aa left join (select zb_val as val ,zb_id as type ,chauditorcode from tb_zb_data where zb_id in (M9990100,M9990098,M9990113) and zb_val 0 and data_date =mydate)bb on aa.chauditorcode =bb.chauditorcode group by a

26、a.name1, bb.type ,bb.datadate,aa.chdeptname DO SET DATADATE = mydate;SET one = V.one;SET two = V.two;SET type = V.type;SET val = V.val;Insert into RMBCKFL_TABLE values(one ,two,datadate,type ,val );END FOR;END3 将代码保存为，存放于E:proc_db2下面。4 在Windows下，进入命令模式，然后输入 db2cmd 就会进入db2 命令模式.5 用connect命令连接数据库，然后输入

27、 db2 -td -vf E:db2prormbckfl_proc.db2 就可以完成存储过程的创建。 6 总结在做报表过程中，为了确保数据的正确性，注重SQL代码的结构和可读性是必须。临时表和函数主是要让我们的代码整体看起来结构清淅。同时临时表存储数据的功能可加快SQL的执行速度。了解SQL的执行顺序，主要是为在做多表连接时，我们最好先删除大量不要的数据再做表连接。索引主要是用于加快SQL的查询速度，同时索引的使用慎重，使用不当会适得其反。存储过程是为了解决非相当复杂的逻辑结构的SQL。同时对于由于多表连接而导致SQL查询较慢的，为了提高用户体验，也可以使用存储过程。总的来说，以上几点各有各的用处，我们在编写代码时须根据业务需求灵活运用。