MySql练习题

MySql基础练习题

1、查询所有列

题目：现在运营想要查看用户信息表中所有的数据，请你取出相应结果

示例：user_profile

id device_id gender age university province
1 2138 male 21 北京大学 Beijing
2 3214 male 复旦大学 Shanghai
3 6543 female 20 北京大学 Beijing
4 2315 female 23 浙江大学 ZheJiang
5 5432 male 25 山东大学 Shandong

根据示例，你的查询应返回以下结果:

id device_id gender age university province
1 2138 male 21 北京大学 Beijing
2 3214 male 复旦大学 Shanghai
3 6543 female 20 北京大学 Beijing
4 2315 female 23 浙江大学 Zhejiang
5 5432 male 25 山东大学 Shandong

答案

select *
from user_profile

select
    id,
    device_id,
    gender,
    age,
    university,
    province
from user_profile

2、查询多列

题目：现在运营同学想要用户的设备id对应的性别、年龄和学校的数据，请你取出相应数据

示例：user_profile

id device_id gender age university province
1 2138 male 21 北京大学 Beijing
2 3214 male 复旦大学 Shanghai
3 6543 female 20 北京大学 Beijing
4 2315 female 23 浙江大学 Zhejiang
5 5432 male 25 山东大学 Shandong

根据示例，你的查询应返回以下结果

device_id gender age university
2138 male 21 北京大学
3214 male 复旦大学
6543 female 20 北京大学
2315 female 23 浙江大学
5432 male 25 山东大学

答案

select device_id,gender,age,university from user_profile

3、查询结果去重

题目：现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据。

示例:user_profile

id device_id gender age university province
1 2138 male 21 北京大学 Beijing
2 3214 male 复旦大学 Shanghai
3 6543 female 20 北京大学 Beijing
4 2315 female 23 浙江大学 ZheJiang
5 5432 male 25 山东大学 Shandong

根据示例，你的查询应返回以下结果：

university
北京大学
复旦大学
浙江大学
山东大学

答案

select distinct university from user_profile

select university from user_profile group by university

4、查询结果限制返回行数

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

示例：

id device_id gender age university province
1 2138 male 21 北京大学 Beijing
2 3214 male 复旦大学 Shanghai
3 6543 female 20 北京大学 Beijing
4 2315 female 23 浙江大学 ZheJiang
5 5432 male 25 山东大学 Shandong

根据输入，你的查询应返回以下结果：

device_id
2138
3214

答案

select device_id from user_profile limit 2

5、将查询后的列重新命名

题目：现在你需要查看前2个用户明细设备ID数据，并将列名改为 'user_infos_example',，请你从用户信息表取出相应结果。

示例：user_profile

id device_id gender age university province
1 2138 male 21 北京大学 Beijing
2 3214 male 复旦大学 Shanghai
3 6543 female 20 北京大学 Beijing
4 2315 female 23 浙江大学 ZheJiang
5 5432 male 25 山东大学 Shandong

根据示例，你的查询应返回以下结果：

user_infos_example
2138
3214

答案

select device_id as 'user_infos_example' from user_profile limit 2

36、查找后排序

题目：现在运营想要取出用户信息表中的用户年龄，请取出相应数据，并按照年龄升序排序。

示例：user_profile

id device_id gender age university gpa
1 2138 male 21 北京大学 3.4
2 3214 male 23 复旦大学 4
3 6543 female 20 北京大学 3.2
4 2315 female 23 浙江大学 3.6
5 5432 male 25 山东大学 3.8
6 2131 male 28 北京师范大学 3.3

根据示例，你的查询应返回以下结果：

device_id age
6534 20
2138 21
3214 23
2315 23
5432 25
2131 28

答案

select device_id,age from user_profile order by age

37、查找后多列排序

题目：现在运营想要取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄升序排序输出，请取出相应数据。

用户信息表：user_profile

id device_id gender age university gpa
1 2138 male 21 北京大学 3.4
2 3214 male 23 复旦大学 4
3 6543 female 20 北京大学 3.2
4 2315 female 23 浙江大学 3.6
5 5432 male 25 山东大学 3.8
6 2131 male 28 北京师范大学 3.3

你的查询应返回以下结果：

device_id gpa age
6534 3.2 20
2131 3.3 28
2138 3.4 21
2315 3.6 23
5432 3.8 25
3214 4 23

答案

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

38、查找后降序排列

题目：现在运营想要取出用户信息表中对应的数据，并先按照gpa、年龄降序排序输出，请取出相应数据。

示例 user_profile：

id device_id gender age university gpa
1 2138 male 21 北京大学 3.4
2 3214 male 23 复旦大学 4
3 6543 female 20 北京大学 3.2
4 2315 female 23 浙江大学 3.6
5 5432 male 25 山东大学 3.8
6 2131 male 28 北京师范大学 3.3

根据示例，你的查询应返回以下结果：

