/*
 Navicat Premium Data Transfer

 Source Server         : window_mysql
 Source Server Type    : MySQL
 Source Server Version : 80027
 Source Host           : localhost:3306
 Source Schema         : project4

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

 Date: 06/09/2022 16:44:48
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for com_sca
-- ----------------------------
DROP TABLE IF EXISTS `com_sca`;
CREATE TABLE `com_sca`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `scid` int NULL DEFAULT NULL,
  `cid` int NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `scid`(`scid`) USING BTREE,
  INDEX `cid`(`cid`) USING BTREE,
  CONSTRAINT `com_sca_ibfk_1` FOREIGN KEY (`scid`) REFERENCES `shop_cart` (`scid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `com_sca_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `commodity` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 123 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of com_sca
-- ----------------------------
INSERT INTO `com_sca` VALUES (14, 3, 2);
INSERT INTO `com_sca` VALUES (16, 4, 3);
INSERT INTO `com_sca` VALUES (17, 4, 1);
INSERT INTO `com_sca` VALUES (42, 6, 27);
INSERT INTO `com_sca` VALUES (43, 6, 1);
INSERT INTO `com_sca` VALUES (44, 6, 1);
INSERT INTO `com_sca` VALUES (45, 6, 1);
INSERT INTO `com_sca` VALUES (46, 6, 1);
INSERT INTO `com_sca` VALUES (47, 6, 1);
INSERT INTO `com_sca` VALUES (48, 6, 1);
INSERT INTO `com_sca` VALUES (49, 6, 1);
INSERT INTO `com_sca` VALUES (50, 6, 1);
INSERT INTO `com_sca` VALUES (51, 6, 1);
INSERT INTO `com_sca` VALUES (52, 6, 1);
INSERT INTO `com_sca` VALUES (53, 6, 1);
INSERT INTO `com_sca` VALUES (54, 6, 1);
INSERT INTO `com_sca` VALUES (55, 6, 1);
INSERT INTO `com_sca` VALUES (56, 6, 1);
INSERT INTO `com_sca` VALUES (57, 6, 1);
INSERT INTO `com_sca` VALUES (58, 6, 1);
INSERT INTO `com_sca` VALUES (59, 6, 1);
INSERT INTO `com_sca` VALUES (60, 6, 1);
INSERT INTO `com_sca` VALUES (61, 6, 1);
INSERT INTO `com_sca` VALUES (62, 6, 1);
INSERT INTO `com_sca` VALUES (63, 6, 1);
INSERT INTO `com_sca` VALUES (64, 6, 1);
INSERT INTO `com_sca` VALUES (65, 6, 1);
INSERT INTO `com_sca` VALUES (66, 6, 1);
INSERT INTO `com_sca` VALUES (67, 6, 1);
INSERT INTO `com_sca` VALUES (68, 6, 1);
INSERT INTO `com_sca` VALUES (69, 6, 1);
INSERT INTO `com_sca` VALUES (70, 6, 1);
INSERT INTO `com_sca` VALUES (71, 6, 2);
INSERT INTO `com_sca` VALUES (72, 6, 3);
INSERT INTO `com_sca` VALUES (73, 6, 184);
INSERT INTO `com_sca` VALUES (74, 6, 184);
INSERT INTO `com_sca` VALUES (75, 6, 184);
INSERT INTO `com_sca` VALUES (76, 6, 184);
INSERT INTO `com_sca` VALUES (77, 6, 184);
INSERT INTO `com_sca` VALUES (78, 6, 184);
INSERT INTO `com_sca` VALUES (79, 6, 184);
INSERT INTO `com_sca` VALUES (80, 6, 184);
INSERT INTO `com_sca` VALUES (81, 6, 184);
INSERT INTO `com_sca` VALUES (82, 6, 184);
INSERT INTO `com_sca` VALUES (83, 6, 184);
INSERT INTO `com_sca` VALUES (84, 6, 184);
INSERT INTO `com_sca` VALUES (85, 6, 2);
INSERT INTO `com_sca` VALUES (86, 6, 2);
INSERT INTO `com_sca` VALUES (87, 6, 2);
INSERT INTO `com_sca` VALUES (88, 6, 2);
INSERT INTO `com_sca` VALUES (89, 6, 6);
INSERT INTO `com_sca` VALUES (90, 6, 6);
INSERT INTO `com_sca` VALUES (91, 6, 6);
INSERT INTO `com_sca` VALUES (92, 6, 184);
INSERT INTO `com_sca` VALUES (93, 6, 184);
INSERT INTO `com_sca` VALUES (94, 6, 184);
INSERT INTO `com_sca` VALUES (95, 6, 184);
INSERT INTO `com_sca` VALUES (96, 6, 184);
INSERT INTO `com_sca` VALUES (97, 6, 184);
INSERT INTO `com_sca` VALUES (98, 6, 184);
INSERT INTO `com_sca` VALUES (99, 6, 184);
INSERT INTO `com_sca` VALUES (100, 6, 184);
INSERT INTO `com_sca` VALUES (102, 1, 184);
INSERT INTO `com_sca` VALUES (103, 1, 182);
INSERT INTO `com_sca` VALUES (104, 1, 186);
INSERT INTO `com_sca` VALUES (105, 1, 182);
INSERT INTO `com_sca` VALUES (106, 1, 182);
INSERT INTO `com_sca` VALUES (107, 1, 182);
INSERT INTO `com_sca` VALUES (108, 1, 182);
INSERT INTO `com_sca` VALUES (109, 1, 182);
INSERT INTO `com_sca` VALUES (110, 1, 182);
INSERT INTO `com_sca` VALUES (115, 8, 188);
INSERT INTO `com_sca` VALUES (118, 9, 190);
INSERT INTO `com_sca` VALUES (119, 9, 1);
INSERT INTO `com_sca` VALUES (120, 9, 2);

-- ----------------------------
-- Table structure for commodity
-- ----------------------------
DROP TABLE IF EXISTS `commodity`;
CREATE TABLE `commodity`  (
  `cid` int NOT NULL AUTO_INCREMENT,
  `sid` int NULL DEFAULT NULL,
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `picture` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `price` double NULL DEFAULT 0,
  `sales` int NULL DEFAULT 0,
  `inventory` int NULL DEFAULT 0,
  `collect` int NULL DEFAULT 0,
  `version` int NULL DEFAULT 0,
  `deleted` int NOT NULL DEFAULT 1,
  PRIMARY KEY (`cid`) USING BTREE,
  INDEX `sid`(`sid`) USING BTREE,
  INDEX `idx_commodity_price_name`(`price`, `name`) USING BTREE,
  INDEX `idx_commodity_sales`(`sales`) USING BTREE,
  INDEX `idx_commodity_collect`(`collect`) USING BTREE,
  CONSTRAINT `commodity_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `shop` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 190 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of commodity
-- ----------------------------
INSERT INTO `commodity` VALUES (1, 1, 'wwww', NULL, '很好好喝哦，哈哈哈哈，针对真的好喝！！！', 4, 888, 95, 140, 8, 1);
INSERT INTO `commodity` VALUES (2, 1, '哇哈哈aaa', NULL, '很好好喝哦', 5, 888, 100, 106, 2, 1);
INSERT INTO `commodity` VALUES (3, 1, '哇哈哈aaa', NULL, '很好好喝哦', 5, 888, 5, 97, 3, 0);
INSERT INTO `commodity` VALUES (4, 1, '哇哈哈', NULL, '很好好喝哦', 7, 888, 100, 94, 1, 1);
INSERT INTO `commodity` VALUES (5, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 90, 0, 1);
INSERT INTO `commodity` VALUES (6, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 93, 0, 1);
INSERT INTO `commodity` VALUES (7, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 90, 0, 0);
INSERT INTO `commodity` VALUES (8, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 90, 0, 0);
INSERT INTO `commodity` VALUES (27, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 91, 0, 1);
INSERT INTO `commodity` VALUES (28, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 90, 0, 1);
INSERT INTO `commodity` VALUES (29, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 90, 0, 1);
INSERT INTO `commodity` VALUES (30, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 90, 0, 1);
INSERT INTO `commodity` VALUES (31, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 90, 0, 1);
INSERT INTO `commodity` VALUES (41, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 91, 0, 1);
INSERT INTO `commodity` VALUES (42, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 90, 0, 0);
INSERT INTO `commodity` VALUES (43, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 90, 0, 0);
INSERT INTO `commodity` VALUES (44, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 90, 0, 0);
INSERT INTO `commodity` VALUES (45, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 90, 0, 0);
INSERT INTO `commodity` VALUES (51, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 91, 0, 1);
INSERT INTO `commodity` VALUES (52, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 90, 0, 1);
INSERT INTO `commodity` VALUES (53, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 90, 0, 1);
INSERT INTO `commodity` VALUES (54, 1, '哇哈哈', NULL, '很好好喝哦', 3.1, 888, 100, 90, 0, 1);
INSERT INTO `commodity` VALUES (182, 1, '11', '/file/6d5492ec-973e-4df9-bb94-24514cfcdb84.png', '1', 111, 0, 11, 10, 0, 1);
INSERT INTO `commodity` VALUES (183, 1, '可乐', '/file/e2b89877-1f7c-4206-b751-610e3eb56788.png', '真的是一款很好喝的可乐', 5.5, 0, 100, 1, 1, 0);
INSERT INTO `commodity` VALUES (184, 6, '可乐', '/file/80d906c0-14d9-4f59-93da-395ebcd934f7.jpg', 'aaa', 1, 0, 0, 22, 1, 1);
INSERT INTO `commodity` VALUES (185, 6, 'vbn', '/file/eae5015c-2f54-46a0-8dc7-259aa86e02d9.jpg', 'undefined', 2, 0, 111110874, 1, 1, 0);
INSERT INTO `commodity` VALUES (186, 1, '可乐', '/file/197d3c0d-aff7-4223-b7af-14a3c5970237.png', '好喝啊\n', 3, 0, 1, 2, 2, 1);
INSERT INTO `commodity` VALUES (187, 6, '商品1', '/file/a1fd7a51-ea9a-45c2-9a04-ffa49299a0fc.jpg', '商品1', 5, 0, 18, 1, 0, 1);
INSERT INTO `commodity` VALUES (188, 2, '可乐', '/file/f7dd73c5-cf26-490a-9b7c-aab83c0524f3.png', '111', 1, 0, 0, 1, 0, 1);
INSERT INTO `commodity` VALUES (189, 8, '新的商品1', '/file/399cb3a0-5887-49ee-bee6-d186477ebac1.png', '新的商品', 50, 0, 1, 0, 0, 0);
INSERT INTO `commodity` VALUES (190, 9, '可乐11', '/file/47406c3d-7270-4b5a-9a3d-abfad560f76d.png', '1', 5, 0, 98, 1, 0, 1);

-- ----------------------------
-- Table structure for log
-- ----------------------------
DROP TABLE IF EXISTS `log`;
CREATE TABLE `log`  (
  `lid` int NOT NULL AUTO_INCREMENT,
  `operation` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `ope_time` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `log` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`lid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of log
-- ----------------------------
INSERT INTO `log` VALUES (1, 'insert_user', '2022-05-25 23:16:52', '注册了新用户:萧某');
INSERT INTO `log` VALUES (2, 'insert_user', '2022-05-29 15:53:16', '注册了新用户:111');
INSERT INTO `log` VALUES (4, 'insert_shop', '2022-05-29 16:46:06', 'uid为3用户申请了一个店铺');
INSERT INTO `log` VALUES (5, 'insert_user', '2022-06-03 21:14:21', '注册了新用户:aaa');
INSERT INTO `log` VALUES (6, 'insert_user', '2022-06-04 20:58:10', '注册了新用户:郑某');
INSERT INTO `log` VALUES (7, 'insert_shop', '2022-06-04 21:02:57', 'uid为6用户申请了一个店铺');
INSERT INTO `log` VALUES (8, 'insert_user', '2022-06-05 09:27:35', '注册了新用户:郑某3');
INSERT INTO `log` VALUES (9, 'insert_shop', '2022-06-05 09:28:02', 'uid为2用户申请了一个店铺');
INSERT INTO `log` VALUES (10, 'insert_user', '2022-06-20 09:54:39', '注册了新用户:萧某3');
INSERT INTO `log` VALUES (11, 'insert_shop', '2022-06-20 10:41:09', 'uid为8用户申请了一个店铺');
INSERT INTO `log` VALUES (12, 'insert_user', '2022-06-21 18:30:23', '注册了新用户:萧某5');
INSERT INTO `log` VALUES (13, 'insert_shop', '2022-06-21 18:32:03', 'uid为9用户申请了一个店铺');
INSERT INTO `log` VALUES (14, 'insert_user', '2022-07-11 19:26:12', '注册了新用户:曾凡香');

-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`  (
  `oid` int NOT NULL AUTO_INCREMENT,
  `uid` int NULL DEFAULT NULL,
  `cid` int NULL DEFAULT NULL,
  `amount` int NOT NULL DEFAULT 1,
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `deliver` int NOT NULL DEFAULT 0,
  `order_time` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`oid`) USING BTREE,
  INDEX `uid`(`uid`) USING BTREE,
  INDEX `cid`(`cid`) USING BTREE,
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `commodity` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 47 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES (5, 1, 2, 2, '啊', 2, '2022-06-04 14:47:37');
INSERT INTO `orders` VALUES (6, 1, 2, 2, '啊', 0, '2022-05-31 16:25:19');
INSERT INTO `orders` VALUES (7, 1, 2, 2, '啊', 2, '2022-06-01 17:54:49');
INSERT INTO `orders` VALUES (8, 1, 2, 2, '啊', 0, '2022-05-31 16:25:19');
INSERT INTO `orders` VALUES (9, 1, 2, 2, '啊', 0, '2022-05-31 16:25:19');
INSERT INTO `orders` VALUES (10, 1, 1, 4, '7777777777777777777', 1, '2022-06-01 17:11:33');
INSERT INTO `orders` VALUES (11, 1, 2, 1, '', 0, '2022-05-31 16:32:39');
INSERT INTO `orders` VALUES (12, 1, 3, 2, '', 0, '2022-05-31 16:33:35');
INSERT INTO `orders` VALUES (14, 1, 2, 1, NULL, 0, '2022-05-31 17:17:25');
INSERT INTO `orders` VALUES (16, 1, 182, 1, '', 1, '2022-06-03 20:56:21');
INSERT INTO `orders` VALUES (17, 1, 183, 2, '快点发货', 2, '2022-06-03 20:55:57');
INSERT INTO `orders` VALUES (19, 6, 1, 1, '', 0, '2022-06-04 21:06:26');
INSERT INTO `orders` VALUES (20, 6, 184, 2, '', 2, '2022-06-04 21:15:47');
INSERT INTO `orders` VALUES (21, 1, 184, 1, '啊啊啊啊', 1, '2022-06-04 21:42:25');
INSERT INTO `orders` VALUES (22, 1, 184, 1, '111111', 2, '2022-06-04 21:43:38');
INSERT INTO `orders` VALUES (23, 1, 185, 2, '啦啦啦啦啦啦啦啦啦啦', 0, '2022-06-04 21:46:53');
INSERT INTO `orders` VALUES (24, 1, 185, 1, '啊按时大三大三', 2, '2022-06-04 21:53:38');
INSERT INTO `orders` VALUES (25, 1, 185, 2, '啊啊啊啊', 0, '2022-06-04 22:01:40');
INSERT INTO `orders` VALUES (26, 1, 185, 222, '啊啊啊啊', 0, '2022-06-04 22:01:56');
INSERT INTO `orders` VALUES (27, 1, 185, 1, '', 0, '2022-06-04 22:05:17');
INSERT INTO `orders` VALUES (29, 2, 187, 1, '', 2, '2022-06-05 09:26:30');
INSERT INTO `orders` VALUES (30, 2, 187, 1, '', 0, '2022-06-05 09:26:53');
INSERT INTO `orders` VALUES (31, 7, 188, 1, '', 0, '2022-06-05 09:28:27');
INSERT INTO `orders` VALUES (32, 1, 1, 1, '1', 0, '2022-06-08 16:03:41');
INSERT INTO `orders` VALUES (33, 1, 1, 1, '', 2, '2022-06-13 11:06:11');
INSERT INTO `orders` VALUES (34, 1, 1, 1, '', 1, '2022-06-14 11:23:36');
INSERT INTO `orders` VALUES (35, 8, 3, 1, NULL, 0, '2022-06-20 10:39:20');
INSERT INTO `orders` VALUES (36, 8, 41, 1, NULL, 0, '2022-06-20 10:39:20');
INSERT INTO `orders` VALUES (37, 8, 51, 1, NULL, 0, '2022-06-20 10:39:20');
INSERT INTO `orders` VALUES (38, 8, 186, 1, NULL, 0, '2022-06-20 10:39:20');
INSERT INTO `orders` VALUES (39, 8, 182, 1, NULL, 0, '2022-06-20 10:39:20');
INSERT INTO `orders` VALUES (40, 8, 189, 1, '无', 2, '2022-06-20 10:43:49');
INSERT INTO `orders` VALUES (41, 1, 186, 1, '11111', 0, '2022-06-21 11:47:17');
INSERT INTO `orders` VALUES (42, 2, 186, 9, '1111111', 0, '2022-06-21 11:52:19');
INSERT INTO `orders` VALUES (43, 9, 190, 2, '1', 1, '2022-06-21 18:33:51');
INSERT INTO `orders` VALUES (44, 2, 1, 1, NULL, 0, '2022-06-21 18:36:16');
INSERT INTO `orders` VALUES (45, 2, 2, 1, NULL, 0, '2022-06-21 18:36:16');
INSERT INTO `orders` VALUES (46, 2, 4, 1, NULL, 1, '2022-06-21 18:36:16');

-- ----------------------------
-- Table structure for shop
-- ----------------------------
DROP TABLE IF EXISTS `shop`;
CREATE TABLE `shop`  (
  `sid` int NOT NULL,
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `manager` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `info` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `telephone` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `sales` double NULL DEFAULT 0,
  `deleted` int NOT NULL DEFAULT 1,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of shop
-- ----------------------------
INSERT INTO `shop` VALUES (1, '萧某的小店', '萧大某', '一个很好的店铺！！', '11111111', 16, 1);
INSERT INTO `shop` VALUES (2, '啊啊', '1111111', '11111', '111111', 0, 1);
INSERT INTO `shop` VALUES (3, '111111111', '111111', '1111111', '11111', 0, 1);
INSERT INTO `shop` VALUES (6, '615', '郑某', '什么都有\n', '12345678901', 0, 1);
INSERT INTO `shop` VALUES (8, '萧某的小店', '萧某', '新开的店铺', '13579797979', 50, 1);
INSERT INTO `shop` VALUES (9, '萧某的小店', '萧大某', NULL, '13555555555', 0, 1);

-- ----------------------------
-- Table structure for shop_cart
-- ----------------------------
DROP TABLE IF EXISTS `shop_cart`;
CREATE TABLE `shop_cart`  (
  `scid` int NOT NULL AUTO_INCREMENT,
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`scid`) USING BTREE,
  CONSTRAINT `shop_cart_ibfk_1` FOREIGN KEY (`scid`) REFERENCES `user` (`uid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of shop_cart
-- ----------------------------
INSERT INTO `shop_cart` VALUES (1, '2022-05-29 14:17:49');
INSERT INTO `shop_cart` VALUES (2, '2022-05-25 23:16:52');
INSERT INTO `shop_cart` VALUES (3, '2022-05-29 15:53:16');
INSERT INTO `shop_cart` VALUES (4, '2022-05-29 16:02:20');
INSERT INTO `shop_cart` VALUES (5, '2022-06-03 21:14:21');
INSERT INTO `shop_cart` VALUES (6, '2022-06-04 20:58:10');
INSERT INTO `shop_cart` VALUES (7, '2022-06-05 09:27:35');
INSERT INTO `shop_cart` VALUES (8, '2022-06-20 09:54:39');
INSERT INTO `shop_cart` VALUES (9, '2022-06-21 18:30:23');
INSERT INTO `shop_cart` VALUES (10, '2022-07-11 19:26:12');

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `uid` int NOT NULL AUTO_INCREMENT,
  `username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `password` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `age` int NULL DEFAULT 0,
  `telephone` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `account` double NOT NULL DEFAULT 0,
  `consumption` double NOT NULL DEFAULT 0,
  `address` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `deleted` int NOT NULL DEFAULT 1,
  PRIMARY KEY (`uid`) USING BTREE,
  UNIQUE INDEX `idx_user_username`(`username`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '萧某2', '123', 21, 'aaaaa', 92653, 1006347, '江西财经大学麦庐3凝露啊啊', 1);
INSERT INTO `user` VALUES (2, '萧某', '123', 21, '18770782666', 47, 53, '江西财经大学', 1);
INSERT INTO `user` VALUES (3, '12', '111', 11, '18770782684', 0, 0, '11', 1);
INSERT INTO `user` VALUES (4, '1234', '1234', 10, '18770782684', 0, 0, '1232', 0);
INSERT INTO `user` VALUES (5, 'aaa', '123', 11, '13511111111', 0, 0, '1111111', 0);
INSERT INTO `user` VALUES (6, '郑某', '123456', 18, '13576759182', 994, 6, '阿松大', 1);
INSERT INTO `user` VALUES (7, '郑某3', '123456', 12, '13576759182', 99, 1, '啊', 1);
INSERT INTO `user` VALUES (8, '萧某3', '123', 11, '13579797979', 324.8, 175.2, '江西财经大学', 1);
INSERT INTO `user` VALUES (9, '萧某5', '123', 11, '13579797979', 490, 10, '11111', 1);
INSERT INTO `user` VALUES (10, '曾凡香', '123', 40, '15179791789', 0, 0, '11', 1);

-- ----------------------------
-- Procedure structure for pro_userAddCommodity
-- ----------------------------
DROP PROCEDURE IF EXISTS `pro_userAddCommodity`;
delimiter ;;
CREATE PROCEDURE `pro_userAddCommodity`(in Byuid int, in Bycid int)
Begin
    update shop_cart set update_time = NOW() where scid = Byuid ;
    Insert into com_sca values (null,Byuid,Bycid);
  End
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table shop
-- ----------------------------
DROP TRIGGER IF EXISTS `shop_trigger`;
delimiter ;;
CREATE TRIGGER `shop_trigger` AFTER INSERT ON `shop` FOR EACH ROW Begin
    insert into log values (null,'insert_shop',now(),concat('uid为',new.sid,'用户申请了一个店铺'));
  end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table user
-- ----------------------------
DROP TRIGGER IF EXISTS `registerUser_trigger`;
delimiter ;;
CREATE TRIGGER `registerUser_trigger` AFTER INSERT ON `user` FOR EACH ROW Begin
		Insert into shop_cart values (new.uid,now());
    insert into log values (null,'insert_user',now(),concat('注册了新用户:',new.username));
  end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table user
-- ----------------------------
DROP TRIGGER IF EXISTS `user_trigger`;
delimiter ;;
CREATE TRIGGER `user_trigger` AFTER DELETE ON `user` FOR EACH ROW Begin
    insert into log values (null,'delete_user',now(),concat(old.username,'用户被删除,uid为:',old.uid));
  end
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;