Oracle 数据库基于SCOTT模式的分组技术和子查询

水深无声 2022-07-03 13:28 156阅读 0赞

1.在SELECT 语句中，可以使用GROUP BY子句进行分组操作，并且可以使用HAVING子句提供分组条件

\---->下面的查询将对SCOTT模式中的EMP表进行分组，以统计各部门的员工人数

select deptno,count(*) as 员工数量 from emp group by deptno;

![Center][]

\------>HAVING子句通常与GROUP BY子句一起使用，以便在完成分组后可以使用HAVING子句对分组的结果进行进一步的筛选。如果不适用GROUP BY子句，HAVING 子句的功能与WHERE 子句一样，对整个表进行筛选。（下面的查询将搜索出所有员工数量大于等于5的部门）

select deptno,count(*) as 员工数量 from emp group by deptno having count(*)
    >=5;

![Center 1][]

\----->理解HAVING子句的方法，就是记住SELECT 语句中的各子句的执行次序。在SELECT语句中，首先执行FROM子句找到表，而WHERE子句则在FROM子句输出的数据中进行筛选，HAVING子句则在GROUP BY，WHERE或FROM子句执行后对结果进行筛选。（下面我们使用两个示例分别展示HAVING和WHERE子句对SELECT语句的影响。第一个检索语句使用HAVING子句对分组的结果进行筛选；第二个检索语句则使用WHERE子句对表中的数据进行筛选后再分组）

select job,avg(sal) as 平均值,max(sal) as 最大值,count(*) as 职工人数 from
    emp group by job having avg(sal)>1000;

![Center 2][]

SQL> select job,avg(sal) as 平均值,max(sal) as 最大值,count(*) as 职工人数 from
    emp where sal>1000 group by job;

![Center 3][]

\----->从查询结果可以看出，WHERE子句直接从表中筛选数据过滤掉了部分员工信息；而HAVING子句则在分组后进行筛选。

2.子查询

2.1 单行子查询

在单行子查询中，该内查询只返回单行单列值，因此可以把这种子查询作为一个常量。在WHERE子句中，可以使用单行比较运算符来比较某个表达式与子查询的结果。可以使用的单行比较运算符包括：等于"=",大于">",大于或等于">=",小于"<",小于或等于"<=",和不等于"<>/!="（如下例子，在子查询中使用统计函数，从EMP表中得到工资最低和工资最高的员工信息）

SQL> select empno,ename,job,sal from emp where sal>=(select max(sal) from emp)
    or sal<=(select min(sal) from emp);

![Center 4][]

\------>红线表示工资最低，绿线表示工资最高的员工

\------>在执行子查询的过程中，如果内查询的结果是空值，那么外查询的条件始终不会满足，该查询的最终结果也是空值。

SQL> select empno,ename,job,sal from emp where deptno=(select deptno from dept
    where dname='MANAGER');

![Center 5][]

2.2 多行子查询

多行子查询可以返回单列多行数据。在这种多行子查询中，必须使用多行运算符来判断，而不能使用单行运算符。使用多行运算符可以执行与一个或多个数据的比较操作。在Oracle系统中，可以使用的多行比较运算符包括：IN（等于列表中的任何一值），ANY(与子查询返回的任何一值进行比较) 和ALL（与子查询返回的所有值进行比较）。

ANY运算符表示与子查询中的任何一个值进行比较。这时，需要将单行比较运算符与该运算符组合起来使用。

\----->例：在子查询的比较条件中，使用>any运算符查询大于MANAGER职位中最小薪金的员工信息。

SQL> select empno,ename,job,sal from emp where sal>any(select sal from emp where
     job='MANAGER');

![Center 6][]

\----->使用IN运算符的多行子查询，用于查询属于ACCOUNTING和RESEARCH部门的员工信息

SQL> select empno,ename,job,sal,deptno from emp where deptno in (select deptno
    from dept where dname='ACCOUNTING' or dname='RESEARCH');

![Center 7][]

\----->使用ALL运算符的多行子查询，用于查询薪金大于所有MANAGER职位的员工信息

SQL> select empno,ename,job,sal from emp where sal>all(select sal from emp where
     job='MANAGER');

![Center 8][]

2.3 关联子查询

在前面两种子查询中，内查询和外查询是分开执行的，即内查询的执行和外查询的执行是没有关系的，外查询仅仅是使用了内查询的最终结果。

但如果在子查询语句中，内查询的执行需要借助于外查询，而外查询的执行又离不开内查询的执行。这时内查询和外查询是相互关联的，这种子查询称为关联子查询。

需要注意的是，关联子查询是对同一个表进行查询操作，所以内层子查询返回的记录都是外层查询所操作的候选对象，所以当数据量较大时，会导致查询效率低下。

\------>现在使用关联子查询检索某个职位的员工薪金是否超出了平均水平

SQL> select ename,job,sal from emp t where sal> (select avg(sal) from emp where
    t.job=job) order by job;

![Center 9][]

[Center]: /images/20220618/54469cc1d61e43cea2bebe1ff42c9498.png
[Center 1]: /images/20220618/2f766423e798499c9dea8031313f7c67.png
[Center 2]: /images/20220618/f249e9dd7d6a41a38de6925a5f2be690.png
[Center 3]: /images/20220618/c8ae0120c3ec4ff0b8ecd7f11629a331.png
[Center 4]: /images/20220618/d6af828eb51d48b9abc1df3e0afa7345.png
[Center 5]: /images/20220618/0f9b1429f15d474e8f4e04364db84dcc.png
[Center 6]: /images/20220618/5afd076d546444e986847bd496cefb34.png
[Center 7]: /images/20220618/ef944b9848b840ae85722b85508c9029.png
[Center 8]: /images/20220618/a2c7853f9cec4f35b136ad4414215781.png
[Center 9]: /images/20220618/a530922f4913429087eb853c58d01c5b.png