利用MySQL实现一个类似美团外卖的外卖订单的数据库管理系统

最新推荐文章于 2024-07-25 17:06:50 发布

落雪染轻愁

最新推荐文章于 2024-07-25 17:06:50 发布

阅读量3.6w

点赞数 73

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/jy1690229913/article/details/79189631

版权

MySQL 专栏收录该内容

2 篇文章 2 订阅

订阅专栏

利用MySQL实现一个类似美团外卖的外卖订单的数据库管理系统

本文的重点在于MySQL触发器的应用

1、数据库需求分析

1）数据库实体：客户、商家、商品、订单。

2）一个客户对应多个订单。

3）一个商家对应多种商品和多个订单。

4）一个订单对应一种商品。

2、系统功能分析

1）客户注册：客户通过添加必要的信息到数据库来完成注册。

2）客户信息修改：客户根据自己的编号来修改除编号外的其他信息。

3）商家注册：商家通过添加必要的信息到数据库来完成注册。

4）商家信息修改：商家根据自己的编号来修改除编号外的其他信息。

5）商品录入：商家通过添加商品必要的信息到数据库来完成商品录入。

6）商品信息修改：商家根据商品编号来修改商品除编号外的其他信息。

7）客户下单：客户通过添加订单信息到数据库来提交订单。

8）商家接单：商家通过修改订单状态来接受订单。

9）客户确认收货：客户通过修改订单状态来确认收货。

10）取消订单：客户通过修改订单状态来退款，但必须在商家接单之前退款。

11）订单状态：“0”表示已支付，“1”表示已接单，“2”表示已送达，“3”表示已退款。

3、关系模式

1）客户（客户编号，客户姓名，消费金额，客户电话，客户地址）

2）商家（商家编号，商户名称，营业额，商户电话，商户地址）

3）商品（商品编号，商品名称，商户编号，商品价格，限购数量）

4）订单（订单编号，订购时间，客户编号，商品编号，数量，总额，状态）

注：含下划线字段为主键或外键。

4、创建数据库及表

1）创建数据库

CREATE DATABASE takeout;

2）创建客户表

CREATE TABLE customers(
  id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL ,
  consumption DOUBLE(16,2) NOT NULL DEFAULT 0,
  phone VARCHAR(16) NOT NULL ,
  address VARCHAR(45) NOT NULL
);

3）创建商家表

CREATE TABLE businesses(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL ,
  turnover DOUBLE(16,2) NOT NULL DEFAULT 0,
  phone VARCHAR(16) NOT NULL ,
  address VARCHAR(45) NOT NULL
);

4）创建商品表

CREATE TABLE goods(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL ,
  business_id INT NOT NULL ,
  price DOUBLE(16,2) NOT NULL ,
  limit_num INT ,
  FOREIGN KEY (business_id) REFERENCES businesses(id)
);

5）创建订单表

CREATE TABLE orders(
  id INT NOT NULL AUTO_INCREMENT,
  order_time DATETIME NOT NULL ,
  customer_id INT NOT NULL ,
  goods_id INT NOT NULL ,
  number INT NOT NULL ,
  total_cost DOUBLE(16,2) NOT NULL ,
  state INT NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (goods_id) REFERENCES goods(id)
);

5、创建触发器

1）创建触发器前修改sql语句结束符为#

DELIMITER #

2）限购以及计算订单总额
创建之后，在客户订购的商品数量超过限购数量时，自动将订购的数量改为限购数量，然后计算出订单总额。

CREATE TRIGGER limit_buy
  BEFORE INSERT
  ON orders
  FOR EACH ROW
  BEGIN
    DECLARE p DOUBLE(16,2);  /*商品单价*/
    DECLARE lim INT;  /*限购数量*/
    SET p=(SELECT price FROM goods WHERE goods.id=NEW.goods_id);
    SET lim=(SELECT limit_num FROM goods WHERE goods.id=NEW.goods_id);
    if (lim IS NOT NULL AND NEW.number>lim) THEN
      SET NEW.number=lim;
    END IF ;
    SET NEW.total_cost=NEW.number*p;
  END #

