数据库的基本操作之单列添加

MySQL数据库单列添加详解

最新推荐文章于 2024-11-05 11:29:23 发布

有一种宿命叫无能为力

最新推荐文章于 2024-11-05 11:29:23 发布

阅读量546

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库的逐步入门文章标签： mysql

本文链接：https://blog.youkuaiyun.com/You_are_my_dream/article/details/52894846

数据库的逐步入门专栏收录该内容

16 篇文章

订阅专栏

本文介绍了在MySQL中如何使用ALTER TABLE语句向表格中添加单列，包括默认添加、在指定列后添加以及在所有列之前添加的方法，并通过实例展示了操作后的表结构变化。

添加单列

ALTER TABLE tbl_name ADD [COLUMNS] col_name column_definition [FIRST | AFTER col_name]

开始时的表结构

mysql> show columns from users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(30) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

默认添加在结尾

mysql> alter table users1 add age smallint unsigned default 10;
Query OK, 0 rows affected (1.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show columns from users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(30) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | smallint(5) unsigned | YES | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

在username列之后添加
mysql> alter table users1 add password varchar(30) not null after username;
Query OK, 0 rows affected (1.42 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show columns from users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(30) | NO | | NULL | |
| password | varchar(30) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | smallint(5) unsigned | YES | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

在所有列之前添加

mysql> alter table users1 add sex enum('1', '2', '3') default '3' first;
Query OK, 0 rows affected (1.20 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show columns from users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| sex | enum('1','2','3') | YES | | 3 | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(30) | NO | | NULL | |
| password | varchar(30) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | smallint(5) unsigned | YES | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
6 rows in set (0.13 sec)