Oracle常用代码总结(2)

java开发中的总结 大家可以看看哦

ngth(fast_name),instr(job_id,'员'),substr(job_id,1,3),trim('r' from fast_name)from guyuan where department_id = 30 order by salary desc（concat,substr,instr,length,lpad,trim函数的综合应用）
select round(4545.1481854156,5),round(4545.1481854156,3),round(4545.1481854156,1),round(4545.1481854156,0),round(4545.1481854156,-3) from dual;（round四舍五入函数）
select trunc(54.26846416,5),trunc(54.26846416,2),trunc(54.26846416,0),trunc(54.26846416,-2),trunc(54.26846416,-4) from dual;(截取字串函数)


多表查询：
select e.employee_id,e.fast_name,d.department_id,department_name from guyuan e,bumen d where e.department_id = d.department_id;（等值连接）
select e.employee_id,e.fast_name,d.department_id,e.department_id,d.department_name from guyuan e,bumen d where e.department_id = d.department_id and fast_name = 'Carry';（多表+条件连接）select e.employee_id,e.fast_name,e.job_id,l.city from guyuan e,bumen d,location l where e.department_id = d.department_id and d.location_id = l.location_id;（多表等值连接）
select * from sal_grades;（查询）
select e.fast_name,e.job_id,e.salary,s.grade from guyuan e,sal_grades s where e.salary between s.lowest_sal and s.highest_sal;（多表连接）
select e.fast_name,e.department_id,d.department_id,d.department_name from guyuan e,bumen d where e.department_id (+)=d.department_id;(左外连接)
select distinct e.employee_id,e.job_id,e.manager_id,m.manager_id from guyuan e,guyuan m where e.employee_id = m.manager_id;（自连接）
select e.employee_id,m.fast_name||' works for '||e.fast_name from guyuan e,guyuan m where e.employee_id = m.manager_id;（自连接）
select * from guyuan cross join bumen;（叉集）
select department_id,department_name,city from bumen natural join location;(自然连接);
select e.employee_id,e.fast_name,d.location_id from guyuan e join bumen d using (department_id);(using字句)
select e.employee_id,e.fast_name,e.department_id,d.department_id from guyuan e join bumen d on (e.department_id = d.department_id);（join on等值连接）
select e.employee_id,e.fast_name,e.job_id,e.department_id,d.location_id,l.city from guyuan e join bumen d on e.department_id = d.department_id join location l on d.location_id = l.location_id;（join on多表连接）
select e.employee_id,e.fast_name,e.job_id,e.manager_id,e.department_id from guyuan e left outer join bumen d on (e.department_id = d.department_id) order by employee_id;（左外连接）
select e.employee_id,e.fast_name,e.department_id,d.department_name from guyuan e right outer join bumen d on (e.department_id = d.department_id);（右外连接）
select e.fast_name,e.employee_id,e.department_id,d.department_name from guyuan e full outer join bumen d on (e.department_id = d.department_id); （全外连接或满外连接）
select e.emp
loyee_id,e.fast_name,e.department_id,d.department_name from guyuan e join bumen d on (e.department_id = d.department_id) and e.manager_id = 1002