Shulou(Shulou.com)06/01 Report--

When we need to change the data table name or modify the data table field, we need to use the MySQL ALTER command. Let's first create a table called: testalter_tbl.

Root@host# mysql-u root-p password;Enter password:*mysql > use OPENKETANG;Database changed mysql > create table testalter_tbl-> (- > I INT,- > c CHAR (1)->); Query OK, 0 rows affected (0.05 sec) mysql > SHOW COLUMNS FROM testalter_tbl +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | I | int (11) | | YES | | NULL | | c | char (1) | YES | | NULL | | +-+-+ 2 rows in set (0.00 sec) |

Delete, add, or modify table fields

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

Mysql > ALTER TABLE testalter_tbl DROP I

If there is only one field left in the data table, you cannot use DROP to delete the field.

Use the ADD clause in MySQL to add columns to the data table. The following example adds the I field to the table testalter_tbl and defines the data type:

Mysql > ALTER TABLE testalter_tbl ADD i INT

After executing the above command, the I field is automatically added to the end of the Datasheet field.

Mysql > SHOW COLUMNS FROM testalter_tbl +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | c | char (1) | | YES | | NULL | | I | int (11) | YES | | NULL | | +-+-+ 2 rows in set (0.00 sec) |

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

Try the following ALTER TABLE statement, and after successful execution, use SHOW COLUMNS to see the changes in the table structure:

ALTER TABLE testalter_tbl DROP ITANTALTER TABLE testalter_tbl ADD i INT FIRST;ALTER TABLE testalter_tbl DROP I TANTINALTER TABLE testalter_tbl ADD i INT AFTER c

The FIRST and AFTER keywords are used only in the ADD clause, so if you want to reset the position of the datasheet field, you need to use DROP to delete the field and then use ADD to add the field and set the location.

Modify field type and name

If you need to change the field type and 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:

Mysql > ALTER TABLE testalter_tbl MODIFY c CHAR (10)

Using the CHANGE clause, the syntax is very different. The CHANGE keyword is followed by the name of the field you want to modify, and then specify the new field name and type. Try the following example:

Mysql > ALTER TABLE testalter_tbl CHANGE i j BIGINT

ALTER TABLE testalter_tbl CHANGE j j INT

The influence of ALTER TABLE on Null values and default values

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

The following example specifies that field j is NOT NULL and the default value is 100.

Mysql > ALTER TABLE testalter_tbl-> MODIFY j BIGINT NOT NULL DEFAULT 100

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

Modify field default values

You can use ALTER to modify the default values of fields. Try the following example:

Mysql > ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000persMySQL > SHOW COLUMNS FROM testalter_tbl +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | c | char (1) | | YES | | NULL | I | int (11) | YES | | 1000 | | +-+-+ 2 rows in set (0.00 sec) |

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

Mysql > ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;mysql > SHOW COLUMNS FROM testalter_tbl +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | c | char (1) | YES | | NULL | | I | int (11) | YES | | NULL | | +-+-+ 2 rows in set (0.00 sec) Changing a Table Type:

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

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

Mysql > ALTER TABLE testalter_tbl ENGINE = MYISAM Mysql > SHOW TABLE STATUS LIKE 'testalter_tbl'\ gateway * 1. Row * Name: testalter_tbl Type: MyISAMRow_format: FixedRows: 0Avg_row_length: 0Data_length: 0Max_data_length: 25769803775Index_length: 1024Data_free: 0Auto_increment: NULL Create_time: 2007-06-03 08:04:36Update_time: 2007- 06-03 08:04:36Check_time: NULL Create_options:Comment:1 row in set (0.00 sec)

Modify table name

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

Try the following example to rename the datasheet testalter_tbl to alter_tbl:

Mysql > ALTER TABLE testalter_tbl RENAME TO alter_tbl

More MySQL courses: https://edu.aliyun.com/course/153/lesson/list?utm_content=g_1000056652

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.