Oracle笔记3

作业15:查看SMITH上司在哪个城市工作?
SELECT e.ename,m.ename,d.loc
FROM emp e,emp m,dept d
WHERE e.mgr = m.empno
AND m.deptno=d.deptno


作业16:
查看平均工资高于2000的那些部门名字以及所在城市?
SELECT AVG(e.sal),d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname,d.loc
HAVING AVG(e.sal)>2000


作业17:
在NEW YORK工作的员工有多少人?
SELECT COUNT(e.ename)
FROM emp e JOIN dept d
ON e.deptno=d.deptno
WHERE d.loc = 'NEW YORK'


作业18:
在DALLAS工作的员工的平均工资是多少?
SELECT AVG(e.sal)
FROM emp e JOIN dept d
ON e.deptno=d.deptno
WHERE d.loc = 'DALLAS'




子查询
子查询是一条查询语句，它是嵌套在其他
SQL语句中的，目的是将查询的结果提供
给外层的SQL语句。


查看谁的工资高于CLARK的工资?
SELECT ename,sal FROM emp
WHERE sal>(SELECT sal FROM emp
           WHERE ename='CLARK')


查看与CLARK同部门的员工?
SELECT ename,deptno
FROM emp
WHERE deptno=(SELECT deptno FROM emp
              WHERE ename='CLARK')
AND ename<>'CLARK'  
  
查看高于公司平均工资的员工?  
SELECT ename,sal
FROM emp
WHERE sal>(SELECT AVG(sal) 
           FROM emp)  
  


在DDL中使用子查询，可以将一个子查询
的结果集当做表快速创建出来。
CREATE TABLE employee
AS
SELECT e.empno,e.ename,e.sal,
       e.job,d.deptno,d.dname,
       d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno(+)


在使用子查询创建表的时候，查询多少个字段
创建的表就有多少个字段，字段名与查询的字段
名字一致(不含有表别名)，类型，长度也一致。
但是需要注意的是，若查询的字段是函数或者
表达式，那么这个字段必须指定别名，并且生成
的表对应的该字段名就是这个别名。




DML中使用子查询
将CLARK所在部门的员工工资上浮10%
UPDATE emp
SET sal=sal*1.1
WHERE deptno=(SELECT deptno FROM emp
              WHERE ename='CLARK')




子查询根据查询结果分为:
单行单列，多行单列，多行多列子查询
其中单列子查询常用在过滤条件中
多列子查询常被当做表使用。


对于多行单列子查询在进行过滤判断
时，需要配合IN,ANY,ALL使用。


查看与职位是SALESMAN同部门的员工?
SELECT ename,job,deptno
FROM emp
WHERE deptno IN (SELECT deptno FROM emp
                 WHERE job='SALESMAN')
AND job<>'SALESMAN'


查看比职位是SALESMAN和CLERK工资都高的员工?
SELECT ename,sal,job
FROM emp
WHERE sal>ALL(SELECT sal FROM emp
              WHERE job IN('SALESMAN','CLERK'))


SELECT deptno, dname FROM dept d


EXISTS关键字
EXISTS用在WHERE中进行过滤，其后跟一个子查询，
只要该子查询至少可以查询出一条记录，那么则该
条件为true.


查看有下属的员工?
SELECT m.ename,m.job,m.sal
FROM emp m
WHERE EXISTS(
  SELECT * FROM emp e
  WHERE e.mgr = m.empno
)


HAVING中使用子查询:
查看每个部门的最低薪水，前提是要高于
30号部门的最低薪水?
SELECT MIN(sal),deptno FROM emp
GROUP BY deptno
HAVING MIN(sal)>(SELECT MIN(sal) FROM emp
                 WHERE deptno=30)




在FROM中使用子查询
通常多列子查询在FROM子句中出现，作用
是当做一张表使用。


查看高于自己所在部门平均工资的员工?
SELECT e.ename,e.sal,e.deptno
FROM emp e,(SELECT deptno,
                   AVG(sal) avg_sal
            FROM emp
            GROUP BY deptno) x
WHERE e.deptno=x.deptno
AND e.sal>x.avg_sal






分页查询
分页查询就是将表中要查询出来的数据
分批分段查询出来。这样做的好处是当
查询的数据量过大时可以减少内存开销，
提高系统响应速度，减少网络传输。
分页是方言，不同数据库都提供了分页
查询的机制，但是语法定义各不相同。


ORACLE是依靠提供了一个伪列:ROWNUM
ROWNUM不存在于任何一张表中，但是每张
表都可以查询该字段，该字段在结果集中的
值就是每条记录的行号。而行号的生成是
伴随查询过程进行的，只要可以从表中查询
出一条记录，ROWNUM字段就是为该条记录
在结果集中产生对应的行号，从1开始递增。


