The ALTER statement in MySQL is used to modify an existing database, table, or index structure. It allows you to make changes such as adding or dropping columns, modifying data types, or renaming elements. Here are some common use cases of the ALTER statement:
Altering a Table: | ||
| S.No. | Title | MYSQL |
| 1 | Adding a Column | ALTER TABLE table_name ADD COLUMN new_column_name datatype; |
| 2 | Dropping a Column | ALTER TABLE table_name DROP COLUMN column_name; |
| 3 | Modifying Column Data Type: | ALTER TABLE table_name MODIFY COLUMN column_name new_datatype; |
| 4 | Renaming a Column: | ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name datatype; |
Modifying Table Properties: | ||
| 1 | Changing Table Engine | ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name datatype; |
| 2 | Adding an Index: | ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...); |
| 3 | Dropping an Index | ALTER TABLE table_name DROP INDEX index_name; |
Renaming a Table | ||
| 1 | Renaming a Table | ALTER TABLE old_table_name RENAME TO new_table_name; |
Modifying Database | ||
| 1 | Modifying Database | ALTER DATABASE database_name COLLATE = new_collation; |
- Ensure that you have the necessary privileges to perform ALTER operations.
- Always make a backup before making significant changes to your database structure.
Keep in mind that the syntax might vary slightly depending on the MySQL version you are using. Adjust the statements accordingly based on your specific requirements and MySQL version.