任务5.2 多表连接查询(课件）-《网站数据库应用技术-SQL Server》同步教学（电子工业版）

项目五 数据查询 1 任务5.1 基本数据查询 任务5.2 多表连接查询 任务5.3 子查询 1 3 2 目录页 CONTENTS PAGE 2 任务5.2 多表连接查询 5.2.1 交叉连接查询 5.2.2 内连接查询 5.2.3 外连接查询 5.2.4 自连接查询 过渡页 TRANSITION PAGE 3 在创建数据库时，由于各方面的原因，所有数据不能存放在一个表中。例如，在libsys数据库中，bookinfo表中只存放了图书名称、作者等信息，图书的借阅信息被存放到了borrowinfo表中，读者的姓名、部门等信息被存放到了readerinfo表中。此时如果想知道某本书被谁借走了，就必须综合这3个表中的信息才能得到答案。这种情况我们称为连接查询。 在查询数据时，如果数据的来源是数据库中两个或两个以上的表，则系统先按照一定的规则将这些表中的数据组合到一起，构成一个虚拟的大表，然后就可以像普通表一样，在这个虚拟表中查询数据了。在通常情况下，多个表之间有着公共的字段或者通过外键约束来建立连接关系。如果两个表没有任何相同的字段，则可以通过比较类型相同的两个列中的值的大小进行查询。 多表连接查询 5.2.1 交叉连接查询 任务5.2 交叉连接又称笛卡儿积，系统会将一个表中的每条记录和其他表中的每条记录分别组合，形成若干条记录，构成一个新的虚拟表。 交叉连接查询的语法格式如下： SELECT 列名1[,列名2…] FROM 表名1 CROSS JOIN 表名2 [CROSS JOIN 表名3…] [WHERE 查询条件] 多表连接查询 5.2.1 交叉连接查询 任务5.2 【例5-20】在readerInfo表和borrowInfo表的交叉连接中，查询读者姓名（ReaderName字段）为“胡大龙”的记录，只需返回readerInfo表中的ReaderID列和ReaderName列内的数据，以及borrowInfo表中的ReaderID列和BookID列内的数据。 SELECT readerInfo.ReaderID,ReaderName,borrowInfo.ReaderID,BookID FROM readerInfo,borrowinfo WHERE ReaderName='胡大龙' 查询结果如图所示 多表连接查询 5.2.1 交叉连接查询 任务5.2 说明： （1）从第二条到之后的记录，可以看到借书人的ReaderID字段与胡大龙的ReaderID字段根本不一样，但数据库依然机械地按照交叉连接的规则将它们组合到了一起，这样的数据对了解信息根本没什么用，甚至会带来错误 （2）在交叉连接查询中，关键字CROSS JOIN可以省略，用逗号代替 （3）由于两个表中都有ReaderID列，因此要以“表名.列名”的形式指定哪个ReaderID列显示在哪个位置。ReaderName列和BookID列各自只存在于一个表中，就不用通过“表名”进行区分了。 多表连接查询 5.2.1 交叉连接查询 任务5.2 在交叉连接中，由于是没有限制的，其中一个表的每条记录都会和另一个表中的每条记录连接在一起，因此产生的数据绝大部分是无效信息。那么如果给记录的连接加上一个限制，则得到的数据就是有效信息了，这就是内连接。内连接查询的语法格式如下： SELECT 列名1[,列名2…] FROM 表名1 INNER JOIN 表名2 ON 连接条件 [INNER JOIN 表名3 ON 连接条件…] [WHERE 查询条件] 多表连接查询 5.2.2 内连接查询 任务5.2 在内连接中，以两个表为例，如果查询语句为以下形式： SELECT * FROM 表1 INNER JOIN 表2 ON 编号=序号 查询结果如图所示 多表连接查询 5.2.2 内连接查询 任务5.2 【例5-21】在readerInfo表和borrowInfo表的内连接中，查询曾经借过书的每位读者的编号、姓名和所借图书的编号。 SELECT readerInfo.ReaderID,ReaderName,borrowInfo.ReaderID,BookID FROM readerInfo INNER JOIN borrowInfo ON readerInfo.ReaderID=borrowInfo.ReaderID 查询结果如图所示 多表连接查询 5.2.2 内连接查询 任务5.2 内连接是使用最频繁的连接语句，SQL语言提供了一种简单写法： SELECT 列名1[,列名2…] FROM 表名1，表名2 [,表名3…] WHERE查询条件 多表连接查询 5.2.2 内连接查询 任务5.2 【例5-23】通过readerInfo、borrowInfo、bookInfo这3个表，查询每位读者都曾经借过哪些图书。 SELECT R.ReaderID,ReaderName,B1.BookID,BookName FROM readerInfo R,borrowInfo B1,bookInfo B2 WHERE R.ReaderID=B1.ReaderID AND B1.BookID=B2.BookID 查询结果如图所示 多表连接查询 5.2.2 内连接查询 任务5.2 说明： （1）为了降低书写表名的难度，FROM子句中为每个表起了一个“别名”，格式和用法与列的“别名”类似。 （2）在这种格式中，如果缺少WHERE子句，就变成了交叉连接语句。 （3）SELECT子句中的B1.BookID列也可以使用B2.BookID，查询结果不变。这说明borrowInfo表作为连接readerInfo表与bookInfo表的桥梁，可以不返回该表中的任何一个列 多表连接查询 5.2.2 内连接查询 任务5.2 在SQL中的外连接查询分为左连接、右连接和全连接，语法格式和内连接查询的语法格式类似，只有一个单词不同。外连接查询的语法格式如下： SELECT 列名1[,列名2…] FROM 表名1 LEFT(或RIGHT,或FUll) JOIN 表名2 ON 连接条件 [LEFT(或RIGHT,或FUll) JOIN 表名3 ON 连接条件…] [WHERE 查询条件] 以两个表为例，如果查询语句为以下形式： SELECT * FROM 表1 LEFT (或RIGHT,或FUll) JOIN 表2 ON 编号=序号 查询结果如图所示 多表连接查询 5.2.3 外连接查询 任务5.2 多表连接查询 5.2.3 外连接查询 任务5.2 【例5-22】在readerInfo表和borrowInfo表中查询，要求返回每位读者的编号、姓名，曾借过书的读者就返回图书的编号，没借过书的读者就返回NULL。 SELECT readerInfo.ReaderID,ReaderName, BookID FROM readerInfo LEFT JOIN borrowInfo ON readerInfo.ReaderID=borrowInfo.ReaderID 查询结果如图所示 多表连接查询 5.2.3 外连接查询 任务5.2 在如图所示的readerInfo表中，如果想查询哪些读者的年龄比胡大龙的年龄大，应该如何编写查询语句？ 是否可以使用以下语句？ SELECT ReaderID,ReaderName,ReaderSex,ReaderAge,Department,ReaderType,Mobile FROM readerInfo WHERE ReaderAge>39 多表连接查询 5.2.4 自连接查询 任务5.2 【例5-24】在readerInfo表中，查询年龄比胡大龙的年龄大的所有读者的姓名和年龄。 思考过程： （1）姓名和年龄都在readerInfo表中，那么如何进行连接查询呢？可以想象将readerInfo表抄写两份，这样就有两个表可以连接了。 SELECT a.ReaderName a表中的姓名,a.ReaderAge a表中的年龄 ,b.ReaderName b表中的姓名, b.ReaderAge b表中的年龄 FROM readerInfo a,readerInfo b 多表连接查询 5.2.4 自连接查询 任务5.2 说明： ①由于是一个表与自己连接，表名相同，因此为了区分列的来源，必须为表起别名。 ②为了便于理解，语句中为每个列也命名了别名。 ③由于没有WHERE查询条件，因此上述语句生成了交叉连接查询结果，右图显示了查询结果的一部分 多表连接查询 5.2.4 自连接查询 任务5.2 （2）在上述语句的基础上，加入WHERE查询条件： SELECT a.ReaderName a表中的姓名,a.ReaderAge a表中的年龄 ,b.ReaderName b表中的姓名,b.ReaderAge b表中的年龄 FROM readerInfo a,readerInfo b WHERE a.ReaderName='胡大龙' 可以得到如图所示的内连接查询结果 多表连接查询 5.2.4 自连接查询 任务5.2 （3）在上述语句的基础上，再加入查询条件“a表中的年龄< b表中的年龄”，并对查询的列进行修改，就可以得到最终查询语句： SELECT b.ReaderName,b.ReaderAge FROM readerInfo a,readerInfo b WHERE a.ReaderName='胡大龙' AND a.ReaderAge<b.ReaderAge 可以得到如图所示的内连接查询结果，这种一个表与自身进行连接查询的方法被称为自连接查询。 多表连接查询 5.2.4 自连接查询 任务5.2 菊次郎的夏天 菊次郎的夏天 久石譲 $$