0、oracle函数
函数
函数一般是在数据上执行的，它给数据的转换和处理提供了方便。只是将取出的数据进行处理，不会改变数据库中的值。
sql函数分为两种，一种是组函数，一种是单行函数。
组函数又被称作聚合函数，用于对多行数据进行操作，并返回一个单一的结果，组函数仅可用于选择列表或查询的having子句
单行函数对单个数值进行操作，并返回一个值。
单行函数
分类：
数值函数
--数值函数
--向上取整 结果为4 ceil
select ceil(3.1) from dual;
--向下取整 结果为3 floor
select floor(3.9) from dual;
--返回x的y次幂, power(x,y)
select power(2,2) from dual;
--返回四舍五入后的值，round()
--如果第二个参数是正数，保留小数点后面两位小数
select round(123.123123,2) from dual;
--如果第二个参数为负数，看小数点前的第几位，不保留小数。
select round(123.123123,-2) from dual;
select round(123.123,-2.6) from dual;
--返回x的平方根，sqrt() ,结果为8
select sqrt(64) from dual;
--mod（x，y）求余数
select mod(23,8) from dual;
--trunc（number，n） 直接截断
select trunc(23.652) from dual;
select trunc(23.652, 2) from dual;
select trunc(23.652, -1) from dual;
字符型函数
--字符型函数
--连接两个字符，concat(str1,str2),用单引号
select concat('abc','de') from dual;
--字符嵌套,从里面往外面，里面的作为外面的第一个字符。
select concat(concat('ab','c'),'de') from dual;
--把每个单词的首字母变为大写，其余都是小写，initcap();
select initcap('ABCDEF') from dual;
--将整个字符转换为小写，lower();
select lower('ABDCK') from dual;
--将整个字符转换为大写，upper();
select upper('ada fda') from dual;
--字符串中搜索字符的位置，instr(c1,c2[,i[,j]]);汉字按一个字符，instrb()汉字按2个字符
--索引从1开始的
select instr('abcde','c')from dual;
--在左边添加字符，lpad(c1,n[,c2]);n代表会显示多少个字符，不够就在左边用c2代替，默认用空格
select lpad('adb',8,'*') from dual;
--在右边添加字符,rpad();
select rpad('acb',5) from dual;
--删除左边的字符串，ltrim(c1,[,c2]);c2默认是空格
select ltrim(' acv ') from dual;
--删除右边的字符串，rtrim(c1[,c2]);
select rtrim(' cbc ') from dual;
--删除两边的字符串，trim(c1[,c2]);
select trim(' cac c ac ') from dual;
--替换字符，replace(c1,c2[,c3]); 用i替换了he
select replace('he love you','he','i') from dual;
--截取字符，substr(c1,n1[,n2]),n1是从第几位开始截取，n2是截取多少位，n2可以没有，默认就截取到结尾，
--是一个汉字一个字符，索引从1开始，substrb()是一个汉字2个字符。
select substr('acbde',2,2)from dual;
--返回字符串长度，length()
select length('abcde') from dual;
日期函数
--日期函数
--返回当前日期，sysdate;
select sysdate from dual;
--返回指定月数后的日期，add_months(date,n);date是指定日期，n是跳到几个月后的日期
select add_months(sysdate,2) from dual;
--返回本月的最后一天的日期,last_day();
select last_day(sysdate) from dual;
--返回两个日期间隔的月数，months_between();
select months_between('1-2月-2020',sysdate) from dual;
--日期时间格式的数值,前面多个+号
--以天或天更小单位时可用数值表达式借用，如1表示1天，1/24表示1小时，1/24/60表示1分钟
select
trunc(sysdate)+(interval '1' second), --加1秒(1/24/60/60)
trunc(sysdate)+(interval '1' minute), --加1分钟(1/24/60)
trunc(sysdate)+(interval '1' hour), --加1小时(1/24)
trunc(sysdate)+(INTERVAL '1' DAY), --加1天(1)
trunc(sysdate)+(INTERVAL '1' MONTH), --加1月
trunc(sysdate)+(INTERVAL '1' YEAR), --加1年
trunc(sysdate)+(interval '01:02:03' hour to second), --加指定小时到秒
trunc(sysdate)+(interval '01:02' minute to second), --加指定分钟到秒
trunc(sysdate)+(interval '01:02' hour to minute), --加指定小时到分钟
trunc(sysdate)+(interval '2 01:02' day to minute) --加指定天数到分钟
from dual;
--返回当前会话的日期
select localtimestamp from dual;
转换函数
| 格式元素 | 含义 |
|---|---|
| YYYY、YY | 代表四位、两位数字的年份 |
| MM | 用数字表示的月份 |
| MON | 月份的缩写、对中文月份来说就是全称 |
| DD | 数字表示的日 |
| DY | 星期的缩写，对中文的星期来说就是全称 |
| HH24，HH12 | 12小时或者24小时进制下的时间 |
| MI | 分钟数 |
| SS | 秒数 |
| 控制符 | 含义 |
|---|---|
| 9 | 代表一位数字，如果该位没有数字则不进行显示，但对于小数点后面的部分仍会强制显示 |
| 0 | 代表一位数字，如果该位没有数字则强制显示0 |
| $ | 显示美元符号 |
| L | 显示本地货币符号 |
| . | 显示小数点 |
| , | 显示千分位符号 |
--转换函数
--to_char
--将日期转换成字符，to_char(date,日期格式)
--YYYY-MM-dd 年月日
--HH24:mi:SS 时分秒,HH24表示24小时制，HH12表示12小时制
--DY 星期数
select to_char(sysdate,'YYYY-MM-dd HH24:mi:SS DY') from dual;
--将数字转换成字符，to_char(number,format);
--9表示自动匹配字符的个数，0则从前面补0
-- ， 还是 ，
-- $ 还是 $
select to_char(1232123.123,'$999,999,999.999') from dual;
--to_number
--将字符转成数字，第二个参数表示怎样去识别字符
select to_number('$123,123.123','$999,999.999') from dual;
--to_date
--将字符转成日期，第二个参数表示怎样去识别日期的年月日
select to_date('20001025','YYYYMMdd') from dual;
其他函数
decode
相当于switch语句
--decode
--查询部门是10的涨薪10%，部门是20的涨薪20%，部门是30的涨薪30%，其他的涨薪15%。
select e.ename,
e.sal 涨薪前,
decode(e.deptno,
10,
e.sal * 1.1,
20,
e.sal * 1.2,
30,
e.sal * 1.3,
e.sal * 1.15) 涨薪后
from emp e；
case when
相当于if-else-if语句
--case when
--查询500-1500薪资的为贫穷群众，1500-2500为贫困群众，2500-5500为小康群众，5500以上为大康群众。
select e.ename,
e.sal 薪资,
case
when e.sal < 1500 then
'贫穷群众'
when e.sal >= 1500 and e.sal < 2500 then
'贫困群众'
when e.sal >= 2500 and e.sal < 5500 then
'小康群众'
-- when e.sal >=5500 then '大康群众 '
else
'大康群众'
end "身份"
from emp e;
组函数
组函数是从一组数据中产生一个值。
例如：
-
avg()求平均值
-
max()求最大值
-
min()求最小值
-
sum()求和
-
count()返回某列的行数
可以通过group by 来分组
group by 后面的字段是分组的标志，要想select后面有字段，此字段必须是跟group by 后面的字段一致。
组函数除了count()外，都跳过空值而处理非空值
select count() from emp;
select count(comm) from emp;
select count(1) from emp;
不能计算空值
select count(distinct deptno) from emp;--组函数 --求平均值 select e.deptno,avg(e.sal) 平均值 from emp e group by e.deptno; --求最大最小值，总和，总条数 select e.deptno,max(e.sal) 最大值,min(e.sal) 最小值,sum(e.sal) 总和,count(e.sal) 总条数 from emp e group by e.deptno;
组函数不能处理null
select avg(comm) from emp;
NVL函数迫使分组函数包括空值
select avg(nvl(comm,0)) from emp;
通用函数（nvl(e.comm,0)）
Sql中允许列值为空，空值用保留字NULL表示。NULL不同与0或者空格，它就是代表了一个不确定的内容。任何含有null值的数学表达式最后的结果都为空值
select ename,sal,comm,(sal*12)+comm “income” from emp;
可以通过函数nvl将NULL转换成为一个不为空的值
select ename, comm, nvl(comm,0) from emp
select ename,sal,comm ,(sal*12)+nvl(comm,0) “income” from emp
数据分组
group by 分组的关键字
Group by 子句可以包含任意数目的列。
除组函数语句外，select语句中的每个列都必须在group by 子句中给出。
如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null值，他们将分为一组。
Group by 子句必须出现在where子句之后，order by 子句之前。
过滤分组（having子句)
Where过滤行，having过滤分组。
Having支持所有where操作符。
分组和排序
一般在使用group by 子句时，应该也给出order by子句。
练习01
--day02练习01
--1、查询82年员工
--select e.* from emp e where e.hiredate between '1-1月-82' and '31-12月-82';
select e.* from emp e where to_char(e.hiredate, 'YYYY') in ('1982');
--2、查询32年工龄的人员
select e.*, trunc(months_between(sysdate, e.hiredate) / 12)
from emp e
where trunc(months_between(sysdate, e.hiredate) / 12) = 32;
--3、显示员工雇佣期 6 个月后下一个星期一的日期
select e.hiredate,next_day(add_months(e.hiredate,6),'星期一'),
from emp e;
--4、找没有上级的员工，把mgr的字段信息输出为 "boss"
select e.ename,nvl(to_char(mgr),'boss')
from emp e;
--5、为所有人长工资，标准是：10部门长10%；20部门长15%；30部门长20%其他部门长18%
select e.sal,
decode(e.deptno,
10,
e.sal * 1.1,
20,
e.sal * 1.15,
30,
e.sal * 1.2,
e.sal * 1.18)
from emp e;
浙公网安备 33010602011771号