mysql alter语句

mysql alter语句

rename重命名数据表

mysql> show tables;
+------------------+
| Tables_in_course |
+------------------+
| choice           |
| course           |
| students         |
+------------------+
mysql> alter table `choice` rename `score`;
mysql> show tables;
+------------------+
| Tables_in_course |
+------------------+
| course           |
| score            |
| students         |
+------------------+

modify修改字段的数据类型

mysql> desc `courses`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| 课程编号 | int         | NO   | PRI | NULL    |       |
| 课程名   | varchar(20) | NO   |     | NULL    |       |
| 学分     | int         | NO   |     | NULL    |       |
| 教师编号 | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
mysql> alter table `courses` modify `教师编号` int not null;
mysql> desc `courses`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| 课程编号 | int         | NO   | PRI | NULL    |       |
| 课程名   | varchar(20) | NO   |     | NULL    |       |
| 学分     | int         | NO   |     | NULL    |       |
| 教师编号 | int         | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

change修改字段名

mysql> desc `courses`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| 课程编号 | int         | NO   | PRI | NULL    |       |
| 课程名   | varchar(20) | NO   |     | NULL    |       |
| 学分     | int         | NO   |     | NULL    |       |
| 教师编号 | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
mysql> alter table `courses` modify `教师编号` int not null;
mysql> desc `courses`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| 课程编号 | int         | NO   | PRI | NULL    |       |
| 课程名   | varchar(20) | NO   |     | NULL    |       |
| 学分     | int         | NO   |     | NULL    |       |
| 教师编号 | int         | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

add添加字段

mysql> alter table `courses` add `教室` varchar(20) ;

mysql> desc `courses`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| 课程编号 | int         | NO   | PRI | NULL    |       |
| 课程名   | varchar(20) | NO   |     | NULL    |       |
| 学分     | int         | NO   |     | NULL    |       |
| 教师编号 | int         | NO   |     | NULL    |       |
| 教室     | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

drop删除字段

mysql> alter table `courses` drop `教室`;

字段重新排序 modify first|after

mysql> desc `courses`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| 课程编号 | int         | NO   | PRI | NULL    |       |
| 课程名   | varchar(20) | NO   |     | NULL    |       |
| 学分     | int         | NO   |     | NULL    |       |
| 教师编号 | int         | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
mysql> alter table `courses` modify `教师编号` int not null first;
mysql> desc `courses`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| 教师编号 | int         | NO   |     | NULL    |       |
| 课程编号 | int         | NO   | PRI | NULL    |       |
| 课程名   | varchar(20) | NO   |     | NULL    |       |
| 学分     | int         | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
mysql> alter table `courses` modify `教师编号` int after `学分`;
mysql> desc `courses`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| 课程编号 | int         | NO   | PRI | NULL    |       |
| 课程名   | varchar(20) | NO   |     | NULL    |       |
| 学分     | int         | NO   |     | NULL    |       |
| 教师编号 | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+