MySQL ALTER: MySQL provides several powerful commands to modify database structures dynamically. Whether you need to add, rename, delete, or modify columns, MySQL offers multiple options to achieve this without affecting existing data.
In this guide, we will explore:
✅ ALTER TABLE – Modify table structure (add/remove/modify columns)
✅ DROP – Delete databases, tables, or columns
✅ RENAME – Rename tables and columns
✅ MODIFY & CHANGE – Alter column data types and constraints
✅ AFTER – Specify column position in a table
ALTER Command – Modify an Existing Table
MySQL ALTER: The ALTER command allows you to:
Add new columns
Modify column data types
Rename columns
Delete columns
Syntax for Adding a Column:
sql
ALTER TABLE table_name ADD COLUMN column_name data_type;
🔹 This command adds a new column column_name with the specified data_type.
Example: Adding a Credit Card Column
sql
ALTER TABLE members ADD COLUMN credit_card_number VARCHAR(25);
Adds credit_card_number to the members table.
DROP Command – Delete Databases, Tables, or Columns
MySQL ALTER: The DROP command is used to permanently remove a database, table, or column.
Syntax for Dropping a Column:
sql
ALTER TABLE table_name DROP COLUMN column_name;
🔹 Removes a specific column from a table.
Example: Dropping the Credit Card Column
sql
ALTER TABLE members DROP COLUMN credit_card_number;
Removes credit_card_number from the members table.
Syntax for Dropping a Table:
sql
DROP TABLE table_name;
🔹 Deletes an entire table from the database.
Example: Dropping an Archived Table
sql
DROP TABLE categories_archive;
Removes the categories_archive table permanently.
RENAME Command – Change Table or Column Names
MySQL ALTER: The RENAME command allows you to rename tables and columns without losing data.
Syntax for Renaming a Table:
sql
RENAME TABLE old_table_name TO new_table_name;
🔹 Changes the table name while preserving all data.
Example: Renaming movierentals Table
sql
RENAME TABLE movierentals TO movie_rentals;
Renames movierentals to movie_rentals.
CHANGE vs. MODIFY – Updating Column Properties
MySQL ALTER: Both CHANGE and MODIFY are used to alter column attributes, but they have key differences:
CHANGE COLUMN – Allows changing the column name, data type, and constraints.
MODIFY COLUMN – Allows changing only the data type and constraints.
Using CHANGE to Rename and Modify a Column
sql
MySQL ALTER: ALTER TABLE members CHANGE COLUMN full_names fullname CHAR(250) NOT NULL;
Renames full_names to fullname, changes its type to CHAR(250), and sets NOT NULL.
Using MODIFY to Change Data Type Without Renaming
sql
ALTER TABLE members MODIFY fullname CHAR(50) NOT NULL;
Changes fullname data type to CHAR(50), keeping the same name.
AFTER Keyword – Changing Column Position
MySQL ALTER: The AFTER keyword allows you to insert a new column at a specific position in a table.
Example: Adding date_of_registration After date_of_birth
sql
ALTER TABLE members ADD date_of_registration DATE NULL AFTER date_of_birth;
Inserts date_of_registration immediately after date_of_birth.
Summary of MySQL Commands
| Command | Purpose | Example |
| ALTER TABLE | Modify a table (add, delete, modify columns) | ALTER TABLE members ADD COLUMN age INT; |
| DROP COLUMN | Delete a column from a table | ALTER TABLE members DROP COLUMN age; |
| DROP TABLE | Delete an entire table | DROP TABLE members; |
| RENAME TABLE | Change table name | RENAME TABLE old_table TO new_table; |
| CHANGE COLUMN | Rename and modify a column | ALTER TABLE members CHANGE full_names fullname CHAR(250) NOT NULL; |
| MODIFY COLUMN | Modify column type without renaming | ALTER TABLE members MODIFY fullname CHAR(50) NOT NULL; |
| AFTER | Add a column in a specific position | ALTER TABLE members ADD date_of_registration DATE AFTER date_of_birth; |
By mastering these MySQL commands, you can efficiently modify database structures while ensuring data integrity.
