[MySQL]购物管理系统—简略版-CSDN博客

本文链接：https://blog.csdn.net/autumn2005/article/details/140023273

本文内容需以MySQL支持

特别感谢baidu comate AI提供的少量虚拟数据

0.建库(建立数据库——utf8字符集，utf8_general_ci排序规则)

1.此项目ER图如下

2.DDL+DML(共九表，27数据)

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods`  (
  `goods_id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  `goods_class_id` int(11) NOT NULL,
  `goods_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`goods_id`) USING BTREE,
  INDEX `goods_class_id`(`goods_class_id`) USING BTREE,
  INDEX `goods_name`(`goods_name`) USING BTREE,
  CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`goods_class_id`) REFERENCES `goods_class` (`goods_class_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `goods_class`;
CREATE TABLE `goods_class`  (
  `goods_class_id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  `base_class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`goods_class_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `goods_info`;
CREATE TABLE `goods_info`  (
  `goods_info_id` int(11) NOT NULL AUTO_INCREMENT,
  `goods_id` int(11) NOT NULL,
  `goods_price` decimal(10, 2) NOT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  `goods_class_id` int(11) NOT NULL,
  `goods_number` int(11) NOT NULL,
  PRIMARY KEY (`goods_info_id`) USING BTREE,
  INDEX `goods_id`(`goods_id`) USING BTREE,
  INDEX `goods_class_id`(`goods_class_id`) USING BTREE,
  CONSTRAINT `goods_info_ibfk_1` FOREIGN KEY (`goods_id`) REFERENCES `goods` (`goods_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `goods_info_ibfk_2` FOREIGN KEY (`goods_class_id`) REFERENCES `goods_class` (`goods_class_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `order`;
CREATE TABLE `order`  (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  `goods_id` int(11) NOT NULL,
  `paystatus` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '\'待支付\'',
  `goodstatus` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '\'待运送\',\'运送中\',\'已送达\'',
  PRIMARY KEY (`order_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `goods_id`(`goods_id`) USING BTREE,
  CONSTRAINT `order_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `order_ibfk_2` FOREIGN KEY (`goods_id`) REFERENCES `goods` (`goods_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `order_log`;
CREATE TABLE `order_log`  (
  `order_log_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `paymode` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `number` int(11) NULL DEFAULT NULL,
  `amount` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`order_log_id`) USING BTREE,
  INDEX `order_id`(`order_id`) USING BTREE,
  CONSTRAINT `order_log_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `order` (`order_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` 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,
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`user_id`) USING BTREE,
  UNIQUE INDEX `username`(`user_name`) USING BTREE,
  UNIQUE INDEX `email`(`email`) USING BTREE,
  UNIQUE INDEX `phone`(`phone`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info`  (
  `user_info_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(3) NULL DEFAULT NULL,
  `sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '1' COMMENT '1:男 0:女',
  `id_number` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `true_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`user_info_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `user_info_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `user_wallet`;
CREATE TABLE `user_wallet`  (
  `user_wallet_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `balance` decimal(10, 2) NOT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`user_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 RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `user_wallet_log`;
CREATE TABLE `user_wallet_log`  (
  `user_wallet_log_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `payamount` decimal(10, 2) NOT NULL,
  `user_wallet_id` int(11) NOT NULL,
  `paymode` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`user_wallet_log_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `user_wallet_id`(`user_wallet_id`) USING BTREE,
  CONSTRAINT `user_wallet_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `user_wallet_log_ibfk_2` FOREIGN KEY (`user_wallet_id`) REFERENCES `user_wallet` (`user_wallet_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `user` VALUES (1, 'Alice', 'password123', 'alice@example.com', '1234567890', '2024-06-27 14:11:28');
INSERT INTO `user` VALUES (2, 'Bob', 'bobpass', 'bob@example.com', '0987654321', '2024-06-27 14:11:28');

INSERT INTO `user_info` VALUES (1, 1, 25, '1', '123456789012345678', 'Alice Smith');
INSERT INTO `user_info` VALUES (2, 2, 30, '0', '098765432109876543', 'Bob Johnson');

INSERT INTO `user_wallet` VALUES (1, 1, 1000.00, '2024-06-27 14:11:28');
INSERT INTO `user_wallet` VALUES (2, 2, 500.00, '2024-06-27 14:11:28');

INSERT INTO `user_wallet_log` VALUES (1, 1, 8999.00, 1, '支付宝', '2024-06-27 14:11:28');
INSERT INTO `user_wallet_log` VALUES (2, 2, 39.90, 2, '微信支付', '2024-06-27 14:11:28');

INSERT INTO `goods_class` VALUES (1, '电子产品', '2024-06-27 14:15:22', '0');
INSERT INTO `goods_class` VALUES (2, '生活用品', '2024-06-27 14:15:23', '1');
INSERT INTO `goods_class` VALUES (3, '书籍', '2024-06-27 14:15:25', '1');

INSERT INTO `goods` VALUES (1, '2024-06-27 14:11:28', 1, 'iPhone 13');
INSERT INTO `goods` VALUES (2, '2024-06-27 14:11:28', 2, '洗发水');
INSERT INTO `goods` VALUES (3, '2024-06-27 14:11:28', 3, '编程入门');

INSERT INTO `goods_info` VALUES (1, 1, 8999.00, '2024-06-27 14:11:28', 1, 100);
INSERT INTO `goods_info` VALUES (2, 2, 39.90, '2024-06-27 14:11:28', 2, 200);
INSERT INTO `goods_info` VALUES (3, 3, 59.90, '2024-06-27 14:11:28', 3, 50);

INSERT INTO `order` VALUES (1, 1, '2024-06-27 14:11:28', 1, '待支付', '待运送');
INSERT INTO `order` VALUES (2, 1, '2024-06-27 14:11:28', 2, '已支付', '运送中');
INSERT INTO `order` VALUES (3, 2, '2024-06-27 14:11:28', 3, '已支付', '已送达');

INSERT INTO `order_log` VALUES (1, 1, '支付宝', 1, '8999.00', '2024-06-27 14:11:28');
INSERT INTO `order_log` VALUES (2, 2, '微信支付', 1, '39.90', '2024-06-27 14:11:28');
INSERT INTO `order_log` VALUES (3, 3, '银行卡', 1, '59.90', '2024-06-27 14:11:28');

-- 查询相关insert

-- goods表相关
INSERT INTO `goods` VALUES (4, now(), 1, 'OPPO Reno7 Pro 5G');
INSERT INTO `goods` VALUES (5, now(), 1, 'OPPO Reno7');

-- goods_info相关
INSERT INTO `goods_info` VALUES ('4', '4', '4800.00', now(), '1', '50');
INSERT INTO `goods_info` VALUES ('5', '5', '4500.00', now(), '1', '50');

3.相关查询

-- 查询'已支付'订单
select * from `order` where `order`.paystatus='已支付'; 

-- 查询用户完全信息
select `user`.*,
`user_info`.age,`user_info`.sex,`user_info`.id_number,`user_info`.true_name 
from `user` join `user_info` 
on `user`.user_id=`user_info`.user_id;

-- 模糊查询相似商品(走索引)
select * from `goods` where `goods`.goods_name like 'OPPO%';

4.触发器建立

-- 加入用户钱包
delimiter $$
create trigger insert_user_wallet
after insert on `user`
for each row
begin
	insert into `user_wallet` (user_id,balance,create_time) 
	values (new.user_id,0.00,now());
end;
$$
delimiter ;

测试用相关插入语句

INSERT INTO `user` VALUES (3, 'season', '20050918season', 'season@secondworld.com', '2002020213', now());

-- 删除所有用户信息(慎用，慎用，慎用)
create trigger delete_user_all
before delete on `user`
for each row
begin
	delete from `user_info` where user_id=old.user_id;
	delete from `user_wallet` where user_id=old.user_id;
	delete from `user_wallet_log` where user_id=old.user_id;
	delete from `order` where user_id=old.user_id;
end;

5.订单建立(存储过程)


create procedure create_order(
in in_user_id int,
in in_goods_id int,
in in_count_id int
)
BEGIN
-- 设局部变量+
	declare in_goods_number int;
	declare in_goods_price decimal(10,2);
	declare final_price decimal(10,2);
	declare in_user_balance decimal(10,2);
	declare in_user_wallet_id int;
	declare in_order_id int;
-- 检测是否有库存
	select goods_number into in_goods_number from goods_info where goods_id=in_goods_id;
	if in_count_id>in_goods_number then
		signal sqlstate '45000' set message_text='商品库存不足。';
	end if;
-- 计算商品总价
	select goods_price into in_goods_price from goods_info where goods_id=in_goods_id;
	set final_price=in_goods_price*in_count_id;
-- 检测账户余额
	select balance into in_user_balance from user_wallet where user_id=in_user_id;
	if in_user_balance<final_price then
		signal sqlstate '45000' set message_text='账户余额不足。';
	end if;
-- 进行相关表的修改
	start transaction;
	insert into `order` (user_id,create_time,goods_id,paystatus,goodstatus) values (in_user_id,now(),in_goods_id,'待支付','未运送');  -- order相关
	set in_order_id=LAST_INSERT_ID();
	insert into `order_log` (order_id,paymode,number,amount,create_time) values (in_order_id,'none',in_goods_number,final_price,now());  -- order_log相关
	update goods_info set goods_number=goods_number-in_count_id where goods_id=in_goods_id;  -- goods_info相关
	update user_wallet set balance=balance-final_price where user_id=in_user_id;  -- user_wallet相关
	select user_wallet_id into in_user_wallet_id from user_wallet where user_id=in_user_id;
	insert into user_wallet_log (user_id,user_wallet_id,payamount,paymode,create_time)values(in_user_id,in_user_wallet_id,final_price,'none',now());  -- user_wallet_log相关
	commit;
END;

-- 使用此语句调用[记得替换'()'中的参数]
call create_order('用户_id','商品_id','购买数量');

——想法很多，思路很好，但人类的力量是有极限的。(无名著）