Oracle数据库之七 多表查询

七、多表查询

​ 对于查询在之前已经学过了简单查询、限定查询、查询排序，这些都属于 sql 的标准语句，而上一章的单行函数，主要功能是为了弥补查询的不足。

​ 而从多表查询开始就正式进入到了复杂查询部分。

7.1、基本语法

• 多表查询就是在一条查询语句中，从多张表里一起取出所需要的数据。如果要想进行多表查询，直接在 from 子句之后跟上多个表即可，语法如下：

  select [distinct] *|列名称 [as][列别名],列名称 [as][列别名],...
  from 表名称1[表别名1],表名称2[表别名2],...
  [where 条件(s)]
  [order by 排序的字段1 asc|desc,排序的字段2 asc|desc,...];

• 下面就将采用 emp 表和 dept 表一起进行多表查询，查询之前多做一个步骤，先确定 emp 和 dept 表中的数据量分别有多少，可以使用 count() 函数统计

范例：统计 emp 表中的数据量 （14 行记录）

select count(*) 
from emp;

范例：统计 dept 表中的数据量 （4行记录）

select count(*) 
from dept;

范例：现在查询所有的雇员和部门的全部详细信息

select count(*) 
from emp,dept;

• 发现此时结果一共返回了56行记录，这就是笛卡尔积 造成的问题。现在就发现了，笛卡尔积的出现，可以让查询结果变得非常的庞大，如果现在两张表的数据量都很大，那么这种庞大是很可怕的，所以现在必须想办法消除掉笛卡尔积。
• 一般而言，如果要想进行笛卡尔积的消除，往往会使用关联字段，由于多张表之间可能会存在重名的字段，所以进行重名字段的访问的时候看，前面需要加上表名称，采用 “ 表名称 字段 ” 的方式来进行访问。

范例：利用等值条件来处理笛卡尔积

select * 
from emp,dept
where emp.deptno = dept.deptno;

• 这时的结果就可以发现已经消除掉笛卡尔积，但是这时积依然存在，只是不显示了而已
• 已经清楚基本概念后，下面就可以针对于数据量做一个分析。在 oracle 中存在了一个 sh 用户，当然此用户保存在了 pdbmldn 插入式数据库之中了
  • 1、打开 sqlplus ：运行 – 输入 sqlplus /nolog
  • 2、管理员连接数据库：conn sys/change_on_install as sysdba;
  • 3、切换到 pdbmldn 数据库：alter session set container=pdbmldn;
  • 4、打开 pdbmldn 数据库：alter database pdbmldn open
  • 马上就要使用 sh 用户进行操作，现在可以使用 sh 用户下的 sales 和 costs 表，这两张表的数量都比较大。

范例：查看 sh.sales 表的数据量 （918843条记录）

select count(*)
from sh.sales;

范例：查看 sh.costs 表的数据量 （82112条记录）

select count(*)
from sh.costs;

范例：如果现在直接将这两张表进行多表查询，那么来观察问题

select count(*)
from sh.sales,sh.costs;

• 一旦开始执行之后，那么这个等待的过程会很长，但是这时是显示所有数据量（包含笛卡尔积的数据量），而后再开始消除，利用等值关联。最终会返回的数据量：75,448,036,416
• 虽然消除掉了所有显示的笛卡尔积，但是数据库的原理机制及表示笛卡尔积会永远存在
• 多表查询会产生笛卡尔积，所以性能较差，可以利用等值关联字段消除笛卡尔积

7.2、多表查询实例

• 虽然多表查询本身存在了性能问题，但并不表示多表查询无法使用，需要一些更合理的做法来解决多表查询问题

范例：查询每个雇员的编号、姓名、职位、基本工资、部门名称、部门位置信息

• 确定所需要的数据表
  • emp 表：查询每个雇员的编号、姓名、职位、基本工资；
  • dept 表：部门名称，部门位置。
• 确定已知的关联字段
  • 部门与雇员关联：emp.deptno = dept.deptno
• 随后还需要按照一个 sql 语句的执行步骤编写：from , where , select 。

select emp.empno,emp.ename ,emp.job,emp.sal,dept.dname,dept.loc
from emp,dept
where emp.deptno = dept.deptno

• 但是在此处就会存在一个问题，上面程序里都是采用了表名称访问的列名称，如果现在表名称很长：yuzhou_yinhexi_diqiu_yazhou_zhongguo_beijing 。所以往往在多表查询的时候为查询的数据定义别名，而别名也是在 from 子句之中定义的，上面的程序可以改写为：

