PostgreSQL教程FG020-PG基础入门综合练习：从建库到数据查询全流程

本文档风哥主要介绍PostgreSQL教程020相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

内容简介：本文是PostgreSQL基础入门篇的综合练习，通过创建一个学生管理系统，演示从数据库创建、表设计、数据插入到数据查询的完整流程。风哥教程参考PostgreSQL官方文档Data Definition、Data Manipulation、Queries等内容，整合前面学习的所有基础知识，帮助读者巩固和应用PostgreSQL的核心技能。

Part01-基础概念与理论知识

1.1 综合练习概念

综合练习是将前面学习的各个知识点整合起来，完成一个完整的数据库应用场景。通过综合练习，可以加深对PostgreSQL基础知识的理解，掌握从数据库设计到数据查询的完整流程，提高实际应用能力。更多视频教程www.fgedu.net.cn

1.2 系统设计要点

一个良好的数据库系统设计需要考虑以下要点：

数据模型设计：合理设计表结构和关系
数据完整性：使用主键、外键、约束等保证数据质量
性能优化：合理使用索引和优化查询语句
扩展性：考虑系统的未来发展和数据增长
安全性：设置适当的权限和访问控制

1.3 练习目标与要求

本次综合练习的目标是创建一个学生管理系统，实现以下功能：

创建数据库和相关表结构
插入测试数据
实现基础查询功能
实现连接查询功能
实现聚合查询功能
实现高级查询功能

Part02-生产环境规划与建议

2.1 数据库规划

风哥教程针对风哥教程针对风哥教程针对生产环境建议：
– 数据库名称应具有意义，便于识别和管理
– 为数据库设置合适的字符集和排序规则
– 考虑数据库的备份和恢复策略
– 监控数据库的性能和资源使用情况

2.2 表结构规划

风哥教程针对风哥教程针对风哥教程针对生产环境建议：
– 表名和字段名应使用有意义的名称，遵循命名规范
– 选择合适的数据类型，避免使用过大的数据类型
– 为表设置主键，确保记录的唯一性
– 使用外键维护表之间的关系
– 为常用查询字段创建索引

2.3 权限规划

风哥教程针对风哥教程针对风哥教程针对生产环境建议：
– 采用最小权限原则，只授予必要的权限
– 为不同的用户设置不同的角色
– 定期审查和更新权限设置
– 记录权限变更日志

Part03-生产环境项目实施方案

3.1 数据库创建

首先，我们需要创建一个学生管理系统的数据库。学习交流加群风哥微信: itpux-com

from oracle:www.itpux.com

$ psql -d pgsql -U pgsql
Password for fgedu pgsql: psql (18.3)
Type “help” for help.

postgres=#

— 创建数据库
CREATE DATABASE fgedu_student_db WITH OWNER pgsql ENCODING ‘UTF8’;

CREATE DATABASE

— 连接到新创建的数据库
\c fgedu_student_db
You are now connected to fgedudb “fgedu_student_db” as fgedu “postgres”.

3.2 表结构创建

接下来，我们需要创建学生管理系统的表结构，包括学生表、课程表和成绩表。

— 创建学生表
CREATE TABLE fgedu_students (
student_id SERIAL PRIMARY KEY,
student_name VARCHAR(50) NOT NULL,
gender VARCHAR(10) NOT NULL,
birth_date DATE NOT NULL,
class VARCHAR(20) NOT NULL,
admission_date DATE NOT NULL
);

CREATE TABLE fgedu_– 创建课程表
CREATE TABLE fgedu_courses (
course_id SERIAL PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
credit INTEGER NOT NULL,
department VARCHAR(50) NOT NULL
);

CREATE TABLE fgedu_– 创建成绩表
CREATE TABLE fgedu_scores (
score_id SERIAL PRIMARY KEY,
student_id INTEGER NOT NULL REFERENCES fgedu_students(student_id),
course_id INTEGER NOT NULL REFERENCES fgedu_courses(course_id),
score NUMERIC(5, 2) NOT NULL,
exam_date DATE NOT NULL
);

