欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

db2的常用操作 博客分类: db2数据库 db2 

程序员文章站 2024-03-23 10:56:58
...
db2的常用操作


说明:该博客为日常知识点积累,会不定期更新,不喜勿喷,当然希望能帮到大家

创建表

DROP TABLE T_PATRON_INFO;

CREATE TABLE T_PATRON_INFO (
  USER_ID        INTEGER       GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  UAER_NM	     VARCHAR(16)   NOT NULL DEFAULT ,
  ROW_CRT_TS	 TIMESTAMP 	   NOT NULL DEFAULT ,
  primary key(USER_ID)
)


sequence操作
创建sequence
CREATE SEQUENCE  T_PATRON_INFO_SEQUENCE  AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDER;

CREATE SEQUENCE  T_PATRON_INFO_SEQUENCE1  START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 24;

查询sequence(获取当前值用prevval,下一个值用nextval)
select nextval for T_PATRON_INFO_SEQUENCE from sysibm.sysdummy1

select nextval for T_PATRON_INFO_SEQUENCE from T_PATRON_INFO;


删除sequence
 DROP SEQUENCE T_PATRON_INFO_SEQUENCE   ;


操作操作时间戳,TIMESTAMP(db2插入时间(date)类型)


下面的时间也可以定义为  2016-10-26 22:22:22
也可以用函数代替 current timestamp

INSERT INTO T_PATRON_INFO(UAER_NM,ROW_CRT_TS) values ('patronli','2016-10-26');


增加字段
ALTER TABLE t_patron_info ADD COLUMN sp_cd CHAR(6);


修改字段
ALTER TABLE t_patron_info ALTER sp_cd SET DATA TYPE DECIMAL(3);


db2根据日期分组(日期转字符)(但是在版本较低的db2中不支持to-char)
to_char(row_crt_ts,'YYYY-MM') as transDate,count(0) as num from T_patronli_info  group by to_char(row_crt_ts,'YYYY-MM')


相关标签: db2