Mysql alter table command arrangement, mysqlalter

Mysql alter table command arrangement, mysqlalter

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

The 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 

Here are several instances:

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

Alter table employee add column Account_Number INT

2. Modify the ID field in the employee table as an index.

Alter table employee add index (ID)

3. Modify the ID field in the table employee as the primary key.

Alter table employee add primary key (ID)

4. Modify the ID field in the table employee as 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. Delete the Customer_ID index in the employee table, but cancel the Customer_ID index and do not delete the Customer_ID field.

Alter table employee drop index Customer_ID

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

Alter table employee MODIFY First_Name varchar (100)

10. Rename the table "employee" 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;

Thank you for reading this article and hope to help you. Thank you for your support for this site!