MySQL之修改与删除数据表（ALTER TABLE语句）(DROP TABLE)

这里建议大家数据库语句大写，这样辨识度高，读取速度也可以增加；
修改表指的是修改数据库中已经存在的数据表的结构。MySQL 使用 ALTER TABLE 语句修改表。常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。

• 修改表的字符集ALTER ABLE 表名 CHARACTER SET 字符集;

  例子

mysql> alter table tb3 character set gbk;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb3;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb3   | CREATE TABLE `tb3` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

• 修改表名ALTER TABLE 旧表名 RENAME TO 新表名;
  例子

mysql> alter table tb3 rename to tb1;
Query OK, 0 rows affected (0.22 sec)
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| tb1            |
+----------------+
1 row in set (0.06 sec)

• 修改字段名ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新字段的数据类型;
  例子

mysql> ALTER TABLE tb1 CHANGE id password double;
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| password | double      | NO   | PRI | NULL    |       |
| username | varchar(20) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.07 sec)

• 修改字段数据类型ALTER TABLE 表名 MODIFY 字段名 新的数据类型;
  例子

mysql> ALTER TABLE tb1 modify password int(20);
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| password | int(20)     | NO   | PRI | NULL    |       |
| username | varchar(20) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.07 sec)

• 增加字段ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名]；
  例子

mysql> ALTER TABLE tb1 ADD id double;
Query OK, 0 rows affected (0.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| password | int(20)     | NO   | PRI | NULL    |       |
| username | varchar(20) | NO   |     | NULL    |       |
| id       | double      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.05 sec)

提示：“FIRST 或 AFTER 已存在的字段名”用于指定新增字段在表中的位置，如果 SQL 语句中没有这两个参数，则默认将新添加的字段设置为数据表的最后列。

• 删除字段ALTER ABLE 表名 DROP 字段名;

例子

mysql> ALTER TABLE tb1 DROP password;
Query OK, 0 rows affected (0.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | NO   |     | NULL    |       |
| id       | double      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.07 sec)

• 删除数据表DROP TABLE 表名;
  例子

mysql> DESC test_tb_del;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | double | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.06 sec)

mysql> DROP TABLE test_tb_del;
Query OK, 0 rows affected (0.21 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| tb1            |
+----------------+
1 row in set (0.06 sec)