oracle多表查询

                     Oracle多表查询

 在学习oracle数据库许多同学多oracle数据库多表查询的应用以及原理有些困惑，下面是我的一些学习经验和技巧，希望和大家分享学习。

一、多表查询的基本概念

在之前所使用的查询操作之中，都是从一张表之中查询出所需要的内容，那么如果现在一个查询语句需要显示多张表的数据，则就必须应用到多表查询的操作，而多表查询的语法如下：

SELECT [DISTINCT]* | 字段 [别名] [,字段 [别名] ,…]

FROM表名称 [别名],[表名称 [别名] ,…]

[WHERE 条件(S)]

[ORDER BY 排序字段 [ASC|DESC][,排序字段 [ASC|DESC] ,…]];

但是如果要进行多表查询之前，首先必须先查询出几个数据 ——雇员表和部门表中的数据量，这个操作可以通过COUNT()函数完成。

范例：查询emp表中的数据量 ——返回了14条记录

SELECTCOUNT(*) FROM emp;

范例：查询dept表中的数据量 ——4条记录

SELECTCOUNT(*) FROM dept;

额外补充一点：何为经验？

在日后的开发之中，很多人都肯定要接触到许多新的数据库和数据表，那么在这种时候有两种做法：

·        做法一：新人做法，上来直接输入以下的命令：

SELECT* FROM 表名称;

如果此时数据量较大的话，一上无法浏览数据，二有可能造成系统的死机；

·        做法二：老人做法，先看一下有多少条记录：

SELECTCOUNT(*) FROM 表名称;

如果此时数据量较小，则可以查询全部数据，如果数据量较大则不能直接使用SELECT查询。

现在确定好了emp和dept表中的记录之后，下面完成一个基本的多表查询：

SELECT* FROM emp, dept;

但是现在查询之后发现一共产生了56条记录 =雇员表的14条记录 *部门表的4条记录，之所以会造成这样的问题，主要都是由数据库的查询机制所决定的，例如，如下图所示。

本问题在数据库的操作之中被称为笛卡尔积，就表示多张表的数据乘积的意思，但是这种查询结果肯定不是用户所希望的，那么该如何去掉笛卡尔积呢？

最简单的方式是采用关联字段的形式，emp表和dept表之间现在存在了deptno的关联字段，所以现在可以从这个字段上的判断开始。

当在查询之中，不同的表中有了相同字段名称的时候，访问这些字段必须加上表名称，即“表.字段”。

SELECT* FROM emp

WHERE emp.deptno=dept.deptno;

此时的查询结果之中已经消除了笛卡尔积，但是现在只属于显示上的消除，而真正笛卡尔积现在依然存在，因为数据库的操作机制就属于逐行的进行数据的判断，那么如果按照这个思路理解的话，现在假设两张表的数据量都很大的话，那么使用这种多表查询的性能。

范例：以sh用户的大数据表为例

SELECTCOUNT(*) FROM sales, costs

WHERE sales.prod_id=costs.prod_id;

这两张表即便消除了笛卡尔积的显示，但是本身也会有笛卡尔积的问题，所以最终的查询结果会很慢显示，甚至是不显示，所以通过这道程序一定要记住，多表查询的性能是很差的，当然，性能差是有一个前提的：数据量大。

但是以上的程序也存在一个问题，在之前访问表中字段的时候使用的是“表.字段”名称，那么如果说现在假设表名称很长，例如“yinhexi_diqiu_yazhou_zhongguo_beijing_xicheng_ren”，所以一般在进行多表查询的时候往往都会为表起一个别名，通过别名.字段的方式进行查询。

SELECT* FROM emp e, dept d

WHERE e.deptno=d.deptno;

范例：查询出每一位雇员的编号、姓名、职位、部门名称、位置

1、确定所需要的数据表：

·        emp表：可以查询出雇员的编号、姓名、职位；

·        dept表：可以查询出部门名称和位置；

2、确定表的关联字段：emp.deptno=dept.deptno；

第一步：查询出每一位雇员的编号、姓名、职位

SELECT e.empno, e.ename, e.job

FROM emp e;

第二步：为查询中引入部门表，同时需要增加一个消除笛卡尔积的条件

SELECT e.empno, e.ename, e.job, d.dname, d.loc

FROM emp e, dept, d

WHERE e.deptno=d.deptno;

以后遇到问题，发现没有解决问题的思路，就按照上面的步骤进行，慢慢的分析解决，因为多表查询不可能一次性全部写出，需要逐步分析的。

范例：要求查询出每一位雇员的姓名、职位、领导的姓名。

现在肯定要准备出两个emp表，所以这个时候可以称为emp表的自身关联，按照之前的分析如下：

1、确定所需要的数据表：

·        emp表（雇员）：取得雇员的姓名、职位、领导编号；

·        emp表（领导）：取得雇员的姓名（领导的姓名）；

2、确定关联字段：emp.mgr=memp.empno（雇员的领导编号 =领导（雇员）的雇员编号）

第一步：查询每一位雇员的姓名、职位

SELECT e.ename, e.job

FROM emp e;

第二步：查询领导信息，加入自身关联

SELECT e.ename, e.job, m.ename

FROM emp e, emp m

WHERE e.mgr=m.empno;

此时的查询结果之中缺少了“KING”的记录，因为KING没有领导，而要想解决这个问题，就需要等待之后讲解的左、右连接的问题了。

范例：查询出每个雇员的编号、姓名、基本工资、职位、领导的姓名、部门名称及位置。

1、确定所需要的数据表：

·        emp表：每个雇员的编号、姓名、基本工资、职位；

·        emp表（领导）：领导的姓名；

·        dept表：部门的名称及位置。

2、确定已知的关联字段：

·        雇员和部门：emp.deptno=dept.deptno;

