day18-使用视图

视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。


说明：SQLite 的视图 
SQLite 仅支持只读视图，所以视图可以创建，可以读，但其内容不能更改。
理解视图的最好方法是看例子。第 12 课用下面的 SELECT 语句从三个表中检索数据：


SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';

此查询用来检索订购了某种产品的顾客。任何需要这个数据的人都必须理解相关表的结构，知道如何创建查询和对表进行联结。检索其他产品（或多个产品）的相同数据，必须修改最后的 WHERE 子句。

现在，假如可以把整个查询包装成一个名为 ProductCustomers 的虚拟表，则可以如下轻松地检索出相同的数据：


SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';
这就是视图的作用。ProductCustomers 是一个视图，作为视图，它不包含任何列或数据，包含的是一个查询（与上面用以正确联结表的查询相同）。

提示：DBMS 的一致支持 
我们欣慰地了解到，所有 DBMS 非常一致地支持视图创建语法。

为什么使用视图？

我们已经看到了视图应用的一个例子。下面是视图的一些常见应用。
 重用 SQL 语句。
 简化复杂的 SQL 操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
 使用表的一部分而不是整个表。
 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。


创建视图之后，可以用与表基本相同的方式使用它们。可以对视图执行SELECT 操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据（添加和更新数据存在某些限制，关于这个内容稍后做介绍）。

重要的是，要知道视图仅仅是用来查看存储在别处数据的一种设施。视图本身不包含数据，因此返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

注意：性能问题 
因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时需要的所有检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，性能可能会下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

视图的规则和限制

创建视图前，应该知道它的一些限制。不过，这些限制随不同的 DBMS而不同，因此在创建视图时应该查看具体的 DBMS 文档。

下面是关于视图创建和使用的一些最常见的规则和限制。
 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
 对于可以创建的视图数目没有限制。
 创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予。
 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。
所允许的嵌套层数在不同的 DBMS中有所不同（嵌套视图可能会严重降低查询的性能，因此在产品环境中使用之前，应该对其进行全面测试）。

 许多 DBMS 禁止在视图查询中使用 ORDER BY 子句。
 有些 DBMS 要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名（关于列别名的更多信息，请参阅第 7 课）。
 视图不能索引，也不能有关联的触发器或默认值。
 有些 DBMS 把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表。详情请参阅具体的 DBMS 文档。
 有些 DBMS 允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新。例如有一个视图，只检索带有电子邮件地址的顾客。

如果更新某个顾客，删除他的电子邮件地址，将使该顾客不再属于视图。这是默认行为，而且是允许的，但有的 DBMS 可能会防止这种情况发生。

提示：参阅具体的 DBMS 文档 
上面的规则不少，而具体的 DBMS 文档很可能还包含别的规则。因此，在创建视图前，有必要花点时间了解必须遵守的规定。

创建视图

理解了什么是视图以及管理它们的规则和约束后，我们来创建视图。

视图用 CREATE VIEW 语句来创建。与 CREATE TABLE 一样，CREATE VIEW只能用于创建不存在的视图。

说明：视图重命名 
删除视图，可以使用 DROP 语句，其语法为 DROP VIEW viewname;。
覆盖（或更新）视图，必须先删除它，然后再重新创建。

利用视图简化复杂的联结

一个最常见的视图应用是隐藏复杂的 SQL，这通常涉及联结。请看下面的例子：
输入▼
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

分析▼
这条语句创建一个名为 ProductCustomers 的视图，它联结三个表，返回已订购了任意产品的所有顾客的列表。如果执行 SELECT * FROM ProductCustomers，将列出订购了任意产品的顾客。

检索订购了产品 RGAN01 的顾客，可如下进行：
输入▼
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';


分析▼
这条语句通过 WHERE 子句从视图中检索特定数据。当 DBMS 处理此查询时，它将指定的 WHERE 子句添加到视图查询中已有的 WHERE 子句中，以便正确过滤数据。

可以看出，视图极大地简化了复杂 SQL 语句的使用。利用视图，可一次性编写基础的 SQL，然后根据需要多次使用。

提示：创建可重用的视图 
创建不绑定特定数据的视图是一种好办法。例如，上面创建的视图返回订购所有产品而不仅仅是 RGAN01 的顾客（这个视图先创建）。扩展视图的范围不仅使得它能被重用，而且可能更有用。这样做不需要创建和维护多个类似视图。

用视图重新格式化检索出的数据

如前所述，视图的另一常见用途是重新格式化检索出的数据。下面的SELECT 语句（来自第 7 课）在单个组合计算列中返回供应商名和位置：

输入▼
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
 AS vend_title
FROM Vendors
ORDER BY vend_name;




下面是相同的语句，但使用了||语法（如第 7 课所述）：
输入▼
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
 AS vend_title
FROM Vendors
ORDER BY vend_name;



