常用函数与行列转换
[]
:表示其中的内容可省略dual
:Oracle内置的单行表
普通函数
-
sysdate
:当前的日期包含时间select sysdate from dual; -- 结果: yyyy-MM-dd hh:mi:ss
-
length(str)
函数:返回字符串的长度select length('abd') from dual; -- 结果: 3 select ename,length(ename) from emp; -- 查询姓名有6个字符的员工信息 select * from emp where length(ename)=6;
-
concat(str1,str2)
:str1,str2都是字符串,将字符串str1和str2拼接在一起select concat('aa', 'bb') from dual; -- 结果: aabb -- || :它是字符串连接符,不是函数 select 'aa'||'bb' from dual; -- 结果: aabb select 'aa' + 'bb' from dual; -- false select '12' + '21' from dual; -- 结果: 33 select '12' + 21 from dual; -- 结果: 33
-- 查询当前用户下数据库中所有的表名 select table_name from user_tables; -- 查询表中都有哪些列 select column_name from user_tab_columns where table_name='emp'; -- 字符串要用单引号括起来,在字符串(单引号中)中使用两个连着的单引号,这时第一个单引号是一个转义符号 select '''' from dual; -- 结果:' select ',''' from dual; -- 结果:,'
-
chr(ASCCII):它将ASCCII码转换成字符
select chr(65) from dual; -- 结果: A select chr(97) from dual; -- 结果: a
-
substr(str, index[,len])
:字符串截取函数,str 是一个字符串,index 开始截取的位置(包含这个位置),len 截取的长度[index,index+len-1]
,字符串下标从1开始select substr('abcdefg', 2) from dual; -- 结果: bcdefg select substr('abcdefg', 2, 2) from dual; -- 结果: bc select substr('abcdefg', 0, 2) from dual; -- 结果: ab select substr('abcdefg', 1, 2) from dual; -- 结果: ab select substr('abcdefg', 2, 6) from dual; -- 结果: bcdefg select substr('abcdefg', 2, 8) from dual; -- 结果: bcdefg select substr('abcdefg', -2, 2) from dual; -- 结果: fg
-
trim([s from] str)
:str 表示字符串,s 表示字符,表示去除字符串两端的空格,如果加s,表示去除字符串两端的字符ltrim(str[,'s'])
:去除字符串左边的空格或者字符srtrim(str[,'s'])
:去除字符串右边的空格或者字符s
select ' abc ' from dual; -- 结果: ' abc ' select trim(' abc ') from dual; -- 结果: abc select trim('#' from '###abc###') from dual; -- 结果: abc select ltrim('###abc###', '#') from dual; -- 结果: abc### select rtrim('###abc###', '#') from dual; -- 结果: ###abc
-
replace(str,s,d)
:替换函数,str 字符串,s 要替换的内容,d 替换成内容,表示将字符中 str 中的 s 替换成 dselect replace('abcedcfg', 'c', 'W') from dual; -- 结果:abWedWfg
-
lpad/rpad(str,len[,s])
:str 表示字符串,len 表示扩展后的字符串长度,s 表示要扩展的内容-- 先看扩展后的长度 -- 若原字符串长度小于扩展后的长度,则添加 select lpad('aa', 4) from dual; -- 结果: 'ab ' select lpad('aa', 4, 'bb') from dual; -- 结果: aabb -- 若原字符串长度等于扩展后的长度,则不添加 select lpad('aa', 2, 'bb') from dual; -- 结果: aa select rpad('aa', 2, 'bb') from dual; -- 结果: aa -- 若原字符串长度大于扩展后的长度,则重从前面开始截取 select lpad('aacc', 2, 'bb') from dual; -- 结果: aa
-
initcap(str):str 表示字符串,将字符串中的英文单词首字母大写
select initcap('hello world') from dual; -- 结果:Hello World select initcap('helloworld') from dual; -- 结果:Helloworld
-
lower/upper(str):将字符串全部转换成小写/大写
select lower('HELLOWORLD') from dual; -- 结果:helloworld select upper('helloworld') from dual; -- 结果:HELLOWORLD
-
instr(str,s[,n1,n2]):在字符串中找某个字符的位置,str 字符串,s 要查询的字符串,n1 表示从位置开始查询,n2 表示这个字符第几次出现
-- 查字符串首次出现j的位置 select instr('abcdejfjhijklmnojpqrstjuvwxuzj','j') from dual; -- 从字符串的第10个字符开始查找第一次出现j的位置 select instr('abcdejfjhijklmnojpqrstjuvwxuzj','j',10) from dual; -- 从字符串的第10个字符开始查找第3次出现j的位置 select instr('abcdejfjhijklmnojpqrstjuvwxuzj','j',10,3) from dual; -- 从字符串的倒数第3个字符开始反向查找第3次出现j的位置 select instr('abcdejfjhijklmnojpqrstjuvwxuzj','j',-3,3) from dual;
-
floor(n)
:取整函数,只取整数部分,不进行四舍五入select floor(3.45) from dual; -- 结果:3 select floor(3.99) from dual; -- 结果:3
-
mod(m,n)
:求 m 除以 n 的余数的函数select mod(5,3) from dual; -- 结果:2
-
round(n,s)
:四舍五入函数,n 表示数字,s 表示精度,默认是0select round(3.1415) from dual; -- 结果:3 select round(3.5415) from dual; -- 结果:4 select round(5.48,1) from dual; -- 结果:5.5 select round(5.64,1) from dual; -- 结果:5.6
-
power(m,n):求 m 的 n 次方
select power(2,8) from dual; -- 结果:256
-
to_number(str[,format])
:将字符串转换成数字,str 字符串,format 格式字符串,str字符串必须是数字字符串select to_number('78.1234') from dual; -- 结果:78.1234 select to_number('78.123a') from dual; -- false
-
to_date(str,format)
:将字符串转换成固定格式的日期,str 是一个日期,format 日期格式- yyyy:表示四位的年
- MM:表示两位的月
- dd:调试两位的天
- hh:表示12小时制
- hh24:表示24小时制
- mi:表示分钟
- ss:表示秒钟
- day:表示星期
select to_date('2023-05-25 11:59:25','yyyy-MM-dd hh:mi:ss') from dual; select to_date('2023-05-25 13:59:25','yyyy-MM-dd hh24:mi:ss') from dual; select to_date('2023/05/25 13:59:25','yyyy/MM/dd hh24:mi:ss') from dual;
-
to_char(date,format)
:将日期转换成字符串,date 是一个日期,format 日期格式select to_char(sysdate,'yyyy') from dual; select to_char(sysdate,'MM') from dual; select to_char(sysdate,'dd') from dual; select to_char(sysdate,'hh24:mi:ss') from dual; select to_char(sysdate,'day') from dual; -- 随机一个月以内的日期 select to_date(round(DBMS_RANDOM.VALUE(to_char(sysdate,'J'),to_char(add_months(sysdate,-1),'J'))),'J') from dual;
-
add_months(date,n)
:给日期加减 n 个月-- 日期直接加上3个月 select add_months(sysdate,3) from dual; -- 日期直接减去2个月 select add_months(sysdate,-2) from dual; -- 日期直接加上一个整数,相当于加的天数 select sysdate-1 from dual;
-
months_between(date1,date2)
:求两个日期相差多少个月select months_between(sysdate,to_date('2023-01-01','yyyy-MM-dd')) from dual;
-
lsat_day(date)
:取日期当前月的最后一天select last_day(sysdate) from dual; -- 格林尼治标准时间(+8后是中国北京时间) select current_date from dual;
-
trunc()
:截断函数--截断数字类型,第二个参数表示精度,不会四舍五入 select trunc(3.141592,3) from dual; -- 结果:3.141 select trunc(31.41592,-2) from dual; -- 结果:0 select trunc(314.1592,-2) from dual; -- 结果:300 select trunc(3141.592,-2) from dual; -- 结果:3100 --截取到年(本年的第一天) select trunc(sysdate,'yyyy') from dual; --截取到月(本月的第一天) select trunc(sysdate,'MM') from dual; --截取到月(本月的第一天) select trunc(sysdate,'month') from dual; --截取到周(本周第一天,即上周日) select trunc(sysdate,'day') from dual; --截取到当天 select trunc(sysdate,'dd') from dual;
-
decode(exp, res1,value1, res2,value2,…, fefault)
- exp:表达式
- res:表达式的结果
- value:值
如果表达式结果和某一个 res 值相等,它就会返回 res 后面对应的 value,如果表达式的结果没有一个res 值和它匹配,它会返回 default 默认值
select decode(ssex,'男','1','女','0','未知') from student;
-
nvl(number,value)
:如果number 的值是空时,函数返回value,如果number 的值不为空返回number 的值select nvl(null,200) from dual; -- 结果:200 select nvl(100,200) from dual; -- 结果:100 select nvl('null',200) from dual; -- 结果:null
行列转换
1、行转列
行转列的大致意思是,将表中多行数据转换成一行的多个字段输出。需要行转列的业务逻辑通常是,将表中的一个字段分类统计后作为多个结果字段输出
-- studentScores 表
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);
方式一:使用case when
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;
方式二:使用decode()函数
select username,
sum(decode(subject, '语文', score) ) 语文,
sum(decode(subject, '英语', score) ) 英语,
sum(decode(subject, '数学', score) ) 数学
from studentScores group by username;
方式三:使用pivot 函数
-- 用pivot函数查出来的字段名称就是'语文',注意这两个单引号也是名称一部分,所以这行要用双引号括起来
select username,"'语文'","'英语'","'数学'" from studentScores
pivot(sum(score) for subject in('语文','英语','数学'));
其他方式
select username,
(select score from studentScores s1 where s1.username=s.username and s1.subject='语文') 语文,
(select score from studentScores s1 where s1.username=s.username and s1.subject='英语') 英语,
(select score from studentScores s1 where s1.username=s.username and s1.subject='数学') 数学
from studentScores s group by username;
2、列转行
-- studentScores2 表
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);
方式一:使用union all(union)
- union all:表示取两个集合的并集,不删除重复的元素
- union:表示取两个集合的并集,删除重复的元素
-- union all
select username,'yuwen' subject, yuwen score from studentScores2
union all
select username,'shuxue' subject, shuxue from studentScores2
union all
select username,'yingyu' subject, yingyu from studentScores2;
-- union
select username,'yuwen' subject, yuwen score from studentScores2
union
select username,'shuxue' subject, shuxue from studentScores2
union
select username,'yingyu' subject, yingyu from studentScores2;
方式二:使用unpivot()函数
/*
unpivot函数是列转行函数
*/
-- subject:为列转行中多列变成一列的值取别名
-- in(yuwen,yingyu,shuxue):在结果中成为一列的原多列取统一别名
select username,subject,score from studentScores2
unpivot(score for subject in(yuwen,yingyu,shuxue));