Oracle学习-day04

直接写例子

	SELECT *FROM emp;
--子查询，也是一种查询语句，只是它嵌套在其他SQL语句之中，作用是为外层的SQL提供数据
--查看比CLARK工资高的员工
	SELECT ename,sal FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='CLARK');
--谁与SMITH相同部门
	SELECT ename,deptno FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='SMITH');
	
--查看比公司平均工资高的员工
	SELECT ename,sal FROM emp WHERE sal>(SELECT avg(sal) FROM emp);

--子查询也可以在DDL和DML中使用  DDL中使用子查询：基于一个子查询的结果集快速创建一张表
--在哪个表后面加（+），就有null值的连接
	CREATE TABLE myemp AS SELECT  e.empno,e.ename,e.JOB,e.deptno,d.dname,d.loc 
		FROM emp e,dept d WHERE e.deptno=d.deptno(+);
		
		SELECT * FROM myemp;
--DML中使用子查询，将SMITH部门的所有员工工资提高10%
	UPDATE emp SET sal=sal*1.1 WHERE deptno=(SELECT deptno FROM emp WHERE ename='SMITH');
	
--查看与职位是SALESMAN相同部门的其他员工
	SELECT ename,JOB,deptno FROM emp WHERE deptno IN(SELECT deptno FROM emp WHERE JOB='SALESMAN')AND JOB<> 'SALESMAN';
	
--查看比职位是CLERK和SALESMAN工资都高的员工
	SELECT ename,sal FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE JOB IN('CLERK','SALESMAN'));
	SELECT ename,sal FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE JOB IN('CLERK','SALESMAN'));

--exists关键字，exists后面要跟一个子查询，当该子查询可以至少查询出一条数据，exists返回真，not exists这是相反的操作
--查看有员工的部门
	SELECT deptno,dname FROM dept d WHERE EXISTS(SELECT * FROM emp e WHERE d.deptno=e.deptno);
	
--查看没有员工的部门
	SELECT deptno,dname FROM dept d WHERE NOT EXISTS(SELECT * FROM emp e WHERE d.deptno=e.deptno);
	
--having中使用子查询：
--查看最低薪水高于30号部门最低薪水的部门
	SELECT deptno,MIN(sal) FROM emp GROUP BY deptno HAVING MIN(sal)>(SELECT MIN(sal) FROM emp WHERE deptno=30);
	
--多列子查询常被当作一张表看待而出现在from子句中
--查看高于自己部门平均工资的员工--子查询中的字段若是函数或者表达式，那么必须给别名。
	SELECT e.ename,e.sal,e.deptno FROM emp e,(SELECT avg(sal) avg_sal,deptno FROM emp GROUP BY deptno) t WHERE e.deptno=t.deptno AND e.sal>t.avg_sal;
	
--select子句中也可以使用子查询：
	SELECT e.ename,e.sal,(SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) dname FROM emp e;
	
--分页查询，是将查询的数据分段显示，这样做的目的是可以减少资源占用，提高相应速度。分页在不同的数据库中的SQL语句不一样的（方言）。
--ORACLE中的分页依靠伪列：ROWNUM实现的，ROWNUM不存在任何表中，但是任何表都可以查询该列，该列的值随着查询数据的过程中生成，只要可以查询处一条记录，那么ROWNUM字段的值就是该记录的行号，从1开始。
	SELECT ROWNUM,e.ename,e.sal,e.deptno FROM emp e;
	
--使用rownum对结果集进行编号的过程中不要使用rownum做>1以上的过滤判断，否则结果集得不到任何记录
	SELECT e.ename, e.sal, e.deptno FROM emp e WHERE ROWNUM BETWEEN 6 AND 10;
	
--查看6到10的数据
	SELECT * FROM (SELECT ROWNUM rn,e.ename,e.sal,e.deptno FROM emp e) WHERE rn BETWEEN 6 AND 10;
	
