Oracle数据库知识梳理---续(单行函数,多行函数,分组数据,分组过滤)

5.单行函数,多行函数,分组数据,分组过滤

多行函数

显示员工表行数
select count(*) from emp;

              COUNT(*)
            ----------
                    14

字符函数

select Lower('Hello') 转小写, upper('hello') 转大写, initcap('hello woRld')  首字母大写 from dual;


            转小  转大  首字母大写
            ----- ----- -----------
            hello HELLO Hello World

组函数

select avg(sal),sum(sal),max(sal),min(sal),count(sal) from emp;

AVG(SAL)   SUM(SAL)   MAX(SAL)   MIN(SAL) COUNT(SAL)
--------- ---------- ---------- ---------- ----------
2073.21429     29025     5000       800       14

组函数和null在一起

求员工的平均工资
selectavg(comm),sum(comm)/count(comm),sum(somm)/sount(*) from emp;                  
上述输出结果都不同

解析如下:可见空值没有被计算在内,组函数自动滤空,组函数忽略空值.
select count(comm),count(*) from emp;
                    COUNT(COMM)   COUNT(*)
                    ----------- ----------
                              4         14

修正组函数的滤空.
select count(nvl(comm,0)),count(*) from emp;

                    COUNT(NVL(COMM,0))   COUNT(*)
                    ------------------ ----------
                                    14         14

分组数据

求各个部门的平均工资
需要将各个部门划分开
select deptno,avg(sal) from emp
group by deptno;
            DEPTNO   AVG(SAL)
            ---------- ----------
                    30 1566.66667
                    20       2175
                    10 2916.66667   

解析:
select检索的列,必须要位于group by后面的集合列中
组函数设计的本意:必须要在分组数据之上,进行结果集的检索
注意:group by 字句要求:所有在select中出现的列,都必须在group by分组子句中
          1  select  deptno,  avg(sal), ename
          2     from emp
          3     group by deptno;

        select  deptno,  avg(sal), ename
                                   *
        第 1 行出现错误:
        ORA-00979: 不是 GROUP BY 表达式

在group by子句中包含多个列
按部门 不同的职位 统计平均工资
先按照部门分组,再按照job分组,如果deptno和job一样,就在同一组,然后求平均工资

每一个部门,每一个工种的平均工资
select deptno,job,avg(sal) from emp group by deptno,job order by 1;

DEPTNO JOB         AVG(SAL) COUNT(DEPTNO)
---------- --------- ---------- -------------
   10 CLERK           1300             1
   10 MANAGER         2450             1
   10 PRESIDENT       5000             1
   20 ANALYST         3000             2
   20 CLERK            950             2
   20 MANAGER         2975             1
   30 CLERK            950             1
   30 MANAGER         2850             1
   30 SALESMAN        1400             4

分组过滤

查询平均工资大于2000的部门
select deptno,avg(sal) from emp 
group by deptno
having avg(sal) > 2000;

            DEPTNO   AVG(SAL)
        ---------- ----------
                20       2175
                10 2916.66667

求10号部门的平均工资

---方法1  ---先分组 再过滤
    select deptno, avg(sal)
    from emp
    group by deptno 
    having   deptno=10

---方法2 ---先过滤在分组.....
    select deptno, avg(sal)
    from emp
    where deptno =10
    group by deptno