Oracle学习记录——4.Oracle的进阶查询

朱雀 2023-06-14 13:55 194阅读 0赞

### 文章目录 ###

*  1.多表查询
 *   *  多表查询的基本概念
     *  左、右连接
     *  SQL1999语法对SQL的支持
 *  2.统计函数及分组查询
 *   *  统计函数
     *  分组查询
 *  3.子查询
 *  4.事务处理
 *  5.数据伪列
 *   *  ROWNUM
     *  ROWID

# 1.多表查询 #

## 多表查询的基本概念 ##

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

SELECT [DISTINCT] * |列 [别名]，……
    FROM 表名称[别名]，[表名称[别名]，……]
    [WHERE 条件(s)]
    [ORDER BY 排序字段 ASC|DESC] [,排序字段 ASC|DESC,……]]

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

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

有两种做法：

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

SELECT * FROM 表名称

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

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

SELECT COUNT(*) FROM 表名称

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

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

Select * from emp,dept;

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTUxNjU5_size_16_color_FFFFFF_t_70]

本问题在数据库的操作之中成为笛卡儿积，就表示多张表的数据乘积，但是这种查询结果不是我们希望的，怎么去掉笛卡尔积呢？

最简单的形式是采用**关联字段**的形式，emp 表和 dept 表之间现在存在了deptno 的关联字段。

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

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

开发过程中，不到万不得已不会用多表查询。

数据量和性能成正比。

但是此时又存在一个新的问题，如果现在表名称过长的话，写起来不方便，所以在使用的时候一般会为表其别名。

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

*  确定所需要的数据表
 *  确定关联字段

## 左、右连接 ##

关于左、右连接指的是查询判断条件的参考方向，例如：下面有如下查询：

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

部门一共有四个，但是现在只返回了三个部门的信息，缺少４０部门，因为在雇员表中没有一条记录属于４０部门的，所以现在不会显示４０部门的信息，即：现在的查询是以ｅ ｍｐ表为参考，那么如果说现在非要显示４０部门呢？就必须改变这种参考的方向，就需要使用左、右连接，如下所示：

SELECT * FROM emp e right on dept d on e.deptno = d.deptno;

可以发现，此时４０部门已经出现，所以，此时就使用到了右连接，证明以下的规律：

*  （＋）在＝左边，表示左连接
 *  （＋）在＝右边，表示右连接

但是不用刻意的区分是左还是右，只是根据查询结果而定，如果发现有些需要的数据没有显示出来，就使用此符号更改连接方向。

例1：要求查询出每一位雇员的姓名、职位、部门的名字，地点。

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

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

select e1.empno,e1.ename,e1.job,e2.ename,d.dname,d.loc from emp e1 left join emp e2 on e1.mgr=e2.empno right join dept d on e1.deptno=d.deptno;

## SQL1999语法对SQL的支持 ##

SQL:1999 语法格式如下：

以上实际上是属于多个语法的联合，下面分块儿说明语法的使用

1.  交叉连接（CROSS JOIN）：用于产生笛卡尔积
2.  自然连接（NATURAL JOIN）:自动找到匹配的关键字段，消除笛卡尔积
3.  JOIN … USING 子句：用户自己指定一个消除笛卡尔积的关键字段
4.  JOIN …ON 子句：用户自己指定一个可以消除笛卡尔积的关联条件
5.  连接方向的改变：
    
     *  左（外）连接：LEFT OUTER JOIN … ON;
     *  右（外）连接：RIGHT OUTER JOIN … ON;
     *  全（外）连接：FULL OUTER JOIN … ON; ->把两张表中没有显示的数据都显示在 Oracle 之外的数据库都使用以上的 SQL:1999 语法操作，所以这个语法还必须会。

再次强调：多表查询的性能肯定不高，而且性能一定要在大数据量的情况下才能被发现。

# 2.统计函数及分组查询 #

## 统计函数 ##

在之前学习过一个 COUNT 函数，此函数的功能可以统计出表中的数据量，实际上这就是一个统 计函数，而常用的统计函数有如下几个：

● COUNT( )：查询表中的数据记录；

● AVG( )：求出平均值；

● SUM( )：求和；

● MAX()：求出最大值；

● MIN()：求出最小值；

另外，最大、最小支持字符串（**计算首字母的ASCII值**）

示例：统计出公司的所有员工，每个月支付的平均工资及总工资

select sum(sal),avg(sal) from emp;

**注意点：关于 COUNT（）函数**

