SQL函数查询

矫情吗；* 2022-06-09 05:26 109阅读 0赞

-- 字符串函数
    字符串函数主要处理字符串数据(字符串数据可以是列上的内容，也可以是指定的常量)。
    常用的字符串函数有以下几种：
    函数名称									返回值(结果)类型			说明
    LOWER(列\字符串常量);						字符串						将传入的字符转为小写
    UPPER(列\字符串常量);						字符串						将传入的字符转为大写
    INITCAP(列\字符串常量);						字符串						将传入的字符转为除了首字符大写其他字母小写
    LENGTH(列\字符串常量);						数值						返回传入的字符串的字符长度
    SUBSTR(列\字符串常量,开始索引,截取长度);	字符串						将字符串从开始索引的位置截取指定长度
    SUBSTR(列\字符串常量,开始索引);				字符串						将字符串从开始索引的位置截取到末尾
    REPLACE(列\字符串常量,旧字符串,新字符串);	字符串						将指定字符串中的旧字符串替换成新字符串
    
    --字节个数
    SELECT LENGTHB('ab哈') FROM dual;
    注：字符串下标从1开始
    
    LOWER(列|常量)
    针对列
    -- 将 emp 表的 ENAME 列的数据转为小写。
    SELECT LOWER(ename) FROM emp;
    
    针对常量
    -- 将 HELLOWORLD 转为小写。
    SELECT LOWER('HELLOWORLD') FROM emp;
    
    注意：
    所有函数想要进行验证使用，都必须编写SQL 语法。不能单独使用函数。
    而直接查询 emp 表的话满足条件的结果都将输出来。
    有时候只是想测试一下常量，而结果也会输出多条记录。
    
    提示：
    Oracle 专门提供了一张虚拟表 dual，测试常量的时候，只需要把查询的表 换成 dual即可。
    
     -- UPPER(列|常量)
     SELECT UPPER('abc') FROM dual;
    
    -- INITCAP(列|常量)
    SELECT INITCAP('abc') FROM dual;
    
    -- LENGTH(列|常量)
    SELECT LENGTH('abc') FROM dual;
    
    -- SUBSTR(列|常量,开始索引,截取长度)
    SELECT SUBSTR('哈哈一二三',2,4) FROM dual;
    
    -- SUBSTR(列|常量,开始索引)
    SELECT SUBSTR('哈哈一二三',2) FROM dual;
    
    -- REPLACE(列|常量,旧字符串,新字符串)
    SELECT REPLACE('哈哈哈1111','1111','2222') FROM dual;
    
    
    -- 数值函数
    字符串函数主要处理字符串数据(字符串数据可以是列上的内容，也可以是指定的常量)。
    常用的字符串函数有以下几种：
    函数名称											返回值(结果)类型	说明
    ROUND(列\数值常量 [,小数点位数]);					数值				实现数据的四舍五入，可以设置保留小数位
    TRUNC(列\数值常量 [,小数点位数]);					数值				实现数据的截取，即：不进位
    MOD(列\数值常量(除数) , 列\	数值常量(被除数));		数值				取模(计算余数)
    
     -- ROUND(列|数值常量 [,小数点位数]);
     
    -- 对 768.6178 进行四舍五入，无指定小数位
    SELECT ROUND(768.6178) FROM dual;
    
    -- 对 768.6178 进行四舍五入，指定保留2位小数
    SELECT ROUND(768.6178,2) FROM dual;
    
    -- 对 768.6178 进行四舍五入，指定保留到 十位。（使用比较少,了解即可...）
    SELECT ROUND(768.6178,-1) FROM dual;
    
    -- TRUNC(列|数值常量 [,小数点位数]);
    
    将数字截尾取整：TRUNC() 函数 和 ROUND() 函数的使用方式一样，效果差不多，只不过 TRUNC() 函数不进行四舍五入。
    
    -- 对 768.6718 进行 数字截尾取整，无指定小数位
    SELECT TRUNC(768.6718) FROM dual;
    
    -- 对 768.67 进行 数字截尾取整，指定保留2位小数
    SELECT TRUNC(768.6718,2) FROM dual;
    
    -- 对 768.67 进行 数字截尾取整，指定保留到 十位。（使用比较少,了解即可...）
    SELECT TRUNC(768.6718,-1) FROM dual;
    
    -- MOD(列|数值常量(除数) , 列|数值常量(被除数));
    -- 10 对 3取余。
    SELECT MOD(10,3) FROM dual;
    
    -- 测试 emp 表 EMPNO列数据 对 DEPTNO列数据 取余。
    SELECT MOD(empno,deptno) FROM emp;
    
    
    还有一些函数使用频率较少，如：
    CEIL(列|数值常量) 向前取整：
    如：CEIL(1.2) 结果为2；
    FLOOR(列|数值常量) 向后取整：
    如：CEIL(1.2) 结果为1；
    SQRT()(列|数值常量) 平方根：
    如：SQRT(9) 结果为3；
    
    
    
    -- 日期函数
    
    如果想要处理日期函数，就必须知道系统当前时间是多少。Oracle为专门提供了两个 虚拟(伪)列 SYSDATE和SYSTIMESTAMP 用来查询当前日期时间。两个虚拟伪劣都包含日期和时间内容，但 SYSDATE列 只显示日期，而SYSTIMESTAMP 显示日期和时间。用的较多的是SYSDATE。
    伪列：在查询所有数据表的时候都可以访问该列。
    
    如：查询 emp 表的 雇员编号，雇员姓名，当前时间。那么就可以把 sysdate 当做一列进行查询
    SELECT empno,ename,SYSDATE FROM emp;
    
    清楚了获取当前时间的方式后，还需要以下三个日期的计算公式：
    日期 + 数字(天数) = 日期 ——表示若干天后的日期
    日期 - 数字(天数) = 日期 ——表示若干天前的日期
    
    SELECT SYSDATE,SYSDATE+7,SYSDATE-7 FROM dual;
    
    日期 - 日期 = 数字(天数) ——表示两个日期的天数差
    -- 查询 emp 表员工姓名、雇佣日期、当前日期、雇佣的天数
    SELECT ename,hiredate,SYSDATE,SYSDATE-hiredate FROM emp;
    
    
    注意：通过 日期 - 日期 公式得到是天数，我们很难从天数算出经过多少年，多少月份等，因为我们每一年的天数是不一样的，以及月份的天数也是不一样的。所以，除了要知道三个简单的日期公式之外，还需要学会以下四个常用日期函数。
    日期函数：
    
    函数名称										返回值(结果)类型				说明
    ADD_MONTHS(列\日期常量 ,月数);					日期							在指定日期增加若干月份后的日期
    MONTHS_BETWEEN(列\日期常量,列\日期常量);		数值							返回两个日期所经历过的月份数
    LAST_DAY(列\日期常量);							日期							返回指定日期所在月份的最后一天的日期
    NEXT_DAY(列\日期常量, '星期X' );				日期							下一个星期X的日期
    
    -- ADD_MONTHS(列|日期常量 ,月数);
    -- 查看4个月后的日期。
    SELECT SYSDATE AS 当前日期, ADD_MONTHS(SYSDATE,4) AS "4个月后的日期" FROM dual;
    
    -- 查看 姓名为 SCOTT 的雇员 雇佣日期前一个月的日期。
    SELECT hiredate 雇佣日期,ADD_MONTHS(hiredate,-1) 前一个月的日期 FROM emp WHERE ename='SCOTT';
    
    MONTHS_BETWEEN(列|日期常量,列|日期常量);
    -- 查看 所有雇员到今天为止 所雇佣的月份数。
    SELECT hiredate 雇日期, SYSDATE 当前日期,MONTHS_BETWEEN(SYSDATE,hiredate) 所雇佣的月份数 FROM emp;
    
    
    -- LAST_DAY(列|日期常量);
    -- 查看当前时间日期所在月份的最后一天的日期。
    SELECT LAST_DAY(SYSDATE) FROM dual;
    
    -- 查看 姓名为 BLAKE 的雇员 雇佣日期所在月份的最后一天的日期。
    SELECT ename,hiredate,LAST_DAY(hiredate) FROM emp WHERE ename='BLAKE';
    
    -- 查看 所有雇佣日期在雇佣日期所在月末10天内的雇员信息。(即在月末-10 至 月末)
    -- SELECT * FROM emp WHERE LAST_DAY(hiredate)-hiredate BETWEEN 0 AND 10;
    SELECT * FROM emp WHERE  hiredate <= LAST_DAY(hiredate) AND hiredate>= LAST_DAY(hiredate)-9;
    
    
    
    -- NEXT_DAY(列|日期常量, 星期X );
    -- 查看下一个星期一的日期。（当前日期为2017年4月10日 星期一）
    SELECT NEXT_DAY('10-4月-17','星期一') FROM dual;
    
    -- 查看下一个星期三的日期。（当前日期为2017年4月10日 星期一）
    SELECT NEXT_DAY(SYSDATE,'星期三') FROM dual;
    
    
    
    -- 转换函数
    我们已经学过了 字符串函数、数值函数、日期函数 ，那么这三类数据之间也是可以相互转换的。Oracle提供了以下三个常用转换函数。
    
    函数名称	返回值(结果)类型	说明
    TO_NUMBER(列、字符串);	数值	将字符串转换为数值
    TO_CHAR(列、数值、日期,转换格式);	字符串	将数值类型或者日期类型转换为字符串类型
    TO_DATE(列、字符串,转换格式);	日期	将指定字符串通过转换格式转换为日期
    
    -- TO_NUMBER(列|字符串);
    -- 将 '13' 字符串数值内容和 '22' 字符串数值内容 相加。
    SELECT TO_NUMBER('13')+TO_NUMBER('22') FROM dual;
    
    注意：Oracle 带有自动转换的功能，上面的Sql 语法可以简写成
    SELECT '13' +'22' FROM dual;
    
    所以说： TO_NUMBER()函数 意义不大。了解即可
    
    -- TO_CHAR(列|数值|日期,转换格式);（转换函数的重点）
    
    常用的日期时间格式化符号：年(yyyy)、月(mm)、日(dd) 、时(hh/hh24)、分(mi)、秒(ss)、星期几(dy)
    -- 将当前日期转为字符串一。
    SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') FROM dual;
    -- 将当前日期转为字符串二。（实现日期时间的拆分）
    SELECT TO_CHAR(SYSDATE,'yyyy') 年,TO_CHAR(SYSDATE,'mm') 月,TO_CHAR(SYSDATE,'dd') 日,TO_CHAR(SYSDATE,'hh24') 时,TO_CHAR(SYSDATE,'mi') 分,TO_CHAR(SYSDATE,'ss') 秒,TO_CHAR(SYSDATE,'dy') 星期 FROM dual;
    
    
    -- 常用的数值格式化符号：任意数字(9)、本地货币符号(L)。
    SELECT TO_CHAR(12345678,'L999,999,999') FROM dual;
    
    
    -- TO_DATE(列|字符串,转换格式);（与java类似）
    
    -- 将 '2020-02-02' 字符串转为日期格式。
    SELECT TO_DATE('2020-02-02','yyyy-mm-dd') FROM dual;
    -- 现在有一个时间'2018-08-08'的字符串。计算出该时间与当前时间相隔的天数。
    SELECT TO_DATE('2018-08-08','yyyy-mm-dd')-SYSDATE FROM dual;
    
    
    
    -- 通用函数
    
    通用函数一般指Oracle特色函数，在Oracle的函数发展中，有部分函数内部的实现也是通用函数演变过来的。那么Oracle主要有两个通用函数：
    
    函数名称				返回值(结果)类型	说明
    NVL(列(数值),默认数值);		数值	针对指定数值列做非空处理，如果数值为NUll，则返回默认值
    DECODE(列、数值、字符串,比较内容1,结果内容1,比较内容2,结果内容2……[,默认值]);	数据类型	类似于 java的 Switch，判断等值情况，将传入的数值或字符串，与每一个比较内容比较，比较成功则返回对应的结果内容。如果都比较失败，则返回NULL，如果有默认值，则返回默认值。
    
     -- NVL(列(数值),默认数值);
    -- 查看 emp 的雇员编号、姓名、基本工资、佣金、月总收入(薪资+提层)
    SELECT empno,ename,sal,comm,sal+NVL(comm,0) 总收入 FROM emp;
    
    -- DECODE(列|数值|字符串,比较内容1,结果内容1,比较内容2,结果内容2……[,默认值]);
    -- 查询 雇佣编号、姓名、职位(中文显示)。
    SELECT empno,ename,DECODE(job,'MANAGER','经理','CLERK','办事员','SALESMAN','销售','其它') 
     FROM  emp;

本文中使用的数据表可在 《SQL 基本语句》文章中查看 [点击打开链接][Link 1]

[Link 1]: http://blog.csdn.net/benxiaohai888/article/details/77655709