SQL学习之函数，基于Oracle下的HR用户（一）

3.1.1 什么是函数
函数：是数据库产品中提供的能够处理查询结果的方法。函数能够用于下面的目的：
• 执行数据计算
• 修改单个数据项
• 格式化显示的日期和数字
• 转换列数据类型
• 函数有输入参数，并且总有一个返回值。
3.1.2 函数类型
单行函数：这些函数仅对单个行进行运算，并且每行返回一个结果。
多行函数(聚合函数)：这些函数能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数。

3.1.3 函数语法
function_name(arg1,arg2,…)
function_name：是函数的名字。
arg1, arg2：是由函数使用的任意参数。参数可以是一个列名、用户提供的常数、变量值、或者一个表达式。
3.2 单行函数
3.2.1 单行函数的特性包括：
• 作用于每一个返回行，每行返回一个结果
• 可能需要一个或多个参数
• 可以修改结果集的数据类型
• 可以嵌套
• 可能返回一个与参数不同类型的数据值
• 能够用在 SELECT、WHERE 和 ORDER BY 子句中
3.2.2 单行函数分类
字符函数：接受字符输入，可以返回字符或者数字值
数字函数：接受数字输入，返回数字值
日期函数：对 DATE 数据类型的值进行运算 (除MONTHS_BETWEEN 函数返回一个数字，所有日期函数都返回一个 DATE 数据类型的值。)
转换函数：从一个数据类型到另一个数据类型转换一个值
通用函数：
− NVL
− NVL2 − NULLIF − COALSECE
− CASE
− DECODE
3.3 字符函数
字符函数：单行字符函数接受字符数据作为输入，既可以返回字符值也可以返回数字值。
3.3.1 字符函数分类
• 大小写处理函数
• 字符处理函数

大小写处理函数	结果	含义
lower(‘SQL Course’)	sql course	将字符串转换为小写
upper(‘SQL course’)	SQL COURSE	将字符转换为大写
initcap(‘SQL Course’)	Sql Course	将单词首字母转换成大写

3.3.3 示例一

查询员工表，使用 “ The Job id for ”链接转换大写格式后的员工姓名，并使用“ is ” 字符串链接他们工作 ID ，要求将工作 ID 转换小写格式。修改列名为“ EMPLOYEE DETAILS”。
select ' The job id for'||upper(last_name)||'is'|| lower(job_id) "EMPLOYEE DETAILS" from employees;

3.3.4 示例二

显示雇员 higgins 的雇员号、姓名和部门号
select employee_id,last_name,department_id from employees where last_name = initcap('higgins');

3.4 字符处理函数
dual 表：dual 是一张只有一个字段，一行记录的表。dual 表也称之为’伪表’，因为他不存储主题数据。如果我们不需要从具体的表来取得表中数据，而是单纯地为了得到一些我们想得到的信息,并要通过 select 完成时,就要借助 dual 表来满足结构化查询语言的格式。

函数	结果	说明
concat(‘hello’，‘Word’)	helloWord	两个字符串连接
substr(‘HelloWord’,1,5)	Hello	根据指定参数截取字符串
length(‘helloword’)	10	计算字符串长度
instr(‘helloword’,‘w’)	6	返回指定字符的位置
lpad(salary, 10 , ‘*’)	*****24000	按指定参数在字符串前面添加指定字符
rpad(salary, 10, ‘*’)	24000*****	按指定参数在字符串后面添加指定字符
trim(‘H’ from ‘Hello Word’)	elloWord	去掉指定字符

CONCAT(arg1,arg2)：连接值在一起 (CONCAT 函数有两个输入参数)
arg1：字符串类型。字符拼接的值。
arg2：字符串类型。字符拼接的值。

SUBSTR(arg1,arg2,arg3)：截取子串。
arg1：字符串类型。原字符串，
arg2：整数类型。开始位置(开始位置可以是一个负数，-1 表示原串的最后一位，- 2 则表示倒是第二位以此类推)，
arg3：整数类型。截取个数。

SUBSTR(arg1,arg2)：截取子串。
arg1：字符串类型。原字符串。
arg2：开始位置(开始位置可以是一个负数，-1 表示原串的最后一位，-2 则表示倒是第二位以此类推)截取到末尾。