COUNT( ) 函数的主要功能是进行数据统计，但是在进行数据统计的时候，如果一张表中没有统计记 录，COUNT()也会返回数据，只是这个数据是“0”。

如果使用的是其它函数，则有可能返回的是 null，但是 COUNT()永远都会返回一个具体的数字， 这一点以后在开发之中都会使用到。

## 分组查询 ##

在讲解分组操作之前首先必须明确一点，什么情况下可能分组，例如：

*  公司的所有雇员，要求男性一组，女性一组，之后可以统计男性和女性的数量；
 *  按照年龄分组，18 岁以上的分一组，18 岁以下的分一组；
 *  按照地区分组：西安一组，延安一组，汉中一组等等；

这些信息如果都保存在了数据库之中，肯定在数据的某一列上会存在重复的内容，例如：按照性 别分组的时候，性别肯定有重复（男和女），按照年龄分组（在一个范围的重复），按照地区分组（在 一个地区的信息重复） 所以分组之中有一个不成文的规定：当数据重复的时候分组才有意义，因为一个人也可以分组（没 什么实际意义），而如果要想分组，则需要使用 GROUP BY 子句完成，语法格式如下：

SELECT [DISTINCT] * | 分组字段 1 [别名][，分组字段 2 [别名]，…] | 统计函数 
    FROM 表名称[别名]，[表名称[别名]，……] 
    [WHERE 条件(s)] 
    [GROUP BY 分组字段 1 [,分组字段 2,…]] 
    [ORDER BY 排序字段 ASC|DESC] [,排序字段 ASC|DESC,……]]

例1：按照部门编号分组，求出每个部门的人数，平均工资

SELECT deptno,count(deptno),avg(sal) from emp group by deptno;

**注意**

*  一旦分组之后，实际上对于语法上就会出现了新的限制，对于分组有以下要求： 分组函数单独使用： SELECT COUNT(empno) FROM emp; 错误的使用，出现了其他的字段：SELECT empno, COUNT(empno) FROM emp;
 *  如果现在要进行分组的话，则 SELECT 子句之后，只能出现分组的字段和统计函数， 其他字段不能出现。
 *  分组函数允许嵌套，但是嵌套之后的分组函数的查询之中不能再出现任何的其他字段。

例2：查询出每个部门的名称、部门的人数、平均工资

SELECT emp.deptno,count(emp.deptno),avg(sal),d.dname from emp right join dept d on emp.deptno = d.deptno group by emp.deptno,d.dname;

以上就是多字段分组，但是不管是单字段还是多字段，一定要有一个前提，存在了重复数。

例3：要求统计出每个部门的详细信息，并且要求这些部门的平均工资高于 2000

SELECT emp.deptno,count(emp.deptno),avg(sal),d.dname from emp right join dept d on emp.deptno = d.deptno group by emp.deptno,d.dname where avg(sal)>2000;

在以上的基础上完成开发，在之前唯一所学习的限定查询的语法只有 WHERE 子句，所以若使用 WHERE 完成要求。会出现错误。

本错误的核心意思就是在 WHERE 子句中不能使用统计函数，之所以在 WHERE 子句中 不能使用，实际上跟 WHERE 子句的主要功能有关，WHERE 的主要功能是从全部的数据之中 取出部分的数据。

此时如果要对分组后的数据再次进行过滤，则使用 HAVING 子句完成，那么此时的 SQL 语法格式如下：

SELECT [DISTINCT] * | 分组字段 1 [别名][，分组字段 2 [别名]，…] | 统计函数 
    FROM 表名称[别名]，[表名称[别名]，……] 
    [WHERE 条件(s)] 
    [GROUP BY 分组字段 1 [,分组字段 2,…]] 
    [HAVING 分组后的过滤条件（可以使用统计函数）] 
    [ORDER BY 排序字段 ASC|DESC] [,排序字段 ASC|DESC,……]]

修改后的例3如下：

SELECT emp.deptno,count(emp.deptno),avg(sal),d.dname from emp right join dept d on emp.deptno = d.deptno group by emp.deptno,d.dname habing avg(sal)>2000;

**注意点： WHERE 和 HAVING 的区别**

*  WHERE：是在执行 GROUP BY 操作之前进行的过滤，表示从全部数据之中筛选 出部分的数据，在 WHERE 之中不能使用统计函数。
 *  HAVING：是在 GROUP BY 分组之后的再次过滤，可以在 HAVING 子句中使用统计 函数。

# 3.子查询 #

