How to modify the table and column names of mysql

Xiaobian to share with you how to modify mysql table name and column name, I hope you read this article after the harvest, let us discuss it together!

In mysql, table names can be modified with the statement ALTER TABLE old table name RENAME new table name; and column names can be modified with the statement ALTER TABLE table name CHANGE old field name/column name new field name/column name new data type;.

Change mysql table name

MySQL uses the ALTER TABLE statement to modify table names. The syntax rules are as follows:

ALTER TABLE RENAME [TO] ;

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

Example 1

Use ALTER TABLE to rename the data table student to tb_students_info, and the SQL statement and run results are as follows.

mysql> ALTER TABLE student RENAME TO tb_students_info;Query OK, 0 rows affected (0.01 sec)mysql> SHOW TABLES;+------------------+| Tables_in_test |+------------------+| tb_students_info |+------------------+1 row in set (0.00 sec)

Tip: Changing a table name does not change the structure of the table, so the structure of the table after changing the name is the same as the table before changing the name. Users can use the DESC command to view the modified table structure,

Modify mysql column name (field name)

MySQL data table is composed of rows and columns, usually the table "column" called field (Field), the table "row" called record (Record).

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

ALTER TABLE CHANGE ;

Of which:

Old field name: refers to the field name before modification;

New field name: refers to the modified field name;

New data type: refers to the modified data type. If the data type of the field does not need to be modified, the new data type can be set to be the same as the original data type, but the data type cannot be empty.

Example 2

Use ALTER TABLE to modify the structure of table tb_emp1, change the col1 field name to col3, and change the data type to CHAR(30). The SQL statement and running results 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 modify 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, modifying 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.

After reading this article, I believe you have a certain understanding of how to modify mysql table names and column names. If you want to know more relevant knowledge, welcome to pay attention to the industry information channel. Thank you for reading!