Oracle学习笔记 _04_子查询

Oracle学习 第四天

 

            —— Oracle表的复杂查询

 

        常用的聚合函数

                        1、MAX (列名 | 表达式)         返回最大值 。      返回一个值，即使最大值有多个同时最大。

                        2、MIN  (列名 | 表达式)         返回最小值 。      返回一个值。

                        3、AVG  (列名 | 表达式)         返回平均值 。      返回一个值。

                        4、SUM (列名 | 表达式)         返回平均值 。      返回一个值。

                        5、COUNT (列名)                  对一列字段统计。返回条目数。

 以上几个聚合函数返回的都是一个值。可以同时使用。如下：

 

SELECT MAX(SAL), MIN(SAL * 12) FROM EMP;

 

 

 

但是返回值个数不同的聚合函数不能在一起使用。

★ 注意：以上聚合函数会自动忽略空值。这对MAX、MIN、SUM来说没有什么影响，但对AVG的使用影响很大。

    所以，为了防止空值的出现导致平均值的异常。

    我们有两种做法：1. 使用 SUM(calunm)/COUNT(*) ，即 总和/行数 来求平均值。

                               2. 使用 NVL 函数，令所有的空值默认为0，然后使用AVG求平均值。

                                    如：NVL(calunm, 0)  意为 将calunm列的所有null值替换为 0

 

         简单子查询  

                        例如：查询emp表中月薪最高的员工的名字和工号。

                                             我们首先需要查到最高的月薪，然后根据该值去查找对应的人名和工号。

 

SELECT E.EMPNO, E.ENAME FROM EMP E WHERE E.SAL= (SELECT MAX(SAL) FROM EMP);

 

 

 

★ 注意：子查询中的 SELECT 语句用括号包括；

 

         分组统计

                          GROUP BY 和 HAVING 

 ★ 注意：GROUP BY 后跟分组信息，通常分组语句与聚合函数搭配。

               HAVING 后跟分组筛选条件，与GROUP BY 同时出现。用法基本与WHERE一致。唯一的不同就是HAVING后可以跟分组函数，而WHERE不可以。

               所有查询的字段中的非聚合函数字段都必须出现在 GROUP BY 中

               聚合函数只能出现在查询字段、HAVING语句、ORDER BY语句中

               GROUP BY 、HAVING、ORDER BY 三个的出现顺序是固定的。如果没有则可以不写，但前后顺序不能乱。

 

SELECT AVG(SAL),MAX(SAL),DEPTNO FROM EMP GROUP BY DEPTNO HAVING SUM(SAL)>2000 ORDER BY AVG(SAL) DESC;

 

        多表查询

 

                         多表联合查询的需求是实际开发中不可避免的。

                        但是，需要注意的是，如非特殊情况，一般是不会有需求同时进行三张以上的表联合查询的，因为多表查询的数据处理需要大量的资源，如果在设计中遇到四五张表的联合查询，首先想到的不是该如何关联表格，而应该的考虑数据表的设计是否合理。

                         

                         多表查询简单示例：

                                需求：员工表中存储员工详细信息；部门表中存储部门详细信息。我们现在需要查询满足某一个条件的员工所在的部门的详细信息。而员工表中只有员工所在的部门编号和部门名称两个简单信息。此时就需要两张表的联合查询。

 

SELECT E.EMPNO, E.ENAME, E.SAL,D.DEPTNO, D.DNAME, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO; 

 

 

 

★ 注意：多表查询的条件一般不会少于  ( 表的个数 - 1 ) 。如果查询条件不足，则会出现笛卡尔集。 

        实际上，这么写并不会报错，也会返回查询结果。但是一般情况下，大多数多表查询是尽量避免笛卡尔集的。

        当然，专门需要返回两张表的笛卡尔集的案例除外

 

 

        多行子查询

                        子查询也叫嵌套查询。即在一条语句中多个SELECT语句嵌套。

                        子查询分 单行子查询 和 多行子查询。这里的单行/多行指的是子查询语句返回的结果是单个/多个。

                        

                        单行子查询的用法如上面 简单子查询 中示例

                        多行子查询：

                                1、如  1号部门中存在n种工作岗位，筛选出所有  岗位包括在1号部门  中的员工信息

 

SELECT * FROM EMP WHERE JOB IN (SELECT DISTINCT JOB FROM DEPT WHERE DEPTNO = 10)

              

                                2、筛选 薪水  高于部门20中所有员工薪水  的员工的基本信息

 

SELECT * FROM EMP WHERE SAL > ALL(SELECT SAL FROM EMP WHERE DEPTNO = 20)
或 SELECT * FROM EMP WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 20)

                                3、筛选 薪水  高于部门30中任意一个员工薪水  的员工的基本信息

 

SELECT * FROM EMP WHERE SAL > ANY (SELECT SAL FROM EMP WHERE DEPTNO = 30)
或  SELECT * FROM EMP WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 30)

       

       多列子查询

                       多行子查询指的是返回单列的多行数据，针对的都是单列而言，而多列则指的是返回多个列数据。

                       示例：查询  与 工号7499的员工  的部门和工作  都完全相同的  所有员工

 

SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE EMPNO = 7499) AND JOB = (SELECT JOB FROM EMP WHERE EMPNO = 7499)
或 SELECT * FROM EMP WHERE(DEPTNO, JOB) = (SELECT DEPTNO, JOB FROM EMP WHERE EMPNO = 7499)

 

 

                           

                         如上：

                                第一种写法明显感觉冗余，如果条件更多，代码量更大；

                                而第二种写法，一定要注意第一个WHERE条件等号左右字段的对应顺序。

 

 

        FROM子句中的子查询

                        以上的所有子查询示例都是将子查询的结果作为 WHERE 语句的条件。

                        接下来我们学习将子查询的结果作为另一个查询的源表的写法。即将子查询语句放于 FROM 子句中。

    

                        示例：筛选高于  自己部门的平均工资  的所有员工的详细信息

                                    

                                    首先，需要知道  所有部门的平均工资，将其作为一张临时源表

                                    然后，从  所有部门的平均工资表  中筛选出  自己部门的平均工资

                                    最后，从员工信息表中筛选出  工资高于自己部门平均工资  的员工

 

SELECT * FROM EMP E,(SELECT AVG(SAL) AS AVG_, DEPTNO FROM EMP GROUP BY DEPTNO) T WHERE E.DEPTNO = T.DEPTNO AND E.SAL > T.AVG_

★ 注意：在 FROM 子句中使用子查询时， 必须要给子查询指定别名，否则访问不到临时表的数据。