4.函数（SQL基础）

函数：

定义：函数是封装好的，可以直接拿来使用的程序

分类：

1.系统函数******数据库系统已经封装好的，可以直接拿来使用的程序

2.自定义函数 ----》pl\sql 需要程序员自己去编写，自己实现函数功能的一些程序。

1.系统函数******数据库系统已经封装好的，可以直接拿来使用的程序

(1)转换函数

(2)字符型函数

(3) 数值函数

(4) 日期函数

(5)聚合函数（分组函数）

(6)开窗函数（分析函数）

(7)其他函数

-----知识点杂乱多，重要，多记多练

1.转换函数

1.1. to_date(参数1，参数2) --将字符型数据转成日期型数据

参数1：目标字符

参数2：日期格式 yyyy/mm-dd

返回值：日期型数据

dual： oracle中一张虚拟表 --只为了补全sql语法，方便查询

select '张三' from dual;

select 100+99 from dual;

select * from dual;

select '张三' from emp;

select 100+99 from dual;

select to_date('2024','yyyy') from dual; --->2024-07-01 --格式中没有mm,返回当前月份的第一天

select to_date('202406','yyyy-mm') from dual;--->2024-06-01 ---格式中没有dd,返回参数月中的第一天

1.2. to_char(参数1，参数2)--将日期型或者数值型数据转成想要的字符型数据

参数1：目标日期或者数据型数据

参数2：格式

返回值：字符型数据

1.2.1.将数值转成字符

select 123,to_char(123),'123' from dual;

--格式：0 ，9 表示参数中的格式

0 代表格式，如果整数位无数字，则强制显示为0，若小数位无数字，则以0补全

9 代表格式，如果整数位无数字，则格式不生效， 若小数位无数字，则以0补全

FM 作用：将用0，或者9 去格式化后带来的空格 删除，还可以给用9格式化数据时的小数位补全的0 删除

select 123.45,to_char(123.45),to_char(123.45,'0000.0') from dual; ----小数位格式化，位数不够，结果小数位会四舍五入

select 123.45,to_char(123.45),to_char(123.45,'990.00'),to_char(123.45,'0000.999') from dual; --0,9格式可以混合用

select 123.4567,to_char(123.4567),to_char(123.4567,'999.99'),to_char(123.4567,'999.999') from dual;

--0作为格式，小数位精度不够

select 123.4567,to_char(123.4567),to_char(123.4567,'99.00'),to_char(123.4567,'00.000') from dual;

--无效的数字格式模型，格式化只能用0,9

select 123.4567,to_char(123.4567),to_char(123.4567,'88.00'),to_char(123.4567,'00.770') from dual;--千位符 123,456--货币符号 ￥ 人民币 $ 美元--千位符加在什么位置，就会显示在什么位置

select to_char(1234567.889,'fm99,999,999.999'),to_char(1234567.889,'fm99,9,99,9,99.999') from dual;--加货币符号时，$代表美元符号，￥：该符号不能使用，如果想显示，应该使用：L L：当地货币

select to_char(1234567.889,'fm99,999,9$99.999'), ---$ 位置不受限制，但是只能出现一次

to_char(1234567.889,'fmL99,999,L999.999') ---L 只能放在最前面

select to_char(1234567.889,'fm99,9$99,9$99.999') ---$ 位置不受限制，但是只能出现一次

1.2.2将日期转成字符

sysdate ---获取当前的系统时间

select sysdate from dual;

select sysdate,to_char(sysdate),to_char(sysdate,'yyyy-mm-dd') from dual;

select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual; --->12小时制

select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; ---》24小时制

select sysdate,to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') from dual; --->12小时制

select sysdate,to_char(sysdate,'yyyy') from dual; ----获取年份

select sysdate,to_char(sysdate,'year') from dual; ----twenty twenty-four

select sysdate,to_char(sysdate,'mm') from dual;-----获取月份

select sysdate,to_char(sysdate,'month') from dual;-----7月

select sysdate,to_char(sysdate,'mon') from dual;-----7月

select sysdate,to_char(sysdate,'dd') from dual; ----获取当月的第几天

select sysdate,to_char(sysdate,'day') from dual; ---》星期四

select sysdate,to_char(sysdate,'d') from dual; ----获取当周的第几天 周日为第一天

