mysql查询的一个问题

mysql查询的一个问题

带着问题去学习,然后将这个过程中涉及到的知识点都学习一遍

查询出每个老师(要有老师名字) 对应科目所有学生的平均分,最低分,最高分,和总分,并且按照平均分降序排序

数据表结构介绍

t_student:有字段s_id,s_name,t_id,学生的id,名字,学生所属老师id

t_teacher:有字段t_id,t_name,教师d,名字

t_score:有字段scorre_id,score_score,score_subject,stu_id,成绩id,分数,科目,是哪个学生的成绩。

表的sql放到了,最后

分析一下问题:

  • 看到“每个老师”,“对应科目”,会想到分组查询,需要按照老师来分组,然后再根据科目来分组
  • 分组后,需要用到组函数,AVG,MAX,MIN,得出平均分,最低分,最高分
  • 按照平均分降序,需要用到排序 ORDER BY DESC (DESC代表降序,ASC代表升序,默认是升序)
  • 需要的这几个字段分布在三个表中,要多表连接查询

答案

SELECT r.*
FROM(
SELECT st.t_id,t.t_name,sc.score_subject,AVG(sc.score_score) avg_score,MIN(sc.score_score) 最低分,MAX(sc.score_score) 最高分,SUM(sc.score_score) 总分
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
INNER JOIN t_teacher t
ON st.t_id=t.t_id
GROUP BY st.t_id,sc.score_subject
)r
ORDER BY r.avg_score DESC;

忽然一下上来这么多,有点懵,我们分解开来看,先由一个个的小问题,积累解决

解题

我们先从简单的,一步一步来。

查每个老师有多少的学生

SELECT s.t_id,COUNT(s.t_id) # 用到了计数的组函数count
FROM t_student s
GROUP BY s.t_id;  # 按照 教师 分组

+------+---------------+
| t_id | COUNT(s.t_id) |
+------+---------------+
|    1 |             3 |
|    2 |             2 |
+------+---------------+
2 rows in set (0.00 sec)

我们只需要对学生表 按照教师的id分组,来单表查询,使用组函数计数

可是我们发现,这里只有老师的id,显示出来不好看

那么,我们就可以做一个连接查询,将教师表和学生表连接起来(通过教师的id,他们都有的字段)

SELECT s.t_id,t.t_name,COUNT(s.t_id) # 用到了计数的组函数count
FROM t_student s
INNER JOIN t_teacher t # 通过t_id,来内连接学生表和教师表
ON t.t_id=s.t_id
GROUP BY s.t_id;  # 按照 教师 分组

+------+--------+---------------+
| t_id | t_name | COUNT(s.t_id) |
+------+--------+---------------+
|    1 | 张老师 |             3 |
|    2 | 王老师 |             2 |
+------+--------+---------------+
2 rows in set (0.00 sec)

教师姓名在t_teacher中,所以我们需要和t_student来做连接查询,使用的是内连接 INNER JOIN … ON

连接还有 左外连接和右外连接(LEFT JOIN 和 RIGHT JOIN),按照左外连接为例:左表中有的记录,必定出来,如果右表中没有对应的,那么值设置为null(可以想一下,先把select选中的字段头写出来,然后,左表的所有记录都填进去,右表的只填有共同特点的)

可以参考https://blog.csdn.net/plg17/article/details/78758593,这里图解很清晰

查每个老师学生的平均值

SELECT s.t_id,AVG(sc.score_score)
FROM t_student s
INNER JOIN t_score sc
ON sc.stu_id=s.s_id
GROUP BY s.t_id;

+------+---------------------+
| t_id | AVG(sc.score_score) |
+------+---------------------+
|    1 |             76.6667 |
|    2 |             78.3333 |
+------+---------------------+
2 rows in set (0.00 sec)

把学生表和成绩表内连接,按照老师的id分组,利用AVG函数得出平均分数

还可以加入一个内连接到教师表,根据教师的id得到名字(这里就不写出来了)

查询每个学生的科目数

