How to modify the table structure of MySQL database

Methods for modifying table structure in MySQL database: 1. Use add to add fields and use drop to delete fields; 2. Use alter to modify field names; 3. Modify column types; 4. Modify table names; 5. Modify tables Option; 6. Modify column attributes.

[Related graphic tutorial: mysql database graphic tutorial]

MySQL database modification table structure Method:

1. Add and delete fields

(1), add

Alter table table name add[column] field Name column type column attribute [first|after field name]

(2), delete

Alter table table name drop[column] field name;

2. Modify the field name

Statement: alter table table name change original field name new field name column type column attribute;

Note: Even if only the field name is modified, the original type and original attributes of the field must be rewritten, otherwise they will be deleted.

3. Modify column type

Statement: alter table table name modify field name column type column attribute;

4. Modify the table name

Statement: alter table table name rename to new table name;

5. Modify table options

Statement: alter table table name table option;

Description: Although MYSQL provides a command to modify table options, if If there is already data in a table, do not execute the command to modify the character set.

6. Modify column attributes

Column attributes include not null, default, unique, primary key, auto_increment

After a table is created, we can use alter table table name modify to operate column attributes. During operation, if the column attribute is written, the column attribute is added; if not, the column attribute is deleted.

Special column attributes: Primary key and unique.

Add column attributes

Add common attributes:

Statement: alter table table name modify field name column type column attribute;

Note:

A. When adding auto_increment, please note that the field must be an integer, and it must be a unique or primary key.

B. Unique and default cannot be used together.

Add primary key attributes:

Statement: alter table table name add primary key (field name);

Related Learning recommendation:

mysql tutorial

The above is the detailed content of How to modify the table structure of MySQL database. For more information, please follow other related articles on the PHP Chinese website!