1.数据库启动：db2start
数据库停止：db2stop
数据库重启：db2restart
2.显示当前活动数据库
list active databases
系统数据库目录
list database directory
3.建立数据库db2
create database db2
4.连接数据库 db2 密码：abc
connect to db2 user db2admin using abc
5.显示所有表
list tables
6.建立表 tt
create table tt(id int ,name varchar(30))
7.插入数据
insert into tt values(1,'username')
8.显示数据
select 数据 from tt where id=1
select * from tt (全表数据显示)
查看列
select SUBSTR(COLNAME,1,20) as 列名,TYPENAME as 类型,LENGTH as 长度
from columns
where tabname='tt';
9.查看表结构
describe table tt
describe select * from tt
10.列出所有的系统表
list tables for system
11.建立视图 hh
create view hh as select tabschema,tabname from tt;
12.建立触发器
CREATE TRIGGER tt_del
AFTER DELETE ON tt
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
Insert into tt values(1,'jj')
13.建立唯一性索引
CREATE UNIQUE INDEX hh
ON tt_tables(tabname);
14.查看表的索引
db2 describe indexes for table tt
15.查看视图
select viewname from views
where viewname='tt';
16.查看索引
select indname from indexes
where indname='tt';
17.查看存贮过程
SELECT SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15)
FROM SYSCAT.PROCEDURES;
18.类型转换(cast)
ip datatype:varchar
select cast(ip as integer)+50 from log_comm_failed
19. view application
LIST APPLICATION;
20. kill application
FORCE APPLICATION(0);
db2 force applications all (强迫所有应用程序从数据库断开)
21. lock table
lock table test in exclusive mode
22. 共享
lock table test in share mode
23. 查看命令选项
list command options
24. 表空间
list tablespaces
25. 表空间容器
LIST TABLESPACE CONTAINERS FOR
Example: LIST TABLESPACE CONTAINERS FOR 1
26. 显示用户数据库的存取权限
GET AUTHORIZATIONS
27. 信息帮助 (? XXXnnnnn )
例:? SQL30081
28. SQL 帮助(说明 SQL 语句的语法)
help statement
例如，help SELECT
29. SQLSTATE 帮助(说明 SQL 的状态和类别代码)
? sqlstate 或 ? class-code
30. 更改与"管理服务器"相关的口令
db2admin setid username password
31. 修改表结构
create table tt
(id varchar(10),
password varchar(10),
name varchar(10),
);
create table hh
(id varchar(10),
password varchar(10)
name varchar(60))
alter table tt ALTER name SET DATA TYPE varchar(13);
alter table hh ALTER id SET DATA TYPE int;
alter table tt alter hh set id type int;
insert into tt values
(1,'123456','user01'),
(2,'123456','user02');
数据库停止：db2stop
数据库重启：db2restart
2.显示当前活动数据库
list active databases
系统数据库目录
list database directory
3.建立数据库db2
create database db2
4.连接数据库 db2 密码：abc
connect to db2 user db2admin using abc
5.显示所有表
list tables
6.建立表 tt
create table tt(id int ,name varchar(30))
7.插入数据
insert into tt values(1,'username')
8.显示数据
select 数据 from tt where id=1
select * from tt (全表数据显示)
查看列
select SUBSTR(COLNAME,1,20) as 列名,TYPENAME as 类型,LENGTH as 长度
from columns
where tabname='tt';
9.查看表结构
describe table tt
describe select * from tt
10.列出所有的系统表
list tables for system
11.建立视图 hh
create view hh as select tabschema,tabname from tt;
12.建立触发器
CREATE TRIGGER tt_del
AFTER DELETE ON tt
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
Insert into tt values(1,'jj')
13.建立唯一性索引
CREATE UNIQUE INDEX hh
ON tt_tables(tabname);
14.查看表的索引
db2 describe indexes for table tt
15.查看视图
select viewname from views
where viewname='tt';
16.查看索引
select indname from indexes
where indname='tt';
17.查看存贮过程
SELECT SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15)
FROM SYSCAT.PROCEDURES;
18.类型转换(cast)
ip datatype:varchar
select cast(ip as integer)+50 from log_comm_failed
19. view application
LIST APPLICATION;
20. kill application
FORCE APPLICATION(0);
db2 force applications all (强迫所有应用程序从数据库断开)
21. lock table
lock table test in exclusive mode
22. 共享
lock table test in share mode
23. 查看命令选项
list command options
24. 表空间
list tablespaces
25. 表空间容器
LIST TABLESPACE CONTAINERS FOR
Example: LIST TABLESPACE CONTAINERS FOR 1
26. 显示用户数据库的存取权限
GET AUTHORIZATIONS
27. 信息帮助 (? XXXnnnnn )
例:? SQL30081
28. SQL 帮助(说明 SQL 语句的语法)
help statement
例如，help SELECT
29. SQLSTATE 帮助(说明 SQL 的状态和类别代码)
? sqlstate 或 ? class-code
30. 更改与"管理服务器"相关的口令
db2admin setid username password
31. 修改表结构
create table tt
(id varchar(10),
password varchar(10),
name varchar(10),
);
create table hh
(id varchar(10),
password varchar(10)
name varchar(60))
alter table tt ALTER name SET DATA TYPE varchar(13);
alter table hh ALTER id SET DATA TYPE int;
alter table tt alter hh set id type int;
insert into tt values
(1,'123456','user01'),
(2,'123456','user02');
32. 表或视图特权
grant select,delete,insert,update on tables to user
grant all on tables to user WITH GRANT OPTION
33. 程序包特权
GRANT EXECUTE
ON PACKAGE PACKAGE-name
TO PUBLIC
35. 模式特权
GRANT CREATEIN ON SCHEMA SCHEMA-name TO USER
36. 数据库特权
grant connect,createtab,dbadm on database to user
37. 索引特权
grant control on index index-name to user
grant select,delete,insert,update on tables to user
grant all on tables to user WITH GRANT OPTION
33. 程序包特权
GRANT EXECUTE
ON PACKAGE PACKAGE-name
TO PUBLIC
35. 模式特权
GRANT CREATEIN ON SCHEMA SCHEMA-name TO USER
36. 数据库特权
grant connect,createtab,dbadm on database to user
37. 索引特权
grant control on index index-name to user
