【和chatGPT对话】考校一下关系型数据库，看看比赛的关系理解得怎么样

前言和小结

SQL生成是 ChatGPT 的技能之一，但是生成SQL需要理解语境和需求。在单表可以轻松过的情况下，复杂的对象及其关系如何处理呢？本文试图考校一下。通过 N 次对话，发现多次复杂对话时 ChatGPT 会遗忘前面的数据表，建新表来干活。一批评就说对不起，指出问题所在也不改，经常搞一题多解。为了保证准确性，本次会话就尽量使用长语句，一次描述更多的需求。这样的情况下，可以获得较为理想的结果。

Q1

现在创建mysql关系型数据库，管理学生绘画比赛数据。 1、每个学校需要一个辅导教师注册作为联系人，负责管理学校信息、作品信息。 2、绘画比赛是全国性质，学校信息要包括所在省、市、县，学校名称，联系人、联系电话。 3、每个学校可以有多幅作品参赛。每幅作品包含类型、组别、名称、描述、获奖等级、获奖分数。 4、每幅作品对应一名学生，学生信息包括姓名、班级、联系电话。 5、每幅作品对应多名辅导教师。 6、每幅作品需要提交原始照片文件，系统自动生成缩略图方便预览，因此作品信息要包含原始图片地址，缩略图地址。 7、获奖等级默认空值，对应的获奖分数为0分，表示未获奖。获奖等级从高到底依次为特等奖、一等奖、二等奖、三等奖、优胜奖、参赛奖，对应的获奖分数分别对应100、85、70、50、10、1分。

A1

下面是一个可能的MySQL关系型数据库设计，以管理学生绘画比赛数据为例。

学校信息表（schools）

学校ID (school_id) - 主键
省 (province)
市 (city)
县 (county)
学校名称 (school_name)
联系人姓名 (contact_name)
联系人电话 (contact_phone)

学生信息表（students）

学生ID (student_id) - 主键
学生姓名 (student_name)
所属班级 (class)
联系电话 (phone)

作品信息表（artworks）

作品ID (artwork_id) - 主键
学校ID (school_id) - 外键（关联学校信息表的学校ID）
学生ID (student_id) - 外键（关联学生信息表的学生ID）
作品类型 (type)
作品组别 (group)
作品名称 (name)
作品描述 (description)
原始图片地址 (original_image)
缩略图地址 (thumbnail_image)
获奖等级 (award_level)
获奖分数 (award_score)

辅导教师表（teachers）

辅导教师ID (teacher_id) - 主键
辅导教师姓名 (teacher_name)

作品与辅导教师关系表（artwork_teacher）

作品ID (artwork_id) - 外键（关联作品信息表的作品ID）
辅导教师ID (teacher_id) - 外键（关联辅导教师表的辅导教师ID）

在这个设计中，学校信息表、学生信息表、作品信息表、辅导教师表都是单独的实体表，每个表都有一个唯一的ID作为主键来标识记录。作品信息表包含了很多字段，包括作品的类型、组别、名称、描述、原始图片地址、缩略图地址、获奖等级和获奖分数等信息。辅导教师表和作品与辅导教师关系表是用来管理辅导教师的信息和作品与辅导教师的关系的。

为了提高查询效率，可以在表中使用索引来加速查询。例如，可以在作品信息表的学校ID、学生ID、作品类型、作品组别和获奖等级字段上创建索引。

Q2 显示创建表的SQL

以下是创建上述MySQL关系型数据库所需的SQL命令，以创建表并添加外键约束：

学校信息表（schools）

CREATE TABLE schools (
  school_id INT NOT NULL AUTO_INCREMENT,
  province VARCHAR(255) NOT NULL,
  city VARCHAR(255) NOT NULL,
  county VARCHAR(255) NOT NULL,
  school_name VARCHAR(255) NOT NULL,
  contact_name VARCHAR(255) NOT NULL,
  contact_phone VARCHAR(255) NOT NULL,
  PRIMARY KEY (school_id)
);

学生信息表（students）

CREATE TABLE students (
  student_id INT NOT NULL AUTO_INCREMENT,
  student_name VARCHAR(255) NOT NULL,
  class VARCHAR(255) NOT NULL,
  phone VARCHAR(255) NOT NULL,
  PRIMARY KEY (student_id)
);

作品信息表（artworks）

