1. 多行函数
- 函数可以没有参数，但必须要有返回值
- 多行函数: 对某一列的所有行进行处理
max() min() count() sum() avg()，直接忽略空值 - 统计员工工资总和
select sum(sal) from emp;
- 统计员工奖金总和
select sum(comm) from emp;
- 统计员工人数
select count(1) from emp;
- 统计员工的平均奖金 ， 错误 2200/14 = 157
select avg(comm) from emp;
select sum(comm)/count(1) from emp;
select ceil(sum(comm)/count(1)) from emp;
2. 单行函数
- 单行函数: 对某一行中的某个值进行处理 数值函数 字符函数 日期函数 转换函数 通用函数
2.1 数值函数
- 包括：
round,trunc,mod,ceil,floor - 数值函数
ceil，该函数返回的最小整数值，但不能小于X
select ceil(45.926) from dual;
floor，返回小于等于n的最大整数
select floor(45.926) from dual;
round，四舍五入的方法，即传回一个数值，该数值是按照指定的小数位元数进行四舍五入运算的结果
SELECT ROUND( number, [ decimal_places ] ) FROM DUAL
参数 number 是指需要处理的数值，是必须填写的值。
参数 decimal_places 是指在进行四舍五入运算时 , 小数的应取的位数，该参数可以不填，不填的时候，系统默认小数位数取0。
select round(45.926, 0) from dual;
select round(45.926, 1) from dual;
select round(45.926, 2) from dual;
select round(45.926, -1) from dual;
select round(45.926, -2) from dual;
select round(65.926, -2) from dual;
- 截断，
trunc
select trunc(45.926, 0) from dual;
select trunc(45.926, 1) from dual;
select trunc(45.926, 2) from dual;
select trunc(45.926, -1) from dual;
select trunc(45.926, -2) from dual;
- 求余，
mod
select mod(9, 3) from dual;
select mod(9, 4) from dual;
2.2 字符串函数
- 字符函数，
substr(str1,起始索引,长度) - 注意: 起始索引不管写
0还是1都是从第一个字符开始截取
select substr('abcdefg',0,3) from dual;
select substr('abcdefg',1,3) from dual;
select substr('abcdefg',2,3) from dual;
- 获取字符串长度，
length
select length('abcdefg') from dual;
- 去除字符两边的空格，
trim
select trim(' hello ') from dual;
- 替换字符串，
replace
select replace('hello', 'h', 'w') from dual;
2.3 日期函数
- 日期函数，查询今天的日期
select sysdate from dual;
- 查询3个月后的今天的日期
select add_months(sysdate,3) from dual;
- 查询3天后的日期
select sysdate + 3 from dual;
- 查询员工入职的天数
select ceil(sysdate - hiredate) from emp;
- 查询员工入职的周数
select (sysdate - hiredate)/7 from emp;
- 查询员工入职的月数
select months_between(sysdate,hiredate) from emp;
- 查询员工入职的年份
select months_between(sysdate,hiredate)/12 from emp;
2.4 转换函数
-
转换函数：数值转字符，字符转数值， 日期转字符
-
字符转数值：
to_number(str)，默认已经转换了
select 100+'10'from dual;
- 标准写法
select 100 + to_number('10') from dual;
- 数值转字符，
to_char(p1,'格式化字符串')
select to_char(sal,'$9,999.99') from emp;
select to_char(sal,'L9,999.99') from emp;
- 日期转字符
to_char()
select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') from dual;
- 24小时制显示，在 hh 后面加上 24
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
- 只显示年份
select to_char(sysdate, 'yyyy') from dual;
- 只显示日，dd：代表一个月中的第几天
select to_char(sysdate, 'dd') from dual;
- d：代表一个星期的第几天，因为西方是从星期天开始算的，今天周四，所以是第五天
select to_char(sysdate, 'd') from dual;
- ddd：代表一年中的第几天
select to_char(sysdate, 'ddd') from dual;
- dy：日期的简称，就是星期几
select to_char(sysdate, 'dy') from dual;
select to_char(sysdate, 'day') from dual;
- 字符转日期，
to_date(字符,'格式化字符串')
select to_date('2021-12-30', 'yyyy-mm-dd') from dual;
- 查询1981年 -- 1985年入职的员工信息
select * from emp where hiredate between to_date('1981','yyyy') and to_date('1985','yyyy');
2.5 通用函数
nvl(参数1,参数2)，如果 参数1 = null 就返回参数2
select nvl(null,1) from dual;
nvl2(参数1,参数2,参数3)，如果参数1 = null ,就返回参数3, 否则返回参数2
select nvl2(1,2,3) from dual;
nullif(参数1,参数2)，如果 参数1 = 参数2， 那么就返回 null , 否则返回参数1
select nullif(4,5) from dual;
coalesce: 返回第一个不为null的值
select coalesce(null,null,7,8,9) from dual;
2.6 条件表达式
- 通用语法，mysql和Oracle都可以使用
case 字段:
when 值1 then 值
when 值2 then 值
else
默认值
end
select
case ename
when 'SMITH' then 'zhangsan'
when 'ALLEN' then 'lisi'
else
'wangwu'
end "中文别名"
from emp;
- Oracle专用的方式:
decode(字段,'if1','then1','if2','then2','else')
select decode(ename,'SMITH','zhangsan','ALLEN','lisi','wangwu') from emp;
2.7 分组表达式
select 分组的条件,分组之后的操作from 表名 group by 分组的条件 having 条件过滤
- 分组统计所有部门的平均工资，找出平均工资大于2000的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
- 如果avg(sal) 使用别名，报错了
select deptno,avg(sal) sal from emp group by deptno having sal >2000;
- 原因如下：
- SQL的编写顺序：
select ... from ... where ... group by ... having ... order by
- SQL的执行顺序：
from ... where ... group by ... having ... select ... order by ...
- where 和 having 区别： where 后面不能接聚合函数，可以接单行函数 having 是在 group by 之后执行的，可以接聚合函数
