DB2 Alter Table简介及使用

页面导航:首页 > 数据库 > DB2 > DB2 Alter Table简介及使用

DB2 Alter Table简介及使用

来源: 作者: 时间:2016-01-23 11:15 【

DB2 Alter Table简介及使用对于普通表的修改造作,需要考虑下面两种情况:1.表中有数据当表中存放着数据的时候,对列的修改仅限于将该列的数据类型修改为相应兼容的数据类型,或者保
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,下面有一篇关于重命名表的文章:修改表名
重命名列: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
 
--the end--
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<