You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
436 lines
20 KiB
436 lines
20 KiB
/*
|
|
Navicat Premium Data Transfer
|
|
|
|
Source Server : a
|
|
Source Server Type : MySQL
|
|
Source Server Version : 90100
|
|
Source Host : localhost:3306
|
|
Source Schema : db
|
|
|
|
Target Server Type : MySQL
|
|
Target Server Version : 90100
|
|
File Encoding : 65001
|
|
|
|
Date: 30/12/2024 13:03:38
|
|
*/
|
|
|
|
SET NAMES utf8mb4;
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
-- ----------------------------
|
|
-- Table structure for employee
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `employee`;
|
|
CREATE TABLE `employee` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
|
|
`wage` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
|
|
`work` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`) USING BTREE,
|
|
UNIQUE INDEX `name`(`name` ASC) USING BTREE
|
|
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
|
|
|
|
-- ----------------------------
|
|
-- Records of employee
|
|
-- ----------------------------
|
|
INSERT INTO `employee` VALUES (1, '江远', '5000', '制作豆类糖水');
|
|
INSERT INTO `employee` VALUES (2, '沈煜', '3000', '制作果类糖水');
|
|
INSERT INTO `employee` VALUES (3, '周余', '3000', '制作奶类糖水');
|
|
INSERT INTO `employee` VALUES (4, '许中', '5000', '收银员');
|
|
INSERT INTO `employee` VALUES (5, '周米', '1000', '制作滋补类糖水');
|
|
INSERT INTO `employee` VALUES (6, '家回', '5000', '收银员');
|
|
|
|
-- ----------------------------
|
|
-- Table structure for good
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `good`;
|
|
CREATE TABLE `good` (
|
|
`id` int NOT NULL,
|
|
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
`type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
|
|
`price` float NOT NULL,
|
|
`surplus` int NULL DEFAULT NULL,
|
|
`ingredient` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
|
|
`employee` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`, `name`, `price`) USING BTREE,
|
|
INDEX `ingredient_id`(`ingredient` ASC) USING BTREE,
|
|
UNIQUE INDEX `name`(`name` ASC) USING BTREE,
|
|
INDEX `employee`(`employee` ASC) USING BTREE,
|
|
UNIQUE INDEX `id`(`id` ASC) USING BTREE,
|
|
INDEX `price`(`price` ASC) USING BTREE,
|
|
CONSTRAINT `employee` FOREIGN KEY (`employee`) REFERENCES `employee` (`name`) ON DELETE SET NULL ON UPDATE RESTRICT,
|
|
CONSTRAINT `ingredient_id` FOREIGN KEY (`ingredient`) REFERENCES `ingredient` (`name`) ON DELETE SET NULL ON UPDATE CASCADE
|
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
|
|
|
|
-- ----------------------------
|
|
-- Records of good
|
|
-- ----------------------------
|
|
INSERT INTO `good` VALUES (1, '红豆糖水', '豆类', 14, 10, '红豆', '江远');
|
|
INSERT INTO `good` VALUES (2, '绿豆沙', '豆类', 12, 10, '绿豆', '许中');
|
|
INSERT INTO `good` VALUES (3, '芋圆西米露', '果类', 15, 2, '芋头', '沈煜');
|
|
INSERT INTO `good` VALUES (4, '姜汁撞奶', '奶类', 14, 3, '牛奶', '周余');
|
|
INSERT INTO `good` VALUES (5, '莲子百合红豆沙', '豆类', 18, 4, '莲子', '江远');
|
|
INSERT INTO `good` VALUES (6, '双皮奶', '奶类', 16, 4, '牛奶', '周余');
|
|
INSERT INTO `good` VALUES (7, '雪梨银耳汤', '果类', 13, 5, '雪梨', '沈煜');
|
|
INSERT INTO `good` VALUES (8, '椰汁西米露', '果类', 17, 5, '西米', '沈煜');
|
|
INSERT INTO `good` VALUES (9, '红枣枸杞糖水', '滋补类', 19, 6, '红枣', '周米');
|
|
INSERT INTO `good` VALUES (10, '地瓜糖水', '滋补类', 10, 4, '地瓜', '周米');
|
|
|
|
-- ----------------------------
|
|
-- Table structure for ingredient
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `ingredient`;
|
|
CREATE TABLE `ingredient` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
|
|
`surplus` int NULL DEFAULT NULL,
|
|
`supplier` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
|
|
`money` float NULL DEFAULT NULL,
|
|
`date` datetime NOT NULL,
|
|
PRIMARY KEY (`id`, `name`) USING BTREE,
|
|
UNIQUE INDEX `name`(`name` ASC) USING BTREE,
|
|
INDEX `id`(`id` ASC) USING BTREE,
|
|
INDEX `supplier`(`supplier` ASC) USING BTREE,
|
|
CONSTRAINT `supplier` FOREIGN KEY (`supplier`) REFERENCES `supplier` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE = InnoDB AUTO_INCREMENT = 22 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
|
|
|
|
-- ----------------------------
|
|
-- Records of ingredient
|
|
-- ----------------------------
|
|
INSERT INTO `ingredient` VALUES (1, '红豆', 20, '王五', 200, '2024-12-27 20:24:03');
|
|
INSERT INTO `ingredient` VALUES (2, '绿豆', 20, '王五', 100, '2025-02-01 20:36:21');
|
|
INSERT INTO `ingredient` VALUES (3, '椰浆', 15, '许伟', 600, '2025-01-05 20:37:15');
|
|
INSERT INTO `ingredient` VALUES (4, '冰糖', 36, '西西', 300, '2025-02-02 20:37:49');
|
|
INSERT INTO `ingredient` VALUES (5, '西米', 24, '李守', 600, '2024-12-17 20:38:16');
|
|
INSERT INTO `ingredient` VALUES (6, '牛奶', 12, '刘远', 700, '2025-01-04 20:38:49');
|
|
INSERT INTO `ingredient` VALUES (7, '莲子', 43, '刘明', 500, '2024-12-23 20:39:32');
|
|
INSERT INTO `ingredient` VALUES (8, '鸡蛋', 65, '谢芳', 300, '2025-01-01 20:40:14');
|
|
INSERT INTO `ingredient` VALUES (9, '雪梨', 13, '余周', 100, '2024-12-24 20:40:46');
|
|
INSERT INTO `ingredient` VALUES (10, '红枣', 45, '黄瑞', 800, '2024-12-24 20:41:32');
|
|
INSERT INTO `ingredient` VALUES (11, '地瓜', 45, '明寒', 400, '2025-01-02 20:42:03');
|
|
INSERT INTO `ingredient` VALUES (12, '芋头', 12, '明寒', 100, '2024-12-16 20:48:15');
|
|
INSERT INTO `ingredient` VALUES (13, '奶油', 56, '西西', 600, '2024-12-13 13:29:17');
|
|
|
|
-- ----------------------------
|
|
-- Table structure for order
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `order`;
|
|
CREATE TABLE `order` (
|
|
`id` int NOT NULL,
|
|
`content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
|
|
`date` datetime NULL DEFAULT NULL,
|
|
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
|
|
`money` decimal(10, 2) NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`, `name`) USING BTREE,
|
|
UNIQUE INDEX `id`(`id` ASC) USING BTREE,
|
|
INDEX `user_name`(`name` ASC) USING BTREE,
|
|
CONSTRAINT `name` FOREIGN KEY (`name`) REFERENCES `users` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE
|
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
|
|
|
|
-- ----------------------------
|
|
-- Records of order
|
|
-- ----------------------------
|
|
INSERT INTO `order` VALUES (1, '2芋圆西米露', '2024-12-26 01:22:00', 'Amber Mills', 22.00);
|
|
INSERT INTO `order` VALUES (2, '2绿豆沙', '2024-12-17 12:31:55', 'Nakamura Hina', 24.00);
|
|
INSERT INTO `order` VALUES (3, '1芋圆西米露', '2024-12-07 12:32:01', 'Nakamura Hina', 15.00);
|
|
INSERT INTO `order` VALUES (4, '1姜汁撞奶', '2024-12-11 12:32:05', 'Kono Ryota', 14.00);
|
|
INSERT INTO `order` VALUES (5, '2双皮奶', '2024-12-25 15:23:26', 'Lu Rui', 18.00);
|
|
INSERT INTO `order` VALUES (6, '3椰汁西米露', '2024-12-18 12:32:11', 'Fan Kwok Yin', 16.00);
|
|
INSERT INTO `order` VALUES (7, '1双皮奶', '2024-12-29 12:32:16', 'Noguchi Takuya', 16.00);
|
|
INSERT INTO `order` VALUES (8, '1地瓜糖水', '2024-12-09 12:32:21', 'Noguchi Takuya', 10.00);
|
|
INSERT INTO `order` VALUES (9, '2双皮奶', '2024-12-12 12:32:27', 'Esther Woods', 32.00);
|
|
INSERT INTO `order` VALUES (10, '1红枣枸杞糖水', '2024-12-25 12:32:31', 'Fan Kwok Yin', 19.00);
|
|
INSERT INTO `order` VALUES (11, '1姜汁撞奶', '2024-12-17 12:32:39', 'Nakamura Hina', 14.00);
|
|
INSERT INTO `order` VALUES (12, '3芋圆西米露', '2024-12-16 12:32:45', 'Noguchi Takuya', 15.00);
|
|
INSERT INTO `order` VALUES (13, NULL, NULL, 'Esther Woods', NULL);
|
|
|
|
-- ----------------------------
|
|
-- Table structure for ordercon
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `ordercon`;
|
|
CREATE TABLE `ordercon` (
|
|
`id` int NOT NULL AUTO_INCREMENT,
|
|
`orderid` int NULL DEFAULT NULL,
|
|
`good` int NULL DEFAULT NULL,
|
|
`number` int NULL DEFAULT NULL,
|
|
`price` decimal(10, 2) NULL DEFAULT NULL,
|
|
`money` decimal(10, 2) NULL DEFAULT NULL,
|
|
`state` enum('已完成','未完成') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`) USING BTREE,
|
|
INDEX `good_id`(`good` ASC) USING BTREE,
|
|
INDEX `id`(`orderid` ASC) USING BTREE,
|
|
CONSTRAINT `id` FOREIGN KEY (`orderid`) REFERENCES `order` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
|
|
CONSTRAINT `good_id` FOREIGN KEY (`good`) REFERENCES `good` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
|
|
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
|
|
|
|
-- ----------------------------
|
|
-- Records of ordercon
|
|
-- ----------------------------
|
|
INSERT INTO `ordercon` VALUES (1, 1, 1, 1, 10.00, 12.00, NULL);
|
|
INSERT INTO `ordercon` VALUES (2, 1, 2, 1, 12.00, 12.00, NULL);
|
|
INSERT INTO `ordercon` VALUES (3, 2, 3, 2, 12.00, 24.00, NULL);
|
|
INSERT INTO `ordercon` VALUES (4, 3, 3, 1, 15.00, 15.00, NULL);
|
|
INSERT INTO `ordercon` VALUES (5, 4, 4, 1, 14.00, 14.00, NULL);
|
|
INSERT INTO `ordercon` VALUES (6, 5, 6, 2, 16.00, 32.00, NULL);
|
|
INSERT INTO `ordercon` VALUES (7, 6, 8, 3, 17.00, 51.00, NULL);
|
|
INSERT INTO `ordercon` VALUES (8, 7, 6, 1, 16.00, 16.00, NULL);
|
|
INSERT INTO `ordercon` VALUES (9, 8, 10, 1, 10.00, 10.00, NULL);
|
|
INSERT INTO `ordercon` VALUES (10, 9, 6, 2, 16.00, 16.00, NULL);
|
|
INSERT INTO `ordercon` VALUES (11, 10, 9, 1, 19.00, 19.00, NULL);
|
|
INSERT INTO `ordercon` VALUES (12, 11, 4, 1, 14.00, 14.00, NULL);
|
|
INSERT INTO `ordercon` VALUES (13, 12, 3, 3, 15.00, 45.00, NULL);
|
|
INSERT INTO `ordercon` VALUES (14, 13, 7, 2, 13.00, 26.00, NULL);
|
|
|
|
-- ----------------------------
|
|
-- Table structure for supplier
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `supplier`;
|
|
CREATE TABLE `supplier` (
|
|
`id` int NOT NULL,
|
|
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
|
|
`phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
|
|
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
|
|
`ingredient` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
|
|
`number` int NULL DEFAULT NULL,
|
|
`state` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`, `name`) USING BTREE,
|
|
UNIQUE INDEX `name`(`name` ASC) USING BTREE,
|
|
INDEX `ingredient`(`ingredient` ASC) USING BTREE
|
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
|
|
|
|
-- ----------------------------
|
|
-- Records of supplier
|
|
-- ----------------------------
|
|
INSERT INTO `supplier` VALUES (1, '王五', '760-029-3801', '广西', '红豆，绿豆', 649, '合作');
|
|
INSERT INTO `supplier` VALUES (2, '许伟', '760-079-0782', '广西', '椰浆', 908, '合作');
|
|
INSERT INTO `supplier` VALUES (3, '西西', '838-088-7149', '广西', '冰糖', 832, '停止');
|
|
INSERT INTO `supplier` VALUES (4, '李守', '(20) 7943 9862', '广东', '西米', 268, '停止');
|
|
INSERT INTO `supplier` VALUES (5, '刘远', '760-3593-6621', '贵州', '牛奶', 619, '停止');
|
|
INSERT INTO `supplier` VALUES (6, '刘明', '838-198-4024', '云南', '莲子', 516, '合作');
|
|
INSERT INTO `supplier` VALUES (7, '谢芳', '162-4143-1043', '云南', '鸡蛋', 82, '合作');
|
|
INSERT INTO `supplier` VALUES (8, '余周', '(151) 485 3440', '湖南', '雪梨', 273, '合作');
|
|
INSERT INTO `supplier` VALUES (9, '黄瑞', '614-712-5158', '湖南', '红枣', 746, '合作');
|
|
INSERT INTO `supplier` VALUES (10, '明寒', '(116) 727 1411', '湖北', '地瓜', 542, '合作');
|
|
|
|
-- ----------------------------
|
|
-- Table structure for users
|
|
-- ----------------------------
|
|
DROP TABLE IF EXISTS `users`;
|
|
CREATE TABLE `users` (
|
|
`id` int NOT NULL,
|
|
`account` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '账号',
|
|
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '密码',
|
|
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
|
|
`phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '手机号',
|
|
PRIMARY KEY (`id`, `name`) USING BTREE,
|
|
UNIQUE INDEX `name`(`name` ASC) USING BTREE,
|
|
INDEX `id`(`id` ASC) USING BTREE
|
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
|
|
|
|
-- ----------------------------
|
|
-- Records of users
|
|
-- ----------------------------
|
|
INSERT INTO `users` VALUES (1, '993', '123456', 'Amber Mills', '213-997-2362');
|
|
INSERT INTO `users` VALUES (2, '380', 'LCIbMnJ3CN', 'Murakami Kasumi', '10-0045-3026');
|
|
INSERT INTO `users` VALUES (3, '600', 'F3fNKq0FkG', 'Fan Kwok Yin', '213-516-0115');
|
|
INSERT INTO `users` VALUES (4, '899', 'OBZFIStIf2', 'Nakamura Hina', '186-0063-2734');
|
|
INSERT INTO `users` VALUES (5, '936', 'BlZxkl0FZP', 'Kevin Washington', '5832 060970');
|
|
INSERT INTO `users` VALUES (6, '992', 'FWf0bzYgyA', 'Esther Woods', '213-769-7280');
|
|
INSERT INTO `users` VALUES (7, '954', 'etgXH3MsNj', 'Kono Ryota', '52-164-7344');
|
|
INSERT INTO `users` VALUES (8, '23', 'iyXymwxLNV', 'Lu Rui', '7459 798893');
|
|
INSERT INTO `users` VALUES (9, '983', '8hi7jrqR38', 'Noguchi Takuya', '7784 176690');
|
|
INSERT INTO `users` VALUES (10, '839', 'XGQmJC8lch', 'Fong Kwok Yin', '10-6181-9374');
|
|
INSERT INTO `users` VALUES (11, '658', '123456789', 'xingxu', '124-654-753');
|
|
|
|
-- ----------------------------
|
|
-- View structure for 原料花费表
|
|
-- ----------------------------
|
|
DROP VIEW IF EXISTS `原料花费表`;
|
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `原料花费表` AS select `ingredient`.`name` AS `name`,`ingredient`.`money` AS `money` from `ingredient` order by `ingredient`.`money`;
|
|
|
|
-- ----------------------------
|
|
-- View structure for 原料补货表
|
|
-- ----------------------------
|
|
DROP VIEW IF EXISTS `原料补货表`;
|
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `原料补货表` AS select `ingredient`.`name` AS `name`,`ingredient`.`surplus` AS `surplus`,`ingredient`.`supplier` AS `supplier`,`ingredient`.`money` AS `money`,`supplier`.`phone` AS `phone`,`supplier`.`address` AS `address`,`supplier`.`number` AS `number` from (`ingredient` join `supplier` on((`ingredient`.`supplier` = `supplier`.`name`)));
|
|
|
|
-- ----------------------------
|
|
-- View structure for 甜品制作所需信息表
|
|
-- ----------------------------
|
|
DROP VIEW IF EXISTS `甜品制作所需信息表`;
|
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `甜品制作所需信息表` AS select `g`.`employee` AS `employee`,`g`.`name` AS `name`,`g`.`type` AS `type`,`g`.`price` AS `price`,`g`.`ingredient` AS `ingredient`,`ingredient`.`surplus` AS `surplus` from ((`employee` `e` join `good` `g` on((`e`.`name` = `g`.`employee`))) join `ingredient` on((`g`.`ingredient` = `ingredient`.`name`)));
|
|
|
|
-- ----------------------------
|
|
-- View structure for 订单和用户信息表
|
|
-- ----------------------------
|
|
DROP VIEW IF EXISTS `订单和用户信息表`;
|
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `订单和用户信息表` AS select `o`.`content` AS `content`,`o`.`date` AS `date`,`u`.`name` AS `name`,`o`.`money` AS `money`,`u`.`phone` AS `phone`,`u`.`account` AS `account` from (`order` `o` join `users` `u` on((`o`.`name` = `u`.`name`)));
|
|
|
|
-- ----------------------------
|
|
-- View structure for 订单数量最多的甜品
|
|
-- ----------------------------
|
|
DROP VIEW IF EXISTS `订单数量最多的甜品`;
|
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `订单数量最多的甜品` AS select `good`.`name` AS `name`,`order`.`id` AS `id`,`order`.`content` AS `content`,`ordercon`.`number` AS `number` from ((`good` join `ordercon` on((`good`.`id` = `ordercon`.`good`))) join `order` on((`order`.`id` = `ordercon`.`orderid`))) order by `ordercon`.`number` desc;
|
|
|
|
-- ----------------------------
|
|
-- Procedure structure for cancel_order
|
|
-- ----------------------------
|
|
DROP PROCEDURE IF EXISTS `cancel_order`;
|
|
delimiter ;;
|
|
CREATE PROCEDURE `cancel_order`(IN p_user VARCHAR(255),
|
|
IN p_order_id INT)
|
|
BEGIN
|
|
-- 删除记录
|
|
DELETE FROM `order`
|
|
WHERE `name` = p_user AND id = p_order_id;
|
|
END
|
|
;;
|
|
delimiter ;
|
|
|
|
-- ----------------------------
|
|
-- Procedure structure for create_new_user
|
|
-- ----------------------------
|
|
DROP PROCEDURE IF EXISTS `create_new_user`;
|
|
delimiter ;;
|
|
CREATE PROCEDURE `create_new_user`(IN p_account VARCHAR(255),
|
|
IN p_password VARCHAR(255), -- 注意：这里存储的是明文密码，通常应该存储哈希值
|
|
IN p_name VARCHAR(255),
|
|
IN p_phone VARCHAR(255))
|
|
BEGIN
|
|
-- 检查用户名是否已存在
|
|
IF EXISTS (SELECT 1 FROM users WHERE account = p_account) THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Username already exists';
|
|
ELSE
|
|
-- 如果用户名不存在，则插入新用户
|
|
INSERT INTO users (account, password, name, phone)
|
|
VALUES (p_account, p_password, p_name, p_phone);
|
|
END IF;
|
|
END
|
|
;;
|
|
delimiter ;
|
|
|
|
-- ----------------------------
|
|
-- Procedure structure for getUserOrders
|
|
-- ----------------------------
|
|
DROP PROCEDURE IF EXISTS `getUserOrders`;
|
|
delimiter ;;
|
|
CREATE PROCEDURE `getUserOrders`(IN p_user VARCHAR(255))
|
|
BEGIN
|
|
|
|
SELECT
|
|
o.id AS order_id,
|
|
o.content, -- 订单内容的字段
|
|
o.`name` AS order_name,
|
|
o.date,
|
|
oc.good, -- 商品名称的字段
|
|
oc.price AS good_price -- 商品价格的字段
|
|
FROM
|
|
`order` o
|
|
LEFT JOIN
|
|
`ordercon` oc ON o.id = oc.orderid -- 正确的连接条件
|
|
WHERE
|
|
|
|
o.`name` = p_user; --
|
|
END
|
|
;;
|
|
delimiter ;
|
|
|
|
-- ----------------------------
|
|
-- Procedure structure for place_dessert_order
|
|
-- ----------------------------
|
|
DROP PROCEDURE IF EXISTS `place_dessert_order`;
|
|
delimiter ;;
|
|
CREATE PROCEDURE `place_dessert_order`()
|
|
BEGIN
|
|
UPDATE ingredient
|
|
INNER JOIN good ON ingredient.`name`=good.`name`
|
|
SET ingredient.surplus=ingredient.surplus-(NEW.surplus-OLD.surplus)
|
|
WHERE good.id=NEW.id;
|
|
END
|
|
;;
|
|
delimiter ;
|
|
|
|
-- ----------------------------
|
|
-- Procedure structure for update_user_info
|
|
-- ----------------------------
|
|
DROP PROCEDURE IF EXISTS `update_user_info`;
|
|
delimiter ;;
|
|
CREATE PROCEDURE `update_user_info`(IN p_user_id INT,
|
|
IN p_account VARCHAR(255),
|
|
IN p_password VARCHAR(255), -- 注意：通常应存储密码的哈希值
|
|
IN p_name VARCHAR(255),
|
|
IN p_phone VARCHAR(255))
|
|
BEGIN
|
|
-- 更新用户信息
|
|
UPDATE users
|
|
SET account = p_account,
|
|
`password` = p_password,
|
|
name = p_name,
|
|
phone = p_phone
|
|
WHERE id = p_user_id;
|
|
|
|
|
|
END
|
|
;;
|
|
delimiter ;
|
|
|
|
-- ----------------------------
|
|
-- Triggers structure for table good
|
|
-- ----------------------------
|
|
DROP TRIGGER IF EXISTS `ingredient_number`;
|
|
delimiter ;;
|
|
CREATE TRIGGER `ingredient_number` AFTER UPDATE ON `good` FOR EACH ROW BEGIN
|
|
|
|
UPDATE ingredient
|
|
INNER JOIN good ON ingredient.`name` = good.`name`
|
|
SET ingredient.surplus = ingredient.surplus -
|
|
(NEW.surplus - OLD.surplus)
|
|
WHERE good.id = NEW.id;
|
|
END
|
|
;;
|
|
delimiter ;
|
|
|
|
-- ----------------------------
|
|
-- Triggers structure for table ordercon
|
|
-- ----------------------------
|
|
DROP TRIGGER IF EXISTS `reduce_supplier`;
|
|
delimiter ;;
|
|
CREATE TRIGGER `reduce_supplier` BEFORE UPDATE ON `ordercon` FOR EACH ROW BEGIN
|
|
IF new.state = '已完成' THEN
|
|
UPDATE good
|
|
SET `supplier` = `supplier` - 1
|
|
WHERE id = new.good;
|
|
END IF;
|
|
END
|
|
;;
|
|
delimiter ;
|
|
|
|
-- ----------------------------
|
|
-- Triggers structure for table ordercon
|
|
-- ----------------------------
|
|
DROP TRIGGER IF EXISTS `order_get_price`;
|
|
delimiter ;;
|
|
CREATE TRIGGER `order_get_price` BEFORE UPDATE ON `ordercon` FOR EACH ROW BEGIN
|
|
DECLARE good_price DECIMAL(10, 2);
|
|
SELECT price INTO good_price
|
|
FROM good
|
|
WHERE good.id = NEW.good;
|
|
|
|
SET NEW.price = good_price;
|
|
END
|
|
;;
|
|
delimiter ;
|
|
|
|
-- ----------------------------
|
|
-- Triggers structure for table ordercon
|
|
-- ----------------------------
|
|
DROP TRIGGER IF EXISTS `count_money`;
|
|
delimiter ;;
|
|
CREATE TRIGGER `count_money` BEFORE UPDATE ON `ordercon` FOR EACH ROW BEGIN
|
|
SET NEW.money = NEW.price * NEW.number;
|
|
END
|
|
;;
|
|
delimiter ;
|
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|