Mysql statement to modify the table structure is "ALTER TABLE". This statement can change the structure of the original table, such as adding or deleting columns, changing the original column type, renaming columns or tables, etc.; syntax format is "ALTER TABLE ccc43248daffbac9770dee47fdaff697 [modify options]".
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
Mysql statement to modify the table structure is "ALTER TABLE".
ALTER TABLEstatement 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, etc. .
MySQL uses the ALTER TABLE statement to modify the table. Commonly used operations to modify a table include modifying the table name, modifying field data types or field names, adding and deleting fields, modifying the arrangement of fields, changing the storage engine of the table, deleting foreign key constraints of the table, etc.
The commonly used syntax format is as follows:
ALTER TABLE <表名> [修改选项]
The syntax format for modifying options is as follows:
{ ADD COLUMN <列名> <类型> | CHANGE COLUMN <旧列名> <新列名> <新列类型> | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } | MODIFY COLUMN <列名> <类型> | DROP COLUMN <列名> | RENAME TO <新表名> | CHARACTER SET <字符集名> | COLLATE <校对规则名> }
Add fields
As the business changes, it may be necessary to add new fields to existing tables. A complete field includes field name, data type, and integrity constraints. The syntax format of adding a field is as follows:
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名]；
The new field name is the name of the field that needs to be added; FIRST is an optional parameter, its function is to set the newly added field as the first field of the table; AFTER is Optional parameter, its function is to add the newly added field after the specified existing field name.
[Example 1] Use ALTER TABLE to modify the structure of table tb_emp1, and add an int type field col1 to the first column of the table. The input SQL statement and running results are as follows.
mysql> ALTER TABLE tb_emp1 -> ADD COLUMN col1 INT FIRST; Query OK, 0 rows affected (0.94 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> 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: "FIRST or AFTER existing field name" is used to specify the position of the new field in the table. If there are no these two parameters in the SQL statement, the newly added field will be set to by default. The last column of the data table.
[Example 2] Use ALTER TABLE to modify the structure of table tb_emp1, and add an int type field col2 after the name column. The input SQL statement and running results are as follows.
mysql> ALTER TABLE tb_emp1 -> ADD COLUMN col2 INT AFTER name; Query OK, 0 rows affected (0.50 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> 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)
As you can see, a field named col2 has been added to table tb_emp1. Its position is behind the specified name field. The field was added successfully.
Modify field data type
Modifying the data type of a field is to convert the data type of the field into another data type. The syntax rules for modifying field data types in MySQL are as follows:
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
Among them, the table name refers to the name of the table where the field whose data type needs to be modified is located, the field name refers to the field that needs to be modified, and the data type refers to the modified field. New data type.
[Example 3] Use ALTER TABLE to modify the structure of table tb_emp1, and change the data type of the name field from VARCHAR(22) to VARCHAR(30). The input SQL statement and running results are as follows.
mysql> ALTER TABLE tb_emp1 -> MODIFY name VARCHAR(30); Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> 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 table tb_emp1 has been modified to VARCHAR(30), and the modification is successful.
Deleting a field
Deleting a field is to remove a field in the data table from the table. The syntax format is as follows:
ALTER TABLE <表名> DROP <字段名>；
Among them, the field name refers to the name of the field that needs to be deleted from the table.
[Example 4] Use ALTER TABLE to modify the structure of table tb_emp1 and delete the col2 field. The input SQL statement and running results are as follows.
mysql> ALTER TABLE tb_emp1 -> DROP col2; Query OK, 0 rows affected (0.53 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> 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 names
The syntax rules for modifying table field names in MySQL are as follows:
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；
Among them, the old field The name refers to the field name before modification; the new field name refers to the modified field name; 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 be the same as the original, but the data Type cannot be empty.
[Example 5] 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 input SQL statement and running results are as follows.
mysql> ALTER TABLE tb_emp1 -> CHANGE col1 col3 CHAR(30); Query OK, 0 rows affected (0.76 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> 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 only modify the data type to achieve the same effect as MODIFY. The method is to set the "new field name" and "old field name" in the SQL statement to the same name, and only change the "data type".
Tip: Since 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 modify the data type easily.
Modify the table name
MySQL uses the ALTER TABLE statement to modify the table name. 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. The input SQL statement and running results are as follows.
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)
提示：用户可以在修改表名称时使用 DESC 命令查看修改后两个表的结构，修改表名并不修改表的结构，因此修改名称后的表和修改名称前的表的结构是相同的。
【相关推荐：mysql视频教程】
The above is the detailed content of What is the statement that mysql uses to modify the table structure?. For more information, please follow other related articles on the PHP Chinese website!