select e.empno, e.ename, e.job, e.sal, d.dname, d.loc
from emp e, dept d
where e.deptno=d.deptno;

• 在以后的程序编写之中几乎都会使用到别名

范例：查询出每个雇员的编号、姓名、雇佣日期、基本工资、工资等级

• 确定所需要的数据表
  • emp 表：查询每个雇员的编号、姓名、雇佣日期、基本工资；
  • salgrade 表：工资等级。
• 确定已知的关联字段
  • 雇员和工资等级：emp.sal between salgrade.local and salgrade.hisal ;

select e.empno, e.ename, e.hiredate, e.sal, s.grade
from emp e, salgrade s 
where e.sal between s.losal and s.hisal;

• 在之前的查询里面，发现只是显示了数字1,2,3,4,5，现在希望可以将其替换为中文，如果要替换，肯定使用 decode() 函数。

范例：为了更加清楚的显示出工资等级的信息，现在希望可以按如下格式进行替换显示：

​ grade = 1 : 显示为 “ e等工资 ”

​ grade = 2 : 显示为 “ d等工资 ”

​ grade = 3 : 显示为 “ c等工资 ”

​ grade = 4 : 显示为 “ b等工资 ”

​ grade = 5 : 显示为 “ a等工资 ”

select e.empno, e.ename, e.hiredate, e.sal,
    decode(s.grade,1,'e等工资',2,'d等工资',3,'c等工资',4,'b等工资',5,'a等工资') grade
from emp e, salgrade s 
where e.sal between s.losal and s.hisal;

• 前面讲的都是针对于两张表进行多表查询，而且多表查询里面都只使用了一个条件来消除笛卡尔积；如果现在是多个消除笛卡尔积的条件，那么往往使用 and 将这些条件连接在一起

范例：查询出每个雇员的姓名、职位、基本工资、部门名称、工资等级

• 确定所需要的数据表
  • emp 表：每个雇员的姓名、职位、基本工资；
  • dept 表：部门名称
  • salgrade 表：工资等级。
• 确定已知的关联字段
  • 雇员和部门：emp.deptno = dept.deptno
  • 雇员和工资等级：emp.sal between salgrade.local and salgrade.hisal ;

select e.ename, e.job, e.sal, d.dname,
    decode(s.grade,1,'e等工资',2,'d等工资',3,'c等工资',4,'b等工资',5,'a等工资') grade
from emp e, dept d, salgrade s 
where e.deptno = d.deptno and e.sal between s.losal and s.hisal;

• 多表查询之中，每当增加一个关联表都需要设置消除笛卡尔积的条件

7.3、表的连接操作

• 对于数据表的连接操作在数据库中一共定义了两种：
  • 内连接：也称为等值连接（或称为连接，还可以被称为普通连接或者自然连接），是最早的一种连接方式，内连接是从结果表中删除与其他被连接表中没有匹配行的所有元组，所以当匹配条件不满足时内连接可能会丢失信息。在之前所使用的连接方式都属于内连接，而在 where 子句之中设置的消除笛卡尔积的条件就是采用了等值判断的方式进行的。
  • 外连接：内连接中只能够显示等值满足的条件，如果不满足的条件则无法显示，如果现在希望特定表中的数据可以全部显示，就利用外连接，外连接分为三种：左外连接（简称：左连接）、右外连接（简称：右连接）、全外连接（简称：全连接，在 sql :1999 语法部分讲解）
• 在之前所编写的表连接操作都属于内连接的定义范畴
• 为了更好的观察各个连接方式的区别，首先需要在 emp 表中增加一条数据，这个增加语法会在后面解释

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) 
    values(8888,'李华华','clerk',7369,sysdate,800,100,null);

• 这时增加完的数据是一个没有部门的雇员，即此雇员的部门编号是 null ，增加完成之后查看一下 emp 表中当前的全部数据

select * from emp;

• 此处没有部门编号，下面就演示等值连接所带来的效果

范例：使用等值连接

select *
from emp e, dept d
where e.deptno = d.deptno;

• 通过此时的结果可以发现两个问题：
  • 问题一：没有部门的雇员没有显示
  • 问题二：有一个40部门没有显示
• 所以现在就可以发现，使用内连接只有满足连接条件的数据才会全部显示。可是如果说现在希望 emp 或是 dept 表中的数据显示完整，就可以利用外连接进行。
• 外连接现在主要使用两种：
  • 左外连接：左关系属性 = 右关系属性(+) ，+ 放在了等号的右边，表示左连接；
  • 右外连接：左关系属性(+) = 右关系属性 ，+ 放在了等号的左边，表示右连接。