select sysdate,to_char(sysdate,'ddd') from dual;----获取当年的第几天

select sysdate,to_char(sysdate,'w') from dual; ----》获取当月的第几周

select sysdate,to_char(sysdate,'ww') from dual;----》获取当年的第几周

select sysdate,to_char(sysdate,'q') from dual; ---》当年的第几季度

练习：

--找出emp表中在2月受雇的员工

select t.* ,to_char(hiredate,'mm')from emp t where to_char(hiredate,'mm')=2;

select ename, hiredate, to_char(hiredate, 'mm')
  from emp
 where to_char(hiredate, 'mm') = 2;

1.3. to_number(参数1，参数2)

参数1：要转换的字符串

参数2：格式

功能：将字符串参数1转为数字

返回值：数值型数据

注意：

1.被转换的字符串必须符合数据型格式。如果被转换的字符串不符合数据型格式，oracle将抛出错误提示。

2.如果数字在格式范围内的话是正确的，否则会报错

格式： 0 9

9 代表一个数字，标识一个有效位

0 代表格式时，格式中整数部分0的个数和数字位数要保持一致

$ 代表美元符号

L 代表当地货币符号

. 显示为小数点

, 显示一个千位符

2.字符型函数

2.1拼接函数

concat(字符1，字符2)

作用：将字符1和字符2 进行链接，连接成一个字符

返回值： 字符型数据

和|| 对比：

1.concat 只能链接两个参数，要想连接多个，必须要用函数的嵌套

select concat(concat(concat('姓名',ename),'的工资是'),sal )from emp;

select '姓名'||ename||'的工资是'||sal from emp;

2.concat是函数，存在于多个数据库中 ， ||只适用于oracle数据库

2.2 大小写转换函数

upper(字符)

作用：将参数转为大写

返回值：字符型数据

lower(字符)

作用：将参数转为小写

返回值：字符型数据

2.3首字母大写函数

initcap(字符)

作用：将一串字符的首字符转为大写，其余转为小写

返回值：字符型数据

2.4 替换函数

(1)replace(原字符，目标字符，替换后的字符) ---整体替换

(2)translate(原字符，目标字符，替换后的字符) ---逐一对应替换

select translate('ABCDEDF','BCD','ghyttt') from dual;-->AghyEyF ---ttt多余字符不影响替换结果

select translate('abcdefgatbhifjik','abf','hmmmm') from dual;-->hmcdemghtmhimjik 逐一对应替换

select translate('abcdefgatbhifjik','abf','hm') from dual; ---》hmcdeghtmhijik -》如果参数3中没有对应的字符，则替换成 null

select translate('abcdefgatbhifjik','abf','hm ') from dual; ---》hmcde ghtmhi jik --》 f 替换成 空格

select translate('123456','#123','#') from dual; --》456 如果想筛掉参数1中某些字符，应在参数2中传入一个不相关的字符，同时，在参数3中也传入一个不相关的字符

select translate('123456','#','') from dual; --》如果替换字符（参数3）整个为空，则结果直接返回为null

select translate('123456','','h') from dual; --》如果目标字符（参数2）整个为空，则结果直接返回为null

select translate('123456','#',' ') from dual;---》123456

2.5 去除空格函数

trim(字符)

作用：去除字符左右两边的空格

返回值：字符型数据

ltrim(字符)

作用：去除字符左边的空格

返回值：字符型数据

rtrim(字符)

作用：去除字符右边的空格

返回值：字符型数据

2.6截取函数

substr(原字符，位置参数n，长度参数L)

作用：将原字符从第n位开始截取，截取出L个长度

返回值：字符型数据

例：

截取员工表中姓名的前两位字符

select ename,substr(ename,1,2) from emp;

select ename,substr(ename,0,2) from emp; --位置参数传0和1效果一样，都是从第一位开始截取

select ename,substr(ename,-1,4) from emp; --位置参数传负数，从倒数第几位开始截取，正向截取长度

select ename,substr(ename,2,-3) from emp; ---长度参数必须大于0，否则返回为空 null

select ename,substr(ename,2) from emp; ----长度参数不传参，默认截取到原字符的最后一位

select ename,substr(ename,2,10000) from emp;----长度参数超过剩余位数，截取到最后一位

2.7 填充函数

lpad(原字符，总长度L，要填充的值)

