Oracle 10g数据库基础之基本查询语句-下-连接&子查询

 Oracle 10g数据库基础之基本查询语句-下-连接&子查询

--资料参考：张烈 张建中《数据库管理员培训讲义》

实验18：表的连接查询

该实验的目的是掌握基本的联合查询.

表的连接

我们要从多张表中要得到信息，就得以一定的条件将表连接在一起查询。

Cartesian（笛卡儿）连接

当多张表在一起查询时，没有给定正确的连接条件，结果是第一张表的所有行和第二张表的所有行进行矩阵相乘，得到n*m行的结果集。

一般来说笛卡儿连接不是我们需要的结果。

但表如果有一行的情况下，结果有可能正确。

SQL> select ename,dname from emp,dept;

 

ENAME      DNAME

---------- --------------

SMITH      ACCOUNTING

ALLEN      ACCOUNTING

……..

SMITH      RESEARCH

ALLEN      RESEARCH

……..

SMITH      SALES

ALLEN      SALES

……..

SMITH      OPERATIONS

ALLEN      OPERATIONS

……..

 

56 rows selected

 

SQL>

结果为每个员工在每个部门上了一次班，4*14=56，这并不是我们想得到的结果。

要避免笛卡儿连接一定要给定一个正确的连接条件。

等值连接

在连接中给定一个相等的连接条件。

SQL> select ename,dname from emp,dept where emp.deptno=dept.deptno;

当列的名称在两张表内重复的时候，要加表的前缀来区分，避免不明确的定义。

表的别名

1。便于书写

2。将同名的表区分

3。一旦定义了别名，表的本名就无效

4。只在该语句内有效

5。定义方式为表名后紧跟别名，用空各间隔。

SQL> select ename,dname from emp e,dept d where e.deptno=d.deptno;

SQL99的书写方式

select ename,dname from emp e join dept d on (e.deptno=d.deptno)；

效率是相同的，SQL99是国标

列的别名，为了区分相同的列的名称，这是别名的本质。

SQL> select ename,dname,e.deptno,d.deptno

  from emp e,dept d

  where e.deptno=d.deptno;

上述显示有两个列名称都叫deptno,我们无法区分。

SQL> select ename,dname,e.deptno "员工表",d.deptno "部门表"

  from emp e,dept d

  where e.deptno=d.deptno;

不等连接

连接条件不是一个相等的条件。

SQL> select ename,sal,grade

  from emp,salgrade

  where sal between LOSAL and hisal;

外键连接

将一张表有，而另一张表没有的行也显示出来。

SQL> select ename,dname,emp.deptno from emp,dept

where emp.deptno=dept.deptno;

这句话不会显示40号部门，因为40部门没有员工。

SQL> select ename,dname,dept.deptno from emp,dept where emp.deptno(+)=dept.deptno;

+号的意思为将没有员工的部门，用NULL来匹配

+号不能同时放在等号的两边，只能出现在一边。

自连接

表的一列和同一个表的另一列作为连接的条件。

SQL> select w.ename "下级" ,m.ename "上级"

  from emp w,emp m

  where w.mgr=m.empno(+);

其中“下级”和“上级”为列的别名。区分相同的列。

W和m 为表的别名。区分相同的表。别名的本质。

（+）为了将没有上级的人也显示。

过滤结果

想在结果中过滤去一些内容请用and运算。

SQL> select w.ename "下级" ,m.ename "上级"

  from emp w,emp m

  where w.mgr=m.empno(+)

  and w.deptno=30;

实验19：sql99规则的表连接操作

该实验的目的是掌握新的ORACLE表之间的联合查询语法.

SQL99规则的书写格式

. Nature(自然)连接

这是SQL99规则。

所有同名的列都作为等值条件。

同名的列的数据类型必须匹配。

列的名称前不能加表的前缀。

SQL> select ename,deptno,dname from emp natural join dept;

Using指定列的连接

当有多列同名，但想用其中某一列作为连接条件时使用。

SQL> select ename,deptno ,dname from emp join dept using (deptno);

SQL99的外键连接

SQL99写法

select ename,dname,dept.deptno

from dept left outer join emp

on(dept.deptno=emp.deptno);

9I前的写法

select ename,dname from emp,dept

where emp.deptno(+)=dept.deptno;

SQL> select ename,dname,dept.deptno

  from dept left outer join emp

  on(dept.deptno=emp.deptno);

知识点

1。笛卡儿连接

2。等值连接

3。不等连接

4。外键连接

5。自连接

6。SQL99的书写格式

实验20：子查询

该实验的目的是掌握子查询的语法和概念.

 子查询

谁的工资最多

简单子查询

1。先于主查询执行。

2。主查询调用了子查询的结果。

3。注意列的个数和类型要匹配。

4。子查询返回多行要用多行关系运算操作。

5。子查询要用括号括起来。

SQL> --查询工资总和高于10号部门工资总和的部门。

SQL> select deptno,sum(sal)

  from emp

  group by deptno

  having sum(sal)>(select sum(sal) from emp where deptno=10);

SQL> --查询每个部门的最大工资是谁。

SQL> select deptno,ename,sal from emp

  where (deptno,sal) in

  (select deptno,max(sal) from emp group by deptno);

子查询返回多行，用=不可以，得用in。

子查询返回多列，所以对比的列也要匹配。

Any 和all操作

SQL> select ename,sal from emp where sal

小于2000就可以

SQL> select ename,sal from emp where sal

必须小于1000

小于all小于最小，大于all大于最大

SQL> select ename,sal,deptno from emp

where sal

小于any小于最大，大于any大于最小

select ename,sal,deptno from emp

where sal>any(select avg(sal) from emp group by deptno)

From子句中的子查询

查询工资大于本部门平均工资的员工。

select ename,e.deptno,sal,asal

from emp e,

(select deptno ,avg(sal) asal from emp group by deptno) a

where e.deptno=a.deptno and sal>asal;

A为视图，为什么要使用别名asal,因为表达式不能当列的名称，别名的本质使用方法是使非法的合法化。

相互关联的子查询

select ename,sal ,deptno

from emp o

where sal>(select avg(sal) from emp where deptno=o.deptno) ;

先运行主查询，得到第一行，将DEPTNO传入到子查询，由子查询求出AVG（SAL），在判定主查询的行是否符合查询的条件。

执行计划是将子查询看作视图的关联。这叫做SQL的自动改写。

Exists操作

找领导，其中3是常量，你写什么都可以。

当子查询有行时，Exists返回true

查到行后就不再继续查询

当子查询没有行时为假，Exists返回false

select ename,empno,mgr from emp o

where exists(select 3 from emp where mgr=o.empno);

《完》

--xjzhujunjie

--2012/05/17