范例：使用左外连接，显示雇员编号是8888的信息

select *
from emp e, dept d 
where e.deptno = d.deptno(+);

范例：使用右外连接，显示部门编号为40的信息

select *
from emp e, dept d 
where e.deptno(+) = d.deptno;

• 上面结果中因为40部门没有雇员，所以所有的雇员信息都为 null 。
• 个人总结：使用外连接的环境，如果所需要的数据信息没有显示出来，那么就使用外连接，而具体是左外还是右外，个人认为没必要去记，去试就行！

7.4、自身关联

• 在 emp 表中存在有一个 mgr 字段，这个字段表示的是雇员的领导

select * from emp;

• 现在如果要显示雇员的领导信息，那么肯定利用雇员表和雇员表自己的连接操作完成。利用雇员表中的领导编号，找到对应此编号的雇员信息。

范例：查询出每个雇员的编号、姓名及其上级领导的编号、姓名

• 确定所需要的数据表
  • emp 表：雇员的编号、姓名
  • emp 表：找到领导的编号、姓名
• 确定已知的关联字段
  • 雇员和领导：emp.mgr = memp.empno （雇员的领导编号 = 领导的信息）
• 步骤一：直接进行自身连接的操作

select e.empno eno, e.ename ename, m.empno mno, m.ename mname
from emp e, emp m 
where e.mgr = m.empno;

​ 现在表中一共有15条记录，但是只有14条的记录显示，等值连接在没有条件满足的时候，是不可能有数据显示的。

​ 在 emp 表中 king 这个雇员是没有领导的，这个时候就必须考虑外连接。

• 步骤二：使用左外连接

select e.empno eno, e.ename ename, m.empno mno, m.ename mname
from emp e, emp m 
where e.mgr = m.empno(+);

​ 对于没有领导信息的雇员，对应的领导信息，全部使用 null 进行表示

范例：查询出在1981年雇员的全部雇员的编号、姓名、雇佣日期（按照年-月-日显示）、工作、领导姓名、雇员月工资、雇员年工资（基本工资+奖金）、雇员工资等级、部门编号、部门名称、部门位置、并且要求这些雇员的月基本工资在1500~3500之间，将最后的结果按照年工资的降序排列，如果年工资相等，则按工作进行排序

• 确定所需要的数据表
  • emp 表：编号、姓名、雇佣日期、工作、月工资、计算年工资；
  • emp 表：领导姓名；
  • dept 表：部门编号、名称、位置；
  • salgrade 表：工资等级。
• 确定已知的关联字段
  • 雇员和领导：emp.mgr = memp.empno;
  • 雇员和部门：emp.deptno = dept.deptno;
  • 雇员和工资等级：emp.sal between salgrade.losal and salgrade.hisal .
• 步骤一：查询出所有在1981年雇佣的雇员编号、姓名、雇佣日期、工作、月工资、年工资、并且月薪在1500~3500之间。只需要 emp 单张表即可实现。

select e.empno, e.ename, e.hiredate, e.sal, (e.sal+nvl(e.comm,0))*12 income
from emp e 
where to_char(e.hiredate,'yyyy') = '1981' and e.sal between 1500 and 3500;

• 步骤二：加入领导信息，使用自身关联

select e.empno, e.ename, e.hiredate, e.job, e.sal, (e.sal+nvl(e.comm,0))*12 income,
    m.ename mname
from emp e, emp m 
where to_char(e.hiredate,'yyyy') = '1981' and e.sal between 1500 and 3500
    and e.mgr = m.empno(+);

• 步骤三：加入部门信息

select e.empno, e.ename, e.hiredate, e.job, e.sal, (e.sal+nvl(e.comm,0))*12 income,
    m.ename mname, d.deptno dno, d.dname dname, d.loc 
from emp e, emp m, dept d  
where to_char(e.hiredate,'yyyy') = '1981' and e.sal between 1500 and 3500
    and e.mgr = m.empno(+)
    and e.deptno = d.deptno;

• 步骤四：查询出工资等级

select e.empno, e.ename, e.hiredate, e.job, e.sal, (e.sal+nvl(e.comm,0))*12 income,
    m.ename mname, d.deptno dno, d.dname dname, d.loc, s.grade
