Oracle笔记之函数

最新推荐文章于 2024-07-18 14:34:06 发布

JAVA开发区

最新推荐文章于 2024-07-18 14:34:06 发布

阅读量397

点赞数 4

分类专栏： Oracle 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/qq_45609369/article/details/121523698

版权

Oracle 专栏收录该内容

27 篇文章 14 订阅

订阅专栏

概述
本篇博文中主要探讨以下内容：

Oracle中的单行函数和多行函数
重点：to_char和to_date

函数分为系统内置函数自定义函数（ 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);

JAVA开发区

关注

4
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
Oracle笔记之函数

概述本篇博文中主要探讨以下内容：Oracle中的单行函数和多行函数重点：to_char和to_dateOracle函数1. 单行函数1.1. 日期函数1.1.1. 当前时间1.1.2. 修改日期（天数加+减-）1.1.3. 修改月份1.1.4. 月份之差1.1.5. 最后一天1.1.6. 下一个星期的时间1.2. 转换函数（重点）1.3. 查询82的员工信息1.4. 其他函数 (保证类型兼容)1.5. 分析函数 (了解)2. 多行|聚合|组函数2.1. count：求和2.2. max min
复制链接

扫一扫