select * from user1
select * from dicts
-- 多对一（字典类应用）
drop table user1;
create table user1(
id varchar2(10),
username varchar2(20),
sex varchar2(10),
minzu varchar2(10)
);
insert into user1 values(1,'小明','男','汉族');
insert into user1 values(1,'小芳','女','傣族');
commit;
select * from user1
<option value="0">男</option>
<option value="1">女</option>
-----------------------------
drop table user1;
create table user1(
id varchar2(10) primary key,
user_name varchar2(20),
sex number(1),
minzu number(2)
);
insert into user1 values(1,'小明',1,1);
insert into user1 values(2,'小芳',0,2);
commit
select * from user1
drop table dicts
create table dicts(
id number primary key,
display varchar2(100), -- 显示内容
key_value number, -- 真实的值
key_type varchar2(10), -- 类别
lang varchar2(10) -- 语言
);
insert into dicts values(1,'男',1,'sex','cn');
insert into dicts values(2,'女',0,'sex','cn');
insert into dicts values(3,'boy',1,'sex','en');
insert into dicts values(4,'girl',0,'sex','en');
insert into dicts values(5,'汉族',1,'minzu','cn');
insert into dicts values(6,'傣族',2,'minzu','cn');
insert into dicts values(7,'hanzu',1,'minzu','en');
insert into dicts values(8,'daizu',2,'minzu','en');
select * from dicts
-- 传统的关联方式
select
user1.id,
user_name,
dict_sex.display sex,--sex,
dict_minzu.display minzu--minzu
from user1,dicts dict_sex,dicts dict_minzu
where
user1.sex=dict_sex.key_value and dict_sex.key_type='sex' and dict_sex.lang='en'
and
user1.minzu=dict_minzu.key_value and dict_minzu.key_type='minzu' and dict_minzu.lang='en'
-- 传统的关联方式2
select * from user1
select * from dicts where lang='cn' and key_type='sex'
select * from dicts where lang='cn' and key_type='minzu'
select user1.id,user_name,dict1.display sex,dict2.display minzu from
user1,
(select * from dicts where lang='cn' and key_type='sex') dict1,
(select * from dicts where lang='cn' and key_type='minzu') dict2
where
user1.sex=dict1.key_value
and
user1.minzu=dict2.key_value
-- 优化
select
user1.id,
user_name,
dict1.display sex,
dict2.display minzu
from user1
left join (select * from dicts where lang='cn' and key_type='sex') dict1
on user1.sex = dict1.key_value
left join (select * from dicts where lang='cn' and key_type='minzu') dict2
on user1.minzu = dict2.key_value
select
user1.id,
user_name,
nvl(dict1.display,'未知') sex,
nvl(dict2.display,'未知') minzu
from user1
left join (select * from dicts where lang='cn' and key_type='sex') dict1
on user1.sex = dict1.key_value
left join (select * from dicts where lang='cn' and key_type='minzu') dict2
on user1.minzu = dict2.key_value
-- 实验
select * from user1
insert into user1 values(4,'小红',2,1);
insert into user1 values(5,'小林',1,3);
commit
-- 观察内关联的结果
（小红和小林都丢了）
-- 观察左关联的结果
-- 继续对右表操作
select * from dicts
insert into dicts values(9,'外星族',99,'minzu','cn');
commit
select null from dual
select nvl('123','未知') from dual
select nvl(null,'未知') from dual
select * from dicts
insert into dicts values(10,'维吾尔族',3,'minzu','cn')
commit