--子查询
--子查询是一条查询语句,它是嵌套在其他SQL语句中的。
--目的是将查询的结果提供给外层SQL语句。
--查询谁的工资高于CLARK的工资?
SELECT ename,sal
FROM emp_gl
WHERE sal>(SELECT sal FROM emp_gl
WHERE ename='CLARK')
--谁和CLARK同部门?
SELECT ename,deptno
FROM emp_gl
WHERE deptno=(SELECT deptno FROM emp_gl
WHERE ename='CLARK')
--查看高于公司平均工资的员工
SELECT ename,sal
FROM emp_gl
WHERE sal>(SELECT AVG(sal) FROM emp_gl)
--在DDL中使用子查询,可以将一个子查询的结果集当做表快速创建出来。
CREATE TABLE employee_gl AS
SELECT e.empno,e.ename,e.sal,e.job,d.deptno,d.dname,d.loc
FROM emp_gl e,dept_gl d
WHERE e.deptno=d.deptno
DESC employee_gl
SELECT * FROM employee_gl
--在使用子查询创建表的时候,查询多少个字段创建的表就有
--多少个字段,字段名与查询的字段名字一致(不含有表别名)
--类型,长度也一致。但是需要注意的是,若查询的字段是函数
--或者表达式,那么这个字段必须指定别名,并且生成的表对应
--的该字段名就是这个别名。
--DML中使用子查询
--将CLARK所在部门的员工工资上浮10%
UPDATE emp
SET sal=sal*1.1
WHERE deptno=(SELECT deptno FROM emp_gl WHERE ename='CLARK')
SELECT ename,sal
FROM emp_gl
WHERE deptno=10
--子查询根据查询结果分为:
--单行单列,多行单列,多行多列子查询
--其中单列子查询常用在过滤条件中,多列子查询常被当作表使用
--对于多行单列子查询在进行过滤判断时,需要配合IN,ANY,ALL使用
--查看与职位是SALESMAN同部门的员工?
SELECT ename,job ,deptno
FROM emp_gl
WHERE deptno IN(SELECT deptno FROM emp_gl
WHERE job='SALESMAN')
--查看比职位是SALESMAN和CLERK 工资都高的员工?
SELECT ename,sal,job
FROM emp_gl
WHERE sal>ALL(SELECT sal FROM emp_gl
WHERE job IN('SALESMAN','CLERK'))
SELECT deptno,dname,loc
FROM dept_gl d
WHERE EXISTS(SELECT * FROM emp_gl e
WHERE e.deptno=d.deptno)
--EXISTS关键字
--EXISTS用在WHERE中进行过滤,其后根一个子查询
--查看有下属的员工?
SELECT m.ename,m.sal,m.job
FROM emp_gl m
WHERE EXISTS(
SELECT * FROM emp_gl e
WHERE e.mgr=m.empno
)
--HAVING使用子查询:
--查看每个部门的最低薪水,前提是要高于30号部门的最低薪水
SELECT MIN(sal),deptno
FROM emp_gl
GROUP BY deptno HAVING MIN(sal)>(
SELECT MIN(sal) FROM emp_gl
WHERE deptno=30
)
--在FROM中使用子查询
--通常多列子查询在FROM子句中出现,作用是当做一张表使用
--查看高于自己所在部门平均工资的员工?
SELECT e.ename,e.sal,e.deptno
FROM emp_gl e,(SELECT deptno,AVG(sal) avg_sal
FROM emp_gl
GROUP BY deptno) x
WHERE e.deptno=x.deptno
AND e.sal>x.avg_sal
--子查询在SELECT部分
SELECT e.ename,e.sal,(
SELECT d.dname FROM dept_gl d
WHERE d.deptno=e.deptno
) dname
FROM emp_gl e
--分页查询
--分页查询就是将表中要查询出来的数据分批分段查询出来
--这样做的好处就是当查询的数据量过大时可以减少内存开
--销,提高系统响应速度,减少网络传输。
--分页是方言,不同数据库都提供了分页查询的机制,但是
--语法定义各不相同。
--ORACLE是依靠提供了一个伪列:ROWNUM
--ROWNUM不存在于任何一张表中,但是每张表都可以查询
--该字段,该字段在结果集中的值就是每条记录的行号。
--而行号的生成是伴随查询过程进行的,只要可以从表中查
--询出一条记录,ROWNUM字段就是为该条记录在结果集中
--产生对应的行号,从1开始递增。
SELECT ROWNUM,ename,job,sal
FROM emp_gl
SELECT ROWNUM,deptno,dname,loc
FROM dept_gl
--需要注意,在使用ROWNUM对结果集编行号的过程中,不要
--使用ROWNUM做大于1以上数字的判断,否则得不到结果。
SELECT *
FROM (SELECT ROWNUM row_num,ename,job,sal
FROM emp_gl) e
WHERE e.row_num BETWEEN 6 AND 10
--查看工资排名的第6-10个员工
SELECT *
FROM (SELECT ROWNUM rn,ename,job,sal
FROM(SELECT ename,job,sal
FROM emp_gl
ORDER BY sal DESC
)
WHERE ROWNUM <=10
)
WHERE rn >=6
--换算范围的公式:
--PageSize:每页可以显示的条目数
--Page:页数
--start:(Page-1)*PageSize+1
--end:PageSize*Page
--DECODE 函数
--DECODE 函数可以实现分支结构
SELECT ename,sal,job ,
DECODE(job,
'MANAGER',sal*1.2,
'ANALYST',sal*1.1,
'SALESMAN',sal*1.05,
sal) bonus
FROM emp_gl
--CASE 语句
SELECT ename,sal,job,
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_gl
--DECODE 在GROUP BY中可以实现将字段不同的值的记录分为一组。
SELECT COUNT(*),DECODE(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER')
FROM emp_gl
GROUP BY DECODE(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER')
SELECT deptno,dname,loc
FROM dept_gl
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_gl
--RANK():生成组内不连续不唯一的数字
SELECT ename,sal,deptno,
RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp_gl
--DENSE_RANK():生成组内连续但不唯一的数字
SELECT ename,sal,deptno,
DENSE_RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp_gl
-----------------------------------------------------
CREATE TABLE sales_tab_gl(
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_tab_gl
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))AS sales_value
FROM dual
CONNECT BY level<=1000;
DROP TABLE sales_tab_gl
-----------------------------------------------------
--并集
SELECT ename,job,sal FROM emp_gl
WHERE job ='MANAGER'
UNION
SELECT ename,job,sal FROM emp_gl
WHERE sal>2500
--全并集
SELECT ename,job,sal FROM emp_gl
WHERE job ='MANAGER'
UNION ALL
SELECT ename,job,sal FROM emp_gl
WHERE sal>2500
--交集
SELECT ename,job,sal FROM emp_gl
WHERE job='MANAGER'
INTERSECT
SELECT ename,job,sal FROM emp_gl
WHERE sal>2500
--差集
SELECT ename,job,sal FROM emp_gl
WHERE job='MANAGER'
MINUS
SELECT ename,job,sal FROM emp_gl
WHERE sal>=2500
--高级分组函数
--ROLLUP () 函数
--ROLLUP中的每一个参数就是需要分组的字段
--ROLLUP会将参数字段逐个递减直至一个没有
--然后将这些分组统计的结果并在一个结果集中显示
--GROUP BY ROLL(a,b,c)
--等价于:
--GROUP BY a,b,c
--UNION ALL
--GROUP BY a,b
--UNION ALL
--GROUP BY a
--UNION ALL
--全表
--在一个结果集中看到每天,每月,每年以及总共的营业额
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab_gl
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_tab_gl
GROUP BY CUBE(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id
--GROUPING SETS()
--GROUPING SETS的每一个参数是一种分组方式
--它会将这些分组统计的结果并在一个结果集中显示。
--查看每天与每月营业额?
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab_gl
GROUP BY GROUPING SETS(
(year_id,month_id,day_id),
(year_id,month_id)
)
ORDER BY year_id,month_id,day_id
--1:查看与CLARK相同职位的员工
SELECT ename,job,deptno
FROM emp_gl
WHERE job=(SELECT job FROM emp_gl WHERE ename='CLARK');
--2:查看低于公司平均工资的员工
SELECT ename,sal
FROM emp_gl
WHERE sal<(SELECT AVG(sal) FROM emp_gl);
--3:查看与ALLEN同部门的员工
SELECT ename,deptno
FROM emp_gl
WHERE deptno=(SELECT deptno FROM emp_gl WHERE ename='ALLEN');
--4:查看平均工资低于20号部门平均工资的部门平均工资
SELECT AVG(SAL),deptno
FROM emp_gl
GROUP BY deptno HAVING AVG(sal)<(
SELECT AVG(sal) FROM emp_gl WHERE deptno=20);
--5:查看低于自己所在部门平均工资的员工
SELECT e.ename,e.sal,e.deptno
FROM emp_gl e,(SELECT AVG(sal) avg_sal,deptno FROM emp_gl GROUP BY deptno) d
WHERE e.deptno=d.deptno
AND e.sal