db2的常用操作

[align=center][size=large][color=red][b]db2的常用操作[/b][/color][/size][/align]

[color=blue][b]说明：该博客为日常知识点积累，会不定期更新，不喜勿喷，当然希望能帮到大家[/b][/color]

[color=darkblue][b]创建表
[/b][/color]

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)
)

[color=blue][b]sequence操作[/b][/color]
[color=blue][b]创建sequence[/b][/color]

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;

[color=blue][b]查询sequence[/b][/color][color=red][b]（获取当前值用prevval，下一个值用nextval）[/b][/color]

select nextval for T_PATRON_INFO_SEQUENCE from sysibm.sysdummy1

select nextval for T_PATRON_INFO_SEQUENCE from T_PATRON_INFO;

[color=blue][b]删除sequence[/b][/color]

 DROP SEQUENCE T_PATRON_INFO_SEQUENCE   ;

[color=blue][b]操作操作时间戳，TIMESTAMP（db2插入时间（date）类型）
[/b][/color]

[color=red][b]下面的时间也可以定义为 2016-10-26 22：22：22
也可以用函数代替 current timestamp[/b][/color]

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

[color=blue][b]增加字段[/b][/color]

ALTER TABLE t_patron_info ADD COLUMN sp_cd CHAR(6);

[color=blue][b]修改字段[/b][/color]

ALTER TABLE t_patron_info ALTER sp_cd SET DATA TYPE DECIMAL(3);

[color=blue][b]db2根据日期分组（日期转字符）（但是在版本较低的db2中不支持to-char）[/b][/color]

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')