学习视频：【课程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,4limit普通用法不用多说，但是像这种要取第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) >= 30where挑选出演过第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) score2
from game ga
left join goal go on ga.id = go.matchid
group by ga.mdate,ga.team1,ga.team2
order 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 continent
from world
where name in (Argentina,Australia)
)跟这两个国家在同一大洲是我们的条件，而这个条件没办法直接写出来，我们要先求出跟这两个国家在同一大洲的洲有哪些，所以用到子查询
select constituency,party
from
(select constituency,party,
rank()over(partition by constituency over by votes desc) posn,
votes
from ge
where 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 population
from world
where name= 'Canada'
)
and population < (
select population
from world
where name = 'Poland'
)
不难，就是用两次子查询
select name,population,continent
from world
where continent not in (
select distinct continent
from world
where 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 world
group by continent
)这里要求一个区域最大，用子查询可以对洲和区域同时查询，这样都不用distinct了
select continent,name,area
from
(select continent,name,area,
rank()over(partition by continent order by area desc) as posn
from world
group by continent) as rk
where rk.posn = 1
我觉得第二种做法也可以，就是仿照之前求选举人的做法
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')
) re
order by 排名
这里将之前学些的窗口函数和偏移函数结合在一起，加强练习
云端数据库练习
创建好云端数据库后，导入学习资料的三张表，然后实战练习
直接用describe描述shop表，就可以看到表格的总体描述
tips：
- 如果Mysql导出csv格式数据乱码，是csv文件本身的文本编码问题导致的
- 1. 鼠标右键点击选中的 csv 文件，在弹出的菜单中选择“编辑”，则系统会用文本方式（记事本）打开该 csv 文件
- 2. 打开 csv 文件后，进行“另存为”操作，在弹出的界面底部位置有“编码”，修改编码方式即可： 从UTF-8改成 ANSI，然后保存
- 3. 再用 Excel 打开后，显示汉字正常
找寻题目中的字段在哪张表中，发现shop表中包含了所有字段，不必连接表，用到之前所学的知识，都能解决
这里得知GMV和cpc总费用在不同表中，需要连接两张表，这里可以用门店ID和日期两个字段一起作为连接依据，这样有效去除很多重复数据
接下来我的本意是用left join，这样可以保证所有门店都包含在内，就是会有很多空值
这里要求和，不是求每天数据那种，每个门店只要最后汇总的数据即可，那么用group by分一下组
聚合函数不能放在where，所以写在having后面
我总是喜欢写group by，但是要注意写了group by之后select后的语句就得出现在group by后面
SQL内容算是复习完了，接下来边学其他内容，边练习牛客网的题目
993
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
