Orlace语句

数据库比较枯燥，我说的是使用数据库不是做数据库。总是用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，欢迎明天的到来，数据库这块算是完了！ 



加油！