SQL 基础（六）多关系连接查询

发表于 分类于 阅读次数： 本文字数： 4.5k 阅读时长 ≈ 4 分钟

多关系表连接查询

连接查询：一个查询需要对多张表操作，查询结果称表之间的连接；连接关系通过字段值体现，称为连接字段

当我们查询的数据、字段值分布在不同的表中时，这种情况下需要使用多关系表的连接查询

连接类型：内连接（INNER JOIN）、外连接（OUTER JOIN）、交叉连接（）、自然连接（）

连接谓词：连接两个表的条件

内连接查询

关键字（INNER JOIN），功能：仅返回连接条件为真的行，有 from 和 where 字句两种方式

这里要注意，两张表连接时，同名属性需要使用前缀区分（列名唯一不需要）

实现内连接的三种方式举例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- method1
select t.tno,tn,cno
from t,tc
where (t.tno=tc.tno) and (tn='XX') -- 连接条件、查询条件

-- method2
select t.tno,tn,cno -- 前缀指名tno为t表属性
from t inner join tc -- inner join 内连接关键字
on t.tno=tc.tno -- 连接条件
where (tn='XX')

-- method3
select r1.tno,r2.tn,r1.cno -- 授课关系 教师号、教室关系 姓名、授课关系 课程号
from
(select tno,cno from tc) as r1
inner join
(select tno,tn from t where tn='XX') as r2
on r1.tno=r2.tno

以上方法中，method1 比较简单且常用 ，建议重点掌握

两张表连接

查询每位学生及选修课程信息

1.from 子句实现

1
2
3
select *
from tb_student inner join tb_score -- from 表1名称 连接类型(内连接 inner join) 表2名称
on tb_student.sno=tb_score.sno -- 同名属性需要加表名称(否则会出错,系统无法识别)

以上代码可简化为

1
2
3
select *
from tb_student sc inner join tb_score s -- 给表起别名，简化代码量
on sc.sno=s.sno

执行结果中发现存在相同列(学号)，说明为等值连接，未删去重复列

2.where 子句实现

1
2
3
select *
from tb_student s,tb_score sc
where s.sno=sc.sno

查询每名同学成绩,包含学号,课程编号,成绩,课程名称信息

1
2
3
select sno,sc.cno,cn,score
from tb_score sc, tb_course c
where sc.cno=c.cno

多张表连接

1.from 子句

1
2
3
4
select s.sno,sn,cn,score
from tb_student s join tb_score sc
on (s.sno=sc.sno) join tb_course c
on (sc.cno=c.cno)

2.where 子句

1
2
3
select s.sno,sn,cn,score
from tb_student s,tb_score sc,tb_course c
where s.sno=sc.sno and sc.sno=c.cno

外连接查询

外连接中，符合连接条件的数据返回到结果集，不符合连接条件的列会被系统用 NULL 填充，再返回结果集

*注：bit 类型无 NULL 值，会填充 0 后返回结果集中

使用主表所在的方向位置判断连接类型，例如：主表在左，即为左外连接

复习下关系运算中，连接的相关知识

那么上图两张表分别进行外、左外、右外连接后的结果为

举例：查询所有学生选课情况,包括未选课学生信息

左外连接 left join

1
2
3
-- left join
select * from tb_student s left join tb_score sc
on (s.sno=sc.sno)

右外连接 right join

1
2
3
-- right join
select * from tb_score sc right join tb_student s
on (sc.sno=s.sno)

完全外连接 full join

1
2
3
-- full join
select * from tb_student s full join tb_score sc
on (s.sno=sc.sno)

交叉连接查询

又称 “笛卡尔乘积” 连接，实际应用中很少使用

考虑到计算机性能，执行效率也不大相同

这里主要知道关键字为 CROSS JOIN，结果集行数为两张表行数乘积，列数为两表列数总和，然后参考下下方示例即可

1
2
3
-- 对学生表和成绩表进行 交叉查询
select *
from tb_student cross join tb_score

自连接查询

如果我们要查询的结果集中，所包含的信息均在同一张信息表中，这样的查询方式称为自连接查询

示例：查询工资表中，所有比 XXX 工资高的同事姓名、工资和 XXX 的工资

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- method1 
select x.tn,x.sal as sal_a,y.sal as sal_b
from t as x,t as y -- self connect
where x.sal > y.sal and y.tn='XXX'

-- method2 inner join1
select x.tn.x.sal,y.sal
from t as x,t as y
where (x.sal>y.sal) and y.tn='XXX' -- where ... and

