MySQL数据库基础练习系列37、问卷调查统计分析系统

-- 用户表
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 '密码',
    name VARCHAR(100) NOT NULL COMMENT '姓名',
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
    gender ENUM('男', '女') NOT NULL COMMENT '性别'
);


-- 问卷表
CREATE TABLE Surveys (
    survey_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '问卷ID',
    title VARCHAR(255) NOT NULL COMMENT '问卷标题',
    description TEXT COMMENT '问卷描述',
    creator_id INT NOT NULL COMMENT '创建者ID',
    creation_date DATE NOT NULL COMMENT '创建日期',
    status ENUM('草稿', '发布', '归档') NOT NULL COMMENT '问卷状态',
    FOREIGN KEY (creator_id) REFERENCES Users(user_id)
);


-- 问题表
CREATE TABLE Questions (
    question_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '问题ID',
    survey_id INT NOT NULL COMMENT '所属问卷ID',
    question_text TEXT NOT NULL COMMENT '问题文本',
    question_type ENUM('文本', '单选', '多选', '量表') NOT NULL COMMENT '问题类型',
    order_number INT NOT NULL COMMENT '问题排序号',
    FOREIGN KEY (survey_id) REFERENCES Surveys(survey_id)
);


-- 选项表（针对单选、多选问题）
CREATE TABLE Choices (
    choice_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '选项ID',
    question_id INT NOT NULL COMMENT '所属问题ID',
    choice_text VARCHAR(255) NOT NULL COMMENT '选项文本',
    order_number INT NOT NULL COMMENT '选项排序号',
    FOREIGN KEY (question_id) REFERENCES Questions(question_id)
);


-- 回答表
CREATE TABLE Answers (
    answer_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '回答ID',
    user_id INT NOT NULL COMMENT '用户ID',
    survey_id INT NOT NULL COMMENT '问卷ID',
    question_id INT NOT NULL COMMENT '问题ID',
    answer_text TEXT COMMENT '文本类回答内容', -- 用于文本类回答
    choice_ids VARCHAR(255) COMMENT '选项ID列表，逗号分隔', -- 用于单选或多选类回答，存储选择的选项ID
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (survey_id) REFERENCES Surveys(survey_id),
    FOREIGN KEY (question_id) REFERENCES Questions(question_id)
);


-- 问卷参与记录表（可选，用于记录用户的问卷参与情况）
CREATE TABLE SurveyParticipations (
    participation_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '参与记录ID',
    user_id INT NOT NULL COMMENT '用户ID',
    survey_id INT NOT NULL COMMENT '问卷ID',
    start_time DATETIME COMMENT '开始时间',
    finish_time DATETIME COMMENT '结束时间',
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (survey_id) REFERENCES Surveys(survey_id)
);

INSERT INTO Users (username, password, name, email, gender) VALUES
('诸葛亮', '123', '诸葛亮', 'zhugeliang@example.com', '男'),
('孙悟空', '123', '孙悟空', 'sunwukong@example.com', '男'),
('林黛玉', '123', '林黛玉', 'lindaiyu@example.com', '女');

-- 假设用户ID 1 是 '诸葛亮', 用户ID 2 是 '孙悟空', 用户ID 3 是 '林黛玉'
INSERT INTO Surveys (title, description, creator_id, creation_date, status) VALUES
('用户满意度调查', '关于我们的产品用户满意度的调查', 1, '2023-01-01', '发布'),
('员工工作体验问卷', '收集员工对公司的工作体验反馈', 2, '2023-02-01', '草稿'),
('客户服务质量调查', '评估我们的客户服务质量和效果', 3, '2023-03-01', '归档');

-- 假设问卷ID 1 是 '用户满意度调查', 问卷ID 2 是 '员工工作体验问卷', 问卷ID 3 是 '客户服务质量调查'
INSERT INTO Questions (survey_id, question_text, question_type, order_number) VALUES
(1, '您对我们的产品整体满意度如何？', '量表', 1),
(1, '您认为我们产品的哪些方面需要改进？', '文本', 2),
(2, '您对公司的福利待遇满意吗？', '单选', 1),
(2, '您认为公司的工作氛围如何？', '文本', 2),
(3, '您对我们客服的响应速度满意吗？', '单选', 1),
(3, '您希望我们的客服在哪些方面提升？', '文本', 2);

-- 假设问题ID 2 是 '您对公司的福利待遇满意吗？', 问题ID 6 是 '您对我们客服的响应速度满意吗？'
INSERT INTO Choices (question_id, choice_text, order_number) VALUES
(2, '非常满意', 1),
(2, '一般', 2),
(2, '不满意', 3),
(6, '非常满意', 1),
(6, '比较满意', 2),
(6, '一般', 3),
(6, '不满意', 4);

-- 假设用户ID 1 是 '诸葛亮', 用户ID 2 是 '孙悟空', 问卷ID 1 是 '用户满意度调查'
INSERT INTO Answers (user_id, survey_id, question_id, answer_text, choice_ids) VALUES
(1, 1, 1, '5分（满分）', NULL), -- 量表问题，假设5分是满分
(1, 1, 2, '希望产品界面更加友好', NULL), -- 文本问题
(2, 1, 1, '3分', NULL),
(2, 1, 2, '功能需要更完善', NULL),
(1, 2, 2, '工作氛围很融洽', NULL), -- 假设这个问题没有选项
(2, 2, 2, '有一些问题需要改进', NULL),
(1, 3, 1, '2', NULL), -- 假设选项ID从1开始
(1, 3, 2, '希望客服更加专业', NULL),
(2, 3, 1, '3', NULL);
INSERT INTO SurveyParticipations (user_id, survey_id, start_time, finish_time) VALUES
(1, 1, '2023-01-02 10:00:00', '2023-01-02 10:30:00'),
(2, 1, '2023-01-02 11:00:00', '2023-01-02 11:15:00');