数据库技术 课件7.3 多表查询

数据库技术7.3多表查询7.3多表查询MySQL多表查询是通过SQL语句在多个表之间检索数据的过程，常用方法包括INNERJOIN（内连接）、LEFTJOIN（左连接）、RIGHTJOIN（右连接）和FULLJOIN（全连接，MySQL中不直接支持，需通过UNION模拟）。通过指定连接条件和选择字段，实现跨表数据获取。

连接(JOIN)的类型与使用7.3.17.3.1连接(JOIN)的类型与使用1.内连接（INNERJOIN）内连接查询返回两个表中满足连接条件的匹配行。它是最常用的连接类型，查询的是两张表交集的部分。内连接有两种语法形式，隐式内连接和显式内连接是，其中显式内连接使用INNERJOIN关键字，而隐式内连接则直接在WHERE子句中指定连接条件。7.3.1连接(JOIN)的类型与使用（1）隐式内连接语法格式如下：SELECT列名1,列名2,...FROM表1,表2WHERE表1.连接字段=表2.连接字段;参数说明：列名1,列名2,...：要检索的字段列表，可以来自表1、表2或两者都有。表1,表2：要连接的表名。WHERE表1.连接字段=表2.连接字段：连接条件，指定了两个表中用于匹配的字段。7.3.1连接(JOIN)的类型与使用任务19：查询选修了课程编号为“230101”课程的学生学号、姓名、课程编号、成绩。代码如下：SELECTstudents.stu_id,stu_name,crs_id,sc_gradeFROMstudents,scoresWHEREstudents.stu_id=scores.stu_idandcrs_id='230101';运行结果如图7.19所示：说明：在scores表中可以查询到学生的学号、课程编号、成绩，不能查询到同学的姓名。但如果知道学生的学号，可以到students表中查找到对应的同学姓名。这就可以用多表查询来完成。由于两个表中都有stu_id字段，所以在连接这两个表时，需要明确指定stu_id字段来自哪一张表，以避免歧义。7.3.1连接(JOIN)的类型与使用（2）显式内连接语法格式如下：SELECT列名1,列名2,...FROM表1INNERJOIN表2ON表1.连接字段=表2.连接字段;;参数说明：

列名1,列名2,...：同样表示要检索的字段列表。

表1：第一个要连接的表名。

INNERJOIN表2：使用INNERJOIN关键字连接第二个表。

ON表1.连接字段=表2.连接字段：通过ON子句指定连接条件。7.3.1连接(JOIN)的类型与使用任务20：查询选修了课程编号为“230203”课程的学生学号、姓名、课程编号、成绩。代码如下：SELECTstudents.stu_id,stu_name,crs_id,sc_gradeFROMstudentsINNERJOINscoresONstudents.stu_id=scores.stu_idWHEREcrs_id='230203';运行结果如图7.19所示：注意：

隐式内连接和显式内连接在逻辑上是等价的，但显式内连接通常更易于阅读和维护。

在处理复杂查询时，显式内连接可以帮助你更清晰地表达查询逻辑。

在某些情况下，隐式内连接可能会导致性能问题，因为数据库优化器可能无法像处理显式连接那样有效地优化查询计划。因此，建议使用显式内连接。7.3.1连接(JOIN)的类型与使用2.外连接（OUTERJOIN）在内部连接操作中只有当两个表中至少有一个行符合联接条件时，才返回行，因为内部连接消除了两个表中不匹配的行。而外部联接则会返回FROM子句中至少一个表或视图中的所有行，只要这些行符合WHERE或HAVING搜索条件的。外连接分为左外连接（LEFTJOIN或LEFTOUTERJOIN）、右外连接（RIGHTJOIN或RIGHTOUTERJOIN）和全外连接（FULLJOIN）。MySQL不直接支持FULLJOIN。左外连接就是将左表作为主表，主表中所有行分别与右表中的每一行进行连接，结果集中除了满足连接条件的行外，还有主表中不满足连接条件的行，在右表的相应列上自动填充NULL值。右外连接则相反。7.3.1连接(JOIN)的类型与使用任务21：查找所有学生的学号，姓名和成绩信息，若学生没有选修课程，也要显示其信息。代码如下：SELECTstudents.stu_id,stu_name,scores.*FROMstudentsLEFTJOINscoresONstudents.stu_id=scores.stu_id;如果不使用左外连接，查询结果中不会包含没有选修过课程的同学信息。使用了左外连接后，结果集中返回的行中有没用选修过的同学信息，相应的行的成绩信息为NULL。运行结果如图7.21所示：子查询与嵌套查询7.3.27.3.2子查询与嵌套查询

在MySQL中，子查询和嵌套查询被视为处理复杂SQL查询需求的同一概念的不同表述方式。从字面含义上解析，“子查询”一词更多地聚焦于查询之间的层级或嵌套关系，意味着一个查询作为另一个查询的组成部分存在；相对而言，“嵌套查询”则更强调查询结构的层次性，即一个查询被另一个查询所包裹。然而，在MySQL的实际应用中，这两个术语具有相同的含义，均指在一个查询语句内部嵌入另一个查询语句。为了保持术语的一致性和清晰性，本书统一采用“子查询”这一表述来指代这种查询结构。这样的定义有助于我们更准确地理解和运用MySQL中的复杂查询技术。

