DB2 SQL命令总结.txt

#1、数据操作语言(DML:select,delete,insert,update)
# <1>查询数据库目录:
# db2 list db directory
# <2>查询数据库中表
db2 list tables 当前用户
db2 list tables for all 所有表
db2 list tables for schema schemaname 指定模式的表
# <3>显示表结构#
db2 describe table tablename#
<4>插入数据#
db2 insert into tablename(字段名,字段名...) values (与字段名一一对应的值)
# db2 insert into tablename1(字段1,字段2,字段3...)
select 字段1,字段2,字段3...from tablename2 + 查询条件
<5>更改表或视图数据
db2 update tablename/viewname set 字段名1='',字段2='',...+查询条件
#<6>删除数据#
db2 delete from tablename where + 条件
<7>导入数据
db2 "import from E:\ of del insert into tableName"
db2 "import from E:\ of mitcount 5000 insert /create/replace into tableName"
db2 "load client from D:\ of del insert/replace into tabName"(不需要写日志,但插入前表必须存在;不能create table)#
db2 "load client from D:\ of del restart/terminate into tabName" 当导入数据出现问题被强行中断时,此表会被加锁,通过此命令可以解锁
# <8>导出数据#
db2 "export to E:\ of del select * from tableName"
db2 "export to E:\ of del MODIFIED BY NOCHARDEL select * from tableName"(导出不带分号的数据)#
导出表结构和数据
db2 "export to E:\ of ixf MODIFIED BY NOCHARDEL select * from tableName"
db2 "export to E:\ of ixf MODIFIED BY NOCHARDEL select * from tableName fetch first (取数+UNM) rows only"(取固定条数)
导出表结构
db2look -d dbName -e -t tableName -o D:\(path) -i userName -w password
# db2look -d dbName -z tabSchema -e -c -i userName -w