MySQL - Rename View

MySQL is a popular open-source relational database management system (RDBMS) that is usually used for developing scalable and high-performance databases. MySQL was developed by MySQL AB (currently owned by Oracle Corporation) in 1995.

MySQL is known for its robust, easy, and reliable features with quick processing speeds. MySQL is generally used by dynamic web applications and is commonly used by languages such as PHP, Python, and other server-side programming languages.

In this article, you will learn how to RENAME a view in MySQL along with some examples.

MySQL - Rename View

In relational database management systems (RDBMS) like MySQL, a VIEW is a virtual table interactive with data generated from one or more underlying tables through either a defined query. Unlike a regular table, the VIEW as a query doesn't store the data itself. Instead, it creates a result set when someone queries it.

MySQL provides a feature to RENAME an Existing VIEW. Renaming a VIEW in MySQL will not change the data in the underlying tables it will only change the name by which the view is referenced in SQL queries and the database schema.

Syntax:

RENAME TABLE old_view_name TO new_view_name;

Explanation: In the above syntax you can see that we are Renaming the VIEW from the old_view_name to the new_view_name. For a VIEW to be renamed the VIEW must be present.

Examples of MySQL RENAME VIEW

Let's take an example of the EMPLOYEE table having EMP_ID, NAME, AGE, and SALARY as columns.

CREATE TABLE EMPLOYEE (
    EMP_ID INT PRIMARY KEY,
    NAME VARCHAR(50),
    AGE INT,
    SALARY INT
);

Insert the data on it:

INSERT INTO EMPLOYEE (EMP_ID, NAME, AGE, SALARY) VALUES
(1, 'Sahil', 21, 15000),
(2, 'Alen', 22, 13000),
(3, 'John', 22, 14000),
(4, 'Alex', 20, 13000),
(5, 'Mathew', 22, 14000),
(6, 'Sia', 21, 15000),
(7, 'David', 22, 16000),
(8, 'Tim', 21, 14000),
(9, 'Leo', 20, 15000),
(10, 'Tom', 21, 16000);

EMPLOYEE Table:

Now Let's CREATE 2 VIEWS from the EMPLOYEE Table.

Query:

 CREATE VIEW view1 AS
  SELECT EMP_ID, SALARY
  FROM EMPLOYEE;

CREATE VIEW view2 AS
SELECT EMP_ID, AGE, SALARY
FROM EMPLOYEE
WHERE SALARY=14000;

Output: view1

view2:

Examples of MySQL Rename View

Example 1: Renaming view1 using RENAME keyword

Let RENAME view1 to employee_details1.

Syntax:

RENAME TABLE old_view_name TO new_view_name;

Query:

RENAME TABLE view1 TO employee_details1;

Output:

Explanation: In the above example we are renaming view1 to employee_details1. In the output we can see that there are 0 rows affected, this is because renaming a view will not change the underlying data in the table.

Example 2: Renaming view2 using RENAME keyword

Lets RENAME view2 to employee_details2

Syntax:

RENAME TABLE old_view_name TO new_view_name;

Query:

RENAME TABLE view1 TO employee_details1;

Output:

Explanation: In the above example we are renaming view2 to employee_details2. In the output we can see that there are 0 rows affected, this is because renaming a view will not change the underlying data in the table.

Example 3: Renaming a View using DROP VIEW and CREATE VIEW Sequence

Let's assume we have a view employees_above_20 that we want to rename to employees_above_age_20. Here's how you can achieve this:

• 1. Existing View: employees_above_20 Suppose employees_above_20 is defined as:

CREATE VIEW employees_above_20 AS
SELECT *
FROM EMPLOYEE
WHERE AGE > 20;

• Renaming employees_above_20 to employees_above_age_20:

-- Drop the existing view if it exists
DROP VIEW IF EXISTS employees_above_age_20;

-- Create a new view with the desired name and definition
CREATE VIEW employees_above_age_20 AS
SELECT *
FROM EMPLOYEE
WHERE AGE > 20;

Output:

+--------+--------+------+--------+
| EMP_ID | NAME   | AGE  | SALARY |
+--------+--------+------+--------+
|      1 | Sahil  |   21 |  15000 |
|      2 | Alen   |   22 |  13000 |
|      3 | John   |   22 |  14000 |
|      5 | Mathew |   22 |  14000 |
|      6 | Sia    |   21 |  15000 |
|      7 | David  |   22 |  16000 |
|      8 | Tim    |   21 |  14000 |
|     10 | Tom    |   21 |  16000 |
+--------+--------+------+--------+

Conclusion

In conclusion, MySQL allows you to RENAME an existing VIEW. It is a metric that is quite essential in cases where you need to make sure that each naming convention is consistent or helps improve the clarity of the database structure. Be reminded that the renaming of a VIEW does not change the underlying data but rather it does modify only the name by which the view is referenced. Moreover, you should be caring about any occurrence of the dependencies on the VIEW, for instance, stored routines or other views, thus, such dependencies will be updated after the renaming has been fulfilled.