wonderful-sql：(3)复杂一点的查询

教程地址：https://linklearner.com/learn/detail/70

《SQL基础教程（第2版）》：https://www.ituring.com.cn/book/1880

《DataWhale SQL 基础教程》：https://fastly.jsdelivr.net/gh/datawhalechina/wonderful-sql@main/materials/SQL%E5%9F%BA%E7%A1%80%E6%95%99%E7%A8%8B-V2.0.pdf

wonderful-sql：https://github.com/datawhalechina/wonderful-sql

这是笔者参与开源学习《wonderful-sql》的学习记录，希望通过分享自己的学习过程能够帮助到零基础的同学快速入门 SQL。

本次学习借助开源学习社区 DataWhale，不仅有优秀的助教辅导，还有优秀的小伙伴一起学习，计划大概如下：

前言

从学习群的打卡反馈来看，这次的 Task03 是一个分水岭，毕竟随着课程的深入，难度也有所提升，说实话，以笔者新手小白的水平，过了一遍教程，发现开始有些概念是第一次接触，比如视图(和我们之前接触过的东东不太一样) 、子查询(一个关联子查询就把笔者整蒙蔽了)、谓词等。因此本次的学习笔记优先做习题，通过习题再反推学习知识点。废话不多说，开整吧！

习题1

题目：

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

- 条件 1：销售单价大于等于 1000 日元。
- 条件 2：登记日期是 2009 年 9 月 20 日。
- 条件 3：包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。

`
SELECT * FROM ViewPractice5_1;
`

执行结果

product_name | sale_price | regist_date
--------------+------------+------------
T恤衫         | 　 1000    | 2009-09-20
菜刀          |    3000    | 2009-09-20

知识点

首先，我们来看看题目中提到的视图是什么？用一句概括：
视图不是表，视图是虚表，视图依赖于表。
怎么理解呢？我们把真实的表称为基础表，视图是虚拟表，它是由一个或多个表的数据衍生而来的，不包含实际的数据，仅存储SQL语句。视图允许我们以表的形式查询或更新数据，而无需直接访问基础表。
那么为什么要使用视图？笔者的理解是便捷、安全，因为使用视图有很多好处：

1. 简化复杂查询：视图可以将复杂的查询逻辑封装在一个虚拟表中，让用户只需关注简单的表结构，方便查询和使用。

2. 数据安全：通过视图，可以隐藏敏感数据，只允许用户访问他们需要的数据。这样可以有效保护数据安全性。

3. 数据抽象：通过视图，可以隐藏底层表结构的变化，使应用程序与数据库解耦，提供更好的数据抽象层。

4. 重用查询逻辑：如果某个查询在多个地方被使用，将其定义为视图，可以避免在每个地方都重复编写相同的查询逻辑。

5. 性能优化：数据库优化器可以对视图和查询进行优化，提高查询性能。

视图增删查改的基本语法：

1. 创建视图：

CREATE VIEW <视图名称> AS
SELECT <列名1>,<列名2>,... 
FROM <表名>
WHERE <条件表达式>;

2. 查询视图：

SELECT <列名1>,<列名2>,... 
FROM  <视图名称>
WHERE <条件表达式>;

3. 更新视图：

-- 更新视图可能会影响基础表
ALTER VIEW <视图名> AS <SELECT语句>;

4. 删除视图：

-- 需要有相应的权限才能成功删除
DROP VIEW <视图名>;

5. 删除视图数据：

-- 删除视图中的数据可能会影响基础表
DELETE FROM  <视图名>
WHERE <条件表达式>;

解题

结合上述知识点，我们通过以下SQL语句实现查找结果：

CREATE VIEW ViewPractice5_1 AS  -- 创建视图
SELECT product_name, sale_price, regist_date -- 选择字段
FROM product
WHERE sale_price >= 1000 AND regist_date = '2009-09-20'; -- 筛选条件

SELECT * FROM ViewPractice5_1; -- 查询视图数据

习题2

题目：

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

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

知识点

题目中提到向视图中插入数据，当尝试向一个视图中插入数据时，数据库管理系统 (DBMS) 将会根据视图的定义来确定是否允许进行插入操作。视图是从一个或多个基本表中派生而来的虚拟表，它是一个动态查询结果的展示。视图本身并不存储数据，而是基于基本表的数据进行实时计算和筛选得到的。一般来说，当我们通过INSERT语句向视图插入数据时 DBMS 将会执行以下步骤：

1. 分析 INSERT 语句并检查目标表是否为一个视图。
2. 检查视图是否满足以下条件：

• 视图的定义必须是单表查询，不能包含多个基本表。
• 视图的定义不能包含聚合函数（如 SUM、COUNT 等）或 GROUP BY 子句。
  此外，还可能受基础表字段限制条件，比如字段不为空、类型为整型等等。

解题

会报错:

ERROR 1423 (HY000): Field of view 'shop.viewpractice5_1' underlying table doesn't have a default value

大意是视图’shop.viewpractice5_1’基本表的字段没有默认值，因为我们创建基本表 product 时定义了 product_id、product_type NOT NULL，此时插入语句中默认这个两个字段是 NULL，因此无法插入成功。

