How do you use the ALTER TABLE statement to modify an existing table’s structure? — MySQL Interview Question #34

ALTER TABLE statement is used to modify the structure of an existing table in MySQL. We can use it to add, modify or delete columns, add or remove indexes, rename a table, and many more.

The syntax of the ALTER TABLE statement is as follows:

ALTER TABLE table_name action;

Here, ‘table_name‘ is the name of the table that we want to modify and ‘action‘ is the modification we want to make to the table.

Some of the most common modifications that can be made using ALTER TABLE are:

1. Adding a Column: We can use the ‘ADD‘ keyword to add a new column to the table.

ALTER TABLE table_name ADD column_name column_definition;

Here, ‘column_name‘ is the name of the column to be added and ‘column_definition‘ specifies the data type and other properties of the column.

Example:

Suppose we have a table named ‘students‘ with columns ‘id‘, ‘name‘, and ‘age‘. Now, we want to add a new column named ‘email‘ to the table. The ALTER TABLE statement for this would be:

ALTER TABLE students ADD email VARCHAR(100);

2. Modifying a Column: We can use the ‘MODIFY‘ keyword to modify the definition of an existing column.

ALTER TABLE table_name MODIFY column_name new_column_definition;

Here, ‘column_name‘ is the name of the column to be modified and ‘new_column_definition‘ specifies the new data type and other properties of the column.

Example:

Suppose we have a table named ‘students‘ with a column ‘age‘ of data type ‘INT‘. Now, we want to modify the data type of the column to ‘SMALLINT‘. The ALTER TABLE statement for this would be:

ALTER TABLE students MODIFY age SMALLINT;

3. Removing a Column: We can use the ‘DROP‘ keyword to remove an existing column from the table.

ALTER TABLE table_name DROP column_name;

Here, ‘column_name‘ is the name of the column to be removed.

Example:

Suppose we have a table named ‘students‘ with columns ‘id‘, ‘name‘, ‘age‘, and ‘email‘. Now, we want to remove the column named ‘email‘ from the table. The ALTER TABLE statement for this would be:

ALTER TABLE students DROP email;

4. Renaming a Table: We can use the ‘RENAME‘ keyword to rename an existing table.

ALTER TABLE old_table_name RENAME TO new_table_name;

Here, ‘old_table_name‘ is the current name of the table and ‘new_table_name‘ is the new name.

Example:

Suppose we have a table named ‘students‘. Now, we want to rename the table to ‘classroom‘. The ALTER TABLE statement for this would be:

ALTER TABLE students RENAME TO classroom;

These are some of the most common modifications that can be made using ALTER TABLE statement. However, there are many other modifications that can be made as well, such as adding or removing indexes, setting default values, and more.