device_id gpa age
3214 4 23
5432 3.8 25
2315 3.6 23
2138 3.4 21
2131 3.3 28
6543 3.2 20

答案

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

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

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

示例：user_profile

id device_id gender age university province
1 2138 male 21 北京大学 Beijing
2 3214 male 复旦大学 Shanghai
3 6543 female 20 北京大学 Beijing
4 2315 female 23 浙江大学 ZheJiang
5 5432 male 25 山东大学 Shandong

根据示例，你的查询应返回以下结果：

device_id university
2138 北京大学
6543 北京大学

答案

select device_id,university from user_profile where university = "北京大学"

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

题目：现在运营想要针对24岁以上的用户开展分析，请你取出满足条件的设备ID、性别、年龄、学校。

用户信息表：user_profile

id device_id gender age university province
1 2138 male 21 北京大学 Beijing
2 3214 male 复旦大学 Shanghai
3 6543 female 20 北京大学 Beijing
4 2315 female 23 浙江大学 ZheJiang
5 5432 male 25 山东大学 Shandong

根据输入，你的 查询应返回以下结果：

device_id gender age university
5432 male 25 山东大学

答案

select device_id,gender,age,university from user_profile where age > 24

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

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

用户信息表：user_profile

id device_id gender age university province
1 2138 male 21 北京大学 Beijing
2 3214 male 复旦大学 Shanghai
3 6543 female 20 北京大学 Beijing
4 2315 female 23 浙江大学 ZheJiang
5 5432 male 25 山东大学 Shandong

根据输入，你的查询应返回以下结果：

device_id gender age
2138 male 21
6543 female 20
2315 female 23

答案

select device_id,gender,age from user_profile where age > 19 and age < 24

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

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

题目：现在运营想要查看除复旦大学以外的所有用户明细，请你取出相应数据

示例：user_profile

id device_id gender age university province
1 2138 male 21 北京大学 Beijing
2 3214 male 复旦大学 Shanghai
3 6543 female 20 北京大学 Beijing
4 2315 female 23 浙江大学 ZheJiang
5 5432 male 25 山东大学 Shandong

根据输入，你的查询应返回以下结果：

device_id gender age university
2138 male 21 北京大学
6543 female 20 北京大学
2315 female 23 浙江大学
5432 male 25 山东大学

答案

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

10、用where过滤空值练习

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

示例：user_profile

id device_id gender age university province
1 2138 male 21 北京大学 Beijing
2 3214 male 复旦大学 Shanghai
3 6543 female 20 北京大学 Beijing
4 2315 female 23 浙江大学 ZheJiang
5 5432 male 25 山东大学 Shandong

根据输入，你的 查询应返回以下结果：

device_id gender age university
2138 male 21 北京大学
6543 female 20 北京大学
2315 female 23 浙江大学
5432 male 25 山东大学

答案

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

11、高级操作符练习(1) and

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

示例：user_profile

id device_id gender age university gpa
1 2138 male 21 北京大学 3.4
2 3214 male 复旦大学 4.0
3 6543 female 20 北京大学 3.2
4 2315 female 23 浙江大学 3.6
5 5432 male 25 山东大学 3.8

根据输入，你的查询应返回以下结果：

device_id gender age university gpa
3214 male 复旦大学 4.0
5432 male 25 山东大学 3.8

答案

select 
device_id,gender,age,university,gpa 
from user_profile 
where gpa>3.5 and gender = "male"

