sql-DQL-多表联查

多表查询

笛卡尔积

• 左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积
  

select * from emp, dept;

• 笛卡尔积引入了很多无用的数据，要完成多表查询，需要设置过滤条件来消除无用的数据

连接查询

1. 从哪些表中查询数据
2. 关联条件是什么
3. 查询哪些字段

内连接查询

用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键

1. 隐式内连接：使用where

-- 查询所有员工信息和对应的部门信息
select * from emp ,dept where emp.DEPTNO = dept.DEPTNO;

-- 查询员工表的工号\姓名\部门名称
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;

-- mysql不支持 这种写法
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;

select emp.*,dept.DNAME from dept RIGHT join emp on emp.DEPTNO = dept.DEPTNO;

-- mysql不支持这种写法
select emp.*,dept.DNAME from emp , dept where emp.DEPTNO(+) = dept.DEPTNO;
-- 该sql仅用于演示右外连接写法 和上面2条sql 含义不一样

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
select level,empno,ename,mgr from emp
connect by prior empno=mgr
start with mgr is null
order by 1;

子查询

查询中嵌套查询，称嵌套查询为子查询。

示例：查询工资比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' );

子查询注意：
a.子查询可以出现的位置：where、select、having、from ；不能出现在group by后面。
b. 主查询和子查询可以不是同一张表
e. 一般不在子查询中排序；但在Top-N分析问题中，必须对子查询排序
子查询中的null
not in不能有null
not in (10,20,null)
示例：查询是老板的员工信息
select * from emp where empno not in (select mgr from emp);
必须排空： (select mgr from emp where mgr is not null)

子查询不同情况

1. 子查询的结果是单行单列的
	可以作为条件，使用运算符去判断	运算符： > >= < <= =
	也可以使用在 select 后 或 having后

-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.sal < (SELECT AVG(sal) FROM emp);

2. 子查询的结果是 *多行单列* 的
	可以作为条件，使用多行运算符来判断

in
-- 查询'SALES'和'OPERATIONS'所有的员工信息
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE dNAME = 'SALES' OR dNAME = 'OPERATIONS');

子查询中的运算符 any、all

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…)

3. 子查询的结果是多行多列的
	可以作为一张虚拟表参与查询

-- 查询员工入职日期是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，补集：返回 包含在第一个查询集合中，但同时不包含在第二个查询集合中的记录。

-- intersect 和 minus mysql不支持

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;