73 lines
2.9 KiB
SQL
73 lines
2.9 KiB
SQL
-- 创建数据库
|
||
CREATE DATABASE IF NOT EXISTS llm_survey DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
||
USE llm_survey;
|
||
|
||
-- 创建用户表
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
name VARCHAR(50) NOT NULL COMMENT '姓名',
|
||
phone VARCHAR(20) NOT NULL COMMENT '手机号码',
|
||
work_area VARCHAR(20) NOT NULL COMMENT '工作领域',
|
||
position_type VARCHAR(20) NOT NULL COMMENT '岗位性质',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_phone (phone)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
|
||
|
||
-- 创建问题表
|
||
CREATE TABLE IF NOT EXISTS questions (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
question_number INT NOT NULL COMMENT '问题序号',
|
||
content TEXT NOT NULL COMMENT '问题内容',
|
||
question_type VARCHAR(20) NOT NULL COMMENT '问题类型',
|
||
work_area VARCHAR(20) COMMENT '针对的工作领域（NULL表示通用问题）',
|
||
is_required BOOLEAN DEFAULT TRUE COMMENT '是否必答',
|
||
next_question_logic JSON COMMENT '跳转逻辑',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY uk_question_number (question_number)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='问题表';
|
||
|
||
-- 创建选项表
|
||
CREATE TABLE IF NOT EXISTS options (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
question_id BIGINT NOT NULL COMMENT '关联的问题ID',
|
||
option_code VARCHAR(10) NOT NULL COMMENT '选项代码（如A、B、C）',
|
||
content TEXT NOT NULL COMMENT '选项内容',
|
||
requires_text BOOLEAN DEFAULT FALSE COMMENT '是否需要填写文本',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (question_id) REFERENCES questions(id),
|
||
UNIQUE KEY uk_question_option (question_id, option_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='问题选项表';
|
||
|
||
-- 创建答案表
|
||
CREATE TABLE IF NOT EXISTS survey_responses (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
user_id BIGINT NOT NULL COMMENT '用户ID',
|
||
question_id BIGINT NOT NULL COMMENT '问题ID',
|
||
selected_options JSON COMMENT '选中的选项代码列表',
|
||
text_answer TEXT COMMENT '文本答案',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES users(id),
|
||
FOREIGN KEY (question_id) REFERENCES questions(id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='问卷答案表';
|
||
|
||
-- 插入基础问题数据
|
||
INSERT INTO questions (question_number, content, question_type, is_required) VALUES
|
||
(1, '您的工作领域', 'SINGLE_CHOICE', TRUE),
|
||
(2, '岗位性质', 'SINGLE_CHOICE', TRUE);
|
||
|
||
-- 插入基础选项数据
|
||
INSERT INTO options (question_id, option_code, content) VALUES
|
||
(1, 'A', '研发'),
|
||
(1, 'B', '项目'),
|
||
(1, 'C', '保险'),
|
||
(1, 'D', '财务'),
|
||
(1, 'E', '运营'),
|
||
(1, 'F', '客服'),
|
||
(1, 'G', '综合管理');
|
||
|
||
INSERT INTO options (question_id, option_code, content) VALUES
|
||
(2, 'A', '管理岗'),
|
||
(2, 'B', '技术岗'),
|
||
(2, 'C', '业务岗'),
|
||
(2, 'D', '职能支持岗'); |