oracle总结

1.oracle单行函数

单行函数:对单个行进行运算，并且对每个行返回一个结果。主要介绍字符，数字，日期和转换函数

多行函数:能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数



---dual表
dual表用的所有者为sys用户,并且可以被所有用户访问，用来返回常数值或者不来自用户数据表的表达式

SQL> select 1+2 sums from dual;

---字符函数


upper()/lower()


SQL> select upper(lower(ename)) from emp;


SQL> select upper('yangry') names from dual;

initcap(c):将字符串中单词的第一个字母大写，其他小写


SQL> select initcap('yANGRY') names from dual;


concat(c1,c2):连接字符串，等价于||

select concat(concat(last_name,' '),first_name ) emp_name from employees;

select last_name || ' ' || first_name emp_name  from employees;



substr(c1,n1[,n2])截取指定长度的字符串
c1表示字符串
n1为从那个位置开始截取
n2为截取长度

select substr('nconverge',3) substing from dual;
select substr('nconverge',3,4) substring from dual;


length(c):返回指定字符串的长度


select length(phone_number) len_phone from employees;

select * from employees where length(phone_number)>=18;



replace(c1,c2[,c3]):将c1字符串中的c2替换为c3,如果c3为null,则从c1中删除所有c2

select replace(first_name,'teven','lack') from employees where first_name='Steven';

select replace(first_name,'teven') from employees where first_name='Steven';



instr(c1,c2[,n1[,n2]]):返回c2在c1中的位置,c1为原字符串，c2为要查找的字符串，n1为查询起始位置，n2为第几个匹配项


select instr('corporate  floor','or') instr_str from dual;

select instr('corporate floor','or',3,2) instr_str  from dual;
14


lpad(c1,n[,c2]):返回指定长度等于n的字符串　　(rpad(c1,n[,c2]))

select lpad('what is this',5)　lpad_str from dual;


select lpad('what is this',5),lpad('what is this',25),lpad('what is this',25,'-')　lpad_str from dual;

n>c1 and c2 is null,以空格从左向右补充字符长度至n并返回
n>c1 and c2 is not null,以指定字符c2从左向右补充字符长度至n并返回



trim([[leading|trailing|both] trim_character from ] trim_source)

leading:出除头部的trim_character字符
trailing:出除尾部的trim_character字符
both：出除头尾部的trim_character字符
trim(trim_source):没有指定任何参数，出处trim_source头尾空格

select trim('  jone come from henan   ') trim_str from dual;

select trim(leading  'j' from 'jone come from henan') lead_str,
　　　　trim(trailing  'n' from 'jone come from henan') trail_str,
　　　　trim(both 'n' from 'nike come from henan') both_str
from dual;



--－数字函数

round(n1[,n2]):四舍五入函数,n2缺省为0
trunc():截断函数
mod():求余函数


select round(23.56),round(23.56,1),
round(23.56,-1),round(25.56,-1) from dual;



trunc(n1[,n2])  n2缺省值为0
select trunc(23.56),trunc(23.56,1),trunc(23.56,-1) from dual;


mod(n1,n2)
select mod(8,3) from dual;


---时间函数


select sysdate from dual;

select current_date from dual;

add_months(date,integer):用于返回date之后的interger个月所对应的日期时间

select add_months(hire_date,-1),add_months(hire_date,1) netx_month,hire_date ,last_name
from employees
where last_name ='Baer';

last_day(date)：返回月份的最后一天

next_day(date,n)：n为下周的某一天

select last_day(sysdate),sysdate from dual;

select next_day(sysdate,3) from dual;


yyyy-mm-dd hh24:mi:ss am(上午)/pm(下午)


---转换函数
to_char(date,fmt)用于将日期值转换为字符串,fmt用于指定日期格式
select to_char(hire_date,'yyyy-mm-dd hh24:mi:ss'),last_name from employees；

select last_name,hire_date from employees
where to_char(hire_date,'yyyy')=2005;


to_date(char,fmt)用于将符合日期格式的字符串转换为date类型的值

select  last_name ,hire_date from employees
where hire_date > to_date('2005-09-21','yyyy-mm-dd');


