MySQL函数
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档
目录
MySQL函数
一、函数
length(str):返回一个字符串的长度
concat(str1,str2)：字符串连接函数,它和||实现的功能一样。
chr():将一个ASCII码转换成字符
ascii(字符)：将一个字符转换成ASCII码值
trim(str)函数：如果只跟一个参数，表示去除字符串两端的空格
ltrim(str,):去掉左边空格
rtrim(str):去掉右边空格
pad(str,len,'符号'):填充函数,len表示长度,'符号'表示不够的用特定符号填充
lpad():在左边填充
rpad():在右边填充
replace(str,s,d)：字符串替换函数，将字符串str中的s字符替换成字符d
instr(str,字符,ind,n):表示在字符串str中查找字符，从ind位置开始，n表示查询第n次出现
.substr(str,ind,len):表示从ind位置开始截取字符串str，截取的长度为len
initcap(str)：将字符串的英文单词首字母大写
upper/lower():大小写转换函数
floor/ceil:向下取整和向上取整
mod()：求余函数
round(num,精度)：四舍五入函数，num是数字，精度是小数点后边保留几位小数
trunc():截断函数，可以将数字，日期等截断
power()：幂指函数
sqrt()：平方根
sysdate：返回当前时间
.to_number():将偏数据类型字符串转换成数字，货币符号也可以添加 $,￥是货币符号 L是本地货币符号
.to_date('时间'，'yyyy'):将字符串转换成日期 yyyy表示4位的年
MM 表示2位的月
dd 表示2位日
hh24 表示24小时制时间
mi:表示分钟 ss：表示秒 day:表示时期
to_char():可以将数字，日期等转换成字符串
last_day(日期):取当前日期月的最后一天
next_day():求周
add_months(日期,月)：给一个日期加上若干个月
add_months(日期,月)：给一个日期加上若干个月
开窗函数（分析函数）：partition by 替换group by
row_number()over（）:必须和over形容函数联用，排序的序号和rownum伪列相同，连续序号，不考虑值相等的情况（值 相同序号不相同）
dense_rank()over:可以用来做排序，它序号连续，考虑重复数据，如果值相等序号就相同 1123
rank()over:可以用来做排序，它序号不连续，考虑重复数据，如果值相等序号相同 1134
sum(case when) :列转行
case when then/decode:如果....则..
二、函数示例
1.示例
1.length(str):返回一个字符串的长度
----查询所有员工的姓名长度
select length(ename) from emp;
----查询 hahaha 的长度
select length('hahahha') from dual;
----查询工作长度为5的员工姓名和工种
select ename,job from emp where length(job)=5;
2.concat(str1,str2)：字符串连接函数,它和||实现的功能一样。
select 'aaa'||'bbb' from dual;
select concat('aaa','bbb') from dual;
select empno||':'||ename||':'||job from emp;
select concat(empno,ename,job) from emp;
---求员工的工资，以 姓名是：smith,工资是：800 的形式显示
select ' 姓名是：'||ename,'工资是：'||sal from emp;
3.chr():将一个ASCII码转换成字符
select chr(97) from dual;
select chr(10) from dual;
将工资与姓名之间以空格的形式显示
select sal||chr(10) ||ename from emp;
select sal||' '||ename from emp;
4.ascii(字符)：将一个字符转换成ASCII码值
select ascii('a') from dual;
select ascii('$') from dual;
5.trim(str)函数：如果只跟一个参数，表示去除字符串两端的空格
ltrim(str,):去掉左边空格
rtrim(str):去掉右边空格
select trim(' adsf ') from dual;
select ltrim(' adsf ') from dual;
select rtrim(' adsf ') from dual;
6.pad(str,len,'符号'):填充函数,len表示长度,'符号'表示不够的用特定符号填充
lpad():在左边填充
rpad():在右边填充
select lpad(ename,10,'*') from emp;
---将姓名补充到10位，不够的在左边用‘*’表示，再将名字补充到15位，不够的在右边用‘￥’表示
select Rpad(lpad(ename,10,'*'),15,'￥') from emp;
6.replace(str,s,d)：字符串替换函数，将字符串str中的s字符替换成字符d
select replace('asdfasdf','f','A') from dual;
7.instr(str,字符,ind,n):表示在字符串str中查找字符，从ind位置开始，n表示查询第n次出现
select instr('abkasdawe','a',1,1) from dual;
select instr('abkasdawe','a',-1,1) from dual;
select ename,instr( ename,'A',1) from emp;
select ename,instr( ename,'A') from emp;
---求第五位是H的员工姓名
select ename from emp where ename like '____H%';
select ename from emp where instr(ename,'H',5,1)=5;
8.substr(str,ind,len):表示从ind位置开始截取字符串str，截取的长度为len
select substr('aa|bb|cc|dd|ee',1,3) from dual;
select substr('aa|bb|cc|dd|ee',-3,2) from dual;
select ename,substr(ename,3) from emp;
求第五十位是H的员工姓名
select ename from emp where instr(ename,'H',50,1)=50;
select ename from emp where substr(ename,50,1)='H';
9.initcap(str)：将字符串的英文单词首字母大写
select initcap(ename) from emp;
10.upper/lower():大小写转换函数
select * from emp;
select lower(ename) from emp;
select upper('hahhah') from dual;
---将员工的姓名以首字母小写，其他字母大写的方式显示
select lower(substr(ename,1,1))||upper(substr(ename,2)) from emp；
11.floor/ceil:向下取整和向上取整
select floor(1234.838134) from dual;
select floor(-166.1235) from dual;
select ceil(1234.1234) from dual;
select ceil(-1234.1234) from dual;
---11：floor向下取整 ceil向上取整
select * from emp
select floor (1254.2342) from dual ---返回结果1254
select floor (-1667.324) from dual ---返回结果-1668
select ceil (234.45) from dual; ---返回结果235
select ceil (-1234.456) from dual ---返回结果-1234
select ceil (-1234.456) from emp ;--已有表查询
---12：mod()求余函数
select mod(123,4) from dual ;
查询姓名，编号是 编号/工资余数是169的信息
select ename,empno from emp where mod(empno,sal)=169;
查询工资数是偶数的员工姓名
select ename,empno from emp where mod(,sal，2)=0;
---13：round(num,精度)：四舍五入函数，num是数字，精度正数是小数点后边保留几位小数，负数是小数点前面几位
select round(48888.866859,2) from dual;
select round(48888.866859,-2) from dual;
---14：trunc():截断函数，可以将数字，日期等截断 精度正数是小数点后边保留几位小数，负数是小数点前面几位
select trunc(1234.8964,-2) from dual;
select trunc(-1234.8934) from dual; ---默认截断小数点后所有
select trunc (’yuiefd‘,2) from dual 不能截断字符串，
select trunc(sysdate,'yyyy')from dual ;
select trunc(hiredate,'dd') from emp;
select trunc(sysdate,'mm') from dual;
截取当前字段的日期，之后返回字段当月第一天或当年日期的第一月第一天
---15 sysdate返回当前系统日期
select sysdate from dual;--
---16 --power(2,2)幂指函数，第一个数值，第二个指数
select power(20,2) zhi from dual;-
---17--sqrt（）开方根
select sqrt(64)from dual;
---18-- to_number 将偏数据类型字符串转换成数字，货币符号也可以添加(模板需要加‘’)
数值模板的最小位数需要包含是转换字符数值最小位位数的，模板数值需为9或0(0时需要加’‘)
select to_number ('2647.00',9999.99)from dual
select to_number ('2647.00',9999.99)from dual
select to_number('97.9890','99.0000000') from dual;
select to_number('$97.9890','$99.0000000') from dual;
select to_number('$97.9890','$99.0000000') from dual;
---19.to_date('时间'，'yyyy'):将字符串转换成日期
yyyy表示4位的年
MM表示2位的月
dd表示2位日
hh24表示24小时制时间
mi:表示分钟
ss：表示秒
day:表示时期
select to_date('1999','yyyy') from dual;
select to_date('1999-05-07','yyyy-mm-dd') from dual;
select to_date('1999-05-07-12-14-45','yyyy-mm-dd-hh24-mi-ss') from dual;
--- 20--to_char():可以将数字，日期等转换成字符串
select to_char(hiredate,'yyyy-mm') from emp;
select to_char(sysdate,'yyyy-mm') from dual;
---21--last_day（） :去当前日期约的最后一天
select last_day(hiredate)from emp;
select last_day(sysdate) from dual;
---查询员工倒数第三天入职的员工
select hiredate，ename from emp where hiredate=last_day(hiredate)-2;
---22--next_day() 未来几天的日期
select next_day(sysdate,7)from dual;
select next_day(next_day(sysdate,7),7) "下下周" from dual;
---23--add-months 某日期加上若干个月
select add_months(sysdate,6)from dual
select add_months(sysdate,-6)from dual
---24 months_between(date1,date2)取两个日期相差的月数
--查询2015-09-09入职的年数
select months_between(sysdate,to_date('2015-09-09','yyyy-mm-dd'))/12 from dual;
***25.开窗函数（分析函数）：
partition by 替换group by
row_number()over（）:
必须和over形容函数联用，排序的序号和rownum伪列相同，连续序号，不考虑值相等的情况（值相同序号不相同）
dense_rank()over:
可以用来做排序，它序号连续，考虑重复数据，如果值相等序号就相同 1123
rank()over:
可以用来做排序，它序号不连续，考虑重复数据，如果值相等序号相同 1134
select deptno ,sal ,row_number() over(partition by deptno order by sal desc) biaoyi from emp;
select deptno ,sal ,dense_rank()over (partition by deptno order by sal desc) biaoer from emp;
select deptno ,sal,rank ()over(partition by deptno order by sal desc) biaoer from emp;
select deptno,sal,row_number()over(partition by deptno order by sal desc) from emp ;
select deptno,sal,dense_rank()over(partition by deptno order by sal desc) from emp ;
select deptno,sal,rank()over(partition by deptno order by sal desc) from emp ;
---26.sum(case when) :列转行
---查询十号、二十号、三十号和其他部门和部门的人数
select deptno,
sum(case when deptno =10 then 1 end) 十号部门人数,
sum (case when deptno=20 then 1 end) 二十号部门人数，
sum(case when deptno=30 then 1 end ) 三十好部门人数，
sum(case when deptno not in (10,20,30) then 1 end) 其余部门人数
from emp group by deptno
---查询 clerk salesman manager analyst 的人数
select
sum(case when job ='CLERK' then 1 else 0 end ) clerk,
sum(case when job='SALESMAN' then 1 else 0end) salseman,
sum(case when job='MANAGER' then 1 else 0 end) manager ,
sum (case when job='ANALYST' then 1 else 0 end )analayst
from emp
---case when then/decode:如果....则...
select sal, case when sal < 1000 then 'low'
when sal >=1000 and sal <2000 then 'middle'
else 'high'
end gzfb
from emp;
select sal,decode(sal,800,'low',2000,'middle',3000,'high') from emp
2.练习
1.将所有员工的名字按小写的方式显示
select lower(ename) from emp ;
2.显示正好为5个字符的员工的姓名。
select ename from emp where length(ename)=5;
3.显示所有员工姓名的前三个字符。
select substr(ename,1,3) from emp
4 显示所有员工姓名的后三个字符
select substr(ename,-3) from emp;
5.以首字母大写,后面小写的方式显示所有员工的姓名。(两种方法)
select initcap(ename) from emp ;
select upper(substr(ename,1,1))||lower(substr(ename,2)) from emp;
6.以首字母小写后面大写方式显示所有员工姓名。
select lower(substr(ename,1,1))||upper(substr(ename,2)) from emp
7.显示所有员工的姓名，用“我是老虎”替换所有“A”
select replace(ename,'A','我是老虎') from emp
8.以字符长度为10的方式显示员工职位，多余的位数在右边以*来填充
selselect rpad(job,10,'*') from emp
9.找出字符串"oracle training"中第二个ra出现的位置
select instr('oracle training','ra',1,2) from dual
10. 去除字符串‘ aadde gf ’两边的空格
select trim(' aadde gf ') from dual
11.以指定格式显示员工的工资（格式：SMITH 的工资是 800）
select ename||'的工资是'||sal from emp
12.显示所有员工的姓名,用a替换所有"A"
select replace(ename,'A','a') from emp
13.显示员工姓名中包含‘H’的员工（三种方法）
select ename from emp where ename like'%H%'
select ename from emp where instr(ename,'H')>0
select ename from emp where substr(ename,instr(ename,'H'),1)='H'
14.显示员工姓名中第二个字符是‘L’的员工（三种方法）
select ename from emp where ename like '_L%';
select ename from emp where instr(ename,'L',2,1)=2;
select ename from emp where substr(ename,2,1)='L'
15.显示员工姓名中最后一个字符是‘T’的员工（四种方法）
select ename from emp where ename like'%T';
select ename from emp where substr(ename,-1)='T';
select ename from emp where instr(ename,'T',-3,1)=length(ename)-2;
select ename from emp where instr(ename,'T',length(ename),1)>0;
16.查找已经入职8个月多的员工
select empno,ename from emp where months_between(sysdate,hiredate)>8;
--17显示满10年服务年限的员工的姓名和受雇日期。
select ename ,hiredate from emp where (months_between(sysdate,hiredate)/12)>=10;
--18对于每个员工，显示其加入公司的天数。
select ename,trunc (sysdate-hiredate,0) 天数 from emp;
--19找出各月倒数第3天受雇的所有员工。
select ename from emp where hiredate=(last_day(hiredate)-2);
--20显示1981年入职的所有员工
select ename from emp where to_char(hiredate,'yyyy')=1981
--21显示所有10月份入职的员工
select ename from emp where to_char (hiredate,'mm')=10
--22查询一个月前入职的员工
select ename from emp where months_between(sysdate,hiredate)>1
select ename from emp where add_months(hiredate,1)<sysdate
23.显示在一个月为30天的情况所有员工的日薪,忽略余数
select ename, trunc (sal/30) from emp
24.显示员工在此公司工作了几个月（要求结果是整数）
select ename ,trunc(months_between(sysdate,hiredate))from emp
25.显示所有12月份入职的员工
select ename from emp where to_char(hiredate,'mm')=12
26. 显示所有员工的姓名、加入公司的年份和月份，并且按照年份排序
select ename ,to_char(hiredate,'yyyy') 年份,to_char(hiredate,'mm') 月份 from emp order by 2 ;
27.查询当前日期
select trunc (sysdate) from dual;
28.将字符串"1980-12-17"转成日期
select to_date('1980-12-17','yyyy-mm-dd') 日期 from dual
29.使职位是分析员的，工资+1000;职位是经理的，工资+800；职位是其它的，工资+400
select ename， case when job='ANALYST' THEN SAL+1000
when job='MANAGER' then sal+800
else sal+400
end 工资
from emp
30.员工总工资，平均工资，四舍五入，保留小数点后2位
select sum(sal+nvl(comm,0)) 总工资,round(avg(sal+nvl(comm,0)),2) 平均工资 from emp;
select round(sum(sal+nvl(comm,0)),2),round(avg(sal+nvl(comm,0)),2) from emp;
31.按部门求出部门平均工资，且平均工资取整数
select deptno，trunc(avg(sal)) from emp group by deptno