关闭

函数总结

121人阅读 评论(0) 收藏 举报
分类:

单行函数:

--字符串函数、数字函数、日期函数、通用函数、类型转换函数

 

1.字符串函数:

 

--1)大小写转换函数

--lower(列名或者字面量):将参数值转换成全小写字母返回

--upper(列名或者字面量):将参数值转换成全大写字母返回

--initcap(列名或者字面量):将参数值中的每个单词

--的首字母转换成大写,其余字母转换成小写字母,然后返回

 

--如何调用函数?

--使用select语句

select lower('SQL Course'),upper('SQLCourse'),

       initcap('SQLCourse')

  from dual;

  

--dual表:它是一张系统表,属于sys用户。

--该表非常特殊,只有一行一列。主要用来完成select语句的语法。

--常用来调用函数、在plsql中给变量赋值等操作。

desc dual;

 

select * from dual;

 

--2)length(列名或者字面量):返回字符串的长度

 

select length('hello world')

  from dual;

  

--注意,该函数是计算字符的个数,不是求存储长度

select length('你好')

  from dual;

 

--3)substr:从源字符串中查找一个子串并返回

select substr('Hello World',3,3)

  from dual;

 

select substr('Hello World',3)

  from dual;  

 

--4)instr:从源字符串中查找一个子串,返回子串出现的位置

----查找第1个o出现的位置

select instr('Hello World','o',1)

  from dual;

 

--查找第二个o出现的位置

select instr('Hello World','o',1,2)

  from dual;

 

--5)lpad|rpad:在源字符串的左边或者右边添加特定数量的字符,

--返回填充后的结果

select lpad('Hello',10,'*'),

      rpad('Hello',10,'*')

  from dual;

  

select lpad('Hello',10,' ') rs1,

       rpad('Hello',10,'')  rs2

  from dual;  

 

--6)trim:去掉源字符串的首尾空格

select trim('  Hello  ') rs

   from dual;

 

--只去掉左边的空格   

select trim(leading ' ' from '  Hello ') rs

   from dual;   

 

--ltrim ,rtrim:只去掉左边或者右边的空格 

select ltrim('  Hello  ')rs1,rtrim('  Hello  ') rs2

   from dual;

 

--7)replace:在源字符串中查找一个子串,找到后用一个新的子串来替换它,

--返回替换后的结果

select replace('Jue and Jack','J','Bl')

  from dual;

 

--8)concat:等价于||操作符,将两个参数的值首尾相连后返回

select concat('Hello','World')

  from dual;

  

 

--根据雇员姓名进行查找,如果不确定雇员姓名的大小写,就可以在

--where条件中使用函数,将列值转换成全小写或者全大写字母后

--在做比较

select last_name,salary

  from employees

  where lower(last_name)='higgins';

  

2.数字函数:

--3个:

--round:四舍五入函数,将给定的数字四舍五入到指定的小数位

--trunc:截断函数,将给定的数字截断到指定的小数位

--mod:取余数函数,返回两个整数相除后的余数

 

--注意:小数位为负的表示从小数点的左边进行四舍五入 

select round(45.923,2),round(45.923,0),

      round(45.923,-1),round(45.923,-2)

   from dual;

   

--注意:小数位为负的表示从小数点的左边进行截断 

select trunc(45.923,2),trunc(45.923,0),

      trunc(45.923,-1),trunc(45.923,-2)

   from dual;

  

--注意:round和trunc函数还可以用来处理日期

 

--求一个数的除2余数,可以判断该数是否为偶数

select mod(15,2)

  from dual;

 

3.日期函数  

--oracle客户端工具都有默认的日期显示格式,一般都是DD-MON-RR格式,

--其含义是:年部分(RR)是2个数字,月部分(MON)是月名称的前三个字母缩写,

--日部分(DD)是2个数字。

 

--一般不要使用两位数字的年,而是使用4位数字,这样可以避免产生

--混乱(RR格式解释年有特殊的规则)。通常将日期格式模型改为

--YYYY-MM-DD,这样在任何客户端可以通用。

 

select last_name,hire_date

  from employees

  where hire_date='2007-05-21';

 

--sysdate函数:返回数据库当前日期和时间

select sysdate 

  from dual;

 

--错。

select sysdate()

  from dual;

  

--current_date函数:返回用户会话的当前日期和时间

select current_date 

  from dual;

  

--因为数据库把日期作为数字存储,因此可以对日期进行加减运算。

--给一个日期加几天

select sysdate + 2

  from dual;

  

--给一个日期减几天

select sysdate - 2

  from dual;  

 

--两个日期相减返回相差的天数

select last_name,sysdate - hire_date

  from employees;

 

--给日期加几个小时:date+几/24

select to_char(sysdate,'YYYY-MM-DDHH:MI:SS'),

        to_char(sysdate +1/24,'YYYY-MM-DD HH:MI:SS')

  from dual;

  

-- MONTHS_BETWEEN(date1, date2): 返回两个日期差几个月 

