master

分支 (1)

管理

管理

master

vmm
/
vmm.sql

/*
 Navicat Premium Data Transfer

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 80035
 Source Host           : localhost:3306
 Source Schema         : vmm

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

 Date: 12/01/2024 17:17:02
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for admin
-- ----------------------------
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '管理员id',
  `admin` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '管理员账户',
  `password` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '管理员密码，使用MD5加密',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '管理员表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for business_statistics
-- ----------------------------
DROP TABLE IF EXISTS `business_statistics`;
CREATE TABLE `business_statistics`  (
  `stat_id` int NOT NULL AUTO_INCREMENT COMMENT '统计ID，主键，自动递增 ',
  `type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '统计类型，不能为空  ',
  `value` decimal(10, 2) NOT NULL COMMENT ' 统计值，不能为空  ',
  PRIMARY KEY (`stat_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '业务报表统计表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for commission_statistics
-- ----------------------------
DROP TABLE IF EXISTS `commission_statistics`;
CREATE TABLE `commission_statistics`  (
  `stat_id` int NOT NULL AUTO_INCREMENT COMMENT '统计ID，主键，自动递增  ',
  `employee_id` int NULL DEFAULT NULL COMMENT '员工ID，外键关联employees表的employee_id',
  `amount` decimal(10, 2) NOT NULL COMMENT '提成金额，不能为空  ',
  PRIMARY KEY (`stat_id`) USING BTREE,
  INDEX `employee_id`(`employee_id`) USING BTREE,
  CONSTRAINT `commission_statistics_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '提成统计表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for customers
-- ----------------------------
DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers`  (
  `customer_id` int NOT NULL AUTO_INCREMENT COMMENT '客户ID，主键，自动递增 ',
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ' 客户姓名，不能为空',
  `vehicle_id` int NOT NULL COMMENT '车辆id，不能为空',
  `contact_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '联系电话，不能为空  ',
  `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '电子邮箱，可以为空  ',
  `created_date` datetime NOT NULL COMMENT '创建日期，不能为空  ',
  `last_contacted_date` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '最后联系日期，可以为空  ',
  PRIMARY KEY (`customer_id`) USING BTREE,
  INDEX `customers_ibfk_2`(`vehicle_id`) USING BTREE,
  CONSTRAINT `customers_ibfk_2` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`vehicle_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '客户信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for employee_materials
-- ----------------------------
DROP TABLE IF EXISTS `employee_materials`;
CREATE TABLE `employee_materials`  (
  `record_id` int NOT NULL AUTO_INCREMENT COMMENT ' 记录ID，主键，自动递增  ',
  `employee_id` int NULL DEFAULT NULL COMMENT '员工ID，外键关联employees表的employee_id  ',
  `part_id` int NULL DEFAULT NULL COMMENT '配件ID，外键关联parts表的part_id  ',
  `pick_date` date NOT NULL COMMENT '领取日期，不能为空  ',
  `quantity` int NOT NULL COMMENT '领取数量，不能为空  ',
  PRIMARY KEY (`record_id`) USING BTREE,
  INDEX `employee_id`(`employee_id`) USING BTREE,
  INDEX `part_id`(`part_id`) USING BTREE,
  CONSTRAINT `employee_materials_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `employee_materials_ibfk_2` FOREIGN KEY (`part_id`) REFERENCES `parts` (`part_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '员工领料记录表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `employee_id` int NOT NULL AUTO_INCREMENT COMMENT '员工ID，主键，自动递增',
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工姓名，不能为空  ',
  `contact_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '联系电话，不能为空  ',
  `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '电子邮箱，不能为空  ',
  `hire_date` datetime NOT NULL COMMENT '入职日期，不能为空  ',
  PRIMARY KEY (`employee_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '员工信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for friend_relation
-- ----------------------------
DROP TABLE IF EXISTS `friend_relation`;
CREATE TABLE `friend_relation`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '关系id',
  `uid` int NOT NULL COMMENT '用户id',
  `fid` int NOT NULL COMMENT '好友id',
  `beizhu` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '当前用户对好友的备注信息',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '最后修改时间',
  `status` tinyint NOT NULL COMMENT '状态 0-普通好友 1-拉黑',
  `del_flag` tinyint NOT NULL COMMENT '删除标识',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uid`(`uid`, `fid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '好友关系' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for friend_request
-- ----------------------------
DROP TABLE IF EXISTS `friend_request`;
CREATE TABLE `friend_request`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '好友申请id',
  `from_id` int NOT NULL COMMENT '申请人id',
  `to_id` int NOT NULL COMMENT '被申请人id',
  `beizhu` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '申请人对被申请人的备注',
  `info` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '验证消息',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '最后修改时间',
  `status` tinyint NOT NULL COMMENT '状态 0-待处理 1-已通过 2-已拒绝',
  `del_flag` tinyint NOT NULL COMMENT '删除标识',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '好友申请表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for inventory_check
-- ----------------------------
DROP TABLE IF EXISTS `inventory_check`;
CREATE TABLE `inventory_check`  (
  `check_id` int NOT NULL AUTO_INCREMENT COMMENT '盘点ID，主键，自动递增  ',
  `part_id` int NULL DEFAULT NULL COMMENT '配件ID，外键关联parts表的part_id  ',
  `check_date` date NOT NULL COMMENT '盘点日期，不能为空  ',
  `quantity` int NOT NULL COMMENT '盘点数量，不能为空 ',
  PRIMARY KEY (`check_id`) USING BTREE,
  INDEX `part_id`(`part_id`) USING BTREE,
  CONSTRAINT `inventory_check_ibfk_1` FOREIGN KEY (`part_id`) REFERENCES `parts` (`part_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '库存盘点表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for maintenanceorders
-- ----------------------------
DROP TABLE IF EXISTS `maintenanceorders`;
CREATE TABLE `maintenanceorders`  (
  `order_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NULL DEFAULT NULL,
  `vehicle_id` int NULL DEFAULT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `service_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `technician_id` int NULL DEFAULT NULL,
  `appointment_date` date NULL DEFAULT NULL,
  `estimated_completion_date` date NULL DEFAULT NULL,
  `actual_completion_date` date NULL DEFAULT NULL,
  `total_cost` decimal(10, 2) NOT NULL,
  `comments` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  PRIMARY KEY (`order_id`) USING BTREE,
  INDEX `customer_id`(`customer_id`) USING BTREE,
  INDEX `vehicle_id`(`vehicle_id`) USING BTREE,
  CONSTRAINT `maintenanceorders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `maintenanceorders_ibfk_2` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`vehicle_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'order_id (主键): 维修订单的唯一标识符。\r\ncustomer_id: 与客户表相关联的外键，表示维修订单所属的客户。\r\nvehicle_id: 与车辆表相关联的外键，表示维修的车辆。\r\ndescription: 描述维修订单的具体内容或问题。\r\nservice_type: 维修服务的类型（例如：小修、中修、大修等）。\r\nstatus: 维修订单的状态（例如：待处理、进行中、已完成等）。\r\ntechnician_id: 与技术员表相关联的外键，表示负责该维修订单的技术员。\r\nappointment_date: 预约的维修日期。\r\nestimated_completion_date: 预计的完成日期。\r\nactual_completion_date: 实际的完成日期。\r\ntotal_cost: 维修订单的总成本。\r\ncomments: 其他备注或说明。' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for msg_history
-- ----------------------------
DROP TABLE IF EXISTS `msg_history`;
CREATE TABLE `msg_history`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '消息id',
  `uid` int NOT NULL COMMENT '发送者id',
  `fid` int NOT NULL COMMENT '接受者id',
  `msg` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '消息的内容',
  `type` tinyint NOT NULL COMMENT '消息的类型 0-文本消息 1-图片消息 2-语音消息 3-视频通话',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '最后修改时间',
  `status` tinyint NOT NULL COMMENT '状态 0-未接收 1-已接收',
  `del_flag` tinyint NOT NULL COMMENT '删除标识',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '历史消息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for outbound
-- ----------------------------
DROP TABLE IF EXISTS `outbound`;
CREATE TABLE `outbound`  (
  `outbound_id` int NOT NULL AUTO_INCREMENT COMMENT '出库ID，主键，自动递增  ',
  `part_id` int NULL DEFAULT NULL COMMENT ' 配件ID，外键关联parts表的part_id  ',
  `outbound_date` date NOT NULL COMMENT ' 出库日期，不能为空  ',
  `quantity` int NOT NULL COMMENT '出库数量，不能为空  ',
  PRIMARY KEY (`outbound_id`) USING BTREE,
  INDEX `part_id`(`part_id`) USING BTREE,
  CONSTRAINT `outbound_ibfk_1` FOREIGN KEY (`part_id`) REFERENCES `parts` (`part_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '出库记录表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for parts
-- ----------------------------
DROP TABLE IF EXISTS `parts`;
CREATE TABLE `parts`  (
  `part_id` int NOT NULL AUTO_INCREMENT COMMENT '配件ID，主键，自动递增',
  `part_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '配件名称，不能为空  ',
  `part_category` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ' 配件类别，不能为空  ',
  `purchase_price` decimal(10, 2) NOT NULL COMMENT '采购价格，不能为空  ',
  `selling_price` decimal(10, 2) NOT NULL COMMENT '销售价格，不能为空 ',
  `stock_quantity` int NOT NULL COMMENT '库存数量，不能为空  ',
  PRIMARY KEY (`part_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '配件表\r\n' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for purchases
-- ----------------------------
DROP TABLE IF EXISTS `purchases`;
CREATE TABLE `purchases`  (
  `purchase_id` int NOT NULL AUTO_INCREMENT COMMENT '采购ID，主键，自动递增  ',
  `part_id` int NULL DEFAULT NULL COMMENT '配件ID，外键关联parts表的part_id  ',
  `purchase_date` date NOT NULL COMMENT '采购日期，不能为空  ',
  `quantity` int NOT NULL COMMENT '供应商名称，不能为空 ',
  `supplier` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '外键约束，关联parts表的part_id',
  PRIMARY KEY (`purchase_id`) USING BTREE,
  INDEX `part_id`(`part_id`) USING BTREE,
  CONSTRAINT `purchases_ibfk_1` FOREIGN KEY (`part_id`) REFERENCES `parts` (`part_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '采购记录表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for sales
-- ----------------------------
DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales`  (
  `sale_id` int NOT NULL AUTO_INCREMENT COMMENT '销售ID，主键，自动递增  ',
  `part_id` int NULL DEFAULT NULL COMMENT '配件ID，外键关联parts表的part_id  ',
  `sale_date` date NOT NULL COMMENT '销售日期，不能为空  ',
  `quantity` int NOT NULL COMMENT '销售数量，不能为空  ',
  `customer` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ' 客户名称，不能为空  ',
  PRIMARY KEY (`sale_id`) USING BTREE,
  INDEX `part_id`(`part_id`) USING BTREE,
  CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`part_id`) REFERENCES `parts` (`part_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '销售记录表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
  `password` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码',
  `nickname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '昵称',
  `header` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '头像',
  `email` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '邮箱',
  `pinyin` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '昵称的拼音',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '最后修改时间',
  `status` tinyint NOT NULL COMMENT '状态',
  `del_flag` tinyint NOT NULL COMMENT '删除标识',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `username`(`username`) USING BTREE,
  UNIQUE INDEX `email`(`email`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for vehicles
-- ----------------------------
DROP TABLE IF EXISTS `vehicles`;
CREATE TABLE `vehicles`  (
  `vehicle_id` int NOT NULL AUTO_INCREMENT,
  `license_plate` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `make` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `model` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `year` int NOT NULL,
  `mileage` int NOT NULL,
  `status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`vehicle_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'vehicle_id：车辆的唯一标识符，使用自增主键。\r\nlicense_plate：车辆的牌照号码，使用 VARCHAR 类型存储。\r\nmake：车辆的品牌，使用 VARCHAR 类型存储。\r\nmodel：车辆的型号，使用 VARCHAR 类型存储。\r\nyear：车辆的制造年份，使用 INT 类型存储。\r\nmileage：车辆的里程数，使用 INT 类型存储。\r\nstatus：车辆的状态（例如：在用、停用等），使用 VARCHAR 类型存储。' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;