ALTER TABLE

The ALTER TABLE statement is used to add, modify, or delete columns in an existing table. It can also be used to modify the characteristics of existing columns, such as data type, data length, or default values.

Syntax:

ALTER TABLE table_name
ADD column_name data_type;

ALTER TABLE table_name
MODIFY column_name data_type;

ALTER TABLE table_name
DROP column_name;

Example:

ALTER TABLE employees
ADD department varchar(50);

ALTER TABLE employees
MODIFY department varchar(100);

ALTER TABLE employees
DROP department;

Output:

The ALTER TABLE statement adds, modifies, or deletes the desired columns from the specified table.

Explanation:

• The ADD keyword is used to add a new column to the existing table. The column name and data type must be specified.
• The MODIFY keyword is used to modify the data type, data length, or default value of a specific column in the existing table.
• The DROP keyword is used to delete the specified column from the table.

Use:

The ALTER TABLE statement is used when you want to make structural changes to an existing table. It is essential when you realize that the existing table needs modification as it no longer serves the intended purpose.

Important Points:

• The ALTER TABLE statement does not change the data in the existing table;
• The ALTER statement add columns, modify columns, or drop quantity.
• The old data in the table will be retained after modification.

Summary:

The ALTER TABLE statement is used to add, modify, or delete columns from an existing table. It is used when structural changes need to be made to an existing table. The ALTER statement is essential for the database administrators and developers who want to modify the table from time to time.