第十八课 使用视图



第十八课 使用视图

       预习与回顾：

       第十七课学习了CREATE TABLE用来创建新表，ALTER TABLE用来更改表列（或其他诸如约束或索引等对象）以及DROP TABLE用来完整地删除一个表；第十八课将学习什么是视图，它们怎样工作，何时使用它们以及如何利用视图简化前几课中执行的某些SQL操作。

    18.1、视图

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

输入▼
SELECT cust_name, cust_contact
                FROM Customers, Orders,OrderItems
                            WHERECustomers.cust_id = Orders.cust_id
                                          AND OrderItems.order_num = Orders.order_num
                                          AND prod_id = 'RGAN01';检索订购了某种产品的顾客。

把整个查询包装成一个名为ProductCustomers的虚拟表：

输入▼
SELECT cust_name, cust_contact
                FROM ProductCustomers
                            WHERE prod_id= 'RGAN01';ProductCustomers是一个视图，作为视图，它不包含任何列或数据，包含的是一个查询。

    18.1.1、为什么使用视图

1、重用SQL语句。
2、简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
3、使用表的一部分而不是整个表。
4、保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
5、更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

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

    18.1.2、视图的规则与限制

1、与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
2、对于可以创建的视图数目没有限制。
3、创建视图，必须具有足够的访问权限。
4、视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。

5、许多DBMS禁止在视图查询中使用ORDER BY子句。
6、有些DBMS要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名。
7、视图不能索引，也不能有关联的触发器或默认值。
8、有些DBMS把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表。
9、有些DBMS允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新。

     18.2、创建视图

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

    18.2.1、利用视图简化复杂的联结

         隐藏复杂的SQL，这通常涉及联结。

       输入▼
CREATE VIEW ProductCustomers AS
                 SELECT cust_name,cust_contact, prod_id
                                 FROMCustomers, Orders, OrderItems
                                            WHERE Customers.cust_id = Orders.cust_id
                                                           AND OrderItems.order_num = Orders.order_num;

      创建一个名为ProductCustomers的视图，它联结三个表，返回已订购了任意产品的所有顾客的列表

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

      利用视图，可一次性编写基础的SQL，然后根据需要多次使用。

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

      在单个组合计算列中返回供应商名和位置：

语法回顾：
输入▼
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
                AS vend_title
                FROM Vendors
                            ORDER BYvend_name;

使用||语法的相同语句：

输入▼
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
               AS vend_title
               FROM Vendors
                           ORDER BYvend_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) || ')'
                               ASvend_title
                 FROM Vendors;

SELECT * FROMVendorLocations;

      18.2.3、使用视图过滤不想要的数据

      输入▼
CREATE VIEW CustomerEMailList AS
                 SELECT cust_id,cust_name, cust_email
                                 FROMCustomers
                 WHERE cust_email IS NOTNULL;---在将电子邮件发送到邮件列表时，需要排除没有电子邮件地址的用户。

       SELECT *
FROM CustomerEMailList;

       18.2.4、使用视图与计算字段

       检索某个订单中的物品，计算每种物品的总价格：
输入▼
SELECT prod_id,
               quantity,
               item_price,
               quantity*item_price ASexpanded_price
FROM OrderItems
             WHERE order_num = 20008;
        转换为视图

       输入▼
CREATE VIEW OrderItemsExpanded AS
                 SELECT order_num,
                                 prod_id,
                                 quantity,
                                 item_price,
                                 quantity*item_priceAS expanded_price
                 FROM OrderItems;

SELECT *
FROM OrderItemsExpanded
            WHERE order_num = 20008;