数据库系统 第10节 视图 (View)

视图在数据库管理系统（DBMS）中是一个非常有用的功能，它允许你定义一个SQL查询的结果集作为一张虚拟的表。视图本身不包含实际的数据行，而是代表了数据的一个逻辑视图。当你对视图执行查询时，数据库会实时地运行定义视图的查询语句来生成结果。

视图的优点：

1. 简化复杂查询：视图可以被用来简化复杂的查询，使得用户不需要了解底层数据表的结构和关系，只需要与视图交互即可。
2. 数据安全：视图可以作为一种安全机制使用，通过创建只显示某些列或者特定行的视图，可以防止未经授权的用户访问敏感数据。
3. 数据保护：通过视图，可以控制哪些用户可以更新哪些数据。例如，你可以创建一个视图，让用户只能更新某个字段而不能看到其他字段的内容。
4. 一致性：视图可以确保从多个表中获取的数据的一致性。如果需要对多个表进行相同的修改，只需要更改视图的定义即可。

案例分析

假设我们有一个学校数据库系统，其中包含了三个表：Students、Courses 和 Enrollments。这些表之间的关系如下：

• Students 表：包含学生的个人信息。
• Courses 表：包含课程的信息。
• Enrollments 表：记录学生报名的课程信息。

表结构示例：

• Students

  • StudentID (int)
  • Name (varchar)
  • Age (int)
  • Gender (char)

• Courses

  • CourseID (int)
  • Title (varchar)
  • Credits (int)

• Enrollments

  • EnrollmentID (int)
  • StudentID (int)
  • CourseID (int)
  • Grade (int)

创建视图

假设我们想要创建一个视图，该视图展示每个学生的姓名、年龄以及他们所选课程的名称和学分。我们可以创建一个名为 StudentCourseInfo 的视图。

CREATE VIEW StudentCourseInfo AS
SELECT s.Name, s.Age, c.Title, c.Credits
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;
1
2
3
4
5

使用视图

现在我们可以通过查询这个视图来获取所需的信息，而不需要关心底层表的结构和连接逻辑。

SELECT * FROM StudentCourseInfo;
1

数据安全

为了保证数据安全，我们可以创建另一个视图 StudentSafeInfo，仅展示学生的姓名和年龄，而不展示学生的学号或其他敏感信息。

CREATE VIEW StudentSafeInfo AS
SELECT Name, Age
FROM Students;
1
2
3

这样，我们就可以授予某些用户对 StudentSafeInfo 视图的访问权限，而不必担心他们能够查看到学生的学号等敏感信息。

通过这些例子，我们可以看到视图如何帮助简化查询、提供数据安全和维护数据的一致性。

让我们继续使用之前的学校数据库系统作为例子，但这次我们将关注于不同的需求和场景，以进一步说明视图的应用。

案例 2: 学生成绩统计视图

假设我们需要创建一个视图，用于展示每个学生的总学分和平均成绩。这将有助于教师和管理人员快速了解学生的整体表现。

创建视图

我们可以创建一个视图 StudentGradeSummary，它将包含每个学生的姓名、年龄、总学分和平均成绩。

CREATE VIEW StudentGradeSummary AS
SELECT s.Name,
       s.Age,
       SUM(c.Credits) AS TotalCredits,
       AVG(e.Grade) AS AverageGrade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
GROUP BY s.Name, s.Age;
1
2
3
4
5
6
7
8
9

使用视图

通过这个视图，教师和管理人员可以轻松查询每个学生的成绩概况。

SELECT * FROM StudentGradeSummary;
1

案例 3: 教师的课程列表视图

假设我们还有一张表 Teachers，包含教师的信息。我们需要创建一个视图 TeacherCourses，用于显示每位教师教授的所有课程。

表结构示例：

• Teachers
  • TeacherID (int)
  • Name (varchar)

假设我们还有一个表 Teaches，记录教师教授的课程。

• Teaches
  • TeacherID (int)
  • CourseID (int)

创建视图

CREATE VIEW TeacherCourses AS
SELECT t.Name AS TeacherName,
       c.Title AS CourseTitle,
       c.Credits AS CourseCredits