现在，假设经常需要这个格式的结果。我们不必在每次需要时执行这种拼接，
而是创建一个视图，使用它即可。把此语句转换为视图，可按如下进行：

输入▼
CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
 AS vend_title
FROM Vendors;

下面是使用||语法的相同语句：
输入▼
CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
 AS vend_title 
FROM Vendors;




分析▼
这条语句使用与以前 SELECT 语句相同的查询创建视图。要检索数据，创建所有的邮件标签，可如下进行：
输入▼
SELECT * FROM VendorLocations;



输出：
mysql> CREATE VIEW VendorLocations AS
    -> SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
    ->  AS vend_title
    -> FROM Vendors;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE VIEW VendorLocations AS
    -> SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
    ->  AS vend_title 
    -> FROM Vendors;
ERROR 1050 (42S01): Table 'VendorLocations' already exists
mysql> SELECT * FROM VendorLocations;
+------------+
| vend_title |
+------------+
|          0 |
|          0 |
|          0 |
|          0 |
|          0 |
|          0 |
+------------+
6 rows in set, 24 warnings (0.00 sec)

mysql> 



说明：SELECT 约束全部适用 
在这一课的前面提到，各种 DBMS 中用来创建视图的语法相当一致。那么，为什么会有多种创建视图的语句版本呢？

因为视图只包含一个SELECT 语句，而这个语句的语法必须遵循具体 DBMS 的所有规则和约束，所以会有多个创建视图的语句版本。

用视图过滤不想要的数据

视图对于应用普通的 WHERE 子句也很有用。例如，可以定义 CustomerEMailList 视图，过滤没有电子邮件地址的顾客。为此，可使用下面的语句：


CREATE VIEW CustomerEMailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;


输出：
mysql> CREATE VIEW CustomerEMailList AS
    -> SELECT cust_id, cust_name, cust_email
    -> FROM Customers
    -> WHERE cust_email IS NOT NULL;
Query OK, 0 rows affected (0.00 sec)



分析▼
显然，在将电子邮件发送到邮件列表时，需要排除没有电子邮件地址的用户。这里的 WHERE 子句过滤了 cust_email 列中具有 NULL 值的那些行，使它们不被检索出来。

现在，可以像使用其他表一样使用视图 CustomerEMailList。
输入▼
SELECT *
FROM CustomerEMailList;


输出：mysql> SELECT *
    -> FROM CustomerEMailList;
+------------+--------------+---------------------------------+
| cust_id    | cust_name    | cust_email                      |
+------------+--------------+---------------------------------+
| 1000000001 | Village Toys | sales@villagetoys.com           |
| 1000000003 | Fun4All      | jjones@fun4all.com              |
| 1000000004 | Fun4All      | dstephens@fun4all.com           |
| 1000000042 | Ben's Toys   | ben@fortabbwpc.wpcomstaging.com |
+------------+--------------+---------------------------------+
4 rows in set (0.00 sec)

mysql> 





说明：WHERE 子句与 WHERE 子句 
从视图检索数据时如果使用了一条 WHERE 子句，则两组子句（一组在视图中，另一组是传递给视图的）将自动组合

使用视图与计算字段

在简化计算字段的使用上，视图也特别有用。下面是第 7 课中介绍的一条 SELECT 语句，它检索某个订单中的物品，计算每种物品的总价格：



SELECT prod_id,
 quantity,
 item_price,
 quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;



输出：


mysql> SELECT prod_id,
    ->  quantity,
    ->  item_price,
    ->  quantity*item_price AS expanded_price
    -> FROM OrderItems
    -> WHERE order_num = 20008;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| RGAN01  |        5 |       4.99 |          24.95 |
| BR03    |        5 |      11.99 |          59.95 |
| BNBG01  |       10 |       3.49 |          34.90 |
| BNBG02  |       10 |       3.49 |          34.90 |
| BNBG03  |       10 |       3.49 |          34.90 |
+---------+----------+------------+----------------+
5 rows in set (0.00 sec)

mysql> 




要将其转换为一个视图，如下进行：
输入▼
CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
 prod_id,
 quantity,
 item_price,
 quantity*item_price AS expanded_price
FROM OrderItems;


输出：



检索订单 20008 的详细内容（上面的输出），如下进行：
输入▼
SELECT *
FROM OrderItemsExpanded
WHERE order_num = 20008;



输出：
mysql> SELECT *
    -> FROM OrderItemsExpanded
    -> WHERE order_num = 20008;
+-----------+---------+----------+------------+----------------+
| order_num | prod_id | quantity | item_price | expanded_price |
+-----------+---------+----------+------------+----------------+
|     20008 | RGAN01  |        5 |       4.99 |          24.95 |
|     20008 | BR03    |        5 |      11.99 |          59.95 |
|     20008 | BNBG01  |       10 |       3.49 |          34.90 |
|     20008 | BNBG02  |       10 |       3.49 |          34.90 |
|     20008 | BNBG03  |       10 |       3.49 |          34.90 |
+-----------+---------+----------+------------+----------------+
5 rows in set (0.00 sec)

