SQL入门-视图和子查询

SQL入门-视图和子查询

3.1 视图

3.1.1 什么是视图

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。

3.1.2 视图与表有什么区别

视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上，视图与表的区别—“是否保存了实际的数据”。

3.1.3 为什么会存在视图

那既然已经有数据表了，为什么还需要视图呢？主要有以下几点原因：

1. 通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
2. 通过定义视图可以使用户看到的数据更加清晰。
3. 通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
4. 通过定义视图可以降低数据的冗余。

3.1.4 如何创建视图

语法：

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

其中SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。而且视图的列名是在视图名称之后的列表中定义的。
需要注意的是视图名在数据库中需要是唯一的，不能与其他视图和表重名。

视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。

虽然在视图上继续创建视图的语法没有错误，但是我们还是应该尽量避免这种操作。这是因为对多数 DBMS 来说， 多重视图会降低 SQL 的性能。

注意事项

需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句。

3.1.5 如何修改视图结构

语法 :

ALTER VIEW <视图名> AS <SELECT语句>

3.1.6 如何更新视图内容

因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

• 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
• DISTINCT 关键字。
• GROUP BY 子句。
• HAVING 子句。
• UNION 或 UNION ALL 运算符。
• FROM 子句中包含多个表。

视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么 视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了

3.1.7 如何删除视图

删除视图的基本语法如下：

DROP VIEW <视图名1> [ , <视图名2> …]

注意：需要有相应的权限才能成功删除。

• 删除视图

我们删除刚才创建的productSum视图

DROP VIEW productSum;

如果我们继续操作这个视图的话就会提示当前操作的内容不存在。

3.2 子查询

3.2.1 什么是子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

3.2.2 子查询和视图的关系

子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。其中AS studentSum可以看作是子查询的名称，而且由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中， 而是在 SELECT 语句执行之后就消失了。

3.2.3 嵌套子查询

与在视图上再定义视图类似，子查询也没有具体的限制，例如我们可以这样

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;

其中最内层的子查询我们将其命名为productSum，这条语句根据product_type分组并查询个数，第二层查询中将个数为4的商品查询出来，最外层查询product_type和cnt_product两列。
虽然嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。

3.2.4 标量子查询

标量就是单一的意思，那么标量子查询也就是单一的子查询，那什么叫做单一的子查询呢？

所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。例如我们有下面这样一张表

product_id | product_name | sale_price 
------------+-------------+----------
0003       | 运动T恤       | 4000 
0004       | 菜刀          | 3000 
0005       | 高压锅        | 6800

那么我们执行一次标量子查询后是要返回类似于，“0004”，“菜刀”这样的结果。

3.2.5 标量子查询有什么用

例子：

1. 查询出销售单价高于平均销售单价的商品
2. 查询出注册日期最晚的那个商品

答案1：

SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);

答案2：

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

从例子上可以看出标量子查询是把结果作为一种标量，它可以用在WHERE ，AS，GROUP BY 子句、HAVING 子句， ORDER BY 子句中。

3.2.6 关联子查询

关联子查询是对子查询进行关联查询，有点类似for 循环，总结如下：

1. 首先执行不带WHERE的主查询，以主查询的结果递归给子查询。
2. 子查询根据主查询讯结果匹配product_type，获取子查询结果。
3. 将子查询结果再与主查询结合执行完整的SQL语句，完成循环的一次结果
4. 重复第一次动作继续循环。

具体分析看博文https://zhuanlan.zhihu.com/p/41844742?spm=5176.12282029.0.0.268d11b7VAFsyH，这种分析思路很好。

练习题

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

product_id | product_name | product_type | sale_price | sale_price_all
------------+-------------+--------------+------------+---------------------
0001       | T恤衫         | 衣服         | 1000       | 2097.5000000000000000
0002       | 打孔器        | 办公用品      | 500        | 2097.5000000000000000
0003       | 运动T恤       | 衣服          | 4000      | 2097.5000000000000000
0004       | 菜刀          | 厨房用具      | 3000       | 2097.5000000000000000
0005       | 高压锅        | 厨房用具      | 6800       | 2097.5000000000000000
0006       | 叉子          | 厨房用具      | 500        | 2097.5000000000000000
0007       | 擦菜板        | 厨房用具       | 880       | 2097.5000000000000000
0008       | 圆珠笔        | 办公用品       | 100       | 2097.5000000000000000

答案：

SELECT
	product_id,
	product_name,
	product_type,
	sale_price,
	(
		SELECT
			avg(sale_price)
		FROM
			product
	) AS sale_price_al
FROM
	product

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

product_id | product_name | product_type | sale_price | avg_sale_price
------------+-------------+--------------+------------+---------------------
0001       | T恤衫         | 衣服         | 1000       |2500.0000000000000000
0002       | 打孔器         | 办公用品     | 500        | 300.0000000000000000
0003       | 运动T恤        | 衣服        | 4000        |2500.0000000000000000
0004       | 菜刀          | 厨房用具      | 3000        |2795.0000000000000000
0005       | 高压锅         | 厨房用具     | 6800        |2795.0000000000000000
0006       | 叉子          | 厨房用具      | 500         |2795.0000000000000000
0007       | 擦菜板         | 厨房用具     | 880         |2795.0000000000000000
0008       | 圆珠笔         | 办公用品     | 100         | 300.0000000000000000

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

分析：

        1.关键列avg_sale_price求的是product_type列的数据分类之后的平均值。

        2.第一题是求的所有商品的总平均值，本题是商品分类平均值，那么就需要对分类进行关联求平均值了，也就是要用到关联子查询。

 解题：

        1.先根据题目结果写出主查询

SELECT
	product_id,
	product_name,
	product_type,
	sale_price,
	avg_sale_price
FROM
	product

         2.写出子查询，即求avg_sale_price的值

SELECT
	product_id,
	product_name,
	product_type,
	sale_price,
	(
		SELECT
			avg(sale_price)
		FROM
			product AS p2
		WHERE
			p1.product_type = p2.product_type
	) AS avg_sale_price
FROM
	product AS p1

答案：

SELECT
	product_id,
	product_name,
	product_type,
	sale_price,
	(
		SELECT
			avg(sale_price)
		FROM
			product AS p2
		WHERE
			p1.product_type = p2.product_type
		GROUP BY
			p1.product_type
	) AS avg_sale_price
FROM
	product AS p1

这里的group by 可以去掉，用处不大的样子。

从这题看出，关联子查询类似for 循环，先写好外层循环，再填内层循环，就可以很清晰的解题了。