函数
单行函数
分类:
- 字符函数
- 数字函数
- 日期函数
- 转换函数
- 通用函数
字符函数:主要是进行字符串数据的操作
- UPPER(字符串|列) 将输入的字符串变为大写返回
- LOWER(字符串|列) 将输入的字符串变为小写返回
- INITCAP(字符串|列) 开头首字母大写
- LENGTH(字符串|列) 求出字符串长度
- REPLACE(字符串|列) 进行替换
- SUBSTR(字符串|列) 开始点[结束点],字符串截取
转大写
select upper('hello') from dual;
select * from emp where ename=upper('&str');
使用字符“_”替换雇员姓名中的所有字母“A”
select replace(ename,'A','_') from emp;
字符串截取有两种语法:
SUBSTR(字符串|列,开始点),表示从开始点一直截取到结尾
select ename,substr(ename,3) from emp;
SUBSTR(字符串|列,开始点,截取多少位),表示从开始点截取多少位
0和1都表示从第一个开始
select ename,substr(ename,0,3) from emp;
select ename,substr(ename,1,3) from emp;
要求截取每个雇员名字的后三个字母
通过长度-2 确定开始点
select ename,substr(ename,length(ename)-2) from emp;
设置负数,表示从后指定截取位置
select ename,substr(ename,-3) from emp;
数字函数
- ROUND(数字|列[,保留小数的范围]):四舍五入的操作
- TRUNC(数字|列[,保留小数的范围]): 舍弃指定位置的内容
- MOD(数字 1,数字 2):取模,取余数
ROUND
select round(903.535),round(-903.535),round(903.535,-1),round(903.535,2) from
dual;
TRUNC
select trunc(903.53567),trunc(-903.53567),trunc(903.53567,-1),trunc(903.53567,2) from dual;
MOD
select mod(10,3) from dual;
select mod(3,10) from dual;
日期函数
注意:在所有开发之中,如果是日期的操作,建议使用以上的函数,可以避免闰年的问题
取得今天的日期,可以使用‘’SYSDATE”
select sysdate from dual;
日期的计算有以下几种计算:
- 日期+数字=日期,表示若干天之后的日期
- 日期-数字=日期,表示若干天之前的日期
- 日期-日期=数字,表示两个日期之间的天数,但是必须大日期减小日期
求出每个雇员到今天为止的雇佣天数
select ename,hiredate,sysdate-hiredate from emp;
四个操作函数
LAST_DAY(日期):求出指定日期当月的最后一天
求出本月的最后一天日期
select last_day(sysdate) from dual;
NEXT_DAY(日期,星期数):求出下一个指定星期几的日期
下一个周六是几号
select next_day(sysdate,'sat') from dual;
下一个周日是几号
select next_day(sysdate,'sun') from dual;
ADD_MONTHS(日期,数字):求出若干月之后的日期
四个月后是几号
select add_months(sysdate,4) from dual;
MOUNTHS_BETWEEN(日期 1,日期 2):求出两个日期之间所经历的月份
求出每个雇员到今天为止的雇佣月份
select ename,hiredate,trunc(months_between(sysdate,hiredate)) from emp;
转换函数
一共有三种转换函数
- TO_CHAR(字符串|列,格式字符串):将日期或者数字变为字符串显示;
- TO_DATE(字符串,格式字符串):将字符串转变为 DATE 数据显示;
- TO_NUMBER(字符串):将字符串变为数字显示.
TO_CHAR()
拆分年月日
格式:年(yyyy),月(mm),日(dd)
select to_char(sysdate,'yyyy-mm-dd'),to_char(sysdate,'yyyy') year,to_char(sysdate,'mm')
month,to_char(sysdate,'dd') day from dual;
干掉日期中的前导零
select to_char(sysdate,'fmyyyy-mm-dd') day from dual;
使用 sysdate 显示时间
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') haha from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
使用 24 小时制
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
格式化数字
select to_char(22222222222,'999,999,999,999,999,999,999') shuzi from dual;
格式化数字为货币格式
使用“L”标记表示转换为当前语言环境下的货币符号
select to_char(22222222222,'L999,999,999,999,999,999,999') huobi from dual;
TO_DATE()
TO_DATE(): (熟悉用法)
select to_date('1989-09-12','yyyy-mm-dd') from dual;
TO_NUMBER()
TO_NUMBER(): (基本不用,会用即可)
select to_number('2')+to_number('3') from dual;
在 oracle 中,不使用该函数也可以完成该功能,如下:
select ‘2’+‘3’ from dual;
通用函数
通用函数主要有 NVL(),DECODE()两个,这两个函数算是 oracle 的特色函数
NVL() 处理 null
comm 中的 null 值变成 0
查看转换过程
select ename,sal,comm,nvl(comm,0) from emp;
DECODE():多数值判断
- 判断的是数值而不是条件,类似编程中的 if…else…
- 说明:这种判断肯定是进行逐行判断,所以这个时候就必须采用
- DECODE(),而此函数的用法如下:
DECODE(数值|列,判断值 1,显示值 1,判断值 2,显示值 2,…)
统计函数
分类:
- COUNT():查询表中的数据记录
- AVG(): 求出平均值
- SUM(): 求和
- MAX(): 求出最大值
- MIN(): 求出最小值
COUNT()
COUNT()函数的主要功能是进行数据的统计,但是在进行数据统计的时候,如果一张表中没有记录,那么 COUNT()也会返回数据,只是这个数据是“0"
null不计算在内
分组统计
当数据重复的时候分组才有意义,一个人也可以单独分一组,但是没有意义
GROUP BY 子句,语法如下:
SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数
FROM 表名称 [别名],[表名称 [别名],...]
[WHERE 条件(s)]
[GROUP BY 分组字段 1 [,分组字段 2,...]]
[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],...]]
分组函数有以下要求:
1、分组函数可以在没有分组的时候单独使用,可是不能出现其他的查询字段
2、如果要进行分组,则 select 子句之后,只能出现分组的字段和统计函数,其他字段不能出现
3、分组函数允许嵌套,但是嵌套之后的分组函数的查询之中不能再出现任何的其他字段
再次过滤 HAVING
如果要对分组后的数据再次进行过滤,需要使用 HAVING 子句,语法格式如下:
SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数
FROM 表名称 [别名],[表名称 [别名],...]
[WHERE 条件(s)]
[GROUP BY 分组字段 1 [,分组字段 2,...]]
[HAVING 分组后的过滤条件(可以使用分组函数)]
[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],...]]
select d.deptno,d.dname,d.loc,count(e.empno),trunc(nvl(avg(e.sal),0))
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.deptno,d.dname,d.loc
having avg(sal)>2000;
WHERE 和 HAVING 的区别
- WHERE:在执行 GROUP BY 操作之前进行的过滤,表示从全部数据中进行过滤,不能使用统计函数;
- HAVING: 在 GROUP BY 分组之后的再次过滤,可以使用统计函数。