SQL函数
函数就是java中的方法 ：函数有输入有输出 输入就是参数 输出就是结果 而且输入可以是多个 输出的结果只有一个
SQL函数分为：单行函数和多行函数
1、单行函数
单行函数：只能对一行进行变换，每行返回一个结果。可以转换数据类型 在使用的时候 还可以嵌套。参数可以是一列或一个值
单行函数： 字符函数 数值函数 日期函数 转换函数 通用函数
1 . 字符函数
控制大小写： LOWER UPPER INITCAP
--查询所有雇员信息 职位名称使用小写字母显示
SELECT lower(job) FROM emp;
--查询所有雇员信息 职位名称的首字母大写
SELECT initcap(job) FROM emp;字符的控制函数： CONCAT SUNSTR LENGTH TRIM
-- 字符控制函数
--dual是oracle所提供的一张虚拟表 不存在 但是可以使用 主要用来测试函数
SELECT CONCAT ('hello','WORLD') FROM dual;
SELECT SUBSTR('helloworld',2,3) FROM dual;--从第二个字符开始截取 截取3个字符
SELECT LENGTH('ORACLE') FROM dual;--获取字符串的长度
SELECT TRIM(' HE LL O ') FROM dual;--去除收尾空格
SELECT Length(TRIM(' HE LL O ')) FROM dual;--嵌套使用
SELECT REPLACE('helloworld','l','P') FROM dual;--替换2 . 数值函数
-- ROUND(x,y)返回四舍五入后的值，如果y不为整数则截取y整数部分，如果y>0则四舍五入为y位小数，如果y小于0则四舍五入到小数点向左第y位。
SELECT round(45.96) from dual;--46
SELECT round(45.46) from dual;--45
SELECT round(45.9653,2) from dual;-- 45.97 四舍五入 保留两位小数
SELECT round(12345.9653,-2) from dual; --12300
SELECT round(65589.4653,-2) from dual; --65600
--trunc 按照规则截取 但是不四舍五入
SELECT TRUNC(45.96),TRUNC(45.46),TRUNC(45.9653,2),TRUNC(65589.4563,-2) from dual;-- 45 45 45.96 65500
-- mod 求余
SELECT MOD(5,3) FROM dual; -- 23 . 转换函数
隐形（自动类型转换） 显性（强制转换）
to_char函数对日期的转换
to_char(date,'format_model')
/*
必须包含单引号 其中的format_model是大小写敏感
可以包含任意的日期格式
日期和格式之间使用逗号分割
*/
--查询系统当前日期
SELECT sysdate FROM dual;
--转换格式为yyyy-mm-dd hh:mi:ss
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;字符转化为日期 to_date
-- 返回hiredae为Date型，且格式为****-**-**的员工信息
select to_date(to_char(hiredate,'yyyy-mm-dd'),'yyyy-mm-dd')from emp;数值型转换为字符型 to_char
9 表示数字
0 表示0
$ 表示美元
L 表示本地货币
. 表示小数点
--将员工的薪资转换为美元表示
SELECT TO_CHAR(sal,'$999,999.00') FROM emp;将字符转换为数值型to_number
--字符到数值的转换
SELECT to_number('1234589342.5645','9999999999.0000') FROM dual;--要转换的字符型数字 只能比格式短 不能比他位数多
select TO_NUMBER('199912'),TO_NUMBER('450.05') from dual;4 . 日期函数
日期的运算：只能做减法，不能做加法
-- 计算员工从入职到现在所度过的周数
select ename,round((sysdate - hiredate)/7,2) weeks from emp;
--返回指定月数后的日期
select add_months(sysdate,2) from dual;
--计算员工入职至今的月数
select months_between(sysdate,hiredate) from emp;
--next_day
select next_day(to_date('2020-11-11','yyyy-mm-dd'),'星期一') from dual;5 . 通用函数
这些函数可以适用于任何数据类型 包括null值
- NVL函数
1、【语法】NVL (expr1, expr2)
【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。
注意两者的类型要一致2、【语法】NVL2 (expr1, expr2, expr3)
【功能】expr1不为NULL，返回expr2；expr2为NULL，返回expr3。
expr2和expr3类型不同的话，expr3会转换为expr2的类型
--计算雇员的年收入
select empno,ename,job,sal,sal * 12 + NVL(comm,0) yearSal from emp;
--计算雇员的年收入 如果该雇员的奖金为空 则增加1000元
select empno,ename,job,sal,sal * 12 + NVL2(comm,comm,1000) yearSal from emp;- nullIF函数
-- 查询雇员信息 如果雇员的名字的长度和他的职位名称的长度相等 null 否则就返回他的姓名的长度
select ename,job,LENGTH(ename),LENGTH(job) ,NULLIF(LENGTH(ename),LENGTH(job)) res from emp;- COALESCE
COALESCE(c1, c2, …,cn)
【功能】返回列表中第一个非空的表达式，如果所有表达式都为空值则返回1个空值
--查询雇员基本信息 如果雇员的奖金为空 则返回薪资，否则返回 1000
SELECT ename,job,sal ,COALESCE(comm,sal,1000) from emp;2、条件表达式
可以在SQL中使用判断逻辑（if – then — else）
可以使用两种方法
1 . case表达式
case expr when 值一 then 结果1
when 值一 then 结果1
when 值一 then 结果1
when 值一 then 结果1
else 结果N--查询部门编号为10 20 30 ，40的员工信息，若部门编号为10 则员工的涨薪1.1倍 如果20部门 则涨薪1.2倍 如果30部门 涨薪1.3倍
--其他部门一律涨薪1.5倍
select ename,job,sal,deptno,
case deptno
when 10 then sal * 1.1
when 20 then sal * 1.2
when 30 then sal * 1.3
else sal * 1.5
end salary
from emp;2 . decode函数
--DECODE
select ename,job,sal,deptno,
DECODE( deptno
，10, sal * 1.1
, 20, sal * 1.2
, 30, sal * 1.3
,sal * 1.5 )
salary
from emp;3、分组函数（多行函数）
分组函数作用于一组数据，并对一组数据返回一个值
1 . 分组函数的类型
- AVG 求取一组数据的平均值
- COUNT 统计函数
- MAX 求取一组数据的最大值
- MIN 求取一组数据的最小值
- SUM 对一组数据求和
2 . 组函数的使用
-- 求所有雇员的平均薪资 最高薪资 最低薪资
SELECT AVG(sal) ,MAX(sal),MIN (sal) from emp;
-- 查询入职最早的员工和入职最晚的员工
SELECT MIN(hiredate) , MAX(hiredate) from emp;
--统计员工的数量
select count(*) from emp;
select count(empno) from emp;--效率高
--统计员工中有奖金的人 对空值不统计
select count(comm) from emp;
-- 查询奖金的平均值 总的奖金数
select sum(comm),avg(comm) from emp; --不计算空值3 . 在组函数中使用 nvl 函数
--统计员工中有奖金的人 对空值不统计
select count(nvl(comm,0)) from emp;
-- 查询奖金的平均值 总的奖金数
select sum(comm),avg(nvl(comm,0)) from emp;4 . 去重统计（distinct）
--在emp表中查询部门的数量
select count(distinct deptno) from emp;