3.3 数据插入

现在，我们需要向表中插入测试数据。

— 插入学生数据
INSERT INTO fgedu_students (student_name, gender, birth_date, class, admission_date)
VALUES (‘风哥1号’, ‘男’, ‘2005-03-15’, ‘计算机1班’, ‘2023-09-01’),
(‘风哥2号’, ‘男’, ‘2005-05-20’, ‘计算机1班’, ‘2023-09-01’),
(‘王五’, ‘女’, ‘2005-08-10’, ‘计算机1班’, ‘2023-09-01’),
(‘赵六’, ‘男’, ‘2005-11-25’, ‘计算机2班’, ‘2023-09-01’),
(‘钱七’, ‘女’, ‘2005-01-18’, ‘计算机2班’, ‘2023-09-01’),
(‘孙八’, ‘男’, ‘2005-07-05’, ‘计算机2班’, ‘2023-09-01’),
(‘周九’, ‘女’, ‘2005-04-30’, ‘计算机3班’, ‘2023-09-01’),
(‘吴十’, ‘男’, ‘2005-09-12’, ‘计算机3班’, ‘2023-09-01’);

INSERT 0 8

— 插入课程数据
INSERT INTO fgedu_courses (course_name, credit, department)
VALUES (‘高等数学’, 4, ‘数学学院’),
(‘大学英语’, 3, ‘外国语学院’),
(‘计算机导论’, 2, ‘计算机学院’),
(‘C语言程序设计’, 4, ‘计算机学院’),
(‘数据结构’, 4, ‘计算机学院’),
(‘数据库原理’, 3, ‘计算机学院’),
(‘操作系统’, 4, ‘计算机学院’),
(‘计算机网络’, 3, ‘计算机学院’);

INSERT 0 8

— 插入成绩数据
INSERT INTO fgedu_scores (student_id, course_id, score, exam_date)
VALUES (1, 1, 85.5, ‘2024-01-15’),
(1, 2, 78.0, ‘2024-01-16’),
(1, 3, 92.5, ‘2024-01-17’),
(1, 4, 88.0, ‘2024-01-18’),
(2, 1, 79.0, ‘2024-01-15’),
(2, 2, 82.5, ‘2024-01-16’),
(2, 3, 85.0, ‘2024-01-17’),
(2, 4, 90.5, ‘2024-01-18’),
(3, 1, 91.0, ‘2024-01-15’),
(3, 2, 88.5, ‘2024-01-16’),
(3, 3, 95.0, ‘2024-01-17’),
(3, 4, 92.0, ‘2024-01-18’),
(4, 1, 76.5, ‘2024-01-15’),
(4, 2, 74.0, ‘2024-01-16’),
(4, 3, 81.5, ‘2024-01-17’),
(4, 4, 79.0, ‘2024-01-18’),
(5, 1, 88.0, ‘2024-01-15’),
(5, 2, 92.5, ‘2024-01-16’),
(5, 3, 89.0, ‘2024-01-17’),
(5, 4, 94.5, ‘2024-01-18’),
(6, 1, 72.5, ‘2024-01-15’),
(6, 2, 77.0, ‘2024-01-16’),
(6, 3, 84.0, ‘2024-01-17’),
(6, 4, 81.5, ‘2024-01-18’),
(7, 1, 84.5, ‘2024-01-15’),
(7, 2, 86.0, ‘2024-01-16’),
(7, 3, 90.0, ‘2024-01-17’),
(7, 4, 87.5, ‘2024-01-18’),
(8, 1, 90.5, ‘2024-01-15’),
(8, 2, 83.0, ‘2024-01-16’),
(8, 3, 93.5, ‘2024-01-17’),
(8, 4, 91.0, ‘2024-01-18’);

INSERT 0 32

Part04-生产案例与实战讲解

4.1 基础查询练习

基础查询是数据库操作的核心，包括简单的SELECT、WHERE、ORDER BY等语句。

