修改列的数据类型

1. alter table 表名 modify 列名 数据类型;
   
   假设表 singer 的列布局如下

   mysql> desc singer;
   +----------+-------------+------+-----+---------+-------+
   | Field    | Type        | Null | Key | Default | Extra |
   +----------+-------------+------+-----+---------+-------+
   | name     | varchar(5)  | YES  |     | NULL    |       |
   | birthday | date        | YES  |     | NULL    |       |
   | song     | varchar(10) | YES  |     | NULL    |       |
   +----------+-------------+------+-----+---------+-------+
   3 rows in set (2.982 sec)
   
   mysql>
                                   

   把 name 的数据类型由 varchar(5) 修改为 varchar(8)，执行下面的命令
   
   alter table singer modify name varchar(10);
   

mysql> alter table singer modify name  varchar(10);
Query OK, 0 rows affected (2.967 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
                                               

修改列的顺序

1. alter table 表名 modify 列名 数据类型 after 某列名;
   
   假设表 singer 的列 song 的位置在最后，把它的位置改变到 name 的后面，使用下面的命令
   
   alter table singer modify song varchar(10) afer name;
   

   mysql> alter table singer modify song varchar(10) after name;
   Query OK, 0 rows affected (6.652 sec)
   Records: 0  Duplicates: 0  Warnings: 0
   
   mysql>
                                   

   把列 song 的位置改变到最前面，使用下面的命令
   
   alter table singer modify song varchar(10) first;
   

改变列名

1. alter table 表名 change 列名 新列名 数据类型;
   
   把 singer 的列 name 的名改变为 nickname，使用下面的命令
   
   alter table singer change name nickname varchar(10);
   

   mysql> alter table singer change name nickname varchar(10);
   Query OK, 0 rows affected (1.505 sec)
   Records: 0  Duplicates: 0  Warnings: 0
   
   mysql>
                                   

   name 被改为 nickname 后的列的布局

   mysql> desc singer;
   +----------+-------------+------+-----+---------+-------+
   | Field    | Type        | Null | Key | Default | Extra |
   +----------+-------------+------+-----+---------+-------+
   | song     | varchar(10) | YES  |     | NULL    |       |
   | nickname | varchar(10) | YES  |     | NULL    |       |
   | birthday | date        | YES  |     | NULL    |       |
   +----------+-------------+------+-----+---------+-------+
   3 rows in set (0.050 sec)
   
   mysql>
                                   

添加列

1. alter table 表名 add 列名 数据类型;
   
   为 singer 增加新列 id，使用下面的命令
   
   alter table singer add id int;
   

   mysql> alter table singer add id int;
   Query OK, 0 rows affected (1.407 sec)
   Records: 0  Duplicates: 0  Warnings: 0
   
   mysql>
                                   

   增加新列 id 后的列的布局

   mysql> desc singer;
   +----------+-------------+------+-----+---------+-------+
   | Field    | Type        | Null | Key | Default | Extra |
   +----------+-------------+------+-----+---------+-------+
   | song     | varchar(10) | YES  |     | NULL    |       |
   | nickname | varchar(10) | YES  |     | NULL    |       |
   | birthday | date        | YES  |     | NULL    |       |
   | id       | int         | YES  |     | NULL    |       |
   +----------+-------------+------+-----+---------+-------+
   4 rows in set (0.044 sec)
   
   mysql>
                                   

删除列

1. alter table 表名 drop 列名;
   
   把 singer 的列 birthday 删除了，使用下面的命令
   
   alter table singer drop birthday;
   

   mysql> alter table singer drop birthday;
   Query OK, 0 rows affected (1.333 sec)
   Records: 0  Duplicates: 0  Warnings: 0
   
   mysql>