MySQL数据库基础练习系列16、在线问卷调查系统

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

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

数据库环境

MySQL版本：5.7.31-log

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

字符集：utf8 排序规则：utf8_general_ci

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

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

项目名称与项目简介

在线问卷调查系统是一个允许用户创建、发布、管理和分析问卷调查的在线平台。系统主要面向市场调研、学术研究、产品反馈等场景，为用户提供一套完整的问卷制作、发布、收集及数据分析的解决方案。

主要功能包括：

1. 用户管理：注册、登录、个人信息管理。
2. 问卷管理：问卷创建、编辑、发布、删除。
3. 题目管理：支持单选、多选、文本输入等多种题型，题目添加、编辑、删除。
4. 问卷发布：设置问卷发布时间、目标受众等。
5. 数据收集：用户填写问卷，系统自动收集数据。
6. 数据分析：提供问卷结果统计、图表展示等功能。

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

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

代码语言：javascript复制

-- 创建用户表
CREATE TABLE users (
    -- 用户ID，自增主键
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 用户名，唯一且不能为空
    username VARCHAR(50) NOT NULL UNIQUE,
    -- 用户密码，不能为空（建议存储加密后的密码）
    password VARCHAR(255) NOT NULL,
    -- 用户邮箱，唯一且可以为空（根据实际情况可设为NOT NULL）
    email VARCHAR(100) UNIQUE,
    -- 用户性别，通过char(2)类型存储，如'男'或'女'，默认值为'男'
    gender_id char(2) DEFAULT '男'
);


-- 创建调查表
CREATE TABLE surveys (
    -- 调查ID，自增主键
    survey_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 调查标题，不能为空
    title VARCHAR(255) NOT NULL,
    -- 调查描述，可以为空
    description TEXT,
    -- 创建者ID，外键关联users表的user_id
    creator_id INT,
    -- 创建时间，默认为当前时间戳
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 设置外键约束，确保引用的数据存在
    FOREIGN KEY (creator_id) REFERENCES users(user_id)
);


-- 创建问题表
CREATE TABLE questions (
    -- 问题ID，自增主键
    question_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 所属调查ID，外键关联surveys表的survey_id
    survey_id INT,
    -- 问题文本，不能为空
    question_text VARCHAR(255) NOT NULL,
    -- 问题类型，如单选、多选、文本等，不能为空
    question_type VARCHAR(50) NOT NULL,
    -- 设置外键约束，确保引用的数据存在
    FOREIGN KEY (survey_id) REFERENCES surveys(survey_id)
);


-- 创建选项表
CREATE TABLE options (
    -- 选项ID，自增主键
    option_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 所属问题ID，外键关联questions表的question_id
    question_id INT,
    -- 选项文本，不能为空
    option_text VARCHAR(255) NOT NULL,
    -- 设置外键约束，确保引用的数据存在
    FOREIGN KEY (question_id) REFERENCES questions(question_id)
);


-- 创建回答表
CREATE TABLE responses (
    -- 回答ID，自增主键
    response_id INT AUTO_INCREMENT PRIMARY KEY,
    -- 用户ID，外键关联users表的user_id
    user_id INT,
    -- 所属调查ID，外键关联surveys表的survey_id
    survey_id INT,
    -- 所属问题ID，外键关联questions表的question_id
    question_id INT,
    -- 回答文本，用于存储文本输入类型的答案，可以为空
    response_text TEXT,
    -- 选项ID，用于存储选择类型的答案，可以为空（对于文本类型的答案则不使用此字段）
    option_id INT,
    -- 回答时间，默认为当前时间戳
    response_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 设置外键约束，确保引用的数据存在
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (survey_id) REFERENCES surveys(survey_id),
    FOREIGN KEY (question_id) REFERENCES questions(question_id),
    -- 注意：此处的option_id外键设置可能需要根据业务逻辑进行调整，因为不是所有问题类型都有选项
    -- 如果只有选择题才有选项，则可以考虑去掉这个外键约束，或者采用其他方式处理
    FOREIGN KEY (option_id) REFERENCES options(option_id)
);

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

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

代码语言：javascript复制

INSERT INTO users (username, password, email, gender_id) VALUES
('张三', 'password1', 'user1@example.com', '男'),
('李四', 'password2', 'user2@example.com', '女'),
('赵六', 'password3', 'user3@example.com', '男'),
('阮小七', 'password4', 'user4@example.com', '女'),
('王五', 'password5', 'user5@example.com', '男');

INSERT INTO surveys (title, description, creator_id) VALUES
('用户满意度调查', '关于产品使用满意度的调查', (SELECT user_id FROM users WHERE username = '赵六')),
('新功能反馈', '收集用户对新功能的意见和建议', (SELECT user_id FROM users WHERE username = '王五'));

INSERT INTO questions (survey_id, question_text, question_type) VALUES
((SELECT survey_id FROM surveys WHERE title = '用户满意度调查'), '您对产品整体满意吗？', '单选'),
((SELECT survey_id FROM surveys WHERE title = '用户满意度调查'), '您最喜欢产品的哪个功能？', '多选'),
((SELECT survey_id FROM surveys WHERE title = '新功能反馈'), '您认为新功能有哪些可以改进的地方？', '文本');

INSERT INTO options (question_id, option_text) VALUES
((SELECT question_id FROM questions WHERE question_text = '您对产品整体满意吗？' AND survey_id = (SELECT survey_id FROM surveys WHERE title = '用户满意度调查')), '非常满意'),
((SELECT question_id FROM questions WHERE question_text = '您对产品整体满意吗？' AND survey_id = (SELECT survey_id FROM surveys WHERE title = '用户满意度调查')), '满意'),
((SELECT question_id FROM questions WHERE question_text = '您最喜欢产品的哪个功能？' AND survey_id = (SELECT survey_id FROM surveys WHERE title = '用户满意度调查')), '功能A'),
((SELECT question_id FROM questions WHERE question_text = '您最喜欢产品的哪个功能？' AND survey_id = (SELECT survey_id FROM surveys WHERE title = '用户满意度调查')), '功能B'),
((SELECT question_id FROM questions WHERE question_text = '您最喜欢产品的哪个功能？' AND survey_id = (SELECT survey_id FROM surveys WHERE title = '用户满意度调查')), '功能C');

INSERT INTO responses (user_id, survey_id, question_id, response_text, option_id) VALUES
((SELECT user_id FROM users WHERE username = 'user1'), (SELECT survey_id FROM surveys WHERE title = '用户满意度调查'), (SELECT question_id FROM questions WHERE question_text = '您对产品整体满意吗？' AND survey_id = (SELECT survey_id FROM surveys WHERE title = '用户满意度调查')), NULL, (SELECT option_id FROM options WHERE option_text = '非常满意')),
((SELECT user_id FROM users WHERE username = 'user1'), (SELECT survey_id FROM surveys WHERE title = '用户满意度调查'), (SELECT question_id FROM questions WHERE question_text = '您最喜欢产品的哪个功能？' AND survey_id = (SELECT survey_id FROM surveys WHERE title = '用户满意度调查')), '功能A, 功能B', NULL);

遵循的数据库三范式

数据库建表的三范式（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 人点赞