MySQL change table structure alter (ALTER) usage

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.