Oracle数据库：oracle组函数，聚合函数，多行函数，avg，sum，min，max，count，group by，having

2022找工作是学历、能力和运气的超强结合体，遇到寒冬，大厂不招人，可能很多算法学生都得去找开发，测开
测开的话，你就得学数据库，sql，oracle，尤其sql要学，当然，像很多金融企业、安全机构啥的，他们必须要用oracle数据库
这oracle比sql安全，强大多了，所以你需要学习，最重要的，你要是考网络警察公务员，这玩意你不会就别去报名了，耽误时间！
oracle系列文章：
【1】Oracle数据库：啥是oracle数据库？你为啥要学oracle？
【2】Oracle数据库：oracle 11g安装教程，已安装好的oracle各个文件夹的作用，oracle用户权限怎么样
【3】Oracle数据库：oracle启动，oracle客户端工具plsql安装教程和使用方法
【4】Oracle数据库：创建表空间，创建新用户，给用户分配对象、角色和系统权限，登录新用户建表
【5】Oracle数据库：链接配置，包括sqlnet.ora里面的transnames.ora配置数据库标识符SID，listener暂时简单了解
【6】Oracle数据库：net configureation assistant工具配置监听listener，配置本地网络访问服务器上的数据库
【7】Oracle数据库：oracle字符类型、数字类型、创建表表名的命名规则
【8】Oracle数据库：约束条件：主键约束、唯一约束、检查约束、非空约束、外键约束、默认值填写
【9】Oracle数据库：表的关系：一对多，一对一，多对多，一一继承和修改的关系，表不是重点，重点是数据和约束关系
【10】Oracle数据库：sql语言结构，数据查询语言DQL，select * from table；算术，别名，连接，去重等操作
【11】Oracle数据库：约束行限制where语句，判断条件，比较条件，字符串日期格式，in,like,escape,null语句
【12】Oracle数据库：逻辑运算and，or，not和各种运算的优先级控制
【13】Oracle数据库：排序order by语句，select from where order by的执行先后顺序，各种样例
【14】Oracle数据库：oracle函数，单行函数，多行函数，upper，lower，initcap，字符串函数
【15】Oracle数据库：数字函数，日期函数，round，trunc，mod，months_between,add_months,next_day,last_day,sysdate
【16】Oracle数据库：oracle数据类型转换to_char()日期和数字转字符,to_number()字符转数字,to_date()字符转日期函数
【17】Oracle数据库：oracle函数嵌套，nvl函数，nvl2函数，nullif函数，coalesce合并函数
【18】Oracle数据库：条件表达式case when then else end，decode函数，oracle单行函数练习示例
【19】Oracle数据库：oracle多表查询，等值连接，非等值连接，自连接的sql语句和规则
【20】Oracle数据库：oracle外连接left/right/full outer join on,oracle扩展的左右外连接展示符号(+)
【21】Oracle数据库：自然连接natural join，using语句，注意避免写交叉连接
【22】Oracle数据库：oracle内连接inner join on，多表查询各种自链接、内连接、外连接的练习示例

文章目录

oracle组函数，聚合函数，多行函数

在这里插入图片描述

在这里插入图片描述
单行函数是处理多行，输出多行
分组聚合函数是处理多行，输出一行

在这里插入图片描述
这种就是要底层的取max函数了

在这里插入图片描述
在这里插入图片描述
这些我们在数据结构与算法中讲太多了
这都是弱鸡操作

在这里插入图片描述
在这里插入图片描述
可能里面会用单行函数操作一波
嵌套好说

AVG和SUM搞数字类型的处理

在这里插入图片描述
在这里插入图片描述
干一波

SQL> select avg(salary) avg, sum(salary) sum from employees;

       AVG        SUM
---------- ----------
6461.83177     691416

既然没有group by分组
那就是默认对整个表做一个组来计算

这种返回的结果就一条
这就是分组函数

min和max函数

在这里插入图片描述
在这里插入图片描述

SQL>  select min(salary) min, max(salary) max from employees;

       MIN        MAX
---------- ----------
      2100      24000

太easy

SQL>  select min(last_name) min, max(last_name) max from employees;

MIN                       MAX
------------------------- -------------------------
Abel                      Zlotkey

