DB2 Alter Table简介及使用
对于普通表的修改造作,需要考虑下面两种情况:
1.表中有数据
当表中存放着数据的时候,对列的修改仅限于将该列的数据类型修改为相应兼容的数据类型,
或者保持原来的数据类型不变,长度变小了(不能小于存储值的最大长度),或表长了。
2.表中无数据
列的修改还是只能在相互兼容的数据类型之间,有check约束的列不能修改数据类型。
对于表的修改操作,主要就是增加、修改、删除,下面分别从这三个方面去介绍。
1、增加
增加列:ALTER TABLE tablename ADD COLUMN colname DATATYPE
对表的列有如下的一些设置,约束,控制,压缩存储等:
1.CONSTRAINT constraint_name PRIMARY KEY | UNIQUE |
CHECK (colname IN (check_list)) DEFAULT list |
REFERENCES table_name (colname)
2.NOT NULL:非空
3.COMPRESS SYSTEM DEFAULT:对列指定当前数据类型的默认值,若insert时未提供此列的值的时候;
且压缩存储该列值;
该压缩存储的列,不能为DATE,TIME,TIMESTAMP,XML,或者结构化类型;
需要注意的是,该选项会影响insert和update的性能。
增加约束:
主键:ALTER TABLE tablename ADD CONSTRAINT constraint_name PRIMARY KEY(colname)。
外键:ALTER TABLE talename ADD CONSTRAINT constraint_name FOREIGN KEY(colname1)
REFERENCES tablename (colname2) 后面跟着:
1.ON DELETE NO ACTION:当删除colname2的时候,colname1不做任何操作
2.ON DELETE RESTRICT | CASCADE | SET NULL:但删除colname2的时候,restrict限制不让删除,
cascade级联删除colname1的行,set null置colname1为空。
3.ON UPDATE NO ACTION:当colname2被更新的时候,colname1不进行任何操作
4.ON UPDATE RESTRICT:当有参照完整性的时候,不允许先更新父表。
唯一:ALTER TABLE tablename ADD CONSTRAINT constraint_name UNIQUE(colnamme)。
检查:ALTER TABLE tablename ADD CONSTRAINT constraint_name CHECK 后面跟着:
1.(colname IN (check_list)):限定列的取值为列表中的一个,如sex in ('F','M')。
2.(colname1 DETERMINED BY colname2):colname1完全依赖于colname2。
3.(expression):此表达式为一个判断,如:alter table test1 add constraint chk_sal check ((salary + comm) > 80000),
对于违反此表达式约束的操作,将会出现如下错误:
db2 => insert into test1 select * from employee order by salary desc
DB21034E 该命令被当作 SQL 语句来处理,因为它是无效的“命令行处理器”命令。
在SQL 处理期间,它返回:SQL0545N 因为行不满足检查约束"LENOVO.TEST1.CHK_SAL",所以不允许所请求的操作。SQLSTATE=23513
db2 => select salary + comm from test1
1
------------
156970.00
97550.00
101310.00
83389.00
99063.00
92130.00
88242.00
7 条记录已选择。
不能删除表:ALTER TABLE tablename ADD RESTRICT ON DROP
表不能被删除,表所在的表空间也不可以被删除。
自动生成数据的列:ALTER TABLE tablename ADD COLUMN col_name DATATYPE 后面跟着:
1.时间戳:GENERATED ALWAYS|BY DEFAYLT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,该列必须为not null。
2.表达式:GENERATED ALWAYS expression,通过表达式产生此列的值,如,员工总的工资为:
... GENERATED ALWAYS (salary + comm)。
3.固定值:WITH DEFAULT constant --常量
datetime-special-register --特殊寄存器:current_timestamp(current timestamp),current_date(current date),current_time(current time).
user-special-register --与用户有关的寄存器:current_user(current user),
session_user(session user),system_user(system user).
CURRENT SCHEMA --当前模式
NULL --空值
EMPTY_CLOB() --针对CLOB类型的列,写入一个0长度的字符串
EMPTY_DBCLOB() --针对DBCLOB类型的列,写入一个0长度的字符串
EMPTY_BLOB() --针对BLOG类型的列,写入一个0长度的字符串
cast_function(str)★ --针对用户自定义类型,此时DATATYPE为用户自定义类型distinct type;cast_function是自定义类型的名称,
主要就涉及基于blob、date、time、timestamp的自定义类型;
此处参数str可以为上面提到的寄存器,常量值和CURRENT SCHEMA。
★自动生成的列,不能通过alter table语句修改为序列值,换句话说,序列只能在定义表的时候指定。
db2 => create table test2(id integer generated always as identity,sex char(1) check (sex in ('F','M')))
DB20000I SQL 命令成功完成。
db2 => alter table test2 add column name varchar(20) with default 'Unknow'
DB20000I SQL 命令成功完成。
★在表中新增列或者删除列之后,记得重构下表,否则报错:
db2 => insert into test2(sex) values('F')
DB21034E 该命令被当作 SQL 语句来处理,因为它是无效的“命令行处理器”命令。在
SQL 处理期间,它返回:
SQL0668N 不允许对表 "LENOVO.TEST2" 执行操作,原因码为 "7"。 SQLSTATE=57016
db2 => reorg table test2
DB20000I REORG 命令成功完成。
★
db2 => insert into test2(sex) values('F'),('M')
db2 => select * from test2
ID SEX NAME
----------- --- --------------------
1 F Unknow
2 M Unknow
2 条记录已选择。
db2 => alter table test2 add column col_chg timestamp not null generated always for each row on update as row change timestamp
DB20000I SQL 命令成功完成。
db2 => select * from test2
ID SEX NAME COL_CHG
----------- --- -------------------- --------------------------
1 F Unknow 0001-01-01-00.00.00.000000
2 M Unknow 0001-01-01-00.00.00.000000
2 条记录已选择。
db2 => update test2 set name='Scott' where id=2
DB20000I SQL 命令成功完成。
db2 => select * from test2
ID SEX NAME COL_CHG
----------- --- -------------------- --------------------------
1 F Unknow 0001-01-01-00.00.00.000000
2 M Scott 2013-04-01-20.09.27.954000 --★
2 条记录已选择。
db2 => alter table test2 add constraint pk_test2 primary key(id)
DB20000I SQL 命令成功完成。
2、删除
清空表:ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE。
删除列:ALTER TABLE tablename DROP COLUMN column_name CASCADE | RESTRICT
CASCADE:参照此列的所有视图将会失效,所有依赖于该索引,触发器,函数,约束,都将被删除。
RESTRICT:若有其他数据库对象依赖于此列,则不允许删除之。
删除约束:
主键:ALTER TABLE tablename DROP PRIMARY KEY
外键:ALTER TABLE tablename DROP FOREIGN KEY foreignkey_name
唯一:ALTER TABLE tablename DROP UNIQUE unique_name
检查:ALTER TABLE tablename DROP CHECK check_name
其他约束:ALTER TABLE tablename DROP CONSTRAINT constraint_name
非空:ALTER TABLE tablename ALTER COLUMN DROP NOT NULL
解除表删除:ALTER TABLE tablename DROP RESTRICT ON DROP。
删除自动生成列的表达式:ALTER TABLE tablename ALTER COLUMN DROP EXPRESSION
针对定义为自动生成值的列。
删除列的默认值:ALTER TABLE tablename ALTER COLUMN DROP DEFAULT
删除表中列的IDENTITY属性:ALTER TABLE tablename ALTER COLUMN DROP IDENTITY
每个表中只能有一个列被定义为IDENTITY列(非空、不能有默认值)。
3、修改
重命名表:RENAME TABLE tablename old_name to new_name,下面有一篇关于重命名表的文章:DB2修改表名
重命名列:ALTER TABLE tablename RENAME COLUMN old_colname TO new_colname
改变列数据类型:ALTER TABLE tablename ALTER COLUMN col_name SET DATA TYPE data_type
改变约束:
合并分区:
锁大小:
列值压缩存储:ALTER TABLE tablename ALTER COLUMN 后跟着:
1.COMPRESS SYSTEM DEFAULT:压缩存储,前提是定义表的时候指定VALUES COMPRESSION,如:
CREATE TABLE test(column_list) VALUE COMPRESSION。
2.OFF:对数据不进行压缩存储。
激活或禁用数据压缩:ALTER TABLE tablename ACIVATE | DEACTIVATE VALUE COMPRESSION
自动填充列的值:ALTER TABLE tablename ALTER COLUMN 后面跟着:
1.SET GENERATED ALWAYS | BY DEFAULT 后面跟着下面的序列信息:
2.SET INCREMENT BY constant
SET NO MINVALUE | MINVALUE constant
SET NO MAXVALUE | MAXVALUE constant
SET NO CYCLE | CYCLE
SET NO CACHE | CACHE
SET NO ORDER | ORDER
RESTART | RESTART WITH constant。
这里提到的主要是针对在定义表的时候,指定了增长序列的列,
如create table tb (col_1 integer start with 1 increment by 2 no maxvalue no cycle cache 10 no order,...)
第二点可以作为独立的选项,如alter table tablename alter column_alter set increment by 10等
3.SET EXPRESSION AS expression:修改该自动生成列值的产生表达式。
附加数据:ALTER TABLE tablename APPEND ON|OFF
ON:新添加的数据将会插入到有空闲空间的页中;
OFF:新增加的数据存放到最后一个页上,若该页存放满了,则数据将会存放到下一个页上。
数据的最小存放空间为页(page),页大小有4K,8K,16K,32K。
清空表数据:
实例:
db2 => create table test1 like employee
DB20000I SQL 命令成功完成。
db2 => select count(*) from test1
1
-----------
0
1 条记录已选择。
db2 => insert into test1 select * from employee
DB20000I SQL 命令成功完成。
db2 => select count(*) from test1
1
-----------
42
1 条记录已选择。
db2 => alter table test1 activate not logged initially with empty table
DB20000I SQL 命令成功完成。
db2 => select count(*) from test1
1
-----------
0
1 条记录已选择。
来源:http://blog.csdn.net/bobo12082119/article/details/8749494