oracle分别查询多个表的总数据,【Oracle】数据库多表查询

多表查询

1.多表查询的实现形式。

2.笛卡尔积的处理。

3.数据表的连接操作。

4.数据的集合操作。

多表查询

在之前的查询里，from子句里只设置了一张数据表，如果现在需要同时从多张数据表里取出数据就就属于多表查询。

基本形式：

SELECT * FROM 表名称[别名]，表名称[别名]，表名称[别名]… WHERE 过滤条件 ORDER BY 字段[ASC|DECS].

下面观察一个现象：

两表一起查询的时候行数是两表行数的乘积emp表的数据被重复的显示了4次(也就是dept表的数据量)。

可以看到，以上的数据库看成=是集合的话，他们出现了乘积的概念，这种关系在数据库上称为笛卡尔积问题。现在如果想消除掉笛卡尔积的问题必须为两张表设置关系。

我们想两张表成为这样一个关系：

即雇员的deptno=部门的deptno

因为不同的表可能会存在相同名称的列名，此时可以在列名前加上表名称：表名.列名。

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

这样就变为了12行且雇员的部门编号也消除，但是以上代码只是消除了显示的笛卡尔积，而在数据库的多表查询之中，笛卡尔积会一直存在，只要是存在数据表那么会存在笛卡尔积。而且一般在设计表的时候往往同样的列名称会成为关联字段。

例：

观察多表查询带来的问题：

在Oracle数据库里的sh用户下：

select * from sales,costs where sales.prod_id=costs.prod_id;

执行了的话会发现运行时间十分的长，因为从750亿条数据中筛选了100万条。

多表查询性能是很差的，在开发过程之中一定要尽可能回避。但是在进行列访问的时候使用了表名称，这样也有不好的地方，万一表名称很长呢？这时候建议使用别名。

例：使用别名

SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;

任何情况下，如果要实现多表查询操作，永远都有一个前提：要关联的数据表一定要存在关联字段或者关联条件，不满足这些要求的不能使用多表查查询。

例：显示每个雇员的编号，姓名，职位，部门名称和部门位置；

SELECT e.empno,e.ename,e.job,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno;

下面看一个三表查询的操作：

查询每个雇员的编号，姓名，职位，工资,工资等级，部门名称：

首先确定需要用到的表：

emp，dept，salgrade

再确定关联条件：

emp.deptno = dept.deptno;

emp.sal BETWEEN salgrade.losal AND salgrade.hisal;

一般情况下，多个消除笛卡尔积的条件都使用and进行里连接。

SELECT e.empno,e.ename,e.job,e.sal,s.grade,d.dname FROM emp e,dept d,salgrade s WHERE e.deptno=d.deptno and e.sal between s.losal and s.hisal;

多表查询操作的注意事项：

1.没有关联字段的两张数据表是永远不可能实现多表查询操作的。

2.在进行多表查询的复杂查询操作的时候，建议分步骤分析，分布解决问题。

表的连接

既然可以连接多张数据表进行查询，那么在进行表连接的时候需要设置一个数据主控方，对于这样的连接主要分为两种形式：

1.内连接(等值连接)：所有满足条件的数据都会被显示出来；

2.外连接(左外连接、右外连接、全外连接)：控制左表与右表的数据是否全部显示。

为了描述现在的问题，下面向emp表里加入一行数据：

INSERT INTO emp (empno,ename,job) VALUES (8888,’李晔’,’CLERK’);

1.内连接：

之前使用的都是等值连接。

若条件子句为：WHERE emp.deptno=dept.deptno, 那么只有当这个条件满足之后才会显示出对应的数据，如果此时数据为空，那么条件无法满足，内容无法显示。这时候想显示出李晔这个内容该怎么办呢？

2.外连接：

让等值判断左右两边有一边的数据可以完全显示出来，如果要实现外连接则要使用(+)的语法完成。

左外连接：字段=字段(+)；

右外连接：字段(+)=字段；

这是一个左外连接：

SELECT *FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO(+);

可以看到：李晔的信息即使没有部门编号也被显示了出来。

这是一个右外连接：

SELECT *FROM EMP E,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO;

可以看到虽然没有雇员是部门40的，但是40部门的信息还是显示了。