-- method3 inner join2
select x.tn,x.sal,y.sal
from t as x inner join t as y
on x.sal > y.sal and y.tn='XXX' -- inner join ... on

-- method4 inner join3
select r1.tn,r1.sal,r2.sal
from
(select tn,sal from t) as r1
inner join
(select tn,sal from t where tn='XXX') as r2
on r1.sal > r2.sal -- inner join ... on

子查询

又称嵌套查询，形式是在 WHERE 中再次包含 SELECT - FROM - WHERE 的查询

程序从内向外执行 SQL 语句，外部查询称为父查询，父查询需要接收子查询（嵌套查询）的结果

普通子查询

普通子查询仅执行一次

返回一个值

该例子解释父级查询需要子查询结果的概念

1
2
3
4
5
select tno,tn
from t
where prof=(select prof
			from t
			where tn='XXX')

示例中，prof 的值由子查询查出结果后返回给父查询做结果，上述语句等价为

1
2
3
select tno,tn
from t
where prof=‘子查询 prof 值’

返回一组值

比较运算符仅适用于查询所需返回值为单个值得情况，当返回结果为集合时不再使用，可使用如下方式

ANY

ANY：任何一个

示例：查询讲授课程号为 c5 的教师姓名

1
2
3
4
5
select tn -- 姓名
from t 
where (tno = ANY (select tno -- 教师号
				  from tc
				  where cno='c5')) -- 课程号

首先执行子查询，找到讲授 c5 课程的教师号，父级查询根据教师号再查询教师姓名

意思是，tno 是子查询结果集中的任（ANY）一个

IN

和 ANY 一样的例子，我们也可以使用 IN 实现

1
2
3
4
5
select tn -- 姓名
from t 
where (tno IN (select tno -- 教师号
				  from tc
				  where cno='c5')) -- 课程号

这里的意思是，tno 在（IN）子查询结果集中

ALL

ALL：全部

示例：查询其他系中比计算机系所有教师工资都高的教师姓名和工资

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 使用 ALL
select tn,sal -- 姓名、工资
from t
where (sal > ALL (select sal
			      from t
			      where dept='计算机'))
	  and (dept <> '计算机') -- 父级条件限制 “其他系” 也即为：不属于 ”计算机系”

-- 使用聚合函数 MAX()
select tn,sal 
from t
where (sal > (select MAX(sal)
			  from t
			  where dept='计算机'))
	  and (dept <> '计算机')

相关子查询

由上面的内容我们知道，子查询程序执行顺序是由内到外，也就是说父级需要子级的消息返回

但是，我们同样会遇到子查询需要父查询相关信息的情况，这样的情况我们称之为相关子查询

示例：查询不讲授课程号为 c5 的课程的教师姓名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- method1 <> ALL
select distinct tn -- 姓名去重
from t
where ('c5' <> ALL (select cno -- 课程号 
					from tc
					where tno=t.tno)) -- tno 教师号

-- method2 NOT IN
select distinct tn
from t
where ('c5' NOT IN (select cno 
					from tc
					where tno=t.tno)) 

-- method3 NOT EXISTS
select tn
from t
where NOT EXISTS (select *
				  from tc
				  where tno=t.tno and cno='c5')

该例中，子查询判断课程号 cno 时，需要数据表 t 中教师号 tno 信息，为相关子查询

集合运算查询

在各个子查询对应数据条目和数据类型一致的条件下，可以使用 UNION 关键字将不同的查询得到的数据组合起来

且 UNION 会自动删除重复数据行

使用方法比较简单，给出一个例题供参考

1
2
3
4
5
6
7
8
9
select sno,sum(score)
from tb_a
where(sno='001')
group by sno
UNION
select sno,sum(score)
from tb_b
where(sno='002')
group by sno

上面的 SQL 语句实现：将从 tb_a 中查询出学号为 001 同学的学号和总成绩信息和从 tb_b 中查询出学号为 002 同学的学号和总成绩信息合并为一个结果集

存储查询结果

此处“存储”的含义是指将 A 表中查询的数据结果集存储到其他表，B 表中

我们使用 SQL 语句查询到的结果，仅临时导出让用户（我们）看到，并未真正影响（存储）到对应数据库中，那如何实现查询结果的存储呢？

语法格式为：

1
SELECT ... INTO ...

具体实现如下

1
2
3
4
selelct sno,sum(score) -- 学号,总成绩
into tb_b 
from tb_a
group by sno -- 按照学号分组

上面的 SQL 语句实现从 tb_a 中查询出学号和总成绩信息并存放到 tb_b 表中