十、多表查询
*多表查询的本质就是把多张表合并成一个临时表
1、多表数据合并方案
-
连接查询合并方案【最常见】
- 首先确保两张表之间存在【隶属关系】
- 将两张表中的数据行【沿着水平方向进行拼接】
- 最终得到一个拥有【完整隶属关系】的新数据行
-
联合查询合并方案
- 不需要两张表之间存在【隶属关系】
- 将两张表中的数据行【沿着垂直方向进行堆砌】
2、连接查询【水平拼接】
1、join
拼接的临时表行数是两个表的笛卡尔积(A×B)
内联和外联区别:
外键有空值或者不匹配时,外联依然显示左表所有数据,内联不会显示该数据
1、cross join(交叉连接)
交叉连接返回emp、dept的笛卡尔积,即emp行数 × dept行数条数据,一般在不需要任何过滤条件的情况下使用
SELECT * FROM emp,dept;
SELECT * FROM emp CROSS JOIN dept;
-- 两条语句是等效的,第一条语句是隐式的交叉连接,第二条是显示的交叉连接
2、inner join(内连接)
- 内连接也是返回emp和dept的笛卡尔积,但是在内连接后一般都会加on语句附加过滤条件,所以返回的是符合条件的笛卡尔积
- 拼接的新临时表为避免重名,列名都是“表名.列名”格式
- 为了剔除脏数据,使用【连接查询过滤方案】 on 进行过滤
SELECT empno,ename,sal,emp.deptno,dname,loc
FROM emp
INNER JOIN dept -- 运行到这里查出来的数据必然存在脏数据
ON emp.deptno=dept.deptno; -- 用 on 定位合法数据行,剔除脏数据,将合法数据行放到一个新的临时表里
3、natural join (自然连接)
相同的列数值相等连接
***自然连接和内连接区别:**自然连接是特殊的内连接,连接的列只能显示一个列(注:使用的列(重复列)不能有限定词)
-- 格式
select 列...
from 表1
natural outer join 表2
-- 栗子
SELECT empno,ename,sal,deptno,dname,loc
FROM emp
NATURAL JOIN dept;
4、outer join(外连接)
1、left outer join (左外连接)
左表为基础,左表信息全部显示,右表信息对应显示
-- 格式
select 列...
from 左表
left outer join 右表
on 连接条件 [ + 查询条件 (先筛选再连接)]
[ where 查询条件 (先连接再筛选) ]
-- 栗子
SELECT
e.(all),
d.(all)
FROM emp e -- 左边的表emp是主表
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;
-- 92语法
SELECT
e.*,d.*
FROM emp e,dept d
WHERE e.deptno=d.deptno(+);
2、right outer join (右外连接)
以右表为基础,右表信息全部显示,左表信息对应显示
-- 格式
select 列...
from 左表
right outer join 右表
on 连接条件 [ + 查询条件 (先筛选再连接)]
[ where 查询条件 (先连接再筛选) ]
-- 栗子
SELECT
e.(all),
d.(all)
FROM emp e -- 右边的表dept是主表
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;
-- 92语法
SELECT
e.*,d.*
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno;
3、full outer join (满外连接)※注意:MySql不支持满外连接!※
左右表的数据都显示
-- 格式
select 列...
from 表1
full outer join 表2 -- Oracle用法
on 连接条件 [ + 查询条件 (先筛选再连接)]
[ where 查询条件 (先连接再筛选) ]
-- 栗子
SELECT
e.(all),
d.(all)
FROM emp e
FULL OUTER JOIN dept d -- Oracle用法
ON e.deptno=d.deptno;
2、自连接
自己连接自己
-- 查询员工名字和对应领导的名字(所有人的信息都在这一张表里,包括上下级关系)
-- 把emp当成两张表:worker、leader
-- 查询条件是:worker.mgr=leader.empno
SELECT worker.empno,worker.ename,worker.mgr,leader.ename
FROM emp worker, emp leader
WHERE worker.mgr=leader.empno;
3、非等值连接
between … and …
-- 查询员工工资等级范围(salgrade表)
-- on的条件是个范围用between…and
SELECT
e.empno,e.ename,e.sal,s.grade
FROM emp e
INNER JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;
3、联合查询【垂直堆砌】
*联合查询的3个特征:
- 要求参与合并的两个临时表的字段结构必须保持一致【字段个数,字段类型顺序】
- 将两个临时表数据行沿着垂直方向堆砌到同一个临时表
- 联合查询生成临时表的字段只能来自于【第一个临时表字段】
1、intersect (交)
对两个结果集进行交集操作,不包括重复行,不排序
SELECT empno,ename,sal,deptno FROM emp
INTERSECT
SELECT empno,ename,sal,deptno FROM emps;
2、union (并)
1、union
对两个结果集进行并集操作,不包括重复行,默认进行了union默认的排序
SELECT empno,ename,sal,deptno FROM emp
UNION
SELECT empno,ename,sal,deptno FROM emps;
2、union all
对两个结果集进行并集操作,包括重复行,没有排序
SELECT empno,ename,sal,deptno FROM emp
UNION ALL
SELECT empno,ename,sal,deptno FROM emps;
3、minus (差)
对两个结果集进行差集操作,不包括重复行,默认进行minus的默认排序
SELECT empno,ename,sal,deptno FROM emp
MINUS
SELECT empno,ename,sal,deptno FROM emps;
4、except (除去)※注意:MySql不支持except!※
EXCEPT运算自动去除重复,如果想保留所有的重复,必须用EXCEPT ALL代替EXCEPT,结果中出现的重复元组数等于两集合出现的重复元组数之差(前提是差是正值)
-- Oracle用法
SELECT empno,ename,sal,deptno FROM emp
EXCEPT
SELECT empno,ename,sal,deptno FROM emps;