2.5　修改數據表

修改表指的是修改數據庫中已經存在的數據表的結構。MySQL使用ALTER TABLE語句修改表。常用的修改表的操作有：修改表名、修改字段數據類型或字段名、增加和刪除字段、修改字段的排列位置、更改表的存儲引擎、刪除表的外鍵約束等。本節將對和修改表有關的操作進行講解。

2.5.1　修改表名

MySQL是通過ALTER TABLE語句來實現表名的修改的，具體的語法規則如下：

     ALTER TABLE <舊表名> RENAME [TO] <新表名>;

其中TO為可選參數，使用與否均不影響結果。

【例2.16】將數據表tb_dept3改名為tb_deptment3。

執行修改表名操作之前，使用SHOW TABLES查看數據庫中所有的表。

使用ALTER TABLE將表tb_dept3改名為tb_deptment3，SQL語句如下：

     ALTER TABLE tb_dept3 RENAME tb_deptment3;

語句執行之后，檢驗表tb_dept3是否改名成功。使用SHOW TABLES查看數據庫中的表，結果如下：

經過比較可以看到，數據表列表中已經有了名稱為tb_deptment3的表。

2.5.2　修改字段的數據類型

修改字段的數據類型就是把字段的數據類型轉換成另一種數據類型。在MySQL中修改字段數據類型的語法規則如下：

     ALTER TABLE <表名> MODIFY <字段名>  <數據類型>

其中，“表名”指要修改數據類型的字段所在表的名稱，“字段名”指需要修改的字段，“數據類型”指修改后字段的新數據類型。

【例2.17】將數據表tb_dept1中name字段的數據類型由VARCHAR(22)修改成VARCHAR(30)。

執行修改表名操作之前，使用DESC查看tb_dept1表結構，結果如下：

可以看到現在name字段的數據類型為VARCHAR(22)，下面修改其類型。輸入如下SQL語句并執行：

     ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);

再次使用DESC查看表，結果如下：

語句執行之后，檢驗會發現表tb_dept1表中name字段的數據類型已經修改成了VARCHAR(30)，修改成功。

2.5.3　修改字段名

MySQL中修改表字段名的語法規則如下：

     ALTER TABLE <表名> CHANGE <舊字段名> <新字段名> <新數據類型>;

其中，“舊字段名”指修改前的字段名；“新字段名”指修改后的字段名；“新數據類型”指修改后的數據類型，如果不需要修改字段的數據類型，那么將新數據類型設置成與原來一樣即可，但數據類型不能為空。

【例2.18】將數據表tb_dept1中的location字段名稱改為loc，數據類型保持不變，SQL語句如下：

     ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);

使用DESC查看表tb_dept1，會發現字段的名稱已經修改成功，結果如下：

【例2.19】將數據表tb_dept1中的loc字段名稱改為location，同時將數據類型變為VARCHAR(60)，SQL語句如下：

     ALTER TABLE tb_dept1 CHANGE loc location VARCHAR(60);

使用DESC查看表tb_dept1，會發現字段的名稱和數據類型均已經修改成功，結果如下：

2.5.4　添加字段

隨著業務需求的變化，可能需要在已經存在的表中添加新的字段。一個完整字段包括字段名、數據類型、完整性約束。添加字段的語法格式如下：

“新字段名”為需要添加的字段的名稱；FIRST為可選參數，其作用是將新添加的字段設置為表的第一個字段；AFTER為可選參數，其作用是將新添加的字段添加到指定的“已存在字段名”的后面。

1. 添加無完整性約束條件的字段

【例2.20】在數據表tb_dept1中添加一個沒有完整性約束的INT類型的字段managerId（部門經理編號），SQL語句如下：

     ALTER TABLE tb_dept1 ADD managerId INT(10);

使用DESC查看表tb_dept1，會發現在表的最后添加了一個名為managerId的INT類型的字段，結果如下：

2. 添加有完整性約束條件的字段

【例2.21】在數據表tb_dept1中添加一個不能為空的VARCHAR(12)類型的字段column1，SQL語句如下：

     ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) not null;

使用DESC查看表tb_dept1，會發現在表的最后添加了一個名為column1的VARCHAR(12)類型且不為空的字段，結果如下：

3. 在表的第一列添加一個字段

【例2.22】在數據表tb_dept1中添加一個INT類型的字段column2，SQL語句如下：

     ALTER TABLE tb_dept1 ADD column2 INT(11) FIRST;

使用DESC查看表tb_dept1，會發現在表第一列添加了一個名為column2的INT(11)類型的字段，結果如下：

4. 在表的指定列之后添加一個字段

