1.Oracle 函数
Oracle 提供一系列用于执行特定操作的函数
2.单行函数
单行函数对于从表中查询的每一行只返回一个值
单行函数可以大致划分为:字符函数,日期时间函数,数字函数,转换函数,混合函数
字符函数
字符函数接受字符输入并返回字符或数值
initcap(char):将字符串中的英文首字母大写
lower(char): 将字符串全部转换成小写
upper(char): 将字符串全部转换成大写
trim(str):str表示字符串,表示去除字符串两端的空格
trim(s from str):str表示字符串,s表示字符,表示去除字符串两端的空格,如果两边加相同字符,表示去除字符串两端的字符
ltrim(str ,s):去除字符串str左边的空格或者字符s
rtrim(str ,s):去除字符串str右边的空格或者字符s
replace(str,s,d):替换函数
str字符串,s要替换的内容,d替换成内容,表示将字符中str中的s替换成d
instr(str,s,n1,n2):在字符串中找某个字符的位置
str字符串,s:要查询的字符,n1表示从位置开始查询,n2表示这个字符第几次出现
substr(str,index,len):字符串截取函数
str是一个字符串,index截取的开始位置,len截取的长度(第三个参数可省略)
concat (v1, v2): 将v1和v2拼接在一起
||:它是连接符
select 'aa'||'bb' from dual;
select concat(concat('aa','bb'),'cc') from dual;
select 'aa'||'bb'||'cc' from dual;
注意:字符串要用单引号括起来,在字符串(单引号中)中使用两个连着的单引号,这时第一个
单引号是一个转义符号
select '''' from dual;
select ',''' from dual;
select 'insert into dept values('||deptno||','''||dname||''','''||loc||''');' from dept;
chr(a) 它将ASCII码转换成字符
ascii(str)它将字符转换成ASCCII码
lpad(str,len,s)和rpad(str,len,s):
str表示字符串,len表示扩展后的字符串长度,
s:表示要扩展的内容,可选参数表示在字符串的左边填充空格或者s让字符串的长度到len的长度
length():返回长度
日期时间函数
日期函数对日期值进行运算,并生成日期数据类型或数值类型的结果
sysdate:当前的日期包含时间
select sysdate from dual;
add_months(date,n):给日期加减n个月
select add_months(sysdate,3) from dual;
select add_months(sysdate,-2) from dual;
日期直接加上一个整数,相当于加的天数
select sysdate-1 from dual;
months_between(date1,date2):求两个日期相差多少个月
select months_between(sysdate,to_date
('20210120','yyyyMMdd')) from dual;
last_day(date):取日期当前月的最后一天
next_day(date,char) date参数为日期型, char:可用1~7,1表示星期日,2代表星期一..
select next_day(sysdate,1) from dual;
数字函数
数字函数接受数字输入并返回数值结果
abs(x) 返回x的绝对值
select abs(-15) from dual;
sqrt(x) 返回x的平方根
select sqrt(9) from dual;
ceil(n) 取大于等于数值n的最小整数
select ceil(44.278) from dual;
floor(n):取整函数,只取整数部分,不进行四舍五入
select floor(3.41) from dual;
select floor(3.98) from dual;
power(m,n):求m的n次方
select power(2,8) from dual;
mod(m,n):求m除以n的余数的函数
select mod(3,2) from dual;
round(n,s):四舍五入函数,n表示数字,s表示精度,默认是0
select round(3.134) from dual;
select round(5.76) from dual;
select round(5.68,1) from dual;
trunc():截断函数
select trunc(3.13453,3) from dual; 截断数字类型,第二个参数表示精度,不会四舍五入
select trunc(3234.1324,-2) from dual;
select trunc(sysdate,'yyyy') from dual; --截取到年(本年的第一天)
select trunc(sysdate,'MM') from dual; --截取到月(本月的第一天)
select trunc(sysdate,'month') from dual; --截取到月(本月的第一天)
select trunc(sysdate,'day') from dual; --截取到周(本周第一天)
转换函数
转换函数将值从一种数据类型转换为另一种数据类型
to_char(date,format):将日期转换成字符串,date是一个日期,format:日期格式
yyyy:表示4位的年
MM:表示两位月
dd:表示两位的天
hh24:表示24小时制的小时
mi:表示分钟
ss:表示秒
day:表示星期
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'hh24:mi:ss') from dual;
select to_char(sysdate,'day') from dual;
to_date(str,format):将字符串转换成固定格式的日期,str是一个日期的字符串,format:日期格式
select to_date('1999-09-09 14:24:34','yyyy-MM-dd hh24:mi:ss') from dual;
to_number(str[,format]):将字符串转换成数字,str字符串,format格式字符串
select to_number('78.1234') from dual;
select to_number('$78.12','$99.99') from dual;
混合函数
DECODE()函数
DECODE 中的if-then-else逻辑
在逻辑编程中,经常用到If – Then –Else 进行逻辑判断。在DECODE的语法中,实际上就是这样的逻 辑处理过程。它的语法如下:
DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )
Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试,如果 value的值为if1,Decode 函数的结果是then1;如果value等于if2,Decode函数结果是then2;等等。 事实上,可以给出多个if/then 配对。如果value结果不等于给出的任何配对时,Decode 结果就返回 else 。
需要注意的是,这里的if、then及else 都可以是函数或计算表达式。
select decode(ssex,'男','男生','女','女生','未知') from student;
以下是几个用来转换空值的函数:
nvl(string1, replace_with):如果string1为NULL,
则NVL函数返回replace_with的值,否则返回string1的值。
select nvl(comm,200) from emp;
nvl2(e1, e2, e3):如果e1为NULL,则函数返回e3,若e1不为null,则返回E2。
select nvl2(comm,'有','没有') from emp;
nullif(e1,e2):如果e1和e2相等则返回空值,如果e1和e2不相等则返回e1的结果。
4.聚合函数(分组函数)
分组函数基于一组行来返回结果,为每一组行返回一个值
AVG():求平均值
MIN():求最小值
MAX():求最大值
SUM():求和
COUNT():求记录、数据个数
5.分析函数
分析函数用于计算完成聚集的累计排名、序号等,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值。分析函数为每组记录返回多个行
分析函数带有一个开窗函数over(),包含三个分析子句:
分组(partition by), 可省略
排序(order by),
窗口(rows) ,窗口就是分析函数分析时要处理的数据范围. 可省略
使用形式:over(partition by xxx order by xxx rows between xxx and xxx )
rows between xxx and xxx 可出现的关键词:
unbounded :无限的
preceding :在...之前
following:在...之后
current row:当前行
分析函数:max()over() min()over() avg()over() sum()over count()over()
--查询员工工资,并显示第一行到最后一行的工资汇总
select emp.*, sum(sal)over(order by sal
rows between unbounded preceding and unbounded following
)from emp
--查询员工工资,并显示第一行到当前行的工资汇总
select emp.*, sum(sal)over(order by sal
rows between unbounded preceding and current row
)from emp
--查询员工工资,并显示从当前行到最后一行的汇总
select emp.*, sum(sal)over(order by sal
rows between current row and unbounded following
)from emp
--查询员工工资,并显示上一行到当前行的汇总
select emp.*, sum(sal)over(order by sal
rows between 1 preceding and current row
)from emp
--查询员工工资,并显示当前行的上一行到当前行的下一行的汇总
select emp.*, sum(sal)over(order by sal
rows between 1 preceding and 1 following
)from emp
--查询员工工资,按照部门分组并显示每个组的工资总和
select emp.*, sum(sal)over(partition by deptno order by sal
rows between unbounded preceding and unbounded following
)from emp
--查询员工工资,按照部门分组并显示每个组从第一行到当前行的工资总和
select emp.*, sum(sal)over(partition by deptno order by sal
rows between unbounded preceding and current row
)from emp
注:普通的聚合函数用group by分组,每个分组返回一个统计值,而分析函数采用partition by分组, 并且每组每行都可以返回一个统计值。
first_value()与last_value() : 取首尾记录值
select emp.*, first_value(sal)over(order by sal)from emp
select emp.*, last_value(sal)over(order by sal
rows between unbounded preceding and unbounded following
)from emp
rank(),dense_rank()与row_number():求排序
以下三个分析函数用于计算一个行在一组有序行中的排位,序号从1开始
ROW_NUMBER 返回连续的排序,不论值是否相等
DENSE_RANK 具有相等值的行排序相同,序号是连续的
RANK 具有相等值的行排序相同,序数随后跳跃
6.行列转换
1.行转列
行转列的意思是将表中多行数据转成一行的多个字段输出。
需要行转列的业务逻辑通常是,将表中的一个字段分类统计后作为多个结果字段输出。
如:
create table studentScores(
username varchar2(20),
subject varchar2(30),
score number
);
insert into studentScores values('张三','语文',90);
insert into studentScores values('张三','英语',80);
insert into studentScores values('张三','数学',70);
insert into studentScores values('李四','语文',60);
insert into studentScores values('李四','英语',61);
insert into studentScores values('李四','数学',62);
commit;
select username,
sum(case when subject='语文' then score else 0 end) 语文,
sum(case when subject='英语' then score else 0 end) 英语,
sum(case when subject='数学' then score else 0 end) 数学
from studentScores group by username;
pivot函数:行转列的函数
也会进行分组,会把聚合函数用到的列与for后面用的列排除
使用剩下的列进行分组,会按照列名的值对应聚合
select username from studentScores pivot(sum(score)
for subject in('语文','英语','数学'));
2.列转行
列转行,将表中多个列,每个列拉成一行数据。
如:
create table studentScores2(
username varchar2(20),
yuwen number,
yingyu number,
shuxue number
);
insert into studentScores2 values('张三',90,80,70);
insert into studentScores2 values('李四',60,61,62);
select username,'yuwen' as subject,yuwen as score from studentScores2
union all
select username,'shuxue',shuxue from studentScores2
union all
select username,'yingyu', yingyu from studentScores2;
unpivot函数是列转行函数
select username,subject,score from studentScores2
unpivot(score --为列转行中多列变成一列的值取别名
for subject --在结果中成为一列的原多列取统一别名
in(yuwen,yingyu,shuxue));