在使用外连接的过程中，不要刻意区分左右连接，一切以查询结果为主，如果等值查询中缺少了内容则使用外连接。

例：

查询雇员的姓名和领导姓名。

分析：需要emp表1，emp表2.

关联字段：雇员的mgr=雇员的empno。

select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;

发现一个问题:数据显示不完整：李晔和KING的数据不完整。

改进： select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno(+);

以上例题的这种自己关联自己的查询也称为自身关联，但是不管是不是自身关联，程序认的是from后存在几张表，只要是多张表那么就得消除笛卡尔积。

(+)标记是Oracle才有的，其他数据库用什么呢？所以可以利用SQL1999语法来实现查询。

整个SQL1999语法的多表查询语法如下：

SELECT [DISTINCT] * |列[别名]

FROM 表名称1

[CROSS JOIN 表名称2]

[NATURAL JOIN 表名称2]

[JOIN 表名称 ON(条件)|USING (字段)]

[LEFT|RIGHT|FUL OUTER JOIN 表名称2];

以上语法由多个部分组成，下面拆分来观察。

1.交叉连接：CROSS JOIN,主要的功能是产生笛卡尔积，简单的实现多表查询；

2.自然连接：自动使用关联字段消除笛卡尔积(一般的关联字段是外键，此处以，名字相同为主，属于内连接的概念)，默认情况下会将关联字段设置在第一列上，重复的内容不再显示。

3.USING子句：若一张表里有多个关联字段，那么可以使用USING来明确的设置一个关联字段。

SELECT * FROM emp JOIN dept USING(deptno);

4.ON子句：若没有关联字段，则可以使用ON子句设置条件：

SELECT * FROM emp JOIN salgrade ON (emp.sal BETWEEN salgrade.losal AND salgrade.hisal);

5.外链接：在SQL1999里明确定义了左外连接，右外连接，全外连接

例：左/右外连接：

SELECT * FROM emp LEFT OUTER JOIN dept USING(deptno);

SELECT * FROM emp RIGHT OUTER JOIN dept USING(deptno);

全外连接:

SELECT * FROM emp FULL OUTER JOIN dept USING(deptno);

只有在SQL1999里才明确的可以使用全外连接，但是这样的操作几乎是没有意义的。

数据集合操作

之前都属于多张数据表的关联操作，但是数据的集合操作主要负责连接的是查询结果，提供有4种操作符：UNION, UNION ALL, INTERSECT,MINUS利用这几个符号可以实现多个查询语句的连接。

格式如下：

SELECT * …

FROM …

WHERE…

ORDER BY…

UNION|UNION ALL|INTERSECT|MINUS

SELECT * …

FROM …

WHERE…

ORDER BY…

UNION|UNION ALL|INTERSECT|MINUS

SELECT * …

FROM …

WHERE…

ORDER BY…

UNION|UNION ALL|INTERSECT|MINUS

…

相当于把多个查询结果连接为一个查询结果返回，那么也就有一个要求：多个查询结果返回的列的结果必须相同。

UNION

例：

SELECT * FROM emp WHERE deptno=10 UNION SELECT *FROM emp;

此时将两个查询结果合并在了一起，但是union的操作特点是如果遇见了相同的内容不会重复显示。

UNION ALL

SELECT * FROM emp WHERE deptno=10 UNION ALL SELECT *FROM emp;

可见union的操作特点是如果遇见了相同的内容会重复显示。

INTERSECT

SELECT * FROM emp WHERE deptno=10 INTERSECT SELECT *FROM emp;

此处返回了相同的数据部分属于交集

MINUS

SELECT * FROM emp MINUS SELECT *FROM emp WHERE deptno=10;、

经过分析可知：返回的结果是差集，利用第一个集合-第二个集合。

小结：

只要是多表查询就一定会存在笛卡尔积，所以优秀的系统设计绝对不可能取去考虑多表查询。

多表连接查询时，必须存在关联字段。

大部分使用的是内连接，Oracle提供的外链接控制是(+)。

在Oracle外的数据库必须利用SQL1999语法实现外连接操作

可以使用集合操作将多个查询结果合并到一起显示，但是要求返回的结构必须相同。

标签：多表,数据库,查询,emp,Oracle,deptno,连接,SELECT

来源： https://blog.csdn.net/qq_44753180/article/details/90521463