数据库之函数
概念
- 单行函数：针对sql语句影响的每一行都进行处理，并针对这一行返回一个结果sql影响多少行就返回多少个结果
- 组函数 ：对sql语句影响的所有行进行综合处理，最终返回一个结果无论sql语句影响多少行都只返回一个结果（组函数会忽略空值）
对字符串处理的函数
dual表说明
假设一张表有100w条数据，测试单行函数，会返回100w条数据，这样返回会占屏幕，很麻烦所以系统提供一个测试表，dual（单行单列的表）
select * from dual;
可以用它进行单行函数的测试（也可以自己再建一个单行单列的表）
upper（par1） 对字符串变大写
演示：
select first_name, upper(first_name) from s_emp where id=1;
影响了一行的情况（id为1的只有一个）：
select first_name, upper(first_name) from s_emp where id<1;
一行都没有影响的情况（id小于1的不存在）：
select first_name, upper(first_name) from s_emp where id>1;
影响多行的情况（id大于1的有24个）：
lower（par1） 对字符串变小写
演示：测试lower函数：
select lower('HELLO') from dual;
initcap（par1） 对每个单词的首字母变大写
演示：测试initcap函数
select initcap('one world one dream') from dual;
oncat函数（用的少，一般用||）
oncat（par1 varchar2，par2）//把类型写后面 连接字符串
演示：测试concat函数：
select concat('hello', 'world') from dual;
要是复杂拼接，用concat不方便。
select concat(concat('a','b'),concat('c','d')) from dual;
需要函数嵌套（把被嵌套函数的返回值作为函数的参数使用）
select 'a' || 'b' || 'c' || 'd' con from dual;
用|| 就更方便。
substr函数
substr（par1，par2，par3） 截取字符串
- par1：是要处理的数据
- par2：
- 指定从什么位置进行处理。
- 编号从1开始，可以是负数（从后向前处理）。
- -1代表最后一个字符
- par3：截取多长
如果非要从0编号，oracle会自动把0变为1。
select substr('hello',0,2) from dual;
select substr('hello',1,2) from dual;
select substr('hello',-3,2) from dual;
演示：把s_emp表中的first_name和first_name的后三个字符显示出来
select first_name ,substr(first_name,-3,3) from s_emp;
replace函数
replace（par1，par2，par3）
- par1 要处理的内容
- par2 要被替代的内容
- par3 要替换成的内容
select replace('one world one dream','one','two') from dual;
nvl函数
nvl（par1，par2） 为空时替换
- par1 要处理的内容
- par2 为空时要替换的内容
处理数字的函数
round函数
round（par1，par2） 四舍五入
- par1 要处理的数据
- par2 指定要如何处理（默认是取整）
- 0是默认四舍五入取整
- 正数n是保留小数点后n位
- 负数n是直接对小数点前n位进行四舍五入取整（可能会丢失数据）
演示：
四舍五入默认取整:
select round(9.58) from dual;
保留小数点后两位四舍五入：
select round(9.486,2) from dual;
对小数点前两位进行四舍五入取整：
select round(190.486,-2) from dual;
trunc函数
trunc（par1，par2） 截取
- par1 要处理的数据
- par2 指定要如何处理 默认是取整截取
- 0是默认取整截取
- 正数n是保留小数点后n位
- 负数n是直接对小数点前n位进行截取
select trunc(9.58) from dual; select trunc(9.486,2) from dual; select trunc(190.486,-2) from dual; select trunc(190.486,-1) from dual;
to_number函数
to_number（par1）
- 把数字字符串转换为数字
- 只能转换数字字符串，不能是非数字的字符串
- 实际上sql里会自动进行转换，所以这个函数用的很少
所以下面三个结果一样：
select id, first_name from s_emp where id='1';
select id, first_name from s_emp where id=to_numer('1');
select id, first_name from s_emp where id=1;
to_char函数
to_char（par1，par2） 转换数字的显示格式
- par1 要处理的数字或者数字字段
- par2 显示格式 可以省略，代表把一个类型变成字符串类型
- fm 开头
- $ 美元符号
- L 本地货币符号 和操作系统的本地语言有关 中国的是￥
- 9 ：在小数点前面，代表0~9的任意数字；
在小数点后，代表1~9的任意数字
- 0 ：在小数点前面，代表强制显示前导零 e.g. 12345—> 012 , 345.00
在小数点后面代表0~9的任意数字
- ，：分割符号
- . ： 小数点
演示：
select to_char(12345,'fm$099,999.99') from dual;
select to_char(12345,'fm$099,999.00') from dual;
select to_char(12345.85,'fmL099,999.99') from dual;
select salary , to_char(salary,'fm$000,000.00') from s_emp;
select salary, to_char(salary,'fm$099,999.00') from s_emp;
如何修改本地语言
（1）远程登录服务器后，切换shell
bash
（2）打开配置文件
vi .bash_profile
（3）写入配置
export NLS_LANG=’SIMPLIFIED CHINA.ZHS16GBK’（简体中文编码）
export NLS_LANG=’AMERICAN_AMERICA.ZHS16GBK’（美语）
（4）保存退出
Esc+shift+z z
（5）source .bash_profile 让配置文件生效
（6）重新进入sqlplus
函数的嵌套
把一个函数的返回值作为另一个函数的参数
演示：把s_emp表中的first_name和first_name的后三个字符输出结合substr和length（计算长度）
select first_name, substr(first_name,length(first_name)-2,3);
因为编号是从1开始的，length（first_name）是倒数第一个。
length（first_name）-2就是倒数第三个了
演示：显示s_emp表中的id first_name manager_id，如果manager_id是NULL，则显示成BOSS
select id, first_name, nvl(to_char(manager_id), 'BOSS');
组函数
常见的函数：
- count（par1） 统计个数
- max（par1） 统计最大值
- min（par1） 统计最小值
- avg（par1） 统计平均值
- sum（par1） 统计和
演示：统计s_emp表中工资的最大值和最小值
select count(*), max(salary), min(salary) from s_emp;
（*只用在count里面，统计有多少条）
演示：统计s_emp表中的工资平均值和工资的和
select count(*), avg(salary), sum(salary) from s_emp;
把重复的值去掉之后再统计：
select count(*), avg(distinct salary), sum(distinct salary) from s_emp;
组函数对NULL是如何处理的？
忽略不统计（排序里把空当做最大值，但是在组函数统计最大值是是忽略空）
例如：25个员工里，有些人的提成为空；
演示：统计s_emp中提成的个数，提成的和，提成的平均工资
select count(commission_pct), sum(commision_pct), avg(commission_pct) from s_emp;
