SQL mother查询语句

题目教程网址：SQL之母

基础查询

select * from student;

select
    xxx, xxxx as name, ddd,
    xxx*2 as double_xxx
from
    a_table
where
    xx>10 or dd!=1 and kk=9;

别名，常量和运算，where，运算符(=,!=,>,<)，逻辑运算(and,or,not)。

模糊查询

select something from a_table
where value like '%值%';

模糊查询用like或not like。
'%值'匹配开头，'值%'匹配结尾，'%值%'代表包含'值'。

空值

select something from a_table
where value is not null;

空值用null，和Python类似。

去重

select distinct A, B, C from a_table;

去重就是select distinct这样固定搭配，会按照后面的字段组合进行去重。

排序

select something from a_table
order by value desc, another_value asc;

排序根据给定的字段顺序进行排序，默认asc是升序，desc是降序。

截断和偏移

select something from a_table
order by value desc, another_value asc
limit 0, 3;

limit m, n从第m个开始取，取n个结果(索引从0开始)。
limit n 从第0个开始取，取n个结果(索引从0开始)。

条件分支

SELECT
  name,
  CASE
    WHEN (age > 60) THEN '老同学'
    WHEN (age > 20) THEN '年轻'
    ELSE '小同学'
  END AS age_level
FROM
  student;

case ... end，里面把when当if那样用，then或else决定值是多少。
整个是决定一个字段的值。

函数

分组聚合

SELECT
  class_id,
  exam_num,
  COUNT(*) AS total_num
FROM
  student
GROUP BY
  class_id,
  exam_num;

SELECT
  class_id,
  SUM(score) AS total_score
FROM
  student
GROUP BY
  class_id
HAVING
  SUM(score) > 150;

根据group by后面的组合进行分组，整个语句的查询就是在每一个组里面单独运行。
having是类似于where的筛选，但WHERE子句用于在分组之前进行过滤，而HAVING子句用于在分组之后进行过滤。

关联查询

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

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

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

将两个表组合在一起输出(table.field用于定位具体列，用别名简化代码)
cross join简单粗暴。
inner join用on来指定两个表的交集，只输出交集部分。
outer join加了个left，将完整输出主表的数据，即使不在交集中。(from后是主表，join后是关联表，不关联的部分值为null)

子查询

select
  name,
  score,
  class_id
from
  student
where
  class_id in (
    select distinct
      id
    from
      class
  );

-- 主查询
SELECT name, total_amount
FROM customers
WHERE EXISTS (
    -- 子查询
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.customer_id
);

现在子查询中查出一个表，再根据这个表查询
in，not in，exist， not exist

组合查询

-- UNION ALL操作
SELECT name, age, department
FROM table1
UNION ALL
SELECT name, age, department
FROM table2;

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

开窗函数

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS cumulative_total_amount
FROM
    orders;

用over聚焦到分组，它允许我们在查询中进行对分组数据进行计算、 同时保留原始行的详细信息 。即仅在这个地方用分组，但不影响全局。
partition by进行分组，order by进行排序，排序后它应该是只取该组该条record前面顺序的。

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS customer_rank
FROM
    orders;

rank() over 分组排名，相同的值将被赋予相同的排名，当存在并列（相同排序值）时，Rank 会跳过后续排名，并保留相同的排名。

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS row_number
FROM
    orders;

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

SELECT 
    student_id,
    exam_date,
    score,
    LAG(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS previous_score,
    LEAD(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS next_score
FROM
    scores;

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

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