select months_between(sysdate,hire_date)

  from employees;

  

--ADD_MONTHS(date, n): 给日期加减几个月

selectadd_months(sysdate,3),add_months(sysdate,-3)

  from dual;

  

--NEXT_DAY(date, ‘char’):  找到从date开始的下一个星期几的日期。char表示星期几

select next_day(sysdate,'星期日')

  from dual;

  

--ROUND(date[,'fmt']): TRUNC(date[, 'fmt']): 将给定的日期

--按照月或者年进行四舍五入或者截断

 

--按月进行截断:不管给定的日期是否过了该月的一半,直接截断到

--本月的1号

--按年进行截断:不管给定的日期是否过了该年的一半,直接截断到

--本年的1月1号

select trunc(sysdate,'month'),

       trunc(sysdate,'year'),trunc(sysdate)

   from dual;

   

4.类型转换函数:

--1、to_char(date,'fmt_model'):将日期值按照

--特定的日期格式模型转换成字符串

--常见的日期格式元素:YYYY、MM、DD、RR、DAY、D、MON、MONTH等等

 

--将雇员的入职日期转换成特定的显示格式

select last_name,hire_date,

      to_char(hire_date,'DD MONTH YYYY')

  from employees;

 

--fm元素用于压缩前导的0  

select last_name,hire_date,

      to_char(hire_date,'fmDD MONTH YYYY')

  from employees;  

 

--返回的是日期所代表的天的名字

select last_name,hire_date,

      to_char(hire_date,'DAY')

  from employees;

 

--返回的是一个数字型的字符串,表示该日期是一周中的第几天 

select last_name,hire_date,

      to_char(hire_date,'D')

  from employees;  

 

--2、to_char(number,'fmt_model'):将数字按照特定的格式模型

--转换成字符串。常用的数字格式元素有:9、0、$,L,.,

 

select salary,to_char(salary,'$99,999.00')

    from employees;



select salary,to_char(salary,' 99999.00')

    from employees;

 

--注意,L的效果依赖于初始化参数NLS_DUAL_CURRENCY的设置    

select salary,to_char(salary,'L99,999.00')

    from employees;    

 

select * from nls_session_parameters;

 

--注意,格式宽度不够,转换后的结果会出现#号

select salary,to_char(salary,'9,999.00')

    from employees; 

 

--3、to_number(char[,fmt_model]):将字符串按照特定的数字

--格式模型转成数字

 

select to_number('$12,567.88','$99,999.99')

   from dual;

 

--注意,不写格式模型只能将纯数字形式的字符串转成数字  

select to_number('12567.88')

   from dual;   

 

--ORA-01722: 无效数字

select to_number('$12567.88')

   from dual;  

 

--4、to_date(char[,'fmt_model']):将字符串按照特定的日期

--格式模型转成一个日期

 

select to_date('2000 05 18','YYYY MM DD')

  from dual;

 

--错。值不满足默认的日期显示格式  

select to_date('18-05-2000')

  from dual;  

 

select to_date('18-05-2000','dd-mm-yyyy')

  from dual;

 

select to_date('18-2月-2000','dd-mon-yyyy')

  from dual; 

 

--注意:如果月份是字母表示的,那么即使格式模型和其匹配,

--如果月份值不满足默认值的日期显示格式,转换还是失败

--ORA-01843: 无效的月份

select to_date('18-feb-2000','dd-mon-yyyy')

  from dual;

  

--在where条件中,当比较日期时,通常使用to_date函数

--提供一个真正的date值来进行比较,避免自动类型转换的发生

 

--查询2005-6-25入职的雇员

select last_name,hire_date

  from employees

  where hire_date = to_date('2005-06-25','YYYY-MM-DD');

 

--不提倡这么写,因为有自动类型转换发生  

select last_name,hire_date

  from employees

  where hire_date = '2005-06-25';

 

--单行函数可以嵌套使用,嵌套的深度没有限制

--函数嵌套:把一个函数调用作为参数直接传递给另一个函数

 

select last_name,

      upper(concat(substr(last_name,1,8),'_US'))

  from employees;

5.通用函数

--使用通用函数

--这些函数的共同特点就是可以对null值做转换

-- nvl、nvl2、nullif、coalesce、decode、case

 

--1、nvl(列,expr):判断第一个参数的值是否为空,如果不为空

--就直接返回它,如果为空,就返回第二个参数

 

--计算雇员的年收入(年薪+佣金)

selectlast_name,commission_pct,nvl(commission_pct,0),

       (12*salary) +(12*salary*nvl(commission_pct,0)) ann_sal

  from employees;

  

--2/NVL2(expr1, expr2, expr3)

--NVL2函数解释第一个表达式。如果它的值不为null,

--函数返回第二个表达式。如果第一个表达式的值为null,

--函数返回第三个表达式。 

 

--查询雇员的姓名、薪水以及年收入构成

