使用alter修改表字段内容
1、修改数据表的名字
语法：
rename table 旧的表名字 to 新的表名字
mysql> use kings;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| Tanks |
+-----------------+
1 row in set (0.00 sec)
mysql> rename table Tanks to tanks;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| tanks |
+-----------------+
1 row in set (0.00 sec)
mysql>
2、alter命令修改表名
语法：alter table 当前表名 rename to 新表名
mysql> alter table tanks rename to TANKS;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| TANKS |
+-----------------+
1 row in set (0.00 sec)
mysql>
3、修改数据表的字段
（1）查看数据表的具体信息
mysql> desc tanks;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(100) | NO | | NULL | |
| skill | varchar(255) | NO | | NULL | |
| price | int(11) | NO | | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
4、添加字段 Introduction 具体的介绍信息
语法：alter table 表名 add 字段名 字段的数据类型（长度）额外的字段属性
mysql> alter table tanks add introduction varchar(255) not null;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tanks;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(100) | NO | | NULL | |
| skill | varchar(255) | NO | | NULL | |
| price | int(11) | NO | | NULL | |
| introduction | varchar(255) | NO | | NULL | |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
5、添加指定位置的字段
枚举类型，二选一
mysql> alter table tanks add summoner_skill ENUM('flush','fire') not null default 'flush' after skill;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tanks;
+----------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(100) | NO | | NULL | |
| skill | varchar(255) | NO | | NULL | |
| summoner_skill | enum('flush','fire') | NO | | flush | |
| price | int(11) | NO | | NULL | |
| introduction | varchar(255) | NO | | NULL | |
+----------------+----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql>
6、一次性添加多个字段
指定位置添加多个字段
mysql> alter table tanks add camp varchar(50) after price,add pic varchar(255) after camp;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tanks;
+----------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(100) | NO | | NULL | |
| skill | varchar(255) | NO | | NULL | |
| summoner_skill | enum('flush','fire') | NO | | flush | |
| price | int(11) | NO | | NULL | |
| camp | varchar(50) | YES | | NULL | |
| pic | varchar(255) | YES | | NULL | |
| introduction | varchar(255) | NO | | NULL | |
+----------------+----------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql>
7、修改表字段的类型
语法：alter table 表名 change 旧字段名 新字段名 新数据类型
把pic的varchar改为char
mysql> alter table tanks change pic pic_url char(200);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc tanks;
+----------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(100) | NO | | NULL | |
| skill | varchar(255) | NO | | NULL | |
| summoner_skill | enum('flush','fire') | NO | | flush | |
| price | int(11) | NO | | NULL | |
| camp | varchar(50) | YES | | NULL | |
| pic_url | char(200) | YES | | NULL | |
| introduction | varchar(255) | NO | | NULL | |
+----------------+----------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql>
附：
删除table表的字段
语法：alter table 表名 drop 字段
mysql> alter table tanks drop summoner_skill;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tanks;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(100) | NO | | NULL | |
| skill | varchar(255) | NO | | NULL | |
| price | int(11) | NO | | NULL | |
| introduction | varchar(255) | NO | | NULL | |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
