Mysql练习
牛客SQL快速入门
01 基础查询
SQL3 查询结果去重
题目：现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据。
1.使用distinct关键字去重
select distinct university from user_profile
2.使用group by实现去重
select university
from user_profile
group by university
SQL4 查询结果限制返回行数
题目：现在运营只需要查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果。
select device_id
from user_profile
limit 0,2 -- 0,可省略
SQL5 将查询后的列重新命名
题目5：现在你需要查看前2个用户明细设备ID数据，并将列名改为 ‘user_infos_example’,，请你从用户信息表取出相应结果。
select device_id as 'user_infos_example' -- as可省略 ''可省略
from user_profile
limit 0,2 -- 0,可省略
02 条件查询
SQL6 查找学校是北大的学生信息
题目：现在运营想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表中取出满足条件的数据，结果返回设备id和学校。
select device_id, university from user_profile where university = '北京大学'
-- 要加''
SQL8 查找某个年龄段的用户信息
题目：现在运营想要针对20岁及以上且23岁及以下的用户开展分析，请你取出满足条件的设备ID、性别、年龄。
用户信息表：user_profile
- 使用and
select device_id, gender, age
from user_profile
where age >= 20 and age <= 23
- 使用between and
select device_id, gender, age
from user_profile
where age between 20 and 23
SQL9 查找除复旦大学的用户信息
题目：现在运营想要查看除复旦大学以外的所有用户明细包括的字段有 device_id、gender、age、university，请你取出相应数据
示例：user_profile
select device_id,gender,age,university
FROM user_profile
WHERE university not like '复旦大学';//1.模糊查询 列值中不含有复旦大学四个字儿
WHERE university not in ('复旦大学');//2.in+集合，集合中放的是列值
WHERE university != '复旦大学';//3.这个列值不是复旦大学
SQL10 用where过滤空值练习
题目：现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息。
select device_id, gender, age, university
from user_profile
where age != ''
-- where age is not null
SQL12 高级操作符练习(2）
题目：现在运营想要找到学校为北大或GPA在3.7以上(不包括3.7)的用户进行调研，请你取出相关数据**（使用OR实现）**
select device_id,gender,age,university,gpa
from user_profile
where university ="北京大学" or gpa > 3.7
SQL13 Where in 和Not in
题目：现在运营想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据.
select device_id, gender, age, university, gpa
from user_profile
where university in('北京大学', '复旦大学', '山东大学')
SQL15 查看学校名称中含北京的用户
题目：现在运营想查看所有大学中带有"北京"的用户的信息(device_id,age,university)，请你取出相应数据。
select device_id, age, university
from user_profile
where university like '北京%'
匹配串中可包含如下四种通配符：
_：匹配任意一个字符；
%：匹配0个或多个字符；
[ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )；
[^ ]：不匹配[ ]中的任意一个字符。
03 高级查询
SQL17 计算男生人数以及平均GPA
题目：现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少，用以辅助设计相关活动，请你取出相应数据。结果使用round保留到小数点后面1位
select count(gender) 'male_num', round(avg(gpa), 1) 'avg_gpa'
from user_profile
where gender = 'male'
SQL18 分组计算练习题
题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。
select gender, university, count(*) 'user_num', avg(active_days_within_30) 'avg_active_day', avg(question_cnt) 'avg_question_cnt'
from user_profile
group by gender, university
order by gender, university asc
SQL38 查找后降序排列
题目：现在运营想要取出用户信息表中对应的数据，并先按照gpa降序排列、gpa相同的按照年龄降序排序输出，请取出相应数据。
表: user_profile
思路: 注意 order by 后面两个desc怎么写
代码:
select device_id, gpa, age
from user_profile
order by gpa desc, age desc
04 多表查询
SQL21 浙江大学用户题目回答情况
题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据
select qpd.device_id, qpd.question_id, qpd.result
from question_practice_detail as qpd
inner join user_profile as up
on qpd.device_id = up.device_id and up.university='浙江大学'
order by qpd.question_id asc
SQL23 统计每个学校各难度的用户平均刷题数
题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据
在标准 SQL 规范中，GROUP BY 的字段必须出现在 SELECT 语句中
-- 不同学校 group by university
-- 不同难度 group by result
-- 用户平均答题量(结果在小数点位数保留4位，4位之后四舍五入)： count(question_id)/count(device_id)
select university, qd.difficult_level as difficult_level, round(count(qpd.question_id)/count(distinct qpd.device_id), 4) as avg_answer_cnt
from user_profile as up
inner join question_practice_detail as qpd
on up.device_id = qpd.device_id
inner join question_detail as qd
on qpd.question_id = qd.question_id
group by university, difficult_level
SQL25 查找山东大学或者性别为男生的信息
题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。
表: user_profile
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'
05 必会的常用函数
SQL26 计算25岁以上和以下的用户数量
题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量
表: user_profile
case when then else end
select
case
when age < 25 or age is null then '25岁以下'
when age >= 25 then '25岁及以上'
end as age_cut, # 将所有age符合条件的加上名为age_cut这一列
count(device_id) as number
from user_profile
group by age_cut
if(ex1, ex2, ex3)
select if(age >= 25, '25岁及以上', '25岁以下') as age_cut, count(device_id) as number
from user_profile
group by age_cut
SQL27 查看不同年龄段的用户明细
题目：现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。（注：若年龄为空请返回其他。）
表: user_profile
select device_id, gender,
case
when age < 20 then '20岁以下'
when age >= 20 and age <= 24 then '20-24岁'
when age >= 25 then '25岁及以上'
else '其他'
end as age_cut
from user_profile
age20-24岁可表示为:
age >= 20 and age <= 24age between 20 and 24age in (20, 21, 22, 23, 24)
SQL28 计算用户8月每天的练题数量
题目：现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。
表: question_practice_detail
思路: 用where筛选
代码:
select day(date) as day, count(question_id) as question_cnt
from question_practice_detail
# where date like '2021-08-%'
where year(date) = 2021 and month(date) = 08
group by date
SQL29 计算用户的平均次日留存率
题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的留存率。请你取出相应数据。
表: question_practice_detail
思路: 对同一张表连接查询, 要求两张表按照device_id连接并且相同device_id的情况下q2.date = q1.date + 1, 对两张表都要去重, 因为同一天同一台设备可能多次参与答题, 此时q1每一天答题的人, q2剩下符合条件的第二天答题的人, 次日留存率 = 第二天答题的天数 / 答题的总体天数
代码:
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 统计每种性别的人数
题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果
表: user_submit
**思路: **
- 使用
if(ex1, ex2, ex3)对profile进行判断指定和分组 - 使用
subtring_index(col, sep, num)对profile进行取值, 指定
代码:
-
select substring_index(profile, ",", -1) as gender, count(device_id) as number from user_submit group by gender -
select if(profile like '%female', 'female', 'male') as gender, count(device_id) as number from user_submit group by gender
SQL32 截取出年龄
题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个年龄的用户分别有多少参赛者，请取出相应结果
表: user_submit
select substring(profile, 12, 2) as age, count(device_id) as number
from user_submit
group by age
SQL 规定：SELECT 语句中，如果使用了聚合函数（比如 COUNT），那么非聚合的列（这里是 age）必须出现在 GROUP BY 里，否则数据库无法确定 age 应该如何取值。
SQL33 找出每个学校GPA最低的同学
题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。
表: user_profile
思路: right join, 先用分组查出university及对应的min(gpa), 作为u2, 再根据u2查出university, min(gpa)对应的device_id
代码:
select u1.device_id, u1.university, u1.gpa
from user_profile as u1
right join
(select university, min(gpa) as gpa
from user_profile
group by university) as u2
on u1.university = u2.university and u1.gpa = u2.gpa
order by university
思路: 窗口函数
row_number() over (partition by university order by gpa) 对各个 university 根据 gpa 进行升序排序并逐个填充
代码:
select device_id, university, gpa
from (
select *,
row_number() over (partition by university order by gpa) as rk
from user_profile
) as t1
where rk = 1
order by university
06 综合练习
SQL34 统计复旦用户8月练题情况
题目： 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.
思路: 根据device_id进行分组, 左连接以后记的在聚合函数的if中判断月份, 答题数目的聚合可以根据是否存在question_id, 如果存在则为1, 否则为0
代码:
select # 注意时间的筛选
up.device_id, university, sum(if(question_id and month(date) = 8, 1, 0)) as question_cnt,
sum(if(result and month(date) = 8, 1, 0)) as right_question_cnt
from user_profile as up
left join question_practice_detail as qpd
on up.device_id = qpd.device_id
where university = '复旦大学' # where可以写在on后, group by前
group by up.device_id
SQL35 浙大不同难度题目的正确率
题目：现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。
**思路: ** 三表联查, 答题正确率要从question_practice_detail 题目练习表中查询, 且$ 答题正确率 = 答题正确的个数/ 已答题目总个数 $
代码:
select
qd.difficult_level,
sum(if(qpd.result = 'right', 1, 0))/sum(if(qpd.question_id, 1, 0)) as correct_rate
from user_profile as up
inner join question_practice_detail as qpd
on up.device_id = qpd.device_id
inner join question_detail as qd
on qpd.question_id = qd.question_id
where up.university = '浙江大学'
group by qd.difficult_level
order by correct_rate
SQL39 21年8月份练题总数
题目： 现在运营想要了解2021年8月份所有练习过题目的总用户数和练习过题目的总次数，请取出相应结果
表: question_practice_detail
思路: 使用count计数, 使用distinct去重
代码:
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
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
