SQL server数据库基础——子查询

• • 1. 子查询用作派生表
  • 2. 子查询用作表达式

连接查询和子查询的区别：

​ ① 连接查询可以查询任意一个的表的字段；代价更高

​ ② 子查询只能查询一个表的字段；代价更低

-- 查询图像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'
)

1. 子查询用作派生表

-- 查询比所有课程类别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 = '女'

2. 子查询用作表达式

-- 查询开设了课程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 案例