Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in /home/lsp4you/public_html/connect.php on line 2 LSP4YOU - Learner's Support Publications
DBMS BCA Unit-3 Introduction - SQL SQL - Data Types DDL - Commands SQL - CREATE DATABASE SQL - SELECT DATABASE SQL - DROP DATABASE SQL - CREATE TABLE SQL - DESCRIBE Statement SQL - DROP TABLE SQL - ALTER Statement SQL - INSERT INTO Statement SQL - DELETE Statement SQL - UPDATE Statement SQL - FOREIGN KEY SQL - INSERT INTO Example


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>