select last_name,salary,commission_pct,

      nvl2(commission_pct,'sal+comm','sal') income

   from employees;    

 

--3、NULLIF (expr1, expr2)

--NULLIF比较expr1 and expr2。

--如果它们相等则函数返回null。如果它们不等,

--函数返回expr1。但是,你不能指定expr1是一个字面量null。

 

select nullif('hello','hello')

  from dual;

 

--ORA-00932: 数据类型不一致: 应为 -, 但却获得 CHAR

select nullif(null,'hello')

  from dual;

 

--4、COALESCE (expr1, expr2, ...exprn)

--返回参数列表中第一个非null的表达式

--注意,所有的表达式必须是一样的类型

 

--查询雇员的姓名、编号,如果拿佣金,则输出佣金;如果不拿佣金,但是

--普通雇员,则输出其经理编号;如果既不拿佣金,又没有经理管辖,则

--输出一句话

select last_name,employee_id,

      coalesce(to_char(commission_pct),to_char(manager_id),'no comm and nomanager')

  from employees;

  

case:

--使用case表达式,在select语句中完成if  then else逻辑计算 

--有两种形式:

--1、simple case表达式

--语法:

--case 选择器(列)

     when 值1 then 返回值1

     when 值2 then 返回值2

     ....

     esle 默认值 

  end

--工作过程:把选择器的值和值1比较,如果相等,返回返回值1;

--如果不等,把选择器的值和值2比较,如果相等,返回返回值2;

--以此类推。如果和所有when子句中的值都不等,则返回默认值。

--所有的返回值必须是同样的数据类型。

--查询雇员的姓名、工作、薪水以及修订后的薪水

select last_name,job_id,salary,

       case job_id

         when'IT_PROG' then 1.1*salary

         when'ST_CLERK' then 1.15*salary

         when'SA_REP' then 1.2*salary

         else salaryend  "REVISED_SALARY"

   from employees;

   

   

--2、searched case表达式

--语法:

--case 

     when 布尔表达式1  then 返回值1

     when 布尔表达式2  then 返回值2

     ....

     esle 默认值 

  end

--工作过程:首先判断布尔表达式1,如果为true,则返回 返回值1;

--如果不为true,则判断布尔表达式2,如果为true,则返回 返回值2;

--以此类推。如果所有when子句中的条件都不为true,则返回默认值。

 

--使用searched case改写上例:

select last_name,job_id,salary,

       case 

         whenjob_id='IT_PROG' then 1.1*salary

         whenjob_id='ST_CLERK' then 1.15*salary

         whenjob_id='SA_REP' then 1.2*salary

         else salaryend  "REVISED_SALARY"

   from employees;

 

--decode函数完成和case表达式一样的功能。

--他是oracle特有的函数

 

--使用decode函数改写上例:

select last_name,job_id,salary,

       decode( job_id

             , 'IT_PROG' , 1.1*salary

             , 'ST_CLERK' , 1.15*salary

             , 'SA_REP' , 1.2*salary

             , salary)  "REVISED_SALARY"

   from employees;

 

使用组函数:

--组函数对每一组行进行操作,返回一个值。

--5个:

-- avg、max、min、count、sum

 

--计算所有雇员的平均、最大、最小薪水以及薪水总和

select avg(salary),max(salary),min(salary),

       sum(salary)

  from employees;

  

--注意:组函数是在分完组以后才开始计算的。上面的查询没有对结果集中的

--行明确分组,那么结果集中的所有行就自动形成一组,然后

--进行组函数的计算。

 

--可以对日期值以及字符串值求最大和最小

 

--查询雇员的最早和最晚的入职日期

select min(hire_date),max(hire_date)

  from employees;

  select min(last_name),max(last_name)

  from employees;  

--count函数:返回表中满足条件的行的数量 

--3种写法:

--1)、COUNT(*): 返回表中满足where条件的行的数量

--查询部门50的雇员数量

select count(*)

  from employees

  where department_id=50;

 

--查询所有雇员的数量

select count(*)

  from employees;

 

--2)COUNT(列名):返回列值非空的行的数量

--统计所有拿佣金的雇员的数量

select count(commission_pct)

  from employees;

 

--3)COUNT(DISTINCT 列名):返回列值非空的、并且列值不重复的行的数量

--统计所有雇员分布的部门的数量

select count(distinct department_id)

  from employees;

 

--组函数和null值的处理

--默认情况下,组函数会忽略列值为空的行

 

--统计公司的平均佣金,只有拿佣金的雇员参与计算

select avg(commission_pct)

  from employees;

  

 

--统计公司的平均佣金,要求所有的雇员参与计算

--解决办法:使用nvl函数对commission_pct做空值转换

select avg(nvl(commission_pct,0))

  from employees;  

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:149409次
    • 积分:4315
    • 等级:
    • 排名:第7265名
    • 原创:295篇
    • 转载:0篇
    • 译文:1篇
    • 评论:2条
    文章分类
    最新评论