SQL学习笔记

空值(NULL)

不能用 =
应该用 is null 或 is not null

使用 ifnull(col, default) 来处理空值

col : 列名
default : 默认值

order by 排序

不仅能对数字大小排序也可以根据字母顺序排序

asc 升序
desc 降序

默认升序排列

在排序过程中NULL视为最小值

多字段排序时优先级为左到右

1 2	# 先按年龄再按身高 select * from person order by age, height;

limit

limit N : 返回 N 条记录
limit N, M : 相当于 limit M offset N 从第 N 条记录开始返回 M 条记录

聚合函数

max() 求列中最大值
min() 求列中最小值
avg() 求列的平均值
count() 求列的总行数
- count(*) 统计所有行数量，包括空值
- count(colname) 统计colname这列，空值不计
- count(distinct colname) 统计colname这列，非空且唯一的数量
sum() 求列的和

聚合函数会忽略空值(NULL)
解决办法 avg(ifnull(score, 0))

group by 分组查询

当使用分组查询的时候 select 后面只能跟 group by 的字段名
或者用聚合函数

1 2	# 同年龄的各有多少人 select age, count(1) from t_person group by age;

having 过滤

没有使用 group by 分组时 having 可替代 where
使用 group by 分组时使用 having 进行二次过滤

窗口函数

where 条件执行之后，才会执行窗口函数。

窗口函数不能在 where 中使用

group by 是分组函数只保留分组字段和聚合函数的结果
partition by 是分析函数保留原有数据

使用多重分组来保证每组的唯一性与数据的准确性
例如 over(partition by name, tel)

排序函数

rank() 允许排名并列存在序号不连续的情况
dense_rank() 允许排名并列序号一定连续
row_number() 不允许排名并列，按查询到的顺序排序（索引）
ntile(x) 将数据分为x组，并给每组分配一个数字
例如：ntile(3) 按照排序分为3组，1代表上等，2代表中等，3代表下等

多重排序

字段1相等时按字段2排序
rank() over(order by col1, col2)

with 子查询

作用：创建临时表（不写入数据库）
由于不能直接获取窗口函数的结果，因此需要创建临时表来进行子查询

# 查询评分排名第二的游戏
with ranking as (
    select
        name,
        dense_rank() over(order by editor_rating desc) as `rank`
    from game
)

select name from ranking where `rank` = 2;

自定义窗口

作用：以 当前行 为基准，精确控制要选取的数据范围（开窗大小）

rows 物理窗口
range 逻辑窗口

unbounded preceding 对上限无限制
n preceding 当前行之前的第n行
current row 仅当前行
n following 当前行之后的第n行
unbounded following 对下限无限制
between lower_bound and upper_bound 当前行加下限与上限的区间之内

简写
sum(price) over(order by `date` rows unbounded preceding)

等价于

sum(price) over(order by `date` rows between current row and unbounded preceding)

下限不能简写！

range 和 rows 不同，range 的范围取决于跟在 order by 后的字段的值！

假设函数为

over(order by size range between 2 preceding and 2 following)

则窗口大小是字段size的值在 [size-2, size+2]内的行

like 模糊查询

% 0或多个任意字符
_ 一个任意字符

分析函数

作用：计算增量或转化率

向下面行取

lead(x, y, z)
- x 要获取的字段
- y 偏移（省略则默认为1）
- z 默认值（省略则默认为NULL）

lead(x, 2) over()

当前行 为第一行时，获取第3行的x字段值，若没有则返回z

向上面行取

lag(x, y, z)

lag(x, 2) over()

当前行 为第3行时，获取第1行的x字段值，若没有则返回z

lead(...) over(order by ...)

等价于

lag(...) over(order by ... desc)

返回指定列的第一个值

first_value(x)

返回指定列的最后一个值

last_value(x)

当 over 子句中包含 order by 时，如果不显式定义窗口大小，SQL会带上默认的语句 range unbounded preceding，意味着查询范围被限定在第一行至当前行！

想要通过 last_value 与 over() 取所有数据排序后的最后一个值，则需要定义窗口大小为如下。

last_value(col1) over(order by col2 [rows|range] between unbounded preceding and unbounded following)

返回指定列的第几行的值

nth_value(x, n)
- x 列名
- n 第几行