How to MySQL- ALTER TABLE Statement

This MySQL tutorial explains how to use the MySQL ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table.
The ALTER statement is always used with “ADD”, “DROP” and “MODIFY” commands. The MySQL ALTER TABLE statement is also used to rename a table.

 

ADD a column(s) in the table

Let’s look how to add/insert a column(s) to table. The following syntax will add a column(s) to the table.

 

Syntax
     ALTER TABLE table_name
     ADD new_column_name column_definition
     [ FIRST | AFTER column_name ];

new_column_name: It specifies the name of the new column that you want to add to the table.

column_definition: It specifies the data type and definition of the column (NULL or NOT NULL, etc).

FIRST | AFTER column_name: It is optional. It tells MySQL wherein the table to create the column. If this parameter is not specified, the new column will be added to the end of the table.

 

Example:

In this example, we add a new column “designation” in the existing table “contacts”.

 

Example Query

MySQL Alter Table

 

 

Output

MySQL Alter Table

 

The following syntax is used to add multiple columns to MySQL table.

 

Syntax
     ALTER TABLE table_name  
     ADD new_column_name column_definition  
     [ FIRST | AFTER column_name ],  
     ADD new_column_name column_definition  
     [ FIRST | AFTER column_name ],  
     ...  
     ;

 

MODIFY column in the table

Let’s look how to Modify a column in a table. The following syntax will modify a column in a table.

Syntax
     ALTER TABLE table_name  
     MODIFY column_name column_definition  
     [ FIRST | AFTER column_name ];

column_definition: The modified data type and definition of the column (NULL or NOT NULL, etc).

FIRST | AFTER column_name: It is optional. It tells MySQL wherein the table to create the column. If this parameter is not specified, the new column will be added to the end of the table.

 

Example:

In this example, we modify the column designation to be a data type of varchar(40) and force the column to allow NULL values.

Example Query

MySQL ALTER TABLE Statement

 

Output

MySQL ALTER TABLE Statement

 

DROP column in table

The syntax to drop a column in a table in MySQL (using the ALTER TABLE statement) is

Syntax
     ALTER TABLE table_name  
     DROP COLUMN column_name;

column_name: The name of the column to delete from the table.

 

Example:

The example that shows how to drop a column in a MySQL table using the ALTER TABLE statement.

Example Query

MySQL ALTER TABLE Statement

 

This ALTER TABLE example will drop the column called designation from the table called contacts.

Anwar Yakkiparamban

Anwar Yakkiparamban is the founder of Lauyou Learning. Prior to Lauyou learning, Anwar worked at ARD Engineering & Development, Qatar. He holds bachelor degree in Electronics and Communication Engineering from Govt. Engineering College Idukki.

You may also like...