MySQL ALTER TABLE Statement

The ALTER TABLE statement in MySQL is crucial for adjusting the structure of tables, including adding or deleting columns, altering column types, and renaming tables, a vital tool for database schema updates. Let's discuss ALTER TABLE Statement in detail.

MySQL ALTER TABLE Statement

Given below are the scenarios where the ALTER TABLE in MySQL needs to be used. Each one of these comprises different parameters to use along with the MySQL statement.

We can have a table of employees working in an organization. To begin with, there can be minimal data to show the information and the other ones can be added/removed in the later sections.

Here's the query in MySQL to create the table with emp_id, first_name, age, and dept with each of the records having emp_id as their PRIMARY KEY.

ALTER TABLE - ADD COLUMN

The ADD clause in MySQL is used to add a new column to the existing table. In order to add a new column, the name of the column, its data type, and any other necessary attribute (like specifying the size of the data that the column can hold) should be specified in the ALTER TABLE statement.

Syntax:

Example : We can add a new column that contains the last name of all the employees in an organization with the column name last_name. So, we should specify the last name can have a maximum of 15 characters.

Let's insert some queries to see how the data is displayed from the table.

Output:

Since the column last_name was introduced after the table had been created, it is being added to the end of the table by default.

There are specifiers like FIRST and AFTER that can help you specify the position of a column in a table. If you wish to put a column at the very beginning, then use FIRST to put it first in the table or you can specify the name of any column after which it should be placed in the table.

Here's the syntax for the ALTER TABLE to use along with the specifiers in the ADD clause.

Example: Let's add a new column salary in the employees table.

Output:

Here's one additional example to show how you can add multiple columns to a table by using ALTER TABLE in MySQL.

ALTER TABLE - DROP COLUMN

Actually, no one wishes their salary to be so transparent to everyone and neither do these employees as well. So, it's good to remove the entire column salary from the table. We can use the DROP clause with the ALTER TABLE in MySQL to drop a column from the table.

Syntax:

Use the query given below to drop the column salary from the table.

Output:

ALTER TABLE - MODIFY COLUMN

MODIFY COLUMN is used to change the data type or length of an existing column. You need to specify the name of the column and the new data type or length.

Syntax to modify the data type of a column:

We can also reposition a column in a table by using the MODIFY COLUMN clause. The same set of specifiers (FIRST | AFTER) can be applied to MODIFY COLUMN to reposition an already existing column in a table.

In our example, we can put the column last_name beside first_name. And we should also modify it to have a maximum of 20 characters.

Output:

ALTER TABLE - RENAME COLUMN

RENAME COLUMN is used to rename an existing column in the table. You need to specify the current name of the column and the new name you want to give it.

Syntax:

Example:

Output:

ALTER TABLE - RENAME TABLE

We can also rename the entire table by just specifying the current name of the table and the new name that we wish to set for it. The RENAME TABLE clause can be used to rename a table.

Syntax:

Example:

Output: