- MySQL Basics
- MySQL - Home
- MySQL - Introduction
- MySQL - Features
- MySQL - Versions
- MySQL - Variables
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Node.js Syntax
- MySQL - Java Syntax
- MySQL - Python Syntax
- MySQL - Connection
- MySQL - Workbench
- MySQL Databases
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Show Database
- MySQL - Copy Database
- MySQL - Database Export
- MySQL - Database Import
- MySQL - Database Info
- MySQL Users
- MySQL - Create Users
- MySQL - Drop Users
- MySQL - Show Users
- MySQL - Change Password
- MySQL - Grant Privileges
- MySQL - Show Privileges
- MySQL - Revoke Privileges
- MySQL - Lock User Account
- MySQL - Unlock User Account
- MySQL Tables
- MySQL - Create Tables
- MySQL - Show Tables
- MySQL - Alter Tables
- MySQL - Rename Tables
- MySQL - Clone Tables
- MySQL - Truncate Tables
- MySQL - Temporary Tables
- MySQL - Repair Tables
- MySQL - Describe Tables
- MySQL - Add/Delete Columns
- MySQL - Show Columns
- MySQL - Rename Columns
- MySQL - Table Locking
- MySQL - Drop Tables
- MySQL - Derived Tables
- MySQL Queries
- MySQL - Queries
- MySQL - Constraints
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Replace Query
- MySQL - Insert Ignore
- MySQL - Insert on Duplicate Key Update
- MySQL - Insert Into Select
- MySQL Indexes
- MySQL - Indexes
- MySQL - Create Index
- MySQL - Drop Index
- MySQL - Show Indexes
- MySQL - Unique Index
- MySQL - Clustered Index
- MySQL - Non-Clustered Index
- MySQL Operators and Clauses
- MySQL - Where Clause
- MySQL - Limit Clause
- MySQL - Distinct Clause
- MySQL - Order By Clause
- MySQL - Group By Clause
- MySQL - Having Clause
- MySQL - AND Operator
- MySQL - OR Operator
- MySQL - Like Operator
- MySQL - IN Operator
- MySQL - ANY Operator
- MySQL - EXISTS Operator
- MySQL - NOT Operator
- MySQL - NOT EQUAL Operator
- MySQL - IS NULL Operator
- MySQL - IS NOT NULL Operator
- MySQL - Between Operator
- MySQL - UNION Operator
- MySQL - UNION vs UNION ALL
- MySQL - MINUS Operator
- MySQL - INTERSECT Operator
- MySQL - INTERVAL Operator
- MySQL Joins
- MySQL - Using Joins
- MySQL - Inner Join
- MySQL - Left Join
- MySQL - Right Join
- MySQL - Cross Join
- MySQL - Full Join
- MySQL - Self Join
- MySQL - Delete Join
- MySQL - Update Join
- MySQL - Union vs Join
- MySQL Keys
- MySQL - Unique Key
- MySQL - Primary Key
- MySQL - Foreign Key
- MySQL - Composite Key
- MySQL - Alternate Key
- MySQL Triggers
- MySQL - Triggers
- MySQL - Create Trigger
- MySQL - Show Trigger
- MySQL - Drop Trigger
- MySQL - Before Insert Trigger
- MySQL - After Insert Trigger
- MySQL - Before Update Trigger
- MySQL - After Update Trigger
- MySQL - Before Delete Trigger
- MySQL - After Delete Trigger
- MySQL Data Types
- MySQL - Data Types
- MySQL - VARCHAR
- MySQL - BOOLEAN
- MySQL - ENUM
- MySQL - DECIMAL
- MySQL - INT
- MySQL - FLOAT
- MySQL - BIT
- MySQL - TINYINT
- MySQL - BLOB
- MySQL - SET
- MySQL Regular Expressions
- MySQL - Regular Expressions
- MySQL - RLIKE Operator
- MySQL - NOT LIKE Operator
- MySQL - NOT REGEXP Operator
- MySQL - regexp_instr() Function
- MySQL - regexp_like() Function
- MySQL - regexp_replace() Function
- MySQL - regexp_substr() Function
- MySQL Fulltext Search
- MySQL - Fulltext Search
- MySQL - Natural Language Fulltext Search
- MySQL - Boolean Fulltext Search
- MySQL - Query Expansion Fulltext Search
- MySQL - ngram Fulltext Parser
- MySQL Functions & Operators
- MySQL - Date and Time Functions
- MySQL - Arithmetic Operators
- MySQL - Numeric Functions
- MySQL - String Functions
- MySQL - Aggregate Functions
- MySQL Misc Concepts
- MySQL - NULL Values
- MySQL - Transactions
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - SubQuery
- MySQL - Comments
- MySQL - Check Constraints
- MySQL - Storage Engines
- MySQL - Export Table into CSV File
- MySQL - Import CSV File into Database
- MySQL - UUID
- MySQL - Common Table Expressions
- MySQL - On Delete Cascade
- MySQL - Upsert
- MySQL - Horizontal Partitioning
- MySQL - Vertical Partitioning
- MySQL - Cursor
- MySQL - Stored Functions
- MySQL - Signal
- MySQL - Resignal
- MySQL - Character Set
- MySQL - Collation
- MySQL - Wildcards
- MySQL - Alias
- MySQL - ROLLUP
- MySQL - Today Date
- MySQL - Literals
- MySQL - Stored Procedure
- MySQL - Explain
- MySQL - JSON
- MySQL - Standard Deviation
- MySQL - Find Duplicate Records
- MySQL - Delete Duplicate Records
- MySQL - Select Random Records
- MySQL - Show Processlist
- MySQL - Change Column Type
- MySQL - Reset Auto-Increment
- MySQL - Coalesce() Function
MySQL - Rename View
Renaming Views in MySQL
The MySQL RENAME TABLE statement in MySQL is generally used to rename the name of a table. But this statement can also be used to rename views because views are typically virtual tables created by a query.
Before renaming a view, we need to ensure that no active transactions are being performed on the view using its old name. It is, however, recommended to delete the existing view and re-create it with a new name instead of renaming it.
Syntax
Following is the basic syntax of the RENAME TABLE query to rename a view in MySQL −
RENAME TABLE original_view_name TO new_view_name;
Example
First of all, let us create a table with the name CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS( ID int NOT NULL, NAME varchar(20) NOT NULL, AGE int NOT NULL, ADDRESS varchar(25), SALARY decimal(18, 2), PRIMARY KEY (ID) );
Here, we are inserting some records into the above-created table using the query below −
INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', '32', 'Ahmedabad', 2000), (2, 'Khilan', '25', 'Delhi', 1500), (3, 'Kaushik', '23', 'Kota', 2500), (4, 'Chaitali', '26', 'Mumbai', 6500), (5, 'Hardik','27', 'Bhopal', 8500), (6, 'Komal', '22', 'MP', 9000), (7, 'Muffy', '24', 'Indore', 5500);
Creating a view −
Now, let us create a view based on the above created table using the following query −
CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS;
The view will be created as follows −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
Renaming the view −
Now, we know that we are having an existing view in our database named "CUSTOMERS_VIEW". So, we are going to rename this view to VIEW_CUSTOMERS using the below query −
RENAME TABLE CUSTOMERS_VIEW TO VIEW_CUSTOMERS;
Verification
Using the following SELECT statement, we can verify whether the view is renamed or not −
SELECT * FROM VIEW_CUSTOMERS;
The "VIEW_CUSTOMERS" view displayed is as follows −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
Rules to be followed while Renaming Views
There are some rules and practices to ensure that the renaming process goes smoothly and one should follow them while renaming a view in MySQL. They are listed below:
Avoid renaming system views: In MySQL, the system views are views that contain all the information about the database management system. It is recommended not to rename these views because it can cause issues with the functioning of the database.
Update all references to the view: After renaming a view in MySQL, any stored procedures, triggers, or other database objects that reference the view will need to be updated to use the new name of the view. If we failed to update these references results in errors or issues with the functioning of the database system.
Test thoroughly: It is important to test the renaming process thoroughly in the development or testing environment to make sure that all references to the view have been updated correctly.
Use a consistent naming convention: While working with views in MySQL, it's recommended to use a consistent naming convention. If you need to rename a view, follow the same naming convention you've used for other views in the database.
Backup the database: Before renaming a view, it is recommended to have a backup of the database to make sure that you have a restore point.
Renaming a View Using a Client Program
Until now, we used an SQL statement to rename a view directly in the MySQL database. But, we can also perform the same rename operation on a view using another client program.
Syntax
To Rename a view into MySQL Database through a PHP program, we need to execute the RENAME statement using the mysqli function named query() as follows −
$sql = "RENAME TABLE first_view To tutorial_view"; $mysqli->query($sql);
To Rename a view into MySQL Database through a JavaScript program, we need to execute the RENAME statement using the query() function of mysql2 library as follows −
sql = "RENAME TABLE CUSTOMERS_VIEW TO VIEW_CUSTOMERS"; con.query(sql);
To Rename a view into MySQL Database through a Java program, we need to execute the RENAME statement using the JDBC function named executeQuery() as follows −
String sql = "RENAME TABLE first_view TO tutorial_view"; st.executeQuery(sql);
To Rename a view into MySQL Database through a python program, we need to execute the RENAME statement using the execute() function of the MySQL Connector/Python as follows −
rename_view_query = "RENAME TABLE tutorial_view TO new_tutorial_view" cursorObj.execute(rename_view_query)
Example
Following are the programs −
$dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'password'; $dbname = 'TUTORIALS'; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { printf("Connect failed: %s
", $mysqli->connect_error); exit(); } // printf('Connected successfully.
'); // A view can be renamed by using the RENAME TABLE old_view_name TO new_view_name $sql = "RENAME TABLE first_view To tutorial_view"; if ($mysqli->query($sql)) { printf("View renamed successfully!.
"); } if ($mysqli->errno) { printf("View could not be renamed!.
", $mysqli->error); } $mysqli->close();
Output
The output obtained is as follows −
View renamed successfully!.
var mysql = require('mysql2'); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); sql = "create database TUTORIALS" con.query(sql); sql = "USE TUTORIALS" con.query(sql); sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR (25),SALARY DECIMAL (18, 2),PRIMARY KEY (ID));" con.query(sql); sql = "INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),(2, 'Khilan', 25, 'Delhi', 1500.00 ),(3, 'kaushik', 23, 'Kota', 2000.00 ),(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),(5, 'Hardik', 27, 'Bhopal', 8500.00 ),(6, 'Komal' ,22, 'MP', 4500.00 ),(7, 'Muffy', 24, 'Indore', 10000.00 );" con.query(sql); //Creating View sql = "Create view CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS"; con.query(sql); //Renaming View sql = "RENAME TABLE CUSTOMERS_VIEW TO VIEW_CUSTOMERS;" con.query(sql); //Displaying records from view sql = "SELECT * FROM VIEW_CUSTOMERS;" con.query(sql, function(err, result){ if (err) throw err console.log("**Views after deleting:**"); console.log(result); }); });
Output
The output produced is as follows −
Connected! -------------------------- [ {ID: 1, NAME: 'Ramesh', AGE: 32, ADDRESS: 'Ahmedabad', SALARY: '2000.00'}, {ID: 2, NAME: 'Khilan', AGE: 25, ADDRESS: 'Delhi', SALARY: '1500.00'}, {ID: 3, NAME: 'kaushik', AGE: 23, ADDRESS: 'Kota', SALARY: '2000.00'}, {ID: 4, NAME: 'Chaitali', AGE: 25, ADDRESS: 'Mumbai', SALARY: '6500.00'}, {ID: 5, NAME: 'Hardik', AGE: 27, ADDRESS: 'Bhopal', SALARY: '8500.00'}, {ID: 6, NAME: 'Komal', AGE: 22, ADDRESS: 'MP', SALARY: '4500.00' }, {ID: 7, NAME: 'Muffy', AGE: 24, ADDRESS: 'Indore', SALARY: '10000.00'} ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class RenameView { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String username = "root"; String password = "password"; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); System.out.println("Connected successfully...!"); //Rename Created View..... String sql = "RENAME TABLE first_view TO tutorial_view"; statement.executeUpdate(sql); System.out.println("Renamed view Successfully...!"); ResultSet resultSet = statement.executeQuery("SELECT * FROM tutorial_view"); while (resultSet.next()) { System.out.print(resultSet.getInt(1)+" "+ resultSet.getString(2)+ " "+ resultSet.getString(3)); System.out.println(); } connection.close(); } catch (Exception e) { System.out.println(e); } } }
Output
The output obtained is as shown below −
Connected successfully...! Renamed view Successfully...! 1 Learn PHP John Paul
import mysql.connector #establishing the connection connection = mysql.connector.connect( host='localhost', user='root', password='password', database='tut' ) cursorObj = connection.cursor() rename_view_query = "RENAME TABLE tutorial_view TO new_tutorial_view" cursorObj.execute(rename_view_query) connection.commit() print("View renamed successfully.") cursorObj.close() connection.close()
Output
Following is the output of the above code −
View renamed successfully.
To Continue Learning Please Login
Login with Google