SQL笔记18：使用视图

视图

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

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

现在，将整个查询包装成一个名为ProductCustomers虚拟表

输入：

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

为什么使用视图

1. 重用SQL语句
2. 简化复杂SQL操作
3. 使用表的一部分而不是全部
4. 保护数据
5. 更改数据格式和表示

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

视图的规则和限制

详情见具体的DBMS文档，如下为最常见的规则和限制

1. 唯一命名
2. 视图数据无限制
3. 创建视图需要足够䣌访问权限
4. 视图可以嵌套
5. 禁止在视图查询中使用ORDER BY子句
6. 视图不能索引，也不能有关联的触发器或默认值
7. 有些DBMS将视图作为只读查询

创建视图

使用CREATE VIEW语句创建视图，删除视图使用DROP VIEW语句，覆盖更新视图，必须删除再创建。

利用视图简化复杂的联结

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,'(',vend_country,')')
		AS vend_title
FROM Vendors
ORDER BY vend_name;

输出：

CREATE VIEW VendorLocation AS
SELECT concat(vend_name,'(',vend_country,')')
		AS vend_title
FROM Vendors
ORDER BY vend_name;

输入：

SELECT * FROM VendorLocation;

输出：

用视图过滤不想要的数据

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

输入：

SELECT * FROM CustomerEMailList;

输出：

使用视图与计算字段

输入：

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. 视图是虚拟的表，包含的不是数据，而是根据需要检索数据的查询
2. 提供了一种封装SELECT语句的层次
3. 简化数据处理，重新格式化或保护基础数据

挑战题

1. Create a view called CustomersWithOrders that contains all of the columns in Customers, but only includes those who have placed orders. Hint, you can JOIN the Orders table to filter just the customers you want. Then use a SELECT to make sure you have the right data.

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

2. What is wrong with the following SQL statement? (Try to figure it out without running it):

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 is not allowed in views. Views are used like tables, if you need sorted data use ORDER BY in the SELECT that retrieves data from the view.

值得说明的是，至少在mysql-community-8.0.28.0环境中，使用视图时被允许使用ORDER BY子句

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

输入：

SELECT * FROM OrderItemsExpanded
ORDER BY order_num;

输出：