Mysql学习记录【Mysql数据字段操作】

最新推荐文章于 2022-12-22 15:43:28 发布

licheric

最新推荐文章于 2022-12-22 15:43:28 发布

阅读量161

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/licheric/article/details/108835117

版权

一、查看表结构

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 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 user ADD id INT(10) FIRST;
示例说明 user表中在最开始的位置增加一个字段为id,类型为int，长度为10

四、删除表字段

基本语法
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)