Oracle 学习(3)

2023-12-27 06:30:07

分组函数

多行函数也叫组函数,本章学习目标:

  1. 了解组函数。
  2. 描述组函数的用途。
  3. 使用GROUP BY 子句数据分组。
  4. 使用HAVING 子句过滤分组结果集。

分组函数作用于一组数据,并对一组数据返回一个值。如:AVG、COUNT、MAX、MIN、SUM操作的是一组数据,返回一个结果。

求员工的工资总额:

SQL> ?select sum(sal) from emp; ? sum() 对指定列的各行求和。

员工人数:

SQL> ?select count(*) from emp; ? count() 统计指定列的非空行数。

平均工资:

SQL> select sum(sal)/count(*) 方式一, avg(sal) 方式二 ?from emp; ?

方式一和方式二结果一样,当有空值得时候结果有可能不一样。如:奖金。

求员工的平均奖金:

SQL> select sum(comm)/count(*) 方式一, ?sum(comm)/count(comm) 方式二, ?avg(comm) 方式三 ? ??from emp; ?

结果:方式一结果不同,方式二 和 方式三结果一样。

☆NULL空值组函数都有自动滤空功能(忽略空值),所以:

SQL> select count(*), count(comm) from emp; ? 执行结果不相同。

如何屏蔽 组函数 的滤空功能:

SQL> select count(*), count(nvl(comm,0)) from emp; ?

但是实际应用中,结果为14和结果为4都有可能对,看问题本身是否要求统计空值。

count函数:求个数,如果要求不重复的个数,使用distinct。

求emp表中的工种:

SQL> select count(distinct job) from emp; ? ?

分组数据 ???????????????????????????????????????????????????????????????????

group by ?????

按照group by 后给定的表达式,将from后面的table进行分组。针对每一组,使用组函数。

查询“部门”的平均工资:

分析:结合select * from emp order?by deptno ??结果分析分组

SQL> select ?deptno, ?avg(sal) ?from emp ?group by deptno; ?

上SQL语句可以抽象成:select a, 组函数(x) from 表 group by a; ?这样的格式。

如果select a, b 组函数(x) …… group by 应该怎么写? ??

注意: SELECT 列表中所有没有包含在组函数中的列,都必须在group by的后面出现。所以上问应该写成group by a, b;没有b语法就会出错,不会执行SQL语句。但,反之可以。Group by a,b,c;??c可以不出现在select语句中。 ???

group by后面有多列的情况:

SQL> select deptno, job, avg(sal) ?from emp ?group by deptno, job ?order by 1; ???

分析该SQL的作用:

因为deptno, job 两列没有在组函数里面,所以必须同时在group by后面。

该SQL的语义:按部门,不同的职位统计平均工资。先按第一列分组,如果第一列相同,再按第二列分组。

??所以查询结果中,同一部门中没有重复的职位。

常见的非法使用组函数的情况,主要出现在缺少group?by 子句。如hr用户下执行查询语句:

SELECT department_id, COUNT(last_name)

FROM ?employees;

会显示如下错误:

SELECT department_id, COUNT(last_name)

???????*

ERROR at line 1:

ORA-00937: not a single-group group function

意为:GROUP BY 子句中缺少列

? ?

Having

使用 HAVING 过滤分组:

1. 行已经被分组。

2. 使用了组函数。

3. 满足HAVING 子句中条件的分组将被显示。

其语法:

SELECT column, group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column];

查询平均薪水大于2000的部门 :

分析:该问题实际上是在分组的基础上过滤分组。

SQL> select deptno, avg(sal) ?from emp ?group by deptno ?having avg(sal)>2000;??------注意:having后面不能使用别名

  1. 不能在 WHERE 子句中使用组函数(注意)。
  2. 可以在 HAVING 子句中使用组函数。

从功能上讲,where和having都是将满足条件的结果进行过滤。但是差别是where子句中不能使用 组函数!所以上句中的having不可以使用where代替。

求10号 部门的平均工资:

分析:在上一条的基础上,having deptno=10; 此时 where也可以做这件事。

? SQL> select deptno, avg(sal) ?from emp ?where deptno=10 group by deptno;?? 因为没有组函数。

在子句中没有使用组函数的情况下,where、having都可以,应该怎么选择?

※SQL优化: 尽量采用where。

如果有分组的话,where是先过滤再分组而having是先分组再过滤当数据量庞大如1亿条,where优势明显。

多表查询

理论基础:——笛卡尔集 ?????

笛卡尔集的行数?=?table1的行数?x?table2的行数

笛卡尔集的列数?=?table1的列数?+?table2的列数

在操作笛卡尔集的时候,应该避免使用“笛卡尔全集”,因为里面含有大量错误信息。?

多表查询就是按照给定条件(连接条件),从笛卡尔全集中选出正确的结果

