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

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL数据库基础练习系列5、会员管理系统

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

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

数据库环境

MySQL版本:5.7.31-log

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

字符集:utf8

排序规则:utf8_general_ci

使用工具:Navicat Premium 15

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

数据库DDL(注意创建顺序)

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

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(注意插入数据顺序)

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

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. 非主键列必须直接依赖于主键,不能存在传递依赖。即非主键列必须直接依赖于整个主键,而不能依赖于主键的一部分。

解释

  • 第三范式是在第二范式的基础上进一步细化的。它主要关注于消除传递依赖,即非主键列不应该依赖于主键的某一部分,而应该直接依赖于整个主键。
  • 如果存在传递依赖,那么应该考虑将这个非主键列分离出去,形成新的表,并通过主键或外键与原表进行关联。
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
12天前
|
存储 SQL 关系型数据库
现代数据库管理系统的发展与挑战
数据库管理系统(DBMS)作为现代软件开发的核心组成部分,其在数据存储、管理和访问方面的演进至关重要。本文探讨了现代DBMS的发展历程、关键技术特点以及面临的挑战,包括大数据处理、云计算环境下的优化和安全性等方面。 【7月更文挑战第7天】
|
21天前
|
存储 安全 Java
基于Java+MySQL停车场车位管理系统详细设计和实现(源码+LW+调试文档+讲解等)
基于Java+MySQL停车场车位管理系统详细设计和实现(源码+LW+调试文档+讲解等)
|
9天前
|
存储 自然语言处理 DataWorks
通过云上数据库三步构建RAG系统
本文介绍了如何使用OpenSearch LLM智能问答版通过云上数据库一站式构建RAG系统。
18515 9
|
8天前
|
Java 关系型数据库 MySQL
班级通讯录管理系统(Java+MySQL)
构建了一个Java Swing应用,搭配MySQL,实现班级通讯录管理。系统具备管理员登录、班级与学生信息的增删改查功能,每个班级窗口独立且自适应布局。利用GBK编码处理中文,JDBC连接数据库,优化窗口复用和代码结构,数据变更实时同步。示例截图展示详细界面。
班级通讯录管理系统(Java+MySQL)
|
14天前
|
SQL 自然语言处理 网络协议
【Linux开发实战指南】基于TCP、进程数据结构与SQL数据库:构建在线云词典系统(含注册、登录、查询、历史记录管理功能及源码分享)
TCP(Transmission Control Protocol)连接是互联网上最常用的一种面向连接、可靠的、基于字节流的传输层通信协议。建立TCP连接需要经过著名的“三次握手”过程: 1. SYN(同步序列编号):客户端发送一个SYN包给服务器,并进入SYN_SEND状态,等待服务器确认。 2. SYN-ACK:服务器收到SYN包后,回应一个SYN-ACK(SYN+ACKnowledgment)包,告诉客户端其接收到了请求,并同意建立连接,此时服务器进入SYN_RECV状态。 3. ACK(确认字符):客户端收到服务器的SYN-ACK包后,发送一个ACK包给服务器,确认收到了服务器的确
131 1
|
1月前
|
SQL 缓存 监控
✅系统日活递增,如何优化提升大规模数据库
数据库性能优化涵盖硬件升级(如SSD、内存)、数据库设计简化、SQL查询优化、索引管理、缓存利用(如Redis)、负载均衡(读写分离、集群)、分区分片、备份恢复策略及性能监控。综合调整这些方面可提升系统性能和可用性。[MySQL索引设计][1]和[SQL优化实践][2]是深入学习的好资源。
|
27天前
|
存储 搜索推荐 数据库
软件系统【标签tag功能】的两种数据库设计
软件系统中的标签功能可采用两种数据库设计。方案一,文章和Tag各一表,Tag信息存储在文章表内(`tags`和`tagids`字段),优点是模型简单,但查询效率低且易引发数据冗余和一致性问题。方案二,增加Tagmap表,用于存储标签-文章映射,利于索引查询和数据更新,适用于高效率需求,但结构更复杂。
48 0
软件系统【标签tag功能】的两种数据库设计
|
28天前
|
存储 关系型数据库 MySQL
系统数据库
【6月更文挑战第20天】系统数据库。
12 1
|
1月前
|
关系型数据库 MySQL 数据挖掘
MySQL窗口函数详解(概念+练习+实战)
MySQL窗口函数详解(概念+练习+实战)
101 1
|
1月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)