SQL学习笔记2

本期接着分享跟随戴戴戴师兄的个人空间-戴戴戴师兄个人主页-哔哩哔哩视频学习SQL的笔记。

<ge>表基本信息如下所示：

<game、goal、eteam>表基本信息如下所示：

<teacher、dept>表基本信息如下所示：

<movie、casting、actor>表基本信息如下所示：

    其他表格信息请参考上一期内容。   

知识点八：窗口函数

语法：窗口函数over([partition by 字段名] [order by 字段名 asc|desc])

         排序窗口函数：rank()over()；dense_rank()over()；row_number()over()

         偏移分析函数：lag(字段名,偏移量[,默认值])over()；lead(字段名,偏移量[,默认值])over()

例题1：查询每一年S14000021选区中所有候选人所在的团体(party)和得票数(votes)，并对每一年中的所有候选人根据选票数的高低赋予名次，选票数最高则为1，第二名则为2，后续以此类推，最后根据团体(party)和年份(yr)排序

select yr, party, votes,
rank()over(partition by yr order by votes desc) as posn
from ge
where constituency = 'S14000021'
order by party, yr 

例题2：查询法国和德国1月每天新增确诊人数，最后显示国家名、标准日期（2020-01-27）、当天截至时间累计确诊人数、昨天截至时间累计确诊人数、每天新增确诊人数，按照截至时间排序

select name, 
       date_format(whn,'%Y-%m-%d') date,
       confirmed 当天截至时间累计确诊人数,
       lag(confirmed,1) over(partition by name order by whn) 昨天截至时间累计确诊人数,
       (confirmed - lag(confirmed,1) over(partition by name order by whn)) 每天新增确诊人数
from covid
where name in ('France','Germany') and month(whn) = 1
order by whn

例题3：查询截至时间为2020年4月20日的国家名，确诊人数，确诊人数排名，死亡人数，死亡人数排名，按照确诊人数降序排名

select name,
       confirmed 确诊人数,
       rank() over(order by confirmed desc) as 确诊人数排名,
       deaths 死亡人数,
       rank() over(order by deaths desc) as  死亡人数排名    
from covid
where whn = '2020-04-20'
order by confirmed desc

例题4：查询意大利每周新增确诊数（显示每周一的数值 weekday(whn) = 0）。最后显示国家名，标准日期（2020-01-27），每周新增人数。按照截至时间排序

select name,
       date_format(whn,'%y-%m-%d') 日期,
       (confirmed - lag(confirmed,1) over(partition by name order by whn)) New
from covid
where name = 'Italy' and weekday(whn) = 0
order by whn

知识点九：表连接

例题5：查询有球员名包括'Mario%'进球的队伍1（team1），队伍2（team2）及球员姓名

select team1,team2,player
from game
join goal 
on game.id = goal.matchid
where player like 'Mario%'

例题6：查询队伍1（team1）的教练是“Fernando Santos”的球队名称（teamname）、比赛日期（mdate）和赛事编号（id）

select teamname, mdate, game.id
from game
join eteam
on game.team1 = eteam.id
where coach = 'Fernando Santos'

例题7：使用合适的连接显示所有教师及其所教授的科目名

select teacher.name, dept.name
from teacher
left join dept
on teacher.dept = dept.id

例题8：查询至少出演过第1主角30次的演员名

select actor.name
from actor 
join casting
on actor.id = casting.actorid
where casting.ord = 1
group by casting.actorid
having sum(casting.ord) >= 30

例题9：查询在比赛前十分钟有进球记录的球员，他的队伍编号(teamid)，教练(coach), 进球时间(gtime)

select goal.player, eteam.id,eteam.coach,goal.gtime
from goal
join eteam
on goal.teamid = eteam.id
where goal.gtime <= 10

知识点十：子查询

例题10：查询出gdp高于欧洲每个国家的所有国家名，有一些国家gdp值可能为NULL，请排除这些国家

select name from world 
where gdp is null and gdp > (select max(gdp) from world where continent = 'Europe')

例题11：查询跟阿尔及尼亚（Argentina）和澳大利亚（Australia）在同一大洲的所有国家名及其所属大洲， 并按照国家名进行排序

select name, continent from world 
where continent in (
select distinct continent from world where name in ('Argentina','Australia'))
order by name

例题12：查询2017年所有在爱丁堡的选区当选议员所在选区（constituency）及其团队（party），已知爱丁堡选区编号为S14000021至S14000026，当选议员即各选区得票数最高的候选人

select constituency, party,
rank() over(partition by constituency order by votes)
from ge where yr ='2017' and constituency between 'S14000021' and 'S14000026'

例题13：查询在欧洲(Europe)人均gdp大于英国 (United Kingdom)的国家名。人均国内生产总值（人均GDP）=国内生产总值（GDP）/人口（populaiton）

select name from world 
where gdp/population
> (select gdp/population from world where name = 'United Kingdom')
and continent = 'Europe'

例题14：查询人口数（population）超过加拿大（Canada）但是少于波兰（Poland）的国家，结果显示这些国家名（name）和人口数（population）

select name, population from world 
where population > (select population from world where name = 'Canada')
and population < (select population from world where name = 'Poland')

例题15：查询所有国家人口均≤25000000的大洲，及其国家名（name）和人口（population）

注：感觉SQL还需要一定的阅读理解能力

select name,continent,population from world
where name not in (
select name from world
where population > 25000000)

例题16：查找每个大陆（continent）中最大的国家（按区域area），显示该大洲（continent），国家名（name）和面积（area）

select continent, name, area from
(select continent, name, area,
rank() over(partition by continent order by area desc) rk
from world) as a
where a.rk = 1

例题17：查询德国和意大利每天新增治愈人数并从高到低排名，查询结果按国家名，截至日期（输出格式为'xxxx年xx月xx日'），新增治愈人数，按排名排序

select name, 日期, 每天新增治愈人数,
rank() over(partition by name order by 每天新增治愈人数 desc) 排名
from (
select name, date_format(whn, '%Y年%m月%d日') 日期,
(recovered-lag(recovered, 1) over(partition by name order by whn)) 每天新增治愈人数
from covid where name in ('France','Italy')) as a
order by 排名

心得体会

通过本节内容的学习，我充分理解掌握了SQL语句的执行顺序、表连接的逻辑、常用窗口函数的使用方法以及子查询跟在select，from，where，having子句后面的使用，尤其是在from后面需要将查询出来的表重命名。而且感觉SQL还考察一定的阅读理解能力。还有要提出疑问，有些内容视频或课本不一定对，要多查找一些其他的参考资料再加上自己的思考进行学习，形成自己的一套理解逻辑。下期我要分享一些关于SQL表连接的知识点。