oracle学习笔记(二)查询语句

求大于2000的部门平均工资 

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

 

求部门的最多薪水的人及钱

  select cname,sal from cmp
    join (select max(sal) max_sal, deptno from emp group deptno) t
       on (cmp.sal = t.max_sal and cmp.deptno = t.deptno);

求自己和自己经理的名字(自连接)

   select e1.ename,e2.ename from emp e1, emp e2 where e1.mgr = e2.empno

 

连接

select e1.ename,e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno)
left/right/full join
cross join交叉连接：产生笛卡尔积
natural join自然连接：自动进行关联字段的匹配
using子句：直接指定关联的操作列
select * from emp a join emp b useing(emptno);
ON子句：用户自己编写连接的条件

使用多表查询的时候会产生笛卡尔积，如果表的数据越多，那么笛卡尔积越大：要想去掉笛卡尔积必须使用字段关联的操作。

嵌套的分组函数:select Max(avg(sal)) from emp group by deptno;

 

求出：部门名称，部门的员工数，部门的平均工资，部门的最低收入的姓名；
select d.dname, ed.c, ed.a, e.ename
from dept d,(
 select deptno, count(empno) c, avg(sal), min(sal) min
 from emp
 group by deptno) ed, emp e
where d.deptno = ed.deptno and e.sal = ed.min;

 

求各部门工资最低的人的资料
select * from emp
where (job,sal) in (select job, min(sal) from emp group by job);

 

求出每个部门最低工资的雇员信息。
select * from emp where sal in ( select min(sal) from emp group by deptno);
=any 相当于in : select * from emp where sal =any( select min(sal) from emp group by deptno);
>any比里面最小的值大 ：select * from emp where sal >any( select min(sal) from emp group by deptno);
<any比里面最大的值小 ：select * from emp where sal >any( select min(sal) from emp group by deptno);
>all大于里面最大值:select * from emp where sal >all( select min(sal) from emp group by deptno);
<all小于里面最小值:select * from emp where sal <all( select min(sal) from emp group by deptno);

 

将表结构及数据完整的复制出来。
create table myemp as select * from emp;加一个永远不成立的条件则是复制表结构，数据则不复制出来如：where 1 = 2

 

ROWNUM:表示行号，实际上是一个列，但是这个列是一个伪列，此列可以在每张表中出现，以自动编号的形式出现,显示前5条记录时 ROWNUM <= 5
如果现在要想进行中间的截取操作，则只能采用子查询，例如现在假设每页显示5条，第二页显示6-10条，那么对于数据库的操作来讲，它查询的时候应该首先查询出1-10条，之后再查询的结果中取出后5条
select * from
(select ROWNUM rn ,empno,ename,sal,job,hiredate from emp where ROWNUM <= 10) temp
where temp.rn > 5;