【无标题】

/*
 Navicat Premium Data Transfer

 Source Server         : 131
 Source Server Type    : MySQL
 Source Server Version : 50731
 Source Host           : localhost:3306
 Source Schema         : schooldb

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

 Date: 13/06/2024 14:58:41
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for order
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order`  (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `order_status` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '待支付',
  `order_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  `payment_status` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '未支付',
  `payment_time` timestamp(0) NULL DEFAULT NULL,
  `total_price` decimal(10, 2) NOT NULL,
  PRIMARY KEY (`order_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `order_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES (1, 2, '待支付', '2024-06-14 14:50:58', '未支付', '2024-06-05 14:51:10', 120.00);
INSERT INTO `order` VALUES (2, 5, '已支付', '2024-06-27 14:51:30', '已支付', '2024-06-18 14:51:39', 230.00);

-- ----------------------------
-- Table structure for order_info
-- ----------------------------
DROP TABLE IF EXISTS `order_info`;
CREATE TABLE `order_info`  (
  `order_info_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit_price` decimal(10, 2) NOT NULL,
  PRIMARY KEY (`order_info_id`) USING BTREE,
  INDEX `order_id`(`order_id`) USING BTREE,
  INDEX `product_id`(`product_id`) USING BTREE,
  CONSTRAINT `order_info_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `order` (`order_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `order_info_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of order_info
-- ----------------------------
INSERT INTO `order_info` VALUES (1, 1, 1, 2, 50.00);
INSERT INTO `order_info` VALUES (2, 1, 2, 1, 60.00);

-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product`  (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `price` decimal(10, 2) NOT NULL,
  `stock` int(11) NOT NULL DEFAULT 0,
  `type_id` int(11) NOT NULL,
  `created_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`product_id`) USING BTREE,
  INDEX `type_id`(`type_id`) USING BTREE,
  CONSTRAINT `product_ibfk_1` FOREIGN KEY (`type_id`) REFERENCES `product_type` (`type_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES (1, 'iPhone 13', 6999.00, 100, 1, '2024-06-05 22:13:55');
INSERT INTO `product` VALUES (2, 'MacBook Pro', 14999.00, 50, 2, '2024-06-06 22:13:55');
INSERT INTO `product` VALUES (3, '小米智能音箱', 199.00, 200, 3, '2024-06-07 22:13:55');

-- ----------------------------
-- Table structure for product_type
-- ----------------------------
DROP TABLE IF EXISTS `product_type`;
CREATE TABLE `product_type`  (
  `type_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `parent_id` int(11) NULL DEFAULT NULL,
  `description` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `created_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`type_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of product_type
-- ----------------------------
INSERT INTO `product_type` VALUES (1, '智能手机', NULL, '包含各种品牌和型号的智能手机，涵盖不同价格区间和功能特点。', '2024-05-31 22:13:55');
INSERT INTO `product_type` VALUES (2, '笔记本电脑', NULL, '提供多种类型和配置的笔记本电脑，适用于不同工作和学习需求。', '2024-06-01 22:13:55');
INSERT INTO `product_type` VALUES (3, '智能家居', NULL, '包含智能家居设备，如智能音箱、智能照明、智能门锁等。', '2024-06-02 22:13:55');
INSERT INTO `product_type` VALUES (4, '电视与显示器', NULL, '涵盖各种尺寸和分辨率的电视与显示器，适用于家庭和企业使用。', '2024-06-03 22:13:55');
INSERT INTO `product_type` VALUES (5, '耳机与音响', NULL, '提供高品质耳机和音响设备，满足用户的音频体验需求。', '2024-06-04 22:13:55');

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `phone` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `created_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`user_id`) USING BTREE,
  UNIQUE INDEX `username`(`username`) USING BTREE,
  UNIQUE INDEX `email`(`email`) USING BTREE,
  UNIQUE INDEX `phone`(`phone`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '王语嫣', 'password1', 'user1@example.com', '13800000001', '2024-06-06 22:07:39');
INSERT INTO `user` VALUES (2, '小龙女', 'password2', 'user2@example.com', '13800000002', '2024-06-09 22:07:39');
INSERT INTO `user` VALUES (3, '赵灵儿', 'password3', 'user3@example.com', '13800000003', '2024-06-09 22:07:39');
INSERT INTO `user` VALUES (4, '杨过', 'password4', 'user4@example.com', '13800000004', '2024-05-31 22:07:39');
INSERT INTO `user` VALUES (5, '向问天', 'password5', 'user5@example.com', '13800000005', '2024-06-04 22:07:39');
INSERT INTO `user` VALUES (6, '杨潇', 'password6', 'user6@example.com', '13800000006', '2024-06-01 22:07:39');

-- ----------------------------
-- Table structure for user_wallet
-- ----------------------------
DROP TABLE IF EXISTS `user_wallet`;
CREATE TABLE `user_wallet`  (
  `wallet_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `balance` decimal(10, 2) NOT NULL,
  `created_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`wallet_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `user_wallet_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_wallet
-- ----------------------------
INSERT INTO `user_wallet` VALUES (1, 1, 422.00, '2024-06-08 22:07:42');
INSERT INTO `user_wallet` VALUES (2, 2, 897.00, '2024-06-04 22:07:42');
INSERT INTO `user_wallet` VALUES (3, 3, 354.00, '2024-06-03 22:07:42');
INSERT INTO `user_wallet` VALUES (4, 4, 758.00, '2024-06-07 22:07:42');
INSERT INTO `user_wallet` VALUES (5, 5, 319.00, '2024-06-06 22:07:42');
INSERT INTO `user_wallet` VALUES (6, 6, 350.00, '2024-06-08 22:07:42');

-- ----------------------------
-- Table structure for user_wallet_log
-- ----------------------------
DROP TABLE IF EXISTS `user_wallet_log`;
CREATE TABLE `user_wallet_log`  (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `transaction_type` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `amount` decimal(10, 2) NOT NULL,
  `transaction_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`log_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `user_wallet_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_wallet_log
-- ----------------------------
INSERT INTO `user_wallet_log` VALUES (1, 1, '充值', 100.00, '2024-06-01 22:07:48');
INSERT INTO `user_wallet_log` VALUES (2, 2, '消费', 50.00, '2024-06-08 22:07:48');
INSERT INTO `user_wallet_log` VALUES (3, 3, '提现', 200.00, '2024-06-09 22:07:48');
INSERT INTO `user_wallet_log` VALUES (4, 4, '充值', 150.00, '2024-05-31 22:07:48');

SET FOREIGN_KEY_CHECKS = 1;