MySQL数据库基础练习系列：会员管理系统

梦醒如希

已于 2024-06-28 09:32:56 修改

阅读量243

点赞数 4

文章标签：数据库 mysql

于 2024-06-28 09:27:40 首次发布

本文链接：https://blog.csdn.net/2302_81555060/article/details/140033259

版权

DDL

CREATE TABLE `member` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `username` varchar(255) NOT NULL COMMENT '用户名',  `password` varchar(255) NOT NULL COMMENT '密码',  `email` varchar(255) NOT NULL COMMENT '邮箱',  `phone` varchar(20) NOT NULL COMMENT '电话',  `gender` char(1) NOT NULL COMMENT '性别（M/F）',  `birthdate` date NOT NULL COMMENT '出生日期',  PRIMARY KEY (`id`),  UNIQUE KEY `username` (`username`),  UNIQUE KEY `email` (`email`),  UNIQUE KEY `phone` (`phone`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员表’;

CREATE TABLE `member_address` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `member_id` int(11) NOT NULL COMMENT '会员ID',  `province` varchar(255) NOT NULL COMMENT '省份',  `city` varchar(255) NOT NULL COMMENT '城市',  `district` varchar(255) NOT NULL COMMENT '区县',  `street` varchar(255) NOT NULL COMMENT '街道',  `postcode` varchar(10) NOT NULL COMMENT '邮编',  PRIMARY KEY (`id`),  FOREIGN KEY (`member_id`) REFERENCES `member` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,  UNIQUE KEY `member_id` (`member_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员地址表';

CREATE TABLE `member_level` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `level_name` varchar(255) NOT NULL COMMENT '等级名称',  `discount` decimal(5,2) NOT NULL COMMENT '折扣（0-1）',  PRIMARY KEY (`id`),  UNIQUE KEY `level_name` (`level_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员等级表';

CREATE TABLE `member_level_rel` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `member_id` int(11) NOT NULL COMMENT '会员ID',  `level_id` int(11) NOT NULL COMMENT '等级ID',  PRIMARY KEY (`id`),  FOREIGN KEY (`member_id`) REFERENCES `member` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,  FOREIGN KEY (`level_id`) REFERENCES `member_level` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,  UNIQUE KEY `member_id` (`member_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员与等级关联表';

CREATE TABLE `order` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `member_id` int(11) NOT NULL COMMENT '会员ID',  `order_no` varchar(255) NOT NULL COMMENT '订单号',  `total_amount` decimal(10,2) NOT NULL COMMENT '订单总金额',  `create_time` datetime NOT NULL COMMENT '创建时间',  PRIMARY KEY (`id`),  FOREIGN KEY (`member_id`) REFERENCES `member` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,  UNIQUE KEY `order_no` (`order_no`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

CREATE TABLE `order_detail` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `order_id` int(11) NOT NULL COMMENT '订单ID',  `product_id` int(11) NOT NULL COMMENT '商品ID',  `quantity` int(11) NOT NULL COMMENT '购买数量',  `price` decimal(10,2) NOT NULL COMMENT '单价',  PRIMARY KEY (`id`),  FOREIGN KEY (`order_id`) REFERENCES `order` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,  FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,  UNIQUE KEY `order_id` (`order_id`, `product_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单详情表';

CREATE TABLE `product` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) NOT NULL COMMENT '商品名称',  `price` decimal(10,2) NOT NULL COMMENT '价格',  `stock` int(11) NOT NULL COMMENT '库存',  PRIMARY KEY (`id`),  UNIQUE KEY `name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

DROP TABLE IF EXISTS `member_log`;
CREATE TABLE `member_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `action` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

DML

INSERT INTO `member` (`username`, `password`, `email`, `phone`, `gender`, `birthdate`)VALUES('user1', 'password1', 'user1@example.com', '13800000001', 'M', '1990-01-01'),('user2', 'password2', 'user2@example.com', '13800000002', 'F', '1991-02-02'),('user3', 'password3', 'user3@example.com', '13800000003', 'M', '1992-03-03'),('user4', 'password4', 'user4@example.com', '13800000004', 'F', '1993-04-04'),('user5', 'password5', 'user5@example.com', '13800000005', 'M', '1994-05-05');

INSERT INTO `member_address` (`member_id`, `province`, `city`, `district`, `street`, `postcode`)VALUES(1, '广东省', '广州市', '天河区', '中山大道西', '510000'),(2, '江苏省', '南京市', '玄武区', '中山路', '210000'),(3, '浙江省', '杭州市', '西湖区', '文三路', '310000'),(4, '四川省', '成都市', '武侯区', '人民南路', '610000'),(5, '北京市', '北京市', '东城区', '长安街', '100000');

INSERT INTO `member_level` VALUES ('1', '普通会员', '0.95');
INSERT INTO `member_level` VALUES ('2', '白银会员', '0.90');
INSERT INTO `member_level` VALUES ('3', '黄金会员', '0.80');
INSERT INTO `member_level` VALUES ('4', '铂金会员', '0.65');
INSERT INTO `member_level` VALUES ('5', '钻石会员', '0.30');
INSERT INTO `member_level` VALUES ('6', '星耀会员', '0.10');

INSERT INTO `member_level_rel` VALUES ('1', '1', '1');
INSERT INTO `member_level_rel` VALUES ('2', '2', '1');
INSERT INTO `member_level_rel` VALUES ('3', '3', '1');
INSERT INTO `member_level_rel` VALUES ('4', '4', '1');
INSERT INTO `member_level_rel` VALUES ('5', '5', '2');

INSERT INTO `order` VALUES ('1', '1', 'O20220101001', '199.99', '2022-01-01 10:00:00');
INSERT INTO `order` VALUES ('2', '2', 'O20220101002', '299.99', '2022-01-01 11:00:00');
INSERT INTO `order` VALUES ('3', '3', 'O20220101003', '399.99', '2022-01-01 12:00:00');
INSERT INTO `order` VALUES ('4', '4', 'O20220101004', '499.99', '2022-01-01 13:00:00');
INSERT INTO `order` VALUES ('5', '5', 'O20220101005', '599.99', '2022-01-01 14:00:00');
INSERT INTO `order` VALUES ('7', '1', 'O20220101006', '99.99', '2022-01-01 15:00:00');

INSERT INTO `order_detail` VALUES ('1', '1', '1', '2', '99.99');
INSERT INTO `order_detail` VALUES ('2', '2', '2', '3', '99.99');
INSERT INTO `order_detail` VALUES ('3', '3', '3', '4', '99.99');
INSERT INTO `order_detail` VALUES ('4', '4', '4', '5', '99.99');
INSERT INTO `order_detail` VALUES ('5', '5', '5', '6', '99.99');
INSERT INTO `order_detail` VALUES ('7', '7', '1', '1', '99.99');

INSERT INTO `product` VALUES ('1', '商品1', '99.99', '9');
INSERT INTO `product` VALUES ('2', '商品2', '99.99', '20');
INSERT INTO `product` VALUES ('3', '商品3', '99.99', '30');
INSERT INTO `product` VALUES ('4', '商品4', '99.99', '40');
INSERT INTO `product` VALUES ('5', '商品5', '99.99', '50');
INSERT INTO `product` VALUES ('15', '测试商品', '100.00', '10');

INSERT INTO `member_log` VALUES (1, 'testuser', 'testpassword', 'INSERT', '2024-6-24 12:51:46');

ER图

数据库模型图

简单查询

查询member表中的id和phone字段，并将结果分别命名为'姓名'和'手机号'。

SELECT id AS '姓名', phone AS '手机号' FROM member;

在product表中创建名为idx_product_name的索引，然后查询名字包含"商品"的所有产品，并解释这个查询的性能。

CREATE INDEX idx_product_name ON product(name);SELECT * FROM product WHERE name LIKE '%商品%';EXPLAIN SELECT * FROM product WHERE name LIKE '%商品%';

查询每个会员的订单数量，并按订单数量降序排列

SELECT member_id, COUNT(*) as order_countFROM `order`GROUP BY member_idORDER BY order_count DESC;

复杂查询

查询会员等级和会员基本信息，以及将它们联合起来进行多表查询

SELECT * FROM member_level;  -- 复杂查询会员等级查询SELECT * FROM member; -- 复杂查询会员的基本信息SELECT m.id, m.username, m.email, m.phone, m.gender, m.birthdate, l.level_name, l.discountFROM member mJOIN member_level l ON m.id = l.id; -- 多表联合查询

查询每个会员等级的平均折扣率

SELECT level_name, AVG(discount) as average_discountFROM member_levelGROUP BY level_name;

查询每个会员的订单总额，并考虑会员等级的折扣率

SELECT    ml.id AS member_id,    ml.level_name,    SUM(o.total_amount * (1 - ml.discount)) AS discounted_total_amountFROM    member_level mlJOIN    `order` o ON ml.id = o.member_idGROUP BY    ml.id,    ml.level_name;

触发器

DELIMITER //
CREATE TRIGGER before_order_delete
BEFORE DELETE ON `order`
FOR EACH ROW
BEGIN
    -- 在删除订单信息前，先删除订单详情表中的订单信息
    DELETE FROM `order_detail` WHERE `order_id` = OLD.`id`;
END;
//
DELIMITER ;

-- 插入一条订单数据
INSERT INTO `order` (`member_id`, `order_no`, `total_amount`, `create_time`) VALUES (1, 'O123456', 100.00, NOW());

-- 插入一条订单详情数据
INSERT INTO `order_detail` (`order_id`, `product_id`, `quantity`, `price`) VALUES (LAST_INSERT_ID(), 1, 2, 50.00);

-- 查询订单和订单详情表，确认数据已插入
SELECT * FROM `order`;
SELECT * FROM `order_detail`;

-- 删除刚刚插入的订单数据
DELETE FROM `order` WHERE `id` = LAST_INSERT_ID();

-- 查询订单和订单详情表，确认订单详情数据已被删除
SELECT * FROM `order`;
SELECT * FROM `order_detail`;

DELIMITER //
CREATE TRIGGER trg_insert_member
AFTER INSERT ON member
FOR EACH ROW
BEGIN
  -- 将新插入的用户信息记录到日志表中
  INSERT INTO member_log (username, password, action, created_at)
  VALUES (NEW.username, NEW.password, 'INSERT', NOW());
END;
//
DELIMITER ;

-- 插入一个新用户
INSERT INTO member (username, password, email, phone, gender, birthdate)
VALUES ('testuser', 'testpassword', 'test@example.com', '1234567890', 'M', '1990-01-01');

INSERT INTO member (username, password, email, phone, gender, birthdate, level_id)
VALUES ('testuser', 'testpassword', 'test@example.com', '1234567890', 'M', '1990-01-01', 1)

-- 查询日志表，查看是否记录了新插入的用户信息
SELECT * FROM member_log;

DELIMITER //
CREATE TRIGGER check_price_change
BEFORE UPDATE ON product
FOR EACH ROW
BEGIN
  -- 计算原始价格的±10%范围
  DECLARE min_price DECIMAL(10,2);
  DECLARE max_price DECIMAL(10,2);
  SET min_price = OLD.price * 0.9;
  SET max_price = OLD.price * 1.1;

  -- 检查新价格是否在范围内
  IF NEW.price < min_price OR NEW.price > max_price THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '商品价格变动超过10%，不允许修改';
  END IF;
END;
//
DELIMITER ;

INSERT INTO product (name, price, stock) VALUES ('测试商品', 100.00, 10);
-- 接下来，我们来测试这个触发器。首先，插入一条商品记录

UPDATE product SET price = 110.00 WHERE id = 1;
-- 然后尝试修改商品价格，使其在允许的范围内

UPDATE product SET price = 130.00 WHERE id = 1;
-- 最后，尝试修改商品价格，使其超出允许的范围

-- 执行上述测试语句后，您将看到第二条更新语句会因为触发器的限制而失败，并显示错误信息“商品价格变动超过10%，不允许修改”。

存储过程

-- 完整的存储过程语句
DELIMITER //
CREATE PROCEDURE create_order_infos(IN p_member_id INT, IN p_order_no VARCHAR(255), IN p_total_amount DECIMAL(10,2), IN p_create_time DATETIME, IN p_product_id INT, IN p_quantity INT)
BEGIN
  -- 插入订单表
  INSERT INTO `order` (`member_id`, `order_no`, `total_amount`, `create_time`)
  VALUES (p_member_id, p_order_no, p_total_amount, p_create_time);

  -- 获取刚插入的订单ID
  SET @order_id = LAST_INSERT_ID();

  -- 插入订单详情表
  INSERT INTO `order_detail` (`order_id`, `product_id`, `quantity`, `price`)
  VALUES (@order_id, p_product_id, p_quantity, (SELECT price FROM product WHERE id = p_product_id));

  -- 修改商品库存
  UPDATE product
  SET stock = stock - p_quantity
  WHERE id = p_product_id;
END //
DELIMITER ;

-- 测试语句
CALL create_order_infos(1, 'O20220101006', 99.99, '2022-01-01 15:00:00', 1, 1);

验证测试是否成功
-- 查询订单表
SELECT * FROM `order` WHERE order_no = 'O20220101006';

-- 查询订单详情表
SELECT * FROM `order_detail` WHERE order_id = (SELECT id FROM `order` WHERE order_no = 'O20220101006');

-- 查询商品库存
SELECT * FROM product WHERE id = 1;