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

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