Home » MySQL

MySQL ALTER TABLE Statement

MySQL | ALTER TABLE: Learn about the MySQL ALTER TABLE Statement, with its explanation, syntax, and query examples.
Submitted by Apurva Mathur, on September 03, 2022

ALTER TABLE Statement

When we work on large projects then we don't have an idea how many columns we'll be required to store the values, in between the process many times we modify the table like we add some new columns, or we delete some columns, changing the column name so all these tasks are possible through MySQL ALTER TABLE statement.

Need of ALTER TABLE Statement

• This statement helps us by modifying the table in whichever format we want.
• With the help of this statement, we can even change the definition of columns like constraints, data type, size, etc.
• This statement even deletes the column if we don't want them.

ALTER TABLE Statement in MySQL Command-Line Client

First, we'll see how to use ALTER TABLE statement in the MySQL command-line client.

1) ALTER TABLE - ADD Column

To add a column in an existing table, the ALTER TABLE statement with ADD keyword is used. Here is the syntax.

ALTER TABLE table_name 
ADD field_name data type;

Suppose we have a table named "student_details" and inside that table, we have the following columns and values.

Now If I want to add one column to this table then I'll write:

ALTER TABLE student_details ADD CGPA int;

As you can see in the picture new column named "CGPA" has been added to the table.

2) ALTER TABLE – CHANGE COLUMN

To update a column definition i.e., change/rename a column in an existing table, the ALTER TABLE statement with CHANGE COLUMN keyword is used. Here is the syntax.

ALTER TABLE table_name 
CHANGE COLUMN 'old_field_name' 'new_field_name' data type;

Now, suppose I want to modify my column definition named "CGPA" which is INT to VARCHAR. For this, we'll use,

ALTER TABLE student_details CHANGE COLUMN `CGPA` `CGPA` VARCHAR(45) ;

3) ALTER TABLE – DROP COLUMN

To drop a column definition in an existing table, the ALTER TABLE statement with DROP COLUMN keyword is used. Here is the syntax.

ALTER TABLE table_name
DROP COLUMN column_name;

Now, if I want to drop/ delete any column then in that case I'll use:

ALTER TABLE student_details DROP COLUMN CGPA;

You can see that our column named "CGPA" is deleted.

4) ALTER TABLE – MODIFY Column

To modify a column definition, the ALTER TABLE statement with MODIFY keyword is used. Here is the syntax.

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

5) ALTER TABLE – RENAME TO (Renaming a table)

To rename a table, the ALTER TABLE statement with RENAME TO keyword is used. Here is the syntax.

ALTER TABLE old_table_name  
RENAME TO new_table_name;  

ALTER TABLE Statement in MySQL Workbench

Now, let us see how we can alter the table in MYSQL Workbench.

Step 1: Go to SCHEMAS and select the table you want to alter.

Here I'll alter my table named student_details.

Step 2: After selecting the table you want to alter, right-click on the table and click on alter table as shown in the picture.

Step 3: After clicking on alter table you can change any column you want to change.

As soon as you'll click on apply, the changes you have made will be visible to you.

---