作用 ：向原字符的左侧填充想要的值，使其总长达到L

返回值：字符型数据

rpad(原字符，总长度L，要填充的值)

作用 ：向原字符的右侧填充想要的值，使其总长达到L

返回值：字符型数据

2.8 获取字符长度

length(参数)

作用：返回参数的字符长度

返回值：数字

lengthb(参数)

作用：返回参数的字节长度

返回值：数字

select lengthb('ertg') from dual;-----一个英文占1个字节

select lengthb('张三') from dual;---一个汉字占2个字节（GBK） 一个汉字占3个字节UTF-8）

2.9 获取字符位置的函数 定位字符位置函数

instr(参数1，参数2，n,m)

作用：定位字符位置，查询字符位置

返回值：数字

参数1：原字符

参数2：目标字符

参数3：n 从原字符的第几位开始找

参数4：m 目标字符出现的次数 不传参，默认找第1次出现

返回：目标字符出现第m次的位置（原字符的第一位开始数）

select instr('qweroracle','e',1,2),length('qweroracle') from dual;--->10

select instr('hello world',' ',1,1),length('qweroracle') from dual;--->6---空格要占一位

select instr('qwerorweracle','wer',1,2),length('qweroracle') from dual;--->7--查询字符串时，字符串要整体匹配，返回目标字符中第一个字符出现的位置

select instr('qwerorweraweclre','wer',1,3) from dual;--->0

---最后一个参数不传参，默认为第一次出现的位置

select length('jshfuakfidjdkaiwj'),instr('jshfuakfidjdkaiwj','j',3) from dual; ---》11

select instr('jshfuakfidjdkaiwj','j') from dual; --位置参数不写默认的是1

--第3个为参数是负数时，倒数第n位开始找，倒着找目标字符 位置：顺位数位置（正着数位置）

3.数值函数 number

3.1 绝对值函数

abs(数值)

作用：返回参数的绝对值

返回值：数值类型

3.2 向上取整

ceil(数值)

作用：返回离参数最近的比较大的整数

3.3 向下取整

floor(数值)

作用：返回离参数最近的比较小的整数

3.4取余函数 ---可以判断一个数的奇偶性

mod(m,n)

作用：返回m除以n的余数

返回值：数值

扩展：

--查询员工的岗位长度，判断该岗位长度的奇偶性

select job,length(job), case when mod(length(job),2)=0 then '偶数' else '奇数' end from emp;

3.5power(m,n) --取m的n次方

3.6 round(m,n)

作用：保留m中的n位小数，并且会四舍五入

返回值：数值

3.7 trunc (m,n)

作用：保留m中的n位小数，不会四舍五入

返回值：数值

3.8sign(n) --判断正负 ---可以判断两个数的大小

作用：判断正负

返回值：数值 --参数为负数，返回-1，参数为正数 返回1，参数为0 返回0，参数为空值，返回空

4.日期函数

4.1. round() --会四舍五入

trunc() --不会四舍五入

select sysdate,

round(sysdate), ---最近0点的日期

round(sysdate, 'day'), --最近的星期日

round(sysdate, 'month'),--最近的月初日期

round(sysdate, 'year'),---最近的年初日期

round(sysdate, 'q') ----最近的季初日期

from dual;

select sysdate,

trunc(sysdate), ---当天的日期

trunc(sysdate, 'day'), --本周的星期日

trunc(sysdate, 'month'),--本月的月初日期

trunc(sysdate, 'year'),---本年的年初日期

trunc(sysdate, 'q') ----本季的季初日期

from dual;

select trunc(to_date('2024-5-13','yyyy-mm-dd'),'day') from dual;

4.2 months_bewteen(date1,date2)

--传参时如果date1>date2,返回值是正数，否则，为负数

作用:计算两个日期的间隔月数

返回值：数值

4.3 add_months(date1,n) --月份加减函数

作用：在原有日期基础上加 n 个月

返回值：日期型数据

select sysdate,add_months(sysdate,6) from dual; ---月份+

select sysdate,add_months(sysdate,-6) from dual; ---月份 -

4.4 last_day(date) ---获取月末日期函数

作用：返回参数所在月的最后一天

返回值：日期型数据

日期间的直接运算：

1.日期可以直接相减

