此篇文章所有题都来源于牛客网
记录一下自己的做题的历程,废话不多说直接开整
1.查询所有列
select * from user_profile
user_profile代表表table的意思
2.查询多列
select device_id,gender,age,university from user_profile;
3.查询结果去重
select distinct university from user_profile;
distinct的作用去除结果中重复的记录
如在university当中有两个北京大学的记录但咱们只想要一个没有重复的大学排列,就可以用distinct从而只会显示一个北京大学
4.查询结果限制返回行数
select device_id from user_profile limit 0,2;
这个题用到了limit限制返回行数,上面就是返回device_id这个列的前两行
5.将查询后的列重新命名
select device_id as user_infos_example from user_profile limit 0,2;
这里用as将device_id重新命名为user_infos_example
6.查找后排序
select device_id,age from user_profile order by age asc;
asc表示升序,desc表示降序。
7.查找后多列排序
select device_id,gpa,age from user_profile order by gpa,age;
order by默认按照升序排序
如果是降序语句如下
SELECT device_id, gpa, age
FROM user_profile
ORDER BY gpa DESC, age DESC;
8.查找后降序排列
select device_id,gpa,age from user_profile order by gpa desc,age desc;
9.查找学校是北大的学生信息
select device_id,university from user_profile where university='北京大学';
利用where去进行条件定义
这里也介绍一下like,在这里用一下like做一下这道题
select device_id,university from user_profile where university like '%北京%';
like就是模糊匹配
%表示匹配0个或多个字符
_表示只匹配一个字符
10.查找年龄大于24岁的用户信息
select device_id,gender,age,university from user_profile where age is not null and age>24;
and表示两边的条件要全部成立
11.查找某个年龄段的用户信息
select device_id,gender,age from user_profile where age between 20 and 23;
between and 表示一个区间
12.查找除复旦大学的用户信息
select device_id,gender,age,university from user_profile where university!='复旦大学';
!=表示不等于
13.过滤空值
select device_id,gender,age,university from user_profile where age is not null;
14.高级操作符练习
select device_id,gender,age,university,gpa from user_profile where gender='male' and gpa>3.5;
where条件和and的使用
15.高级操作符
select device_id,gender,age,university,gpa from user_profile where gender='male' and gpa>3.5;
and替换成or就行
16.where in 和Not in
select device_id,gender,age,university,gpa from user_profile where university in('北京大学','复旦大学','山东大学');
17.操作符混合使用
select device_id,gender,age,university,gpa from user_profile where gpa>3.5 and university='山东大学' or gpa>3.8 and university='复旦大学';
就是and和or的混合使用
18.查找GPA的最高值
select max(gpa) from user_profile where university='复旦大学';
max最大值的使用
19.计算男生人数以及平均GPA
select count(gender) as male_num,round(avg(gpa),1) avg_gpa from user_profile where gender='male';
count是计算数量总数,avg表示平均数,as为命名,round表示保留几位小数,1就是表示留一位小数;
20.分组计算练习题
select gender,university,count(device_id) as user_num,avg(active_days_within_30) as avg_active_days,avg(question_cnt) as avg_question_cnt from user_profile group by gender,university;
count这里的聚合函数是关于group by后的内容进行聚合的及这个例子是根据性别和大学进行分类
21.分组过滤练习题
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 university having avg_question_cnt<5 or avg_answer_cnt<20;
having是针对group by 进行过滤
22.分组排序练习题
select university,avg(question_cnt) as avg_question_cnt from user_profile group by university order by avg_question_cnt;
先将所选出来的按照学校进行分组在按照升序进行排序
23.浙江大学用户题目回答
select device_id,question_id,result from question_practice_detail where device_id in (select device_id from user_profile where university='浙江大学') order by question_id;
用两个where条件,后一个where条件是用来确定device_id的范围是在浙江大学的范围内,然后进行排序
select qpd.device_id, qpd.question_id, qpd.result
from question_practice_detail as qpd
inner join user_profile as up
on up.device_id=qpd.device_id and up.university='浙江大学'
order by question_id
inner join这里介绍一下这里它的作用,它是一个连接字符,后面加上成立的条件,如果成立的条件成立那么那个数据才会呈现出来.
24.统计每个学校的打过题的用户的平均答题数
select university,count(question_id)/count(distinct qpd.device_id) as avg_answer_cnt from question_practice_detail as qpd inner join user_profile as up on qpd.device_id=up.device_id group by university;
distinct的作用就是count的一种作用就是去掉重复的,从而能计算出人数并且不重复。
25.统计每个学校各难度的用户平均刷题数
select
university,
difficult_level,
round(count(qpd.question_id) / count(distinct qpd.device_id), 4) as avg_answer_cnt
from question_practice_detail as qpd
left join user_profile as up
on up.device_id=qpd.device_id
left join question_detail as qd
on qd.question_id=qpd.question_id
group by university, difficult_level
left join表连接的一种方式,会保持左表全部,而另一个表需要满足条件才能使用在最终的表中,如上面的例子当中第一个left join会保持qpd的全部内容,而up表中需要满足up.device_id=qpd.device_id这个条件。
26.统计每个用户的平均的刷题数
select university,difficult_level,round(count(qpd.question_id)/count(distinct qpd.device_id),4) from question_practice_detail as qpd left join user_profile as up on up.device_id=qpd_device_id left join question_detail as qd on qd.question_id=qpd.question_id where university='山东大学' group by university,difficult_level;
和上一道题差不多就是多一个条件,大学为山东大学
27.查找山东大学或者为男生的信息
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';
union all 查询结果不去重,语句很好理解。
28.计算25岁以上和以下的用户数量
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;
case的使用,这个使用很明显和c语言的差不多,end之后是对这一列的命名
29.查看不同年龄段的用户明细
select device_id,gender,case when age>=25 then '25岁及以上' when age>=20 then '20-24岁' when age <20 then '20岁以下' else '其他' end as age_cut from user_profile;
同上一道题一样的知识点
30.计算用户8月每天的练题数量
select day(date) as day,count(question_id) as question_cnt from question_practice_detail where month(date)=8 and year(date)=2021 group by date;
这里有几个日期的函数
day()提取日期中天那个属性
month() 提取日期中的月
year() 提取日期中的年的属性
31.计算用户的平均次日留存率
select count(date2)/count(date1) as avg_ret from (select distinct qpd.device_id,qpd.date as date1,uniq_id_date.date as date2 from question_practice_detail as qpd left join(select distinct device_id,date from question_practice_detail) as uniq_id_date on qpd.device_id=uniq_id_date.device_id and date_add(qpd.date,interval 1 day)=uniq_id_date.date) as id_last_next_date;
这道题就是通过device_id进行连接,计算第一天的人数,在计算第二天的人数,第二天的除以第一天的人数就是所要的概率
32.统计每种性别的人数
select if(profile like '%female','female','male') as gender,count(*) as number from user_submit group by gender;
用if进行判断,like模糊匹配。
这里在介绍一个字符串截取的函数substring_index(str,delim,count)
str是要截取的字符串,delim是分隔符,count是这个需要截取字符的位置,如这个例子sql语句就如下
SELECT SUBSTRING_INDEX(profile,",",-1) gender,COUNT(*) number
FROM user_submit
GROUP BY gender;
33.截取出年龄
select substring_index(substring_index(profile,',',3),',',-1) as age,count(device_id) from user_submit group by age;
和上一道题一样多几个条件去使用就可以了
34.提取博客url中的用户名
select device_id,substring_index(blog_url,'/',-1) as user_name from user_submit;
还是使用substring_index()这个函数
35.找出每个学校GPA最低的同学
select device_id,university,gpa from user_profile where (university,gpa) in (select university,min(gpa) from user_profile group by university) order by university;
加一个where条件去限制一下university和gpa就可以了
36.统计复旦用户八月练题情况
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;
多了一个sum函数其它的还是很好理解的
37.浙大不同难度题目正确率
select difficult_level,
avg(if(qpd.result='right', 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 qd.question_id = qpd.question_id
where up.university = '浙江大学'
group by qd.difficult_level
order by correct_rate asc;
这个题就是先计算出正确率然后利用device_id和question_id连接两个表最后在加条件分组排序;
38.21年8月份练题总数
select count(distinct device_id) as did_cnt,count(question_id) as question_cnt from question_practice_detail where date like "2021-08%";
这个题比较简单