SQL—SQL函数

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; -- 2

3 . 转换函数
隐形（自动类型转换） 显性（强制转换）

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值

1. 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;

2. nullIF函数

-- 查询雇员信息  如果雇员的名字的长度和他的职位名称的长度相等 null  否则就返回他的姓名的长度
select ename,job,LENGTH(ename),LENGTH(job) ,NULLIF(LENGTH(ename),LENGTH(job)) res from emp;

3. 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;