07oracle之多表（关联）查询、表连接（内外全）、子查询、TOP-N分析法

多表（关联）查询：

多表查询也称之为关联查询、多表关联查询等，主要是指通过多个表的关联来获取数据的一种方式。

• 1. 多表映射关系
• 一对多：A表的一行数据，对应B表中的多条。如:一个部门可以对应多个员工.
• 多对一：B表中的多条对应A表的一行数据.如:多个员工对应一个部门.
• 多对多：学生和选修课表----学生和课程对应表
• 2. 笛卡尔集
  -笛卡尔集对于我们数据库的数据查询结果的影响：
  1．数据冗余。—笛卡尔集并不是我们所需要的数据.
  2．效率问题：导致数量级的增长。100w *100w，====》1w亿。如果你在查询大量数据的时候，不注意这个笛卡尔集的话，会导致你的查询结果时间非常非常非常长，还会导致数据库故障。
  因此，在实际运行环境下，应避免使用笛卡尔集。
• 笛卡尔集产生的条件：

1. 省略连接条件
2. 连接条件无效

• 如何避免笛卡尔集：

  在 WHERE 加入有效的连接条件。

• 3. 多表连接的类型
  根据连接方式的不同，Oracle的多表关联的类型分为：
  内连接、外连接、自连接。
  内连接分为：等值内连接、不等值内连接
  外连接分为：左外连接、右外连接、全外连接
  自连接是一种特殊的关联（一个表，自己连自己），可以包含内连接和外连接的连接方式。

内连接：

• 等值内连接

等值内连接也称之为等值连接。
【示例】

--需求：查询一下员工信息，并且显示其部门名称
SELECT * FROM emp t1,dept t2 WHERE t1.deptno=t2.deptno;--等值内连接，数据库的私有
扩展语法：隐式内连接（mysql，oracle都支持）
SELECT * FROM emp t1 INNER JOIN dept t2 ON t1.deptno=t2.deptno;--sql99语法，
显示内连接
扩展：表设计的”范式“，尽量满足前三范式。

• 不等值内连接

【示例】

 需求:查询员工信息,要求显示员工的编号、姓名、月薪、工资级别。
    --分析：要完成这个需求，需要使用到下面两张表：
    --需求:查询员工信息,要求显示员工的编号、姓名、月薪、工资级别。
    SELECT * FROM emp t1, salgrade t2 WHERE t1.sal >=t2.losal AND t1.sal<=t2.hisal;
    --隐式语法
    SELECT * FROM emp t1,salgrade t2 WHERE t1.sal BETWEEN t2.losal AND t2.hisal;
    --隐式不等值连接

外连接：

左外连接，右外连接，全外连接

• 左外连接：

示例：

--查询"所有"员工信息，要求显示员工号，姓名 ,和部门名称--要求使用左外连接
SELECT * FROM emp t1 LEFT OUTER JOIN dept t2 ON t1.deptno=t2.deptno;
--sql99标准语法
SELECT * FROM emp t1,dept t2 WHERE t1.deptno=t2.deptno(+);
--oracle私有语法（mysql不支持），+放到右边是左外，你可以认为(+)是附加补充的意思。
--要求查询所有的信息的表，我们可以称之为主表，而补充信息的表，称之为从表

• 右外连接：

示例：

----查询“所有”部门及其下属的员工的信息。--右外连接
SELECT * FROM emp t1 RIGHT OUTER JOIN dept t2 ON t1.deptno=t2.deptno;
--sql99--右外连接--右边表（dept）数据全部显示。
SELECT  * FROM emp t1,dept t2 WHERE t1.deptno(+)=t2.deptno;
--oracle语法，右外连接

如何选择左外和右外

SELECT t1.*,t2.* FROM dept t1 ,emp t2 WHERE t1.deptno=t2.deptno(+);
--1.到底是使用左外还是右外,主要是看两张表的在语句中的位置,
--两张表是有主从关系,一般把主表放在左边,----一般两张表的情况下,我们都使用左连接.
--2.+到底是放在条件哪边?左外连接的（+）放在右边,右外连接的（+）放在左边.
----记忆的方法:(+)放在从表的一方,起到数据附加的作用.

简单的说：左外连接就是左边的表的数据全部显示，右外就是右边的表的数据全部显示。

注意：这种（+）的写法，只能用在Oracle。不能用于mysql！

一定要有主表和从表这个概念，分清那张是主表，哪张是从表。 把你想查询基础表当成左表。想把谁全部都查询出来就当成主表。
到底哪张是主表哪张是从表？最终还看你的需求。 一般我们把主表放在左边，使用左外连接。 一般情况下，我们就用左连接就行了。

• 全外连接：

左表和右表的数据全部都显示，而且不是笛卡尔集。
相当于左外+右外的数据。

示例：

需求：要求将所有员工和所有部门都显示出来
--全外连接
SELECT * FROM emp t1 LEFT OUTER JOIN dept t2 on t1.deptno=t2.deptno
UNION
SELECT * FROM emp t1 RIGHT OUTER JOIN dept t2 ON t1.deptno=t2.deptno;

