SQL：查询【下】

参考资料：SQL之母 - SQL自学网站 (yupi.icu)

16. SELECT SUM(score) AS total_score, AVG(score) AS avg_score, MAX(score) AS max_score, MIN(score) AS min_score FROM student

    聚合函数:汇总计算

    • COUNT：计算指定列的行数或非空值的数量。
    • SUM：计算指定列的数值之和。
    • AVG：计算指定列的数值平均值。
    • MAX：找出指定列的最大值。
    • MIN：找出指定列的最小值。

17. SELECT class_id, AVG(score) AS avg_score FROM student GROUP BY class_id

    分组聚合：单字段

    这个应用的时候有点不好理解。主打一个按照类别分别汇总

    统计学生表中的班级编号（class_id）和每个班级的平均成绩（avg_score）

18. select class_id,exam_num,count(id) as total_num from student group by class_id,exam_num

    分组聚合 - 多字段分组

19. select class_id,sum(score) as total_score from student group by class_id having sum(score) >150

    HAVING 子句过滤

    • 用于在分组聚合后对分组进行过滤

    • HAVING 子句与条件查询 WHERE 子句的区别在于，WHERE 子句用于在 分组之前 进行过滤，而 HAVING 子句用于在 分组之后 进行过滤。

    通俗的说，就是使用group by后还需要使用where的地方把where变成having

20. select s.name as student_name,s.age as student_age,s.class_id, c.name as class_name from student s cross join class c

    关联查询 - cross join

    一种用于联合多个数据表中的数据的查询方式

    CROSS JOIN 是一种简单的关联查询，不需要任何条件来匹配行，它直接将左表的 每一行 与右表的 每一行 进行组合，返回的结果是两个表的笛卡尔积

21. select s.name student_name,s.age student_age,s.class_id,c.name class_name,c.level class_level from student s join class c on s.class_id = c.id

    关联查询 - inner join

    根据两个表之间的关联条件，将满足条件的行组合在一起。

    INNER JOIN 只返回两个表中满足关联条件的交集部分，即在两个表中都存在的匹配行。

    用法：select巴拉巴拉 join 表1 on 表2 on 表1.共同列名=表2.共同列名

22. select s.name student_name,s.age student_age,s.class_id,c.name class_name,c.level class_level from student s left join class c on s.class_id = c.id

    关联查询 - outer join

    根据指定的关联条件，将两个表中满足条件的行组合在一起，并 包含没有匹配的行 。

    • 查询左表或右表的所有行（即使没有被匹配），再加上满足条件的交集部分

      • LEFT OUTER JOIN 左表

        • 通俗的说，就是1、把左表右表匹配的部分及匹配的信息找出来2、把左表在右表没匹配到的信息也显示出来，需要查询的右表信息（即，没匹配到的）显示NULL

      • RIGHT OUTER JOIN 右表

23. select name,score,class_id from student where class_id in(select id from class**)**

    子查询

    在一个查询语句内部 嵌套 另一个完整的查询语句，内层查询被称为子查询。子查询可以用于获取更复杂的查询结果或者用于过滤数据。

    当执行包含子查询的查询语句时，数据库引擎会首先执行子查询，然后将其结果作为条件或数据源来执行外层查询。

    例：查询出订单总金额 > 200 的客户的姓名和他们的订单总金额

    -- 主查询
    SELECT name, total_amount
    FROM customers
    WHERE customer_id IN (
        -- 子查询
        SELECT DISTINCT customer_id
        FROM orders
        WHERE total_amount > 200
    );
    

