2009-12-07传智播客 数据库——orcale表连接和子查询 （转载）

数据库比较枯燥，我说的是使用数据库不是做数据库。总是用SQL语句那么跑来跑去，想把数据库讲解的十分有趣，着实不是一件容易的事。只是在PLSQL里编写SQL语句，然后调用、查看结果。虽然数据库很枯燥，但它在项目中的功用却是十分重要的。我原未如此的深入使用过数据库，近两天一见，其功能也让我十分兴奋，以前自己写的小东西，走了些弯路！




     无论如何还是要搞好数据库的，尤其是Oracle数据库。明天是最后一天学习数据库了，终于要把这块阵地给解放了！


     OK，来看重点内容吧！




     常用的分组函数，这个比较重要，在其他数据库中也有：

1.        AVG，求平均值。如果某一记录为null，则不加入平均值的计算。（不加入被除数）

2.        COUNT，求表中所有符合条件的记录数。如果某一记录为null，则不计算在内！

3.        MAX，求最大值。

4.        MIN，求最小值。

5.        SUM，求合。



还有一个特殊的函数NVL，将字段值为空的记录替换为指定的值，AVG(NVL(commission_pct,0)。比如上面的AVG函数将null记录剔除在外。这样调用就不会把null值的记录剔除在外了：NVL(AVG(NVL(commission_pct,0)))。



Group by 子句，是最低级的运算语句，它被放置在语句的最后，对Group by子句分组的记录进行过滤应使用having语句。Where是用于Group by之前的记录过滤。在Select列表中出现的字段，如果不是在组函数中，那么它必须也被放置到Group by子句中。



接下来是十分重要的内容：多表连接和子查询！



首先让我们来回顾一下“外键”字段，比如有一个部门表，有一个员工表。员工表中使用的部门ID就是一个外键，这个部门ID就是部门表中的ID。这样设计有益于节省空间、快速查询和易于管理等特点。这样的表查询的操作比较多，我们应该如何实现便利、快速的查询呢？难道从员工表中取出部门ID然后再到部门表中查询这个ID？NO，这样太麻烦了，访问量多了，明显会使速度降下来。所以表连接出现了！表连接就是将多个表，根据表之间的相关字段关系连接起来，就像一张表一样。对这一大张表的操作十分方便、快捷！



Oracle的语法，使用表连接从多个表中查询数据：

SELECT          table1.column, table2.column

FROM                            table1, table2

WHERE          table1.column1 = table2.column2;

在 WHERE 子句中写入连接条件。

当多个表中有重名列时，必须在列的名字前加上表名作为前缀。



例，查询员工的姓名和所在部门：



其中的E和D分别是表EMP和表DEPT的别名，这里不能使用AS。

EMP表中的DEPTNO字段是个外键，它指向DEPT表中的DEPTNO。



在Oracle中都有哪些表连接？在Oracle8i之前有：

1.        等值连接 – Equijoin，这是连接操作中最常见的一种。通常在存在主外键约束条件的多表上建立的，连接中的两个字段通过等号建立等值关系。如上面的例子是使用主外键对两个表的连接，还可以对多个表连接，此时可以使用AND增加WHERE子句的连接条件。

2.        非等值连接 -- Non-equijoin。例，通过EMP表和SALGRADE（薪水等级）表，查询员工薪水所处等级：



语句也可写成：

SELECT E.ENAME,E.JOB,S.GRADE

FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL


使用JOIN ON语句，更直观些！它是9i中加入的，可以使用多个JOIN ON语句，连接多个表，它们之间不需要添加任何关键字，比如“AND”。

3.        外连接 -- Outer join，外连接可以查询出不满足条件的记录，它分为左外连接和右外连接（继续关注下面9i中的外连接）。

左外连接：



正是子句中的“(+)”起的作用，这样将DEPTNO表中的所有记录都显示出来了，即使有没有员工的部门也显示了。

右外连接：



正是子句中的“(+)”起的作用，将EMP表中的记录全部显示出来了，即使没有部门的员工也显示了。

4.        自连接 -- Self join，就是连接自己。例，取对员工对应的领导：



因为公司所有职员都在一张表中（包括领导），所以为了查找出对应员工的领导，使用了自连接。自连接就像是为自己创建了一个引用。



到Oracle9i时，新增加的连接（SQL1999适应性连接）：

1．   交叉连接 -- Cross joins，产生了一个笛卡尔积，就象是在连接两个表格时忘记加入一个WHERE子句一样。相互交叉，返回的记录数正好是两个表记录数的乘积。转一个兄弟对交叉链接的介绍：http://tech.techweb.com.cn/thread-384787-1-1.html



上面的语句与这个功效一样：

SELECT E.ENAME,E.JOB,D.DNAME

FROM EMP E,DEPT D




2．   自然连接 -- Natural joins，基于两个表中列名完全相同的多个列产生连接。从两个表中选出连接列的值相等的所有行，如果两个列的名称相同,但是具有不同的数据类型，则查询会返回一个错误。



EMP表与DEPT表中的DEPTNO字段名称相同，且类型相同。



3．   使用Using子句的连接

如上面（2）的例子，如果两个字段的名称相同，但类型不同就会出现错误。此时可以在语句后边加上USING(字段名)子句，来设置用于等值连接，只要值可比，类型不同也可以。



4．   完全外连接或者左右外连接

左外连接：

显示左边表中所有行，即使在右边的表中没有可对应的列值。这与8i中的右外连接是相同的，但这个更直观的指向左边，而不像8i中那样反着。





右外连接：

显示右边表中所有行，即使在左边的表中没有可对应的列值。这与8i中的左外连接是相同的，但这个更直观的指向右边，而不像8i中那样反着。





完全外连接：显示连接表中的所有记录，包括不存在对应记录的记录。全外连接是对两个表中的记录都不加限制。



5．   搞不懂里边的条件语句还有什么用。不加上就语法错误！（外连接中可以使用任意的连接条件

）



子查询：

之前我们学习的都是直接使用某一固定或函数返回值做为限定条件，但这能满足我们的操作吗？即使有连接这样看起来十分强大的功能。当然不能，子查询解决了更多的问题！



SELECT select_list

FROM   table

WHERE  expr  operator

(SELECT select_list FROM  table);



1、子查询在主查询前执行一次

2、主查询使用子查询的结果

3、子查询语句中不能使用外部的别名



使用子查询的注意事项：

1、子查询要用括号括起来

2、将子查询放在比较运算符的右边(增强可读性)

3、只有在执行Top-N分析时，子查询中才需要使用Order by子句

4、在Oracle8i之前的版本中，子查询不能包含Order by子句

5、对单行子查询使用单行运算符

6、对多行子查询使用多行运算符



子查询的种类：

1.        单行单列子查询：只包含一个字段的查询，返回的查询结果也只包含一行数据

2.        多行单列子查询：只包含了一个字段，但返回的查询结果可能多行或者零行

3.        多列子查询：包含多个字段的返回，查询结构可能是单行或者多行。



单行子查询所使用的比较运算符：

Operator
Meaning

=
Equal to

>
Greater than

>=
Greater than or equal to

<
Less than

<=
Less than or equal to

<>
Not equal to


例，查询薪金小于平均薪金的员工，对他们加强培训提高工作技能，涨工资：





多行子查询所使用的比较运算符：

Operator
Meaning

IN
与IN列表中任意一个值相等

ANY
与子查询返回的任意一个值比较，ANY通常与大小写符号搭配使用，不单独使用。可以是<ANY 和>ANY ：

<ANY 小于子查询数据中的最大值

>ANY 大于子查询数据中的最小值

ALL
与子查询返回的每一个值比较，ALL通常与大小写符号搭配使用，不单独使用。可以是<ALL 和>ALL ：

>ALL 指大于子查询数据中的最大值

<ALL 指小于子查询数据中的最小值




例，查询出比平均工资高的员工，给他们升职和更多成长的机会：





以上都只是简单的举了几个例子，函数和语句等组合应用，会让你感到吃惊，功能惊人。当然，我也这只是一个程序员对数据库的了解。DBA玩的就深一些，那里有更强大的功能，主要是对表优化的。先不深入了，这些够用了，明天讲解Oracle触发器、存储过程、视图等！



下面例出三个今日老冯给我们做的练习：
1. 查询各个管理者的编号及其手下员工的最低工资，其中最低工资不能低于1200，没有管理者的员工不计算在内。





2. 查询所有部门的名称，loc，员工数量和工资平均值。





3. 查询在loc为纽约的部门工作的员工的员工号，ename,deptno,job(使用连接查询,子查询两种查询方式)







OK，欢迎明天的到来，数据库这块算是完了！



加油！