from emp e, emp m, dept d,salgrade s   
where to_char(e.hiredate,'yyyy') = '1981' and e.sal between 1500 and 3500
    and e.mgr = m.empno(+)
    and e.deptno = d.deptno
    and e.sal between s.losal and s.hisal;

select e.empno, e.ename, e.hiredate, e.job, e.sal, (e.sal+nvl(e.comm,0))*12 income,
    m.ename mname, d.deptno dno, d.dname dname, d.loc, s.grade,
    decode(s.grade,1,'e等工资',2,'d等工资',3,'c等工资',4,'b等工资',5,'a等工资') 工资等级
from emp e, emp m, dept d,salgrade s   
where to_char(e.hiredate,'yyyy') = '1981' and e.sal between 1500 and 3500
    and e.mgr = m.empno(+)
    and e.deptno = d.deptno
    and e.sal between s.losal and s.hisal;

• 步骤五：进行排序，由于 select 是在 order by 子句之前执行，所以在 select 子句之中所定义的别名，order by 子句是可以直接使用的。

select e.empno, e.ename, e.hiredate, e.job, e.sal, (e.sal+nvl(e.comm,0))*12 income,
    m.ename mname, d.deptno dno, d.dname dname, d.loc, s.grade,
    decode(s.grade,1,'e等工资',2,'d等工资',3,'c等工资',4,'b等工资',5,'a等工资') 工资等级
from emp e, emp m, dept d,salgrade s   
where to_char(e.hiredate,'yyyy') = '1981' and e.sal between 1500 and 3500
    and e.mgr = m.empno(+)
    and e.deptno = d.deptno
    and e.sal between s.losal and s.hisal;
order by income desc, e.job;

• 通过这一稍微复杂点的题目，可以发现，所有的分析必须要分步进行，而且这些分析过程，需要大量的练习才可以巩固。
• 自身关联属于一张表自己关联自己的情况，此时依然会产生笛卡尔积。

7.5、sql:1999 语法的支持

• 在之前使用的 “ (+) ” 标记只适合在 oracle 数据库之中应用，如果是其他的数据库，是无法使用的
• sql:1999 语法

select [distinct] *|列名称 [as][列别名],列名称 [as][列别名],...
from 表1 表别名1 [cross join 表2 表别名2]|
[natural join 表2 表别名2]|
[join 表2 using(关联列名称)]|
[join 表2 on(关联条件)]|
[left|right|full outer join 表2 on(关联条件)]
[where 条件(s)]
[order by 排序的字段1 asc|desc, 排序的字段2 asc|desc,...];

• 实际上以上给出的是综合语法，下面将拆分进行讲解

7.5.1 交叉连接

• 交叉连接（cross join）作用于两个关系上，并且第一个关系的每个元组与第二个关系的所有元组进行连接，这样的操作形式与笛卡尔积是完全相同的，交叉连接的语法如下所示：

select [distinct] *|列名称 [as][列别名],列名称 [as][列别名],...
from 表1 表别名1 [cross join 表2 表别名2]|
[where 条件(s)]
[order by 排序的字段1 asc|desc, 排序的字段2 asc|desc,...];

• 交叉连接的主要功能就是产生笛卡尔积

范例：使用交叉连接查询信息

select * 
from emp cross join dept;

• 一般而言，在进行多表连接的时候都一定会存在关联字段以消除笛卡尔积，而关联字段的名称一般都会一样，如果不一样，也会有部分相同，现在讨论的是一样的情况，例如 deptno 字段这就表示一样，就可以利用自然连接来消除掉笛卡尔积

7.5.2 自然连接

• 自然连接（natural join）运算作用于两个关系，最终会通过两个关系产生出一个关系作为结果。与交叉连接（笛卡尔积）不同的是，自然连接只考虑那些在两个关系模式中都出现的属性上取值相同的元组对。自然连接的操作语法如下：

select [distinct] *|列名称 [as][列别名],列名称 [as][列别名],...
from 表1 表别名1 [natural join 表2 表别名2]|
[where 条件(s)]
[order by 排序的字段1 asc|desc, 排序的字段2 asc|desc,...];

范例：使用自然连接查询信息

select * 
from emp natural join dept;

• 这个时候会把连接的字段放在第一列上进行显示，而且这种方式就是一种内连接的方式。

7.5.3 using 子句

• 通过自然连接可以直接使用关联字段消除掉笛卡尔积，那么如果现在的两张表中没有存在这种关联字段的话，就可以通过 using 子句完成笛卡尔积的消除，using 子句的语法如下：

