5-1　视图

视图和表

视图的优点

• 第一点是由于视图无需保存数据，因此可以节省存储设备的容量
  • 表中存储的是实际数据，而视图中保存的是从表中取出数据所使用的SELECT语句
• 第二个优点就是可以将频繁使用的 SELECT 语句保存成视图，这样
  就不用每次都重新书写了

-- 通过视图等SELECT语句保存数据
SELECT product_type, SUM(sale_price), SUM(purchase_price)
FROM Product
GROUP BY product_type;

创建视图的方法

创建视图的CREATE VIEW语句

CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
AS
<SELECT语句>

使用视图的查询
在 FROM 子句中使用视图的查询，通常有如下两个步骤：

1. 首先执行定义视图的 SELECT 语句
2. 根据得到的结果，再执行在 FROM 子句中使用视图的 SELECT 语句

应该避免在视图的基础上创建视图。多重视图会降低 SQL 的性能

CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;

SELECT product_type, cnt_product
FROM ProductSum;

CREATE VIEW ProductSumJim (product_type, cnt_product)
AS
SELECT product_type, cnt_product
FROM ProductSum
WHERE product_type = '办公用品';

SELECT product_type, cnt_product 
FROM ProductSumJim;

视图的限制①-——定义视图时不能使用ORDER-BY子句

因为视图和表一样， 数据行都是没有顺序的

-- 不能像这样定义视图
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
ORDER BY product_type;


SELECT * FROM ProductSum;

视图的限制②-——对视图进行更新

没有聚合又没有结合的SELECT语句

-- 对 ProductSum 视图执行如下 INSERT 语句
INSERT INTO ProductSumNew VALUES ('电器制品', 5);

-- 可以更新的视图
CREATE VIEW ProductJim (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
AS
SELECT *
FROM Product
WHERE product_type = '办公用品';

-- 向视图中添加数据行
INSERT INTO ProductJim VALUES ('0009', '印章', '办公用品', 95, 10, '2009-11-30');

SELECT * FROM ProductJim;
SELECT * FROM Product;

删除视图

DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……)

-- 删除视图
DROP VIEW ProductSum;

5-2　子查询

子查询和视图

子查询就是将用来定义视图的SELECT语句直接用于FROM子句当中

子查询作为内层查询会首先执行。

视图ProductSum和确认用的SELECT语句
-- 根据商品种类统计商品数量的视图
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;

-- 确认创建好的视图
SELECT product_type, cnt_product
FROM ProductSum;

-- 在FROM子句中直接书写定义视图的SELECT语句
SELECT product_type, cnt_product
FROM ( SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type ) AS ProductSum;

SELECT语句的执行顺序
① 首先执行 FROM 子句中的 SELECT 语句（子查询）
SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type;
② 根据①的结果执行外层的 SELECT 语句
SELECT product_type, cnt_product
FROM ProductSum;

尝试增加子查询的嵌套层数
SELECT product_type, cnt_product
FROM (SELECT *
FROM (SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type) AS ProductSum 
WHERE cnt_product = 4) AS ProductSum2;

子查询的名称

为子查询设定名称时需要使用 AS 关键字

标量子查询

标量子查询就是返回单一值的子查询

-- 在WHERE子句中不能使用聚合函数
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > AVG(sale_price);

-- 选取出销售单价（ sale_price）高于全部商品的平均单价的商品
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product);

标量子查询的书写位置

能够使用常数或者列名的地方，无论是 SELECT 子句、 GROUP BY 子句、 HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

-- 在SELECT子句中使用标量子查询
SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM Product) AS avg_price
FROM Product;

-- 在HAVING子句中使用标量子查询
SELECT product_type, AVG(sale_price)
FROM Product
GROUP BY product_type
HAVING AVG(sale_price) > 
(SELECT AVG(sale_price) FROM Product);

使用标量子查询时的注意事项

该子查询绝对不能返回多行结果

-- 由于不是标量子查询，因此不能在SELECT子句中使用
SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM Product
GROUP BY product_type) AS avg_price
FROM Product;

5-3　关联子查询

普通的子查询和关联子查询的区别

在细分的组内进行比较时，需要使用关联子查询。

-- 按照商品种类计算平均价格
SELECT AVG(sale_price)
FROM Product
GROUP BY product_type;

-- 通过关联子查询按照商品种类对平均销售单价进行比较
SELECT product_type, product_name, sale_price
FROM Product AS P1 
WHERE sale_price > (SELECT AVG(sale_price) FROM Product AS P2 WHERE P1.product_type = P2.product_type GROUP BY product_type);

关联子查询也是用来对集合进行切分的

结合条件一定要写在子查询中

子查询内部设定的关联名称，只能在该子查询内部使用。换句话说，就是“内部可以看到外部，而外部看不到内部”

-- 错误的关联子查询书写方法
SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE P1.product_type = P2.product_type
AND sale_price > (SELECT AVG(sale_price)
FROM Product AS P2
GROUP BY product_type);

子查询内的关联名称的有效范围
SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE sale_price>(SELECT AVG(sale_price)
FROM Product AS P2
WHERE P1.product_type=P2.product_type
GROUP BY product_type);

练习题

5.1 创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 Product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。

• 条件 1： 销售单价大于等于 1000 日元。
• 条件 2： 登记日期是 2009 年 9 月 20 日。
• 条件 3： 包含商品名称、销售单价和登记日期三列。

CREATE VIEW ViewPractice5_1 (product_name, sale_price,regist_date)
AS
SELECT product_name, sale_price, regist_date
FROM Product
WHERE sale_price >= 1000
AND regist_date = '2009-09-20';

SELECT * FROM ViewPractice5_1;

5.2 向习题 5.1 中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果呢？

INSERT INTO ViewPractice5_1 VALUES ('刀子', 300, '2009-11-02');

会发生错误。

解答:对视图的更新归根结底是对视图所对应的表进行更新。因此，该 INSERT 语句实质上和下面的 INSERT 语句相同。

INSERT INTO Product (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
VALUES (NULL, '刀子', NULL, 300, NULL, '2009-11-02');

5.3 请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。

SELECT product_id, product_name, product_type, sale_price,
(SELECT AVG(sale_price) FROM Product) as sale_price_all
FROM Product;

5.4 请根据习题 5.1 中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为 AvgPriceByType）。
执行结果

提示：其中的关键是avg_sale_price列。与习题5.3不同，这里需要计算出的是各商品种类的平均销售单价。这与5-3节中使用关联子查询所得到的结果相同。
也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询

CREATE VIEW AvgPriceByType(product_id, product_name, product_type, sale_price, avg_sale_price)
AS 
SELECT product_id, product_name, product_type, sale_price, 
(SELECT AVG(sale_price) FROM Product p2 GROUP BY product_type HAVING p1.product_type = p2.product_type)
FROM Product p1;

CREATE VIEW AvgPriceByType
AS 
SELECT product_id, product_name, product_type, sale_price, 
(SELECT AVG(sale_price) 
	FROM Product p2 
	GROUP BY product_type 
	HAVING p1.product_type = p2.product_type) as avg_sale_price
FROM Product p1;


SELECT * FROM AvgPriceByType;