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 --the end--
