（尚桂谷） ORCL大小写的控制函数 ，字符控制函数，数字函数，月期函数

--比较运算
select last_name , salary
from employees
where salary <= 3000; 

--between
select last_name , salary
from employees 
where salary between 2500 and 3500; 

--in
select employee_id , last_name , salary , manager_id
from employees
where manager_id in (100 , 101 , 201);

--like
select first_name
from employees
where first_name like 'K%';

select last_name
from employees
where last_name like '_o%'

select job_id
from jobs
where job_id like 'IT\_%' escape '\';

select last_name , manager_id
from employees
where manager_id is null;

select employee_id , last_name , job_id , salary
from employees
where salary >= 10
and last_name like '%M%';

select employee_id , last_name , job_id , salary
from employees
where salary >= 1000
or last_name like '%M%'

select last_name , job_id 
from employees
where job_id 
      not in ('IT_PROG' , 'ST_CLERK' , 'SA_REP');

/*
 * ORCL大小写的控制函数
 * lower('SQL Ccourse') sql course
 * upper('SQL Course') SQL COURSE
 * initcap('SQL Course') Sql Course
 *
 * select employee_id , last_name , department_id
 * from employees
 * where last_name = 'higgins'
 * --where lower(last_name) = 'higgins'
 */

/*
 * 字符控制函数
 * concat('Hello' , 'World') HelloWorld
 * substr('HelloWorld',1,5) Hello
 * length('HelloWorld') 10
 * instr('HelloWorld' , 'W' ) 6
 * lpad (salary , 10 , '*') *****24000
 * rpad (salary , 10 , '*') 24000*****
 * trim ('H' from 'HellowWorld') elloWorld
 * replace ( 'abcd' , 'b' , 'm' ) amcd
 */

数字函数
< round 四舍五入  round(45.926)  45.93
< trunc 截断           trunc(45.926)   45.92
< mod 求余               mod(1600 , 3000) 100


select concat ('Hello' , 'World')
from dual


select substr('HelloWorld' , 1,5)
from dual


select length ('HelloWorld')
from dual


select instr ('HelloWorld' , 'W')
from dual


select lpad (salary , 10 , '*')
from employees


select rpad(salary , 10 , '&')
from employees


select trim ('H' from 'HelloWorld')
from dual


select replace('abcd' , 'b' , 'm')
from dual


select round(45.923 , 2) , round(45.923 , 0) , round (45.923 , -1)
from dual


select trunc(45.923 , 2) , trunc(45.923) , trunc(45.923 , -2)
from dual


select last_name , salary , mod(salary , 3)
from employees
where job_id = 'SA_REP'

月期函数
months_betueen      两个日期相差的月份
add_months              向指定日期加上若干的月
next_day                   向指定日期下一个星期的对应日期
last_day                    本月的最后一天
round                       日期四舍五入
trunc                         日期截断
------------------ 
日期
select last_name , hire_date 
from employees
where last_name like 'G%'

日期的数学运算
select last_name ,(sysdate-hire_date)/7 as weeks
from employees
where department_id = 90

months_between ('01-SEP-95' , '11-JAN-94') 

ADD_MONTHS('11_JAN_94' , 6)

next_day('01-sep-95' , 'friday')

last_day('011-FEB_95')

日期函数
Assume sysdate = '25-jul-95'

select round(sysdate , 'month' )
from dual 
--14-10-1

select round (sysdate , 'year')
from dual 
--15-1-1

select trunc(sysdate , 'month')
from dual 
--14-10-1

select trunc(sysdate , 'year')
from dual; 
--14-1-1

---TO_CHR函数对日期的转换
select to_char (sysdate , 'yyyy-mm-dd hh:mmi:ss')
from dual 
--2014-10-10 09:104:04

/*
必须包含在单引号中而且大小写敏感。
可以包含任意的有效的日期格式。
日期之间用逗号隔开。
*/

select employee_id , last_name , to_char(hire_date , 'DD-mm-yyyy') as datee
from employees
/*
TO_DATE 函数对字符的转换
to_date(char [, 'format_model'])
*/

select to_date('2012年10月29日 08:10:21' , 'yyyy"年"mm"月"dd"日"hh:mi:ss')
from dual 

select to_date('2012年07月30日 08:23:90 ' , 'yyyy"年"mm"月"dd"日" hh:mi:ss' )
from dual 

select employee_id , last_name , hire_date 
from employees
where to_char (hire_date , 'yyyy-mm-dd') = '1987-09-17'

select to_date('2012YEAR10月29日08:10:21' , 'yyyy"YEAR"mm"月"dd"日"hh:mi:ss')
from dual 
--12-10-29 8:10:21

--TO_CHAR函数的字符的转换

select to_char(salary , '$99,999.00')salaRy
from employees
where lower(last_name) ='ernst'

select to_number('￥1,234,567,890.00' , 'L999,999,999,999.99')+10
from dual

select to_number('￥1,234,567,890.00' , 'L999,999,999,999.99')+90 as salay
from dual 

select last_name , salary , nvl(commission_pct , 0) ss , 
(salary+12) + (salary * 12 * nvl(commission_pct , 0)) an_sal
from employees

select last_name , nvl(commission_pct , 0) ss , 
(salary+12) + (salary * 12 * nvl (commission_pct , 0))an_avl
from employees
<img src="https://img-blog.csdn.net/20141011221404804?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMjY1MTM4OQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast" width="278" height="200" alt="" />
--nvl2函数
select last_name , salary , commission_pct , nvl2(commission_pct , 'SAL+CCMM' , 'SAL') income 
from employees
where department_id in (50 , 80) 
--NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。


--使用 NULLIF 函数
select first_name , length(FIRST_name ) "expr1" , 
       last_name , length(last_name ) "expr2" , 
       nullif(length(first_name ), length(last_name )) result
from employees

--
select last_name , commission_pct , salary ,coalesce(commission_pct , salary , 10) comm
from employees
order by commission_pct

select last_name , job_id , salary ,
    case job_id when'IT_PROG' then 1.10*salary
                when 'ST_CLERK' then 1.15*salary
                when 'SA_REP' then 1.20*salary
    else salary 
    end "revised_salry"
from employees

select last_name , job_id , salary , 
          case job_id when 'IT_PROG' then 1.10*salary
                      when 'ST_CLERK'then 1.15*salary
                      when 'SA_REP' then 1.20*salary
          else salary
          end "hello Orcl"
from employees
                
--DECODE 函数
select last_name , job_id , salary , 
        decode (job_id , 'IT_PROG' , 1.10*salary , 
                         'ST_CLERK' , 1.15*salary , 
                         'SA_REP' , 1.20*salary ,
                         salary 
                    )hello
from employees
---s 
select last_name , salary , 
       decode(trunc(salary/200 , 0) , 
                0,00.00,
                1,0.009,
                2,0.20,
                3,0.30,
                4,0.40,
                5,0.42,
                6,0.44,
                  0.45 
                )tax_rate
from employees
where department_id = 80;

--嵌套函数???
select last_name , nvl( to_char(manager_id) , 'no manager' )
from employees
where manager_id is null