-- 一对一
-- 身份证表
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
('123456789087654321','小军','珠海东方','傣族');
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