Summary: in this tutorial, you will learn how to add a new column, drop a column, modify an existing column, rename an existing column and rename a table using MySQL ALTER TABLE statement.
In the real-world scenario, sometimes we need to change the name of the table and column to give a more meaningful name to match the name with the data it contains.
MySQL ALTER TABLE statement allows us to change the name of an existing table and the name of an existing column. It also provides the capability to add a new column and delete an existing column.
The ALTER TABLE statement is always used with some other commands like ADD, DROP and modify according to the need.
Following are the use of an ALTER TABLE statement in MySQL.
1) Add a column to a table
To add a new column to a table, you use the following syntax.
Syntax:
ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ];
In this syntax,
First, you specify the name of the in which you want to add the new column after the ALTER TABLE statement.
Second, Specify the name of the new column and its definition like datatype, maximum size, column constraint, etc. after the ADD keyword.
Finally, specify the position of the new column. You can add the new column as the first column using the FIRST keyword or after an existing column by specifying the column name after the AFTER keyword.
Example:
In this example, we add a new column “student_age” in the existing table “students” table.
Use the following query to do this:
ALTER TABLE students ADD student_age int NOT NULL;
Below is the output of the above query:
mysql> ALTER TABLE students
-> ADD student_age int NOT NULL;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
You can check now the recently added column using the SELECT statement.
mysql> SELECT * FROM Students;
Output:
mysql> SELECT * FROM Students; +---------+------------+-----------+-------------+ | roll_no | first_name | last_name | student_age | +---------+------------+-----------+-------------+ | 1 | Ram | Meena | 0 | | 2 | Sagar | Sharma | 0 | | 3 | Priyankit | Jha | 0 | +---------+------------+-----------+-------------+ 3 rows in set (0.00 sec)
2) Add multiple columns to a table
To add multiple columns in a table, you use the following syntax.
Syntax:
ALTER TABLE table_name ADD new_column_name new_ncolumn_definition [ FIRST | AFTER column_name ], ADD new_column_name new_column_definition [ FIRST | AFTER column_name ], ... ;
Example:
In this example, we add two new columns student_address and student_email to the existing Students table. student_email will be added after the last_name column and student_address will be added at the last i.e. after student age.
ALTER TABLE Students ADD student_email varchar(100) NOT NULL AFTER last_name, ADD student_address varchar(200) NOT NULL;
Following is the output:
mysql> ALTER TABLE Students -> ADD student_email varchar(100) NOT NULL -> AFTER last_name, -> ADD student_address varchar(200) NOT NULL; Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0
Verify the newly added columns in the Students table.
mysql> SELECT * FROM Students;
Output:
mysql> SELECT * FROM Students; +---------+------------+-----------+---------------+-------------+-----------------+ | roll_no | first_name | last_name | student_email | student_age | student_address | +---------+------------+-----------+---------------+-------------+-----------------+ | 1 | Ram | Meena | | 0 | | | 2 | Sagar | Sharma | | 0 | | | 3 | Priyankit | Jha | | 0 | | +---------+------------+-----------+---------------+-------------+-----------------+ 3 rows in set (0.00 sec)
3) Modify a column
The modify command is used along with the ALTER TABLE is to change the column definition.
Syntax:
ALTER TABLE table_name MODIFY column_name column_definition [ FIRST | AFTER column_name ];
Example:
In the example, we will modify the column definition of student_address to data type varchar(255) and force the column to accept the NULL value.
ALTER TABLE Students MODIFY student_address varchar(255) NULL;
Here is the output:
mysql> ALTER TABLE Students -> MODIFY student_address varchar(255) NULL; Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0
We can verify the structure of the table definition using DESCRIBE table name command.
mysql> DESCRIBE Students;
Output:
+-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | roll_no | int | NO | PRI | NULL | auto_increment | | first_name | varchar(50) | NO | | NULL | | | last_name | varchar(50) | YES | | NULL | | | student_email | varchar(100) | NO | | NULL | | | student_age | int | NO | | NULL | | | student_address | varchar(255) | YES | | NULL | | +-----------------+--------------+------+-----+---------+----------------+ 6 rows in set (0.08 sec)
4) Modify multiple columns
You can also modify multiple columns using MODIFY command along with the ALTER TABLE command.
Syntax:
ALTER TABLE table_name MODIFY column_name column_definition [ FIRST | AFTER column_name], MODIFY column_name column_definition [ FIRST | AFTER column_name], ... ;
Example:
In this example, we will change the maximum length of the student_address column back to varchar(200) and length of the student_email to varchar(50).
ALTER TABLE Students MODIFY student_address varchar(200) NULL, MODIFY student_email varchar(50);
Finally, verify the table structure as below:
mysql> SELECT * FROM Students;
Output:
+-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | roll_no | int | NO | PRI | NULL | auto_increment | | first_name | varchar(50) | NO | | NULL | | | last_name | varchar(50) | YES | | NULL | | | student_email | varchar(50) | YES | | NULL | | | student_age | int | NO | | NULL | | | student_address | varchar(200) | YES | | NULL | | +-----------------+--------------+------+-----+---------+----------------+ 6 rows in set (0.01 sec)
5) Rename a column in a table
To change the name of the column, you use the CHANGE keyword along with the ALTER TABLE command.
Syntax:
ALTER TABLE table_name CHANGE COLUMN old_name new_name column_definition [ FIRST | AFTER column_name ];
Example:
In this example, we will change the name of the column student_address to stu_address.
ALTER TABLE Students CHANGE COLUMN student_address stu_address varchar(200);
Now verify the structure of the table as below.
mysql> DESCRIBE Students;
Output:
+---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | roll_no | int | NO | PRI | NULL | auto_increment | | first_name | varchar(50) | NO | | NULL | | | last_name | varchar(50) | YES | | NULL | | | student_email | varchar(50) | YES | | NULL | | | student_age | int | NO | | NULL | | | stu_address | varchar(200) | YES | | NULL | | +---------------+--------------+------+-----+---------+----------------+ 6 rows in set (0.01 sec)
6) DROP column in table
To drop a column from the table, you use the DROP keyword along with the ALTER TABLE statement.
Syntax:
ALTER TABLE table_name DROP COLUMN column_name;
Example:
In this example, we will drop the column stu_address from the Students table.
ALTER TABLE Students DROP stu_address;
Now, verify the structure of the table as below.
mysql> DESCRIBE Students;
Output:
+---------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+----------------+ | roll_no | int | NO | PRI | NULL | auto_increment | | first_name | varchar(50) | NO | | NULL | | | last_name | varchar(50) | YES | | NULL | | | student_email | varchar(50) | YES | | NULL | | | student_age | int | NO | | NULL | | +---------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec)
7) RENAME table
To change the name of an existing table, you use the RENAME table keyword along with the ALTER TABLE statement.
Syntax:
ALTER TABLE table_name RENAME TO new_table_name;
Example:
In this example, we will change the name of table Students to Student_details.
ALTER TABLE Students RENAME TO Student_detials;
You can check the new table using the below command.
SHOW tables;
Output:
+-------------------------+ | Tables_in_mysqltutorial | +-------------------------+ | student_details | +-------------------------+ 1 row in set (0.01 sec)