根据连接条件的不同可以划分为:等值链接、不等值链接、外链接、自连接

  1. Oracle?连接:
    1. Equijoin:等值连接
    2. Non-equijoin:不等值连接
    3. Outer?join:外连接
    4. Self?join:自连接
  2. SQL:?1999
    1. Cross?joins
    2. Natural?joins
    3. Using?clause
    4. Full?or?two?sided?outer joins

等值连接:

从概念上,区分等值连接和不等值连接非常简单,只需要辨别where子句后面的条件,是“=”为等值连接。不是“=”为不等值连接。

查询员工信息:员工号 姓名 月薪和部门名称

分析:这个问题涉及emp(员工号,姓名,月薪) 和dept(部门名称)两张表 ?——即为多表查询。

通常在进行多表查询的时,会给表起一个别名,使用“别名.列名”的方式来获取数据,直接使用“表名.列名”语法上是允许的,但是实际很少这样用。

如果:select e.empno, e.ename, e.sal, e.deptno, d.dname, d.deptno ?from emp e, dept d;???

直接得到的是笛卡尔全集。其中有错误结果。所以应该加 where 条件进行过滤。

? SQL> ?select e.empno, e.ename, e.sal, d.dname ?from emp e, dept d ?where e.deptno=d.deptno; ?

如果有N个表,where后面的条件至少应该有N-1个。

不等值连接:

将上面的问题稍微调整下,查询员工信息:员工号 姓名 月薪 和 薪水级别(salgrade表)

分析:SQL> ?select * from salgrade; ? 看到员工总的薪水级别,共有5级,员工的薪水级别应该满足 >=当前级别的下限,<=该级别的上限:

过滤子句应该: where e.sal >= s.losal and e.sal <= s.hisal

所以: SQL> select e.empno, e.ename, e.sal, s.grade ?from emp e, salgrade s ? ?? where e.sal >= s.losal and e.sal <= s.hisal;

更好的写法应该使用between…and:

SQL> ?select s.grade, e.empno, e.ename, e.sal, e.job ?from emp e, salgrade s ?

where e.sal between s.losal and s.hisal

order by 1 ??

外链接:

按部门统计员工人数,显示如下信息: 部门号 部门名称 人数

分析:

人数:一定是在emp表中,使用count()函数统计emp表中任一非空列均可。

部门名称:在dept表dname中,直接读取即可。

部门号:任意,两张表都有。

SQL> select d.deptno 部门号, d.dname 部门名称, count(e.empno) 人数 ?from emp e, dept d

?? ?where e.deptno=d.deptno ?group by d.deptno, d.dname ?

注意由于使用了组函数count()所以组函数外的d.deptno和d.dname必须放到group?by后。

得到查询结果,但是select * from dept发现40号部门没有显示出来,原因是40号部门没有员工,where没满足。结果不对,40号部门没有员工,应该在40号部门位置显示0。

我们希望: 在最后的结果中,包含某些对于where条件来说不成立的记录?(外链接的作用)

左外链接:当 where e.deptno=d.deptno 不成立的时候,=左边所表示的信息,仍然被包含。

写法:与叫法相反:where e.deptno=d.deptno(+)??

右外链接:当 where e.deptno=d.deptno 不成立的时候,=右边所表示的信息,仍然被包含。

写法:依然与叫法相反:where e.deptno(+)=d.deptno

以上我们希望将没有员工的部门仍然包含到查询的结果当中。因此应该使用外链接的语法。

SQL> select d.deptno 部门号, d.dname 部门名称, count(e.empno) 人数 ?from emp e, dept d

?? ? where e.deptno(+)=d.deptno??group by d.deptno, d.dname; ? 右外链接写法

SQL> select d.deptno 部门号, d.dname 部门名称, count(e.empno) 人数 ?from emp e, dept d

?? ? where d.deptno = e.deptno(+) group by d.deptno, d.dname; 左外链接写法

这样就可以将40号部门包含到整个查询结果中。人数是0

注意不能使用count(e.*)

自连接:

核心,通过表的别名,将同一张表视为多张表。

查询员工信息:xxx的老板是 yyy?

分析:执行select * from emp;?发现,员工的老板也在员工表之中,是一张表。要完成多表查询我们可以假设,有两张表,一张表e(emp)只存员工、另一张表b(boss)只存员工的老板。—— from e, b;

老板和员工之间的关系应该是:where ?e.mgr=b.empno??(即:员工表的老板?=?老板表的员工)

SQL> select e.ename || ' ?的老板是 ' ?|| b.ename from emp e, emp b where e.mgr=b.empno??

执行,发现结果正确了,但是KING没有显示出来。KING的老板是他自己。应该怎么显示呢?

SQL> select e.ename?||?' 的老板是 '?|| nvl(b.ename, '他自己' )

??from emp e, emp b

??where e.mgr=b.empno(+) ?

使用concat函数应该怎么做呢???

SQL> select concat( e.ename, concat(' 的老板是 ', ?nvl(b.ename, '他自己' )) )

?from emp e, ?emp b

??where e.mgr = b.empno(+)

?

文章来源:https://blog.csdn.net/cat_fish_rain/article/details/135215784
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。