/*
 Navicat Premium Data Transfer

 Source Server         : MySQL
 Source Server Type    : MySQL
 Source Server Version : 50738 (5.7.38)
 Source Host           : localhost:3306
 Source Schema         : badminton_vip

 Target Server Type    : MySQL
 Target Server Version : 50738 (5.7.38)
 File Encoding         : 65001

 Date: 30/01/2023 21:54:07
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for administrator
-- ----------------------------
DROP TABLE IF EXISTS `administrator`;
CREATE TABLE `administrator`  (
  `administrator_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '管理员 ID',
  `username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '管理员用户名',
  `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '管理员密码',
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '管理员真实姓名',
  `phonenumber` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '管理员手机号码',
  PRIMARY KEY (`administrator_ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of administrator
-- ----------------------------
INSERT INTO `administrator` VALUES (1, 'admin', 'admin', 'admin', '15888888');

-- ----------------------------
-- Table structure for appraisal
-- ----------------------------
DROP TABLE IF EXISTS `appraisal`;
CREATE TABLE `appraisal`  (
  `appraisal_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '评价 ID',
  `user_ID` int(11) NULL DEFAULT NULL COMMENT '用户 ID',
  `area_ID` int(11) NULL DEFAULT NULL COMMENT '场地 ID',
  `info` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '具体评价',
  `app_status` int(2) NULL DEFAULT NULL COMMENT '评价是好评还是差评 (0 代表好评 1 代表差评)',
  `app_time` date NULL DEFAULT NULL COMMENT '评价的时间',
  PRIMARY KEY (`appraisal_ID`) USING BTREE,
  INDEX `用户ID外键`(`user_ID`) USING BTREE,
  INDEX `场地外键`(`area_ID`) USING BTREE,
  CONSTRAINT `场地外键` FOREIGN KEY (`area_ID`) REFERENCES `area` (`area_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `用户ID外键` FOREIGN KEY (`user_ID`) REFERENCES `user` (`user_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of appraisal
-- ----------------------------
INSERT INTO `appraisal` VALUES (5, 1, 1, '好评!!', 1, '2023-01-21');
INSERT INTO `appraisal` VALUES (6, 2, 2, '场地特别好, 我们玩得很尽兴~~~', 1, '2023-01-26');
INSERT INTO `appraisal` VALUES (7, 3, 3, '由于天气原因, 场地不是很适合打羽毛球, 算是自己倒霉吧, 但是场馆也有错误, 应该给我半价的!!!', 0, '2023-01-26');
INSERT INTO `appraisal` VALUES (8, 1, 1, '11', 1, '2023-01-30');
INSERT INTO `appraisal` VALUES (9, 1, 1, '232434', 1, '2023-01-30');
INSERT INTO `appraisal` VALUES (10, 1, 1, '232434', 1, '2023-01-30');
INSERT INTO `appraisal` VALUES (11, 1, 1, '232434', 1, '2023-01-30');
INSERT INTO `appraisal` VALUES (12, 1, 1, '11', 1, '2023-01-30');
INSERT INTO `appraisal` VALUES (13, 1, 1, '123', 1, '2023-01-30');

-- ----------------------------
-- Table structure for appraisalpic
-- ----------------------------
DROP TABLE IF EXISTS `appraisalpic`;
CREATE TABLE `appraisalpic`  (
  `apppic_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `appraisal_ID` int(11) NULL DEFAULT NULL COMMENT '评价的 ID',
  `pic` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '对应的图片信息',
  PRIMARY KEY (`apppic_ID`) USING BTREE,
  INDEX `评价ID外键`(`appraisal_ID`) USING BTREE,
  CONSTRAINT `评价ID外键` FOREIGN KEY (`appraisal_ID`) REFERENCES `appraisal` (`appraisal_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of appraisalpic
-- ----------------------------

INSERT INTO `appraisalpic` VALUES (5, 9, 'upload/30eeb8c9-2216-45c2-a042-cb8692062e75.jpg');
INSERT INTO `appraisalpic` VALUES (6, 9, 'upload/cba709f6-e042-4581-8772-33616059cd5a.jpg');
INSERT INTO `appraisalpic` VALUES (8, 10, 'upload/7c88033d-5d54-463a-b21f-0e1067c36132.jpg');
INSERT INTO `appraisalpic` VALUES (10, 11, 'upload/3c112851-d2e9-4038-bd62-84b7c77d76e7.jpg');
INSERT INTO `appraisalpic` VALUES (12, 12, 'upload/313df19b-be8b-4d2f-98eb-b46aa8e09583.jpg');
INSERT INTO `appraisalpic` VALUES (13, 12, 'upload/173d5952-4889-4c8b-8295-1ac23710846b.jpg');
INSERT INTO `appraisalpic` VALUES (15, 13, 'upload/25318b5d-8478-414f-9b4c-58974a41116d.jpg');


-- ----------------------------
-- Table structure for area
-- ----------------------------
DROP TABLE IF EXISTS `area`;
CREATE TABLE `area`  (
  `area_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '场地ID',
  `areaname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '场地名称',
  `price` decimal(10, 2) NULL DEFAULT NULL COMMENT '价格',
  `des` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '具体描述',
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地址',
  `starttime` int(3) NULL DEFAULT NULL COMMENT '营业的开始时间',
  `stoptime` int(11) NULL DEFAULT NULL COMMENT '营业的截止时间',
  `profit` decimal(10, 0) NULL DEFAULT NULL COMMENT '总盈利',
  `good_num` int(3) NULL DEFAULT NULL COMMENT '好评数',
  `bad_num` int(3) NULL DEFAULT NULL COMMENT '差评数',
  `good_rate` int(3) NULL DEFAULT NULL COMMENT '好评率',
  PRIMARY KEY (`area_ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 107 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of area
-- ----------------------------
INSERT INTO `area` VALUES (1, '一号场地', 30.00, '小型场', '体育馆一楼', 9, 20, 0, 1, 0, 100);
INSERT INTO `area` VALUES (2, '二号场地', 40.00, '小型场', '体育馆一楼', 8, 21, 0, 0, 0, 85);
INSERT INTO `area` VALUES (3, '三号场地', 30.00, '小型场', '体育馆一楼', 8, 21, 0, 0, 0, 43);
INSERT INTO `area` VALUES (4, '四号场地', 45.00, '中型场', '体育馆二楼', 8, 21, 0, 0, 0, 91);
INSERT INTO `area` VALUES (5, '五号场地', 50.00, '中型场', '体育馆二楼', 8, 21, 0, 0, 0, 77);
INSERT INTO `area` VALUES (6, '六号场地', 60.00, '中型场', '体育馆二楼', 8, 21, 0, 0, 0, 84);
INSERT INTO `area` VALUES (7, '七号场地', 90.00, '大型场', '体育馆三楼', 8, 21, 0, 0, 0, 66);
INSERT INTO `area` VALUES (8, '八号场地', 85.00, '大型场', '体育馆三楼', 8, 21, 0, 0, 0, 54);
INSERT INTO `area` VALUES (9, '九号场地', 100.00, '大型场', '体育馆三楼', 8, 20, 0, 0, 0, 79);
INSERT INTO `area` VALUES (10, '十号场地', 20.00, '双人场', '体育馆四楼', 8, 21, 0, 0, 0, 95);
INSERT INTO `area` VALUES (11, '十一号场地', 30.00, '双人场', '体育馆四楼', 8, 20, 0, 0, 0, 88);
INSERT INTO `area` VALUES (22, '十二号场地', 40.00, '双人场', '体育馆四楼', 7, 19, 0, 0, 0, 96);

-- ----------------------------
-- Table structure for areapic
-- ----------------------------
DROP TABLE IF EXISTS `areapic`;
CREATE TABLE `areapic`  (
  `areapic_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `area_ID` int(11) NULL DEFAULT NULL COMMENT '场地的 ID',
  `pic` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '场地对应的图片信息',
  PRIMARY KEY (`areapic_ID`) USING BTREE,
  INDEX `场地外键1`(`area_ID`) USING BTREE,
  CONSTRAINT `场地外键1` FOREIGN KEY (`area_ID`) REFERENCES `area` (`area_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 80 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of areapic
-- ----------------------------
INSERT INTO `areapic` VALUES (67, 2, 'upload/0b8463cf-235e-4a8f-8572-c3380c455740.jpg');
INSERT INTO `areapic` VALUES (69, 1, 'upload/a1c0f83d-c752-4c03-9f76-b1a8ddd21653.jpg');
INSERT INTO `areapic` VALUES (70, 3, 'upload/a5533df9-3aff-40be-8396-9db6e57f44d7.jpg');
INSERT INTO `areapic` VALUES (71, 4, 'upload/70e0531b-ce9e-44e7-82cd-922159014cc3.jpg');
INSERT INTO `areapic` VALUES (72, 5, 'upload/a421a850-c91c-453d-b84a-eef7e61a4e22.jpg');
INSERT INTO `areapic` VALUES (73, 6, 'upload/ce4d210f-2166-4d66-94e7-cff470841eee.jpg');
INSERT INTO `areapic` VALUES (74, 7, 'upload/1e3d39b5-3360-48ad-b153-96cc1f695549.jpg');
INSERT INTO `areapic` VALUES (75, 8, 'upload/afefa903-df1f-4042-a757-589efd1faefa.jpg');
INSERT INTO `areapic` VALUES (76, 9, 'upload/5f90d4d2-76f2-4c49-9bc6-09e209ae5803.jpg');
INSERT INTO `areapic` VALUES (77, 10, 'upload/eddc907b-68f9-412f-8519-8bff6d726786.jpg');
INSERT INTO `areapic` VALUES (78, 11, 'upload/1707486f-151d-48d1-a9eb-517ec23a4e62.jpg');
INSERT INTO `areapic` VALUES (79, 22, 'upload/8e09b240-e7e4-4c45-ac32-7595affe7a18.jpg');

-- ----------------------------
-- Table structure for reservation
-- ----------------------------
DROP TABLE IF EXISTS `reservation`;
CREATE TABLE `reservation`  (
  `reservation_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '预订 ID',
  `area_ID` int(11) NULL DEFAULT NULL COMMENT '场地 ID',
  `user_ID` int(11) NULL DEFAULT NULL COMMENT '用户 ID',
  `pre_date` date NULL DEFAULT NULL COMMENT '预订的日期',
  `starttime` int(3) NULL DEFAULT NULL COMMENT '开始时间',
  `stoptime` int(3) NULL DEFAULT NULL COMMENT '截止时间',
  `pre_status` int(2) NULL DEFAULT NULL COMMENT '预订的状态 (0 表示待审核 1 表示审核成功)',
  `primarykry` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单实际的唯一标识',
  PRIMARY KEY (`reservation_ID`) USING BTREE,
  INDEX `会员ID外键1`(`user_ID`) USING BTREE,
  INDEX `场地外键2`(`area_ID`) USING BTREE,
  CONSTRAINT `会员ID外键1` FOREIGN KEY (`user_ID`) REFERENCES `user` (`user_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `场地外键2` FOREIGN KEY (`area_ID`) REFERENCES `area` (`area_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 60 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of reservation
-- ----------------------------
INSERT INTO `reservation` VALUES (39, 2, 1, '2023-01-24', 10, 11, 2, '2023-01-24 22:34:52');
INSERT INTO `reservation` VALUES (40, 2, 1, '2023-01-24', 8, 9, 2, '2023-01-24 23:49:31');
INSERT INTO `reservation` VALUES (41, 2, 1, '2023-01-24', 12, 13, 2, '2023-01-24 23:49:56');
INSERT INTO `reservation` VALUES (42, 1, 1, '2023-01-24', 15, 16, 1, '2023-01-24 23:49:56');
INSERT INTO `reservation` VALUES (43, 1, 1, '2023-01-24', 18, 19, 1, '2023-01-24 23:49:56');
INSERT INTO `reservation` VALUES (44, 4, 1, '2023-01-24', 12, 13, 2, '2023-01-24 23:49:56');
INSERT INTO `reservation` VALUES (45, 5, 1, '2023-01-24', 16, 17, 2, '2023-01-24 23:49:56');
INSERT INTO `reservation` VALUES (46, 6, 1, '2023-01-24', 13, 14, 2, '2023-01-24 23:49:56');
INSERT INTO `reservation` VALUES (47, 1, 1, '2023-01-30', 8, 9, 2, '2023-01-30 18:52:12');
INSERT INTO `reservation` VALUES (48, 1, 21, '2023-01-30', 9, 10, 0, '2023-01-30 21:29:33');
INSERT INTO `reservation` VALUES (49, 3, 21, '2023-01-30', 16, 17, 0, '2023-01-30 21:29:33');
INSERT INTO `reservation` VALUES (50, 4, 21, '2023-01-30', 17, 18, 0, '2023-01-30 21:29:33');
INSERT INTO `reservation` VALUES (51, 5, 21, '2023-01-30', 12, 13, 0, '2023-01-30 21:29:33');
INSERT INTO `reservation` VALUES (52, 6, 21, '2023-01-30', 10, 11, 0, '2023-01-30 21:29:33');
INSERT INTO `reservation` VALUES (53, 6, 21, '2023-01-30', 14, 15, 0, '2023-01-30 21:29:33');
INSERT INTO `reservation` VALUES (54, 1, 21, '2023-01-30', 9, 10, 0, '2023-01-30 21:30:32');
INSERT INTO `reservation` VALUES (55, 3, 21, '2023-01-30', 16, 17, 0, '2023-01-30 21:30:32');
INSERT INTO `reservation` VALUES (56, 4, 21, '2023-01-30', 17, 18, 0, '2023-01-30 21:30:32');
INSERT INTO `reservation` VALUES (57, 5, 21, '2023-01-30', 12, 13, 0, '2023-01-30 21:30:32');
INSERT INTO `reservation` VALUES (58, 6, 21, '2023-01-30', 10, 11, 0, '2023-01-30 21:30:32');
INSERT INTO `reservation` VALUES (59, 6, 21, '2023-01-30', 14, 15, 0, '2023-01-30 21:30:32');

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '会员用户名',
  `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '会员密码',
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '会员的真实姓名',
  `sex` int(2) NULL DEFAULT NULL COMMENT '性别 (0 表示男性 1 表示女性)',
  `phonenumber` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '电话号码',
  `price_status` int(2) NULL DEFAULT NULL COMMENT '会员的身份 (0 表示普通会员 1 表示高级会员)',
  `user_pic` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '会员的头像信息 (也可以没有)',
  `del_status` int(2) NULL DEFAULT NULL COMMENT '逻辑删除字段判断',
  `balance` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '账户余额',
  PRIMARY KEY (`user_ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 22 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'alice', '123456', 'Alice', 1, '15930599712', NULL, NULL, NULL, NULL);
INSERT INTO `user` VALUES (2, 'bella', '321', 'Bella', 0, '122123', 1, NULL, 0, NULL);
INSERT INTO `user` VALUES (3, '123', '123', '123', 0, '15930599703', 0, NULL, 0, NULL);
INSERT INTO `user` VALUES (4, 'elizabeth', '1231314', 'Elizabeth', 1, '15930599674', 0, NULL, 1, NULL);
INSERT INTO `user` VALUES (5, 'charlotte', '9999', 'Charlotte', 1, '15839461234', 0, NULL, 0, '100');
INSERT INTO `user` VALUES (6, 'daisy', '1234567', 'Daisy', 1, '15984755977', 1, NULL, 0, NULL);
INSERT INTO `user` VALUES (7, 'afiq', '123123', 'Afiq', 0, '15849306653', 1, NULL, 1, NULL);
INSERT INTO `user` VALUES (8, 'stanex', '123', 'StaNex', 0, '15930545673', 1, NULL, 0, NULL);
INSERT INTO `user` VALUES (9, 'felix', '12345', 'Felix', 0, '15930599807', 0, NULL, 0, NULL);
INSERT INTO `user` VALUES (10, 'hakurei', '123', 'Hakurei', 1, '15930599703', 1, NULL, 0, NULL);




-- ----------------------------
-- Table structure for match
-- ----------------------------

DROP TABLE IF EXISTS `match`;
CREATE TABLE `match`  (
  `match_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '比赛 ID',
  `user1_ID` int(11) NULL DEFAULT NULL COMMENT '选手1 ID',
  `user2_ID` int(11) NULL DEFAULT NULL COMMENT '选手2 ID',
  `match_date` date NULL DEFAULT NULL COMMENT '比赛的日期',
  `stage` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '比赛阶段',
  `result` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '比赛结果',
  PRIMARY KEY (`match_ID`) USING BTREE,
  INDEX `选手1_ID外键`(`user1_ID`) USING BTREE,
  INDEX `选手2_ID外键`(`user2_ID`) USING BTREE,
  CONSTRAINT `选手1_ID外键` FOREIGN KEY (`user1_ID`) REFERENCES `user` (`user_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `选手2_ID外键` FOREIGN KEY (`user2_ID`) REFERENCES `user` (`user_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;


-- ----------------------------
-- Records of match
-- ----------------------------

INSERT INTO `match` VALUES (1, 1, 2, '2022-08-10', '16强', '21-17');
INSERT INTO `match` VALUES (2, 3, 4, '2022-08-15', '16强', '21-19');
INSERT INTO `match` VALUES (3, 5, 6, '2022-08-20', '8强', '21-15');
INSERT INTO `match` VALUES (4, 7, 8, '2022-08-25', '8强', '21-18');
INSERT INTO `match` VALUES (5, 1, 3, '2022-08-30', '半决赛', '21-19');



-- ----------------------------
-- Table structure for record
-- ----------------------------

DROP TABLE IF EXISTS `record`;
CREATE TABLE `record`  (
  `record_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '比赛记录 ID',
  `match_ID` int(11) NULL DEFAULT NULL COMMENT '比赛 ID',
  `user1_score` int(3) NULL DEFAULT NULL COMMENT '选手 1 得分',
  `user2_score` int(3) NULL DEFAULT NULL COMMENT '选手 2 得分',
  `description` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '比赛描述',
  PRIMARY KEY (`record_ID`) USING BTREE,
  INDEX `比赛_ID外键`(`match_ID`) USING BTREE,
  CONSTRAINT `比赛_ID外键` FOREIGN KEY (`match_ID`) REFERENCES `match` (`match_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 50 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;


-- ----------------------------
-- Records of record
-- ----------------------------

INSERT INTO `record` VALUES (1, 1, 21, 17, 'Alice 在 16 强比赛中以 21-17 击败了 Bella.');
INSERT INTO `record` VALUES (2, 2, 19, 21, 'Elizabeth 在 16 强比赛中以 21-19 击败了 123.');
INSERT INTO `record` VALUES (3, 3, 21, 15, 'Charlotte在 8 强比赛中以 21-15 击败了 Daisy.');
INSERT INTO `record` VALUES (4, 4, 18, 21, 'StaNex 在 8 强比赛中以 21-18 击败了 afiq.');
INSERT INTO `record` VALUES (5, 5, 21, 19, 'Alice 在半决赛中以 21-19 击败了选手 123.');



SET FOREIGN_KEY_CHECKS = 1;