oracle使用（三）_函数

Oracle函数：
函数一般是在数据中执行的，方便对数据进行转换和处理，只查出的数据新型处理，不改变数据库中的数据

查询出来的数据相当于一张虚拟表

组函数（聚合函数）：输入多个参数，得出一个结果
组函数的使用场景：仅可用于选择列或者查询的having子句
-- 查询所有员工月薪水的总和

select sum(sal) from emp;

-- 查询表中有多少条记录
select count() from emp;
-- 按deptno进行分组，并求每个分组的记录数并找出大于3分组
select deptno, count() from emp group by deptno having count(*) > 3;
where只能查询表中实际存在的字段，而聚合函数的结果，where则不适合

单行函数：输入一个值,输出一个值

--字符函数

--concat：表示字符的连接等价 ||

select concat('my name is',ename) from emp;

--将字符串首字母转为大写
select initcap(ename) from emp;

--将字符串转为大写
select upper(ename) from emp;

--将字符串转为大写
select lower(ename) from emp;

--填充字符串规定长度为10，不足左侧用补全
select lpad(ename,10,'') from emp;

--填充字符串规定长度为10，不足右侧用补全
select rpad(ename,10,'') from emp;

-- 去除空格
select trim(ename) from emp;

--去左空格
select ltrim(ename) from emp;
--去右空格
select rtrim(ename) from emp;

-- 字符出现的位置
select instr(ename,'A') from emp;

select instr('BBBACCDD','A') from emp; //4

--查看字符串的长度
select length(ename) from emp;

--字符串截取操作
select substr(ename,0,2) from emp;
select substr('aaabbbccc',0,2) from emp;

-- 替换操作
select replace('ababefg','ab','hehe') from emp;

--数值函数

-- 小数四舍五入，指定小数位数为2
select round(123.123,2) from dual;

select round(123.128,2) from dual;

-- 截取两位小数，但不四舍五入
select trunc(123.128,2) from dual

-- 取模操作
select mod(10,4) from dual;

--向上取整
select ceil(12.56) from dual;
select ceil(12.11) from dual;

--向下取整
select floor(13.99) from dual;

-- 求绝对值
select abs(-100) from dual;

--求正负 1表示正 -1表示负 0表示0
select sign(100) from dual;

-- 求2^3
select power(2,3) from dual;

日期函数

-- 查询系统时间
select sysdate from dual;

-- 查询当前日期
select current_date from dual;

-- 查出的月份+2
select add_months(hiredate,2),hiredate from emp;

--返回本月最后一天
select last_day(sysdate) from dual;

--返回的两个时间间隔月份数
select months_between(sysdate,hiredate),sysdate,hiredate from emp;

-- round(sysdate) 最近的0点日期，最近的星期日，最近的月初，最近的季初，最近的年初
select sysdate,round(sysdate),round(sysdate,'day'),round(sysdate,'month'),round(sysdate,'q'),round(sysdate,'year') from dual;

-- 求下周的星期一日期
select next_day(sysdate,'星期一') from dual;

-- 提取日期中得时间

select extract (year from sysdate) year, extract (month from sysdate) month, extract (day from sysdate) day from dual;

select extract (year from date '2015-05-04') year, extract (month from date'2015-05-04') month, extract (day from date '2011-05-04') day from dual;

--- Oracle INTERVAL数据类型 date和timestamp用于存储时间点，interval用于存储时间段
-- interval 使用和含义https://www.yiibai.com/oracle/oracle-interval.html

-- INTERVAL '999' DAY(3) 含义999天
-- INTERVAL '11 10:09' DAY TO MINUTE 11天 10小时09分
-- INTERVAL '09:30' HOUR TO MINUTE 9小时30分钟
--给指定的时间单位添加数值

-- 当前时间+1s
select trunc(sysdate) + (interval '1' second),
-- 当前00点+一小时
trunc(sysdate) + (interval '01:00:00' hour to second),
-- 当前日期+1
trunc(sysdate) + (interval '1' DAY)
from dual;

-- trunc函数的使用 https://blog.csdn.net/qq_29171935/article/details/89478520

--- trunc可截取数值也可截取日期
-- 截取当年第一天
select trunc(sysdate,'yy') from dual;

-- 截取到当月第一天
select trunc(sysdate, 'mm') from dual
--截取到当当前天，也就是当前年月日
select trunc(sysdate,'dd') from dual;
select trunc(sysdate,'d') from dual;
select trunc(sysdate, 'hh') from dual

/**
转换函数:

隐式转换：

显示转换：

虽然有隐式转换，但最好使用显示转换保持可读性

to_char():将日期、数值转为字符串

to_date()：将字符串、数值转日期

to_number：将字符串转数值
*/

-- 隐式转换
select '999'-1 from dual;

--显示转换
-- 日期转字符串
select to_char(sysdate,'yyyy-MM-dd') from dual;
select to_char(sysdate,'yyyy-MM-dd HH24:Mi:ss') from dual;

-- 数值转char 转为4位数字，9是控制符，该位没有则不显示
select to_char(123.456789,'9999') from dual;
-- 0是控制字符，该位没有，则强制显示0
select to_char(123.456789,'0000') from dual;

