oracle数据库操作---3 单行函数，嵌套函数及条件表达式

单行函数，嵌套函数及条件表达式

单行函数

单行函数:
操作数据对象
接受参数返回一个结果
只对一行进行变换
每行返回一个结果
可以转换数据类型
可以嵌套
参数可以是一列或一个值

1. 字符函数

   1. 大小写控制函数（用到的关键字，lower，upper，initcap）

      --将字符串转换为小写  
      select LOWER('ORACLE SQL') from dual;
      
      --将字符串转换为大写
      select UPPER('oracle sql') from  dual;
      
      --将每个单词的首字母大写
      select initcap('oracle  sql') from  dual;
      

      此处需要注意的是dual是一个虚拟表（又称伪表），用于测试sql语句中简单的数据运算或者简单的语句能否执行，除了这个虚拟表之外还有一些其他的虚拟列等之后的学习也会遇到

      --查询lastname 为De Haan 的雇员信息
      select * from employees where last_name= initcap('de haan');
      

   2. 字符控制函数
      

      • concat 拼接字符串
      • substr 分割字符串
      • length 字符串长度计算
      • instr 找出特定字符或者字符串（首字母位置）在字符串中的位置并返回
      • lpad 在字符串左端以设定符号自动补齐未满位数
      • rpad 在字符串右端以设定符号自动补齐未满位数
      • trim 去除首位或者末尾的第一个字符，通常用来去除开头或结尾的空格
      • replace 以一指定的字符替换字符串中的指定的另一字符

      select concat('hello' ,'world') from dual;
      --substr  arg1:要截取字符串 arg2：截取的开始位置  arg3：截取的长度
      select substr('helloworld',2,5) from dual;
      
      --查找某一个字符或字符串在字符串中出现的位置  如果是一个字符串 则是首字母的位置
      --如果查找的字符 或字符串不存在 则返回0
      select instr('helloworld','wor') from dual;
      
      --LPAD  RPAD 判断字符串的长度是=是否满足给定的长度 如果不足 则在左边或右边使用
      --给定的字符来补充
      SELECT LPAD('helloworld',15,'*') from dual;
      SELECT RPAD('helloworld',15,'*') from dual;
      
      --TRIM 表示去除字符串两端的空格
      select length('helloworld   ')from dual;
      select length(Trim('helloworld   ')) from dual;
      
      --REPLACE 替换
      select replace('hello world','l','L')from dual;
      

2. 数字函数
   ROUND: 四舍五入
   如 ROUND(45.926, 2) 45.93
   TRUNC: 截断
   TRUNC(45.926, 2) 45.92
   MOD: 求余
   MOD(1600, 300) 100

   --ROUND括号中的0,2，-2是根据小数点来判断保留几位，正数向右保留，负数向左保留
   SELECT ROUND(45.9362,0),ROUND(45.9362,2),ROUND(145.9362,-2) FROM dual;
   --TRUNC与上相同
   SELECT TRUNC(45.9362,0),TRUNC(45.9362,2),TRUNC(145.9362,-2) FROM dual
   --MOD
   SELECT MOD(1560,200) FROM dual;
   

   
   注意round和trunc括号中的0,2，-2是根据小数点来判断保留几位，正数向右保留，负数向左保留，当然也可以省略不写，代表为0，即取整。

