BoEing 发布 规范 DB数据库开发规范PPT教学课件

1、BoEing_DB2BoEing_DB2开发规范开发规范DB2 SQL 语句编写规范语句编写规范1DB2 数据类型应用规范数据类型应用规范2DB2 数据库索引应用规范数据库索引应用规范3第2页/共77页第1页/共77页BoEing_DB2BoEing_DB2开发规范开发规范DB2 数据库控制语言数据库控制语言1DB2 数据库定义语言数据库定义语言2DB2 数据库操作语言数据库操作语言3DB2 数据库常用函数数据库常用函数4DB2 数据库操作建议数据库操作建议5第3页/共77页第2页/共77页BoEing_DB2BoEing_DB2开发规范开发规范第4页/共77页第3页/共77页DB2 DB2

2、数据库控制语言数据库控制语言数据控制语言：简称数据控制语言：简称DCL（Data Control Language）。主要用于对数据库操作权）。主要用于对数据库操作权限的控制，包括限的控制，包括GRANT（授权）语句和（授权）语句和REVOKE（回收权限）语句。（回收权限）语句。 主要包括对主要包括对COLLECTION、DATABASE、PACKAGE、TABLE、VIEW、USER等的权限管理。等的权限管理。 GRANT privilege ON object_type object_name to USER|GROUP|PUBLIC With grant option;REVOKE pr

3、ivilege ON object_type object_name from USER|GROUP|PUBLIC;给所有用户授予绑定给所有用户授予绑定(BIND)权限：权限：GRANTBINDADDTOPUBLIC;GRANTPACKADMONCOLLECTION*TOPUBLIC;给某个用户授予一个数据库管理员的权限：给某个用户授予一个数据库管理员的权限：GRANTDBADMONDATABASExxxxxxxxTOBJAPxxxWITHGRANTOPTION;将表授权给某个用户将表授权给某个用户GRANTALL|SELECT|UPDATE|INSERT|DELETEONTABLEtable

4、nameTOBJAPxxx;给某一用户授予系统管理员权限给某一用户授予系统管理员权限:GRANTSYSADMTOBJAPxxx;第5页/共77页第4页/共77页BoEing_DB2BoEing_DB2开发规范开发规范DB2 数据库控制语言数据库控制语言1DB2 数据库定义语言数据库定义语言2DB2 数据库操作语言数据库操作语言3DB2 数据库常用函数数据库常用函数4DB2 数据库操作建议数据库操作建议5第6页/共77页第5页/共77页BoEing_DB2BoEing_DB2开发规范开发规范DB2 数据库控制语言数据库控制语言1DB2 数据库定义语言数据库定义语言2DB2 数据库操作语言数据库操

5、作语言3DB2 数据库常用函数数据库常用函数4DB2 数据库操作建议数据库操作建议5第7页/共77页第6页/共77页DB2 DB2 数据库定义语言数据库定义语言_CREATE_CREATECREATECREATE用于创建数据库对象，主要包括：用于创建数据库对象，主要包括： 数据库（数据库（DATABASEDATABASE） 表空间（表空间（TABLE SPACETABLE SPACE） 表（表（TABLETABLE） 触发器（触发器（TRIGGERTRIGGER） 视图（视图（VIEWVIEW） 索引（索引（INDEXINDEX）第8页/共77页第7页/共77页ALTERALTER语句可以用来

6、改变现有数据库对象的一些特性，包括：语句可以用来改变现有数据库对象的一些特性，包括： 数据库（数据库（DATABASEDATABASE） 表空间（表空间（TABLE SPACETABLE SPACE） 表（表（TABLETABLE） 触发器（触发器（TRIGGERTRIGGER） 视图（视图（VIEWVIEW） 索引（索引（INDEXINDEX）DB2 DB2 数据库定义语言数据库定义语言_ALTER_ALTER第9页/共77页第8页/共77页DROPDROP语句可以删除任何语句可以删除任何CREATECREATE语句创建的数据库对象。语句创建的数据库对象。DROPDROP语句将在删除数据库对

7、语句将在删除数据库对象的同时也删除系统目录中关于该对象的定义。由于数据库对象之间可能存在某些依象的同时也删除系统目录中关于该对象的定义。由于数据库对象之间可能存在某些依赖关系，所以删除对象可能会使有关的对象变成无效的状态。赖关系，所以删除对象可能会使有关的对象变成无效的状态。DB2 DB2 数据库定义语言数据库定义语言_DROP_DROP第10页/共77页第9页/共77页DB2DB2支持的数据类型支持的数据类型第11页/共77页第10页/共77页表创建的一个实例表创建的一个实例 CREATE TABLE EMP_INFO CREATE TABLE EMP_INFO (EMPNO INTEGER

8、 GENERATED ALWAYS (EMPNO INTEGER GENERATED ALWAYS AS IDENTITYAS IDENTITY, , EMP_INFOCHANGE NOT NULL EMP_INFOCHANGE NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP AS ROW CHANGE TIMESTAMP, , EMP_ADDRESS VARCHAR(300), EMP_ADDRESS VARCHAR

9、(300), EMP_PHONENO CHAR(4), EMP_PHONENO CHAR(4), PRIMARY KEY PRIMARY KEY (EMPNO)(EMPNO)第12页/共77页第11页/共77页BoEing_DB2BoEing_DB2开发规范开发规范DB2 数据库控制语言数据库控制语言1DB2 数据库定义语言数据库定义语言2DB2 数据库操作语言数据库操作语言3DB2 数据库常用函数数据库常用函数4DB2 数据库操作建议数据库操作建议5第13页/共77页第12页/共77页BoEing_DB2BoEing_DB2开发规范开发规范DB2 数据库控制语言数据库控制语言1DB2 数据库

10、定义语言数据库定义语言2DB2 数据库操作语言数据库操作语言3DB2 数据库常用函数数据库常用函数4DB2 数据库操作建议数据库操作建议5第14页/共77页第13页/共77页DB2 DB2 数据库操作语言数据库操作语言_SELECT_SELECT（1）SELECT（主要用于检索表或视图数据）（主要用于检索表或视图数据）检索全表所有数据，如SELECT*FROMTABLE_NAME；FETCHFIRST：用于限制结果集显示的行数，如SELECT*FROMTABLE_NAMEFETCHFIRSTnumberROWSONLY；通过指定选择列表并用逗号分隔列名，可检索指定的列，如SELECTcolum

11、n_name_1,column_name_2FROMTABLE_NAME；使用DISTINCT子句来排除结果集中重复的记录行信息，如SELECTDISTINCTcolumn_nameFROMTABLE_NAME；使用AS子句为选择列表上的表达式或项指定一个有意义的名字，如：SELECTcolumn_name_1,column_name_2ASother_nameFROMTABLE_NAME；WITHUR：用于指定使用只读方式查询数据。如：SELECT*FROMTABLE_NAMEWITHUR。第15页/共77页第14页/共77页DB2 DB2 数据库操作语言数据库操作语言_INSERT_INS

12、ERT（2）INSERT（主要用于向表或视图中添加新行）（主要用于向表或视图中添加新行）INSERT语句可以用于向表或视图中添加新行。向视图中插入一行，那么这一行也会被插入到该视图所基于的表中。可以使用VALUES子句来指定一行或多行的列数据。如：INSERTINTOTABLE_NAMEcolumn_name_1,column_name_2VALUES（column_value_1,column_value_2）；第16页/共77页第15页/共77页DB2 DB2 数据库操作语言数据库操作语言_UPDATE_UPDATE（3）UPDATE（主要用于改变表或视图中的数据）（主要用于改变表或视图中

13、的数据）UPDATE语句用于改变表或视图中的数据，用户可以改变满足WHERE子句指定条件的每一行中的一列或多列的值。如果不指定WHERE语句，则DB2会更新表或视图中的每一行数据信息。UPDATETABLE_NAMESETcolumn_name_1=value_new_1，column_name_2=value_new_2WHEREcolumn_name_1=value_old_1andcolumn_name_2=value_old_2第17页/共77页第16页/共77页DB2 DB2 数据库操作语言数据库操作语言_DELETE_DELETE（4）DELETE（主要用于从表中删除整行数据）（主

14、要用于从表中删除整行数据）DELETE语句用于从表中删除整行数据。用户可以删除满足WHERE子句指定条件的每一行。若不指定WHERE子句，则DB2将删除表或视图中的所有记录行。DELETEFROMTABLE_NAMEWHEREcolumn_name=value。第18页/共77页第17页/共77页DB2 DB2 数据库操作语言数据库操作语言_WHERE_WHERE（5）WHERE子句（主要用于指定数据选择的范围条件）子句（主要用于指定数据选择的范围条件）通过WHERE子句可以指定若干选择条件或搜索条件，用来在表或视图中选择某些特定行。搜索条件由一个或多个谓词组成。谓词指定了行的某种条件，它可能

15、是TRUE或FALSE。建立搜索条件时需要注意：只对数值型数据类型应用算术运算；只在兼容数据类型间进行比较；字符型的值需要用单引号；%和_是DB2中的字符通配符，%可以代表多个字符，_代表单个字符。第19页/共77页第18页/共77页DB2 DB2 数据库操作语言数据库操作语言_ORDER BY_ORDER BY（6） ORDER BY子句（主要用于对结果集进行排序）子句（主要用于对结果集进行排序）使用ORDERBY子句根据一列或多列中的值对结果集进行排序。ORDERBY子句中指定的列名不一定要在选择的列表中指定。可以在ORDERBY子句中指定DESC以降序排列数据结果集；若不指定或指定ASC

16、，则是以升序排列数据结果集。【注意】DB2V9另外一个新的程序特性是可以使用SQLORDERBY加FETCHFIRSTnROWS进行子选择子选择和全选择。这个特性使得程序能够快速获取少量适当排序的行。例如：如果只需要获取排序数据集中的一部分数据，则SQL语句如下：SELECTCOLUMN_NAME_1,COLUMN_NAME_NFROMTABLE_NAME_1WHERECOLUMN_NAME_3IN(SELECTCOLUMN_NAME_5FROMTABLE_NAME_2ORDERBYCOLUMN_NAME_6DESCFETCHFIRST3ROWSONLY)第20页/共77页第19页/共77页D

17、B2 DB2 数据库操作语言数据库操作语言_JOIN_JOIN（7）JOIN子句（主要用于对多个表的数据进行联合查询）子句（主要用于对多个表的数据进行联合查询）JOIN连接是指把两个或两个以上的表中数据组合在一起进行查询。连接生成的结果集包含了多个表的列。 全连接（又称笛卡儿积）全连接（又称笛卡儿积）全连接是最基本的连接，但并不实用。如：SELECT*FROMTABLE_NAME_1,TABLE_NAME_2； 等值连接等值连接等值连接是最常用的一种连接方式，可连接两张或多张表。如：SELECT*FROMTABLE_NAME_1ASA,TABLE_NAME_2ASBWHEREA.column_

18、name_value=B.column_name_value；第21页/共77页第20页/共77页DB2 DB2 数据库操作语言数据库操作语言_JOIN_JOIN 不等值连接不等值连接等值连接是在WHERE子句中使用等号，而不等值连接则是在WHERE子句中使用了除等号以外的其他比较运算符。如：SELECT*FROMTABLE_NAME_1ASA,TABLE_NAME_2ASBwhereA.column_name_valueB.column_name_value； 内连接内连接内连接是只从笛卡儿积中返回满足连接条件的行。如果某一行在一个表中存在，但不在另一张表中，那么结果集中将不包括这一行。为了

19、明确地说明内连接，可以通过在FROM子句中用INNERJOIN操作符及关键字ON为将要连接的表指定连续条件。如SELECT*FROMTABLE_NAME_1ASAJOINTABLE_NAME_2ASBONA.column_name_value=；第22页/共77页第21页/共77页DB2 DB2 数据库操作语言数据库操作语言_JOIN_JOIN 外连接外连接返回的是内连接操作生成的行，以及内连接操作无法返回的行。外连接共有两类，即：左外连接、右外连接、全外连接。p 左外连接包括内连接和在左表中但内连接不会返回的那些行。这类连接在FROM子句中使用LEFTOUTERJOIN（或LEFTJOIN）

20、操作符。如：SELECT*FROMTABLE_NAME_1ALEFTOUTERJOINTABLE_NAME_2BONA.column_name_value=；p 右外连接包括内连接和在右表中但内连接不会返回的那些行。这类连接在FROM子句中使用RIGHTOUTERJOIN（或RIGHTJOIN）操作符。如：SELECT*FROMTABLE_NAME_1ARIGHTOUTERJOINTABLE_NAME_2BONA.column_name_value=；p 全外连接包括内连接和在左表、右表中但内连接不会返回的那些行。这类连接在FROM子句中使用FULLOUTERJOIN（或FULLJOIN）操作

21、符。第23页/共77页第22页/共77页DB2 数据库操作语言数据库操作语言_JOIN & GROUP_ BY 表的自连接表的自连接可以看成是两个相同表之间的直接连接，表的自连接是检查表中数据一致性的很好的办法。如：SELECT*FROMTABLE_NAMEA,TABLE_NAMEBWHEREA.column_name_1=B.column_name_1ANDA.column_name_2B.column_name_2； GROUPBY子句在结果集中可以使用GROUPBY子句来组织行信息，每一组用结果集中的一行来表示。第24页/共77页第23页/共77页 UNION（主要用于集合运算）（主要用

22、于集合运算）可以使用UNION、UNIONALL把两个或两个以上的查询合并成一个查询。UNIONALL与UNION类似，区别是它不删除重复值。UNION集合运算符会把两个或两个以上其他结果集合并生成一个结果集，并且将重复值删除掉。DB2 DB2 数据库操作语言数据库操作语言_UNION_UNION第25页/共77页第24页/共77页 INTERSECT:DCLC1CURSORFOR(SELECTCOL1FROMR1)INTERSECTDISTINCT(SELECTCOL1FROMR2);DOWHILE(SQLCODE=0)FETCHNEXT 传统的方式：DCLC1CURSORFORSELECT

23、DISTINCTCOL1FROMR1WHEREEXISTS(SELECTCOL1FROMR2WHERER1.COL1=R2.COL1);DOWHILE(SQLCODE=0)FETCHNEXT【INTERSECT语句比传统的处理语句在性能上都有显著提高】DB2 DB2 数据库操作语言数据库操作语言_INTERSECT_INTERSECT第26页/共77页第25页/共77页 EXCEPT:DCLC1CURSORFOR(SELECTCOL1FROMR1)EXCEPTDISTINCT(SELECTCOL1FROMR2);DOWHILE(SQLCODE=0)FETCHNEXT传统的方式：DCLC1CUR

24、SORFORSELECTDISTINCTCOL1FROMR1WHERENOTEXISTS(SELECTCOL1FROMR2WHERER1.COL1=R2.COL1);DOWHILE(SQLCODE=0)FETCHNEXT【EXCEPT语句比传统的处理语句在性能上都有显著提高】DB2 DB2 数据库操作语言数据库操作语言_EXCEPT_EXCEPT第27页/共77页第26页/共77页DB2 DB2 数据库操作语言数据库操作语言_ _子查询子查询 非相关子查询和相关子查询：非相关子查询非相关子查询SELECTDEPTNAMEFROMDSN8910.DEPTDWHERED.DEPTNOIN(SELE

25、CTP.DEPTNOFROMDSN8910.PROJP)相关子查询相关子查询SELECTFIRSTNME,LASTNAMEFROMDSN8910.EMPEWHERESALARY=(SELECTMAX(SALARY)FROMDSN8910.EMPE2WHEREE2.WORKDEPT=E.WORKDEPT)第28页/共77页第27页/共77页 HAVING与WHERE的区别从功能角度来看，HAVING和WHERE是类似的，但是他们是对不同类型数据进行操作的。任何一条SQL语句都可以使用WHERE子句来指定要返回的行所需满足的条件。WHERE子句对表、视图、同义词和别名中的数据进行操作。而HAVIN

26、G子句则不同，它对分组的信息进行操作。只有使用了GROUPBY子句的SQL语句才能使用HAVING子句。DB2 DB2 数据库操作语言数据库操作语言_HAVING_HAVING第29页/共77页第28页/共77页 CASE表达式CASE语句根据指定的表达式的值，从多条语句中选择一条来执行。CASE语句常用来替代多个表进行组合的UNION语句。如：Selectcreator,name,TABLEfromsysibm.systableswheretype=Tunionall；Selectcreatot,name,VIEWfromsysibm.systableswheretype=Vunionall

27、；Selectcreator,name,ALIASfromsysibm.systableswheretype=A；上述语句可以用CASE语句编写为如下形式：Selectcreator,name,CASEtypeWhenTthenTABLEWhenVthenVIEWWhenAthenALIASElseOTHERENDFROMsysibm.systables；使用CASE语句代替多个合并操作时，性能可能将有所提高，因为DB2在产生结果集时，对数据的访问次数更少。同时，CASE表达式的另一个有价值的用法是用来进行表的旋转。一个常见的需求就是对一个符合范式的表达式产生不符合范式的查询结果。DB2 数据

28、库操作语言数据库操作语言_CASE第30页/共77页第29页/共77页DB29引入了新的SQL语句MERGE。MERGE提升了应用程序的性能，允许DB2在SQL语句中集成数据到一个表中。MERGE语句可以提高客户每夜进行的批处理数据库INSERT和UPDATE过程，允许DB2避免进行SELECT数据存在性检查和INSERT或UPDATE表的操作。现在一个SQLMERGE语句可以决定是否数据库关键字行已经在表中，并且集成数据到数据库中。MERGESQL语句操作可以使用多个行作为输入数组。当多个输入行被使用时，词组NOTCONTINUEONSQLEXCEPTION可以被指定。这个词组允许DB2独立

29、处理每一行。如果在合并行的时候发生错误，只有错误的行会被剔除掉。DB2继续里剩下的输入行。DB2将会取多个输入行的每一行，然后决定正确的插入或升级操作来将它们集成到表里。当表中有多个触发器时，每个成功合并的行将会引发触发器以采取适当的行动。MERGE错误行将不触发触发器。DB2 DB2 数据库操作语言数据库操作语言_MERGE_MERGE第31页/共77页第30页/共77页 传统的处理方式：UPDATETABLENAMESETVAL1=:HV_VAL1,VAL2=:HV_VAL2Ifrecordnotfound(ifSQLcode0)then.INSERTINTOTABLE_NAME(VAL1

30、,VAL2)VALUES(:HV_VAL1,:HV_VAL2) DB2V9使用MERGE的SQL语句处理方式：MERGEINTOTABLE-NAMEASAUSING(VALUES(:HV_VAL1,:HV_VAL2)FORNROWSAST(VAL1,VAL2)ONA.VAL1=T.VAL1WHENMATCHEDTHENUPDATESETVAL2=A.VAL2+T.VAL2WHENNOTMATCHEDTHENINSERT(VAL1,VAL2)VALUES(T.VAL1,T.VAL2);DB2 DB2 数据库操作语言数据库操作语言_MERGE_MERGE第32页/共77页第31页/共77页 在DB

31、2V8中已经支持SELECTFROMINSERT，如下所示：SELECTcol1FROMFINALTABLE(INSERTINTOTABLE_NAME_2(col1,col2)VALUES(aaaaa,bbbbb); DB2V9新增SELECTFROMDELETE/UPDATE语句，如下所示：(1)SELECTFROMDELETE:SELECTSUM(col1)FROMOLDTABLE(DELETEFROMTABLE_NAME_2WHEREcol2=ccccc);(2)SELECTFROMUPDATE:SELECTName,SalaryFROMFINALTABLE(UPDATETABLE_NA

32、ME_2SETcol1=col1*1.1WHEREcol2=ccccc);DB2 数据库操作语言数据库操作语言_SELECTFROMINSERTUPDATEDELETE第33页/共77页第32页/共77页在DB2中有三种游标类型，分别为只读游标、可更新游标和模糊游标。DB2对三种游标类型的处理有所不同，这些不同主要体现在性能方面。 只读游标：当DB2知道游标是只读的时，就可以应用某些性能方面的优势：DB2通过能够执行记录组块（recordblocking）来从服务器一次检索多行，而不必担心如何获得允许更新行的锁。DB2有时可以为查询选择更好的访问计划。如果我们知道游标不会用于更新或删除行，则应

33、该将其标为只读，方法是将FORREADONLY（或FORFETCHONLY（WITHUR）添加到该游标的SELECT语句中。如果游标的SELECT语句要联结多个表，或者包括像ORDERBY或GROUPBY这样的子句，那么该游标将被自动列为只读。DB2 DB2 数据库操作语言数据库操作语言_ _游标操作游标操作第34页/共77页第33页/共77页 可更新游标如果在游标的SELECT语句中指定了FORUPDATE子句，则该游标就是可更新的，这意味着游标中的行将被一条UpdateWhereCurrentOf语句更新。在SELECT语句中只能引用一个表（或视图）。因为必须维持数据完整性，DB2只能对可

34、更新游标执行最少的优化。【可更改游标声明时必须使用“FORUPDATEWITH字段名”子句。】 模糊游标如果DB2不能通过游标的定义决定它是只读的还是可更新的，则属于模糊游标。换句话说，游标的SELECT语句既没有指定FORREADONLY，也没有指定FORUPDATE，则该游标就是模糊游标。对于模糊游标，DB2根据针对应用程序的BIND命令的BLOCKING选项的值来选择是否为选择使用记录组块。如果执行了记录组块，但是又出现了更新，就会产生负面的性能影响，因此最好尽可能避免使用模糊游标。【注意：声明时必须指定是“只读游标”还是“可更新游标”。】DB2 DB2 数据库操作语言数据库操作语言_

35、_游标操作游标操作第35页/共77页第34页/共77页 限制返回集的大小p 如果我们不希望在应用程序中提取n行以上的记录，应该在编程时指定“FETCHFIRSTnROWSONLY”子句；反之，如果不指定该子句，结果集中可能就会有很多行记录（大于n），而实际上这些行我们确使用不到，白白的消耗系统资源。p 我们应用开发中的典型使用例子就是联机多笔查询交易。在做多笔查询的时候，由于CICS通讯区大小的限制，一般不可能一次将所有查询的记录返回给客户端，都是返回通讯区允许范围内的数据记录数。如果不使用这个子句的话，每次都要查询出整个结果集，但真正用到的确不多，浪费了很多系统资源。如果指定了“FETCHF

36、IRSTnROWSONLY”子句，并使N等与每次返回的记录条数，那么就会提高系统的效率。【注意，该子句不能与FORUPDATE子句同时使用。】DB2 DB2 数据库操作语言数据库操作语言_ _游标操作游标操作_ _注意事项注意事项第36页/共77页第35页/共77页 使用游标时要特别注意在游标的循环内有没有Commit或Rollback语句，如果游标声明时没有指明“WITH HOLD”子句的话，那么，执行Commit或Rollback语句后，该游标将被关闭。想要保持该游标不被关闭的话必须要加上“WITH HOLD”子句。【提醒：避免游标被意外关闭应使用“WITH HOLD”子句。】 另外，对于

37、需要很多更新的程序（如我们的后台批处理程序）来说，保持一个合适的更新频率也是非常重要的。如果更新频率过高的话（如每次改动后都做提交），虽然程序的并发性比较好，但由于提交操作需要大量的时间，整个程序的效率就比较低。如果更新频率过低的话，相对来说，程序的执行效率比较高，但程序的并发性就比较低了，如果控制得不好的话，非常容易产生死锁或长时间等待。【根据IBM的建议，一般每10005000条记录做一次提交比较适合。】DB2 DB2 数据库操作语言数据库操作语言_ _游标操作游标操作_ _注意事项注意事项第37页/共77页第36页/共77页当批量处理程序中使用游标获取数据信息，则可以通过MULTI-RO

38、WFETCH来获取多条数据信息，与一条条获取数据相比来说它能有效地提高效率，特别是在分布式环境中，能有效地减少网络传输的消耗。例如：longserial_num10;structshortlen;chardata18;name10;EXECSQLDECLAREC1CURSORFORSELECTNAME,SERIAL_NOFROMEMPLOYEEWITHROWSETPOSITIONING;EXECSQLOPENC1;EXECSQLFETCHFIRSTROWSETFROMC1FOR10ROWSINTO:NAME,:SERIAL_NUM;DB2 DB2 数据库操作语言数据库操作语言_ _游标操作游标

39、操作_ _注意事项注意事项第38页/共77页第37页/共77页BoEing_DB2BoEing_DB2开发规范开发规范DB2 数据库控制语言数据库控制语言1DB2 数据库定义语言数据库定义语言2DB2 数据库操作语言数据库操作语言3DB2 数据库常用函数数据库常用函数4DB2 数据库操作建议数据库操作建议5第39页/共77页第38页/共77页DB2 DB2 数据库操作函数数据库操作函数使用SQL语句对DB2表中的数据进行操作时，有两类内置的函数可供使用，它们是列函数和标量函数。可以使用这些函数进一步简化对复杂数据的访问。DB2同时也提供了让用户创建自己定义函数的功能，称为“用户定义函数”。（1

40、）列函数（）列函数（Column Function）：）：可从一组数据中计算出一个特定的列值或表达式值。它提供了汇集数据的能力，使用户能够在一条SQL语句中进行跨越多个行的数据统计和计算。使用列函数需注意如下几条规则：列函数只能用在SELECT语句中；对列函数不许显式地指定列名或表达式；每个列函数对所做的SELECT操作的数据行稽核只返回一个值；如果对SELECT语句的某个列使用列函数，除非也使用GROUPBY，否则必须对同一个SELECT语句中的其他所有列也使用这个函数；使用GROUPBY子句来对一组命令列使用列函数，任何在这条SELECT语句中的命令列也必将被这个列函数处理；第40页/共7

41、7页第39页/共77页 除COUNT和COUNT_BIG函数外，列函数的结果值与它处理的列具有相同的数据类型。COUNT列函数返回整型值，而COUNT_BIG列函数返回小数值； 如果预先在WHERE子句中定义的条件没有返回数据，而是返回空值NULL，则列函数将不返回，SQLCODE为100； 当在可以取空值的列上使用AVG、MAX、MIN、STDDEV、SUM和VARIANGE函数时，应在使用这些函数之前将所有的空值剔除； 在使用某个列函数之前，可以使用DISTINCT关键字来去除重复值，DISTINCT对MAX和MIN函数无效； 可以使用ALL关键字指出重复的值不被去除，ALL在列函数使用中

42、是默认的； 只有当一个WHERE子句是HAVING子句的子查询的一部分时，列函数才能在WHERE子句中被定义； 在列函数表达式中指定的每个列名都必须被相同的组所引用。DB2 DB2 数据库操作函数数据库操作函数_ _列函数列函数第41页/共77页第40页/共77页COUNT函数是对某个表的行数进行计数，或者对某个给定的具有不同值的列进行计数。COUNT函数可以在列一级或行一级上进行操作，相应的语法有所不同。COUNT函数只是简单地对行数进行计数并返回结果值，并不关心所计数的行中存储的数据值。具体语法如下：SELECTCOUNT(*)FROMTable_Name；DB2 DB2 数据库操作函数数

43、据库操作函数_ _列函数列函数_COUNT_COUNT第42页/共77页第41页/共77页MAX函数返回指定的列或表达式值中的最大值。MAX函数返回结果应当与指定的列或表达式具有相同的数据类型。它的参数是除去大对象类型（CLOB、DBCLOB或BLOB）外任何系统内置的数据类型。字符串类型的参数不能长于255字节，而图形串类型的参数不能长于127字节。具体语法如下：SELECTMAX(COLUMN_NAME)FROMTABLE_NAME;DB2 DB2 数据库操作函数数据库操作函数_ _列函数列函数_MAX_MAX第43页/共77页第42页/共77页MIN函数返回指定的列或表达式值中的最小值。

44、MIN函数返回结果应当与指定的列或表达式具有相同的数据类型。它的参数是除去大对象类型（CLOB、DBCLOB或BLOB）外任何系统内置的数据类型。字符串类型的参数不能长于255字节，而图形串类型的参数不能长于127字节。具体语法如下：SELECTMIN(COLUMN_NAME)FROMTABLE_NAME;DB2 DB2 数据库操作函数数据库操作函数_ _列函数列函数_MIN_MIN第44页/共77页第43页/共77页SUM函数返回对指定列或表达式的值的累加和，它的参数可以使用任何系统定义的数值型数据，返回值必须在其数据类型可以允许接受的值的范围之内。除下列情况外，函数返回值的数据类型必须同参

45、数值的数据类型相同:对SMALLINT值求和将返回INTEGER值；对单精度数求和将返回双精度值。具体语法如下：SELECTSUM(COLUMN_NAME)FROMTABLE_NAME;DB2 DB2 数据库操作函数数据库操作函数_ _列函数列函数_SUM_SUM第45页/共77页第44页/共77页DB2 DB2 数据库操作函数数据库操作函数_ _标量函数标量函数标量函数是应用在某个列或表达式上对单个值进行运算处理的函数，而列函数是应用在某个数据集合上的。标量函数将某个列或表达式的值进行转换并将转换结果作为返回值。DB2提供的标量函数如下：ABS：返回数的绝对值；HEX：返回值的十六进制表示；

46、LENGTH：返回自变量中的字节数（对于图形字符串则返回双字节字符数）；YEAR：抽取日期时间值的年份部分。CONCATLTRIM、RTRIMSTRIPSUBSTR第46页/共77页第45页/共77页BoEing_DB2BoEing_DB2开发规范开发规范DB2 数据库控制语言数据库控制语言1DB2 数据库定义语言数据库定义语言2DB2 数据库操作语言数据库操作语言3DB2 数据库常用函数数据库常用函数4DB2 数据库操作建议数据库操作建议5第47页/共77页第46页/共77页约束是数据库管理程序实施的规则，DB2包含4种类型的约束处理。唯一性约束：确保表中的关键字值是唯一的。检查对组成主关键

47、字的列的任何更改，以保证唯一性；参照完整性约束：在插入、更新和删除操作上实现参考约束。所有外部关键字的所有值都有效才是数据库的一个正确状态；表检查约束：验证更改后的数据有无违反创建或更改表时指定的条件；触发器：定义要执行的一组操作，当对指定的表执行更新、删除或插入操作时要调用这组操作。DB2 DB2 数据库操作建议数据库操作建议_ _数据库约束数据库约束第48页/共77页第47页/共77页 唯一性约束是一个规则，它确保关键字值在表中是唯一的。在唯一约束中组成该关键字的每一列必须定义为NOT NULL。可以使用PRIMARY KEY或UNIQUE子句的CREATE TABLE或ALTER TAB

48、LE语句中定义唯一约束。 一个表可有任意多个唯一约束，但是只能定义一个唯一约束作为一个表的主键，此外，一个表在相同的一组列上不能又多个唯一约束。当定义一个唯一约束时，DB2就会创建一个唯一索引并将它指定为系统必需的主索引或唯一索引。此约束通过唯一索引来实现。一旦在某列上建立了唯一约束，则在多行更新期间对唯一性的检查将延迟到更新结束后进行。DB2 DB2 数据库操作建议数据库操作建议_ _数据库约束数据库约束_ _唯一性约束唯一性约束第49页/共77页第48页/共77页 DB2通过参照约束维护参照完整性。参照完整性要求子表给定的属性的所有值存在于父表的某些列中。参照约束是指一个指定的外部关键字的

49、非NULL值只有是指定表的唯一关键字的值时才有效。参照约束的目的是保证数据库表之间的关系得到维持，并遵守数据输入规则。 对于参照约束的表，在INSERT、DELETE、UPDATE和DROP等SQL操作上有某些限制。 （1）INSERT规则： 可以随时在父表中插入一行，而不必在子表中执行任何操作，但不能在子表中插入行，除非在父表中有一行键值等于要插入的行的外键，或该外键值是NULL；DB2 DB2 数据库操作建议数据库操作建议_ _数据库约束数据库约束_ _参照完整参照完整性约束性约束第50页/共77页第49页/共77页（2）DELETE规则： 当从父表中删除一行时，需检查在子表中是否有某行或

50、某些行的外键值等于父表被删除的行的键值。若不存在则可正确删除；若存在，是否可以删除该行由创建子表时指定的删除规则决定。若定义为RESTRICT，则阻止父表删除任何行，如果一定要删除，需先删除子表对应的行再删除父表的行才可实现；若定义为NOACTION，则强制要求父表不能删除行；若定义为CASCADE，则在删除父表的行后自动删除子表对应的行，无需首先删除子表对应的行。若子表还有关联的子表，则再对之后的子表实施删除规则，DB2可实现级联删除；若定义为SET NULL，则在删除父表的行后将子表中的外键值置为NULL，而该行的其他部分保持不变；若创建子表时未定义删除规则，则默认为NOACTION规则。

51、在一个删除操作中可能涉及的任何表称为删除连接的表。下列限制应用于删除连接的关系：当多个表组成的一个参照循环中，一个表不能与它自己是删除连接关系；当一个表通过多个从属关系与另一个表形成删除连接关系时，这些关系必须有相同的删除规则CASCADE或NOACTION；当一个自参照表是CASCADE关系中另一个表的子表时，自参照关系的删除规则也必须是CASCADE。DB2 DB2 数据库操作建议数据库操作建议_ _数据库约束数据库约束_ _参照完整参照完整性约束性约束第51页/共77页第50页/共77页（3）UPDATE规则： 若需要更新子表的外键，而该外键值不NULL时，它必须与父表的某个键值匹配，否

52、则不允许更新任何行。若更新父表的某个键值时： 若子表中存在某些行与父表的该键值匹配，且更新规则是RESTRICT时拒绝该更新； 若更新语句完成时（触发后的情况除外）子表中的任何行没有对应的父表键值，当更新规则未NOACTION时拒绝该更新。 要更新父表的该行，必须通过下列操作首先除去与子表的某些子行的关系： 删除子行； 更新子表中的外键值，以包括另一个有效的关键字。DB2 DB2 数据库操作建议数据库操作建议_ _数据库约束数据库约束_ _参照完整参照完整性约束性约束第52页/共77页第51页/共77页BoEing_DB2BoEing_DB2开发规范开发规范DB2 SQL 语句编写规范语句编写

53、规范1DB2 数据类型应用规范数据类型应用规范2DB2 数据库索引应用规范数据库索引应用规范3第53页/共77页第52页/共77页BoEing_DB2 BoEing_DB2 数据类型应用规范数据类型应用规范序号序号数据类型名称数据类型名称COBOLAppBuilderDB2取值范围取值范围描述信息描述信息1小整型小整型PIC S9(4)Small IntegerSmallInt-32768+32767(1)短整型是两个短整型是两个字节的整数，精字节的整数，精度为度为5位；位；(2)在在AppBuilder中选择中选择Small Integer or Integer选项，选项，Field Len

54、gth的长的长度则填度则填15；2整型整型PIC S9(9)IntegerInteger-2147483648+2147483647(1)整型是四个字整型是四个字节的整数，精度节的整数，精度为为10位；位；(2)在在Appbuilder中选择中选择Small Integer or Integer选项，选项，Field Length的长的长度则填度则填31；第54页/共77页第53页/共77页BoEing_DB2 BoEing_DB2 数据类型应用规范数据类型应用规范序号序号数据类型名称数据类型名称COBOLAppBuilderDB2取值范围取值范围描述信息描述信息1长整型长整型PIC S9(1

55、8)Long IntegerBigInt-9223372036854775808+9223372036854775807长整型是八个长整型是八个字节的整数，字节的整数，精度为精度为19位。位。2小数小数S9(16)V9(2)DecimalDecimal(p,s)或或Numeric(p,s)-2147483648+2147483647(1)整型是四整型是四个字节的整数，个字节的整数，精度为精度为10位；位；(2)在在Appbuilder中选择中选择Small Integer or Integer选项，选项，Field Length的长度则填的长度则填31；第55页/共77页第54页/共77页Bo

56、Eing_DB2 BoEing_DB2 数据类型应用规范数据类型应用规范序号序号数据类型名称数据类型名称COBOLAppBuilderDB2取值范围取值范围描述信息描述信息1固定长度字符串固定长度字符串PIC X(200)CharactersCharacters或或Char1254字节字节如果不指定长度，如果不指定长度，则默认为则默认为1个字节；个字节；2可变长度字符串可变长度字符串PIC S9(4)或或PIC X(300)VarCharVarChar132672字节字节(1)整型是四个字节整型是四个字节的整数，精度为的整数，精度为10位；位；(2)在在Appbuilder中中选择选择Smal

57、l Integer or Integer选项，选项，Field Length的长的长度则填度则填31；3二进制串二进制串USAGE SQL TYPE IS BINARY(10)Binary1-255字节字节存储的是定长二进存储的是定长二进制，不与编码码制制，不与编码码制关联，通常存储加关联，通常存储加密的数据密的数据4日期日期PIC X(10)DateDateYYYY-MM-DD日期日期5时间时间PIC X(12)TimeTimeHH-MM-SS-MSS时间时间6时间戳时间戳PIC X(26)TimeStampTimeStampYYYY-MO-DD-HH-MM-SS-MSSSSS日期时间日期时

58、间第56页/共77页第55页/共77页BoEing_DB2BoEing_DB2开发规范开发规范DB2 SQL 语句编写规范语句编写规范1DB2 数据类型应用规范数据类型应用规范2DB2 数据库索引应用规范数据库索引应用规范3第57页/共77页第56页/共77页BoEing_DB2 BoEing_DB2 数据库索引数据库索引索引是表的一个或多个列的键值的有序列表。创建索引的原因主要有两个： 确保一个或多个列中值的唯一性； 提高对表进行查询的性能。当执行查询时想以更快的速度中找到所需要的数据列，或要以索引的顺序显示查询结果时，DB2优化器选择使用索引。【如果表上不存在索引，那么必须对SQL查询中引

59、用的每个表执行表扫描，表越大，表扫描所花费的时间越长，因为表扫描需要顺序访问每个表行。】尽管索引能显著缩短数据库表的访问时间，但是他们也会给性能带来负面影响。在创建索引前，考虑多个索引给磁盘空间和处理时间带来的影响。每个索引都需要存储器和磁盘空间，准确的容量取决于表的大小以及关系索引中的列的大小和数目。对一个表执行的每个INSERT或DELETE操作都需要对该表上的每个索引进行额外的更新。对于更改索引键值的每个UPDATE操作，也是如此。关系索引就是常规索引。 第58页/共77页第57页/共77页 对于分区表，需要一些键值来控制表的分区，简单的来讲如果这些键值划分是在CREATE TABLE建

60、表语句中，这个表就是Table-Control的表，如果是在Create Index语句中，这个表就是Index-Control。 使用Index-Control，一定需要有一个来控制分区的索引，而且这个索引需要建成聚簇索引，不存在第二个索引为分区索引。而对于Table-Control就不存在这种情况，分区由表控制，和索引无关，不必须使用聚簇索引，且可以有多个分区索引。BoEing_DB2 BoEing_DB2 数据库索引数据库索引第59页/共77页第58页/共77页（1）对于Index-Control的表，索引分为两种，第一种为分区且聚簇索引，第二种为非分区非聚簇索引。（2）对于Table-