12、高级操作符练习(2）or

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

示例：user_profile

id device_id gender age university gpa
1 2138 male 21 北京大学 3.4
2 3214 male 复旦大学 4.0
3 6543 female 20 北京大学 3.2
4 2315 female 23 浙江大学 3.6
5 5432 male 25 山东大学 3.8

根据输入，你的查询应返回以下结果：

device_id gender age university gpa
2138 male 21 北京大学 3.4
3214 male 复旦大学 4.0
6543 female 20 北京大学 3.2
5432 male 25 山东大学 3.8

答案

select 
device_id,gender,age,university,gpa 
from user_profile 
where university ="北京大学" or gpa>3.7

13、 Where in 和Not in

题目：现在运营想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据。

示例：user_profile

id device_id gender age university gpa
1 2138 male 21 北京大学 3.4
2 3214 male 复旦大学 4.0
3 6543 female 20 北京大学 3.2
4 2315 female 23 浙江大学 3.6
5 5432 male 25 山东大学 3.8

根据输入，你的查询应返回以下结果：

device_id gender age university gpa
2138 male 21 北京大学 3.4
3214 male 复旦大学 4.0
6543 female 20 北京大学 3.2
5432 male 25 山东大学 3.8

答案

select 
device_id,gender,age,university,gpa 
from user_profile 
where university in("北京大学","复旦大学","山东大学")

14、操作符混合运用

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

示例：user_profile

id device_id gender age university province gpa
1 2138 male 21 北京大学 BeiJing 3.4
2 3214 male NULL 复旦大学 Shanghai 4
3 6543 female 20 北京大学 BeiJing 3.2
4 2315 female 23 浙江大学 ZheJiang 3.6
5 5432 male 25 山东大学 Shandong 3.8

根据输入，你的查询应返回以下结果：(该题对于小数点后面的0不需要计算与统计，后台系统会统一输出小数点后面1位)

device_id gender age university gpa
3214 male NULL 复旦大学 4
5432 male 25 山东大学 3.8

答案

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

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

题目：现在运营想查看所有大学中带有北京的用户的信息，请你取出相应数据。

示例：用户信息表：user_profile

id device_id gender age university gpa
1 2138 male 21 北京大学 3.4
2 3214 male 复旦大学 4.0
3 6543 female 20 北京大学 3.2
4 2315 female 23 浙江大学 3.6
5 5432 male 25 山东大学 3.8
6 2131 male 28 北京师范大学 3.3

根据示例，你的查询应返回如下结果：

device_id age university
2138 21 北京大学
6543 20 北京大学
2131 28 北京师范大学

答案

select 
device_id,age,university 
from user_profile 
where university like '%北京%'
#where university regexp "北京"

16、查找GPA最高值

题目：运营想要知道复旦大学学生gpa最高值是多少，请你取出相应数据

示例：某user_profile表如下:

id device_id gender age university gpa
1 2234 male 21 北京大学 3.2
2 2235 male NULL 复旦大学 3.8
3 2236 female 20 复旦大学 3.5
4 2237 female 23 浙江大学 3.3
5 2238 male 25 复旦大学 3.1
6 2239 male 25 北京大学 3.6
7 2240 male NULL 清华大学 3.3
8 2241 female NULL 北京大学 3.7

根据输入，你的查询应返回以下结果，结果保留到小数点后面1位(1位之后的四舍五入):

gpa
3.8

答案

select round(max(gpa),1) 
from user_profile 
where university = "复旦大学" 

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

17、计算男生人数以及平均GPA

题目：现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少，用以辅助设计相关活动，请你取出相应数据。

示例：user_profile

id device_id gender age university gpa
1 2138 male 21 北京大学 3.4
2 3214 male 复旦大学 4.0
3 6543 female 20 北京大学 3.2
4 2315 female 23 浙江大学 3.6
5 5432 male 25 山东大学 3.8
6 2131 male 28 北京师范大学 3.3

根据输入，你的查询应返回以下结果，结果保留到小数点后面1位(1位之后的四舍五入)：

male_num avg_gpa
4 3.6

答案

select 
count(gender) as male_num,round(avg(gpa),1)as avg_gpa 
from user_profile
where gender = "male"

18、分组计算练习题

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

用户信息表：user_profile

30天内活跃天数字段（active_days_within_30）

发帖数量字段（question_cnt）

回答数量字段（answer_cnt）

id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt
1 2138 male 21 北京大学 3.4 7 2 12
2 3214 male 复旦大学 4.0 15 5 25
3 6543 female 20 北京大学 3.2 12 3 30
4 2315 female 23 浙江大学 3.6 5 1 2
5 5432 male 25 山东大学 3.8 20 15 70
6 2131 male 28 山东大学 3.3 15 7 13
7 4321 male 26 复旦大学 3.6 9 6 52

第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4在过去的30天里面活跃了7天，发帖数量为2，回答数量为12

。。。

最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄26岁，复旦大学，gpa为3.6在过去的30天里面活跃了9天，发帖数量为6，回答数量为52

你的查询返回结果需要对性别和学校分组，示例如下，结果保留1位小数，1位小数之后的四舍五入：

gender university user_num avg_active_day avg_question_cnt
male 北京大学 1 7.0 2.0
male 复旦大学 2 12.0 5.5
female 北京大学 1 12.0 3.0
female 浙江大学 1 5.0 1.0
male 山东大学 2 17.5 11.0

解释:

第一行表示：北京大学的男性用户个数为1，平均活跃天数为7天，平均发帖量为2

。。。

最后一行表示：山东大学的男性用户个数为2，平均活跃天数为17.5天，平均发帖量为11

答案

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

19、分组过滤练习题

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

示例：user_profile

id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt
1 2138 male 21 北京大学 3.4 7 2 12
2 3214 male 复旦大学 4.0 15 5 25
3 6543 female 20 北京大学 3.2 12 3 30
4 2315 female 23 浙江大学 3.6 5 1 2
5 5432 male 25 山东大学 3.8 20 15 70
6 2131 male 28 山东大学 3.3 15 7 13
7 4321 female 26 复旦大学 3.6 9 6 52

第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4在过去的30天里面活跃了7天，发帖数量为2，回答数量为12
。。。
最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄26岁，复旦大学，gpa为3.6在过去的30天里面活跃了9天，发帖数量为6，回答数量为52

根据示例，你的查询应返回以下结果，请你保留3位小数(系统后台也会自动校正)，3位之后四舍五入：

university avg_question_cnt avg_answer_cnt
北京大学 2.5000 21.000
浙江大学 1.000 2.000

解释: 平均发贴数低于5的学校或平均回帖数小于20的学校有2个

属于北京大学的用户的平均发帖量为2.500，平均回答数量为21.000

属于浙江大学的用户的平均发帖量为1.000，平均回答数量为2.000

答案

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;

Sql语句执行顺序

FROM - ON - JOIN - WHERE - GROUP BY - WITH - HAVING - SELECT - DISTINCT - ORDER BY - LIMIT

(8) SELECT (9) DISTINCT
(1) FROM
(3)  JOIN
(2)    ON
(4) WHERE
(5) GROUP BY
(6) WITH {CUBE|ROLLUP}
(7) HAVING
(10) ORDER BY
(11) LIMIT

20、分组排序练习题

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

示例：user_profile

id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt
1 2138 male 21 北京大学 3.4 7 2 12
2 3214 male 复旦大学 4.0 15 5 25
3 6543 female 20 北京大学 3.2 12 3 30
4 2315 female 23 浙江大学 3.6 5 1 2
5 5432 male 25 山东大学 3.8 20 15 70
6 2131 male 28 山东大学 3.3 15 7 13
7 4321 female 26 复旦大学 3.6 9 6 52

第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4在过去的30天里面活跃了7天，发帖数量为2，回答数量为12
。。。
最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄26岁，复旦大学，gpa为3.6在过去的30天里面活跃了9天，发帖数量为6，回答数量为52

根据示例，你的查询应返回以下结果：

university avg_question_cnt
浙江大学 1.0000
北京大学 2.5000
复旦大学 5.5000
山东大学 11.0000

答案

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

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

题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

示例 ：question_practice_detail

id device_id question_id result
1 2138 111 wrong
2 3214 112 wrong
3 3214 113 wrong
4 6543 114 right
5 2315 115 right
6 2315 116 right
7 2315 117 wrong

第一行表示:id为1的用户的常用信息为使用的设备id为2138，在question_id为111的题目上，回答错误

....

最后一行表示:id为7的用户的常用信息为使用的设备id为2135，在question_id为117的题目上，回答错误

示例：user_profile

id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt
1 2138 male 21 北京大学 3.4 7 2 12
2 3214 male 复旦大学 4.0 15 5 25
3 6543 female 20 北京大学 3.2 12 3 30
4 2315 female 23 浙江大学 3.6 5 1 2
5 5432 male 25 山东大学 3.8 20 15 70
6 2131 male 28 山东大学 3.3 15 7 13
7 4321 female 26 复旦大学 3.6 9 6 52

第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4在过去的30天里面活跃了7天，发帖数量为2，回答数量为12
。。。
最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄26岁，复旦大学，gpa为3.6在过去的30天里面活跃了9天，发帖数量为6，回答数量为52

根据示例，你的查询应返回以下结果，查询结果根据question_id升序排序：

img

解释:

根据题目的数据只有1个浙江大学的用户，那么把浙江大学这个用户所有答题数据查询出来就行

答案

子查询

select 
device_id,question_id,result 
from question_practice_detail as qpd 
where qpd.device_id = (select device_id from user_profile where university ="浙江大学")

连接查询

select q.device_id,q.question_id,q.result 
from question_practice_detail q,user_profile u
where q.device_id = u.device_id and u.university = '浙江大学'

内连接

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

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

运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。

用户信息表 user_profile，其中device_id指终端编号（认为每个用户有唯一的一个终端），gender指性别，age指年龄，university指用户所在的学校，gpa是该用户平均学分绩点，active_days_within_30是30天内的活跃天数。

device_id gender age university gpa active_days_within_30
2138 male 21 北京大学 3.4 7
3214 male NULL 复旦大学 4 15
6543 female 20 北京大学 3.2 12
2315 female 23 浙江大学 3.6 5
5432 male 25 山东大学 3.8 20
2131 male 28 山东大学 3.3 15
4321 male 28 复旦大学 3.6 9

第一行表示:用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4，在过去的30天里面活跃了7天

最后一行表示:用户的常用信息为使用的设备id为4321，性别为男，年龄28岁，复旦大学，gpa为3.6，在过去的30天里面活跃了9天

答题情况明细表 question_practice_detail，其中question_id是题目编号，result是答题结果。

device_id question_id result
2138 111 wrong
3214 112 wrong
3214 113 wrong
6543 111 right
2315 115 right
2315 116 right
2315 117 wrong
5432 118 wrong
5432 112 wrong
2131 114 right
5432 113 wrong

第一行表示用户的常用信息为使用的设备id为2138，在question_id为111的题目上，回答错误

....

最后一行表示用户的常用信息为使用的设备id为5432，在question_id为113的题目上，回答错误

请你写SQL查找每个学校用户的平均答题数目(说明：某学校用户平均答题数量计算方式为该学校用户答题总次数除以答过题的不同用户个数)根据示例，你的查询应返回以下结果（结果保留4位小数），注意：结果按照university升序排序！！！

university avg_answer_cnt
北京大学 1.0000
复旦大学 2.0000
山东大学 2.0000
浙江大学 3.0000

解释:

第一行：北京大学总共有2个用户，2138和6543，2个用户在question_practice_detail里面答了2题，平均答题数目为2/2=1.0000

....

最后一行:浙江大学总共有1个用户，2315，这个用户在*question_practice_detail里面答了3题，平均答题数目为3/1=3.0000*

答案

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

执行顺序：from user_profile u ---> on u.device_id=qpd.device_id ----> join question_practice_detail qpd ----> group by university----->select university,(count(question_id)/count(distinct(qpd.device_id))) as avg_answer_cnt

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

题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据

用户信息表：user_profile

id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt
1 2138 male 21 北京大学 3.4 7 2 12
2 3214 male NULL 复旦大学 4 15 5 25
3 6543 female 20 北京大学 3.2 12 3 30
4 2315 female 23 浙江大学 3.6 5 1 2
5 5432 male 25 山东大学 3.8 20 15 70
6 2131 male 28 山东大学 3.3 15 7 13
7 4321 male 28 复旦大学 3.6 9 6 52

第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4，在过去的30天里面活跃了7天，发帖数量为2，回答数量为12

最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄28岁，复旦大学，gpa为3.6，在过去的30天里面活跃了9天，发帖数量为6，回答数量为52

题库练习明细表：question_practice_detail

id device_id question_id result
1 2138 111 wrong
2 3214 112 wrong
3 3214 113 wrong
4 6534 111 right
5 2315 115 right
6 2315 116 right
7 2315 117 wrong
8 5432 117 wrong
9 5432 112 wrong
10 2131 113 right
11 5432 113 wrong
12 2315 115 right
13 2315 116 right
14 2315 117 wrong
15 5432 117 wrong
16 5432 112 wrong
17 2131 113 right
18 5432 113 wrong
19 2315 117 wrong
20 5432 117 wrong
21 5432 112 wrong
22 2131 113 right
23 5432 113 wrong

第一行表示:id为1的用户的常用信息为使用的设备id为2138，在question_id为111的题目上，回答错误

......

最后一行表示:id为23的用户的常用信息为使用的设备id为5432，在question_id为113的题目上，回答错误

表：question_detail

id question_id difficult_level
1 111 hard
2 112 medium
3 113 easy
4 115 easy
5 116 medium
6 117 easy

第一行表示: 题目id为111的难度为hard

....

第一行表示: 题目id为117的难度为easy

请你写一个SQL查询，计算不同学校、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)：