3. 日期相关函数

   -- 查看系统当前日期
   select sysdate from dual;
   

   Oracle 中的日期型数据实际含有两个值: 日期和时间。
   比如说上述代码返回了2019/12/19 21:05:59
   日期的数学运算
   在日期上加上或减去一个数字结果仍为日期。
   两个日期相减返回日期之间相差的天数。
   日期不允许做加法运算，无意义
   可以用数字除24来向日期中加上或减去天数。

   --查询employees表中每个员工入职的工龄（年）
   select first_name,last_name, round((sysdate-hire_date)/365) from employees;
   

   日期中所涉及的一些函数
   

   • MONTHS_BETWEEN (‘01-SEP-95’,‘11-JAN-94’) 19.6774194

   • ADD_MONTHS (‘11-JAN-94’,6) ‘11-JUL-94’

   • NEXT_DAY (‘01-SEP-95’,‘FRIDAY’) ‘08-SEP-95’

   • LAST_DAY(‘01-FEB-95’) ‘28-FEB-95’

   • 假定： SYSDATE = ‘25-JUL-95’:

   • ROUND(SYSDATE,‘MONTH’) 01-AUG-95

   • ROUND(SYSDATE ,‘YEAR’) 01-JAN-96

   • TRUNC(SYSDATE ,‘MONTH’) 01-JUL-95

   • TRUNC(SYSDATE ,‘YEAR’) 01-JAN-95

     -- 查询employees表中每个员工入职的月数
     select first_name,last_name,months_between(sysdate,hire_date) from employees;
     --add_months
     select add_months(sysdate,3)from dual;
     
     --next_day 下一个星期几（右第二个参数指定）的日期 
     select next_day(sysdate,'FRIDAY') from dual;
     
     --当前系统时间的当前月份的最后一天
     select last_day(sysdate) from dual;
     

4. 转换函数
   分为隐性转换和显性转换两种

   1. 隐性转换
      Oracle 自动完成下列转换：
   2. 显式数据类型转换
      
      一般常用的是
      日期时间与字符串 以及字符串与数字之间的相互转换

      • TO_CHAR函数对日期的转换
        必须包含在单引号中而且大小写敏感。
        可以包含任意的有效的日期格式。
        日期之间用逗号隔开。
        日期格式的元素
        
        时间格式
        
        使用双引号向日期中添加字符
        

        TO_CHAR(date, 'format_model')
        

        12/24小时制

        --12小时制
        select to_char(sysdate,'yyyy-mm-dd hh:mi:ss')from dual;
        --24小时制
        select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;
        

        一些实际应用

        --查询入职时间为1994年之前的员工  将条件中的日期转换为date在和hiredate进行比较
        select first_name,last_name,hire_date from employees where hire_date < to_date('2004-01-01','yyyy-mm:dd');
        -- 将hire_date 转换为字符串 之后 在和'2004-01-01'
        select first_name,last_name,hire_date from employees where to_char(hire_date,'yyyy-mm-dd') < '2004-01-01';
        

        两种特殊方式

        --特殊格式
        select  to_char(hire_date,'yyyy-MON-dd') from employees;
        --特殊格式
        select  to_char(hire_date,'yyyy-MONTH-dd') from employees;
        

        
        

      • TO_DATE 函数对字符的转换
        

      • TO_CHAR函数对数字的转换
        
        注意对于货币只能使用$ 及L 其他符号不能使用

        --查询所有雇员的薪资  并以$符开头  整数部分三位使用千位符进行分隔  小数部分使用00表示
        select first_name,last_name,to_char(salary,'L999,999,999,999,999.00')from employees;
        

      • TO_NUMBER 函数对字符的转换

        select to_number('$123456789123.12','L999999999999999999.00')from dual;
        select to_number('$123456789123.12','$999999999999999999.00')from dual;
        select to_number('123456789123.12','999999999999999999.00')from dual;
        select to_number('123456789123.12')from dual;
        

