第三章第二节：修改数据表——ALTER (一)ADD、DROP

最新推荐文章于 2025-05-16 16:20:02 发布

转载最新推荐文章于 2025-05-16 16:20:02 发布 · 409 阅读

MySQL 专栏收录该内容

28 篇文章

订阅专栏

表级约束很少用

添加单列

ALTER TABLE tb_name ADD col_name columns_definition [FIRST|AFTER clo_name]

向表中添加一列，选择放在最前面还是放在某列之后，不选默认最后

举例：

在表users1中添加一列，先显示表中内容

mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+

只有三个属性，id，username，和pid

向表中添加年龄列,默认放置在最后

ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;

SHOW COLUMNS FROM users1;

+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+

在列username后面加上password列

ALTER TABLE usres1 ADD password VARCHAR(20) NOT NULL AFTER username;

SHOW COLUMNS FROM users1;

+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+

在最前列加上turename列

ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;

SHOW COLUMNS FROM users1;

+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| turename | varchar(20) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+

增加多列

ALTER TABLE tb_name ADD (col_name column_definition,.....)

相比增加单列，多列要加括号和逗号，其次不能指定位置了。这里不演示了。

删除列

ALTER TABLE tb_name DROP col_name;

举例：

删除truename列。

ALTER TABLE users1 DROP truename;

SHOW COLUMNS FROM users1;

+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+

删除age和password两列

ALTER TABLE users1 DROP age,DROP password;

SHOW COLUMNS FROM users1;

+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+

删除username，增加turename

ALTER TABLE users1 DROP username , ADD truename VARCHAR(20);

SHOW COLUMNS FROM users1;

+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| truename | varchar(29) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+