SQL如何使用各种函数

1.如何根据特定名称去重

使用函数 distinct

去掉university重复的数据
select distinct university from user_profile

2.只查询前2条数据

使用limit函数

select device_id from user_profile limit 0,2
//查询的结果重新命名
select device_id as user_infos_example from user_profile limit 0,2

3.分组过滤

注意使用聚合函数结果作为筛选条件时，不能使用where，使用having语法
题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校

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'

4.分组排序

题目：现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据。

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

5.统计每个答过题用户的平均数

select
university,(count(q.question_id) / count(distinct(u.device_id)))
from user_profile u
 join question_practice_detail q on  u.device_id=q.device_id
group by university

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

解题思路：我们首先明确需要跟据什么进行分组（学校，难度）
3张表：用户信息表，题库练习明细表，练习明细对应难度表
首先查出通过学校分组，使用count（刷的题目id）除去，count（去重以后的用户设备id） 得到每个学校的平均刷题数，然后通过难度表的难度进行分组即可

select
u.university,qd.difficult_level,
count(qpd.question_id) / count(distinct(qpd.device_id))
as difficult_level
from user_profile u
join question_practice_detail qpd on u.device_id=qpd.device_id
join question_detail qd on qpd.question_id= qd.question_id
group by u.university,qd.difficult_level

7：统计某个学校的平均刷题数

题目：运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据
解题思路：我们首先明确需要跟据什么进行分组（学校,难度）
3张表：用户信息表，题库练习明细表，练习明细对应难度表
首先查出通过学校分组，刷题数通过统计函数count实现，使用count（刷的题目id）除去，count（去重以后的用户设备id） 得到每个学校的平均刷题数，然后通过难度表的难度进行分组即可并且要加上条件学校为山东大学

select u.university ,qd.difficult_level,
count(qpd.question_id) / count(distinct(qpd.device_id))
from user_profile u
join question_practice_detail qpd on u.device_id=qpd.device_id
join question_detail qd on qpd.question_id=qd.question_id
and u.university='山东大学'
group by u.university ,qd.difficult_level

8.查出山东大学或者性别为男生的信息

要求：结果不能去重
最开始写法(这种写法会去重)

select device_id , gender, age ,gpa from user_profile 
where
university ='山东大学' or gender = 'male'

如何不去重 使用union all

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"

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

1.这题主要用到了if函数
解题思路：
首先判断age小于25 或者age等于null的 通过这个查出来分组，统计一下个数（id）

select 
if(age<25 or age  is null ,"25岁以下","25岁及以上") as age_cut,
count(id) as num 
from user_profile
group by  age_cut

本人认为这题可以用case when 实现

select 
case 
when age<25 or age is null  then '25岁以下'
when age>=25 then '25岁及以上' end age_cut,
count(id) as number
from user_profile
group by age_cut

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

题目：现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。（注：若年龄为空请返回其他。）
解题思路 本题用case when 即可解决

select device_id,gender,
case 
when age<20 then '20岁以下'
when age between 20 and 24 then '20-24岁'
when age>=25 then '25岁及以上'
else '其他'
end age_cut
from user_profile

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

解题思路：我们通过每天进行分组，所有说我们需要用到函数可以取到日期里面的天数可以用到DAY()函数，然后统计练题的数量，最后条件把日期截取以后判断一下，在通过每天分组即可

select
DAY(date) as day,count(question_id) as question_cnt
from question_practice_detail
where substr(date,1,7)='2021-08'
group by day

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

题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据
解题思路：这题意思就是假设用户第一天来了，第二天也来了，需要得到这种概率的平均数，所有我们需要得到第一天的所有用户的数据，然后除去用户第二天也来的数据，首先是日期上的限制还是用户ID上的限制。可以使用用datediff区分第一天和第二天在线的时间是否一样
用到left outer join 表的自连接

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 outer join question_practice_detail as q2
on q1.device_id=q2.device_id and DATEDIFF(q2.date,q1.date)=1

13.统计每种性别的人数

现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果
substring_index(profile,‘,’,-1) 截取最后一个逗号后面的数据

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

14.截取出年龄

substring_index 嵌套玩法

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

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

现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.
需要用到sum函数相加 然后在sum里面进行一下判断

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 as u LEFT JOIN question_practice_detail as q
ON u.device_id=q.device_id
WHERE university='复旦大学' and (month(date)=8 or date is null)
GROUP BY u.device_id;

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

现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。
解题思路，需要得到答题正确的总数量使用sum（）加if（）实现，然后除去答题的数量，然后得到正确率，然后条件为学校为浙江大学，并且按照升序输出

select 
    d.difficult_level,
    sum(if(result = 'right', 1, 0)) / count(*) as correct_rate
from 
    user_profile u, question_practice_detail qp, question_detail d
where 
    u.university = '浙江大学' 
    and u.device_id = qp.device_id
    and qp.question_id = d.question_id
group by d.difficult_level
order by correct_rate

表的插入还有提示操作

1.如何去备份一个表

创建了一张新表exam_record_before_2021用来备份2021年之前的试题作答记录，结构和exam_record表一致，请将2021年之前的已完成了的试题作答纪录导入到该表。

insert into exam_record_before_2021 
    (uid, exam_id, start_time, submit_time, score) 
    select uid, exam_id, start_time, submit_time, score from exam_record
    where year(submit_time) < '2021' and score is not null

2.无论如何都要插入成功某条记录

现在有一套ID为9003的高难度SQL试卷，时长为一个半小时，请你将 2021-01-01 00:00:00 作为发布时间插入到试题信息表examination_info（其表结构如下图），不管该ID试卷是否存在，都要插入成功，请尝试插入它。

/*REPLACE INTO 首先尝试插入数据到表中,
如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，
然后插入新的数据;否则，直接插入新数据。
要注意的是：插入数据的表必须有主键或者是唯一索引！
否则的话，REPLACE INTO 会直接插入数据，这将导致表中出现重复的数据。
*/
replace into examination_info values(null,9003,"SQL","hard",90,"2021-01-01 00:00:00")