sql基础语句题练

此篇文章所有题都来源于牛客网

牛客网在线编程_SQL篇_非技术快速入门

记录一下自己的做题的历程，废话不多说直接开整

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%";

这个题比较简单