MySQL - Alter Table

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.