里面都会自动排序的

SQL>  select min(hire_date) min, max(hire_date) max from employees;

MIN         MAX
----------- -----------
2001/1/13   2008/4/21

好说

组函数的使用要注意的地方
avg和sum，你想要投影名字，这种单行函数，是不行的
因为它返回结果很多，没法跟你一个结果放一起

select last_name,salary, avg(salary) avg, sum(salary) sum from employees

ORA-00937: 不是单组分组函数

gg

回头我们用group by再分组的列那搞清楚这个事情

count函数，返回表中的行数

在这里插入图片描述
在这里插入图片描述
根据返回的结果集中的所有数据计算
有where限制，就是限制之后的结果计算all

SQL> select count(*) from employees;

  COUNT(*)
----------
       107

表有107行

SQL> select count(*) from employees where commission_pct is not null;

  COUNT(*)
----------
        35

有条件，就是条件筛选之后的，因为条件语句的执行比select早

SQL> select count(*) from employees e where e.last_name like '%a%';

  COUNT(*)
----------
        52

有52个含有a字符的名字

在这里插入图片描述
相当于颗粒度细致了些许

在这里插入图片描述

SQL> select count(commission_pct) from employees e where e.department_id=80;

COUNT(COMMISSION_PCT)
---------------------
                   34

是count中指定的参数x不为null，节约了一个 is not null

懂？

在这里插入图片描述
剔除重复行

SQL> select count(distinct e.department_id) from employees e;

COUNT(DISTINCTE.DEPARTMENT_ID)
------------------------------
                            11

SQL> select count(*) from employees e;

  COUNT(*)
----------
       107

SQL> select count(e.department_id) from employees e;

COUNT(E.DEPARTMENT_ID)
----------------------
                   106

107个数据
1个的部门是null
但是重复去掉后
拢共就11个部门

组函数就这几个，就很简单

组函数会忽略null值，通过nvl，nvl2转化为真实的数值

在这里插入图片描述
在这里插入图片描述
直接就忽略了null
不管

SQL> select avg(commission_pct) from employees;

AVG(COMMISSION_PCT)
-------------------
  0.222857142857143

SQL> select count(commission_pct) from employees;

COUNT(COMMISSION_PCT)
---------------------
                   35

只有35个人有佣金的
那其他的null默认忽略了
你想将这些null转化为某个值的话
可以的

在这里插入图片描述
就转化0了

SQL> select count(nvl(commission_pct,0)) from employees;

COUNT(NVL(COMMISSION_PCT,0))
----------------------------
                         107

SQL> select avg(nvl(commission_pct,0)) from employees;

AVG(NVL(COMMISSION_PCT,0))
--------------------------
        0.0728971962616822

这个值小多了，为啥呢，分母大了哈哈哈哈哈哈

懂？？？

知道组函数默认干掉null了吧

创建分组:group by

在这里插入图片描述
一组一组搞，这样很舒服
比如男生的平均薪资，女生的平均薪资，这样就要分为俩组

或者部门分开，再算薪资平均值
在这里插入图片描述
在这里插入图片描述
where之后紧跟group by，先分组
再去做order by
在这里插入图片描述
where是总共条件拿数据
然后分组
分组完默认升序排序

列别名后执行，因为数据取出来在前面

having子句约束分组的结果，不能用where

在这里插入图片描述

SQL> select department_id,job_id,sum(salary) from employees group by department_id,job_id order by sum(salary);

DEPARTMENT_ID JOB_ID     SUM(SALARY)
------------- ---------- -----------
           10 AD_ASST           4400
           20 MK_REP            6000
           40 HR_REP            6500
              SA_REP            7000
          110 AC_ACCOUNT        8300
           70 PR_REP           10000
           30 PU_MAN           11000
          110 AC_MGR           12008
          100 FI_MGR           12008
           20 MK_MAN           13000
           30 PU_CLERK         13900
           90 AD_PRES          24000
           60 IT_PROG          28800
           90 AD_VP            34000
           50 ST_MAN           36400
          100 FI_ACCOUNT       39600
           50 ST_CLERK         55700
           80 SA_MAN           61000
           50 SH_CLERK         64300
           80 SA_REP          243500

20 rows selected

