笔记：ORACLE数据库基础学习 第四天

--子查询--子查询是一条查询语句，它是嵌套在其他SQL语句中的。--目的是将查询的结果提供给外层SQL语句。--查询谁的工资高于CLARK的工资？SELECT ename,salFROM emp_glWHERE sal>(SELECT sal FROM emp_gl            WHERE ename='CLARK')--谁和CLARK同部门？SELECT ename,deptnoFROM emp_glWHERE deptno=(SELECT deptno FROM emp_gl              WHERE ename='CLARK')              --查看高于公司平均工资的员工SELECT ename,salFROM emp_glWHERE sal>(SELECT AVG(sal) FROM emp_gl)--在DDL中使用子查询，可以将一个子查询的结果集当做表快速创建出来。CREATE TABLE employee_gl AS  SELECT e.empno,e.ename,e.sal,e.job,d.deptno,d.dname,d.loc  FROM emp_gl e,dept_gl d  WHERE e.deptno=d.deptnoDESC employee_glSELECT * FROM employee_gl--在使用子查询创建表的时候，查询多少个字段创建的表就有--多少个字段，字段名与查询的字段名字一致（不含有表别名）--类型，长度也一致。但是需要注意的是，若查询的字段是函数--或者表达式，那么这个字段必须指定别名，并且生成的表对应--的该字段名就是这个别名。--DML中使用子查询--将CLARK所在部门的员工工资上浮10%UPDATE empSET sal=sal*1.1WHERE deptno=(SELECT deptno FROM emp_gl WHERE ename='CLARK')SELECT ename,salFROM emp_glWHERE deptno=10--子查询根据查询结果分为：--单行单列，多行单列，多行多列子查询--其中单列子查询常用在过滤条件中，多列子查询常被当作表使用--对于多行单列子查询在进行过滤判断时，需要配合IN，ANY，ALL使用--查看与职位是SALESMAN同部门的员工？SELECT ename,job ,deptnoFROM emp_glWHERE deptno IN(SELECT deptno FROM emp_gl                 WHERE job='SALESMAN')--查看比职位是SALESMAN和CLERK 工资都高的员工？SELECT ename,sal,jobFROM emp_glWHERE sal>ALL(SELECT sal FROM emp_gl              WHERE job IN('SALESMAN','CLERK'))              SELECT deptno,dname,locFROM dept_gl dWHERE EXISTS(SELECT * FROM emp_gl e             WHERE e.deptno=d.deptno)--EXISTS关键字--EXISTS用在WHERE中进行过滤，其后根一个子查询--查看有下属的员工？SELECT m.ename,m.sal,m.jobFROM emp_gl mWHERE EXISTS(  SELECT * FROM emp_gl e  WHERE e.mgr=m.empno)--HAVING使用子查询：--查看每个部门的最低薪水，前提是要高于30号部门的最低薪水SELECT MIN(sal),deptnoFROM emp_glGROUP BY deptno HAVING MIN(sal)>(  SELECT MIN(sal) FROM emp_gl  WHERE deptno=30)--在FROM中使用子查询--通常多列子查询在FROM子句中出现，作用是当做一张表使用--查看高于自己所在部门平均工资的员工？SELECT e.ename,e.sal,e.deptnoFROM emp_gl e,(SELECT deptno,AVG(sal) avg_sal               FROM emp_gl               GROUP BY deptno) xWHERE e.deptno=x.deptnoAND e.sal>x.avg_sal--子查询在SELECT部分SELECT e.ename,e.sal,(  SELECT d.dname FROM dept_gl d  WHERE d.deptno=e.deptno) dnameFROM emp_gl e--分页查询--分页查询就是将表中要查询出来的数据分批分段查询出来--这样做的好处就是当查询的数据量过大时可以减少内存开--销，提高系统响应速度，减少网络传输。--分页是方言，不同数据库都提供了分页查询的机制，但是--语法定义各不相同。--ORACLE是依靠提供了一个伪列：ROWNUM--ROWNUM不存在于任何一张表中，但是每张表都可以查询--该字段，该字段在结果集中的值就是每条记录的行号。--而行号的生成是伴随查询过程进行的，只要可以从表中查--询出一条记录，ROWNUM字段就是为该条记录在结果集中--产生对应的行号，从1开始递增。SELECT ROWNUM,ename,job,salFROM emp_glSELECT ROWNUM,deptno,dname,locFROM dept_gl--需要注意，在使用ROWNUM对结果集编行号的过程中，不要--使用ROWNUM做大于1以上数字的判断，否则得不到结果。SELECT *FROM (SELECT ROWNUM row_num,ename,job,sal      FROM emp_gl) eWHERE e.row_num BETWEEN 6 AND 10--查看工资排名的第6-10个员工SELECT * FROM (SELECT ROWNUM rn,ename,job,sal      FROM(SELECT ename,job,sal           FROM emp_gl           ORDER BY sal DESC           )      WHERE ROWNUM <=10     ) WHERE rn >=6--换算范围的公式：--PageSize:每页可以显示的条目数--Page：页数--start:(Page-1)*PageSize+1--end:PageSize*Page--DECODE 函数--DECODE 函数可以实现分支结构SELECT ename,sal,job ,       DECODE(job,              'MANAGER',sal*1.2,              'ANALYST',sal*1.1,              'SALESMAN',sal*1.05,               sal) bonusFROM emp_gl--CASE 语句SELECT ename,sal,job,       CASE job WHEN 'MANAGER' THEN sal*1.2                WHEN 'ANALYST' THEN sal*1.1                WHEN 'SALESMAN' THEN sal*1.05                ELSE sal END       bonusFROM emp_gl--DECODE 在GROUP BY中可以实现将字段不同的值的记录分为一组。SELECT COUNT(*),DECODE(job,                'MANAGER','VIP',                'ANALYST','VIP',                'OTHER') FROM emp_glGROUP BY DECODE(job,                'MANAGER','VIP',                'ANALYST','VIP',                'OTHER')SELECT deptno,dname,locFROM dept_glORDER BY DECODE(dname,'OPERATIONS',1,                      'ACCOUNTING',2,                      'SALES',3)--排序函数--排序函数可以将结果集按照指定的字段分组--然后组内再按照指定的字段排序后对每组的--记录产生一个行号。--ROW_NUMBER()：生成组内连续且唯一的数字--查看每个部门的工资排名？SELECT ename,sal,deptno,       ROW_NUMBER() OVER(       PARTITION BY deptno       ORDER BY sal DESC       ) RANKFROM emp_gl--RANK()：生成组内不连续不唯一的数字SELECT ename,sal,deptno,       RANK() OVER(       PARTITION BY deptno       ORDER BY sal DESC       ) rankFROM emp_gl--DENSE_RANK()：生成组内连续但不唯一的数字SELECT ename,sal,deptno,       DENSE_RANK() OVER(       PARTITION BY deptno       ORDER BY sal DESC       ) rankFROM emp_gl-----------------------------------------------------CREATE TABLE sales_tab_gl(  year_id NUMBER NOT NULL,  month_id NUMBER NOT NULL,  day_id NUMBER NOT NULL,  sales_value NUMBER(10,2)NOT NULL)INSERT INTO sales_tab_glSELECT TRUNC(DBMS_RANDOM.value(2010,2012))AS year_id,       TRUNC(DBMS_RANDOM.value(1,13))AS month_id,       TRUNC(DBMS_RANDOM.value(1,32))AS day_id,       ROUND(DBMS_RANDOM.value(1,100))AS sales_valueFROM  dualCONNECT BY level<=1000;DROP TABLE sales_tab_gl-------------------------------------------------------并集SELECT ename,job,sal FROM emp_glWHERE job ='MANAGER'UNIONSELECT ename,job,sal FROM emp_glWHERE sal>2500--全并集SELECT ename,job,sal FROM emp_glWHERE job ='MANAGER'UNION ALLSELECT ename,job,sal FROM emp_glWHERE sal>2500--交集SELECT ename,job,sal FROM emp_glWHERE job='MANAGER'INTERSECTSELECT ename,job,sal FROM emp_glWHERE sal>2500--差集SELECT ename,job,sal FROM emp_glWHERE job='MANAGER'MINUSSELECT ename,job,sal FROM emp_glWHERE sal>=2500--高级分组函数--ROLLUP () 函数--ROLLUP中的每一个参数就是需要分组的字段--ROLLUP会将参数字段逐个递减直至一个没有--然后将这些分组统计的结果并在一个结果集中显示--GROUP BY ROLL(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_glGROUP BY ROLLUP(year_id,month_id,day_id)ORDER BY year_id,month_id,day_id--CUBE()函数，将每个参数的每种组合都进行一次分组--然后将所有统计的结果并在一个结果集中显示，所以--CUBE分组次数为2的参数个数次方SELECT year_id,month_id,day_id,SUM(sales_value)FROM sales_tab_glGROUP 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_glGROUP BY GROUPING SETS((year_id,month_id,day_id),(year_id,month_id))ORDER BY year_id,month_id,day_id--1:查看与CLARK相同职位的员工SELECT ename,job,deptnoFROM emp_gl WHERE job=(SELECT job FROM emp_gl WHERE ename='CLARK');--2:查看低于公司平均工资的员工SELECT ename,salFROM emp_glWHERE sal<(SELECT AVG(sal) FROM emp_gl);--3:查看与ALLEN同部门的员工SELECT ename,deptnoFROM emp_glWHERE deptno=(SELECT deptno FROM emp_gl WHERE ename='ALLEN');--4:查看平均工资低于20号部门平均工资的部门平均工资SELECT AVG(SAL),deptnoFROM emp_glGROUP BY deptno HAVING AVG(sal)<(SELECT AVG(sal) FROM emp_gl WHERE deptno=20);--5:查看低于自己所在部门平均工资的员工SELECT e.ename,e.sal,e.deptnoFROM emp_gl e,(SELECT AVG(sal) avg_sal,deptno FROM emp_gl GROUP BY deptno) dWHERE e.deptno=d.deptnoAND e.sal