DB2常用SQL语句集.doc

DB2常用SQL语句集 查看表结构： describe table tablename describe select * from tablename 列出系统数据库目录的内容： list database directory 查看数据库配置文件的内容： get database configuration for DBNAME 启动数据库： restart database DBNAME 关闭表的日志 alter table TBLNAME active not logged inially 重命名表 rename TBLNAME1 to TBLNAME2 取当前时间 select current time stamp from sysibm.sysdummy1 创建别名 create alias ALIASNAME for PRONAME(table、view、alias、nickname) 查询前几条记录 select?*?from?TBLNAME??fetch?first?N?rows? 联接数据库 db2?connect?to?DB?user?db2?using?PWD 绑定存储过程命令 db2?bind?BND.bnd 整理优化表 db2 reorgchk on table TBLNAME db2 reorg table TBLNAME db2?runstats?on?table?TBNAME?with?distribution?and?indexes?all 导出表 db2?export?to?TBL.txt?of?del?select?*?from?TBLNAME db2?export?to?TBL.ixf?of?ixf?select?*?from?TBLNAME 以指定分隔符‘|’下载数据： db2?export?to?cmmcode.txt?of?del?modified?by?coldel|?select?*?from?cmmcode” 导入表 db2 import?from?TBL.txt?of?del??insert?into?TBLNAME db2 import?from?TBL.txt?of?del commitcount?5000??insert?into?TBLNAME db2?import?from?TBL.ixf?of?ixf?commitcount?5000?insert?into?TBLNAME db2?import?from?TBL.ixf?of?ixf?commitcount?5000?insert_update?into?TBLNAME db2?import?from?TBL.ixf?of?ixf?commitcount?5000?replace?into?TBLNAME db2?import?from?TBL.ixf?of?ixf?commitcount?5000?create?into?TBLNAME?(仅IXF)db2?import?from?TBL.ixf?of?ixf?commitcount?5000?replace_create?into?TBLNAME?(仅IXF) 以指定分隔符“|”加载db2?import?from?btpoper.txt?of?del?modified?by?coldel|?insert?into?btpoper 显示当前用户所有表命令 db2 “list tables” 查看锁情况命令： db2?get?snapshot?for?locks?on?DBNAME list?applications?for?db?DBNAME?show?detail 打开锁的监视开关命令 db2?update?monisor?switches?using?lock?on 游标的使用： declare cursorN cursor with hold for select CHAR from TBNAME for update; for update不能和GROUP BY、 DISTINCT、 ORDER BY、 FOR READ ONLY及UNION, EXCEPT, or INTERSECT（但 UNION ALL除外）一起使用 。 update TBNAME set CHAR=0 where current of cursorN; decode的转码操作： decode(A1,1,n1,2,n2,n3) AA1 from TBNAME; 计算两个日期的相差天数： days(date(‘2001-06-05’)) – days(date(‘2001-04-01’)) days 返回的是从 0001-01-01 开始计算的天数； 取得处理的记录数