-- 9和.都是控制符 .表示显示小数点
select to_char(123.456789,'9999.999') from dual;

-- $显示美元符号 select to_char(123.456789,'$ 9999.99') from dual;

--L 显示本地货币符号
select to_char(123.456789,'L9999.99') from dual;

--,表示千分字符
select to_char(123456789,'999,999,999') from dual;

--to_date:转换之后都是固定格式 2019/10/10 10:10:10

select to_date('2019/10/10 10:10:10','YYYY-MM-DD HH24:MI:SS') from dual;

-- to_number（数值字符串，格式）

select to_number('123,456,789','999,999,999') from dual;

-- 显示没有上级管理的首脑 mgr是数值类型，默认值没法是字符串所以做to_char转换
select ename,nvl(to_char(mgr),'boss') from emp where mgr is null;

--显示员工雇佣期满6个月后下个星期五的日期
--显示员工雇佣时间满6个月后，下个星期五日期
-- https://www.cnblogs.com/ray-bk/p/10239119.html
select hiredate,next_day(add_months(hiredate,6),6) from emp;
select hiredate,next_day(add_months(hiredate,6),'星期五') from emp;

/**
条件函数
decode()函数：当列为值1时，值为解码值1，以此类推
decode（列名，值1,解码值1,值2,解码值2,...值n,解码值n）
任何非null值与null求max或min，结果为前者
case when end语句
/
--给不同部门员工涨薪，10部门涨10%，20部门涨20%，30部门涨30%
select deptno,ename,sal,decode(deptno,10,1.1sal,20,1.2sal,30,1.3sal) from emp;

select deptno,ename,sal,case deptno when 10 then 1.1sal when 20 then 1.2sal when 30 then 1.3*sal end from emp;

-- 行转列
create table test(
id number(10) primary key,
type number(10) ,
t_id number(10),
value varchar2(5)
);
insert into test values(100,1,1,'张三');
insert into test values(200,2,1,'男');
insert into test values(300,3,1,'50');

insert into test values(101,1,2,'刘二');
insert into test values(201,2,2,'男');
insert into test values(301,3,2,'30');

insert into test values(102,1,3,'刘三');
insert into test values(202,2,3,'女');
insert into test values(302,3,3,'10');
请写出一条查询语句结果如下：
姓名性别年龄

张三男 50

select max(decode(type,1,value)) 姓名,max(decode(type,2,value)) 性别,max(decode(type,3,value)) 年龄 from test group by t_id;

select min(decode(type,1,value)) 姓名,min(decode(type,2,value)) 性别,min(decode(type,3,value)) 年龄 from test group by t_id;

/**
group by 按照某些相同的值进行分组
group 进行分组操作的时候，可以使用表中实际存在的一个列或者多个列，
但在使用group by之后
选择列表只能包含组函数和group by子句包含的列
例如 ename既不是组函数也不是group by的列，会报错不是group by表达式
select deptno，avg(sal), ename from emp group by deptno having avg(sal) > 2000 ;

group by子句必须在where之后，order by之前
*/
-- 如果分组的列中有null值，null也为一个分组
select deptno from emp group by dempno;

---组函数
select avg(sal) from emp; //avg只适用数值类型

select min(sal) from emp; //min适用任何类型数据

select max(sal) from emp; //max适用任何类型数据

select sum(sal) from emp; //sum只适用数值类型

select count() from emp; //count一般用来获取记录的条数，获取记录条数时可用或者具体列，甚至可用数字，从性能角度推荐适用具体列和数字

-- count函数会跳过null，查询记录数时，使用具体列时需要注意不要选择可能有null值的列
select count(ename) from emp;

select count(1) from emp;

select count(1000) from emp;

-- nvl:空值转换函数，将null转为指定类型的值
select avg(nvl(comm,0)) from emp;

-- 求每个部门的平均薪水

select deptno,avg(sal) from emp group by deptno;

-- 求平均薪水大于1500的部门,此处过滤数据不能用where只能作用表中真实存在的列
select deptno，avg(sal) from emp group by deptno having avg(sal) > 2000 ;

/**
select语句的执行顺序
*/

image.png

-- 部门下雇员工资>2000的人数
select deptno,count(1) from emp where sal>2000 group by deptno;

-- 求部门薪水最高的
select deptno,max(sal) from emp group by deptno;

--求部门不同岗位最高工资
select deptno,job,max(sal) from emp group by deptno,job;

最后编辑于：2021.11.13 23:59:09

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 195,898评论 5赞 462
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 82,401评论 2赞 373
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 143,058评论 0赞 325
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 52,539评论 1赞 267
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 61,382评论 5赞 358
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 46,319评论 1赞 273
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 36,706评论 3赞 386
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 35,370评论 0赞 254
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 39,664评论 1赞 294
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 34,715评论 2赞 312
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 36,476评论 1赞 326
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 32,326评论 3赞 313
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 37,730评论 3赞 299
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,003评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,275评论 1赞 251
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 41,683评论 2赞 342
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 40,877评论 2赞 335

oracle使用（三）_函数

推荐阅读更多精彩内容