修改数据表--添加/删除列

1、添加单列

ALTER TABLE tbl_name ADD [COLUMN] col_name  column_definition [first | after col_name]

(1) 显示数据表users2

mysql> SHOW COLUMNS FROM users2;  
+----------+----------------------  +------+-----+---------+----------------  +
| Field      | Type                      | Null | Key | Default | Extra              |
+----------+---------------------  -+------+-----+---------+----------------  +
| id          | smallint(6)             | NO   | PRI | NULL    |auto_increment|
|username| varchar(10)            | NO   |       | NULL    |                      |
| pid        |smallint(5) unsigned | YES | MUL| NULL    |                      |
+----------+----------------------+------+-----+---------+----------------+

(2) 添加age列，默认添加到最后一列

mysql> ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users2;
+----------   +---------------------    -+------+-----+---------+----------------+
| Field         | Type                       | Null  | Key | Default | Extra            |
+----------   +---------------------   -+----- -+-----+---------+----------------+
| id             | smallint(6)              | NO    | PRI | NULL    | auto_increment |
| username  | varchar(10)             | NO    |       | NULL    |                  |
| pid           | smallint(5) unsigned | YES  | MUL| NULL    |                  |
| age          | tinyint(3) unsigned   | NO   |        | 10        |                 |
+----------+----------------------+------+-----+--------- +----------------+

（3）在username后添加列password

mysql> ALTER TABLE users2 ADD password VARCHAR(32) NOT NULL AFTER username;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+----------------+
| Field      | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id          | smallint(6) | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| pid         | smallint(5) unsigned | YES | MUL | NULL | |
| age        | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
(4) 在最前面添加列truename

mysql> ALTER TABLE users2 ADD truename VARCHAR(20) NOT NULL FIRST;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users2;
+----------  +----------------------+------+-----+---------+----------------+
| Field        | Type | Null | Key | Default | Extra |
+----------  +----------------------+------+-----+---------+----------------+
| truename | varchar(20) | NO | | NULL | |
| id            | smallint(6) | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| password  | varchar(32) | NO | | NULL | |
| pid           | smallint(5) unsigned | YES | MUL | NULL | |
| age          | tinyint(3) unsigned | NO | | 10 | |
+----------  +----------------------+------+-----+---------+----------------+

2、添加多列

ALTER TABLE tbl_name ADD [COLUMN] (col_name  column_definition,...)

3、删除列

ALTER TABLE tbl_name  DROP [COLUMN]  col_name

mysql> ALTER TABLE users2 DROP truename;
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users2;
+---------    -+----------------------+------+-----+---------+----------------+
| Field          | Type | Null | Key | Default | Extra |
+--------    --+----------------------+------+-----+---------+----------------+
| id             | smallint(6) | NO | PRI | NULL | auto_increment |
| username  | varchar(10) | NO | | NULL | |
| password   | varchar(32) | NO | | NULL | |
| pid           | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
同时删除两个（删除和添加也可以同时进行）

mysql> ALTER TABLE users2 DROP password, DROP age;
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users2;
+----------  +----------------------+------+-----+---------+----------------+
| Field        | Type | Null | Key | Default | Extra |
+---------  -+----------------------+------+-----+---------+----------------+
| id            | smallint(6) | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| pid          | smallint(5) unsigned | YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+