5. 通用函数
   将空值转换成一个已知的值
   这些函数适用于任何数据类型，同时也适用于空值：
   NVL (expr1, expr2)
   NVL2 (expr1, expr2, expr3)
   NULLIF (expr1, expr2)
   COALESCE (expr1, expr2, …, exprn)

   • NVL(expr1, expr2)（如果查询到expr1为空，返回expr2）

     --如果在表中查询到commission_pct为空，则显示0
     SELECT last_name, salary, NVL(commission_pct, 0),
        (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
     FROM employees;
     

   • NVL2 (expr1, expr2, expr3)
     –nvl2 第一个参数为null 不论第二个参数是否为null 都取第三个数为结果
     – 第一个参数不为null 结果为第二个参数的值
     – 前边两个都为null 则取第三个参数的值
     NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。

     select nvl2(null,null,100)from dual;
     

   • NULLIF (expr1, expr2)（相等返回NULL，不等返回expr1）

     --NULLIF (expr1, expr2) :  相等返回NULL，不等返回expr1
     --判断雇员的first_name和last_name的长度 如果长度相同则返回null  
     --不同则返回first——name的长度
     select length(first_name) rexp1,length(last_name) rexp2 ,nullif(length(first_name),length(last_name)) from employees;
     

   • COALESCE (expr1, expr2, …, exprn)（ 返回第一个非空的值 如果三个或者以上都为空 则返回null）

     -- COALESCE 返回第一个非空的值   如果三个都为空  则返回null
     select COALESCE(null,null,null) from dual;
     

嵌套函数

单行函数可以嵌套。
嵌套函数的执行顺序是由内到外。

条件表达式

在 SQL 语句中使用IF-THEN-ELSE 逻辑
使用两种方法:
CASE 表达式
DECODE 函数

• CASE 表达式

  --语法
  CASE expr WHEN comparison_expr1 THEN return_expr1
           [WHEN comparison_expr2 THEN return_expr2
            WHEN comparison_exprn THEN return_exprn
            ELSE else_expr]
  END
  --------------------------------
  /*练习：查询部门号为 10, 20, 30 的员工信息, 
  若部门号为 10, 则打印其工资的 1.1 倍, 
  20 号部门, 则打印其工资的 1.2 倍, 
  30 号部门打印其工资的 1.3 倍数*/
  select first_name,last_name,salary,department_id,
    case department_id when 10 then salary * 1.1
                       when 20 then salary * 1.2
                       when 30 then salary * 1.3
                       else salary 
    end
  from employees
  where department_id in(10,20,30); 
  
  

• DECODE 函数

  --语法
  Select decode（columnname，值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值）
  From talbename
  Where 
  

  其中：columnname为要选择的table中所定义的column；
  　　 缺省值可以是你要选择的column name本身，也可以是你想定义的其他值，比如Other等；
  主要作用：相当于IF语句， 将查询结果翻译成其他值。（即以其他形式表现出来）。
  在需要使用 IF-THEN-ELSE 逻辑时:

  --查询所有的职位  将职位名称全部翻译成中文
  	select * from jobs;
  	select job_id,job_title,
  	       decode(job_title,'President','pre',
  	                        'Finance Manager','财务总监',
  	                        'Sales Manager','销售总监',
  	                        'Programmer' ,'程序员',
  	                        'other'
  	               )
  	from  jobs;
  	--------------------------------------
  	/*
  	根据雇员薪资 计算该雇员的个人所得税的税率，个人所得税的起征点为2000，
  	2000-3000 税率为0.09
  	3000-5000 税率为0.20
  	5000-7000 税率为0.30
  	7000-9000 税率为0.40
  	9000-12000 税率为0.42
  	12000-14000 税率为0.44
  	1400以上税率为0.45
  	*/
  	select first_name,last_name,salary,
  	       
  	       decode(trunc(salary/2000,0),	1,0.09,
  	       								2,0.20,
  	       								3,0.30,
  	       								4,0.40,
  	       								5,0.42,
  	      								6,0.44,
  	       								0.45) tax_rate
  	from employees;
  	--------------------------------------------
  	--查询雇员中没有领导的雇员信息，
  	--如果该雇员没有经理，
  	--则将manager_id 修改为'No Manager'
  	--在此处  manager_id为number类型 因此 在使用nvl函数时 必须先转换为字符串
  	
  	select first_name,last_name,manager_id+100,
  	nvl(to_char(manager_id),'no manager')||100--隐式转换 将此处的100转换为字符串
  	from employees;