第三节 多表查询
3.1 内连接查询
实际开发中往往需要针对两张甚至更多张数据表进行操作,而这多张表之间需要使用主键和外键关联在一起,然后使用连接查询来查询多张表中满足要求的数据记录。
一条SQL语句查询多个表,得到一个结果,包含多个表的数据。效率高。在SQL99中,连接查询需要使用join关键字实现。
提供了多种连接查询的类型: cross natural using on
交叉连接(CROSS JOIN)是对两个或者多个表进行笛卡儿积操作,所谓笛卡儿积就是关系代数里的一个概念,表示两个表中的每一行数据任意组合的结果。比如:有两个表,左表有m条数据记录,x个字段,右表有n条数据记录,y个字段,则执行交叉连接后将返回m*n条数据记录,x+y个字段。笛卡儿积示意图如图所示。
笛卡尔积不管是否匹配,都连接。没有实际意义,有理论意义 笛卡尔积便于理解连接查询的原理
【示例7】多表连接查询
-- SQL99多表连接查询
-- 查询员工的编号、姓名和部门编号
select * from emp
select empno,ename,deptno from emp
-- 查询员工的编号、姓名和部门编号、部门的名称
-- 交叉连接
select * from dept -- 4
select * from emp -- 14
select *
from emp
cross join dept -- 56 = 4*14
select *
from dept
cross join emp -- 表有左右之分 左连接 右连接
-- 自然连接
-- 会自动的按照所有的同名列进行匹配,并且同名列只显示一次
-- 优点:简单 natural
select *
from emp
natural join dept
select empno,ename,deptno,dname
from emp
natural join dept
-- 缺点:没有指定各个字段属于哪个表,执行时需要进行判断,效率低了
-- 解决:给各个字段指定表的名字.
select emp.empno,emp.ename,deptno,dept.dname -- 自然连接中同名列不能使用表名前缀
from emp
natural join dept
-- 缺点:表名如果长,字段如果多,SQL语句稍显复杂
-- 解决:不使用表名,使用别名
select e.empno,e.ename,deptno,d.dname
from emp e
natural join dept d
-- Using子句
-- 自然连接的缺点:会自动的按照“所有的”同名列进行匹配,如果希望按照某一个同名列进行匹配,自然连接无能为力
-- 解决:Using子句
select *
from emp e
join dept d
using(deptno) -- e.deptno = d.deptno
select e.empno,e.ename,deptno,d.dname
from emp e
join dept d
using(deptno)
-- on子句
-- natural、using的缺点:都必须有同名列 ,前提
-- 连接查询要求两个表必须有关联列(比如emp的deptno和dept的deptno),但是不要同名
-- 如果有关联列,但是不同名,那怎么办
select empno,ename,d.deptno,d.dname -- 关联列必须使用前缀
from emp e
join dept d
on (e.deptno = d.deptno)
-- 查询员工的编号、姓名和部门编号、部门的名称,要求薪水大于2000
select empno,ename,e.sal,d.deptno,d.dname -- 关联列必须使用前缀
from emp e
join dept d
on (e.deptno = d.deptno)
where sal > 2000
总结
开发中尽量使用on子句 ,不管是否有同名列,都可以使用;虽然稍显复杂,但是可读性高
SQL99连接查询的优点:连接条件和筛选条件是分开
连接条件:on (e.deptno = d.deptno) using(deptno) natural cross
筛选条件 where sal > 2000
SQL92中 连接条件和筛选条件是没有分开的
3.2 外连接查询
内连接:只显示匹配的数据
外连接:显示匹配的数据,还显示(部分或者全部)不匹配的数据,那就是显示(全部!!??)的数据左外连接 left [outer] join 显示匹配的数据和左边表的不匹配的数据
右外连接 right [outer] join 显示匹配的数据和右边表的不匹配的数据
全外连接 full [outer] join 显示匹配的数据,和左、右边表的不匹配的数据。MySQL不支持全外连接,所以只能采取关键字UNION来联合左、右连接的方法
【示例8】外连接查询
-- SQL99连接查询:内连接 外连接
select * from dept; -- 4
select * from emp; -- 14 40号部门没有员工
update emp set deptno = null where empno = 7788; -- scott员工没有部门
-- 13条数据 ,缺少了没有部门的员工scott,缺少了没有员工的部门40;只显示匹配的数据
select *
from emp e
join dept d
on(e.deptno = d.deptno)
-- 左外连接 left [outer] join
select *
from emp e
left join dept d
on(e.deptno = d.deptno)
-- 右外连接 right [outer] join
select *
from emp e
right join dept d
on(e.deptno = d.deptno)
-- 全外连接
select *
from emp e
left join dept d
on(e.deptno = d.deptno)
union
select *
from emp e
right join dept d
on(e.deptno = d.deptno)
本节作业
1. 练习交叉连接、自然连接、using子句、on子句多表连接查询
2. 练习左外连接、右外连接
本知乎号每日更新2篇java基础视频贴,以及2-4篇java基础技术文章,有兴趣的同学可以关注学习。