Add or Delete Columns in MySQL

Introduction

While working with databases, you may need to modify table columns by adding new ones or removing existing ones.

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

This allows you to update the table structure without deleting the table.

Adding Columns in MySQL

Syntax

ALTER TABLE table_name ADD column_name datatype; 

Example

ALTER TABLE Students ADD email VARCHAR(100); 

This adds a new column named email to the Students table.

Adding Multiple Columns

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

Deleting Columns in MySQL

Syntax

ALTER TABLE table_name DROP COLUMN column_name; 

Example

ALTER TABLE Students DROP COLUMN age; 

This removes the age column from the table.

Important Points

• Adding columns does not affect existing data

• Dropping columns permanently deletes data in that column

• Always verify before deleting columns

Example Scenario

Suppose you created a table:

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

Later, you want to add an email:

ALTER TABLE Students ADD email VARCHAR(100); 

If age is no longer needed:

ALTER TABLE Students DROP COLUMN age; 

Common Mistakes

• Dropping the wrong column

• Forgetting the data type while adding a column

• Not checking the existing structure

• Losing important data without backup

Key Points to Remember

• Use ALTER TABLE ADD to add columns

• Use ALTER TABLE DROP COLUMN to delete columns

• Changes are permanent

• Always review before modifying tables