子查询基本语法格式如下：SELECT列名

FROM表名

WHERE列名

比较运算符(SELECT列名FROM表名WHERE条件);

括号内的部分即为子查询。子查询可以用在WHERE子句、HAVING子句、SELECT子句（作为计算列）等位置。7.3.2子查询与嵌套查询1．使用IN的子查询使用IN（或NOTIN）的子查询结果是包含零个值或多个值的列表。IN子查询用于检查某个值是否存在于另一个查询的结果集中。适用于过滤记录，根据一个字段的值是否匹配另一个查询返回的集合来决定是否包含该记录。任务22：查询19计算机应用1班，19旅游管理1班学生的信息。代码如下：SELECT*FROMstudentsWHEREcls_idIN(SELECTcls_idFROMclassesWHEREcls_name='19计算机应用1班'ORcls_name='19旅游管理1班');运行结果如图7.22所示：在运行包含子查询的SELECT语句时，系统先运行子查询，产生一个结果表，再运行查询。在本任务中，先运行子查询“SELECTcls_idFROMclassesWHEREcls_name='19计算机应用1班'ORcls_name='19旅游管理1班'”，得到一个只含有班级编号的表；再运行外查询，如果学生表中某行的班级编号列值等于子查询结果表中的任意一个值，则该行就会被选择。7.3.2子查询与嵌套查询2.使用比较运算符的的子查询常用的比较运算符有：=、>、<、>=、<=、<>、!=。直接使用比较运算符的子查询（即后面不接ANY或ALL的比较运算符）必须返回单个值而不是值列表。因为比较运算符设计用于单个值之间的比较。如果子查询返回了多于一个的值，MySQL会抛出一个错误。任务23：查询选修了“230301”号课程，且成绩高于该课程平均分的学生的学号。代码如下：查询选修了“230301”号课程，且成绩高于该课程平均分的学生的学号。SELECTstu_idFROMscoresWHEREsc_grade>(SELECTAVG(sc_grade)FROMscoresWHEREcrs_id='230301')ANDcrs_id='230301';运行结果如图7.23所示：在这个任务中，首先查询计算课程编号为“'230301”这门课程的平均分（单个值），然后再根据这个平均分找到所有选修了该课程，且成绩高于平均分的学生student_id。7.3.2子查询与嵌套查询任务23：查询选修了“230301”号课程，且成绩高于该课程平均分的学生共有多少人。代码如下：SELECTcount(stu_id)as高于平均分人数FROMstudentsWHEREstu_idIN(SELECTstu_idFROMscoresWHEREsc_grade>(SELECTAVG(sc_grade)FROMscoresWHEREcrs_id='230301')ANDcrs_id='230301');运行结果如图7.24所示：在这个任务中中，最内层的子查询首先查询计算课程编号为“'230301”这门课程的平均分，然后中层的子查询根据这个平均分找到所有选修了该课程，且成绩高于平均分的学生student_id，最后外层的查询根据这些stu_id找到对应的学生信息,并进行统计计数。7.3.2子查询与嵌套查询2.使用比较运算符的的子查询常用的比较运算符有：=、>、<、>=、<=、<>、!=。直接使用比较运算符的子查询（即后面不接ANY或ALL的比较运算符）必须返回单个值而不是值列表。因为比较运算符设计用于单个值之间的比较。如果子查询返回了多于一个的值，MySQL会抛出一个错误。任务23：查询选修了“230301”号课程，且成绩高于该课程平均分的学生的学号。代码如下：查询选修了“230301”号课程，且成绩高于该课程平均分的学生的学号。SELECTstu_idFROMscoresWHEREsc_grade>(SELECTAVG(sc_grade)FROMscoresWHEREcrs_id='230301')ANDcrs_id='230301';运行结果如图7.23所示：在这个任务中，首先查询计算课程编号为“'230301”这门课程的平均分（单个值），然后再根据这个平均分找到所有选修了该课程，且成绩高于平均分的学生student_id。7.3.2子查询与嵌套查询3．使用ANY（或SOME）、ALL的子查询ANY（或SOME）、ALL关键字，可以与比较运算符结合使用，以便在子查询返回的值集合中进行比较。ANY（或SOME）：SOME与ANY是同义词，在MySQL中它们是等价的。如果外部查询中的值与子查询返回的集合中的任何一个值满足比较条件，则返回TRUE,否则返回FALSE。ALL：如果外部查询中的值与子查询返回的集合中的所有值都满足比较条件，则返回TRUE,否则返回FALSE。7.3.2子查询与嵌套查询任务25：查询选修了“230301”号课程，学生的学号，姓名。先查找选修了“230301”号课程的同学的学号。代码如下：SELECTstu_idFROMscoresWHEREcrs_id='230301';运行结果如图7.25所示：因为有多名同学选修了“230301”号课程，所以在子查询中要用ANY（或SOME）。任务25的代码如下：SELECTstu_id,stu_nameFROMstudentsWHEREstu_id=ANY(SELECTstu_idFROMscoresWHEREcrs_id='230301');运行结果如图7.26所示：图7.25查询选修了“230301”号课程的同学的学号图7.26查询选修了“230301”号课程，学生的学号，姓名7.3.2子查询与嵌套查询本任务也可以用IN子查询，任务25的代码与下例语句是代码是等价的。SELECTstu_id,stu_nameFROMstudentsWHEREstu_idIN(SELECTstu_idFROMscoresWHEREcrs_id='230301');7.3.2子查询与嵌套查询任务26：查询比“51020171”班中所有学生年龄都小的其他班的学生的学号与姓名。要完成这个任务，需要先知道班级编号为51020171班同学的出生日期，因为“51020171”班级中不只有一名同学，所以会有多个出生日期，可以通过以下代码查询“51020171”班同学的出生日期，代码如下：SELECTstu_birthFROMstudentsWHEREcls_id='51020171';运行结果如图7.27所示：因为比“51020171”班同学年龄都小就是比子查询中每一条记录的出生日期都要大，所以在子查询的比较条件中要用ALL，任务26的代码如下：SELECTstu_id,stu_nameFROMstudentsWHEREstu_birth>ALL(SELECTstu_birthFROMstudentsWHEREcls_id='51020171');运行结果如图7.28所示：图7.27查询“51020171”班同学的出生日期图7.28查询比“51020171”班中所有学生年龄都小的其他班的学生的学号与姓名7.3.2子查询与嵌套查询5．使用EXISTS的子查询EXISTS用于检查子查询是否返回任何行。如果子查询返回至少一行，则EXISTS条件为真，否则为假。EXISTS通常用于提高查询效率，特别是在处理存在性检查时。EXISTS引出的子查询的目标列通常为*。任务27：查询选修了“230301”课程的学生的学号与姓名。代码如下：SELECTstu_id,stu_nameFROMstudentsWHEREEXISTS(SELECT*FROMscoresWHEREcrs_id='230301'ANDstu_id=students.stu_id);运行结果如图7.29所示：图7.29查询选修了“230301”课程的学生的学号与姓名

