MySQL_表_进阶(2/2)

Bear on Toilet

已于 2024-09-26 17:22:29 修改

阅读量1k

点赞数 9

文章标签： mysql 数据库

于 2024-09-26 14:05:59 首次发布

本文链接：https://blog.csdn.net/2301_80682565/article/details/142536332

版权

上一章我们谈了排序子句，使用ORDER BY 字段 DESC/ASC。以及左右连接的多关系查询。

今天，没错，四张表最后两个需求 ✨涉及聚合函数查询与指定别名

四张表：

学院表：(testdb.dept)

课程表：(testdb.course)

选课表:（testdb.sc）

学生表 :（testdb.stu）

需求如下：

查询每个学生的总成绩，显示学号、姓名、总成绩，列名以中文显示。
查询每门课程的选课人数，显示课程编号、课程名称、选课人数，没有学生选的课程也要显示，按选课人数降序排列。（提示：选课人数需要计算）

需求一：

查询每个学生的总成绩，显示学号、姓名、总成绩，列名以中文显示。

我们看到一个要求，也算闻所未闻：列名以中文显示。A，你发现，原来查询出来的结果表还可以自己命名列名。

这其实是对字段的取别名操作：

“列名以中文显示”——给字段名指定别名

我们在SELECT 语句后往往加上我们想要的字段，这时候，往往结果查出来的列表头就是字段名。

比如：我们查询学号为“2022121001”的学生的选课信息，要求显示他的学号，姓名和所选课程编号。

SELECT sc.stuid,stuname,cid
FROM testdb.sc
JOIN testdb.stu
ON stu.stuid = sc.stuid AND sc.stuid = '2022121001';
#因为stuname属于stu表，所以需要联合查询
#一连就发现，两张表都有stuid这个字段，因此，每个stuid前都需要加前缀。

最后显示的结果：

你可以看到，列表头，就是我们刚刚select语句要求显示的字段名：stuid,stuname,cid。

有些时候，为了更好的理解显示的信息，我们会为显示的字段指定别名。你想，stuid我若是原先不说是学生学号，其实是要反应时间的。取成中文，理解成本就会大大降低。

怎么取呢？

SELECT 字段 AS 别名
FROM ...
....

刚刚，显示的学生学号，姓名，课程编号，我全部取别名就是这样：

SELECT sc.stuid AS 学号,stuname AS 姓名,cid AS 选课课程号
FROM testdb.sc
JOIN testdb.stu
ON stu.stuid = sc.stuid AND sc.stuid = '2022121001';

效果如下：

需求分析：

显示学号，姓名没问题，就是这个总成绩......看起来有点问题，testdb.sc表（选课表）是有score(成绩)这个字段，但不是总成绩。

要显示总成绩，按照我们自己的逻辑：我们会把相同学号的几行合成一行，然后行的最后显示几个score的总和，这个总和就是他们的总成绩。

比如：学号2022121004这个同学，下表中他有两行记录，那么最后显示的结果，学号姓名总成绩

2022121004 李四 188

这个188就是把学号都是2022121004的score记录都加起来的

选课表:（testdb.sc）

对于这种，SQL内部提供了许多聚合函数：

常见的聚合函数
函数名称	功能
AVG	按列计算平均值
SUM	按列计算值的总和
MAX	求一列中的最大值
MIN	求一列中的最小值
COUNT	按列值统计个数

我们这里要计算“总成绩”，明显是要用这里的SUM，但是这个“按列”，就很有说法了。

按谁的列，按照什么来划分？

我们要求显示，每个同学的总成绩，testdb.sc（选课表）表里体现同学的，就只有stuid(学号)。那得出结论：按照相同的学号来分组，每个组内都使用聚合函数(此处是sum)，得到就是每个同学的总成绩。

分组的关键字：GROUP BY 加字段名（作为分组的依据）

代码就是这样写的：

SELECT sc.stuid AS 学号,stuname AS 姓名,SUM(score) AS 总成绩
FROM testdb.sc
#这里的总成绩调用的聚合函数，函数自然是需要参数，传进去的就是单个成绩
#主要的表还是sc表，但是stuname这个字段是来自stu表，还需要联合
GROUP BY sc.stuid
#按照学生学号来分组，相同学号的不同学科成绩进行聚合，就是咱们的总成绩

添加联合查询：

SELECT sc.stuid AS 学号,stuname AS 姓名,SUM(score) AS 总成绩
FROM testdb.sc
JOIN testdb.stu
ON stu.stuid = sc.stuid
GROUP BY sc.stuid;

所以，这个需求的代码就如上显示。

需求二：

查询每门课程的选课人数，显示课程编号、课程名称、选课人数，没有学生选的课程也要显示，按选课人数降序排列。（提示：选课人数需要计算）

