SQL刷题系列之基础篇

SQL刷题系列之基础篇

  • 开篇简介
  • 基础查询
    • SQL1 查询多列
    • SQL2 查询所有列
    • SQL3 查询结果去重(DISTINCT)
    • SQL4 查询结果限制返回行数(limit)
    • SQL5 将查询后的列重新命名(as)
  • 排序
    • SQL36 查找后排序(order by)
    • SQL37 查找后多列排序
  • 条件查询
    • SQL6 查找学校是北大的学生信息(where)
    • SQL7 查找年龄大于24岁的用户信息
    • SQL8 查找某个年龄段的用户信息(between ... and ...)
    • SQL9 查找除复旦大学的用户信息(!=,not in 用法)
    • SQL10 用where过滤空值练习
    • SQL13 Where in 和Not in
    • SQL14 操作符混合运用(AND 、OR)
    • SQL15 查看学校名称中含北京的用户(like 用法)
  • 高级查询
    • SQL16 查找GPA最高值(max()用法)
    • SQL17 计算男生人数以及平均GPA(count(), round()现身)
  • 分组查询
    • SQL18 分组计算练习题(group by 登场)
    • SQL19 分组过滤练习题(having 出现)
    • SQL20 分组排序练习题
  • 多表查询
    • 子查询
      • SQL21 浙江大学用户题目回答情况(JOIN)
    • 链接查询
      • SQL22 统计每个学校的答过题的用户的平均答题数
      • SQL23 统计每个学校各难度的用户平均刷题数
      • SQL24 统计每个用户的平均刷题数
    • 组合查询
      • SQL25 查找山东大学或者性别为男生的信息
  • 常用函数
    • 条件函数
      • SQL26 计算25岁以上和以下的用户数量
      • SQL27 查看不同年龄段的用户明细
    • 日期函数
      • SQL28 计算用户8月每天的练题数量
      • SQL29 计算用户的平均次日留存率
    • 文本函数
      • SQL30 统计每种性别的人数
      • SQL31 提取博客URL中的用户名
      • SQL32 截取出年龄
    • 窗口函数
      • SQL33 找出每个学校GPA最低的同学
  • 综合练习
    • SQL34 统计复旦用户8月练题情况
    • SQL35 浙大不同难度题目的正确率
    • SQL39 21年8月份练题总数

开篇简介

题目来源于牛客网,边刷题边记录巩固,为了方便自己复习和分享。基础篇即入门教程,有些很简单的就不解释了,一步步来,无需着急,学徒心态。(有借鉴许多人的题解链接等,知识搬运工,侵删)

~

部分规则,照顾萌新
标点符号切记要英文的,不能中文的标点符号!!!
SQL刷题系列之基础篇_第1张图片

~

基础查询

SQL1 查询多列

描述
题目:现在运营同学想要用户的设备id对应的性别、年龄和学校的数据,请你取出相应数据

示例:user_profile
SQL刷题系列之基础篇_第2张图片
SQL刷题系列之基础篇_第3张图片

select device_id, gender, age, university
from user_profile

SQL2 查询所有列

SQL刷题系列之基础篇_第4张图片

select *
from user_profile

或者

select id, device_id, gender, age, university, province
from user_profile

SQL3 查询结果去重(DISTINCT)

SQL刷题系列之基础篇_第5张图片

select DISTINCT university
from user_profile

distinct这个关键字用来过滤掉多余的重复记录只保留一条,可以理解为过滤。

SQL4 查询结果限制返回行数(limit)

SQL刷题系列之基础篇_第6张图片

select device_id
from user_profile limit 2

利用 limit 来限制查询结果的返回行数

SQL5 将查询后的列重新命名(as)

SQL刷题系列之基础篇_第7张图片

select device_id as user_infos_example
from user_profile limit 2

只是在上一题基础上将列名用 as 修改了

排序

SQL36 查找后排序(order by)

SQL刷题系列之基础篇_第8张图片

select device_id, age
from user_profile
order by age ASC

order by 列名 按照’列名‘进行排序,asc/desc 升序/降序,根据要求使用(不写默认是asc 升序)

SQL37 查找后多列排序

SQL刷题系列之基础篇_第9张图片
以下三种都可以,因为order by 默认升序。

SELECT device_id,gpa,age from user_profile order by gpa,age;
SELECT device_id,gpa,age from user_profile order by gpa,age asc;
SELECT device_id,gpa,age from user_profile order by gpa asc,age asc;

条件查询

SQL6 查找学校是北大的学生信息(where)

SQL刷题系列之基础篇_第10张图片

