常用函数与行列转换
[]：表示其中的内容可省略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));
5418
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
