?️ MySQL ALTER Statement

? Quick Overview

The ALTER statement in MySQL is used to modify the structure of an existing database object such as a table. It allows you to add, delete, or modify columns, indexes, and constraints. ALTER is a powerful command that gives you flexibility when managing database schema changes without losing any data.

? Key Concepts

• ALTER works on existing tables
• Schema changes do not remove data by default
• Supports columns, indexes, and constraints

? Syntax & Theory

The ALTER command changes table definitions after creation. It can add, remove, or update table components without recreating the table.

? Common ALTER Operations

• ADD COLUMN: Adds a new column
• DROP COLUMN: Removes a column
• MODIFY COLUMN: Changes column definition
• RENAME COLUMN: Renames a column
• ADD INDEX: Creates an index
• DROP INDEX: Deletes an index

? Code Examples

➕ Add a New Column

// Add a new column to an existing table
ALTER TABLE employees ADD COLUMN email VARCHAR(255);

➖ Drop a Column

// Remove an existing column from a table
ALTER TABLE employees DROP COLUMN email;

✏️ Modify a Column

// Change the data type or size of a column
ALTER TABLE employees MODIFY COLUMN email VARCHAR(100);

? Rename a Column

// Rename an existing column (MySQL 8+)
ALTER TABLE employees RENAME COLUMN old_name TO new_name;

? Add an Index

// Create an index to improve search performance
ALTER TABLE employees ADD INDEX idx_email (email);

?️ Drop an Index

// Remove an index from the table
ALTER TABLE employees DROP INDEX idx_email;

? Use Cases

• Adding new features to applications
• Optimizing database performance
• Updating schema for evolving requirements