3.4 数据查询 v3.4.1 单表查询v3.4.2 连接查询v3.4.3 嵌套查询v3.4.4 集合查询v3.4.5 Select语句的一般形式 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.An Introduction to Database System3.4.2 连接查询 v 连接查询：同时涉及多个表的查询v 连接条件或连接谓词：用来连接两个表的条件一般格式：n . .n . BETWEEN . AND .v 连接字段：连接谓词中的列名称n 连接条件中的各连接字段类型必须是可比的，但名字不必是相同的Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.An Introduction to Database System连接操作的执行过程v 嵌套循环法(NESTED-LOOP) 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。 表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。 重复上述操作，直到表1中的全部元组都处理完毕 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.An Introduction to Database System排序合并法(SORT-MERGE)常用于=连接 首先按连接属性对表1和表2排序 对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.An Introduction to Database System排序合并法 找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续 重复上述操作，直到表1或表2中的全部元组都处理完毕为止 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.An Introduction to Database System索引连接(INDEX-JOIN) 对表2按连接字段建立索引 对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组 Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.An Introduction to Database System连接查询（续）一、等值与非等值连接查询 二、自身连接 三、外连接 四、复合条件连接Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.An Introduction to Database System一、等值与非等值连接查询 v等值连接：连接运算符为=例33 查询每个学生及其选修课程的情况SELECT Student.*，SC.*FROM Student，SCWHERE Student.Sno = SC.Sno；Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.An Introduction to Database System等值与非等值连接查询（续）Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade200215121李勇男20CS200215121192200215121李勇男20CS200215121285200215121李勇男20CS200215121388200215122刘晨女19CS200215122290200215122刘晨女19CS200215122380查询结果：Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.An Introduction to Database System等值与非等值连接查询（续）v自然连接：例34 对例33用自然连接完成。SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROM Student，SCWHERE Student.Sno = SC.Sno；Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.An Introduction to Database System连接查询（续）一、等值与非等值连接查询 二、自身连接 三、外连接 四、复合条件连接Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.An Introduction to Database System二、自身连接 v 自身连接：一个表与其自己进行连接v 需要给表起别名以示区别v 由于所有属性名都是同名属性，因此必须使用别名前缀例35查询每一门课的间接先修课（即先修课的先修课）SELECT FIRST.Cno，SECOND.CpnoFROM Course FIRST，Course SECONDWHERE FIRST.Cpno = SECOND.Cno；Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.An Introduction to Database System自身连接（续）FIRST表（Course表） Cno CnameCpnoCcredit1数据库 5 4 2数学23信息系统 1 44操作系统 6 35数据结构 7 46数据处理 27PASCAL语言 6 4Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2