6.3 数据字段操作 · p1p

假设我们存在user表，user结构如下： > mysql> desc user; > +----------+---------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +----------+---------------+------+-----+---------+-------+ > | username | varchar(10) | YES | | NULL | | > | password | varchar(32) | YES | | NULL | | > | createtime | int(10) | YES | | NULL | | > | createip | int(10) | YES | | NULL | | > +----------+---------------+------+-----+---------+-------+ > 4 rows in set (0.01 sec) ## 修改表字段类型 modify | 类别 | 详细解示 | | --- | --- | | 基本语法 | alter table 表名 modify 字段名 varchar(20); | | 示例 | alter table user modify username varchar(20); | | 示例说明 | 将user表的username的类型改为varchar(20) | 我们执行一下，看看结果： > mysql> alter table user modify username varchar(20); > Query OK, 0 rows affected (0.48 sec) > Records: 0 Duplicates: 0 Warnings: 0 > mysql> desc user; > +----------+---------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +----------+---------------+------+-----+---------+-------+ > | username | varchar(20) | YES | | NULL | | > | password | varchar(32) | YES | | NULL | | > | createtime | int(10) | YES | | NULL | | > | createip | int(10) | YES | | NULL | | > +----------+---------------+------+-----+---------+-------+ > 4 rows in set (0.01 sec) ##增加表字段 | 类别 | 详细解示 | | --- | --- | | 基本语法 | alter table 表名 add column 字段名类型; | | 示例 | alter table user add column age int(3); | | 示例说明 | 添加一个字段为age，类型为整型长度为3 | > mysql> alter table emp add column age int(3); > Query OK, 0 rows affected (0.40 sec) > Records: 0 Duplicates: 0 Warnings: 0 > mysql> desc user; > +----------+---------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +----------+---------------+------+-----+---------+-------+ > | username | varchar(20) | YES | | NULL | | > | password | varchar(32) | YES | | NULL | | > | createtime | int(10) | YES | | NULL | | > | createip | int(10) | YES | | NULL | | > | age | int(3) | YES | | NULL | | > +----------+---------------+------+-----+---------+-------+ > 5 rows in set (0.00 sec) ##增加字段时控制字段顺序我们刚刚学了增加字段。如果你仔细实验发现每次都是增加在最后面，如何在第一个增加或者在指字字段之后增加呢？ | 类别 | 详细解释 | | --- | --- | | 基本语法 | ALTER TABLE 表名 ADD 字段名字段类型 AFTER 字段名; | | 示例 | ALTER TABLE user ADD email VARCHAR(60) AFTER createip; | | 示例说明 | user表中，在createip后增加一个字段为email，类型为varchar，长度为60 | | 类别 | 详细解示 | | --- | --- | | 基本语法 | ALTER TABLE 表名 ADD 字段名字段类型; | | 示例 | ALTER TABLE user ADD id INT(10) FIRST; | | 示例说明 | user表中在最开始的位置增加一个字段为id,类型为int，长度为10 | > ALTER TABLE user ADD email VARCHAR(60) AFTER createip; > Query OK, 0 rows affected (0.40 sec) > Records: 0 Duplicates: 0 Warnings: 0 > mysql> desc user; > +----------+---------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +----------+---------------+------+-----+---------+-------+ > | username | varchar(20) | YES | | NULL | | > | password | varchar(32) | YES | | NULL | | > | createtime | int(10) | YES | | NULL | | > | createip | int(10) | YES | | NULL | | > | email | varchar(60) | YES | | NULL | | > | age | int(3) | YES | | NULL | | > +----------+---------------+------+-----+---------+-------+ > 6 rows in set (0.00 sec) ##删除表字段 | 类别 | 详细解示 | | --- | --- | | 基本语法 | alter table 表名 drop column 字段名; | | 示例 | alter table user drop column age; | | 示例说明 | 在user表中删除字段age | > mysql> alter table user drop column age; > Query OK, 0 rows affected (0.27 sec) > Records: 0 Duplicates: 0 Warnings: 0 > mysql> desc user; > +----------+---------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +----------+---------------+------+-----+---------+-------+ > | username | varchar(20) | YES | | NULL | | > | password | varchar(32) | YES | | NULL | | > | createtime | int(10) | YES | | NULL | | > | createip | int(10) | YES | | NULL | | > | email | varchar(60) | YES | | NULL | | > +----------+---------------+------+-----+---------+-------+ > 5 rows in set (0.00 sec) ##表字段改名 | 类别 | 详细解示 | | --- | --- | | 基本语法 | alter table 表名 change 字段原名字段新名字段类型; | | 示例 | alter table user change email em varchar(60); | | 示例说明 | 在user表中将字段中的email字段名字为em | 详细示例： mysql> alter table user change email em varchar(60); Query OK, 0 rows affected (0.38 sec) Records: 0 Duplicates: 0 Warnings: 0 > mysql> desc user; > +----------+---------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +----------+---------------+------+-----+---------+-------+ > | username | varchar(20) | YES | | NULL | | > | password | varchar(32) | YES | | NULL | | > | createtime | int(10) | YES | | NULL | | > | createip | int(10) | YES | | NULL | | > | em | varchar(60) | YES | | NULL | | > +----------+---------------+------+-----+---------+-------+ > 5 rows in set (0.00 sec) ##修改表字段排列顺序在前的字段增加和修改语句（add/change/modify）中，最后都可以加一个可选项 first|after。增加表字段时我们已经学过了如何调整顺序。我们现在在来看看另外的change或modify如何来调整顺序。我们用first做个小实验。 ###使用modify调整顺序 > mysql> alter table user modify em varchar(60) first; > Query OK, 0 rows affected (0.41 sec) > Records: 0 Duplicates: 0 Warnings: 0 > mysql> desc user; > +----------+---------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +----------+---------------+------+-----+---------+-------+ > | em | varchar(60) | YES | | NULL | | > | username | varchar(20) | YES | | NULL | | > | password | varchar(32) | YES | | NULL | | > | createtime | int(10) | YES | | NULL | | > | createip | int(10) | YES | | NULL | | > +----------+---------------+------+-----+---------+-------+ > 5 rows in set (0.00 sec) ##修改表名 | 类别 | 详细解示 | | --- | --- | | 基本语法 | alter table 旧表名 rename 新的表名; | | 示例 | alter table user rename new\_user; | | 示例说明 | 将user表名改为new\_user | > mysql> alter table user rename new\_user; > Query OK, 0 rows affected (0.35 sec) > mysql> desc new\_user; > +----------+---------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +----------+---------------+------+-----+---------+-------+ > | em | varchar(60) | YES | | NULL | | > | username | varchar(20) | YES | | NULL | | > | password | varchar(32) | YES | | NULL | | > | createtime | int(10) | YES | | NULL | | > | createip | int(10) | YES | | NULL | | > +----------+---------------+------+-----+---------+-------+ > 5 rows in set (0.00 sec)