DB2内部培训.ppt

1、DB2内部培训第1页，共13页。DB2逻辑组成图第2页，共13页。DB2基本概念 实例:Instance 数据库：Database 用户模式:Schema 表空间：DMS SMS 表：Table第3页，共13页。DB2安装与配置 Win系统安装 实例目录：DB2 安装目录$:Program FilesIBMSQLLIB 运行：db2cmd 建库：create db dbname;drop db dbname 建表空间：建表：第4页，共13页。DB2安装与配置 LinuxAIX系统安装 安装DB2程序 建用户：实例用户和管理器用户 建实例：db2inst1 建库：create db dbname

2、 建表空间：建表：第5页，共13页。DB2管理类命令 Db2start ,db2stop,Db2stop force Catalog List db directory Db2 list application(show detail)Db2 force application all Db2 get dbm cfg(update dbm cfg using XXX?)Db2 get db cfg(update db cfg using XXX?)DB2?SQLCODE,db2?sql0803 Db2 connect to Dbname ;db2 connect reset第6页，共13页。D

3、B2数据库对象CREATE 语句用于创建数据库对象，包括：n缓冲池（Buffer pool）n事件监控程序（Event monitor）n函数（Function）n索引（Index）n模式（Schema）n存储过程（Stored procedure）n表（Table）n表空间（Table space）n触发器（Trigger）n视图（View）第7页，共13页。DB2数据维护 Export to fliename of del select*from tabname Import from filename of del insert into tabnameimport from filen

4、ame of del|ixf replace into tabname import from filename of del|ixf commitcount 1000 replace into tabname Load from filename of del insert into tabname nonrecoverable Db2move dbname export Db2move dbname import Backup:backup db dbname Restore:restore db dbname第8页，共13页。DB2SQL 列函数:rtrim,length,substr。

5、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 on a.t1=b.t1 聚合：sum,min,max,avg，count(*)Select a,sum(fse)from tab group by a having sum(fse)1000 Select distinct col from tab 字符串匹配 like qwe%范围查找between and 空值查询 col is null 查找一组值 col in(1,2);Select

6、,update,delete,insert第9页，共13页。DB2SQL子查询Select*from tabA where col1 in(select col from tabB);case 表达式Select case When score 65 then not passed When score=90 then passed Else excellent EndFrom tabA第10页，共13页。DB2SQL集合运算符 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 col from tabB第11页，共13页。DB2SQLinsert selectInsert into tabA select col1,col2 from tabB选择的列与插入的列数目、类型一致.第12页，共13页。演讲完毕，谢谢观看！第13页，共13页。