DB2內(nèi)部培訓(xùn)ppt課件

1、DB2內(nèi)部培訓(xùn)DB2邏輯組成圖DB2根本概念實(shí)例:Instance數(shù)據(jù)庫：Database用戶方式: Schema表空間：DMS SMS表：TableDB2安裝與配置 Win系統(tǒng)安裝實(shí)例目錄：DB2安裝目錄$:Program FilesIBMSQLLIB運(yùn)轉(zhuǎn)：db2cmd建庫：create db dbname;drop db dbname建表空間： 建表：DB2安裝與配置 LinuxAIX系統(tǒng)安裝安裝DB2程序建用戶：實(shí)例用戶和管理器用戶建實(shí)例：db2inst1建庫：create db dbname建表空間： 建表：DB2管理類命令Db2start ,db2stop, Db2stop forc

2、eCatalogList db directoryDb2 list application (show detail)Db2 force application allDb2 get dbm cfg(update dbm cfg using XXX ?)Db2 get db cfg(update db cfg using XXX ?)DB2 ?SQLCODE, db2 ? sql0803Db2 connect to Dbname ;db2 connect resetDB2數(shù)據(jù)庫對(duì)象CREATE 語句用于創(chuàng)建數(shù)據(jù)庫對(duì)象，包括：緩沖池Buffer pool 事件監(jiān)控程序Event monitor

3、函數(shù)Function 索引Index 方式Schema 存儲(chǔ)過程Stored procedure 表Table 表空間Table space 觸發(fā)器Trigger 視圖View DB2數(shù)據(jù)維護(hù)Export to fliename of del select * from tabnameImport from filename of del insert into tabnameimport from filename of del|ixf replace into tabname import from filename of del|ixf commitcount 1000 replace

4、into tabnameLoad from filename of del insert into tabname nonrecoverable Db2move dbname export Db2move dbname importBackup: backup db dbnameRestore: restore db dbnameDB2SQL列函數(shù):rtrim,length,substr。Join： selet a.col,b.col from tab a,tab b where a.t1=b.t1; select a.col,b.col from tab a left join tab b

5、on a.t1=b.t1聚合：sum,min,max,avg，count(*)Select a, sum(fse) from tab group by a having sum(fse)1000Select distinct col from tab字符串匹配 like qwe%范圍查找between and空值查詢 col is null查找一組值 col in (1,2);Select ,update, delete, insertDB2SQL子查詢Select * from tabA where col1 in (select col from tabB);case 表達(dá)式Select case When score 65 then not passed When score=90 then passed Else excellent EndFrom tabADB2SQL集合運(yùn)算符 union intersect except并：union select col from tabA union select col from tabB交: intersect select col from tabA intersect select col from tabB差：except select col from tabA except select