阿里天池SQL训练营task3

目录

视图

子查询

视图

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

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

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

这段话的含义就是，试图其实是数据表的映射，是一种底层基础的存在，并不能包含聚合汇总等操作在里面，可以简单理解为试图就是数据表的简洁模式，存在的意义就是创建了另一张包含所需数据的“数据表”，降低了数据的冗余，合理利用服务器性能。

视图可看做是原表的一个窗口，修改原表内容也只能修改窗口能看到的内容，但不推荐这种方式修改原表数据。

子查询

在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

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

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;

思路：问题是从表中商品种类为4的商品种类名称，因为有聚合的步骤在里边，直接查肯定不好查，所以分两步1.子查询建立聚合值得“数据表”2.从聚合值“数据表”中查询所需信息。

以上思路中存在一个错误，即查询的不是种类为4的商品，而是数量为4的商品种类名称，存在此错误的原因是错误的理解了group by与聚合函数的关系，并非是聚合字段就一定是group by 的分组字段，这里有些类似数据透视表，这些语句的目的只是想查看维度下的度量值为多少。

子查询是从最内层开始执行的（由内而外）

例题

创建出满足下述三个条件的视图（视图名称为 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语句即可。

问题：

当实现某一需求时候，不适用子查询直接写一个查询与使用子查询的区别？

请根据习题一中的条件编写一条 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

标准答案：

CREATE VIEW AvgPriceByType
as
select product_id，product_name，product_type，sale_price，

（select avg（sale_price）from product p2）

    where p1.product——type = p2.product_price

    group by p1.product_price）as avg_sale_price

from product p1；

我的答案：

CREATE VIEW AvgPriceByType
as
select product_id,product_name,product_type,sale_price,avg_sale_price
  from product
  group by product_type;

解答：

差别还是很大的，看图片可知，是需要得到没类商品下每种商品的具体情况，而如果不使用子查询的话，就会直接得到以product_type聚合的结果，这里的平均价格需要用子查询作为一个常量。

like：前方一致，后方一致，中间一致。

%：代表1个或多个字符。

_：匹配1个字符。

between：闭区间

<>：开区间

选取空值不可以用 = 要用is，选取null以外的用 is not null

or：选取多个条件，当条件很多时用in（），否定形式用not in（）

IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。子查询就是 SQL内部生成的表，因此也可以说“能够将表作为 IN 的参数”。同理，我们还可以说“能够将视图作为 IN 的参数”。

EXIST 通常会使用关联子查询作为参数。由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件“商店编号（shop_id）为 '000C'，商品（product）表和商店商品（shopproduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才返回真（TRUE）。

因此，使用下面的查询语句，查询结果也不会发生变化。

SELECT product_name, sale_price FROM product AS p WHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数 FROM shopproduct AS sp WHERE sp.shop_id = '000C' AND sp.product_id = p.product_id);

当利用case表达式进行行专列操作时候，注意

• 当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数；
• 当待转换列为文本时，可以使用MAX MIN等聚合函数

一定会有个group by函数，因为行转列本质上就是多行记录的聚合，所以对应的在case表达式中，需要用sum、avg、max等聚合函数与之呼应。

例题：

对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？

①

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000);

②

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000, NULL);

谓词无法与null进行比较，第二个查询结果为空。