university difficult_level avg_answer_cnt
北京大学 hard 1.0000
复旦大学 easy 1.0000
复旦大学 medium 1.0000
山东大学 easy 4.5000
山东大学 medium 3.0000
浙江大学 easy 5.0000
浙江大学 medium 2.0000

解释：

第一行：北京大学有设备id为2138，6543这2个用户，这2个用户在question_practice_detail表下都只有一条答题记录，且答题题目是111，从question_detail可以知道这个题目是hard，故 北京大学的用户答题为hard的题目平均答题为2/2=1.0000

第二行，第三行：复旦大学有设备id为3214，4321这2个用户，但是在question_practice_detail表只有1个用户(device_id=3214有答题，device_id=4321没有答题，不计入后续计算)有2条答题记录，且答题题目是112，113各1个，从question_detail可以知道题目难度分别是medium和easy，故 复旦大学的用户答题为easy, medium的题目平均答题量都为1(easy=1或medium=1) /1 (device_id=3214)=1.0000

第四行，第五行：山东大学有设备id为5432和2131这2个用户，这2个用户总共在question_practice_detail表下有12条答题记录，且答题题目是112，113，117，且数目分别为3，6，3，从question_detail可以知道题目难度分别为medium,easy,easy，所以，easy共有9个，故easy的题目平均答题量= 9(easy=9)/2 (device_id=3214 or device_id=5432) =4.5000，medium共有3个，medium的答题只有device_id=5432的用户，故medium的题目平均答题量= 3(medium=9)/1 ( device_id=5432) =3.0000

