In MySQL, the ALTER TABLE statement is used to modify the structure of an existing table. It allows you to add or remove columns, change the data type of a column, add or remove indexes, and perform other changes to the table's schema.
Here are some examples of how you might use the ALTER TABLE statement:
Adding a new column to a table:
ALTER TABLE my_table ADD COLUMN new_column INT;
This statement adds a new column called new_column of type integer (INT) to the my_table table.
Removing a column from a table:
ALTER TABLE my_table DROP COLUMN old_column;
This statement removes a column called old_column from the my_table table.
Changing the data type of a column:
ALTER TABLE my_table MODIFY COLUMN existing_column VARCHAR(100);
Renaming a column:
Suppose we have a table called customers with a column named old_name that we want to rename to new_name. Here's the SQL command we would use:
ALTER TABLE customers CHANGE COLUMN old_name new_name VARCHAR(50);
This command will rename the old_name column to new_name and change its data type to VARCHAR(50).
Removing a column:
Suppose we want to remove a column called column_name from a table called my_table. Here's the SQL command we would use:
ALTER TABLE my_table DROP COLUMN column_name;
This command will remove the column_name column from the my_table table.
Adding a column in between or at first:
Suppose we want to add a new column called new_column after an existing column called existing_column in a table called my_table. Here's the SQL command we would use:
ALTER TABLE my_table ADD COLUMN new_column VARCHAR(50) AFTER existing_column;
This command will add a new column called new_column with a data type of VARCHAR(50) after the existing_column column.
To add the new column at the beginning of the table, we would use the following SQL command:
ALTER TABLE my_table ADD COLUMN new_column VARCHAR(50) FIRST;
This command will add the new column called new_column with a data type of VARCHAR(50) at the beginning of the my_table table.
