函数介绍
什么是函数
函数:是数据库产品中提供的能够处理查询结果的方法
函数能够用于下面的目的:
- 执行数据计算
- 修改单个数据项
- 格式化显示的日期和数字
- 转换列数据类型
- 函数有输入参数,并且总有一个返回值
函数类型
单行函数:这些函数仅对单个行进行运算,并且每行返回一个结果
多行函数(聚合函数):这些函数能够操纵成组的行,每个行组给出一个结果,这些函数也被成为组函数
SQL函数的两种类型
函数语法
function_name(arg1,arg2,......)
function_namer:是函数的名称
arg1,arg2:是由函数使用的任意参数。参数可以是一个列名、用户提供的常数、变量值、或者一个表达式
单行函数
单行函数的特性包括:
- 作用于每一个返回行,每行返回一个结果
- 可能需要一个或者多个参数
- 可以修改结果集的数据类型
- 可以嵌套
- 可能返回一个与参数不同类型的数据值
- 能够用在select 、where 和order by 子句中
单行函数分类
字符函数:接受字符输入,可以返回字符或者数字值
数字函数:接收数字输入,返回数字值
日期函数:对date数据类型的值进行运算(除了months_between函数返回一个数字,所有日期函数都返回一个date数据类型的值)
转换函数:从一个数据类型到另一个数据类型转换一个值
通用函数:
- nvl
- nvl2
- nullif
- coalsece
- case
- decode
字符函数
字符函数:单行字符函数接受字符数据作为输入,既可以返回字符值也可以返回数字值
字符函数分类
- 大小写处理函数
- 字符处理函数
大小写处理函数
函数 | 结果 |
lower(‘SQL course’) upper(‘SQL course’) initcap(‘SQL course’) | sql course SQL COURSE Sql Course |
lower
:转换大小写混合的字符串为小写字符串
upper
:转换大小写混合的字符串为大写字符串
initcap
:将每个单词的首字母转换为大写,其他字母为小写
大小写处理函数需要一个参数,参数类型为字符串类型,返回一个字符串
--查询员工表,使用‘The Job id for’链接转换大写格式后的员工姓名,并使用‘is’字符串链接他们的工作ID转换为小写格式
--修改列名为‘EMPLOYEE DETAILS’
select 'The Job id for'||upper(last_name)||'is'||lower(job_id) as "EMPLOYEE DETAILS"
from employees
--显示雇员higgins的雇员号,姓名,部门号
select employee_id,last_name,department_id from employees
where last_name='Higgins'or first_name='Higgins'
--或者用lower
select employee_id,last_name,department_id from employees
where lower(last_name)='higgins'or lower(first_name)='higgins'
字符处理函数
dual表:dual是一张只有一个字段,一行记录的表。dual表也称为为‘伪表’,因为它不存储主题数据。如果我们不需要从具体的表来取得表中数据,而是单纯地为了得到一些我们想得到的信息,并且通过select完成时,就需要借助dual表来满足结构化查询语言的格式
函数 | 结果 |
concat(‘Hello’,‘World’) substr(‘HelloWorld’,1,5) length(‘HelloWorld’) instr(‘HelloWorld’,‘W’) lpad(salary,10,‘*’) rpad(salary,10,‘*’) trim(‘H’ from ‘HelloWorld’) | HelloWorld Hello 10 6 *****24000 24000***** elloWorld |
concat(arg1,arg2)
:连接值在一起,字符串拼接(concat函数有两个输入参数)
arg1:字符串类型,字符拼接的值
arg2:字符串类型,字符拼接的值
--dual伪表
--concat字符串拼接
select concat('Hello','World') from dual
select concat(employee_id,last_name) from employees
substr(arg1,arg2,arg3)
:截取子串
arg1:字符串类型,原字符串
arg2:整数类型,开始位置(开始位置可以是一个负数,-1表示原串的最后一位,-2则表示倒数第二位以此类推)
arg3:整数类型,截取个数
--substr字符串截取
--注意-1虽然代表是最后一位,但截取顺序依然是从左往右
select upper(substr(last_name,1,3)) from employees
select substr('HelloWorld',1,5) from dual --Hello
select substr('HelloWorld',-1,5) from dual --d
select substr('HelloWorld',-5,5) from dual --World
substr(arg1,arg2)
:截取子串
arg1:字符串类型,原字符串
arg2:整数类型,开始位置(开始位置可以是一个负数,-1表示原串的最后一位,-2则表示倒数第二位以此类推)截取到末尾
--两个参数,从当前位置开始截取到末尾
select substr('HelloWorld',-5) from dual --World
select substr('HelloWorld',6) from dual --World
length(arg1)
:以数字值显示员工字符串的长度
arg1:字符串类型,计算长度的字符串
--length 计算字符串长度
select last_name,length(last_name) as "员工名字长度" from employees;
select length('HelloWorld') from dual --10
instr(arg1,arg2)
:找到一个给定字符的数字位置
arg1:字符串类型,原字符串
arg2:字符串类型,查找内容
instr(arg1,arg2,arg3,arg4)
:指定查找位置以及出现的次数
arg1:字符串类型,原字符串
arg2:字符串类型,查找内容
arg3:整数类型,开始位置(从哪个位置进行查找)
arg4:整数类型,第几次出现(要查找的字符在当前字符串中是第几次出现)
--instr(x,x)找到给定字符的数字位置
select last_name,instr(last_name,'a') from employees
select instr('HelloWorld','o') from dual --5
--instr(x,x,x,x) 指定位置搜索以及出现次数----下标
select instr('HelloWorld','l',1,2) from dual --4
select instr('HelloWorld','l',1,3) from dual --9
select instr('HelloWorld','l',6,1) from dual --9
lpad(arg1,arg2,arg3)
:用给定的字符左填充字符串到给定的长度
arg1:字符串类型,原字符串
arg2:整数类型,总长度
arg3:字符串类型,填充的子字符串
--lpad(x,x,x) 给定的字符左填充到指定长度--l--left
--原字符串,总长度,填充的字符
select lpad('hello',5,'*') from dual --*****hello
select lpad(last_name,2,'q') from employees --******Abel
--rpad(x,x,x) 给定的字符右填充到指定长度--r--right
略
rpad(arg1,arg2,arg3)
:用给定的字符右填充字符串到给定的长度
arg1:字符串类型,原字符串
arg2:整数类型,总长度
arg3:字符串类型,填充的子字符串
trim(arg1)
:从一个字符串中除去头(leading)或尾两侧(both)的字符(默认为头尾两侧)如果trim_character或trim_sources是一个文字字符,必须放在单引号中
arg1:需要操作的字符串,from为关键字
格式1:需要去掉的内容,‘要去除的字符’ from 原字符串
格式2:leading|trailing|both 需要去掉的内容from原字符串
--trim 去除头尾字符
select trim('A' from last_name) from employees;
select last_name from employees;
--默认是有both关键字的
select trim(both 'l' from 'lHelloWorldl') from dual --HelloWorld
--只去掉头
select trim(leading 'l' from 'lHelloWorldl') from dual --HelloWorldl
--只去掉尾
select trim(trailing 'l' from 'lHelloWorldl') from dual --lHelloWorld
replace(arg1,arg2,arg3)
:replace函数是用另外一个值来替代串中的某个值
arg1:字符串类型,原字符串
arg2:字符串类型,需要替换的子串
arg3:字符串类型,替换的内容
--replace 用一个值替换串中某个值
select replace(salary,2600,100000) from employees --2600替换成了100000
select replace('Helloworld','l','*') from dual --He**owor*d
字符函数案例练习
--将工作岗位名称从第4个字符位置开始显示,将雇
--员的姓和名连接显示在一起,还显示雇员名的长度,以及名字中字母a的位置。
select substr(job_id,4),length(first_name||last_name)
as "雇员姓名长度",instr(first_name||last_name,'a')
as "雇员姓名‘a’的位置" from employees
order by 1 asc,2 desc,3 desc
--显示名字是以n结束的雇员的数据,将雇员的姓和名连接显示在一起,还显示雇员名
--的的长度,以及名字中字母a的位置。
select first_name||last_name as "雇员名字以‘n’结尾",length(first_name||last_name)
as "雇员姓名长度",instr(first_name||last_name,'a')
as "雇员姓名‘a’的位置" from employees
where substr(last_name,-1) in 'n'
order by 2 desc
--将手机号中间四位用星号替代
select replace('18238657913','38657','*****') from dual
--先找到要替换的字符串,再进行替换
--replace 替换,substr 截取(源字符串,从哪个截取,截取长度)
select replace('18238657913',substr('18238657913',4,5),'*****' )
from dual
数字函数 ![](https://i-blog.csdnimg.cn/blog_migrate/8ccd521875f0fc7948f7fdc04ccb65fe.png)
round(arg1,arg2)
:四舍五入指定小数的值
arg1:数字类型,原数字
arg2:整数类型,小数点保留的位数,可以用一个负数,负数表示指定整数的位置
round(arg1)
:四舍五入保留整数
arg1:数字类型,原数字
arg2:整数类型,小数点保留的位数
trunc(arg1,arg2)
:截断指定小数的值,不做四舍五入处理
arg1:数字类型,原数字
arg2:整数类型,小数点保留的位数,可以用一个负数,负数表示指定整数的位置
trunc(arg1)
:不做四舍五入保留整数
arg1:数字类型,原数字
arg2:整数类型,小数点保留的位数
mod(arg1,arg2)
:取余
arg1:数字类型,被除数
arg2:数字类型,除数
日期处理
日期的说明
sysdate函数: 是一个日期函数,返回当前数据库服务器的日期和时间
用日期计算
- 从日期加或减得到一个数,结果是一个日期值
- 两个日期相减,得到两个日期之间的天数
- 用小数除以24,可以加小数到日期上
运算 | 结果 | 说明 |
date+number | 日期 | 加一个天数到一个日期上 |
date-number | 日期 | 从一个日期上减一个天数 |
date-date | 天数 | 用一个日期减另一个日期上 |
date+number/24 | 日期 | 加一个小时数到一个日期上 |
用日期做算术运算
略
日期函数
函数 | 说明 |
months_between | 两个日期之间的月数 |
add_months | 加日历月到日期 |
next_day | 下个星期几是几号 |
last_day | 指定月的最后一天 |
round | 四舍五入日期 |
trunc | 截断日期 |
数据类型转换
隐式数据类型转换
隐式转换:当数据源的类型和目标数据的类型不同时,如果没有转换函数,就会发生隐式转换,也称自动转换
从 | 到 |
varchar2 or char | number |
varchar2 or char | date |
number | varchar2 |
date | varchar2 |
从 | 到 |
varchar2 or char | number |
varchar2 or char | date |
隐式转换的问题
性能影响:最大的问题就是转换时会导致索引的无效,进而可能导致全表扫描。当表的数据量很大的时候,会产生很大的性能问题;比如varchar2和nvarchar2隐式数据类型转换导致的性能问题
不便于阅读:难以了解到究竟发了怎样的类型转换,而且如果代码很长查出问题就需要时间
显示数据类型转换
通过数据库中的转换函数完成对数据类型的转换
to_char(arg1,‘fmt’):将一个日期或者数字转换为字符类型,带格式化样式fmt。
arg1:数字或日期类型,需要转换的数据
fmt:转换格式
to_char日期转换
to_char数字转换
to_number字符串到数字转换
to_number(‘arg1’,‘fmt’):将字符串转换为数值型的格式,带格式化样式fmt
arg1:字符串类型,需要转换的数据
fmt:转换格式
to_date字符到日期转换
to_date(‘arg1’,‘fmt’):将字符串转换为数值型的格式,带格式化样式fmt
arg1:字符串类型,需要转换的数据
fmt:转换格式
select to_date('2019-03-09','yyyy-MM-dd') from dual;
select to_date('2019-03-09 11:57','yyyy-MM-dd HH:MI') from dual;
select to_date('2019年03月9日 11点30分','yyyy"年"MM"月"DD"日"HH"点"MI"分"') from dual;
函数嵌套
- 单行函数能够被嵌套任意层次
- 嵌套函数的计算是从最里层到最外层,参数格式也要正确
通用函数
通过函数:可用于任意数据类型,并且适用于空值
nvl(expr1,expr2)
nvl2(expr1,expr2,expr3)
nullif(expr1,expr2)
coalesce(expr1,expr2...,exprn)
nvl(expr1,expr2)函数:
转换一个空值到一个实际的值(参数类型必须匹配)
expr1,expr2:可用的数据类型可以是日期、字符和数字。两个参数的数据类型必须匹配
expr1:是包含空值的源值或者表达式
expr2:是用于转换空值的目的值
--计算所有员工的年薪,如果有佣金则包含佣金
--如果佣金是空值,则因为空值运算,都变为空值了
select last_name,salary*12*commission_pct from employees
-- nvl(expr1,expr2)使用函数
select last_name,salary*12*nvl(commission_pct,1) from employees order by 2 desc
--求年薪,有佣金则年薪乘以佣金百分比
select last_name,salary*12+salary*12*nvl(commission_pct,0),salary,commission_pct
from employees order by 2 desc
nvl2(expr1,expr2,expr3)函数:
nvl2函数检查第一个表达式,如果第一个表达式不为空,那么nvl2函数返回第二个表达式;如果第一个表达式为空,那么第三个表达式被返回
expr1:是包含空值的源值或者表达式
expr2:expr1非空时的返回值
expr3:expr1非空时的返回值
--查询雇员信息,有佣金显示sal+comm,没有显示sal
select last_name,nvl2(commission_pct,'sal+comm','sal')
from employees order by 2 asc
--这里包含了一个隐式转换
--查询雇员信息,有佣金显示sal+comm,没有显示sal
select last_name,nvl2(to_char(commission_pct),'sal+comm','sal')
from employees order by 2 asc
nullif(expr1,expr2)函数:
比较两个表达式,如果相等函数返回空,如果不相等函数返回第一个表达式。第一个表达式不能为null
expr1是对于expr2的被比较原值
expr2是对于expr1的被比较原值(如果它不等于expr1,expr1被返回)
--nullif(expr1,expr2)
--查询雇员,显示他们的first_name与长度,长度列命名为exprl。last_name与长度,长
--度命名为expr2。判断他们的first_name与last_name的长度,如果长度相同返回空,否则返
--回first_ name的长度。判断结果列命名为result.
select first_name expr1,last_name expr2,
nullif(length(first_name),length(last_name)) result
from employees
coalesce(expr1,expr2...,exprn)函数:
expr1:如果它非空,返回该表达式
expr2:如果第一个表达式为空并且该表达式非空,返回该表达式
expr3:如果前面的表达式都为空,返回该表达式
--查询雇员表,如果COMMISSION PCT值是非空,显示它。如果COMMISSION PCT
--值是空,则显示SALARY。如果COMMISSION PCT和SALARY值都是空,那么显示
--10。
select last_name,coalesce(commission_pct,salary,10) from employees order by 2 asc
条件表达式
在SQL语句中提供 if-then-else 逻辑的使用
两种用法:
- case表达式
- decode函数
case表达式
case表达式:可以让你再SQL语句中使用if-then-else的逻辑。如果没有when...then满足条件,并且else子句存在。Oracle返回else_expr。否则,Oracle返回null。所有的表达式(expr、comparison_expr和return_expr)必须是相同的数据类型
--查询雇员,显示last_ name,job_ id,salary 如果JOB_ ID是IT_ PROG,薪水增加10%;
--如果JOB_ ID是ST_ CLERK,薪水增加15%; 如果JOB_ ID是SA REP,薪水增加20%。
--对于所有其他的工作角色,不增加薪水。
select last_name,job_id,salary,
case job_id
when 'IT_PROG' then salary*1.1
when 'ST_CLERK' then salary*1.15
when 'SA_REP' then salary*1.2
end
from employees;
decode函数
decode函数:以一种类似在多种语言中使用的if-then-else逻辑的方法判断一个表达式。decode函数在比较表达式(expression)和每个查找值(search)值后,如果表达式与查找相同,返回结果。如果省略默认值,当没有查找值与表达式相匹配时返回一个空值
--decode函数实现
select last_name,job_id,salary,
decode(
job_id,'IT_PROG',salary*1.1,
'ST_CLERK',salary*1.15,
'SA_REP',salary*1.2
) from employees;