select sysdate-to_date('2024-6-26','yyyy-mm-dd') from dual; ---返回结果以天数为单位

2.天数的加减 --返回结果为日期

select sysdate+5, sysdate-5 from dual;

3.日期的加减公式

select sysdate,sysdate + interval '7' year from dual;

4.5 to_char()--获取日期的年份，月份，季度，周几

select sysdate,to_char(sysdate,'yyyy') from dual; ----获取年份

select sysdate,to_char(sysdate,'year') from dual; ----twenty twenty-four

select sysdate,to_char(sysdate,'mm') from dual;-----获取月份

select sysdate,to_char(sysdate,'month') from dual;-----7月

select sysdate,to_char(sysdate,'mon') from dual;-----7月

select sysdate,to_char(sysdate,'dd') from dual; ----获取当月的第几天

select sysdate,to_char(sysdate,'day') from dual; ---》星期四

select sysdate,to_char(sysdate,'d') from dual; ----获取当周的第几天 周日为第一天

select sysdate,to_char(sysdate,'ddd') from dual;----获取当年的第几天

select sysdate,to_char(sysdate,'w') from dual; ----》获取当月的第几周

select sysdate,to_char(sysdate,'ww') from dual;----》获取当年的第几周

select sysdate,to_char(sysdate,'q') from dual; ---》当年的第几季度

5.其他函数

5.1 nvl(参数1，参数2) --去空函数

--要求两个参数的数据类型保持一致

作用：将参数1中的空值替换成想要的值（参数2）

select ename,job, nvl(job,999) from emp; ---字符型可以兼容数字型数据

select ename，hiredate,nvl(hiredate,999) from emp; ---要求两个参数的数据类型保持一致

5.2 coalesce(参数1，参数2，参数3，参数4...)

---判断非空函数 要求参数的数据类型保持一致

返回：返回第一个不为空的参数

5.3 distinct 关键字 去重

作用：将查询结果去重

--分组查询可以实现去重

5.4 条件判断函数

5.4.1 decode

decode(条件表达式，值1，返回值1，值2，返回值2。。，默认值) --默认值如果不传参，返回空

5.4.2 case when

case when 条件1 ---和where 后边的条件写法一样 sal>3000

then 结果1 --满足条件1后 要执行的内容

when 条件2

then 结果2

。。。

else

其他结果 ---不满足以上所有条件，要执行的内容

end

练习（case when）

--1.显示员工的名字，工资，如果工资小于1000，显示小可怜，大于1000小于2000，

--显示一千多，如果工资大于2000小于3000 显示二千多，如果工资大于3000小于4000 显示三千多。。

--其他 显示 土豪

select ename,
       sal,
       case
         when sal < 1000 then
          '小可怜'
         when sal >= 1000 and sal <2000 then
          '一千多'
         when sal >= 2000 and sal < 3000 then
          '二千多'
         when sal >= 3000 and sal < 4000 then
          '三千多'
         else
          '土豪'
       end
  from emp;

2.某公司一三季度业绩不好，想将第一三季度入职员工降薪20%，给二四季度加薪10%

select ename ,to_char(hiredate,'q'),sal,case when to_char(hiredate,'q')=1 or to_char(hiredate,'q')=3 then sal*0.8 else sal*1.1 end from emp;

select ename,sal,decode(to_char(hiredate,'q'),1,sal*0.8,3,sal*0.8,sal*1.1) from emp;

select sal,
       to_char(hiredate, 'q'),
       case
         when to_char(hiredate, 'q') in (1, 3) then
          sal * 0.8
       else
          sal + sal * 0.1
       end
  from emp;
	
select ename,hiredate,sal,case when mod(to_char(hiredate,'q'),2) = 1 then sal*0.8 else sal*1.1 end from emp;

--5.emp表中工资能被3整除的,提成加工资的10%，被4整除的提成加工资的20%，被5整除的提成加工资的50%
select ename,sal,
       nvl(comm, 0),
       case
         when mod(sal, 5) = 0 then
          nvl(comm, 0) + sal * 0.5
         when mod(sal, 4) = 0 then 
					 nvl(comm, 0) + sal * 0.2 
					 when  mod(sal, 3) = 0 then
          nvl(comm, 0) + sal * 0.1
         else
          nvl(comm, 0)
       end
  from emp;
	
select * from emp;