数据库SQL入门题目及答案记录

Triangulum

已于 2022-12-26 02:11:42 修改

阅读量2.7k

点赞数 3

文章标签：数据库 sql 大数据

于 2022-12-04 22:44:10 首次发布

本文链接：https://blog.csdn.net/weixin_40634691/article/details/128178309

版权

题目来源

题目一查询所有列

题目地址
题目：现在运营想要查看用户信息表中所有的数据，请你取出相应结果。
在这里插入图片描述

解答：
（1）当运营对表中列的展示顺序无要求时，命令可为如下两种，此时查询结果中各列按照原始表中的顺序展示；
注意：使用*会进行全表扫描导致数据路性能下降,速度慢

select * from user_profile;

以下不使用*的方法更好

SELECT 
  id,device_id,
  gender,
  age,
  university,province
FROM user_profile;

（2）当运营对表中列的展示顺序有要求时，命令可为：select 列1 列3 列4··· from user_profile，可以按照需求的列顺序来展示查询结果。

题目二查询多列

题目地址
题目：现在运营同学想要用户的设备id对应的性别、年龄和学校的数据，请你取出相应数据
在这里插入图片描述

select device_id, gender,age, university
from user_profile;

题目三查询结果去重

题目地址
题目：现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据。
在这里插入图片描述
解法一：使用distinct(关键词distinct用于返回唯一不同的值)

select distinct university from user_profile;

解法二：使用group by按照university进行分组

select university from user_profile group by university;

题目四查询结果限制返回行数

题目地址
题目：现在运营只需要查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果。
在这里插入图片描述

select device_id from user_profile limit 2;

解析：
使用LIMIT限制结果集
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。
LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。
如果只给定一个参数，它表示返回最大的记录行数目。
如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
注意：初始记录行的偏移量是 0(而不是 1)。也就是从0开始表示第一行。
举例：
检索记录行 6-10（第一个5表示从第6行开始，第二个5表示行数，也就是指从第5行开始(包括第5行在内)的往下的5行数据(总共5行数据)）：

SELECT * FROM table LIMIT 5,5

检索前 5 个记录行（limit 5 相当于 limit 0,5）：

SELECT * FROM table LIMIT 5

题目五将查询后的列重新命名

题目地址
题目：现在你需要查看前2个用户明细设备ID数据，并将列名改为 ‘user_infos_example’,，请你从用户信息表取出相应结果。
在这里插入图片描述

select device_id as user_infos_example
from user_profile limit 2;

解析：
这里主要是用到了起别名关键字 as 以及组合限制查询 limit 索引,个数

select device_id as user_infors_example from user_profile limit 0,2;

其中as可以省略,索引为0可以省略

select device_id user_infors_example from user_profile limit 2;

注：
1.as 写不写都可
2.别名加不加引号（单双）都可
//加引号：别名就是引号内的内容。
//不加引号：别名如果为小写，会解析为大写，别名实际为大写。
//以上两点在调用别名时要注意，易报错：找不到对应的列（大小写对应的是不同的列）
3.这里的重命名仅仅针对本次查询有效，并不更改原始表的内容

题目六查找后排序

题目地址
题目：现在运营想要取出用户信息表中的用户年龄，请取出相应数据，并按照年龄升序排序。
在这里插入图片描述

SELECT device_id,age FROM user_profile ORDER BY age;

等价：

SELECT device_id,age FROM user_profile ORDER BY age ASC;

升序是ASC，倒序是DESC
默认升序，即升序可以省略ASC

举例：
将学生按班号的升序排序：

SELECT * FROM 学生表
ORDER BY 班号

查询选修了“M01F011 ”号课程的学生的学号及其成绩，查询结果按成绩降序排列。：

SELECT 学号,成绩 FROM 成绩表
WHERE 课程号='M01F011'
ORDER BY 成绩 DESC

查询全体学生的信息，查询结果按班号升序排列，同班的学生按出生年份降序排列：

