ORACLE数据库总结04

子查询(有函数要用别名)

子查询是嵌套在其他SQL语句之中的，为外层SQL语句提供数据。

 

查看谁的工资比CLARK高？

SELECT ename,sal

FROM emp_brown

WHERE sal>(SELECT sal FROM emp_brown WHERE ename='CLARK')

 

查看与JONES相同部门的员工

SELECT ename,deptno

FROM emp_brown

WHERE deptno=(SELECT　deptno FROM emp_brown WHERE ename='JONES')

 

 

 

 

DDL中使用子查询

可以给予一个查询结果集快速创建一张表

 

创建employee，表中的数据来源于emp表

要求的字段:

          empno,ename,sal,deptno,dname,loc

          

CREATE TABLE emp_b

AS

SELECT e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc

FROM emp_brown e JOIN dept_brown d

ON e.deptno=d.deptno(+)

 

或者

 

CREATE TABLE emp_b

AS

SELECT e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc

FROM emp_brown e ,dept_brown d

WHERE e.deptno=d.deptno(+)

 

select *from emp_b

 

当使用子查询创建表时，若SELECT中的字段含有函数或者表达式

必须指定别名

 

 

 

DML中使用子查询

将JONES部门所有人工资提高10%

UPDATE emp_b

SET sal=sal*1.1

WHERE deptno=(SELECT deptno

              FROM emp_b

              WHERE ename='JONES')

              

 

查找薪水比整个机构平均薪水高的员工：

SELECT deptno, ename, sal

FROM emp_brown e

WHERE sal>(SELECT AVG(sal) FROM emp_brown )

 

select * from emp_brown

select * from dept_brown

 

多行单列子查询要配合IN，ANY,ALL进行过滤(不能使用'=')

 

查询出部门中有SALESMAN但职位不是SALESMAN的员工的信息：

SELECT ename,job,deptno

FROM emp_brown

WHERE deptno IN (SELECT deptno FROM emp_brown WHERE job='SALESMAN')

AND job<>'SALESMAN'

 

查看比职位是CLERK与SALESMAN工资都高的员工--职位(WHERE子查询) 工资(WHERE)

SELECT ename,sal

FROM emp_brown

WHERE sal>ALL(SELECT sal FROM emp_brown WHERE job IN ('CLERK','SALESMAN'))

 

 

EXISTS关键字(前面可以加NOT)

EXISTS后面跟一个子查询，当该子查询可以查询出至少一条记录时返回真

 

哪些有员工的部门信息

SELECT deptno,dname 

FROM dept_brown d

WHERE EXISTS(SELECT * FROM emp_brown e

             WHERE d.deptno=e.deptno)

 

查看有下属的员工(先看领导再看下属)

SELECT m.ename,m.mgr,m.empno

FROM emp_brown m

WHERE EXISTS(SELECT * FROM emp_brown e

             WHERE e.mgr=m.empno)

 

查看公司部门的最低薪水,前提是(HAVING)该薪水要高于30号部门的最低薪水

 

SELECT MIN(sal)

FROM emp

WHERE deptno=30

 

SELECT MIN(sal),deptno

FROM emp

WHERE GROUP BY deptno

 

SELECT MIN(sal),deptno

FROM emp_brown

GROUP BY deptno

HAVING MIN(sal)>(SELECT MIN(sal)

                FROM emp_brown

                WHERE deptno=30)

                

 

 

 

FROM当中使用子查询，就是将子查询的结果集

当成一张表看待。

 

查看哪些员工比自己所在部门平均工资高？

1查看每个部门的平均工资

SELECT AVG(sal) avg_sal,deptno

FROM emp

GROUP BY deptno

 

假设有T表

SELECT e.ename,e.sal,e.deptno

FROM emp e,T t

WHERE e.deptno=t.deptno

AND e.sal>avg_sal

即：

SELECT e.ename,e.sal,e.deptno

FROM emp_brown e,(SELECT AVG(sal) avg_sal,deptno

                  FROM emp

                  GROUP BY deptno) t

WHERE e.deptno=t.deptno

AND e.sal>avg_sal

 

 

子查询在SELECT子句中的使用(类似左右外连接)

SELECT e.ename,e.sal,(SELECT d.dname 

                      FROM dept_brown d

                      WHERE d.deptno = e.deptno) dname

FROM emp_brown e                     

 

 

 

 

分页查询

 

当数据库中查询的数据量过大时，会使用分页查询，一次只查询出部分数据，

这样做可以降低系统资源消耗，提高数据库响应速度，减少网络传输等

 

分页查询标准SQL中没有为其定义语法，所以不用的数据库中分页语句写法不同

 

 

 

ROWNUM:伪列

ROWNUM不存在于任何表，但是任何表都可以查询该字段，

该字段的值随着查询结果集的产生而动态生成值。

当查询语句从表中查询出一条记录时，该记录的ROWNUM字段值从1开始，

每当查询出一条记录ROWNUM自增1

 

SELECT ROWNUM,ename,sal,deptno

FROM emp_brown

 

SELECT ROWNUM,ename,sal,deptno

FROM emp_brown

WHERE ROWNUM>6

在使用ROWNUM为结果集编号的查询过程中

不应使用ROWNUM做>1以上数字的判断为过滤条件，否则得不到任何结果。

 

查看公司工资排名的6-10名:

(ORDER BY 执行顺序靠后)

SELECT *

FROM(SELECT ROWNUM rn,ename,sal,deptno

     FROM emp_brown)

WHERE rn BETWEEN 6 AND 10

 

 

select * from emp_brown

(1.先排序)

SELECT ename,sal,deptno

FROM emp_brown

ORDER BY sal desc

(2.编号 3.取范围)

SELECT * 

