本文共 5565 字,大约阅读时间需要 18 分钟。
【Oracle】day04_子查询_分页查询_DECODE_排序_集合操作_高级分组
1.子查询
子查询的作用是为其他SQL语句提供数据,以便其他SQL可以根据该数据进行操作. 子查询可以嵌套在DQL,DML,DDL中使用 最常见的地方是在DQL当中使用. SELECT ename,sal FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='WARD'); SELECT ename,deptno FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='JONES'); 1)DDL中使用子查询 CREATE TABLE newemp_qxl AS SELECT e.ename,e.job,e.deptno,d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno; desc newemp_qxl; SELECT * from newemp_qxl; 2)DML中使用子查询 DElETE FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE UPPER(ename)=('MILLER')); update emp set sal=sal*1.2 where deptno=(select deptno from emp where UPPER(ename)=('KING')); select * from emp; select ename,sal from emp where sal>(select avg(sal) from emp); SELECT ename,deptno,job FROM emp WHERE deptno in(SELECT deptno from emp WHERE job='SALESMAN') AND job<>'SALESMAN'; SELECT ename,sal FROM emp WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=20); 3)EXISTS关键字 用在WHERE子句中,其后要跟一个子查询,而EXISTS是否返回为TRUE取决于后面的子查询是否能查到数据. 在EXISTS前面还可以加上NOT. SELECT d.deptno,d.dname FROM dept d WHERE not exists( select * from emp e where d.deptno=e.deptno ); select min(sal),deptno from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=30); 4)多列子查询,通常当作一张表看待,定义在外层查询的FROM子句中使用. --查询出薪水比本部门平均薪水高的员工信息 select e.ename,e.sal from emp e,(SELECT deptno,AVG(sal) avg_sal FROM emp group by deptno) t where e.sal>t.avg_sal AND e.deptno=t.deptno; 在SELECT子句中也能使用子查询,是外连接的一种写法 子查询的结果必须是单行单列. SELECT e.ename,e.sal,e.deptno, (SELECT d.dname FROM dept d WHERE d.deptno=e.deptno) dname FROM emp e; 2.分页查询
分页的目的是,当数据量过大时,若一次性全部查询出来,会对系统资源造成不必要开销,而且处理速度会变慢,为此我们可以分段式的将数据一点一点的取出.分页的语句SQL没有定义,所以不同的数据库分页语句不一样. 1)ROWNUM:伪列,并不是表中的一个真实字段,但是在任何表的查询中都可以使用该列,该列的值为结果集每一条记录编一个行号,只要可以查询出一条记录该字段的值就会+1,行号从1开始. SELECT ROWNUM,ename,sal FROM emp; 由于只有从表中查询出一条数据,ROWNUM才会使用1进行编号,然后其自动涨为2.所以,不要在第一次查询数据数据使用ROWNUM编号的时候在WHERE中做大于1以上的数字判断,否则不会查出任何数据. select rownum,ename,sal from emp where rownum>1; --无任何数据被查出. (ROENUM一开始不大于1,所以WHERE不满足条件,不满足WHERE条件就查询不出数据,查询不出数ROWNUM就不会涨,不涨就不大于1,所以WHERE不满足条件.....) SELECT * FROM (select ROWNUM rn,ename,sal,job,deptno FROM emp) t WHERE t.rn BETWEEN 6 AND 10; 2)分页的同时还有排序的需求时: 查看公司中工资排名的6-10位的员工信息? 由于使用ROWNUM编号是在查询的过程中完成的,而排序是在查询出数据之后进行的,所以这就导致排序后之前的编号就失去意义了. 下面并不会得到实际想要的数据. SELECT * FROM( SELECT ROWNUM rn,ename,sal,deptno FROM emp ORDER BY sal DESC ) t WHERE t.rn BETWEEN 6 AND 10; 所以若有排序需求,分页需要三次查询才能实现:1-排序 2-编号 3-取范围 SELECT * FROM( SELECT ROWNUM rn,t.* FROM( SELECT ename,sal FROM emp ORDER BY sal DESC ) t ) WHERE rn BETWEEN 6 AND 10; 分页时,BETWEEN中两个数字的公式: page:页数 pagesize:每页显示的条数 根据上面两个值计算: start:(page-1)*pagesize+1 end:page*pagesize 3.DECODE函数
SELECT ename,sal,job, DECODE( job, 'MANAGER',sal*1.2, 'SALESMAN',sal*1.1, 'ANALYST',sal*1.05, sal )bonus FROM emp; 2)CASE语句 SELECT ename,sal,job, CASE job WHEN 'MANAGER' THEN sal*1.2 WHEN 'SALESMAN'THEN sal*1.1 WHEN 'ANALYST'THEN sal*1.05 ELSE sal END bonus FROM emp; 3)GROUP BY 中使用DECODE函数 可以将字段值不同的记录看成一组,只要使用DECODE将需要划分一组的不同值转换为相同值即可. SELECT COUNT(*) job_cnt,DECODE(job,'MANAGER','VIP','ANALYST','VIP',job) job_name FROM emp GROUP BY DECODE(job,'MANAGER','VIP','ANALYST','VIP',job); 4)ORDER BY 中使用DECODE SELECT deptno,dname,loc FROM dept ORDER BY DECODE(dname, 'OPERATIONS',1, 'ACCOUNTING',2); 4.排序
1)ROW_NUMBER函数,可以根据指定的字段分组,再根据指定的字段排序,然后生成组内连续且唯一的数字. --查看每个部门的工资排名情况? SELECT ename,sal,deptno, ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC)rank FROM emp; 2)RANK函数:生成组内不连续不唯一的数字 SELECT ename,sal,deptno, RANK() OVER(PARTITION BY deptno ORDER BY sal DESC)rank FROM emp; 3)DENSE_RANK函数:生成组内连续但不唯一的数字 SELECT ename,sal,deptno, DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC)rank FROM emp_qxl; 5.集合操作
1)UNION 并集 SELECT ename,job,sal FROM emp WHERE job='MANAGER' UNION SELECT ename,job,sal FROM emp WHERE sal>2500; 2)INTERSECT 交集 SELECT ename,job,sal FROM emp WHERE job='MANAGER' INTERSECT SELECT ename,job,sal FROM emp WHERE sal>2500; 3)MINUS 差集 SELECT ename,job,sal FROM emp WHERE job='MANAGER' MINUS SELECT ename,job,sal FROM emp WHERE sal>4000; 6.高级分组
--准备数据CREATE TABLE sales_tab_qxl( year_id NUMBER NOT NULL, month_id NUMBER NOT NULL, day_id NUMBER NOT NULL, sales_values NUMBER(10,2) NOT NULL);DESC sales_tab_qxl;INSERT INTO sales_tab_qxlSELECT 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 dayr_id, ROUND(DBMS_RANDOM.value(1,100),2) AS sales_valueFROM dualCONNECT BY level<=1000;select * from sales_tab_qxl;--查看每天的销售额?SELECT year_id,month_id,day_id,SUM(sales_values) valueFROM sales_tab_qxlGROUP BY year_id,month_id,day_idORDER BY year_id,month_id,day_id;--查看每月的销售额?SELECT year_id,month_id,SUM(sales_values) valueFROM sales_tab_qxlGROUP BY year_id,month_idORDER BY year_id,month_id;--查看每年的销售额?SELECT year_id,SUM(sales_values) valueFROM sales_tab_qxlGROUP BY year_idORDER BY year_id;
1)ROLLUP 高级分组函数 ROLLUP函数用在GROUP BY子句中,ROLLUP函数中可以传入若干参数,作用是参数逐个递减,每次进行一回分组并统计结果,然后将这些结果并在一起显示. SELECT year_id,month_id,day_id,SUM(sales_values) value FROM sales_tab_qxl GROUP BY ROLLUP(year_id,month_id,day_id) ORDER BY year_id,month_id,day_id; 2)CUBE() 高级分组函数 会将给定的字段的每一种组合都进行一次分组,然后将所有结果并在一起显示.分组的次数是2的参数个数次方. SELECT year_id,month_id,day_id,SUM(sales_values) value FROM sales_tab_qxl GROUP BY CUBE(year_id,month_id,day_id) ORDER BY year_id,month_id,day_id; 3)GROUPING SETS 高级分组函数 可以按照给定的分组方式进行分组,然后结果并在一起显示. 每个参数表示一种分组方式,参数可以使用括号将要分组的字段组合起来表示一种. SELECT year_id,month_id,day_id,SUM(sales_values)value FROM sales_tab_qxl GROUP BY GROUPING SETS( (year_id,month_id,day_id), (year_id,month_id) ) ORDER BY year_id,month_id,day_id; 转载地址:http://ngews.baihongyu.com/