[SQL] Alter table | 修改資料表

修改table 屬性:

mysql> alter table testtable2 CHARSET = utf8;
看修改後的結果:
mysql> show create table testtable2;

| testtable2 | CREATE TABLE `testtable2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(35) CHARACTER SET latin1 NOT NULL,
  `price` char(35) CHARACTER SET latin1 NOT NULL,
  `car` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |

查看索引資訊:

mysql> show index from testtable2;

增加一個欄位

mysql> alter table testtable2 add job varchar(20) default 'sw'after name;
mysql> select* from testtable2;                             
+----+-------+------+-------+-----+
| id | name  | job  | price | car |
+----+-------+------+-------+-----+
|  1 | lewis | sw   | 2     |   2 |
|  2 | lewis | sw   | 3     | 111 |
|  3 | leo   | sw   | 4     | 123 |
|  4 | jim   | sw   | 7     |   6 |
+----+-------+------+-------+-----+
4 rows in set (0.00 sec)

增加多個欄位

mysql> alter table testtable2 add (job2 varchar(20) , year varchar(20) );
mysql> select* from testtable2;                             
+----+-------+------+-------+-----+------+------+
| id | name  | job  | price | car | job2 | year |
+----+-------+------+-------+-----+------+------+
|  1 | lewis | sw   | 2     |   2 | NULL | NULL |
|  2 | lewis | sw   | 3     | 111 | NULL | NULL |
|  3 | leo   | sw   | 4     | 123 | NULL | NULL |
|  4 | jim   | sw   | 7     |   6 | NULL | NULL |
+----+-------+------+-------+-----+------+------+
4 rows in set (0.00 sec)

修改欄位: 
1.CHANGE 可以修改欄位的名稱與定義.
2.MODIFY 只能修改欄位的定義，不能修改欄位名稱.
 一般來說會覺得change涵蓋兩種功能所以我們用change就好，
MODIFY可以防止不小心改到欄位名的狀況.

CHANGE

mysql> alter table testtable2 change job2 title varchar(30);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select* from testtable2;                             
+----+-------+------+-------+-----+-------+------+
| id | name  | job  | price | car | title | year |
+----+-------+------+-------+-----+-------+------+
|  1 | lewis | sw   | 2     |   2 | NULL  | NULL |
|  2 | lewis | sw   | 3     | 111 | NULL  | NULL |
|  3 | leo   | sw   | 4     | 123 | NULL  | NULL |
|  4 | jim   | sw   | 7     |   6 | NULL  | NULL |
+----+-------+------+-------+-----+-------+------+
4 rows in set (0.00 sec)

MODIFY

mysql> alter table testtable2 modify year int(10) after title;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select* from testtable2;                            
+----+-------+------+-------+-----+-------+------+
| id | name  | job  | price | car | title | year |
+----+-------+------+-------+-----+-------+------+
|  1 | lewis | sw   | 2     |   2 | NULL  | NULL |
|  2 | lewis | sw   | 3     | 111 | NULL  | NULL |
|  3 | leo   | sw   | 4     | 123 | NULL  | NULL |
|  4 | jim   | sw   | 7     |   6 | NULL  | NULL |
+----+-------+------+-------+-----+-------+------+
4 rows in set (0.00 sec)

刪除其中一個欄位

mysql> alter table testtable2 drop year;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select* from testtable2;
+----+-------+------+-------+-----+-------+
| id | name  | job  | price | car | title |
+----+-------+------+-------+-----+-------+
|  1 | lewis | sw   | 2     |   2 | NULL  |
|  2 | lewis | sw   | 3     | 111 | NULL  |
|  3 | leo   | sw   | 4     | 123 | NULL  |
|  4 | jim   | sw   | 7     |   6 | NULL  |
+----+-------+------+-------+-----+-------+
4 rows in set (0.01 sec)