在MySQL中修改数据库表结构

Alter Table - MySQL Command by Angela Bradley

Drop Column:

alter table icecream drop column flavor ;

Drop Column is used to remove an entire column and all its data from a table.

Add Column:

alter table icecream add column flavor varchar (20) ;

Add Column is used to add a column to your existing table.

Change:

alter table icecream change taste flavor varchar (10) ;

Change is used to change the column name. In our example it changes "taste" to be "flavor".

Add Unique:

alter table icecream add unique (quantity)

Add unique adds a new column to your table only if it does not already exist.

Modify:

alter table icecream modify flavor VARCHAR(35) ;

Modify is used to change a column's size. In our example we increase the size of the "flavor" column to 35 characters.

试验结果如下：

Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 1
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Type 'help;' or '/h' for help. Type '/c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hibernate01        |
| mysql              |
| struts1            |
| test               |
+--------------------+
5 rows in set (0.08 sec)

mysql> use struts1;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_struts1 |
+-------------------+
| person            |
| userdb            |
+-------------------+
2 rows in set (0.00 sec)

mysql> desc person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     |         |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.17 sec)

mysql> alter table person add column age int;
Query OK, 10 rows affected (0.75 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> desc person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     |         |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table person drop column age;
Query OK, 10 rows affected (0.63 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> desc person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     |         |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> alter table person add column email varchar(10);
Query OK, 10 rows affected (0.23 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> desc person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     |         |                |
| email | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> alter table person change email e-mail varchar(10);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '-mail
 varchar(10)' at line 1
mysql> alter table person change email varchar(20);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'varch
ar(20)' at line 1
mysql> alter table person change email address varchar(20);
Query OK, 10 rows affected (0.53 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> desc person;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | NO   |     |         |                |
| address | varchar(20) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table person modify address varchar(40);
Query OK, 10 rows affected (0.31 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> desc person;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | NO   |     |         |                |
| address | varchar(40) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table person add unique(name);
Query OK, 10 rows affected (0.52 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> desc person;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | NO   | UNI |         |                |
| address | varchar(40) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>