【例2.23】在數據表tb_dept1中name列后添加一個INT類型的字段column3，SQL語句如下：

     ALTER TABLE tb_dept1 ADD column3 INT(11) AFTER name;

使用DESC查看表tb_dept1，結果如下：

可以看到，tb_dept1表中增加了一個名稱為column3的字段，其位置在指定的name字段后面，添加字段成功。

2.5.5　刪除字段

刪除字段是將數據表中的某個字段從表中移除，語法格式如下：

     ALTER TABLE <表名> DROP <字段名>;

“字段名”指需要從表中刪除的字段的名稱。

【例2.24】刪除數據表tb_dept1表中的column2字段。

首先，執行刪除字段之前，使用DESC查看tb_dept1表結構，結果如下：

刪除column2字段，SQL語句如下：

     ALTER TABLE tb_dept1 DROP column2;

再次使用DESC查看表tb_dept1，結果如下：

可以看到，tb_dept1表中已經不存在名稱為column2的字段，刪除字段成功。

2.5.6　修改字段的排列位置

對于一個數據表來說，在創建的時候，字段在表中的排列順序就已經確定了。但表的結構并不是完全不可以改變的，可以通過ALTER TABLE來改變表中字段的相對位置。語法格式如下：

     ALTER TABLE <表名> MODIFY <字段1> <數據類型> FIRST|AFTER <字段2>;

“字段1”指要修改位置的字段；“數據類型”指“字段1”的數據類型；FIRST為可選參數，指將“字段1”修改為表的第一個字段；“AFTER <字段2>”指將“字段1”插入“字段2”后面。

1. 修改字段為表的第一個字段

【例2.25】將數據表tb_deptl中的column1字段修改為表的第一個字段，SQL語句如下：

     ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;

使用DESC查看表tb_dept1，發現字段column1已經被移至表的第一列，結果如下：

2. 修改字段到表的指定列之后

【例2.26】將數據表tb_dept1中的column1字段插入location字段后面，SQL語句如下：

     ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER location;

使用DESC查看表tb_dept1，結果如下：

可以看到，tb_dept1表中的字段column1已經被移至location字段之后。

2.5.7　更改表的存儲引擎

通過前面章節的學習，知道存儲引擎是MySQL中的數據存儲在文件或者內存中時采用的不同技術實現。可以根據自己的需要選擇不同的引擎，甚至可以為每一張表選擇不同的存儲引擎。MySQL中主要的存儲引擎有MyISAM、InnoDB、MEMORY（HEAP）、BDB、FEDERATED等。可以使用SHOW ENGINES;語句查看系統支持的存儲引擎。表2.3列出了5.5.13版本的MySQL所支持的存儲引擎。

表2.3　MySQL支持的存儲引擎

更改表的存儲引擎的語法格式如下：

     ALTER TABLE <表名> ENGINE=<更改后的存儲引擎名>;

【例2.27】將數據表tb_deptment3的存儲引擎修改為MyISAM。

在修改存儲引擎之前，先使用SHOW CREATE TABLE查看表tb_deptment3當前的存儲引擎，結果如下：

可以看到，表tb_deptment3當前的存儲引擎為ENGINE=InnoDB。接下來修改存儲引擎類型，輸入如下SQL語句并執行：

     mysql> ALTER TABLE tb_deptment3 ENGINE=MyISAM;

使用SHOW CREATE TABLE再次查看表tb_deptment3的存儲引擎，發現表tb_dept的存儲引擎變成了“MyISAM”，結果如下：

2.5.8　刪除表的外鍵約束

對于數據庫中定義的外鍵，如果不再需要，那么可以將其刪除。外鍵一旦刪除，就會解除主表和從表間的關聯關系，MySQL中刪除外鍵的語法格式如下：

     ALTER TABLE <表名> DROP FOREIGN KEY <外鍵約束名>

“外鍵約束名”指在定義表時CONSTRAINT關鍵字后面的參數，詳細內容可參考2.3.3節的“使用外鍵約束”。

【例2.28】刪除數據表tb_emp9中的外鍵約束。

首先創建表tb_emp9，創建外鍵deptId關聯tb_dept1表的主鍵id，SQL語句如下：

使用SHOW CREATE TABLE查看表tb_emp9的結構，結果如下：

可以看到，已經成功添加了表的外鍵。下面刪除外鍵約束，SQL語句如下：

     ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;

執行完畢之后，將刪除表tb_emp9的外鍵約束，使用SHOW CREATE TABLE再次查看表tb_emp9的結構，結果如下：

可以看到，tb_emp9中已經不存在FOREIGN KEY，原有的名稱為fk_emp_dept的外鍵約束刪除成功。