SELECT st.t_id,st.s_name,COUNT(*) 学生科目数
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
GROUP BY st.s_id;

+------+--------+------------+
| t_id | s_name | 学生科目数 |
+------+--------+------------+
|    1 | 张三   |          3 |
|    1 | 李四   |          3 |
|    2 | 王五   |          3 |
|    2 | 王qi7  |          3 |
+------+--------+------------+
4 rows in set (0.00 sec)

按照学生的 id 来分组,然后计算每组总数

通过 t_score 和 t_student 的内连接,根据学生id 得到 学生名字

查询每个科目的平均分、最高分、最低分

SELECT sc.score_subject subject,AVG(sc.score_score) avgScore,MAX(sc.score_score) maxScore,MIN(sc.score_score) minScore
FROM t_score sc
GROUP BY sc.score_subject;

+---------+----------+----------+----------+
| subject | avgScore | maxScore | minScore |
+---------+----------+----------+----------+
| 数学    |  80.0000 |       80 |       80 |
| 英语    |  62.5000 |       70 |       60 |
| 语文    |  90.0000 |       90 |       90 |
+---------+----------+----------+----------+
3 rows in set (0.00 sec)

按照 科目 来分组,然后计算每组的平均分、最大分,最小分

查询出每个老师(不带老师名字的) 对应科目所有学生的平均分,最低分,最高分,和总分

SELECT st.t_id,sc.score_subject subject,AVG(sc.score_score) avgScore,MAX(sc.score_score) maxScore,MIN(sc.score_score) minScore,SUM(sc.score_score) allScore  # 选取哪些字段?  并且给选取的字段取别名
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
GROUP BY st.t_id,sc.score_subject; # 对老师 、科目 分组

+------+---------+----------+----------+----------+----------+
| t_id | subject | avgScore | maxScore | minScore | allScore |
+------+---------+----------+----------+----------+----------+
|    1 | 数学    |  80.0000 |       80 |       80 |      160 |
|    1 | 英语    |  60.0000 |       60 |       60 |      120 |
|    1 | 语文    |  90.0000 |       90 |       90 |      180 |
|    2 | 数学    |  80.0000 |       80 |       80 |      160 |
|    2 | 英语    |  65.0000 |       70 |       60 |      130 |
|    2 | 语文    |  90.0000 |       90 |       90 |      180 |
+------+---------+----------+----------+----------+----------+
6 rows in set (0.00 sec)

要分两次组,第一次按照老师分组 ,然后第二次对 科目分组

然后,分别计算他们的平均分,最低分,最高分,和总分

接下来把老师的名字加上

#查询出每个老师(要有老师名字) 对应科目所有学生的平均分,最低分,最高分,和总分
SELECT st.t_id,t.t_name,sc.score_subject,AVG(sc.score_score) 平均分,MIN(sc.score_score) 最低分,MAX(sc.score_score) 最高分,SUM(sc.score_score) 总分
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
INNER JOIN t_teacher t
ON st.t_id=t.t_id
GROUP BY st.t_id,sc.score_subject;

+------+--------+---------------+---------+--------+--------+------+
| t_id | t_name | score_subject | 平均分  | 最低分 | 最高分 | 总分 |
+------+--------+---------------+---------+--------+--------+------+
|    1 | 张老师 | 数学          | 80.0000 |     80 |     80 |  160 |
|    1 | 张老师 | 英语          | 60.0000 |     60 |     60 |  120 |
|    1 | 张老师 | 语文          | 90.0000 |     90 |     90 |  180 |
|    2 | 王老师 | 数学          | 80.0000 |     80 |     80 |  160 |
|    2 | 王老师 | 英语          | 65.0000 |     60 |     70 |  130 |
|    2 | 王老师 | 语文          | 90.0000 |     90 |     90 |  180 |
+------+--------+---------------+---------+--------+--------+------+
6 rows in set (0.00 sec)

和上边的相比,这部分代码就是多了,一个 按照 老师id,对 老师表 和 学生表 的内连接。

