Sql 简明教程

SQL - DROP or DELETE View

SQL 允许你删除现有的视图，并从数据库中的视图中删除记录。SQL 使用 DROP 语句删除视图中的所有记录以及其定义，并且使用 DELETE 语句，仅删除记录，而视图的视图定义保持不变。

SQL allows you to drop an exiting view and delete records from a view in a database. SQL uses DROP statement to delete all the records from the view along with its definition and using the DELETE statement, only the records are deleted while the view definition of the view remains unchanged.

The DROP VIEW Statement

SQL DROP VIEW 语句用于删除现有视图及其定义和其他信息。一旦删除该视图，其所有权限也将被移除。我们还可以使用此语句删除已编制索引的视图。

The SQL DROP VIEW statement is used to delete an existing view, along with its definition and other information. Once the view is dropped, all the permissions for it will also be removed. We can also drop indexed views with this statement.

假设使用 DROP TABLE 命令删除了一个表并且它有一个与之关联的视图，也必须使用 DROP VIEW 命令显式删除此视图。

Suppose a table is dropped using the DROP TABLE command and it has a view associated to it, this view must also be dropped explicitly using the DROP VIEW command.

While trying to perform queries, the database engine checks all the objects referenced in that statement are valid and exist. So, if a view does not exist in the database, the DROP VIEW statement will throw an error.
To drop a table in a database, one must require ALTER permission on the said table and CONTROL permissions on the table schema.

Syntax

此 DROP VIEW 语句的基本语法如下 −

The basic syntax of this DROP VIEW statement is as follows −

DROP VIEW view_name;

Example

假设我们使用以下查询使用 CREATE TABLE 语句创建名为 CUSTOMERS 的表：

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement using the following query −

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

假设我们使用 CREATE VIEW 语句创建了 3 个视图，如下所示 −

Assume we have created 3 views using the CREATE VIEW statement as shown below −

CREATE VIEW CUSTOMERS_VIEW1 AS SELECT * FROM CUSTOMERS;
CREATE VIEW CUSTOMERS_VIEW2 AS SELECT * FROM CUSTOMERS;
CREATE VIEW CUSTOMERS_VIEW3 AS SELECT * FROM CUSTOMERS;

您可以使用以下查询来验证所有视图的列表：

You can verify the list of all the views using the following query −

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA='tutorials';

这将按如下方式显示视图列表：

This will display the list of views as follows −

TABLE_SCHEMA

TABLE_NAME

tutorials

CUSTOMERS_VIEW1

tutorials

CUSTOMERS_VIEW2

tutorials

CUSTOMERS_VIEW3

现在，让我们使用 DROP VIEW 语句从上述创建的视图中删除两个视图。

Now, lets drop two views from the above created views using the DROP VIEW statement.

DROP VIEW CUSTOMERS_VIEW1;
DROP VIEW CUSTOMERS_VIEW2;

Verification

一旦我们删除了所有视图，如果您尝试检索视图列表，您将得到一个空集，如下图所示：

Once we have deleted all the views if you try to retrieve the list of views you will get an empty set as shown below −

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA='tutorials';

剩余的视图列表如下：

The remaining list of views is as follows −

TABLE_SCHEMA

TABLE_NAME

tutorials

CUSTOMERS_VIEW3

The IF EXISTS clause

在删除现有视图时，您可以在 DROP VIEW 语句中使用 IF EXISTS 子句。当在 DROP VIEW 查询中指定此子句时，它将自动检查该视图是否存在于当前数据库中，然后将其删除，如果是的话。如果该视图不存在于数据库中，则该查询将被忽略。

While deleting an existing view, you can use the IF EXISTS clause in the DROP VIEW statement. This clause, when specified in the DROP VIEW query, will automatically check whether the view exists in the current database and then drops it, if yes. If the view does not exist in the database, the query will be ignored.

Syntax

以下是 DROP VIEW IF EXISTS 的基本语法：

Following is the basic syntax of DROP VIEW IF EXISTS −

DROP VIEW [IF EXISTS] view_name;

Example

如果您尝试删除一个不存在于数据库中的视图，而不使用 IF EXISTS 子句，如下所示：

If you try to drop a view that doesn’t exist in the database, without using the IF EXISTS clause, as shown below −

DROP VIEW DEMO_VIEW;

将生成一个错误：

An error will be generated −

ERROR 1051 (42S02): Unknown table 'tutorials.demo_view'

但是，如果您使用 IF EXISTS 子句连同 DROP VIEW 语句一起使用，如下所示，将删除指定事件；如果具有给定名称的视图不存在，则查询将被忽略。

But if you use the IF EXISTS clause along with the DROP VIEW statement as shown below, the specified event will be dropped; and if a view with the given name doesn’t exist the query will be ignored.

DROP VIEW IF EXISTS DEMO_VIEW;

该查询将被忽略，并显示以下输出：

The query will be ignored with the following output displayed −

Query OK, 0 rows affected, 1 warning (0.04 sec)

Deleting Rows from a View

我们可以使用 DELETE 语句删除整个视图，也可以删除视图中的所选行。

Instead of removing an entire view, we can also delete selected rows of a view using the DELETE statement.

Syntax

以下 DELETE 语句的语法：

Following is the syntax of the DELETE statement −

DELETE FROM view_name WHERE condition;

Example

以下查询删除在上述创建的 CUSTOMERS 表上创建的 third_view 中的一条记录。对视图中数据所做的更改最终将反映到基础表 CUSTOMERS 中。

Following query deletes a record from the third_view created on the CUSTOMERS table created above. The changes made to the data in view will finally be reflected in the base table CUSTOMERS.

DELETE FROM CUSTOMERS_VIEW3 WHERE AGE = 22;

这最终将从基础表 CUSTOMERS 中删除一行，并且同样会反映到视图本身中。现在，查询基础表，SELECT 语句将生成以下结果：

This would ultimately delete a row from the base table CUSTOMERS and the same would reflect in the view itself. Now, query the base table and the SELECT statement would produce the following result −

SELECT * FROM CUSTOMERS;

CUSTOMERS 表显示如下：

The CUSTOMERS table is displayed as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	35	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
7	Muffy	24	Indore	10000.00