[align=center][size=large][color=red][b]db2的常用操作[/b][/color][/size][/align]
[color=blue][b]说明:该博客为日常知识点积累,会不定期更新,不喜勿喷,当然希望能帮到大家[/b][/color]
[color=darkblue][b]创建表
[/b][/color]
[color=blue][b]sequence操作[/b][/color]
[color=blue][b]创建sequence[/b][/color]
[color=blue][b]查询sequence[/b][/color][color=red][b](获取当前值用prevval,下一个值用nextval)[/b][/color]
[color=blue][b]删除sequence[/b][/color]
[color=blue][b]操作操作时间戳,TIMESTAMP(db2插入时间(date)类型)
[/b][/color]
[color=red][b]下面的时间也可以定义为 2016-10-26 22:22:22
也可以用函数代替 current timestamp[/b][/color]
[color=blue][b]增加字段[/b][/color]
[color=blue][b]修改字段[/b][/color]
[color=blue][b]db2根据日期分组(日期转字符)(但是在版本较低的db2中不支持to-char)[/b][/color]
[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')