【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_qxl
SELECT 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_value
FROM dual
CONNECT BY level<=1000;
select * from sales_tab_qxl;
--查看每天的销售额?
SELECT year_id,month_id,day_id,SUM(sales_values) value
FROM sales_tab_qxl
GROUP BY year_id,month_id,day_id
ORDER BY year_id,month_id,day_id;
--查看每月的销售额?
SELECT year_id,month_id,SUM(sales_values) value
FROM sales_tab_qxl
GROUP BY year_id,month_id
ORDER BY year_id,month_id;
--查看每年的销售额?
SELECT year_id,SUM(sales_values) value
FROM sales_tab_qxl
GROUP BY year_id
ORDER 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;