SELECT col_name [ALL|DISTINCT] # COL_NAME 要查询的列名 [ALL|DISTINCT] 是否去重
FROM table_name [ALIAS] # table_name 要查询的表名 [ALIAS] 表的别名
WHERE # 过滤列数据
GROUP BY # 根据字段名进行分组
HAVING # 根据分组的字段名 或 聚合函数生成的字段 进行过滤
ORDER BY # 根据分组的字段名 或 聚合函数生成的字段 进行对查询结果排序 分组条件在前 列名条件在后
LIMIT 2, 18 # 返回从第3行开始的18行记录 注意写法不同生成的结果不同
LIMIT 6 OFFSET 8 # 从数据库表中选择所有列 并限制返回结果的行数为6行 从第9行开始返回结果
LIMIT 5 # 直接返回前5列数据

# 20230503 1
select
   customer_id,
   customer_name
from # 表中的数据本身就是distinct的 所以select字段中无需加d%
    Customers
where # 算法确保customer没买过C && 买过A && 买过B
     customer_id NOT IN (
         select distinct customer_id
         from Orders
         where product_name = 'C'
     ) && customer_id IN (
         select distinct customer_id
         from Orders
         where product_name = 'A'
     ) && customer_id IN (
         select distinct customer_id
         from Orders
         where product_name = 'B'
     )
order by customer_id

select
   student_id,
   MIN(course_id) as course_id,
   grade
from
   Enrollments
WHERE
   # 1 先用where子句过滤出每位同学的最高成绩所有列
   (student_id, grade) IN (
       select student_id, MAX(grade)
       from Enrollments
       group by student_id
   )
# 2 然后按照学生的id和成绩进行分组 
# 为什么要按照两个字段进行分组？因为SELECT字段选择了grade
group by student_id, grade
order by student_id asc

SELECT
   left_operand,  # 左操作数
   operator,      # 操作符
   right_operand, # 右操作数

   (CASE
        WHEN V1.value > V2.value AND operator = '>' THEN 'true'
        WHEN V1.value = V2.value AND operator = '=' THEN 'true'
        WHEN V1.value < V2.value AND operator = '<' THEN 'true'
        ELSE 'false'
        END 
   ) AS value # 计算结果
       
FROM
   # 通过JOIN关键字增广表 -> 构造查询范围
   Expressions E
       # 先把 x、y 的值填进表中（以增广的形式）
       LEFT JOIN Variables V1 ON E.left_operand = V1.name
       LEFT JOIN Variables V2 ON E.right_operand = V2.name

SELECT
   t.team_id,
   t.team_name,
   # 注意这里IFNULL的处理 如果为NULL 则按照0作为字段结果
   IFNULL(a.score, 0) num_points
FROM(
       # 因此 聚合之后 再次根据team_id进行分组 从而求得真正的总分
       SELECT 
           team_id, SUM(score) score
       FROM ( 
           	    # 子表b只包含team_id和总分
                SELECT  host_team team_id,
                        SUM(CASE
                                WHEN host_goals > guest_goals THEN 3
                                WHEN host_goals < guest_goals THEN 0
                                ELSE 1
                            END) score # 内部查询结果必须有一个别名
                FROM matches
                GROUP BY host_team

                UNION ALL

                SELECT guest_team team_id,
                       SUM(CASE
                               WHEN host_goals > guest_goals THEN 0
                               WHEN host_goals < guest_goals THEN 3
                               ELSE 1
                           END) score
                FROM matches
                GROUP BY guest_team
            ) b
       # 这个地方非常关键 两个子查询的联合中 
       # 同一个team的得分分别分布在第一个子表的host_goals和第二个子表的guest_goals字段
       GROUP BY team_id 
   ) a
# 求得总分之后 将结果与team表进行联合 从而得到三项的结果
RIGHT JOIN teams t ON t.team_id = a.team_id
# 最后根据要求进行排序即可
ORDER BY num_points DESC, t.team_id ASC;