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决定值是多少。
整个是决定一个字段的值。
函数
| 函数名 | 功能 |
|---|---|
| date() | 返回此刻日期 |
| time() | 返回当前时间 |
| datetime() | 返回date＋time的组合 |
| upper(field) | 将指定列大写 |
| lower(field) | 将指定列小写 |
| length(field) | 求字符长度 |
| max(field) | 求该列最大值 |
| min(field) | 求该列最小值 |
| sum(field) | 求和 |
| avg(field) | 求平均 |
| count(...) | 计算指定列的行数或非空值的数量。 |
分组聚合
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子句可选，用于分组和排序数据。