LENGTH(arg1)：以数字值显示一个字符串的长度。
arg1：字符串类型。计算长度的字符串。

INSTR(arg1,arg2)：找到一个给定字符的数字位置。
arg1：字符串类型。原字符串。
arg2：字符串类型。查找内容。

INSTR(arg1,arg2,arg3,arg4)：指定查找位置以及出现的次数。
arg1：字符串类型。原字符串。
arg2：字符串类型。查找内容。
arg3：整数类型。开始位置。
arg4：整数类型。第几次出现。

LPAD(arg1,arg2,arg3)：用给定的字符左填充字符串到给定的长
度。
arg1：字符串类型。原字符串。
arg2：整数类型。总长度。
arg3：字符串类型。填充的子字符串。

RPAD(arg1,arg2,arg3)：用给定的字符右填充字符串到给定的长度。
arg1：字符串类型。原字符串。
arg2：整数类型。总长度。
arg3：字符串类型。填充的子字符串。

TRIM(arg1)：从一个字符串中去除头(leading)或尾(trailing)或头尾两侧(both)的字符 (默认为头尾两侧) 如果 trim_character 或 trim_source 是一个文字字符，必须放在单引号中。
arg1 需要操作的字符串。FROM 为关键字。
格式 1：需要去掉的内容 FROM 原字符串
格式 2：leading|trailing|both 需要去掉的内容 FROM 原字符
串。

--去掉头尾两侧方法一：
select trim('H from HelloWorldH') from dual;
--去掉头尾两侧方法二：
select trim(both 'H' from 'HelloWorldH') from dual;
--去掉头：
select trim(leading 'H' from 'HelloWorldH') from dual;
--去掉尾：
select trim(trailing 'H' from 'HelloWorldH') from dual;

REPLACE(arg1,arg2,arg3)：REPLACE 函数是用另外一个值来替代串中的某个值。
arg1：字符串类型。原字符串。
arg2：字符串类型。需要替换的子串。
arg3：字符串类型。替换的内容。

3.4.1 示例一

--显示所有工作岗位名称从第 4 个字符位置开始包含字符串 REP 的雇员的信息，将雇员的姓和名连接显示在一起，还显示雇员名的长度，以及名字中字母 a 的位置。
select concat(first_name,last_name),length(last_na me),instr(last_name,'a') from employees where substr(job_id,4)='REP';

3.4.2 示例二

--显示名字是以 n 结束的雇员的数据，将雇员的姓和名连接显示在一起，还显示雇员名的长度，以及名字中字母 a 的位置。
select concat(first_name,last_name),length(last_name),instr(last_name,'a') from employees where substr(last_name,-1)='n';

3.4.3 示例三

--将手机号中间四位用星号代替。
select replace('13622329860',substr('13622329860',4,4),'****') from dual;

3.5 数字函数

函数	结果	说明
round(45.926,2)	45.93	四舍五入指定小数的值
trunc(45.926,2)	45.92	截取小数点后的位数，不做四舍五入处理
mod(1600,300)	100	取余

ROUND(arg1,arg2)：四舍五入指定小数的值。
arg1：数字类型。原数字。
arg2：整数类型。小数点保留的位数，可以是一个负数。负数则表示指定整数的位置。

ROUND(arg1)：四舍五入保留整数。
arg1：数字类型。原数字。
arg2：整数类型。小数点保留的位数。

TRUNC(arg1,arg2)：截断指定小数的值，不做四舍五入处理。
arg1：数字类型。原数字。
arg2：整数类型。小数点保留的位数，可以使一个负数。负数则表示指定整数的位置。

TRUNC(arg1)：四舍五入保留整数。
arg1：数字类型。原数字。
arg2：整数
类型。小数点保留的位数。

MOD(arg1,arg2)：取余。
arg1：数字类型。被除数。
arg2：数字类型。除数。

3.5.1 示例一

计算所有是销售代表(SA_REP)的雇员的工资被 5000 除后的余数。
select last_name,salary,mod(salary,5000)
from employees where job_id ='SA_REP';