--比较运算
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