Pay_Project/byc_mysql.sql

/*
 Navicat Premium Dump SQL

 Source Server         : Oracle_byc_mysql
 Source Server Type    : MySQL
 Source Server Version : 80036 (8.0.36)
 Source Host           : 139.185.41.147:3306
 Source Schema         : byc_mysql

 Target Server Type    : MySQL
 Target Server Version : 80036 (8.0.36)
 File Encoding         : 65001

 Date: 10/11/2024 17:29:00
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for debtors
-- ----------------------------
DROP TABLE IF EXISTS `debtors`;
CREATE TABLE `debtors`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `debtor_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `total_amount` decimal(10, 2) NULL DEFAULT 110000.00,
  `last_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `debtor_name`(`debtor_name` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of debtors
-- ----------------------------
INSERT INTO `debtors` VALUES (1, '白宇超', 122500.00, '2024-11-01 10:32:08');

-- ----------------------------
-- Table structure for repayments
-- ----------------------------
DROP TABLE IF EXISTS `repayments`;
CREATE TABLE `repayments`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `debtor_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `repayment_date` date NOT NULL,
  `repayment_method` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '微信',
  `repayment_amount` decimal(10, 2) NULL DEFAULT 1250.00,
  `comments` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `debtor_name`(`debtor_name` ASC) USING BTREE,
  CONSTRAINT `repayments_ibfk_1` FOREIGN KEY (`debtor_name`) REFERENCES `debtors` (`debtor_name`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of repayments
-- ----------------------------
INSERT INTO `repayments` VALUES (4, '白宇超', '2024-03-12', '微信', 2500.00, NULL);
INSERT INTO `repayments` VALUES (5, '白宇超', '2024-04-12', '微信', 1250.00, NULL);
INSERT INTO `repayments` VALUES (6, '白宇超', '2024-05-13', '微信', 1250.00, NULL);
INSERT INTO `repayments` VALUES (7, '白宇超', '2024-06-12', '微信', 1250.00, NULL);
INSERT INTO `repayments` VALUES (8, '白宇超', '2024-07-12', '微信', 1250.00, NULL);
INSERT INTO `repayments` VALUES (9, '白宇超', '2024-08-12', '微信', 1250.00, NULL);
INSERT INTO `repayments` VALUES (10, '白宇超', '2024-09-12', '微信', 1250.00, NULL);
INSERT INTO `repayments` VALUES (11, '白宇超', '2024-10-12', '微信', 1250.00, NULL);

-- ----------------------------
-- View structure for debtstatus
-- ----------------------------
DROP VIEW IF EXISTS `debtstatus`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `debtstatus` AS select `d`.`debtor_name` AS `姓名`,`d`.`total_amount` AS `总欠款金额`,(`d`.`total_amount` - ifnull(sum(`r`.`repayment_amount`),0)) AS `剩余欠款金额` from (`debtors` `d` left join `repayments` `r` on((`d`.`debtor_name` = `r`.`debtor_name`))) group by `d`.`id`,`d`.`debtor_name`,`d`.`total_amount`;

-- ----------------------------
-- Event structure for monthly_increase
-- ----------------------------
DROP EVENT IF EXISTS `monthly_increase`;
delimiter ;;
CREATE EVENT `monthly_increase`
ON SCHEDULE
EVERY '1' MONTH STARTS '2024-12-01 00:00:00'
DO UPDATE Debtors
    SET total_amount = total_amount + 1250
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;