DB2常用命令

1 创建数据库 :
  ->db2 create db dbname
  创建表:
  ->db2 create table name(col1 type1 [not null][primary key],col2 type2 [not null]) in tablespace_name
  根据已有的表创建新表:
  ->db2 create table new like old
  ->db2 create table new as select col1,col2... from old definition only
  创建表空间 :
  ->db2 create tablespace name pagesize 4k managed by database using (file/device 'file/rlv' size) bufferpool bfp_name extentsize prefecthsz
  创建缓冲池:
  ->db2 create bufferpool name immediate size 250 pagesize 4 K
  创建index:
  ->db2 create index name on tabname(col)
  创建view:
  ->db2 create view name as select statement
2 删除 数据 库:
  ->db2 drop db name
  删除表:
  ->db2 drop table name
  删除表空间:
  ->db2 drop tablespce name
  删除缓冲池:
  ->db2 drop bufferpool name
  删除索引 :
  ->db2 drop index name
  删除view:
  ->db2 drop view name
3 查看哪些view可以执行删除：
  ->db2 select * from sysibm.sysviews where creator='USER'
4 Alter table:
  ->db2 alter table name add primary key(col)
  ->db2 alter table name drop primary key(col)
  ->db2 rename old to new
  ->db2 alter table name activate not logged initially with empty table:快速清表
  ->db2 alter table name activate not logged initially:关闭表的log
5 ->db2 alter tablespace name ...
  ->db2 alter bufferpool name ...
6 ->db2 select ..from tabname where...
  ->db2 delete from tabname where...
  ->db2 insert into tabname(col1,col2) values(value1,value2)
  ->db2 update tabname set (col1=value1,col2=value2) where...
7 with:
  ->db2 with a1 as (select max(id) as aa1 from test) select id,aa1 from test1,a1
8 case:
  ->db2 select id,name,case when integer(flag)=0 then '假' when integer(flag)=1 then '真' else '异常' end from test
  ->db2 select id,name,case integer(flag) when 0 then '假' when 1 then '真' else '异常' end from test
9 case:类型 转换
  ->db2 select case (current time as char(8)) from sysibm.sysdummy
10 查看sql 执行需要的时间 :
   ->db2batch -d sample -f test.sql:其中test.sql里面为一个需要执行的sql语句
11 value:
   ->db2 value(timestamp)
   ->db2 select value(id,'') from t1
12 取得表的行数:
   ->db2 select row_number()over() from test
   ->db2 select count(*) from test
13 DDL:create/drop
   DML:select/insert/update/delete
   DCL:grant/revoke/commit/rollback
14 union:组合2个表的结果,all不消除重复的行
   except:包含在表一但不在表二的行
   intersect:2个表的交集
15 left outer join:包括连接表的匹配行与左连接表的行
   right outer join:包括连接表的匹配行与右连接表的行
   full outer join:包括连接表的匹配行与2个表的行
16 group by...having