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+1END = pageSize*page;--decode函数可以实现分支的效果，如果不写后面那个‘sal’则默认NULLSELECT 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.05ELSE 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_valueFROM 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 ALLSELECT 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'MINUSSELECT 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 salesGROUP 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 salesGROUP BY grouping sets((year_id, month_id),(year_id, month_id, day_id))ORDER BY year_id,month_id,day_id;

如有问题欢迎留言！