mysql-添加删除字段(5)

最新推荐文章于 2023-05-11 08:56:39 发布

cppNoException

最新推荐文章于 2023-05-11 08:56:39 发布

阅读量331

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_36744627/article/details/81211474

版权

mysql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

上一篇讲到如何创建数据表，此篇讲解如何添加以及删除字段

添加和删除字段使用alter关键字，
添加字段命令格式如下：alter table 表格名称 add 字段名 [约束性条件];
删除字段命令格式如下：alter table 表格名称 drop 字段名;
下面测试一下这两个命令


mysql> create table test_alter(
    -> id int unsigned auto_increment key,
    -> name varchar(20) unique not null)
    -> engine=innodb charset=utf8;
Query OK, 0 rows affected (0.28 sec)

mysql> desc test_alter;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)      | NO   | UNI | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> alter table test_alter add
    -> email varchar(50) unique not null default '1122@126.com';
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_alter;
+-------+------------------+------+-----+--------------+----------------+
| Field | Type             | Null | Key | Default      | Extra          |
+-------+------------------+------+-----+--------------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL         | auto_increment |
| name  | varchar(20)      | NO   | UNI | NULL         |                |
| email | varchar(50)      | NO   | UNI | 1122@126.com |                |
+-------+------------------+------+-----+--------------+----------------+
3 rows in set (0.00 sec)
mysql> alter table test_alter drop email;
dQuery OK, 0 rows affected (0.63 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_alter;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)      | NO   | UNI | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

读者在添加字段的时候，还可以使用first或者after指定列的位置，
命令格式如下：

alter table 表格名称  add 字段名 [约束性条件] [ first / after 字段名];
--下面测试first 和after指定增加字段的位置
mysql> desc test_alter;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| usrname | varchar(30)      | NO   | UNI | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> alter table test_alter add email varchar(50) not null unique first, add address varchar(30) not null default '北京' after id;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_alter;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| email   | varchar(50)      | NO   | UNI | NULL    |                |
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| address | varchar(30)      | NO   |     | 北京    |                |
| usrname | varchar(30)      | NO   | UNI | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)