简单超市信息管理数据库模型，sql实现~

某(超市、购物网站、淘宝、……)商品管理有以下四个数据表：

流程： 购买商品----->加入商品明细表------>生成订单表

/**

商品表(商品编号,商品名称,商品售价,最新库存)
·product:p_id,p_name,p_price,kucun
*/
create table t_product(
p_id varchar2(10) primary key,
p_name varchar2(20) not null,
p_price number(10, 2),
kucun number(10)
);

/**
客户表：客户卡号、客户姓名、办卡日期、卡类型(1-普通卡；2-VIP卡)
·customer:c_id,c_name,c_date,c_type
*/

create table t_customer(
c_id varchar2(10) primary key,
c_name varchar2(10) not null,
c_date date,
c_type number(1)
);
/**
订单表/小票(订单号,客户卡号,订单日期,订单总价)
·order:order_id,c_id,o_date,total_price
*/

create table t_order(
order_id varchar2(10) primary key,
c_id varchar2(10),
o_date date,
total_price number(10, 2)
);

/**
小票的详细内容
订单明细表 (流水号,订单号,产品号,商品名称,商品数量,购买单价)
·order_items:autoid,order_id,p_id, p_name, p_num, p_price
*/

create table t_items(
autoid number(10) primary key,
order_id varchar2(10),
p_id varchar2(10),
p_name varchar2(20),
p_num number(5),
p_price number(10, 2)
);

1、写出添加商品语句(P001,洗发水,20元；P002,沐浴露,10元)
insert into t_product(p_id, p_name, p_price, kucun)
values ('p001', '洗发水', 20, 50);
insert into t_product(p_id, p_name, p_price, kucun)
values ('p002', '沐浴露', 10, 100);
commit;

2、写出客户办卡语句(卡号K0123,张三,2008-5-6,VIP卡)
insert into t_customer(c_id, c_name, c_date, c_type)
values ('k0123', '张三', to_date('2008-6-5', 'yyyy-dd-mm'), 2);
insert into t_customer(c_id, c_name, c_date, c_type)
values ('k0125', '李四', to_date('2008-8-9', 'yyyy-dd-mm'), 2);
commit;
select * from t_customer;

3、写出洗发水降价10%的SQL语句
update t_product tp set tp.p_price = tp.p_price * (1-0.1)
where tp.p_id = 'p002';
commit;

select * from t_product;

--创建序列

create sequence seq_pro;
4、仔细观察表格结构,写出客户买了2瓶洗发水以后和3瓶沐浴露以后,应该使用哪些SQL语句
--在商品明细表中加入商品信息，模拟扫描商品的条形码
insert into t_items(autoid, order_id, p_id, p_num)
values (seq_pro.nextval, '100053', 'p001', 5);
insert into t_items(autoid, order_id, p_id, p_num)
values (seq_pro.nextval, '100053', 'p002', 2);
--订单表中加入订单信息，模拟扫描用户的会员卡
insert into t_order(c_id)
values('k0125');

--提交

commit;

--回滚

rollback;

--查询测试

select * from t_items;
select * from t_product;
select * from t_order;
5、查询张三所有消费明细信息：客户编号、客户姓名、订单号、订单总价,订单日期
create or replace view v_order
as

--查询语句（顺便用于视图）
select  o.c_id,  tc.c_name,  o.order_id,  o.total_price,  o.o_date
from  t_order  o,  t_customer  tc
where  o.c_id = tc.c_id;
6、查询张三消费金额最大的一张订单
create or replace view v_max_order
as

--查询语句（顺便用于视图）
select * from  v_order  v
where v.total_price = (select max(v.total_price) from  v_order  v);

7、查询张三消费金额最大的一张订单中的商品信息：商品编号,商品名称,购买数量,购买金额
select  ti.p_id,  ti.p_name,  ti.p_num,  ti.p_price
from  t_items  ti,  v_max_order  vmo
where  vmo.order_id = ti.order_id;

8、查询所有客户,购买价格高于目前商品售价所有订单信息：客户号,客户名称,订单号,产品号,购买单价,商品售价
select tc.c_id 客户号, tc.c_name 客户名, ti.order_id 订单号,
       ti.p_id 商品号, ti.p_name 商品名, ti.p_price 购买价格, tp.p_price 现价
from t_product tp join t_items ti
--比较客户购买价格与目前商品价格
on ti.p_price > tp.p_price and ti.p_id = tp.p_id
join t_order o
on ti.order_id = o.order_id
join t_customer tc
on o.c_id = tc.c_id;

/**
  触发器1
  在明细表中插入商品信息
  使用原因：为了减少信息冗余，条形码中只用保留商品的编号，
  而商品名称和商品的价格可以通过商品ID从商品表中查到
*/
create or replace trigger tri_items
--因为是在提交前获得的信息，所以使用before
before insert

on t_items

for each row

declare
    temp_p_name t_product.p_name%type;
    temp_price t_product.p_price%type;
begin
    --通过商品ID获得商品名称
    select p_name into temp_p_name from t_product where :new.p_id = p_id;
    --通过商品ID获得商品价格
    select p_price into temp_price from t_product where :new.p_id = p_id;
   
    :new.p_name := temp_p_name;
    :new.p_price := temp_price;  
end;

/**
   触发器2
   购买商品后要在商品表中的库存减去购买的商品
*/

 

create or replace trigger tri_product
--因为是数据提交后才对库存进行操作，所以用after
after insert

on t_items

for each row

declare
    temp_kucun t_product.kucun%type;
begin
    --从商品表中获取库存信息
    select kucun into temp_kucun from t_product where p_id = :new.p_id;
    --如果购买商品大于库存才出错
    if :new.p_num > temp_kucun then
         raise_application_error(-20000,'库存不够');
    end if;
    --修改库存信息
    update t_product set kucun = kucun - :new.p_num where p_id = :new.p_id;

end;

/**
   触发器3
   扫描客户ID后，生成订单信息
*/
create or replace trigger tri_order
--因为在插入订单表前进行，所以要用before
before insert

on t_order

for each row

declare
    temp_order_id t_order.order_id%type;
    temp_total t_order.total_price%type;
begin
    --获取最大的订单号，说明是最新订单
    select  max(order_id) into temp_order_id from t_items;
   
    --将最新订单的价格总和算出来
    select sum(p_price * p_num) into temp_total from t_items
    where order_id = (select  max(order_id) from t_items);
   
    --最新订单号
    :new.order_id := temp_order_id;
    --订单产生日期为当前日期
    :new.o_date := sysdate;
    --该订单的总价格
    :new.total_price := temp_total;
end;