4.3　修改數(shù)據(jù)表

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

4.3.1　修改表名

MySQL是通過ALTER TABLE語句來實現(xiàn)表名的修改的，具體的語法規(guī)則如下：

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

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

【例4.13】將數(shù)據(jù)表tb_dept3改名為tb_deptment3。

執(zhí)行修改表名操作之前，使用SHOW TABLES查看數(shù)據(jù)庫中所有的表。

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

　　ALTER TABLE tb_dept3 RENAME tb_deptment3;

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

經(jīng)過比較可以看到，數(shù)據(jù)表列表中已經(jīng)有了名稱為tb_deptment3的表。

4.3.2　修改字段的數(shù)據(jù)類型

修改字段的數(shù)據(jù)類型，就是把字段的數(shù)據(jù)類型轉(zhuǎn)換成另一種數(shù)據(jù)類型。在MySQL中修改字段數(shù)據(jù)類型的語法規(guī)則如下：

　　ALTER TABLE <表名> MODIFY <字段名>　<數(shù)據(jù)類型>

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

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

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

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

　　ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);

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

語句執(zhí)行之后，檢驗會發(fā)現(xiàn)表tb_dept1表中name字段的數(shù)據(jù)類型已經(jīng)修改成了VARCHAR(30)，修改成功。

4.3.3　修改字段名

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

　　ALTER TABLE <表名> CHANGE <舊字段名> <新字段名> <新數(shù)據(jù)類型>;

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

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

　　ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);

使用DESC查看表tb_dept1，會發(fā)現(xiàn)字段的名稱已經(jīng)修改成功，結果如下：

【例4.16】將數(shù)據(jù)表tb_dept1中的loc字段名稱改為location，同時將數(shù)據(jù)類型變?yōu)閂ARCHAR(60)，SQL語句如下：

　　ALTER TABLE tb_dept1 CHANGE loc location VARCHAR(60);

使用DESC查看表tb_dept1，會發(fā)現(xiàn)字段的名稱和數(shù)據(jù)類型均已經(jīng)修改成功，結果如下：

4.3.4　添加字段

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

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

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

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

　　ALTER TABLE tb_dept1 ADD managerId INT(10);

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

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

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

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

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

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

【例4.19】在數(shù)據(jù)表tb_dept1中添加一個INT(11)類型的字段column2，SQL語句如下：

　　ALTER TABLE tb_dept1 ADD column2 INT(11) FIRST;

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

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

【例4.20】在數(shù)據(jù)表tb_dept1中name列后添加一個INT(11)類型的字段column3，SQL語句如下：

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

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

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

4.3.5　刪除字段

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

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

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

【例4.21】刪除數(shù)據(jù)表tb_dept1表中的column2字段。

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

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

　　ALTER TABLE tb_dept1 DROP column2;

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

可以看到，tb_dept1表中已經(jīng)不存在名稱為column2的字段，說明刪除字段成功。

4.3.6　修改字段的排列位置

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

　　ALTER TABLE <表名> MODIFY <字段1> <數(shù)據(jù)類型> FIRST|AFTER <字段2>;

其中，“字段1”指要修改位置的字段；“數(shù)據(jù)類型”指“字段1”的數(shù)據(jù)類型；“FIRST”為可選參數(shù)，指將“字段1”修改為表的第一個字段；“AFTER字段2”指將“字段1”插入到“字段2”后面。

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

【例4.22】將數(shù)據(jù)表tb_deptl中的column1字段修改為表的第一個字段，SQL語句如下：

　　ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;

使用DESC查看表tb_dept1，發(fā)現(xiàn)字段column1已經(jīng)被移至表的第一列，結果如下：

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

【例4.23】將數(shù)據(jù)表tb_dept1中的column1字段插入到location字段后面，SQL語句如下：

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

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

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

4.3.7　更改表的存儲引擎

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

表4.3　MySQL支持的存儲引擎

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

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

【例4.24】將數(shù)據(jù)表tb_deptment3的存儲引擎修改為MyISAM。

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

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

　　mysql> ALTER TABLE tb_deptment3 ENGINE=MyISAM;

使用SHOW CREATE TABLE再次查看表tb_deptment3的存儲引擎，發(fā)現(xiàn)表tb_deptment3的存儲引擎變成了“MyISAM”，結果如下：

4.3.8　刪除表的外鍵約束

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

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

“外鍵約束名”指在定義表時CONSTRAINT關鍵字后面的參數(shù)，詳細內(nèi)容可參考4.1.3節(jié)。

【例4.25】刪除數(shù)據(jù)表tb_emp9中的外鍵約束。

首先創(chuàng)建表tb_emp9，創(chuàng)建外鍵deptId關聯(lián)tb_dept1表的主鍵id，SQL語句如下：

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

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

　　ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;

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

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