SELECT * FROM 学生表
ORDER BY 班号, year(出生日期) DESC

SELECT 班号, year(出生日期) 出生年份
FROM 学生表
ORDER BY 班号,出生年份 DESC

题目七查找后多列排序

题目地址
题目：现在运营想要取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄升序排序输出，请取出相应数据。
在这里插入图片描述

SELECT device_id, gpa, age
FROM user_profile
ORDER BY gpa, age;

等价于：

SELECT device_id, gpa, age
FROM user_profile
ORDER BY gpa, age ASC;

等价于：

SELECT device_id, gpa, age
FROM user_profile
ORDER BY gpa ASC, age ASC;

题目八查找后降序排列

题目地址
题目：现在运营想要取出用户信息表中对应的数据，并先按照gpa、年龄降序排序输出，请取出相应数据。
在这里插入图片描述

SELECT device_id, gpa, age
FROM user_profile
ORDER BY gpa DESC, age DESC;

题目九查找学校是北大的学生信息

题目地址
题目：现在运营想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表中取出满足条件的数据，结果返回设备id和学校。
在这里插入图片描述

SELECT device_id, university
FROM user_profile
WHERE university='北京大学';

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

题目地址
题目：现在运营想要针对24岁以上的用户开展分析，请你取出满足条件的设备ID、性别、年龄、学校。用户信息表：user_profile
在这里插入图片描述

SELECT device_id, gender, age, university
FROM user_profile
WHERE age > 24;

题目十一查找某个年龄段的用户信息

题目地址
题目：现在运营想要针对20岁及以上且23岁及以下的用户开展分析，请你取出满足条件的设备ID、性别、年龄。
在这里插入图片描述

SELECT device_id, gender, age
FROM user_profile
WHERE age >= 20 and age <= 23;

或者：

SELECT device_id, gender, age
FROM user_profile
WHERE age BETWEEN 20 and 23;

举例：
例1：查询所有系号不在2和3之间的班号和班名：

SELECT 班号,班名 FROM 班级表 WHERE 系号 NOT BETWEEN 2 AND 3

等价于：

SELECT 班号,班名 FROM 班级表 WHERE 系号 <2 AND 系号 >3

例2：查询1995年3月出生的学生信息：

SELECT 学号, 姓名, 性别, 出生日期 FROM 学生表
WHERE 出生日期 BETWEEN '1995/3/1' AND '1995/3/31

题目十二查找除复旦大学的用户信息

题目地址
题目：现在运营想要查看除复旦大学以外的所有用户明细，请你取出相应数据
在这里插入图片描述

SELECT device_id, gender, age, university
FROM user_profile
WHERE university NOT IN ('复旦大学');

举例：
IN：
查询所有班名是“电子信息工程技术”、“电子声像”或“电子组装技术”的班号和班名：

SELECT 班号, 班名 FROM 班级表
WHERE 班名 IN ('电子信息工程技术' , '电子声像', '电子组装技术')

等价于（注意对应的是OR）：

SELECT 班号,班名 FROM 班级表
WHERE 班名= '电子信息工程技术' OR
班名 = '电子声像' OR
班名 = '电子组装技术'

查询班名既不是“电子信息工程技术”、也不是“电子声像”和“电子组装技术”的班号和班名：

SELECT 班号,班名 FROM 班级表
WHERE 班名 NOT IN ('电子信息工程技术' , '电子声像', '电子组装技术')

等价于（注意对应的是AND）：

SELECT 班号,班名 FROM 班级表
WHERE 班名!= '电子信息工程技术' AND
班名!= '电子声像' AND
班名!= '电子组装技术'

题目十三用where过滤空值练习

题目地址
题目：现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息。
在这里插入图片描述

SELECT device_id, gender, age, university
FROM user_profile
WHERE age is not NULL;

题目十四高级操作符练习(1)