SELECT * FROM emp t1 FULL OUTER JOIN dept t2 ON t1.deptno=t2.deptno;--sql99语法

自连接：

自连接，就是将一张表当成两张表来查询。

自连接的查询的原理：就是将一张表当成两张表来使用.
【示例】

1.查询员工信息，要求同时显示员工和员工的领导的姓名
2.查询“所有”员工信息，要求同时显示员工和员工的领导的姓名
--查询员工信息，要求同时显示员工和员工的领导的姓名
SELECT *  FROM emp t1,emp t2 WHERE t1.mgr=t2.empno;
--查询“所有”员工信息，要求同时显示员工和员工的领导的姓名
SELECT *  FROM emp t1,emp t2 WHERE t1.mgr=t2.empno(+);
自连接是一种特殊的多表连接方式,其实含有内连接和外连接的操作.
注意问题:你也要注意笛卡尔集的产生.

子查询：

子查询也称之为嵌套子句查询。
分为单行子查询（返回单行数据）和多行子查询（返回多行数据）。

语法上的运行使用规则：

1. 子查询 (内查询、嵌套子句) 在主查询之前一次执行完成。（子查询先执行）
2. 子查询的结果被主查询使用 (外查询)。
3. 子查询要包含在括号内。
4. 将子查询放在比较条件的右侧。

• 单行子查询：
• 只返回一行。
• 使用单行比较操作符。
  
  其中<>也可以可以用!=代替，意思一样。
  使用子查询的时候，一定要保证子查询不能为空，否则数据就会出现异常。

示例

--查询部门名称是SALES的员工信息
SELECT * FROM emp WHERE deptno=(SELECT deptno FROM DEPT WHERE dname ='SALES')
了解：子查询可以是一张表的数据，也可以是不同表的数据。

• 多行子查询：
• 返回多行。
• 使用多行比较操作符。
  
• In操作符：

示例：

--需求:查找工作和'SMITH' 'ALLEN' 这两个人的工作一样的员工信息
SELECT JOB FROM emp WHERE ename IN('SMITH','ALLEN');
SELECT * FROM emp WHERE job IN(SELECT JOB FROM emp WHERE ename 
IN('SMITH','ALLEN'));

--需求:查找工作和'SMITH' 'ALLEN' 这两个人的工作不一样的员工信息
SELECT * FROM emp WHERE job NOT IN(SELECT JOB FROM emp WHERE ename 
IN('SMITH','ALLEN'));

• Any和all操作符：

示例：

--需求:查询工资比30号部门任意一个员工的工资高的员工信息。--面试题
SELECT * FROM emp WHERE deptno =30;
--任意一个：比最低的那个高就ok
SELECT * FROM emp WHERE sal >(SELECT MIN(sal) FROM emp WHERE deptno=30);
--any(多行函数)
SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno=30);
--需求:查询工资比30号部门  所有  员工的工资高的员工信息。
SELECT * FROM emp WHERE sal>(SELECT MAX (sal) FROM emp WHERE deptno=30);
--all(多个返回记录)--max(sal)
SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);

子查询注意事项：

• 关于格式：子查询要包含在括号内，最好有合理的书写风格。
• 子查询的位置：可以放在主查询的where、select、having、from的后面。不可以放在主查询的group by后面。
• 子查询和主查询可以是同一张表，也可以不是是不同一张表，只要子查询返回的结果在主查询中能使用即可。
• 关于使用操作符：单行操作符对应单行子查询，多行操作符对应多行子查询。
• 执行顺序：一般子查询先执行，再执行主查询；
• 关于排序：一般不在子查询中使用order by；但在top-N分析问题中，必须在子查询中使用order by。

TOP-N分析法：

1. 方法/步骤1:

针对百度搜索推广的后台数据报告，TOP-N分析法就有很多实际运用，例如：

2. 方法/步骤2:

关键词报告，根据消费降序排列，选取消费TOP前50的关键词：
可以筛选掉与产品或服务相关性若的关键词；
可以对出价高的广泛和短语匹配关键词进行适当调整。

3. 方法/步骤3:

搜索词报告，将一段时间内的搜索词按点击和展现分别进行降序排列，可以重点检查前100或200名的搜索词：
通过点击列表可以查看是否有遗漏的重要关键词；
通过展现列表可以知晓存在哪些展现量大但点击量小的关键词；

4. 方法/步骤4:

同样可以对其他后台报告或其他方式统计到的数据报告进行类似分析，都可能得到一些有价值的信息，从而进一步优化账户。

多行子查询一般用于from后面，作为一张新的虚拟临时表来使用。

子查询和多表关联查询的选择：

理论上，在都可以实现需求的情况下尽量选择多表查询。
原因：子查询会操作两次，多表查询只操作一次。多表的效率高。

但要注意的是，多表查询如果产生了笛卡尔集（语句上要注意条件的使用），则会出现严重的效率问题。
一般不在子查询中使用排序（order by），但在top-N分析问题中必须在子查询中使用排序。