select [distinct] *|列名称 [as][列别名],列名称 [as][列别名],...
from 表1 表别名1 [join 表2 using(关联列名称)]|
[where 条件(s)]
[order by 排序的字段1 asc|desc, 排序的字段2 asc|desc,...];

范例：使用 using 子句查询信息

select * 
from emp join dept using(deptno);

7.5.4 on 子句

• 在之前编写等值连接时，采用了关联字段进行笛卡尔积的消除，那么用户在 sql:1999 语法之中通过 on 子句就可以由用户手工设置一个关联条件，on 子句语法如下：

select [distinct] *|列名称 [as][列别名],列名称 [as][列别名],...
from 表1 表别名1 [join 表2 on(关联条件)]|
[where 条件(s)]
[order by 排序的字段1 asc|desc, 排序的字段2 asc|desc,...];

• using 是设置连接字段，而 on 是设置连接条件

范例：使用 on 子句查询信息

select * 
from emp e join salgrade s on(e.sal between s.losal and s.hisal);

7.5.5 外连接

• 在数据的查询操作中数据的外连接一共分为三种形式：左外连接、右外连接、全外连接，而此时连接的语法如下：

select [distinct] *|列名称 [as][列别名],列名称 [as][列别名],...
from 表1 表别名1 [left|right|full outer join 表2 on(关联条件)]
[where 条件(s)]
[order by 排序的字段1 asc|desc, 排序的字段2 asc|desc,...];

• 对于外连接，在之前使用的是 “ (+) ”，这个标记只能够实现左外或者右外连接，但是对于全外连接无法使用，而全外连接只能够依靠 sql:1999 语法之中规定的内容。

范例：实现右外连接

select * 
from emp e right outer join dept d on (e.deptno = d.deptno);

范例： 实现左外连接

select * 
from emp e left outer join dept d on (e.deptno = d.deptno);

范例：实现全外连接

select * 
from emp e full outer join dept d on (e.deptno = d.deptno);

• 通过以上的分析可以发现，给出的所有语法里面，只有全外连接只能够通过 sql:1999 语法实现，但是对于这种全外连接，使用的情况并不多。
• 而且个人建议，如果使用的是 oracle 数据库，就使用 “ (+) ” 标记控制左右连接，不使用它实现内连接。

7.6、数据的集合运算

• 数据的集合操作指的是查询结果的操作
• 集合运算是一种二目运算符，一共包括四种运算符：并、差、交、笛卡尔积，其中对于笛卡尔积在之前已经演示过了，所以本次主要是看 并、交、差 三种操作。操作集合的语法如下

查询语句
    [union | union all | intersect | minus]
查询语句
    ...

• 要实现集合的运算，主要使用四种运算符：
  • union （并集）：返回若干个查询结果的全部内容，但是重复元组不显示；
  • union all （并集）：返回若干个查询结果的全部内容，重复元组也会显示；
  • minus （差集）：返回若干个查询结果中的不同部分
  • intersect （交集）：返回若干个查询结果中的相同部分

7.6.1 并集操作

• 并集操作是将多个查询的结果连接到一起，而对于并操作提供了两种操作符：union（不显示重复），union all（显示重复）

范例：并集操作：union , union all

• 第一个查询

select * from dept;

• 第二个查询

select * from dept where deptno = 10;

​ 这个时候两个查询结果返回的列的结构相同。

• 使用 union

select * from dept
    union
select * from dept where deptno = 10;

​ 第一个查询已经包含了第二个查询的内容，所以重复数据不显示了。

范例：使用 union all 显示全部

select * from dept
    union all
select * from dept where deptno = 10;

• 提示：在以后进行查询操作编写过程中，建议尽量使用union 或 union all 来代替 or

范例：查询所有销售人员和办事人员的信息

• 实现一：

select * from emp where job = 'salesman' or job = 'clerk';

• 实现二：

select * from emp where job in ('salesman', 'clerk');

• 实现三：

select * from emp where job = 'salesman'
    union
select * from emp where job = 'clerk'

7.6.2 差集操作

范例：使用 minus 执行差集操作

select * from dept
    minus
select * from dept where deptno = 10;

• 结果就显示3行，deptno=10 的不显示了

7.6.3 交集操作

范例：使用 intersect 执行交集操作

select * from dept
    intersect
select * from dept where deptno = 10;

• 结果只显示 deptno=10 一行了
• 集合操作时，各个查询语句返回的结构要求一致。

说明：本学习资料是根据李兴华的oracle开发实战经典整理