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

阳光穿透心脏的1/2处 2023-10-09 09:43 41阅读 0赞

## 1、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 | |
    +-------+-------------+------+-----+---------+-------+

## 2、末尾插入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 | |
    +---------+-------------+------+-----+---------+-------+

## 3、头部插入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 | |
    +---------+-------------+------+-----+---------+-------+

## 4、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 | |
    +---------+-------------+------+-----+---------+-------+

## 5、添加多列: ##

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 | |
    +---------+-------------+------+-----+---------+-------+

## 6、删除数据表中的列： ##

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 | |
    +---------+-------------+------+-----+---------+-------+

## 7、修改列名和数据类型: ##

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 | |
    +---------+-------------+------+-----+---------+-------+

## 8、修改数据类型不改列名： ##

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 | |
    +---------+-------------+------+-----+---------+-------+

## 9、修改表名： ##

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

ALTER TABLE book RENAME TO music;