SQL操作指南四（复杂查询）

视图

• 视图和表
  从SQL的角度看视图其实就是一张表，在SQL语句中并不需要区分哪些是表，哪些是视图。
  视图和表的唯一区别就是：是否保存了实际的数据。
  表的数据通常保存在硬盘中，在我们调用数据的时候，SELECT从硬盘中读取数据；但是视图并不会将数据保存到硬盘中，而且也不会保存到任何地方，我们从视图中读取数据时，视图会执行SQL语句并创建一张临时表。

视图的两大优点：
1.由于视图无需保存数据，因此可以节省存储设备的容量；
2.可以将频繁使用的SELECT语句保存成视图，这样就不用每次都书写了（所以应该将常用的SELECT语句做成视图）；

• 创建视图的方法（CREATE VIEW）
  创建视图的语法：
  CREATE VIEW 视图名称 (<视图列名1>,<视图列名2>,......)
AS
<SELECT语句>
  使用视图的查询
  在FROM子句中使用视图查询，通常有如下两个步骤：
  1.首先执行定义视图的SELECT子句；
  2.根据所得的结果，再执行再FROM子句中使用的SELECT语句；

视图可以再视图的基础上再次创建视图，如图：

但是，多重视图会降低SQL的性能，因此尽量避免在视图的基础上创建视图。

视图的限制
1.定义视图时不能使用ORDER BY子句：因为视图和表的属性是相同的，即数据行是无序的；
2.对视图进行更新：如果定义视图的SELECT语句能够满足某些条件，那么视图就可以被更新：
①SELECT子句中未使用DISTINCT；
②FROM子句中只有一张表；
③未使用GROUP BY子句；
④未使用HAVING子句；
因为视图和表需要同时进行更新，因此通过聚合得到的视图无法进行更新。

• 删除视图
  删除视图需要用到DROP VIEW语句，语法如下：
  DROP VIEW 视图名称(<视图列名1>,<视图列名2>,......)
  例如删除视图“shop_sum”
  DROP VIEW shop_sum;

子查询

子查询的特点概括起来就是一张一次性视图，也就是将用以定义视图的SELECT语句直接用于FROM子句当中。
常规视图语法（示例）：
CREATE VIEW shop_sum(shop_type,sum_shop)
FROM shop_list
GROUP BY shop_type;

SELECT shop_type,sum_shop
FROM shop_sum;

子查询语法（示例）：
SELECT shop_type,sum_shop
FROM (SELECT shop_type,count(*) AS sum_shop FROM shop_list GROUP BY shop_type) AS shop_sum;

两种语法的结果完全相同，子查询作为内层查询会优先执行。
子查询也可以嵌套使用，但是随着子查询的嵌套层数增加，SQL的语句会变得越来越难以读懂，性能也会随之变差，所以我们要尽力避免使用多次嵌套的子查询。

• 子查询的名称
  原则上子查询必须设定名称，为只查询设定名称需要用到关键字“AS”（在Oracle中使用该关键字会发生错误，可以做为例外）。

• 标量子查询
  标量子查询有一个特殊的限制，那就是必须而且只能返回1行1列的结果，即返回单一值的子查询。

在WHERE子句中使用标量子查询
在WHERE子句中，使用聚合函数是错误的，如：
SELECT shop_id,shop_name,price
FROM shop_list
WHERE price>AVG(price);
虽然在语法上看似满足条件，但是在WHERE子句中不能使用聚合函数，所以这样的SELECT语句是错误的。
那么标量子查询就可以解决这类问题了。首先使用聚合函数AVG求出平均值：
SELECT AVG(price)
FROM shop_list;

再将这段代码直接用到之前错误的代码中：
SELECT shop_id,shop_name,price
FROM shop_list
WHERE price>(SELECT AVG(price) FROM shop_list);
这样就能完整地输出正确的结果。

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

关联子查询

• 普通子查询和关联子查询的区别
  普通子查询是以全部数据作为基础，再做聚合函数的运算，而关联子查询是以分组作为基础在做聚合函数的运算。
  使用分类，一般都会用到GROUP BY，但是当我们把GROUP BY加入语句中后，并没有获得正确的输出。

SELECT shop_id,shop_name,sell_price
FROM shop_list
WHERE sell_price>(SELECT AVG(sell_price) 
    FROM shop_list 
    GROUP BY shop_type);

在学习之前的标量子查询的时候，已经知道在WHERE子句中使用子查询，该子句的查询结果必须是单一的。

使用关联子查询解决方案
只需要在上面的SELECT子句中追加一行，就能输出正确的结果了。

SELECT shop_id,shop_name,sell_price
FROM shop_list AS S1
WHERE sell_price>(SELECT AVG(sell_price) 
    FROM shop_list  AS S2
    WHERE S1.shop_type=S2.shop_type
    GROUP BY shop_type);

这里起到关键作用的是子查询中添加的WHERE子句的条件，该条件的意思是：在同一商品种类中各商品的单价和平均单价进行比较。

• 关联子查询也是用来对集合进行切分的
  关联子查询和GROUP BY子句一样，可以对集合进行切分。

  
  根据商品种类对表进行切分
  根据关联子查询进行切分
  

  关联子查询执行时，DBMS内部的执行结果可以查考如下：

  
  关联子查询执行的DBMS的内部运行图

• 结合条件一定要写在子查询中
  在使用关联子查询中时常犯的错误，那就是将关联条件写在子查询之外的外层查询中。

SELECT shop_id,shop_name,sell_price
FROM shop_list as S1
WHERE S1.shop_typle=S2.shop_typle 
AND sell_price>(SELECT AVG(sell_price) 
          FROM shop_list AS S2 
          GROUP BY shop_typle);

因为别名S2的作用域在其定义之后，所以在使用”WHERE S1.shop_typle=S2.shop_typle “时S2还未定义，因此产生定义域的错误。