mysql> 

可以看到，视图非常容易创建，而且很好使用。正确使用，视图可极大地简化复杂数据的处理。

小结

视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。

视图提供了一种封装 SELECT 语句的层次，可用来简化数据处理，重新格式化或保护基础数据。

小练习

1.创建一个名为 CustomersWithOrders 的视图，其中包含 Customers表中的所有列，但仅仅是那些已下订单的列。提示：可以在 Orders表上使用 JOIN 来仅仅过滤所需的顾客，然后使用 SELECT 来确保拥有正确的数据。


CREATE VIEW CustomersWithOrders AS
SELECT Customers.cust_id,
       Customers.cust_name,
       Customers.cust_address,
       Customers.cust_city,
       Customers.cust_state,
       Customers.cust_zip,
       Customers.cust_country,
       Customers.cust_contact,
       Customers.cust_email
FROM Customers
JOIN Orders ON Customers.cust_id = Orders.cust_id;
SELECT * FROM CustomersWithOrders;



输出：

mysql> CREATE VIEW CustomersWithOrders AS
    -> SELECT Customers.cust_id,
    ->        Customers.cust_name,
    ->        Customers.cust_address,
    ->        Customers.cust_city,
    ->        Customers.cust_state,
    ->        Customers.cust_zip,
    ->        Customers.cust_country,
    ->        Customers.cust_contact,
    ->        Customers.cust_email
    -> FROM Customers
    -> JOIN Orders ON Customers.cust_id = Orders.cust_id;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM CustomersWithOrders;
+------------+---------------+---------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id    | cust_name     | cust_address        | cust_city | cust_state | cust_zip | cust_country | cust_contact       | cust_email            |
+------------+---------------+---------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys  | 200 Maple Lane      | Detroit   | MI         | 44444    | USA          | John Smith         | sales@villagetoys.com |
| 1000000001 | Village Toys  | 200 Maple Lane      | Detroit   | MI         | 44444    | USA          | John Smith         | sales@villagetoys.com |
| 1000000003 | Fun4All       | 1 Sunny Place       | Muncie    | IN         | 42222    | USA          | Jim Jones          | jjones@fun4all.com    |
| 1000000004 | Fun4All       | 829 Riverside Drive | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street    | Chicago   | IL         | 54545    | USA          | Kim Howard         | NULL                  |
+------------+---------------+---------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
5 rows in set (0.00 sec)

mysql>

2.下面的 SQL 语句有问题吗？（尝试在不运行的情况下指出。）

CREATE VIEW OrderItemsExpanded AS 

SELECT order_num, 

prod_id, 

quantity,

item_price, 

quantity*item_price AS expanded_price 

FROM OrderItems 

ORDER BY order_num;




解答：



视图中不允许使用 ORDER BY。视图的使用方式与表类似，如果您需要排序的数据，请在从视图中检索数据的 SELECT 中使用 ORDER BY。

posted @ 2025-07-08 20:46 三思博客阅读(13) 评论(0) 收藏举报

刷新页面返回顶部

三思博客

纸上得来终觉浅绝知此事要躬行敢想敢干最重要 Slow is fast No one has a chance of victory, but I am willing to exchange my life for this chance of victory. What about you?

day18-使用视图

day18-使用视图

视图

为什么使用视图？

视图的规则和限制

创建视图

利用视图简化复杂的联结

用视图重新格式化检索出的数据

用视图过滤不想要的数据

使用视图与计算字段

小结

小练习

1.创建一个名为 CustomersWithOrders 的视图，其中包含 Customers表中的所有列，但仅仅是那些已下订单的列。提示：可以在 Orders表上使用 JOIN 来仅仅过滤所需的顾客，然后使用 SELECT 来确保拥有正确的数据。

2.下面的 SQL 语句有问题吗？（尝试在不运行的情况下指出。）

公告

三思博客

纸上得来终觉浅 绝知此事要躬行 敢想敢干最重要 Slow is fast No one has a chance of victory, but I am willing to exchange my life for this chance of victory. What about you?

day18-使用视图

day18-使用视图

视图

为什么使用视图？

视图的规则和限制

创建视图

利用视图简化复杂的联结

用视图重新格式化检索出的数据

用视图过滤不想要的数据

使用视图与计算字段

小结

小练习

1.创建一个名为 CustomersWithOrders 的视图，其中包含 Customers表中的所有列，但仅仅是那些已下订单的列。提示：可以在 Orders表上使用 JOIN 来仅仅过滤所需的顾客，然后使用 SELECT 来确保拥有正确的数据。

2.下面的 SQL 语句有问题吗？（尝试在不运行的情况下指出。）

公告

纸上得来终觉浅绝知此事要躬行敢想敢干最重要 Slow is fast No one has a chance of victory, but I am willing to exchange my life for this chance of victory. What about you?