MySQL ALTER TABLE modify tables command details _mysql

The MySQL alter syntax is as follows:

ALTER [IGNORE] TABLE tbl_name alter_spec [, Alter_spec ...] 
Alter_specification: 
ADD [COLUMN] create_definition [a] After column_name] 
or ADD INDEX [index_name] (index_col_name,...) 
or ADD PRIMARY KEY (index_col_name,...) 
or ADD UNIQUE [index_name] (index_col_name,...) 
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} 
or change [column] Old_col_name create_definition 
or MODIFY [column] Create_definition 
or Drop [COLUMN] col_name 
or drop PRIMARY KEY 
or drop INDEX index_name 
or RENAME [as] new_tbl_name 
or Tab Le_options 
 

Let's look at a few examples:

1, add the Account_number field to table employee and set its field type to int

ALTER TABLE employee ADD COLUMN account_number INT

2, modify the table Employee ID field is indexed

ALTER TABLE employee ADD INDEX (ID)

3, modify table Employee ID field as the primary key primary keys

ALTER TABLE employee ADD PRIMARY KEY (ID)

4, modify table Employee ID field is unique index unique

ALTER TABLE employee ADD UNIQUE (ID)

5. Rename the ID field in the Employee table to salary and set its data type to int

ALTER TABLE Employee Change ID salary INT

6, delete the customer_id field in the Employee table

ALTER TABLE Employee DROP customer_id

7. Delete all primary keys in employee table

ALTER TABLE employee DROP PRIMARY KEY

8. Delete the index of the field customer_id in the employee table, just cancel the customer_id index and do not delete the customer_id field.

ALTER TABLE employee DROP INDEX customer_id

9, modify the Employee Table first_name field type is varchar (100)

ALTER TABLE employee MODIFY first_name varchar (100)

10. Rename the table employee to customer

ALTER TABLE Employee RENAME Customer

11. Multiple commands are written together:

mysql> ALTER TABLE Books
  -> add PRIMARY key (BookID),
  -> add CONSTRAINT fk_1 key (FOREIGN) PubID ES Publishers (PubID),
  -> ADD COLUMN Format ENUM (' paperback ', ' hardcover ') not NULL after BookName;

Thank you for reading, I hope to help you, thank you for your support for this site!