连接查询和分组查询

--查询年级所拥有的人数
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

--如何获得总人数超过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

--内链接
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)