DB2基础知识(二).ppt

,DB2基础知识（二）,杭州中心 数据业务室 席晓勇 2011年7月20日,二、DB2的数据类型,三、DB2的DDL,内容提要,四、DB2的DML,五、DB2的函数,六、总结,学习目标,理解DB2的数据类型 掌握DB2的DDL和DML的原理与编写 DB2中常用函数的使用，理解OLAP函数,SQL概述 数据控制语言（DCL） - 提供对数据库对象的访问控制 数据定义语言（DDL） - 创建、修改或删除数据库对象 数据操作语言（DML） - 选择、插入、更新或删除数据,DB2 SQL使用,1.1 SQL概述 SQLStructured Query Language SQL的功能：数据定义、操纵和控制。 SQL的特点 综合统一 高度非过程化 面向集合的操作方式 两种使用方式 语言简捷，易学易用 SQL语言支持关系数据库三级模式结构,DB2 SQL使用,1.2 数据控制语言（DCL） DCL是SQL的一个子集，提供对数据库对象的访问控制。 GRANT 权限被显式授予单个用户或组，包括数据库，表空间，表，视图，索引，包，模式 GRANT privilege ON object-type object-name TO USER | GROUP | PUBLIC authorization-name WITH GRANT OPTION REVOKE 用户或组显式地撤消权限和特权 REVOKE privilege ON object-type object-name FROM USER | GROUP | PUBLIC authorization-name,DB2 SQL使用,1.3 数据定义语言（DDL） DDL是SQL的一个子集，创建、修改、删除数据库对象。 CREATE 创建数据库对象，包括： 缓冲池（Buffer Pool）、事件控制程序（Event monitor）、函数（Function）、索引（Index）、模式（Schema）、存储过程（Stored Procedure）、表（Table）、视图（View）、触发器（Trigger）、表空间（Table Space） CREATE TABLE BDM_FAT.SAMPLE ( CLT_NBR CHAR(10) NOT NULL, CLT_NAM VARCHAR(25), CLT_AGE SMALLINT),一、DB2 SQL使用,ALTER 更改数据库对象现有的一些特征，包括： 缓冲池（Buffer Pool） 、表（Table）、视图（View）、表空间（Table Space） 索引不可更改 ALTER TABLE BDM_FAT.SAMPLE ADD TTL_COD CHAR,一、DB2 SQL使用,DROP 删除create或declare语句创建的对象包括： 缓冲池（Buffer Pool）、事件控制程序（Event monitor）、函数（Function）、索引（Index）、模式（Schema）、存储过程（Stored Procedure）、表（Table）、视图（View）、触发器（Trigger）、表空间（Table Space） DROP TABLE BDM_FAT.SAMPLE,一、DB2 SQL使用,1.4 数据操作语言（DML） DML是SQL的一个子集，选择、插入、更新或删除数据 SELECT SELECT * FROM tab1 SELECT * FROM tab1 FETCH FIRST 10 ROWS ONLY SELECT DISTINCT a, b FROM tab1 SELECT a, b FROM tab1 WHERE b 20000 SELECT a FROM tab1 WHERE a LIKE S% SELECT a, b FROM tab1 WHERE b 20000 ORDER BY b SELECT a, MAX(b) FROM tab1 GROUP BY a SELECT tab1.a, tab1.b,tab2.b FROM tab1,tab2 where tab1.a=tab2.a UNION EXCEPT INTERSECT,一、DB2 SQL使用,INSERT INSERT INTO tab1 values(a,1); INSERT INTO tab1(a, b) SELECT a, b from tab2; DELETE DELETE FROM tab1 where tab1.a a UPDATE UPDATE tab1 set tab1.b = tab1.b * 2 where tab1.a = a 参考资料：DB2 LUW V9.5 SQL Cookbook,一、DB2 SQL使用,二、DB2的数据类型,2.1 数值型 2.2 字符型 2.3 日期型 2.4 NULL值 2.5 数据类型与存储空间,2.1 数值型,二、DB2的数据类型,2.2 字符型 思考： 字符串student 为student+4个空格； 如果字段定义为char(20)，则该数据占用20个字符； 如果定义为varchar(20)，则占用多少个字符？,二、DB2的数据类型,2.2 字符型,二、DB2的数据类型,2.3 日期型,二、DB2的数据类型,2.4 NULL值 NULL值表示“DB2不知道这个值是什么” NULL值不是空字符串（NULL ） 在定义表结构的字段时指定为：not null 例如：INSERT INTO session.clt(num) values(99.3),(66.2),(25.6),(NULL),(null); 1）select * from session.clt 2）select * from session.clt where num 99.3 3）delete from session.clt where num 99.3 思考：如何处理NULL值？,二、DB2的数据类型,2.5 数据类型与存储空间 企业应用对存储空间的消耗巨大 存储消耗： 硬件存储设备费用+电费+维护费用+其他消耗等 字段类型的选取应合理，在考虑到扩展性的同时注意节约存储空间,二、DB2的数据类型,三、DB2的DDL,3.1 CREATE TABLE 3.2 CREATE INDEX 3.3 CREATE VIEW 3.4 CREATE NICKNAME 3.5 CREATE PROCEDURE 3.6 RENAME,3.1 CREATE TABLE create table DWA_FAT.A80_GRPTRX ( STA_MON INT not null default 200901 constraint C_STA_MON check (STA_MON between 200901 and 200912), BRN_NBR CHAR(6) not null, BBK_NBR CHAR(3), not null GRP_NBR CHAR(2) not null, constraint “P_Key_1“ primary key (STA_MON, BRN_NBR, GRP_NBR) ) in HISDTA3_TBS index in HISIDX1_TBS distribute by ( BRN_NBR ); comment on table DWA_FAT.A80_GRPTRX is 随机销售团队月度业绩统计; comment on column DWA_FAT.A80_GRPTRX.STA_MON is 数据月份; comment on column DWA_FAT.A80_GRPTRX.BBK_NBR is 交易归属分行; comment on column DWA_FAT.A80_GRPTRX.BRN_NBR is 交易归属网点; comment on column DWA_FAT.A80_GRPTRX.GRP_NBR is 团队编号;,三、DB2的DDL,表空间,主键,分区键,表注释及字段注释,Check约束,3.2 CREATE INDEX CREATE INDEX CUSTOMER_NAMES ON CUSTOMER_DATA(LAST_NAME, FIRST_NAME); 1)Select * from customer_names where first_name = A 思考：是否可以使用索引？ 2)索引的设计需要考虑到系统实际的需要,三、DB2的DDL,3.3 CREATE VIEW create view UNICORE.RV_ACACTDTAP as select * from UNICORE.ACACTDTAP2008 where EYDT between 2008-01-01 and 2008-12-31 union all select * from UNICORE.ACACTDTAP2009 where EYDT between 2009-01-01 and 2009-12-31 union all select * from UNICORE.ACACTDTAP2010 where EYDT between 2010-01-01 and 2010-12-31 union all select * from UNICORE.ACACTDTAP2011 where EYDT between 2011-01-01 and 2011-12-31 union all select * from UNICORE.ACACTDTAP 注意： 1）Union All，而不是Union 2）当事实表中包含了Check约束时，视图支持数据插入,三、DB2的DDL,3.4 CREATE NICKNAME Create NickName NICK_UNICORE.NICK_CSEASDTAP for 64SERVER.UNICORE.CSEASDTAP;,三、DB2的DDL,3.5 CREATE PROCEDURE CREATE PROCEDURE . . LANGUAGE SQL BEGIN ATOMIC . DECLARE CONTINUE | EXIT | UNDO HANDLER FOR . END,三、DB2的DDL,3.5 CREATE PROCEDURE-续 存储过程有利于提高系统运行效率 存储过程为静态SQL，但可以添加动态SQL 用途：数据批量处理，报表后台程序,三、DB2的DDL,3.6 RENAME RENAME TABLE UNICORE.SYBRNDTAP TO REBRNDTAP UNICORE.SYBRNDTAP - UNICORE.REBRNDTAP 数据库中将无法查询到UNICORE.SYBRNDTAP 表内数据保持不变，仅表名发生变化,三、DB2的DDL,四、DB2的DML,4.1 SELECT结构 4.2 Field Rename 4.3 谓词的使用（predicate） 4.4 IN的使用 4.5 CAST 4.6 CASE,四、DB2的DML-续,4.7 CURSOR的使用 4.8 SELECT INTO 4.9 INSERT 4.10 UPDATE AND DELETE 4.11 MERGE 4.12 JOIN,四、DB2的DML-续,4.13 UNION 4.14 EXCEPT 4.15 TEMPORARY TABLE,4.1 SELECT SELECT BBK_NBR,EAC_SHR,CLT_NAM FROM UNICORE.CSEASDTAP WHERE OPN_DTE = 2009-06-21,四、DB2的DML,4.2 Field Rename 说明：双引号完成字段重命名,四、DB2的DML,4.3 谓词的使用（predicate） 说明：AND的计算优先级大于OR,四、DB2的DML,4.4 IN 说明：完成多值数据匹配,四、DB2的DML,4.5 CAST 说明：CAST数据类型转换,四、DB2的DML,4.6 CASE 说明：1）CASE为条件判断语句； 2）注意必须使用ELSE，起到验证数据的作用；,四、DB2的DML,4.7 CURSOR 1）返回结果集 2）循环处理,四、DB2的DML,4.8 SELECT INTO 说明： 1）提取数据赋值给变量 2）从表中提取的数据务必保证其唯一性，否则报错,四、DB2的DML,4.9 INSERT 1)INSERT INTO SESSION.USER(USR_NAM,USR_AGE) VALUES(BOB,23); 2)INSERT INTO SESSION.USER(USR_NAM,USR_AGE) VALUES(BOB,23),(JASON,20); 3)INSERT INTO SESSION.USER(USR_NAM,USR_AGE) SELECT USR_NAM,USR_AGE FROM UNICORE.CLCLTDTAP WHERE BBK_NBR = 571 说明：Insert语句的三种常用形式,四、DB2的DML,4.10 UPDATE & DELETE 思考：Insert，Update和Delete与日志的关系，与索引的关系,四、DB2的DML,4.11 MERGE 注意： Old_staff:new_staff=1:* Old_staff:new_staff=1:1 Old_staff:new_staff=*:1 Old_staff:new_staff=*:*,四、DB2的DML,4.12 JOIN 1)Inner Join 2)Left Outer Join 3)Right Outer Join 4)Join语句的执行顺序,四、DB2的DML,4.12 JOIN-续 1)Inner Join 说明：查询条件写在Where和Join中效果相同,四、DB2的DML,4.12 JOIN-续 2)Left Outer Join：对比不同写法的结果区别,四、DB2的DML,查询条件写在Join中,查询条件写在Where中,4