select 
university,difficult_level,count(qpd.question_id)/count(distinct(u.device_id)) as avg_answer_cnt
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 university,difficult_level

SELECT
    university,
    difficult_level,
    count( q.question_id )/ count(
    DISTINCT ( q.device_id )) avg_answer_cnt 
FROM
    user_profile u,
    question_practice_detail q,
    question_detail qd 
WHERE
    u.device_id = q.device_id 
    AND q.question_id = qd.question_id 
GROUP BY
    university,
    difficult_level;

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

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

用户信息表：user_profile

id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt
1 2138 male 21 北京大学 3.4 7 2 12
2 3214 male NULL 复旦大学 4 15 5 25
3 6543 female 20 北京大学 3.2 12 3 30
4 2315 female 23 浙江大学 3.6 5 1 2
5 5432 male 25 山东大学 3.8 20 15 70
6 2131 male 28 山东大学 3.3 15 7 13
7 4321 male 28 复旦大学 3.6 9 6 52

第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4，在过去的30天里面活跃了7天，发帖数量为2，回答数量为12

最后一行表示:id为7的用户的常用信息为使用的设备id为432，性别为男，年龄28岁，复旦大学，gpa为3.6，在过去的30天里面活跃了9天，发帖数量为6，回答数量为52

题库练习明细表：question_practice_detail

