MySQL数据库基础练习系列9、在线投票系统

红目香薰

发布于 2024-06-07 15:59:35

680

发布于 2024-06-07 15:59:35

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

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

数据库环境

MySQL版本：5.7.31-log

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

字符集：utf8 排序规则：utf8_general_ci

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

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

项目名称与项目简介

在线投票系统是一个用于在线进行各种投票活动的平台。它支持用户注册登录、创建投票、参与投票、查看投票结果等功能。通过该系统，用户可以方便地发起各种主题的投票活动，并邀请其他用户参与，从而收集到大量的用户反馈和意见。

系统的主要功能包括：

用户管理：支持用户注册、登录、修改个人信息、密码重置等功能。
投票管理：允许用户创建新的投票，设置投票标题、描述、选项等，并控制投票的开始和结束时间。
投票参与：用户可以选择已发布的投票进行参与，选择自己的选项并提交投票。
结果查看：用户可以查看已结束投票的结果，包括每个选项的得票数、占比等。

数据库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('男', '女') NOT NULL COMMENT '性别',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
CREATE TABLE votes (
    vote_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '投票ID',
    title VARCHAR(100) NOT NULL COMMENT '投票标题',
    description TEXT COMMENT '投票描述',
    start_time DATETIME NOT NULL COMMENT '开始时间',
    end_time DATETIME NOT NULL COMMENT '结束时间',
    created_by INT NOT NULL COMMENT '创建者ID',
    FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='投票信息表';
CREATE TABLE vote_options (
    option_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '选项ID',
    vote_id INT NOT NULL COMMENT '所属投票ID',
    option_text VARCHAR(255) NOT NULL COMMENT '选项内容',
    FOREIGN KEY (vote_id) REFERENCES votes(vote_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='投票选项表';
CREATE TABLE vote_records (
    record_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '记录ID',
    user_id INT NOT NULL COMMENT '用户ID',
    vote_id INT NOT NULL COMMENT '投票ID',
    option_id INT NOT NULL COMMENT '选项ID',
    vote_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '投票时间',
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (vote_id) REFERENCES votes(vote_id) ON DELETE CASCADE,
    FOREIGN KEY (option_id) REFERENCES vote_options(option_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户投票记录表';
CREATE TABLE vote_results (
    result_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '结果ID',
    vote_id INT NOT NULL COMMENT '投票ID',
    option_id INT NOT NULL COMMENT '选项ID',
    vote_count INT NOT NULL COMMENT '得票数',
    FOREIGN KEY (vote_id) REFERENCES votes(vote_id) ON DELETE CASCADE,
    FOREIGN KEY (option_id) REFERENCES vote_options(option_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='投票结果统计表';

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

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

INSERT INTO users (username, password, email, gender) VALUES
('user1', 'password1', 'user1@example.com', '男'),
('user2', 'password2', 'user2@example.com', '女'),
('user3', 'password3', 'user3@example.com', '男'),
('user4', 'password4', 'user4@example.com', '女'),
('user5', 'password5', 'user5@example.com', '男');
INSERT INTO votes (title, description, start_time, end_time, created_by) VALUES
('客户满意度调查', '请对我们的服务进行满意度评价', '2023-01-01 00:00:00', '2023-01-31 23:59:59', 1); -- 假设由 user1 创建
INSERT INTO vote_options (vote_id, option_text) VALUES
(1, '非常满意'),
(1, '满意'),
(1, '一般'),
(1, '不满意'),
(1, '非常不满意');
INSERT INTO vote_records (user_id, vote_id, option_id, vote_time) VALUES
(1, 1, 1, '2023-01-05 10:00:00'), -- user1 选择“非常满意”
(2, 1, 3, '2023-01-05 11:00:00'), -- user2 选择“一般”
(3, 1, 2, '2023-01-05 12:00:00'), -- user3 选择“满意”
(4, 1, 4, '2023-01-05 13:00:00'), -- user4 选择“不满意”
(5, 1, 5, '2023-01-05 14:00:00'); -- user5 选择“非常不满意”
INSERT INTO vote_results (vote_id, option_id, vote_count) VALUES
(1, 1, 1), -- “非常满意”得票数
(1, 2, 1), -- “满意”得票数
(1, 3, 1), -- “一般”得票数
(1, 4, 1), -- “不满意”得票数
(1, 5, 1); -- “非常不满意”得票数

遵循的数据库三范式

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

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

定义：