实验4 SQL数据统计处理

1、在学生选课表中，统计学生平均分大于70的成绩记录。包括学生学号、总分、平均分、课程门数、课程最高分、最低分字段。 2、在学生选课表和学生表中，统计学生平均分在60~70和90~100的学生成绩记录，包括学生学号、学生姓名、总分、平均分、最高分、最低分、所修课程门数字段。 3、在学生选课表和学生表中，查询性别为女并且平均分大于80的学生基本信息。 4、统计平均分各分数段人数。 5.在学生选课表、学生表中，查询学生成绩重修（成绩<60）门数大于10门的学生基本信息（查询结果包括学号、姓名、性别字段）。 ### 实验4 SQL数据统计处理知识点详解 #### 1. 统计学生平均分大于70的成绩记录 在本实验的第一部分中，我们关注的是如何筛选出那些平均分超过70分的学生及其相关信息。这里使用到了`GROUP BY`、`SUM`、`AVG`、`COUNT`、`MAX`和`MIN`等聚合函数来计算每个学生的总分、平均分、选课数量以及分数的最大最小值，并通过`HAVING`子句来限定只返回那些平均分大于70的学生信息。 **知识点解析**: - **`GROUP BY`**: 用于对数据进行分组，便于后续的聚合操作。在这个例子中，我们需要按照学生学号进行分组，以便计算每位学生的各项指标。 - **`SUM`**: 计算总和，这里用来计算每位学生的总分。 - **`AVG`**: 计算平均值，用于计算每位学生的平均成绩。 - **`COUNT`**: 计数器，计算每组内的行数，在这里用以得出每位学生选课的数量。 - **`MAX`和`MIN`**: 分别计算最大值和最小值，这里用来找出每位学生的最高分和最低分。 - **`HAVING`**: 类似于`WHERE`子句，但应用于`GROUP BY`之后的结果集上，用来过滤掉不满足条件的分组。在这里，它确保了只返回平均分大于70分的学生。 **示例代码**: ```sql SELECT Sno as 学号, SUM(grade) AS 总分, AVG(grade) AS 平均分, COUNT(Cno) AS 课程门数, MAX(grade) AS 最高分, MIN(grade) AS 最低分 FROM SC GROUP BY Sno HAVING AVG(grade) > 70; ``` --- #### 2. 统计学生平均分在60~70和90~100的学生成绩记录 这部分实验要求统计那些平均分位于60到70之间或者90到100之间的学生的信息。同样地，我们利用`GROUP BY`、`SUM`、`AVG`等聚合函数，并结合`HAVING`子句来完成这一任务。此外，还需要从两个表（学生表和选课表）中获取信息，因此还涉及到表之间的连接操作。 **知识点解析**: - **表连接**: - **隐式连接**: 使用逗号`,`来连接表，这种写法要求在`WHERE`子句中明确指定连接条件。例如： ```sql SELECT ... FROM SC, Student WHERE SC.Sno = Student.Sno; ``` - **显式连接**: 使用`JOIN`关键字来连接表，这种方式更易于阅读和维护。例如： ```sql SELECT ... FROM SC JOIN Student ON SC.Sno = Student.Sno; ``` **示例代码**: ```sql SELECT Student.Sno as 学号, Sname As 姓名, SUM(grade) AS 总分, AVG(grade) AS 平均分, MAX(grade) AS 最高分, MIN(grade) AS 最低分, COUNT(Cno) AS 所修课程门数 FROM SC JOIN Student ON SC.sno = Student.sno GROUP BY Student.Sno, Student.Sname HAVING (AVG(grade) >= 60 AND AVG(grade) <= 70) OR (AVG(grade) >= 90 AND AVG(grade) <= 100); ``` --- #### 3. 查询性别为女并且平均分大于80的学生基本信息 这一部分的目标是筛选出那些性别为女性且平均分高于80分的学生的基本信息。与前两部分类似，这里仍然需要使用到`GROUP BY`和`HAVING`子句，同时还要加上一个额外的条件来筛选性别。 **知识点解析**: - **多条件筛选**: - 使用逻辑运算符`AND`和`OR`来组合多个条件。 - 在`HAVING`子句中可以同时应用多个条件。 **示例代码**: ```sql SELECT Student.Sno, Student.Sname, Student.Ssex, Student.Sage, Student.Sdept FROM Student JOIN SC ON Student.Sno = SC.Sno GROUP BY Student.Sno, Student.Sname, Student.Ssex, Student.Sage, Student.Sdept HAVING Student.Ssex = '女' AND AVG(SC.grade) > 80; ``` --- #### 4. 统计平均分各分数段人数 这部分的任务是将学生的平均分划分为不同的分数段，并统计每个分数段的人数。这里需要用到嵌套查询和`UNION ALL`来组合不同分数段的结果。 **知识点解析**: - **嵌套查询**: - 外层查询负责统计每个分数段的人数。 - 内层查询则用来过滤出符合特定分数范围的学生。 **示例代码**: ```sql -- 60分以下的人数 SELECT '60以下' AS 分数段, COUNT(*) AS 人数 FROM ( SELECT Student.Sno, AVG(grade) AS avg_score FROM Student JOIN SC ON Student.Sno = SC.Sno GROUP BY Student.Sno HAVING AVG(grade) < 60 ) AS SubQuery60 UNION ALL -- 60-80的人数 SELECT '60-80' AS 分数段, COUNT(*) AS 人数 FROM ( SELECT Student.Sno, AVG(grade) AS avg_score FROM Student JOIN SC ON Student.Sno = SC.Sno GROUP BY Student.Sno HAVING AVG(grade) BETWEEN 60 AND 80 ) AS SubQuery6079 UNION ALL -- 80-100的人数 SELECT '80-100' AS 分数段, COUNT(*) AS 人数 FROM ( SELECT Student.Sno, AVG(grade) AS avg_score FROM Student JOIN SC ON Student.Sno = SC.Sno GROUP BY Student.Sno HAVING AVG(grade) BETWEEN 80 AND 100 ) AS SubQuery80100; ``` --- #### 5. 查询学生成绩重修（成绩<60）门数大于10门的学生基本信息 最后一部分要求筛选出那些有10门以上重修课程（成绩低于60分）的学生的基本信息。这一步同样需要使用到`GROUP BY`和`HAVING`子句，但是这次的重点在于如何统计重修的课程数量。 **知识点解析**: - **子查询**: - 在这里使用了一个子查询来计算每位学生重修的课程数量。 - 主查询根据这个子查询的结果进一步筛选出符合条件的学生。 **示例代码**: ```sql SELECT Student.Sno AS 学号, Sname AS 姓名, Ssex AS 性别 FROM Student WHERE EXISTS ( SELECT * FROM SC WHERE SC.Sno = Student.Sno GROUP BY SC.Sno HAVING COUNT(*) FILTER (WHERE grade < 60) > 10 ); ``` 以上就是本次实验的所有知识点详解。这些SQL语句不仅能够帮助理解如何进行数据统计处理，同时也展示了SQL的强大功能和灵活性。