【从 0 到 1 掌握数据库与 SQL：零基础入门核心知识与实操指南（三）】

6.3 函数

• 运算或函数中含有 NULL 时，结果全都会变为NULL
  6.3.1 算数函数

6.3.2 字符串函数

SUBSTRING_INDEX('www.mysql.com', '.', 2);
www.mysql                                

SUBSTRING_INDEX('www.mysql.com', '.', -2) 
mysql.com      

6.3.3 日期函数

获取当前日期	SELECT CURRENT_DATE;
当前时间	SELECT CURRENT_TIME;
当前日期和时间	SELECT CURRENT_TIMESTAMP;
截取日期元素	EXTRACT(日期元素 FROM 日期)；

6.3.4 转换函数

• 类型转换

CAST（转换前的值 AS 想要转换的数据类型）

• 将NULL转换为其他值

COALESCE(数据1，数据2，数据3……)

6.4 CASE 表达式

6.4.1 简介

CASE 表达式用于实现条件逻辑，类似于编程语言中的 if-else 或 switch 语句。它允许根据条件对数据进行转换、分类或计算。

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

• 依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。无论多么庞大的 CASE 表达式，最后也只会返回一个值。

6.4.2 使用方法

6.4.2.1 根据不同分支得到不同列值

SELECT  product_name,
        CASE WHEN product_type = '衣服' THEN CONCAT('A ： ',product_type)
             WHEN product_type = '办公用品'  THEN CONCAT('B ： ',product_type)
             WHEN product_type = '厨房用具'  THEN CONCAT('C ： ',product_type)
             ELSE NULL
        END AS abc_product_type
  FROM  product;

• ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL
• CASE 表达式最后的“END”是不能省略的，忘记书写 END 会发生语法错误

6.4.2.2 实现列方向上的聚合

SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes,
       SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen,
       SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office
  FROM product;

• CASE WHEN ... THEN ... ELSE ... END：
  条件表达式，对每行数据判断product_type是否等于目标类型。若满足条件，则返回sale_price；否则返回0。
  -SUM()聚合函数：
  对所有行的CASE结果求和。若某类型无匹配记录，对应列返回0（而非NULL）。

6.4.2.3 实现行转列

• 待转换列为数字，用SUM AVG MAX MIN等聚合函数
  转换前
  

SELECT name,
       SUM(CASE WHEN subject = '语文' THEN score ELSE null END) as chinese,
       SUM(CASE WHEN subject = '数学' THEN score ELSE null END) as math,
       SUM(CASE WHEN subject = '外语' THEN score ELSE null END) as english
  FROM score
 GROUP BY name;

转换后

• 待转换列为文本，用MAX MIN等聚合函数

SELECT name,
       MAX(CASE WHEN subject = '语文' THEN subject ELSE null END) as chinese,
       MAX(CASE WHEN subject = '数学' THEN subject ELSE null END) as math,
       MIN(CASE WHEN subject = '外语' THEN subject ELSE null END) as english
  FROM score
 GROUP BY name;
 --结果如下
 +------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 | 语文    | 数学 | 外语    |
| 李四 | 语文    | 数学 | 外语    |
+------+---------+------+---------+

7. 集合运算-表的运算和连结表

在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。

7.1 Bag 模型

bag 模型（也称为 multiset，多重集） 是一种用于描述表结构的数学模型，它解决了传统集合（set）模型无法处理重复元素的问题，更贴合实际数据库中表的特性。
对于两个 bag, 他们的并运算会按照: 1.该元素是否至少在一个 bag 里出现过, 2.该元素在两个 bag 中的最大出现次数 这两个方面来进行计算. 因此对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的并就等于 {1,1,1,2,2,3,4,5,6,7,8}.

7.2 表的运算

7.2.1 表的加法（并运算） UNION

SELECT product_id, product_name
  FROM product
 UNION
SELECT product_id, product_name
  FROM product2;

