基于SQL的餐厅外卖点餐系统数据库

该数据库为一个简易的SQL数据库，没有包含系统程序

可以用来新手学习数据库相关知识或课程设计参考代码

create database waimai
on primary
(name='s_data2',  
 size=150,  
 maxsize=500,
 filegrowth=10,
 filename='D:\keshe.mdf'),
 (name='s1_data2',  
 size=150,  
 maxsize=500,
 filegrowth=10,
 filename='D:\keshe.ndf')
log on
(name='s2_log2', 
 size=100,  
 maxsize=300,
 filegrowth=30%,
 filename='D:\keshe.ldf') 
use waimai 
go
/*菜单表*/
create table caidan
(
cname varchar(20) not null ,  /*餐品名称*/
id int not null primary key,  /*餐品编号*/
price float,                  /*餐品价格*/
num int,                      /*餐品库存*/
xiaoliang int                 /*餐品销量*/
)


/*订单表（可加上价格,）*/
create table dingdan
(
usename varchar(20) not null,              /*客户用户名*/
dname varchar(50) not null ,               /*订单内容*/
jiage int not null,                        /*订单价格*/
ubianhao int not null primary key,         /*订单编号*/  
utine date not null ,                      /*下单时间*/
uarea varchar(20) not null,                /*下单地址*/
ubeizhu varchar(50)                        /*客户备注*/
)

/*成本表*/
create table chengben
(
cname varchar(20) not null ,                           /*餐品名称*/
baseprice int not null primary key   ,                 /*成本价*/
yuanliao varchar(50) not null,                         /*原料*/
wholesale varchar(20) not null                         /*批发地*/
)

/*餐品详情表*/
create table food
(
cname varchar(20) not null ,                           /*餐品名称*/
yuanliao varchar(50) not null,                         /*原料*/
taste varchar(20) not null,                            /*餐品口味*/
cook varchar(20) not null primary key                  /*烹饪方式*/
)

create table useer
(
cellphone int not null primary key,                         /*联系方式*/
usename varchar(20) not null,                               /*客户用户名*/
usex char(2) check (usex='男' or usex='女') not null,       /*客户性别*/
uage int not null,                                          /*用户年龄*/
taste varchar(50) not null,                                  /*餐品口味*/
uarea varchar(50) not null,                                 /*下单地址*/  
                     
)

create table mendian
(
muse varchar(20) not null primary key,              /*门店管理员*/
mid int not null ,                                  /*门店电话*/
mname varchar(20) not null,                         /*门店名称*/
uarea varchar(20) not null,                         /*门店地址*/
)


/*菜单表插入数据*/
insert into caidan values('烤肉拼盘',0101,50,120,100)
insert into caidan values('特色海鲜锅',0102,88,220,120)
insert into caidan values('蔬菜沙拉',0103,18,60,88)
insert into caidan values('牛肉炒饭',0104,28,100,50)
insert into caidan values('蔬菜汤',0105,25,60,30)
insert into caidan values('精致牛肉粒',0106,38,300,68)
insert into caidan values('清蒸海鱼',0107,68,50,110)
insert into caidan values('水果冰淇淋',0108,11,999,77)
insert into caidan values('卤味拼盘',0109,38,50,110)
insert into caidan values('脆皮烧鹅',0110,78,50,130)
insert into caidan values('解腻水果茶',0111,12,9100,30)




/*订单表插入数据*/
insert into dingdan values('张三','烤肉拼盘，特色海鲜锅',138,231201,'2023-12-01 11:20','A区','多加香菜')
insert into dingdan values('李四','牛肉炒饭，蔬菜汤',53,231202,'2023-12-01 12:23','C区','炒饭少辣')
insert into dingdan values('依依','特色海鲜锅,牛肉炒饭,蔬菜汤',141,231203,'2023-12-03 18:45','B区','')
insert into dingdan values('丫丫','清蒸海鱼,蔬菜汤',94,231204,'2023-12-04 19:15','A区','炒饭不要葱')
insert into dingdan values('小白','精致牛肉粒,水果冰淇淋',49,231205,'2023-12-04 17:23','B区','')
insert into dingdan values('小帅','烤肉拼盘,蔬菜沙拉',68,231206,'2023-12-05 12:20','D区','沙拉酱多放一点')
insert into dingdan values('小美','烤肉拼盘,牛肉炒饭,水果冰淇淋',89,231207,'2023-12-05 18:07','D区','')
insert into dingdan values('陈野','清蒸海鱼,特色海鲜锅',156,231208,'2023-12-05 12:22','D区','沙拉酱多放一点')
insert into dingdan values('小帅','烤肉拼盘,脆皮烧鹅,解腻水果茶',140,231209,'2023-12-06 18:05','D区','')
insert into dingdan values('莉莉','牛肉炒饭，脆皮烧鹅，蔬菜汤',128,2312010,'2023-12-07 18:20','D区','')
insert into dingdan values('陈野','清蒸海鱼,解腻水果茶',80,231211,'2023-12-05 12:22','D区','沙拉酱多放一点')
insert into dingdan values('陈野','烤肉拼盘,解腻水果茶',62,231212,'2023-12-06 18:05','D区','')
insert into dingdan values('丫丫','牛肉炒饭，脆皮烧鹅',106,2312013,'2023-12-07 18:20','D区','炒饭多加辣')





