MySQL ALTER Table

The ALTER TABLE statement in MySQL is used to modify an existing table, allowing you to add, modify, or drop columns, change column data types, and perform other changes to the table’s structure. Here’s the basic syntax for the ALTER TABLE statement:

ALTER TABLE table_name
  [ALTER COLUMN column_name {SET DEFAULT default_value | DROP DEFAULT}]
  [ADD [COLUMN] new_column_name datatype [column_constraint [,...]]]
  [MODIFY [COLUMN] column_name datatype [column_constraint [,...]]]
  [CHANGE [COLUMN] old_column_name new_column_name datatype [column_constraint [,...]]]
  [DROP [COLUMN] column_name]
  [RENAME TO new_table_name]
  [table_option [,...]];

Let’s break down the components:

• table_name: The name of the table you want to modify.
• ALTER COLUMN column_name {SET DEFAULT default_value | DROP DEFAULT}: This clause is used to alter column properties, such as setting a default value or dropping a default value.
• ADD [COLUMN] new_column_name datatype [column_constraint [,...]]: Use this clause to add a new column to the table.
• MODIFY [COLUMN] column_name datatype [column_constraint [,...]]: Use this clause to change the data type of an existing column.
• CHANGE [COLUMN] old_column_name new_column_name datatype [column_constraint [,...]]: Use this clause to change the name and data type of an existing column.
• DROP [COLUMN] column_name: Use this clause to remove an existing column from the table.
• RENAME TO new_table_name: This clause renames the table to a new name.
• table_option [,...]: You can specify various table-level options, such as character set and collation.

Here are some examples of how you can use the ALTER TABLE statement:

1. Add a new column to an existing table:

   ALTER TABLE mytable
   ADD COLUMN new_column INT;

2. Modify the data type of an existing column:

   ALTER TABLE mytable
   MODIFY COLUMN existing_column VARCHAR(100);

3. Change the name and data type of an existing column:

   ALTER TABLE mytable
   CHANGE COLUMN old_column new_column VARCHAR(100);

4. Remove a column from the table:

   ALTER TABLE mytable
   DROP COLUMN column_to_remove;

5. Change the table name:

   ALTER TABLE mytable
   RENAME TO new_table_name;

6. Set a default value for a column:

   ALTER TABLE mytable
   ALTER COLUMN column_name SET DEFAULT default_value;

Please note that you should have the necessary privileges to alter a table, and be cautious when making structural changes to a table as it can impact existing data and applications. Always take backups before making significant modifications.