LycanClaw

深浅模式

六 设计题

创建: 2026年01月19日 16:16:11
字数: 0 字
时长: 0 分钟
浏览: --

1 ​

依据要求，用 SQL 语句创建 GWCJB 表，提示：身份证号和岗位编号共同构成 GWCJB 表的主键。

gwcjb(身份证号 字符型(18) ,
岗位编号 字符型(5) ,
资格审核 逻辑型,
笔试成绩 整型
面试成绩 整型

sql
CREATE TABLE gwcjb
(
  身份证号 TEXT(18),
  岗位编号 CHAR(5),
  资格审核 LOGICAL,
  笔试成绩 SMALLINT,
  面试成绩 SMALLINT,
  PRIMARY KEY (身份证号, 岗位编号)
);

TEXT(n)、CHAR(n) 都表示字符型，括号中的数字表示最多存放的字符个数。

  • TEXT(n) 可变长度字符，实际占用空间随存储内容长度变化
  • CHAR(n) 定长字符，实际占用空间固定，与实际内容长度无关

例如：

TEXT(8) 存入 ABCDEF → 实际占用 6 个字符
CHAR(8) 存入 ABCDEF → 实际占用 8 个字符（后补 2 个空格）

其实实际情况下，都用 TEXT 也没什么问题（

几种常见的数字类型及使用判断：

  • 范围很小且不要负整数（0~255） → BYTE

适合计数、年龄、等级、0~100 成绩（确定不会出现 -1/缺考 这种标记值才用）

  • 整数且不大（-3 万 ～ 3 万） → SMALLINT / SHORT

最常用的“小整数”，成绩、人数、年份偏移量等；允许用 -1 表示“缺考/未录入”也更方便

  • 整数但不确定（-21 亿 ～ 21 亿） → INTEGER

ID、数量、计数器、业务里“可能越滚越大”的整数

  • 明确有小数，但不要求精度 → DOUBLE

浮点数，可能有精度误差；不推荐拿来存金额/精确分数

2 ​

用 SQL 语句创建“论坛”表。提示：文章序号为“论坛”表的主键。

论坛 (
文章序号 自动编号,
作者 字符串(20),
内容 长文本型,
更新时间 日期时间型,
EMAIL 地址 字符串（30））

sql
CREATE TABLE 论坛
(
  文章序号 AUTOINCREMENT,
  作者 TEXT(20),
  内容 LONGTEXT,
  更新时间 DATETIME,
  EMAIL地址 TEXT(30),
  PRIMARY KEY (文章序号)
);

只要字段含义是“时间点” → DATETIME

能一行写完 → TEXT
可能很多行 → LONGTEXT

3 ​

数据表 CJB 中有身份证号、岗位编号和成绩 3 个字段，RYB 中含身份证号和姓名 2 个字段。设计能统计每个人申报岗位情况的查询，包含身份证号、姓名、岗位数和平均分 4 列，按岗位数由多到少排序。要求表之间用内连接设计。

sql
SELECT
  RYB.身份证号,
  姓名,
  COUNT(岗位编号) AS 岗位数,
  AVG(成绩) AS 平均分
FROM RYB
INNER JOIN CJB
ON RYB.身份证号 = CJB.身份证号
GROUP BY
  RYB.身份证号,
  姓名
ORDER BY 3 DESC;

要求拆解：

  1. 先联表

为了把 RYB(身份证号, 姓名) 和 CJB(身份证号, 岗位编号, 成绩) 按身份证号对上，这样每条报名记录就能带着姓名。

sql
SELECT
  RYB.身份证号,
  RYB.姓名,
  CJB.岗位编号,
  CJB.成绩
FROM RYB
INNER JOIN CJB
ON RYB.身份证号 = CJB.身份证号;

联表后，一个人报了 3 个岗位 → 会出现 3 行。而且每行都有：身份证号、姓名、岗位编号、成绩。

  1. 每个人统计

联表之后，同一个人如果申报了多个岗位，就会对应多条记录。但题目要求的是 “每个人的申报结果（一行结果）”，并且要在这一行中给出：

岗位数（COUNT()：统计岗位数）
平均分（AVG()：求平均分）

因此，需要先把属于同一个人的多条记录合并成一组，再对这一组数据进行统计。

  • GROUP BY：按“人”分组记录

先分组，保证每个人一行：

sql
SELECT
  RYB.身份证号,
  RYB.姓名
FROM RYB
INNER JOIN CJB
ON RYB.身份证号 = CJB.身份证号
GROUP BY
  RYB.身份证号,
  RYB.姓名;

这一步结果：每个人只有 1 行，但还没有岗位数和平均分。

再加上每个人的统计信息：

sql
SELECT
  RYB.身份证号,
  RYB.姓名,
  COUNT(CJB.岗位编号) AS 岗位数,
	AVG(CJB.成绩) AS 平均分
FROM RYB
INNER JOIN CJB
ON RYB.身份证号 = CJB.身份证号
GROUP BY
  RYB.身份证号,
  RYB.姓名;

AVG(成绩) 这种列名默认会显示成 Expr1 或一长串函数名，加 AS 平均分 是为了让输出列名就是“平均分”，符合题目求，也方便排序/看结果。
同理， COUNT(...) AS 岗位数。

COUNT(字段) 括号内一般填写要统计的事物所对应的编号（ID）字段，在该编号字段不为空的情况下，COUNT(字段) 与 COUNT(*) 结果一致。

最后补上，按岗位数从多到少排序，也就是按第三列排序。

sql
ORDER BY COUNT(CJB.岗位编号) DESC;
ORDER BY 3 DESC;

两种写法是等价的。

4 ​

“课程”表中有 课程号 和 课程名 字段，“成绩”表中有 学号、课程号 和 成绩 字段。
用 SQL 语句设计查询，按课程号由大到小输出有人选的课程号和课程名，多人选的课程仅输出一次。

内连接 ​

sql
SELECT
  课程.课程号,
  课程.课程名
FROM 课程
INNER JOIN 成绩
ON 课程.课程号 = 成绩.课程号
GROUP BY
  课程.课程号,
  课程.课程名
ORDER BY
  课程.课程号 DESC;
  1. 先联表

目的：把 课程表 和 成绩表 按课程号对上，这样可以知道哪些课程真的有人选。

  • 课程（课程号，课程名）
  • 成绩（学号，课程号，成绩）
sql
SELECT
  课程.课程号,
  课程.课程名,
  成绩.学号
FROM 课程
INNER JOIN 成绩
ON 课程.课程号 = 成绩.课程号;

联表后：

  • 一门课程如果有 5 个学生选 → 会出现 5 行
  • 说明：这门课程“有人选”
  1. 多人选的课程只输出一次

联表后，一门课程可能对应多行记录。同样的题目要求： 每门“有人选”的课程只输出一次

因此，需要用把同一课程的多条记录合并成一条。在 SQL 中，这一步通过 GROUP BY 完成。

sql
GROUP BY
  课程.课程号,
  课程.课程名
  1. 只输出课程号和课程名

所以 SELECT 中只保留这两个字段，不需要聚合函数来统计什么。

sql
SELECT
  课程.课程号,
  课程.课程名
  1. 按课程号由大到小排序
sql
ORDER BY 课程.课程号 DESC;

隐式内连接 ​

标准答案的做法是这样的

sql
SELECT DISTINCT
  课程.课程号,
  课程.课程名
FROM 课程, 成绩
WHERE 课程.课程号 = 成绩.课程号
ORDER BY 课程.课程号 DESC;

要求拆解（按标准答案思路）

  1. 先联表（隐式内连接）

标准答案没有写 INNER JOIN，而是用旧式写法：

sql
FROM 课程, 成绩
WHERE 课程.课程号 = 成绩.课程号

这在 Access 中等价于内连接（INNER JOIN），效果是：

  • 只有在 成绩表中出现过的课程号
  • 才能和课程表匹配成功

自然满足“有人选的课程”

  1. 为什么要用 DISTINCT

联表后：

  • 一门课程被 5 个学生选
  • 就会出现 5 行相同的 课程号 + 课程名

但题目要求的是：

多人选的课程仅输出一次

sql
SELECT DISTINCT 课程.课程号, 课程.课程名

DISTINCT 能够对查询结果去重，只保留不重复的行

因此：

  • 同一门课程不管被多少人选
  • 最终只输出 一行

这题不需要统计（不算人数、不算成绩），
只需要去重输出课程信息。

在这种情况下：

  • GROUP BY 可以做到
  • DISTINCT 更简单

只去重，不统计 → 可以用 DISTINCT > 既去重，又统计（COUNT / AVG） → 用 GROUP BY > FROM A, B WHERE 条件 在 Access 中等价于内连接

5 ​

数据表 CJB 中有 身份证号、岗位编号 和 分数 3 个字段，GWB 中有 岗位编号、岗位名称 2 个字段。
设计能统计每个岗位申报情况的查询，包含 岗位编号、岗位名称、申报人数 和 平均分 4 列，按申报人数由多到少排序。
要求用 WHERE <条件> 实现表之间的连接条件。

sql
SELECT
  GWB.岗位编号,
  GWB.岗位名称,
  COUNT(CJB.身份证号) AS 申报人数,
  AVG(CJB.分数) AS 平均分
FROM GWB, CJB
WHERE GWB.岗位编号 = CJB.岗位编号
GROUP BY
  GWB.岗位编号,
  GWB.岗位名称
ORDER BY 3 DESC;

要求拆解

  1. 先联表（隐式内连接）

题目要求使用 WHERE <条件> 进行表连接，因此采用隐式内连接写法（INNER JOIN 用的是 ON<条件>）：

sql
FROM GWB, CJB
WHERE GWB.岗位编号 = CJB.岗位编号

联表目的同样是
把 岗位表 GWB（岗位编号，岗位名称）
和 申报记录表 CJB（身份证号，岗位编号，分数）
按岗位编号对上，这样每条申报记录都能带着岗位名称。

sql
SELECT
  GWB.岗位编号,
  GWB.岗位名称,
  CJB.身份证号,
  CJB.分数
FROM GWB, CJB
WHERE GWB.岗位编号 = CJB.岗位编号;

联表后：

  • 例如，一个岗位被 5 个人申报 → 会出现 5 行
  1. 每个岗位统计（分组 + 聚合）

联表后，一个岗位可能对应多条记录。
但题目要求的是 “每个岗位一行结果”，并在这一行中给出：

  • 申报人数 COUNT()：统计申报人数
  • 平均分 AVG()：计算平均分

因此，需要把同一个岗位GROUP BY：按岗位分组的多条记录合并成一组，再进行统计。

先分组，保证每个岗位一行：

sql
GROUP BY
  GWB.岗位编号,
  GWB.岗位名称
  1. 统计列的选择
sql
COUNT(CJB.身份证号) AS 申报人数
  • 每一条记录对应一次“人申报岗位”
  • 使用身份证号计数，表示统计的是人数

标准答案这里写的是 COUNT(身份证号 | *) 其中 | 表示 “或者”。
等价于在说：

统计身份证号，如果身份证号为空，就统计这一行本身（*）。换句话说，只要这一行存在，就算 1 条申报记录。

但在这种表结构里：

  • 一条记录 = 一次申报
  • 身份证号一般不会为空
sql
COUNT(身份证号)
COUNT(*)
COUNT(身份证号 | *)

所以在结果上这三种结果是一样的。

sql
AVG(CJB.分数) AS 平均分
  • 对同一岗位下的所有分数求平均
  1. 按申报人数由多到少排序
sql
ORDER BY 3 DESC;

6 ​

CJ 表中包含 姓名、课程名、等级（字符型）和 成绩（数值）字段。
将课程名中含“计算机”的记录，按成绩划分为
A（90 及以上）、B（89 至 80）、C（79 至 60）、D（59 至 0）、E（小于 0 或大于 100），
并填入 等级 字段。

sql
UPDATE CJ
SET 等级 =
  IIf(成绩 > 100 OR 成绩 < 0, 'E',
    IIf(成绩 >= 90, 'A',
      IIf(成绩 >= 80, 'B',
        IIf(成绩 >= 60, 'C', 'D')
      )
    )
  )
WHERE 课程名 LIKE '*计算机*';

要求拆解

  1. 这是更新操作（UPDATE）

本题不是查询结果，而是修改表中已有数据，因此使用的是 UPDATE 语句，而不是 SELECT。

sql
UPDATE 表名
SET 字段名 = 新值
WHERE 条件;

如果要更新多个字段：

sql
UPDATE 表名
SET 字段1 = 新值1,
    字段2 = 新值2
WHERE 条件;

一般来说：

  • SELECT：只查询数据，不改变表内容
  • UPDATE / DELETE：会修改或删除数据，通常需要配合 WHERE 条件，限定要操作的记录范围
  1. 只处理“课程名中含计算机”的记录

题目不是更新整张表，而是有条件的更新，条件写在 WHERE 中：

sql
WHERE 课程名 LIKE '*计算机*'

在 Access 中，LIKE 使用 * 作为通配符。
*计算机\* 表示前后都可以是任意内容，因此能匹配“包含计算机”的课程名。

偶尔还会用到这些组合：

  • LIKE '计算机*'：以“计算机”开头
  • LIKE '*计算机'：以“计算机”结尾
  • LIKE '*计算机*'：中间包含（最常用、最安全）
  1. 用 IIf 按成绩区间逐层判断

Access 中常用 IIf(条件, 真值, 假值) 进行条件判断。
多个区间时，通过嵌套 IIf 的方式，从“最特殊”到“最一般”依次判断。

这道题的判断顺序是：

  • 成绩 < 0 或 > 100 → E（异常情况，最先处理）
  • 成绩 ≥ 90 → A
  • 成绩 ≥ 80 → B
  • 成绩 ≥ 60 → C
  • 其余情况 → D

因此写成层层嵌套的 IIf 结构。当一条记录进入该表达式时，Access 会从外到内、逐层判断：

sql
SET 等级 =
  IIf(成绩 > 100 OR 成绩 < 0, 'E',
    IIf(成绩 >= 90, 'A',
      IIf(成绩 >= 80, 'B',
        IIf(成绩 >= 60, 'C', 'D')
      )
    )
  )

执行流程可以理解为：

  • 先判断成绩是否异常（小于 0 或大于 100），若是则直接赋值为 'E'
  • 否则继续判断是否 ≥ 90，满足则赋值为 'A'
  • 再判断是否 ≥ 80，满足则赋值为 'B'
  • 再判断是否 ≥ 60，满足则赋值为 'C'
  • 以上条件均不满足时，执行最后的 'D'

也就是说，前面的条件已经把异常和高分情况筛掉后，
最后一个 'D' 自然对应 0 ～ 59 分，不需要再额外写判断条件。

上述判断得到的结果，最终通过 UPDATE CJ SET 等级 = <计算结果> 写回到当前记录的 等级 字段中，完成更新操作。

7 ​

KCB 中有课程号和课程名字段，CJB 中有学号、课程号和成绩字段。按课程号由大到小输出 KCB 中所有课程的课程号、课程名和选课人数（或最高分、最低分、平均分）三列信息；对没人选的课程，选课人数列输出 0（或“无人选”等）。要求用 Inner Join 和 UNION 联合设计一条 SQL 语句。

sql
SELECT
  KCB.课程号,
  课程名,
  COUNT(学号) AS 选课人数
FROM KCB INNER JOIN CJB
ON KCB.课程号 = CJB.课程号
GROUP BY
  KCB.课程号,
  课程名

UNION ALL

SELECT
  课程号,
  课程名,
  0 AS 选课人数
FROM KCB
WHERE 课程号 NOT IN (SELECT 课程号 FROM CJB)

ORDER BY 1 DESC;

要求拆解

  1. 为什么需要 UNION ALL

只用 INNER JOIN 的结果只会包含“有人选”的课程。
但题目要求输出 KCB 的所有课程，因此需要把“没人选的课程”也补进结果中。标准答案的做法是：

  • 第一段：用 INNER JOIN 统计“有人选”的课程
  • 第二段：从 KCB 中找出“没人选”的课程，把人数补成 0
  • 最后：用 UNION ALL 把两段结果合并

UNION
合并结果时会自动去重，相同的行只保留一条。

UNION ALL
合并结果时不去重，两边的结果全部保留。

放在这里：

  • 第一段：统计 有人选的课程
  • 第二段：补上 没人选的课程

这两部分本来就不可能重复（一门课不可能既“有人选”又“没人选”），
所以用：

UNION ALL

更贴近标准答案，虽然直接使用 UNION 在本题中结果相同。

  1. 第一段：统计“有人选”的课程（Inner Join + 分组）
sql
SELECT
  KCB.课程号,
  课程名,
  COUNT(学号) AS 选课人数
FROM KCB INNER JOIN CJB
ON KCB.课程号 = CJB.课程号
GROUP BY
  KCB.课程号,
  课程名
  • INNER JOIN：只会保留在 CJB 中出现过的课程号 → 这些就是“有人选”的课程
  • COUNT(学号)：统计该课程出现了多少条选课记录（选课人数）
  • GROUP BY：按课程号、课程名把同一课程的多行记录合并成一行结果
  1. 第二段：补齐“没人选”的课程（NOT IN + 人数置 0）
sql
SELECT
  课程号,
  课程名,
  0 AS 选课人数
FROM KCB
WHERE 课程号 NOT IN (SELECT 课程号 FROM CJB)
  • SELECT 课程号 FROM CJB：列出所有“有人选”的课程号
  • NOT IN (...)：筛出 KCB 中不在该列表里的课程 → 这些就是“没人选”的课程
  • 对这些课程，按题意把人数列固定输出为 0
  1. 合并与排序（UNION ALL + ORDER BY）
sql
UNION ALL

把两段结果上下拼接在一起，得到“所有课程”的输出。

sql
ORDER BY 1 DESC

按第 1 列（课程号）倒序排序，满足“按课程号由大到小输出”。

补充（同一题的“最高分/最低分/平均分”版本）

如果题目要统计的是成绩信息，把第一段的 COUNT(学号) 换成聚合函数即可，例如：

sql
MAX(成绩) AS 最高分
MIN(成绩) AS 最低分
AVG(成绩) AS 平均分

第二段对应的列也改成 0（或“无人选”）即可，保证两段 SELECT 的列数和顺序一致。

8 ​

学生表中包含 学号 和 姓名 等字段，输出重名者的学号和姓名。要求用 SQL 嵌套查询实现。

sql
SELECT 学号, 姓名
FROM 学生
WHERE EXISTS
(
  SELECT *
  FROM 学生 AS XS
  WHERE 学生.姓名 = XS.姓名
    AND 学生.学号 <> XS.学号
);

要求拆解

  1. 这是“查重名”的问题

题目要找的是：
姓名相同，但学号不同的学生记录。
也就是说，一条学生记录是否存在“另一个和他同名的人”。

  1. 外层查询：决定输出什么
sql
SELECT 学号, 姓名
FROM 学生

外层查询负责最终输出结果：
只输出学号和姓名。

  1. 内层查询：判断“是否存在另一个同名的人”
sql
SELECT *
FROM 学生 AS XS
WHERE 学生.姓名 = XS.姓名
  AND 学生.学号 <> XS.学号

这里对学生表再查一次，并起别名 XS，用来和外层的 学生 作比较：

  • 学生.姓名 = XS.姓名
    → 姓名相同（重名的核心条件）

  • 学生.学号 <> XS.学号
    → 排除自己，确保是另一个人

  1. EXISTS 的作用
sql
WHERE EXISTS (子查询)

EXISTS 用来判断：

对当前这条学生记录，是否“存在”另一条 姓名相同但学号不同 的记录。

如果存在，这条学生记录就被保留下来；
如果不存在，就被 WHERE过滤掉。

  1. 查询结果
  • 所有姓名只出现一次的学生 → 不会被输出
  • 所有参与重名的学生 → 都会被输出（每人一行）

例如两个人同名：
→ 两条记录都会满足 EXISTS 条件。

9 ​

成绩表中有 学号、课程号 和 分数 等字段，输出每门课程的 课程号、最高分 和获得此分数的 学号。要求用 SQL 嵌套实现。

sql
SELECT 课程号, 分数 AS 最高分, 学号
FROM 成绩
WHERE 分数 =
(
  SELECT MAX(分数)
  FROM 成绩 AS C2
  WHERE C2.课程号 = 成绩.课程号
);

要求拆解

  1. 题目要的不是“只求最高分”，还要“拿到最高分的人”

每门课程可能有多个学生。需要：

  • 先找出该课程的最高分
  • 再找出分数等于最高分的那条记录（就能得到学号）
  1. 外层查询：决定输出什么
sql
SELECT 课程号, 分数 AS 最高分, 学号
FROM 成绩

外层查询负责最终输出三列：课程号、最高分（用分数列，AS 改名）、学号。

  1. 内层查询：求“同一门课程”的最高分

内层查询并不是只执行一次，而是对外层查询中的每一条记录，都会执行一次。

当外层查询扫描到成绩表中的一条记录时（例如某条“课程号 = X，分数 = Y，学号 = Z”的记录），
内层查询会以这条记录的课程号为条件，去成绩表中重新查一遍同一门课程的所有成绩：

sql
SELECT MAX(分数)
FROM 成绩 AS C2
WHERE C2.课程号 = 成绩.课程号

这一步得到的是：

  • MAX(分数)：求最大分数
  • WHERE C2.课程号 = 成绩.课程号：保证只在“当前这门课程”的记录中求最大值
  1. 用外层的 WHERE 把“最高分记录”筛出来
sql
WHERE 分数 = (子查询结果)

含义是：
只保留分数等于“本课程最高分”的记录。
这样留下来的记录中就包含了对应的 学号。

  1. 如果同一课程有多人并列最高分

这种写法会把并列最高分的学生都输出出来（每人一行），符合“获得此分数的学号”的含义。

10 ​

依据 习题.accdb 中的 课程表 和 成绩表，设计 SQL 语句，
使每门课程分两行输出最低分和最高分，并按下表格式显示：

课程名分数标注
高等数学45最低分
高等数学85最高分
计算机应用技术77最低分
计算机应用技术95最高分
..................
  • 列：课程名、分数、标注（“最低分 / 最高分”）
sql
SELECT 课程名, MAX(成绩) AS 分数, "最高分" AS 标注
FROM 课程, 成绩
WHERE 课程.课程号 = 成绩.课程号
GROUP BY 课程名

UNION

SELECT 课程名, MIN(成绩) AS 分数, "最低分" AS 标注
FROM 课程, 成绩
WHERE 课程.课程号 = 成绩.课程号
GROUP BY 课程名;

要求拆解

题目要求每门课程输出两行结果（一行最低分，一行最高分）。单条 SELECT 无法把一行结果“拆成两行”，因此需要两条查询再合并结果。

  1. 第一段：每门课程的最高分
sql
SELECT
  课程名,
  MAX(成绩) AS 分数,
  "最高分" AS 标注
FROM 课程, 成绩
WHERE 课程.课程号 = 成绩.课程号
GROUP BY 课程名

含义是：

  • 课程, 成绩 + WHERE 课程.课程号 = 成绩.课程号
    → 使用 隐式内连接，只统计有成绩记录的课程
  • GROUP BY 课程名
    → 按课程分组
  • MAX(成绩)
    → 求该课程的最高分
  • "最高分"
    → 补一列固定文字，用作标注

这一段的结果是：每门课程一行（最高分）。

  1. 第二段：每门课程的最低分
sql
SELECT
  课程名,
  MIN(成绩) AS 分数,
  "最低分" AS 标注
FROM 课程, 成绩
WHERE 课程.课程号 = 成绩.课程号
GROUP BY 课程名

写法与第一段完全一致，只是：

  • MAX 换成 MIN
  • 标注文字换成 "最低分"

这一段的结果是：每门课程一行（最低分）。

  1. 用 UNION 合并两段结果
sql
UNION

UNION 用来把两段查询结果上下合并成一个结果集。
标准答案使用 UNION（而非 UNION ALL），其实结果输出都差不多。

最终效果是：

同一门课程 → 输出两行

  • 一行最低分
  • 一行最高分

评论