📌 Definition

The ALTER statement is used to modify the structure of an existing table in a database.

👉 It allows you to:

• Add columns
• Modify columns
• Delete columns
• Rename columns
• Rename tables

🧠 Basic Syntax

ALTER TABLE table_name
operation;

🔧 1. ADD COLUMN

📌 Definition

Used to add a new column to an existing table.

🧠 Syntax

ALTER TABLE table_name
ADD column_name datatype;

✅ Example

ALTER TABLE Students
ADD Email VARCHAR(100);

💡 Result

A new column Email is added to the Students table.

🔧 2. ADD MULTIPLE COLUMNS

🧠 Syntax

ALTER TABLE table_name
ADD column1 datatype,
ADD column2 datatype;

✅ Example

ALTER TABLE Students
ADD City VARCHAR(50),
ADD Phone VARCHAR(15);

🔧 3. MODIFY COLUMN

📌 Definition

Used to change the datatype or size of a column.

🧠 Syntax

ALTER TABLE table_name
MODIFY column_name new_datatype;

✅ Example

ALTER TABLE Students
MODIFY Name VARCHAR(100);

💡 Explanation

Changes the size of the Name column.

🔧 4. CHANGE COLUMN (Rename + Modify)

📌 Definition

Used to rename a column and optionally change its datatype.

🧠 Syntax

ALTER TABLE table_name
CHANGE old_column_name new_column_name datatype;

✅ Example

ALTER TABLE Students
CHANGE Name FullName VARCHAR(100);

🔧 5. RENAME COLUMN (MySQL 8+)

🧠 Syntax

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

✅ Example

ALTER TABLE Students
RENAME COLUMN Age TO StudentAge;

🔧 6. DROP COLUMN

📌 Definition

Used to delete a column from a table.

🧠 Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

✅ Example

ALTER TABLE Students
DROP COLUMN Phone;

⚠️ Warning

• All data in that column will be permanently deleted.

🔧 7. RENAME TABLE

📌 Definition

Used to rename an existing table.

🧠 Syntax

ALTER TABLE old_table_name
RENAME TO new_table_name;

✅ Example

ALTER TABLE Students
RENAME TO StudentDetails;

🔧 8. ADD CONSTRAINT

📌 Definition

Used to add constraints like PRIMARY KEY, UNIQUE, etc.

🧠 Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_name);

✅ Example

ALTER TABLE Students
ADD CONSTRAINT pk_id PRIMARY KEY (ID);

🔧 9. DROP CONSTRAINT

🧠 Syntax (MySQL)

ALTER TABLE table_name
DROP PRIMARY KEY;

✅ Example

ALTER TABLE Students
DROP PRIMARY KEY;

🔧 10. SET DEFAULT VALUE

🧠 Syntax

ALTER TABLE table_name
ALTER column_name SET DEFAULT value;

✅ Example

ALTER TABLE Students
ALTER Age SET DEFAULT 18;

📊 Summary Table

⚠️ Important Notes

• Always backup data before using ALTER.
• Some operations may fail if data conflicts exist.
• Large tables may take time to alter.

🎯 Key Insight

The ALTER statement is essential for:

• Updating database design
• Fixing structure issues
• Scaling applications over time

👉 It allows databases to evolve without deleting existing data.