MySQL数据库操作（3）表结构操作

ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

添加单列:
ALTER TABLE 表名 ADD [COLUNM] 列名 数据类型 [FIRST|AFTER 指定列];
例子：

+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| s_id | int(11) | NO | PRI | NULL | |
| sex | varchar(10) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+

末尾插入address

ALTER TABLE student_details ADD address varchar(50);

+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| s_id | int(11) | NO | PRI | NULL | |
| sex | varchar(10) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+

头部插入name

ALTER TABLE student_details ADD name varchar(10) FIRST;

+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| s_id | int(11) | NO | PRI | NULL | |
| sex | varchar(10) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+

sex后插入marry

ALTER TABLE student_details ADD marry varchar(10) AFTER sex;

+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| s_id | int(11) | NO | PRI | NULL | |
| sex | varchar(10) | NO | | NULL | |
| marry | varchar(10) | YES | | NULL | |
| age | int(11) | NO | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+

　　

添加多列:
ALTER TABLE 表名 ADD [COLUMN] 列名1 数据类型 [FIRST|AFTER 指定列], ADD [COLUMN] 列名2 数据类型 [FIRST|AFTER 指定列];
例子：
age后插入father, mother

ALTER TABLE student_details ADD father varchar(10) AFTER age,ADD mother varchar(10) AFTER age;

+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| s_id | int(11) | NO | PRI | NULL | |
| sex | varchar(10) | NO | | NULL | |
| marry | varchar(10) | YES | | NULL | |
| age | int(11) | NO | | NULL | |
| mother | varchar(10) | YES | | NULL | |
| father | varchar(10) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+

 

删除数据表中的列：
ALTER TABLE 表名 DROP [COLUMN] 列名 ;
例子：
删除father,mother

ALTER TABLE student_details DROP father, DROP mother;

+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| s_id | int(11) | NO | PRI | NULL | |
| sex | varchar(10) | NO | | NULL | |
| marry | varchar(10) | YES | | NULL | |
| age | int(11) | NO | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+

　

修改列名和数据类型:
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
例子：
marry 改名 married

ALTER TABLE student_details CHANGE marry married varchar(12);

+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| s_id | int(11) | NO | PRI | NULL | |
| sex | varchar(10) | NO | | NULL | |
| married | varchar(12) | YES | | NULL | |
| age | int(11) | NO | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+

　　

修改数据类型不改列名：
ALTER TABLE 表名 MODIFY 列名 新数据类型;
例子：
sex 修改数据类型 INT

ALTER TABLE student_details MODIFY sex INT;

+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| s_id | int(11) | NO | PRI | NULL | |
| sex | int(11) | YES | | NULL | |
| married | varchar(12) | YES | | NULL | |
| age | int(11) | NO | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+

　

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

+-----------------+
| Tables_in_mydb |
+-----------------+
| book |
| course |
| course_select |
| department |
| student |
| student_details |
| tb1 |
+-----------------+

ALTER TABLE book RENAME TO music;

+-----------------+
| Tables_in_mydb |
+-----------------+
| course |
| course_select |
| department |
| music |
| student |
| student_details |
| tb1 |
+-----------------+