oracle等值连接与外连接操作Word下载.docx
《oracle等值连接与外连接操作Word下载.docx》由会员分享,可在线阅读,更多相关《oracle等值连接与外连接操作Word下载.docx(12页珍藏版)》请在冰豆网上搜索。
NEWYORK
20RESEARCH
DALLAS
30SALES
CHICAGO
40OPERATIONS
BOSTON
select*fromemp;
EMPNOENAME
JOB
MGRHIREDATE
SAL
COMM
DEPTNO
---------------------------------------------------------------------------------
7369SMITH
CLERK
790217-DEC-80
800
20
7499ALLEN
SALESMAN
769820-FEB-81
1600
300
30
7521WARD
769822-FEB-81
1250
500
7566JONES
MANAGER
783902-APR-81
2975
7654MARTIN
769828-SEP-81
1400
7698BLAKE
783901-MAY-81
2850
7782CLARK
783909-JUN-81
2450
10
7788SCOTT
ANALYST
756619-APR-87
3000
7839KING
PRESIDENT
17-NOV-81
5000
7844TURNER
769808-SEP-81
1500
0
7876ADAMS
778823-MAY-87
1100
7900JAMES
769803-DEC-81
950
7902FORD
756603-DEC-81
7934MILLER
778223-JAN-82
1300
当然了,为了将来能够方便操作,我们通常会在表上建立主外键约束关系(不是必须的),并通过主外键的对应关系来找到我们想要的数据,在上面的两张表中deptno是dept表的主键,而deptno是emp表的外键。
这其实就是Oracle的等值连接(或者称为内连接)想要达到的目的。
比如我们有这样一个需求,找到员工表中的员工编号和员工名称以及他所在的部门编号、部门名称和部门所在地,我们来看一下实现的语句:
selectdept.deptno,dname,loc,empno,ename
2
fromemp,dept
3
whereemp.deptno=dept.deptno;
LOC
EMPNOENAME
---------------------------------------------------------
DALLAS
7369SMITH
CHICAGO
7499ALLEN
7521WARD
7566JONES
7654MARTIN
7698BLAKE
NEWYORK
7782CLARK
7788SCOTT
7839KING
7844TURNER
7876ADAMS
7900JAMES
7902FORD
7934MILLER
这就是Oracle的等值连接,不过对于这样的一个语句来说,有三个地方最好做一些调整:
1.不同表的列使用表的名称作为前缀(如果表使用了别名,前缀就不能再用表的真实名字)
2.不使用表的原名称而使用表的别名,这样语句会更简短(有用吗?
当然,oracle会把语句的完整文本放到内存中,语句越短,占内存越小)
3.格式化语句,考虑折行、语句区分大小写(如果你的语句有问题,没人愿意为你的乱七八糟的语句给出建议)
以下是我的书写习惯,仅供参考
selectd.deptno,
d.dname,
d.loc,
e.empno,
e.ename
fromemp
e,
deptd
wheree.deptno=d.deptno;
以上的连接方法是Oracle自己的语法,当然对于这样的需求使用SQL99语法也非常的容易,并且可以使用其中的几种语法来实现,在这里我们仅以自然连接(naturaljoin)来举一个小
例子:
selectdeptno,
4
5
6
enaturaljoindeptd;
当然对于自然连接,Oracle有一些限制,比如要求两个表的列的名称和数据类型一样,另外,在做查询的时候,连接的列不能使用前缀,否则会报错
ORA-25155:
columnusedinNATURALjoincannothavequalifier,如果列的类型不一样的话,我们可以选择使用joinusing语法。
等值连接是oracle数据库当中最简单也是使用最多的一种关联方式。
我们再来看一下Oracle的外连接操作,凭心而论,Oracle的语法实现比SQL99外连接语法要简单一些,不过没有SQL99语法具体和完善,比如Oracle没有全外连接的概念。
还是先来看一下为什么要使用外连接吧!
如果我们有这样的一个需求,列出公司里所有的部门和部门下的员工,很显然,用上面的等值连接是实现不了的。
我们看到40部门是存在的,但是等值连接里并不能显示这样的记录,道理很简单,40部门没有一个员工,所以在和员工表做等值连接的时候无法显示出来,那如何来实现这样的需求呢?
很简单
Oracle提供了(+)这样的外连接方法
我们来看下面的句子:
selectd.deptno,
7
8
whered.deptno=e.deptno(+);
我们看到40部门(OPERATIONS)被显示出来了,当然由于他没有员工所以在员工信息部分会补空值。
也许有人会有这样的疑问,whered.deptno=e.deptno(+)中的(+)符号是应该出现在那个表的列后面呢?
是随意的吗?
我们来稍微总结一下:
1.(+)只能在关联条件的一端出现,本例中是e.deptno(+),也可以说是e表的一边
2.如果在where后面有其他的条件,那其他的所有条件都要使用(+)
3.(+)出现在值少的一端,比如本例中,员工表没有员工能和40部门对应,我们认为员工表记录少
我们来看部门表记录少的一个例子:
updateempsetdeptno=nullwhereempno=7369;
1rowupdated.
commit;
Commitcomplete.
800
通过修改,我们把7369(SMITH)的部门号置为空,这就意味着,当我们使用部门号deptno做等值查询时,7369不会显示出来,因为部门表没有一条记录可以和7369对应,现在的情况是员工表记录多,如果我们想要把7369显示,就要在少的一端(部门表列)上添加(+)
whered.deptno(+)=e.deptno;
------------