很多学生或者说是初学者在学习完成数据库的基础增删改查后就自认为在数据库这里就很熟悉了,但是不接触项目根本部知道需求,我这里准备了50个项目的基本需求来让大家来熟练各类项目的列信息,让大家更好的深入项目进行实战式的练习,可以让大家在后面面试的时候有更多更丰富的资历让大家可以与面试官侃侃而谈。
MySQL版本:5.7.31-log
数据库字符集,所有数据库通用字符集与排序规则,支持中文数据。
字符集:utf8 排序规则:utf8_general_ci
使用工具:Navicat Premium 15,可以在下面的连接中下载
https://download.csdn.net/download/feng8403000/89403778
用户注册与登录系统是一个基本的用户管理系统,它允许用户进行注册操作以创建新账户,并提供登录功能以验证用户身份并允许访问受保护的资源。该系统应保证用户信息的安全性,包括密码的加密存储和登录尝试的限制等。
主要功能包括:
为了直接运行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='密码重置请求表';
插入数据的时候也要注意主外键关系,如果没有外检的情况下是没有办法插入从表数据的。
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)
定义:
解释:
第二范式(2NF, Second Normal Form)
定义:
解释:
第三范式(3NF, Third Normal Form)
定义:
解释: