1 DROP DATABASE IF EXISTS order_food;
2 CREATE DATABASE order_food DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
3 use order_food;
4
5
6 -- 权限表
7 DROP TABLE IF EXISTS c_quanxian;
8 CREATE TABLE c_quanxian (
9 q_id int ,
10 q_Name varchar(20),
11 PRIMARY KEY (q_id)
12 );
13
14 insert into c_quanxian values(1,'超级管理员');
15 insert into c_quanxian values(2,'商家');
16 insert into c_quanxian values(3,'用户');
17 select * from c_quanxian;
18
19 -- 用户表
20 DROP TABLE IF EXISTS c_user;
21 CREATE TABLE c_user (
22 u_id int AUTO_INCREMENT,
23 u_nickName varchar(20),
24 u_loginName varchar(20),
25 u_loginPassword varchar(20),
26 u_sex varchar(20),
27 u_phone varchar(20),
28 u_address varchar(100),
29 u_birth DATE,
30 u_quanxian int,
31 PRIMARY KEY (u_id)
32 )AUTO_INCREMENT=1001;
33
34 insert into c_user values(null,'admin','admin','admin','男','15671134302','湖北武汉','1999-11-16',1);
35 insert into c_user values(null,'张三','张三','123','男','15671134302','湖北武汉','1999-11-16',3);
36 insert into c_user values(null,'李四','李四','123','女','15671134302','湖北武汉','2000-11-01',3);
37 insert into c_user values(null,'王五','王五','123','男','15671134302','湖北武汉','2006-11-20',2);
38 insert into c_user values(null,'詹柳','詹柳','123','男','15671134302','湖北武汉','2006-11-20',2);
39 insert into c_user values(null,'666','666','123','男','15671134302','湖北武汉','2006-11-20',2);
40 select * from c_user;
41
42 --
43 -- -- 菜系表
44 -- DROP TABLE IF EXISTS caixi;
45 -- CREATE TABLE caixi (
46 -- caixi_id int AUTO_INCREMENT,
47 -- caixi_Name varchar(20),
48 -- caixi_beizhu varchar(200) ,
49 -- PRIMARY KEY (caixi_id)
50 -- )AUTO_INCREMENT=1110001;
51 -- insert into caixi values(null,'湖北菜');
52 -- insert into caixi values(null,'湖南菜');
53 -- insert into caixi values(null,'广东菜');
54 -- insert into caixi values(null,'广西菜');
55 -- insert into caixi values(null,'上海菜');
56 -- insert into caixi values(null,'浙江菜');
57 -- select * from caixi;
58 --
59 --
60
61 -- 商户表
62 DROP TABLE IF EXISTS shanghu;
63 CREATE TABLE shanghu (
64 shanghu_id int AUTO_INCREMENT,
65 shanghu_u_id int,
66 shanghu_Name varchar(20),
67 shanghu_is_keyong int, -- 1可用，0不可用
68 PRIMARY KEY (shanghu_id),
69 FOREIGN KEY (shanghu_u_id) REFERENCES c_user (u_id)on delete CASCADE
70 )AUTO_INCREMENT=110001;
71 insert into shanghu values(null,1004,'鸡公煲',1);
72 insert into shanghu values(null,1005,'襄阳牛肉面',1);
73 insert into shanghu values(null,1004,'天天香川菜馆',1);
74 select * from shanghu;
75 -- DELETE from shanghu where shanghu_Name='天天香川菜馆';
76
77
78
79
80 -- 菜品表
81 DROP TABLE IF EXISTS caiping;
82 CREATE TABLE caiping (
83 caiping_id int AUTO_INCREMENT,
84 caiping_shanghu_id int ,
85 caiping_Name varchar(20),
86 caiping_jiage float ,
87 caiping_beizhu varchar(200) ,
88 PRIMARY KEY (caiping_id),
89 FOREIGN KEY (caiping_shanghu_id) REFERENCES shanghu (shanghu_id)on delete CASCADE
90 )AUTO_INCREMENT=1220001;
91
92 insert into caiping values(null,110003,'鱼香肉丝',20.0,'无');
93 insert into caiping values(null,110003,'小草鸡',30.0,'无');
94 insert into caiping values(null,110003,'酸菜鱼',33.0,'无');
95 insert into caiping values(null,110003,'麻辣虾',50.0,'无');
96 insert into caiping values(null,110003,'麻婆豆腐',15.0,'无');
97 insert into caiping values(null,110003,'青椒肉丝',20.0,'无');
98
99 insert into caiping values(null,110001,'青椒肉丝',20.5,'无');
100 insert into caiping values(null,110001,'青椒肉丝',20.0,'无');
101 insert into caiping values(null,110001,'青椒肉丝',20.0,'无');
102 insert into caiping values(null,110001,'青椒肉丝',20.5,'无');
103 select * from caiping;
104
105
106
107 -- 购物车表
108 DROP TABLE IF EXISTS gouwuche;
109 CREATE TABLE gouwuche (
110 -- gouwuche_id int AUTO_INCREMENT,
111 gouwuche_u_id int ,
112 gouwuche_caiping_id int ,
113 gouwuche_shuliang int ,
114 FOREIGN KEY (gouwuche_u_id) REFERENCES c_user (u_id)on delete CASCADE,
115 FOREIGN KEY (gouwuche_caiping_id) REFERENCES caiping (caiping_id)on delete CASCADE
116 );
117
118 insert into gouwuche values(1001,1220001,1);
119 insert into gouwuche values(1001,1220002,1);
120 insert into gouwuche values(1001,1220003,1);
121 insert into gouwuche values(1001,1220004,1);
122 insert into gouwuche values(1001,1220005,1);
123 select * from gouwuche;
124
125 -- 账户表
126
127 DROP TABLE IF EXISTS zhanghu;
128 CREATE TABLE zhanghu (
129 zhanghu_u_id int ,
130 zhanghu_yu_e FLOAT ,
131 zhanghu_is_keyong int, -- 1可用，0不可用
132 PRIMARY KEY (zhanghu_u_id),
133 FOREIGN KEY (zhanghu_u_id) REFERENCES c_user (u_id)on delete CASCADE
134 )AUTO_INCREMENT=1420001;
135
136 insert into zhanghu values(1001,1000.5,1);
137 insert into zhanghu values(1002,3000.5,1);
138 insert into zhanghu values(1003,2000.5,1);
139 insert into zhanghu values(1004,4000.5,1);
140 insert into zhanghu values(1005,6050.5,1);
141 select * from zhanghu;
142
143
144
145
146 -- 订单表
147
148 DROP TABLE IF EXISTS dingdan;
149 CREATE TABLE dingdan (
150 dingdan_id int AUTO_INCREMENT,
151 dingdan_u_id int ,
152 dingdan_caiping_id int ,
153
154 dingdan_count_jiage float,
155
156 dingdan_liuyan varchar(200),
157 PRIMARY KEY (dingdan_id),
158 FOREIGN KEY (dingdan_u_id) REFERENCES c_user (u_id)on delete CASCADE,
159 FOREIGN KEY (dingdan_caiping_id) REFERENCES caiping (caiping_id)
160 )AUTO_INCREMENT=1520001;
161
162 insert into dingdan values(null,1001,1220001,20,'微辣');
163 insert into dingdan values(null,1001,1220001,20,'多加点米饭，米饭往死里压');
164 insert into dingdan values(null,1001,1220001,20,'');
165 insert into dingdan values(null,1001,1220001,20,'');
166 select * from dingdan;
167
168
169 -- 订单流程表
170
171 DROP TABLE IF EXISTS liucheng;
172 CREATE TABLE liucheng (
173 liucheng_id int AUTO_INCREMENT,
174 liucheng_u_id int,
175 liucheng_dingdan_id int ,
176
177 liucheng_dingdan_start_time DATETime,-- 下单时间
178 liucheng_dingdan_sure_time DATETime, -- 商家确认时间
179 liucheng_dingdan_end_time DATETime, -- 确认收货,订单完成时间
180
181 dingdan_is_wancheng int, -- 1下单，2商家确认，0确认收货完成订单
182 PRIMARY KEY (liucheng_id),
183
184 FOREIGN KEY (liucheng_u_id) REFERENCES c_user (u_id)on delete CASCADE,
185 FOREIGN KEY (liucheng_dingdan_id) REFERENCES dingdan (dingdan_id)on delete CASCADE
186 )AUTO_INCREMENT=1620001;
187 insert into liucheng values(null,1001,1520001,NOW(),NOW(),'2022-12-09',0);
188 insert into liucheng values(null,1001,1520001,NOW(),NOW(),'2022-12-09',1);
189 insert into liucheng values(null,1001,1520001,NOW(),NOW(),'2022-12-09',2);
190 select * from liucheng;
191
192 -- 留言表
193
194 DROP TABLE IF EXISTS liuyan;
195 CREATE TABLE liuyan (
196 liuyan_id int AUTO_INCREMENT,
197 liuyan_u_id int,
198
199 liuyan_time DATETime,-- 留言时间
200 liuyan_xinxi varchar(200),
201 PRIMARY KEY (liuyan_id),
202 FOREIGN KEY (liuyan_u_id) REFERENCES c_user (u_id)on delete CASCADE
203 )AUTO_INCREMENT=2020001;
204 insert into liuyan values(null,1001,NOW(),'味道真的不错，朋友都喜欢吃。今天还是比较幸运的，外卖很快就送来了。量也是绝对是够的。');
205 insert into liuyan values(null,1002,NOW(),'这款产品比较好，色泽比较好，而且价格优惠价格实惠，服务周到，及时，好评！');
206 insert into liuyan values(null,1003,NOW(),'每一次都服务很好但是这家店铺，还是相对我觉得可以的，有机会大家可以去试试看。');
207 select * from liuyan;
208
209 select * from c_user;
210 select * from caiping;
211 select * from shanghu;
212 select * from c_quanxian;
213
214 -- 商品展示查询
215 select u.u_id,u.u_nickName,u.u_loginName,
216 sh.shanghu_id,sh.shanghu_Name,sh.shanghu_is_keyong,
217 c.caiping_id,c.caiping_Name,c.caiping_jiage,c.caiping_beizhu
218 from caiping as c,shanghu as sh ,c_user as u
219 where
220 c.caiping_shanghu_id = sh.shanghu_id and
221 u.u_id=sh.shanghu_u_id
222
223 -- 5.删除课程名为'数据库系统' 的所有信息 注意级联删除
224 -- delete from 孙武浩_课程 c where c.课程名称='数据库系统';
225
226 -- 菜品展示视图
227 drop view if exists caipinShow;
228 create view caipinShow
229 AS
230 (
231 select u.u_id,u.u_nickName,u.u_loginName,u.u_quanxian,
232 sh.shanghu_id,sh.shanghu_Name,sh.shanghu_is_keyong,
233 c.caiping_id,c.caiping_Name,c.caiping_jiage,c.caiping_beizhu,
234 qx.q_id
235 from caiping as c,shanghu as sh ,c_user as u,c_quanxian as qx
236 where
237 c.caiping_shanghu_id = sh.shanghu_id and
238 u.u_id=sh.shanghu_u_id and
239 u_quanxian=qx.q_id
240 );
241 select * from caipinShow;
242
243 -- 购物车展示视图
244 drop view if exists gouwucheShow;
245 create view gouwucheShow
246 AS
247 (
248 select
249 u.u_id,u.u_nickName,u.u_loginName,u.u_quanxian,
250 sh.shanghu_id,sh.shanghu_Name,sh.shanghu_is_keyong,sh.shanghu_u_id,
251 c.caiping_id,c.caiping_Name,c.caiping_jiage,c.caiping_beizhu,
252 qx.q_id,
253 gwc.gouwuche_caiping_id,gwc.gouwuche_shuliang,gwc.gouwuche_u_id
254 from caiping as c,shanghu as sh,c_user as u,c_quanxian as qx,gouwuche as gwc
255 where
256 c.caiping_shanghu_id = sh.shanghu_id and
257 u.u_id=sh.shanghu_u_id and
258 u_quanxian=qx.q_id and
259 gwc.gouwuche_caiping_id=c.caiping_id
260 );
261 select * from gouwucheShow;
262
263
264 -- 订单页展示视图
265
266 drop view if exists dingdanShow;
267 create view dingdanShow
268 AS
269 (
270 select
271 u.u_id,u.u_nickName,u.u_loginName,
272 sh.shanghu_id,sh.shanghu_Name,sh.shanghu_u_id,
273 c.caiping_id,c.caiping_Name,c.caiping_jiage,c.caiping_beizhu,
274 qx.q_id,
275 dd.dingdan_id,dd.dingdan_caiping_id,dd.dingdan_u_id,dd.dingdan_liuyan,dd.dingdan_count_jiage,
276 lc.liucheng_id,lc.liucheng_u_id,lc.liucheng_dingdan_id,lc.liucheng_dingdan_start_time,lc.liucheng_dingdan_sure_time,lc.liucheng_dingdan_end_time,lc.dingdan_is_wancheng
277 from caiping as c,shanghu as sh,c_user as u,c_quanxian as qx,dingdan as dd,liucheng as lc
278 where
279 c.caiping_shanghu_id = sh.shanghu_id and
280 u_quanxian=qx.q_id and
281 dd.dingdan_u_id=u.u_id and
282 lc.liucheng_u_id=u.u_id
283 );
284 select * from dingdanShow;
285
286
287
288
289
290 select * from c_user;
291 select * from caiping;
292 select * from shanghu;
293 select * from dingdan;
294 select * from c_quanxian;
295 select * from gouwuche;
296 select * from liucheng;
浙公网安备 33010602011771号