FROM Teachers t
JOIN Teaches tc ON t.TeacherID = tc.TeacherID
JOIN Courses c ON tc.CourseID = c.CourseID;
1
2
3
4
5
6
7

使用视图

使用这个视图，我们可以很容易地查询某位教师教授的所有课程及其学分。

SELECT * FROM TeacherCourses WHERE TeacherName = 'Dr. Smith';
1

案例 4: 课程注册统计视图

最后，假设我们需要一个视图来展示每门课程的注册人数。这将有助于教务处更好地管理课程资源。

创建视图

CREATE VIEW CourseEnrollmentStats AS
SELECT c.Title AS CourseTitle,
       COUNT(e.StudentID) AS NumberOfStudents
FROM Courses c
JOIN Enrollments e ON c.CourseID = e.CourseID
GROUP BY c.Title;
1
2
3
4
5
6

使用视图

使用这个视图，教务处可以查看每门课程的注册情况。

SELECT * FROM CourseEnrollmentStats;
1

通过这些案例，我们可以看到视图在不同场景下的应用，包括简化复杂查询、提供数据安全以及维护数据的一致性和完整性。视图不仅简化了数据访问，而且提高了应用程序的效率和安全性。

接下来，我们将继续构建一些新的案例来展示视图的不同用途。

案例 5: 学生选课建议视图

假设我们需要创建一个视图来帮助学生选择他们可能感兴趣的课程。这将基于学生的已选课程来推荐相关或互补的课程。

创建视图

为了实现这一目标，我们可以创建一个视图 CourseRecommendations，该视图将基于学生已选课程的类别或主题来推荐其他课程。

CREATE VIEW CourseRecommendations AS
SELECT s.Name AS StudentName,
       c2.Title AS RecommendedCourseTitle,
       c2.Credits AS RecommendedCourseCredits
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
JOIN Courses c2 ON c.Category = c2.Category AND c.CourseID <> c2.CourseID
GROUP BY s.Name, c2.Title, c2.Credits;
1
2
3
4
5
6
7
8
9

使用视图

使用这个视图，学生可以查询与他们已选课程相关的推荐课程。

SELECT * FROM CourseRecommendations WHERE StudentName = 'John Doe';
1

案例 6: 学生课程完成进度视图

假设我们需要一个视图来跟踪学生完成课程的情况，这将有助于学生和教师了解学生的课程进度。

创建视图

我们可以创建一个视图 StudentProgress，它将展示每个学生已完成课程的学分总数和剩余需要完成的学分数。

CREATE VIEW StudentProgress AS
SELECT s.Name AS StudentName,
       SUM(c.Credits) AS CompletedCredits,
       (SELECT SUM(Credits) FROM Courses) - SUM(c.Credits) AS RemainingCredits
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
GROUP BY s.Name;
1
2
3
4
5
6
7
8

使用视图

使用这个视图，学生可以查询他们的课程完成进度。

SELECT * FROM StudentProgress WHERE StudentName = 'Jane Doe';
1

案例 7: 高级课程注册资格视图

假设我们需要一个视图来确定哪些学生有资格注册高级课程。这将基于学生的平均成绩和已修课程数量。

创建视图

我们可以创建一个视图 AdvancedCourseEligibility，它将列出符合特定条件的学生，比如平均成绩超过某个阈值且已完成一定数量的课程。

CREATE VIEW AdvancedCourseEligibility AS
SELECT s.Name AS StudentName,
       AVG(e.Grade) AS AverageGrade,
       COUNT(e.CourseID) AS NumberOfCoursesCompleted
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
GROUP BY s.Name
HAVING AVG(e.Grade) >= 3.5 AND COUNT(e.CourseID) >= 5;
1
2
3
4
5
6
7
8

使用视图

使用这个视图，我们可以查询符合高级课程注册资格的学生名单。

SELECT * FROM AdvancedCourseEligibility;
1

通过这些案例，我们可以看到视图如何帮助我们简化复杂的查询逻辑，同时为用户提供定制化的数据视图。视图不仅可以提高数据的安全性，还可以增强数据的可读性和可用性。