Oracle第七课

Oracle第七课

一、函数

  1. 内置函数

    • 单行函数

      字符函数,数字函数,日期函数,转换函数
    • 多行函数

      • 统计函数(最大,最小,平均值,求和,计数)

        -- 有多少员工(计数)
        SELECT COUNT(*) FROM emp;
        -- 最高工资
        SELECT MAX(sal) FROM emp;
        -- 最低工资
        SELECT MIN(sal) FROM emp;
        -- 平均工资
        SELECT AVG(sal) FROM emp;
        -- 求每个部门的最高,最低,平均工资,人数,工资总和
        SELECT deptno,MAX(sal),MIN(sal),AVG(sal),SUM(sal),COUNT(*) FROM emp GROUP BY deptno HAVING deptno IS NOT NULL;
        -- 删除员工表里部门为空的员工
        DELETE FROM emp WHERE deptno IS NULL;

        -- group by having 过滤条件,having是在分组之后,根据其结果再过滤

        -- 分组和统计是相关的,使用的比较多,having只能跟在分组后,不能单独使用

        -- SQL优化:尽量在分组前过滤数据,而不是在分组后过滤数据

      • nv1(x1,x2)函数:如果x1为null,则值为x2

        -- 将员工中奖金为空的换成0
        SELECT nv1(comm,0) FROM emp;
      • nv3(x1,x2,x3)函数:如果值是x1,则返回x2,否则的话返回x3

        -- 将员工有奖金的置为1,没奖金的置为0
        select nv2(comm,1,2) from emp;

        -- nvl是解决空值的问题,nvl2也是空值的问题,区别是nvl2当参数1不为空时提供了额外的

      • decode函数:相当于switch选择语句

        常用作报表的处理

        -- 如果job = 'CLERK',则显示为店员,如果job = 'SALESMAN',则显示为业务员...
        SELECT DECODE(job,'CLERK','店员','SALESMAN','业务员','MANAGER','经理','PRESIDENT','董事长','ANALYST','分析员','未知') FROM emp;
        -- 60到80为普通,80到90为良好,90到100为优秀
        SELECT decode(trunc(&cj/10),6,'普通',7,'普通',8,'良好',9,'优秀',10,'优秀','不及格') FROM dual;
    • sum和decode的应用:行转列

      CREATE TABLE t_score(NAME VARCHAR(10),course VARCHAR(10),score NUMBER(4,1));
      INSERT INTO t_score VALUES('赵一','Java',100);
      INSERT INTO t_score VALUES('赵一','C',96);
      INSERT INTO t_score VALUES('赵一','C++',85);
      INSERT INTO t_score VALUES('钱一','Java',95);
      INSERT INTO t_score VALUES('钱一','C',84);
      INSERT INTO t_score VALUES('钱一','C++',93);
      SELECT NAME,SUM(score) 总成绩, SUM(DECODE(course,'Java',score)) JAVA,SUM(DECODE(course,'C',score)) C,SUM(DECODE(course,'C++',score)) CPlus FROM t_score GROUP BY NAME;

      -- 用sum函数把多行数据统计起来,一个sum是一列

    • 应用

      -- 建一个居民收入表,1-12月,每月输入它的工资,表中有居民姓名name
      CREATE TABLE resident(NAME VARCHAR(6) NOT NULL,r_month number(2) NOT NULL,sal NUMBER(8) NOT NULL);
      INSERT INTO resident VALUES('赵一',1,10000);
      INSERT INTO resident VALUES('赵一',2,10500);
      INSERT INTO resident VALUES('赵一',3,9000);
      INSERT INTO resident VALUES('赵一',4,10030);
      INSERT INTO resident VALUES('赵一',5,10500);
      INSERT INTO resident VALUES('赵一',6,10000);
      INSERT INTO resident VALUES('赵一',7,12000);
      INSERT INTO resident VALUES('赵一',8,10500);
      INSERT INTO resident VALUES('赵一',9,10800);
      INSERT INTO resident VALUES('赵一',10,10900);
      INSERT INTO resident VALUES('赵一',11,10010);
      INSERT INTO resident VALUES('赵一',12,10800);
      SELECT * FROM resident;
      DROP TABLE resident;
      -- 显示每个人每月的工资,以列的形式显示,2020我国的平均收入是40000元左右,显示该居民是否达到或拖后腿
      SELECT NAME,SUM(DECODE(r_month,1,sal)) 一月,
      SUM(DECODE(r_month,2,sal)) 二月,
      SUM(DECODE(r_month,3,sal)) 三月,
      SUM(DECODE(r_month,4,sal)) 四月,
      SUM(DECODE(r_month,5,sal)) 五月,
      SUM(DECODE(r_month,6,sal)) 六月,
      SUM(DECODE(r_month,7,sal)) 七月,
      SUM(DECODE(r_month,8,sal)) 八月,
      SUM(DECODE(r_month,9,sal)) 九月,
      SUM(DECODE(r_month,10,sal)) 十月,
      SUM(DECODE(r_month,11,sal)) 十一月,
      SUM(DECODE(r_month,12,sal)) 十二月,
      DECODE(TRUNC(SUM(sal)/40000),0,'拖后腿','合格') 是否合格
      FROM resident r GROUP BY NAME;
  2. SQL的递归

    • 查出一个奶牛场某头奶牛的所有后代和父辈

      -- 第一代奶牛的父非为0
      insert into cow values(1,0);
      insert into cow values(100,1);
      insert into cow values(101,1);
      insert into cow values(800,100);
      insert into cow values(902,101);
      insert into cow values(2,0);
      insert into cow values(302,2);

      -- 找id为1的父辈和字辈
      -- 从id为1开始,找儿子:当前id等于下一行的parent_id,找父亲:当前id的parent_id等于下一个要找的id
      SELECT * FROM cow START WITH ID=1 CONNECT BY PRIOR ID = parent_id;

二、作业

一个公司有很多部门有上级部门

一个员工有他所在的部门,根据员工编号找到他所在的所有上级部门。

编号 姓名 部门
1 张三 301
2 李四 401
编号 名称 上级部门
301 财务部 11
11 集团中心 2
2 财务总监 0

显示出张三的上级部门:财务部,集团中心,财务总监



上一篇:条件查询


下一篇:开篇——SQL面试题痛的领悟