# 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;


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.

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
[ FIRST | AFTER column_name ],
[ FIRST | AFTER column_name ],
….   ;


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;