题目地址
题目：现在运营想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研，请你取出相关数据。
在这里插入图片描述

SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE gender = 'male' AND gpa > 3.5;

举例：
查询所有系号大于1且班名以“电子 ” 开头的班号和班名：

SELECT 班号,班名 FROM 班级表
WHERE 系号>1 AND 班名 LIKE '电子%'

查询11212P和11214D班所有男生的学号、姓名、性别和班号：

SELECT 学号,姓名,性别,班号 FROM 学生表
WHERE (班号= '11212P' OR 班号= '11214D') AND 性别='男'

也可写为：

SELECT 学号,姓名,性别,班号 FROM 学生表
WHERE 班号 IN( '11212P' , '11214D')
AND 性别= '男'

题目十五高级操作符练习(2）

题目地址
题目：现在运营想要找到学校为北大或GPA在3.7以上(不包括3.7)的用户进行调研，请你取出相关数据（使用OR实现）
在这里插入图片描述

SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE university = '北京大学' OR gpa > 3.7;

题目十六 Where in 和Not in

题目地址
题目：现在运营想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据。
在这里插入图片描述

SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE university in ('北京大学', '复旦大学', '山东大学');

题目十七操作符混合运用

题目地址
题目：现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据。
在这里插入图片描述

解题关键知识点：and的优先级大于or，如果不确定可以用()来改变运算的优先级

SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE gpa > 3.5 AND university = '山东大学' OR gpa > 3.8 AND university = '复旦大学';

等价于：

SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE (gpa > 3.5 AND university = '山东大学') OR (gpa > 3.8 AND university = '复旦大学');

另外一种解法（子查询）：

SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE device_id in (SELECT device_id FROM user_profile WHERE gpa > 3.5 AND university = '山东大学')
OR device_id in (SELECT device_id FROM user_profile WHERE gpa > 3.8 AND university = '复旦大学');

题目十八查看学校名称中含北京的用户

题目地址
题目：现在运营想查看所有大学中带有北京的用户的信息，请你取出相应数据。
在这里插入图片描述

SELECT device_id, age, university
FROM user_profile
WHERE university LIKE '%北京%';

涉及知识点：字符匹配
一般形式为：列名 [NOT] LIKE

匹配串中可包含如下四种通配符：
_：匹配任意一个字符；
%：匹配0个或多个字符；
[ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表达 )；
[^ ]：不匹配[ ]中的任意一个字符。

举例：
例1.查询学生表中姓‘张’的学生的详细信息。

SELECT * FROM 学生表 WHERE 姓名 LIKE '张%'

例2.查询姓“张”且名字是3个字的学生姓名。

SELECT * FROM 学生表 WHERE 姓名 LIKE '张__'

例3.如果把姓名列的类型改为nchar(20)，在SQL Server 2012中执行没有结果。原因是姓名列的类型是char(20)，当姓名少于20个汉字时，系统在存储这些数据时自动在后边补空格，空格作为一个字符，也参加LIKE的比较。可以用rtrim()去掉右空格。

SELECT * FROM 学生表 WHERE rtrim(姓名) LIKE '张__'

例4.查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。

SELECT * FROM 学生表 WHERE 姓名 LIKE '[张李刘]%'

例5.查询学生表表中名字的第2个字为“小”或“大”的学生的姓名和学号。

SELECT 姓名,学号 FROM 学生表 WHERE 姓名 LIKE '_[小大]%'

例6.查询学生表中所有不姓“刘”的学生。

SELECT 姓名 FROM 学生 WHERE 姓名 NOT LIKE '刘%'

例7.从学生表表中查询学号的最后一位不是2、3、5的学生信息。

SELECT * FROM 学生表 WHERE 学号 LIKE '%[^235]'

题目十九查找GPA最高值

题目地址
题目：运营想要知道复旦大学学生gpa最高值是多少，请你取出相应数据。根据输入，你的查询应返回以下结果，结果保留到小数点后面1位(1位之后的四舍五入)。
在这里插入图片描述

注意：因为题目要求结果保留1位小数，所以需要用round函数：round(需要保留小数的变量,保留小数的个数)
解法1：gpa最高值：max(gpa)

SELECT round(max(gpa),1) FROM user_profile
WHERE university = '复旦大学';

解法2：进行order by gpa desc，按gpa降序排序，取第一行的数据即可

SELECT round(gpa,1) FROM user_profile
WHERE university = '复旦大学'
ORDER BY gpa DESC LIMIT 1;

题目二十计算男生人数以及平均GPA

题目地址
题目：现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少，用以辅助设计相关活动，请你取出相应数据。
在这里插入图片描述
问题分解：
1.限定条件为男性用户；
2.有多少人，明显是计数，count函数；
3.平均gpa，求平均值用avg函数；

细节问题：根据输出示例，有两个问题需要注意：
1.表头重命名，用as语法
2.浮点数的平均值可能小数点位数很多，按照示例保存一位小数，用round函数

SELECT count(gender) as male_num, round(avg(gpa),1) as avg_gpa
FROM user_profile
WHERE gender = 'male';

题目二十一分组计算练习题

题目地址
题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。
在这里插入图片描述

问题分解：
1.限定条件：无；
2.每个学校每种性别：按学校和性别分组：group by gender, university
3.用户数：count(device_id)
4.30天内平均活跃天数：avg(active_days_within_30)
5.平均发帖数量：avg(question_cnt)

细节问题：
1.表头重命名：as
注意输出顺序，与示例保持一致

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 university, gender;

题目二十二分组过滤练习题

题目地址
题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。
在这里插入图片描述

问题分解：
1.限定条件：平均发贴数低于5或平均回帖数小于20的学校，avg(question_cnt)<5 or avg(answer_cnt)<20，聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可；
2.按学校输出：需要对每个学校统计其平均发贴数和平均回帖数，因此group by university

细节问题：
1.表头重命名：as
2.用having不用where

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那一句（可以用别名）：

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的知识点：
HAVING子句用于对分组后的结果再进行过滤，
它的功能有点像WHERE子句，但它用于组而不是单个记录。
在HAVING子句中可以使用统计函数，但在WHERE子句中则不能。
HAVING通常与GROUP BY子句一起使用。
举例：
例1.查询学生表中人数大于等于3的班号和人数。

SELECT 班号, COUNT(*) 人数
FROM 学生表
GROUP BY 班号
HAVING COUNT(*) >= 3

例2.查询平均成绩大于等于80的学生的学号、选课门数和平均成绩。

SELECT 学号, COUNT(*) 选课门数,
AVG(成绩) 平均成绩 FROM 成绩表
GROUP BY 学号
HAVING AVG(成绩) >= 80

题目二十三分组排序练习题

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

不同大学：按学校分组group by university
平均发帖数：avg(question_cnt)
升序排序：order by avg_question_cnt

SELECT
university,
round(avg(question_cnt),4) as avg_question_cnt
FROM user_profile
group by university
order by avg_question_cnt;

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

题目地址
题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据
在这里插入图片描述

法一：先从user_profile表找到浙江大学的所有学生id列表where university=‘浙江大学’，再去question_practice_detail筛选出id在这个列表的记录，用where in

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;

等价于(把in换成=)：

select device_id, question_id, result
from question_practice_detail
where device_id = (
    select device_id from user_profile where university = '浙江大学'
)
order by question_id;

法二：先将两张表关联在一起，然后再筛选出浙江大学的明细数据。join两个表，用inner join，条件是t2.device_id = t1.device_id and t2.university = ‘浙江大学’

select t1.device_id, t1.question_id, t1.result
from question_practice_detail as t1
inner join user_profile as t2
on t2.device_id = t1.device_id and t2.university = '浙江大学'
order by question_id;

简化为(可以省略第一行后面的t1，可以省略as)：

select t1.device_id, question_id, result
from question_practice_detail t1
inner join user_profile t2
on t2.device_id = t1.device_id and t2.university = '浙江大学'
order by question_id;

等价于(把and t2.换成where)：

select t1.device_id, question_id, result
from question_practice_detail t1
inner join user_profile t2
on t2.device_id = t1.device_id where university = '浙江大学'
order by question_id;

法三：先将两张表关联在一起，然后再筛选出浙江大学的明细数据。join两个表，用left join。
注：也就是把法二的inner换成left而已，其余地方都不变（法二的上述三种代码形式都适用）

select t1.device_id, question_id, result
from question_practice_detail t1
left join user_profile t2
on t2.device_id = t1.device_id where university = '浙江大学'
order by question_id;

涉及等值连接(inner join)、左联接(left join)知识点—>查看定义。

题目二十五统计每个学校的答过题的用户的平均答题数

题目地址
题目：运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。
在这里插入图片描述

每个学校：按学校分组，group by university；
平均答题数量：在每个学校的分组内，用总答题数量除以总人数即可得到平均答题数量count(question_id) / count(distinct device_id)；
count(distinct t2.device_id)就是取出答过题目的去除重复的用户数量【由于存在一个设备账号多次登录的情况】；
表连接：学校和答题信息在不同的表，需要做连接。

select
  university,
  count(question_id) / count(distinct t2.device_id) as avg_answer_cnt
from
  question_practice_detail as t2
  inner join user_profile as t1 on t2.device_id = t1.device_id
group by
  university;

结果保留4位小数，用round：

select
  university,
  round(count(question_id) / count(distinct t2.device_id),4) as avg_answer_cnt
from
  question_practice_detail as t2
  inner join user_profile as t1 on t2.device_id = t1.device_id
group by
  university;

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

题目地址
题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据。
分析：
每个学校：按学校分组group by university
不同难度：按难度分组group by difficult_level
平均答题数：总答题数除以总人数count(t2.question_id) / count(distinct t2.device_id)
来自上面信息三个表，需要联表，t1与t2用device_id连接，t3与t2用question_id连接。
表头重命名：as
平均值精度：保留4位小数round(x, 4)

select
    university,
    difficult_level,
    round(count(t2.question_id) / count(distinct t2.device_id), 4) as avg_answer_cnt
from question_practice_detail as t2
 
left join user_profile as t1
on t1.device_id=t2.device_id
 
left join question_detail as t3
on t3.question_id=t2.question_id
 
group by university, difficult_level;

题目二十七统计每个用户的平均刷题数

题目地址
题目：运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据。

第一反应是在上一题的基础上加上where university = ‘山东大学’：

select
    university,
    difficult_level,
    round(count(t2.question_id) / count(distinct t2.device_id), 4) as avg_answer_cnt
from question_practice_detail as t2
 
left join user_profile as t1
on t1.device_id=t2.device_id
 
left join question_detail as t3
on t3.question_id=t2.question_id

where university = '山东大学'
group by difficult_level;

等价于加上 and t1.university = ‘山东大学’，但这种方法只能用inner join，用left join会报错：

select
    university,
    difficult_level,
    round(count(t2.question_id) / count(distinct t2.device_id), 4) as avg_answer_cnt
from question_practice_detail as t2
 
inner join user_profile as t1
on t1.device_id=t2.device_id and t1.university = '山东大学'
 
inner join question_detail as t3
on t3.question_id=t2.question_id

group by difficult_level;

更好的方法（推荐）：

SELECT
    t1.university,
    t3.difficult_level,
    COUNT(t2.question_id) / COUNT(DISTINCT(t2.device_id)) as avg_answer_cnt
from
    user_profile as t1,
    question_practice_detail as t2,
    question_detail as t3
WHERE
    t1.university = '山东大学'
    and t1.device_id = t2.device_id
    and t2.question_id = t3.question_id
GROUP BY
    t3.difficult_level;

题目二十八查找山东大学或者性别为男生的信息

题目地址
题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。
在这里插入图片描述

限定条件：学校为山东大学或者性别为男性的用户：university=‘山东大学’, gender=‘male’；
分别查看&结果不去重：所以直接使用两个条件的or是不行的，直接用union也不行，要用union all，分别去查满足条件1的和满足条件2的，然后合在一起不去重
细节问题：
不去重：union all。
结果不去重就用union all，去重就用 union。
按照题目的意思如果一条数据既是山东大学又是male的话应该输出两次，使用or无法达到这个效果。

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';

题目三十计算25岁以上和以下的用户数量

题目地址
题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量。
在这里插入图片描述
注意：记得count是GROUP BY之后计数的！
count(*)也行，count(device_id)也行。
法一：用if函数判断（推荐）

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

法二：用case

select
    (case when age >= 25 then '25岁及以上'
    else '25岁以下'
    end) as age_cut,
    count(*) as number
from
    user_profile
group by age_cut;

等价于：

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

题目三十一查看不同年龄段的用户明细

题目地址
题目：现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。（注：若年龄为空请返回其他。）
在这里插入图片描述

划分年龄段：数值条件判断，可以用多重if，不过更方便的是用case when [expr] then [result1]…else [default] end
表头重命名：as
输出的段明文在题目描述和输出示例中不一致，以输出示例为准
注：此题就无需分组了

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

题目三十二计算用户8月每天的练题数量

题目地址
题目：现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。
在这里插入图片描述
限定条件：2021年8月，写法有很多种，比如用year/month函数的year(date)=2021 and month(date)=8，比如用date_format函数的date_format(date, “%Y-%m”)=“202108”
每天：按天分组group by date
题目数量：count(question_id)
表头重命名：as
输出示例中每天的字段只取了几号，要去掉年月，用day函数即可

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

题目三十三计算用户的平均次日留存率（困难题）

题目地址
题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。
在这里插入图片描述
注意：此题为困难题！！
解题方法解析–>指路

需要注意到的知识点：
1.DISTINCT：这个关键字要么不同，用就需要要放在select的最前面，对所有列去重；若有多个列，是对多个列的组合去重
2.COUNT：在对列进行计数时不统计值为 null的条目
3.LEFT JOIN：其实q1和q2查询出来的结果，用LEFT JOIN连接后，q1就表示不同日期（第一天）有做过题的信息，q2表示第二天有做过题的信息
4.第一天和第二天的确定：q2.date = DATE_ADD(q1.date, interval 1 day)

SELECT
    COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret
FROM
    (SELECT DISTINCT device_id, date FROM question_practice_detail)as q1
LEFT JOIN
    (SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2
ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day)

总结自己在这过程中常见的错误：
1.在select后面有很多项的情况下，特别是有xx as xx的情况下，最后一个名词也就是在from前面，千万记得不要习惯性地加逗号，加了逗号就会报错了
2.用round函数来保留一位小数时候，不要只是单纯地用round加个小括号就把名词括在里面了，名词后面还有一个变量用于表示保留小数的位数：round(a,1)

面试题（地址）：
问：分组SQL语句中，select和from和where和group by 和 having 这几部分的执行顺序是怎么样的
答：先是from（组装来自不同数据源的数据），再到 where（基于指定的条件，对数据进行筛选过滤），再到group by（将筛选后的数据划分为多个分组），再到having，最后select。
原因：首先是要确定数据从哪张表来，然后按where条件对数据进行筛选过滤，再然后才能进行group by分组（分组条件可以有多个，按字段顺序依次分组），分组之后由having对结果集进行过滤之后把数据呈现出来。

补充：
sql语句书写顺序不能颠倒：select···from···where···group by···having···order by···limit···
一般执行顺序是from···where···group by···having···select···order by···limit···;

未完待续。。。