“This is the fourth day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

In this article, we explain how to create a table, and how to do the general operations when creating a table, such as defining increment primary keys, unique fields, non-empty attributes, carrying default values, and specifying foreign keys. However, these operations are planned in advance when we build the table. In fact, we will add, subtract, and modify fields, which is the purpose of this chapter

Table maintenance

Field to add

Append at the end

alter table <The name of the table> add <New field name><The data type>[Constraints];Copy the code

This way, fields are added to the end of the table

Add at the beginning

alter table <The name of the table> add <New field name> <The data type>[Constraints]first;
Copy the code

Mysql by default adds new fields at the end of the table. We can restrict the added fields to the first row of the table by using the keyword first

Add anywhere in the table

In addition to the above two methods, mysql also supports inserting new fields anywhere in the middle of the table. This requires the keyword after to specify which of the existing fields in the table the new field is inserted after

alter table <The name of the table> add <New field name> <The data type>[c]<Name of an existing field>;
Copy the code

Field to delete

In the new project, we designed a table that, after many field additions and a long run, had some fields that were no longer used

In order to optimize the table structure, we need to remove these redundant fields, the command is as follows:

alter table table_name drop column field
Copy the code

The UUID column inserted in the previous test has been removed

Field changes

Rename field

alter table <The name of the table> change <The old field name> <New field name> <New data type>[Constraints];Copy the code

Uname -> username, and also changed its default value

Field adjustment

alter table <The name of the table> modify <The field name> <The data type>
Copy the code

Not only has the length been changed, but the default values have been changed and unique constraints have been added

Additional foreign key

alter table <The name of the table> add foreignKey [custom foreign key name](<Table foreign key field>) references <Associative table>(<Associated table primary key>);

- the sample
alter table user add foreign key [fk_user_dept_id](dept_id) references dept(id);
Copy the code

Remove the foreign key

alter table <The name of the table> drop foreign key <Foreign key constraint name>

- The foreign key fk_user_dept_id added in the previous step is used as an example
alter table user drop foreignThe key fk_user_dept_id;Copy the code