/*成本原料表数据*/
insert into chengben values('烤肉拼盘',20,'鸡肉猪肉牛肉','广东')
insert into chengben values('特色海鲜锅',40,'各类海鲜','福建')
insert into chengben values('蔬菜沙拉',8,'紫甘蓝新鲜蔬菜','福建')
insert into chengben values('牛肉炒饭',13,'牛肉,大米','青海')
insert into chengben values('蔬菜汤',10,'当季新鲜蔬菜','福建')
insert into chengben values('精致牛肉粒',18,'西北特色牛肉','青海')
insert into chengben values('清蒸海鱼',30,'新鲜海鱼','福建')
insert into chengben values('水果冰淇淋',6,'当季水果,动物奶油','福建')
insert into chengben values('卤味拼盘',28,'各类肉','福建')
insert into chengben values('脆皮烤鹅',45,'大鹅','广东')
insert into chengben values('解腻水果茶',5,'当季水果','福建')



/*菜品详情数据*/
insert into food values('烤肉拼盘','鸡肉猪肉牛肉','香','现烤')
insert into food values('特色海鲜锅','各类海鲜','原味','砂锅煮')
insert into food values('蔬菜沙拉','紫甘蓝新鲜蔬菜','咸甜','凉拌')
insert into food values('牛肉炒饭','牛肉,大米','孜然','现炒')
insert into food values('蔬菜汤','当季新鲜蔬菜','清淡','炖')
insert into food values('精致牛肉粒','西北特色牛肉','微辣','现煎')
insert into food values('清蒸海鱼','新鲜海鱼','鲜甜','清蒸')
insert into food values('水果冰淇淋','当季水果,动物奶油','酸甜','现打')
insert into food values('卤味拼盘','各类肉','微辣','现卤')
insert into food values('脆皮烤鹅','大鹅','香','慢烤')
insert into food values('解腻水果茶','解腻水果','酸甜','现做')




/*用户数据*/
insert into useer values(100000,'张三','男',24,'清淡','A区')
insert into useer values(123456,'李四','男',32,'微辣','B区')
insert into useer values(123123,'王二','男',26,'酸甜','D区')
insert into useer values(111236,'小红','女',20,'清淡','C区')
insert into useer values(666666,'依依','女',18,'香','A区')
insert into useer values(111333,'陈野','男',23,'清淡','B区')
insert into useer values(226677,'丫丫','女',20,'酸甜','D区')
insert into useer values(999999,'李雅','女',28,'孜然','C区')
insert into useer values(111111,'小白','男',22,'微辣','A区')
insert into useer values(226688,'小帅','男',25,'香','D区')
insert into useer values(159753,'莉莉','女',27,'孜然','B区')
insert into useer values(111112,'小海','男',18,'微辣','C区')




/*门店管理数据*/
insert into mendian values('A店长',111,'餐厅A店','A区')
insert into mendian values('B店长',222,'餐厅B店','B区')
insert into mendian values('C店长',333,'餐厅C店','C区')
insert into mendian values('D店长',444,'餐厅D店','D区')



/*餐品价格及成本视图*/
go
create view canpin
as
select caidan.cname,caidan.price,caidan.num,caidan.xiaoliang,chengben.baseprice
from caidan,chengben
where caidan.cname=chengben.cname




/*用户下单视图，可修改美观*/
go
create view yonghu
as
select dingdan.dname,dingdan.utine,dingdan.uarea,useer.usename,useer.usex,useer.taste
from dingdan,useer
where dingdan.uarea=useer.uarea



/*餐品介绍视图*/
go
create view finefood
as
select chengben.cname,chengben.yuanliao,chengben.wholesale,food.taste,food.cook
from chengben,food
where chengben.yuanliao=food.yuanliao





/*订单触发器*/
go
create trigger myTrigger1
on dingdan
instead of update
as
begin
  print'对不起，您无法修改订单数据';
end




/*菜单触发器删除餐品内容*/
go
create trigger myTrigger2
on caidan
after delete
as
begin
     declare @cname varchar(20);
	 select @cname=i.cname
	 from deleted as i;
	 delete from food
	 where cname=@cname;
end



/*成本存储过程，加密该信息P212*/
go
create procedure chengbenjiami with encryption
as  
    select chengben.cname,chengben.baseprice,chengben.wholesale
	from chengben;


/*连接语句查询餐品详情*/
go
select useer.usename,food.cname,food.taste,food.cook
from useer,food
where useer.taste=food.taste




/*连接语句查询用户可点的外卖信息*/
go
select useer.usename,useer.cellphone,useer.uarea,mendian.mid,mendian.uarea
from useer,mendian
where useer.uarea=mendian.uarea



/*查询D区购买烤肉拼盘的客户的订单详情*/
go
select dingdan.usename 用户名,dingdan.dname 订单内容,dingdan.jiage 价格,dingdan.uarea 地区
from dingdan
where uarea like 'D区%' and dname like '烤肉拼盘%'



/*使用in的嵌套查询蔬菜沙拉和清蒸海鱼相同原产地的餐品信息*/ 
go
select *from chengben
where chengben.wholesale in(
      select chengben.wholesale
	  from chengben
	  where chengben.cname='蔬菜沙拉'or chengben.cname='清蒸海鱼');

以上为该设计的代码，如果能够帮助到您麻烦一键三连，需要完整实验报告关注私聊谢谢