基于RDS PostgreSQL的表设计入门_云数据库 RDS(RDS)-阿里云帮助中心

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

场景说明

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

表设计

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

具体设计细节如下：

学生表（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)	成绩。

数据库建表操作步骤

创建RDS PostgreSQL实例。
创建账号和数据库。
设置白名单。
连接PostgreSQL实例。

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

学生表（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)
);

假设学生从前端程序选择了课程，老师录入了相关同学的成绩，相关数据传回后端数据库。在数据库中模拟插入如下测试数据。

-- 向学生表插入测试数据
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语句来连接students、enrollments和courses表。它通过学生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语句来连接students、enrollments和courses表。它通过学生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语句来连接students、enrollments和courses表。它通过学生ID（stu_id）和课程ID（cou_id）在enrollments表中进行连接。然后，使用GROUP BY子句按学生姓名进行分组，并使用SUM函数计算每个学生的总学分（如果一门课的成绩为C，将无法获得该门课的学分），使用ORDER BY子句按总学分从大到小进行排序。最后，使用LIMIT 3获取学分最高的三名学生数据。