— 查询所有学生信息
SELECT * FROM fgedu_students;
student_id | student_name | gender | birth_date | class | admission_date
————+————–+——–+————+———–+—————-+
1 | 风哥1号 | 男 | 2005-03-15 | 计算机1班 | 2023-09-01
2 | 风哥2号 | 男 | 2005-05-20 | 计算机1班 | 2023-09-01
3 | 王五 | 女 | 2005-08-10 | 计算机1班 | 2023-09-01
4 | 赵六 | 男 | 2005-11-25 | 计算机2班 | 2023-09-01
5 | 钱七 | 女 | 2005-01-18 | 计算机2班 | 2023-09-01
6 | 孙八 | 男 | 2005-07-05 | 计算机2班 | 2023-09-01
7 | 周九 | 女 | 2005-04-30 | 计算机3班 | 2023-09-01
8 | 吴十 | 男 | 2005-09-12 | 计算机3班 | 2023-09-01
(8 rows)

— 查询计算机1班的学生信息
SELECT * FROM fgedu_students WHERE class = ‘计算机1班’;
student_id | student_name | gender | birth_date | class | admission_date
————+————–+——–+————+———–+—————-+
1 | 风哥1号 | 男 | 2005-03-15 | 计算机1班 | 2023-09-01
2 | 风哥2号 | 男 | 2005-05-20 | 计算机1班 | 2023-09-01
3 | 王五 | 女 | 2005-08-10 | 计算机1班 | 2023-09-01
(3 rows)

— 查询成绩大于90分的记录
SELECT * FROM fgedu_scores WHERE score > 90.0;
score_id | student_id | course_id | score | exam_date
———-+————+———–+——-+———–+
3 | 1 | 3 | 92.5 | 2024-01-17
11 | 3 | 3 | 95.0 | 2024-01-17
12 | 3 | 4 | 92.0 | 2024-01-18
20 | 5 | 4 | 94.5 | 2024-01-18
27 | 7 | 3 | 90.0 | 2024-01-17
31 | 8 | 3 | 93.5 | 2024-01-17
32 | 8 | 4 | 91.0 | 2024-01-18
(7 rows)

4.2 连接查询练习

连接查询用于查询多个表中的数据，通过表之间的关系获取更完整的信息。学习交流加群风哥QQ113257174

— 查询学生的成绩信息，包括学生姓名、课程名称和成绩
SELECT
s.student_name,
c.course_name,
sc.score
FROM fgedu_students s
JOIN fgedu_scores sc ON s.student_id = sc.student_id
JOIN fgedu_courses c ON sc.course_id = c.course_id;
student_name | course_name | score
————–+—————–+——-+
风哥1号 | 高等数学 | 85.5
风哥1号 | 大学英语 | 78.0
风哥1号 | 计算机导论 | 92.5
风哥1号 | C语言程序设计 | 88.0
风哥2号 | 高等数学 | 79.0
风哥2号 | 大学英语 | 82.5
风哥2号 | 计算机导论 | 85.0
风哥2号 | C语言程序设计 | 90.5
王五 | 高等数学 | 91.0
王五 | 大学英语 | 88.5
王五 | 计算机导论 | 95.0
王五 | C语言程序设计 | 92.0
赵六 | 高等数学 | 76.5
赵六 | 大学英语 | 74.0
赵六 | 计算机导论 | 81.5
赵六 | C语言程序设计 | 79.0
钱七 | 高等数学 | 88.0
钱七 | 大学英语 | 92.5
钱七 | 计算机导论 | 89.0
钱七 | C语言程序设计 | 94.5
孙八 | 高等数学 | 72.5
孙八 | 大学英语 | 77.0
孙八 | 计算机导论 | 84.0
孙八 | C语言程序设计 | 81.5
周九 | 高等数学 | 84.5
周九 | 大学英语 | 86.0
周九 | 计算机导论 | 90.0
周九 | C语言程序设计 | 87.5
吴十 | 高等数学 | 90.5
吴十 | 大学英语 | 83.0
吴十 | 计算机导论 | 93.5
吴十 | C语言程序设计 | 91.0
(32 rows)

