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 * 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