MySQL修改表

　　MySQL对表的操作主要包括表的修改以及删除。

1.修改表名

　　修改表名的指令为RENAME TABLE oldname TO newname;，下面为将我本地数据库test中表t1改名为student：

$ mysql -uroot -p
Enter password: ******
mysql> use test;
Database changed
mysql> RENAME TABLE t1 TO student;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| example0       |
| student        |
+----------------+
2 rows in set (0.00 sec)

　　
　　下面指令也可以修改表名，作用跟上述指令一样：

ALTER TABLE t1 RENAME student ;

2.修改表中字段类型名

　　修改表中字段类型名通过指令格式如下：

ALTER TABLE 表名 CHANGE 旧字段名称 新字段名称 字段类型;

　　下面给出将表student中字段tid改为sno：

mysql> ALTER TABLE student CHANGE tid sno INT;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | int(11)     | NO   | PRI | NULL    |       |
| tname | varchar(30) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

3.增加或删除列

　　下面给出在表student增加一列的指令：

mysql> ALTER TABLE student ADD sscore TINYINT;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sno    | int(11)     | NO   | PRI | NULL    |       |
| tname  | varchar(30) | NO   |     | NULL    |       |
| sscore | tinyint(4)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

　　下面将刚才增加的一列删除，指令如下：

mysql> ALTER TABLE student DROP COLUMN sscore;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | int(11)     | NO   | PRI | NULL    |       |
| tname | varchar(30) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

4.删除表

　　下面给出删除表的指令：

-- 删除表t1
DROP TABLE t1;

-- 如果表t1存在删除表t1
DROP TABLE IF EXISTS t1;

-- 批量删除多个表
DROP TABLE t1.t2,t3;