上一篇讲到如何创建数据表,此篇讲解如何添加以及删除字段
添加和删除字段使用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)