998211c483
- 从服务器拉取完整代码 - 按框架规范整理项目结构 - 配置 Drone CI 测试环境部署 - 包含后端(FastAPI)、前端(Vue3)、管理端 技术栈: Vue3 + TypeScript + FastAPI + MySQL
216 lines
10 KiB
SQL
216 lines
10 KiB
SQL
-- 为admin用户（id=2）添加考试和错题数据
|
||
-- 方便直接使用admin账号测试成绩报告和错题本功能
|
||
|
||
USE kaopeilian;
|
||
|
||
-- ========================================
|
||
-- 一、插入admin的考试记录（包含三轮得分）
|
||
-- ========================================
|
||
|
||
-- 考试1：皮肤生理学基础（完成三轮，成绩优秀）
|
||
INSERT INTO exams (
|
||
user_id, course_id, exam_name, question_count, total_score, pass_score,
|
||
start_time, end_time, duration_minutes,
|
||
round1_score, round2_score, round3_score, score, is_passed, status
|
||
) VALUES (
|
||
2, 1, '皮肤生理学基础 - 动态考试', 10, 100.0, 60.0,
|
||
'2025-10-01 09:00:00', '2025-10-01 10:25:00', 60,
|
||
85, 95, 100, 100, TRUE, 'submitted'
|
||
);
|
||
|
||
-- 考试2：医美产品知识与应用（完成三轮）
|
||
INSERT INTO exams (
|
||
user_id, course_id, exam_name, question_count, total_score, pass_score,
|
||
start_time, end_time, duration_minutes,
|
||
round1_score, round2_score, round3_score, score, is_passed, status
|
||
) VALUES (
|
||
2, 2, '医美产品知识与应用 - 动态考试', 10, 100.0, 60.0,
|
||
'2025-10-03 14:00:00', '2025-10-03 15:30:00', 60,
|
||
78, 90, 95, 95, TRUE, 'submitted'
|
||
);
|
||
|
||
-- 考试3：美容仪器操作与维护（完成两轮）
|
||
INSERT INTO exams (
|
||
user_id, course_id, exam_name, question_count, total_score, pass_score,
|
||
start_time, end_time, duration_minutes,
|
||
round1_score, round2_score, round3_score, score, is_passed, status
|
||
) VALUES (
|
||
2, 3, '美容仪器操作与维护 - 动态考试', 10, 100.0, 60.0,
|
||
'2025-10-05 10:00:00', '2025-10-05 11:10:00', 60,
|
||
92, 100, NULL, 100, TRUE, 'submitted'
|
||
);
|
||
|
||
-- 考试4：医美项目介绍与咨询（完成三轮）
|
||
INSERT INTO exams (
|
||
user_id, course_id, exam_name, question_count, total_score, pass_score,
|
||
start_time, end_time, duration_minutes,
|
||
round1_score, round2_score, round3_score, score, is_passed, status
|
||
) VALUES (
|
||
2, 4, '医美项目介绍与咨询 - 动态考试', 10, 100.0, 60.0,
|
||
'2025-10-06 15:00:00', '2025-10-06 16:20:00', 60,
|
||
72, 85, 100, 100, TRUE, 'submitted'
|
||
);
|
||
|
||
-- 考试5：轻医美销售技巧（完成三轮）
|
||
INSERT INTO exams (
|
||
user_id, course_id, exam_name, question_count, total_score, pass_score,
|
||
start_time, end_time, duration_minutes,
|
||
round1_score, round2_score, round3_score, score, is_passed, status
|
||
) VALUES (
|
||
2, 5, '轻医美销售技巧 - 动态考试', 10, 100.0, 60.0,
|
||
'2025-10-07 09:30:00', '2025-10-07 11:00:00', 60,
|
||
88, 95, 100, 100, TRUE, 'submitted'
|
||
);
|
||
|
||
-- 考试6：客户服务与投诉处理（完成两轮）
|
||
INSERT INTO exams (
|
||
user_id, course_id, exam_name, question_count, total_score, pass_score,
|
||
start_time, end_time, duration_minutes,
|
||
round1_score, round2_score, round3_score, score, is_passed, status
|
||
) VALUES (
|
||
2, 6, '客户服务与投诉处理 - 动态考试', 10, 100.0, 60.0,
|
||
'2025-10-08 14:00:00', '2025-10-08 15:15:00', 60,
|
||
95, 100, NULL, 100, TRUE, 'submitted'
|
||
);
|
||
|
||
-- 考试7：社媒营销与私域运营（完成一轮，不及格）
|
||
INSERT INTO exams (
|
||
user_id, course_id, exam_name, question_count, total_score, pass_score,
|
||
start_time, end_time, duration_minutes,
|
||
round1_score, round2_score, round3_score, score, is_passed, status
|
||
) VALUES (
|
||
2, 7, '社媒营销与私域运营 - 动态考试', 10, 100.0, 60.0,
|
||
'2025-10-09 10:00:00', '2025-10-09 10:48:00', 60,
|
||
58, NULL, NULL, 58, FALSE, 'submitted'
|
||
);
|
||
|
||
-- 考试8：卫生消毒与感染控制（完成三轮，最近的考试）
|
||
INSERT INTO exams (
|
||
user_id, course_id, exam_name, question_count, total_score, pass_score,
|
||
start_time, end_time, duration_minutes,
|
||
round1_score, round2_score, round3_score, score, is_passed, status
|
||
) VALUES (
|
||
2, 9, '卫生消毒与感染控制 - 动态考试', 10, 100.0, 60.0,
|
||
'2025-10-11 09:00:00', '2025-10-11 10:18:00', 60,
|
||
90, 95, 100, 100, TRUE, 'submitted'
|
||
);
|
||
|
||
-- 考试9：美容心理学（完成三轮）
|
||
INSERT INTO exams (
|
||
user_id, course_id, exam_name, question_count, total_score, pass_score,
|
||
start_time, end_time, duration_minutes,
|
||
round1_score, round2_score, round3_score, score, is_passed, status
|
||
) VALUES (
|
||
2, 10, '美容心理学 - 动态考试', 10, 100.0, 60.0,
|
||
'2025-10-12 08:30:00', '2025-10-12 09:55:00', 60,
|
||
82, 90, 100, 100, TRUE, 'submitted'
|
||
);
|
||
|
||
-- ========================================
|
||
-- 二、插入admin的错题记录
|
||
-- ========================================
|
||
|
||
-- 获取刚插入的考试ID
|
||
SET @admin_exam1 = (SELECT id FROM exams WHERE user_id=2 AND course_id=1 ORDER BY id DESC LIMIT 1);
|
||
SET @admin_exam2 = (SELECT id FROM exams WHERE user_id=2 AND course_id=2 ORDER BY id DESC LIMIT 1);
|
||
SET @admin_exam4 = (SELECT id FROM exams WHERE user_id=2 AND course_id=4 ORDER BY id DESC LIMIT 1);
|
||
SET @admin_exam5 = (SELECT id FROM exams WHERE user_id=2 AND course_id=5 ORDER BY id DESC LIMIT 1);
|
||
SET @admin_exam7 = (SELECT id FROM exams WHERE user_id=2 AND course_id=7 ORDER BY id DESC LIMIT 1);
|
||
SET @admin_exam9 = (SELECT id FROM exams WHERE user_id=2 AND course_id=9 ORDER BY id DESC LIMIT 1);
|
||
|
||
-- 皮肤生理学基础 - 第一轮2道错题
|
||
INSERT INTO exam_mistakes (
|
||
user_id, exam_id, question_content, correct_answer, user_answer, question_type, created_at
|
||
) VALUES
|
||
(2, @admin_exam1, '表皮层最外层的细胞是？\nA. 基底细胞\nB. 角质细胞\nC. 黑色素细胞\nD. 朗格汉斯细胞', 'B', 'A', 'single', '2025-10-01 09:15:00'),
|
||
(2, @admin_exam1, '真皮层的主要成分包括哪些？（多选）\nA. 胶原蛋白\nB. 弹性蛋白\nC. 透明质酸\nD. 角质蛋白', 'A,B,C', 'A,B', 'multiple', '2025-10-01 09:28:00');
|
||
|
||
-- 医美产品知识与应用 - 第一轮3道错题
|
||
INSERT INTO exam_mistakes (
|
||
user_id, exam_id, question_content, correct_answer, user_answer, question_type, created_at
|
||
) VALUES
|
||
(2, @admin_exam2, '肉毒素的作用机制是？', '阻断神经肌肉接头处的信号传递，使肌肉松弛，从而减少动态皱纹', '收缩肌肉', 'essay', '2025-10-03 14:18:00'),
|
||
(2, @admin_exam2, '光子嫩肤术后___天内避免高温环境', '7', '3', 'blank', '2025-10-03 14:35:00'),
|
||
(2, @admin_exam2, '所有类型的色斑都可以用激光去除', '错误', '正确', 'judge', '2025-10-03 14:42:00');
|
||
|
||
-- 医美项目介绍与咨询 - 第一轮3道错题
|
||
INSERT INTO exam_mistakes (
|
||
user_id, exam_id, question_content, correct_answer, user_answer, question_type, created_at
|
||
) VALUES
|
||
(2, @admin_exam4, '面部埋线提升的维持时间通常是？\nA. 3-6个月\nB. 6-12个月\nC. 12-24个月\nD. 永久', 'C', 'B', 'single', '2025-10-06 15:20:00'),
|
||
(2, @admin_exam4, '水光针注射的最佳频率是___，共___次为一个疗程', '每3-4周一次，3-5次', '每周一次，10次', 'blank', '2025-10-06 15:38:00'),
|
||
(2, @admin_exam4, '请说明如何向客户介绍热玛吉项目的原理和效果', '原理：利用射频能量深入真皮层和筋膜层，刺激胶原蛋白重组再生。效果：紧致提升、改善皱纹、轮廓重塑，效果可持续1-2年。适合25岁以上肌肤松弛人群', '射频加热皮肤，可以紧致', 'essay', '2025-10-06 15:55:00');
|
||
|
||
-- 轻医美销售技巧 - 第一轮2道错题
|
||
INSERT INTO exam_mistakes (
|
||
user_id, exam_id, question_content, correct_answer, user_answer, question_type, created_at
|
||
) VALUES
|
||
(2, @admin_exam5, '有效挖掘客户需求的提问技巧包括？（多选）\nA. 开放式提问\nB. 封闭式确认\nC. 诱导式提问\nD. 深入式追问', 'A,B,D', 'A,B', 'multiple', '2025-10-07 09:52:00'),
|
||
(2, @admin_exam5, '客户说"太贵了"时，最佳应对策略是先___客户真正的顾虑', '了解', '降价', 'blank', '2025-10-07 10:15:00');
|
||
|
||
-- 社媒营销与私域运营 - 第一轮5道错题（不及格）
|
||
INSERT INTO exam_mistakes (
|
||
user_id, exam_id, question_content, correct_answer, user_answer, question_type, created_at
|
||
) VALUES
|
||
(2, @admin_exam7, '私域流量的核心价值在于？\nA. 降低获客成本\nB. 提高复购率\nC. 建立品牌忠诚度\nD. 以上都是', 'D', 'B', 'single', '2025-10-09 10:10:00'),
|
||
(2, @admin_exam7, '有效的社群运营需要具备哪些要素？（多选）\nA. 明确定位\nB. 持续输出\nC. 互动反馈\nD. 硬性推销', 'A,B,C', 'A,B', 'multiple', '2025-10-09 10:18:00'),
|
||
(2, @admin_exam7, '朋友圈营销应该每天发布10条以上内容', '错误', '正确', 'judge', '2025-10-09 10:25:00'),
|
||
(2, @admin_exam7, '社群活跃度下降时，应采取的措施包括___和___', '话题引导，福利刺激', '不管它，等着看', 'blank', '2025-10-09 10:32:00'),
|
||
(2, @admin_exam7, '请设计一个针对医美客户的短视频内容策略', '策略要点：1.专业科普（解答常见疑问）2.案例展示（真实效果对比）3.专家访谈（增强信任）4.互动活动（提升参与）5.客户见证（口碑传播）。发布频率：每周3-5条，时间选择晚上7-9点黄金时段', '每天发产品广告', 'essay', '2025-10-09 10:40:00');
|
||
|
||
-- 卫生消毒与感染控制 - 第一轮1道错题
|
||
INSERT INTO exam_mistakes (
|
||
user_id, exam_id, question_content, correct_answer, user_answer, question_type, created_at
|
||
) VALUES
|
||
(2, @admin_exam9, '医疗器械的消毒等级分为哪几类？（多选）\nA. 高水平消毒\nB. 中水平消毒\nC. 低水平消毒\nD. 无菌处理', 'A,B,C,D', 'A,B,C', 'multiple', '2025-10-11 09:25:00');
|
||
|
||
-- ========================================
|
||
-- 三、验证结果
|
||
-- ========================================
|
||
|
||
-- admin的考试统计
|
||
SELECT
|
||
'admin考试统计' as info,
|
||
COUNT(*) as total_exams,
|
||
COUNT(round1_score) as has_round1,
|
||
COUNT(round2_score) as has_round2,
|
||
COUNT(round3_score) as has_round3,
|
||
ROUND(AVG(round1_score), 1) as avg_round1,
|
||
ROUND(AVG(round2_score), 1) as avg_round2,
|
||
ROUND(AVG(round3_score), 1) as avg_round3
|
||
FROM exams
|
||
WHERE user_id = 2 AND round1_score IS NOT NULL;
|
||
|
||
-- admin的错题统计
|
||
SELECT
|
||
'admin错题统计' as info,
|
||
COUNT(*) as total_mistakes,
|
||
COUNT(DISTINCT exam_id) as distinct_exams,
|
||
COUNT(DISTINCT question_type) as distinct_types
|
||
FROM exam_mistakes
|
||
WHERE user_id = 2;
|
||
|
||
-- 按题型统计admin的错题
|
||
SELECT
|
||
question_type,
|
||
COUNT(*) as count
|
||
FROM exam_mistakes
|
||
WHERE user_id = 2 AND question_type IS NOT NULL
|
||
GROUP BY question_type
|
||
ORDER BY count DESC;
|
||
|
||
-- 显示admin最近5条考试记录
|
||
SELECT
|
||
id,
|
||
exam_name,
|
||
round1_score as 第一轮,
|
||
round2_score as 第二轮,
|
||
round3_score as 第三轮,
|
||
is_passed as 是否通过,
|
||
DATE_FORMAT(start_time, '%m-%d %H:%i') as 考试时间
|
||
FROM exams
|
||
WHERE user_id = 2 AND round1_score IS NOT NULL
|
||
ORDER BY start_time DESC
|
||
LIMIT 10;
|
||
|