Summary of MySQL ALTER command knowledge points

When we need to change the table name or modify the table fields, we need to use the MySQL ALTER command .

1. Create a table named testalter_tbl.

2. Delete, add or modify table fields

The following command uses the ALTER command and the DROP clause to delete the i column of the table created above:

3. If there is only one field left in the data table, DROP cannot be used to delete the field.

In MySQL, the ADD clause is used to add columns to a table. The following example adds the i field to the testalter_tbl table and defines the data type:

4. After executing the above command, the i field will be automatically added to the end of the data table field.

5. If you need to specify the location of the new field, you can use the keywords FIRST (set to the first column) and AFTER field name (set to after a certain word) provided by MySQL.

Try the following ALTER TABLE statement. After successful execution, use SHOW COLUMNS to view the changes in the table structure:

6. The FIRST and AFTER keywords are only used in the ADD clause, so if you want to reset the position of the data table fields, you need to use DROP to delete the fields and then use ADD to add the fields and set the position. The FIRST and AFTER keywords are only used in the ADD clause, so if you want to reset the position of a table field you need to first use DROP to remove the field and then use ADD to add the field and set the position.

7. Modify field type and name

If you need to change the column type or name, you can use the MODIFY or CHANGE clause in the ALTER command.

For example, to change the type of field c from CHAR(1) to CHAR(10), you can execute the following command:

8. When using the CHANGE clause, the syntax is very different. After the CHANGE keyword, follow the name of the field you want to modify, and then specify the new field name and type. Try the following example:

9. Effect of ALTER TABLE on Null Values ​​and Default Values

When you modify a field, you can specify whether to include it or whether to set a default value.

In the following example, the specified field j is NOT NULL and has a default value of 100.

10. If you do not set a default value, MySQL will automatically set the field to NULL.

Modify field default value

You can use ALTER to modify the default value of a column. Try the following example:

11. You can also use the ALTER command and the DROP clause to delete the default value of a field, as shown in the following example:

12. To modify the data table type, use the ALTER command and the TYPE clause. Try the following example, we will change the type of the table testalter_tbl to MYISAM:

Note: You can use the SHOW TABLE STATUS statement to view the data table type.

13. Modify table name

If you need to change the name of a data table, you can use the RENAME clause in the ALTER TABLE statement to do so.

Try the following example to rename the data table testalter_tbl to alter_tbl:

Remark:

Change the storage engine: change to myisam

alter table tableName engine=myisam;

Delete foreign key constraint: keyName is the foreign key alias

alter table tableName drop foreign key keyName;

Modify the relative position of the field: Here name1 is the field you want to modify, type1 is the original type of the field, and you can choose between first and after. This should be obvious. First is placed first, and after is placed after the name2 field.

alter table tableName modify name1 type1 first|after name2;