外卖数据库系统

show databases
create database if not exists trade
use trade

 

create table if not exists store
(Sno varchar(20) not null primary key ,
Spass varchar(20) ,
Sname varchar (50) not null ,
Saddr varchar(50) not null ,
Stel varchar(12) not null,
Smoney int ,
Sstate varchar(20),
check(Sstate in('工作','休息')),
check(Smoney>=0)
);

create table if not exists goods
(Gno varchar(20) not null primary key,
Sno varchar(20) ,
Gname varchar(50),
Gprice int ,
Gstock int ,
foreign key(Sno) references store(Sno)
);

create table if not exists customer
(Cno varchar(20) not null primary key,
Cpass varchar(20),
Cname varchar(50),
Csex varchar(2),
Caddress varchar(50),
Ctel varchar(12) not null ,
Cmoney int ,
check(Csex in('男','女')),
check(Cmoney>=0)
);

 

create table if not exists deliverer
(Dno varchar(20) not null primary key,
Dpass varchar(20) ,
Dname varchar(50),
Dsex varchar(2),
Dtel varchar(12) not null,
Dmoney int ,
Dstate varchar(20),
check(Dsex in ('男','女')),
check(Dstate in ('工作','休息')),
check(Dmoney>=0)
);

 

 

create table if not exists orderer
(Ono varchar(20) not null primary key,
Dno varchar(20),
Cno varchar(20) not null ,
Sno varchar(20),
Ostate varchar(20) not null,
Otip varchar(50),
Odelfee int ,
Omoney int ,
Obtime datetime,
FOREIGN KEY (Dno) REFERENCES Deliverer(Dno),
FOREIGN KEY (Cno) REFERENCES Customer(Cno) ,
FOREIGN KEY (Sno) REFERENCES Store(Sno) ,
CHECK (Omoney >= 0),
CHECK (ODelfee >= 0),
CHECK (Ostate IN('正在出餐','正在配送','订单完成'))
);

create table if not exists purchase
(Ono varchar(20) not null ,
Gno varchar(20) not null ,
Pamount int,
PRIMARY KEY (Ono, Gno ),
FOREIGN KEY (Ono) REFERENCES Orderer(Ono),
FOREIGN KEY (Gno) REFERENCES Goods(Gno)
);

-- 用户订单视图
-- 用户可以看到订单的信息
CREATE VIEW view_Cus_look AS SELECT
orderer.Ono,
store.Sname,
orderer.Omoney,
orderer.Ostate,
orderer.Obtime,
orderer.Cno
FROM orderer,store
WHERE store.Sno = Orderer.Sno
ORDER BY orderer.Obtime DESC ;

-- 用户点单视图
-- 用户点单时可以看见所有商家的所有菜品。用户需要看到的是商店名而不是商店编号，商品名而不是商品编号。
CREATE VIEW view_Cus_buy AS
SELECT Sname '店名',Gname '商品',Gprice '价格'
FROM goods,store
WHERE goods.Sno = store.Sno
AND store.Sstate = '工作'

//查询外卖系统位于山东的餐厅
select * from store where Saddr like '山东%';

 

//查询送餐人情况
select * from deliverer;

//查询价格大于八元的商品信息
select 店名,商品 from view_Cus_buy where 价格>8;

//查询餐厅出餐情况
select * from view_cus_look;

//查询菜品与价格
select Sname,Saddr,Gname,Gprice from store,goods where store.Sno=goods.Sno;

//查询外卖员人数
select count(*) as卖员总人数 from deliverer;

//查询各店里单价大于5的菜品信息
select Sname,Gname,Gprice from store join goods on goods.Sno=store.Sno where Gprice>5;

//查询个餐厅菜品种类数
select Sno,count(*) as 菜品数 from goods
group by Sno with rollup;

//删除某订单信息
delimiter $$
create procedure del_member(in num int )
begin
delete orderer,purchase from orderer,purchase where orderer.Ono=num and purchase.Ono=num;

end $$
delimiter ;

call del_member(02);