欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

数据库之函数

程序员文章站 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');

数据库之函数