连接查询和分组查询

一、分组查询：用于统计时经常使用分组查询

1、group by分组

01.例如查询每个年级的总人数

--一旦通过group by分组，那么我们只能获取组相关的信息，而不能获取组中成员的信息

--在select后只能跟聚合函数和group by后面的列，其他列不允许

select gradeid as 年级编号,COUNT(studentno) as 总人数
from student
group by gradeid

聚合函数：

sum()--count()--max() min()--avg()

2、多列分组

01.每个年级的男生总人数，，以及女生总人数

select gradeid as  年级编号,gender as 性别,COUNT(studentno) as 总人数
from student
group by GradeId,Gender
order by GradeId,Gender

3、Having的使用

01.如何查询每门课程的平均分和执行顺序

select SubjectId as 课程编号,AVG(studentresult) as 平均分  --05.投影结果
from Result                          --01。定位到表
where SubjectId<3  --，              --02.分组前第一道过滤
group by SubjectId                   --03.分组
having COUNT(studentno)>3            --04.分组后第二道过滤
order by 课程编号 desc               --06.最后排序

02获取总人数超过3人的年级编号及人数

select gradeid as 年级编号,COUNT(studentno) as 总人数
from student
group by GradeId
having COUNT(studentno)>3

注意：

having是对分组后的数据进行第二次筛选或者过滤，也就是说没有group by就没having

where之后不能有聚合函数

二、连接查询

 

连接分三种：内连接，外连接，交叉连接

1、内联结

--A和B两张表，A和B必须有公共字段

--输出每个学员的姓名和成绩

--实现方式二：where

select studentname as 姓名,studentresult as 成绩,subjectname as 科目,Gradename  as 年级
from student,Result,Subject,Grade
where student.StudentNo=Result.StudentNo
and Result.SubjectId=Subject.SubjectId
and student.GradeId=Grade.GradeId

 

--实现方式一：inner join on

select studentname as 姓名,studentresult as 成绩,subjectname as 科目
from Result inner join student
on student.StudentNo=Result.StudentNo
inner join Subject
on Result.SubjectId=Subject.SubjectId

2、外联结

--外连接特点

--对于左外连接：左边的表叫主表，查询结果会显示左表和右表中公共字段相匹配的

--结果(内连接)外，还会将左表中在右表中找不到匹配项的记录用null值填充

--检索所有学员的成绩，如果某个学员没有参加考试，课程及成绩用NULL值代替

select studentName,studentresult,subjectname
from student inner join result 
on student.studentNo=result.studentNo
inner join subject 
on result.subjectid=subject.subjectid
where subjectname='走进Java编程世界'