表连接和分组查询

时间:2022-12-26 09:52:40

分组查询:分组查询就是按某一列分组,相同的列合并,得到结果可能他少于总记录

使用group by
分组查询:按什么分(年级、姓氏、地址、年龄)年级

分组查询语法:
Select * from <表名>
Where 条件
Group by 列名

Group by后的列名必须在*里面出现

Demo:
Select GradeId
From Student
Group by GradeId

--统计每学期男女同学的人数 该怎么办
select GradeId as 编号,COUNT(Sex) as 总和
from Student
where Sex = '男'
group by GradeId

select COUNT(*) as 人数,GradeId as 年级,Sex as 性别
from Student
group by Sex,GradeId

Group by 分组
Order by排序(默认asc)desc
倒须
select COUNT(*) as 人数,GradeId as 年级,Sex as 性别
from Student
group by Sex,GradeId
order by GradeId desc

 

 

 


需求3
如何获取人数超过人的年级
select COUNT(*) as 人数,GradeId as 年级
from Student
group by GradeId
having COUNT(*) > 23
分组筛选语句Hhaving
Having 子句对分组后的数据进行筛选,将“组”看做“列”来限定条件
语法:
Select * from <表名>
Where 条件
Group by 列名
Having 函数
Having用法与where 用法类似,有三点不同
Having只能用在Group by(分组统计用语句)
Where 是用于在初始表中筛选查询,having用在where和group by结果果你中查询
Having可以使用聚合函数,where不能

Where 和Having对比
Where子句
·····用来筛选from子句中指定的操作所产生的行
Gorup by子句
·····用来分组where子句的输出
Having子句
·····用来从分组的结果中筛选行
顺序:where Group by Having
Where :子句从数据源中去掉不符合搜索的数据
Group by:子句搜索数据行到各个组中
Having:子句去掉不符合搜索条件的各个数据行

 

如何从两张表中的到所需的数据
常用的多表连接查询
内连接通常使用“=”“<>”比较运算符来判断两个列的数值是否相等
使用inner join…on关键字或where子句来查询
内连接(inner join)
语法:select * select
From <表名> 等价 from <表名1,2,n>
Inner join 表1 =====》 where 条件
inner join 表n
On 连接条件

select Student.StudentName as 姓名,Grade.GradeName as 年级
from Student,Grade
where Student.GradeId = Grade.GradeId

select stu.StudentName as 姓名,g.GradeName as 年级
from Student stu
inner join Grade g
on stu.GradeId = g.GradeId

--三个表Student、Subject、Result
select s.StudentName as 姓名,r.StudentResult as 成绩,j.SubjectName as 科目
from Student s
inner join Result r on s.StudentNo = r.StudentNo
inner join Subject j on j.SubjectId = r.SubjectId

--where实现三个表的连查
select s.StudentName as 姓名,r.StudentResult as 成绩,j.SubjectName as 科目
from Student s,Result r,Subject j
where(s.StudentNo = r.StudentNo) and (r.SubjectId = j.SubjectId)

外链接
左外连接(left join)
右外链接(right join)