DB2基礎(chǔ)知識(shí)(二).ppt

,DB2基礎(chǔ)知識(shí)（二）,杭州中心 數(shù)據(jù)業(yè)務(wù)室 席曉勇 2011年7月20日,二、DB2的數(shù)據(jù)類型,三、DB2的DDL,內(nèi)容提要,四、DB2的DML,五、DB2的函數(shù),六、總結(jié),學(xué)習(xí)目標(biāo),理解DB2的數(shù)據(jù)類型 掌握DB2的DDL和DML的原理與編寫 DB2中常用函數(shù)的使用，理解OLAP函數(shù),SQL概述 數(shù)據(jù)控制語(yǔ)言（DCL） - 提供對(duì)數(shù)據(jù)庫(kù)對(duì)象的訪問控制 數(shù)據(jù)定義語(yǔ)言（DDL） - 創(chuàng)建、修改或刪除數(shù)據(jù)庫(kù)對(duì)象 數(shù)據(jù)操作語(yǔ)言（DML） - 選擇、插入、更新或刪除數(shù)據(jù),DB2 SQL使用,1.1 SQL概述 SQLStructured Query Language SQL的功能：數(shù)據(jù)定義、操縱和控制。 SQL的特點(diǎn) 綜合統(tǒng)一 高度非過程化 面向集合的操作方式 兩種使用方式 語(yǔ)言簡(jiǎn)捷，易學(xué)易用 SQL語(yǔ)言支持關(guān)系數(shù)據(jù)庫(kù)三級(jí)模式結(jié)構(gòu),DB2 SQL使用,1.2 數(shù)據(jù)控制語(yǔ)言（DCL） DCL是SQL的一個(gè)子集，提供對(duì)數(shù)據(jù)庫(kù)對(duì)象的訪問控制。 GRANT 權(quán)限被顯式授予單個(gè)用戶或組，包括數(shù)據(jù)庫(kù)，表空間，表，視圖，索引，包，模式 GRANT privilege ON object-type object-name TO USER | GROUP | PUBLIC authorization-name WITH GRANT OPTION REVOKE 用戶或組顯式地撤消權(quán)限和特權(quán) REVOKE privilege ON object-type object-name FROM USER | GROUP | PUBLIC authorization-name,DB2 SQL使用,1.3 數(shù)據(jù)定義語(yǔ)言（DDL） DDL是SQL的一個(gè)子集，創(chuàng)建、修改、刪除數(shù)據(jù)庫(kù)對(duì)象。 CREATE 創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象，包括： 緩沖池（Buffer Pool）、事件控制程序（Event monitor）、函數(shù)（Function）、索引（Index）、模式（Schema）、存儲(chǔ)過程（Stored Procedure）、表（Table）、視圖（View）、觸發(fā)器（Trigger）、表空間（Table Space） CREATE TABLE BDM_FAT.SAMPLE ( CLT_NBR CHAR(10) NOT NULL, CLT_NAM VARCHAR(25), CLT_AGE SMALLINT),一、DB2 SQL使用,ALTER 更改數(shù)據(jù)庫(kù)對(duì)象現(xiàn)有的一些特征，包括： 緩沖池（Buffer Pool） 、表（Table）、視圖（View）、表空間（Table Space） 索引不可更改 ALTER TABLE BDM_FAT.SAMPLE ADD TTL_COD CHAR,一、DB2 SQL使用,DROP 刪除create或declare語(yǔ)句創(chuàng)建的對(duì)象包括： 緩沖池（Buffer Pool）、事件控制程序（Event monitor）、函數(shù)（Function）、索引（Index）、模式（Schema）、存儲(chǔ)過程（Stored Procedure）、表（Table）、視圖（View）、觸發(fā)器（Trigger）、表空間（Table Space） DROP TABLE BDM_FAT.SAMPLE,一、DB2 SQL使用,1.4 數(shù)據(jù)操作語(yǔ)言（DML） DML是SQL的一個(gè)子集，選擇、插入、更新或刪除數(shù)據(jù) 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的數(shù)據(jù)類型,2.1 數(shù)值型 2.2 字符型 2.3 日期型 2.4 NULL值 2.5 數(shù)據(jù)類型與存儲(chǔ)空間,2.1 數(shù)值型,二、DB2的數(shù)據(jù)類型,2.2 字符型 思考： 字符串student 為student+4個(gè)空格； 如果字段定義為char(20)，則該數(shù)據(jù)占用20個(gè)字符； 如果定義為varchar(20)，則占用多少個(gè)字符？,二、DB2的數(shù)據(jù)類型,2.2 字符型,二、DB2的數(shù)據(jù)類型,2.3 日期型,二、DB2的數(shù)據(jù)類型,2.4 NULL值 NULL值表示“DB2不知道這個(gè)值是什么” NULL值不是空字符串（NULL ） 在定義表結(jié)構(gòu)的字段時(shí)指定為：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的數(shù)據(jù)類型,2.5 數(shù)據(jù)類型與存儲(chǔ)空間 企業(yè)應(yīng)用對(duì)存儲(chǔ)空間的消耗巨大 存儲(chǔ)消耗： 硬件存儲(chǔ)設(shè)備費(fèi)用+電費(fèi)+維護(hù)費(fèi)用+其他消耗等 字段類型的選取應(yīng)合理，在考慮到擴(kuò)展性的同時(shí)注意節(jié)約存儲(chǔ)空間,二、DB2的數(shù)據(jù)類型,三、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 隨機(jī)銷售團(tuán)隊(duì)月度業(yè)績(jī)統(tǒng)計(jì); comment on column DWA_FAT.A80_GRPTRX.STA_MON is 數(shù)據(jù)月份; comment on column DWA_FAT.A80_GRPTRX.BBK_NBR is 交易歸屬分行; comment on column DWA_FAT.A80_GRPTRX.BRN_NBR is 交易歸屬網(wǎng)點(diǎn); comment on column DWA_FAT.A80_GRPTRX.GRP_NBR is 團(tuán)隊(duì)編號(hào);,三、DB2的DDL,表空間,主鍵,分區(qū)鍵,表注釋及字段注釋,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)索引的設(shè)計(jì)需要考慮到系統(tǒng)實(shí)際的需要,三、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）當(dāng)事實(shí)表中包含了Check約束時(shí)，視圖支持?jǐn)?shù)據(jù)插入,三、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-續(xù) 存儲(chǔ)過程有利于提高系統(tǒng)運(yùn)行效率 存儲(chǔ)過程為靜態(tài)SQL，但可以添加動(dòng)態(tài)SQL 用途：數(shù)據(jù)批量處理，報(bào)表后臺(tái)程序,三、DB2的DDL,3.6 RENAME RENAME TABLE UNICORE.SYBRNDTAP TO REBRNDTAP UNICORE.SYBRNDTAP - UNICORE.REBRNDTAP 數(shù)據(jù)庫(kù)中將無法查詢到UNICORE.SYBRNDTAP 表內(nèi)數(shù)據(jù)保持不變，僅表名發(fā)生變化,三、DB2的DDL,四、DB2的DML,4.1 SELECT結(jié)構(gòu) 4.2 Field Rename 4.3 謂詞的使用（predicate） 4.4 IN的使用 4.5 CAST 4.6 CASE,四、DB2的DML-續(xù),4.7 CURSOR的使用 4.8 SELECT INTO 4.9 INSERT 4.10 UPDATE AND DELETE 4.11 MERGE 4.12 JOIN,四、DB2的DML-續(xù),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 說明：雙引號(hào)完成字段重命名,四、DB2的DML,4.3 謂詞的使用（predicate） 說明：AND的計(jì)算優(yōu)先級(jí)大于OR,四、DB2的DML,4.4 IN 說明：完成多值數(shù)據(jù)匹配,四、DB2的DML,4.5 CAST 說明：CAST數(shù)據(jù)類型轉(zhuǎn)換,四、DB2的DML,4.6 CASE 說明：1）CASE為條件判斷語(yǔ)句； 2）注意必須使用ELSE，起到驗(yàn)證數(shù)據(jù)的作用；,四、DB2的DML,4.7 CURSOR 1）返回結(jié)果集 2）循環(huán)處理,四、DB2的DML,4.8 SELECT INTO 說明： 1）提取數(shù)據(jù)賦值給變量 2）從表中提取的數(shù)據(jù)務(wù)必保證其唯一性，否則報(bào)錯(cuò),四、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語(yǔ)句的三種常用形式,四、DB2的DML,4.10 UPDATE & DELETE 思考：Insert，Update和Delete與日志的關(guān)系，與索引的關(guān)系,四、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語(yǔ)句的執(zhí)行順序,四、DB2的DML,4.12 JOIN-續(xù) 1)Inner Join 說明：查詢條件寫在Where和Join中效果相同,四、DB2的DML,4.12 JOIN-續(xù) 2)Left Outer Join：對(duì)比不同寫法的結(jié)果區(qū)別,四、DB2的DML,查詢條件寫在Join中,查詢條件寫在Where中,4