mysql数据库修改结构_在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>