sql 连接.........

Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 16:59:52 scan@LEE2>create table person

17:00:30   2  ( id number,

17:00:42   3  name varchar2(10));

 

表已创建。

 

已用时间:  00: 00: 00.39

17:00:54 scan@LEE2>insert into person values(1,'li');

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

17:01:24 scan@LEE2>insert into person values(2,'mo');

 

已创建 1 行。

 

已用时间:  00: 00: 00.01

17:01:32 scan@LEE2>insert into person values(3,'ch');

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

17:01:42 scan@LEE2>insert into person values(34,'ll');

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

17:01:49 scan@LEE2>commit;

 

提交完成。

 

已用时间:  00: 00: 00.01

17:01:51 scan@LEE2>select * from person;

 

        ID NAME

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

         1 li

         2 mo

         3 ch

        34 ll

 

已用时间:  00: 00: 00.04

17:01:56 scan@LEE2>create table pjob

17:02:37   2  (name varchar2(10),

17:02:49   3  job varchar2(10));

 

表已创建。

 

已用时间:  00: 00: 00.03

17:02:58 scan@LEE2>insert into pjob values('li','teacher');

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

17:03:21 scan@LEE2>insert into pjob values('mo','lawer');

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

17:03:35 scan@LEE2>commit;

 

提交完成。

 

已用时间:  00: 00: 00.00

17:03:38 scan@LEE2>select * from pjob;

 

NAME       JOB

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

li         teacher

mo         lawer

 

已用时间:  00: 00: 00.01

17:03:44 scan@LEE2>

 

 

//开始 链接查询

 

17:06:16 scan@LEE2>select person.* ,pjob.*

17:06:33   2  from person inner join pjob on person.name=pjob.name;

 

        ID NAME       NAME       JOB

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

         1 li         li         teacher

         2 mo         mo         lawer

//和一般的等值链接一样的！！

17:07:44 scan@LEE2>select person.* ,pjob.*

17:07:54   2  from person,pjob

17:08:12   3  where person.name=pjob.name;

 

        ID NAME       NAME       JOB

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

         1 li         li         teacher

         2 mo         mo         lawer

 

已用时间:  00: 00: 00.01        

 

//左连接（很明显，以左边的表为主）

 

17:08:23 scan@LEE2>select person.*, pjob.*

17:28:08   2  from person left join pjob on person.name=pjob.name;

 

        ID NAME       NAME       JOB

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

         1 li         li         teacher

         2 mo         mo         lawer

         3 ch

        34 ll

 

已用时间:  00: 00: 00.03

17:28:44 scan@LEE2>select person.*, pjob.*

17:28:57   2  from pjob left join person on person.name=pjob.name;

 

        ID NAME       NAME       JOB

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

         1 li         li         teacher

         2 mo         mo         lawer

 

已用时间:  00: 00: 00.00

 

//同理，右链接是以有的为主

 

17:49:43 scan@LEE2>select person.*, pjob.*

17:52:31   2  from person right join pjob on person.name=pjob.name;

 

        ID NAME       NAME       JOB

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

         1 li         li         teacher

         2 mo         mo         lawer

 

已用时间:  00: 00: 00.00

17:52:49 scan@LEE2>select person.*, pjob.*

17:53:26   2  from pjob right join person on person.name=pjob.name;

 

        ID NAME       NAME       JOB

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

         1 li         li         teacher

         2 mo         mo         lawer

         3 ch

        34 ll

 

 

//完全连接（就是该有的就有）

17:53:44 scan@LEE2>select person.*, pjob.*

17:56:14   2  from pjob full join person on person.name=pjob.name;

 

        ID NAME       NAME       JOB

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

         1 li         li         teacher

         2 mo         mo         lawer

         3 ch

        34 ll

 

已用时间:  00: 00: 00.03

17:56:20 scan@LEE2>select person.*, pjob.*

17:56:23   2  from person full join pjob on person.name=pjob.name;

 

        ID NAME       NAME       JOB

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

         1 li         li         teacher

         2 mo         mo         lawer

         3 ch

        34 ll

 

已用时间:  00: 00: 00.01

 

//试试在pjob里面加一个跟person没有联系的（看出来了，就是有相等值的就连起来，没有的就另外放，但是还是都要显示出来）

 

17:56:36 scan@LEE2>insert into pjob values('oo','gay');

 

已创建 1 行。

 

已用时间:  00: 00: 00.00

17:58:30 scan@LEE2>commit;

 

提交完成。

 

已用时间:  00: 00: 00.00

17:58:33 scan@LEE2>select * from pjob;

 

NAME       JOB

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

li         teacher

mo         lawer

oo         gay

 

已用时间:  00: 00: 00.00

17:58:37 scan@LEE2>select person.*, pjob.*

17:58:42   2  from person full join pjob on person.name=pjob.name;

 

        ID NAME       NAME       JOB

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

         1 li         li         teacher

         2 mo         mo         lawer

         3 ch

        34 ll

                      oo         gay

 

已用时间:  00: 00: 00.01

17:58:43 scan@LEE2>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11134734/viewspace-448772/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11134734/viewspace-448772/