【SQL server数据库基础】连接查询与并、交、差运算

1. 内连接

• 内连接：使用比较运算符比较两个表共有的字段列，返回满足条件的记录行 。[Inner] Join

select TeachInfoName,TeachInfoSex,TeachInfoTitle,TeachTypeName,TeachInfo.TeachTypeID 
-- 查询两个表中的相同字段，需要指定引用其中一个表
from dbo.TeachInfo join dbo.TeachType
on TeachInfo.TeachTypeID = TeachType.TeachTypeID

select TeachInfoName,TeachInfoSex,TeachInfoTitle,TeachTypeName,a.TeachTypeID
from dbo.TeachInfo a join dbo.TeachType b  -- 取别名简化代码
on a.TeachTypeID = b.TeachTypeID

2. 外连接

左外连接：包括左表中不满足条件的行。left join

右外连接：包括右表中不满足条件的行。right join

完全外连接：包括左表和右表中不满足条件的行。full join

-- 查看所有教师的授课情况，不管教师有没有授课都要包括其情况（左外连接）
select TeachInfoNum,TeachInfoName,TeachInfoSex,TeachInfoTitle,CourseInfoID
from dbo.TeachInfo a left join dbo.TeachCourse b  
-- 查询所有教师，就把教师信息表放左边，以教师表为参考，查询授课情况,左表中的字段即使有 Null 值也会显示
on a.TeachInfoID = b.TeachInfoID

-- 查看所有教师授课课程的选修情况（右外连接）
select StudCourseID,StdInfoID,TeachInfoID,CourseInfoID
from dbo.StudentCourse a right join dbo.TeachCourse b
on a.TeachCourseID = b.TeachCourseID

-- 使用完全外连接查看每位教师的授课情况
select TeachInfoNum,TeachInfoName,TeachInfoSex,TeachInfoTitle,TeachCourseID,CourseInfoID
from dbo.TeachCourse a full join dbo.TeachInfo b
on a.TeachInfoID = b.TeachInfoID

3. 其他连接查询

-- 查询所有女生的所在班级信息
select StdInfoName,StdInfoSex,ClassInfoName
from dbo.ClassInfo a join dbo.StudentInfo b
on a.ClassInfoID = b.ClassInfoID
where StdInfoSex = '女'

-- 查询学生所属的班级信息和专业信息
select StdInfoName,SpilInfoName,ClassInfoName
from dbo.StudentInfo a join dbo.ClassInfo b
on a.ClassInfoID = b.ClassInfoID join dbo.SpecilInfo c
on b.SpilInfoID = c.SpilInfoID

-- 查询选课程最多的学生由高到低排序
select StdInfoName,COUNT(b.StdInfo) 课程门数
from dbo.StudentInfo a join dbo.StudentCourse b
on a.StdInfoID = b.StdInfoID
group by StdInfoName  -- 查询包括字段和聚合函数，需要加group by
order by 课程门数 desc

-- 查询计算机工程系的教师授课课程的选修情况
select TeachInfoName,DepInfoName,COUNT(StdInfoID)
from dbo.DepInfo a join dbo.TeachInfo b
on a.DepInfoID = b.DepInfoID join dbo.TeachCourse c
on b.TeachInfoID = c.TeachInfoID join dbo.StudentCourse d
on c.TeachCourseID = d.TeachCourseID
where DepInfoName = '计算机工程系'
group by TeachInfoName,DepInfoName

4. 查询结果的并、交、差运算

并运算运算符 union ：将两个或多个查询语句的结果集合并为一个结果集

交运算运算符 intersect ：返回两个查询结果集中各个列的值均相同的记录

差运算运算符 except ：返回再第一个集合中有但第二个集合中没有的数据

-- 利用并运算查询学生和老师的姓名
select TeachInfoName from dbo.TeachInfo  -- 查询结果字段名以第一个查询的字段名为准
union
select StdInfoName from dbo.StudentInfo

-- 利用交运算查询彭欢老师和朱志奇老师教授的同一门课程。列出课程名称
select CourseInfoName
from dbo.CourseInfo a join dbo.TeachCourse b
on a.CourseInfoId = b.CourseInfoId join dbo.TeachInfo c
on b.TeachInfoID = c.TeachInfoID
where TeachInfoName = '彭欢'
intersect
select CourseInfoName
from dbo.CourseInfo a join dbo.TeachCourse b
on a.CourseInfoId = b.CourseInfoId join dbo.TeachInfo c
on b.TeachInfoID = c.TeachInfoID
where TeachInfoName = '朱志奇'

-- 利用差运算查询彭欢老师授课而没有由朱志奇老师授课的课程。列出课程名称
select CourseInfoName
from dbo.CourseInfo a join dbo.TeachCourse b
on a.CourseInfoId = b.CourseInfoId join dbo.TeachInfo c
on b.TeachInfoID = c.TeachInfoID
where TeachInfoName = '彭欢'  
except
select CourseInfoName
from dbo.CourseInfo a join dbo.TeachCourse b
on a.CourseInfoId = b.CourseInfoId join dbo.TeachInfo c
on b.TeachInfoID = c.TeachInfoID
where TeachInfoName = '朱志奇'

主文章：备考计算机三级数据库——SQL 案例