Community Learn Tools Library Leisure
search
English

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

CategoryDetailed explanationBasic syntaxalter table table name modify field name varchar(20);##ExampleExample description Let’s execute it and see the result:
alter table user modify username varchar(20);
Change the type of username in the user table to 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)


Add table fields

CategoryDetailed explanation##Basic syntaxalter table table name add column field name type; alter table user add column age int(3);Add a field as age, type is integer, length is 3
Example
Example description

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?

CategoryDetailed explanationBasic syntaxALTER TABLE table name ADD field name field type AFTER field name;ExampleALTER TABLE user ADD email VARCHAR(60) AFTER createip;Example descriptionIn the user table, add a field as email after createip, the type is varchar, the length is 60
CategoryDetailed explanation##Basic syntaxExampleExample description

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 table name ADD Field name Field type;
ALTER TABLE user ADD id INT(10) FIRST;
In the user table, add a field as id at the beginning, type is int, length is 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)

Table field rename

CategoryDetailed explanationBasic syntaxalter table table name change field original name field new name field type;Examplealter table user change email em varchar(60);Example descriptionIn 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 one at the end Optional first|after.

We have already learned how to adjust the order when adding table fields. Let's now take a 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 table name

CategoryDetailed explanation
Basic syntaxalter table old table name rename new table name;
Examplealter table user rename new_user;
Example descriptionChange the user table name to 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)

Continuing Learning
||
<?php echo "Hello Mysql"; ?>
submitReset Code
Previous section Next Section
Tutorial List
get help
  • Course Recommendations
  • Courseware download
The courseware is not available for download at the moment. The staff is currently organizing it. Please pay more attention to this course in the future~

Students who have watched this course are also learning

The latest ThinkPHP 5.1 world premiere video tutorial (60 days to become a PHP expert online training course)  492400 plays

Public welfare online PHP training，Help PHP learners grow quickly！

About us Disclaimer Sitemap

© php.cn All rights reserved