MySQL ALTER alter database table structure usage

1. Alter operation table Field

(1) Add a field

Alter table table name add field Name field type;

Alter table student Add name varchar (10 );

(2) Modify Fields

Alter table table name change old field name new field Name field type;

Alter table table name Modify Field Name field type; // modify the field type

Alter table student change name varchar (20) not null default 'liming'; // After modifying the field type default

Default Field Value

Alter table student change name name1 varchar (20) not null default 'liming'; // modify the field name

(3) Delete A Field

Alter table Table Name drop field name;

Alter table student drop name;

2. Alter index operation

(1) Add an index

Alter table table name Add Index name (field name 1, field name 2 .....);

Alter table student add index stu_name (name );

(2) Deleting an index

Alter table Table Name drop index name;

Alter table student drop index stu_name;

(3) view the index of a table

Show index from table name;

(4) add an index with unique restrictions

Alter table table name add unique index name (field name );

3. Primary Key operation

Add primary key:

Alter table table name add primary key (field name );

Delete primary key:

Alter table Table Name drop primary key; (if the primary key is not automatically increased)

Alter table name Modify Field field type, drop primary key; (when the primary key is automatically increased)

Alter table 123 modify ID int, drop primary key;