然后输出 老师的名字

查询出每个老师(要有老师名字) 对应科目所有学生的平均分,最低分,最高分,和总分,并且按照平均分降序排序

SELECT r.*
FROM( # 子查询开始
SELECT st.t_id,t.t_name,sc.score_subject,AVG(sc.score_score) avg_score,MIN(sc.score_score) 最低分,MAX(sc.score_score) 最高分,SUM(sc.score_score) 总分
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
INNER JOIN t_teacher t
ON st.t_id=t.t_id
GROUP BY st.t_id,sc.score_subject
)r # 子查询结束,并取别名为 r
ORDER BY r.avg_score DESC;
#### 注意: 作为二维表使用的查询结果必须有表别名 ,这里是 r

+------+--------+---------------+-----------+--------+--------+------+
| t_id | t_name | score_subject | avg_score | 最低分 | 最高分 | 总分 |
+------+--------+---------------+-----------+--------+--------+------+
|    1 | 张老师 | 语文          |   90.0000 |     90 |     90 |  180 |
|    2 | 王老师 | 语文          |   90.0000 |     90 |     90 |  180 |
|    1 | 张老师 | 数学          |   80.0000 |     80 |     80 |  160 |
|    2 | 王老师 | 数学          |   80.0000 |     80 |     80 |  160 |
|    2 | 王老师 | 英语          |   65.0000 |     60 |     70 |  130 |
|    1 | 张老师 | 英语          |   60.0000 |     60 |     60 |  120 |
+------+--------+---------------+-----------+--------+--------+------+
6 rows in set (0.00 sec)



相比上一个问题,就是多了一个,按照平均分降序

那么,这里使用子查询,将上一个查询结果,作为子查询,并且取别名为 r,对r 按照平均分降序排序

也可以不使用子查询,这两种写法是等价的

# 另一种写法
SELECT st.t_id,t.t_name,sc.score_subject,AVG(sc.score_score) avg_score,MIN(sc.score_score) 最低分,MAX(sc.score_score) 最高分,SUM(sc.score_score) 总分
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
INNER JOIN t_teacher t
ON st.t_id=t.t_id
GROUP BY st.t_id,sc.score_subject
ORDER BY avg_score DESC;




在分组后我们也可以对数据进行筛选

比如,选出每组里面的,平均分大于80的,注意,这里要使用HAVING,而不是where

### 对于分组的筛选,必须使用HAVING
SELECT st.t_id,t.t_name,sc.score_subject,AVG(sc.score_score) avg_score,MIN(sc.score_score) 最低分,MAX(sc.score_score) 最高分,SUM(sc.score_score) 总分
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
INNER JOIN t_teacher t
ON st.t_id=t.t_id
GROUP BY st.t_id,sc.score_subject
HAVING avg_score > 80  # 分组筛选条件
ORDER BY avg_score DESC;


####  对于子查询,使用where
SELECT r.*
FROM( # 子查询开始
SELECT st.t_id,t.t_name,sc.score_subject,AVG(sc.score_score) avg_score,MIN(sc.score_score) 最低分,MAX(sc.score_score) 最高分,SUM(sc.score_score) 总分
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
INNER JOIN t_teacher t
ON st.t_id=t.t_id
GROUP BY st.t_id,sc.score_subject
)r # 子查询结束,并取别名为 r
WHERE r.avg_score > 80   # 子查询,没有分组,这里使用where筛选
ORDER BY r.avg_score DESC;

+------+--------+---------------+-----------+--------+--------+------+
| t_id | t_name | score_subject | avg_score | 最低分 | 最高分 | 总分 |
+------+--------+---------------+-----------+--------+--------+------+
|    1 | 张老师 | 语文          |   90.0000 |     90 |     90 |  180 |
|    2 | 王老师 | 语文          |   90.0000 |     90 |     90 |  180 |
+------+--------+---------------+-----------+--------+--------+------+
2 rows in set (0.00 sec)

