??? 在数据库操作中,个人觉得使用得最多的就是查询,然后就是对表的修改操作了,尤其是当数据库的设计工作没有做好时.下面就MySQL中一些常用的修改表的操作进行总结:

??? 为了方便后面的说明,先创建一个表,创建语句如下:

Create Table member(

??? id Int(3),

??? name Varchar(8),

??? pass Varchar(25)

);

1.向表member中增加一个新列email,MySQL语句如下:

? Alter Table member Add email Varchar(50) Not Null;

? Describe member;

+-------+-------------+------+-----+---------+-------+
| Field | Type??????? | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id??? | int(3)????? | YES? |???? | NULL??? |?????? |
| name? | varchar(8)? | YES? |???? | NULL??? |?????? |
| pass? | varchar(8)? | YES? |???? | NULL??? |?????? |
| email | varchar(50) | NO?? |???? | NULL??? |?????? |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

2.向表member中添加一个新的主键id,MySQL语句如下:

? Alter Table member Add Primary Key(id);

? Describe member;

+-------+-------------+------+-----+---------+-------+
| Field | Type??????? | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id??? | int(3)????? | NO?? | PRI | 0?????? |?????? |
| name? | varchar(8)? | YES? |???? | NULL??? |?????? |
| pass? | varchar(8)? | YES? |???? | NULL??? |?????? |
| email | varchar(50) | NO?? |???? | NULL??? |?????? |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3.修改已存在字段id的名字和类型,MySQL语句如下:

? Alter Table member Change id mid Int(8) Auto_Increment Unique;

? Describe member;

+-------+-------------+------+-----+---------+----------------+
| Field | Type??????? | Null | Key | Default | Extra????????? |
+-------+-------------+------+-----+---------+----------------+
| mid?? | int(8)????? | NO?? | PRI | NULL??? | auto_increment |
| name? | varchar(8)? | YES? |???? | NULL??? |??????????????? |
| pass? | varchar(8)? | YES? |???? | NULL??? |??????????????? |
| email | varchar(50) | NO?? |???? | NULL??? |??????????????? |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

说明:修改字段类型时,MySQL中也可以将Change更改为Modify,两者的区别在于:Change要求在修改表时指定旧的的新的字段名,而Modify则只是修改相应字段的类型,但不更改该字段的名字.如下所示:

? Alter Table member Change id id Int(8) Auto_Increment Unique;

? Alter Table member Modify id Int(8) Auto_Increment Unique;

4.删除字段键pass,MySQL语句如下:

? Alter Table member Drop pass;

? Describe member;

+-------+-------------+------+-----+---------+----------------+
| Field | Type??????? | Null | Key | Default