Oracle数据库从入门到精通 多表查询知识以及范例

时间:2021-10-20 12:36:04

视频课程:李兴华 Oracle从入门到精通视频课程

学习者:阳光罗诺

视频来源:51CTO学院

总体内容:

  1. 多表查询的意义以及基本问题。
  2. 表的连接查询
  3. SQL:1999语法标准对多表查询的支持。
  4. 数据的集合操作。

一、认识多表查询

所谓的多表查询就是同时从多张表中取出数据并且显示的一种操作。语法只是做了一些简单的修改。

语法格式:

 【③选出所需要的数据列】SELECT [DISTINCT] *  列[别名],列[别名],列[别名]······

 【①确定数据来源(行和列的集合)】FROM 表名称 [别名],表名称 [别名],······

 【②筛选数据行】[WHERE 限定条件]  此时的条件可以是多个语法结构。

 【④数据排序】[ORDER BY 排序字段 [ASC|DESC] 可以设置多个]

于是我们就可以按照这样子的语法结构来实现多表查询。本次将会使用emp和dept两张表格进行多表的查询。于是在查询之前,先做一些准备,在数据库中有一个COUNT()函数,这个函数的主要作用:可以统计出一张数据表中的数据量。

准备查询一:查询dept表中的数据量。(4行记录)

代码示例:

 select count(*) from dept;

Oracle数据库从入门到精通  多表查询知识以及范例

准备查询二:查询emp表中的数据量。(14行记录)

代码示例:

 select count(*) from emp;

Oracle数据库从入门到精通  多表查询知识以及范例

目前两张表的记录加起来,总记录是18行。根据语法格式可以实现多表的查询。

范例:实现emp与dept的多表查询。

Oracle数据库从入门到精通  多表查询知识以及范例

发现在每一行emp表中的记录出现了4次,而4次是dept表中的数据量,所以最终产生了14行X4行=56行记录。

Oracle数据库从入门到精通  多表查询知识以及范例

之所以出现这样的情况,主要用于数据库的产生原理有关———数学的集合。在这样子的集合操作里面,我们会将这两个集合(数据表)统一查询,作为乘法的形式出现。结果一定会产生积————笛卡儿积。在任何情况下要进行的多表查询中都一定会存在有笛卡儿积的问题。事实上,这些积的产生对用户而言是没有实质上的用处,需要想办法去消除积。

如果要消除积,就必须有关联字段。

很明显,现在emp和dept数据表之中存在有关联字段(大部分情况下,都习惯将关联字段设置为同名)。我们就可以使用关联字段来消除笛卡儿积。

代码示例:

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

Oracle数据库从入门到精通  多表查询知识以及范例

可以得出结论:只要是多表查询,这多张表之间就一定要存在有关联关系。没有关联关系的表是不可能多表查询的。

但是就目前而言,代码依然是存在有问题。

此时进行字段访问的时候采用的是“表名称.字段名称”,如果是表名称短的话,一般是没有什么问题的。而如果表名称长了,例如:“yuzhou_yinghexi_diqiu_yazhoudalu_shanghai_ren”,所以在进行多表查询的时候,强烈建议使用别名。

实际上笛卡儿积的存在对于整个程序的影响是相当巨大的,即使现在可以消除显示的笛卡儿积,但是从本质上来讲,永远无法避免笛卡儿积。

例如:在Oracle的样本数据中有sh的大数据用户。

分析一:取得costs表中的记录数。

SELECT COUNT(*) FROM costs;

多表查询操作案例(分析过程)

范例:要求查询出每一个雇员的编号、姓名、职位、基本工资、部门名称、部门位置。

·确定要使用的数据表

- emp表:雇员的编号、姓名、职位、基本工资

- dept表:部门名称、部门位置

·确定已知的关联字段

-        雇员与部门关联:emp.deptno = dept.deptno

第一步:查询出每一个雇员的编号、姓名、职位、基本工资。现在只需要使用emp一张数据即可。

代码示例:

 select e.empno,e.ename,e.job,e.sal from emp e;

Oracle数据库从入门到精通  多表查询知识以及范例

第二步:查询出每一个雇员对应的部门信息。需要引入dept表(引入表的时候一定要考虑有关联),这两张表直接可以利用dept表deptno字段关联,所以需要利用WHERE子句来消除笛卡儿积。

代码示例:

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

