Data field operations

Assume we have a user table, and the user structure is as follows:

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 the table field type modify

Let’s execute it and see the result:

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)

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)

Control the order of fields when adding fields

We just learned to add fields. If you carefully experiment and find that every time it is added at the end, how to add it at the first one or after the pointing field?

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)

Delete table fields

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)

Example

alter table user   change email em varchar(60);

Example description

In the user table, name the email field in the field em

Detailed example:

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)

Modify the order of table fields

In the previous field addition and modification statements (add/change/modify), you can add an optional first|after option at the end.

We have already learned how to adjust the order when adding table fields. Let's now look at how another change or modify can adjust the order.

Let’s do a small experiment with first.

Use modify to adjust the order

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)

Modify the table name

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)