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;