在分组查询中,必须使用HAVING来筛选,不可以使用WHERE

涉及到的知识点

  • 多表连接查询(内连接),没有用到(左外连接、右外连接)
  • 分组查询(GROPU BY ),筛选使用HAVING关键字,不可以使用WHERE
  • 分组查询里面,涉及到的组函数: AVG,MAX,MIN,COUNT,这是最常用的,还有一些其他的没有用到
  • 排序(ORDER BY),DESC 降序,ASC升序,默认升序
  • 子查询 ,一个查询的结果作为另一个查询的输入,作为二维表使用的查询结果必须有表别名
  • 再说最基本的查询格式 : SELECT [要查询的字段] from [表] WHERE [筛选的条件]
  • 对查询结果取别名,对表取别名

具体的知识点,概念,就不在这里详细罗列了,很多地方都有

分组

多表连接

排序

子查询

练习用的数据表

/*
SQLyog Ultimate v8.32 
MySQL - 5.7.19 : Database - student
*********************************************************************
*/
CREATE TABLE `t_score` (
  `scorre_id` int(11) NOT NULL AUTO_INCREMENT,
  `score_score` int(3) DEFAULT NULL,
  `score_subject` varchar(50) DEFAULT NULL,
  `stu_id` int(9) DEFAULT NULL,
  PRIMARY KEY (`scorre_id`),
  KEY `FK_t_score` (`stu_id`),
  CONSTRAINT `FK_t_score` FOREIGN KEY (`stu_id`) REFERENCES `t_student` (`s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

/*Data for the table `t_score` */

insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (3,90,'语文',2);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (4,80,'数学',2);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (5,60,'英语',2);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (6,90,'语文',1);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (7,80,'数学',1);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (8,60,'英语',1);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (9,90,'语文',3);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (10,80,'数学',3);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (11,60,'英语',3);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (12,90,'语文',4);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (13,80,'数学',4);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (15,70,'英语',4);

/*Table structure for table `t_student` */

CREATE TABLE `t_student` (
  `s_id` int(9) NOT NULL AUTO_INCREMENT,
  `s_name` varchar(50) DEFAULT NULL,
  `t_id` int(9) DEFAULT NULL,
  PRIMARY KEY (`s_id`),
  KEY `FK_t_student` (`t_id`),
  CONSTRAINT `FK_t_student` FOREIGN KEY (`t_id`) REFERENCES `t_teacher` (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `t_student` */

insert  into `t_student`(`s_id`,`s_name`,`t_id`) values (1,'张三',1);
insert  into `t_student`(`s_id`,`s_name`,`t_id`) values (2,'李四',1);
insert  into `t_student`(`s_id`,`s_name`,`t_id`) values (3,'王五',2);
insert  into `t_student`(`s_id`,`s_name`,`t_id`) values (4,'王qi7',2);
insert  into `t_student`(`s_id`,`s_name`,`t_id`) values (5,'钱一',1);

/*Table structure for table `t_teacher` */

CREATE TABLE `t_teacher` (
  `t_id` int(9) NOT NULL AUTO_INCREMENT,
  `t_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `t_teacher` */

insert  into `t_teacher`(`t_id`,`t_name`) values (1,'张老师');
insert  into `t_teacher`(`t_id`,`t_name`) values (2,'王老师');

参考链接

https://blog.csdn.net/plg17/article/details/78758593

https://zhuanlan.zhihu.com/p/46925457

https://blog.csdn.net/J080624/article/details/72909945

ULL,
PRIMARY KEY (t_id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table t_teacher */

insert into t_teacher(t_id,t_name) values (1,‘张老师’);
insert into t_teacher(t_id,t_name) values (2,‘王老师’);




## 参考链接

https://blog.csdn.net/plg17/article/details/78758593

https://zhuanlan.zhihu.com/p/46925457

https://blog.csdn.net/J080624/article/details/72909945

https://www.runoob.com/mysql/mysql-order-by.html
已标记关键词 清除标记
相关推荐
<p style="text-align:left;"> <span> </span> </p> <p class="ql-long-24357476" style="font-size:11pt;color:#494949;"> <span style="font-family:"color:#E53333;font-size:14px;background-color:#FFFFFF;line-height:24px;"><span style="line-height:24px;">限时福利1:</span></span><span style="font-family:"color:#3A4151;font-size:14px;background-color:#FFFFFF;">购课进答疑群专享柳峰(刘运强)老师答疑服务。</span> </p> <p> <br /> </p> <p class="ql-long-24357476"> <strong><span style="color:#337FE5;font-size:14px;">为什么说每一个程序员都应该学习MySQL?</span></strong> </p> <p class="ql-long-24357476"> <span style="font-size:14px;">根据《2019-2020年中国开发者调查报告》显示,超83%的开发者都在使用MySQL数据库。</span> </p> <p class="ql-long-24357476"> <img src="https://img-bss.csdn.net/202003301212574051.png" alt="" /> </p> <p class="ql-long-24357476"> <span style="font-size:14px;">使用量大同时,掌握MySQL早已是运维、DBA的必备技能,甚至部分IT开发岗位也要求对数据库使用和原理有深入的了解和掌握。</span><br /> <br /> <span style="font-size:14px;">学习编程,你可能会犹豫选择 C++ 还是 Java;入门数据科学,你可能会纠结于选择 Python 还是 R;但无论如何, MySQL 都是 IT 从业人员不可或缺的技能!</span> </p> <span></span> <p> <br /> </p> <p> <span> </span> </p> <h3 class="ql-long-26664262"> <p style="font-size:12pt;"> <strong class="ql-author-26664262 ql-size-14"><span style="font-size:14px;color:#337FE5;">【课程设计】</span></strong> </p> <p style="font-size:12pt;"> <span style="color:#494949;font-weight:normal;"><br /> </span> </p> <p style="font-size:12pt;"> <span style="color:#494949;font-weight:normal;font-size:14px;">在本课程中,刘运强老师会结合自己十多年来对MySQL的心得体会,通过课程给你分享一条高效的MySQL入门捷径,让学员少走弯路,彻底搞懂MySQL。</span> </p> <p style="font-size:12pt;"> <span style="color:#494949;font-weight:normal;"><br /> </span> </p> <p style="font-size:12pt;"> <span style="font-weight:normal;font-size:14px;">本课程包含3大模块:</span><span style="font-weight:normal;font-size:14px;"> </span> </p> </h3> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <strong class="ql-author-26664262"><span style="font-size:14px;">一、基础篇:</span></strong> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <span class="ql-author-26664262" style="font-size:14px;">主要以最新的MySQL8.0安装为例帮助学员解决安装与配置MySQL问题,并对MySQL8.0的新特性做一定介绍,为后续的课程展开做好环境部署。</span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <span class="ql-author-26664262" style="font-size:14px;"><br /> </span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <strong class="ql-author-26664262"><span style="font-size:14px;">二、SQL语言篇</span></strong><span class="ql-author-26664262" style="font-size:14px;">:</span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <span class="ql-author-26664262" style="font-size:14px;">本篇主要讲解SQL语言的四大部分数据查询语言DQL,数据操纵语言DML,数据定义语言DDL,数据控制语言DCL,</span><span style="font-size:14px;">学会熟练对库表进行增删改查等必备技能。</span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <span style="font-size:14px;"><br /> </span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <strong class="ql-author-26664262"><span style="font-size:14px;">三、MySQL进阶篇</span></strong><span style="font-size:14px;">:</span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <span style="font-size:14px;">本篇可以帮助学员更加高效的管理线上的MySQL数据库;具备MySQL的日常运维能力,语句调优、备份恢复等思路。</span> </p> <span><span> <p style="font-size:11pt;color:#494949;"> <span style="font-size:14px;"> </span><img src="https://img-bss.csdn.net/202004220208351273.png" alt="" /> </p> </span></span>
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页