id device_id question_id result
1 2138 111 wrong
2 3214 112 wrong
3 3214 113 wrong
4 6534 111 right
5 2315 115 right
6 2315 116 right
7 2315 117 wrong
8 5432 117 wrong
9 5432 112 wrong
10 2131 113 right
11 5432 113 wrong
12 2315 115 right
13 2315 116 right
14 2315 117 wrong
15 5432 117 wrong
16 5432 112 wrong
17 2131 113 right
18 5432 113 wrong
19 2315 117 wrong
20 5432 117 wrong
21 5432 112 wrong
22 2131 113 right
23 5432 113 wrong

第一行表示:id为1的用户的常用信息为使用的设备id为2138，在question_id为111的题目上，回答错误

......

最后一行表示:id为23的用户的常用信息为使用的设备id为5432，在question_id为113的题目上，回答错误

表：question_detail

id question_id difficult_level
1 111 hard
2 112 medium
3 113 easy
4 115 easy
5 116 medium
6 117 easy

第一行表示: 题目id为111的难度为hard

....

第一行表示: 题目id为117的难度为easy

请你写一个SQL查询，计算山东、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)：

university difficult_level avg_answer_cnt
山东大学 easy 4.5000
山东大学 medium 3.0000

山东大学有设备id为5432和2131这2个用户，这2个用户总共在question_practice_detail表下有12条答题记录，且答题题目是112，113，117，且数目分别为3，6，3，从question_detail可以知道题目难度分别为medium,easy,easy，所以，easy共有9个，故easy的题目平均答题量= 9(easy=9)/2 (device_id=3214 or device_id=5432) =4.5000，medium共有3个，medium的答题只有device_id=5432的用户，故medium的题目平均答题量= 3(medium=9)/1 ( device_id=5432) =3.0000

答案

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

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

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

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

示例：user_profile

id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt
1 2138 male 21 北京大学 3.4 7 2 12
2 3214 male 复旦大学 4 15 5 25
3 6543 female 20 北京大学 3.2 12 3 30
4 2315 female 23 浙江大学 3.6 5 1 2
5 5432 male 25 山东大学 3.8 20 15 70
6 2131 male 28 山东大学 3.3 15 7 13
7 4321 male 26 复旦大学 3.6 9 6 52

根据示例，你的查询应返回以下结果（注意输出的顺序，先输出学校为山东大学再输出性别为男生的信息）：

device_id gender age gpa
5432 male 25 3.8
2131 male 28 3.3
2138 male 21 3.4
3214 male None 4
5432 male 25 3.8
2131 male 28 3.3
4321 male 28 3.6 
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，因为or自带去重，而union等价于or，但union all 可以不去重，所以本体考察or与union的细节使用。

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

题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

本题注意：age为null 也记为 25岁以下

示例：user_profile

id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt
1 2138 male 21 北京大学 3.4 7 2 12
2 3214 male 复旦大学 4 15 5 25
3 6543 female 20 北京大学 3.2 12 3 30
4 2315 female 23 浙江大学 3.6 5 1 2
5 5432 male 25 山东大学 3.8 20 15 70
6 2131 male 28 山东大学 3.3 15 7 13
7 4321 male 26 复旦大学 3.6 9 6 52

根据示例，你的查询应返回以下结果：

age_cut number
25岁以下 4
25岁及以上 3

答案

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

27、查看不同年龄的用户明细

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

示例：user_profile

id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt
1 2138 male 21 北京大学 3.4 7 2 12
2 3214 male 复旦大学 4 15 5 25
3 6543 female 20 北京大学 3.2 12 3 30
4 2315 female 23 浙江大学 3.6 5 1 2
5 5432 male 25 山东大学 3.8 20 15 70
6 2131 male 28 山东大学 3.3 15 7 13
7 4321 male 26 复旦大学 3.6 9 6 52

根据示例，你的查询应返回以下结果：