子查询 = 简单查询+限定查询+多表查询+统计查询的综合体；

在之前强调过多表查询不建议大家使用，因为性能很差，但是多表查询最有利的替代者 就是子查询，所以子查询在实际的开发之中使用的相当的多。

所谓的子查询指的就是在一个查询之中潜逃了其他的若干查询，嵌套子查询之后的查询

语句如下：

SELECT [DISTINCT] * | 分组字段 1 [别名][，分组字段 2 [别名]，…] | 统计函数 
    ) 
    
    FROM 表名称[别名]，[表名称[别名]，……],( 
    
    SELECT [DISTINCT] * | 分组字段 1 [别名][，分组字段 2 [别名]，…] | 统计函数， 
    FROM 表名称[别名]，[表名称[别名]，……] 
    [WHERE 条件(s)] 
    [GROUP BY 分组字段 1 [,分组字段 2,…]] 
    [HAVING 分组后的过滤条件（可以使用统计函数）] 
    [ORDER BY 排序字段 ASC|DESC] [,排序字段 ASC|DESC,……]] 
    ) 
    [WHERE 条件(s),( 
    SELECT [DISTINCT] | 分组字段 1 [别名][，分组字段 2 [别名]，…] | 统计函数， 
    FROM 表名称[别名]，[表名称[别名]，……] 
    [WHERE 条件(s)] 
    [GROUP BY 分组字段 1 [,分组字段 2,…]] 
    [HAVING 分组后的过滤条件（可以使用统计函数）] 
    [ORDER BY 排序字段 ASC|DESC] [,排序字段 ASC|DESC,……]] 
    )] 
    [GROUP BY 分组字段 1 [,分组字段 2,…]] 
    [HAVING 分组后的过滤条件（可以使用统计函数）] 
    [ORDER BY 排序字段 ASC|DESC] [,排序字段 ASC|DESC,……]]

理论上子查询可以出现在查询语句的任意位置上，但是从个人而言，子查询出现自WHERE 和 FROM 子句之中比较多；个人总结如下：

*  WHERE：子查询一般只返回单行单列、多行单列、单行多列的数据
 *  FROM：子查询返回的一般是多行多列的数据，当作一张临时表出现。

例1：要求查询出工资比 SMITH 还要高的全部雇员信息。

SELECT * FROM emp where sal > (SELECT sal FROM emp WHERE ename = 'SMITH');

以上所返回的是单行单列，但是在子查询之中，也可以返回单行多列的数据，只是这种 子查询很少出现。

如果现在的子查询返回的是多行单列数据的话，这个时候就需要使用三种判断符判断 了：IN、ANY、ALL；

1.  **IN 操作符**：用于指定一个子查询的判断范围

这个操作符的使用实际上与之前讲解的 IN 是一样的，唯一不同的是，里边的范围由子查询指定了。

但是在使用 IN 的时候还要注意 NOT IN 的问题，如果使用 NOT IN 操作，在子查询之中， 如果有一个内容是 null，则不会查询出任何的结果

1.  **ANY 操作符**：与每一个内容相匹配，有三种匹配形式
    
    1.  =ANY: 功能与 IN 操作符是完全一样的；
    2.  .>ANY: 比子查询中返回记录最小的还要大的数据； 实际any进行最小值操作
    3.  <ANY: 比子查询中返回记录的最大的还要小；实际any进行最大值操作
2.  **ALL 操作符**：与每一个内容相匹配，有两种匹配形式；
    
    1.  .>ALL：比子查询中返回的最大的记录还要大 ，最大值操作
    2.  <ALL：比子查询中返回的最小的记录还要小 ，最小值操作

以上的所有子查询都是在 WHERE 子句中出现的，那么下面再来观察在 FROM 子句中出现的子查询，这个子查询一般返回的是多行多列的数据，当做一张临时表的方式来处理的。

例：查询出每个部门的编号、名称、位置、部门人数、平均工资

**分析**

*  回顾：最早的时候使用的是多字段分组统计查询；
 *  新的解决方案：通过子查询完成，所有的统计查询只能在 GROUP BY 中出现，所以子 查询之中负责统计数据，而在外部的查询之中，负责将统计数据和 dept 表数据相统一。
    
    现在程序中所操作的数据量：
    
     *  子查询中统计的记录是 14 条记录，最终统计的显示的结果是 3 条记录；
     *  dept 表中一共有 4 条记录；
     *  如果现在产生笛卡尔积的话只有 12 条记录，再加上雇员的 14 条记录，一共才 26 条 记录。

