oracle常用表操作：集合操作符以及简单函数的应用

创建表格时增加列约束

create table more_product(
prd_id integer constraint m_p_pk primary key,
prd_type_id integer constraint m_p_fk references produc_types(typeid),
name varchar(30) not null,
available char(1)
)

集合操作符的使用

union all 返回各个查询检索出的所有行，包括重复的行
select id,typeid,name from product union all select prd_id,prd_type_id,name from more_product

union 返回各个查询检索出的所有行，不包括重复的行
select id,typeid,name from product union select prd_id,prd_type_id,name from more_product

intersect 返回两个查询检索出的共有行
select id,typeid,name from product intersect select prd_id,prd_type_id,name from more_product
/
minus 返回将第一个检索出的结果减去第二个检索出结果的相同行，剩余的行
select id,typeid,name from product minus select prd_id,prd_type_id,name from more_product

函数

translate(x,fromstr,tostr),在x中查找fromstr中的字符，并将其转换成to_string中对应的字符

select translate('hello world','hello','abcde') from dual
/
decode(value,search_value,result,default_value)将value与search_value进行比较，若相等，返回result,否则返回default_value

 nvl(exp1,0)若exp1 is null,返回0，否则返回 exp1

 select decode(pro_price,'',0,pro_price) as price from product，实现pro_price无值的时候返回0

 

 

 

case
select id,typeid,case typeid when 1 then 'book' when 2 then 'food' when 3 then 'cd' else 'magazine' end from product
/

 

rollup 为每一个分组返回小计，并为全部分组返回总计

 

 

从一张表中向另外一张表复制数据（复制相同表中的数据）

insert into product select 8,name,typeid,pro_price from product where id=6

使用returning子句

avg_price为已定义变量
sql>variable avg_price number;
修改商品价格并将其平均值返回到变量avg_price
update product set pro_price=pro_price *0.5 returning avg(pro_price) into :avg_price

可以使用print avg_price 打出变量值

merge合并数据

merge into product p
using more_product mp on(p.name=mp.name)
when matched then update
set p.name=mp.name,
p.id=mp.prd_id,
p.typeid=mp.prd_type_id
when not matched then
insert(p.id,p.typeid,p.name)
values(mp.prd_id,mp.prd_type_id,mp.name)