24. select name,age,class_id from student where not exists (select class_id from class where class.id = student.class_id)

    子查询 - exists

    可以嵌套在主查询中，帮助我们进行更复杂的条件过滤和数据检索。

    子查询中的一种特殊类型是 “exists” 子查询，用于检查主查询的结果集是否存在满足条件的记录，它返回布尔值（True 或 False），而不返回实际的数据。

    not exists，用于查找不满足存在条件的记录

    例如：查询出 存在订单的 客户姓名和订单金额

    # 先遍历客户信息表的每一行，获取到客户编号；然后执行子查询，从订单表中查找该客户编号是否存在，如果存在则返回结果。
    -- 主查询
    SELECT name, total_amount
    FROM customers
    WHERE EXISTS (
        -- 子查询
        SELECT 1   # 为什么这里是select 1啊
        FROM orders
        WHERE orders.customer_id = customers.customer_id
    );
    

25. select name,age,score,class_id from student union all select name,age,score,class_id from student_new

    组合查询

    一种将多个 SELECT 查询结果合并在一起的查询操作。（合并两张表的数据，一般是同样的表不同版本，用来更新数据）

    • 两种常见的组合查询操作
      • UNION 操作：它用于将两个或多个查询的结果集合并， 并去除重复的行 。即如果两个查询的结果有相同的行，则只保留一行。
      • UNION ALL 操作：它也用于将两个或多个查询的结果集合并， 但不去除重复的行 。即如果两个查询的结果有相同的行，则全部保留。

26. select id,name,age,score,class_id,avg(score) over(partition by class_id) as class_avg_score from student

    开窗函数 - sum over

    开窗函数是一种强大的查询工具，它允许我们在查询中进行对分组数据进行计算、 同时保留原始行的详细信息 。

    开窗函数可以与聚合函数（如 SUM、AVG、COUNT 等）结合使用，但与普通聚合函数不同，开窗函数不会导致结果集的行数减少。

    打个比方，可以将开窗函数想象成一种 “透视镜”，它能够将我们聚焦在某个特定的分组，同时还能看到整体的全景。

    • 分类：
      • 总和sum over ： SUM(计算字段名) OVER (PARTITION BY 分组字段名**)**
      • 平均数avg over:用法相同

    举例：

    1、计算每个客户的订单总金额，并显示每个订单的详细信息。

    SELECT 
        order_id, 
        customer_id, 
        order_date, 
        total_amount,
        SUM(total_amount) OVER (PARTITION BY customer_id) AS customer_total_amount
        # 注意这里想要计算每个顾客id的总金额，所以BY customer_id
    FROM
        orders;
    

    

    2、计算每个班级的学生平均分（class_avg_score），并返回每个学生的详细信息。

    上述本题代码。# 注意这里想要计算每个班级id的平均分，所以BY class_id（尽管分数是在学生id下，但是分数也在班级id下）

27. select id,name,age,score,class_id,sum(score) over(partition by class_id order by score ASC) as class_sum_score from student

    开窗函数 - sum over order by

    实现同组内数据的 累加求和 。

    用法：SUM(计算字段名) OVER (PARTITION BY 分组字段名 ORDER BY 排序字段 排序规则)

    作用：对比26和本题这两张结果图，可以发现其将顾客下订单及累加金额的过程显示了出来。

    

28. select id,name,age,score,class_id,rank() over (partition by class_id order by score DESC) as ranking from student

    开窗函数 - rank

    一种用于对查询结果集中的行进行 排名 的开窗函数。

    • 它可以根据指定的列或表达式对结果集中的行进行排序，并为每一行分配一个排名。在排名过程中，相同的值将被赋予相同的排名，而不同的值将被赋予不同的排名。

    • 当存在并列（相同排序值）时，Rank 会跳过后续排名，并保留相同的排名。

    • Rank 开窗函数的常见用途是在查询结果中查找前几名（Top N）或排名最高的行。

    Rank 开窗函数的语法如下：

    RANK() OVER (
    PARTITION BY 列名1, 列名2, … – 可选，用于指定分组列
    ORDER BY 列名3 [ASC|DESC], 列名4 [ASC|DESC], … – 用于指定排序列及排序方式
    ) AS rank_column

    其中，PARTITION BY 子句可选，用于指定分组列，将结果集按照指定列进行分组；ORDER BY 子句用于指定排序列及排序方式，决定了计算 Rank 时的排序规则。AS rank_column 用于指定生成的 Rank 排名列的别名。

    ❗注意：rank() over这里的()里不用填东西，和sum等用法区分开

    区别:

    • 错误的写法:partition by id

      

    • 正确的写法:partition by class_id

      

