Introduction

After creating a table, you may need to modify its structure. For example, you might want to add new columns, change data types, or remove existing columns.

In MySQL, this is done using the ALTER TABLE statement.

What is ALTER TABLE

The ALTER TABLE statement is used to change the structure of an existing table.

It allows you to:

• Add new columns

• Modify existing columns

• Delete columns

• Rename columns or tables

Basic Syntax

ALTER TABLE table_name operation; 

Add Column

To add a new column:

ALTER TABLE Students ADD email VARCHAR(100); 

This adds an email column to the Students table.

Modify Column

To change the data type or size of a column:

ALTER TABLE Students MODIFY name VARCHAR(100); 

This changes the size of the name column.

Drop Column

To remove a column:

ALTER TABLE Students DROP COLUMN age; 

This deletes the age column permanently.

Rename Column

To rename a column:

ALTER TABLE Students RENAME COLUMN name TO student_name; 

Add Multiple Changes

You can perform multiple changes in one statement:

ALTER TABLE Students ADD phone VARCHAR(15), MODIFY name VARCHAR(100); 

Example Scenario

Suppose you created a table:

CREATE TABLE Students ( id INT, name VARCHAR(50) ); 

Later, you want to add an email column:

ALTER TABLE Students ADD email VARCHAR(100); 

Common Mistakes

• Using incorrect syntax

• Dropping important columns accidentally

• Not checking the existing structure before modifying

• Forgetting column data types

Key Points to Remember

• ALTER TABLE is used to modify the table structure

• You can add, modify, or delete columns

• Changes are permanent

• Always verify before making changes