DB2基础知识二.ppt

DB2基础知识 二 杭州中心数据业务室席晓勇2011年7月20日 一 DB2SQL使用 二 DB2的数据类型 三 DB2的DDL 内容提要 四 DB2的DML 五 DB2的函数 六 总结 学习目标 理解DB2的数据类型掌握DB2的DDL和DML的原理与编写DB2中常用函数的使用 理解OLAP函数 SQL概述数据控制语言 DCL 提供对数据库对象的访问控制数据定义语言 DDL 创建 修改或删除数据库对象数据操作语言 DML 选择 插入 更新或删除数据 DB2SQL使用 1 1SQL概述SQL StructuredQueryLanguageSQL的功能 数据定义 操纵和控制 SQL的特点综合统一高度非过程化面向集合的操作方式两种使用方式语言简捷 易学易用SQL语言支持关系数据库三级模式结构 DB2SQL使用 1 2数据控制语言 DCL DCL是SQL的一个子集 提供对数据库对象的访问控制 GRANT权限被显式授予单个用户或组 包括数据库 表空间 表 视图 索引 包 模式GRANTprivilegeONobject typeobject nameTO USER GROUP PUBLIC authorization name WITHGRANTOPTION REVOKE用户或组显式地撤消权限和特权REVOKEprivilegeONobject typeobject nameFROM USER GROUP PUBLIC authorization name DB2SQL使用 1 3数据定义语言 DDL DDL是SQL的一个子集 创建 修改 删除数据库对象 CREATE创建数据库对象 包括 缓冲池 BufferPool 事件控制程序 Eventmonitor 函数 Function 索引 Index 模式 Schema 存储过程 StoredProcedure 表 Table 视图 View 触发器 Trigger 表空间 TableSpace CREATETABLEBDM FAT SAMPLE CLT NBRCHAR 10 NOTNULL CLT NAMVARCHAR 25 CLT AGESMALLINT 一 DB2SQL使用 ALTER更改数据库对象现有的一些特征 包括 缓冲池 BufferPool 表 Table 视图 View 表空间 TableSpace 索引不可更改ALTERTABLEBDM FAT SAMPLEADDTTL CODCHAR 一 DB2SQL使用 DROP删除create或declare语句创建的对象包括 缓冲池 BufferPool 事件控制程序 Eventmonitor 函数 Function 索引 Index 模式 Schema 存储过程 StoredProcedure 表 Table 视图 View 触发器 Trigger 表空间 TableSpace DROPTABLEBDM FAT SAMPLE 一 DB2SQL使用 1 4数据操作语言 DML DML是SQL的一个子集 选择 插入 更新或删除数据SELECTSELECT FROMtab1SELECT FROMtab1FETCHFIRST10ROWSONLYSELECTDISTINCTa bFROMtab1SELECTa bFROMtab1WHEREb 20000SELECTaFROMtab1WHEREaLIKE S SELECTa bFROMtab1WHEREb 20000ORDERBYbSELECTa MAX b FROMtab1GROUPBYaSELECTtab1 a tab1 b tab2 bFROMtab1 tab2wheretab1 a tab2 aUNIONEXCEPTINTERSECT 一 DB2SQL使用 INSERTINSERTINTOtab1values a 1 INSERTINTOtab1 a b SELECTa bfromtab2 DELETEDELETEFROMtab1wheretab1 a a UPDATEUPDATEtab1settab1 b tab1 b 2wheretab1 a a 参考资料 DB2LUWV9 5SQLCookbook 一 DB2SQL使用 二 DB2的数据类型 2 1数值型2 2字符型2 3日期型2 4NULL值2 5数据类型与存储空间 2 1数值型 二 DB2的数据类型 2 2字符型思考 字符串 student 为 student 4个空格 如果字段定义为char 20 则该数据占用20个字符 如果定义为varchar 20 则占用多少个字符 二 DB2的数据类型 2 2字符型 二 DB2的数据类型 2 3日期型 二 DB2的数据类型 2 4NULL值NULL值表示 DB2不知道这个值是什么 NULL值不是空字符串 NULL 在定义表结构的字段时指定为 notnull例如 INSERTINTOsession clt num values 99 3 66 2 25 6 NULL null 1 select fromsession clt2 select fromsession cltwherenum99 33 deletefromsession cltwherenum99 3思考 如何处理NULL值 二 DB2的数据类型 2 5数据类型与存储空间企业应用对存储空间的消耗巨大存储消耗 硬件存储设备费用 电费 维护费用 其他消耗等字段类型的选取应合理 在考虑到扩展性的同时注意节约存储空间 二 DB2的数据类型 三 DB2的DDL 3 1CREATETABLE3 2CREATEINDEX3 3CREATEVIEW3 4CREATENICKNAME3 5CREATEPROCEDURE3 6RENAME 3 1CREATETABLEcreatetableDWA FAT A80 GRPTRX STA MONINTnotnulldefault200901constraintC STA MONcheck STA MONbetween200901and200912 BRN NBRCHAR 6 notnull BBK NBRCHAR 3 notnullGRP NBRCHAR 2 notnull constraint P Key 1 primarykey STA MON BRN NBR GRP NBR inHISDTA3 TBSindexinHISIDX1 TBSdistributeby BRN NBR commentontableDWA FAT A80 GRPTRXis 随机销售团队月度业绩统计 commentoncolumnDWA FAT A80 GRPTRX STA MONis 数据月份 commentoncolumnDWA FAT A80 GRPTRX BBK NBRis 交易归属分行 commentoncolumnDWA FAT A80 GRPTRX BRN NBRis 交易归属网点 commentoncolumnDWA FAT A80 GRPTRX GRP NBRis 团队编号 三 DB2的DDL 表空间 主键 分区键 表注释及字段注释 Check约束 3 2CREATEINDEXCREATEINDEXCUSTOMER NAMESONCUSTOMER DATA LAST NAME FIRST NAME 1 Select fromcustomer nameswherefirst name A 思考 是否可以使用索引 2 索引的设计需要考虑到系统实际的需要 三 DB2的DDL 3 3CREATEVIEWcreateviewUNICORE RV ACACTDTAPasselect fromUNICORE ACACTDTAP2008whereEYDTbetween 2008 01 01 and 2008 12 31 unionallselect fromUNICORE ACACTDTAP2009whereEYDTbetween 2009 01 01 and 2009 12 31 unionallselect fromUNICORE ACACTDTAP2010whereEYDTbetween 2010 01 01 and 2010 12 31 unionallselect fromUNICORE ACACTDTAP2011whereEYDTbetween 2011 01 01 and 2011 12 31 unionallselect fromUNICORE ACACTDTAP注意 1 UnionAll 而不是Union2 当事实表中包含了Check约束时 视图支持数据插入 三 DB2的DDL 3 4CREATENICKNAMECreateNickNameNICK UNICORE NICK CSEASDTAPfor64SERVER UNICORE CSEASDTAP 三 DB2的DDL 3 5CREATEPROCEDURECREATEPROCEDURE LANGUAGESQLBEGINATOMIC DECLARE CONTINUE EXIT UNDO HANDLERFOR END 三 DB2的DDL 3 5CREATEPROCEDURE 续存储过程有利于提高系统运行效率存储过程为静态SQL 但可以添加动态SQL用途 数据批量处理 报表后台程序 三 DB2的DDL 3 6RENAMERENAMETABLEUNICORE SYBRNDTAPTOREBRNDTAPUNICORE SYBRNDTAP UNICORE REBRNDTAP数据库中将无法查询到UNICORE SYBRNDTAP表内数据保持不变 仅表名发生变化 三 DB2的DDL 四 DB2的DML 4 1SELECT结构4 2FieldRename4 3谓词的使用 predicate 4 4IN的使用4 5CAST4 6CASE 四 DB2的DML 续 4 7CURSOR的使用4 8SELECTINTO4 9INSERT4 10UPDATEANDDELETE4 11MERGE4 12JOIN 四 DB2的DML 续 4 13UNION4 14EXCEPT4 15TEMPORARYTABLE 4 1SELECTSELECTBBK NBR EAC SHR CLT NAMFROMUNICORE CSEASDTAPWHEREOPN DTE 2009 06 21 四 DB2的DML 4 2FieldRename说明 双引号完成字段重命名 四 DB2的DML 4 3谓词的使用 predicate 说明 AND的计算优先级大于OR 四 DB2的DML 4 4IN说明 完成多值数据匹配 四 DB2的DML 4 5CAST说明 CAST数据类型转换 四 DB2的DML 4 6CASE说明 1 CASE为条件判断语句 2 注意必须使用ELSE 起到验证数据的作用 四 DB2的DML 4 7CURSOR1 返回结果集2 循环处理 四 DB2的DML 4 8SELECTINTO说明 1 提取数据赋值给变量2 从表中提取的数据务必保证其唯一性 否则报错 四 DB2的DML 4 9INSERT1 INSERTINTOSESSION USER USR NAM USR AGE VALUES BOB 23 2 INSERTINTOSESSION USER USR NAM USR AGE VALUES BOB 23 JASON 20 3 INSERTINTOSESSION USER USR NAM USR AGE SELECTUSR NAM USR AGEFROMUNICORE CLCLTDTAPWHEREBBK NBR 571 说明 Insert语句的三种常用形式 四 DB2的DML 4 10UPDATE DELETE思考 Insert Update和Delete与日志的关系 与索引的关系 四 DB2的DML 4 11MERGE注意 Old staff new staff 1 Old staff new staff 1 1Old staff new staff 1Old staff new staff 四 DB2的DML 4 12JOIN1 InnerJoin2 LeftOuterJoin3 RightOuterJoin4 Join语句的执行顺序 四 DB2的DML 4 12JOIN 续1 InnerJoin说明 查询条件写在Where和Join中效果相同 四 DB2的DML 4 12JOIN 续2 LeftOuterJoin 对比不同写法的结果区别 四 DB2的DML 查询条件写在Join中 查询条件写在Where中 4 12JOIN 续2 RightOuterJoin 四 DB2的DML 4 13UNION说明 求合集 注意UNION与UNIONALL的区别 四 DB2的DML 4 14EXCEPT说明 注意EXCEPT与EXCEPTALL的区别思考 如果R1中包含两个E字符 EXCEPT的结果是什么 四 DB2的DML 4 15TEMPORARYTABLE语法 案例 四 DB2的DML 4 15TEMPORARYTABLE 续 四 DB2的DML 4 15TEMPORARYTABLE 续案例 说明 1 ONCOMMITPRESERVE DELETEROWS表示在事务提交后是否保留临时表数据2 WITHREPLACE表