SQL实战

学习视频：【课程2.0】SQL从入门到实战|云端数据库搭建|Excel&Tableau连接数据库_哔哩哔哩_bilibili

由于我学习过SQL，所以直接记录一些函数、特殊用法、刷题等实战的知识，后面教学搭建云端数据库和其他软件连接数据库视频讲解很清晰，也已经全部完成，无需记录，每个题目的类型记录为标题方便查找

通配符

select name from world
where name like '%a%' and
name like '%e%' and
name like '%i%' and
name like '%o%' and
name like '%u%' and
name not like '% %'

这一题没什么难度，只是刚开始想的是name like '%a%e%i%o%u%'，这样就限制住了元音字母的顺序，不正确，后面的空格用not like即可

指定数据排序到最后

select winner,subject
from nobel
where yr = 1984
order by subject in ('chemistry','physics'), subject, winner

前面一句话很好实现，主要在于排序也就是order by该怎么写，这里的order by有三个排序条件，subject in ('chemistry','physics')是一个布尔表达式，如果subject等于chemistry或physics，那么表达式的结果返回1，否则为0，而后面按照subject升序排列，只有subject等于chemistry或physics的时候为1，其余情况均为0，1自然会被排到0的后面，也就实现了指定科目放在最后的目标

limit用法

select name,population
from world
order by population desc
limit 3,4

limit普通用法不用多说，但是像这种要取第4到第7的，limit后面第一个数字是第四的前一位就是3，而第二个数字是要查询的个数（4、5、6、7），一共四位数字，就是4

计算表格行数

计算表格行数要用count(*) ，如果选择某一列count，可能里面出现空值就不会计算上

同样，聚合函数也会略过空值

常见部分函数

四舍五入函数

字符串函数

注意区分：substring和limit，前者是从第n个字符开始取，后者是从n+1个字符开始取

数据类型转换函数

日期时间函数

条件判断函数

round和concat嵌套得到百分比数据

select confirmed,deaths,recovered,recovered/confirmed,
concat(round((recovered/confirmed)*100,2),'%') 治愈率
from covid
where recovered/confirmed > 0.3

这里主要是使用concat连接函数，在算好的数值后面添加一个%

练习题

select capital,name
from world
where capital like concat('%',name,'%')
and capital != name

这里巧妙地用到了concat函数，我们需要capital匹配'%变化的name%'，而使用concat就能使name根据每一行数据变化

高级语句

窗口函数

排序窗口函数区别：

rank()over()：高考排名规则，重复值获得相同序号，如果两行数据排名第1，那么下一行排第3

dense_rank()over()：重复值获得相同序号，如果两行数据排名第1，那么下一行排第2

row_number()over()：不论是否有重复值，每一行获得唯一序号

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

难点在于如何对每一年中的候选人根据票数高低赋予名次，用到排序窗口函数，partition by yr代表按照日期进行分区，再按照得票数降序排序，由rank给排名

注意不要太死板，就像这里题目没有要求select yr出来，但是实际当中有日期列看得更加清楚

偏移分析函数

偏移分析函数：

lag()：向前查看多少行，例如lag(column,1)，查看上一行column值

lead()：向后查看多少行，例如lag(column,1)，查看下一行column值

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 date order by confirmed)) 每天新增确诊人数,
from covid
where name in ('France','Germany') and month(whn) = 1
order by whn

显示标准日期：使用date_format对时间whn列改格式

1月份的情况：使用month对whn列取月份

昨天截至时间累计确诊人数：首先over函数会按照国家名分区，然后按照时间升序排列，接下来lag函数会取上一行的confirmed的值，也就是昨天的confirmed值

每天新增确诊人数：直接当天-昨天即可

练习题

select party,votes,
rank()over(order by votes desc)
from ge
where yr = 2017 and constituency = 'S14000024'
order by party

由于我们在where中限制了只有一个选区，所以partition by可以省略

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

select name,date_format(whn,'%Y-%m-%d'),
(confirmed - lag(confirmed,1)over(order by whn)) 每周新增人数
from covid
where name = 'Italy' and weekday(whn) = 0
order by whn

