Sql Server 数据库 多表连接操作

--相对应的数据库文件在下面

--多表连接查询
select Student.StudentName,Subject.SubjectName,Result.StudentResult from Student,Subject,Result
where Student.StudentNo=Result.StudentNo
and Subject.SubjectId=Result.SubjectId
order by StudentName
--inner join 链接的表必须有相同的列，主外键关系
select Student.StudentName,Subject.SubjectName,Result.StudentResult,Grade.GradeName,AVG(Result.StudentResult) as '平均分' from Student
inner join Subject
on Student.GradeId=Subject.GradeId
inner join Result
on Subject.SubjectId=Result.SubjectId
inner join Grade
on Grade.GradeId=Student.GradeId
where Grade.GradeName='s2'
group by Student.StudentName,Grade.GradeName,Subject.SubjectName,Result.StudentResult
having AVG(Result.StudentResult)>60
order by AVG(Result.StudentResult) desc
--查询年纪编号为1的年纪名称、科目名称及学时
select Grade.GradeName,Subject.SubjectName,Subject.ClassHour from Grade
inner join Subject
on Grade.GradeId=Subject.GradeId
where Grade.GradeId=1
--查询学生学号、姓名、考试科目及成绩
select Student.StudentNo,Student.StudentName,Subject.SubjectName,Result.StudentResult from Student
inner join Subject
on Student.GradeId=Subject.GradeId
inner join Result
on Result.SubjectId=Subject.SubjectId
--查询参加‘走进java编程世界’考试的学生姓名、成绩、日期
select Subject.SubjectName,Student.StudentName,Result.StudentResult,Result.ExamDate from Student
inner join Result
on Student.StudentNo=Result.StudentNo
inner join Subject
on Subject.SubjectId=Result.SubjectId
where Subject.SubjectName='走进Java编程世界'
order by Result.StudentResult desc
--左右连接
--左连接，左表和右表有数据差异，查出左表数据，右表差异部分显示为空
select distinct(Student.StudentNo),Result.SubjectId,Result.SubjectId from Student
 left join Result
 on Student.StudentNo=Result.StudentNo
 where Result.SubjectId is null
 --查询所有科目的参加考试的学生信息
 select * from Student
 left join Subject
 on Student.GradeId=Subject.GradeId
 --右连接的左边为右表连接数据
  --查询从未考试的科目信息
 select * from Result
right join Student
 on Student.StudentNo=Result.StudentNo
 where Result.StudentResult is null
 --查询所有年级对应的学生信息
 select * from Grade
 left join Student
 on Grade.GradeId=Student.GradeId