MySQL RENAME TABLE Statement

MySQL RENAME TABLE Statement

The MySQL RENAME TABLE statement is used to rename an existing table in the database. This statement is essential for changing the name of a table without altering its structure or data.

---

Syntax

RENAME TABLE old_table_name TO new_table_name;

The RENAME TABLE statement has the following components:

• old_table_name: The current name of the table to be renamed.
• new_table_name: The new name for the table.

---

Example MySQL RENAME TABLE Statement

Let's look at an example of the MySQL RENAME TABLE statement and how to use it:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

This query creates a table named employees with columns for id, first_name, last_name, and email.

Step 3: Renaming the Table

Rename the table:

RENAME TABLE employees TO staff;

This query renames the table employees to staff.

Step 4: Verifying the Renamed Table

To verify that the table has been renamed, you can list all tables in the current database:

SHOW TABLES;

This query lists all tables in the current database. The result should include the table staff instead of employees.

---

Conclusion

The MySQL RENAME TABLE statement is a simple but powerful tool for changing the names of existing tables. Understanding how to use the RENAME TABLE statement is essential for effective database management in MySQL.