ALTER TABLE in MySQL

ALTER TABLE
In MySQL, the ALTER TABLE statement is used to rename a table or a column in a table or to add, modify, drop or delete a column in a table ALTER TABLE statement is used.

Syntax:

ALTER TABLE table_name action;

ALTER TABLE ADD column:

Syntax 1: To add a column in the existing table.

ALTER TABLE table_name 
ADD column_name column_definition
[ FIRST | AFTER column_name ];

Parameters:
table_name: It is used to specify the name of the table.
Column_name: It is used to specify the column name.
column_definition: It is used to specify the data type, maximum size of the column’s data and its constraints.
FIRST | AFTER column_name: It is an optional parameter which is used to specify where in the table to create the column. By default it is created at the end of the table.

Example 1: Adding a new column to an already existing table.

ALTER TABLE items  
ADD price INT(50) NOT NULL;

Explanation:
The column named “price” is added to the “items” table. The data type of the column is INT with a maximum length of 50 and only accepts NOT NULL values. The recently added column can be checked using the below query.
SELECT* FROM items;

Syntax 2: To add multiple columns in the existing table.

ALTER TABLE table_name  
ADD column_name column_definition
[ FIRST | AFTER column_name ],  
ADD column_name column_definition
[ FIRST | AFTER column_name ],
….   ; 

Example 2: Adding multiple columns to an already existing table.

ALTER TABLE items  
ADD department VARCHAR(100) NOT NULL
FIRST price,
ADD description VARCHAR(100)
AFTER price;

Explanation:
Two new columns named “department” and “description” are added to the “items” table before and after the “price” column respectively. The data type of both the columns is VARCHAR with a maximum length of 100. The “department” column only accepts NOT NULL values. The recently added columns can be checked using the below query.
SELECT* FROM items;

ALTER TABLE MODIFY column

Syntax: To modify a single column of a table.

ALTER TABLE table_name 
MODIFY column_name column_definition
[ FIRST | AFTER column_name ];

Example:

ALTER TABLE items  
MODIFY price INT(100) NULL;

Explanation:
The column named “price” is added to the “items” table. The data type of the column is modified to INT with a maximum length of 100 and it now accepts NULL values. The modification can be verified using the below query.
DESCRIBE items;

ALTER TABLE DROP COLUMN

Syntax:

ALTER TABLE table_name  
DROP COLUMN column_name;  

Example:

ALTER TABLE items  
DROP COLUMN description;

Explanation:
The column named “description” is removed from the “items” table. The elimination can be verified using the below query.
DESCRIBE items;

ALTER TABLE RENAME COLUMN

Syntax:

ALTER TABLE table_name  
CHANGE COLUMN old_name new_name   
column_definition  
[ FIRST | AFTER column_name ];  

Parameters:
old_name: It is used to specify the existing name of the column.
new_name: It is used to specify the desired new name of the column.

Example:

ALTER TABLE items  
CHANGE COLUMN price to item_price
INT(50) NOT NULL;

Explanation:
The column named “price” is renamed to “item_price” in the “items” table. The modification can be verified using the below query.
DESCRIBE items;

ALTER TABLE RENAME TABLE

Syntax:

ALTER TABLE table_name  
RENAME TO new_name;   

Parameters:
table_name: It is used to specify the existing name of the table.
new_name: It is used to specify the desired new name of the table.

Example:

ALTER TABLE items  
RENAME TO item_list;

Explanation:
The table named “items” is renamed to “item_list”. The modification can be verified using the below query.
SHOW tables;

Please Share