Student Gradebook 使用连接
你将构建什么
数据库使用 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
创建表
创建 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
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
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
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
添加索引优化查询
为频繁查询的列创建索引,提升查询性能。索引是数据库性能优化的首要手段,但需注意写操作的开销。
代码-- 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.
相关资源
相关课程
- 连接
学习所有连接类型.
- Aggregations
学习 aggregate 函数.
相关术语
- INNER JOIN
Matching 行仅.
- LEFT JOIN
所有左 行.
相关术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
- INNER JOIN
Matching 行仅.
- LEFT JOIN
所有左 行.
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。