• UNION 等集合运算符通常都会除去重复的记录.
• 若不想去重，而是在结果中保留重复行，使用 UNION ALL语法
• 假设连锁店想要增加毛利率超过 50%或者售价低于 800 的货物的存货量, 可使用 UNION 对分别满足上述两个条件的商品进行查询，同时也可以使用 OR 来实现

SELECT  product_id,product_name,product_type
       ,sale_price,purchase_price
  FROM product 
 WHERE sale_price<800
  
 UNION
 
SELECT  product_id,product_name,product_type
       ,sale_price,purchase_price
  FROM product 
 WHERE sale_price>1.5*purchase_price;

SELECT  product_id,product_name,product_type
      ,sale_price,purchase_price
 FROM product 
WHERE sale_price < 800 
   OR sale_price > 1.5 * purchase_price;

• UNION 与 OR 区别：UNION 适合将多表结果进行合并，而 OR 适合对单张表进行筛选。同时，有时出于效率的考虑，也会选择 UNION

7.2.2 MySQL 8.0 不支持交运算INTERSECT

集合的交, 就是两个集合的公共部分, 由于集合元素的互异性, 集合的交只需通过文氏图就可以很直观地看到它的意义.截止到 MySQL 8.0 版本, MySQL 仍然不支持 INTERSECT 操作。
对于两个 bag, 他们的交运算会按照: 1.该元素是否同时属于两个 bag, 2.该元素在两个 bag 中的最小出现次数这两个方面来进行计算. 因此对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的交运算结果就等于 {1,1,2}.

7.2.3 MySQL 8.0 不支持减法运算符 EXCEPT

MySQL 8.0 还不支持 表的减法运算符 EXCEPT. 不过, 借助 NOT IN 谓词, 我们同样可以实现表的减法.

SELECT * 
  FROM product
 WHERE product_id NOT IN (SELECT product_id 
                            FROM product2)

7.3 链结 JOIN

笛卡尔积现象：若两张表进行连接查询的时候没有任何条件限制，最终的查询结果总数是两张表记录的成绩，该现象称为笛卡尔积现象。

1. 显示每个学生信息，并显示所属班级名称
   SELECT s.name,c.class_name from student s,class c; #触发笛卡尔积现象
   

7. 3.1 连接查询根据：年代分类

1. SQL92语法（隐式JOIN）：select xxx from A 表名,B表名 where 表连接条件 and 数据查询条件;
SELECT s.name,c.class_name from student s,class c where s.class_num = c.class_num;
   缺点：表连接条件与查询条件放在一起，没有分离
2. SQL99语法（显示JOIN）：select xxx from A 表名 join B 表名 on 表的连接条件；
select student.name,class.class_name from student join class on student.class_num = class.class_num;
   优点：表连接独立，结构清晰，如果结果数据不满足要求，可再追加where条件进行过滤；

7.3.2 连接查询根据：连接方式分类

1. 内连接：只返回两边表都匹配的行。

SELECT  SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROMshopproduct AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id
 WHERE SP.shop_name = '东京' -- 若加where语句对连结后的表进行筛选，需要放在最后
   AND P.product_type = '衣服' ;

• 执行顺序：FROM 子句（生成连结表）->WHERE 子句（按条件筛选）->SELECT 子句（选出所需列）

2. 左外连接 包含左边表的全部行（不管右边的表中是否存在与他们匹配的行），以及右边表中全部匹配的行。

SELECT student.name,class.class_name  from student 
LEFT JOIN class on student.class_num = class.class_num;

3. 右外连接 包含右边表的全部行（不管右边的表中是否存在与他们匹配的行），以及左边表中全部匹配的行。

SELECT student.name,class.class_name  from student 
RIGHT JOIN class on student.class_num = class.class_num;

- 查询出每个学生所选择的课程
SELECT student.name,course.gradeName from student
 join relationship
     on student.sno = relationship.sno
 join course
     on relationship.cno = course.cno  

• 多张表进行表连接得语法格式

select
    xxx
from
    A表
join
    B表
on
    连接条件1
