数据关联查询与视图

12002

本课介绍

本课将深入学习数据管理中的关键概念：数据关联查询和视图。我们将介绍关联查询的基本概念，包括INNER JOIN、LEFT JOIN和RIGHT JOIN的用法及应用场景，并通过示例理解多表查询的用法。此外，我们还会学习视图的定义、作用以及如何使用Navicat和SQL语句来创建视图。

1.关联查询基础：INNER JOIN, LEFT JOIN, RIGHT JOIN

在前面的课程中，我们已经学习了 SQL 语句的基本操作，包括增加、删除、修改和查询，但这些操作仅限于一张表。而在数据管理应用中，我们经常需要对多个表进行关联操作，以实现更复杂的数据处理。接下来，我们将学习能够实现多表关联查询的 SQL 语句： JOIN 。

JOIN 语句应用场景及常用类型

JOIN 通常用于关系型数据库中连接两个或多个表，通过表之间的共同字段将它们关联起来，进行关联查询。这种查询方式适用于以下场景：

• 多表关联查询：当数据分布在多个表中，并且这些表之间存在关系时，通过关联查询可以一次性获取多个表中的相关信息。

• 数据关联分析：用于分析数据之间的关联关系，例如，在成绩表中查找学生的成绩信息、查找某一学科参加考试的学生信息等。

• 数据整合：将存储在不同表中的数据整合在一起，形成更完整的数据视图。

JOIN 有多种类型，其中最常见的包括 INNER JOIN、LEFT JOIN 和 RIGHT JOIN，我们来看一下它们的用法和基本语法。

INNER JOIN, LEFT JOIN, RIGHT JOIN的用法

• INNER JOIN

INNER JOIN 用于查询两个表中共同字段匹配的记录。如果一个表中有一个记录，而另一个表中没有匹配的记录，那么这个记录不会出现在查询结果中。INNER JOIN 基本语法如下：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

在这里：

• SELECT column_name(s): 指定要从查询结果中显示的字段名。
• FROM table1: 指定要查询的第一个表。
• INNER JOIN table2: 指定要与第一个表进行内连接的第二个表。
• ON：ON子句用于指定连接条件。
• table1.column_name = table2.column_name: 两个表之间的共同字段。

参考资料：SQL INNER JOIN

• LEFT JOIN

LEFT JOIN 是左连接的意思。当两个表关联时，它关注于查询左表，会显示左表中所有匹配的数据。如果右表有匹配的数据，它会显示出来；如果没有匹配的数据，它会显示 NULL。LEFT JOIN 基本语法如下：

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

在这里：

• SELECT column_name(s): 指定要从查询结果中显示的字段名。
• FROM table1: 指定要查询的左表。
• LEFT JOIN table2: 指定要与左表连接的右表。
• ON：ON子句用于指定连接条件。
• table1.column_name = table2.column_name: 两个表之间的共同字段。

参考资料：SQL LEFT JOIN

• RIGHT JOIN

RIGHT JOIN 是右连接，与LEFT JOIN类似，但它关注于查询右表，会显示右表中所有匹配的数据。如果左表有匹配的数据，它会显示出来；如果没有匹配的数据，它会显示 NULL。RIGHT JOIN 基本语法如下：

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

在这里：

• SELECT column_name(s): 指定要从查询结果中显示的字段名。
• FROM table1: 指定要查询的左表。
• LEFT JOIN table2: 指定要与左表连接的右表。
• ON：ON子句用于指定连接条件。
• table1.column_name = table2.column_name: 两个表之间的共同字段。

参考资料：SQL RIGHT JOIN

在使用LEFT JOIN和RIGHT JOIN时，请确保将左表放在FROM子句后面，右表放在JOIN连接子句后面，以确保连接的准确性和结果的正确性。

接下来，我们将通过具体的例子进一步加深对INNER JOIN、LEFT JOIN和RIGHT JOIN的理解。

2.多表查询的实际应用示例：查找学生成绩

在前面的学习中，已经创建了两张数据表：一个是学生信息表"student"，另一个是成绩表"sc"。这两张表有共同的字段：SId和Sname。因此，我们可以通过JOIN操作，可以将两个表关联起来，根据不同的需求来查询学生的成绩。

