文档

基于RDS PostgreSQL的表设计入门

更新时间:

RDS PostgreSQL是先进的开源关系型数据库,对SQL规范兼容性强,支持多种数据类型,以及丰富的社区插件扩展。本文以介绍学生选课系统为例,演示如何根据业务场景,设计表结构,并在RDS PostgreSQL完成相关库表的创建。

场景说明

某学校为方便学生在线上快捷选课、学期末快速统计成绩,计划建立一个学生选课系统,该系统后端数据库采用阿里云RDS PostgreSQL数据库,针对此场景,需要设计数据库相关表结构,数据库表中需要存储学生信息、课程信息以及成绩信息。

表设计

根据业务场景,可以分别设计学生表、课程表以及选课表,E-R图如下:

image.png

具体设计细节如下:

  • 学生表(students)

    该表存储学生信息。每个学生在这个表中有一条记录,学生ID列(stu_id)用于进行学生与课程成绩的匹配。

    字段名

    字段类型

    字段说明

    stu_id

    SERIAL

    学号。

    stu_name

    VARCHAR(50)

    姓名。

    stu_gender

    VARCHAR(10)

    性别。

    stu_birth

    DATE

    出生日期。

    stu_dept

    VARCHAR(50)

    院系。

  • 课程表(courses)

    该表存储课程信息。每一门课程在这个表中有一条记录。课程有唯一的编号(cou_id)。

    字段名

    字段类型

    字段说明

    cou_id

    SERIAL

    课程编号。

    cou_name

    VARCHAR(100)

    课程名称。

    cou_teacher

    VARCHAR(50)

    授课老师。

    cou_credit

    INTEGER

    学分。

  • 选课表(enrollments)

    字段名

    字段类型

    字段说明

    en_id

    SERIAL

    选课记录编号。

    stu_id

    INTEGER

    学生学号,关联学生表(students)的学号(stu_id)。

    cou_id

    INTEGER

    课程编号,关联课程表(courses)的课程编号(cou_id)。

    grade

    VARCHAR(2)

    成绩。

数据库建表操作步骤

  1. 创建RDS PostgreSQL实例

  2. 创建账号和数据库

  3. 设置白名单

  4. 连接PostgreSQL实例

  5. 根据设计好的表结构创建表。

    • 学生表(students)

      CREATE TABLE students (
        stu_id SERIAL PRIMARY KEY,
        stu_name VARCHAR(50),
        stu_gender VARCHAR(10),
        stu_birth DATE,
        stu_dept VARCHAR(50)
      );
    • 课程表(courses)

      CREATE TABLE courses (
        cou_id SERIAL PRIMARY KEY,
        cou_name VARCHAR(100),
        cou_teacher VARCHAR(50),
        cou_credit INTEGER
      );
    • 选课表(enrollments)

      CREATE TABLE enrollments (
        en_id SERIAL PRIMARY KEY,
        stu_id INTEGER,
        cou_id INTEGER,
        grade VARCHAR(2),
        FOREIGN KEY (stu_id) REFERENCES students(stu_id),
        FOREIGN KEY (cou_id) REFERENCES courses(cou_id)
      );
  6. 假设学生从前端程序选择了课程,老师录入了相关同学的成绩,相关数据传回后端数据库。在数据库中模拟插入如下测试数据。

    -- 向学生表插入测试数据
    INSERT INTO students (stu_name, stu_gender, stu_birth, stu_dept) VALUES
      ('Zhangsan', 'Female', '2000-01-01', 'Computer Science'),
      ('Lisi', 'Male', '1999-05-10', 'Mathematics'),
      ('Wangwu', 'Male', '2001-08-20', 'Physics'),
      ('Zhaoliu', 'Female', '1998-03-15', 'Chemistry'),
      ('Sunqi', 'Female', '2002-11-30', 'Biology');
    
    -- 向课程表插入测试数据
    INSERT INTO courses (cou_name, cou_teacher, cou_credit) VALUES
      ('Database Systems', 'Professor Zhang', 3),
      ('Calculus', 'Professor Wang', 4),
      ('Computer Network', 'Professor Li', 4),
      ('Organic Chemistry', 'Professor Wang', 4),
      ('Biology Basics', 'Professor Zhang', 2);
    
    -- 向选课表插入测试数据
    INSERT INTO enrollments (stu_id, cou_id, grade) VALUES
      (1, 1,'A'),(1, 3,'B+'),(1, 5,'C'),
      (2, 1,'B+'),(2, 2,'A'),(2, 3,'B-'),
      (3, 2,'A-'),(3, 4,'A'),(3, 5,'B+'),
      (4, 3,'B'),(4, 4,'B-'),
      (5, 4,'C');

场景示例

  • 学期末,学生zhangsan需要在学生选课系统中查看自己选的每门课的成绩。

    SELECT courses.cou_name, enrollments.grade
    FROM students
    JOIN enrollments ON students.stu_id = enrollments.stu_id
    JOIN courses ON enrollments.cou_id = courses.cou_id
    WHERE students.stu_name = 'Zhangsan';

    返回结果如下:

         cou_name     | grade
    ------------------+-------
     Database Systems | A
     Computer Network | B+
     Biology Basics   | C
    (3 rows)
    说明

    该查询语句使用了JOIN语句来连接studentsenrollmentscourses表。它通过学生ID(stu_id)和课程ID(cou_id)在enrollments表中进行连接。然后,使用WHERE子句来筛选出学生姓名为Zhangsan的记录。

  • 老师Professor Zhang需要在学生选课系统中查询他教学的所有学生的成绩情况。

    SELECT students.stu_name, courses.cou_name, enrollments.grade
    FROM students
    JOIN enrollments ON students.stu_id = enrollments.stu_id
    JOIN courses ON enrollments.cou_id = courses.cou_id
    WHERE courses.cou_teacher = 'Professor Zhang';

    返回结果如下:

     stu_name |     cou_name     | grade
    ----------+------------------+-------
     Zhangsan | Database Systems | A
     Zhangsan | Biology Basics   | C
     Lisi     | Database Systems | B+
     Wangwu   | Biology Basics   | B+
    (4 rows)
    说明

    该查询语句使用了JOIN语句来连接studentsenrollmentscourses表。它通过学生ID(stu_id)和课程ID(cou_id)在enrollments表中进行连接。然后,使用WHERE子句来筛选出授课教师为Professor Zhang的记录。

  • 学校准备评选优秀学生,需要将所有学生的学分进行排名,前三名将获得优秀学生荣誉。

    SELECT students.stu_name, SUM(
    CASE
    WHEN enrollments.grade = 'C' THEN 0
    ELSE courses.cou_credit
    END
    ) AS total_credits
    FROM students
    JOIN enrollments ON students.stu_id = enrollments.stu_id
    JOIN courses ON enrollments.cou_id = courses.cou_id
    GROUP BY students.stu_name
    ORDER BY total_credits DESC
    LIMIT 3;

    返回结果如下:

     stu_name | total_credits
    ----------+---------------
     Lisi     |            11
     Wangwu   |            10
     Zhaoliu  |             8
    (3 rows)
    说明

    该查询语句使用了JOIN语句来连接studentsenrollmentscourses表。它通过学生ID(stu_id)和课程ID(cou_id)在enrollments表中进行连接。然后,使用GROUP BY子句按学生姓名进行分组,并使用SUM函数计算每个学生的总学分(如果一门课的成绩为C,将无法获得该门课的学分),使用ORDER BY子句按总学分从大到小进行排序。最后,使用LIMIT 3获取学分最高的三名学生数据。

  • 本页导读 (1)