Oracle数据库从入门到精通  多表查询知识以及范例

以上的操作属于之前基本概念的加强,并且给出了明确的关联字段。可是很多的查询是不会明确给出关联字段的。

范例:要求查询出每一个雇员的编号、姓名、职位、基本工资、工资等级。

Oracle数据库从入门到精通  多表查询知识以及范例

      -  确定要使用的数据表。

          emp表:雇员的编号、姓名、职位、基本工资

          salgrade表:工资等级

      -  确定已知的关联字段。

          雇员与工资等级:emp.sal BETWEEN salgrade.losal AND salgrade.hisal;

第一步:查询出每一个雇员的编号、姓名、职位、基本工资。现在只需要使用emp一张数据即可。

代码示例:

 select e.empno,e.ename,e.job,e.sal from emp e;

Oracle数据库从入门到精通  多表查询知识以及范例

第二步:增加salgrade表,增加了数据表之后,就需要引入WHERE子句来消除掉笛卡儿积。

代码示例:

 select e.empno,e.ename,e.job,e.sal,s.grade

 from emp e,salgrade s

 WHERE e.sal BETWEEN s.losal AND s.hisal;

Oracle数据库从入门到精通  多表查询知识以及范例

范例:查询每一个雇员的编号、姓名、职位、基本工资、部门名称、工资等级。

    确定所需要的数据表:

      - emp表:编号、姓名、职位、基本工资

      - dept表:部门名称

      - salgrade表:工资等级

    确定相互关联的字段:

      - 雇员与部门:emp.deptno = dept.deptno;

      - 雇员与工资等级:emp.sal BETWEEN salgrade.losal AND salgrade.hisal;

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

代码示例:

 select e.empno,e.ename,e.job,e.sal

 from emp e;

Oracle数据库从入门到精通  多表查询知识以及范例

第二步:加入部门名称,增加一张表就增加阳光条件就消除笛卡儿积。

代码示例;

 select e.empno,e.ename,e.job,e.sal,d.dname

 from emp e,dept d

 WHERE e.deptno= d.deptno;

Oracle数据库从入门到精通  多表查询知识以及范例

第三步:加入工资等级信息,与原始的消除笛卡儿积条件应该同时满足,所以使用AND 来连接。

示例代码:

 select e.empno,e.ename,e.job,e.sal,d.dname

 from emp e,dept d,salgrade s

 WHERE e.deptno= d.deptno and e.sal BETWEEN s.losal AND s.hisal;

Oracle数据库从入门到精通  多表查询知识以及范例

表的连接

实际上对于两张数据表进行多表查询,对于消除笛卡儿积来讲主要是依靠连接模式来处理的,而对于表的连接模式在数据库的定义上,有两种:

l  内连接:在之前都利用WHERE子句消除了笛卡儿积,这就属于内连接。

l  外连接:分为三种:左外连接、右外连接、全外连接。

为了方便更好的观察到连接的区别,现在已经在dept表中提供了一个没有雇员的部门(40部门),同时在emp表中增加一个没有部门的雇员。

代码示例:

观察1:内连接的实现效果。

Oracle数据库从入门到精通  多表查询知识以及范例

观察2:使用左(外)连接。希望所有的雇员信息都显示出来。即使没有对应的部门。

代码示例:

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

 FROM emp e,dept d

 WHERE e.deptno=d.deptno(+);

Oracle数据库从入门到精通  多表查询知识以及范例

此时没有部门的雇员信息出现了,也就是说左表的数据全部显示了。

观察3:使用右(外)连接。将所有的部门信息显示出来。

代码示例:

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

 FROM emp e, dept d

 WHERE e.deptno(+) = d.deptno;

Oracle数据库从入门到精通  多表查询知识以及范例

内连接就是所有满足关联关系的数据出现,不满足的不出现,外连接就是指定一张数据表中的内容全部都显示,但是没有对应的其他的表的数据内容位null。

         在Oracle中使用了“(+)”来控制连接方式。

                          左外连接:关联字段1=关联字段2(+);

                          右外连接:关联字段1(+)=关联字段2;

大部分情况下一般都只会考虑内连接,但是当你发现所需要的数据不全的时候就可以考虑外连接。、