这怎么也算咱们最后一个需求，这里咱们换个思路：这次真正的先写框架——把学过的都用起来

SELECT cid,cname,选课人数
FROM testsdb.sc
#说“选课人数”，姑且就从sc表选

这里的选课人数，应该就是数有多少个学生，sc表中使用stuid来代表一个学生。我们使用COUNT这个计数函数。

SELECT cid,cname,COUNT(stuid)
FROM testsdb.sc
#这里迟迟定不下cid前面的前缀，是因为course表也有这个字段，最后再来补充也不迟

因为cname这个字段属于course表，所以这里还需要联合查询。但是联合查询：有普通的JOIN，亦有左右连接，你又该怎么选呢？

SELECT cid,cname,COUNT(stuid)
FROM testsdb.sc
JOIN testdb.course
ON course.cid = sc.cid
#连表的条件还是相同的字段就行

继续读题： "没有学生选的课程也要显示"。我们这里若只是简单地使用默认的JOIN查询，它能达到的效果只是匹配两表都存在，返回两个表中有匹配的行。

若使用JOIN连接，一些course表里有的课程，但是在sc里没有被学生选（也就是说，sc表里没有记录）。这种课程，是无法匹配的，最后的结果也不会保留这种没被选的课的记录。

我们为了保持某一张的完整性，往往会采取左右连接，选出主表。这里既然要保证所有课程信息都在，那么，那张被保存下来的表应该就是course表。破案了，所以在上述代码，应该有右连接course表。

SELECT cid,cname,COUNT(stuid)
FROM testsdb.sc
RIGHT JOIN testdb.course
ON course.cid = sc.cid

现在来分组，既然是每个课程的选课人数，分组条件就是课程 ——把选同一门课程的stuid分为一组，COUNT函数对stuid进行计数，得到的结果就是该门课的选课人数。

SELECT cid,cname,COUNT(stuid)
FROM testsdb.sc
RIGHT JOIN testdb.course
ON course.cid = sc.cid
GROUP BY course.cid
#cid的前缀在确定主表的那一刻就注定了：在sc表可能并不存在，但是course里是一定有的。一旦两表的cid匹配不上，sc.cid会为NULL，但course.cid始终有效

注意：GROUP BY子句里面的分组字段，应该是尽力包含所有的SELECT语句的字段(聚合函数除外)。

不同的课程id也有其对应的课程名称cname，将它俩都作为分组条件，才是最推荐（原则上，是视具体问题而定）。你想，条件越细化，其结果的偏差就会越小。

所以，GROUP BY子句里面应该包含SELECT 语句的cid, 它俩具有绑定性。既然course.cid在course（主表）能保证数据的完整和有效，GROUP BY里使用course.cid,那SELECT 里的cid也应该来自testdb.course。

SELECT course.cid,cname,COUNT(stuid)
FROM testsdb.sc
RIGHT JOIN testdb.course
ON course.cid = sc.cid
GROUP BY course.cid

哈哈哈修文：我又回来了，我忽然想起来，这个需求还需要我们降序显示（🤐）

所以：

SELECT course.cid,cname,COUNT(stuid)
FROM testsdb.sc
RIGHT JOIN testdb.course
ON course.cid = sc.cid
GROUP BY course.cid
ORDER BY COUNT(stuid) DESC;
#按选课人数进行降序排列

注：在查询语句中，我们学了WHERE语句，GROUP BY 分组语句，ORDER BY语句。三者的执行顺序：WHERE语句（用于指定查询，联合查询）GROUP BY（搭配聚合函数），ORDER BY(对查询结果排序)，ORDER BY 子句必须出现在其他子句之后。

小总结：

本节重点：聚合函数指定显示字段别名

需求一参考代码：

#查询每个学生的总成绩，显示学号、姓名、总成绩，列名以中文显示。
SELECT sc.stuid AS 学号,stuname AS 姓名,SUM(score) AS 总成绩
FROM testdb.sc
JOIN testdb.stu
ON stu.stuid = sc.stuid
GROUP BY sc.stuid;

需求二参考代码：

#查询每门课程的选课人数，显示课程编号、课程名称、选课人数，没有学生选的课程也要显示，按选课人数降序排列。（提示：选课人数需要计算）
SELECT course.cid,cname,COUNT(stuid)
FROM testsdb.sc
RIGHT JOIN testdb.course
ON course.cid = sc.cid
GROUP BY course.cid
ORDER BY COUNT(stuid) DESC;

这次的左右连接的实践，相信你又对其应用的了解更上一层楼，四张表的应用——从创建表、到数据的增删查改，终于...OVER了（🍵）但是，对表的细节学习，我们不会停下，毕竟：

学无止境也，然则问可少耶