Oracle 常用脚本和查询命令.doc

一、Prompt，pause，accept交互操作脚本1. 输入数值prompt 显示工资高于输入值的员工信息pause 按继续accept var_value number prompt 输入工资:select * from emp where sal&var_value;2. 输入字符串prompt 显示输入姓名的员工信息pause 按继续accept var_value char prompt 输入姓名：select * from emp where ename=&var_value;3. 输入日期rompt 显示晚于输入日期参加工作的员工信息pause 按继续accept var_value DATE prompt 输入日期：select * from emp where hiredate &var_value;二、格式设置脚本Set linesize 120set pagesize 40TTitle center Sample Report |of empBTitle skip 2 center End of Sample Report | of empcolumn sal heading salarycolumn ename format A12 truncate heading employee|namebreak on deptno skip 2select * from emp order by deptno;column sal clearcolumn ename clearclear break;clear compute;三、基于break on的分组统计脚本（小计+总计）1、基于部门的分组小计和总计break on deptno skip page on reportcompute sum of sal on deptno;compute sum of sal on report;Select deptno, sal from emp order by deptno;clear break;clear compute;2、基于部门的分组小计TTitle center Employee Record|hitech corpBTitle right Report By|SQL*PLUS studentbreak on deptno skip 2compute sum of sal on deptnoselect ename, sal, deptno from emp order by deptno;3、统计函数：sum, avg, max, min, count, num四、报表案例脚本1、示例1set pagesize 15set linesize 60TTitle center 工资报表 skip 2BTitle skip 2 right Report By 会计部column ename heading 姓名 format A12column hired heading 雇佣时间column sal heading 工资 format $9999.9column deptno heading 部门编号column sal format $99,999break on deptno skip page on reportcompute sum of sal on deptno compute sum of sal on reportcompute sum of hired on deptnocompute sum of hired on reportspool E:salaryselect ename,trunc(sysdate-hiredate)/7) hired,sal,deptno from emp order by deptno; spool offTTitle offBTitle offclear breakclear computeTTitle offBTitle offclear breakclear compute2、示例2set linesize 140break on deptno skip 2compute sum of sal on deptno;Select * from emp order by deptno;compute max of sal on deptno;Select * from emp order by deptno;compute min of sal on deptno;Select * from emp order by deptno;compute avg of sal on deptno;Select * from emp order by deptno;clear break;clear compute;五、常用的查询语句1. 基本查询select empno,ename,sal,deptno, comm from emp where comm is not null and sal 1500;select ename, job, sal from emp where job in (SALESMAN,CLERK) and sal1500;SELECT empno,ename,sal FROM emp ORDER BY sal desc;SELECT empno,sal*12 salary FROM emp ORDER BY salary; SELECT empno,ename, sal, comm, sal+nvl(comm,0) salary FROM emp ORDER BY salary; 2. 分组统计查询select deptno,sum(sal) from emp where job!= CLERK group by deptno having sum(sal)8000 order by sum(sal) desc;SELECT deptno 部门编号,count(*) 人数,avg(sal) 平均工资 FROM emp GROUP BY deptno;SELECT deptno 部门编号,count(*) 人数,avg(sal) 平均工资 FROM emp GROUP BY deptno;SELECT job,count(*),avg(sal) FROM emp GROUP BY job having avg(sal)2000 order by 3; select deptno, job, avg(sal),sum(sal) from emp group by job,deptno;select deptno, job, avg(sal) from emp group by deptno, job;select distinct deptno,job from emp order by deptno;avg, count(*) max min sum3. 多表连接查询select ename,emp.deptno, dname, loc from emp,dept where emp.deptno=dept.deptno order by emp.deptno;SELECT ename,emp.deptno,dname FROM emp JOIN dept ON emp.deptno=dept.d