初识数据库(分组函数)

分组函数

　　--什么是分组函数 : 可以对结果集中的一组记录进行一次计算

　　　　select sal from emp

　　--单行分组函数

　　　　--MAX、MIN (最大、最小)

　　--查询emp表中工资最大的和工资最小的工资

　　　　select max(sal),min(sal)

　　　　from emp

　　--SUM、AVG(总和，平均数)

　　--查询员工表中部门号是20的员工工资的总和以及平均工资

　　　　select sum(sal),avg(sal)

　　　　from emp e

　　　　where e.deptno = '20'

　　--count 用来记录结果集中的数据条数

　　　　select count(*)

　　　　from emp

　　　　where deptno = '30'

　　--注意 : 所有的分组函数都是忽略null的

　　　　select avg(comm)

　　　　from emp

　　　　select avg(nvl(comm,0))

　　　　from emp

　　--查询员工表中有几个不同的职位

　　　　select count(distinct(job))

　　　　from emp e

多行函数

　　--查询emp表各部门的最大工资

　　　　--group by 关键字

　　--显示部门、最大工资

　　　　select deptno,max(sal)

　　　　from emp

　　　　group by deptno

　　--注意多行分组函数的关键字 group by 要求是from子句后必须是每一组唯一值

　　　　--在select 子句后可以检索的也是每一组的唯一值

　　--查询不同部门下有哪些岗位，计算出不同岗位的平均工资

　　　　select deptno,job,avg(sal)

　　　　from emp

　　　　group by deptno,job

　　　　order by deptno

　　--having 子句 : 对分组结果进行条件判断

　　　　--查询emp表中的平均工资高于2000的部门编号以及平均工资

　　　　select deptno,avg(sal)

　　　　from emp

　　　　group by deptno

　　　　having avg(sal)>2000

　　--为什么不用where子句

　　　　--因为执行顺序 : from -> where -> group by -> having -> select -> order by

分组嵌套

　　--查询各部门人数的平均值

　　　　select avg(count(*))

　　　　from emp

　　　　group by deptno

　　--分析函数

　　--可以在数据中进行分组

　　--计算基于组的某种统计值

　　--每一组的每一行都会返回一个值

　　--包含三个子句

　　　　--分组子句(partition by)

　　　　--排序子句(order by)

　　　　--窗口子句(rows)

　　　　--over() 开窗函数

　　--显示员工编号，员工姓名，部门编号，工资，按部门提取工资和，按部门累计工资

　　　　select e.ename as "员工姓名", e.empno as "员工编号",e.deptno as 部门编号,e.sal as 工资,

　　　　sum(sal) over(partition by deptno)  按部门分组 ,sum(sal) over(partition by deptno order by sal) as  按部门累计工资

　　　　from emp e

　　--分级函数 rank() : 按照某种排序显示等级

　　　　--显示岗位工资和等级

　　　　select e.ename as "员工姓名", e.empno as "员工编号",e.deptno as 部门编号,e.sal 工资,e.job 岗位,

　　　　rank() over(partition by e.job order by e.sal desc) 岗位工资等级

　　　　from emp e

子查询

　　--就是把一条完整的sql语句嵌套到另外一条sql语句中

　　　　--范围 : select , from , wherer , having

　　--单行子查询

　　　　--子查询结果是一行一列的

　　--查询和SCOTT用户同领导的员工信息，但不包含他

　　　　select *

　　　　from emp

　　　　where mgr =

　　　　(select mgr

 　　　　from emp

 　　　　where ename = 'SCOTT')

 　　　　and ename != 'SCOTT'

　　--查询KING、的直属下属

　　　　select *

 　　　　from emp

 　　　　where mgr =

 　　　　(select empno

 　　　　from emp

 　　　　where ename = 'KING')

　　--查询平均工资高于20部门的平均工资部门编号和平均工资

　　　　select deptno,avg(sal)

　　　　from emp

　　　　group by deptno

　　　　having avg(sal)>(select avg(sal) from emp where deptno = '20')

　　--查询人数多于平均人数的部门编号以及人数

　　　　select deptno,count(*)

