OracleSql语句学习(四)

 子查询

①子查询是一条查询语句，它是嵌套在其他SQL语句当中的，作用是为外层的SQL提供数据的。子查询常用在查询语句中，当然也可以用作DML,DDL中.
　　查看与FORD同部门的员工？
　　　　SELECT ename,sal,deptno FROM emp_weiyiji WHERE deptno=(SELECT deptno FROM emp_weiyiji WHERE ename='FORD')先执行括号里面的
　　查看和SMITH同职位的都有谁？
　　　　SELECT ename,sal,job FROM emp_weiyiji WHERE job=(SELECT job FROM emp_weiyiji WHERE ename='SMITH')
　　查看比公司平均工资高的员工信息？
　　　　SELECT ename,sal,job,deptno FROM emp_weiyiji WHERE sal>(SELECT AVG(sal) FROM emp_weiyiji)

②在DDL中使用子查询,可以基于一个子查询的结果集当做表快速的创建出来。
　　创建employee_weiyiji表字段：empno,ename,mgr,sal,job,deptno,dname,dloc
　　　　CREATE TABLE employee_weiyiji AS
　　　　SELECT e.empno,e.ename,e.mgr,e.sal,e.job,d.deptno,d.dname,d.loc FROM emp_weiyiji e LEFT OUTER JOIN dept_weiyiji d ON e.deptno=d.deptno
③子查询根据查询结果不同分为:
单行单列，单行多列：常用在WHERE作为过滤条件
多行多列：常当做表看待
当判断的是一个多行单列子查询结果时，通常要配合IN,ANY,ALL使用。
　　查看与职位是SALESMAN同部门的其他职位员工？
　　　　SELECT ename,job,deptno FROM emp_weiyiji WHERE deptno IN(SELECT deptno FROM emp_weiyiji WHERE job='SALESMAN') AND job<>'SALESMAN'
　　查看比SALESMAN和CLERK职位工资都高的员工？
　　　　SELECT ename,sal deptno FROM emp_weiyiji WHERE sal>ALL(SELECT sal FROM emp_weiyiji WHERE job IN('SALESMAN','CLERK'))

 

EXISTS关键字

①EXISTS在WHERE中使用，后面跟一个子查询，子查询的结果不关注具体指，只关注是否能查询出数据，只要可以则条件成立。
　　查看有员工的部门信息？查看没有员工部门信息在EXISTS前面加NOT就行了
　　　　SELECT deptno,dname FROM dept_weiyiji d WHERE EXISTS(SELECT * FROM emp_weiyiji eWHERE d.deptno = e.deptno)
　　查询部门的最低薪水，前提是要高于30号部门的最低薪水？
　　　　SELECT MIN(sal),deptno FROM emp_weiyiji GROUP BY deptno HAVING MIN(sal)>(SELECT MIN(sal) FROM emp_weiyiji WHERE deptno=30)
②FROM子句中使用子查询，该子查询是当做一张表使用的。
　　查看比自己所在部门平均工资高的员工？
　　　　SELECT e.ename,e.sal,e.deptno  FROM emp_weiyiji e,(SELECT AVG(sal) avg_sal,deptno FROM emp_weiyiji GROUP BY deptno) t WHERE e.deptno=t.deptno AND e.sal>t.avg_sal

　　　　SELECT e.ename,e.sal,(SELECT d.dname FROM dept_weiyiji d WHERE d.deptno = e.deptno) dname FROM emp_weiyiji e

 

分页查询

①分页查询其实就是将数据分段查询出来，当数据量大的时候经常会使用分页查询技术，这样可以减少资源开销，提高系统响应速度。
②标准SQL中没有定义分页语法，所以不同的数据库的分页语法不一样。ORACLE中使用伪列ROWNUM实现ROWNUM是一个伪列，并不存在于任何表中，但是所有的表都可以查询该字段，而该字段的值就是查询出来的结果集中的每条记录的行号，从1开始自动递增。ROWNUM字段的值生成是伴随查询进行的过程中的。
每当可以从表中查询出一条记录，那么ROWNUM的值就是该条查询出来记录的行号。
　　SELECT ROWNUM ,ename,sal,deptno FROM emp_weiyiji WHERE ROWNUM >1
③由于ROWNNUM的值是查询过程中动态生成的，并从1开始所以在使用ROWNUM对结果集编号时不要使用ROWNUM
在WHERE中做>1以上数字的判断，否则查不到任何数据
　　SELECT * FROM(SELECT ROWNUM rn,ename,sal,deptno FROM emp_weiyiji) WHERE rn BETWEEN 6 AND 10
先排序，然后在标号，最后在取需要的部分
SELECT *FROM(SELECT ROWNUM rn,t.*
FROM(SELECT ename,sal,deptno FROM emp_weiyiji
ORDER BY sal DESC) t
WHERE ROWNUM<=10)
WHERE rn>=6

pageSize(每页显示的条目数):5
page(页数):2
start:(page-1)*pageSize+1
end:PageSize*page

 

DECODE函数

①可以实现简单的分支结构

SELECT ename, job ,sal,
DECODE(job,
'MANAGER',sal * 1.2,
'ANALYST',sal * 1.1,
'SALESMAN',sal* 1.05 
)bonus
FROM emp_weiyiji

②和DECODE函数功能相似的有CASE语句，实现类似于if

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
bouns FROM emp_weiyiji;

SELECT COUNT(1),DECODE(job,

'MANAGER','VIP',
'ANALYST','VIP',
'OTHER')
FROM emp_weiyiji
GROUP BY DECODE(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER')

SELECT deptno,dname,loc FROM dept_weiyiji
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_weiyiji

②RANK函数：组内不连续也不唯一的数字(排名4,4过了直接就是6)

查看每个部门的工资排名，若工资一致，排名一致

SELECT ename,sal,deptno,
RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank 
FROM emp_weiyiji;

③DENSE_RANK:生成组内连续但不唯一的数字(排名4,4过了直接就是5)

SELECT ename,sal,deptno,
DENSE_RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp_weiyiji


CREATE TABLE sales_weiyiji(
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_weiyiji
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;

查看并集，既是'MANAGER',工资又高于2500

SELECT ename,job,sal FROM emp_weiyiji WHERE job='MANAGER'
UNION
SELECT ename,job,sal FROM emp_weiyiji WHERE sal>2500;
全并集
SELECT ename,job,sal FROM emp_weiyiji  WHERE job='MANAGER'
UNION ALL
SELECT ename,job,sal FROM emp_weiyiji  WHERE sal>2500;

交集，保留即满足'MANAGER',工资又高于2500
　　SELECT ename,job,sal FROM emp_weiyiji WHERE job='MANAGER'
　　INTERSECT 
　　SELECT ename,job,sal FROM emp_weiyiji WHERE sal>2500
差集
　　SELECT ename,job,sal FROM emp_weiyiji WHERE job='MANAGER'
　　MINUS
　　SELECT ename,job,sal FROM emp_weiyiji WHERE sal>=2500

 

ROLLUP(高级分组函数)

ROLLUP

GROUP BY ROLLUP(a,b,c)
等价于
GROUP BY a,b,c
UNION ALL
GROUP BY a，b
UNION ALL
全表

SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_weiyiji GROUP 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_weiyiji GROUP BY  CUBE(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id 

GROUPING SETS函数
该函数允许自定义分组模式，然后将这些分组结果并在一个结果集显示。其中每个参数为一种分组模式。
　　查看每天与每月的营业额？
　　　　SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_weiyiji
　　　　GROUP BY GROUPING SETS((year_id,month_id,day_id),(year_id,month_id))