4.3 聚合查询练习

聚合查询用于对数据进行统计计算，如计数、求和、平均值等。

— 计算每个学生的平均成绩
SELECT
s.student_name,
AVG(sc.score) AS avg_score
FROM fgedu_students s
JOIN fgedu_scores sc ON s.student_id = sc.student_id
GROUP BY s.student_name
ORDER BY avg_score DESC;
student_name | avg_score
————–+——————–
王五 | 91.6250000000000000
钱七 | 91.0000000000000000
吴十 | 89.5000000000000000
风哥1号 | 86.0000000000000000
周九 | 87.0000000000000000
风哥2号 | 84.2500000000000000
孙八 | 78.7500000000000000
赵六 | 77.7500000000000000
(8 rows)

— 计算每个课程的平均成绩、最高成绩和最低成绩
SELECT
c.course_name,
AVG(sc.score) AS avg_score,
MAX(sc.score) AS max_score,
MIN(sc.score) AS min_score
FROM fgedu_courses c
JOIN fgedu_scores sc ON c.course_id = sc.course_id
GROUP BY c.course_name
ORDER BY avg_score DESC;
course_name | avg_score | max_score | min_score
—————–+——————–+———–+———–+
计算机导论 | 88.6875000000000000 | 95.0 | 81.5
C语言程序设计 | 88.0000000000000000 | 94.5 | 79.0
大学英语 | 82.6875000000000000 | 92.5 | 74.0
高等数学 | 83.4375000000000000 | 91.0 | 72.5
(4 rows)

— 计算每个班级的学生人数
SELECT class, COUNT(*) AS student_count FROM fgedu_students GROUP BY class;
class | student_count
———–+—————+
计算机1班 | 3
计算机2班 | 3
计算机3班 | 2
(3 rows)

— 计算每个班级的平均成绩
SELECT
s.class,
AVG(sc.score) AS avg_class_score
FROM fgedu_students s
JOIN fgedu_scores sc ON s.student_id = sc.student_id
GROUP BY s.class
ORDER BY avg_class_score DESC;
class | avg_class_score
———–+——————–
计算机1班 | 87.9583333333333333
计算机3班 | 88.2500000000000000
计算机2班 | 82.5000000000000000
(3 rows)

4.4 高级查询练习

高级查询包括子查询、窗口函数、CTE等复杂查询方法。

— 使用子查询查询平均成绩最高的学生
SELECT
s.student_name,
AVG(sc.score) AS avg_score
FROM fgedu_students s
JOIN fgedu_scores sc ON s.student_id = sc.student_id
GROUP BY s.student_name
HAVING AVG(sc.score) = (
SELECT MAX(avg_score) FROM (
SELECT AVG(score) AS avg_score FROM fgedu_scores GROUP BY student_id
) AS student_avg_scores
);
student_name | avg_score
————–+——————–
王五 | 91.6250000000000000
(1 row)

— 使用窗口函数查询每个学生的成绩排名
SELECT
s.student_name,
c.course_name,
sc.score,
RANK() OVER (PARTITION BY c.course_id ORDER BY sc.score DESC) AS rank
FROM fgedu_students s
JOIN fgedu_scores sc ON s.student_id = sc.student_id
JOIN fgedu_courses c ON sc.course_id = c.course_id;
student_name | course_name | score | rank
————–+—————–+——-+——+
王五 | 高等数学 | 91.0 | 1
吴十 | 高等数学 | 90.5 | 2
钱七 | 高等数学 | 88.0 | 3
风哥1号 | 高等数学 | 85.5 | 4
周九 | 高等数学 | 84.5 | 5
风哥2号 | 高等数学 | 79.0 | 6
赵六 | 高等数学 | 76.5 | 7
孙八 | 高等数学 | 72.5 | 8
钱七 | 大学英语 | 92.5 | 1
王五 | 大学英语 | 88.5 | 2
周九 | 大学英语 | 86.0 | 3
风哥2号 | 大学英语 | 82.5 | 4
吴十 | 大学英语 | 83.0 | 4
孙八 | 大学英语 | 77.0 | 6
风哥1号 | 大学英语 | 78.0 | 6
赵六 | 大学英语 | 74.0 | 8
王五 | 计算机导论 | 95.0 | 1
吴十 | 计算机导论 | 93.5 | 2
风哥1号 | 计算机导论 | 92.5 | 3
钱七 | 计算机导论 | 89.0 | 4
周九 | 计算机导论 | 90.0 | 5
风哥2号 | 计算机导论 | 85.0 | 6
孙八 | 计算机导论 | 84.0 | 7
赵六 | 计算机导论 | 81.5 | 8
钱七 | C语言程序设计 | 94.5 | 1
王五 | C语言程序设计 | 92.0 | 2
吴十 | C语言程序设计 | 91.0 | 3
风哥2号 | C语言程序设计 | 90.5 | 4
风哥1号 | C语言程序设计 | 88.0 | 5
周九 | C语言程序设计 | 87.5 | 6
孙八 | C语言程序设计 | 81.5 | 7
赵六 | C语言程序设计 | 79.0 | 8
(32 rows)

