How to rename a table in MySQL?

To rename a table, the alter and rename commands can be used. These are demonstrated with the help of the following steps −

First, a table is created with the help of the create command. This is given as follows −

mysql> CREATE table Employee
   -> (
   -> EmpId int,
   -> EmpName varchar(200)
   -> );
Query OK, 0 rows affected (0.49 sec)

The syntax to change the table name is given as follows −

> alter table yourtableName rename toYourNewtableName;

The above syntax is used to change the table name using the following query −

mysql> alter table Employee rename to EmployeeTable;
Query OK, 0 rows affected (0.56 sec)

Now that the table name is changed, the select statement is used to see whether the rename operation was successful or not. The query for that is given as follows −

mysql> SELECT * from Employee;
ERROR 1146 (42S02): Table 'business.employee' doesn't exist

The above query gives an error as there is no longer a table with the name Employee. The name has been changed to EmployeeTable.

The above query is again executed with the table name ‘EmployeeTable’. This is shown below −

mysql> SELECT * from EmployeeTable;
Empty set (0.00 sec)

Now, there is no error for the above query as the table name Employee has been changed to EmployeeTable.