Sql 简明教程

SQL - Rename View

有各种 SQL 语句可对数据库对象执行不同的操作，例如创建、更新、删除以及重命名数据库对象。由于视图也是数据库对象，所有这些操作也可以对视图执行，你可以创建视图、更新视图、删除视图以及重命名视图。

There are various SQL statements that perform different operations on database objects, such as creating, updating, deleting and also renaming a database object. And since a view is also a database object, all these operations can also be performed on a view, you can create a view, update a view, delete a view and also rename a view.

在许多情况下，会建议删除现有视图，然后使用新名称重新创建它。

In many cases, deleting the existing view and then re-creating it with a new name is rather recommended.

Renaming a View in MySQL

RENAME TABLE 语句在 MySQL 数据库中用于重命名视图。你只需确保视图的新名称与任何现有视图的名称不重叠。

The RENAME TABLE statement in MySQL database is used to rename views. You just have to make sure that the new name of the view does not overlap with the name of any existing views.

Syntax

以下是 MySQL 中重命名视图的基本语法 −

Following is the basic syntax to rename a view in MySQL −

RENAME TABLE old_view_name To new_view_name;

在此，我们必须确保旧视图名称存在于数据库中且新视图名称尚不存在。否则，它会发出警告。此外在执行此语句之前，确保表格未锁定且没有活动事务非常重要。

Here, we must ensure that old view name is present in the database and that new view name does not already exist. Otherwise, it will issue a warning. Also before executing this statement, it is important to make sure that the table is not locked and there are no active transactions.

Example

在此示例中，让我们首先创建一个名为 CUSTOMERS 的表格，该表格包含客户的个人详细信息，包括其姓名、年龄、地址和工资等，如下所示 −

In this example, let us first create a table with the name CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. As shown below −

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)
);

现在使用 INSERT 语句向该表中插入值，如下所示：

Now insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS 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, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

该表将按如下方式创建：

The table 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

以下查询基于上述创建的表格创建一个视图 −

Following query creates a view based on the above created table −

CREATE VIEW CUSTOMERS_VIEW AS
SELECT * FROM CUSTOMERS WHERE AGE > 25;

你可以使用 select 查询验证视图的内容，如下所示 −

You can verify the contents of a view using the select query as shown below −

SELECT * from CUSTOMERS_VIEW;

视图显示如下 −

The view is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
5	Hardik	27	Bhopal	8500.00

现在我们知道名为 CUSTOMERS_VIEW 的视图存在于我们的数据库中。因此，我们将直接使用以下查询将此视图重命名为 VIEW_CUSTOMERS −

Now we know that a view with the name CUSTOMERS_VIEW exists in our database. So, we are directly going to rename this view to VIEW_CUSTOMERS, using the following query −

RENAME TABLE CUSTOMERS_VIEW TO VIEW_CUSTOMERS;

Output

获得的结果如下所示 −

The result obtained is as shown below −

Query OK, 0 rows affected (0.08 sec)

Verification

我们可以通过在 SELECT 语句中使用其新名称检索视图的内容来验证视图是否已重命名。以下查询用于显示 VIEW_CUSTOMERS 视图中的记录 −

We can verify whether the view is renamed or not by retrieving its contents using its new name in the SELECT statement. Following is the query to display the records in the VIEW_CUSTOMERS view −

SELECT * from VIEW_CUSTOMERS;

显示的视图如下 −

The view displayed is as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
5	Hardik	27	Bhopal	8500.00

Renaming a View in SQL Server

SQL Server 中没有可以直接重命名视图的查询。但是，它确实让你可以使用一个名为 sp_rename 的存储过程，该存储过程可以重命名视图。在使用新名称重命名视图之前，你必须确保没有使用其旧名称对视图执行任何活动事务。

There isn’t a query in SQL Server that can rename a view directly. But, it does give you access to a stored procedure called sp_rename that can rename a view. You have to make sure there are no active transactions being performed on the view using its old name before renaming it.

sp_rename 是 SQL 中一个系统存储过程（在数据库中执行任务的一组预建子程序），它可用于重命名各种数据库对象，包括表格、列、索引和约束。

