数据库系统原理与应用教程（060）—— MySQL 练习题：操作题 11-20（四）

数据库系统原理与应用教程（060）—— MySQL 练习题：操作题 11-20（四）：分组查询与聚合函数

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

表结构及数据如下：

/*
drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学',3.3);
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

解答：

mysql> select device_id, gender, age, university, gpa from user_profile 
    -> where gender = 'male' and gpa > 3.5;
+-----------+--------+------+--------------+------+
| device_id | gender | age  | university   | gpa  |
+-----------+--------+------+--------------+------+
|      3214 | male   | NULL | 复旦大学     |    4 |
|      5432 | male   |   25 | 山东大学     |  3.8 |
+-----------+--------+------+--------------+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

12、逻辑运算符（2）：OR

题目：要查询学校为北京大学或 GPA 在 3.7 以上（不包括 3.7）的用户信息，取出相关数据。

示例：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

解答：

mysql> select device_id, gender, age, university, gpa from user_profile 
    -> where university = '北京大学' or gpa > 3.7;
+-----------+--------+------+--------------+------+
| device_id | gender | age  | university   | gpa  |
+-----------+--------+------+--------------+------+
|      2138 | male   |   21 | 北京大学     |  3.4 |
|      3214 | male   | NULL | 复旦大学     |    4 |
|      6543 | female |   20 | 北京大学     |  3.2 |
|      5432 | male   |   25 | 山东大学     |  3.8 |
+-----------+--------+------+--------------+------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

13、逻辑运算符（3）：AND 和 OR 混合运用

题目：要查找 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

查询应返回以下结果：

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 university = '山东大学' and gpa > 3.5 OR university = '复旦大学' and gpa > 3.8;
*/
mysql> select device_id, gender, age, university, gpa from user_profile 
    -> where university = '山东大学' and gpa > 3.5 OR university = '复旦大学' and gpa > 3.8;
+-----------+--------+------+--------------+------+
| device_id | gender | age  | university   | gpa  |
+-----------+--------+------+--------------+------+
|      3214 | male   | NULL | 复旦大学     |    4 |
|      5432 | male   |   25 | 山东大学     |  3.8 |
+-----------+--------+------+--------------+------+
2 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

14、比较运算符 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

解答：

-- 使用比较运算符 IN
mysql> select device_id, gender, age, university, gpa from user_profile 
    -> where university in ('北京大学','复旦大学','山东大学');
+-----------+--------+------+--------------+------+
| device_id | gender | age  | university   | gpa  |
+-----------+--------+------+--------------+------+
|      2138 | male   |   21 | 北京大学     |  3.4 |
|      3214 | male   | NULL | 复旦大学     |    4 |
|      6543 | female |   20 | 北京大学     |  3.2 |
|      5432 | male   |   25 | 山东大学     |  3.8 |
+-----------+--------+------+--------------+------+
4 rows in set (0.01 sec)

-- 使用逻辑运算符 OR
mysql> select device_id, gender, age, university, gpa from user_profile 
    -> where university = '北京大学' OR university = '复旦大学' OR university = '山东大学';
+-----------+--------+------+--------------+------+
| device_id | gender | age  | university   | gpa  |
+-----------+--------+------+--------------+------+
|      2138 | male   |   21 | 北京大学     |  3.4 |
|      3214 | male   | NULL | 复旦大学     |    4 |
|      6543 | female |   20 | 北京大学     |  3.2 |
|      5432 | male   |   25 | 山东大学     |  3.8 |
+-----------+--------+------+--------------+------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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	北京师范大学

解答：

mysql> select device_id, age, university from user_profile where university like '%北京%';
+-----------+------+--------------------+
| device_id | age  | university         |
+-----------+------+--------------------+
|      2138 |   21 | 北京大学           |
|      6543 |   20 | 北京大学           |
|      2131 |   28 | 北京师范大学       |
+-----------+------+--------------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

16、查询中使用聚合函数（1）

题目：要查询复旦大学学生 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 位小数：

gpa
3.8

解答：

mysql> select round(max(gpa),1) gpa from user_profile where university like '复旦大学';
+------+
| gpa  |
+------+
|  3.8 |
+------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7

17、查询中使用聚合函数（2）

