sql
SQL小知识
select选的字段可以直接进行运算
Give the name and the per capita GDP for those countries with a population of at least 200 million.
select name, gdp/population
from world where population >= 200000000
distinct去重
只能加在select后面，对最后的结果进行去重
select continent from world
where 条件
in 包含某些内容
select distinct continent from world where continent in ('Africa', 'Asia')
like 字符串匹配
_匹配一个字符，%匹配任意字符
// 所有以A开头的洲名
select distinct continent from world where continent like 'A%'
//第二位是s的洲名
select distinct continent from world where continent like '_s%'
order by排序
升降序
asc, desc表示升序降序，可添加多个排序条件，效果是第一个排序结果相同的行按第二个排序条件来排，以此类推，默认是升序。
//asc可不加，默认升序
select * from nobel order by yr desc, winner asc limit 10
order by + 判断条件
会将判断条件转化为0或1，再根据数字排序
// 将物理学奖放最前面
select * from nobel
order by yr desc, subject='Physics' desc
// 将物理学和化学都放在最前面
select * from nobel
order by yr desc,
subject in ('Physics', 'Chemistry') desc
limit限制输出
用法是 limit offset, n表示从第offset+1行开始一共显示n行
聚合函数
count
用于对某个字段行数求和，如果字段值为空则不会计算，因此，要得到表中一共有多少行数据，应使用count(*)
实际上聚合函数基本上都会忽略空值，例如avg计算平均值时不会将空值考虑在内。
group by 分组
配合聚合函数使用，可以让聚合函数分别作用于不同的组
//不同大洲的总人口数
select continent, count(name)
from world
group by continent
多个字段group by
// 查询每年不同学科的获奖人数
select yr, subject, count(winner) as count
from nobel group by yr, subject
order by yr desc, count desc
having对group by的结果进行过滤
只输出group by后满足having条件的行
//只输出获奖人数为3的组
select yr, subject, count(winner) as count
from nobel group by yr, subject
having count(winner)=3
order by yr desc, count desc
常见函数
需要用到时查询这个即可
窗口函数
只能写在select中，在select之前执行，返回之前的结果，进行分区排序操作，再根据排序结果执行特定函数功能，得到一个字段。
//排序
select yr, party, votes,
rank()over(partition by yr order by votes desc) as r
from ge
where constituency='S14000021'
order by yr, r
//新增病例
select confirmed,
confirmed-lag(confirmed)over(order by whn) as increased
from covid
where WEEKDAY(whn) = 0
and name = 'Italy'
and YEAR(whn) = 2020
常用窗口函数见此
join表连接
没啥难度，基本就是left/right/inner join
子查询
where后
用查出结果用于主查询中where的判断条件
//gdp比欧洲最大gdp的国家还高的国家
select name from world
where gdp > (
select max(gdp) from world where continent='Europe'
)
select的结果也可以是多个，此时是用in来判断
//与中国和澳大利亚相同大洲的国家
select name from world
where continent in (
select continent from world where name in ('China', 'Australia')
)
from后
从一个查询结果中再进行一次查询，一般用于对窗口函数查到的结果筛选
//在窗口函数那章的基础上，再筛选增长大于100的周
select * from
(select whn, confirmed,
confirmed-lag(confirmed)over(order by whn) as increased,
rank()over(order by whn) as week_index
from covid
where WEEKDAY(whn) = 0
and name = 'Italy'
and YEAR(whn) = 2020) as sel
where increased > 100
最后修改于 2024-10-07