我们首先，按照部门，工作级别来分组
然后如果你想要再约束一下sum，过滤这个sum大于5000的菜显示的话
这时，不能用where，而只能用having

SQL> select department_id,job_id,sum(salary) from employees group by department_id,job_id having sum(salary)>5000 order by sum(salary) ;

DEPARTMENT_ID JOB_ID     SUM(SALARY)
------------- ---------- -----------
           20 MK_REP            6000
           40 HR_REP            6500
              SA_REP            7000
          110 AC_ACCOUNT        8300
           70 PR_REP           10000
           30 PU_MAN           11000
          110 AC_MGR           12008
          100 FI_MGR           12008
           20 MK_MAN           13000
           30 PU_CLERK         13900
           90 AD_PRES          24000
           60 IT_PROG          28800
           90 AD_VP            34000
           50 ST_MAN           36400
          100 FI_ACCOUNT       39600
           50 ST_CLERK         55700
           80 SA_MAN           61000
           50 SH_CLERK         64300
           80 SA_REP          243500

19 rows selected

看见having的牛逼之处了吗
其实就是分组函数下的where

你用where就不对了
在这里插入图片描述
在这里插入图片描述
having也可以放在group by左边
常规下放在having之后，这样逻辑上很好理解

在这里插入图片描述
按照部门分组

SQL> select department_id,max(e.salary) from employees e group by department_id having max(e.salary)>10000;

DEPARTMENT_ID MAX(E.SALARY)
------------- -------------
          100         12008
           30         11000
           90         24000
           20         13000
          110         12008
           80         14000

6 rows selected

你要是不过滤
就可能比较多

SQL> select department_id,max(e.salary) from employees e group by department_id;

DEPARTMENT_ID MAX(E.SALARY)
------------- -------------
          100         12008
           30         11000
                       7000
           90         24000
           20         13000
           70         10000
          110         12008
           50          8200
           80         14000
           40          6500
           60          9000
           10          4400

12 rows selected

在这里插入图片描述
要平均薪水

SQL> select e.department_id,avg(e.salary) from employees e group by department_id having max(e.salary)>10000;

DEPARTMENT_ID AVG(E.SALARY)
------------- -------------
          100 8601.33333333
           30          4150
           90 19333.3333333
           20          9500
          110         10154
           80 8955.88235294

6 rows selected

不过滤的

SQL> select e.department_id,avg(e.salary) from employees e group by department_id;

DEPARTMENT_ID AVG(E.SALARY)
------------- -------------
          100 8601.33333333
           30          4150
                       7000
           90 19333.3333333
           20          9500
           70         10000
          110         10154
           50 3475.55555555
           80 8955.88235294
           40          6500
           60          5760
           10          4400

12 rows selected

懂？
好说having

总结

提示：重要经验：

1）
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。

# 数据库 # oracle # sql
Logo
亚马逊云科技技术品牌专区

更多推荐

cover

企业物联网平台如何选择？

avatar 亚马逊云科技技术品牌专区

STM32节点移植lorawan协议连接腾讯云物联网开发平台（IoT Explorer）

STM32移植lorawan协议连接腾讯云物联网开发平台（IoT Explorer）前言前言在移植协议之前，先给大家科普一下Lora 和 lorawan 的区别。LoRa 是LPWAN通信技术中的一种，是美国Semtech公司采用和推广的一种基于扩频技术的超远距离无线传输方案。这一方案改变了以往关于传输距离与功耗的折衷考虑方式为用户提供一种简单的能实现远距离、长电池寿命、大容量的系统，进而扩...

avatar 亚马逊云科技技术品牌专区

从华为的MQTT到TdEngineRPC，解读物联网时代的分布式

今天中秋节，笔者首先祝各位读者们中秋快乐，之所以在今天这个团圆节来谈分布式的话题，就是要聊聊物联网是如何通过MQTT连接各类终端，如何通过RPC整合各种数据的。下面就通过代码+动图的方式来解读一下华为LiteOS的MQTT与TD的RPC。MQTT协议MQTT是一个客户机服务器发布/订阅消息传输协议。它重量轻、开放、简单、易于实现。这些特性使其非常适合在物联网的低带宽、...

avatar 亚马逊云科技技术品牌专区