校园订餐系统数据库

前台模块

  • 前台主要功能有:用户注册、用户登录、我的购物车、我的订单、商品评论、校园资讯

2. 后台模块

  • 后台主要功能有:用户管理、商品管理、订单管理、评论管理、资讯管理等
名称表名备注
category类别表
customer顾客表
order_订单表
orderitem订单内容表
product产品表
role角色表
user用户表
user_role用户权限表
comment咨询表

category(类别表)

名称字段名类型备注
id类别IDint主键
name类别名称varchar

customer(顾客表)

名称字段名类型备注
id顾客IDint主键
name顾客姓名varchar
password顾客密码varchar
address顾客地址varchar
phone顾客联系方式varchar
status顾客状态int0表示未签收,1表示已签收

order_(订单表)

名称字段名类型备注
id订单IDint
code订单编码varchar
address订单地址varchar
status订单状态int
cstid顾客IDint外键(顾客表customer)

orderitem(订单内容表)

名称字段名类型备注
id订单内容IDint主键
pid产品IDint外键(产品表product)
oid订单IDint外键(订单表order_)
cstid顾客IDint外键(顾客表customer)
number订单数量int

product(产品表)

名称字段名类型备注
id产品IDint主键
name产品名称varchar
price产品价格float
zan点赞数int
number卖出数量int
status产品状态int0未下架,1已下架
imageurl产品图片地址varchar
miaoshu产品描述varchar
cid产品类别IDint外键(类别表category)

role(角色表)

名称字段名类型备注
id角色IDbigint主键
name角色姓名varchar
desc_角色排序varchar

user(用户表)

名称字段名类型备注
id用户IDbigint主键
name用户姓名varchar
password用户密码varchar
salt用户tokenvarchar
status用户状态int0是启用,1是禁用
address用户地址varchar
phone用户联系方式varchar
lasttime用户最后登录时间datetime

user_role(用户权限表)

名称字段名类型备注
id用户权限IDbigint主键
uid用户权限bigint0权限最大,1普通用户,2是游客用户
rid用户权限bigint待定

comment评论

名称字段名类型备注
id咨询IDint主键
content内容varchar
cstid顾客IDint外键(顾客表customer)
status状态int0表示审核中,1审核通过
fabudate发布时间datetime

业务:

在这里插入图片描述

