Oracle一对多

-- 一对多 （订单order,订单明细orderDetail）
-- 主从表
-- 订单表
create table t_order (
   order_id number primary key,
   today date,
   desk_num varchar2(10)
);
insert into t_order(order_id,today,desk_num)
values(1,to_date('2018-03-12','yyyy-MM-dd'),'A05');

insert into t_order(order_id,today,desk_num)
values(2,to_date('2018-03-12','yyyy-MM-dd'),'B04');

insert into t_order(order_id,today,desk_num)
values(3,to_date('2018-03-11','yyyy-MM-dd'),'B01');

commit
select * from t_order

-- 订单明细表
drop table t_order_detail
create table t_order_detail(
   id number primary key,
   order_id number,  -- 关系
   food_name varchar2(20),-- 食品名字
   unit_name varchar2(20),-- 单位
   price number(6,2),  --单价
   nums number         --数量
);

insert into t_order_detail
(id,order_id,food_name, unit_name, price,nums)
values
(1,1,'麻婆豆腐','块',1.5,12);
insert into t_order_detail
(id,order_id,food_name, unit_name, price,nums)
values
(2,1,'东坡肉','块',15,3);


insert into t_order_detail
(id,order_id,food_name, unit_name, price,nums)
values
(3,2,'麻婆豆腐','块',1.5,5);
insert into t_order_detail
(id,order_id,food_name, unit_name, price,nums)
values
(4,2,'上海青','斤',6.5,2);
insert into t_order_detail
(id,order_id,food_name, unit_name, price,nums)
values
(5,2,'香干火锅肉','斤',16.5,2);
commit

select * from t_order
select * from t_order_detail

-- 今天赚了多少钱
select to_char(sysdate,'yyyy-MM-dd') from dual
-- 找出2张订单ID
select order_id from t_order 
where 
to_char(today,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')

select sum(price*nums) as 总收入 from t_order_detail
where order_id in(
   select order_id from t_order 
   where 
   to_char(today,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')
)
 
--本质：内关联
select * from t_order,t_order_detail
where t_order.order_id=t_order_detail.order_id
--左关联（左为主，右为辅）
select * from t_order
left join t_order_detail
on
t_order.order_id=t_order_detail.order_id

-- 一对多（订单order,订单明细orderDetail）

-- 主从表

-- 订单表

createtable t_order (

order_id numberprimarykey,

today date,

desk_num varchar2(10)

);

insertinto t_order(order_id,today,desk_num)

values(1,to_date('2018-03-12','yyyy-MM-dd'),'A05');

insertinto t_order(order_id,today,desk_num)

values(2,to_date('2018-03-12','yyyy-MM-dd'),'B04');

insertinto t_order(order_id,today,desk_num)

values(3,to_date('2018-03-11','yyyy-MM-dd'),'B01');

commit

select * from t_order

-- 订单明细表

droptable t_order_detail

createtable t_order_detail(

idnumberprimarykey,

order_id number, -- 关系

food_name varchar2(20),-- 食品名字

unit_name varchar2(20),-- 单位

price number(6,2), --单价

nums number --数量

);

insertinto t_order_detail