连接查询和子查询的区别:
① 连接查询可以查询任意一个的表的字段;代价更高
② 子查询只能查询一个表的字段;代价更低
-- 查询图像2001班的学生信息 -- 连接查询 select * from StudentInfo a join ClassInfo b on a.ClassInfoID = b.ClassInfoID where ClassInfoName = '图像2001' -- 子查询 select * from StudentInfo where ClassInfoID = ( select CalssInfoID from dbo.ClassInfo where ClassInfoName = '图像2001' )
-- 查询比所有课程类别ID为1的课程总学时都要高的课程 select CourseInfoName,CourseTypeID from dbo.CourseInfo where CourseInfoHotHrs > all ( select CourseInfoHotHrs from dbo.CourseInfo where CourseTypeID = 1 ) -- 查询所有已获‘副教授’职称的女教师的姓名、性别、学历、专业和职称 select * from ( select TeachInfoName,TeachInfoSex,TeachInfoKnowl,TeachInfoSpec,TeachInfoTitle from dbo.TeachInfo where TeachInfoTitle = '副教授' ) as t where t.TeachInfoSex = '女'
-- 查询开设了课程ID为7的课程的教师的相关信息 -- 首先查询课程ID为7的课程有哪些教师授课 select TeachInfoID from dbo.TeachCourse where CourseInfoID = 7 -- 其次查询上一个步骤里相关老师的信息 select TeachInfoID,TeachInfoName,TeachInfoSex,TeachInfoKnowl,TeachInfoSpec,TeachInfoTitle from dbo.TeachInfo where TeachInfoID in ( -- in 做范围判断,= 做单值判断 select TeachInfoID from dbo.TeachCourse where CourseInfoID = 7 ) -- 查询选修了7号课程的学生学号、姓名和电话 select StdInfoNum,StdInfoName,StdInfoTel from dbo.StudentInfo where exists ( -- 查询选了课的学生学号 select * from dbo.StudentCourse where StdInfoID = dbo.StudentInfo.StdInfoID and exists ( -- 先查询教授7号课程的教师 select * from dbo.TeachCourse where CourseInfoID = 7 -- 并且教师课程号等于学生选课表里的教师课程号,表示:有教师教授的课才选,剔除没有教师教授的课 and TeachCourseID = dbo.StudentCourse.TeachCourseID ) ) -- 查询与“刘立”同班同学的信息 select * from dbo.StudentInfo where ClassInfoID = ( select ClassInfoID from dbo.StudentInfo where StdInfoName = '刘立' ) -- 查询课程考试不及格的学生的姓名、性别 select StdInfoName,StdInfoSex from dbo.StudentInfo where StdInfoID in ( select StdInfoID from dbo.StudentCourse where StudCourseUsuGrd < 60 ) -- 查询课程考试不及格的学生的姓名和课程名 select 姓名 = ( select StdInfoName from dbo.StudentInfo where StdInfoID = a.StdInfoID -- 限定有课程的学生 ), 课程名 = ( select CourseInfoID from dbo.TeachCourse where CourseInfoID in ( select CourseInfoID from dbo.TeachCourse where TeachCourseID = a.TeachCourseID ) ) from dbo.StudentCourse a where StudCourseUsuGrd < 60 -- 用连接查询 select StdInfoName,CourseInfoName,StudCourseUsuGrd from dbo.CourseInfo a join dbo.TeachCourse b on a.CourseInfoID = b.CourseInfoID join dbo.StudentCourse c on b.TeachCourseID = c.TeachCourseID join dbo.StudentInfo d on c.StdInfoID = d.StdInfoID where StudCourseUsuGrd < 60 -- 查询网页设计课程不及格的学生姓名、性别和联系电话 select StdInfoName,StdInfoSex,StdInfoTel from dbo.StudentInfo where StdInfoID in ( select StdInfoID from dbo.StudentCourse where StudCourseID in ( select TeachCourseID from dbo.TeachCourse where CourseInfoID = ( select CourseInfoID from dbo.CourseInfo where CourseInfoName = '网页设计' ) ) )
主文章:备考计算机三级数据库——SQL 案例