数据库之函数
程序员文章站
2022-06-20 13:23:02
概念 单行函数:针对sql语句影响的每一行都进行处理,并针对这一行返回一个结果sql影响多少行就返回多少个结果 组函数 :对sql语句影响的所有行进行综合处理,最终返回一个结果无论sql语句影响多少行都只返回一个结果(组函数会忽略空值) 对字符串处理的函数 dual表说明 假设一张表有100w条数据 ......
概念
- 单行函数:针对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');