--查看公司工资排名的第六到第十名(由于排序在查询语句中执行顺序是最低的，所以，在分页中若有排序需要求时，应当最先排序，然后根据排序的结果分页查询)
	SELECT * FROM (SELECT ROWNUM rn,t.* FROM (SELECT ename,sal,deptno FROM emp ORDER BY sal DESC) t)WHERE rn BETWEEN 6 AND 10;
--提倡这种写法	
	SELECT * FROM (SELECT ROWNUM rn,t.* FROM (SELECT ename,sal,deptno FROM emp ORDER BY sal DESC) t WHERE ROWNUM<=10)WHERE rn>=6;
	
--根据页数以及每页显示的条目数来求范围的公式：
--pageSize：5  每页显示的条目数					page：1 页数
	--START = (page-1)*pageSize+1				END = pageSize*page;

--decode函数可以实现分支的效果，如果不写后面那个‘sal’则默认NULL
	SELECT ename,JOB,sal,DECODE(JOB,'MANAGER',sal*1.2,'ANALYST',sal*1.1,'SALESMAN',sal*1.05,sal) bonus FROM emp_yxc;

--对不同职位进行不同的加薪
	SELECT ename,JOB,sal,CASE JOB WHEN 'MANAGER' THEN sal*1.2 
															  WHEN 'ANALYST' THEN sal*1.1 
																WHEN'SALESMAN' THEN sal*1.05
																ELSE sal END 
												bonus FROM emp;
	
--可以利用decode将字段值不一样的记录看做同一组，只需将看作一组的记录该字段值替换为一个相同值即可
	select count(*),decode(job,'MANAGER','VIP','ANALYST','VIP','OTHER') decode from emp group by decode(JOB,'MANAGER','VIP','ANALYST','VIP','OTHER');

--按照自定的顺序排序
	SELECT deptno,dname,loc FROM dept ORDER 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) rank FROM emp;

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

--dense_rank:生成组内连续但不唯一的数字
	SELECT ename,sal,deptno,DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) rank FROM emp;
	
	CREATE TABLE sales(
		year_id NUMBER NOT NULL,
		month_id NUMBER NOT NULL,
		day_id NUMBER NOT NULL,
		sales_value NUMBER(10,2) NOT NULL
	);
	
	drop table sales;
	
	INSERT INTO sales 
		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 day_id,
					 round(dbms_random.VALUE(1,100),2) AS sales_value
		FROM dual CONNECT BY LEVEL<=1000;

	SELECT ename,JOB,sal FROM emp WHERE JOB='MANAGER' 
	UNION 
	SELECT ename,JOB,sal FROM emp WHERE sal>2500;
	
	SELECT ename,JOB,sal FROM emp WHERE JOB='MANAGER'
	UNION ALL
	SELECT ename,JOB,sal FROM emp WHERE sal>2500;
	
	SELECT ename,JOB,sal FROM emp WHERE JOB='MANAGER'OR sal>2500;
--差集
	SELECT ename,JOB,sal FROM emp WHERE JOB='MANAGER'
	MINUS
	SELECT ename,JOB,sal FROM emp WHERE sal>2500;

--高级分组函数可以按照该函数要求的分组方式对数据进行分组统计，然后将这些分组方式统计的结果到一个结果集中显示，
--相比我们按照不同分组方式分别统计结果后再使用union all并在一起，书写起来要简便的多。
	SELECT year_id,month_id,day_id,sum(sales_value) FROM sales 
	GROUP BY ROLLUP(year_id,month_id,day_id)
	order by year_id,month_id,day_id;
	
	SELECT year_id,month_id,day_id,sum(sales_value) FROM sales
	GROUP BY cube(year_id, month_id, day_id)
	ORDER BY year_id,month_id,day_id;
	
--grouping sets() 每个参数是一种分组方式，然后这些分组统计的结果并在一个结果集显示，
--由于分组方式可以通过参数传入，所以相比rollup，cube的内定分组方式要灵活
--查看每月与每天的营业额
	SELECT year_id,month_id,day_id,sum(sales_value) FROM sales
	GROUP BY grouping sets((year_id, month_id),(year_id, month_id, day_id))
	ORDER BY year_id,month_id,day_id;

	

如有问题欢迎留言！