这里变通的地方是显示每周新增人数，一开始想的是写成lag(confirmed - 7)就是当前减去7前的数据，但是我们其实要的只是周一的数据而已，所以在where条件处加上一个weekday(whn) = 0，这样求出来的日期就全部是周一的了，这样仍然用lag(confirmed,1)，就是用当周一数据减去上周一数据了

表连接

内连接：遍历所有、向下增添，相当于笛卡尔积，左边两个1右边两个1，最后会增加4个1，没连上的地方填上null，然后将存在null的行全部剔除

左连接：遍历所有、向下增添，相当于笛卡尔积，左边两个1右边两个1，最后会增加4个1，没连上的地方填上null，然后保留左边表的全部值，一个不能少一个不能多，左边不允许null存在

右连接：遍历所有、向下增添，相当于笛卡尔积，左边两个1右边两个1，最后会增加4个1，没连上的地方填上null，然后保留右边表的全部值，一个不能少一个不能多，右边不允许null存在

练习题

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

这里要注意的就是‘所有教师’这个词，我们正常用join会发现结果中少了些老师，如果数据太多我们就不容易检查出来，所以还是用左连接可以完全保证教师都在

还有就是两张表name列同名了，我们需要用表名区分开

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

where挑选出演过第1主角的人，再having挑选出演过30+次的人

select ga.mdate,ga.team1,sum(case when go.temid = ga.team1 then 1 else 0 end) score1,ga.team2,sum(case when go.temid = ga.team2 then 1 else 0 end) score2from game galeft join goal go on ga.id = go.matchidgroup by ga.mdate,ga.team1,ga.team2order by ga.mdate,go.matchid,ga.team1,ga.team2

这里用了case when这个小技巧，我们看到goal表中teamid对应着game表中team1和team2列的数据，那么我们用case when判断，score1：如果teamid在team1中那么我们就记为1，然后求和。这里也是用left join，因为要保证每场赛事都被记录

子查询

select name,continent
from world
where continent in (select continentfrom worldwhere name in (Argentina,Australia)
)

跟这两个国家在同一大洲是我们的条件，而这个条件没办法直接写出来，我们要先求出跟这两个国家在同一大洲的洲有哪些，所以用到子查询

select constituency,party
from(select constituency,party,rank()over(partition by constituency over by votes desc) posn,votesfrom gewhere yr = 2017 and constituency between 'S14000021' and 'S14000026') as rk
where rk.posn = 1

这里比较绕，当我们算出中间那张表也就是找出对每个选区得票进行排序，但是要取出每个每个选区得票数最高的，那么不能直接用limit，那只会返回第一条数据，所以我们在外面再包一张表，查出里面那张表序号为1的，就正确了

其次就是我们这里虽然只有爱丁堡一个选取，可是这个选区有不同的编号，所以partition by不要省略

最后这个str也是可以用between and的

练习题

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

不难，就是用两次子查询

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

这里需要一个反向思维，因为我们需要求国家人口均为<=25000000的大洲，那么正常写continent in(...where population <=25000000...)的话，只要有一个国家符合条件就会把这个大洲算进去，但是我们要求的是每个国家都得符合要求，所以我们可以使用一个not in

select continent,name,area
from world
where (continent,area) in (select continent,max(area)from worldgroup by continent
)

这里要求一个区域最大，用子查询可以对洲和区域同时查询，这样都不用distinct了

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

我觉得第二种做法也可以，就是仿照之前求选举人的做法

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

这里将之前学些的窗口函数和偏移函数结合在一起，加强练习

云端数据库练习

创建好云端数据库后，导入学习资料的三张表，然后实战练习

直接用describe描述shop表，就可以看到表格的总体描述

tips：

如果Mysql导出csv格式数据乱码，是csv文件本身的文本编码问题导致的
- 1. 鼠标右键点击选中的 csv 文件，在弹出的菜单中选择“编辑”，则系统会用文本方式（记事本）打开该 csv 文件
- 2. 打开 csv 文件后，进行“另存为”操作，在弹出的界面底部位置有“编码”，修改编码方式即可：从UTF-8改成 ANSI，然后保存
- 3. 再用 Excel 打开后，显示汉字正常