Skip to content

Student Gradebook 使用连接

简单 约 20 分钟

你将构建什么

数据库使用 students, 课程, and grades 表, plus JOIN 查询那个 produce 完整 gradebook report showing 每个 student's grades 跨越课程.

学习目标

  • 创建 three related 表 (students, 课程, grades)
  • Use INNER JOIN 到组合 student and grade 数据
  • Use LEFT JOIN 到包含 students 使用否 grades
  • Compute 平均 grades 每 student

前置要求

  • Foreign 键 and 关系
  • 基础 SELECT 查询
  • 理解的 JOIN 概念

步骤

  1. 1

    创建表

    创建 students, 课程, and grades 表. grades 表是 junction 表链接 students 到课程使用 score.

    代码
    CREATE TABLE students (
      id   SERIAL PRIMARY KEY,
      name TEXT NOT NULL
    );
    
    CREATE TABLE courses (
      id   SERIAL PRIMARY KEY,
      name TEXT NOT NULL
    );
    
    CREATE TABLE grades (
      student_id  INTEGER REFERENCES students(id),
      course_id   INTEGER REFERENCES courses(id),
      score       NUMERIC(5,2),
      PRIMARY KEY (student_id, course_id)
    );

    预期结果

    Three 表是 创建使用 composite 主键 on grades.

  2. 2

    Insert sample 数据

    Insert students, 课程, and grades. Leave one student 不使用任何 grades 到测试 LEFT JOIN.

    代码
    INSERT INTO students (name) VALUES ('Ada'), ('Grace'), ('Alan');
    INSERT INTO courses (name) VALUES ('Math'), ('Physics');
    INSERT INTO grades VALUES
      (1, 1, 95.0), (1, 2, 88.5),
      (2, 1, 78.0);

    预期结果

    Three students, two 课程, and three grades 是 inserted. Alan 有否 grades.

  3. 3

    INNER JOIN: students 使用 grades

    编写 INNER JOIN 到列表每个 student's name 使用它们的课程 and score. Students 不使用 grades 是排除.

    代码
    SELECT s.name AS student, c.name AS course, g.score
    FROM grades g
    INNER JOIN students s ON g.student_id = s.id
    INNER JOIN courses c ON g.course_id = c.id
    ORDER BY s.name, c.name;

    预期结果

    Ada and Grace appear 使用它们的 grades. Alan 是排除 (否 grades).

  4. 4

    LEFT JOIN: 所有 students 使用平均

    编写 LEFT JOIN 使用 aggregation 到 show 每个 student's 平均 score, 包含 students 使用否 grades (NULL 平均).

    代码
    SELECT s.name, AVG(g.score) AS avg_score
    FROM students s
    LEFT JOIN grades g ON g.student_id = s.id
    GROUP BY s.name
    ORDER BY avg_score DESC NULLS LAST;

    预期结果

    所有 three students appear. Ada 有 highest 平均, Alan 有 NULL.

  5. 5

    添加索引优化查询

    为频繁查询的列创建索引,提升查询性能。索引是数据库性能优化的首要手段,但需注意写操作的开销。

    代码
    -- Create indexes on frequently filtered columns
    CREATE INDEX idx_users_email ON users(email);
    CREATE INDEX idx_orders_user_id ON orders(user_id);
    CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
    
    -- Verify the index is used
    EXPLAIN SELECT * FROM orders WHERE user_id = 42;

    预期结果

    EXPLAIN 输出显示使用 idx_orders_user_id 索引扫描而非全表扫描。

自己动手试试

你学到了什么

你 mastered INNER and LEFT 连接 in 真实-世界场景, 理解当 每个是 appropriate and 如何 组合连接使用 aggregation.

相关资源

相关课程

相关术语

相关术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误

← 返回主题