2. Oracle函数
-- 单行函数: 每一次去一条记录,作为函数的参数, 得到这条记录对应的单个结果
select ename, length(ename)
from emp;
-- 多行函数: 一次性的把多条记录当做参数输入函数, 得到多条记录对应的单个结果
select max(sal)
from emp;
-- 字符函数
select *
from emp
where lower(ename) = 'smith'; -- lower() 转换成小写
select *
from emp
where ename = upper('smith'); -- upper() 转换成大写
select *
from emp
where initcap(ename) = 'Smith'; --initcap 讲首字母转换成大写
select empno || ename, concat(empno, ename) -- concat() 连接两个字符, 与 "||" 一样
from emp;
select ename, substr(ename, 2, 3) -- substr(string, n1, n2) 从 第 n1 位开始取值, 取 n2 个, 省略 n2, 默认取到最后
from emp;
select ename, instr(ename, 'A') -- instr() 找 ename 中 第一次出现'A' 的下标, 没有则返回0;
from emp;
select sal, lpad(sal, 10, '*'), rpad(sal, 10, '#') -- lpad() 把 sal 写成10位, 不够的在左边填充
from emp; -- rpad() 把 sal 写成10位, 不够的在右边填充
select ename, replace(ename, 'A', 'a') -- replace 把 ename 中的 'A' 替换成 'a'
from emp;
-- 数值函数
select round(45.943, 2) "小数点后两位", --- round() 四舍五入
round(45.943, 0) "个位数",
round(45.943, -1) "十位数"
from sys.dual;
select ename, sal, mod(sal, 300) -- mod() 取余
from emp
where empno = 7369;
-- 日期函数
select sysdate from sys.dual; -- sysdate 默认年月日时分秒
select *
from emp
where hiredate = '20-2-1981';
select *
from emp
where hiredate = '20-2月-1981';
select empno, sysdate, hiredate, round((sysdate - hiredate) / 365 ) || '年' 工作年限
from emp;
select empno, sysdate, hiredate, round((sysdate - hiredate) / 30 ) || '月' 工作月份, round(months_between(sysdate, hiredate)) 更精确的工作月份
from emp;
select empno, hiredate 雇佣日期, (hiredate + 90) 粗略的转正日期, add_months(hiredate, 3) "精确的转正日期" -- add_month() hiredate + 3 个月后的日期
from emp;
select sysdate 当前日期, -- 从指定日期的下一个星期几 对应的日期
next_day(sysdate, '星期一') 下周星期一,
next_day(sysdate, '星期一') 下周星期二,
next_day(sysdate, '星期一') 下周星期三,
next_day(sysdate, '星期一') 下周星期四,
next_day(sysdate, '星期一') 下周星期五,
next_day(sysdate, '星期一') 下周星期六,
next_day(sysdate, '星期一') 下周星期日
from dual;
select ename, hiredate, last_day(hiredate) -- last_day() hireday 所在的月的最后一天
from emp;
select sysdate 当前日期,
round(sysdate) 最近0点日期,
round(sysdate, 'day') 最近星期日,
round(sysdate, 'month') 最近的月初,
round(sysdate, 'q') 最近的季初日期,
round(sysdate, 'year') 最近的年初日期
from dual;
-- 转换函数
-- 转换有两种方式, 隐式转换, 手动转换
select *
from emp
where deptno = '30'; -- deptno 是 字符型, 这里自动转换成 字符型
-- to_char() 数值转化为字符, 日期转化为字符
-- to_number() 字符转化成数值
-- to_date() 字符转化为日期 (符合日期形式的字符)
-- to_char(date, 'fmt') 必须用单引号括起来, 并且是大小写敏感, 可包含任何有效日期格式
-- YYYY, YYY, YY 分别代表 4位, 3位, 2位的数字年份
-- YEAR 年的拼写
-- MM 数字月
-- MONTH 月份的全拼名称
-- MON 月份的缩写
-- DD 数字日
-- DAY 星期的全拼
-- DY 星期的缩写
-- AM 表示上午或下午
-- HH24, HH12 24小时制 或 12小时制
-- MI 分钟
-- SS 分钟
-- SP 秒钟
-- TH 数字的序数词
-- "特殊字符" 在日其中加入特殊字符
-- TO_CHAR 把日期转化成字符
select empno, ename, hiredate, TO_CHAR(hiredate, 'yyyy-MM-DD, HH24:MI:SS AM')
from emp
where to_char(hiredate, 'YYYY-MM-DD') = '1980-12-17';
select sysdate, to_char(sysdate, 'yyyy-MM-DD, HH24:MI:SS AM DAY')
from sys.dual;
-- TO_CHAR 把数字转化成字符
-- 9 代表一个数字(有数字,就显示, 没有就不显示)
-- 0 强制显示0
-- $ 放置一个$符
-- L 放置一个本地货币符
-- . 显示小数点
-- , 显示千位指示符
select sal, to_char(sal,'$999.999.00'), to_char(sal, 'L000.000.00')
from emp;
-- TO_NUMBER 把字符型数据转为数值
select to_number('$333,00', '$999.999.00') -- 模板和字符的格式必须一致
from sys.dual;
-- to_date, 把字符型的数据转化为日期型数据
select *
from emp
where hiredate = to_date('1981-2-20', 'YYYY-MM-DD');
-- 其他函数
select empno, ename, sal, comm, (sal*12+comm) "年收入", (sal*12 + nvl(comm, 0)) 年收入2
from emp; -- nvl() comm的值 为 空, 则以0 代替
select ename, job, nvl(job, '还没有工作')
from emp
where empno = 7654;
select empno, ename, sal, comm, nvl2(job, '有工作'|| job, '无工作')
from emp -- nvl2() 如果不为空 则显示 '有工作'|| job, 否则显示 '无工作'
where empno = 7654;
-- NULLIF(expr1, expr2) 比较两个表达式, 如果相等放回空值, 不相等则返回第一个表达式
select ename, nullif(length(ename), length(ename)), nullif(length(ename), length(job))
from emp;
-- case 实现一个 if...else if ...else 的功能
select ename, job, sal,
case job
WHEN 'CLERK' THEN
1.10 *SAL
WHEN 'SALESMAN' then
1.3 * sal
else
sal
end as "修订工资数"
from emp
where ename = 'SMITH';
-- DECODE 更简洁的 if...else ... (功能与 case 一致)
select ename,
job,
sal,
decode(job,
'CLERK',
SAL*1.10,
'SALESHAN',
SAL*1.4,
SAL) AS "修订工资"
from emp
where ename = 'SMITH';
-- 组函数, 就是我们前面提到的多行函数, 把多行输入给函数
select max(sal)
from emp;
-- 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(*)
from emp;
select count(comm)
from emp;
-- 所有的组函数都是忽略空值的
select sum(comm), avg(comm), count(comm), sum(comm)/count(comm)
from emp;
-- 按照人数计算平均奖金
select sum(comm)/count(*), avg(nvl(comm, 0)), avg(comm)
from emp;
-- 对数据进行分组后,使用组函数
-- 1. 出现在查询列表中的字段, 要么出现在组函数中, 要么出现在 group by 子句中
-- 2. 也可以只出现在 group by 中
select max(sal)
from emp
group by deptno;
select deptno, max(sal)
from emp
group by deptno;
-- 按照多个字段进行分组
select deptno, job, max(sal)
from emp
group by deptno, job;
select deptno, max(sal)
from emp
group by deptno, job
select deptno, max(sal)
from emp; --- 注意这是个错误 deptno 没出现在 group by 中
-- 要对分组以后的数据进行过滤, 过滤>=3000 的记录, 不能使用 where 子句, 而是要用 group by 子句
select max(sal)
from emp
where max(sal)>=3000 -- 这是一个错误
group by deptno;
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
-- 组函数也可以嵌套
-- 在组函数嵌套的时候, 必须使用 group by
-- 组函数最多能嵌套两套
select deptno, max(sal)
from emp
where deptno is not null
group by deptno;
select max(max(sal)) -- 分组函数嵌套 ,必须使用 group by
from emp
where deptno is not null
group by deptno;
浙公网安备 33010602011771号