常见报错信息;https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

习题3

题目：

请根据如下结果编写 SELECT 语句，其中 sale_price_avg 列为全部商品的平均销售单价。
+------------+--------------+--------------+------------+----------------+
| product_id | product_name | product_type | sale_price | sale_price_avg |
+------------+--------------+--------------+------------+----------------+
| 0001       | T恤衫        | 衣服         |       1000 |      2097.5000 |
| 0002       | 打孔器       | 办公用品     |        500 |      2097.5000 |
| 0003       | 运动T恤      | 衣服         |       4000 |      2097.5000 |
| 0004       | 菜刀         | 厨房用具     |       3000 |      2097.5000 |
| 0005       | 高压锅       | 厨房用具     |       6800 |      2097.5000 |
| 0006       | 叉子         | 厨房用具     |        500 |      2097.5000 |
| 0007       | 擦菜板       | 厨房用具     |        880 |      2097.5000 |
| 0008       | 圆珠笔       | 办公用品     |        100 |      2097.5000 |
+------------+--------------+--------------+------------+----------------+

知识点

这里涉及到子查询这个知识点，当涉及到SQL查询的复杂性时，子查询是一种非常有用的技术。子查询是在主查询内部嵌套的一个完整的SQL查询。它允许我们在主查询中使用嵌套的查询语句来检索数据，并根据内部查询的结果进行进一步的操作。子查询常用于过滤条件、计算、嵌套查询等场景，它可以帮助我们处理复杂的查询需求。
基本语法：

SELECT <列名1>,<列名2>,...
FROM <表名>
WHERE <列名> <操作> (SELECT <列名> FROM <表名> WHERE <条件>);

常见的有嵌套子查询、标量子查询、关联子查询。

解题

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

当然，还可以使用窗口函数来实现，窗口函数是一种SQL函数，它允许我们在查询的结果集上执行聚合操作，而不会合并行。它使用特定的窗口来确定在计算聚合时应该考虑哪些行。窗口函数通常涉及使用 OVER() 子句来定义窗口的范围，例如计算前几行、后几行、所有行的聚合值等。窗口函数的一个常见用例是计算移动平均值或累积总和。

SELECT
  product_id,
  product_name,
  product_type,
  sale_price,
  AVG(sale_price) OVER() AS sale_price_avg
FROM product;

习题4

题目：

请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。
+------------+--------------+--------------+------------+---------------------+
| product_id | product_name | product_type | sale_price | sale_price_avg_type |
+------------+--------------+--------------+------------+---------------------+
| 0001       | T恤衫        | 衣服         |       1000 |           2500.0000 |
| 0002       | 打孔器       | 办公用品     |        500 |            300.0000 |
| 0003       | 运动T恤      | 衣服         |       4000 |           2500.0000 |
| 0004       | 菜刀         | 厨房用具     |       3000 |           2795.0000 |
| 0005       | 高压锅       | 厨房用具     |       6800 |           2795.0000 |
| 0006       | 叉子         | 厨房用具     |        500 |           2795.0000 |
| 0007       | 擦菜板       | 厨房用具     |        880 |           2795.0000 |
| 0008       | 圆珠笔       | 办公用品     |        100 |            300.0000 |
+------------+--------------+--------------+------------+---------------------+

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

知识点

这里主要强调一下关联子查询，关联子查询是一种特殊的子查询类型，它在子查询中引用了主查询的列，用于连接主查询和子查询的结果。
关联子查询的执行逻辑完全不同于正常的SELECT语,首先执行不带WHERE的主查询,根据主查询讯结果匹配条件，获取子查询结果，将子查询结果再与主查询结合执行完整的SQL语句。

解题

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_type) AS sale_price_avg_type
FROM
  product p1;

我们使用了子查询和关联子查询的组合来计算每个商品种类的平均销售单价，并将其与主查询中的每个商品一起显示。执行逻辑：

1. 主查询：我们product 的表中选择了product_id、product_name、product_type 和 sale_price 列。
2. 子查询：在每一行主查询中，我们使用子查询来计算对应商品种类的平均销售单价。在子查询中，我们使用 product 表的别名 p2 来计算每个商品种类的平均销售单价。我们使用 WHERE p1.product_type = p2.product_type 子句来连接主查询和子查询的结果，以确保只计算相同种类的商品的平均销售单价。
3. 执行过程：对于主查询的每一行数据，关联子查询都会执行一次。子查询根据每行主查询的 product_type 值，计算与该商品种类相对应的平均销售单价。然后，子查询的结果作为sale_price_avg_type 的列添加到主查询的结果中。
4. 结果：最终，查询返回了每个商品的详细信息以及对应商品种类的平均销售单价。由于关联子查询的存在，每个商品的平均销售单价都会根据其所属的种类进行计算，因此每行的 sale_price_avg_type 列的值都是不同的。

同样的，我们也可以通过窗口函数来实现，看上去更简单了：

CREATE VIEW AvgPriceByType AS
SELECT
  product_id,
  product_name,
  product_type,
  sale_price,
  AVG(sale_price) OVER (PARTITION BY product_type) AS sale_price_avg_type
