MySQL study notes 5: Modify tables

When creating a table, we will inevitably consider the problem of weeks. Therefore, the table will be modified later.

The alter table statement is required to modify a table.

 

Modify Table Name

 
MySQL> Alter TableStudent rename person; query OK,0Rows affected (0.03Sec)
 

Student is the original name and person is the modified name.

Rename or rename

Another method is rename table old_name to new_name.

 

Modify the Data Type of a field

MySQL> Alter TablePerson modify nameVarchar(20); Query OK,0Rows affected (0.18Sec) records:0Duplicates:0Warnings:0
 

Here, the name after modify is the field name. We will change the original varchar (25) to varchar (20)

 

Modify Field name

MySQL> Alter TablePerson change stu_name nameVarchar(25); Query OK,0Rows affected (0.20Sec) records:0Duplicates:0Warnings:0
 

Stu_name is the original name and name is the new name.

It should be noted that no matter whether the data type is changed, the subsequent data types must be written

If you do not modify the data type, you only need to write it as the original data type.

TIPS:We can also use change to achieve the modify effect. We only need to write the same field name after it.

 

Add fields without integrity constraints

MySQL> Alter TablePersonAddSex Boolean; query OK,0Rows affected (0.21Sec) records:0Duplicates:0Warnings:0
 

The sex here is followed by only the data type, without the integrity constraints.

 

Add fields with integrity constraints

MySQL> Alter TablePersonAddAgeInt Not Null; Query OK,0Rows affected (0.17Sec) records:0Duplicates:0Warnings:0
 

An age field is added here, followed by the not null Integrity Constraint.

 

Additional integrity constraints

MySQL> Alter TablePersonAdd Primary Key(ID); query OK,0Rows affected (0.18Sec) records:0Duplicates:0Warnings:0
 

Multiple fields are also used here.

 

Add fields to the header

MySQL> Alter TablePersonAddNumInt Primary KeyFirst; query OK,0Rows affected (0.20Sec) records:0Duplicates:0Warnings:0
 

By default, all fields are added to the end of the table. You can add the first field after the add statement to the table header.

 

Add a field to a specified position

MySQL> Alter TablePersonAddBirth date after name; query OK,0Rows affected (0.20Sec) records:0Duplicates:0Warnings:0
 

Add a new field next to the name field.

TPS:The sorting of fields in a table does not affect the table, but it is easier to understand the table with more reasonable sorting.

 

Delete Field

MySQL> Alter TablePersonDropSex; query OK,0Rows affected (0.18Sec) records:0Duplicates:0Warnings:0
 

Like deleting a table or database, you also need to use drop

The difference is that alter table must be used to delete a field with the table name.

Modify the field to the first position

MySQL> Alter TablePerson modify IDIntFirst; query OK,0Rows affected (0.20Sec) records:0Duplicates:0Warnings:0
 

First has been mentioned earlier. Note that the data type should be written after the field.

 

Modify a field to a specified position

MySQL> Alter TablePerson modify nameVarchar(25) After ID; query OK,0Rows affected (0.18Sec) records:0Duplicates:0Warnings:0
 

We put the name field behind the ID. The varchar (25) here should be fully written, but varchar will not work.

We recommend that you desc table before performing the preceding steps.

 

Modify the storage engine of a table

MySQL> Alter Table UserRename person; query OK,0Rows affected (0.05Sec)
 

The features of each storage engine are not described here. There are many contents.

After modification, do not forget to use the show create table statement. Section 3 describes write usage.

TIPS:If a table already contains a large amount of data, do not modify the storage engine easily.

 

Add a foreign key to the table

MySQL> Alter TableScoreAdd ConstraintFKForeign Key(Stu_id)ReferencesStudent (ID); query OK,10Rows affected (0.18Sec) records:10Duplicates:0Warnings:0
 

Here, you only need to use Add. For more information about the syntax, see the foreign key settings in section 4.

 

Delete primary key

MySQL> Alter TablePersonDrop Primary Key; Query OK,0Rows affected (0.22Sec) records:0Duplicates:0Warnings:0
 

Because the primary key does not have an alias, using drop will delete all primary keys.

 

Delete A table's foreign key constraint

MySQL> Alter TableStudent3Drop Foreign KeyFK; query OK,0Rows affected (0.18Sec) records:0Duplicates:0Warnings:0
 

Because the basic table structure description cannot display foreign keys, you 'd better use show create table to view the table before performing this operation.

The FK here is the foreign key just set.

Note: To delete an associated table, you must first Delete the foreign key.

After the foreign key is deleted, the original key becomes a normal key.

To delete a table, write in section 3 and set a foreign key.

If no foreign key is set during table creation, use the above method