29. select id,name,age,score,class_id,row_number() over (partition by class_id order by score desc) as row_number from student

    开窗函数 - row_number

    一种用于为查询结果集中的每一行分配唯一连续排名 的开窗函数。

    • 它与之前讲到的 Rank 函数，Row_Number 函数为每一行都分配一个唯一的整数值，不管是否存在并列（相同排序值）的情况。

    • 每一行都有一个唯一的行号，从 1 开始连续递增。

    Row_Number 开窗函数的语法如下（几乎和 Rank 函数一模一样）：

    ROW_NUMBER() OVER (
    PARTITION BY column1, column2, … – 可选，用于指定分组列
    ORDER BY column3 [ASC|DESC], column4 [ASC|DESC], … – 用于指定排序列及排序方式
    ) AS row_number_column

    其中，PARTITION BY子句可选，用于指定分组列，将结果集按照指定列进行分组。ORDER BY 子句用于指定排序列及排序方式，决定了计算 Row_Number 时的排序规则。AS row_number_column 用于指定生成的行号列的别名。

    作用对比:当出现分数相同时,不会给同一序号,一般用于编号使用(例如入学学号),而rank是为了排名公平。

30. select id,name,age,score,class_id,lag(name,1,NULL) over (partition by class_id order by score desc) as prev_name,lead(name,1,NULL) over (partition by class_id order by score desc) as next_name from student

    开窗函数 - lag / lead

    获取在当前行之前或之后的行的值，这两个函数通常在需要比较相邻行数据或进行时间序列分析时非常有用。

    1. Lag 函数

    Lag 函数用于获取 当前行之前 的某一列的值。它可以帮助我们查看上一行的数据。

    LAG(column_name, offset**,** default_value**) OVER (PARTITION BY partition_column ORDER BY sort_column)**

    2. Lead 函数

    Lead 函数用于获取 当前行之后 的某一列的值。它可以帮助我们查看下一行的数据。

    LEAD(column_name, offset, default_value**) OVER (PARTITION BY partition_column ORDER BY sort_column)**

    参数解释：

    • column_name：要获取值的列名。
    • offset：表示要向上（lead为向下）偏移的行数。例如，offset为1表示获取上/下一行的值，offset为2表示获取上/下两行的值，以此类推。
    • default_value：可选参数，用于指定当没有前一行时的默认值，比如填NULL。
    • PARTITION BY和ORDER BY子句可选，用于分组和排序数据。

额外关卡

1. 冒险者金币

• select adventurer_id,adventurer_name,sum(reward_coins) as total_reward_coins

  from rewards

  group by adventurer_id,adventurer_name

  order by total_reward_coins desc

  limit 3

• 注意：如果分组聚合时只写了group by adventurer_id，只使用了冒险者的 ID 进行分组，而没有明确指定姓名。这意味着对于每个冒险者 ID，数据库引擎会随机选择一个对应的姓名来显示。这导致显示的结果id和姓名对不上。

2. 魔法学院(类比在班级（科目）中按学生分数排名)

• select student_id,student_name,subject_id,subject_name,score,

  rank() over(partition by subject_id order by score desc) as score_rank

  from magic_scores

3. 大浪淘鸡

• select observer_name,observation_date,wave_intensity

  from chicken_observation

  where observation_location like “%大浪淘鸡%”

  and

  wave_intensity > 5

• 注意：先select再from再进行where筛选，from放在最后会报错

a tip

• 看到“不同”想到DISTINCT