Learn how to modify mysql table fields in one minute

We know that the ddl statement not only has the function of creating and deleting databases and creating and deleting tables, but also the function of modifying created tables. The example code in the article is very helpful for learning or review by workers. Let’s follow the editor to read it. Look at the ddl statement.

Before studying this article, you must first create a database and several tables (one table is also acceptable). Before opening the article, I created a database called student and the data table of stu. All the following operations are based on this table. You can look at the data in the stu table for comparison in subsequent operations.

Modify table field type

Syntax:

alter table 表名 modify [colimn] 字段定义 [first|after字段名];

• Table name: refers to the name of the data table we need to modify, such as the stu in this article

• Field definition: refers to the field name and field type we need to modify

Example:

mysql> alter table stu modify column name char;

In this way, the name field in the stu table can be modified to char type.

Add table fields

Syntax:

alter table 表名 add [colimn] 字段定义 [first|after字段名];

Example:

mysql> alter table stu add column sex int;

After executing the above statement, when we use desc to view the form, we find that there is an additional sex field, indicating that we have successfully added the field.

Delete table fields

Syntax:

alter table 表名 drop [colimn] 字段名;

Example:

mysql> alter table stu drop sex;

We can see that the field we just added has now been deleted, and there is no sex field.

Field rename

Syntax:

alter table 表名 change [colimn] 旧的字段名 字段定义 [first|after字段名];

Example:

mysql> alter table stu change name mingzi char;

This way you can The original name field is renamed mingzi. If you don’t want to modify the field name but want to modify the field type, you can also use this method

mysql> alter table stu change mingzi mingzi int;

This allows you to modify the field type without modifying the field name.

Note: Both change and modify can modify the definition of the table. The difference is that change needs to be followed by two column names. , not particularly convenient, the advantage is that change can modify the field name.

Modify the field arrangement and sorting

[first|after field name]This selection can be used to modify the position of the field in the table. Add a new The field is loaded at the last position in the table by default, and change/modify does not change the field position by default.

Note: change/first|after field name These keywords belong to the extension of mysql on standard sql, and on other databases Not necessarily applicable.

Change table name

Syntax:

alter table 表名 rename [to] 新的表名;

Example:

mysql> alter table stu rename students;

In this way we can modify the name of the data table.

Recommended tutorial: mysql video tutorial

The above is the detailed content of Learn how to modify mysql table fields in one minute. For more information, please follow other related articles on the PHP Chinese website!