Oracle03—数据伪劣、分组查询、分组统计查询、子查询

数据伪列

 1.ROWNUM   行号      ---用于分页操作

   定义：实际上,在执行查询的过程中，系统会为每一个查询的结果结自动生成一个序列号,因此ROWNUM 是真实存在的，只不过是隐性的而已。

    也就是说每次查询，ROWNUM都是伴随着结果集的产生而隐性产生的，其和结果集z或者数据源是一一对应的。

    如果要进行中间几行查询的话，需要将数据源的ROWNUM实例化。

作用： 实现查询数据的部分显示以及数据分页

备注：1.数据伪列是用户不需要处理的列，而是由数据库ORACLE自行维护的数据列,

是真实存在的，是隐性的。

1. 查询行数据的时候，ROWNUM必须从1号开始，若要查询中间的一部分，

需要将结果集对应的ROWNUM变为实列。

 示例1：---ROWNUM对应的源表的问题

SELECT ROWNUM,EMPNO,ENAME,SAL FROM EMP;

SLEECT ROWNUM,EMPNO,ENAME,SAL FROM ENP ORDER BY SAL DESC;

 例题2：查询工资最高的三条员工名字

 SELECT ROWNUM,TEMP.ENAME 员工名称,TEMP.SAL 工资                          

 FROM (SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP   

 WHERE ROWNUM<4;

 例题3：查询EMP 6-10条记录

 SELECT ROWNUM,TEMP.ENAME 员工名称,TEMP.SAL 工资                          

 FROM (SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP                                       

 WHERE ROWNUM BETWEEN 6 AND 10;

     此时，并没有返回任何数据，具体原因不知！

 以下的方式可以解决：

SELECT RN,TEM.NAME 员工,TEM.SALA 薪酬 --观察ROWNUM对应的源表

FROM (SELECT ROWNUM RN,TEMP.ENAME NAME,TEMP.  

        ---将ROWNUM实列化，即将系统自动生成的行号转化为具体的记录行号       

FROM (SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP  ) TEM

WHERE RN BETWEEN 6 AND 10;    

 

统计查询、分组统计查询

作用：针对结果集进行分组或聚合

关键词：统计函数、分组查询、分组统计查询、实体表、*临时表、嵌套 、关联表连接

备注：统计函数要不单独使用，要不和GROUP BY一起使用，通常与GROUP BY 共同使用

 (1)统计查询/统计函数 （聚合函数） 

 1.COUNT(列|*)    统计查询后表中的记录总数

 备注：1.记录中的空值不参与计算  

  2.当没有统计记录的时候，也返回数据，这时数据时'0'

 2.SUM(列)    计算列中所有数值项总和

 备注：必须为数值型且空值不参与计算

 3.AVG(列)    计算列中所有数值项的平均值

 备注：必须为数值型且空值不参与计算

 4.MAX(列)     求列中所有数值项的最大值，空值不参与计算

 5.MIN(列)     求列中所有数值项的最小值，空值不参与计

 备注：1.可以进行统计查询和分组统计查询

  2.使用统计函数时，空值不参与计算  

(2)分组查询        

GROUP BY 子句    

功 能 ：对表中或者视图中的数据按照特定字段进行分组    

语法格式：SELECT 分组字段1,分组字段2,分组字段3,...,统计函数

  FROM 表名1，表名2,...(子表)

  WHERE  初次筛选条件

  GROUP BY 分组字段1,分组字段2,分组字段3,...

  HAVING 分组后筛选条件

  ORDER BY 排序字段1 ASC|DESC,排序字段1 ASC|DESC

 备注：1.特点：肯定在数据的某一列上存在重重复的内容，

所以只用某一列上有重复数据才能分组

2.对没有进行分组的数据进行的查询可以将整个表看做分成一个已分组的表，     

只不过分组字段太多

 

单字段分组查询   

 例题1：查询每个部门人数以及平均工资

 SELECT DEPTNO,COUNT(ENAME),AVG(SAL)

 FROM EMP

 GROUP BY DEPTNO;

 例题2: 计算每个职位的平均工资、最高工资和最低工资

 SELECT JOB,AVG(SAL),MAX(SAL),MIN(SAL)

 FROM EMP

     GROUP BY JOB;

 例题4：平均工资最高的职位是哪个    

---嵌套:但是嵌套之后的分组统计查询之中不能再出现其他任何的字段

 WITH TEMP AS (SELECT AVG(SAL) AV FROM EMP GROUP BY JOB)

 SELECT MAX(AV) FROM TEMP;

 例题5：查询每个部门的名称、部门人数、平均工资      

        

------多表查询：外连接

    SELECT A.DEPTNO,A.DNAME,COUNT(*),AVG(SAL)

FROM DEPT A LEFT JOIN EMP B ON A.DEPTNO=B.DEPTNO

GROUP BY A.DEPTNO,A.DNAME

 

 多字段分组查询

 例题1：在HR表中，查询每个部门中每种职位的平均工资

SELECT DEPARTMENT_ID,JOB_ID,AVG(SAL)

    FROM EMPLOYEES

    GROUP BY DEPARTMENT_ID,JOB_ID

 例题2: 在HR表中，查每个部门中不同年份入职员工人数

SELECT DEPARTMENT_ID,TO_CHAR(HIRE_DATE,'YYYY'),

COUNT(TO_CHAR(HIRE_DATE,'YYYY'))

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID ,TO_CHAR(HIRE_DATE,'YYYY')

order by department_id

   备注：可多字段分组查询之后可以用排序看一下

 例题3：select job,count(*)from emp

group by job

select deptno,job, count(*),sum(sal),avg(sal),max(sal),min(sal)

from emp

group by deptno,job;

 

1. HAVING  子句     

对分组、统计后的数据做进一步筛选，放于GROUP by后面

例如：在EMPLOYEES表中，查询部门平均工资大于8000的部门

SELECT DEPARTMENT_ID

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

HAVING AVG(SAL)>8000

       where 和GROUP BY 的区别

           1.位置不同：where在group by 之前，having 在group by 之后

           2.作用不同：having 是对分组之后的数据进行查询；

 

(4)排序:ORDER BY

语法：ORDER BY 字段1 ASC|DESC,字段2 ASC|DESC,.....

备注：1.ORDER BY 放在所有SQl查询语句的最后，是对已经查询出来的结果集按照特

定的字段进行排序

  2.可以对多个字段进行排序

  3.排序的方式有两种：升序：ASC   从小到大排序，默认地

                          降序：DESC  从大到小排序，用户需要指定

例题：要求查询出所有员工信息，按照工资由高到低排序，如果工资相同按照雇佣日期

有早到晚排序

SELECT *

FROM EMPLOYEES

ORDER BY SALARY DESC,HIRE_DATE ASC

 

子查询      

子查询=简单查询+限定查询+多表查询+统计查询 的综合体

定义：在一个查询之中使用其他查询的结果集作为查询条件的一部分，而作“其他查询的结

果集”就是子查询，即实现查询的嵌套使用

语法如下：SELECT 分组字段1,分组字段2,分组字段3,...统计函数,(子查询)    

  FROM 表名1，表名2,... (子查询)            

  WHERE  条件，(子查询)    

      GROUP BY 分组字段1,分组字段2,分组字段3,...  

  HAVING 分组后的条件                                 

  ORDER BY 排序字段1 ASC|DESC,排序字段1 ASC|DESC   

备注 1.子查询的位置灵活多变，理论上可以放在任何位置，使用时必须用括号括起来

       即子查询的结果集既可以作为其他查询的条件，也可以作为其他查询的数据源 

即视为（临时）表

 2.在包含子查询的查询语句中 执行顺序：先执行子查询，再执行其他查询

 3.如果子查询的结果集为NULL，那么外查询的结果也为NULL，

但这不是一个系统错误

   即外查询的结果是依赖子查询的结果集的。

 4.from 子句中的多表连接避免笛卡尔积，即要确定连接条件

 5.子查询都可以用表连接替换，但是表连接执行快

 

(1)从子查询需求的位置上分

      从经验来说，子查询出现在WHERE 和FROM 子句较多.

  WHERE   子查询作为筛选的条件

            子查询一般只返回单行单列，单行多列，多行单列的结果集；

当子查询返回的结果集是单行单列时，可进行比较

  例题1：要求查出工资比SMITH还要高的雇员信息

  SELECT * FROM EMP

      WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SMITH')       

-----子查询返回的是单行单列，可以作为条件使用

  例题2：要求查询出工资高于公司平均工资的全部雇员信息

  SELECT * FROM EMPS

  WHERE SAL>(SELECT AVG(SAL) FROM EMP )

---返回结果是单行单列，可以作为条件使用

 

当子查询返回的是单列多行数据的话，就需要用到以下三种判断符

[NOT] IN :查询一个给定值是否存在于子查询结果集中

     备注：1.注意 NOT IN 操作，在子查询中，如果有一个内容是NUL 

，则不会查询出任何数据

   2.IN 子查询只能返回一列数据，对于比较复杂的查询，

可使用嵌套的子查询

例题1：查询工资等于经理工资的员工信息

  SELECT * FROM EMP

  WHERE SAL IN (SELECT SAL  FROM EMP WHERE  

JOB='MANAGER');

例题2：查询职位与工资和ALLEN相同的员工

 SELECT * FROM          -----返回结果为单列多行

 WHERE (JOB,SAL)=(SELECT JOB,SAL FROM EMP WHERE

ENAME='ALLEN')  -条件是：多个字段与子查询的字段相互匹配

或WHERE (JOB,SAL)in (SELECT JOB,SAL FROM EMP WHERE ENAME='ALLEN')

 ------建议用IN 因为IN 更为符合逻辑

ANY :查询给定值是否与子查询结果集中的任意一个内容相匹配，有三种匹配形式：

1.=ANY : 与子查询结果集的任意一个记录相匹配即可，功能与IN操作符完全一样

SELECT * FROM EMP

WHERE SAL IN (SELECT SAL  FROM EMP WHERE  JOB='MANAGER');

2.>ANY ：比子查询返回结果中最小的记录还大   -注意与平时的逻辑不同

    SELECT * FROM EMP       ------单列多行

      WHERE SAL >ANY(SELECT SAL  FROM EMP WHERE  JOB='MANAGER');

3.<ANY :比子查询返回结果中最大的记录还小 ---注意与平时的逻辑不同

       SELECT * FROM EMP         ---单列多行

 WHERE SAL <ANY (SELECT SAL  FROM EMP WHERE  JOB='MANAGER');

ALL :与子查询中每一个内容相匹配，有两种匹配方式：

1.>ALL :比子查询返回结果的最大的记录还要大    --与平时的逻辑相同

   SELECT * FROM EMP

  WHERE SAL >ALL (SELECT SAL  FROM EMP WHERE  JOB='MANAGER');

例题：查询工资比部门平均工资高的员工信息

SELECT * FROM EMP

WHERE SAL>ALL

(select avg(sal) AV from emp group by deptno)

2.<ALL ：比子查询返回结果的最小的记录还要小  -与平时的逻辑相同

  SELECT * FROM EMP

WHERE SAL <ALL (SELECT SAL  FROM EMP WHERE  JOB='MANAGER');

           FROM   子查询是作为数据源表

 

子查询返回的一般是多行多列的数据，当做是一张临时表处理，

  当有临时表的时候列名和表一定要有别名。---------应用到了分组统计查询和自查询

例题：在HR中查询每个部门的编号、名称、位置、部门人数、平均工资

(1) SELECT DEPT,DNAME,LOC,COUNT(*),AVG(SAL)

FROM (SELECT D.DEPARTMENT_ID DEPT,D.DEPARTMENT_NAME

DNAME,LOCATION_ID LOC,SALARY SAL   -------列别名、表别名

FROM EMPLOYEES E,DEPARTMENTS D

WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID(+))                            

GROUP BY DEPT,DNAME,LOC

1. SELECT DE.DEPARTMENT_ID 部门编号,DE.DEPARTMENT_NAME 名称

,DE.LOCATION_ID,TEMP.CON,TEMP.AV

TMENTS DE,(  SELECT DEPARTMENT_ID DEP,COUNT(*) CON,AVG(SALARY) AV

 FROM EMPLOYEES

GROUP BY DEPARTMENT_ID) TEMP

WHERE DE.DEPARTMENT_ID=TEMP.DEP

评价：-----(2)的查询方式比(1)性能更优；

 

(2)从连接连接谓词的运算符上分

1.[NOT] IN : 

定义：查询一个给定值是否存在于子查询结果集中(同上)

备注：1.注意 NOT IN 操作，在子查询中，如果有一个内容是NULL，

则不会查询出任何数据

     2.IN 子查询只能返回一列数据，对于比较复杂的查询，可使用嵌套的子查询

例题：查询工资等于经理工资的员工信息

SELECT * FROM EMP

WHERE SAL IN (SELECT SAL  FROM EMP WHERE  JOB='MANAGER');

2.比较子查询

  使得表达式的值域子查询结果集进行比较运算

  语法格式为：

<表达式> {<|<=|>|>=|!=|<>} {ANY|SOME|ALL}(子查询)

  其中，ALL、ANY、SOME、等关键字是对比较运算符的限制

ALL :指定表达式与子查询结果集的每个值进行比较，当表达式与每个值都满足比

较关系时，才返回TRUE，否则返回FALSE;

    ANY 和SOME 表示表达式中只要与子查询结果集中的某个值满足比较的关系时，

就返回TRUE，否则返回FALSE;

   例题1：在XSB中查询比所有计算机系学生年龄都大的学生信息

 SELECT *  FROM XSB

WHERE AGE>ALL(SELECT AGE FROM XSB WHERE KE='计算机系' )

*例题2：在XSB和CJB中查找课程号206的成绩不低于101的最低成绩的学生编号,姓

3.[NOT] EXISTES

    1.子查询  相当于IN。

    2.用语判断子查询结果集否为空空集，

    3.若子查询的结果集不为空，则返回TRUE,否则，返回FALSE.

 

(3)从子查询结果集结构上分

   1.单列单行子查询：子查询的结果是单列单行，可以当做一个表或者条件使用

2.单列多行子查询：子查询的结果是单列多行，可以将查询的结果作为一个数据集合

使用, 大多用于WHERE子句，作为筛选条件

 <ALL   小于最小值

 >ALL   大于最大值

 <ANY   即小于最大值

 >ANY   即大于最小值

 IN ALL ANY EXISTS SOME

 IN 和EXISTS 等同，但是EXISTS的结果快，更优化。

 3.多列多行以及多列单行，大多用于from子句，作为数据源表

 4.相关子查询，类似于多表连接