ORACLE1.9一对一与左关联

一对一

身份证表

CREATE TABLE idcard(

　　idcard_num CHAR(18) PRIMARY KEY,

　　name VARCHAR2(20),

　　address VARCHAR2(100),

　　minzu VARCHAR2(20)

);

 

学生表

CREATE TABLE stu(

　　sno VARCHAR2(10) PRIMARY KEY,

　　name VARCHAR2(20),

　　idcard_num CHAR(18)

);

 

INSERT INTO idcard(idcard_num,name,address,minzu)

VALUES('123456789012345678','小明','珠海','汉族');

INSERT INTO idcard(idcard_num,name,address,minzu)

VALUES('123456789012345688','小方','珠海','彝族');

commit;

 

SELECT * FROM idcard;

insert into stu(sno, name, idcard_num)

values

('001','小芳','123456789012345678');

 

insert into stu(sno, name, idcard_num)

values

('002','小军','123456789087654321');

commit

 

select * from idcard;

select * from stu;

update stu set name='大芳' where sno='001'

commit

 

select sno,t1.name,t1.idcard_num,address

 from stu t1,idcard t2 

 where t1.idcard_num = t2.idcard_num

 select * from stu

 

-- 故意修改身份证号码，让它关联不上

 update stu set idcard_num='123456789012345677'

 where idcard_num='123456789012345678'

 commit;

-- 本质上是一个内关联

-- 关联不上的数据，不显示

 select sno,t1.name,t1.idcard_num,address

 from stu t1,idcard t2 

 where t1.idcard_num = t2.idcard_num

 

-- 左关联left join

 -- （以左为主，左边关联不上的数据，继续显示）

  select sno,t1.name,t1.idcard_num,address

 from stu t1,idcard t2 

 where t1.idcard_num = t2.idcard_num(+)

 

--传统的写法

 select * from stu t1

 left join idcard t2

 on t1.idcard_num = t2.idcard_num;