DB2常用命令:
- db2 get dbm cfg # 显示数据库管理器配置参数的当前值
- db2ilist #查看系统中存在的实例
- db2 get instance #明确当前运行中的实例
- db2cmd
- db2licm -l #查看版本信息
- db2
- db2 list db directory
- 删除数据库:
db2 force applications all
drop db database
- 导入数据库命令集:
db2 => create database DB_NAME on ‘c:’ using codeset UTF-8 territory CN
db2 => connect to DB_NAME user db2admin using 123321
db2 => commit work
db2 => connect reset
db2 => terminate
cd Y:\backup
db2move DB_NAME import -u db2admin -p 123321
DB2实用性SQL语句 :
- SELECT * FROM ALL_TABLES fetch first 10 rows only
- SELECT TABNAME FROM SYSCAT.columns WHERE COLNAME LIKE ‘%字段名%’
- SELECT COLNAME FROM SYSCAT.columns WHERE TABNAME = ‘数据表名’
- SELECT TABNAME,count(TABNAME) from SYSCAT.columns GROUP BY TABNAME
- SELECT * FROM 数据库名. 模式名称. 数据表名 WHERE 字段名 NOT IN ( SELECT 字段名 FROM 数据库名. 模式名称. 数据表名 FETCH FIRST 5 ROWS ONLY) FETCH FIRST 10 ROWS ONLY
- SELECT DISTINCT 列名称 FROM 表名称 #返回唯一不同的值
- SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName
- SELECT TABLE_NAME FROM ALL_TABLES WHERE NUM_ROWS > 0 #筛选掉空表
- SELECT “字段1”, SUM(“字段2”) FROM “表名” GROUP BY “字段1”
- DELETE FROM table_name #保存数据表的索引、结构,删除所有的行
- DELETE FROM 表名称 WHERE 列名称 = 值 #保存数据表的索引、结构,删除表中的某行
- DROP TABLE TABNAME #删除表结构
SELECT查询结果保存到新表(CREATE TABLE AS)
步骤1 创建汇总表
CREATE TABLE TABNAME AS(
SELECT * FROM TABNAME
)DATA INITIALLY DEFERRED
REFRESH DEFERRED
步骤2 刷新数据
REFRESH TABLE TABNAME
步骤3 把汇总表改成一般的表
ALTER TABLE TABNAME DROP MATERIALIZED QUERY