DB2内部培训

DB2内部培训DB2逻辑组成图DB2基本概念实例:Instance数据库：Database用户\模式:Schema表空间：DMSSMS表：TableDB2安装与配置Win系统安装实例目录：DB2安装目录$:\ProgramFiles\IBM\SQLLIB运行：db2cmd建库：createdbdbname;dropdbdbname建表空间：建表：DB2安装与配置Linux\AIX系统安装安装DB2程序建用户：实例用户和管理器用户建实例：db2inst1建库：createdbdbname建表空间：建表：DB2管理类命令Db2start,db2stop,Db2stopforceCatalogListdbdirectoryDb2listapplication(showdetail)Db2forceapplicationallDb2getdbmcfg(updatedbmcfgusingXXX???)Db2getdbcfg(updatedbcfgusingXXX???)DB2?SQLCODE,db2?sql0803Db2connecttoDbname;db2connectresetDB2数据库对象CREATE语句用于创建数据库对象，包括：缓冲池（Bufferpool）事件监控程序（Eventmonitor）函数（Function）索引（Index）模式（Schema）存储过程（Storedprocedure）表（Table）表空间（Tablespace）触发器（Trigger）视图（View）DB2数据维护Exporttoflienameofdelselect*fromtabnameImportfromfilenameofdelinsertintotabnameimportfromfilenameofdel|ixfreplaceintotabnameimportfromfilenameofdel|ixfcommitcount1000replaceintotabnameLoadfromfilenameofdelinsertintotabnamenonrecoverableDb2movedbnameexportDb2movedbnameimportBackup:backupdbdbnameRestore:restoredbdbnameDB2SQL列函数:rtrim,length,substr。。。。Join：seleta.col,b.colfromtaba,tabbwherea.t1=b.t1;selecta.col,b.colfromtabaleftjointabbona.t1=b.t1聚合：sum,min,max,avg，count(*)Selecta,sum(fse)fromtabgroupbyahavingsum(fse)1000Selectdistinctcolfromtab字符串匹配like‘qwe%’范围查找betweenand空值查询colisnull查找一组值colin(1,2);Select,update,delete,insertDB2SQL子查询Select*fromtabAwherecol1in(selectcolfromtabB);case表达式SelectcaseWhenscore65then‘notpassed’Whenscore=90then‘passed’Else‘excellent’EndFromtabADB2SQL集合运算符unionintersectexcept并：unionselectcolfromtabAunionselectcolfromtabB交:intersectselectcolfromtabAintersectselectcolfromtabB差：exceptselectcolfromtabAexceptselectcolfromtabBDB2SQLinsert…selectInsertintotabAselectcol1,col2fromtabB选择的列与插入的列数目、类型一致.