LLM-test/test/sql/2-sql测试-1/glm-4.7-web.md

这是一个非常棒的想法。为了全面测试大语言模型（LLM）的 SQL 能力，我们需要一个既有业务逻辑深度，又包含大量“坑”的数据库设计。

我为你设计了一个 “高校教务与捐赠管理系统”。

这个场景足够复杂，包含了层级结构（学院-系）、多对多关系（学生-课程、教师-课程），以及容易混淆的财务和人员信息。

1. 数据库概览

这个数据库包含 6 张表，以下是它们的关系和陷阱说明：

• Faculties (学院表): 顶级组织单位。
• Departments (系/专业表): 隶属于学院，学生和教师归属于此。
  • 关联: Faculties 1:N Departments
• Teachers (教师表): 包含入职信息。
  • 关联: Departments 1:N Teachers
• Students (学生表): 包含学籍信息。
  • 关联: Departments 1:N Students
  • 关联: Teachers N:M (通过 Advisor 关系，这里简化为字段，实际复杂场景可用表)
• Courses (课程表): 课程基础信息。
• Course_Records (选课及成绩表): 核心多对多表。
  • 关联: Students N:M Courses
  • 关联: Teachers 1:N (一门课一个老师教，但老师教多门课)

---

2. 详细表结构设计 (SQL DDL)

你可以直接运行以下 SQL 创建表。

-- 1. 学院表
CREATE TABLE Faculties (
    faculty_id INT PRIMARY KEY,
    faculty_name VARCHAR(100),
    dean_name VARCHAR(100) -- 陷阱1: 这里的名字是人名，不是学院名
);

-- 2. 系/专业表
CREATE TABLE Departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100),
    faculty_id INT,
    FOREIGN KEY (faculty_id) REFERENCES Faculties(faculty_id),
    dept_code VARCHAR(10) UNIQUE -- 陷阱2: 类似ID但不是主键，容易搞混
);

-- 3. 教师表
CREATE TABLE Teachers (
    teacher_id INT PRIMARY KEY,
    teacher_name VARCHAR(100),
    dept_id INT,
    hire_date DATE,
    title VARCHAR(50), -- 职称：Professor, Lecturer
    salary DECIMAL(10, 2), -- 陷阱3: 薪资字段，可能被误用为学生的学费
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

-- 4. 学生表
CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    dept_id INT,
    enrollment_year INT, -- 陷阱4: 这里存的是年份(2023)，不是日期
    admission_date DATE, -- 陷阱5: 这才是真正的日期
    tuition_fee DECIMAL(10, 2), -- 陷阱6: 学费，容易和 teacher.salary 搞混
    status VARCHAR(20), -- 状态: Active, Graduated, Suspended
    advisor_id INT, -- 导师ID
    total_credit INT DEFAULT 0, -- 陷阱7: 已修总学分，容易和课程的 credit 搞混
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id),
    FOREIGN KEY (advisor_id) REFERENCES Teachers(teacher_id)
);

-- 5. 课程表
CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    credit INT, -- 学分
    course_type VARCHAR(50), -- 类型: Core, Elective
    dept_id INT, -- 开课系
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

-- 6. 选课及成绩表 (多对多关系)
CREATE TABLE Course_Records (
    record_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    teacher_id INT,
    semester VARCHAR(20), -- 例如 "2023-Fall"
    score DECIMAL(5, 2), -- 陷阱8: 卷面分数 (0-100)
    grade_point DECIMAL(3, 2), -- 陷阱9: 绩点 (0.0-4.0)，含义相近易混
    attendance_rate INT, -- 考勤率百分比
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id),
    FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id)
);

---

3. 迷惑项与陷阱设计解析

在设计上述数据库时，我特意埋下了以下几类“坑”，用于测试 LLM 的语义理解能力：

1. 相似的字段名与含义:

   • Students.enrollment_year (年份, Integer) vs Students.admission_date (日期, Date)。
     • 测试点: 模型能否识别“年份”和“日期”的区别，在写 WHERE 条件时不会用错。
   • Course_Records.score (考试分数) vs Course_Records.grade_point (绩点) vs Courses.credit (学分)。
     • 测试点: 当问“平均分”时，模型应该算 score；当问“总学分”时，应该累加 Courses.credit。模型经常会混淆“分”和“点”。
   • Teachers.salary (工资) vs Students.tuition_fee (学费)。
     • 测试点: 两个字段都是钱，模型在写 JOIN 时如果只关注金额字段，很容易连错表。

2. 层级关系:

   • Faculties -> Departments -> Students/Teachers。
     • 测试点: 如果问题是“统计 计算机学院 (Faculty) 的所有学生人数”，模型必须通过 Departments 表进行两跳连接 (Students -> Departments -> Faculties)，而不能直接连。

