orcal 中sql字符函数,数字函数,日期函数，转换函数学习,CASE和DECODE表达式学习

字符函数：

大小写字符控制函数：LOWER,UPPER,INITCAP

LOWER:将字符转换成小写

UPPER:将字符转换成大写

INITCAP：将单词首字母转换成大写，其他转换成小写字母

例如： select lower('ASDFAASDF'),upper('asfdasasdf'),initcap('aSDF dasdf Asdf') 

      from dual;
结果：

LOWER('ASDFAASDF') UPPER('ASFDASASDF') INITCAP('ASDFDASDFASDF')
------------------ ------------------- ------------------------
asdfaasdf          ASFDASASDF          Asdf Dasdf Asdf

字符控制函数：CONCAT,SUBSTR,LENGTH,LPAD,RPAD,INSTR,TRIM,REPLACE

CONCAT:连接字符串函数

SUBSTR:截取字符串得到子字符串，[substr(参数1，参数2，参数3) ，参数2表示截取字符串开始的位置，参数3表示截取字符串的长度]

LENGTH；统计字符串中字符的个数

LPAD:左填充  LPAD(参数1，参数2，参数3)  参数1：字段标示符，参数2：待输出参数1的长度，参数3：如果查询结果输出的字符长度不够指定参数2长度，则用参数3指定的字符左填充

RPAD:右填充

INSTR:查找某个字符在字符串中的位置，查找的结果为字符在字符串中首次出现的位置

TRIM:移除字符串中首尾出现的某个字符

REPLACE:用某个字符替换字符串中某个出现的所有的字符

例如：1：select concat('hello','world'),substr('helloworld',2,4) ,length('HelloWorld')

 from dual；
 结果：

CONCAT('HELLO','WORLD') SUBSTR('HELLOWORLD',2,4) LENGTH('HELLOWORLD')
----------------------- ------------------------ --------------------
helloworld              ello                                       10

      2：select ename,lpad(salary,10,'*'),rpad(salary,10,'*') 
     from emp；

结果：

ENAME      LPAD(SALARY,10,'*')     RPAD(SALARY,10,'*')
---------- -------------------- --------------------
SMITH      *******800           800*******
ALLEN      ******1600           1600******
WARD       ******1250           1250******

3：select instr('HELLOWORLD','L') from dual

结果：

INSTR('HELLOWORLD','L')
-----------------------
                      3

4;select trim('h' from 'hellohworldhh') from dual

结果：

TRIM('H'FROM'HELLOHWORLDHH')
----------------------------
ellohworld

5：select replace('hellohworldhh','l','M') from dual

结果：

REPLACE('HELLOHWORLDHH','L','M）
------------------------------
heMMohworMdhh

数字函数：ROUND,TRUNC,MOD

ROUND:四十五入      round(435,3523,1)   ------>       结果：435.4

TRUNC:截取    trunc(435.3523,1) -------> 结果：435.3

MOD：取余     mod(435.3523,5) ---------->结果; 0.3523

日期函数：MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY

MONTHS_BETWEEN:计算两个日期之间的月份

ADD_MONTHS:在某个日期上增加月份

NEXT_DAY:指定日期的下一个星期对应的日期

LAST_DAY:本月的最后一天

例如：1：select trunc(months_between(sysdate,hiredate)) months from emp;

结果：  

  MONTHS
----------
       411
       409
       409

2：select sysdate,add_months(sysdate,2),add_months(sysdate,-2) from dual;
结果：

SYSDATE     ADD_MONTHS(SYSDATE,2) ADD_MONTHS(SYSDATE,-2)
----------- --------------------- ----------------------
2015/4/3 9: 2015/6/3 9:21:03      2015/2/3 9:21:03

 3：select sysdate,next_day(sysdate,'星期一') from dual;

结果;
SYSDATE     NEXT_DAY(SYSDATE,'星期一')
----------- --------------------------
2015/4/3 9: 2015/4/6 9:23:19

4：select sysdate,last_day(sysdate) from dual

结果：

SYSDATE     LAST_DAY(SYSDATE)
----------- -----------------
2015/4/3 9: 2015/4/30 9:30:38

显示转换函数：TO_CHAR,TO_NUMBER,TO_DATE

例如：

1：（1）select ename,hiredate from emp
where to_char(hiredate,'YYYY-MM-DD') = '1981-09-08'

结果：

ENAME      HIREDATE
---------- -----------
TURNER     1981/9/8

 （2）select to_char(hiredate,'YYYY"年"MM"月"DD"日"') from emp
where to_char(hiredate,'YYYY"年"MM"月"DD"日"') = '1981年09月08日'

结果：

TO_CHAR(HIREDATE,'YYYY"年"MM"?
------------------------------
1981年09月08日

 (3)select to_char(12312345.45,'L999,999,999.99') as to_char from dual

结果;

TO_CHAR
-------------------------
          ￥12,312,345.45

2:select to_number('￥12,312,345.45','L999,999,999.99') +100 from dual

结果;

 TO_NUMBER('￥12,312,345.45','L
------------------------------
                   12312445.45

3:select to_date('1994-9-8','YYYY-MM-DD') from dual  

结果;TO_DATE('1994-9-8','YYYY-MM-DD
------------------------------
1994/9/8

 通用函数：NVL,NULLIF

NVL(参数1，参数2)：如果参数1为null，则用参数2代替参数1

例如：

--求员工年薪   comm  定义可以为null
select ename,sal*12*(1+comm) from emp

结果：

ENAME      SAL*12*(1+COMM)
---------- ---------------
SMITH      
ALLEN              5779200
WARD               7515000
JONES      
MARTIN            21015000

--- select ename,sal*12*(1+nvl(comm,0)) from emp

结果;

ENAME      SAL*12*(1+NVL(COMM,0))
---------- ----------------------
SMITH                        9600
ALLEN                     5779200
WARD                      7515000
JONES                       35700
MARTIN                   21015000

--查询员工绩效奖金，如果有则输出，没有则输出没有绩效奖金

select ename,nvl(to_char(comm,'999999.99'),'没有绩效奖金')from emp

结果;

ENAME     NVL(TO_CHAR(COMM,'999999.99'),

---------- ------------------------------

SMITH      没有绩效奖金

ALLEN          300.00

WARD           500.00

JONES      没有绩效奖金

MARTIN        1400.00

BLAKE      没有绩效奖金

CLARK      没有绩效奖金

 

CASE表达式和DECODE表达式，这两个表达式用途相同，只是使用形式不一样

--不同部门工资奖励情况不同，10部门1.1倍，20部门1.2倍，30部门1.3倍

select ename,deptno,sal,

case deptno when10 then sal*1.1

            when 20then sal*1.2

            when 30then sal*1.3

            else sal end asnew_sal

from emp

 

select ename,deptno,sal,

decode( deptno ,10, sal*1.1,

                20 , sal*1.2,

                30 , sal*1.3,

             sal) as new_sal

from emp

 

上面两个语句所得到的结果相同：

ENAME      DEPTNO       SAL   NEW_SAL

---------- ------ --------- ----------

SMITH          20    800.00        960

ALLEN          30   1600.00      2080

WARD           30   1250.00      1625

JONES          20   2975.00      3570

MARTIN         30   1250.00      16ed5

BLAKE          30   2850.00      3705

CLARK          10   2450.00      2695