数据库源代码:
/*
 Navicat Premium Data Transfer

 Source Server         : Mysql
 Source Server Type    : MySQL
 Source Server Version : 80025
 Source Host           : localhost:3306
 Source Schema         : dc

 Target Server Type    : MySQL
 Target Server Version : 80025
 File Encoding         : 65001

 Date: 15/07/2021 09:19:38
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for category
-- ----------------------------
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分类名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of category
-- ----------------------------
INSERT INTO `category` VALUES (1, '奶茶专区');
INSERT INTO `category` VALUES (2, '甜品/蛋糕');
INSERT INTO `category` VALUES (3, '包子/早点');
INSERT INTO `category` VALUES (4, '午饭套餐');
INSERT INTO `category` VALUES (5, '肉类炒菜');
INSERT INTO `category` VALUES (6, '素菜专区');

-- ----------------------------
-- Table structure for comment
-- ----------------------------
DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `content` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资讯内容',
  `cstid` int(0) NULL DEFAULT NULL COMMENT '客户id',
  `status` int(0) NULL DEFAULT 0 COMMENT '审核状态,1:通过,0:未通过;',
  `fabudate` datetime(0) NULL DEFAULT NULL COMMENT '发布时间;',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of comment
-- ----------------------------
INSERT INTO `comment` VALUES (1, '我是第一条资讯', 1, 1, '2018-11-05 14:53:29');
INSERT INTO `comment` VALUES (2, '我是第二个', 1, 1, '2018-11-05 18:05:42');
INSERT INTO `comment` VALUES (3, 'aaaa', 1, 1, '2018-11-06 15:06:38');
INSERT INTO `comment` VALUES (4, '我是一条没审核的资讯', 1, 0, '2018-11-06 15:20:07');
INSERT INTO `comment` VALUES (6, '我想发布一条资讯', 1, 1, '2018-11-26 11:19:57');

-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '买家地址',
  `phone` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '买家联系方式',
  `status` int(0) NULL DEFAULT 0 COMMENT '状态,0:普通用户,1:会员',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES (9, '张欣', '795200', '张欣', '13045924953', 0);

-- ----------------------------
-- Table structure for order_
-- ----------------------------
DROP TABLE IF EXISTS `order_`;
CREATE TABLE `order_`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单编号',
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '买家地址',
  `status` int(0) NULL DEFAULT 0 COMMENT '支付状态,0:未支付,1:正在发货,2:已发货,3:已收货',
  `cstid` int(0) NULL DEFAULT NULL COMMENT '买家id',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `cstid`(`cstid`) USING BTREE,
  CONSTRAINT `order__ibfk_1` FOREIGN KEY (`cstid`) REFERENCES `customer` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of order_
-- ----------------------------
INSERT INTO `order_` VALUES (1, '201811021055177112826', '5a124', 2, NULL);
INSERT INTO `order_` VALUES (2, '201811021055179112826', '我是地址', 2, NULL);
INSERT INTO `order_` VALUES (18, '201811251731209717398', '姓名:byh,邮箱:1191741799@qq.com,配送地址:wodedizhi敖德萨,手机号:123456', 1, NULL);
INSERT INTO `order_` VALUES (19, '201811251750178958169', '姓名:byh,邮箱:1191741799@qq.com,配送地址:dsaddddddddddddddddddddddd,手机号:123456', 1, NULL);
INSERT INTO `order_` VALUES (20, '202012232244129086683', '姓名:张欣,邮箱:1710488546@qq.com,配送地址:张欣000000000,手机号:13045924953', 1, 9);

-- ----------------------------
-- Table structure for orderitem
-- ----------------------------
DROP TABLE IF EXISTS `orderitem`;
CREATE TABLE `orderitem`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `pid` int(0) NULL DEFAULT NULL COMMENT '商品id',
  `oid` int(0) NULL DEFAULT NULL COMMENT '订单id',
  `cstid` int(0) NULL DEFAULT NULL COMMENT '买家id',
  `number` int(0) NULL DEFAULT NULL COMMENT '数量',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `pid`(`pid`) USING BTREE,
  INDEX `oid`(`oid`) USING BTREE,
  INDEX `cstid`(`cstid`) USING BTREE,
  CONSTRAINT `orderitem_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `product` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `orderitem_ibfk_2` FOREIGN KEY (`oid`) REFERENCES `order_` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `orderitem_ibfk_3` FOREIGN KEY (`cstid`) REFERENCES `customer` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 49 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of orderitem
-- ----------------------------
INSERT INTO `orderitem` VALUES (1, 4, 1, NULL, 5);
INSERT INTO `orderitem` VALUES (2, 5, 1, NULL, 1);
INSERT INTO `orderitem` VALUES (5, 3, 1, NULL, 1);
INSERT INTO `orderitem` VALUES (8, 2, 1, NULL, 1);
INSERT INTO `orderitem` VALUES (13, 4, 2, NULL, 3);
INSERT INTO `orderitem` VALUES (14, 5, 2, NULL, 2);
INSERT INTO `orderitem` VALUES (18, 8, 19, NULL, 1);
INSERT INTO `orderitem` VALUES (34, 10, 18, NULL, 11);
INSERT INTO `orderitem` VALUES (41, 1, NULL, NULL, 14);
INSERT INTO `orderitem` VALUES (46, 2, NULL, NULL, 4);
INSERT INTO `orderitem` VALUES (47, 1, 20, 9, 1);
INSERT INTO `orderitem` VALUES (48, 2, 20, 9, 1);

-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
  `price` float(10, 2) NULL DEFAULT NULL COMMENT '价格',
  `zan` int(0) NULL DEFAULT NULL COMMENT '赞',
  `number` int(0) NULL DEFAULT NULL COMMENT '销量',
  `status` int(0) NULL DEFAULT 1 COMMENT '状态,1:上线,0:下线',
  `imageurl` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品图片url',
  `miaoshu` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '商品描述',
  `cid` int(0) NULL DEFAULT NULL COMMENT '分类id',
  `bid` bigint(0) NULL DEFAULT NULL COMMENT '商家id',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `product_ibfk_1`(`cid`) USING BTREE,
  INDEX `product_ibfk_2`(`bid`) USING BTREE,
  CONSTRAINT `product_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `product_ibfk_2` FOREIGN KEY (`bid`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 32 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES (1, '红豆布丁烧奶', 9.00, 54, 94, 1, 'images/product/1.jpg', '软糯红豆嫩布丁,鲜香甘甜,浓郁的奶香与红豆完美相融,满足感充分体现在你的唇齿和心间', 1, 28);
INSERT INTO `product` VALUES (2, '焦糖珍奶', 10.00, 98, 103, 1, 'images/product/2.jpg', '奶茶中的经典款,用不过时,焦糖的甜珍奶的鲜,永远有人喜欢。', 1, 28);
INSERT INTO `product` VALUES (3, '葡萄柚益菌多', 15.00, 45, 63, 1, 'images/product/3.jpg', '新鲜葡萄柚与优菌多相遇,酸甜的滋味在唇齿间流转,只需闭上眼用心品味这让人欲罢不能的清爽感。', 1, 28);
INSERT INTO `product` VALUES (4, '芒果布丁奶绿', 13.00, 41, 43, 1, 'images/product/4.jpg', '清香可口的海南大金煌与柔滑香的甜奶布丁,层层叠加,混搭滋味,随心选择。', 1, 28);
INSERT INTO `product` VALUES (5, '金桔柠檬茶', 18.00, 72, 168, 1, 'images/product/5.jpg', '打破传统奶盖与茶饮分离的做法,将咸奶盖与芒果冰均匀融合。芒果冰的爽滑细腻与咸奶盖的香浓完美融合,口感清甜饱满,独家特制芒砂糖将芒果本身的香甜气息发挥到极致。', 1, 29);
INSERT INTO `product` VALUES (6, '蜂蜜柚子茶', 18.00, 74, 159, 1, 'images/product/6.jpg', '不仅是味道清香可口,更是一款有美白祛斑、嫩肤养颜功效的食品。蜂蜜中含有的L-半胱氨酸具有排毒作用,具有一定的祛斑效果。', 1, 30);
INSERT INTO `product` VALUES (7, '巧克力水果蛋糕', 68.00, 75, 101, 1, 'images/product/7.jpg', '本店蛋糕现做现卖,由于蛋糕的特殊性,请顾客提前义雄安史预定。联系电话:12312345678', 2, 29);
INSERT INTO `product` VALUES (8, '心心相印', 88.00, 45, 99, 1, 'images/product/8.jpg', '为了保证蛋糕的新鲜!所有商品均为顾客下单现做!为了不耽误亲们的宝贵时间,请尽量提前一小时下单!详情致电:12312345678', 2, 29);
INSERT INTO `product` VALUES (9, '美味奶油蛋糕', 45.00, 45, 56, 1, 'images/product/9.jpg', '本店蛋糕现做现卖,请顾客朋友提前预定电话:12312345678', 2, 29);
INSERT INTO `product` VALUES (10, '至尊奶油蛋糕', 98.00, 235, 420, 1, 'images/product/10.jpg', '本店蛋糕现做现卖,由于蛋糕的特殊性,请顾客提前2小时预定。联系电话:12312345678', 2, 30);
INSERT INTO `product` VALUES (11, '丰盛果园', 133.00, 356, 2762, 1, 'images/product/11.jpg', '(15人餐)本店蛋糕现做现卖,请顾客提亲1小时预定,联系电话:12312345678', 2, 30);
INSERT INTO `product` VALUES (12, '太阳花', 166.00, 124, 369, 1, 'images/product/12.jpg', '(20人餐)本店蛋糕现做现卖,请顾客提前一小时预定。联系电话:12312345678', 2, 30);
INSERT INTO `product` VALUES (13, '初见', 105.00, 124, 234, 1, 'images/product/13.jpg', '(12餐)本店蛋糕现做现卖,请顾客提前一小时预定,联系电话:12312345678', 2, 31);
INSERT INTO `product` VALUES (14, '香辣鸡腿堡', 5.00, 124, 254, 1, 'images/product/14.jpg', '开业特惠,豪吃不贵!限时特价商品', 3, 31);
INSERT INTO `product` VALUES (15, '墨西哥肌肉卷', 19.00, 453, 454, 1, 'images/product/15.jpg', '里面用的是番茄酱哈', 3, 31);
INSERT INTO `product` VALUES (16, '爆浆鸡排', 12.00, 45, 4654, 1, 'images/product/16.jpg', '香脆可口', 3, 31);
INSERT INTO `product` VALUES (17, '椰奶芝士蛋糕', 11.00, 54, 132, 1, 'images/product/17.jpg', '该商品暂无描述', 3, 31);
INSERT INTO `product` VALUES (18, '南瓜脆皮芝士包', 8.00, 15, 232, 1, 'images/product/18.jpg', '暂无商品信息', 3, 31);
INSERT INTO `product` VALUES (19, '核桃布里奥斯', 23.00, 33, 323, 1, 'images/product/19.jpg', '定制设计', 3, 30);
INSERT INTO `product` VALUES (20, '卤肉配牛丸', 45.00, 45, 555, 1, 'images/product/20.jpg', '把简单的事情做到不简单,这就是我们存在的意义。优质猪后腿肉,在浓郁的老卤的怀抱中千回百转。30余味的精选香料与肉块的完美融合。', 4, 30);
INSERT INTO `product` VALUES (25, '雪花鸡排饭', 42.00, 444, 444, 1, 'images/product/25.jpg', '鸡腿+脆骨肠+配菜+饮料+米饭', 4, 30);
INSERT INTO `product` VALUES (26, '劲爆麻辣牛肉饭', 35.00, 23, 43, 1, 'images/product/26.jpg', '牛排+脆骨肠+配菜+饮料+米饭', 4, 30);
INSERT INTO `product` VALUES (27, '蜜汁牛排饭', 25.00, 34, 53, 1, 'images/product/27.jpg', '牛排+脆骨肠+配菜+饮料+米饭', 4, 30);
INSERT INTO `product` VALUES (28, '小熊卤肉饭', 23.00, 34, 23, 1, 'images/product/28.jpg', '卤香十足,香甜可口,回味无穷,超级好吃', 4, 30);
INSERT INTO `product` VALUES (29, '法式炸鸡饭', 23.00, 43, 65, 1, 'images/product/29.jpg', '暂无介绍', 4, 30);
INSERT INTO `product` VALUES (30, '清蒸大闸蟹', 98.00, 456, 998, 1, 'images/product/30.jpg', '3.5重的大公蟹,肉质鲜美,淘宝都卖一百四五一只,本店只卖98', 5, 31);
INSERT INTO `product` VALUES (31, '单煎牛排', 45.00, 98, 564, 1, 'images/product/31.jpg', '暂无介绍', 6, 29);

-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `desc_` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES (1, 'admin', '超级管理员');
INSERT INTO `role` VALUES (2, 'productManager', '商品管理员');
INSERT INTO `role` VALUES (3, '游客', '只能查看,不能增删改');

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '管理员姓名',
  `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '管理员密码',
  `salt` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `status` int(0) NULL DEFAULT NULL COMMENT '状态,1:启用,0:停用',
  `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '暂无' COMMENT '商家管理员地址',
  `phone` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `lasttime` datetime(0) NULL DEFAULT NULL COMMENT '最后登陆时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (2, 'admin', '45ec20ba8f289446c041d11cbbc7e8e1', 'OqAF68Wxa5B071jnZYQYGw==', 1, '泉州信息工程学院', '13045924953', '2020-12-23 22:42:42');

-- ----------------------------
-- Table structure for user_role
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT,
  `uid` bigint(0) NULL DEFAULT NULL,
  `rid` bigint(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 98 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_role
-- ----------------------------
INSERT INTO `user_role` VALUES (91, 28, 2);
INSERT INTO `user_role` VALUES (92, 29, 2);
INSERT INTO `user_role` VALUES (93, 30, 2);
INSERT INTO `user_role` VALUES (94, 31, 2);
INSERT INTO `user_role` VALUES (95, 15, 3);
INSERT INTO `user_role` VALUES (96, 1, 1);
INSERT INTO `user_role` VALUES (97, 2, 1);

SET FOREIGN_KEY_CHECKS = 1;


INSERT INTO user_role VALUES (91, 28, 2);
INSERT INTO user_role VALUES (92, 29, 2);
INSERT INTO user_role VALUES (93, 30, 2);
INSERT INTO user_role VALUES (94, 31, 2);
INSERT INTO user_role VALUES (95, 15, 3);
INSERT INTO user_role VALUES (96, 1, 1);
INSERT INTO user_role VALUES (97, 2, 1);

SET FOREIGN_KEY_CHECKS = 1;


  • 6
    点赞
  • 58
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值