·        雇员和领导：emp.mgr=memp.empno;

第一步：查询出每个雇员的编号、姓名、基本工资、职位

SELECT empno, ename, sal, job

FROM emp;

第二步：加入领导的信息，引入自身关联，同时增加消除笛卡尔积的条件

SELECT e.empno, e.ename, e.sal, e.job, m.ename

FROM emp e, emp m

WHERE e.mgr=m.empno;

第三步：加入部门的信息，引入dept表，既然有新的表进来，则需要继续增加消除笛卡尔积的条件

SELECT e.empno, e.ename, e.sal, e.job, m.ename,d.dname, d.loc

FROM emp e, emp m, dept d

WHERE e.mgr=m.empnoAND e.deptno=d.deptno;

所以以后的所有类似的问题最好都能够按照如上的方式编写，形成自己的思路。

思考题：现在要求查询出每一个雇员的编号、姓名、工资、部门名称、工资所在公司的工资等级。

1、确定所需要的数据表：

·        emp表：雇员的编号、姓名、工资；

·        dept表：部门名称；

·        salgrade表：工资等级；

2、确定已知的关联字段：

·        雇员和部门：emp.deptno=dept.deptno;

·        雇员和工资等级：emp.sal BETWEENsalgrade.losal AND salgrade.hisal;

第一步：查询出每一个雇员的编号、姓名、工资

SELECT e.empno, e.ename, e.sal

FROM emp e;

第二步：引入部门表，同时增加一个消除笛卡尔积的条件

SELECT e.empno, e.ename, e.sal, d.dname

FROM emp e, dept d

WHERE e.deptno=d.deptno;

第三步：引入工资等级表，继续增加消除笛卡尔积的条件

SELECT e.empno, e.ename, e.sal, d.dname, s.grade

FROM emp e, dept d, salgrade s

WHERE e.deptno=d.deptnoAND e.salBETWEEN s.losal ANDs.hisal;

如果现在有如下的进一步要求：将每一个工资等级替换成具体的文字信息，例如：

1 替换成第五等工资、2替换成第四等工资、3替换成第三等工资，依次类推 -->依靠DECODE()实现

SELECT e.empno, e.ename, e.sal, d.dname

DECODE(s.grade,1,’第五等工资’,2,’第四等工资’,3,’第三等工资’,4,’第二等工资’,5,’第一等工资’) gradeinfo

FROM emp e, dept d, salgrade s

WHERE e.deptno=d.deptnoAND e.salBETWEEN s.losal ANDs.hisal;

总结：核心是消除笛卡尔积

．笛卡尔集

 笛卡尔集会在下面条件下产生:

• 省略连接条件

• 连接条件无效

• 所有表中的所有行互相连接

为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件。

//查询员工及部门的详细信息  但是会产生一个笛卡尔积的效果

SQL>select * from emp,dept;

二  内连接和外连接

1、              内连接：

内连接根据所使用的比较方式不同,把内连接分为了：

1）  等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

SQL>select * from emp e inner join dept d on e.deptno = d.deptno;

2）  不等连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

SQL>select * from emp e inner join dept d on e.deptno>d.deptno;

3）  自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

SQL>select * from emp natural join dept;

SQL>select  e.*,d.dname,d.loc  from emp e inner join dept d on e.deptno =d.deptno;

SQL>select d.*,e.ename,e.empno,e.job,e.mgr,e.hiredate,e.sal,e.comm from emp e innerjoin dept d on e.deptno=d.deptno;

 

备注：Distinct是去掉重复的行,而自然连接是去掉重复的列。

 

2、  外连接

内连接的查询结果都是满足连接条件的记录。但是，有时我们也希望输出那些不满足连接条件的记录的信息。比如，我们想知道这个部门中所有员工的情况,也包括没有员工的部门，这时就需要使用外连接。外连接是只限制一张表中的数据必须满足连接条件，而另一张表中的数据可以不满足连接条件的连接方式。3种外连接：

　　1) 左外连接（LEFTOUTER JOIN）

　　如果在连接查询中，连接表左端的表中所有的记录都列出来，并且能在右端的表中找到匹配的记录，那么连接成功。如果在右端的表中，没能找到匹配的记录，那么对应的记录是空值（NULL）。这时，查询语句使用关键字 LEFTOUTER JOIN，也就是说，左外连接的含义是限制连接关键字右端的表中的数据必须满足连接条件，而不关左端的表中的数据是否满足连接条件，均输出左端表中的内容。

　　例如：要查询所有部门的员工信息查询语句为

SQL>select * from dept d left outer join emp e on e.deptno=d.deptno order by d.deptno;　　左外连接查询中左端表中的所有记录的信息都得到了保留。

  备注：部门表中记录保留,如果部门中没有员工,部门显示 员工记录用null补充。

　　2）右外连接（RIGHTOUTER JOIN）

　　右外连接与左外连接类似，只是右端表中的所有元组都列出，限制左端表的数据必须满足连接条件，而不管右端表中的数据是否满足连接条件，均输出表中的内容。

　　例如：同上例内容，查询语句为

SQL>select * from emp e right outer join dept d on e.deptno=d.deptno order byd.deptno;

　　右外连接查询中右端表中的所有元组的信息都得到了保留。

　　3）全外连接（FULLOUTER JOIN）

　　全外连接查询的特点是左、右两端表中的记录都输出，如果没能找到匹配的记录，就使用NULL来代替。

　　例如：同左外连接例子内容，查询语句为

　　SQL>select * from emp e full outer join dept d on e.deptno=d.deptno order byd.deptno;

　　全外连接查询中所有表中的元组信息都得到了保留。

以上是我在学习oracle数据库多表查询时个人的经验，希望可以帮助到大家学习。