Oracle 11g 学习四：多表查询、左右连接、SQL1999语法、统计函数及分组查询

一、多表查询

    多表查询的语法：SELECT [DISTINCT] *| 列 [别名] [,列|别名,...] 

                    FROM 表名称 [别名],[表名称 [别名],...]

                    [WHRER 条件(s)]

                    [ORDER BY 排序字段 ASC|DESC [,排序字段 ASC|DESC]] ;

    在多表查询之间一定要进行表中数据量的查询，以避免在大数据量的情况下操作避免造成响应太慢。使用COUNT()函数进行查询，例：SELECT COUNT(*) FROM emp ;

    在多表查询中，不同的表中有可能有相同的字段名称，此时访问这些字段必须加上表名称，即：表名称.字段，由于表名称很长此时可以给表起一个别名。

    例：查询每一位雇员的编号、姓名、职位、部门名称、位置。

      第一步：确定数据表：emp：雇员标号、姓名、职位  dept：部门名称、位置

      第二步：确定两表的关联字段：emp.deptno/dept.deptno

      SELECT e.empno,e.ename,e.job,d.dname,d.loc FROM emp e,dept d WHERE d.deptno=e.deptno ;

    例：查询出每一位雇员的姓名、职位、领导的姓名。

      第一步：确定数据表：emp:雇员姓名、职位  emp:领导的姓名

      第二步：确定量表的关联字段：emp.mgr/emp.deptno

      SELECT e.ename,e.job,m.ename FROM emp e,emp m WHERE e.mgr=e.empno ;

    例：查询每个雇员的编号、姓名、基本工资、职位、领导的姓名、部门及位置

      第一步：确定数据表：emp:雇员姓名、编号、基本工资、职位  emp:领导姓名   dept:部门、位置

      第二步：关联字段： 雇员和领导--> emp.mgr=emp.empno    雇员和部门--> emp.deptno=dept.deptno

      SELECT e.empno,e.ename,e.sal,e.job,m.empno,d.dname,d.loc FROM emp e,emp m,dept d

      WHERE e.mgr=m.empno AND e.deptno=d.deptno ;

    例：查询出每一位雇员的编号，姓名，工资，部门名称，工资所在公司等级

      SELECT e.empno,e.ename,e.sal,d.dname,s.grade FROM emp e,dept d,salgrade s 

      WHERE e.deptno=d.deptno AND sal BETWEEN s.losal AND s.hisal ;

二、左右连接

    左右连接指的是查询判断条件的参考方向。

    在如下例子中：SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno ;输出结果中dept缺少40的部门，这是因为emp表中没有一条记录是属于40的，所以不会显示40部门信息，即：现在查询以emp表为参考，如果现在要显示40部门则使用如下SQL语句：SELECT * FROM emp e,dept d WHERE e.deptno(+)=d.deptno ;现在40部门显示了，即参考方向改变了。

    (+)=:放在等号左边，表示右连接，即放在字段右边：

    =(+):放在等号右边，表示左连接，即放在字段右边；  --> 不必刻意区分，根据查询结果而定

    这是Oracle数据库独有的，其他数据库不能够采用。

三、SQL1999语法

    SQL也提供了表左右连接的操作；SQL1999语法：

     SELECT table1.column,table2.column 

     FROM table1 [CALSS JOIN table2] | [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] |

     [JOIN table2 ON (table1.column_name=table2_column)] |

     [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name=table2.column_name)] ;

    以上属于多语法联合，一下进行说明：

    (1) 交叉连接（CROSS JOIN）：用于产生笛卡尔积，笛卡尔积并不是无用的内容，在某些情况下还是有用的。

        SELECT * FROM emp CROSS JOIN dept ;

    (2) 自然连接（NATURAL JOIN）：自动寻找关联字段，消除笛卡尔积

        SELECT * FROM emp NATURAL JOIN dept ;

       并不是所有字段都是关联字段，设置关联字段需要约束指定

    (3) JOIN...USING()：用户自己指定一个消除笛卡尔积的关联字段

        SELECT * FROM emp JOIN dept USING (deptno) ; 

    (4) JOIN...ON(): 用户自己指定一个消除笛卡尔积的关联字段

        SELECT * FROM emp JOIN dept ON (emp.deptno=dept.deptno) ;

    (5) 连接方向的改变

          左（外）连接：LEFT OUTER JOIN...ON ;

          右（外）连接：RIGHT OUTER JOIN...ON ;

          全（外）连接：FULL OUTER JOIN...ON ; --> 把两张表中都没有的数据都显示

       例：SELECT * FROM emp RIGHT OUTER JOIN dept ON (emp.deptno=dept.deptno) ;

