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, the maximum size of the column’s data, and its constraints.
FIRST | AFTER column_name: It is an optional parameter that 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 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;