1.sysdate:当前的日期包含时间
select sysdate from dual;
2.length(str)函数:返回字符串的长度,str表示一个字符串
select length(‘aa’)from dual;返回2
--查询姓名有6个字符的员工信息
select * from emp where length(ename)=6;
3.concat(str1,str2):str1,str2都是字符串,将字符串str1和str2拼接在一起
select concat(‘aa’,‘bb’)from dual;输出aabb
||:他是字符串连接符
select ‘aa’||‘bb’ from dual;输出aabb
4.chr(ASCII):它将ASCII码转换成字符
select chr(39) from dual;
5.substr(str,index,len):字符串取函数,str是一个字符串,index截取的开始位置,len截取的长度(第三个参数可省略)
表示从字符串str的index位置开始截取len个长度的字符
select substr('abcdefg',2,2) from dual;
select substr('abcdefg',2) from dual;
select substr('abcdefg',2,6) from dual;
select substr('abcdefg',2,8) from dual;
6.trim([s from ]str):str表示字符串,s表示字符,表示去除字符串两端的空格,如果加s,表示去除字符串两端的字符s
select ' abcd ' from dual;
select trim(' abcd ') from dual;
select trim('s' from 'sabcdss') from dual;前面是哪个字母就去除后面字符串两端的哪个字母
ltrim(str[,'s']):去除字符串左边的空格或者字符s
rtrim(str[,'s']):去除字符串右边的空格或者字符s
select ltrim(' abcd ') from dual;
select rtrim(' abcd ') from dual;
select ltrim('$abcd$','$') from dual;
select rtrim('$abcd$','$') from dual;
7.replace(str,s,d):替换函数 str字符串,s要替换的内容,d替换成内容,表示将字符中str中的s替换成d
select replace('abcd1qewr1qerazdf','qe','QE') from dual;
8.lpad/rpad(str,len[,s]):str表示字符串,len表示扩展后的字符串长度,s:表示要扩展的内容,可选参数
表示在字符串的左边填充空格或者s让字符串的长度到len的长度
select lpad('aa',4) from dual;
select lpad('aa',4,'b') from dual;
select lpad('abcd',2,'6') from dual;
select rpad('abcd',2,'6') from dual;
9.initcap(str):str表示字符串,将字符串中的英文单词首字母大写
select initcap('hello world') from dual;
select initcap('helloworld') from dual;
10.lower/upper(str):将字符串全部转换成小写/大写
select lower(ename) from emp;
select upper(ename) from emp;
11.instr(str,s[,n1,n2]):在字符串中找某个字符的位置,str字符串,s:要查询的字符,n1表示从位置开始查询,n2表示这个字符第几次出现
在字符串str中从n1的位置开始查询第n2次出现字符s的位置
select instr('asdoqwerjldajsfoqjwelfjaoidsfj','j') from dual; --查字符串首次出现j的位置
select instr('asdoqwerjldajsfoqjwelfjaoidsfj','j',10) from dual;--从字符串的第10个字符开始查找第一次出现j的位置
select instr('asdoqwerjldajsfoqjwelfjaoidsfj','j',10,3) from dual;--从字符串的第10个字符开始查找第3次出现j的位置
select instr('asdoqwerjldajsfoqjwelfjaoidsfj','j',-3,3) from dual;--从字符串的倒数第3个字符开始反向查找第3次出现j的位置
12.floor(n):取整函数,只取整数部分,不进行四舍五入
select floor(3.41) from dual;
select floor(3.98) from dual;
13.mod(m,n):求m除以n的余数的函数
select mod(3,2) from dual;
14.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;
15.power(m,n):求m的n次方
select power(2,8) from dual;
16.to_number(str[,format]):将字符串转换成数字,str字符串,format格式字符串
select to_number('78.1234') from dual;
17.to_date(str,format):将字符串转换成固定格式的日期,str是一个日期,format:日期格式
yyyy:表示4位的年
MM:表示两位月
dd:表示两位的天
hh24:表示24小时制的小时
mi:表示分钟
ss:表示秒
day:表示星期
select to_date('1999-09-09 14:24:34','yyyy-MM-dd hh24:mi:ss') from dual;
18.to_char(date,format):将日期转换成字符串,date是一个日期,format:日期格式
sysdate
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'hh24:mi:ss') from dual;
select to_char(sysdate,'day') from dual;
19.add_months(date,n):给日期加减n个月
select add_months(sysdate,3) from dual;
select add_months(sysdate,-2) from dual;
日期直接加上一个整数,相当于加的天数
select sysdate-1 from dual;
20.months_between(date1,date2):求两个日期相差多少个月
select months_between(sysdate,to_date('20210120','yyyyMMdd')) from dual;
21.last_day(date):取日期当前月的最后一天
select last_day(sysdate) from dual;
select current_date from dual;
22.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; --截取到周(本周第一天,即上周日)
23.decode滴扣的(exp,res1,value1,res2,value2,...,default)
exp 表达式的
res1,res2 ,..:表达式的结果
如果表达式结果和某一个res值相等,就会返回res后面对应的value
如果表达式的结果没有一个res值和它匹配,它会返回default默认值
select decode(ssex,'男','男生','女','女生','未知') from student;
24.nvl(变量,默认值):如果变量的值是空时,函数返回默认值,如果变量的值不为空返回变量的值
select nvl(comm,200) from emp;
25.行列转换
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);
select * from studentScores;
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函数:行转列的函数
select username,"'语文'","'英语'","'数学'" from studentScores --用pivot函数查出来的字段名称就是'语文',注意这两个单引号也是名称一部分,所以这行要用双引号括起来
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 * from studentScores2;
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));