MySQL – Alter Table
Using the ALTER TABLE statement in MySQL, you can add, modify, or delete columns from a table. It is also possible to rename a table with the ALTER TABLE statement in MySQL.
Add column in table
Syntax
Adding a column to a table in MySQL (using ALTER TABLE) is as follows:
ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ];
table_name – To modify, enter the table name.
new_column_name – To add a new column to the table, give it a name.
column_definition – Defining the datatype and definition of the column (NULL or NOT NULL, etc).
FIRST | AFTER column_name – This is optional. Specifies where the column should be created in the table. By default, the new column will be added at the end of the table if this parameter is not specified.
Example
The following query can be used to add a column named “address” to the “employees” table:
ALTER TABLE employees ADD COLUMN address VARCHAR(100);
Add multiple columns in table
Syntax
Adding multiple columns to a MySQL table (with the ALTER TABLE statement) requires the following syntax:
ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ], ADD new_column_name column_definition [ FIRST | AFTER column_name ], ... ;
table_name – To modify, enter the table name.
new_column_name – To add a new column to the table, give it a name.
column_definition – Defining the datatype and definition of the column (NULL or NOT NULL, etc).
FIRST | AFTER column_name – This is optional. Specifies where the column should be created in the table. By default, the new column will be added at the end of the table if this parameter is not specified.
Example
Here is an example of how to add multiple columns to a MySQL table using the ALTER TABLE command
. ALTER TABLE employees ADD city varchar(40) NOT NULL AFTER last_name, ADD country varchar(35) NULL AFTER city;
In the ALTER TABLE example, the city and country columns are added to the employee table.
The city field will be created as a varchar(40) NOT NULL column and will appear after the last_name column in the table. The country column will be created as a varchar(35) NULL column and will appear after the city column in the table.
Modify column in table
Syntax
Modifying a column in a MySQL table (using the ALTER TABLE statement) is as follows:
ALTER TABLE table_name MODIFY column_name column_definition [ FIRST | AFTER column_name ];
table_name – To modify, enter the table name.
new_column_name – To add a new column to the table, give it a name.
column_definition – Defining the datatype and definition of the column (NULL or NOT NULL, etc).
FIRST | AFTER column_name – This is optional. Specifies where the column should be created in the table. By default, the new column will be added at the end of the table if this parameter is not specified.
Example
Here’s an example of how to use the ALTER TABLE statement to modify a column in a MySQL table.
ALTER TABLE employees MODIFY COLUMN salary INT;
This ALTER TABLE example will modify the column called last_name to be a data type of varchar(50) and force the column to allow NULL values.
Modify Multiple columns in table
Using the ALTER TABLE statement, you can modify multiple columns in a table in MySQL:
Syntax
ALTER TABLE table_name MODIFY column_name column_definition [ FIRST | AFTER column_name ], MODIFY column_name column_definition [ FIRST | AFTER column_name ], ... ;
table_name – To modify, enter the table name.
new_column_name – To add a new column to the table, give it a name.
column_definition – Defining the datatype and definition of the column (NULL or NOT NULL, etc).
FIRST | AFTER column_name – This is optional. Specifies where the column should be created in the table. By default, the new column will be added at the end of the table if this parameter is not specified.
Example
Using the ALTER TABLE statement, we can modify multiple columns in a MySQL table.
ALTER TABLE employees MODIFY last_name varchar(55) NULL AFTER first_name, MODIFY first_name varchar(30) NOT NULL;
Drop column in table:
Using the ALTER TABLE statement in MySQL, you can drop a column in a table as follows:
Syntax
ALTER TABLE table_name DROP COLUMN column_name;
table_name – Modify the name of the table.
column_name – Deletes the column from the table by its name.
Example
Here is an example of using the ALTER TABLE statement to remove a column from a MySQL table.
ALTER TABLE employees DROP COLUMN address;
Rename column in table
Syntax
In MySQL, a column can be renamed by using the ALTER TABLE statement:
ALTER TABLE table_name CHANGE COLUMN old_name new_name column_definition [ FIRST | AFTER column_name ]
Example
ALTER TABLE employees CHANGE COLUMN city city_name varchar(20) NOT NULL;
Rename table
Syntax
In MySQL, you can rename a table by using the following syntax:
ALTER TABLE table_name RENAME TO new_table_name;
table_name – This is the table that needs to be renamed.
new_table_name – Use this new table name.
Example
ALTER TABLE employees RENAME TO staff;