FROM
  product
ORDER BY product_id;

我们使用窗口函数 AVG(sale_price) OVER (PARTITION BY product_type) 来计算每个商品种类的平均销售单价。

• PARTITION BY product_type 子句将数据按照商品种类进行分区，确保窗口函数在每个商品种类内部进行计算。
• AVG(sale_price) 函数用于计算每个分区（即每个商品种类）内 sale_price 列的平均值。

习题5

题目：

四则运算中含有 NULL 时（不进行特殊处理的情况下），运算结果是否必然会变为NULL ？

知识点

题目中提到的四则运算对应的知识点为SQL 函数，

MySQL 函数列表(以8.0未例)：https://dev.mysql.com/doc/refman/8.0/en/functions.html

函数是 SQL 中用于执行特定操作的代码块。一些常见的函数：

• 算术函数：用于执行算术运算，如加法、减法、乘法和除法。
• 字符串函数：用于操作字符串，如连接（CONCAT）、查找（FIND_IN_SET）、替换（REPLACE）和截取（SUBSTRING ）。
• 日期和时间函数：用于操作日期和时间，如当前日期、当前时间、计算日期差和格式化日期。
• 转换函数：用于将一种数据类型转换为另一种数据类型，如将字符串转换为日期、将日期转换为字符串等。
• 聚合函数：用于对一组值执行聚合操作，如 SUM、AVG、COUNT 和 MAX。

解题

当进行四则运算时，如果任何一个操作数为 NULL，通常会导致运算结果为 NULL。这是由于在 SQL 中，NULL 代表缺失或未知的值，而在标准的算术运算中，需要所有操作数都有一个确定的值才能进行运算。如果其中一个操作数是未知的，那么运算结果也将是未知的，因此结果会被视为 NULL。

-- 加法
SELECT 5 + NULL; -- 结果为 NULL
-- 减法
SELECT 10 - NULL; -- 结果为 NULL
-- 乘法
SELECT 3 * NULL; -- 结果为 NULL
-- 除法
SELECT 100 / NULL; -- 结果为 NULL

如果我们希望在运算中处理 NULL 值并得到预期的结果，可以使用 COALESCE() 或 NULLIF() 等函数来处理 NULL 值。

SELECT COALESCE(column_name, default_value) FROM table_name;
-- 或者
SELECT NULLIF(column_name, value_to_replace) FROM table_name;

习题6

题目：

对本章中使用的 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);

知识点

谓词就是返回值为真值的函数，包括TRUE / FALSE / UNKNOWN。SQL谓词可以是各种条件表达式，包括比较运算符（如等于、大于、小于等）、逻辑运算符（如AND、OR、NOT）、LIKE(用于字符串的部分一致查询) IN(OR的简便用法)、BETWEEN(用于范围查询) 等

当查询条件中存在 NULL 值时，会导致整个查询的结果为空，即使在数据表中存在符合其他非 NULL 条件的行，这是由于在 SQL 中，与 NULL 相比较的结果是未知的（UNKNOWN）。当我们在查询条件中使用 NOT IN 或 IN 条件时，数据库引擎会将每个值与查询条件中的列表进行比较，如果值存在于列表中，返回结果为 TRUE，否则返回结果为 FALSE。然而，当与 NULL 进行比较时，结果将是 UNKNOWN，而不是 TRUE 或 FALSE。

解题

① 中返回的结果：

+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 打孔器       |            320 |
| 擦菜板       |            790 |
+--------------+----------------+
2 rows in set (0.00 sec)

② 中返回的结果：

Empty set (0.00 sec)

这是因为WHERE purchase_price NOT IN (500, 2800, 5000, NULL);当purchase_price与 NULL 进行比较时，结果将是 UNKNOWN，而不是 TRUE 或 FALSE, 因此查询不到数据。

习题7

题目：

按照销售单价( sale_price )对练习 3.6 中的 product（商品）表中的商品进行如下分类。

- 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
- 中档商品：销售单价在1001日元以上3000日元以下（菜刀）
= 高档商品：销售单价在3001日元以上（运动T恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。

执行结果

low_price | mid_price | high_price
----------+-----------+------------
        5 |         1 |         2

知识点

CASE 表达式类似编程语言中的条件分支。分为简单CASE表达式和搜索CASE表达式，其语法类似。
语法：

CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     .
     .
     .
ELSE <表达式>
END  

解题

SELECT
  COUNT(CASE WHEN sale_price <= 1000 THEN 1 END) AS low_price,
  COUNT(CASE WHEN sale_price > 1000 AND sale_price <= 3000 THEN 1 END) AS mid_price,
  COUNT(CASE WHEN sale_price > 3000 THEN 1 END) AS high_price
FROM
  product;

后记

截止此时此刻离打卡通道关闭不到两小时，可喜可贺的是只有20余人还没完成本次 Task03 的学习打卡，希望大家都能坚持到最后。课程学习刚好到一半了，笔者认为自己对 SQL 有了初步的了解，简单的 SQL 已经铭记于心，多敲多练多悟！接下来的课程难度更上一层楼，希望自己能继续坚持！