select device_id, university
from user_profile
where university='北京大学'

where 后接条件

SQL7 查找年龄大于24岁的用户信息

SQL刷题系列之基础篇_第11张图片

select device_id, gender, age, university
from user_profile
where age > 24

SQL8 查找某个年龄段的用户信息(between … and …)

SQL刷题系列之基础篇_第12张图片

select device_id, gender, age
from user_profile
where age >= 20 and age <= 23

可以用 and 合并两个条件

select device_id, gender, age
from user_profile
where age BETWEEN 20 and 23

也可以用 between and 达到同样的效果

SQL9 查找除复旦大学的用户信息(!=,not in 用法)

SQL刷题系列之基础篇_第13张图片

select device_id, gender, age, university
from user_profile
where university !='复旦大学'

!= 相当于 ’不等于‘的意思

还可以这样子

select device_id, gender, age, university
from user_profile
where university not in ('复旦大学')

列名 [NOT] IN(常量1, 常量2,…常量n)
IN:当列中的值与IN中的某个常量值相等时,则结果为True,表明此记录为符合查询条件的记录。
NOT IN:当列中的值与某个常量值相等时,结果为False,表明此记录为不符合查询条件的记录。

SQL10 用where过滤空值练习

SQL刷题系列之基础篇_第14张图片

select device_id, gender, age, university
from user_profile
where age != ''
select device_id, gender, age, university
from user_profile
where age is not NULL

这里的考点就是考察数据库的空值,MySQL里的是null

SQL13 Where in 和Not in

SQL刷题系列之基础篇_第15张图片

select device_id,gender,age,university,gpa from user_profile
where university in('北京大学','复旦大学','山东大学');

SQL14 操作符混合运用(AND 、OR)

SQL刷题系列之基础篇_第16张图片

SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE (gpa > 3.5 AND university = '山东大学') or (gpa > 3.8 AND university = '复旦大学')

有两个关系为’或‘的条件,用 or 连接起来。不用括号也行,因为 and 的优先级比 or 高。

SQL15 查看学校名称中含北京的用户(like 用法)

SQL刷题系列之基础篇_第17张图片

select device_id, age, university
FROM user_profile
WHERE university like '%北京%'

这里涉及SQL通配符以及 like 运算符的知识
SQL刷题系列之基础篇_第18张图片
举例(搬运原文):
SQL刷题系列之基础篇_第19张图片

高级查询

SQL16 查找GPA最高值(max()用法)

SQL刷题系列之基础篇_第20张图片

select gpa
from user_profile
WHERE university='复旦大学'
ORDER by gpa DESC LIMIT 1
select max(gpa) as gpa
from user_profile
where university='复旦大学'

两种方法,可以对复旦大学的gpa进行降序排序再限制输出,也可以使用 max()解决

SQL17 计算男生人数以及平均GPA(count(), round()现身)

SQL刷题系列之基础篇_第21张图片

select COUNT(1) as male_num, avg(gpa) as avg_gpa
from user_profile
where gender='male'

上面是我写的,我这里的count(1)是 有多少行数据就会累计加一 的意思

以下为搬运的题解(链接)
在这里插入图片描述

分组查询

SQL18 分组计算练习题(group by 登场)

SQL刷题系列之基础篇_第22张图片

SELECT gender, university, COUNT(device_id) user_num, 
avg(active_days_within_30) avg_active_day, avg(question_cnt) avg_question_cnt
from user_profile
GROUP BY gender, university

用合适的函数处理对应的列,按照题目分组

SQL19 分组过滤练习题(having 出现)

SQL刷题系列之基础篇_第23张图片

SELECT university, avg(question_cnt) avg_question_cnt, avg(answer_cnt) avg_answer_cnt
from user_profile
GROUP BY university
HAVING avg_question_cnt < 5 OR avg_answer_cnt < 20

注意分组条件是university,求的是平均值avg
SQL出现having的原因是,where关键字无法与聚合函数一起使用,having关键字放在group by关键字后面,针对分组后的数据进行筛选.
记住聚合函数后,还需要过滤就使用having即可

SQL20 分组排序练习题

SQL刷题系列之基础篇_第24张图片

SELECT university, avg(question_cnt) avg_question_cnt
from user_profile
GROUP BY university
ORDER BY avg_question_cnt

考察基础的综合运用

多表查询

子查询

JOIN 知识点 -> 链接

SQL21 浙江大学用户题目回答情况(JOIN)

SQL刷题系列之基础篇_第25张图片

SELECT q.device_id, q.question_id, q.result
FROM question_practice_detail q INNER JOIN user_profile u
ON q.device_id=u.device_id
WHERE u.university='浙江大学'

