Shulou(Shulou.com)05/31 Report--

This article is about how mysql modifies the table structure. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The statement that mysql modifies the table structure is "ALTER TABLE", which can change the structure of the original table, such as adding or deleting columns, changing the original column type, renaming columns or tables, etc.; the syntax format is "ALTER TABLE [modify options]".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

The ALTER TABLE statement can change the structure of the original table, such as adding or deleting columns, creating or canceling indexes, changing the original column type, renaming columns or tables, and so on.

MySQL uses the ALTER TABLE statement to modify the table. The commonly used operations to modify the table are to modify the table name, modify the field data type or field name, add and delete fields, modify the arrangement position of fields, change the storage engine of the table, delete the foreign key constraints of the table, and so on.

The common syntax formats are as follows:

ALTER TABLE [modify options]

The syntax format of the modification option is as follows:

{ADD COLUMN | CHANGE COLUMN | ALTER COLUMN {SET DEFAULT | DROP DEFAULT} | MODIFY COLUMN | DROP COLUMN | RENAME TO | CHARACTER SET | COLLATE}

Add a field

As the business changes, you may need to add new fields to an existing table, and a complete field includes field names, data types, and integrity constraints. The syntax format for adding fields is as follows:

ALTER TABLE ADD [constraint] [FIRST | Field name that already exists in AFTER]

The new field name is the name of the field to be added; FIRST is optional and its function is to set the newly added field to the first field of the table; and AFTER is optional to add the newly added field after the specified existing field name.

[example 1] use ALTER TABLE to modify the structure of the table tb_emp1, adding a field of type int col1 in the first column of the table. The SQL statement entered and the running result are shown below.

Mysql > ALTER TABLE tb_emp1-> ADD COLUMN col1 INT FIRST;Query OK, 0 rows affected (0.94 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_emp1 +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | col1 | int (11) | YES | | NULL | id | int (11) | YES | | NULL | | name | varchar (25) | YES | | NULL | | deptId | int (11) | YES | | NULL | salary | float | YES | | NULL | | +-+-- | -+ 5 rows in set (0.00 sec)

Tip: the existing field name of FIRST or AFTER is used to specify the location of the new field in the table, and if these two parameters are not available in the SQL statement, the newly added field is set to the last column of the data table by default.

[example 2] use ALTER TABLE to modify the structure of the table tb_emp1, and add a field of type int col2 after a column of name. The SQL statement entered and the running result are shown below.

Mysql > ALTER TABLE tb_emp1-> ADD COLUMN col2 INT AFTER name;Query OK, 0 rows affected (0.50 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_emp1 +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | col1 | int (11) | YES | | NULL | id | int (11) | YES | | NULL | | name | varchar (25) | YES | | NULL | | col2 | int (11) | YES | | NULL | deptId | int (11) | YES | NULL | | salary | float | YES | | | NULL | | +-+-+ 6 rows in set (0.00 sec) |

You can see that a field named col2 has been added to the table tb_emp1, and its position is after the specified name field. The field was added successfully.

Modify field data type

To change the data type of a field is to convert the data type of the field to another data type. The syntax rules for modifying field data types in MySQL are as follows:

ALTER TABLE MODIFY

Where the table name refers to the name of the table where the field of the data type is to be modified, the field name refers to the field to be modified, and the data type refers to the new data type of the modified field.

[example 3] use ALTER TABLE to modify the structure of the table tb_emp1, and change the data type of the name field from VARCHAR (22) to VARCHAR (30). The input SQL statement and the running result are shown below.

Mysql > ALTER TABLE tb_emp1-> MODIFY name VARCHAR (30); Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_emp1 +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | col1 | int (11) | YES | | NULL | id | int (11) | YES | | NULL | | name | varchar (30) | YES | | NULL | | col2 | int (11) | YES | | NULL | deptId | int (11) | YES | NULL | | salary | float | YES | | | NULL | | +-+-+ 6 rows in set (0.00 sec) |

After the statement is executed, it is found that the data type of the name field in the table tb_emp1 has been changed to VARCHAR (30), and the modification is successful.

Delete a field

Deleting a field removes a field from the data table in the following syntax format:

ALTER TABLE DROP

Where the field name is the name of the field that needs to be deleted from the table.

[example 4] use ALTER TABLE to modify the structure of the table tb_emp1, delete the col2 field, enter the SQL statement and run the result as shown below.

Mysql > ALTER TABLE tb_emp1-> DROP col2;Query OK, 0 rows affected (.53 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_emp1 +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | col1 | int (11) | YES | | NULL | id | int (11) | YES | | NULL | | name | varchar (30) | YES | | NULL | | deptId | int (11) | YES | | NULL | salary | float | YES | | NULL | | +-+-- | -+ 5 rows in set (0.00 sec)

Modify field name

The syntax rules for modifying table field names in MySQL are as follows:

ALTER TABLE CHANGE

Where the old field name refers to the field name before modification, the new field name refers to the modified field name, and the new data type refers to the modified data type. If you do not need to modify the data type of the field, you can set the new data type to the same as the original, but the data type cannot be empty.

[example 5] use ALTER TABLE to modify the structure of the table tb_emp1, change the name of the col1 field to col3, and change the data type to CHAR (30). The SQL statement entered and the running result are shown below.

Mysql > ALTER TABLE tb_emp1-> CHANGE col1 col3 CHAR (30); Query OK, 0 rows affected (0.76 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_emp1 +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | col3 | char (30) | YES | | NULL | id | int (11) | YES | | NULL | | name | varchar (30) | YES | | NULL | | deptId | int (11) | YES | | NULL | salary | float | YES | | NULL | | +-+-- | -+ 5 rows in set (0.01 sec)

CHANGE can also change only the data type to achieve the same effect as MODIFY by setting the "new field name" and "old field name" in the SQL statement to the same name, and changing only the "data type".

Tip: because different types of data are stored in different ways and lengths in the machine, changing the data type may affect the existing data records in the data table. Therefore, when there is already data in the data table, do not easily modify the data type.

Modify table name

MySQL modifies the table name through the ALTER TABLE statement. The syntax rules are as follows:

ALTER TABLE RENAME [TO]

Among them, TO is an optional parameter, and whether it is used or not does not affect the result.

[example 6] use ALTER TABLE to rename the data table tb_emp1 to tb_emp2. Enter the SQL statement and run the result as shown below.

Mysql > ALTER TABLE tb_emp1-> RENAME TO tb_emp2;mysql > SHOW TABLES;+-+ | Tables_in_test_db | +-+ | tb_emp2 | +-+ 1 rows in set (0.00 sec)

Tip: when you change the table name, you can use the DESC command to view the structure of the modified two tables. Changing the table name does not change the structure of the table, so the structure of the table after changing the name is the same as that before changing the name.

Thank you for reading! This is the end of the article on "how to modify the table structure of mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.