首页 › MySQL › 第 5 章 › 习题 › 牛客网练习题

✏️ 牛客网练习题

第 5 章 · 数据查询 · 共 39 题 · user_profile 表

🛠 建表与测试数据（练习前先执行）

第 21～35 题还需用到 question_practice_detail、question_detail、user_submit 等表（牛客网在线环境自带，本地练习时可在牛客网题目页面运行）。

sql
# 牛客网练习题
# 建表

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32)  NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong',3.8);

# 建表结束

1. 查询所有列 select * from table

查看参考答案（可复制）
sql
select * from user_profile

2. 查询多列 select columns

查看参考答案（可复制）
sql
select device_id,gender,age,university from user_profile

3. 查询结果去重 distinct

查看参考答案（可复制）
sql
select university from user_profile group by 1

4. 查询结果限制返回行数 limit

查看参考答案（可复制）
sql
select device_id from user_profile limit 2

5. 将查询后的列重新命名 as重命名

查看参考答案（可复制）
sql
select device_id as user_infos_example from user_profile limit 2

6. 查找学校是北大的学生信息 where查询

查看参考答案（可复制）
sql
select device_id,university from user_profile where university = '北京大学'

7. 查找年龄大于24岁的用户信息 where查询

查看参考答案（可复制）
sql
select device_id,gender,age,university from user_profile where age > 24

8. 查找某个年龄段的用户信息 where and

查看参考答案（可复制）
sql
select device_id,gender,age from user_profile where age >= 20 and age <= 23

9. 查找除复旦大学的用户信息 where !=

查看参考答案（可复制）
sql
select device_id,gender,age,university from user_profile where university != '复旦大学'

10. 用where过滤空值练习 where is not null

查看参考答案（可复制）
sql
select device_id,gender,age,university from user_profile where age is not null

11. 高级操作符练习(1) where and

查看参考答案（可复制）
sql
select device_id,gender,age,university,gpa from user_profile where gpa > 3.5 and gender = 'male'

