oracle 基本查询
建表语句
序列
create sequence seq_jx
increment by 1
start with
1
nomaxvalue
nominvalue
cache 10;
字典类型表
create table sys_dict_type(
id number(20) primary key,
name varchar2(100) ,
type varchar2(100) ,
group_code varchar2(100) ,
status char(1)
);
comment on able sys_dict_type is '系统字典类型表';
comment on olumn sys_dict_type.name is '字典名称';
comment on olumn sys_dict_type.type is '字典类型编码';
comment on olumn sys_dict_type.group_code is '字典分组 (system:系统字典)';
comment on olumn sys_dict_type.status is '状态 (0:正常 1:停用)';
insert into sys_dict_type values(seq_jx.nextval,'性别','gender','system','0');
insert into sys_dict_type values(seq_jx.nextval,'模块','module','system','0');
字典值表
create table sys_dict_data(
id number(20) primary key,
type varchar2(100) ,
group_code varchar2(100) ,
label varchar2(100) ,
value varchar2(100) ,
default_value char(1),
status char(1)
);
comment on able sys_dict_data is '系统字典值表';
comment on olumn sys_dict_data.type is '系统字典类型表';
comment on olumn sys_dict_data.group_code is '字典类型编码';
comment on olumn sys_dict_data.label is '字典标签';
comment on olumn sys_dict_data.value is '字典键值';
comment on olumn sys_dict_data.default_value is '是否默认值 (Y:是 N:否)';
comment on olumn sys_dict_data.status is '状态 (0:正常 1:停用)';
insert into sys_dict_data values(seq_jx.nextval,'gender','system','男','male','Y','0');
insert into sys_dict_data values(seq_jx.nextval,'gender','system','女','female','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','system','系统','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','auth','授权与认证','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','order','订单','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','product','商品','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','member','会员','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','pay','支付','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','stock','库存','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','cart','购物车','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','cms','内容管理','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','report','报表','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','promotion','促销','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','seller','商家','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','search','搜索','N','0');
分页查询
select * from (select rownum no, a.* from sys_dict_data a) where no between 1 and 5;
select * from sys_dict_data where rownum between 1 and 5;
select * from sys_dict_data where rownum < 7 minus select * from sys_dict_data where rownum < 1;
七种 sql join
添加数据
插入两条数据
insert into sys_dict_type values(seq_jx.nextval,'left','left','left','0');
insert into sys_dict_data values(seq_jx.nextval,'right','right','right','right','N','0');
现在两张表的数据如下
左表
SQL> select * from sys_dict_type;
ID NAME TYPE GROUP_CODE STATUS
---------- --------------- --------------- --------------- ---------------
1 性别 gender system 0
21 模块 module system 0
35 left left left 0
SQL>
右表
SQL> select * from sys_dict_data;
ID TYPE GROUP_CODE LABEL VALUE DEFAULT_VALUE STATUS
---------- --------------- --------------- --------------- --------------- --------------- ---------------
11 gender system 男 male Y 0
12 gender system 女 female N 0
22 module system system 系统 N 0
23 module system auth 授权与认证 N 0
24 module system order 订单 N 0
25 module system product 商品 N 0
26 module system member 会员 N 0
27 module system pay 支付 N 0
28 module system stock 库存 N 0
29 module system cart 购物车 N 0
30 module system cms 内容管理 N 0
31 module system report 报表 N 0
32 module system promotion 促销 N 0
33 module system seller 商家 N 0
34 module system search 搜索 N 0
41 right right right right N 0
已选择16行。
SQL>
第一种
select
distinct t1.name,
t1.type,
t1.group_code,
t2.label,
t2.value,
t2.default_value
from
sys_dict_type t1
left join sys_dict_data t2
on
t1.type = t2.type
and t1.group_code = t2.group_code;
第二种
select
distinct t1.name,
t1.type,
t1.group_code,
t2.label,
t2.value,
t2.default_value
from
sys_dict_type t1
left join sys_dict_data t2
on
t1.type = t2.type
and t1.group_code = t2.group_code
where t2.value is not null;
第三种
select
distinct t1.name,
t1.type,
t1.group_code,
t2.label,
t2.value,
t2.default_value
from
sys_dict_type t1
right join sys_dict_data t2
on
t1.type = t2.type
and t1.group_code = t2.group_code;
第四种
select
distinct t1.name,
t1.type,
t1.group_code,
t2.label,
t2.value,
t2.default_value
from
sys_dict_type t1
right join sys_dict_data t2
on
t1.type = t2.type
and t1.group_code = t2.group_code
where t1.type is not null;
第五种
select
distinct
t1.name,
t1.type,
t1.group_code,
t2.label,
t2.value,
t2.default_value
from
sys_dict_type t1
inner join sys_dict_data t2
on
t1.type = t2.type
and t1.group_code = t2.group_code;
第六种
select
distinct
t1.name,
t1.type,
t1.group_code,
t2.label,
t2.value,
t2.default_value
from
sys_dict_type t1
full outer join sys_dict_data t2
on
t1.type = t2.type
and t1.group_code = t2.group_code
where
t1.group_code is null
or t2.group_code is null;
第七种
select
distinct
t1.name,
t1.type,
t1.group_code,
t2.label,
t2.value,
t2.default_value
from
sys_dict_type t1
full outer join sys_dict_data t2
on
t1.type = t2.type
and t1.group_code = t2.group_code;
