mysql中如何开发简单问卷调查系统_mysql问卷系统项目实战

CREATE TABLE surveys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
<p>CREATE TABLE questions (
id INT AUTO_INCREMENT PRIMARY KEY,
survey_id INT,
content TEXT NOT NULL,
type ENUM('radio', 'checkbox', 'text') DEFAULT 'radio',
FOREIGN KEY (survey_id) REFERENCES surveys(id) ON DELETE CASCADE
);</p><p>CREATE TABLE options (
id INT AUTO_INCREMENT PRIMARY KEY,
question_id INT,
option_text VARCHAR(255),
sort_order INT DEFAULT 0,
FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
);</p><p>CREATE TABLE responses (
id INT AUTO_INCREMENT PRIMARY KEY,
survey_id INT,
submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (survey_id) REFERENCES surveys(id)
);</p><p>CREATE TABLE answers (
id INT AUTO_INCREMENT PRIMARY KEY,
response_id INT,
question_id INT,
option_id INT DEFAULT NULL,
text_answer TEXT,
FOREIGN KEY (response_id) REFERENCES responses(id) ON DELETE CASCADE,
FOREIGN KEY (question_id) REFERENCES questions(id),
FOREIGN KEY (option_id) REFERENCES options(id)
);</p>

-- 添加问卷
INSERT INTO surveys (title, description) VALUES ('用户满意度调查', '了解用户体验');
<p>-- 添加题目
INSERT INTO questions (survey_id, content, type) 
VALUES (1, '您对服务满意吗？', 'radio');</p><p>-- 添加选项
INSERT INTO options (question_id, option_text, sort_order) 
VALUES (1, '非常满意', 1), (1, '一般', 2), (1, '不满意', 3);</p>

-- 记录一次提交
INSERT INTO responses (survey_id) VALUES (1);
SET @response_id = LAST_INSERT_ID();
<p>-- 回答选择题
INSERT INTO answers (response_id, question_id, option_id) 
VALUES (@response_id, 1, 1); -- 选了“非常满意”</p><p>-- 回答文本题（假设还有个文本题 question_id=2）
INSERT INTO answers (response_id, question_id, text_answer) 
VALUES (@response_id, 2, '希望响应更快一些');</p>

SELECT o.option_text, COUNT(a.option_id) as count
FROM options o
LEFT JOIN answers a ON o.id = a.option_id
WHERE o.question_id = 1
GROUP BY o.id, o.option_text;