3）更新客户消费总额
在客户提交订单之后，自动将消费金额累加到客户总的消费金额里。

CREATE TRIGGER calculate_consumption
  AFTER INSERT
  ON orders
  FOR EACH ROW
  BEGIN
    UPDATE customers
      SET consumption=consumption+NEW.total_cost
    WHERE customers.id=NEW.customer_id;
  END #

4）更新商家总营业额以及退款
商家接单后，自动将订单金额累加到商家的营业额里；客户取消订单，自动将客户总消费减去本次订单金额。

CREATE TRIGGER calculate_turnover
  BEFORE UPDATE
  ON orders
  FOR EACH ROW
  BEGIN
    IF (NEW.state=1 AND OLD.state=0) THEN
      UPDATE businesses
        SET turnover=turnover+OLD.total_cost
      WHERE id=(SELECT business_id FROM goods WHERE goods.id=NEW.goods_id);
    ELSEIF (NEW.state=3 AND OLD.state=0) THEN
      UPDATE customers
      SET consumption=consumption-OLD.total_cost
      WHERE id=OLD.customer_id;
    ELSEIF (NEW.state<OLD.state) THEN
      SET NEW.state=OLD.state;
    END IF ;
  END #

5）创建完触发器后将sql语句结束符修改回;

DELIMITER ;

6）查看触发器

SHOW TRIGGERS;

结果截图（局部）
这里写图片描述

6、客户注册及信息修改

1）客户注册

INSERT INTO customers(name, phone, address)
VALUES ('李白','18512312300','华东交通大学42公寓');
INSERT INTO customers(name, phone, address)
VALUES ('杜甫','18612312301','华东交通大学43公寓');
INSERT INTO customers(name, phone, address)
VALUES ('马云云','18712312302','华东交通大学37公寓');
INSERT INTO customers(name, phone, address)
VALUES ('马花腾','18712412303','江西理工大学11公寓');
INSERT INTO customers(name, phone, address)
VALUES ('孙悟空','18572352304','江西财经大学32公寓');
INSERT INTO customers(name, phone, address)
VALUES ('唐僧','18632314305','江西财经大学9公寓');
INSERT INTO customers(name, phone, address)
VALUES ('武则天','18712312406','江西财经大学21公寓');
INSERT INTO customers(name, phone, address)
VALUES ('独孤求败','18519312307','江西理工大学13公寓');
INSERT INTO customers(name, phone, address)
VALUES ('展昭','18112332808','江西理工大学25公寓');
INSERT INTO customers(name, phone, address)
VALUES ('赵灵儿','18112812309','华东交通大学8公寓');
INSERT INTO customers(name, phone, address)
VALUES ('东方不败','18152862310','江西财经大学34公寓');
INSERT INTO customers(name, phone, address)
VALUES ('貂蝉','18352388511','江西理工大学24公寓');

结果截图
这里写图片描述

2）客户信息修改

UPDATE customers SET phone='15789654314' WHERE id=2;
UPDATE customers SET address='华东交通大学创新大楼' WHERE id=3;
UPDATE customers SET address='江西财经大学大学生活动中心' WHERE id=7;

结果截图
这里写图片描述

7、商家注册及信息修改

1）商家注册

INSERT INTO businesses(name, phone, address)
VALUES ('回家吃饭','400123100','南昌市双港东大街333号');
INSERT INTO businesses(name, phone, address)
VALUES ('佳佳饺子馆','400123101','南昌市双港东大街531号');
INSERT INTO businesses(name, phone, address)
VALUES ('港式脆排饭','400123102','南昌市邹家自然村交通学院旁');
INSERT INTO businesses(name, phone, address)
VALUES ('膳当家','400123103','南昌市华东交通大学');
INSERT INTO businesses(name, phone, address)
VALUES ('沙县小吃','400123104','南昌市双港东大街立通大厦旁');

结果截图
这里写图片描述

2）商家信息修改

