Use of the alter for MySQL

ALTER Table Statement to add, modify, or delete columns in an existing table

1.ADD [Column] column name (column definitions) [first or after column_name]

Adds a column and can specify that the column be added before or after a column name (column_name)

2. ADD INDEX [index_name] (column_list)

Increase the index of a column

3.ADD PRIMARY KEY (column_list)

Increase the primary key of a column

4.ADD UNIQUE [index_name] (column_list)

Add a column's unique constraint

5.ALTER [COLUMN] column_name {SET default default_value or DROP default}

Set a DEFAULT constraint on a column or delete its default constraint

6.CHANGE [COLUMN] Old_col_name create_definition

Change the name of a column or, attribute

7.DROP [COLUMN] Col_name

Delete a column

8.DROP PRIMARY KEY

Delete the primary key property of a column

9.DROP INDEX index_name

Index of the DELETE key

10.MODIFY [COLUMN] Create_definition RENAME [as] New_tbl_name

function and change the same, just do not need to write two times Old_colname

Note: The employee database needs to be downloaded with the same usage

ALTER TABLE employee ADD COLUMN account_number INT

ALTER TABLE employee ADD INDEX (ID)

ALTER TABLE employee ADD PRIMARY KEY (ID)

ALTER TABLE employee ADD UNIQUE (ID)

ALTER TABLE Employee Change ID salary INT

ALTER TABLE Employee DROP customer_id

ALTER TABLE employee DROP PRIMARY KEY

ALTER TABLE employee DROP INDEX customer_id

ALTER TABLE employee MODIFY first_name varchar (100)

ALTER TABLE Employee RENAME Customer

Here is the usage example:

1.alter Action table field

(1) Add field

ALTER TABLE table name Add field name fields type;

ALTER TABLE student add name varchar (10);

(2) Modifying a field

ALTER TABLE name change old field name new field name fields type;

ALTER TABLE student change name name varchar (a) NOT null default ' liming ';//Modify field type

ALTER TABLE student change name name1 varchar (a) NOT null default ' liming ';//Modify field name

(3) Delete a field

ALTER TABLE name drop field name;

ALTER TABLE student drop name;

2.alter Index Operations

(1) Add index

ALTER TABLE name add index index name (field name 1, field Name 2 ...). ）；

ALTER TABLE student Add index stu_name (name);

(2) Deleting an index

ALTER 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) Increase the index of the unique restriction condition

ALTER TABLE name add unique index name (field name);

3. Primary KEY operation

Add primary key:

ALTER TABLE name Add primary key (field name);

• Add PRIMARY KEY constraint: ALTER TABLE name add constraint primary key (Shape: Pk_ table name) primary key table name (primary key field);
• Add FOREIGN KEY constraint: ALTER TABLE from TABLE ADD constraint foreign key (shape: Fk_ from Table _ Main Table) foreign key from table (foreign key field) references Main Table (primary key field);

Primary KEY 549830479

ALTER TABLE tabelname add new_field_id int (5) unsigned default 0 NOT NULL auto_increment, add primary key (NEW_FIELD_ID);
Add a new column 549830479

ALTER TABLE T2 add d timestamp;
ALTER TABLE infos add ex tinyint NOT null default ' 0 ';
Delete a column 549830479

ALTER TABLE T2 drop column C;
Renaming a column 549830479

ALTER TABLE T1 change a b integer;

Changing the type of a column 549830479

ALTER TABLE T1 change b b bigint not null;
ALTER TABLE infos change list list tinyint not null default ' 0 ';

Renaming a table 549830479

ALTER TABLE t1 rename T2;
Add Index 549830479

Mysql> ALTER TABLE tablename change depno depno int (5) is not null;
Mysql> ALTER TABLE tablename ADD index index name (field name 1[, field Name 2 ...]);
Mysql> ALTER TABLE tablename Add index Emp_name (name);
Index of the plus primary keyword 549830479

Mysql> ALTER TABLE TableName ADD PRIMARY key (ID);
Index of the unique restriction condition 549830479

Mysql> ALTER TABLE tablename add unique emp_name2 (cardnumber);
Delete an index 549830479

Mysql>alter table tablename DROP index emp_name;
Modified table: 549830479

Add Field: 549830479

mysql> ALTER TABLE table_name ADD field_name Field_type;
Modify the original field name and type: 549830479

mysql> ALTER TABLE table_name change old_field_name new_field_name field_type;
Delete field: 549830479

mysql> ALTER TABLE table_name DROP field_name;

Delete PRIMARY key: ALTER TABLE Employees drop PRIMARY key;

Use of the alter for MySQL