目录
一、修改列定义（modify col_name）
二、修改列类型（modify col_name）
三、修改列名称（change col_name）
三、修改表名称(rename table a to b）
用到的语句
a、修改列定义和名称：
语法一：Alter table tbl_name modify[column] col_name [first|after col_name]
讲解：更改 数据表 某某表 修改 【列】 列名称 列定义 【位置|位置】
语法二：alter table tab_name change[column] old_name new_name [first|after col_name]
讲解：更改 数据表 某某表 修改 【列】 列名称 列定义 【位置|位置】
注意：语法二的功能要比语法一大很多，因为语法二既可以更改列定义，由可以更改列名称
b、修改表名称：
语法一： alter table tbl_name rename[to|as] new_tbl_name
讲解：可以更改一张数据表名称
语法二：rename table tbl_name to new_tbl_name [,tbl_name2 TO new_tbl_namew]……
讲解：可以多表更改名称
一、修改列定义(改变age列的位置，使用modify)
- 先确定我们数据库存在我们操作的数据表city2
mysql> show columns from city2;//查询数据表
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | | 0 | |
| username | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+- 案例
//具体操作语句：更改数据表city2,修改age列 位于username之后
mysql> alter table city2 modify age tinyint unsigned NOT NULL after username;
Query OK, 0 rows affected (0.21 sec)//修改成功
//修改前age列位于pid之下
mysql> show columns from city2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | | 0 | |
| username | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
//修改后，age位于username之下，pid之上
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | | 0 | |
| username | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)二、修改列类型（修改age列的储存类型，使用modify）
注意：由大类型改变成小类型的时候有可能造成数据的丢失
语句： alter table city2 modify age smallint unsigned NOT NULL;
解析：更改数据表city2 修改age列，类型：smallint,无符号，非空
//具体操作
mysql> alter table city2 modify age smallint unsigned NOT NULL;
Query OK, 0 rows affected (0.18 sec)//修改成功
//查询数据表
mysql> show columns from city2;
//修改前，age的类型为tinyint
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | | 0 | |
| username | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
+----------+----------------------+------+-----+---------+-------+
//修改后，age的类型改变为smallint
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | | 0 | |
| username | varchar(20) | NO | | NULL | |
| age | smallint(5) unsigned | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
+----------+----------------------+------+-----+---------+-------+
三、修改列名称（使用change改变列的名称和类型，使用change）
注意：change col_name 不仅仅可以修改列定义，还可以修改列的名称。
语法：alter table tab_name change[column] old_name new_name [first|after col_name]
解析：更改数据表某某，改变列 老列名 新列明 列定于【位置first|某列之后】
语法：alter table city2 change pid p_id tinyint unsigned not null
解析：更改数据表city2 改变pid列 老名称：pid,新名称：p_id,类型：tinyint ，无符号，非空。
mysql> alter table city2 change pid p_id tinyint unsigned not null;
Query OK, 0 rows affected (0.20 sec)//修改成功过
mysql> show columns from city2;
//修改前，数据表的名称pid 类型为smallint
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | | 0 | |
| username | varchar(20) | NO | | NULL | |
| age | smallint(5) unsigned | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
+----------+----------------------+------+-----+---------+-------+
//修改后，pid列名称改变为p_id，类型由smallint改变为tinyint
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | | 0 | |
| username | varchar(20) | NO | | NULL | |
| age | smallint(5) unsigned | NO | | NULL | |
| p_id | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
三、修改表名称
- 注意：修改数据表的名称有两种方式。
//可以更改一张数据表
1、alter table tbl_name rename[to|as] new_tbl_name//可以为多张数据表修改名称
2、rename table tbl_name to new_tbl_name [,tbl_name2 TO new_tbl_namew]……
- 一、方法一 单列改名
语句：show tables;
解析：查看数据库里有那些数据表
语句：alter table city2 rename city222;
解析：更改数据表名称tity2 更改为名称city222
mysql> show tables;
+--------------+
| Tables_in_t1 |
+--------------+
| city |
| city1 |
| city2 |
| mm1 |
| mm2 |
| mm3 |
| sheng |
+--------------+
//我们修改city2的表名称为city222
mysql> alter table city2 rename city222;
Query OK, 0 rows affected (0.07 sec)//修改成功
mysql> show tables;
//修改前,数据表city2存在
+--------------+
| Tables_in_t1 |
+--------------+
| city |
| city1 |
| city2 |
| mm1 |
| mm2 |
| mm3 |
| sheng |
+--------------+
//修改后，city2数据表变成了city222
+--------------+
| Tables_in_t1 |
+--------------+
| city |
| city1 |
| city222 |
| mm1 |
| mm2 |
| mm3 |
| sheng |
+--------------+方法二，多列改名
mysql> rename table city to city_1,city222 to city2;
Query OK, 0 rows affected (0.17 sec)//修改成功
mysql> show tables;
//修改前，city和city111数据表存在
+--------------+
| Tables_in_t1 |
+--------------+
| city |
| city1 |
| city222 |
| mm1 |
| mm2 |
| mm3 |
| sheng |
+--------------+
//city和city111数据表的名称 改变为了 city_1 和city2。
+--------------+
| Tables_in_t1 |
+--------------+
| city1 |
| city2 |
| city_1 |
| mm1 |
| mm2 |
| mm3 |
| sheng |
+--------------+