MySQL数据库基础练习系列13、用户注册与登录系统

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: MySQL数据库基础练习系列13、用户注册与登录系统

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

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

数据库环境

MySQL版本:5.7.31-log

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

字符集:utf8

排序规则:utf8_general_ci

使用工具:Navicat Premium 15

项目名称与项目简介

用户注册与登录系统是一个基本的用户管理系统,它允许用户进行注册操作以创建新账户,并提供登录功能以验证用户身份并允许访问受保护的资源。该系统应保证用户信息的安全性,包括密码的加密存储和登录尝试的限制等。

主要功能包括:

  1. 用户注册:用户填写必要的信息(如用户名、密码、邮箱、性别等)进行注册。
  2. 用户登录:已注册用户输入用户名和密码进行登录验证。
  3. 找回密码:用户可以通过邮箱找回或重置忘记的密码。
  4. 用户信息管理:用户可以查看和修改自己的注册信息。
  5. 安全性控制:包括密码加密存储、登录尝试次数限制等。

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

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

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    email VARCHAR(100) UNIQUE NOT NULL COMMENT '电子邮箱',
    gender ENUM('男', '女') COMMENT '性别',
    registration_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册日期',
    last_login_date DATETIME COMMENT '最后登录日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
CREATE TABLE user_roles (
    role_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '角色ID',
    role_name VARCHAR(50) NOT NULL UNIQUE COMMENT '角色名称',
    description TEXT COMMENT '角色描述'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色表';
CREATE TABLE user_role_mapping (
    mapping_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '关联ID',
    user_id INT NOT NULL COMMENT '用户ID',
    role_id INT NOT NULL COMMENT '角色ID',
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES user_roles(role_id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_role (user_id, role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';
CREATE TABLE login_attempts (
    attempt_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '尝试ID',
    user_id INT NOT NULL COMMENT '用户ID',
    attempt_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '尝试时间',
    ip_address VARCHAR(45) COMMENT 'IP地址',
    success BOOLEAN COMMENT '是否成功',
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='登录尝试记录表';
CREATE TABLE password_reset_requests (
    request_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '请求ID',
    user_id INT NOT NULL COMMENT '用户ID',
    request_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '请求时间',
    token VARCHAR(255) NOT NULL COMMENT '重置令牌',
    is_used BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否已使用'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='密码重置请求表';

插入数据DML(注意插入数据顺序)

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

INSERT INTO user_roles (role_name, description) VALUES
('管理员', '拥有最高权限,可以管理所有用户和设置'),
('编辑', '可以发布和编辑内容,但无法管理用户'),
('用户', '只能浏览和评论内容');
INSERT INTO users (username, password, email, gender) VALUES
('admin', 'admin_password', 'admin@example.com', '男'),
('editor', 'editor_password', 'editor@example.com', '女'),
('user1', 'user1_password', 'user1@example.com', '男'),
('user2', 'user2_password', 'user2@example.com', '女'),
('user3', 'user3_password', 'user3@example.com', '男');
 
-- 假设用户ID为1的是管理员,ID为2的是编辑,其他的是用户
INSERT INTO user_role_mapping (user_id, role_id) VALUES
(1, 1), -- admin用户是管理员
(2, 2), -- editor用户是编辑
(3, 3), -- user1用户是普通用户
(4, 3), -- user2用户是普通用户
(5, 3); -- user3用户是普通用户
INSERT INTO login_attempts (user_id, ip_address, success) VALUES
(1, '192.168.1.1', TRUE), -- admin用户登录成功
(3, '127.0.0.1', FALSE), -- user1用户登录失败
(2, '10.0.0.1', TRUE), -- editor用户登录成功
(4, '172.16.0.1', FALSE), -- user2用户登录失败
(5, '192.168.1.2', TRUE); -- user3用户登录成功
INSERT INTO password_reset_requests (user_id, token, is_used) VALUES
(3, 'token1234', FALSE), -- user1用户请求了密码重置
(4, 'another_token', TRUE); -- user2用户请求了密码重置并已使用

遵循的数据库三范式

数据库建表的三范式(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
相关文章
|
11天前
|
存储 SQL 关系型数据库
现代数据库管理系统的发展与挑战
数据库管理系统(DBMS)作为现代软件开发的核心组成部分,其在数据存储、管理和访问方面的演进至关重要。本文探讨了现代DBMS的发展历程、关键技术特点以及面临的挑战,包括大数据处理、云计算环境下的优化和安全性等方面。 【7月更文挑战第7天】
|
9天前
|
存储 自然语言处理 DataWorks
通过云上数据库三步构建RAG系统
本文介绍了如何使用OpenSearch LLM智能问答版通过云上数据库一站式构建RAG系统。
18494 9
|
13天前
|
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包给服务器,确认收到了服务器的确
130 1
|
20天前
|
SQL 弹性计算 数据管理
数据管理DMS产品使用合集之sql server实例,已经创建了数据库,登录时提示实例已存在,该怎么处理
阿里云数据管理DMS提供了全面的数据管理、数据库运维、数据安全、数据迁移与同步等功能,助力企业高效、安全地进行数据库管理和运维工作。以下是DMS产品使用合集的详细介绍。
31 1
|
13天前
|
数据库
超算系统构思01----数据库必须有一个公式系统,右侧选择你想要的公式,要明白的地方,公式在计算机上的表达,数据库必须记住的东西是公式,先做公式,必须得做
超算系统构思01----数据库必须有一个公式系统,右侧选择你想要的公式,要明白的地方,公式在计算机上的表达,数据库必须记住的东西是公式,先做公式,必须得做
|
13天前
|
存储 安全 算法
数据库系统工程师笔记(一)计算机系统
数据库系统工程师笔记(一)计算机系统
36 0
|
15天前
|
存储 JavaScript 前端开发
文本,三步走构思,富文本点击提交能够存储到数据库当中(下),最快的方法,还是会看资料,因此会整合资料最好,直接看资料最快,因为是JWT的资料,我们要设置好登录的内容,看登录的地方怎样写的
文本,三步走构思,富文本点击提交能够存储到数据库当中(下),最快的方法,还是会看资料,因此会整合资料最好,直接看资料最快,因为是JWT的资料,我们要设置好登录的内容,看登录的地方怎样写的
|
20天前
|
移动开发 小程序 关系型数据库
java+ IDEA+ Uniapp+ mysql医院3D智能导诊系统源码
这是一个基于Java、IDEA、Uniapp和MySQL的医院3D智能导诊系统,采用Springboot后端框架和Redis、Mybatis Plus、RocketMQ等技术。系统通过对话式交互,精准推荐就诊科室,解决患者挂号困扰。它还具备智能预问诊功能,提升诊疗效率和准确性,确保医生能快速了解患者详情。此系统还支持小程序和H5,方便患者使用。
22 0
|
12天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
10天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
979 6
Mysql 数据库主从复制