forked from qq1244/java-beta
192 lines
12 KiB
SQL
192 lines
12 KiB
SQL
/*
|
||
Navicat Premium Dump SQL
|
||
|
||
Source Server : localhost_3306
|
||
Source Server Type : MySQL
|
||
Source Server Version : 90200 (9.2.0)
|
||
Source Host : localhost:3306
|
||
Source Schema : cg
|
||
|
||
Target Server Type : MySQL
|
||
Target Server Version : 90200 (9.2.0)
|
||
File Encoding : 65001
|
||
|
||
Date: 18/06/2025 12:47:29
|
||
*/
|
||
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- ----------------------------
|
||
-- Table structure for account
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `account`;
|
||
CREATE TABLE `account` (
|
||
`account_id` bigint NOT NULL AUTO_INCREMENT COMMENT '账户ID',
|
||
`account_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '账户名称',
|
||
`account_password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '账户密码',
|
||
`account_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '手机号码',
|
||
`account_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'customer' COMMENT '账户类型：admin/manager/customer',
|
||
`account_img` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '头像图片URL',
|
||
`account_gender` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '性别：male/female',
|
||
`account_tag` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '账户标签',
|
||
`salary` decimal(10, 2) NULL DEFAULT NULL COMMENT '薪资（员工用）',
|
||
`is_deleted` tinyint(1) NULL DEFAULT 0 COMMENT '是否删除：0-未删除，1-已删除',
|
||
`deleted_at` datetime NULL DEFAULT NULL COMMENT '删除时间',
|
||
`created_at` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`account_id`) USING BTREE,
|
||
INDEX `idx_account_phone`(`account_phone` ASC) USING BTREE,
|
||
INDEX `idx_account_type`(`account_type` ASC) USING BTREE,
|
||
INDEX `idx_is_deleted`(`is_deleted` ASC) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户账户表' ROW_FORMAT = Dynamic;
|
||
|
||
-- ----------------------------
|
||
-- Table structure for branch
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `branch`;
|
||
CREATE TABLE `branch` (
|
||
`branch_id` bigint NOT NULL AUTO_INCREMENT COMMENT '分店ID',
|
||
`branch_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '分店名称',
|
||
`branch_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '分店地址',
|
||
`branch_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '分店电话',
|
||
`branch_start` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '营业开始时间，格式：HH:mm',
|
||
`branch_end` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '营业结束时间，格式：HH:mm',
|
||
`branch_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'open' COMMENT '营业状态：open-营业中，closed-已停业',
|
||
`branch_boss` bigint NULL DEFAULT NULL COMMENT '负责人ID，关联account表',
|
||
`province` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '所属省份',
|
||
`latitude` decimal(10, 8) NULL DEFAULT NULL COMMENT '纬度',
|
||
`longitude` decimal(11, 8) NULL DEFAULT NULL COMMENT '经度',
|
||
`is_deleted` tinyint(1) NULL DEFAULT 0 COMMENT '是否删除：0-未删除，1-已删除',
|
||
`deleted_at` datetime NULL DEFAULT NULL COMMENT '删除时间',
|
||
`created_at` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`branch_id`) USING BTREE,
|
||
INDEX `idx_branch_status`(`branch_status` ASC) USING BTREE,
|
||
INDEX `idx_branch_boss`(`branch_boss` ASC) USING BTREE,
|
||
INDEX `idx_province`(`province` ASC) USING BTREE,
|
||
INDEX `idx_is_deleted`(`is_deleted` ASC) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '分店表' ROW_FORMAT = Dynamic;
|
||
|
||
-- ----------------------------
|
||
-- Table structure for comments
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `comments`;
|
||
CREATE TABLE `comments` (
|
||
`comment_id` bigint NOT NULL AUTO_INCREMENT COMMENT '评论ID',
|
||
`comment_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '评论内容',
|
||
`comment_dish_id` bigint NOT NULL COMMENT '评论的菜品ID',
|
||
`comment_account_id` bigint NOT NULL COMMENT '评论用户ID',
|
||
`comment_rating` int NULL DEFAULT 5 COMMENT '评分：1-5分',
|
||
`parent_comment_id` bigint NULL DEFAULT NULL COMMENT '父评论ID，用于回复功能',
|
||
`is_deleted` tinyint(1) NULL DEFAULT 0 COMMENT '是否删除：0-未删除，1-已删除',
|
||
`deleted_at` datetime NULL DEFAULT NULL COMMENT '删除时间',
|
||
`created_at` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`comment_id`) USING BTREE,
|
||
INDEX `idx_comment_dish_id`(`comment_dish_id` ASC) USING BTREE,
|
||
INDEX `idx_comment_account_id`(`comment_account_id` ASC) USING BTREE,
|
||
INDEX `idx_parent_comment_id`(`parent_comment_id` ASC) USING BTREE,
|
||
INDEX `idx_comment_rating`(`comment_rating` ASC) USING BTREE,
|
||
INDEX `idx_is_deleted`(`is_deleted` ASC) USING BTREE,
|
||
INDEX `idx_created_at`(`created_at` ASC) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '评论表' ROW_FORMAT = Dynamic;
|
||
|
||
-- ----------------------------
|
||
-- Table structure for dishes
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `dishes`;
|
||
CREATE TABLE `dishes` (
|
||
`dish_id` bigint NOT NULL AUTO_INCREMENT COMMENT '菜品ID',
|
||
`dish_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '菜品名称',
|
||
`category` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '菜品分类',
|
||
`price` decimal(10, 2) NOT NULL COMMENT '菜品价格',
|
||
`stock` int NULL DEFAULT 0 COMMENT '库存数量',
|
||
`status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'available' COMMENT '菜品状态：available-可售，unavailable-停售',
|
||
`description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '菜品描述',
|
||
`image_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '菜品图片URL',
|
||
`sales` int NULL DEFAULT 0 COMMENT '累计销售数量',
|
||
`tag` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '菜品标签',
|
||
`is_deleted` tinyint(1) NULL DEFAULT 0 COMMENT '是否删除：0-未删除，1-已删除',
|
||
`deleted_at` datetime NULL DEFAULT NULL COMMENT '删除时间',
|
||
`created_at` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`dish_id`) USING BTREE,
|
||
INDEX `idx_category`(`category` ASC) USING BTREE,
|
||
INDEX `idx_status`(`status` ASC) USING BTREE,
|
||
INDEX `idx_is_deleted`(`is_deleted` ASC) USING BTREE,
|
||
INDEX `idx_dish_name`(`dish_name` ASC) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '菜品表' ROW_FORMAT = Dynamic;
|
||
|
||
-- ----------------------------
|
||
-- Table structure for menu
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `menu`;
|
||
CREATE TABLE `menu` (
|
||
`menu_id` bigint NOT NULL AUTO_INCREMENT COMMENT '菜单ID',
|
||
`branch_id` bigint NOT NULL COMMENT '分店ID',
|
||
`dish_id` bigint NOT NULL COMMENT '菜品ID',
|
||
`is_available` tinyint(1) NULL DEFAULT 1 COMMENT '该菜品在该分店是否可售',
|
||
`branch_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '分店特定价格（如果为空则使用菜品表的价格）',
|
||
`created_at` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`menu_id`) USING BTREE,
|
||
UNIQUE INDEX `uk_branch_dish`(`branch_id` ASC, `dish_id` ASC) USING BTREE,
|
||
INDEX `idx_branch_id`(`branch_id` ASC) USING BTREE,
|
||
INDEX `idx_dish_id`(`dish_id` ASC) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '菜单表' ROW_FORMAT = Dynamic;
|
||
|
||
-- ----------------------------
|
||
-- Table structure for order
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `order`;
|
||
CREATE TABLE `order` (
|
||
`order_id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单ID',
|
||
`account_id` bigint NOT NULL COMMENT '下单用户ID',
|
||
`order_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
|
||
`branch_id` bigint NOT NULL COMMENT '分店ID',
|
||
`order_money` decimal(10, 2) NOT NULL COMMENT '订单总金额',
|
||
`order_remarks` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '订单备注',
|
||
`order_status` int NULL DEFAULT 1 COMMENT '订单状态：1-待处理，2-已完成，-1-已取消',
|
||
`is_deleted` tinyint(1) NULL DEFAULT 0 COMMENT '是否删除：0-未删除，1-已删除',
|
||
`deleted_at` datetime NULL DEFAULT NULL COMMENT '删除时间',
|
||
`created_at` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`order_id`) USING BTREE,
|
||
INDEX `idx_account_id`(`account_id` ASC) USING BTREE,
|
||
INDEX `idx_branch_id`(`branch_id` ASC) USING BTREE,
|
||
INDEX `idx_order_status`(`order_status` ASC) USING BTREE,
|
||
INDEX `idx_order_time`(`order_time` ASC) USING BTREE,
|
||
INDEX `idx_is_deleted`(`is_deleted` ASC) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '订单表' ROW_FORMAT = Dynamic;
|
||
|
||
-- ----------------------------
|
||
-- Table structure for order_list
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `order_list`;
|
||
CREATE TABLE `order_list` (
|
||
`order_list_id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单明细ID',
|
||
`order_id` bigint NOT NULL COMMENT '订单ID',
|
||
`dish_id` bigint NOT NULL COMMENT '菜品ID',
|
||
`number` int NOT NULL DEFAULT 1 COMMENT '数量',
|
||
`created_at` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`order_list_id`) USING BTREE,
|
||
INDEX `idx_order_id`(`order_id` ASC) USING BTREE,
|
||
INDEX `idx_dish_id`(`dish_id` ASC) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '订单明细表' ROW_FORMAT = Dynamic;
|
||
|
||
-- ----------------------------
|
||
-- View structure for branch_order_stats
|
||
-- ----------------------------
|
||
DROP VIEW IF EXISTS `branch_order_stats`;
|
||
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `branch_order_stats` AS select `b`.`branch_id` AS `branch_id`,`b`.`branch_name` AS `branch_name`,count(`o`.`order_id`) AS `total_orders`,coalesce(sum(`o`.`order_money`),0) AS `total_revenue`,cast(`o`.`order_time` as date) AS `order_date` from (`branch` `b` left join `order` `o` on(((`b`.`branch_id` = `o`.`branch_id`) and (`o`.`is_deleted` = false)))) where (`b`.`is_deleted` = false) group by `b`.`branch_id`,`b`.`branch_name`,cast(`o`.`order_time` as date);
|
||
|
||
-- ----------------------------
|
||
-- View structure for dish_sales_view
|
||
-- ----------------------------
|
||
DROP VIEW IF EXISTS `dish_sales_view`;
|
||
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `dish_sales_view` AS select `d`.`dish_id` AS `dish_id`,`d`.`dish_name` AS `dish_name`,`d`.`category` AS `category`,`d`.`price` AS `price`,coalesce(sum(`ol`.`number`),0) AS `total_sales`,count(distinct `o`.`order_id`) AS `order_count` from ((`dishes` `d` left join `order_list` `ol` on((`d`.`dish_id` = `ol`.`dish_id`))) left join `order` `o` on(((`ol`.`order_id` = `o`.`order_id`) and (`o`.`is_deleted` = false)))) where (`d`.`is_deleted` = false) group by `d`.`dish_id`,`d`.`dish_name`,`d`.`category`,`d`.`price`;
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|