搬运的题解 原文链接
SQL刷题系列之基础篇_第26张图片

链接查询

SQL22 统计每个学校的答过题的用户的平均答题数

SQL刷题系列之基础篇_第27张图片

SELECT u.university, COUNT(q.question_id) / COUNT(DISTINCT q.device_id) avg_answer_cnt
FROM user_profile u INNER JOIN question_practice_detail q
on u.device_id=q.device_id
GROUP BY u.university

难点:没想到要剔重,计算公式

搬运的题解 原文链接
SQL刷题系列之基础篇_第28张图片

SQL23 统计每个学校各难度的用户平均刷题数

SQL刷题系列之基础篇_第29张图片
SQL刷题系列之基础篇_第30张图片
SQL刷题系列之基础篇_第31张图片

思路:
给了三张表,分别都有涉及到里面的列,所以将他们链接在一起。
将要求的列 select ,根据之前题目的经验,并且细心发现有重复的数据,所以要剔重。
想计算公式,答题数 ÷ 答题人数
round()偷懒没写

SELECT 
    university,
    difficult_level,
    COUNT(t1.question_id) / COUNT(DISTINCT (t2.device_id)) avg_answer_cnt
FROM
question_detail t1 
join question_practice_detail t2 ON t1.question_id=t2.question_id
join user_profile t3 ON t3.device_id=t2.device_id
GROUP BY university, difficult_level

题解 原文链接
SQL刷题系列之基础篇_第32张图片

SQL24 统计每个用户的平均刷题数

SQL刷题系列之基础篇_第33张图片

只要在上一题的基础上,加上山东大学的判断即可

SELECT 
    t1.university,
    t3.difficult_level,
    COUNT(t2.question_id)/COUNT(distinct(t2.device_id)) as avg_answer_cnt
FROM 
    user_profile t1,
    question_practice_detail t2,
    question_detail t3
WHERE
    t1.device_id = t2.device_id
    and 
    t2.question_id = t3.question_id
    and
    t1.university = '山东大学'
GROUP BY t1.university,t3.difficult_level;

组合查询

SQL25 查找山东大学或者性别为男生的信息

SQL刷题系列之基础篇_第34张图片
刚开始做错了,直接用 or 会去重,因为有可能一条数据同时满足这两个条件

# 做错的
SELECT device_id, gender, age, gpa
FROM user_profile
WHERE university='山东大学' OR gender='male'

所以,要引入一个新的关键词 union
SQL刷题系列之基础篇_第35张图片
union知识图原文链接

SELECT device_id,gender,age,gpa FROM user_profile
WHERE university='山东大学' 
UNION ALL
SELECT device_id,gender,age,gpa FROM user_profile
WHERE gender='male'

搬运的题解链接

常用函数

条件函数

MySQL中提供了三种条件判断函数:IF ()、IFNULL ()与CASE

SQL26 计算25岁以上和以下的用户数量

SQL刷题系列之基础篇_第36张图片

SELECT IF(age >= 25, '25岁及以上','25岁以下') age_cut, COUNT(*) number
FROM user_profile
GROUP BY age_cut
SELECT CASE WHEN age < 25 OR age IS NULL THEN '25岁以下' 
            WHEN age >= 25 THEN '25岁及以上'
            END age_cut,COUNT(*) number
FROM user_profile
GROUP BY age_cut

IF()用法:
if(条件判断,对了怎么样,否则怎么样)
SQL刷题系列之基础篇_第37张图片

case函数知识点链接

SQL27 查看不同年龄段的用户明细

在这里插入图片描述

SELECT 
    device_id,
    gender,
    CASE
        WHEN age < 20 THEN '20岁以下'
        WHEN age >= 20 AND age <= 24 THEN '20-24岁'
        WHEN age >= 25 THEN '25岁及以上'
        WHEN age is NULL THEN '其他'
    END age_cut
FROM user_profile

不要忘记 end 这个关键字

日期函数

SQL28 计算用户8月每天的练题数量

SQL刷题系列之基础篇_第38张图片
做法有很多种(点击下面的链接有解释):
第一种:题解链接

SELECT DAY(date)day,
COUNT(question_id)question_cnt
FROM question_practice_detail
WHERE YEAR(date)="2021" and month(date)="08"
GROUP BY day;

第二种:题解链接

SELECT DAY(date) as day,
COUNT(question_id) AS question_cnt
FROM question_practice_detail
WHERE SUBSTR(date,1,7)='2021-08'
group by day

