SQL 基础(六)多关系连接查询

多关系表连接查询

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

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

连接类型:内连接(INNER JOIN)、外连接(OUTER JOIN)、交叉连接()、自然连接()

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

内连接查询

关键字(INNER JOIN),功能:仅返回连接条件为真的行,有 fromwhere 字句两种方式

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

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

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 表中