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)