需要注意，在使用ROWNUM对结果集编行号
的过程中，不要使用ROWNUM做大于1以上数字
的判断，否则得不到结果。
SELECT ROWNUM,ename,job,sal
FROM emp
WHERE ROWNUM >1




SELECT *
FROM(SELECT ROWNUM rn,ename,job,sal
     FROM emp)
WHERE rn BETWEEN 6 AND 10




查看公司工资排名的第6-10名
SELECT *
FROM(SELECT ROWNUM rn,t.*
     FROM(SELECT ename,job,sal
          FROM emp
          ORDER BY sal DESC) t)
WHERE rn BETWEEN 6 AND 10




SELECT *
FROM(SELECT ROWNUM rn,t.*
     FROM(SELECT ename,job,sal
          FROM emp
          ORDER BY sal DESC) t
     WHERE ROWNUM <=10     
    )
WHERE rn >= 6


换算范围的公式:
PageSize:每页可以显示的条目数
Page:页数


start:(Page-1)*PageSize + 1
end:PageSize*Page




DECODE函数
DECODE函数可以实现分支结构


SELECT ename, job, sal,
     DECODE(job,  
            'MANAGER', sal * 1.2,
            'ANALYST', sal * 1.1,
            'SALESMAN', sal * 1.05,
            sal
     ) bonus
FROM emp;




DECODE在GROUP BY中可以实现将字段不同
值的记录分为一组。
将职位是ANALYST与MANAGER看做一组，其他
职位看做另一组，分别统计人数?


SELECT 
 COUNT(*),DECODE(job,
                  'MANAGER','VIP',
                  'ANALYST','VIP',
                  'OTHER') 
FROM emp
GROUP BY DECODE(job,
               'MANAGER','VIP',
               'ANALYST','VIP',
               'OTHER') 




排序函数
排序函数可以将结果集按照指定的字段分组
然后组内再按照指定的字段排序后对每组的
记录产生一个行号。


ROW_NUMBER():生成组内连续且唯一的数字。


查看每个部门的工资排名?
SELECT 
 ename,job,sal,deptno,
 ROW_NUMBER() OVER(
  PARTITION BY deptno
  ORDER BY sal DESC
 ) rank
FROM emp


RANK():生成组内不连续也不唯一的数字
SELECT 
 ename,job,sal,deptno,
 RANK() OVER(
  PARTITION BY deptno
  ORDER BY sal DESC
 ) rank
FROM emp




DENSE_RANK():生成组内连续但不唯一的数字
SELECT 
 ename,job,sal,deptno,
 DENSE_RANK() OVER(
  PARTITION BY deptno
  ORDER BY sal DESC
 ) rank
FROM emp






高级分组函数


查看每天的营业额?
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id,day_id
ORDER BY year_id,month_id,day_id


查看每月的营业额?
SELECT year_id,month_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id
ORDER BY year_id,month_id


查看每年的营业额?
SELECT year_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id
ORDER BY year_id




查看总共的营业额?
SELECT SUM(sales_value)
FROM sales_tab




在一个结果集中看到每天，每月，每年以及总共的营业额?
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id,day_id
UNION ALL
SELECT year_id,month_id,NULL,SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id
UNION ALL
SELECT year_id,NULL,NULL,SUM(sales_value)
FROM sales_tab
GROUP BY year_id
UNION ALL
SELECT NULL,NULL,NULL,SUM(sales_value)
FROM sales_tab




ROLLUP()函数
ROLLUP中的每一个参数就是需要分组的字段。
ROLLUP会将参数字段逐个递减并分组统计直
至一个字段都没有，然后将这些分组统计的结
果并在一个结果集显示


GROUP BY ROLLUP(a,b,c)
等价:
GROUP BY a,b,c
UNION ALL 
GROUP BY a,b
UNION ALL 
GROUP BY a
UNION ALL
全表


在一个结果集中看到每天，每月，每年以及总共的营业额?
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY ROLLUP(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id




CUBE()函数，将每个参数的每种组合都进行
一次分组，然后将所有统计的结果并在一个
结果集显示，所以CUBE分组次数为2的参数
个数次方。
GROUP BY CUBE(a,b,c)
abc
ab
bc
ac
a
b
c
全表


SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY CUBE(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id






GROUPING SETS()
GROUPING SETS的每一个参数是一种分组方式
它会将这些分组统计的结果并在一个结果集显示。


查看每天与每月营业额?
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY GROUPING SETS(
  (year_id,month_id,day_id),
  (year_id,month_id)
)
ORDER BY year_id,month_id,day_id


GROUP BY year_id,month_id,day_id
GROUP BY year_id,month_id