发布时间 : 星期四 文章ORACLE完整笔记及JAVA习题更新完毕开始阅读eb20843c0622192e453610661ed9ad51f01d5403
FROM emp e ,emp m WHERE e.mgr = m.empno
AND e.hiredate < m.hiredate
第六章分组函数
分组函数：是对表中的一组记录进行操作，每一组能返回一个结果，首先就是要对数据进行分组。组函数只能嵌套两层
MIN：可以用于任何数据类型 MAX：可以用于任何数据类型 AVG：只能用于数值型 SUM：只能用于数值型
COUNT：count *可以计入空值，其他分组函数均是去掉空值计算的！ Groupby 后面只能由两种语句：待分组的列和组函数。 相应的分组过滤字句必须是having里面的， 基础知识没有多少，直接做习题 练习1：
1.查询部门人数大于2的部门编号，部门名称，部门人数 SELECT e.deptno,d.dname,COUNT(e.empno) FROM emp e ,dept d
WHERE e.deptno = d.deptno GROUPBY e.deptno,d.dname HAVINGCOUNT(e.empno)>2 2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排列
SELECT e.deptno,d.dname,COUNT(empno),AVG(sal) FROM emp e,dept d
WHERE e.deptno = d.deptno GROUPBY e.deptno,d.dname HAVINGAVG(sal)>2000 ORDERBYCOUNT(empno)
3．查询部门20 的员工，每个月的工资和及平局工资
SELECTSUM(sal)总工资,AVG(sal)平均工资FROM emp WHERE deptno ='20' 4.查询工作在CHICAGO的员工人数，最高工资及最低工资 SELECTCOUNT(e.empno),MIN(e.sal),MAX(e.sal) FROM emp e,dept d
WHERE e.deptno = d.deptno AND d.loc ='CHICAGO'
5.查询一共有集中岗位类型(去除重复记录并计数)
SELECTCOUNT(DISTINCT job)FROM emp
6.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息 SELECT m.empno,m.ename,COUNT(e.empno) FROM emp e,emp m
WHERE e.mgr = m.empno(+) GROUPBY m.empno,m.ename
--1.部门平均工资在2500以上的部门名称及平均工资
SELECTAVG(e.sal),d.dname FROM emp e ,dept d WHERE e.deptno = d.deptno GROUPBY d.deptno,d.dname HAVINGAVG(sal)>2500
--2.查询岗位不以‘SA’开头，并且平均工资在2500以上的岗位及平均工资，按照工资降序
SELECT job,AVG(sal)FROM emp WHERE job <>'SA%' GROUPBY job ORDERBYAVG(sal)DESC
--3.查询部门人数在两个人以上的部门名称，最低工资，最高工资，并对工资进行四舍五入。
SELECT d.dname,round(MIN(e.sal)),round(max(e.sal)) FROM emp e,dept d WHERE e.deptno = d.deptno GROUPBY d.deptno,d.dname HAVINGCOUNT(empno)>2
--4.查询岗位不是salsesman的，工资和大于2500的每种岗位及其工资和
SELECT job,SUM(sal)FROM emp WHERE job <>'SALESMAN' GROUPBY job HAVINGSUM(sal)>2500
--5.显示经理号码，这个经理管理员工的最低工资，没有经理的king也要显示，不包括最底工资小于3000的，按最低工资由低到高排序
SELECT m.empno,min(e.sal) FROM emp e,emp m
WHERE e.mgr = m.empno(+) GROUPBY m.empno
HAVINGMIN(e.sal)>=3000 ORDERBYmin(e.sal)ASC
--6.查询每个部门的最低工资与最高工资的差额
SELECTMAX(sal)-MIN(sal)FROM emp GROUPBY deptno
第七章子查询
子查询先于主查询执行
主查询与子查询之间的运算符可以使用：= ,>,<,IN<=,>=,<>（单行子查询）， ANY,ALL（多行子查询），单行子查询：子查询的返回结果为一行一列，
多行子查询：返回结果是多行一列（包括0行，也就是0行也算多行）
多列子查询：返回的查询结果为多行多列。
子查询可以用在where，having，from 子句中。 单行子查询例题：
--1.工资最高的员工的员工姓名，工资
SELECT ename ,sal FROM emp WHERE sal =(SELECTMAX(sal)FROM emp )
--2.查询和7369相同工作的，并且工资大于7369的员工的姓名，工作，工资
SELECT ename, job,sal
FROM emp WHERE job =(SELECT job FROM emp WHERE empno =7369) AND sal >(SELECT sal FROM emp WHERE empno =7369)
--3.查询部门最低工资比20号部门最低工资高的部门编号和最低工资
SELECT deptno,MIN(sal)FROM emp GROUPBY deptno
HAVINGMIN(sal)>(SELECTMIN(sal)FROM emp WHERE deptno =20) --4.查询什么部门的人数高于各个部门的平均人数 SELECT deptno FROM emp GROUPBY deptno
HAVINGCOUNT(empno)>(SELECTavg(COUNT(empno))FROM emp GROUPBY deptno) --5.查询入职日期最早的那个员工姓名
SELECT ename FROM emp WHERE hiredate =(SELECTMIN(hiredate)FROM emp)
--6.查询工资比smith高，并且工作地点在chicago的员工姓名，工资，部门名称
SELECT e.ename,e.sal,d.dname FROM emp e,dept d WHERE e.deptno = d.deptno AND d.loc ='CHICAGO'AND e.sal >
(SELECT sal FROM emp WHERE ename ='SMITH')
--7.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期
SELECT ename,min(hiredate)FROM emp WHERE hiredate <(SELECTmin(hiredate)FROM emp WHERE deptno =20)
--8.查询部门人数大于各部门平均人数的部门名称，部门编号，部门人数 SELECT d.dname,e.deptno,COUNT(e.empno)FROM emp e,dept d WHERE e.deptno(+)= d.deptno GROUPBY d.dname,e.deptno HAVINGCOUNT(empno)>
(SELECTAVG(COUNT(empno))FROM emp GROUPBY deptno)
一下练习中演示了多行子查询中多行运算符（IN 、ANY、 ALL）的应用。 --1.查询是经理的员工姓名，工资
SELECT ename,sal FROM emp WHERE empno IN(SELECT mgr FROM emp)
--2.查询部门编号不为10，且工资比10 部门中任意一个员工工资高的员工编号，姓名，职位，工资
SELECT empno,ename,job,sal FROM emp
WHERE deptno <>10AND sal >ANY(SELECT sal FROM emp WHERE deptno =10)
--3.查询部门编号不为10，且工资比10 部门中任意一个员工工资低的员工编号，姓名，职位，工资
SELECT empno,ename,job,sal FROM emp
WHERE deptno <>10AND sal --4.查询部门编号不为10，且工资比10 部门中所有员工工资低的员工编号，姓名，职位，工资 SELECT empno,ename,job,sal FROM emp WHERE deptno <>10AND sal --5.查询部门编号不为10，且工资比10 部门中所有员工工资高的员工编号，姓名，职位，工资 SELECT empno,ename,job,sal FROM emp WHERE deptno <>10AND sal >ALL(SELECT sal FROM emp WHERE deptno =10) 多列子查询：通过一下的例子可以看出，多列子查询其实就是子查询中出现了多个列！ --1.查询职位和部门与smith完全相同的人员姓名， SELECT ename FROM emp WHERE(job,deptno)IN(SELECT job,deptno FROM emp WHERE ename ='SMITH') AND ename <>'SMITH' --2.查询和1981年入职的任意一个员工的部门和职位完全相同，员工姓名，部门，职位，入职日期，不包括1981年入职的员工 SELECT ename,deptno,job,hiredate FROM emp WHERE(deptno,job) IN(SELECT deptno,job FROM emp WHEREEXTRACT(YEARFROM hiredate)=1981) ANDSUBSTR(hiredate,-2)<>81 或substr(hiredate,-2)=81或to_char(hiredate,'YYYY')='1981' --3.查询和1981年入职的任意一个员工的部门或职位相同，员工姓名，部门，职位，入职日期，不包括1981年入职的员工 SELECT ename,deptno,job,hiredate FROM emp WHERE( deptno IN(SELECT deptno FROM emp WHEREEXTRACT(YEARFROM hiredate)=1981) OR job IN(SELECT job FROM emp WHEREEXTRACT(YEARFROM hiredate)=1981)) ANDEXTRACT(YEARFROM hiredate)<>1981 注意：最后一题不是多列子查询，说白了，多列子查询只能解决“列和”的问题，不能解决“列或”的问题。 子查询中的空值问题： 查询不是经理的员工姓名 SELECT ename FROM emp WHERE empno NOTIN(SELECT mgr FROM emp ) 翻译为： SELECT ename FROM emp WHERE (empno <>7839AND empno <>7782AND empno <>NULLAND empno <>7698AND empno <>7902AND empno <>7566) 例子中可以看出，子查询中有了一个空值，整个查询结果就是faulse，一个也查不出来 所以以上查询应该这样写： SELECT ename FROM emp WHERE empno NOTIN(SELECT mgr FROM emp where mgr is not null) From 子句中应用子查询：这一类查询中很明显“自己部门”既没有信息，又是模糊的， 自己部门+平均工资，可以构成一个表。 --1.查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资。 SELECT e.ename,e.sal,e.deptno,tavg.av FROM emp e,(SELECT deptno,AVG(sal) av FROM emp GROUPBY deptno) tavg WHERE e.deptno = tavg.deptno AND e.sal > tavg.av 关于伪列ROWNUM： 1.ROWNUM是一个伪列，它与查询相对应，如果给每个查询都建立一个ROWNUM ，这些伪列之间互不影响。 2.select * from emp where rownum > 2查不出任何数据，因为rownum跟就记录生成而生成，roenum只能用<,<=用，不能用>！！！！ 3.rownum和orderby连用的时候，rownum其实是排完序之后的！可见order by最后执行啊！！ --1.查询入职日期最早的前5名员工姓名，入职日期。 SELECTROWNUM,ename ,hiredate FROM(SELECT ename,hiredate FROM emp ORDERBY hiredate ASC)WHEREROWNUM<=5