chat2db教程：根据对话内容生成SQL语句

一时失言乱红尘 2023-10-15 21:17 85阅读 0赞

## 准备示例表 ##

--学生信息表
    drop table if exists STUDENT;
    CREATE TABLE student (
      id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID',
      name VARCHAR(50) NOT NULL COMMENT '学生姓名',
      gender VARCHAR(10) NOT NULL COMMENT '学生性别',
      birthday DATE NOT NULL COMMENT '学生生日',
      address VARCHAR(100) NOT NULL COMMENT '学生住址',
      phone VARCHAR(20) NOT NULL COMMENT '学生联系方式'
    ) ;
    
    --科目表：
    drop table if exists COURSE;
    CREATE TABLE course (
      id INT PRIMARY KEY AUTO_INCREMENT COMMENT '科目ID',
      name VARCHAR(50) NOT NULL COMMENT '科目名称',
      teacher VARCHAR(50) NOT NULL COMMENT '授课教师',
      credit INT NOT NULL COMMENT '科目学分'
    ) ;
    
    --学生选修科目表：
    drop table if exists STUDENT_COURSE;
    CREATE TABLE student_course (
      id INT PRIMARY KEY AUTO_INCREMENT COMMENT '关系ID',
      student_id INT NOT NULL COMMENT '学生ID',
      course_id INT NOT NULL COMMENT '科目ID',
      FOREIGN KEY (student_id) REFERENCES student(id),
      FOREIGN KEY (course_id) REFERENCES course(id)
    ) ;
    
    --学生成绩表：
    drop table if exists SCORE;
    CREATE TABLE score (
      id INT PRIMARY KEY AUTO_INCREMENT COMMENT '成绩ID',
      student_id INT NOT NULL COMMENT '学生ID',
      course_id INT NOT NULL COMMENT '科目ID',
      score INT NOT NULL COMMENT '成绩',
      FOREIGN KEY (student_id) REFERENCES student(id),
      FOREIGN KEY (course_id) REFERENCES course(id)
    ) ;

## 为以下四张表插入一些测试数据，各10条。STUDENT COURSE STUDENT\_COURSE SCORE ##

--为以下四张表插入一些测试数据，各10条。STUDENT COURSE  STUDENT_COURSE SCORE
    INSERT INTO STUDENT (ID, NAME, GENDER, BIRTHDAY, ADDRESS, PHONE)
    VALUES (1, 'John', 'Male', '1990-01-01', '123 Main St', '123-456-7890'),
           (2, 'Jane', 'Female', '1992-02-02', '456 Elm St', '987-654-3210'),
           (3, 'Mike', 'Male', '1991-03-03', '789 Oak St', '555-555-5555'),
           (4, 'Emily', 'Female', '1993-04-04', '321 Pine St', '111-222-3333'),
           (5, 'David', 'Male', '1990-05-05', '654 Maple St', '444-444-4444'),
           (6, 'Sarah', 'Female', '1992-06-06', '987 Birch St', '777-888-9999'),
           (7, 'Tom', 'Male', '1991-07-07', '654 Oak St', '222-333-4444'),
           (8, 'Amy', 'Female', '1993-08-08', '321 Elm St', '111-222-3333'),
           (9, 'Chris', 'Male', '1990-09-09', '789 Maple St', '444-555-6666'),
           (10, 'Lisa', 'Female', '1992-10-10', '123 Pine St', '777-888-9999');
    
    INSERT INTO COURSE (ID, NAME, TEACHER, CREDIT)
    VALUES (1, 'Math', 'Mr. Smith', 3),
           (2, 'English', 'Ms. Johnson', 4),
           (3, 'Science', 'Dr. Brown', 3),
           (4, 'History', 'Mrs. Davis', 4),
           (5, 'Art', 'Mr. Wilson', 2),
           (6, 'Music', 'Ms. Thompson', 2),
           (7, 'Physical Education', 'Coach Johnson', 1),
           (8, 'Computer Science', 'Mr. Anderson', 3),
           (9, 'Geography', 'Mrs. Martinez', 2),
           (10, 'Foreign Language', 'Ms. Lee', 4);
    
    INSERT INTO STUDENT_COURSE (ID, STUDENT_ID, COURSE_ID)
    VALUES (1, 1, 1),
           (2, 1, 2),
           (3, 2, 3),
           (4, 2, 4),
           (5, 3, 5),
           (6, 3, 6),
           (7, 4, 7),
           (8, 4, 8),
           (9, 5, 9),
           (10, 5, 10);
    
    INSERT INTO SCORE (ID, STUDENT_ID, COURSE_ID, SCORE)
    VALUES (1, 1, 1, 85),
           (2, 1, 2, 90),
           (3, 2, 3, 92),
           (4, 2, 4, 88),
           (5, 3, 5, 95),
           (6, 3, 6, 87),
           (7, 4, 7, 80),
           (8, 4, 8, 92),
           (9, 5, 9, 78),
           (10, 5, 10, 85);

## 查询学生John的各科目成绩,要求展示学生姓名、科目名称、科目分数。 ##

--查询学生John的各科目成绩,要求展示学生姓名、科目名称、科目分数。
    SELECT STUDENT.NAME, COURSE.NAME, SCORE.SCORE
    FROM STUDENT
    JOIN SCORE ON STUDENT.ID = SCORE.STUDENT_ID
    JOIN COURSE ON SCORE.COURSE_ID = COURSE.ID
    WHERE STUDENT.NAME = 'John';

## 查询总成绩排名班级前十的同学 ##

--查询总成绩排名班级前十的同学
    SELECT STUDENT.NAME, SUM(SCORE.SCORE) AS TOTAL_SCORE
    FROM STUDENT
    JOIN SCORE ON STUDENT.ID = SCORE.STUDENT_ID
    GROUP BY STUDENT.NAME
    ORDER BY TOTAL_SCORE DESC
    LIMIT 10;