2022-07-28

字数统计: 1.5k字 | 阅读时长: 7分

MySQL牛客网习题

查询结果去重

1 2	select distinct university from user_profile; select university from user_profile group by university;

查询结果并限制返回行数

1	select device_id from user_profile limit 2;

将查询后的列重新命名

1	select device_id as user_infos_example from user_profile limit 2;

查找学校是北大的学生信息

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

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

1	select device_id, gender, age, university from user_profile where age > '24';

查找某个年龄段的用户信息

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

查找除复旦大学的用户信息

select device_id, gender, age, university from user_profile 
#where university <> '复旦大学';
#where university != '复旦大学';
where university not in ('复旦大学');

用where过滤空值练习

select device_id, gender, age, university from user_profile  
#where age != 'NULL';
#where age is not NULL;
where age <> 'NULL';

高级操作符练习

1
2

select device_id, gender, age, university, gpa from user_profile where gender = 'male' and gpa > '3.5';
select device_id, gender, age, university, gpa from user_profile where university = '北京大学' or gpa > '3.7';

Where in 和Not in

1
2

select device_id, gender, age, university, gpa from user_profile where university in ('北京大学', '复旦大学', '山东大学');
select device_id, gender, age, university, gpa from user_profile where (university = '山东大学' and gpa > 3.5) or (university = '复旦大学' and gpa > 3.8);

查看学校名称中含北京的用户

1	select device_id, age, university from user_profile where university like '%北京%';

查找GPA最高值

1	select gpa from user_profile where university = '复旦大学' order by gpa desc limit 1;

计算男生人数以及平均GPA

1 2	select count(gender) as male_num, round(avg(gpa), 1) as avg_gpa from user_profile where gender = 'male'; #round(column, 1)表示保留一位小数

分组计算练习题

1	select gender, university, count(gender) as user_num, avg(active_days_within_30) as avg_active_day, avg(question_cnt) as avg_question_cnt from user_profile group by university, gender;

分组过滤练习题

1
2
3

select university, avg(question_cnt) as avg_question_cnt, avg(answer_cnt) as avg_answer_cnt from user_profile group by university having avg_question_cnt < 5 or avg_answer_cnt < 20;
#WHERE 关键字无法与合计函数一起使用；
#FROM - ON - JOIN - WHERE - GROUP BY - WITH - HAVING - SELECT - DISTINCT - ORDER BY - LIMIT

分组排序练习题

1	select university, avg(question_cnt) as avg_question_cnt from user_profile group by university order by avg_question_cnt asc;

浙江大学用户题目回答情况

# 连接查询
select u.device_id, q.question_id, q.result from user_profile u, question_practice_detail q where u.university = '浙江大学' and q.device_id = u.device_id;
# 内连接查询
select q.device_id, q.question_id, q.result from question_practice_detail q inner join user_profile on q.device_id = user_profile.device_id and university = '浙江大学';
# 子查询
select device_id,question_id,result from question_practice_detail where device_id=(select device_id from user_profile where university="浙江大学");

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

1	select university, count(q.question_id) / count(distinct(q.device_id)) as avg_answer_cnt from question_practice_detail q, user_profile u where q.device_id = u.device_id group by university;

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

select u.university, qd.difficult_level as difficult_level,count(q.question_id) / count(distinct(q.device_id)) as avg_answer_cnt from user_profile u, question_practice_detail q, question_detail qd where q.device_id = u.device_id and q.question_id = qd.question_id group by u.university, qd.difficult_level;

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

select u.university, qd.difficult_level, count(q.question_id) / count(distinct(q.device_id)) as avg_answer_cnt from 
user_profile u, question_practice_detail q, question_detail qd 
where q.device_id = u.device_id and q.question_id = qd.question_id and u.university = '山东大学'
group by qd.difficult_level;

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

1
2

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'
#注意不能使用or或者union,因为这两个都是自动去重的

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

select if(age >= 25, '25岁及以上', '25岁以下') as age_cut, count(device_id) as number 
from user_profile group by age_cut;
select '25岁及以上' as age_cut, count(device_id) as number 
from user_profile group by age_cut union all
select '25岁以下' as age_cut, count(device_id) as number 
from user_profile group by age_cut;

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

#方法一: union
select device_id, gender, age_cut from(select id,device_id, gender, '20-24岁' as age_cut from user_profile where age >=20 and age <= 24
union select id,device_id, gender, '20岁以下' as age_cut from user_profile where age < 20
union select id,device_id, gender, '25岁及以上' as age_cut from user_profile where age >= 25
union select id,device_id, gender, '其他' as age_cut from user_profile where age is NULL) as t1 order by id;
#方法二: case
select device_id, gender, 
case 
	when age < 20 then '20岁以下' 
	when age < 25 then '20-24岁' 
	when age >= 25 then '25岁及以上' 
	else '其他'
end age_cut 
from user_profile;

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

select day(date) as day, count(question_id) as question_cnt 
from question_practice_detail where date like '2021-08%' group by day(date);
# where date regexp '2021-08'
# where substring(date,1,7) = '2021-08'

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

select count(distinct q2.device_id, q2.date) / count(distinct q1.device_id, q1.date) as avg_ret
from question_practice_detail as q1 left join question_practice_detail as q2 on q1.device_id = q2.device_id
and datediff(q2.date, q1.date) = 1;
# q1代表前一天

对于SQL语句中where和on的理解_不爱吃垃圾的小王的博客-CSDN博客_sql中的on的用法

统计每种性别的人数

1	select substring_index(profile, ',', '-1') as gender, count(device_id) as number from user_submit group by gender;

提取博客URL中的用户名

1	select device_id, substring_index(blog_url, '/', '-1') as user_name from user_submit;

截取出年龄

1	select substring_index(substring_index(profile, ',', '-2'), ',', '1') as age, count(device_id) as number from user_submit group by age;

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

1 2	select device_id, university, gpa from user_profile u where gpa = (select min(gpa) from user_profile where university = u.university) order by university;

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

select u.device_id, university, sum(if(result is not null, 1, 0))as question_cnt, 
sum(if(result = 'right', 1, 0))as right_question_cnt from user_profile u
left join question_practice_detail q on u.device_id = q.device_id and month(q.date) = '08'
where university = '复旦大学' group by u.device_id;

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

select difficult_level, avg(IF(result = 'right',1, 0)) as correct_rate
from user_profile u, question_practice_detail qpd, question_detail qd
where u.device_id = qpd.device_id and qd.question_id = qpd.question_id
and university = '浙江大学' group by difficult_level order by correct_rate;

查找后排序

1	select device_id, age from user_profile order by age asc;

查找后多列排序

1	select device_id, gpa, age from user_profile order by gpa, age;

查找后降序排列

1	select device_id, gpa, age from user_profile order by gpa desc, age desc;

21年8月份练题总数

1	select count(distinct(device_id)) as did_cnt, count(question_id) as question_cnt from question_practice_detail where substring(date,1,7) = '2021-08';