oracle基本操作语句
?
???2??3
时间的函数，包括高级的时间类型?
1一般的时间函数：?
??????? MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')--两个日期之间的月数，返回一个浮点数?
??????? ADD_MONTHS ('11-JAN-94',6)--添加月数?
????? NEXT_DAY ('01-SEP-95','FRIDAY') --下一个星期五的日期?
??????? LAST_DAY('01-FEB-95')--当月的最后一天！?
??????? ROUND(SYSDATE,'MONTH')????????? --四舍五入月?
??????? ROUND(SYSDATE ,'YEAR')??????? --四舍五入年?
??????? TRUNC(SYSDATE ,'MONTH')???????? --阶段月?
???????? TRUNC(SYSDATE ,'YEAR')???????? --截断年???
2从时间中提取年，月，日：使用函数extract?
??????? select extract(year from sysdate) year,extract(month from sysdate),?
extract(day from sysdate) from dual;?
3使用函数得到数月之后的日期：to_yminterval（‘01-02’）表示加上1年2月，不能够到天！！?
??????? select hire_date,hire_date +to_yminterval('01-02') as hire_date_new from employees where department_id=20?
???? 得到多少天之后的日期：直接日期加数字！?
??????? select hire_date +3 from employees where department_id=20?
关于子查询和其他的编程思路?
1group语句：和高级的应用语句：?
??????? SELECT??? department_id, job_id, SUM(salary),?? COUNT(employee_id) FROM????? employees?
?????????????? GROUP BY department_id, job_id ;?
??????? 可以使用having进行约束：?
??????? I.group by rollup:对n列组合得到n+1种情况?
??????? SELECT??? department_id, job_id, SUM(salary)?? FROM????? employees?? WHERE???? department_id < 60 GROUP BY ROLLUP(department_id, job_id);?
??????? II.group by cube:得到2的n次方种情况?
??????? SELECT??? department_id, job_id, SUM(salary) FROM????? employees?? WHERE???? department_id < 60 GROUP BY CUBE (department_id, job_id) ;?
??????? III.使用grouping得到一行中构成列的情况，只是返回1和0：是空的话就返回1，否则返回0(注意不要弄反了！)?
??????? SELECT??? department_id DEPTID, job_id JOB,?? SUM(salary),???? GROUPING(department_id) GRP_DEPT,???? GROUPING(job_id) GRP_JOB?
?????????????? FROM????? employees WHERE???? department_id < 50 GROUP BY ROLLUP(department_id, job_id);?
??????? IV.grouping sets:根据需要得到制定的组合情况?
??????? SELECT??? department_id, job_id, manager_id,avg(salary) FROM????? employees GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id));?
2from中使用子查询：返回每个部门中大于改部门平均工资的与员工信息?
??????? SELECT?? a.last_name, a.salary, a.department_id, b.salavg?? FROM???? employees a,--下面的地方就是子查询了，主要返回的是一组数据！?
???????? (SELECT??? department_id, AVG(salary) salavg??? FROM????? employees?? GROUP BY department_id) b?
??????? WHERE??? a.department_id = b.department_id?
??????? AND????? a.salary > b.salavg;?
3exists语句的使用：?
??????? SELECT employee_id, last_name, job_id, department_id?
??????? FROM??? employees outer--下面的 exists里面的select选择出来的是随便的一个字符或者数字都可以?
??????? WHERE?? EXISTS ( SELECT 'X'??? FROM??? employees WHERE?? manager_id =?? outer.employee_id);?
4厉害的with语句：?
??????? WITH?
???????? dept_costs?? AS (--定义了一个临时的表?
????????????????? SELECT?? d.department_name, SUM(e.salary) AS dept_total--其间定义了一个临时的列dept_total?
????????????????? FROM???? employees e, departments d?
????????????????? WHERE??? e.department_id = d.department_id?
????????????????? GROUP BY d.department_name),/*注意这里有逗号*/?
??????? avg_cost???? AS (?
?????????? SELECT SUM(dept_total)/COUNT(*) AS dept_avg?
?????????? FROM??? dept_costs)--这里的第二张临时表里面就引用了前面定义的临时表和之间的列！?
??????? SELECT *?? FROM??? dept_costs?? WHERE?? dept_total >?? (SELECT dept_avg??? FROM avg_cost) ORDER BY department_name;---最后的查询语句中使用了前面的临时表?
5遍历树：?
??????? SELECT employee_id, last_name, job_id, manager_id?
??????? FROM??? employees?
??????? START?? WITH?? employee_id = 101?
??????? CONNECT BY PRIOR manager_id = employee_id ;--自底向上的遍历树。?
6top-n分析:找到工资最高的5个人。（行内视图）?
select rownum,employee_id from (select employee_id,salary from?
employees order by salary desc)?
?? where rownum<5;?
