Oracle（二） 函数

`-- 单行函数：每次取一条记录作为函数的参数，得到这条记录对应的单个结果 select ename,length(ename) from emp;

-- 多行函数：一次性的把多条记录当作参数输入给函数，得到多条记录对应的单个结果 select max(sal) from emp;

-- 字符函数 -- LOWER ---> 转换为小写 select * from emp where lower(ename) = 'allen';

-- UPPER ---> 转换为大写 select * from emp where ename = UPPER('smith');

-- INITCAP ---> 首字母大写。其他小写 SELECT ENAME,initcap(ENAME) FROM EMP WHERE initcap(ENAME) = 'Smith';

-- CONCAT ---> 将俩个字符串数据拼接在一起 等同于 || select empno || ename,concat(empno,ename) from emp;

-- 取子字符串 select ename,substr(ename,1,2) from emp;

-- INSTR 在一个字符串中搜索指定的字符，返回发送指定的字符位置 select ename,INSTR(ename,'A') from emp;

-- LPAD(c1,n[,c2]) 在字符串c1的左边用字符串c2填充，直到长度为 n 时停止 -- RPAD(c1,n[,c2]) 在字符串c1的右边用字符串c2填充，直到长度为 n 时停止 select sal,LPAD(sal,10,'*'),RPAD(sal,10,'#') from emp;

-- REPLACE(c1,c2[,c3]) 将字符表达式值中部分相同的字符串，替换成新的字符串 -- c1希望被替换的字符串或者变量 c2被替换的字符串 c3要替换的字符串 select ename,REPLACE(ename,'A','a') from emp;

-- 数值函数 -- ROUND 4舍5入到指定的小数位 select 45.943 "数值", ROUND(45.943,2) "小数点后俩位", ROUND(45.943,0) "个位", ROUND(45.943,-1) "十位" from SYS.DUAL;

-- TRUNC 将值截断到指定的小数位 select 45.943 "数值", TRUNC(45.943, 2) "截断到小数点后俩位", TRUNC(45.943, 0) "截断到个位", TRUNC(45.943, -1) "截断到十位" from SYS.DUAL;

-- MOD 返回相除后的余数 select empno,ename,sal,MOD(sal,300) from emp where empno = 7369;

-- 日期函数 select Sysdate from sys.dual;

select * from emp where hiredate = '1981/2/22'; -- 查不出来

select * from emp where hiredate = '22/2月/1981';

-- 俩个日期型的数值相减得到天数 select ename 员工姓名,sysdate "当前日期",hiredate 入职日期,ROUND((sysdate - hiredate)/365) "年薪" from emp;

-- 俩个日期之间相差多少个月 精确计算 MONTHS_BETWEEN(d1,d2),返回结果是日期d1到日期d2之间的月数 select ename 员工姓名,sysdate "当前日期",hiredate 入职日期,(sysdate - hiredate)/30 "不精确月数",months_between(sysdate,hiredate) 精确月数 from emp;

-- 一个日期加上若干个月 精确计算 add_months(d1,d2) select ename "员工姓名", hiredate "雇佣日期", (hiredate + 90) "粗略的转正日期", add_months(hiredate, 3) "精确的转正日期" from emp;

-- next_day 取得从当前日期开始遇到的第一指定星期几的日期 select sysdate 当前日期, next_day(sysdate, '星期一') 下个星期一, next_day(sysdate, '星期二') 下个星期二, next_day(sysdate, '星期三') 下个星期三, next_day(sysdate, '星期四') 下个星期四, next_day(sysdate, '星期五') 下个星期五, next_day(sysdate, '星期六') 下个星期六, next_day(sysdate, '星期日') 下个星期日 from emp;

-- LAST_DAY 指定日期所在月份的最后一天的日期 select ename, hiredate, last_day(hiredate), sysdate "当前日期", LAST_DAY(sysdate) "本月最后一天的日期" from emp;

-- 对日期型的数据进行截取 -- 取得按年或月进行四舍五入得到的新日期 ROUND select sysdate "当前日期", round(sysdate) "最近的0点日期", round(sysdate, 'day') "最近的星期日", round(sysdate, 'month') "最近的月初", round(sysdate, 'q') "最近的季初日期", round(sysdate, 'year') "最近的年初日期" from dual;

-- 取得按年或月进行截取得到的新日期 TRUNC select sysdate "当前日期", TRUNC(sysdate) "最近的0点日期", TRUNC(sysdate, 'day') "最近的星期日", TRUNC(sysdate, 'month') "最近的月初", TRUNC(sysdate, 'q') "最近的季初日期", TRUNC(sysdate, 'year') "最近的年初日期" from dual;

-- 数据类型转换函数 -- 转化有俩种方式：1.隐式转换 2.手动转换 select * from emp where deptno = '30'; -- 隐式转换，应为 deptno 本身是数值型，在这里将字符型 '30'，转换为数值型 30

-- to_char(date,'fmt') 把日期型转换为字符型 1.必须用单引号 '' 括起来，并且是大小写敏感 2.可包含任何有效的日期格式 select empno,ename,hiredate,to_char(hiredate,'YYYY-MM-DD') from emp where to_char(hiredate,'YYYY-MM-DD') = '1981-02-20';

select empno,ename,hiredate,to_char(hiredate,'YYYY-MM-DD HH24:MI:SS AM') from emp where to_char(hiredate,'YYYY-MM-DD') = '1981-02-20';

select sysdate,to_char(sysdate,'YYYY-MM-DD HH24:MI:SS DAY PM') from sys.dual;

-- to_char(number,'fmt') 把数值型转换为字符型 select ename,sal,to_char(sal,'$999,999.00'),to_char(sal,'L000,000.00') from emp;

-- to_number(char,'fmt') 把字符型转换为数值型 模板和字符格式必须一致 select to_number('$2,975.00','$999,999.00') from sys.dual;

-- to_date 把字符型转换为日期型 select * from emp where hiredate = to_date('1981/2/22','YYYY-MM-DD');

-- 其他函数： -- 在算数表达式中出现 null ，得到的结果就是 null -- NVL(COMM,0) 如果 COMM字段 为 null，则用 0 来替换该 null select empno,ename,sal,comm,(sal*12+comm) "年收入1",(sal * 12 + NVL(COMM,0)) "年收入2" from emp;

-- NVL(COMM,0) 注意：字段 COMM 的类型必须要与 0 的类型一致，如下： select ename,job,NVL(JOB,'还没有工作') from emp where empno = 7782;

select ename, hiredate, NVL(hiredate, '02-5月-1998') from emp where empno = 7654;

-- NVL2(expr,expr1,expr2) 如果表达式 exprb 不为null，则返回 expr1;若能为 null，则返回expr2 select ename, job, NVL2(job, JOB, '没有工作') from emp;

-- NULLIF(expr1,expr2) 比较俩个表达式，如果相等返回空值，如果不等，返回第一个表达式 select ename, job, NULLIF(length(ename), length(ename)), NULLIF(length(ename), length(job)) from emp;

-- CASE 表达式 实现逻辑：IF-WHEN-THEN-ELSE 的功能 SELECT ename, job, sal, case job when 'CLERK' then 1.10 * sal when 'SALESMAN' then 1.3 * sal when 'PRESIDENT' then 1.45 * sal else sal end as "修订工资数" FROM emp where ename = 'WARD';

-- DECODE 函数 类似于一系列的 CASE 或 IF-THEN-ELSE 语句 SELECT ename, job, sal, DECODE(job, 'CLERK', sal * 1.10, 'MANAGER', sal * 1.3, 'PRESIDENT', sal * 1.45, sal) as "修订工资数" FROM emp where ename = 'TURNER'

-- 嵌套函数 -- 组函数，就是我们前面提到的多行函数 所有的组函数都是忽略空值的 -- AVG 平均值 -- COUNT 条数 -- MAX 求最大值 -- MIN 求最小值 -- SUM 求总值

-- AVG,SUM只能针对数值型的数据 MAX,MIN,COUNT可以针对任何类型的数据 SELECT MAX(sal) 最大值,MIN(sal) 最小值,AVG(sal) 平均值,SUM(sal) 总值 FROM emp;

SELECT MAX(ename),MIN(ename) FROM emp;

SELECT MAX(hiredate),MIN(hiredate) FROM emp;

-- COUNT有俩种用法 -- 1.COUNT() 查询数据的总条数 select COUNT() from emp;

-- 2.COUNT(字段) 这种情况忽略空值 select COUNT(COMM) from emp;

-- 所有的组函数都是忽略空值的 select SUM(COMM),AVG(COMM),COUNT(COMM) from emp;

-- 按照人数算平均奖金 SELECT SUM(COMM) / COUNT(*),AVG(NVL(COMM,0)) FROM emp;

-- 对数据进行分组后，使用组函数 分组-->GROUP BY SELECT MAX(sal) FROM emp group by deptno;

-- 1.出现在查询列表中的字段，要么出现在组函数中，要么出现在group by 子句中 -- 尺子 -- 2.要么只出现在group by 中 -- 尺子 SELECT deptno,MAX(sal) FROM emp group by deptno;

-- 按照多个字段进行分组 SELECT deptno,job,MAX(sal) FROM emp group by deptno,job ORDER BY DEPTNO; -- ORDER BY 排序，默认从小到大

-- 对分组以后的数据进行过滤，过滤出 >= 3000 的记录 过滤不能使用 WHEN子句，而是要使用 HAVING 子句进行过滤 -- 先分组，在进行过滤 SELECT DEPTNO,MAX(sal) from emp GROUP BY DEPTNO HAVING MAX(SAL) >= 3000 ORDER BY DEPTNO;

-- 首先用 where 对数据过滤，过滤后数据用 group by 进行分组处理，分组后的数据用 having 进行再次过滤，最后将过滤后的数据进行 order by 进行排序 SELECT DEPTNO,MAX(SAL) FROM emp WHERE DEPTNO IS NOT NULL GROUP BY DEPTNO HAVING MAX(SAL) >= 3000 ORDER BY DEPTNO;

-- 组函数也可以嵌套 -- 在组函数嵌套的时候，必须要使用 ORDER BY进行分组 -- 组函数最多能嵌套俩层 select max(sal) from emp where deptno is not null group by deptno order by deptno;

select MAX(max(sal)) from emp where deptno is not null group by deptno order by deptno;

SELECT * FROM emp for update; `