6-oracle查询

desc dept;//显示表结构

set timing on;//显示操作时间

select count(*) from dept;

select distinct job from emp;

select emp.sal,emp.job,dept.dname from emp,dept where emp.deptno=dept.deptno and emp.ename='SMITH'; 

select ename,(sal*12) as "nian" from emp;

select count(*) "num" from emp;

select ename,comm+sal*12 as "nian" from emp;//comm为空的话 结果就是空

select ename,nvl(comm,0)+sal*12 as "nian" from emp;//nvl 如果comm为空 就用0代替

select ename,hiredate from emp where hiredate>'1981-1-1';

select ename,sal from emp where sal>2000 and sal<2500;

select ename,sal from emp where sal between 2000 and 2500;//包括2000,2500

select ename,sal from emp where ename like 'S%';//%标示多个字符

select ename,sal from emp where ename like '_O%';//_标示一个字符

select * from emp where empno in(7839,1,2);

select ename,comm from emp where comm is null;//为空

select ename,comm from emp where comm is not null;

select * from emp where sal>500 or job='MANAGER' and ename like 'J%';

select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';

select * from emp order by empno;//默认asc升序

select * from emp order by empno desc;

select ename,sal*12 as "nian" from emp order by "nian";

select * from emp order by deptno,sal desc;

select max(comm) from emp;

select sum(comm) from emp;

select max(sal),min(sal) from emp;

select ename,job from emp where sal>(select avg(sal) from emp);

select deptno,avg(sal),max(sal) from emp group by deptno;

select deptno,job,avg(sal),min(sal) from emp group by deptno,job;

select deptno,job,avg(sal),min(sal) from emp group by deptno,job order by deptno;

select deptno,job,avg(sal),min(sal) from emp group by deptno,job;

select deptno,avg(sal) from emp group by deptno having avg(sal)<2000;

select a1.ename,a1.job,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;

select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno and a1.deptno=10;

select a1.ename,a2.ename from emp a1,emp a2 where a1.mgr=a2.empno and a1.ename='FORD';//自连接 查询ford 上级

select * from emp where deptno=(select deptno from emp where ename='SMITH');

select * from emp where job in(select job from emp where deptno=10);

select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

select a1.ename,a1.sal,a2.mysal from emp a1,(select deptno,avg(sal) mysal from emp group by deptno) a2 where a1.sal>a2.mysal and a1.deptno=a2.deptno;//查找比本部门平均工资高的员工

select * from (select a1.*,rownum rn from (select * from emp ) a1 where rownum<=5) where rn>0;//分页查询

需要只需要改最里面select的语句