第三种:题解链接
模糊查询,like 关键字

SELECT DAY(date) day,
COUNT(question_id) question_cnt
FROM question_practice_detail
WHERE date like '%2021-08%'
GROUP BY day;

where date like ‘%-08-%’ 这样子也可以,很多写法。不过用 like 耗内存。

日期函数知识点补充 链接

SQL29 计算用户的平均次日留存率

这题较难,理解、细品即可。
在这里插入图片描述
点击 -> 题解链接
SQL刷题系列之基础篇_第39张图片

SELECT 
    COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret 
FROM 
    (SELECT DISTINCT device_id, date FROM question_practice_detail)as q1
LEFT JOIN
    (SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2
ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day)

文本函数

SQL30 统计每种性别的人数

SQL刷题系列之基础篇_第40张图片
有个小坑,用like的时候忽略了 ’,‘逗号。因为’%male%‘会包含female、male两种性别。

SELECT IF(profile LIKE '%,male%', 'male', 'female') gender, COUNT(*) number
FROM user_submit
GROUP BY gender

也可以直接用 female 去模糊匹配

SELECT IF(profile LIKE '%female%', 'female', 'male') gender, COUNT(*) number
FROM user_submit
GROUP BY gender

还有一种解法是使用SUBSTRING_INDEX,字符串截取。点击 -> 题解链接
SQL刷题系列之基础篇_第41张图片

SELECT SUBSTRING_INDEX(profile,",",-1) gender,COUNT(*) number
FROM user_submit 
GROUP BY gender;

第二种解法相对来说,占用内存较大。

SQL31 提取博客URL中的用户名

SQL刷题系列之基础篇_第42张图片

SELECT device_id, SUBSTRING_INDEX(blog_url, '/', -1) user_name
FROM user_submit

就是使用了上一题的截取字符串的方法

SQL刷题系列之基础篇_第43张图片
搬运的原文 链接

SQL32 截取出年龄

SQL刷题系列之基础篇_第44张图片

SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(profile, ',', -2),',', 1) age,
    COUNT(device_id) number
FROM user_submit
GROUP BY age

截取的方式有很多,但都是再嵌套一层函数。是第二题的变式,抬走,下一题。

窗口函数

窗口函数详细的知识点 -> 点击链接
SQL刷题系列之基础篇_第45张图片

想获取窗口函数的详细知识点请点击上面的链接

SQL33 找出每个学校GPA最低的同学

SQL刷题系列之基础篇_第46张图片

利用此前的知识,可以这样做

SELECT device_id, university, gpa
FROM user_profile
WHERE gpa in (SELECT MIN(gpa) FROM user_profile GROUP BY university)
ORDER BY university 

引入新函数——窗口函数

SELECT device_id,university,gpa 
FROM
(SELECT device_id,university,gpa,
 RANK() over (PARTITION BY university order by gpa) as rk
 FROM user_profile) a
WHERE a.rk=1

题解链接
SQL刷题系列之基础篇_第47张图片

综合练习

SQL34 统计复旦用户8月练题情况

SQL刷题系列之基础篇_第48张图片

select up.device_id, 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

搬运的题解 -> 链接
SQL刷题系列之基础篇_第49张图片

SQL35 浙大不同难度题目的正确率

SQL刷题系列之基础篇_第50张图片
经过思考上一题的做法,攻克了这一题,泪目 T v T

SELECT
qd.difficult_level, 
sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id) as correct_rate
FROM user_profile up
JOIN
question_practice_detail qpd
ON up.device_id=qpd.device_id
JOIN
question_detail qd
ON qd.question_id=qpd.question_id
WHERE up.university='浙江大学'
group by qd.difficult_level
ORDER BY correct_rate

还是有高手的呀,大意了 -> 链接
SQL刷题系列之基础篇_第51张图片
SQL刷题系列之基础篇_第52张图片
忘记了还能使用 avg函数,更简洁了。

SQL39 21年8月份练题总数

SQL刷题系列之基础篇_第53张图片

SELECT COUNT(DISTINCT device_id) did_cnt, COUNT(question_id) question_cnt
FROM question_practice_detail
WHERE MONTH(date)=8

搬运的题解 -> 链接
SQL刷题系列之基础篇_第54张图片
这题比较简单。

~

基础篇终于刷完了,不到两天,也找回了刷题的乐趣,要是早点该多好呀,不过“现在”永远不算迟,其实只要行动起来,就会解决很多焦虑、迷茫等问题。只要在路上,永远不算迟;只要能够达成目标,慢一点也无妨。共勉 Peace!

你可能感兴趣的