sql-4-DQL-多表联查
笛卡尔积
-
左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积
-
笛卡尔积引入了很多无用的数据，要完成多表查询，需要设置过滤条件来消除无用的数据
select * from emp, dept;
连接查询
- 从哪些表中查询数据
- 关联条件是什么
- 查询哪些字段
内连接查询
用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键
1. 隐式内连接：使用where
-- 查询员工表的工号\姓名\部门名称
select emp.EMPNO,emp.ENAME,dept.DEPTNO,dept.DNAME
from emp ,dept
where emp.DEPTNO = dept.DEPTNO;
2. 显式内连接：使用inner join ... on ...
select 字段列表 from 表名1 [inner] join 表名2 on 条件
-- 将隐式内连接改成显示内连接
select emp.EMPNO,emp.ENAME,dept.DEPTNO,dept.DNAME
from emp
inner join dept
on emp.DEPTNO = dept.DEPTNO;
select emp.EMPNO,emp.ENAME,dept.DEPTNO,dept.DNAME
from emp
join dept
on emp.DEPTNO = dept.DEPTNO;
外连接查询
1. 左外连接
用左边表的记录去匹配右边表的记录，如果匹配到了符合条件的记录那就显示；对于没有匹配的记录，结果集中的对应列值将为NULL。在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)
select 字段列表 from 表1 left [outer] join 表2 on 条件；
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
select emp.*,dept.DNAME from emp left OUTER join dept on emp.DEPTNO = dept.DEPTNO;
-- 省略OUTER关键字
select emp.*,dept.DNAME from emp left join dept on emp.DEPTNO = dept.DEPTNO;
-- 仅在Oracle中有效，Oracle数据库系统更倾向于使用+运算符
select emp.*,dept.DNAME from emp , dept where emp.DEPTNO = dept.DEPTNO(+);
2. 右外连接
用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL：在内连接的基础上保证右表的数据全部显示
select 字段列表 from 表1 right [outer] join 表2 on 条件；
-- 右连接
select emp.*,dept.DNAME from dept RIGHT OUTER join emp on emp.DEPTNO = dept.DEPTNO;
-- 省略OUTER关键字
select emp.*,dept.DNAME from dept RIGHT join emp on emp.DEPTNO = dept.DEPTNO;
-- 仅在Oracle中有效
select emp.*,dept.DNAME from emp , dept where emp.DEPTNO(+) = dept.DEPTNO;
3.全外连接
全外 = 左外+右外+去重
-- mysql不支持全外连接
select emp.empno,emp.ename,emp.deptno,dept.deptno,dept.dname
from emp
full outer join dept
on emp.deptno = dept.deptno;
自连接
将一张表，通过别名“视为”不同的表
select e.ename 员工, b.ename 领导
from emp e,emp b
where e.mgr = b.empno ;
自连接不适合操作大表
对于层次查询，Oracle的CONNECT BY和START WITH语句是非常强大的工具，用于处理树形结构的数据，比如组织结构、产品分类等。
-- 查询emp表中的层级关系
SELECT level, empno, ename, mgr
FROM emp
START WITH mgr IS NULL -- 从顶层开始，即没有上级的员工（通常是最高层的领导）
CONNECT BY PRIOR empno = mgr -- 连接当前行的empno与上一行的mgr，构建层级关系
ORDER BY 1; -- 按照层级（level）排序
子查询
查询中嵌套查询，称嵌套查询为子查询。
示例：查询工资比SCOTT高的员工信息
第一步：查SCOTT的工资
select sal from emp where ename='SCOTT';
第二步：查询比3000高的员工
select * from emp where sal > 3000;
以上两步骤，合二为一：
子查询：子查询的查询结果，作为主查询的查询条件。
select * from emp where sal > ( select sal from emp where ename='SCOTT' );
子查询注意：
- 子查询可以出现在 WHERE 、 SELECT 、 HAVING 和 FROM 子句中，但不能出现在group by后面。
- 主查询和子查询可以不是同一张表
- 一般不在子查询中排序；但在Top-N分析问题中，必须对子查询排序
- 如果子查询结果包含NULL，使用
NOT IN会导致整个条件不成立（即使其他值满足条件）。因此，当子查询可能返回NULL值时，应排除这些NULL值，如使用WHERE子句过滤掉NULL。
示例：查询是老板的员工信息
select * from emp where empno not in (select mgr from emp);
必须排空： (select mgr from emp where mgr is not null)
子查询不同的结果类型
单行单列：通常用于直接比较，如使用比较运算符（>, <, =, >=, <=）。
多行单列：适合与IN, ANY, ALL等多值比较运算符配合使用。
多行多列：子查询结果作为临时表，通过JOIN（隐式或显式）与外部查询进行联合查询。
- 子查询的结果是 单行单列 ，使用比较运算符（
>, <, =, >=, <=）去判断
-- 也可以使用在 select 后 或 having 后
-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.sal < (SELECT AVG(sal) FROM emp);
- 子查询的结果是 多行单列 ，适合与 IN, ANY, ALL 等多值比较运算符配合使用。
-- 查询'SALES'和'OPERATIONS'所有的员工信息
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE dNAME = 'SALES' OR dNAME = 'OPERATIONS');
子查询中的运算符 any、all
ANY 运算符（也写作 SOME，在MySQL中两者等价）：
当使用 ANY 时，主查询中的值只需要大于、小于或等于子查询返回的任意一个值即可满足条件。这相当于逻辑上的 "存在至少一个匹配"。例如：
-- 查询工资比30号部门其中一个员工高的员工信息
select * from emp where sal > any (select sal from emp where deptno=30);
等价于 > (select min(sal) from …)
ALL 运算符要求主查询中的值必须大于、小于或等于子查询返回的所有值。这相当于逻辑上的 "所有都匹配"。例如：
-- 查询工资比30号部门所有员工高的员工信息
select * from emp where sal > all (select sal from emp where deptno=30);
等价于 > (select max(sal) from…)
- 子查询的结果是多行多列的，可以作为临时表，通过JOIN（隐式或显式）与外部查询进行联合查询。
-- 查询员工入职日期是1986-11-11日之后的员工信息和部门信息
-- 子查询
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.hiredate > '1986-11-11') t2 WHERE t1.deptno = t2.deptno;
-- 注意oracle和mysql日期格式区别 oracle：'11-11月-86'
-- 可以改写成普通内连接
集合
- union:并集，返回各个查询的所有记录，重复部分，只出现一次。
- union all:并集，返回各个查询的所有记录，重复部分 不受影响。
- intersect,交集：返回两个集合 共用的记录。
- minus，补集：返回 包含在第一个查询集合中，但同时不包含在第二个查询集合中的记录。
-- mysql不支持 intersect 和 minus
select * from emp where deptno =10
union
select * from emp where deptno =20;
select * from emp where deptno =10
union all
select * from emp where deptno =20;
select * from emp where deptno != 10
intersect
select * from emp where deptno =20;
select * from emp where deptno !=10
minus
select * from emp where deptno =20;
