oracle程序组,Oracle 表操作-组函数

/*

以下语句均 基于 [scott/tiger] 用户下的操作

*/

--分组统计各部门下工资>500的员工的平均工资

Select Avg(Sal) From Emp Where Sal>500 Group By Deptno ;

--平均工资，处理null情况

Select Round(Avg(Nvl(Comm, 0))) From Emp;

--having 语句

Select Deptno, Job, Avg(Sal)

From Emp

Where Hiredate >= To_Date('1981-05-01','yyyy-mm-dd')

Group By Deptno,Job Having Avg(Sal) > 1200

Order By Deptno,Job;

--统计各部门下平均工资大于500的部门

Select Deptno,Avg(Sal) From Emp

Group By Deptno Having Avg(Sal)>500 ;

--算出部门30中得到最多奖金的员工奖金

Select Max(Comm) From Emp Where Deptno = 30 ;

--算出部门30中得到最多奖金的员工姓名

Select Ename,Comm From Emp

Where Comm = (

Select Max(Comm)

From Emp Where deptno=30);

--算出每个职位的员工数和最低工资

Select Job,Min(Sal),Count(*) Num From Emp Group By Job;

--case查询

Select Empno, Ename, Sal,

Case Deptno

When 10 Then '财务部'

When 20 Then '研发部'

When 30 Then '销售部'

Else '未知部门'

End 部门

From Emp;

--group by 和 order by的联合使用

Select Sum(Sal+Nvl(Comm,0))

From Emp

Group By Emp.Job

Order By Sum(Sal+Nvl(Comm,0)) Desc;

Select Job,Deptno,Sum(Sal+Nvl(Comm,0))

From Emp

Group By Job,Deptno

Order By Sum(Sal+Nvl(Comm,0)) Desc;

Select Deptno,Job,Sum(Sal+Nvl(Comm,0))

From Emp

Group By Deptno,Job;

Order By Sum(Sal+Nvl(Comm,0)) Desc;

--查询allen上司的信息

--方法1，嵌套查询

Select Empno,Ename,Hiredate

From Emp

Where Empno=(

Select Mgr From Emp

Where Ename='ALLEN');

--方法2，自连接查询

Select M.Empno,M.Ename,M.Hiredate

From Emp E,Emp M

Where E.Mgr = M.Empno

And e.Ename='ALLEN';

/*

连接：左外连接，右外连接，满外连接

可以一次用【关键字】

left outer join

right outer join

full outer join

用+号区分时，没有+的是主表，带的是外连接表

*/

/* 左外连接 */

--形式1

Select Empno,Ename,Dname,Job

From Emp

Left Outer Join Dept

On Emp.Deptno = Dept.Deptno;

--形式2

Select Empno,Ename,Dname,Job

From Emp,Dept

where Emp.Deptno = Dept.Deptno(+);

/* 右外连 */

--形式1

Select Empno,Ename,Dname

From Emp

Right Outer Join Dept

On Emp.Deptno = Dept.Deptno;

--形式2

Select Empno,Ename,Dname

From Emp,Dept

where Emp.Deptno(+) = Dept.Deptno;

--满外连

Select Empno,Ename,Dname

From Emp

Full Outer Join Dept

On Emp.Deptno = Dept.Deptno;

/* 表可以外部连接到至多一个其它的表 所以下面这个语句是错误的*/

Select Empno,emp.Ename,Dname,Bonus.Sal

From Emp,Dept,Bonus

Where Emp.Deptno(+) = Dept.Deptno

And Emp.Job(+)=Bonus.Job;

--而下面这样写却是可以的

Select Empno,emp.Ename,Dname,emp.Sal

From Emp,Dept,Bonus

Where Emp.Deptno(+) = Dept.Deptno

And Emp.Job=Bonus.Job(+);

Select * From Emp

Where Sal>Any(

Select Avg(Sal) From Emp

Group By Deptno

);

/* 可能会存在null行*/

Select avg(sal) From emp group by Deptno;

Select * From Emp

Where Sal>all(

Select Avg(Sal) From Emp

Group By Deptno

Having avg(sal)>0

);

/*

oracle 中的分页是通过隐式字段rownum来获取行标，从而进行分段取数据

*/

--取前5条记录，Rownum是每个表的隐藏字段

Select Rownum,Empno,Ename From Emp

Where Rownum >=1 And Rownum <=5;

/*

取emp表6-10行数据可以做如下处理

注意这个rownum隐式字段一定要起个别名如rn

否则也无法进行访问

*/

Select * From

( Select Rownum Rn,Empno,Ename From Emp) Temptable/* 隐式表 */

Where Temptable.Rn >=6 And Temptable.Rn <=10;/*引用别名来实现分页*/

/* 取emp表11-15行数据可以做如下处理 */

Select * From

( Select Rownum Rn,Empno,Ename From Emp) Temptable/* 隐式表 */

Where Temptable.Rn >=11 And Temptable.Rn <=15;