3. 聚合函数的干扰:

   • Students.total_credit (表中已存在的冗余字段) vs 动态计算 SUM(Courses.credit)。
     • 测试点: 严谨的 SQL 应该基于 Course_Records 和 Courses 动态计算，但如果模型偷懒，可能会直接读取 Students.total_credit。我们可以测试问“实际修读学分”看模型是读字段还是计算。

4. 多对多关系的中间表:

   • Course_Records 包含了 student_id, course_id, teacher_id。
     • 测试点: 问“谁教了张三？”，模型需要 JOIN Students -> Course_Records -> Teachers。如果模型误以为 Students.advisor_id 就是任课老师，就会写错。

5. 命名相似:

   • dept_id 与 dept_code。
     • 测试点: 如果在查询条件中给出了代码（如 'CS01'），模型必须知道是用 dept_code 而不是 dept_id 去匹配。

---

4. 插入测试数据

为了让测试更真实，这里插入几条基础数据：

-- Faculties
INSERT INTO Faculties VALUES (1, 'Engineering', 'Dr. Smith');
INSERT INTO Faculties VALUES (2, 'Arts', 'Dr. Joyce');

-- Departments
INSERT INTO Departments VALUES (101, 'Computer Science', 1, 'CS');
INSERT INTO Departments VALUES (102, 'Mechanical Eng', 1, 'ME');
INSERT INTO Departments VALUES (201, 'English Lit', 2, 'ENG');

-- Teachers
INSERT INTO Teachers VALUES (1, 'Prof. Alice', 101, '2020-01-01', 'Professor', 9000.00);
INSERT INTO Teachers VALUES (2, 'Dr. Bob', 101, '2021-06-01', 'Lecturer', 6000.00);
INSERT INTO Teachers VALUES (3, 'Prof. Carol', 201, '2019-09-01', 'Professor', 8500.00);

-- Students
-- 注意：张三和李四在同一个系，王五在不同系
INSERT INTO Students VALUES (1001, 'Zhang San', 101, 2022, '2022-09-01', 5000.00, 'Active', 1, 0);
INSERT INTO Students VALUES (1002, 'Li Si', 101, 2023, '2023-09-01', 5500.00, 'Active', 2, 0);
INSERT INTO Students VALUES (1003, 'Wang Wu', 201, 2022, '2022-09-01', 4500.00, 'Active', 3, 0);

-- Courses
INSERT INTO Courses VALUES (501, 'Database Systems', 4, 'Core', 101);
INSERT INTO Courses VALUES (502, 'Algorithms', 3, 'Core', 101);
INSERT INTO Courses VALUES (503, 'Shakespeare', 3, 'Elective', 201);

-- Course_Records (选课记录)
-- 张三选了数据库，老师是Alice，考了85分
INSERT INTO Course_Records VALUES (1, 1001, 501, 1, '2023-Fall', 85.0, 3.5, 90);
-- 张三选了算法，老师是Bob，考了92分
INSERT INTO Course_Records VALUES (2, 1001, 502, 2, '2023-Fall', 92.0, 4.0, 95);
-- 李四选了数据库，老师是Alice，考了78分
INSERT INTO Course_Records VALUES (3, 1002, 501, 1, '2023-Fall', 78.0, 2.7, 80);
-- 王五选了莎士比亚，老师是Carol
INSERT INTO Course_Records VALUES (4, 1003, 503, 3, '2023-Fall', 88.0, 3.7, 100);

---

5. 推荐的测试 Prompt 样例

你可以用下面这些问题去测试模型，观察它是否掉入陷阱：

1. 测试层级过滤与相似字段名:

   • 问题: “查询 Engineering 学院 (Faculty) 下所有学生的名字和他们的入学年份。”
   • 陷阱: 需要两跳 JOIN；必须选 enrollment_year 而不是 admission_date。

2. 测试多对多关系与字段混淆:

   • 问题: “查询 ‘Database Systems’ 这门课的平均考试分数。”
   • 陷阱: 必须选 Course_Records.score，不能选 grade_point，也不能选 Courses.credit。

3. 测试金额字段区分:

   • 问题: “列出所有学费高于 5000 的学生姓名以及他们所属系的名称。”
   • 陷阱: 必须用 Students.tuition_fee，如果模型写了 salary 就错了。

4. 测试聚合与 JOIN 顺序:

   • 问题: “找出教过学生 ‘Zhang San’ 的所有老师的名字。”
   • 陷阱: 路径是 Students -> Course_Records -> Teachers。不能直接用 Students.advisor_id，因为那只是导师，不一定教过课。

5. 测试相似表名/代码:

   • 问题: “查询系代码为 'CS' 的系里，职称是 'Professor' 的老师薪资总和。”
   • 陷阱: 条件是用 dept_code = 'CS' 而不是 dept_id。

这套设计应该能很好地帮你分辨出 LLM 在 Schema Linking（模式链接）和语义理解方面的真实水平。