DB2数据库SQL编码.docx
《DB2数据库SQL编码.docx》由会员分享，可在线阅读，更多相关《DB2数据库SQL编码.docx（10页珍藏版）》请在冰豆网上搜索。
DB2数据库SQL编码
Db2数据库SQL编码
摘要
本文主要是关于用SR做报表，编写SQL代码的心得，文章从select语句的执行顺序，临时表，自定义函数，索引，存储过程这几个方面的使用，来讨论如何优化SQL代码，加快速查询速度，增强代码的可读性。
其中select语句的执行顺序和索引主要是为了加快SQL的查询速度。
自定义函数，临时表，存储过程的使用是为了处理逻辑复杂的sql代码，让代码的可读性加强。
关键字：
SQL查询速度可读性
1SQL执行顺序
根据SQL的执行顺序和表数据具体情况，在多表边接之前删除多余的数据，加快查询速度。
详解如下：
从sql的执行方式的来优化SQL代码的执行速度。
首先，看看where,groupby,orderby等的执行顺序。
Sql语句的执行顺序其实是如下的:
SelectA.id,COUNT（B.字段）ASnum
6
FROMtable1ASA
1
LEFTOUTERJOINtable2ASB
2
ONA.id=A.id
3
WHEREA.字段=条件
4
GROUPBYA.字段
5
HAVINGCOUNT（B.字段）<3
7
ORDERBYnum
8
首先，on和where两者的区别：
上面的一段SQL是按标号的顺序执行的，SELECT的执行顺序是放在最后的，而FROM则是第一步，并且是两个表先做外连接，然后通过ON滤掉不符合条件的结果。
注意，这里写在on后面的过滤条件和where后面的过滤条件的是不同的，主要是根据连接的类型而定（例如:
FROMTableALEFTJOINTableBONTableA.DATE<>'1981-01-01'当做完LEFT连接后不符合条件的TableA表中的记录是会添加进来的，因为这里使用的是LFETJOIN）。
所以，因此在我们在写sql时应该在where中写最后的过滤条件，外连接的过滤条件不一定能得到我们想要的结果。
鉴于上面的原理：
当两表查询时，如果有一个表中的的数据特别大，另一个表较小，如（指标表和数据表）指标表一般只有几十条，而数据表有几千万条数据（记录每天的数据），在这种情况的下，我们应该在on中添加一个时间的过滤条件，或添加一个子查询过滤掉不要的数据，再做连接。
这样就变成了两个数据量比较小的表进行连接，再不是和一个几千万条数据进行连接后再用where语句去过滤掉大量不要的数据。
其次，再说having和where两者的区别,查询过程中的聚合语句（sum,min,max,avg,count）要比having子句优先执行，而where子句在查询过中的执行顺序要优先于聚合函数（sum,min,max,avg,count）。
Having语句是为了弥补where在分组数据判断的不足（where执行级别要快于聚合语句）。
不要使用select*from，在select后面，指明列表中要检索的列，不然DB2会为被请求返回的每一列消耗附加资源。
如果程序不需要数据，它就不会寻找它。
即程序需要的每一列，最好根据SQL语句中的名称来显式地寻找每一列，以便增加清晰度和避免以前犯的错误。
不要查找已经知道的东西。
如：
SELECTEMPNO,LASTNAME,SALARY FROMEMP WHEREEMPNO='000010';已经知道EMPNO的值，就不要查询列表中加上EMPNO这一个字段了。
因为即使在WHERE子句中列出了EMPNO，DB2还会尽职地检索该列。
这会产生附加开销，从而降低性能。
2临时表（withtempas）
使用临时表可使SQL结构清淅，同时加快查询速度。
使用WITHAS语句可以为一个子查询语句块定义一个名称，这样通过引用这个名称，我们可以在后面的SQL中多处使用到这个子查询。
我们可以在任何一个顶层的SELECT语句以及几乎所有类型的子查询语句前，使用子查询定义子句。
被定义的子查询名称可以在主查询语句以及所有的子查询语句中引用，但未定义前不能引用。
With子句不能嵌套定义，但是with子句可以引用前面已经定义好的with子句。
下面是with子句相关总结：
1.当一个子查询在一段SQL中出现多次时，我们可以用with子句给其定义一个名字，方便在select查询模块中调用。
With子句的定义应在引用的select语句之前定义。
2.with子句的返回结果存到用户的临时表空间中，只做一次查询，反复使用,提高sql的执行效率。
3.在同级select前有多个查询定义的时候，第1个用with，后面的不用with，并且用逗号隔开。
如下（注：
temp后面的字段名可不要）：
withtemp（itemcode,basecode,isbase）as（Selectitemcode,basecode,isbasefromI_BR_SREX）,
temp1（itemcode,basecode,isbase）as（Selectitemcode,basecode,isbasefromI_BR_SREX）
Select*fromtemp
unionall
Select*fromtemp1
4.最后一个with子句与下面的查询之间不能有逗号，只通过右括号分割,with子句的查询必须用括号括起来，如上例。
5.前面的with子句定义的查询在后面的with子句中可以使用。
但是一个with子句内部不能嵌套with子句。
如下例中的temp在temp1中被引用：
withtemp（itemcode,basecode,isbase）as（
Selectitemcode,basecode,isbasefromI_BR_SREX）,
temp1（itemcode,basecode,isbase）as（Selectitemcode,basecode,isbasefromtemp）
Select*fromtemp
unionall
Select*fromtemp1
6.with查询的结果列有别名，引用的时候必须使用别名或*。
7with子句语法：
With别名1as（select1）,
别名2as（select2）,
…
别名3as（selectn）
Select….
注：
1）as和select中的括号都不能省略
2）with只在最开始出现一次，多个临时表用逗号分割，同一个主查询同级别地方。
3）最后定义的个临时表与下面的实际查询之间没有逗号
Withas使用的例子:
1）.一般使用方式
如查询销售部门员工的姓名：
withaas
（selectidfroms_deptwherename=Salesorderbyid）
selectlast_name,titlefroms_empwheredept_idin（select*froma）;--使用select查询别名
使用with子句，可以在复杂的查询中预先定义好一个结果集，然后在查询中反复使用，不使用不会报错（在db2中不使用不会报错）。
2）下面是一个with查询的例子。
查询出部门的总薪水大于所有部门平均总薪水的部门。
部门表s_dept，员工表s_emp。
分析：
做这个查询，首先必须计算出所有部门的总薪水，然后计算出总薪水的平均薪水，再筛选出部门的总薪水大于所有部门总薪水平均薪水的部门。
那么第1步with查询查出所有部门的总薪水，第2步用with从第1步获得的结果表中查询出平均薪水，最后利用这两次的with查询比较总薪水大于平均薪水的结果，如下：
with --step1:
查询出部门名和部门的总薪水
dept_costsas（
selecta.name,sum（b.salary）dept_total
from s_depta,s_empb
wherea.id=b.dept_id
groupbya.name）,
--step2:
利用上一个with查询的结果，计算部门的平均总薪水
avg_costsas（
selectsum（dept_total）/count（*）dept_avg
fromdept_costs ）
--step3:
从两个with查询中比较并且输出查询结果
selectname,dept_total
fromdept_costs where dept_total>
（ selectdept_avg from avg_costs ）
orderbyname;
上面的例子中with的使用让SQL的逻辑更加清淅。
下面说一下在做报表中用到的例子。
如上表样，每个部门前序号的大小是根据后面数值1，数值2，数值3中任意一个的小计的值来决定的。
我们可以把序号，部门，小计这三个字段放在一个临时表中，然后再后面左连接，连接（unoinall）就可以很容易得到上面的的表。
总结临时表的优点如下：
1.SQL可读性增强，让SQL的逻辑结构更加清淅。
比如对于特定with子查询取个有意义的名字，增强代码的模块化。
2.with子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能。
3索引
索引是表的一个或多个列的键值的有序列表。
创建索引的原因有两个：
一，确保一个或多个列中值的唯一性。
二，提高对表进行的查询的性能。
当执行查询想以更快的速度找到所需的列时，或要以索引的顺序显示查询结果时，DB2优化器选择使用索引。
如果表上不存在索引，那么必须对SQL查询中引用的每个表执行表扫描。
表越大，表扫描所花的时间越长，因为表扫描需要顺序访问每个表行。
虽然对于需要表中的大多数行的复杂查询来说，使用表扫描效率可能更高，但是对于只返回部分表行的查询而言，使用索引扫描可以更有效地访问表行。
如果在SELECT语句中引用了索引列，并且优化器估计索引扫描比表扫描快，那么优化器选择索引扫描。
索引文件一般较小，因此读取它所需的时间比读取整个表所需的时间要少，尤其在表增大时更是如此。
索引的定义：
是从表中抽取一个或多个列键值的有序列表。
一，索引的创建：
1、单列索引
单列索引是基于单个列所建立的索引。
sql>createindex索引名on表名（列名）;
2、复合索引
复合索引是基于两列或是多列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同。
createindex索引名on表名（列名1，列名2）;
二十、使用索引的原则：
1、在大表上建立索引才有意义。
2、在where子句或是连接条件上经常引用的列上建立索引。
3，一个表中如果建有大量索引会影响到insert,delete语句的性能，对于update，如果更新的列不是索引的列，刚不会有影响。
因为表中的数据在更改时，所有的索引都要进行适当的修改。
所以，建索引会有利于数据的查询，但会引响数据的更新。
不恰当的索引不但没有起到它应有的作用，反而会降低系统性能。
因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。
比如对下列情况的表建索引：
1、很少或从不引用的字段。
2、逻辑型的字段，如男或女（是或否）等。
综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立。
4自定义函数
好的函数可以让我们的sql代码逻辑更加清淅，功能更加强大，当我们的报表要用查询环比数据时，db2的日期函数，是必须要用到。
仅仅db2本身的函数有时可能不能滿足我们的需求，这时，我们可以自定义函数，满足业务需求。
Db2的本身自带的函数是相当多的，如下对于日期函数
DAYNAME（'2011-01-01'）返回一个大小写混合的字符串，对于参数的日部分，用星期表示这一天的名称（例如，SATURDAY）。
DAYOFWEEK返回参数中的星期几，用范围在1-7的整数值表示，其中1代表星期日。
DAYOFYEAR返回参数中一年中的第几天，用范围在1-366的整数值表示。
DAYS返回日期的整数表示。
给定了日期、时间或时间戳记，则使用适当的函数可以单独抽取出（如果适用的话）年、月、日、时、分、秒及微秒各部分：
YEAR（currenttimestamp）
MONTH（currenttimestamp）
DAY（currenttimestamp）
HOUR（currenttimestamp）
MINUTE（currenttimestamp）
SECOND（currenttimestamp）
MICROSECOND（currenttimestamp）
当在SQL中要查询环比时，我们可用如下函数：
currentdate+1YEAR
currentdate+3YEARS+2MONTHS+15DAYS
currenttime+5HOURS-3MINUTES+10SECONDS
日期类型数据我们的可以直接用date（‘2011-01-01’）字符串转换为日期。
Char（date）日期转换为字符串。
但对于数据如（‘20020202’）这类格式的数据，db2就不能强转了。
我们可以自定义函数：
CREATEFUNCTIONchartodate（yrchar（8））
RETURNSdate
RETURN
date（substr（yr,1,4）||'-'||substr（yr,5,2）||'-'||substr（yr,7,2））
自定义函数有三种，分别是标量函数，行函数，表函数。
这三种函数分别是根据函数返回值的类型而定的。
创建一个函数的结构为：
CreatefunctionfunctionName（参数）
Return返回值类型
SQL-function-body函数的逻辑，核心。
两个简单函数的例子如下：
1，简单的标量函数
CREATEFUNCTIONchartodate（yrchar（8））
RETURNSdate
RETURN
date（substr（yr,1,4）||'-'||substr（yr,5,2）||'-'||substr（yr,7,2））
2表函数
CREATEFUNCTIONdeptemployees1（deptnoCHAR（3））
RETURNSTABLE（itemcodeCHAR（6）,
basecodeVARCHAR（15）
）
RETURN
SELECTitemcode,basecode
FROMI_BR_SREX
5存诸过程
有时由于查询条件的复杂，要涉及到多个表连接，同时用一个Sql不能解决时，或者用一个SQL时查询时，给前台用户的体验很慢时，我们可以使用存诸过程。
首先：
我们须新建一个表，用于存放存储过程的数据。
然后创建存储过程，通过调用存储过程，向表中插入数据。
存储过程的结构如上：
CREATEPROCEDURE名字（参数1，参数2）---存储过程可以设定输入参数和输出参数
LANGUAGESQL----DB2可以用多种语言编写存储过程，这里用的是纯SQL
BEGIN---开始
DECLAREvarvarchar（8）;---定义变量的数据类型;
FORVASSELECT表字段FROM表名where字段=参数
DO---循环体开始
SETvar=表字段;---对var赋值
INSERTINTO表名VALUES（var）;---往事先建好的表中插入数据
ENDFOR;-----循环体结束
END@-----存储过程结束
下面是建一个存储过程的具休操作：
1创建一个表RMBCKFL_TABLE，用于存放存储过程插入的数据：
2创建存储过程：
createPROCEDUREdb2iltbb.rmbckfl_proc（inmydatevarchar（20））
LANGUAGESQL
begin
DECLAREDATADATEvarchar（20）;
DECLAREonevarchar（20）;
DECLAREtwovarchar（20）;
DECLAREvalDECIMAL（20,2）;
DECLAREtypevarchar（10）;
FORVASselectaa.name1asone,aa.chdeptnameastwo,sum（bb.val）asval,bb.typefrom（selectd.name1,d.chdeptcode,d.chdeptname,a.chauditorcode,a.chauditornamefrom（selecta.name1,b.code2aschdeptcode,b.name2aschdeptnamefrom（selectchdeptcodeascode1,chdeptnameasname1fromdepartmentwherechparentdept='2611999'）aleftjoin
（selectchdeptcodeascode2,chdeptnameasname2,chparentdeptasparentcodefromdepartmentwherechparentdeptin（selectchdeptcodefromdepartmentwherechparentdept='2611999'）orderbycode2）bonb.parentcode=a.code1）asd,auditorasawherea.chdeptcode=d.chdeptcodeorderbychdeptcode）aaleftjoin（selectzb_valasval,zb_idastype,chauditorcodefromtb_zb_datawherezb_idin（'M9990100','M9990098','M9990113'）andzb_val<>0anddata_date=mydate）bbonaa.chauditorcode=bb.chauditorcodegroupbyaa.name1,bb.type,bb.datadate,aa.chdeptname
DOSETDATADATE=mydate;
SETone=V.one;
SETtwo=V.two;
SETtype=V.type;
SETval=V.val;
InsertintoRMBCKFL_TABLEvalues（one,two,datadate,type,val）;
ENDFOR;
END@
3将代码保存为
，存放于E:
\proc_db2下面。
4在Windows下，进入命令模式，然后输入db2cmd就会进入db2命令模式.
5用connect命令连接数据库，然后输入db2-td@-vfE:
\db2pro\rmbckfl_proc.db2就可以完成存储过程的创建。
6总结
在做报表过程中，为了确保数据的正确性，注重SQL代码的结构和可读性是必须。
临时表和函数主是要让我们的代码整体看起来结构清淅。
同时临时表存储数据的功能可加快SQL的执行速度。
了解SQL的执行顺序，主要是为在做多表连接时，我们最好先删除大量不要的数据再做表连接。
索引主要是用于加快SQL的查询速度，同时索引的使用慎重，使用不当会适得其反。
存储过程是为了解决非相当复杂的逻辑结构的SQL。
同时对于由于多表连接而导致SQL查询较慢的，为了提高用户体验，也可以使用存储过程。
总的来说，以上几点各有各的用处，我们在编写代码时须根据业务需求灵活运用。
升级会员 