学生表（student）

成绩表（sc）

参加考试的学生(INNER JOIN)

如果要查找参加考试的学生及成绩，可以使用 INNER JOIN 。通过共同字段SId，把学生表和成绩表关联起来，只显示两个表中有匹配记录的学生数据。复制下面的查询语句，在Navicat中运行：

SELECT student.SId, student.Sname, sc.Cname, sc.score
FROM student
INNER JOIN sc
ON student.SId = sc.SId;

这条查询显示出所有参加考试的学生的学号、姓名、考试科目和成绩，结果如下：

没有参加考试的学生(LEFT JOIN)

如果要查找没有参加考试的学生，可以使用 LEFT JOIN 。我们以学生表作为左表，通过共同字段SId，来查询出没有参加考试的学生数据。如果在成绩表中没有成绩的数据，就会显示NULL。复制下面的查询语句，在Navicat中运行：

SELECT student.SId, student.Sname, sc.Cname, sc.score
FROM student
LEFT JOIN sc
ON student.SId = sc.SId;

这条查询显示出所有学生的学号、姓名，参加考试的学生会看到科目和成绩的数据，没有参加考试的学生科目和成绩信息将显示为 NULL，结果如下：

录入成绩时输入错误学号的学生(RIGHT JOIN)

如果录入考试成绩时不小心输入了错误的学号，就需要把这些错误的数据查询出来。这种情况可以使用 RIGHT JOIN 。我们仍以学生表作为左表，通过共同字段SId，查询出成绩表中有成绩，但SId与学生表不一致的数据。复制下面的查询语句，在Navicat中运行：

SELECT student.SId, student.Sname, sc.Cname, sc.score
FROM student
RIGHT JOIN sc
ON student.SId = sc.SId;

这条查询显示所有参加考试的学生成绩，但学生表中没有对应记录的学生，他们的学号、姓名的数据为NULL。

通过以上三个示例，我们更深入地了解了查询语句中 JOIN 的用法。在编写查询时，一定要确保ON子句中指定的连接条件是准确的；此外，在使用 LEFT JOIN 和 RIGHT JOIN 时，要特别注意表的位置。放置表的顺序会影响到查询结果，尤其是在处理左表或右表中没有匹配记录的情况下。正确的表位置可以确保所需数据被正确地匹配和显示，避免结果的不准确性。

3.视图的定义和使用

视图的概念和作用

通过使用 JOIN 语句，我们可以将多个表连接起来，将复杂的数据简化为我们所需的结果。那么，能不能把这些复杂的查询语句保存下来，当做一个表随时使用呢？当然可以。在数据管理中，有一个常用的工具，叫做视图。

视图是一个虚拟的表，你可以将复杂的 SQL 查询保存为视图，然后像查询一个普通表一样查询这个视图。这种做法可以简化复杂的 SQL 操作，能够帮助我们更方便地处理复杂的数据查询和管理任务。以下是一些视图的常见应用场景：

• 简化复杂查询：通过创建视图，可以将复杂的SQL查询语句抽象成一个简单的视图，使得用户可以通过查询视图来获取复杂查询的结果，而无需了解底层的复杂逻辑。

• 限制用户访问权限：通过视图，数据库管理员可以仅向用户提供访问特定列或行的权限，而不必直接访问底层表。这样可以有效地控制用户对数据的访问权限，提高数据安全性。

• 提供定制的数据视图：不同用户可能对相同的数据有不同的需求。通过创建视图，可以为不同用户或应用程序提供定制的数据视图，仅包含他们关心的数据，提高数据的可读性和有针对性。

• 隐藏表结构变化：如果底层表的结构发生变化，而对外提供的接口仍然是视图，可以避免对外部应用程序的影响。视图可以充当一个中间层，隐藏底层表的变化。

比如前面的示例中，我们用INNER JOIN查询参加考试的学生及成绩，就可以把这个查询存成一个视图。这样，无论学生表或成绩表中的数据如何变化，只需查询这个视图，就能够获取到最新的、符合查询条件的结果。下面，我们就来学习一下如何创建视图。

使用sql语句创建视图

我们可以使用 SQL 语句来创建视图，语法如下：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

在这里：

