第十一章笔记

分组查询和链接查询

1:分组查询语句:

语法: 实例:select count(*) as 人数

select .......... from Student

from<表名> group by gradeID

where

group by

注意:select 列表中只能包含:

1:被分组的列

2:为每个分组返回一个值的表达式.如聚合函数

use MySchool




--查询年级所拥有的人数
select GradeId as 年级,COUNT(Phone) as 人数 from Student
group by GradeId





--根据性别进行分组
select Sex as 性别,COUNT(*) as 人数 from Student
group by Sex




--查询每门课程的平均分
select SubjectId as 课程编号,AVG(StudentResult) as 平均分
from Result
group by SubjectId




--按地区分类，查询地区的人数
select COUNT(*) as 人数,Address as 地址 from Student
group by Address

--查询每门课程的平均分，并且按照分数由低到高的顺序排列显示
select SubjectId as 课程编号,AVG(StudentResult) as 平均分 from Result
group by SubjectId
order by AVG(StudentResult) desc




--统计每学期男女同学的人数
select COUNT(*) as 人数,GradeId as 年级,Sex as 性别 from Student
Group by GradeId,Sex
order by GradeId
--性别：男和女	年级：1，2，3

　二:多列分组:

--统计每学期男女同学的人数
select COUNT(*) as 人数,GradeId as 年级,Sex as 性别 from Student
Group by GradeId,Sex
order by GradeId
--性别：男和女	年级：1，2，3

　　分组筛选语句:

语法:

select .......from<表名>

where.......

group by......

having ........

--如何获得总人数超过2人的年级
select COUNT(*) as 人数,GradeId as 年级,Sex as 性别 from Student
Group by GradeId,Sex
having COUNT(*)>=2
order by GradeId



--出生日期大于1990年的学生,获得总人数超过2人的年级
select COUNT(*) as 人数,GradeId as 年级,Sex as 性别 from Student
where BornDate >'1990/01/01'
Group by GradeId,Sex
having COUNT(*)>=2
order by GradeId

　　同属从两个表中获取数据

--同时从这两个表中取得数据
select Student.StudentName as 姓名,Result.StudentResult as 成绩,
Result.SubjectId AS 科目编号 from Student,Result
where Student.StudentNo=Result.StudentNo

　where局:

用来筛选from子句中的指定的操作所产生的行

group by:

用来分组where子句中的输出

having

用来从分组中的结果筛选行

内连接和外连接:

内连接的语法:

select.......

from 表1

inner join表2

左外连接:

主表的student 中的数据逐条匹配表Result中的数据

1:匹配返回到结果集

2:无匹配ＮＵＬＬ值返回到结果集

-内链接
select S.StudentName as 姓名,R.StudentResult as 成绩,
R.SubjectId AS 科目编号 from Result as R
inner join Student as S on(S.StudentNo=R.StudentNo)


select S.StudentName as 姓名,R.StudentResult as 成绩,
SU.SubjectName AS 科目名称 from Result as R
inner join Student as S on(S.StudentNo=R.StudentNo)
inner join Subject as SU on(R.SubjectId=SU.SubjectId)

select Student.StudentName as 姓名,Result.StudentResult as 成绩,
Subject.SubjectName AS 科目名称 from Student,Result,Subject
where Student.StudentNo=Result.StudentNo and Result.SubjectId=Subject.SubjectId

--左外连接

select S.StudentName,R.SubjectId,R.StudentResult 
From Student AS S
LEFT JOIN Result as R
on(S.StudentNo=R.StudentNo)

--右外连接
select S.StudentName,R.SubjectId,R.StudentResult 
From Result AS R
RIGHT JOIN Student as S
on(S.StudentNo=R.StudentNo)

第十一章笔记

推荐阅读