MySQL change table structure alter (ALTER) usage

Mysql Statement Change table structure ALTER (ALTER)
Original

Table A For example:

The code is as follows	Copy Code
CREATE TABLE A ( A_a int NOT NULL, A_b int NOT NULL, A_c Char ) Engine=innodb Charset=utf8;

Syntax: ALTER TABLE name action:

Description: Action can be the following statement:

You can add a column to a table, or add a column at the end of a column if you do not specify first or after, otherwise add a new column to the specified column

Add column name < CREATE TABLE statement > (i | after column name)

The code is as follows	Copy Code
ALTER TABLE A add a_d int A; #first added in the first column. ALTER TABLE A add a_e int after a_b; #first added in the first column. ALTER TABLE A add A_f int;

Adds a primary key to the table and an error occurs if the primary key already exists

The code is as follows	Copy Code
Add primary key (column name) ALTER TABLE A add primary key (A_A);

You can change the default value for the specified column

The code is as follows	Copy Code
ALTER COLUMN name set default defaults ALTER TABLE A ALTER A_C set default ' Y ';

You can change the column type, and if the name of the original column is the same as the name of the new column, then the changes and modify act the same

The code is as follows	Copy Code
Change (Modify) Column name < table statement > (i | after) ALTER TABLE A change a_c a_f int [i | after x column name]; #更改列名 type position the new column name is in front of the old column name ALTER TABLE A modify A_e Char [after the x column name]; #只更改列类型或者位置

You can delete a column

Drop Column Name

The code is as follows	Copy Code
ALTER TABLE A drop a_d;

You can delete a primary key

The code is as follows	Copy Code
Drop PRIMARY Key ALTER TABLE A drop PRIMARY key;

You can delete an index

The code is as follows	Copy Code
Drop index index_name; ALTER TABLE A DROP INDEX index_name;

You can change the table name

code is as follows	copy code
Rename as New table name   Rename table name to new table name;     alter table  A rename as b;  Rename Ta ble B to A; #旧表名 to new table name