SQL - ALTER Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints in an existing table.

ALTER TABLE - ADD Column: To add columns in a table.

ALTER TABLE <Table Name>
ADD (Column_Name1 Data_Type (Column_Width)[Constraints],
[Column_Name2 Data_Type (Column_Width)[Constraints],
--------------------------------------------------,
]);

ALTER TABLE - DROP COLUMN: To delete a column in a table.

ALTER TABLE <Table Name>
DROP COLUMN <Column Name>;

ALTER TABLE - ALTER/MODIFY COLUMN: To change the data type of a column in a table.

ALTER TABLE <Table Name>
MODIFY COLUMN (Column_Name Data_Type (Column_Width)[Constraints];

For example:
For adding a new column Manager Id in the DEPARTMENT table:
mysql> alter table department 
    -> add column (manager_id varchar(3));
Query OK, 0 rows affected (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
To see the changes in the structure:
mysql> desc department;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| dept_id    | varchar(3)  | NO   | PRI | NULL    |       |
| dept_name  | varchar(30) | NO   |     | NULL    |       |
| location   | varchar(30) | YES  |     | NULL    |       |
| manager_id | varchar(3)  | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>
To delete the newly added attribute manager_id:
mysql> alter table department drop column manager_id;
Query OK, 0 rows affected (0.84 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
To modify the datatype of an existing attribute in the DEPARTMENT table:
mysql> alter table department modify location varchar(20);
Query OK, 0 rows affected (0.86 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
To see the changes in the structure:
mysql> desc department;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | varchar(3)  | NO   | PRI | NULL    |       |
| dept_name | varchar(30) | NO   |     | NULL    |       |
| location  | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>