第4节-MySQL表的修改与删除

1、修改表名

alter table student rename student_new;

2、修改字段名字

alter table student change sname new_name char(8);

3、修改字段的数据类型

alter table student modify sname char(10);

4、修改字段前后位置

mysql> desc student;
+-------+-------------------+------+-----+---------+-------+
| Field | Type              | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| sage  | tinyint(4)        | YES  |     | NULL    |       |
| sno   | char(5)           | NO   | PRI | NULL    |       |
| sname | char(10)          | YES  |     | NULL    |       |
| ssex  | enum('女','男')   | YES  |     | 男      |       |
| sdept | varchar(20)       | YES  |     | NULL    |       |
+-------+-------------------+------+-----+---------+-------+

mysql> alter table student modify sage tinyint(4) after sno;
# after 或 first

mysql> desc student;
+-------+-------------------+------+-----+---------+-------+
| Field | Type              | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| sno   | char(5)           | NO   | PRI | NULL    |       |
| sage  | tinyint(4)        | YES  |     | NULL    |       |
| sname | char(10)          | YES  |     | NULL    |       |
| ssex  | enum('女','男')   | YES  |     | 男      |       |
| sdept | varchar(20)       | YES  |     | NULL    |       |
+-------+-------------------+------+-----+---------+-------+

5、添加字段

alter table student add idcard char(18);

6、删除字段

alter table student drop idcard;

7、删除表

drop table student;