Oracle 常用脚本和查询命令

一、Prompt，pause，accept交互操作脚本
1.输入数值
prompt 显示工资高于输入值的员工信息
pause '按<Enter>继续'
accept var_value number prompt '输入工资:'
select * from emp where sal>&var_value;
2.输入字符串
prompt 显示输入姓名的员工信息
pause '按<Enter>继续'
accept var_value char prompt '输入姓名：'
select * from emp where ename='&var_value';
3.输入日期
rompt 显示晚于输入日期参加工作的员工信息
pause '按<Enter>继续'
accept var_value DA TE prompt '输入日期：'
select * from emp where hiredate >'&var_value';
二、格式设置脚本
Set linesize 120
set pagesize 40
TTitle center 'Sample Report |of emp'
BTitle skip 2 center 'End of Sample Report || of emp'
column sal heading salary
column ename format A12 truncate heading 'employee|name'
break on deptno skip 2
select * from emp order by deptno;
column sal clear
column ename clear
clear break;
clear compute;
三、基于break on的分组统计脚本（小计+总计）
1、基于部门的分组小计和总计
break on deptno skip page on report
compute 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 corp'
BTitle right 'Report By|SQL*PLUS student'
break on deptno skip 2
compute sum of sal on deptno
select ename, sal, deptno from emp order by deptno;
3、统计函数：sum, avg, max, min, count, num
四、报表案例脚本
1、示例1
set pagesize 15
set linesize 60
TTitle center '工资报表' skip 2
BTitle skip 2 right 'Report By 会计部'
column ename heading '姓名' format A12
column hired heading '雇佣时间'
column sal heading '工资' format $9999.9
column deptno heading '部门编号'
column sal format $99,999
break on deptno skip page on report
compute sum of sal on deptno
compute sum of sal on report
compute sum of hired on deptno
compute sum of hired on report
spool E:\salary
select ename,trunc((sysdate-hiredate)/7) hired,sal,deptno from emp order by deptno; spool off
TTitle off
BTitle off
clear break
clear compute
TTitle off
BTitle off
clear break
clear compute
2、示例2
set linesize 140
break on deptno skip 2
compute 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 sal>1500; 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 sum
3.多表连接查询
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.deptno; SELECT ename,emp.deptno,dname FROM emp,dept WHERE emp.deptno=dept.deptno; select ename,sal from emp e where sal>(select avg(sal) from emp);
选出那些工资高于部门平均工资的员工。