小伙伴们，最近有新同事入职，经理让我出一个关于sqlserver相关的试卷，想着既然有deepseek，我们就偷懒下直接用deepseek给我们自动生成出来。
打开deepseek官网，输入提示词：
出一套SQL的试题要有基础考察，表创建、存储过程应用等,
最后出一两道操作题. 给出 试卷版 和 带答案版 给出完整的word 格式供下载
Deepseek经过深度思考给出了相应的结果
但是下载的链接是虚拟的，我们要重新优化下，继续给出提示词：
以上内容作为html 形式输出，添加两个按钮 试卷版 答案版 供用户下载
Deepseek经过思考后给出了完整的HTML代码，我们运行下看看效果。
发现实际下载链接还是不对，继续优化，输入提示词：
优化html 代码 要能够正确下载对应的word 格式
最终实现了word的可下载功能，下载的word预览如下：
全部代码：
<!DOCTYPE html>
<html lang="zh-CN">
<head><meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0"><title>SQL技能测试试卷</title><style>* {margin: 0;padding: 0;box-sizing: border-box;font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;}body {background: linear-gradient(135deg, #1a2980, #26d0ce);color: #333;min-height: 100vh;padding: 20px;}.container {max-width: 1200px;margin: 0 auto;}header {text-align: center;padding: 30px 0;color: white;text-shadow: 0 2px 4px rgba(0,0,0,0.3);}h1 {font-size: 2.8rem;margin-bottom: 10px;}.subtitle {font-size: 1.2rem;opacity: 0.9;max-width: 800px;margin: 0 auto;}.exam-container {background-color: white;border-radius: 15px;box-shadow: 0 10px 30px rgba(0,0,0,0.2);overflow: hidden;margin-top: 20px;}.tabs {display: flex;background: linear-gradient(to right, #3a7bd5, #00d2ff);}.tab {flex: 1;text-align: center;padding: 20px;font-size: 1.3rem;font-weight: bold;color: white;cursor: pointer;transition: all 0.3s ease;border-bottom: 4px solid transparent;}.tab.active {background-color: rgba(255,255,255,0.2);border-bottom: 4px solid #ffd700;}.tab-content {display: none;padding: 30px;}.tab-content.active {display: block;}.download-buttons {display: flex;justify-content: center;gap: 20px;padding: 20px 0;}.btn {padding: 15px 30px;font-size: 1.1rem;border: none;border-radius: 50px;cursor: pointer;display: flex;align-items: center;gap: 10px;transition: all 0.3s ease;font-weight: bold;box-shadow: 0 4px 10px rgba(0,0,0,0.15);}.btn-download {background: linear-gradient(to right, #00b09b, #96c93d);color: white;}.btn-download:hover {transform: translateY(-3px);box-shadow: 0 6px 15px rgba(0,0,0,0.2);}.btn-download:active {transform: translateY(1px);}.content-section {margin-bottom: 30px;padding: 20px;border-radius: 10px;background-color: #f8f9fa;box-shadow: 0 3px 10px rgba(0,0,0,0.05);}.section-title {font-size: 1.5rem;color: #2c3e50;margin-bottom: 15px;padding-bottom: 10px;border-bottom: 2px solid #3498db;}.question {margin-bottom: 20px;padding: 15px;background-color: white;border-radius: 8px;box-shadow: 0 2px 5px rgba(0,0,0,0.05);}.question-text {font-weight: bold;margin-bottom: 10px;}.options {list-style-type: none;padding-left: 20px;}.option {margin-bottom: 8px;}.code-block {background-color: #2d2d2d;color: #f8f8f2;padding: 20px;border-radius: 8px;font-family: 'Courier New', monospace;overflow-x: auto;margin: 15px 0;line-height: 1.5;font-size: 1rem;box-shadow: inset 0 0 10px rgba(0,0,0,0.5);}.sql-keyword {color: #f92672;}.sql-function {color: #66d9ef;}.sql-string {color: #e6db74;}.sql-comment {color: #75715e;}.answer-indicator {display: inline-block;padding: 3px 10px;background-color: #2ecc71;color: white;border-radius: 4px;font-size: 0.9rem;margin-left: 10px;}.note {background-color: #fffde7;border-left: 4px solid #ffc107;padding: 15px;margin: 20px 0;border-radius: 0 8px 8px 0;}.success-message {background-color: #d4edda;color: #155724;padding: 15px;border-radius: 5px;margin: 15px 0;display: none;}footer {text-align: center;padding: 30px 0;color: rgba(255,255,255,0.8);font-size: 0.9rem;}@media (max-width: 768px) {.tabs {flex-direction: column;}.download-buttons {flex-direction: column;align-items: center;}.btn {width: 100%;max-width: 300px;}h1 {font-size: 2.2rem;}}</style>
</head>
<body><div class="container"><header><h1>SQL技能测试试卷</h1><p class="subtitle">本测试包含SQL基础知识、表创建、存储过程应用以及综合操作题，全面考察您的数据库操作能力</p></header><div class="exam-container"><div class="tabs"><div class="tab active" data-tab="questions">试卷版</div><div class="tab" data-tab="answers">答案版</div></div><div class="tab-content active" id="questions"><div class="success-message" id="questions-download-success">试卷版Word文档下载成功！如果下载没有自动开始，请检查浏览器设置。</div><div class="content-section"><h2 class="section-title">一、基础选择题（每题 2 分，共 10 分）</h2><div class="question"><p class="question-text">1. 删除表结构的 SQL 命令是：</p><ul class="options"><li class="option">A. DELETE</li><li class="option">B. TRUNCATE</li><li class="option">C. DROP</li><li class="option">D. REMOVE</li></ul></div><div class="question"><p class="question-text">2. 以下哪个约束确保字段值唯一？</p><ul class="options"><li class="option">A. PRIMARY KEY</li><li class="option">B. FOREIGN KEY</li><li class="option">C. UNIQUE</li><li class="option">D. CHECK</li></ul></div><div class="question"><p class="question-text">3. 事务的 ACID 特性中，"C" 代表：</p><ul class="options"><li class="option">A. Consistency</li><li class="option">B. Concurrency</li><li class="option">C. Commit</li><li class="option">D. Constraint</li></ul></div><div class="question"><p class="question-text">4. 查询姓名为"张三"的员工记录，WHERE 子句正确的是：</p><ul class="options"><li class="option">A. WHERE name = "张三"</li><li class="option">B. WHERE name = '张三'</li><li class="option">C. WHERE name LIKE "张三"</li><li class="option">D. WHERE name EQUALS '张三'</li></ul></div><div class="question"><p class="question-text">5. 聚合函数 AVG() 的作用是：</p><ul class="options"><li class="option">A. 计数</li><li class="option">B. 求和</li><li class="option">C. 平均值</li><li class="option">D. 最大值</li></ul></div></div><div class="content-section"><h2 class="section-title">二、表创建与操作（20 分）</h2><div class="question"><p class="question-text">1. 创建表 Students，包含以下字段：</p><ul class="options"><li class="option">- StudentID（主键，自增长整数）</li><li class="option">- Name（非空字符串，最大 50 字符）</li><li class="option">- BirthDate（日期类型）</li><li class="option">- Score（小数，范围 0.0~100.0）</li><li class="option">- ClassID（外键，关联表 Classes 的 ClassID）</li></ul></div><div class="question"><p class="question-text">2. 修改 Students 表：</p><ul class="options"><li class="option">- 添加字段 Email（唯一约束，字符串，最大 100 字符）</li><li class="option">- 删除字段 BirthDate</li></ul></div></div><div class="content-section"><h2 class="section-title">三、存储过程应用（20 分）</h2><div class="question"><p class="question-text">编写存储过程 sp_UpdateScore：</p><ul class="options"><li class="option">- 输入参数：@StudentID（整数），@NewScore（小数）</li><li class="option">- 功能：</li><li class="option">1. 更新 Students 表中对应学生的 Score</li><li class="option">2. 若 @NewScore > 100 则自动修正为 100</li><li class="option">3. 返回更新后的学生姓名和新分数</li></ul></div></div><div class="content-section"><h2 class="section-title">四、操作题（50 分）</h2><div class="question"><p class="question-text">表结构：</p><ul class="options"><li class="option"><strong>Employees</strong>：EmployeeID (INT, PK), Name (VARCHAR(50)), DepartmentID (INT), Salary (DECIMAL(10,2))</li><li class="option"><strong>Departments</strong>：DepartmentID (INT, PK), DepartmentName (VARCHAR(50))</li></ul></div><div class="question"><p class="question-text">1. 查询优化（25 分）</p><p>列出部门名称、平均工资（保留两位小数），仅显示平均工资 > 10000 的部门，按平均工资降序排序。</p></div><div class="question"><p class="question-text">2. 数据操作（25 分）</p><ul class="options"><li class="option">- 将"销售部"员工的工资增加 10%</li><li class="option">- 删除工资低于 5000 且不属于"技术部"的员工记录</li></ul></div></div><div class="download-buttons"><button class="btn btn-download" id="download-questions"><svg xmlns="http://www.w3.org/2000/svg" width="20" height="20" fill="currentColor" viewBox="0 0 16 16"><path d="M.5 9.9a.5.5 0 0 1 .5.5v2.5a1 1 0 0 0 1 1h12a1 1 0 0 0 1-1v-2.5a.5.5 0 0 1 1 0v2.5a2 2 0 0 1-2 2H2a2 2 0 0 1-2-2v-2.5a.5.5 0 0 1 .5-.5z"/><path d="M7.646 11.854a.5.5 0 0 0 .708 0l3-3a.5.5 0 0 0-.708-.708L8.5 10.293V1.5a.5.5 0 0 0-1 0v8.793L5.354 8.146a.5.5 0 1 0-.708.708l3 3z"/></svg>下载试卷（Word格式）</button></div></div><div class="tab-content" id="answers"><div class="success-message" id="answers-download-success">答案版Word文档下载成功！如果下载没有自动开始，请检查浏览器设置。</div><div class="content-section"><h2 class="section-title">一、基础选择题答案</h2><div class="question"><p class="question-text">1. 删除表结构的 SQL 命令是： <span class="answer-indicator">C. DROP</span></p></div><div class="question"><p class="question-text">2. 以下哪个约束确保字段值唯一？ <span class="answer-indicator">C. UNIQUE</span></p></div><div class="question"><p class="question-text">3. 事务的 ACID 特性中，"C" 代表： <span class="answer-indicator">A. Consistency</span></p></div><div class="question"><p class="question-text">4. 查询姓名为"张三"的员工记录，WHERE 子句正确的是： <span class="answer-indicator">B. WHERE name = '张三'</span></p></div><div class="question"><p class="question-text">5. 聚合函数 AVG() 的作用是： <span class="answer-indicator">C. 平均值</span></p></div></div><div class="content-section"><h2 class="section-title">二、表创建与操作答案</h2><pre class="code-block"><span class="sql-comment">-- 1. 创建表</span>
<span class="sql-keyword">CREATE TABLE</span> <span class="sql-function">Students</span> (StudentID <span class="sql-keyword">INT PRIMARY KEY IDENTITY</span>(<span class="sql-number">1</span>,<span class="sql-number">1</span>),<span class="sql-function">Name</span> <span class="sql-keyword">VARCHAR</span>(<span class="sql-number">50</span>) <span class="sql-keyword">NOT NULL</span>,BirthDate <span class="sql-keyword">DATE</span>,Score <span class="sql-keyword">DECIMAL</span>(<span class="sql-number">5</span>,<span class="sql-number">2</span>) <span class="sql-keyword">CHECK</span> (Score <span class="sql-keyword">BETWEEN</span> <span class="sql-number">0.0</span> <span class="sql-keyword">AND</span> <span class="sql-number">100.0</span>),ClassID <span class="sql-keyword">INT FOREIGN KEY REFERENCES</span> Classes(ClassID)
);<span class="sql-comment">-- 2. 修改表</span>
<span class="sql-keyword">ALTER TABLE</span> <span class="sql-function">Students</span>
<span class="sql-keyword">ADD</span> Email <span class="sql-keyword">VARCHAR</span>(<span class="sql-number">100</span>) <span class="sql-keyword">UNIQUE</span>;<span class="sql-keyword">ALTER TABLE</span> <span class="sql-function">Students</span>
<span class="sql-keyword">DROP COLUMN</span> BirthDate;</pre></div><div class="content-section"><h2 class="section-title">三、存储过程应用答案</h2><pre class="code-block"><span class="sql-keyword">CREATE PROCEDURE</span> sp_UpdateScore@StudentID <span class="sql-keyword">INT</span>,@NewScore <span class="sql-keyword">DECIMAL</span>(<span class="sql-number">5</span>,<span class="sql-number">2</span>)
<span class="sql-keyword">AS</span>
<span class="sql-keyword">BEGIN</span><span class="sql-comment">-- 修正分数超过100的情况</span><span class="sql-keyword">IF</span> @NewScore > <span class="sql-number">100</span> <span class="sql-keyword">SET</span> @NewScore = <span class="sql-number">100</span>;<span class="sql-comment">-- 更新分数</span><span class="sql-keyword">UPDATE</span> <span class="sql-function">Students</span><span class="sql-keyword">SET</span> Score = @NewScore<span class="sql-keyword">WHERE</span> StudentID = @StudentID;<span class="sql-comment">-- 返回结果</span><span class="sql-keyword">SELECT</span> <span class="sql-function">Name</span>, Score<span class="sql-keyword">FROM</span> <span class="sql-function">Students</span><span class="sql-keyword">WHERE</span> StudentID = @StudentID;
<span class="sql-keyword">END</span>;</pre></div><div class="content-section"><h2 class="section-title">四、操作题答案</h2><div class="question"><p class="question-text">1. 查询优化</p><pre class="code-block"><span class="sql-keyword">SELECT</span> d.DepartmentName,<span class="sql-function">ROUND</span>(<span class="sql-function">AVG</span>(e.Salary), <span class="sql-number">2</span>) <span class="sql-keyword">AS</span> AvgSalary
<span class="sql-keyword">FROM</span> <span class="sql-function">Departments</span> d
<span class="sql-keyword">JOIN</span> <span class="sql-function">Employees</span> e <span class="sql-keyword">ON</span> d.DepartmentID = e.DepartmentID
<span class="sql-keyword">GROUP BY</span> d.DepartmentName
<span class="sql-keyword">HAVING</span> <span class="sql-function">AVG</span>(e.Salary) > <span class="sql-number">10000</span>
<span class="sql-keyword">ORDER BY</span> AvgSalary <span class="sql-keyword">DESC</span>;</pre></div><div class="question"><p class="question-text">2. 数据操作</p><pre class="code-block"><span class="sql-comment">-- 更新工资</span>
<span class="sql-keyword">UPDATE</span> <span class="sql-function">Employees</span>
<span class="sql-keyword">SET</span> Salary = Salary * <span class="sql-number">1.10</span>
<span class="sql-keyword">WHERE</span> DepartmentID = (<span class="sql-keyword">SELECT</span> DepartmentID <span class="sql-keyword">FROM</span> <span class="sql-function">Departments</span> <span class="sql-keyword">WHERE</span> DepartmentName = <span class="sql-string">'销售部'</span>
);<span class="sql-comment">-- 删除记录</span>
<span class="sql-keyword">DELETE FROM</span> <span class="sql-function">Employees</span>
<span class="sql-keyword">WHERE</span> Salary < <span class="sql-number">5000</span>
<span class="sql-keyword">AND</span> DepartmentID <span class="sql-keyword">NOT IN</span> (<span class="sql-keyword">SELECT</span> DepartmentID <span class="sql-keyword">FROM</span> <span class="sql-function">Departments</span> <span class="sql-keyword">WHERE</span> DepartmentName = <span class="sql-string">'技术部'</span>
);</pre></div></div><div class="download-buttons"><button class="btn btn-download" id="download-answers"><svg xmlns="http://www.w3.org/2000/svg" width="20" height="20" fill="currentColor" viewBox="0 0 16 16"><path d="M.5 9.9a.5.5 0 0 1 .5.5v2.5a1 1 0 0 0 1 1h12a1 1 0 0 0 1-1v-2.5a.5.5 0 0 1 1 0v2.5a2 2 0 0 1-2 2H2a2 2 0 0 1-2-2v-2.5a.5.5 0 0 1 .5-.5z"/><path d="M7.646 11.854a.5.5 0 0 0 .708 0l3-3a.5.5 0 0 0-.708-.708L8.5 10.293V1.5a.5.5 0 0 0-1 0v8.793L5.354 8.146a.5.5 0 1 0-.708.708l3 3z"/></svg>下载答案（Word格式）</button></div></div></div><div class="note"><p><strong>注意：</strong> 本页面使用FileSaver.js实现Word文档下载功能，实际下载的文件为.doc格式，可直接用Microsoft Word打开。</p></div><footer><p>© 2023 SQL技能测试中心 | 全面考察您的数据库操作能力</p></footer></div><!-- 引入FileSaver.js用于文件下载 --><script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.5/FileSaver.min.js"></script><script>// 选项卡切换功能document.querySelectorAll('.tab').forEach(tab => {tab.addEventListener('click', () => {// 移除所有活动标签document.querySelectorAll('.tab').forEach(t => t.classList.remove('active'));document.querySelectorAll('.tab-content').forEach(c => c.classList.remove('active'));// 激活当前标签tab.classList.add('active');const tabId = tab.getAttribute('data-tab');document.getElementById(tabId).classList.add('active');});});// 试卷版下载功能document.getElementById('download-questions').addEventListener('click', function() {// 创建Word文档内容const content = `<html xmlns:o='urn:schemas-microsoft-com:office:office'xmlns:w='urn:schemas-microsoft-com:office:word'xmlns='http://www.w3.org/TR/REC-html40'><head><meta charset='utf-8'><title>SQL技能测试试卷</title></head><body><h1 style="text-align:center;">SQL技能测试试卷</h1><p style="text-align:center;">本测试包含SQL基础知识、表创建、存储过程应用以及综合操作题</p><h2>一、基础选择题（每题 2 分，共 10 分）</h2><p>1. 删除表结构的 SQL 命令是：</p><p>A. DELETE B. TRUNCATE C. DROP D. REMOVE</p><p>2. 以下哪个约束确保字段值唯一？</p><p>A. PRIMARY KEY B. FOREIGN KEY C. UNIQUE D. CHECK</p><p>3. 事务的 ACID 特性中，"C" 代表：</p><p>A. Consistency B. Concurrency C. Commit D. Constraint</p><p>4. 查询姓名为"张三"的员工记录，WHERE 子句正确的是：</p><p>A. WHERE name = "张三" B. WHERE name = '张三' C. WHERE name LIKE "张三" D. WHERE name EQUALS '张三'</p><p>5. 聚合函数 AVG() 的作用是：</p><p>A. 计数 B. 求和 C. 平均值 D. 最大值</p><h2>二、表创建与操作（20 分）</h2><p>1. 创建表 Students，包含以下字段：</p><ul><li>StudentID（主键，自增长整数）</li><li>Name（非空字符串，最大 50 字符）</li><li>BirthDate（日期类型）</li><li>Score（小数，范围 0.0~100.0）</li><li>ClassID（外键，关联表 Classes 的 ClassID）</li></ul><p>2. 修改 Students 表：</p><ul><li>添加字段 Email（唯一约束，字符串，最大 100 字符）</li><li>删除字段 BirthDate</li></ul><h2>三、存储过程应用（20 分）</h2><p>编写存储过程 sp_UpdateScore：</p><ul><li>输入参数：@StudentID（整数），@NewScore（小数）</li><li>功能：</li><li>1. 更新 Students 表中对应学生的 Score</li><li>2. 若 @NewScore > 100 则自动修正为 100</li><li>3. 返回更新后的学生姓名和新分数</li></ul><h2>四、操作题（50 分）</h2><p>表结构：</p><ul><li><strong>Employees</strong>：EmployeeID (INT, PK), Name (VARCHAR(50)), DepartmentID (INT), Salary (DECIMAL(10,2))</li><li><strong>Departments</strong>：DepartmentID (INT, PK), DepartmentName (VARCHAR(50))</li></ul><p>1. 查询优化（25 分）</p><p>列出部门名称、平均工资（保留两位小数），仅显示平均工资 > 10000 的部门，按平均工资降序排序。</p><p>2. 数据操作（25 分）</p><ul><li>将"销售部"员工的工资增加 10%</li><li>删除工资低于 5000 且不属于"技术部"的员工记录</li></ul></body></html>`;// 创建Blob对象const blob = new Blob([content], {type: 'application/msword'});// 使用FileSaver保存文件saveAs(blob, 'SQL技能测试试卷.doc');// 显示下载成功消息document.getElementById('questions-download-success').style.display = 'block';// 5秒后隐藏消息setTimeout(() => {document.getElementById('questions-download-success').style.display = 'none';}, 5000);});// 答案版下载功能document.getElementById('download-answers').addEventListener('click', function() {// 创建Word文档内容const content = `<html xmlns:o='urn:schemas-microsoft-com:office:office'xmlns:w='urn:schemas-microsoft-com:office:word'xmlns='http://www.w3.org/TR/REC-html40'><head><meta charset='utf-8'><title>SQL技能测试试卷答案</title></head><body><h1 style="text-align:center;">SQL技能测试试卷答案</h1><h2>一、基础选择题答案</h2><p>1. C. DROP</p><p>2. C. UNIQUE</p><p>3. A. Consistency</p><p>4. B. WHERE name = '张三'</p><p>5. C. 平均值</p><h2>二、表创建与操作答案</h2><pre>-- 1. 创建表
CREATE TABLE Students (StudentID INT PRIMARY KEY IDENTITY(1,1),Name VARCHAR(50) NOT NULL,BirthDate DATE,Score DECIMAL(5,2) CHECK (Score BETWEEN 0.0 AND 100.0),ClassID INT FOREIGN KEY REFERENCES Classes(ClassID)
);-- 2. 修改表
ALTER TABLE Students
ADD Email VARCHAR(100) UNIQUE;ALTER TABLE Students
DROP COLUMN BirthDate;</pre><h2>三、存储过程应用答案</h2><pre>CREATE PROCEDURE sp_UpdateScore@StudentID INT,@NewScore DECIMAL(5,2)
AS
BEGIN-- 修正分数超过100的情况IF @NewScore > 100 SET @NewScore = 100;-- 更新分数UPDATE StudentsSET Score = @NewScoreWHERE StudentID = @StudentID;-- 返回结果SELECT Name, ScoreFROM StudentsWHERE StudentID = @StudentID;
END;</pre><h2>四、操作题答案</h2><p>1. 查询优化</p><pre>SELECT d.DepartmentName,ROUND(AVG(e.Salary), 2) AS AvgSalary
FROM Departments d
JOIN Employees e ON d.DepartmentID = e.DepartmentID
GROUP BY d.DepartmentName
HAVING AVG(e.Salary) > 10000
ORDER BY AvgSalary DESC;</pre><p>2. 数据操作</p><pre>-- 更新工资
UPDATE Employees
SET Salary = Salary * 1.10
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = '销售部'
);-- 删除记录
DELETE FROM Employees
WHERE Salary < 5000
AND DepartmentID NOT IN (SELECT DepartmentID FROM Departments WHERE DepartmentName = '技术部'
);</pre></body></html>`;// 创建Blob对象const blob = new Blob([content], {type: 'application/msword'});// 使用FileSaver保存文件saveAs(blob, 'SQL技能测试试卷答案.doc');// 显示下载成功消息document.getElementById('answers-download-success').style.display = 'block';// 5秒后隐藏消息setTimeout(() => {document.getElementById('answers-download-success').style.display = 'none';}, 5000);});</script>
</body>
</html>
感谢大家的点赞和关注，我们下期见！
)