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);

△分组函数group by  having order by ——①三者顺序不能换②一定要有group by
*显示每个部门每种岗位的平均工资和最高工资
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 havingavg(sal)<2000;

△笛卡尔集规定：多表查询的条件至少不能少于表的个数-1


△自连接查询——在同一张表的连接查询
*如何显示某个员工的上机领导姓名——使用别名，将一张表当成两张表
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)=(selectdeptno,job from emp where ename='SMITH');

△在from句子中使用子查询
*如何显示高于自己部门平均工资的员工信息
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;

△orecle的分页查询
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;

△合并查询
union——取得并集(自动去掉重复的行)
union all——取得并集
intersect——取得交集
minus——取得差集