join
    C表
on
    连接条件2     

8. 高级查询：窗口函数

8.1 简介

窗口函数是 SQL 中一种强大的工具，用于在不改变原始表结构的前提下，对查询结果进行分组、排序和计算聚合值。与普通聚合函数（如 SUM()、AVG()）不同，窗口函数不会将多行合并为一行，而是为每一行保留原始记录，并添加计算结果。
窗口函数也称为OLAP函数。OLAP 是OnLine Analytical Processing 的简称，意思是对数据库数据进行实时分析处理。常规的SELECT语句都是对整张表进行查询，而窗口函数可以让我们有选择的去某一部分数据进行汇总、计算和排序。
窗口函数只能在SELECT子句中使用：本质上是因为 SQL 语句的执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY 。如果在 WHERE, GROUP BY, HAVING 使用了窗⼝函数，就是说提前进行了一次排序，排序之后再去除记录、汇总、汇总过滤，第一次排序结果就是错误的，没有实际意义。而 ORDER BY 语句执行顺序在 SELECT 语句之后，自然是可以使用的。
要求 MySQL 8.0 版本以上

• 语法

<窗口函数> OVER ([PARTITION BY <列名>] --[]中的内容可以省略。省略就是针对序列进行全局排列
                     ORDER BY <排序用列名>)  

• PARTITON BY是用来分组，即选择要看哪个窗口，类似于GROUP BY 子句的分组功能，但是PARTITION BY 子句并不具备GROUP BY 子句的汇总功能，并不会改变原始表中记录的行数。
• ORDER BY是用来排序，即决定窗口内，是按那种规则(字段)来排序的。
  SEsLqElCT product_name ,product_type ,sale_price ,RANK() OVER (PARTITION BY product_type ORDER BY sale_price) AS ranking FROM product
  

8.2 窗口函数种类

8.2.1 RANK、DENSE_RANK等排序用的专用窗口函数

RANK 函数（英式排序）

计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
如有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……

DENSE_RANK函数（中式排序）

同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
如有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……

ROW_NUMBER函数

赋予唯一的连续位次。
如有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位

SELECT  product_name
       ,product_type
       ,sale_price
       ,RANK() OVER (ORDER BY sale_price) AS ranking
       ,DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking
       ,ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num
  FROM product  

8.2.2 SUM、MAX、MIN等聚合函数用在窗口函数中

SELECT  product_id
       ,product_name
       ,sale_price
       ,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
       ,AVG(sale_price) OVER (ORDER BY product_id) AS current_avg
       ,MIN(sale_price) OVER (ORDER BY product_id) AS current_min
       ,MAX(sale_price) OVER (ORDER BY product_id) AS current_max
  FROM product; 

8.3 窗口函数应用-计算移动平均

• 语法

<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS n PRECEDING )  --截止到之前 n 行
                 
<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS BETWEEN n PRECEDING AND n FOLLOWING) --截止到之前 n 行以及之后 n 行

SELECT  product_id
       ,product_name
       ,sale_price
       ,AVG(sale_price) OVER (ORDER BY product_id
                               ROWS 2 PRECEDING) AS moving_avg
       ,AVG(sale_price) OVER (ORDER BY product_id
                               ROWS BETWEEN 1 PRECEDING 
                                        AND 1 FOLLOWING) AS moving_avg  
  FROM product 

8.4 GROUPING 运算符 - ROLLUP

• 生成分组小计和总计的高级聚合功能。它能自动创建多层级的汇总数据，简化复杂报表的生成过程。

SELECT  product_type
       ,regist_date
       ,SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type, regist_date WITH ROLLUP 

执行顺序和书写顺序

• SQL执行顺序：

FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

• SQL数学顺序：

SELECT 列名/表达式  
    FROM 表名  
    [JOIN 表名 ON 连接条件]  
    WHERE 行过滤条件  
    GROUP BY 分组列  
    HAVING 分组过滤条件（含聚合）  
    ORDER BY 排序列 [ASC/DESC];