【SQL自学打卡|DAY7】——综合练习

祖国滴花骨朵儿

已于 2022-10-09 17:04:59 修改

阅读量338

点赞数 4

分类专栏： MySQL数据库学习文章标签： sql 数据库 mysql

于 2022-09-06 13:24:32 首次发布

本文链接：https://blog.csdn.net/m0_62279905/article/details/126719848

版权

MySQL数据库学习专栏收录该内容

21 篇文章 6 订阅

订阅专栏

前言

今天这篇文章是SQL入门篇的结尾啦，今天需要好好总结以下入门篇的知识点，我会单独发一篇关于SQL入门篇所涉及到的所有知识点，话不多说，开始今天的练习吧！

首先回顾一下昨天必会函数当中涉及到的知识点：
1、日期函数
datediff(次日，当日)=1即次日-当日=1（相隔一天）
DATEDIFF()函数返回两个日期之间的差异

2、自联结
from …left outer join … on …建立自联结

3、IF的用法
select 语句中 if 的用法
IF( expr1 , expr2 , expr3 )
expr1 的值为 TRUE，则返回值为 expr2
expr1 的值为FALSE，则返回值为 expr3

4、substring_index用法
substring_index(str,delim,count)
str:要处理的字符串
delim:分隔符
count:计数
如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容
如果count是负数，那么就是从右往左数，第N个分隔符的右边的全部内容
如果需要中间内容，需要嵌套使用
注意：如果count为1(而不是0)，表示从第一个位置开始【数据库的记录都是从1开始，没有从0开始】。

5、窗口函数
<窗口函数>() over (partition by <用于分组的字段> order by <用于排序的字段>)
<窗口函数>的位置可以放两种函数
1 专用窗口函数rank， dense_rank， row_number 等等
2 聚合函数，如sum, avg, count, max, min 等等

SLogan:利用有限的时间，撸起袖子加油干！既要面包也要玫瑰！！！

一、学习过程

在这里插入图片描述

【SQL34】统计复旦用户8月练题情况
在这里插入图片描述

解题思路：
限定条件：题目要求复旦大学的（来自表user_profile.university），8月份练习情况（来自表question_practice_detail.date）
从date中取month=8
question_cnt：count(question_id)
right_question_cnt：sum(if(qpd.result=‘right’, 1, 0))
按列聚合：需要输出每个用户的统计结果，因此加上group by up.device_id
对于在8月份没有练习过的用户，答题数结果返回0，使用left join即可

代码如下：

SELECT up.device_id,'复旦大学' as university,
  count(question_id) as question_cnt,
  sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as up
 
left join question_practice_detail as qpd
  on qpd.device_id = up.device_id and month(qpd.date) = 8
 
where up.university = '复旦大学'
group by up.device_id

结果：
在这里插入图片描述

【SQL35】浙大不同难度题目的正确率
在这里插入图片描述

解题思路：
这一题和上一题所用方法差不多，就是需要自己弄清楚correct_rate是怎样计算出来的，正确的题数/总的答题数，也就是device_id的数量，前提是浙江大学的用户。知道这些之后，还需要将三张表联结起来，以此来完成本道题目。
代码如下：

SELECT qd.difficult_level,
      SUM(if(qpd.result='right',1,0)) / count(qpd.device_id) as correct_rate
FROM 
    (question_practice_detail AS qpd
    LEFT JOIN  question_detail AS qd  
    ON qpd.question_id=qd.question_id

    LEFT JOIN  user_profile AS up
    ON up.device_id=qpd.device_id)
WHERE university='浙江大学'
group by qd.difficult_level
order by correct_rate ASC

结果：
在这里插入图片描述

【SQL39】 21年8月份练题总数
在这里插入图片描述

解题思路：
由表可知，device_id是有重复的，所以我们要多device_id进行去重（DISTINCT），其他操都是我们之前所碰到过的，列重命名（AS），还有时间函数的使用。

代码如下：

SELECT 
    count(distinct device_id)  AS did_cnt,
    count(question_id) as question_cnt
from question_practice_detail
WHERE YEAR(date)=2021 AND MONTH(date)=8

结果：
在这里插入图片描述

总结

SQL入门篇我们已经学完了，其实很多题目都不止一种解法，我用的方法都是我自己掌握并且能够应用的，希望大家可以去思考，尝试用更多的方法解题，这是一个思维发散的过程，也能够帮助我们看到题目之后快速形成解题思路，一步一步来，多动手，才能更加熟练。入门篇告一段落，接下来我会把碰到过的知识点单独拎出来写一篇blog，然后我们就开始【SQL必知必会】。
还是那句老话，希望大家能够多多支持我，指点我！！！