— 使用CTE查询每个学生的总分和平均成绩
WITH student_scores AS (
SELECT
student_id,
SUM(score) AS total_score,
AVG(score) AS avg_score
FROM fgedu_scores
GROUP BY student_id
)
SELECT
s.student_name,
s.class,
ss.total_score,
ss.avg_score
FROM fgedu_students s
JOIN student_scores ss ON s.student_id = ss.student_id
ORDER BY ss.total_score DESC;
student_name | class | total_score | avg_score
————–+———–+————-+——————–
王五 | 计算机1班 | 366.5 | 91.6250000000000000
钱七 | 计算机2班 | 364.0 | 91.0000000000000000
吴十 | 计算机3班 | 358.0 | 89.5000000000000000
周九 | 计算机3班 | 348.0 | 87.0000000000000000
风哥1号 | 计算机1班 | 344.0 | 86.0000000000000000
风哥2号 | 计算机1班 | 337.0 | 84.2500000000000000
孙八 | 计算机2班 | 315.0 | 78.7500000000000000
赵六 | 计算机2班 | 311.0 | 77.7500000000000000
(8 rows)

— 查询不及格的学生信息（成绩低于60分）
SELECT
s.student_name,
s.class,
c.course_name,
sc.score
FROM fgedu_students s
JOIN fgedu_scores sc ON s.student_id = sc.student_id
JOIN fgedu_courses c ON sc.course_id = c.course_id
WHERE sc.score < 60.0; student_name | class | course_name | score --------------+-------+-------------+-------+ (0 rows)

Part05-风哥经验总结与分享

风哥提示：通过本次综合练习，我们完成了从数据库创建、表设计、数据插入到数据查询的完整流程，巩固了PostgreSQL的基础知识。在实际应用中，数据库设计和查询优化是非常重要的，需要不断学习和实践才能掌握。

1. 数据库设计是整个系统的基础，合理的表结构设计可以提高数据的完整性和查询效率。

2. 数据完整性是数据库设计的重要原则，使用主键、外键、约束等可以保证数据的准确性和一致性。

3. 查询是数据库操作的核心，掌握各种查询方法可以满足不同的业务需求。

4. 连接查询是多表查询的基础，通过表之间的关系可以获取更完整的信息。

5. 聚合查询用于数据统计分析，可以帮助了解数据的分布和趋势。

6. 高级查询如子查询、窗口函数、CTE等可以实现更复杂的查询需求。

7. 性能优化是数据库应用的重要方面，合理使用索引和优化查询语句可以提高查询效率。

8. 持续学习和实践是掌握PostgreSQL的关键，通过实际项目可以加深对数据库知识的理解和应用。更多学习教程公众号风哥教程itpux_com

9. 数据库安全是不容忽视的，设置适当的权限和访问控制可以保护数据的安全。

10. 备份和恢复是数据库运维的重要工作，定期备份可以防止数据丢失。from PostgreSQL:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html