UPDATE businesses SET phone='18788484186' WHERE id=2;
UPDATE businesses SET address='南昌市江西理工大学对面' WHERE id=3;
UPDATE businesses SET address='南昌云鹏大酒店旁' WHERE id=5;

结果截图
这里写图片描述

8、商品录入及商品信息修改

1）商品录入

INSERT INTO goods(name, business_id, price, limit_num)
VALUES ('重庆鸡公煲',1,13.9,1);
INSERT INTO goods(name, business_id, price, limit_num)
VALUES ('麻辣香锅',1,12.5,5);
INSERT INTO goods(name, business_id, price, limit_num)
VALUES ('土豆烧牛腩',1,14.9,NULL);
INSERT INTO goods(name, business_id, price, limit_num)
VALUES ('韭菜猪肉馅',2,14.9,3);
INSERT INTO goods(name, business_id, price, limit_num)
VALUES ('香菇猪肉馅',2,14.9,3);
INSERT INTO goods(name, business_id, price, limit_num)
VALUES ('玉米猪肉馅',2,15.9,3);
INSERT INTO goods(name, business_id, price, limit_num)
VALUES ('黑椒脆排饭',3,9.9,NULL);
INSERT INTO goods(name, business_id, price, limit_num)
VALUES ('孜然脆排饭',3,9.5,NULL);
INSERT INTO goods(name, business_id, price, limit_num)
VALUES ('叉烧脆排饭',3,11.8,2);
INSERT INTO goods(name, business_id, price, limit_num)
VALUES ('腐竹黄焖鸡',4,10.8,3);
INSERT INTO goods(name, business_id, price, limit_num)
VALUES ('黄焖鸡小份微辣',4,13,5);
INSERT INTO goods(name, business_id, price, limit_num)
VALUES ('黄焖鸡小份中辣',4,14,5);
INSERT INTO goods(name, business_id, price, limit_num)
VALUES ('鲜肉蒸饺',5,4,6);
INSERT INTO goods(name, business_id, price, limit_num)
VALUES ('香拌混沌',5,5,3);
INSERT INTO goods(name, business_id, price, limit_num)
VALUES ('蛋炒南昌米粉',5,7,NULL);

结果截图
这里写图片描述

2）商品信息修改

UPDATE goods SET name='韭菜牛肉馅' WHERE id=4;
UPDATE goods SET limit_num=5 WHERE id=6;
UPDATE goods SET price=4.5 WHERE id=13;

结果截图
这里写图片描述

9、外卖订购流程

1）客户提交订单

INSERT orders(order_time, customer_id, goods_id, number)
VALUES ('2017-12-8 12:14:35',2,3,2);
INSERT orders(order_time, customer_id, goods_id, number)
VALUES ('2017-12-8 11:45:12',4,5,5);
INSERT orders(order_time, customer_id, goods_id, number)
VALUES ('2017-12-9 18:14:35',5,13,7);
INSERT orders(order_time, customer_id, goods_id, number)
VALUES ('2017-12-9 19:44:35',9,12,1);
INSERT orders(order_time, customer_id, goods_id, number)
VALUES ('2017-12-10 11:55:34',7,14,6);
INSERT orders(order_time, customer_id, goods_id, number)
VALUES ('2018-1-3 12:04:42',1,7,9);
INSERT orders(order_time, customer_id, goods_id, number)
VALUES ('2018-1-5 13:11:56',3,4,1);
INSERT orders(order_time, customer_id, goods_id, number)
VALUES ('2018-1-11 18:09:27',9,8,1);
INSERT orders(order_time, customer_id, goods_id, number)
VALUES ('2018-1-12 13:11:56',11,8,4);
INSERT orders(order_time, customer_id, goods_id, number)
VALUES ('2018-1-15 19:28:49',5,14,3);
INSERT orders(order_time, customer_id, goods_id, number)
VALUES ('2018-1-16 13:08:40',7,1,2);
INSERT orders(order_time, customer_id, goods_id, number)
VALUES ('2018-1-17 17:30:29',9,4,5);
INSERT orders(order_time, customer_id, goods_id, number)
VALUES ('2018-1-19 12:00:26',12,9,2);
INSERT orders(order_time, customer_id, goods_id, number)
VALUES ('2018-1-21 17:34:56',4,14,4);

