mysql alter table modification table command arrangement_MySQL

The MYSQL ALTER TABLE command is used to modify the table structure, such as adding/modifying/deleting fields, indexes, primary keys, etc. This article introduces how to use the MYSQL ALTER TABLE statement through examples,

MySQL ALTER syntax is as follows:

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...] 
alter_specification: 
ADD [COLUMN] create_definition [FIRST | 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 table_options 

Let’s look at a few examples:

1. Add the Account_Number field to the employee table and set its field type to INT

ALTER TABLE employee ADD COLUMN Account_Number INT

2. Modify the ID field in the employee table to be an index

ALTER TABLE employee ADD INDEX (ID)

3. Modify the ID field in the employee table to be the primary key PRIMARY KEY

ALTER TABLE employee ADD PRIMARY KEY (ID)

4. Modify the ID field in the employee table to be the 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 the employee table

ALTER TABLE employee DROP PRIMARY KEY

8. Deleting the index of the field Customer_ID in the employee table only cancels the index of Customer_ID and does not delete the Customer_ID field.

ALTER TABLE employee DROP INDEX Customer_ID

9. Modify the field type of First_Name in the employee table to varchar(100)

ALTER TABLE employee MODIFY First_Name varchar(100)

10. Rename the employee table to Customer

ALTER TABLE employee RENAME Customer

11. Write multiple commands together:

mysql> ALTER TABLE Books
  -> ADD PRIMARY KEY (BookID),
  -> ADD CONSTRAINT fk_1 FOREIGN KEY (PubID) REFERENCES Publishers (PubID),
  -> ADD COLUMN Format ENUM('paperback', 'hardcover') NOT NULL AFTER BookName;

Thanks for reading this article, I hope it can help everyone, thank you for your support of this site!