题目：要查询男性用户有多少人以及他们的平均 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 位小数：

male_num	avg_gpa
4	3.6

解答：

mysql> select count(*) male_num, round(avg(gpa),1) avg_gpa from user_profile where gender = 'male';
+----------+---------+
| male_num | avg_gpa |
+----------+---------+
|        4 |     3.6 |
+----------+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

18、分组查询（1）

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

示例：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	male	26	复旦大学	3.6	9	6	52

说明：

active_days_within_30：30 天内活跃天数
question_cnt：发帖数量字段
answer_cnt：回答数量字段

查询结果如下（需要对性别和学校分组），查询结果四舍五入保留 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

表结构及数据如下：

/*
drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` float,
`question_cnt` float,
`answer_cnt` float
);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

解答：

/*
select gender, university, count(*) user_num, 
avg(active_days_within_30) avg_active_day,
avg(question_cnt) avg_question_cnt
from user_profile group by gender, university;
*/
mysql> select gender, university, count(*) user_num, 
    -> avg(active_days_within_30) avg_active_day,
    -> avg(question_cnt) avg_question_cnt
    -> from user_profile group by gender, university;
+--------+--------------+----------+----------------+------------------+
| gender | university   | user_num | avg_active_day | avg_question_cnt |
+--------+--------------+----------+----------------+------------------+
| female | 北京大学     |        1 |             12 |                3 |
| female | 浙江大学     |        1 |              5 |                1 |
| male   | 北京大学     |        1 |              7 |                2 |
| male   | 复旦大学     |        2 |             12 |              5.5 |
| male   | 山东大学     |        2 |           17.5 |               11 |
+--------+--------------+----------+----------------+------------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

19、分组查询（2）

题目：要查看每个学校用户的平均发贴和回帖情况，取出平均发贴数低于 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

查询应返回以下结果，数据四舍五入保留 3 位小数：

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

解答：

/*
select university, 
round(avg(question_cnt),3) avg_question_cnt,
round(avg(answer_cnt),3) avg_answer_cnt
from user_profile 
group by university
having avg_question_cnt < 5 or avg_answer_cnt < 20;
*/
mysql> select university, 
    -> round(avg(question_cnt),3) avg_question_cnt,
    -> round(avg(answer_cnt),3) avg_answer_cnt
    -> from user_profile 
    -> group by university
    -> having avg_question_cnt < 5 or avg_answer_cnt < 20;
+--------------+------------------+----------------+
| university   | avg_question_cnt | avg_answer_cnt |
+--------------+------------------+----------------+
| 北京大学     |            2.500 |         21.000 |
| 浙江大学     |            1.000 |          2.000 |
+--------------+------------------+----------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

20、分组查询（3）

题目：要查看不同大学的用户平均发帖情况，结果按照平均发帖情况进行升序排列，取出相应数据。

示例：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

查询应返回以下结果：

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

解答：

/*
select university, 
round(avg(question_cnt),4) avg_question_cnt
from user_profile 
group by university
order by 2;
*/
mysql> select university, 
    -> round(avg(question_cnt),4) avg_question_cnt
    -> from user_profile 
    -> group by university
    -> order by 2;
+--------------+------------------+
| university   | avg_question_cnt |
+--------------+------------------+
| 浙江大学     |           1.0000 |
| 北京大学     |           2.5000 |
| 复旦大学     |           5.5000 |
| 山东大学     |          11.0000 |
+--------------+------------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

000

相关阅读:
[树上倍增]Eezie and Pie 2022牛客多校第6场 B
重采样--学习笔记
 荧光标记氨基酸：荧光标记L-苯丙氨酸乙酯盐酸盐，L-phenylalanine ethylester labeled
第二部分：CSS3
Win10开机启动项设置方法汇总
 计算机毕业设计基于SpringBoot产业园区智慧公寓管理系统的设计与实现 Javaweb项目 Java实战项目前后端分离文档报告代码讲解安装调试
 毕业设计之基于node.js+Vue的企业员工信息管理系统 Elementui
无胁科技-TVD每日漏洞情报-2022-10-26
pc端使用微信扫码登录（思路篇）
ShardingSphere主从复制
原文地址：https://blog.csdn.net/weixin_44377973/article/details/126019525