【数据库系列】| 视图和子查询

5.1视图

从SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实
际的数据，而视图中保存的是 SELECT 语句（视图本身并不存储数据）。
使用视图，可以轻松完成跨多表查询数据等复杂操作。
可以将常用的 SELECT 语句做成视图来使用。
创建视图需要使用 CREATE VIEW 语句。
视图包含“不能使用 ORDER BY ”和“可对其进行有限制的更新”两项限制。
删除视图需要使用 DROP VIEW 语句。

视图的优点大体有三点。
第一点是由于视图无需保存数据，因此可以节省存储设备的容量。
第二个优点就是可以将频繁使用的 SELECT 语句保存成视图，这样就不用每次都重新书写了
第三个优点是视图中的数据会随着原表的变化自动更新

5.1.1创建单一视图

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

SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列
顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的
第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。视图
的列名在视图名称之后的列表中定义。

定义视图时可以使用任何 SELECT 语句，既可以使用 WHERE 、 GROUP
BY 、 HAVING ，也可以通过 SELECT * 来指定全部列。

CREATE VIEW productSum (product_type,cnt_product)AS
SELECT product_type,COUNT(*)FROM product
GROUP BY product_type;

5.1.2使用视图

视图的使用也是用select进行的，但是from子句后面带着的是视图名称

SELECT product_type,cnt_product
FROM productSum;

5.1.3创建多重视图

什么叫多重视图呢？就是以视图为基础创建的视图(视图的视图)
虽然语法上没有错误，但是我们还是应该尽量避免在视图的基础上创
建视图。这是因为对多数DBMS来说，多重视图会降低SQL的性能。

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;

5.1.4视图的限制

• 视图的限制① ——有些数据库定义视图时不能使用 ORDER BY 子句

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

• 视图的限制② ——对视图进行更新
  这个不怎么用，所以就不介绍了

5.1.5视图的删除

DROP VIEW 视图名称;

5.2 子查询

一言以蔽之，子查询就是一次性视图（ SELECT 语句）。与视图不同，子查
询在 SELECT 语句执行完毕之后就会消失。
由于子查询需要命名，因此需要根据处理内容来指定恰当的名称。
标量子查询就是只能返回一行一列的子查询。

5.2.1子查询和视图

视图并不是用来保存数据的，而是通过保存读取数据的 SELECT 语句的方法来为用户提供便利。反之，子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。

--  根据商品种类统计商品数量的视图
CREATE VIEW ProductSum (product_type, cnt_product)ASSELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
--  确认创建好的视图
SELECT product_type, cnt_product
FROM ProductSum;


--  相同功能的子查询
子查询作为内层查询会首先执行。
SELECT *
FROM (SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type
ORDER BY product_type) AS productSum; #注意在Oracle，AS不能要

5.2.2增加子查询的层数

子查询的层数没有限制，可以在子查询中嵌套子查询

随着子查询嵌套层数的增加，SQL 语句会变得越来越难读懂，
性能也会越来越差。因此，尽量避免使用多层嵌套的子查询。

SELECT * FROM
 (SELECT * FROM 
  (SELECT product_type, COUNT(*) AS cnt_product
   FROM Product
   GROUP BY product_type
   ORDER BY product_type) AS productSum
 WHERE cnt_product = 4) AS productSum2;

5.3 标量子查询

标量就是单一的意思，在数据库之外的领域也经常使用。
标量子查询就是返回单一值的子查询。
之前所讲的子查询是以表的形式为结果的
由于返回的是单一的值，因此标量子查询的返回值可以用在 = 或者 <> 这样需要单一值的比较运算符之中。这也正是标量子查询的优势所在。

5.3.1在WHERE 子句中使用标量子查询

-- 查询出销售单价高于平均销售单价的商品。
SELECT * 
FROM product
WHERE sale_price > (SELECT AVG(sale_price)FROM product);

5.3.2标量子查询的书写位置

标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可
以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的
地方，无论是 SELECT 子句、 GROUP BY 子句、 HAVING 子句，还是
ORDER BY 子句，几乎所有的地方都可以使用。

SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price) FROM product) AS avg_price
FROM product;


-- 选取出按照商品种类计算出的销售单价高于全部商品的平均销售单价的商品种类
SELECT product_type
FROM product
GROUP BY product_type
HAVING AVG(sale_price) >(
SELECT AVG(sale_price) FROM product);

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

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

5.4 关联子查询

关联子查询会在细分的组内进行比较时使用。
关联子查询和 GROUP BY 子句一样，也可以对表中的数据进行切分。
关联子查询的结合条件如果未出现在子查询之中就会发生错误。

现在有一个需求，即选取出各
商品种类中高于该商品种类的平均销售单价的商品。
那么，我们可以利用关联子查询

SELECT product_name,sale_price,product_type FROM 
product AS p1
WHERE sale_price>(
SELECT AVG(sale_price) AS avg_sale 
FROM product AS p2
WHERE p1.`product_type`=p2.`product_type` #这个是关键句子GROUP BY product_type
);

但是如果要将平均值也显示出来的话

SELECT product_name,sale_price,product_type,avg_sale FROM
(SELECT product_name,product.product_type,sale_price,avg_sale FROM product,(SELECT product_type,AVG(sale_price) AS avg_sale FROM product
GROUP BY product_type) AS product2
WHERE product.`product_type`=product2.product_type) AS product3
WHERE product3.sale_price>product3.avg_sale;

作者：鸣人吃土豆
链接：https://www.jianshu.com/p/172b3cfe0f88