CREATE TABLE artworks (
  artwork_id INT NOT NULL AUTO_INCREMENT,
  school_id INT NOT NULL,
  student_id INT NOT NULL,
  type VARCHAR(255) NOT NULL,
  group VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  original_image VARCHAR(255) NOT NULL,
  thumbnail_image VARCHAR(255) NOT NULL,
  award_level VARCHAR(255),
  award_score INT NOT NULL DEFAULT 0,
  PRIMARY KEY (artwork_id),
  FOREIGN KEY (school_id) REFERENCES schools(school_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

辅导教师表（teachers）

CREATE TABLE teachers (
  teacher_id INT NOT NULL AUTO_INCREMENT,
  teacher_name VARCHAR(255) NOT NULL,
  PRIMARY KEY (teacher_id)
);

作品与辅导教师关系表（artwork_teacher）

CREATE TABLE artwork_teacher (
  artwork_id INT NOT NULL,
  teacher_id INT NOT NULL,
  PRIMARY KEY (artwork_id, teacher_id),
  FOREIGN KEY (artwork_id) REFERENCES artworks(artwork_id),
  FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);

在这个示例中，每个表都使用了自增长的主键，表示为INT NOT NULL AUTO_INCREMENT。此外，外键约束的创建使用了FOREIGN KEY和REFERENCES语句。注意，创建外键约束时需要确保参考的列具有索引，以确保查询效率。

Q3

现在做数据查询： 1、查询评奖进度，包括各组别的参赛作品总数，已评奖作品数，未评奖作品数。 2、查询获奖作品，包括作品名称、作品描述、学生姓名、所在学校、班级、获奖等级、辅导教师。 3、按参赛组别统计每个学校获奖总分排行，显示参赛作品数量、各个获奖等级数量。 4、按参赛组别统计每个省获奖学校数量、获奖作品数量、各个获奖等级数量，前10%颁发优秀组织奖。 5、统计每位辅导教师获奖总分排行，显示参赛学生数量、获奖作品数量、各个获奖等级数量，前20%颁发优秀辅导教师奖。

A3

以下是可以用于实现上述数据查询任务的SQL查询语句：

1、查询评奖进度，包括各组别的参赛作品总数，已评奖作品数，未评奖作品数。

SELECT 
  group, 
  COUNT(*) AS total,
  COUNT(award_level) AS awarded,
  COUNT(*) - COUNT(award_level) AS not_awarded
FROM artworks
GROUP BY group;

2、查询获奖作品，包括作品名称、作品描述、学生姓名、所在学校、班级、获奖等级、辅导教师。

SELECT 
  artworks.name AS artwork_name,
  artworks.description,
  students.student_name,
  schools.school_name,
  students.class,
  artworks.award_level,
  GROUP_CONCAT(teachers.teacher_name SEPARATOR ', ') AS teachers
FROM artworks
JOIN students ON artworks.student_id = students.student_id
JOIN schools ON artworks.school_id = schools.school_id
LEFT JOIN artwork_teacher ON artworks.artwork_id = artwork_teacher.artwork_id
LEFT JOIN teachers ON artwork_teacher.teacher_id = teachers.teacher_id
WHERE artworks.award_level IS NOT NULL
GROUP BY artworks.artwork_id;

3、按参赛组别统计每个学校获奖总分排行，显示参赛作品数量、各个获奖等级数量。

SELECT 
  schools.school_name,
  artworks.group,
  COUNT(*) AS total_count,
  SUM(award_score) AS total_score,
  COUNT(CASE WHEN award_level = '特等奖' THEN 1 END) AS first_count,
  COUNT(CASE WHEN award_level = '一等奖' THEN 1 END) AS second_count,
  COUNT(CASE WHEN award_level = '二等奖' THEN 1 END) AS third_count,
  COUNT(CASE WHEN award_level = '三等奖' THEN 1 END) AS fourth_count,
  COUNT(CASE WHEN award_level = '优胜奖' THEN 1 END) AS fifth_count,
  COUNT(CASE WHEN award_level = '参赛奖' THEN 1 END) AS sixth_count
FROM artworks
JOIN schools ON artworks.school_id = schools.school_id
GROUP BY schools.school_id, artworks.group
ORDER BY total_score DESC;

4、按参赛组别统计每个省获奖学校数量、获奖作品数量、各个获奖等级数量，前10%颁发优秀组织奖。

SELECT 
  province, 
  COUNT(DISTINCT school_id) as num_schools, 
  COUNT(DISTINCT artwork_id) as num_artworks, 
  COUNT(CASE WHEN award_level = '特等奖' THEN 1 END) as num_special_awards, 
  COUNT(CASE WHEN award_level = '一等奖' THEN 1 END) as num_first_awards, 
  COUNT(CASE WHEN award_level = '二等奖' THEN 1 END) as num_second_awards, 
  COUNT(CASE WHEN award_level = '三等奖' THEN 1 END) as num_third_awards, 
  COUNT(CASE WHEN award_level = '优胜奖' THEN 1 END) as num_honorable_awards, 
  COUNT(CASE WHEN award_level = '参赛奖' THEN 1 END) as num_participation_awards 
FROM 
  artwork 
  INNER JOIN school ON artwork.school_id = school.id 
WHERE 
  province IS NOT NULL 
GROUP BY 
  province 
ORDER BY 
  num_schools DESC 
LIMIT 
  (SELECT COUNT(DISTINCT province) * 0.1 FROM school WHERE province IS NOT NULL)

5、统计每位辅导教师获奖总分排行，显示参赛学生数量、获奖作品数量、各个获奖等级数量，前20%颁发优秀辅导教师奖。

SELECT 
  teacher.name as teacher_name, 
  COUNT(DISTINCT artwork.student_id) as num_students, 
  COUNT(DISTINCT artwork.id) as num_artworks, 
  SUM(CASE WHEN award_level = '特等奖' THEN 100 
           WHEN award_level = '一等奖' THEN 85 
           WHEN award_level = '二等奖' THEN 70 
           WHEN award_level = '三等奖' THEN 50 
           WHEN award_level = '优胜奖' THEN 10 
           ELSE 1 END) as total_score, 
  COUNT(CASE WHEN award_level = '特等奖' THEN 1 END) as num_special_awards, 
  COUNT(CASE WHEN award_level = '一等奖' THEN 1 END) as num_first_awards, 
  COUNT(CASE WHEN award_level = '二等奖' THEN 1 END) as num_second_awards, 
  COUNT(CASE WHEN award_level = '三等奖' THEN 1 END) as num_third_awards, 
  COUNT(CASE WHEN award_level = '优胜奖' THEN 1 END) as num_honorable_awards, 
  COUNT(CASE WHEN award_level = '参赛奖' THEN 1 END) as num_participation_awards 
FROM 
  artwork 
  INNER JOIN teacher_artwork ON artwork.id = teacher_artwork.artwork_id 
  INNER JOIN teacher ON teacher_artwork.teacher_id = teacher.id 
GROUP BY 
  teacher.id 
ORDER BY 
  total_score DESC 
LIMIT 
  (SELECT COUNT(DISTINCT teacher.id) * 0.2 FROM teacher)

Q4 优化查询4和查询5，分数可以直接使用 award_score求和

好的，以下是优化后的查询4和查询5：

查询4：按参赛组别统计每个省获奖学校数量、获奖作品数量、各个获奖等级数量，前10%颁发优秀组织奖。


SELECT 
    s.province,
    COUNT(DISTINCT s.school_id) AS num_awarded_schools,
    COUNT(DISTINCT a.artwork_id) AS num_awarded_artworks,
    SUM(CASE WHEN a.award_level = '特等奖' THEN 1 ELSE 0 END) AS num_special_awards,
    SUM(CASE WHEN a.award_level = '一等奖' THEN 1 ELSE 0 END) AS num_first_place_awards,
    SUM(CASE WHEN a.award_level = '二等奖' THEN 1 ELSE 0 END) AS num_second_place_awards,
    SUM(CASE WHEN a.award_level = '三等奖' THEN 1 ELSE 0 END) AS num_third_place_awards,
    SUM(CASE WHEN a.award_level = '优胜奖' THEN 1 ELSE 0 END) AS num_honorable_mentions,
    SUM(CASE WHEN a.award_level = '参赛奖' THEN 1 ELSE 0 END) AS num_participation_awards,
    SUM(a.award_score) AS total_award_score
FROM 
    schools s
    JOIN contacts c ON s.school_id = c.school_id
    JOIN artworks a ON s.school_id = a.school_id
WHERE 
    a.award_level IS NOT NULL
GROUP BY 
    s.province
HAVING 
    COUNT(DISTINCT s.school_id) / (SELECT COUNT(DISTINCT province) FROM schools) >= 0.1
ORDER BY 
    total_award_score DESC;

查询5：统计每位辅导教师获奖总分排行，显示参赛学生数量、获奖作品数量、各个获奖等级数量，前20%颁发优秀辅导教师奖。

SELECT 
  teacher.name as teacher_name, 
  COUNT(DISTINCT artwork.student_id) as num_students, 
  COUNT(DISTINCT artwork.id) as num_artworks, 
  SUM(artwork.award_score) AS total_award_score
  COUNT(CASE WHEN award_level = '特等奖' THEN 1 END) as num_special_awards, 
  COUNT(CASE WHEN award_level = '一等奖' THEN 1 END) as num_first_awards, 
  COUNT(CASE WHEN award_level = '二等奖' THEN 1 END) as num_second_awards, 
  COUNT(CASE WHEN award_level = '三等奖' THEN 1 END) as num_third_awards, 
  COUNT(CASE WHEN award_level = '优胜奖' THEN 1 END) as num_honorable_awards, 
  COUNT(CASE WHEN award_level = '参赛奖' THEN 1 END) as num_participation_awards 
FROM 
  artwork 
  INNER JOIN teacher_artwork ON artwork.id = teacher_artwork.artwork_id 
  INNER JOIN teacher ON teacher_artwork.teacher_id = teacher.id 
GROUP BY 
  teacher.id 
ORDER BY 
  total_score DESC 
LIMIT 
  (SELECT COUNT(DISTINCT teacher.id) * 0.2 FROM teacher)

Q5 继续

按参赛组别统计每个省获奖学校数量、获奖作品数量、各个获奖等级数量，前10%颁发优秀组织奖。

SELECT 
  s.province,
  s.competition_group,
  COUNT(DISTINCT s.school_name) AS award_schools,
  COUNT(DISTINCT p.id) AS award_projects,
  SUM(CASE WHEN p.award_level IS NOT NULL THEN 1 ELSE 0 END) AS awarded_projects,
  SUM(p.award_score) AS total_award_score,
  COUNT(CASE WHEN p.award_level = '特等奖' THEN 1 END) AS special_awards,
  COUNT(CASE WHEN p.award_level = '一等奖' THEN 1 END) AS first_place_awards,
  COUNT(CASE WHEN p.award_level = '二等奖' THEN 1 END) AS second_place_awards,
  COUNT(CASE WHEN p.award_level = '三等奖' THEN 1 END) AS third_place_awards,
  COUNT(CASE WHEN p.award_level = '优胜奖' THEN 1 END) AS merit_awards,
  COUNT(CASE WHEN p.award_level = '参赛奖' THEN 1 END) AS participation_awards
FROM schools s
JOIN projects p ON s.school_name = p.school_name
GROUP BY s.province, s.competition_group
HAVING COUNT(DISTINCT s.school_name) > 0
ORDER BY total_award_score DESC
LIMIT (SELECT COUNT(DISTINCT s.province) * 0.1 FROM schools s)

统计每位辅导教师获奖总分排行，显示参赛学生数量、获奖作品数量、各个获奖等级数量，前20%颁发优秀辅导教师奖。

SELECT 
  t.teacher_name,
  COUNT(DISTINCT s.student_name) AS participating_students,
  COUNT(DISTINCT p.id) AS participating_projects,
  SUM(CASE WHEN p.award_level IS NOT NULL THEN 1 ELSE 0 END) AS awarded_projects,
  SUM(p.award_score) AS total_award_score,
  COUNT(CASE WHEN p.award_level = '特等奖' THEN 1 END) AS special_awards,
  COUNT(CASE WHEN p.award_level = '一等奖' THEN 1 END) AS first_place_awards,
  COUNT(CASE WHEN p.award_level = '二等奖' THEN 1 END) AS second_place_awards,
  COUNT(CASE WHEN p.award_level = '三等奖' THEN 1 END) AS third_place_awards,
  COUNT(CASE WHEN p.award_level = '优胜奖' THEN 1 END) AS merit_awards,
  COUNT(CASE WHEN p.award_level = '参赛奖' THEN 1 END) AS participation_awards
FROM teacher_project tp
JOIN projects p ON tp.project_id = p.id
JOIN students s ON p.student_id = s.id
JOIN teachers t ON tp.teacher_id = t.id
GROUP BY t.teacher_name
HAVING COUNT(DISTINCT s.student_name) > 0
ORDER BY total_award_score DESC
LIMIT (SELECT COUNT(DISTINCT t.teacher_name) * 0.2 FROM teachers t)

对话截图

以下是部分对话截图

对话截图

以下是部分对话截图

【和chatGPT对话】考校一下关系型数据库，看看比赛的关系理解得怎么样

::: hljs-center 图1 认真给出数据表结构 :::

【和chatGPT对话】考校一下关系型数据库，看看比赛的关系理解得怎么样

::: hljs-center 图2 在创建报表（视图）时创建了新的数据表 :::

【和chatGPT对话】考校一下关系型数据库，看看比赛的关系理解得怎么样

::: hljs-center 图3 使用了不存在的数据表 :::

【和chatGPT对话】考校一下关系型数据库，看看比赛的关系理解得怎么样 ::: hljs-center 图4 继续使用了不存在的数据表 :::

秒客网

【和chatGPT对话】考校一下关系型数据库，看看比赛的关系理解得怎么样

前言和小结

Q1

A1

Q2 显示创建表的SQL

Q3

A3

Q4 优化查询4和查询5，分数可以直接使用 award_score求和

Q5 继续

对话截图

对话截图

相关文章