《SQL必知必会》第十八课 使用视图 利用视图简化SQL操作

第十八课 使用视图

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

一、视图

#视图是虚拟的表。
#与包含数据的表不一样，视图只包含使用时动态检索数据的查询
#MySQL从版本5开始支持视图，较早版本不适用
#视图非常容易创建，且很好使用
#正确使用视图，可极大简化复杂数据的处理

【1】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的虚拟表，则可以轻松检索出上述相同数据

【2】SELECT cust_name, cust_contact FROM productCustomers WHERE prod_id = 'RGAN01';
#此即视图的作用
#productCustomers是一个视图
#productCustomers视图不包含任何列或数据，其包含的是一个查询

注意1：
#所有DBMS一致的支持视图的创建语法

1.1 为什么使用视图：

视图的一些常见应用：

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

#创建视图后，可用与表基本相同的方式使用它们
#可对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加或更新数据
#视图仅仅是用来查看存储在别处数据的一种设施
#视图本身不包含数据，返回的数据是从其他表中检索出来的
#在添加或更改这些表中的数据时，视图将返回改变过的数据

注意2：
性能问题
[1] 视图不包含数据，每次使用视图时，必须处理查询执行时需要的所有检索
[2] 创建复杂的视图或嵌套视图，性能下降可能会很厉害
[3] 在部署使用大量视图的应用前，应该进行测试

1.2 视图的规则和限制：

关于视图创建和使用的一些最常见的规则和限制：

• 与表名一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）
• 可创建的视图数目没有限制
• 创建视图必须有足够的访问权限，这些权限由数据库管理人员授予
• 视图可以嵌套，即可以从其他视图中检索数据的查询来构造一个视图，所允许的嵌套层数在不同的DBMS中有所不同
• 许多DBMS禁止在视图查询中使用ORDER BY子句
• 有些DBMS要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名
• 视图不能索引，也不能有关联的触发器或默认值
• 有些DBMS把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表
• 有些DBMS允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新

二、创建视图

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

注意2：
视图重命名：
删除视图，可以用DROP语句，其语法为DROP VIEW viewname;
覆盖或更新视图，必须先删除它，然后在重新创建

2.1 利用视图简化复杂的联结：

#一个最常见的视图应用是隐藏复杂的SQL，这通常涉及联结

【2】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的视图
#它联结三个表：Customers、Orders和OrderItems，返回已订购了任意产品的所有顾客的列表。

【3】SELECT cust_name, cust_contact FROM productCustomers WHERE prod_id = 'RGAN01';
#检索订购了产品RGAN01的顾客
#通过WHERE子句从视图中检索特定数据
#当DBMS处理此查询时，它将指定的WHERE子句添加到视图查询中已有的WHERE子句中，以便正确过滤数据
#视图极大简化复杂SQL语句的使用
#利用视图，可一次性编写基础的SQL，然后根据需要多次使用

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

#视图的另一个常见用途是重新格式化检出的数据
#下面使用SELECT语句（第七课），在单个组合计算列中返回供应商和位置
【4】SELECT Concat(vend_name, ' (', RTRIM(vend_country), ')') AS vend_title FROM Vendors ORDER BY vend_name;

【5】SELECT RTRIM(vend_name) || ' (', RTRIM(vend_country) || ')' AS vend_title FROM Vendors ORDER BY vend_name;
#与上句SQL语句相同的语句，使用了||语法
#不适用于MySQL
#假设经常需要这个格式的结果，不必在每次需要时执行这种拼接，而是创建一个视图，使用它即可
#将上述语句转换为视图，执行下述操作

【6】CREATE VIEW VendorLocations AS SELECT Concat(vend_name, ' (', RTRIM(vend_country), ')') AS vend_title FROM Vendors;
#使用与之前的SELECT相同的查询创建视图

【7】SELECT * FROM VendorLocations;
#检索数据，创建所有的邮件标签

注意3：
SELECT约束全部适用：
#视图只包含一个SELECT语句，这个语句的语法必须遵循DBMS的所有规则和约束，所以会有多个创建视图的语句版本

2.3 用视图过滤不想要的数据：

#视图对于应用普通的WHERE子句也很有用
#可以定义CustomersEMailList视图，过滤没有电子邮件的顾客

【8】CREATE VIEW CustomersEMailList AS SELECT cust_id, cust_name, cust_email FROM Customers WHERE cust_email IS NOT NULL;
#WHERE子句过滤cust_email列中具有NULL值的行，使它们不被检索出来
#可像使用其他表一样使用视图CustomersEMailList

【9】SELECT * FROM CustomersEMailList;

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

2.4 使用视图与计算字段：

#在简化计算字段的使用上，视图也特别有用
#第七课中的SELECT语句，检索某个订单中的物品，计算每种物品的总价格

【10】SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;
#将其转换为一个视图，如下进行

【11】CREATE VIEW OrderItemsExpanded AS SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems;

【12】SELECT * FROM OrderItemsExpanded WHERE order_num = 20008;
#检索订单20008的详细内容

程序代码（DBMS：MySQL）：

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


SELECT cust_name, cust_contact
FROM productCustomers
WHERE prod_id = 'RGAN01';

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;

SELECT cust_name, cust_contact
FROM productCustomers
WHERE prod_id = 'RGAN01';


SELECT Concat(vend_name, ' (', RTRIM(vend_country), ')')
AS vend_title
FROM Vendors
ORDER BY vend_name;

SELECT RTRIM(vend_name) || ' (', RTRIM(vend_country) || ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;

CREATE VIEW VendorLocations AS
SELECT Concat(vend_name, ' (', RTRIM(vend_country), ')')
AS vend_title
FROM Vendors;

SELECT *
FROM VendorLocations;

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

SELECT *
FROM CustomersEMailList;

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

CREATE VIEW OrderItemsExpanded AS
SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM OrderItems;

SELECT *
FROM OrderItemsExpanded
WHERE order_num = 20008;

需要完整代码的小伙伴可关注微信公众号：菜田里守望者

打开微信扫一扫关注吧，你们的支持就是我的动力

参考文献：
【1】《SQL必知必会》第四版 人民邮电出版社 [美] Ben Forta 著 钟鸣 刘晓霞 译