FROM(SELECT ROWNUM rn,t.* 

     FROM(SELECT * FROM emp_brown 

         ORDER BY sal DESC)t)

WHERE rn BETWEEN 6 AND 10 

 

优化执行速度(2.编号步骤时，10以后的就不用编号了)

SELECT * 

FROM(SELECT ROWNUM rn,t.* 

     FROM(SELECT * FROM emp_brown 

         ORDER BY sal DESC)t

         WHERE ROWNUM <=10)

WHERE rn >= 6

 

 

 

使用子查询进行分页

 

每页显示的条目数:pageSize

页数:page

例如:

pageSize:5

page:2

start:(page-1)*pageSize+1

end=pageSize*page

 

 

 

DECODE函数

DECODE(expr, search1, result1[, search2, result2…][, default])

当第一个参数等于第二个参数值，函数返回第三个参数值，否则判断是否等于第四个参数值，

若相等则返回第五个参数值，以此类推。最后可以单独制定一个参数，当前面的所有判断都不成立时

返回最后单独的参数，若不制定，函数返回NULL。函数要求至少传入三个参数。

SELECT ename,job,sal,

      DECODE(job,

            'MANAGER',sal*1.2,

            'ANALYST',sal*1.1,

            'SALESMAN',sal*1.05,

            sal) bonus

FROM emp_brown

 

和DECODE函数功能相似的有CASE语句，实现类似于if-else的操作。

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_brown;

 

DECODE在GROUP BY中的应用

将MANAGER与ANALYST看做一组，其他职位看做另一组分别统计人数？

SELECT

    COUNT(*),DECODE(job,

                    'MANAGER','VIP',

                    'ANALYST','VIP',

                    'OTHER') job

FROM emp_brown

GROUP BY DECODE(job,

                    'MANAGER','VIP',

                    'ANALYST','VIP',

                    'OTHER');

 

 

DECODE函数也可以按字段内容排序，例如：Dept表中按“OPERATIONS”、“ACCOUNTING”、“SALES”排序

SELECT deptno, dname, loc

FROM dept_brown

ORDER BY 

      DECODE(dname, 'OPERATIONS',1,'ACCOUNTING',2,'SALES',3), loc;

      

 

 

      

排序函数

排序函数可以将结果集按照制定的字段分组

在按照制定的字段对组内记录排序，最终生成组内编号。

 

 

ROW_NUMBER

组内部排序后的顺序编号，组内连续且唯一。

表示根据col1分组，在分组内部根据col2排序。

ROW_NUMBER() OVER(

          PARTITION BY col1 ORDER BY col2)

 

查看每个部门的工资排名      

SELECT ename,sal,deptno,

ROW_NUMBER() OVER(

                  PARTITION BY deptno

                  ORDER BY sal DESC) rank

FROM emp_brown;

 

RANK

组内部排序后的顺序编号，组内不连续且不唯一。

SELECT ename,sal,deptno,

RANK() OVER(

            PARTITION BY deptno

            ORDER BY sal DESC) rank

FROM emp_brown;

 

 

 

 

DENSE_RANK

生成组内连续但不唯一的数字

SELECT ename,sal,deptno,

DENSE_RANK() OVER(

                  PARTITION BY deptno

                  ORDER BY sal DESC) rank

FROM emp_brown; 

 

 

 

集合操作

UNION,UNION ALL

并集(会去掉合并后重复的记录)

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'

INTERSECT

SELECT ename,job,sal From emp

WHERE sal>2500;

 

差集(在第一个结果集中存在，第二个结果集中不存在的。相当于2在1中的补集)

SELECT ename,job,sal From emp

WHERE job = 'MANAGER'

MINUS

SELECT ename,job,sal From emp

WHERE sal>2500;

 

 

 

高级分组函数

 

CREATE TABLE sales_brown (

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_brown

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;

 

desc sales_brown;

select * from sales_brown;

 

营业额按年月日分组排序

SELECT year_id,month_id,day_id,sales_value

FROM sales_brown

ORDER BY year_id,month_id,day_id;

 

每天的营业额？

SELECT year_id,month_id,day_id,SUM(sales_value)

FROM sales_brown

GROUP BY year_id,month_id,day_id

ORDER BY year_id,month_id,day_id;

 

每月的营业额？

SELECT year_id,month_id,SUM(sales_value)

FROM sales_brown

GROUP BY year_id,month_id

ORDER BY year_id,month_id;

 

每年的营业额？

SELECT year_id,SUM(sales_value)

FROM sales_brown

GROUP BY year_id

ORDER BY year_id;

 

总营业额？

SELECT SUM(sales_value)

FROM sales_brown

 

ROLLUP（）函数

GROUP BY ROLLUP(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_brown

GROUP BY ROLLUP(year_id,month_id,day_id)

ORDER BY year_id,month_id,day_id;

 

CUBE函数

CUBE会将每个参数的组合都进行一次分组

分组次数为2的参数个数次方

GROUP BY CUBE(a,b,c)

相当于:

a,b,c

a,b

a,c

b,c

a

b

c

全表

 

SELECT year_id,month_id,day_id,SUM(sales_value)

FROM sales_brown

GROUP BY CUBE(year_id,month_id,day_id)

ORDER BY year_id,month_id,day_id;

 

 

 

 

GROUPING SETS()函数

GROUPING SET的每一个参数是一个分组方式

该函数会将这些分组统计的结果并在一个结果集显示

 

只查看每天与每月营业额？

SELECT year_id,month_id,day_id,SUM(sales_value)

FROM sales_brown

GROUP BY GROUPING SETS(

                      (year_id,month_id,day_id),

                      (year_id,month_id)

)

ORDER BY year_id,month_id,day_id;