19、数据类型
数字型：整型：smallint integer bigint
小数：decfloat decimal numeric
浮点型：real double

字符串：字节型：单字节：char varchar clob
双字节：graphic dbclob
二进制型：blob
时间：date time timestamp

XML:xml
20、描述表,查看用户下所有的表
db2 describe table db2inst1.employee
list tables
list tables for schema
list tables for all
syscat.tables
21、约束
1\db2 "alter table t1 add primary key(c4)"
2\db2 "alter table albums primary key(itemno) add constraint fkartno foreign key(artno) references artists on delete cascade"
3\db2 "alter table t1 add unique(c4)"
4\db2 "alter table stock add constraint cctype check(type in ('1','2','3','4'))"

22、表压缩：对表中字段重复值进行替换
db2 alter table table_name compress yes;
db2 reorg table db2inst1.emp; -- 重组

describe indexes for table show detail
syscat.indexes # 索引视图

23、序列
create sequence db2inst1.seq_zbn as bigint start with 1 increment by 1 no maxvalue cycle cache 100 # 缓存100，在内存中的缓存值，太大的话，很快序列就到达最大值

create sequence my_seq start with 1 increment by 1 cache 100
db2 insert into t1 values(nextval for my_seq,'xxx');
syscat.sequence

字段自增 generated always as identity 系统自动产生，用户不能指定,generated by default as identity 用户可以指定，未指定时系统自动生成
create table t1(
custno bigint not null generated always as identity(start with 100, increment by 1,minvalue 100,maxvalue 99999999,no cycle,no cache,no order),
custname varchar(16)
)

insert into t1 values('xxx');
select * from syscat.columns;

ITPub博客

6

最新文章