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 |