The sp_rename is a system stored procedure (set of pre-built subroutines that perform tasks within the database) in SQL that can be used to rename various database objects including tables, columns, indexes, and constraints.

Syntax

以下是 SQL 中重命名视图的基本语法 −

Following is the basic syntax to rename a view in SQL −

EXEC sp_rename 'old_view_name', 'new_view_name'

在此，我们必须确保旧视图名称存在于数据库中且新视图名称尚不存在。否则，它会发出警告。在执行此语句之前，确保表格未锁定且没有活动事务非常重要。

Here, we must ensure that old view name is present in the database and that new view name does not already exist. Otherwise, it will issue a warning. Before executing this statement, it is important to make sure that the table is not locked and there are no active transactions.

Example

在此示例中，让我们首先尝试创建一个名为 CUSTOMERS 的表格，该表格包含客户的个人详细信息，包括其姓名、年龄、地址和工资等，如下所示 −

In this example, let us first try to create a table with the name CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. As shown below −

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)
);

现在使用 INSERT 语句向该表中插入值，如下所示：

Now insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS 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 );

以下查询基于上述创建的表格创建一个视图 −

Following query creates a view based on the above created table −

CREATE VIEW CUSTOMERS_VIEW AS
SELECT * FROM CUSTOMERS WHERE SALARY >2000;

你可以使用 select 查询验证视图的内容，如下所示 −

You can verify the contents of a view using the select query as shown below −

SELECT * from CUSTOMERS_VIEW;

视图将创建为 −

The view will be created as −

NAME

AGE

ADDRESS

SALARY

Chaitali

Mumbai

6500.00

现在，我们知道我们的数据库中有现有视图 CUSTOMERS_VIEW 。因此，我们将使用以下查询将此视图重命名为 VIEW_CUSTOMERS −

Now, we know that we have an existing view CUSTOMERS_VIEW in our database. So, we are going to rename this view to VIEW_CUSTOMERS, using the following query −

EXEC sp_rename CUSTOMERS_VIEW, VIEW_CUSTOMERS;

Verification

我们可以通过在 SELECT 语句中使用其新名称检索视图的内容来验证视图是否已重命名。以下查询用于显示 VIEW_CUSTOMERS 视图中的记录 −

We can verify whether the view is renamed or not by retrieving its contents using its new name in the SELECT statement. Following is the query to display the records in the VIEW_CUSTOMERS view −

SELECT * FROM VIEW_CUSTOMERS;

显示的视图如下 −

The view displayed is as follows −

NAME

AGE

ADDRESS

SALARY

Chaitali

Mumbai

6500.00

我们已将视图重命名为 VIEW_CUSTOMERS；如果用户尝试使用旧视图名称获取详细信息，将会抛出视图不存在的错误。

We have renamed the view to VIEW_CUSTOMERS; if the user tries to get the details by using the old view name, it will throw an error showing that the view does not exist.

Rules to be followed while Renaming Views

当在 SQL 中重命名视图时，有一些规则和最佳实践应该遵循，以确保重命名过程顺利并且不会造成任何意外的后果或问题。

When renaming views in SQL, there are some rules and best practices that should be followed to ensure that the renaming process goes smoothly and does not cause any unintended consequences or issues.

以下是重命名 SQL 中的视图时要记住的一些一般规则：

Here are some general rules to keep in mind when renaming views in SQL −

Avoid renaming system views − System views are views that contain all the information about the database management system. Renaming these views can cause issues with the functioning of the database system, so it is generally not recommended to rename system views.
Update all references to the view − After renaming a view, 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. Failure to update these references can result in errors or issues with the functioning of the database system.
Test thoroughly − Before renaming a view in a production environment, it is important to test the renaming process thoroughly in a development or testing environment to ensure that all references to the view have been updated correctly and that the database system continues to function as expected.
Use a consistent naming convention − It is a good practice to use a consistent naming convention for views and other database objects to make it easier to understand and maintain the database system. If you need to rename a view, consider following the same naming convention that you have used for other views in the database.
Backup the database − Before renaming a view, it is recommended to create a backup of the database to ensure that you have a restore point; in case anything goes wrong during the renaming process.