device_id gender age_cut
2138 male 20-24岁
3214 male 其他
6543 female 20-24岁
2315 female 20-24岁
5432 male 25岁及以上
2131 male 25岁及以上
4321 male 25岁及以上

答案

select
device_id,gender,
(case when  20<=age and age <24 then '20-24岁'
    when age>=25 then '25岁及以上'
    else '其他' end ) as age_cut
FROM user_profile

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

题目：现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。

示例：question_practice_detail

id device_id question_id result date
1 2138 111 wrong 2021-05-03
2 3214 112 wrong 2021-05-09
3 3214 113 wrong 2021-06-15
4 6543 111 right 2021-08-13
5 2315 115 right 2021-08-13
6 2315 116 right 2021-08-14
7 2315 117 wrong 2021-08-15
……

根据示例，你的查询应返回以下结果：

day question_cnt
13 5
14 2
15 3
16 1
18 1

答案

select 
day(date) as day,count(question_id) as question_cnt
from question_practice_detail
group by date
having year(date) = '2021' and month(date) = '08'

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

题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

示例：question_practice_detail

id device_id quest_id result date
1 2138 111 wrong 2021-05-03
2 3214 112 wrong 2021-05-09
3 3214 113 wrong 2021-06-15
4 6543 111 right 2021-08-13
5 2315 115 right 2021-08-13
6 2315 116 right 2021-08-14
7 2315 117 wrong 2021-08-15
……

根据示例，你的查询应返回以下结果：

avg_ret
0.3000

答案

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)

30、统计每种性别的人数

题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果

示例：user_submit

device_id profile blog_url
2138 180cm,75kg,27,male http:/url/bigboy777
3214 165cm,45kg,26,female http:/url/kittycc
6543 178cm,65kg,25,male http:/url/tiger
4321 171cm,55kg,23,female http:/url/uhksd
2131 168cm,45kg,22,female http:/urlsydney

根据示例，你的查询应返回以下结果：

gender number
male 2
female 3

答案

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

31、提取博客URL中的用户名

题目：对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据。

示例：user_submit

device_id profile blog_url
2138 180cm,75kg,27,male http:/ur/bisdgboy777
3214 165cm,45kg,26,female http:/url/dkittycc
6543 178cm,65kg,25,male http:/ur/tigaer
4321 171 cm,55kg,23,female http:/url/uhksd
2131 168cm,45kg,22,female http:/url/sydney

根据示例，你的查询应返回以下结果：

device_id user_name
2138 bisdgboy777
3214 dkittycc
6543 tigaer
4321 uhsksd
2131 sydney

答案

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

法2：替换函数
select 
device_id,
replace('http:/url/','') as user_name
from user_submit
;

法3：trim函数
select 
device_id ,
trim('http:/url/' from blog_url) as user_name 
from user_submit

32、截取年龄

题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个年龄的用户分别有多少参赛者，请取出相应结果

示例：user_submit

device_id profile blog_url
2138 180cm,75kg,27,male http:/ur/bigboy777
3214 165cm,45kg,26,female http:/url/kittycc
6543 178cm,65kg,25,male http:/url/tiger
4321 171cm,55kg,23,female http:/url/uhksd
2131 168cm,45kg,22,female http:/url/sydney

根据示例，你的查询应返回以下结果：

age number
27 1
26 1
25 1
23 1
22 1

答案

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

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

题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

示例：user_profile

id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt
1 2138 male 21 北京大学 3.4 7 2 12
2 3214 male 复旦大学 4 15 5 25
3 6543 female 20 北京大学 3.2 12 3 30
4 2315 female 23 浙江大学 3.6 5 1 2
5 5432 male 25 山东大学 3.8 20 15 70
6 2131 male 28 山东大学 3.3 15 7 13
7 4321 female 26 复旦大学 3.6 9 6 52

根据示例，你的查询结果应参考以下格式，输出结果按university升序排序：

device_id university gpa
6543 北京大学 3.2000
4321 复旦大学 3.6000
2131 山东大学 3.3000
2315 浙江大学 3.6000

答案

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

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

题目： 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

示例：用户信息表user_profile

id device_id gender age university gpa active_days_within_30
1 2138 male 21 北京大学 3.4 7
2 3214 male 复旦大学 4.0 15
3 6543 female 20 北京大学 3.2 12
4 2315 female 23 浙江大学 3.6 5
5 5432 male 25 山东大学 3.8 20
6 2131 male 28 山东大学 3.3 15
7 4321 female 26 复旦大学 3.6 9

示例：question_practice_detail

id device_id question_id result date
1 2138 111 wrong 2021-05-03
2 3214 112 wrong 2021-05-09
3 3214 113 wrong 2021-06-15
4 6543 111 right 2021-08-13
5 2315 115 right 2021-08-13
6 2315 116 right 2021-08-14
7 2315 117 wrong 2021-08-15
……

根据示例，你的查询应返回以下结果：

device_id university question_cnt right_question_cnt
3214 复旦大学 3 0
4321 复旦大学 0 0

