SQL(Oracle)学习20170917

一、知识点：
1.rownum只能使用<,<=,不能使用>,>=










二、练习题
题目1：
分页显示查询员工信息:显示员工工号，姓名，月薪
分页显示四条记录
显示第二页的员工
按照月薪降序排列


第一步：
按照月薪降序筛选信息
SELECT empno,ename,sal FROM emp ORDER BY sal DESC;
第二步：
将以上信息作为子查询，筛选行号<=8的数据
SELECT ROWNUM r,empno,ename,sal FROM
(SELECT empno,ename,sal FROM emp ORDER BY sal DESC) A1
WHERE rownum<=8;
第三步：
将以上信息作为子查询，筛选临时表列r>=5的数据
SELECT empno,ename,sal FROM
(SELECT ROWNUM r,empno,ename,sal FROM
(SELECT empno,ename,sal FROM emp ORDER BY sal DESC) A1
WHERE rownum<=8) A2
WHERE A2.r>=5;




题目2：
找到员工表中薪水大于本部门平均薪水的员工


子查询方法：
第一步：
按部门列出平均薪水
SELECT A.deptno,AVG(a.sal) FROM emp A
GROUP BY A.deptno;
第二步：
找出本部门大于平均薪水的员工
SELECT A.empno,A.ename,A.sal,B.avgsal
FROM emp A,
(SELECT A.deptno,AVG(a.sal) avgsal FROM emp A
GROUP BY A.deptno) B
WHERE A.deptno=B.deptno
AND A.sal>B.avgsal;
/*查看执行计划
EXPLAIN PLAN FOR
SELECT A.empno,A.ename,A.sal,B.avgsal
FROM emp A,
(SELECT A.deptno,AVG(a.sal) avgsal FROM emp A
GROUP BY A.deptno) B
WHERE A.deptno=B.deptno
AND A.sal>B.avgsal;


在sqlplus窗口执行
已解释


SELECT * FROM TABLE(dbms_xplan.display);
显示如下，消耗28个CPU
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    43 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     1 |    43 |     7  (15)| 00:00:01 |
|   2 |   VIEW               |      |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP  |    14 |   238 |     3   (0)| 00:00:01 |
*/


相关子查询方法：
第一步：
查询员工信息
SELECT empno,ename,sal
FROM emp A;
同时找出平均薪水
(SELECT AVG(sal) FROM emp WHERE deptno=A.deptno) avgsal
第二步：
合并
SELECT empno,ename,sal,(SELECT AVG(sal) FROM emp WHERE deptno=A.deptno) avgsal
FROM emp A
WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno=A.deptno);




题目3：
按员工入职年份统计员工数量
DECODE函数方法
第一步：
统计全部员工数量
SELECT COUNT(*) Total
FROM emp;
第二步：
统计1981年入职的员工数
SELECT SUM(DECODE(to_char(hiredate,'YYYY'),'1981',1,0)) "1981"
FROM emp;
统计其他年份入职人数并合并语句
SELECT
COUNT(*) Total,
SUM(DECODE(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",
SUM(DECODE(to_char(hiredate,'YYYY'),'1981',1,0)) "1981",
SUM(DECODE(to_char(hiredate,'YYYY'),'1982',1,0)) "1982",
SUM(DECODE(to_char(hiredate,'YYYY'),'1987',1,0)) "1987"
FROM emp;


统计如下图所示：
     TOTAL       1980       1981       1982       1987
---------- ---------- ---------- ---------- ----------
        14          1         10          1          2


子查询方法
第一步：
统计全部员工数
SELECT COUNT(*) FROM emp;
第二步：
统计1981年入职的员工数
SELECT COUNT(*)
FROM emp
WHERE TO_CHAR(hiredate,'YYYY')='1981'
第三步：
合并语句
SELECT
(SELECT COUNT(*) FROM emp) Total,
(SELECT COUNT(*) FROM emp WHERE TO_CHAR(hiredate,'YYYY')='1980') "1980",
(SELECT COUNT(*) FROM emp WHERE TO_CHAR(hiredate,'YYYY')='1981') "1980",
(SELECT COUNT(*) FROM emp WHERE TO_CHAR(hiredate,'YYYY')='1982') "1980",
(SELECT COUNT(*) FROM emp WHERE TO_CHAR(hiredate,'YYYY')='1987') "1980"
FROM dual;