首页 > WEB开发 > 数据库 > Oracle Select语句深入学习
2014
11-17

Oracle Select语句深入学习

5、多表连接查询

关于多表的连接查询,可以先参考《SQL入门》一文中的多表查询。

连接的类型:

Oracle select 06

1)等值连接

查询员工信息:员工号、姓名、月薪和部门名称

03. Oracle Select语句深入学习4571

注:

① 在多表查询时需注意,如果两张表中有相同列名时,在列名之前应加上表名的前缀,也可以使用表的别名

② 连接N个表时,至少需要n-1个连接条件。例如:连接三个表,至少需要两个连接条件。

2)不等值连接

查询员工信息:员工号、姓名、月薪和工资级别

03. Oracle Select语句深入学习4700

3)外连接查询


使用外连接可以查询不满足连接条件的数据(☆。Oracle中外连接的符号是“(+)”,在连接条件中使用。当然也可以在from后面直接使用SQL99中定义的语法(见《SQL入门》)。

按部门统计员工人数:部门号,部门名称和人数

03. Oracle Select语句深入学习4825

4)自连接

查询员工信息:员工和员工的老板姓名

03. Oracle Select语句深入学习4852

自连接通过表的别名,将同一张表视为多张表,因此它的实质还是多表查询。自连接不适合操作大表,这种情况可以使用层次查询。下面使用层次查询改写上面的自连接查询。

首先分析其中的层次图(树,如下图),因此只要从KING这个员工(e.mgr=null)开始遍历整棵树即可得到上面的结果:

03. Oracle Select语句深入学习4993

对Emp表的层次查询如下:

03. Oracle Select语句深入学习5009

对上面层次查询语法的说明如下(☆):

① 由于层次查询只查询一张表,因此不需要用when子句

② connect by相当于多表查询时的when

③ prior empno表示上一层的员工号,mgr表示当前节点的老板号,因为层次查询时的条件就是:上一层的员工号=当前节点的老板号

④ start with:层次查询需要指定一个开始节点。如果是从根节点开始遍历,开始条件可以直接写成mgr is null。

⑤ level是Oracle中的一个伪列。

6、子查询

1)子查询要解决的问题:不能一步求解

如在emp表中查询谁的工资比SCOTT员工高,可分为两步:①SCOTT的工资是多少?(4832)②查询工资比4832高的员工。如下图:

03. Oracle Select语句深入学习5329

这种问题就可以通过子查询解决,如下:

03. Oracle Select语句深入学习5350
注:一般情况下,子查询(内查询)先于主查询之前执行,且子查询的结果会被主查询使用(外查询)。【相关子查询 例外。】

2)单行操作符对应单行子查询,多行操作符对应多行子查询。

单行子查询:只返回一行结果。单行操作符包括:=、>、>=、<、<=、<>。下面的例子演示了单行子查询的错误使用:

03. Oracle Select语句深入学习5500

多行子查询:返回多行结果。多行操作符包括:in、any、all。

  • in:等于列表中的一个即可
  • any:和子查询返回的任意一个值比较,符合即可
  • all:和子查询返回的所有值比较,符合才行

Demo1:查询工资比30号部门任意一个员工高的员工(any)

03. Oracle Select语句深入学习5629

Demo2:查询工资比30号部门所有员工高的员工

03. Oracle Select语句深入学习5657

3)子查询的位置:可以在主查询的where、select、having、from后面放置子查询。但不能在group by、order by后放置(跟的是列名等,不是一个值)。

这里需要注意的是from后面的子查询,由于from后边跟的是一张表,所以可以将子查询的结果看成一张新表,例如:

03. Oracle Select语句深入学习5804

4)一般不在子查询中使用order by。但在Top-N问题中,必须对子查询排序。

5)主查询和子查询可以不是同一张表,只要子查询返回的结果,主查询可以使用即可。

Demo:查询部门名称是“SALES”的员工

03. Oracle Select语句深入学习5913

6)子查询的应用:Oracle分页

Demo:按工资降序排列,查找排在第5~8位的员工

03. Oracle Select语句深入学习5959

关于rownum的一些说明:

① rownum是Oracle中的一个伪列,表示查询结果集的行号。且rownum永远按照默认的顺序生成,即每行对应的行号永远是一样的。如下:

03. Oracle Select语句深入学习6047

② 可以在where子句中出现rownum <= n,但不能出现rownum >= n。

7、集合运算

1)集合运算符:

可以认为select返回的结果列表是一个集合,这样,对多条select语句返回的结果就可以做如下运算:

  • 并集:union
  • 交集:intersect
  • 差集:minus

2)集合运算的注意事项:

  • select语句中的参数类型和个数要一致,即保持列一致
  • 集合运算采用第一个语句的表头作为表头
  • 如果有order by子句,必须要放到最后一句查询语句后

3)集合运算的Demo

Demo1:查询10和20号部门的员工

03. Oracle Select语句深入学习6314

Demo2:在分组查询最后一个生成报表的Demo中曾经提到:group by rollup(a,b) = group by a,b + group by a + group by null,下面尝试使用Union的方式实现类似的查询:

03. Oracle Select语句深入学习6435

关于本例的说明见注意事项第一点和第二点。

通过打开计时器分析(set timing on)发现,使用集合运算得到同样的结果耗时更长。因此,在开发中尽量不使用集合运算。


留下一个回复

你的email不会被公开。