ORCL BI大数据

截取字段,从X位开始,Y位长度,如果X为负数,则从 -- 倒数的第X位开始往 后截取；截取字符串'ABCDEFGFAFAFDF'中'B'之后的内容SELECT SUBSTR('ABCDEFGFAFAFDF',3) FROM DUAL。

ORCL BI大数据(续） 2022年

• 3.30
  • Oracle常用函数
    • 数学函数
      • ABS 取绝对值
        • SELECT ABS(-4.33) FROM DUAL;
          • 数值取绝对值
        • SELECT ABS(SAL) FROM EMP;
          • 目标字段的值取绝对值
      • ROUND 四舍五入
        • SELECT ROUND(2.567,2) FROM DUAL；
          • 取小数点后两位的值，进行四舍五入
        • SELECT ROUND(2.567) FROM DUAL ;
          • -- 没有指定小数位则取整
      • TRUNC 截断
        • SELECT TRUNC(2.5675657,1) FROM DUAL;
          • 取小数点后一位，不需要四舍五入
        • SELECT TRUNC(2.5675657) FROM DUAL ;
          • -- 直接返回整数,舍弃小数位
      • FLOOR 向下取整
        • SELECT FLOOR(3.84) FROM DUAL;
          • 取小于等于该值的最近的一个整数
      • CEIL 向上取整
        • SELECT CEIL(3.14) FROM DUAL;
          • 取大于等于该值的最近一个整数
      • MOD 取余数
        • SELECT MOD(10,3) FROM DUAL;
          • 10/3 10除以3（3除10）商为3，余为1
      • TRUNC 取商
        • SELECT TRUNC(8/3) FROM DUAL;
          • 被除数 除以 除数 等于商……余数 8÷3=2……2
      • POWER 取幂
        • SELECT POWER(2,10) FROM DUAL;
          • 2的10次方
    • 日期函数
      • 日期 + 整数 得到的是 日期
        • 系统时间：SYSDATE 返回Oracle数据库当前的时间
          • SELECT SYSDATE - 1 FROM DUAL ;
            • 前一天的这个时候
          • SELECT TO_DATE('20200101','YYYYMMDD') - 1 FROM DUAL ;
            • 2020/01/01的前一天2019/12/31
      • 日期 - 日期 得到的是 数字 单位是 天
        • SELECT TO_DATE('20201231','YYYYMMDD') -TO_DATE('20200101','YYYYMMDD') + 1 FROM DUAL ;
          • -- 得到2020年总共366天
        • SELECT SYSDATE - TO_DATE('20200101','YYYYMMDD') FROM DUAL;
          • -- 得到的是小数, 因为 SYSDATE 包含时分秒，所以有小数
      • SELECT TRUNC(SYSDATE,'DD') FROM DUAL -- 截断/截取到天,舍弃 时分秒 SELECT TO_DATE( TO_CHAR(SYSDATE,'YYYYMMDD') ,'YYYYMMDD') FROM DUAL; -- TO_CHAR+TO_DATE 转换成 年月日格式
      • SELECT TRUNC(SYSDATE,'MM') --本月第一天
      • SELECT TRUNC(SYSDATE,'MM') -1 -- 上个月最后一天
      • 截取当年第一天 SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL;
      • 截取当前季度的第一天 SELECT TRUNC(SYSDATE ,'Q') FROM DUAL; -- QUARTER 季度（三个月）
      • 返回下个月的当天 SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL -- ADD 增加 MONTHS 月
      • 往前推10年 SELECT ADD_MONTHS(SYSDATE,-10*12) FROM DUAL --（-10）*12 往前推10个12月
      • 社保缴纳 15号是一个月的分割期 SELECT ROUND(SYSDATE + 10,'MM') FROM DUAL；
        • 'MM' 小于等于15号 输出结果是当月第一天的年月日；超过15号就是下个月第一天的年月日 'DD' 以12：00为分割点，超过12：00即四舍五入为明天的年月日 'YYYY' 以6月1号为分割点，超过6月1号即四舍五入到下一年1月1号
      • 返回当月最后一天 SELECT TRUNC( ADD_MONTHS(SYSDATE,1) -- 下个月 ,'MM') -- 当月第一天 - 1 -- 本月最后一天 FROM DUAL;
        • 相当于 SELECT LAST_DAY(SYSDATE) FROM DUAL；
          • --输出 EMP表中 入职时间 的 上个季度 的 第一天的 月末 SELECT /*HIREDATE, TRUNC(HIREDATE,'Q') 这个季度, ADD_MONTHS(TRUNC(HIREDATE,'Q'),-3) 上个季度的第一天,*/ LAST_DAY( ADD_MONTHS(TRUNC(HIREDATE,'Q'),-3)) 上个季度第一天的月末 FROM EMP;
        • 解释：截取下一个月的这一天的这个月份的第一天的前一天 就是当月最后一天
      • MONTHS_BETWEEN(大的日期,小的日期) -- 得到的结果是个 NUMBER 可以是负数
        • 计算当前日期跟 2020-01-01 相差多少个月 SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,TO_DATE('20200101','YYYYMMDD'))) FROM DUAL;
    • 字符函数
      • ---REPLACE(字段名称,待替换的值,替换之后的值)
        • 将EMP表所有的员工姓名中的A替换成B SELECT ENAME,REPLACE(ENAME,'A','B') 替换后 FROM EMP
        • 将字符串' A BC D '中间的空格去掉 SELECT REPLACE(' A BC D ',' ','') FROM DUAL
          • 也可以 SELECT REPLACE(' A BC D ',' ') FROM DUAL -- 不写 替换后的值 默认替换为空
        • 统计 '0100111100110011ADNSA' 里面有多少个 1 SELECT LENGTH('0100111100110011ADNSA') - --原始长度 LENGTH(REPLACE('0100111100110011ADNSA','1')) --干掉1之后的长度 FROM DUAL;
          • 原长度 - 去掉1的长度=有几个1
      • 输出EMP表所有ENAME的长度 SELECT ENAME,LENGTH(ENAME) AS LEN FROM EMP;
        • 空格会占据一个字节长度 例如：SELECT LENGTH('AB C') FROM DUAL；
      • 拼凑
        • || --管道符
          • 输出"SCOTT的薪资是1500" 拼接 可以用 || SELECT ENAME || '的薪资是' || SAL AS INFO FROM EMP
            • 输出 员工编号 员工姓名 员工薪资 拼接到一起 SELECT EMPNO || ENAME || SAL INFO FROM EMP;
        • CONCAT（ ， ）
          • 拼接只能 CONCAT(X,Y) 不能CONCAT(X,Y,Z..)
            • 输出 员工编号 员工姓名 员工薪资 拼接到一起 SELECT CONCAT(CONCAT(EMPNO,ENAME),SAL) AS INFO FROM EMP;
        • 将EMP表所有的ENAME 拼接到一起 (拼接某一列) SELECT WM_CONCAT(ENAME) FROM EMP
        • 将员工表 所有的 不同工作拼接到一起 并以 '-' 进行 分割 -- SALEMAN-MANAGER-CLERK-PRESIENT SELECT REPLACE( --将所有拼接的不重复的工作的“，”替换成“-” WM_CONCAT( --把所有不重复的工作拼接 DISTINCT JOB --去掉重复的工作 ),',','-' ) AS INFO FROM EMP;
        • 按照某个字段 可以分组去拼接 还是用 WM_CONCAT
          • 每个工种的薪资 拼接到一起 SELECT JOB,WM_CONCAT(SAL) AS INFO FROM EMP GROUP BY JOB;
      • INSTR 查找位置
        • 查找字段COL中字符串STR,从第X位开始,第Y次出现的索引位置 如果找不到,则返回 0 INSTR(COL,STR[,X,Y])
          • 查找EMP表中ENAME列,从第2位开始 第1次出现 O 的位置 SELECT ENAME,INSTR(ENAME,'O',2,1) AS LOC FROM EMP;
            • 省略不写[X,Y]则表示’字符串‘第一次出现的位置 SELECT ENAME,INSTR(ENAME,'A') AS LOC FROM EMP;
        • 模糊查询
          • 找出EMP表,姓名包含 A 的员工 SELECT ENAME,INSTR(ENAME,'A') S FROM EMP WHERE INSTR(ENAME,'A') > 0;
      • 大小写转换
        • UPPER 转换成大写
          • SELECT UPPER('abc') FROM DUAL;
        • LOWER 转换成小写
          • SELECT LOWER(ENAME) FROM EMP;
      • TRIM 去左右空格
        • 去掉字符串' A B C D ' 的左右空格 SELECT TRIM(' A B C D ') 去左右空格, --输出 'A B C D' LTRIM(' A B C D ') 去左空格, --输出 'A B C D ' RTRIM(' A B C D ') 去右空格, --输出 ' A B C D' REPLACE(' A B C D ',' ') 去掉所有空格 --输出 'ABCD' FROM DUAL;
      • SUBSTR 截取
        • SUBSTR(字段,X[,Y])
          • -- 截取字段,从X位开始,Y位长度,如果X为负数,则从 -- 倒数的第X位开始往 后截取
          • 截取字符串'ABCDEFG' 中的'CDEF' SELECT SUBSTR('ABCDEFG',3,4) FROM DUAL;
          • 截取字符串'ABCDEFGFAFAFDF'中'B'之后的内容 SELECT SUBSTR('ABCDEFGFAFAFDF',3) FROM DUAL;
          • 截取字符串'ABCDEFG' 中的'CDEF' SELECT SUBSTR('ABCDEFG',-5,4) FROM DUAL;
            • -5代表从倒数第5位开始截取,截取4位长度