四、统计函数及分组查询

    1、统计函数有如下几个：COUNT():查询表中的数据记录，AVG():平均值，SUM()：求和，MAX():求最大值，MIN():求最小值。

    例：统计全公司雇员的每个月支出的平均工资及总工资。

         SELECT COUNT(empno),SUM(sal),AVG(sal) FROM emp ;

    例：统计雇员中最高工资和最低工资

         SELECT MAX(sal),MIN(sal) FROM emp ;

    注意：使用COUNT()函数查询表中的记录，若表示空表，则COUNT返回‘0’，则使用其他统计函数查询空表返回‘NULL’,COUNT()函数永远返回一个数字。

    2、分组统计

    分组的前提：数的某些字段存在重复分组才有意义。分组使用GROUP BY字句完成，此时SQL语句如下：

       SELECT [DISTINCT] * | 分组字段1 [别名][,分组字段2 [别名]] | 统计函数

       FROM 表名称 [别名],[表名称 [别名],...]

       [WHERE 条件(s)] 

       [GROUP BY 分组字段1，[，分组字段2，...]] 

       [ORDER BY 排序字段 ASC|DESC [,排序字段 ASC|DESC]] ;

    例：按部门编号分组，求出每个部门的人数，平均工资

      SELECT deptno,COUNT(empno),AVG(sal) FROM emp GROUP BY deptno ;

    例：按照职位分组，求出每个职位的最高和最低工资

      SELECT job,MAX(sal),MIN(sal) FROM emp GROUP BY job ;

    注意：a. 统计分组函数可以单独使用，但是不能出现其他的查询字段

           正确：SELECT COUNT(empno) FROM emp ; 错误：SELECT empno,COUNT(empno) FROM emp ;

          b. 进行分组时，SELECT字段只能出现分组字段和统计函数，其他字段不能出现

           正确：SELECT job,COUNT(empno),AVG(sal)SELECT COUNT(empno) FROM emp ;         

           错误：SELECT deptno,job,COUNT(empno),AVG(sal)SELECT COUNT(empno) FROM emp ;

          c. 分组查询允许嵌套，但是嵌套之后在SELECT字句不在允许出现任何其他字段

           例：按照职位分组，查询统计最高的平均工资

           正确：SELECT MAX(AVG(sal)) FORM emp GROUP BY job ;

           错误：SELECT job,MAX(AVG(sal)) FORM emp GROUP BY job ;

    例：查询出每个部门的名称、部门人数，平均工资——>加入多表查询和左右链接

        SELECT d.dname,COUNT(e.empno),NVL(AVG(e.sal),0) FROM dept d,emp e WHERE d.deptno(+)=e.deptno

        GROUP BY d.dname ;

    分组操作也可用于多字段分组：

    例：查询显示每个部门编号，名称，位置，部门人数，平均工资

        SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),NVL(AVG(e.sal),0) FROM dept d,emp e

        WHERE d.deptno(+)=e.deptno GROUP BY d.deptno,d.dname,d.loc ;

    3、HAVING字句

    HAVING字句使用在GROUP BY分组之后的过滤条件下，HAVING的SQL语法：       

       SELECT [DISTINCT] * | 分组字段1 [别名][,分组字段2 [别名]] | 统计函数

       FROM 表名称 [别名],[表名称 [别名],...]

       [WHERE 条件(s)] 

       [GROUP BY 分组字段1，[，分组字段2，...]] 

       [HAVING 分组后过滤条件（可以使用统计函数）]

       [ORDER BY 排序字段 ASC|DESC [,排序字段 ASC|DESC]] ;

     WHERE和HAVING的区别：

       WHERE:位于GROUP BY操作之前，表示从全部数据中筛选出部分数据，在WHERE字句中不能使用统计函数

       HAVING:位于GROUP BY操作之后，表示从分组之后的数据中再进行筛选，可以在HAVING字句中使用统计函数

     例：输出部门的详细信息，并且要求这些部门的平均工资大于2000

       SELECT d.dname,COUNT(e.empno),NVL(AVG(e.sal),0) FROM dept d,emp e WHERE d.deptno(+)=e.deptno

       GROUP BY d.dname HAVING AVG(e.sal)>2000 ;

*************by jixiangrurui  转载请注明出处*************