范例:查询每一个雇员的编号、姓名、职位、领导姓名、领导职位。

    确定所需要的数据表。

        - emp表(雇员信息):编号、姓名、职位。

        - emp表(领导信息):领导姓名、领导职位。

    确定已知的关联字段。

        -  雇员与领导:emp.mgr = memp.empno

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

代码示例:

 SELECT e.empno,e.ename,e.job FROM emp e;

Oracle数据库从入门到精通  多表查询知识以及范例

第二步:加入领导信息。需要引入自身关联,而后消除笛卡儿积。

代码示例:

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

 FROM emp e, emp m

 WHERE e.mgr=m.empno;

Oracle数据库从入门到精通  多表查询知识以及范例

第三步:发现emp表(雇员信息)数据不完整,因为不满足于等值关联判断,所以要让雇员信息显示完整,则必须使用外连接控制。

Oracle数据库从入门到精通  多表查询知识以及范例

SQL1999语法定义

对于数据表的连接操作,从实际使用来讲各个数据库都是有所支持的,所以对于所有的数据库,进行表连接最好的做法是利用以下的语法可以完成:

语法:

 SELECT [DISTINCT] * | 列 [别名] ,列 [别名],······

 FROM 表1 [别名]

          [CROSS JOIN 表2 [别名]]

          [NATURE JOIN 表2 [别名]]

          [JOIN 表2 [别名] ON (条件)| USING(关联字段)]

          [LEFT | RIGHT | FULL OUTER JOIN ON(条件)表2];

个人在进行表连接的时候,如果是内连接一定使用等值判断,如果是外连接才会使用LEFT、RIGHT、OUTER等操作。

  1. 交叉连接:目的是产生笛卡儿积

语法:

 SELECT [DISTINCT] * | 列 [别名] ,列 [别名],······

 FROM 表1 [别名]  [CROSS JOIN 表2 [别名]]

范例:实现交叉连接

代码示例:SELECT  * FROM emp CROSS JOIN dept;

  1. 自然连接:利用关联字段自己进行消除笛卡儿积(只要字段名称相同即可)

语法:

 SELECT [DISTINCT] * | 列 [别名] ,列 [别名],······

 FROM 表1 [别名]  [NATURE JOIN 表2 [别名]]

范例:实现自然连接。(内连接)

代码示例:

 SELECT  * FROM emp NATURAL JOIN dept;
  1. 使用自然连接是要求两张表的字段名称相同,但是如果说有一天不相同了呢?或者两张表中中有两组字段都是重名的。所以这种使用ON子句指定关联条件,而利用USING子句设置关联字段。

范例:利用USING子句设置关联字段实现自然连接。

 SELECT  * FROM emp  JOIN dept USING(deptno);

范例:利用ON子句设置关联条件。

 SELECT  * FROM emp e JOIN dept d ON(e.deptno=d.deptno);

外连接

 SELECT [DISTINCT] * | 列 [别名] ,列 [别名],······

 FROM 表1 [别名]  [LEFT | RIGHT | FULL OUTER JOIN ON(条件)表2];

范例:观察左外连接 

 SELECT  * FROM emp e LEFT OUTER JOIN dept d  ON (e.deptno=d.deptno);

Oracle数据库从入门到精通  多表查询知识以及范例

范例:观察右外连接。

 SELECT  * FROM emp e RIGHT OUTER JOIN dept d  ON (e.deptno=d.deptno);

Oracle数据库从入门到精通  多表查询知识以及范例

范例:全外连接

代码示例:

 SELECT  * FROM emp e FULL OUTER JOIN dept d  ON (e.deptno=d.deptno);

部分截图:

Oracle数据库从入门到精通  多表查询知识以及范例

Oracle数据库从入门到精通  多表查询知识以及范例

数据集合操作

数学集合:交集、并集、补集。

每一次查询实际上都会返回数据集合,所以返回的结果可以使用UNION、UNION ALL、MINUS、INTERSECT来实现集合的操作。

 SELECT [DISTINCT] *  列[别名],列[别名],列[别名]······

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

 [WHERE 限定条件]  此时的条件可以是多个语法结构。

 [ORDER BY 排序字段 [ASC|DESC] 可以设置多个]

          UNION | UNION ALL | INTERSECT | MINUS

 SELECT [DISTINCT] *  列[别名],列[别名],列[别名]······

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

 [WHERE 限定条件]  此时的条件可以是多个语法结构。

 [ORDER BY 排序字段 [ASC|DESC] 可以设置多个]