在这个任务中，虽然是单表查询，但查询条件使用了外查询的列名引用“students.stu_id”，表示这里的学号来自于表students。在这个任务中，内层查询要处理多次，因为内层查询与“students.stu_id”有关，在外层查询中students表的不同行有不同的学号。其处理过程是：首先查找外层查询中students的第一行，根据该行的“学号”列值处理内层查询，若结果不为空，则where条件为真，把该行的学号、姓名值取出作为结果集的一行；然后再找students表的第2，3，4……行，重复上述处理过珵直到students表的所有行都查找完为止。联合查询7.3.37.3.3联合查询MySQL实际上并不直接支持全连接（FULLJOIN）语法，但可以通过组合左连接（LEFTJOIN）和右连接（RIGHTJOIN）并使用联合查询（UNION查询）来模拟全连接的效果。全连接旨在返回两个表中所有匹配的记录以及不匹配的记录，对于不匹配的部分，结果集中的相应列将包含NULL值。联合查询使用UNION或UNIONALL关键字。注意：

列数和数据类型：所有SELECT语句必须返回相同数量的列，并且相应列的数据类型必须兼容。如果不兼容，MySQL会尝试进行隐式类型转换。

默认去除重复：UNION去除重复的行，返回唯一的结果集。UNIONALL包含所有的行，包括重复的行。

列名：结果集的列名来自第一个SELECT语句。如果后续的SELECT语句中有不同的列名，这些列名会被忽略，但列的数据会按照位置对应。

排序：ORDERBY子句应用于整个结果集，并且必须位于最后一个SELECT语句之后。LIMIT子句同样应用于整个结果集，用于限制返回的行数。

WHERE子句：每个SELECT语句都可以有自己的WHERE子句来过滤行。7.3.2子查询与嵌套查询任务28：将学生成绩表中学号为“23510102710101”成绩信息与课程编号为“230303”的成绩信息合并。查询成绩表中学号为“23510102710101”成绩信息，代码如下：SELECTstu_id,crs_id,sc_grade,sc_rebuildFROMscoresWHEREstu_id="23510102710101';运行结果如图7.30所示：查询课程编号为“230303”的成绩信息，代码如下：SELECTstu_id,crs_id,sc_grade,sc_rebuildFROMscoresWHEREcrs_id="230303";运行结果如图7.31所示：图7.30查询成绩表中学号为“23510102710101”成绩信息图7.31查询课程编号为“230303”的成绩信息7.3.2子查询与嵌套查询任务28：使用UNION将图7.30和图7.31的结果合并，代码如下：SELECTstu_