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

2024-06-07 15:56:58 浏览数 (1)

MySQL数据库基础练习系列目标

很多学生或者说是初学者在学习完成数据库的基础增删改查后就自认为在数据库这里就很熟悉了，但是不接触项目根本部知道需求，我这里准备了50个项目的基本需求来让大家来熟练各类项目的列信息，让大家更好的深入项目进行实战式的练习，可以让大家在后面面试的时候有更多更丰富的资历让大家可以与面试官侃侃而谈。

数据库环境

MySQL版本：5.7.31-log

数据库字符集，所有数据库通用字符集与排序规则，支持中文数据。

字符集：utf8 排序规则：utf8_general_ci

使用工具：Navicat Premium 15，可以在下面的连接中下载

https://download.csdn.net/download/feng8403000/89403778

项目名称与项目简介

会员管理系统是一个旨在有效管理和跟踪会员信息、会员消费记录、会员积分以及会员等级等信息的软件系统。通过该系统，管理员可以方便地添加、查询、修改和删除会员信息，并且能够实时追踪会员的消费行为，进行统计分析，为企业的市场营销策略提供数据支持。此外，系统还可以根据会员的消费金额和积分情况自动调整会员等级，为不同等级的会员提供不同的优惠和服务。

数据库DDL（注意创建顺序）

为了直接运行DDL语句并创建表，我们需要确保在创建含有外键约束的表之前，相关的被引用表（即外键指向的表）已经存在。所以我们在创建表的时候一定要按照一定的顺序来创建，否则就会出现没有外键关系导致的创建异常。

代码语言：javascript复制
CREATE TABLE Members (
    member_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '会员ID',
    member_name VARCHAR(50) NOT NULL COMMENT '会员姓名',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    phone_number VARCHAR(15) UNIQUE NOT NULL COMMENT '手机号',
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
    address VARCHAR(255) COMMENT '地址',
    registration_date DATE NOT NULL COMMENT '注册日期',
    last_login_date DATETIME COMMENT '最后登录日期'
) COMMENT '会员信息表';

CREATE TABLE MemberLevels (
    level_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '等级ID',
    level_name VARCHAR(50) NOT NULL COMMENT '等级名称',
    min_points INT NOT NULL COMMENT '最小积分要求',
    discount DECIMAL(4,2) NOT NULL COMMENT '折扣率'
) COMMENT '会员等级表';

CREATE TABLE MemberPoints (
    point_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '积分记录ID',
    member_id INT NOT NULL COMMENT '会员ID',
    points_earned INT NOT NULL COMMENT '获得的积分',
    points_used INT COMMENT '使用的积分',
    transaction_date DATE NOT NULL COMMENT '交易日期',
    description TEXT COMMENT '交易描述',
    FOREIGN KEY (member_id) REFERENCES Members(member_id) ON DELETE CASCADE
) COMMENT '会员积分记录表';


CREATE TABLE MemberPurchases (
    purchase_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '消费记录ID',
    member_id INT NOT NULL COMMENT '会员ID',
    product_name VARCHAR(100) NOT NULL COMMENT '产品名称',
    quantity INT NOT NULL COMMENT '购买数量',
    price_per_unit DECIMAL(10,2) NOT NULL COMMENT '单价',
    total_price DECIMAL(10,2) NOT NULL COMMENT '总价',
    purchase_date DATE NOT NULL COMMENT '购买日期',
    FOREIGN KEY (member_id) REFERENCES Members(member_id) ON DELETE CASCADE
) COMMENT '会员消费记录表';


CREATE TABLE Coupons (
    coupon_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '优惠券ID',
    code VARCHAR(50) NOT NULL UNIQUE COMMENT '优惠券码',
    discount_amount DECIMAL(10,2) NOT NULL COMMENT '折扣金额',
    expiration_date DATE COMMENT '有效期至',
    used_by_member_id INT COMMENT '使用此优惠券的会员ID',
    used_on DATE COMMENT '使用日期',
    FOREIGN KEY (used_by_member_id) REFERENCES Members(member_id) ON DELETE SET NULL
) COMMENT '优惠券表';

插入数据DML（注意插入数据顺序）

插入数据的时候也要注意主外键关系，如果没有外检的情况下是没有办法插入从表数据的。

