In MySQL, The ALTER TABLE statement is used to modify an existing table.
It allows us to "add", "modify", or "drop" columns, change data types, add or remove indexes, and make other structural changes to a table.
We can add a new column to an existing table using the `ADD` keyword in Table.
ALTER TABLE tableName ADD COLUMN new_column_name data_type;
tableName: The name of the table you want to alter.
new_column_name: The name of the new column.
data_type: The data type of the new column.
ADD COLUMN: Statement will new column in Table.
ALTER TABLE products ADD COLUMN product_name VARCHAR(50);
In the above query, we are adding a new column named `product_name` with the size of `50` in the `products` table.
We can modify the data type or other properties of an existing column using the `MODIFY` keyword in Table.
ALTER TABLE tableName MODIFY COLUMN column_name new_data_type;
ALTER TABLE products MODIFY COLUMN product_name VARCHAR(200);
In the above query, we are modifying a `product_name` column size to `200` in the `products` table.
We can rename an existing column using the `CHANGE` keyword in Table.
ALTER TABLE tableName CHANGE old_column_name new_column_name data_type;
CHANGE: Statement will change column name and its type in the Table.
old_column_name: The name of the column needs to be updated in the Table.
new_column_name: New name of the existing column in the Table.
data_type: Data Type of the Specific Column in the Table
ALTER TABLE products CHANGE price product_price VARCHAR(50);
In the above query, we are renaming a `price` column name to `product_price` in the `products` table.
We can remove a column from a table using the `DROP` keyword in Table.
ALTER TABLE tableName DROP COLUMN columnName;
DROP COLUMN: Statement will drop column from the Table.
ALTER TABLE products DROP COLUMN category;
In the above query, we are dropping a `category` column from the `products` table.
It essentially helps us to add or drop columns, change data types, rename columns or the table itself, and add or modify constraints as well.