How to modify the table structure of MySQL database

This article mainly takes you to understand and use the MySQL ALTER TABLE statement to change the existing table structure (such as adding or deleting columns, changing column attributes, etc.).

MySQL ALTER TABLE statement

ALTER TABLE table_name action1[,action2,…]

• First, specify the name of the table to be changed after the ALTER TABLE clause.

• Secondly, list a set of operations to be applied to the table. The operation can be anything like adding a new column, adding a primary key, renaming a table, etc. The ALTER TABLE statement allows multiple operations to be applied in a single ALTER TABLE statement, with each operation separated by a comma (,).
  

First, create a new database:

CREATE TABLE tasks (
    id INT NOT NULL,
    subject VARCHAR(45) NULL,
    start_date DATE NULL,
    end_date DATE NULL
)charset utf8;

1) Modify the table name

alter table tasks rename  student;

2 ) Modify the storage engine of the table

alter table student ENGINE=MyISAM;

3) Delete columns from the table

alter table student drop column end_date;

4) Add new columns to the table, (Use after, before for specific positions)

alter table student
add column complete DECIMAL(2,1) NULL
AFTER subject;

5) Modify field names and field definitions

 alter table student change subject math varchar(20);

6) Modify field definitions

 alter table student modify math varchar(10);

Recommended: 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!