答案

select 
u.device_id,
university,
count(question_id) as question_cnt,
count(if(q.result='right',1,null)) as right_question_cnt
from user_profile as u
left join question_practice_detail as q
on u.device_id = q.device_id and month(q.date) = '8'
group by university,device_id
having university = '复旦大学'

注

count(if(q.result='right',1,0))  mysql 数据库中 count() 函数是统计查询结果中的行数 如果是0 还是默认会累加 但count 会忽略掉 NULL 的结果
SUM(IF(result = "right", 1, 0))  mysql 中 sum() 函数用于计算某一字段中所有行的数值之和（ sum 求和时会对 null 进行过滤，不计算）

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

题目：现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。

示例： user_profile

id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt
1 2138 male 21 北京大学 3.4 7 2 12
2 3214 male 复旦大学 4 15 5 25
3 6543 female 20 北京大学 3.2 12 3 30
4 2315 female 23 浙江大学 3.6 5 1 2
5 5432 male 25 山东大学 3.8 20 15 70
6 2131 male 28 山东大学 3.3 15 7 13
7 4321 female 26 复旦大学 3.6 9 6 52

示例： question_practice_detail

id device_id question_id result
1 2138 111 wrong
2 3214 112 wrong
3 3214 113 wrong
4 6543 111 right
5 2315 115 right
6 2315 116 right
7 2315 117 wrong

示例： question_detail

question_id difficult_level
111 hard
112 medium
113 easy
115 easy
116 medium
117 easy

根据示例，你的查询应返回以下结果：

difficult_level correct_rate
easy 0.5000
medium 1.0000

答案

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

39、21年8月份练题总数

题目： 现在运营想要了解2021年8月份所有练习过题目的总用户数和练习过题目的总次数，请取出相应结果

示例：question_practice_detail

id device_id question_id result date
1 2138 111 wrong 2021-05-03
2 3214 112 wrong 2021-05-09
3 3214 113 wrong 2021-06-15
4 6543 111 right 2021-08-13
5 2315 115 right 2021-08-13
6 2315 116 right 2021-08-14
7 2315 117 wrong 2021-08-15
……

根据的示例，你的查询应返回以下结果：

did_cnt question_cnt
3 12

答案

select
count(distinct device_id) as did_cnt,count(question_id	) as question_cnt
from question_practice_detail
where month(date) = '8'

100、确定最佳顾客的另一种方式

OrderItems表代表订单信息，确定最佳顾客的另一种方式是看他们花了多少钱，OrderItems表有订单号order_num和item_price商品售出价格、quantity商品数量

order_num item_price quantity
a1 10 105
a2 1 1100
a2 1 200
a4 2 1121
a5 5 10
a2 1 19
a7 7 5

Orders表含有字段order_num 订单号、cust_id顾客id

order_num cust_id
a1 cust10
a2 cust1
a3 cust2
a4 cust22
a5 cust221
a7 cust2217

顾客表Customers有字段cust_id 客户id、cust_name 客户姓名

cust_id cust_name
cust10 andy
cust1 ben
cust2 tony
cust22 tom
cust221 an
cust2217 hex

【问题】编写 SQL 语句，返回订单总价不小于1000 的客户名称和总额（OrderItems 表中的order_num）。

提示：需要计算总和（item_price 乘以 quantity）。按总额对结果进行排序，请使用INNER JOIN 语法。

【示例结果】

cust_name total_price
andy 1050
ben 1319
tom 2242

【示例解析】

总额（item_price 乘以 quantity）大于等于1000的订单号，例如a2对应的顾客id为cust1，cust1的顾客名称cust_name是ben，最后返回ben作为order_num a2的quantity * item_price总和的结果1319。

答案

select cust_name,sum(item_price * quantity) as total_price
from Customers as a
join Orders as b on a.cust_id = b.cust_id
join OrderItems as c on b.order_num = c.order_num
group by c.order_num ,cust_name
having total_price>=1000
order by total_price

117、删除记录

现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录，结构如下表：

Filed Type Null Key Extra Default Comment
id int(11) NO PRI auto_increment (NULL) 自增ID
uid int(11) NO (NULL) 用户ID
exam_id int(11) NO (NULL) 试卷ID
start_time datetime NO (NULL) 开始时间
submit_time datetime YES (NULL) 提交时间
score tinyint(4) YES (NULL) 得分

请删除exam_record表中所有记录，并重置自增主键。

答案

truncate table exam_record

注

1.DELETE 可以加where 条件，truncate 不能加

2.truncate删除，效率高一些

3.假如要删除的表中有自增长列，如果用delete 删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始


4.truncate删除后没有返回值，delete 删除有返回值

5.truncate 删除后不能回滚，delete 删除可以回滚
posted @ 2023-09-08 18:03  glimpsee  阅读(530)  评论(0)    收藏  举报