DB2基礎知識二.ppt

DB2基礎知識 二 杭州中心數(shù)據業(yè)務室席曉勇2011年7月20日 一 DB2SQL使用 二 DB2的數(shù)據類型 三 DB2的DDL 內容提要 四 DB2的DML 五 DB2的函數(shù) 六 總結 學習目標 理解DB2的數(shù)據類型掌握DB2的DDL和DML的原理與編寫DB2中常用函數(shù)的使用 理解OLAP函數(shù) SQL概述數(shù)據控制語言 DCL 提供對數(shù)據庫對象的訪問控制數(shù)據定義語言 DDL 創(chuàng)建 修改或刪除數(shù)據庫對象數(shù)據操作語言 DML 選擇 插入 更新或刪除數(shù)據 DB2SQL使用 1 1SQL概述SQL StructuredQueryLanguageSQL的功能 數(shù)據定義 操縱和控制 SQL的特點綜合統(tǒng)一高度非過程化面向集合的操作方式兩種使用方式語言簡捷 易學易用SQL語言支持關系數(shù)據庫三級模式結構 DB2SQL使用 1 2數(shù)據控制語言 DCL DCL是SQL的一個子集 提供對數(shù)據庫對象的訪問控制 GRANT權限被顯式授予單個用戶或組 包括數(shù)據庫 表空間 表 視圖 索引 包 模式GRANTprivilegeONobject typeobject nameTO USER GROUP PUBLIC authorization name WITHGRANTOPTION REVOKE用戶或組顯式地撤消權限和特權REVOKEprivilegeONobject typeobject nameFROM USER GROUP PUBLIC authorization name DB2SQL使用 1 3數(shù)據定義語言 DDL DDL是SQL的一個子集 創(chuàng)建 修改 刪除數(shù)據庫對象 CREATE創(chuàng)建數(shù)據庫對象 包括 緩沖池 BufferPool 事件控制程序 Eventmonitor 函數(shù) Function 索引 Index 模式 Schema 存儲過程 StoredProcedure 表 Table 視圖 View 觸發(fā)器 Trigger 表空間 TableSpace CREATETABLEBDM FAT SAMPLE CLT NBRCHAR 10 NOTNULL CLT NAMVARCHAR 25 CLT AGESMALLINT 一 DB2SQL使用 ALTER更改數(shù)據庫對象現(xiàn)有的一些特征 包括 緩沖池 BufferPool 表 Table 視圖 View 表空間 TableSpace 索引不可更改ALTERTABLEBDM FAT SAMPLEADDTTL CODCHAR 一 DB2SQL使用 DROP刪除create或declare語句創(chuàng)建的對象包括 緩沖池 BufferPool 事件控制程序 Eventmonitor 函數(shù) Function 索引 Index 模式 Schema 存儲過程 StoredProcedure 表 Table 視圖 View 觸發(fā)器 Trigger 表空間 TableSpace DROPTABLEBDM FAT SAMPLE 一 DB2SQL使用 1 4數(shù)據操作語言 DML DML是SQL的一個子集 選擇 插入 更新或刪除數(shù)據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的數(shù)據類型 2 1數(shù)值型2 2字符型2 3日期型2 4NULL值2 5數(shù)據類型與存儲空間 2 1數(shù)值型 二 DB2的數(shù)據類型 2 2字符型思考 字符串 student 為 student 4個空格 如果字段定義為char 20 則該數(shù)據占用20個字符 如果定義為varchar 20 則占用多少個字符 二 DB2的數(shù)據類型 2 2字符型 二 DB2的數(shù)據類型 2 3日期型 二 DB2的數(shù)據類型 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的數(shù)據類型 2 5數(shù)據類型與存儲空間企業(yè)應用對存儲空間的消耗巨大存儲消耗 硬件存儲設備費用 電費 維護費用 其他消耗等字段類型的選取應合理 在考慮到擴展性的同時注意節(jié)約存儲空間 二 DB2的數(shù)據類型 三 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 隨機銷售團隊月度業(yè)績統(tǒng)計 commentoncolumnDWA FAT A80 GRPTRX STA MONis 數(shù)據月份 commentoncolumnDWA FAT A80 GRPTRX BBK NBRis 交易歸屬分行 commentoncolumnDWA FAT A80 GRPTRX BRN NBRis 交易歸屬網點 commentoncolumnDWA FAT A80 GRPTRX GRP NBRis 團隊編號 三 DB2的DDL 表空間 主鍵 分區(qū)鍵 表注釋及字段注釋 Check約束 3 2CREATEINDEXCREATEINDEXCUSTOMER NAMESONCUSTOMER DATA LAST NAME FIRST NAME 1 Select fromcustomer nameswherefirst name A 思考 是否可以使用索引 2 索引的設計需要考慮到系統(tǒng)實際的需要 三 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約束時 視圖支持數(shù)據插入 三 DB2的DDL 3 4CREATENICKNAMECreateNickNameNICK UNICORE NICK CSEASDTAPfor64SERVER UNICORE CSEASDTAP 三 DB2的DDL 3 5CREATEPROCEDURECREATEPROCEDURE LANGUAGESQLBEGINATOMIC DECLARE CONTINUE EXIT UNDO HANDLERFOR END 三 DB2的DDL 3 5CREATEPROCEDURE 續(xù)存儲過程有利于提高系統(tǒng)運行效率存儲過程為靜態(tài)SQL 但可以添加動態(tài)SQL用途 數(shù)據批量處理 報表后臺程序 三 DB2的DDL 3 6RENAMERENAMETABLEUNICORE SYBRNDTAPTOREBRNDTAPUNICORE SYBRNDTAP UNICORE REBRNDTAP數(shù)據庫中將無法查詢到UNICORE SYBRNDTAP表內數(shù)據保持不變 僅表名發(fā)生變化 三 DB2的DDL 四 DB2的DML 4 1SELECT結構4 2FieldRename4 3謂詞的使用 predicate 4 4IN的使用4 5CAST4 6CASE 四 DB2的DML 續(xù) 4 7CURSOR的使用4 8SELECTINTO4 9INSERT4 10UPDATEANDDELETE4 11MERGE4 12JOIN 四 DB2的DML 續(xù) 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的計算優(yōu)先級大于OR 四 DB2的DML 4 4IN說明 完成多值數(shù)據匹配 四 DB2的DML 4 5CAST說明 CAST數(shù)據類型轉換 四 DB2的DML 4 6CASE說明 1 CASE為條件判斷語句 2 注意必須使用ELSE 起到驗證數(shù)據的作用 四 DB2的DML 4 7CURSOR1 返回結果集2 循環(huán)處理 四 DB2的DML 4 8SELECTINTO說明 1 提取數(shù)據賦值給變量2 從表中提取的數(shù)據務必保證其唯一性 否則報錯 四 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語句的執(zhí)行順序 四 DB2的DML 4 12JOIN 續(xù)1 InnerJoin說明 查詢條件寫在Where和Join中效果相同 四 DB2的DML 4 12JOIN 續(xù)2 LeftOuterJoin 對比不同寫法的結果區(qū)別 四 DB2的DML 查詢條件寫在Join中 查詢條件寫在Where中 4 12JOIN 續(xù)2 RightOuterJoin 四 DB2的DML 4 13UNION說明 求合集 注意UNION與UNIONALL的區(qū)別 四 DB2的DML 4 14EXCEPT說明 注意EXCEPT與EXCEPTALL的區(qū)別思考 如果R1中包含兩個E字符 EXCEPT的結果是什么 四 DB2的DML 4 15TEMPORARYTABLE語法 案例 四 DB2的DML 4 15TEMPORARYTABLE 續(xù) 四 DB2的DML 4 15TEMPORARYTABLE 續(xù)案例 說明 1 ONCOMMITPRESERVE DELETEROWS表示在事務提交后是否保留臨時表數(shù)據2 WITHREPLACE表