Alter Command in MySQL Workbench

• The `ALTER` command in MySQL is used to modify the structure of an existing table, such as adding, deleting, or modifying columns, changing data types, setting default values, and more.

• Let me provide you with some detailed explanations and examples for different use cases of the `ALTER` command in MySQL Workbench:

Adding a Column:

• You can use the `ADD` clause to add a new column to an existing table. Here's an example:

    ALTER TABLE employees

    ADD COLUMN email VARCHAR(50);

Modifying Column Data Type:

• To change the data type of a column, use the `MODIFY` clause:

    ALTER TABLE employees

    MODIFY COLUMN salary DECIMAL(10, 2);

Renaming a Column:

• You can use the `CHANGE` clause to rename a column:

    ALTER TABLE employees

    CHANGE COLUMN old_column_name new_column_name INT;

Renaming a Table:

• You can use the `RENAME` clause to rename a table:

    ALTER TABLE table_name

    RENAME new_table_name;

Dropping a Column:

• To remove a column from a table, use the `DROP` clause:

    ALTER TABLE employees

    DROP COLUMN column_to_remove;

Adding a Primary Key:

• To add a primary key to an existing table, use the `ADD PRIMARY KEY` clause:

    ALTER TABLE employees

    ADD PRIMARY KEY (employee_id);

Adding an Index:

• To add an index to a column, use the `ADD INDEX` clause:

    ALTER TABLE employees

    ADD INDEX idx_last_name (last_name);

Modifying Default Values:

• You can change the default value of a column using the `ALTER` command:

    ALTER TABLE employees

    ALTER COLUMN hire_date SET DEFAULT '2023-01-01';

Dropping Constraints:

• To remove a constraint from a table, use the `DROP` clause:

    ALTER TABLE employees

    DROP PRIMARY KEY;

Changing Table Engine:

• You can change the storage engine of a table using the `ENGINE` clause:

    ALTER TABLE employees

    ENGINE = InnoDB;

• Remember to be cautious when using the `ALTER` command, especially on production databases, as it can modify the structure and integrity of your data. Always back up your data before making significant changes to your table structure.