基础语句：
模糊匹配：
% 任意字符，_占位符
like '_t%';
多字段排序:
select winner,yr,subject from nobel where winner like 'Sir%' order by yr desc,winner asc
按照特殊排序，把chemistry,physics排在后面
select winner,subject from nobel where yr = 1984 order by subject in ('chemistry','physics') ,subject, winner
limit限制范围：
select name,population from world
order by population desc
limit 3,4
sg:从第4行到第7行的数据
聚合函数忽略空值
某个字段包含另外字段 可以用concat+like进行筛选
高级语句：
窗口函数、子查询、表链接
窗口函数相当于把having后面的数据拿去新窗口操作再返回到原表，不影响原表排序
over([partition by xxx] order by xxx)
只能写在select里面,而且分区不去重
sg:
SELECT name, DATE_FORMAT(whn,'%Y-%m-%d'), confirmed,lag(confirmed,1)over(partition by name order by whn),(confirmed-lag(confirmed,1)over(partition by name order by whn)) new1 FROM covid WHERE name = 'Italy' AND WEEKDAY(whn) = 0 ORDER BY whn
计算一周的新增人数 = 这下周（周一）减这一周周一 间隔7天
表连接（基于广义迪卡尔积筛选）：
内连接 from 表1 join 表2 on 字段名=字段名
左右连接 left join and right join
SELECT mdate,team1,sum(case when team1=teamid then 1 else 0 end) score1 ,team2,sum(case when team2=teamid then 1 else 0 end) score2 from game ga left join goal go on ga.id=go.matchid group by mdate,team1,team2 order by mdate,matchid, team1 and team2
统计同场比赛球队的进球情况
子查询：
from 子查询要起别名
sg:select name,continent,population from world where continent not in ( select distinct continent from world where population>25000000)
select name,continent,population from world where continent in( select continent from world group by continent having max(population)<= 25000000)
求洲里面所有国家的人口数小于等于25000000，只要排除洲里面包含的国家有超过这个数的就好
`select continent,name,area
from(
select continent,name,
area,rank()over(partition by continent order by area desc) position
from world
order by name) as r
where r.position =1 select continent,name,area
from world
where (continent,area) in (
select continent,max(area)
from world
group by continent
)
`
查询每个洲里面面积最大的国家