通过以上的分析，可以发现，使用子查询的确要比使用多表查询更加节省性能，所以在 开发之中子查询出现时最多的，**而且再给出一个开发不成文的规定：大部分情况下，如果最终的查询结果之中需要出现 SELECT 子句，但是又不能直接使用统计函数的时候，就在子查询中统计信息，即：有复杂统计的地方大部分都需要子查询。**

# 4.事务处理 #

对于数据表的操作，很明显查询要比更新操作更加安全，因为更新操作有可能会出现错 误，以导致没有按照既定的要求正确的完成更新操作。

但是在很多的时候更新有可能会有很多指令共同完成，例如：以银行转账的方式为例：

*  判断 A 账户上是否有 1000W;
 *  判断 B 账户是否存在并且状态是否正常；
 *  从 A 账户上移走 1000W；
 *  向 B 账户之中增加 1000W；
 *  向银行支付手续费用 5000；

以上的 5 个数据操作是一个整体，可以理解为一个完整的业务，如果这之中第三点出错了，那么其他的操作呢？所有的操作都应该不再执行，并且回归到最原始的状态，而这一个操作流程就是事务的操作。

所有的事务处理操作都是针对于每一个 session 进行的，在 oracle 数据库之中，把每一 个链接到数据库上的用户都称为一个 session，每一个 session 之间彼此独立，不会有任何的 通讯，而每一个 session 独享自己的事务控制，而事务控制之中主要使用几个命令：

*  事务的回滚：ROLLBACK，更新操作回到原点；
 *  事务的提交：COMMIT，真正的发出更新的操作，一旦提交之后无法回滚；
 *  事物保存点：SAVEPOINT，保存点，用来在事物中做一些标记，可以使事物撤销到指 定的保存点。

但是这样一来也会出现一些问题，例如：某一个 session 在更新数据表的时候还没有提 交事务，其他 session 是无法跟新的，必须等待之前的 session 提交后才可以；

**所有的数据更新**一**定都会受到事务的控制。**

# 5.数据伪列 #

数据伪列指的是用户不需要处理的列，而是由 ORACLE 自行维护的数据列，在 ORACLE 之中有两个数据伪列：ROWNUM、ROWID;

## ROWNUM ##

ROWNUM 从单词的含义上讲应该表示的是行号，实际上 ROWNUM 为每一个显示的记 录都会自动的随着查询生成行号，例如，通过两个代码观测 ROWNUN 行号并不是永久固定的，是每次动态重新生成的，那么既然有了 ROWNUM 之 后，下面可以实现数据的部分显示。

**注意：ROWNUM不能使用>号！**

例1：查询前 5 条记录

select * from emp where rownum <=5

例2：查询 6-10 条记录

正常使用 BETWEEN…AND…

是不能返回任何数据的，因为 ROWNUM 不是真实列，不支持大于号，而想要真正的 实现这种查询的思路是：先查询前 10 条记录，之后再显示后 5 条记录，要依靠子查询完成 ：

select * from (select rownum as rm,e.* from emp e where rownum<=10) b where b.rm > 5;

**分页**

*  第一页

当前所在的页（currentPage）为：１；

每页显示的记录长度（lineSize）为 5；

*  第二页

当前所在的页（currentPage）为：2；

每页显示的记录长度（lineSize）为 5；

*  第三页

当前所在的页（currentPage）为：3；

每页显示的记录长度（lineSize）为 5；

*  由此推断，可以得出相应公式

StartNum(起始页) = (pageNo - 1) \* pageSize

endNum(结束页) = (pageNo \* pageSize) + 1

*  万能分页公式如下：

select * from (select rownum as rm,e.* from emp e where rownum<to_number('&endNum')) b where b.rm > to_number('&startNum');

## ROWID ##

ROWID 表示的是每一行数据保存的物理地址的编号，如观察如下的查询：

SELECT ROWID,deptno,dname,loc FROM dept;

每一条记录的 ROWID 都不会重复，所以即便表中所有列的数据内容都重复了，ROWID 也是不会重复的。

ROWID组成：

*  前六位：数据对象号
 *  紧接着的三位：相对文件号
 *  紧接着的六位：数据块号
 *  最后三位：数据行号

小结，在所有的伪列之中，只有 ROWNUM 是以后开发之中最为重要的部分，一定要掌 握，而对于 ROWID 知道就行了，不必做太深入的了解。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTUxNjU5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191122160049225.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTUxNjU5,size_16,color_FFFFFF,t_70