---通用函数

---mysql ifnull()

nvl(expr1,expr2):如果expr1为null，则返回expr2,否则返回expr1

select last_name,nvl(to_char(commission_pct),'ccc') pct
from employees where last_name like 'B%';


nvl2(expr1,expr2,expr3):如果expr1为null，返回expr3,如果expr1不为null，则返回expr2

select last_name,salary,nvl(commission_pct,0) pct,nvl2(commission_pct,salary + salary*commission_pct,salary) sum_salary
from employees
 where last_name like 'B%';

simple case:
case expr when comparision_expr then return_expr end


select last_name,job_id,salary,
case job_id when 'IT_PROG' then salary * 1.5
when 'ST_CLERK' then salary * 1.4
when 'ST_MAN' then salary * 1.1
else salary
end  rasie_salary
from employees where job_id in ('IT_PROG','ST_CLERK','ST_MAN','AC_ACCOUNT') ;


search case:
case when condition then return_expr end


select last_name,salary,
case when salary>4000 then salary
else 4000
end avg_salary
from employees ;


decode(expr,s1,r1,s2,r2...,default_value):expr作为初始参数,s1作为比对值,相同则返回r1

select last_name,job_id,salary,
decode(job_id,'IT_PROG',salary * 1.5,'ST_CLERK',
salary * 1.4,'ST_MAN',salary * 1.1, salary) avg_salary
from employees
where job_id in ('IT_PROG','ST_CLERK','ST_MAN','AC_ACCOUNT');


2.多表查询

---查询员工对应的部门名称
select a.last_name ||' ' ||a.first_name emp_name,b.department_name
from employees a
join departments b on a.department_id = b.department_id;


连接类型:等值连接，外连接(左外连接，右外连接)，自连接

left join
right join

---left join：将employees表中不满足连接条件的大的行数据全部查询出来
select a.last_name ||' ' ||a.first_name emp_name,b.department_name
from employees a
left join departments b on a.department_id = b.department_id;


select a.last_name ||' ' ||a.first_name emp_name,b.department_name
from employees a ,departments b where a.department_id = b.department_id(+);



---right join：将departments表中不满足连接条件的大的行数据全部查询出来
select a.last_name ||' ' ||a.first_name emp_name,b.department_name
from employees a
right join departments b on a.department_id = b.department_id;


select a.last_name ||' ' ||a.first_name emp_name,b.department_name
from employees a , departments b
where a.department_id(+) = b.department_id;

---self join：连接

select a.last_name || ' ' || a.first_name emp_name,
b.last_name || ' ' || b.first_name manager_name
from employees a
join employees b on a.manager_id = b.employee_id;




---合并查询


select１
union|union all|minus|intersect
select2
union|union all|minus|intersect
select3


union用于获取两个结果集的并集。当使用该操作符时，会自动出掉结果集中的重复行

#union all
select ename,sal,job from emp where sal>2500
union all
select ename,sal,job from emp where job='MANAGER';

#union

select ename,sal,job from emp where sal>2500
union
select ename,sal,job from emp where job='MANAGER';

minus用于获取两个结果集的差集，当使用该操作符时，只会显示在第一个结果集中存在，在第二个结果集中不存在的数据。

#minus
select ename,sal,job from emp where sal>2500
minus
select ename,sal,job from emp where job='MANAGER';


intersect用于获取两个结果集的交集，当使用该操作符时，只会显示同时存在于两个结果集中的数据。
#intersect
select ename,sal,job from emp where sal>2500
intersect
select ename,sal,job from emp where job='MANAGER';



---多表查询，不小于３张表
查询员工对应的部门名和城市名
select a.last_name || ' ' || a.first_name emp_name,
b.department_name,
c.city
from EMPLOYEES a
join DEPARTMENTS b on a.department_id=b.department_id
join locations c on c.location_id = b.location_id;


select a.last_name || ' ' || a.first_name emp_name,
b.department_name,
c.city
from EMPLOYEES a, DEPARTMENTS b ,locations c
where a.department_id=b.department_id and  c.location_id = b.location_id;














组函数／子查询／sqlplus