概述
本篇博文中主要探讨以下内容：
- Oracle中的单行函数和多行函数
- 重点：to_char和to_date
Oracle函数
函数分为系统内置函数 自定义函数（ plsql中定义）；了解系统内置函数（方法），重点掌握 to_date，to_char （字符和日期的转换）根据函数的返回结果，我们将函数分为单行函数和多行函数
- 单行函数:一条记录返回一个结果
- 多行函数 组函数 聚合函数 (重点) :多条记录 返回一
个结果 (重点）
1. 单行函数
1.1. 日期函数
日期函数: 注意区分 db数据库时间 ，java应用服务器的时间。以一方为准
- oracle以内部数字格式存储日期年月日小时分钟秒
- sysdate/current_date 以date类型返回当前的日期
- add_months(d,x) 返回加上x月后的日期d的值
- last_day(d) 返回的所在月份的最后一天
- months_between(date1,date2) 返回date1和date2之间月的数目
- next_day(sysdate,星期一) 下周星期一
1.1.1. 当前时间
select current_date from dual where 1=1;
select sysdate from dual where 1=1;
1.1.2. 修改日期（天数加+减-）
--两天后的时刻
select sysdate+2 from dual;
1.1.3. add_months() 修改月份
--当前5个月后的时间
select add_months(sysdate,5) from dual;
--雇佣日期 2个月的时间
select ename,hiredate,add_months(hiredate,2) from emp;
1.1.4. months_between() 月份之差
--雇佣日期 距离现在的 月份数
select ename, months_between(sysdate ,hiredate) from emp;
1.1.5. last_day() 最后一天
--返回雇佣日期 当月最后一天的时间
select ename, last_day(hiredate) d from emp;
1.1.6. next_day() 下一个星期的时间
--下一个星期二
select next_day(sysdate, '星期二') from dual;
1.2. 转换函数（重点）
- to_date(c,m) -> 字符串以指定格式转换为日期
- to_char(d,m) -> 日期以指定格式转换为字符串
select to_date('2017-3-21 18:12:12', 'yyyy-mmdd hh24:mi:ss') time from dual;
select to_char(sysdate, 'yyyy-mm-dd') from dual;
select to_char(sysdate, 'yyyy/mm/dd') from dual;
select to_char(sysdate, 'yyyy\mm\dd') from dual;
--注意中文的问题
--select to_char(sysdate,'yyyy年mm月dd日') from dual;
select to_char(sysdate, 'yyyy"年"mm"月"dd"日"') from dual;
1.3. 查询82的员工信息
select * from emp where hiredate like '%82%' ;
或
select * from emp where to_char(hiredate,'yyyy')='1982';
或
select * from emp where hiredate between to_date('1982-01-01',
'yyyy-mm-dd') and to_date('1982-12-31', 'yyyymm-dd')
1.4. 其他函数 (保证类型兼容)
1.4.1 nvl() 函数，用来处理null
nvl(string1,string2) 如果string1为null,则结果为string2的值
select ename, nvl(null,0) from emp;
select ename, nvl(to_char(comm),'hello') from emp;
1.4.2 decode() 函数（Oracle 特有）
--语法
decode(condition,case1,express1,case2 ,express2,….casen , expressn, expressionm)
如果值condition等于case1则返回express1,等于case2 返回express2...等于casen返回expressn,都不等于返回expressionm
很像Java中的switch
--举例
select ename,decode(deptno, 10,'十',20,'二十') from emp;
1.4.3 case when then else end函数
--栗子
--给所有的员工 涨薪,10-->10% 20-->8% 30 -->15% 其他-->20% --decode
select ename,sal,deptno,
decode(deptno,
10,sal * 1.1,
20,sal * 1.08,
30,sal * 1.15,
sal * 1.2) raisesal
from emp;
--case when then else end 简单的case
--语法
CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
ELSE default_result
END
--应用
select ename,sal,deptno,
(case deptno
when 10 then sal * 1.1
when 20 then sal * 1.08
when 30 then sal * 1.15
else sal * 1.2
end) raisesal
from emp
--扩展case
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE default_result
END
--相比第一个case，少了case后面的search_expression，有什么区别呢，扩展case可以在when 后面加条件
--例如 按工资范围给出级别
select ename,sal,
case
when sal >3000 then '高'
when sal>2000 then '中'
else '低'
end as "级别"
from emp order by sal desc;
注意：decode没有end，而case和后面有end
1.4.4 replace() 函数
--把zhangsan中的zhang替换成wang
select replace('zhangsan','zhang' ,'wang') from dual;
--不指定被替换的字符则为删除
select replace('zhangsan','zhang') from dual;
--用||拼接两个字符串
select (' zhangsan') || 'is a student' from dual;
1.4.5 trim() 函数
--去掉左右空格
select trim(' zhangsan') || 'is a student' from dual;
--删除字符串的前后某个字符(只能是一个)
select trim('z' from 'zzhangsanzz') from dual;
--功能同上
select trim(both 'z' from 'zzhangsanzz') from dual;
--只去除左边的某个字符
select trim(leading 'z' from 'zzhangsanzz') from dual;
--只去除右边的某个字符
select trim(trailing 'z' from 'zzhangsanzz') from dual;
1.4.6 round() 函数
--round 函数
SQL> select round(1234.456,2) from dual;
ROUND(1234.456,2)
-----------------
1234.46
SQL> select round(1234.756) from dual;
ROUND(1234.756)
---------------
1235
SQL> select round(1238.456,-1) from dual;
ROUND(1238.456,-1)
------------------
1240
1.4.7 trunc() 函数
--trunc函数
SQL> select trunc(1238.456,2) from dual;
TRUNC(1238.456,2)
-----------------
1238.45
SQL> select trunc(1238.456) from dual;
TRUNC(1238.456)
---------------
1238
SQL> select trunc(1238.456,-1) from dual;
TRUNC(1238.456,-1)
------------------
1230
1.4.8 mod() 函数
--mod 函数
SQL> select mod(3,2) from dual;
MOD(3,2)
----------
1
1.4.9 按月round()和trunc()
--按月取四舍五入
SQL> select round(sysdate,'MONTH') from dual;
ROUND(SYSDATE,'MONTH')
----------------------
2013-10-1
SQL> select round(to_date('20121-10-23','YYYY-MM-DD'),'MONTH') from dual;
ROUND(TO_DATE('2013-10-23','YYYY-MM-DD'),'MONTH')
------------------------------
2013-11-1
--按年四舍五入
SQL> select round(sysdate,'YEAR') from dual;
ROUND(SYSDATE,'YEAR')
---------------------
2014-1-1
SQL> select round(to_date('2013-1-23','YYYY-MM-DD'),'YEAR') from dual;
ROUND(TO_DATE('2013-1-23','YYY
------------------------------
2013-1-1
--按月取整
SQL> select trunc(sysdate,'MONTH') from dual;
TRUNC(SYSDATE,'MONTH')
----------------------
2013-10-1
SQL> select trunc(to_date('2013-10-23','YYYY-MM-DD'),'MONTH') from dual;
TRUNC(TO_DATE('2013-10-23','YY
------------------------------
2013-10-1
--按年取整
SQL> select trunc(sysdate,'Year') from dual;
TRUNC(SYSDATE,'YEAR')
---------------------
2013-1-1
SQL> select trunc(to_date('2013-1-23','YYYY-MM-DD'),'YEAR') from dual;
TRUNC(TO_DATE('2013-1-23','YYY
------------------------------
2013-1-1
1.5. 分析函数 (了解)
select ename, sum(1) over(order by sal desc) aa from emp;
select deptno,ename,sal,
sum(sal) over(order by ename) 连续求和,
sum(sal) over() 总和, -- 此处sum(sal) over () 等同于sum(sal)
100 * round(sal / sum(sal) over(), 4) "份额(%)"
from emp;
select deptno,ename,sal,
sum(sal) over(partition by deptno order by ename) 部门连续求和, --各部门的薪水"连续"求和
sum(sal) over(partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变
100 * round(sal / sum(sal) over(partition by deptno), 4) "部门份额(%)",
sum(sal) over(order by deptno, ename) 连续求和, --所有部门的薪水"连续"求和 sum(sal)
over() 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
100 * round(sal / sum(sal) over(), 4) "总份额(%)"
from emp;
2. 多行|聚合|组函数
组函数|多行函数|聚合函数 即多条记录 返回一个结果。
我们需要掌握如下几个组函数：
avg 、sum、 min、max、count
- count :统计记录数 count() ; --》* 或一个列名
- max min: 最大值 最小值
- sum：求和
- avg:平均值
注意:
1. 组函数仅在选择列表和Having子句中有效
2. 出现组函数，select 只能有组函数或分组字段
说明：
- 组信息 与单条记录不能同时查询
- 组函数 不能用在 where中，能使用的地方 select having
- null 不参与运算
2.1. count： 求和
--1、count统计所有的员工数
--1)、*
--2)、主键
--3)、推荐
select ename,1 from emp;
select count(1) from emp where 1=1;
--2、null不参与运算
--存在佣金的员工数
--不推荐/不需要
select count(comm) from emp where comm is not null; --推荐
select count(comm) from emp;
--统计 部门编号30的员工数
select count(1) from emp where deptno=30;
--统计数量过程中 ，可能处理重复
--统计 存在员工的 部门数量
select count(distinct(deptno)) 有人的部门 from emp;
--统计10和20部门一共有多少人
select distinct(count(1)) from emp where deptno in(10,20);
2.2. max min: 最大值 最小值
--查询所有员工的 最高薪水 ，最低薪水，员工总数 -->组
信息
select max(sal) maxSal , min(sal) minSal ,count(1) from emp;
--查询 最高薪水的员工名称 及薪水
--组信息 与单条记录不能同时查询
select max(sal), ename, sal from emp;
2.3. sum：求和
-- 查询10部门的所有员工的工资总和
select sum(sal) from emp where deptno=10;
2.4. avg: 平均
-- 查询工资低于平均工资的员工编号，姓名及工资
select empno, ename,sal from emp where sal<(select avg(sal) from emp);
--查看 高于本部门平均薪水员工姓名
select * from emp e1 where sal>(select avg(sal) from emp e2 where e1.deptno=e2.deptno);
3. 分组
分组: group by , 将符合条件的记录 进一步的分组
过滤组:having, 过滤组信息 ，表达式同where 一致
现在的结构如下
select distinct * | 字段 | 表达式 | 函数 as 别名
from 表 表别名
where 过滤行记录条件
group by 分组字段列表
having 过滤组
order by 字段列表 asc | desc
解析步骤
1、from 2、where 3、group 4、having 5、select 6、order by
group by : 分组
1)、select出现分组函数,就不能使用 非分组信息，可以使用group by 字段
2)、group by字段 可以不出现 select 中 ，反之select 除组函数外的，其他字段必须出现在group by中
having : 过滤组
- where : 过滤行记录，不能使用组函数
- having : 过滤组 可以使用组函数
--按 部门 查询 平均工资
select avg(sal) from emp group by deptno;
--按 部门岗位 查询 平均工资
select avg(sal) from emp group by deptno,job;
--按 部门 查询 平均工资，且平均工资大于2000的部门编号
--1、先分组 后过滤 (不推荐)
select * from (select deptno, avg(sal) avsal from emp where 1 = 1 group by deptno) where avsal > 2000;
--2、过滤组 ,分组同时 过滤
select avg(sal), deptno from emp group bydeptno having avg(sal)>2000;
--查询 最低平均工资的部门编号
--1)、按部门求出平均薪水
select avg(sal) from emp group by deptno;
--2)、找出最低的平均薪水
select min(avg(sal)) from emp group by deptno;
--3)、过滤组
select deptno from emp where 1 = 1 group by deptno having avg(sal) = (select min(avg(sal)) from emp where 1 = 1 group by deptno);
--查看 高于本部门平均薪水员工姓名
--1、按部门求出平均薪水
--2、关联子查询
select *
from emp e
where exists
(select deptno
from (select deptno, avg(sal) avgsal
from emp group by deptno) e2
where e.deptno = e2.deptno and e.sal > avgsal);
--另外一种 (推荐)
select * from emp e1 where sal > (select avg(sal) from emp e2
where e2.deptno = e1.deptno);
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· 单元测试从入门到精通
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 上周热点回顾（3.3-3.9）
· winform 绘制太阳，地球，月球 运作规律