12. 高级操作符练习(2） where or

查看参考答案（可复制）
sql
select
device_id
,gender
,age
,university
,gpa
from user_profile
where university = '北京大学' or gpa > 3.7

13. Where in 和Not in where in

查看参考答案（可复制）
sql
select
device_id
,gender
,age
,university
,gpa
from user_profile
where university in ('北京大学', '复旦大学', '山东大学')

14. 操作符混合运用 where and or

查看参考答案（可复制）
sql
select
device_id
,gender
,age
,university
,gpa
from user_profile
where (gpa > 3.5 and university = '山东大学')
or (gpa > 3.8 and university = '复旦大学')

15. 查看学校名称中含北京的用户 where like

查看参考答案（可复制）
sql
select
device_id
,age
,university
from user_profile
where university like '%北京%'

16. 查找GPA最高值 max

查看参考答案（可复制）
sql
select
round(max(gpa), 1) as max_gpa
from user_profile
where university = '复旦大学'

17. 计算男生人数以及平均GPA avg

查看参考答案（可复制）
sql
select
count(1) as male_num
,avg(gpa) as avg_gpa
from user_profile
where gender = 'male'

18. 分组计算练习题 count、avg、group by

查看参考答案（可复制）
sql
select
gender
,university
,count(device_id) as user_num
,round(avg(active_days_within_30), 1) as avg_active_day
,round(avg(question_cnt), 1) as avg_question_cnt
from user_profile
group by 1, 2

19. 分组过滤练习题 avg、group by、having过滤

查看参考答案（可复制）
sql
select
university
,round(avg(question_cnt), 3) as avg_question_cnt
,round(avg(answer_cnt), 3) as avg_answer_cnt
from user_profile
group by 1
having avg(question_cnt) < 5 or avg(answer_cnt) < 20

20. 分组排序练习题 order by

查看参考答案（可复制）
sql
select
university
,avg(question_cnt) as avg_question_cnt
from user_profile
group by 1
order by 2

21. 浙江大学用户题目回答情况 left join表连接 + where筛选

查看参考答案（可复制）
sql
select
a.device_id as device_id
,b.question_id as question_id
,b.result as result
from user_profile a
left join question_practice_detail b
on a.device_id = b.device_id
where a.university = '浙江大学'
order by 2

22. 统计每个学校的答过题的用户的平均答题数 left join表连接 + 聚合

查看参考答案（可复制）
sql
select
b.university as university
,round(count(1) / count(distinct a.device_id), 4) as avg_answer_cnt
from question_practice_detail a
left join user_profile b
on a.device_id = b.device_id
group by 1
order by 1

23. 统计每个学校各难度的用户平均刷题数 left join表连接 + 聚合

查看参考答案（可复制）
sql
select
b.university as university
,c.difficult_level as difficult_level
,count(1) / count(distinct a.device_id) as avg_answer_cnt
from question_practice_detail a
left join user_profile b
on a.device_id = b.device_id
left join question_detail c
on a.question_id = c.question_id
group by 1, 2

24. 统计每个用户的平均刷题数 left join表连接 + where筛选

查看参考答案（可复制）
sql
select
b.university as university
,c.difficult_level as difficult_level
,count(1) / count(distinct b.device_id) as avg_answer_cnt
from question_practice_detail a
left join user_profile b
on a.device_id = b.device_id
and b.university = '山东大学'
left join question_detail c
on a.question_id = c.question_id
where b.device_id is not null
group by 1, 2

25. 查找山东大学或者性别为男生的信息 union all

查看参考答案（可复制）
sql
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'

26. 计算25岁以上和以下的用户数量 case when

查看参考答案（可复制）
sql
select
case when age < 25 or age is null then '25岁以下'
     when age >= 25 then '25岁及以上'
end as age_cut
,count(1) as number
from user_profile
group by 1

27. 查看不同年龄段的用户明细 case when

查看参考答案（可复制）
sql
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

28. 计算用户8月每天的练题数量 left函数

查看参考答案（可复制）
sql
select
day(`date`) as day
,count(1) as question_cnt
from question_practice_detail
where left(`date`, 7) = '2021-08'
group by 1

29. 计算用户的平均次日留存率 date_add函数、with临时表

查看参考答案（可复制）
sql
-- 用户注册表
-- 用户活跃表
with tmp
as(
    select
    device_id
    ,date
    from question_practice_detail
    group by 1, 2
)

select
count(b.date) / count(a.date)
from tmp a
left join tmp b
on a.device_id = b.device_id
and a.date = date_sub(b.date, interval 1 day)

30. 统计每种性别的人数 substring_index函数

查看参考答案（可复制）
sql
select
substring_index(profile,',',-1) as gender
,count(device_id)
from user_submit
group by 1

31. 提取博客URL中的用户名 substring_index函数

查看参考答案（可复制）
sql
select
device_id
,substring_index(blog_url, '/', -1)
from user_submit

32. 截取出年龄 substring_index函数

查看参考答案（可复制）
sql
select
substring_index(substring_index(profile, ',', -2), ',', 1) as age
,count(1) as number
from user_submit
group by 1

33. 找出每个学校GPA最低的同学 聚合+left join+from子查询

查看参考答案（可复制）
sql
select
t2.device_id as device_id
,t1.*
from
(
    select
    university
    ,min(gpa) as gpa
    from user_profile
    group by 1
) t1
left join
(
    select
    university
    ,device_id
    ,gpa
    from user_profile
) t2
on t1.university = t2.university
and t1.gpa = t2.gpa
order by 2

34. 统计复旦用户8月练题情况 union all

查看参考答案（可复制）
sql
select
a.device_id as device_id
,b.university as university
,count(1) as question_cnt
,sum(if(result = 'right', 1, 0)) as right_question_cnt
from question_practice_detail a
left join user_profile b
on a.device_id = b.device_id
and left(a.date , 7) = '2021-08'
and b.university = '复旦大学'
where b.device_id is not null
group by 1, 2

union all
select
a.device_id as device_id
,a.university as university
,0 as question_cnt
,0 as right_question_cnt
from user_profile a
left join question_practice_detail b
on a.device_id = b.device_id
and a.university = '复旦大学'
where b.device_id is null
and a.university = '复旦大学'

35. 浙大不同难度题目的正确率 sumif

查看参考答案（可复制）
sql
select
c.difficult_level as difficult_level
,sum(if(a.result = 'right', 1, 0)) / count(1) as correct_rate
from question_practice_detail a
left join user_profile b
on a.device_id = b.device_id
and b.university = '浙江大学'
left join question_detail c
on a.question_id = c.question_id
where b.device_id is not null
group by 1
order by 2

36. 查找后排序 order by

查看参考答案（可复制）
sql
select
device_id
,age
from user_profile
order by 2

37. 查找后多列排序 order by

查看参考答案（可复制）
sql
select
device_id
,gpa
,age
from user_profile
order by 2, 3

38. 查找后降序排列 order by desc

查看参考答案（可复制）
sql
select
device_id
,gpa
,age
from user_profile
order by 2 desc, 3 desc

39. 21年8月份练题总数 count(distinct)

查看参考答案（可复制）
sql
select
count(distinct device_id) as did_cnt
,count(1) as question_cnt
from question_practice_detail
where left(`date`, 7) = '2021-08'
←
上一个课堂练习 2
下一个SQL 查询经典 50 题
→