• CREATE VIEW view_name ：CREATE VIEW 是 SQL 的语法，用于创建视图。 view_name是创建的视图名称。
• AS：在SQL中常用于给表、列或视图等命名别名，在创建视图时，AS 后面的查询语句会被保存为指定的视图名称，即 view_name。

我们可以使用这段 SQL语句创建一个简单的视图，查询考试成绩分数高于60分的学生：

1.复制下面的SQL语句，在Navicat中运行，看到结果如下

CREATE VIEW sc_over_60 AS
SELECT *FROM SC
WHERE score >60;

2.单击”视图”，右键选择”刷新”

3.我们看到名为sc_over_60视图创建成功，打开后显示出考试成绩分数高于60分的学生信息

我们还可以把查询语句存储成为一个视图。比如，我们需要经常查询没有参加考试的学生信息，你可以复制下面的SQL语句，在Navicat中运行：

CREATE VIEW student_no_score AS
SELECT SId, Sname
FROM student
LEFT JOIN sc ON student.SId = sc.SId
WHERE sc.score IS NULL;

这样，就可以看到名为 `student_no_score 的视图创建好了，打开后显示出没有参加考试的学生信息

你可以通过阅读菜鸟教程文档，更多的尝试实例，熟悉用SQL语句创建视图的操作。

参考资料：SQL CREATE VIEW

使用Navicat创建视图：视图创建工具

除了使用SQL语句创建视图外，在Navicat中还有一个更加直观的创建视图的方法，就是使用”视图创建工具”。

• 使用一张表创建视图

我们先以一张表为例，看一下如何使用Navicat中的视图创建工具，创建一个查询考试成绩分数低于60分的学生的视图：

1.单击”视图”，右键选择”新建视图”

2.然后，点击”视图创建工具”

3.打开视图创建工具后，你可以在左侧选择要使用的数据表，然后拖拽到指定位置如下

4.这里我们使用的是数据表sc，选择视图中要显示的字段后，再添加查询条件

5.我们添加了查询条件score <60后，再点击”构建并运行”，就看到了创建的视图如下

6.最后，点击左上角的”保存”按钮，并将视图命名为student_low_60，就完成视图的创建了

在这里我们可以看到，视图创建工具就是将SQL 查询语句保存的过程。如果构建并运行后，视图的结果不是我们想要的，你还可以继续使用视图创建工具修改查询，正确之后再做保存的操作。

• 使用两张表创建视图

接下来，我们使用视图创建工具为两张表的查询创建视图。在之前的示例中，我们查询了录入成绩时输入错误学号的学生，为了更直观的显示出正确的学号和错误的学号，可以这样创建：

1.打开视图创建工具后，在左侧选择要使用的数据表student和sc，然后拖拽到指定位置如下。这里需要注意的表的位置，它决定了后面要使用的查询语是LEFT JOIN 还是 RIGHT JOIN

2.选择每张表分别要显示的字段，这里需要注意的是：数据表student中的SId是准确的，数据表sc中的SId存在错误。为了区分它们，我们为数据表sc中的SId设置一个别名incorrectSld，用于区分错误的SId

3.之后，我们将两张表的共同字段Sname作为关联条件，连接两张表；并将默认的 INNER JOIN 查询改成 RIGHT JOIN

4.接下来，添加查询条件。我们要查询两张表中SId不相等的数据，因此添加的查询条件为student.sId<>sc.sId

5.点击”构建并运行”后，看到了创建的视图如下。确认无误后，保存视图并命名为student_incorrectsid，就完成视图的创建了

在这个视图中，我们可以直观的看到哪些学生的SId是错误的，并同时显示正确的SId，这有助于我们纠正数据表sc中的错误信息。

4.作业

1. 点击下载本课作业需要用到的SQL文件：点击此处下载

2. 在Navicat里面，打开你的数据库，运行第9课的SQL文件，来更新你的数据库数据和视图。

3. 使用Student表、Course表与SC表做JOIN，找出错误的数据。

4. 计算学生的课程总数、总成绩和平均成绩，并且把你的SQL语句保存为视图。

5. 计算每个学生的最高分和最低分。

6. 计算每个课程有多少个学生，平均分是多少。

7. 计算每个课程学生成绩的最高分和最低分。