CH06_联接查询和分组查询

本章目标

  • 掌握GROUP BY分组查询
  • 掌握多表联接查询

数据分组查询

###GROUP BY关键字

  • GROUP BY是SQL中的一个子句，用于根据一个或多个列对结果集进行分组。它通常与聚合函数（如COUNT、SUM、AVG等）一起使用，以便对每个组进行计算。通过GROUP BY子句，可以将具有相似值的行分组在一起，并对每个组执行聚合计算，从而对数据进行汇总和分析。

  • 使用GROUP BY子句时，需要指定要分组的列。可以按照一个或多个列进行分组，以便将数据分成不同的组。在每个组内，可以对列进行聚合计算，以获得每个组的汇总结果。

  • GROUP BY子句在数据分析和报表生成中非常有用，因为它允许用户对数据进行分组和汇总，以便更好地理解数据和提取有价值的信息。

GROUP BY语法如下：

1
2
3
SELECT column1, column2, ..., aggregate_function(column)  
FROM table_name  
GROUP BY column1, column2, ...;

案例1：根据成绩表的科目分组，计算学员每科目的平均分

1
2
3
SELECT SubjectId as 学科,AVG(StudentResult) as 平均分  
FROM Result
GROUP BY SubjectId

1705631726753

案例2：根据成绩表的科目分组，计算学员每科目的平均分，并根据平均分逆序排序

1
2
3
4
SELECT SubjectId as 学科,AVG(StudentResult) as 平均分
FROM Result
GROUP BY SubjectId
ORDER BY AVG(StudentResult) DESC

1705633617015

多表联接查询

​多表联接查询是数据库查询的一种，它允许用户在查询中组合来自两个或更多表的数据。联接查询基于两个或多个表之间的某些列的相似性，将它们的数据组合在一起。

以下是一些常见的多表联接查询的示例：

  • 内联接（INNER JOIN）：返回两个表中具有匹配值的行。
1
2
3
4
SELECT *  
FROM table1  
INNER JOIN table2  
ON table1.column_name = table2.column_name;

案例1：查询学生表中学生学号、姓名、性别和成绩表中成绩

1
2
3
SELECT a.StudentNo,a.StudentName,a.Sex,b.StudentResult,b.ExamDate
FROM dbo.Student AS a
INNER JOIN dbo.Result AS b ON a.StudentNo = b.StudentNo

1705634200689

方法2：使用WHERE关键字模拟INNER JOIN（查询结果与上面一样）

1
2
3
SELECT a.StudentNo,a.StudentName,a.Sex,b.StudentResult,b.ExamDate
FROM Student AS a ,Result AS b
WHERE a.StudentNo = b.StudentNo

1705634200689

  • 左联接（LEFT JOIN）：返回左表中的所有行和右表中匹配的行。如果右表中没有匹配的行，则结果为NULL。
1
2
3
4
SELECT *  
FROM table1  
LEFT JOIN table2  
ON table1.column_name = table2.column_name;

案例2：查询学员信息考试情况

1
2
3
SELECT a.StudentNo,a.StudentName,a.Sex,b.StudentResult,b.ExamDate
FROM dbo.Student AS a
LEFT JOIN dbo.Result AS b ON a.StudentNo = b.StudentNo;

1705634763250

上述查询将返回一个结果集，其中包含学生表中的所有学生记录，以及与之相关的成绩表中的考试信息。如果某个学生没有参加考试记录，则对应的考试信息将显示为NULL 。

通过左连接，可以获取到左表中的所有记录，并根据连接条件将右表中的相关记录与之关联起来。这样可以方便地查询需要的信息，并且避免了因缺失数据而导致的问题。

  • 右联接（RIGHT JOIN）：返回右表中的所有行和左表中匹配的行。如果左表中没有匹配的行，则结果为NULL。
1
2
3
4
SELECT *  
FROM table1  
RIGHT JOIN table2  
ON table1.column_name = table2.column_name;

案例3：查询参加考试的学员信息考试情况

1
2
3
SELECT a.StudentNo,a.StudentName,a.Sex,b.StudentResult,b.ExamDate
FROM dbo.Student AS a
RIGHT JOIN dbo.Result AS b ON a.StudentNo = b.StudentNo;

1705634920323

  • 全外联接（FULL OUTER JOIN）
    • 返回左表和右表中的所有行。如果某一边没有匹配的行，则结果为NULL。
    • 是一种关系型数据库中的连接操作，它能够将两个表中的所有记录进行合并，并保留两个表中的全部数据。全外连接基于关系代数的笛卡尔积操作和连接条件进行。
  • 全外联接缺点
    • 结果集可能包含大量空值
    • 可能存在重复的行
    • 性能问题
    • 可能导致数据的不一致性
1
2
3
4
SELECT *  
FROM table1  
FULL OUTER JOIN table2  
ON table1.column_name = table2.column_name;

案例4：查询学生表和成绩表全部的信息记录

1
2
3
4
SELECT a.StudentNo ,StudentName,Sex,StudentResult,ExamDate
FROM Student AS a	
FULL OUTER JOIN Result AS b
ON a.StudentNo = b.StudentNo;

1705649589470

课后作业

1.查询前端中级班所学课程信息

2.查询考试成绩在80分以上所有的学员信息

3.查询各年纪名总课时升序排序，按年级分组

4.查询考试记录等于大于两次的所有学员信息

5.查询金蝶学员所有考试科目成绩信息

6.查询多个年纪班考试平均分，平均成绩分数低于70分的年纪信息

7.查询各年纪班的人数

8.查询各年级班学习Java相关课程数

参考答案