函数总结

单行函数：

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

 

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;