　　　　from emp

　　　　group by deptno

　　　　having count(*)>

　　　　(select avg(count(*))

　　　　from emp

　　　　group by deptno)

　　--查询和SMITH同部门并且工资比SMITH高的

　　　　select *

　　　　from emp

　　　　where

　　　　deptno =

　　　　(select deptno

　　　　from emp

　　　　where ename = 'SMITH') and

　　　　sal >

　　　　(select sal

　　　　from emp

　　　　where ename = 'SMITH')

多行子查询

　　--子查询的结果是一列多行

　　　　--不能使用比较运算符

　　　　--可以使用逻辑运算符

　　--in 依次匹配

　　--查询哪些员工是领导

　　　　select *

　　　　from emp

　　　　where empno

　　　　in(select mgr from emp)

　　--查询各部门工资排倒数第一、第二的

　　　　select * from

　　　　(select min(sal) 倒第一,deptno

　　　　from emp

　　　　group by deptno)

　　　　join

　　　　(select min(sal) 倒第二 ,deptno

　　　　from emp

　　　　where sal not in

　　　　(select min(sal)

　　　　from emp

　　　　group by deptno)

　　　　group by deptno)

　　　　using(deptno)

　　--排序函数

　　　　--row_number () : 对固定的数据进行排序，根据序号进行判断

　　--查询各部门工资在前两位的

　　　　select *

　　　　from

　　　　(select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) rn

　　　　from emp)

　　　　where rn < 3

　　--any : 与子查询表中任意的一条数据相匹配(最小的数据相匹配)

　　--查询20部门下的工资不低于10部门下的任意员工的工资的员工

　　　　select deptno,sal from emp where sal > any

　　　　(select sal from emp where deptno = '10')

　　　　and deptno = '20'

　　　　select deptno,sal from emp where sal >

　　　　(select min(sal) from emp where deptno = '10')

　　　　and deptno = '20'

　　--all

　　--与子查询表中所有的数据进行匹配(最大的数据进行匹配)

　　　　select deptno,sal from emp where sal > all

　　　　(select sal from emp where deptno = '10')

　　　　and deptno = '20'

　　--多行多列子查询

　　　　--就是子查询结果集是多行多列的

　　　　--主查询也应该是多行多列的

　　--查询员工表中各部门员工工资的最大值以及员工的姓名

　　　　select deptno 部门编号,ename MAX工资员工姓名,sal 工资

　　　　from emp

　　　　where (deptno,sal) in

　　　　(select deptno,max(sal)

　　　　from emp

　　　　group by deptno)

表合并

　　--union : 累计 -- 去掉重复 

　　--union all : 累计 -- 不去掉重复

　　　　select * from haha

　　　　select name , num, sl,(sl*num) 小计

　　　　from haha

　　　　union all

　　　　(select '合计',sum(num),sum(sl),sum(sl*num)

　　　　from haha)

　　--内联视图

　　--查询和SMITH同部门入职比SMITH晚，且工资比他高的

　　　　select e.*

　　　　from emp e ,

　　　　(select deptno,hiredate,sal from emp

　　　　where ename = 'SMITH') a

　　　　where e.deptno = a.deptno and e.hiredate > a.hiredate

　　　　and e.sal > a.sal

　　--伪列：oracle对所有的表都提供了一个排序的隐藏列

　　　　--rownum

　　　　select rownum, dept.* from dept

　　--特性:

　　　　--1、rownum编号 是在 order by 之前进行

　　　　　　select rownum, dept.* from dept

　　　　　　order by dname

　　　　--2、rownum 的比较只能使用 < 或 <=

　　　　　　select rownum, dept.* from dept

　　　　　　where rownum <= 2

分页

　　--查询员工表中5-8位的员工信息

　　　　select b.*

　　　　from

　　　　(select a.*,rownum rn

　　　　from (select * from emp) a

　　　　where rownum <= 8) b

　　　　where rn >= 5

　　　　select b.*

　　　　from

　　　　(select emp.*,rownum rn

　　　　from emp

　　　　where rownum <= 8) b

　　　　where rn >= 5