数据关联查询与视图

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 基本语法如下:

  1. SELECT column_name(s)
  2. FROM table1
  3. INNER JOIN table2
  4. ON table1.column_name=table2.column_name;

在这里:

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

参考资料:SQL INNER JOIN

  • LEFT JOIN

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

  1. SELECT column_name(s)
  2. FROM table1
  3. LEFT JOIN table2
  4. ON table1.column_name=table2.column_name;

在这里:

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

参考资料:SQL LEFT JOIN

  • RIGHT JOIN

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

  1. SELECT column_name(s)
  2. FROM table1
  3. RIGHT JOIN table2
  4. ON table1.column_name=table2.column_name;

在这里:

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

参考资料:SQL RIGHT JOIN

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

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

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

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

学生表(student)

join1.png

成绩表(sc)

join2.png

参加考试的学生(INNER JOIN)

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

  1. SELECT student.SId, student.Sname, sc.Cname, sc.score
  2. FROM student
  3. INNER JOIN sc
  4. ON student.SId = sc.SId;

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

join3.png

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

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

  1. SELECT student.SId, student.Sname, sc.Cname, sc.score
  2. FROM student
  3. LEFT JOIN sc
  4. ON student.SId = sc.SId;

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

join4.png

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

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

  1. SELECT student.SId, student.Sname, sc.Cname, sc.score
  2. FROM student
  3. RIGHT JOIN sc
  4. ON student.SId = sc.SId;

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

join6.png

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

3.视图的定义和使用

视图的概念和作用

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

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

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

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

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

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

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

使用sql语句创建视图

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

  1. CREATE VIEW view_name AS
  2. SELECT column1, column2, ...
  3. FROM table_name
  4. WHERE condition;

在这里:

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

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

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

  1. CREATE VIEW sc_over_60 AS
  2. SELECT *FROM SC
  3. WHERE score >60;

view1.png

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

view2.png

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

view3.png

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

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

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

view3.png

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

参考资料:SQL CREATE VIEW

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

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

  • 使用一张表创建视图

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

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

viewgj1.png

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

viewgj2.png

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

viewgj3.png

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

viewgj4.png

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

viewgj5.png
viewgj6.png

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

viewgj7.png
viewgj8.png

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

  • 使用两张表创建视图

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

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

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

view2gj2.png

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

view2gj3.png
view2gj4.png

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

view2gj5.png

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

view2gj6.png
view2gj7.png

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

4.作业

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

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

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

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

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

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

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