Oracle表的查询及注意事项

△查询指定列(去掉重复行)
select distinct deptno, job from emp;

△名字必须大写
select deptno,job from emp where ename='SMITH';

△处理null值 使用nvl函数
select sal*13+nvl(comm,0)*13 "年工资",ename,comm from emp;

△where条件中使用in
select * from emp where empno in(123,456,879);

△is null的使用
select ename from emp where mgr is null;

△逻辑运算判断查询
select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';

△按两个字段的查询并排序
select * from emp order by deptno,sal desc;

△按照列的别名排序
select ename,sal+(nvl(comm,0))*12 "年薪" from emp order by "年薪";

△子查询 ——分组函数 max min avg sum count

select min(sal),max(sal) from emp;
select ename,sal from emp where sal=(select mac(sal) from emp);

*显示每个部门每种岗位的平均工资和最高工资
select avg(sal),max(sal),deptno,job from emp group by deptno,job;

*显示平均工资低于2000的部门号和它的平均工资
select deptno ,avg(sal) ,max(sal) from emp group by deptno having avg(sal)<2000;

*如何显示某个员工的上机领导姓名——使用别名，将一张表当成两张表
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno;

①单行子查询
*如何显示与SMITH同一部门的所有员工
select * from emp where deptno=(select deptno from emp where ename='SMITH');
②多行子查询——in
*如何查询和部门10的工作相同的员工姓名、工资
select eanem,sal from emp where job in (select distinct job from emp where deptno=10);
③多行子查询使用all、any操作符
*如何显示工资比30号部门所有员工的雇员的姓名、工资
select ename,sal from emp where sal> all (select sal from emp where deptno=30);
④多列子查询——返回多列
*如何查询于SMITH的部门和岗位完全想通的所有雇员
select ename from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

1.先查出所有部门的平均工资
select deptno,avg(sal) a from emp group by deptno;
2.将1的结果当成一张子表(内嵌视图) 得出结果
select * from emp a2,(select deptno,avg(sal) a from emp group by deptno) a1 
where a1.deptno=a2.deptno and a2.sal>a1.a;

1.先得到所有数据
select * from emp;
2.将1的结果当作内嵌视图 并得到伪列rn 然后提出第一个限定条件如：rn<10
select a1.*,rownum rn from (select * from emp) a1 where rownum<=10;
3.将2的结果当作内嵌视图 提出第二个限制条件 如rn>6
select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>6;

create table mytable(id,sal,deptno) as select empno,sal,deptno from emp;