代码语言：javascript复制
INSERT INTO Members (member_name, gender, phone_number, email, address, registration_date, last_login_date) VALUES
('张三', '男', '13800001111', 'zhangsan@example.com', '北京市朝阳区', '2023-01-01', '2023-01-10 10:00:00'),
('李四', '女', '13900002222', 'lisi@example.com', '上海市浦东新区', '2023-02-01', '2023-02-15 15:00:00'),
('王五', '男', '13700003333', 'wangwu@example.com', '广州市天河区', '2023-03-01', '2023-03-20 20:00:00'),
('赵六', '女', '13600004444', 'zhaoliu@example.com', '深圳市南山区', '2023-04-01', '2023-04-10 10:30:00'),
('孙七', '男', '13500005555', 'sunqi@example.com', '成都市武侯区', '2023-05-01', '2023-05-20 15:45:00');

INSERT INTO MemberLevels (level_name, min_points, discount) VALUES
('青铜', 0, 0.95),
('白银', 1000, 0.90),
('黄金', 5000, 0.85),
('钻石', 10000, 0.80),
('黑金', 20000, 0.75);
INSERT INTO MemberPoints (member_id, points_earned, points_used, transaction_date, description) VALUES
(1, 500, 0, '2023-01-15', '首次注册奖励'),
(2, 1000, 200, '2023-02-20', '购买商品获得积分'),
(1, 300, 0, '2023-03-05', '参与活动获得积分'),
(3, 800, 0, '2023-03-15', '生日奖励'),
(4, 2000, 500, '2023-04-20', '使用积分抵扣部分金额');
INSERT INTO MemberPurchases (member_id, product_name, quantity, price_per_unit, total_price, purchase_date) VALUES
(1, '产品A', 2, 100.00, 200.00, '2023-01-20'),
(2, '产品B', 1, 250.00, 250.00, '2023-02-10'),
(3, '产品C', 3, 50.00, 150.00, '2023-03-10'),
(4, '产品D', 5, 80.00, 400.00, '2023-04-05'),
(5, '产品E', 2, 120.00, 240.00, '2023-05-15');
INSERT INTO Coupons (code, discount_amount, expiration_date) VALUES
('COUPON1', 10.00, '2023-06-30'),
('COUPON2', 20.00, '2023-07-31'),
('COUPON3', 50.00, '2023-08-31'),
('COUPON4', 15.00, '2023-09-30'),
('COUPON5', 30.00, '2023-10-31');

遵循的数据库三范式

数据库建表的三范式（3NF，Third Normal Form）是关系型数据库设计的基本原则，用于确保数据库结构的逻辑性和减少数据冗余。这三个范式是逐步细化的，每一个范式都是在前一个范式的基础上建立的。下面我将详细解释这三个范式：

第一范式（1NF, First Normal Form）

定义：

  1. 列不可分割，即数据库表的每一列都是不可分割的原子数据项。
  2. 每一列都是不可再分的最小数据单元（也称为最小的原子单元）。

解释：

  • 在第一范式中，主要关注的是列的原子性。也就是说，表中的每一列都应该只包含一个值，而不能包含集合、数组或其他复合数据类型。
  • 例如，如果有一个“地址”列，它包含了街道、城市、省份和国家等信息，那么这就违反了第一范式。应该将这个“地址”列拆分成多个独立的列，如“街道”、“城市”、“省份”和“国家”。

第二范式（2NF, Second Normal Form）

定义：

  1. 满足1NF。
  2. 非主键列必须完全依赖于主键，而不能只依赖于主键的一部分（针对复合主键而言）。

解释：

  • 第二范式建立在第一范式的基础上，主要关注于主键与非主键列之间的依赖关系。
  • 在第二范式中，一个表只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
  • 如果表中的某一列只与复合主键的一部分有关，那么它就不应该存在于这个表中，而应该被分离出去形成另外一张新表。

第三范式（3NF, Third Normal Form）

定义：

  1. 满足2NF。
  2. 非主键列必须直接依赖于主键，不能存在传递依赖。即非主键列必须直接依赖于整个主键，而不能依赖于主键的一部分。

解释：

  • 第三范式是在第二范式的基础上进一步细化的。它主要关注于消除传递依赖，即非主键列不应该依赖于主键的某一部分，而应该直接依赖于整个主键。
  • 如果存在传递依赖，那么应该考虑将这个非主键列分离出去，形成新的表，并通过主键或外键与原表进行关联。
产品 基础 数据 数据库 mysql

0 人点赞