MySQL Modify table Structure statement

I. First, create a test table TableA

CREATE TABLE ' TableA ' (' createtime ' int (ten) unsigned not NULL default 0 COMMENT ' createtime ') Engine=myisam auto_incremen T=4 DEFAULT Charset=utf8;

Two. Add fields

Format: ALTER TABLE tableName ADD Newfield TYPE; Add primary key chestnut: ALTER TABLE tableA Add ID INT (TEN) UNSIGNED not NULL auto_increment COMMENT ' id ', add PRIMARY KEY (ID); add element chestnut: ALTER TABLE tableA ADD nameStr VARCHAR (255) Not NULL default ' default ' COMMENT ' name '

Execution results

CREATE TABLE ' TableA ' (' createtime ' int (ten) unsigned NOT null DEFAULT ' 0 ' COMMENT ' tmp ', ' id ' int (ten) unsigned NOT NULL Auto_increment COMMENT ' id ', ' nameStr ' varchar (255) Not NULL default ' default ' COMMENT ' name ', PRIMARY KEY (' id ')) ENGI Ne=myisam auto_increment=4 DEFAULT Charset=utf8;

Three. modifying fields

Format: ALTER TABLE tableName change Oldfield Newfield TYPE; Chestnut: ALTER TABLE tableA Change nameStr newnamestr VARCHAR (255) Not NULL default ' default ' COMMENT ' name '

Execution Result:

CREATE TABLE ' TableA ' (' createtime ' int (ten) unsigned NOT null DEFAULT ' 0 ' COMMENT ' tmp ', ' id ' int (ten) unsigned NOT NULL Auto_increment COMMENT ' id ', ' newnamestr ' varchar (255) Not NULL default ' default ' COMMENT ' name ', PRIMARY KEY (' id ')) E Ngine=myisam auto_increment=4 DEFAULT Charset=utf8;

Four. Delete a field

Format: ALTER TABLE tableName DROP COLUMN Removefield; Chestnut: ALTER TABLE tableA DROP COLUMN createtime;

Execution Result:

CREATE TABLE ' TableA ' (' id ' int (ten) unsigned not NULL auto_increment COMMENT ' id ', ' newnamestr ' varchar (255) NOT NULL D Efault ' default ' COMMENT ' name ', PRIMARY KEY (' id ')) engine=myisam auto_increment=4 default Charset=utf8;

Five. Create an index

Format: Create index KeyName on TableName (fieldonename,fieldtwoname), chestnut: Create index ' newnamestr ' on ' TableA ' (NEWNAMESTR);

Execution Result:

CREATE TABLE ' TableA ' (' id ' int (ten) unsigned not NULL auto_increment COMMENT ' id ', ' newnamestr ' varchar (255) NOT NULL D Efault ' default ' COMMENT ' name ', PRIMARY key (' ID '), key ' Newnamestr ' (' newnamestr ')) Engine=myisam auto_increment=4 DEF Ault Charset=utf8;

MySQL Modify table Structure statement