ORACLE_多行函数_分组函数_聚合函数

1.SUM //求和  SELECT SUM(*) FROM *;

   SELECT SUM(*) FROM *;

2.GROUP BY  //分组函数   SELECT * FROM * GROUP BY *;

    SELECT SUM(SAL),DEPTNO FROM EMP GROUP BY DEPTNO;

3.AVG //平均数   SELECT AVG(*) FROM *;

    SELECT AVG(SAL) FROM EMP;

4.MAX //取最大结果值  MIN //取最小结果值   SELECT MAX(*),MIN(*) FROM *;

    SELECT MAX(SAL) ,MIN(SAL) FROM EMP;

5.COUNT  //计数  SELECT COUNT(*) FROM *;

   SELECT COUNT(*) FROM EMP;

SELECT SUM(SAL) 工资总和,AVG(SAL) 平均工资,MAX(SAL) 最大工资,MIN(SAL) 最小工资,COUNT(*) 总人数 FROM EMP;

--注意：SUM,AVG只能适用于NUMBER类型，MAX,MIN,COUNT适用于任何字段类型

    字母A最小，字母z最大，

    日期离当前越久越小，日期离当前越近越大

--统计每个部门的总人数

SELECT COUNT(ENAME) ,DEPTNO FROM EMP GROUP BY DEPTNO;

--每个部门不同岗位的平均工资 SELECT * FROM * GROUP BY * HAVING *>* ORDER BY *;

  SELECT AVG(SAL),JOB,DEPTNO FROM  EMP GROUP BY DEPTNO,JOB  HAVING AVG(SAL)>2000 ORDER BY DEPTNO;

6.TO_CHAR//转换函数  SELECT TO_CHAR(*,*) FROM *;

    SELECT TO_CHAR(SYSDATE,'YY-MM-DD') FROM DUAL;

    SELECT TO_CHAR(111.111,'9999.9999') FROM DUAL;// 9,不足0

    SELECT TO_CHAR(111.111,'0000,0000') FROM DUAL; //0，补0

7.TO_DATE //转换为日期类型 SELECT TO_DATE(*.*) FROM *;

    SELECT TO_DATE('2015-12-15','YYYY-MM-DD') FROM DUAL;

8.TO_NUMBER //将字符串转换成数字  SELECT TO_NUMBER(*) FROM *;

    SELECT TO_NUMBER('123.123') FROM DUAL;

9.NVL  //如果内容为空，将转换为后面的参数 SELECT NVL(*,*) FROM EMP;

     SELECT NVL(COMM,0) FROM EMP;

     SELECT EMPNO,ENAME,SAL+NVL(COMM,0) 总工资 FROM EMP;