结果截图
可以看到订单表的订单金额已自动计算好了
这里写图片描述
客户的消费金额也已经自动更新

2）客户取消订单

UPDATE orders SET state=3 WHERE id=4;
UPDATE orders SET state=3 WHERE id=10;

结果截图
这里写图片描述

3）商家接单

UPDATE orders SET state=1 WHERE id=1;
UPDATE orders SET state=1 WHERE id=2;
UPDATE orders SET state=1 WHERE id=3;
UPDATE orders SET state=1 WHERE id=5;
UPDATE orders SET state=1 WHERE id=6;
UPDATE orders SET state=1 WHERE id=7;
UPDATE orders SET state=1 WHERE id=8;
UPDATE orders SET state=1 WHERE id=9;
UPDATE orders SET state=1 WHERE id=11;
UPDATE orders SET state=1 WHERE id=12;
UPDATE orders SET state=1 WHERE id=13;
UPDATE orders SET state=1 WHERE id=14;

结果截图
这里写图片描述
商家的营业额已自动更新

4）客户确认收货

UPDATE orders SET state=2 WHERE id=1;
UPDATE orders SET state=2 WHERE id=2;
UPDATE orders SET state=2 WHERE id=3;
UPDATE orders SET state=2 WHERE id=5;
UPDATE orders SET state=2 WHERE id=6;
UPDATE orders SET state=2 WHERE id=7;
UPDATE orders SET state=2 WHERE id=8;
UPDATE orders SET state=2 WHERE id=9;
UPDATE orders SET state=2 WHERE id=11;
UPDATE orders SET state=2 WHERE id=12;
UPDATE orders SET state=2 WHERE id=13;
UPDATE orders SET state=2 WHERE id=14;

结果截图
这里写图片描述

10、创建订单详情视图

CREATE VIEW order_details AS
SELECT orders.id AS 订单编号, businesses.name AS 商家名称, goods.name AS 商品名称,
  number AS 数量, total_cost AS 总额, state AS 订单状态, customers.name AS 客户姓名,
  customers.phone AS 电话, customers.address AS 地址, order_time AS 时间
FROM ((businesses INNER JOIN goods ON businesses.id = goods.business_id)
  INNER JOIN orders ON goods.id = orders.goods_id)
  INNER JOIN customers ON orders.customer_id=customers.id;

查看视图

SELECT * FROM order_details;

这里写图片描述

11、客户总消费及商家总营业额对比

SELECT sum(consumption) AS 总消费
FROM customers;
SELECT sum(turnover) AS 总营业额
FROM businesses;

得出的结果应该是一样的，结果截图
这里写图片描述

12、查看2018年1月份商家的营业额排行榜

SELECT businesses.id AS 商家编号, businesses.name AS 商家名称, 
    sum(total_cost) AS 2018年1月营业额
FROM (orders INNER JOIN goods ON orders.goods_id = goods.id) 
    INNER JOIN businesses ON business_id = businesses.id
WHERE state IN (1,2) AND date(order_time) BETWEEN '2018-01-01' AND '2018-01-31'
GROUP BY businesses.id
ORDER BY 2018年1月营业额 DESC ;

结果截图
这里写图片描述

落雪染轻愁

关注

73
点赞
踩
463

收藏

觉得还不错? 一键收藏
21
评论
利用MySQL实现一个类似美团外卖的外卖订单的数据库管理系统

利用MySQL实现一个类似美团外卖的外卖订单的数据库管理系统本文的重点在于MySQL触发器的应用1、数据库需求分析 1）数据库实体：客户、商家、商品、订单。 2）一个客户对应多个订单。 3）一个商家对应多种商品和多个订单。 4）一个订单对应一种商品。2、系统功能分析 1）客户注册：客户通过添加必要的信息到数据库来完成注册。
复制链接

扫一扫

专栏目录