数据库第四版课件.

AnIntroductiontoDatabaseSystem上海第二工业大学计算机与信息学院数据库系统概论AnIntroductiontoDatabaseSystem第三章关系数据库标准语言SQL(续1）AnIntroductiontoDatabaseSystem3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结AnIntroductiontoDatabaseSystem3.3.3连接查询Select查询的From后面可以包含多个表，表示查询数据来源于多个表，通常需要使用连接条件把多个表连接起来连接条件的最常见的形式是“表名1.列名1=表名2.列名”AnIntroductiontoDatabaseSystem例：学生表student级表class学号姓名班号班号班名snosnameclassnoclassnoclnameS01王海燕C01C0107计科S1S02李冰C01C0206软工A1S03黄涛C02要求查询：学号姓名班名S01王海燕07计科S1S02李冰07计科S1S03黄涛06软工A1要求查询的内容的数据来源于两个表，连接条件是：students.classno=classes.classnoAnIntroductiontoDatabaseSystem连接操作的执行过程嵌套循环法(NESTED-LOOP)首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕AnIntroductiontoDatabaseSystem排序合并法(SORT-MERGE)常用于=连接首先按连接属性对表1和表2排序对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续AnIntroductiontoDatabaseSystem排序合并法找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续重复上述操作，直到表1或表2中的全部元组都处理完毕为止AnIntroductiontoDatabaseSystem索引连接(INDEX-JOIN)对表2按连接字段建立索引对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组AnIntroductiontoDatabaseSystem连接查询（续）SQL中连接查询的主要类型广义笛卡尔积等值连接(含自然连接)非等值连接查询自身连接查询外连接查询复合条件连接查询AnIntroductiontoDatabaseSystem一、广义笛卡尔积无连接条件例如：SELECT*FROMStudent,Classe或：select*fromstudentcrossjoinclass较少使用，无连接条件意味着其查询结果是全两个表行的全组合，没有包含两个表之间的任何关系信息AnIntroductiontoDatabaseSystem二、等值与非等值连接查询等值连接、自然连接、非等值连接[例]查询每个学生及其所在班级SELECT*FROMStudentjoinClassonStudent.classno=Class.Classno或把连接条件放在where中：SELECT*FROMStudent,ClassWHEREStudent.classno=Class.Classno；AnIntroductiontoDatabaseSystem等值连接连接运算符为=的连接操作[表名1.]列名1=[表名2.]列名2任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一的属性名时可以省略表名前缀。可以为每个表起一个别名，引用时可简单些：select*fromstudenta,classebwherea.classno=b.classnoAnIntroductiontoDatabaseSystem自然连接等值连接的一种特殊情况，把目标列中重复的属性列去掉。Select中没有自然连接功能，只能通过选列做到这一点selecta.cno,a.cname,b.classno,b.clnamefromstudenta,classbwherea.classno=b.classnoAnIntroductiontoDatabaseSystem非等值连接查询连接运算符不是=的连接操作[表名1.]列名1比较运算符[表名2.]列名2比较运算符：、、=、=、!=[表名1.]列名1BETWEEN[表名2.]列名2AND[表名2.]列名3AnIntroductiontoDatabaseSystem三、自身连接一个表与其自己进行连接，称为表的自连接必须给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀AnIntroductiontoDatabaseSystem自身连接（续）[例]查询每一门课的名称以及其先修课的名称SELECTa.Cname，b.CnameFROMCoursea，CoursebWHEREa.Cpno=b.Cno；AnIntroductiontoDatabaseSystem四、外连接（OuterJoin）外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出AnIntroductiontoDatabaseSystem左外连接Join中处于左边表中的行全部作为查询结果输出[例]查询每个学生学号、姓名、选修课程号及成绩的情况，查询结果要包括没有选修课程的学生--用外连接操作SELECTa.Sno,a.Sname,b.Cno,b.GradeFROMStudentaLEFTJOINSCbona.Sno=b.SnoSQLServer使用where条件的连接也支持左外连接，上述等价的语句为：SELECTa.Sno,a.Sname,b.Cno,b.GradeFROMStudenta,SCbWHEREa.Sno*=b.SnoAnIntroductiontoDatabaseSystem右外连接Join中处于右边表中的行全部作为查询结果输出上述查询语句中把student和sc交换位置，要实现相同的查询，就可以使用右外连接SELECTa.Sno,a.Sname,b.Cno,b.GradeFROMSCbRIGHTJOINSCaona.Sno=b.SnoSQLServer中使用WHERE不支持右外连接AnIntroductiontoDatabaseSystem五、复合条件连接WHERE子句中含多个连接条件时，称为复合条件连接[例]查询选修2号课程且成绩在90分以上的所有学生的学号、姓名SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND/*连接谓词*/SC.Cno=‘2’AND/*其他限定条件*/SC.Grade90/*其他限定条件*/AnIntroductiontoDatabaseSystem多表连接[例]查询每个学生的学号、姓名、选修的课程名及成绩。SELECTStudent.Sno,Sname,Cname,GradeFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno；结果：Student.SnoSnameCnameGrade95001李勇数据库9295001李勇数学8595001李勇信息系统8895002刘晨数学9095002刘晨信息系统80AnIntroductiontoDatabaseSystem3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结AnIntroductiontoDatabaseSystem3.3.4嵌套查询（子查询）[例]查询与“刘晨”在同一个系学习的学生的学号、姓名和所在系。此查询要求可以分步来完成：①确定“刘晨”所在系名：SELECTSdeptFROMStudentWHERESname='刘晨'；结果为：IS②查找所有在IS系学习的学生。SELECTSno，Sname，SdeptFROMStudentWHERESdept='IS'；AnIntroductiontoDatabaseSystem构造嵌套查询将第一步查询嵌入到第二步查询的条件中SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘刘晨’)或：SELECTSno，Sname，SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname=‘刘晨’)//使用“=”必须确保子查询结果为单个值AnIntroductiontoDatabaseSystem嵌套查询(子查询)嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询AnIntroductiontoDatabaseSystem嵌套查询(续)子查询的限制不能使用ORDERBY子句层层嵌套方式反映了SQL语言的结构化有些嵌套查询可以用连接运算替代AnIntroductiontoDatabaseSystem与上述查询语句结果等价的查询语句：SELECTa.Sno，a.Sname，a.SdeptFROMStudenta,StudentbWHEREa.Sdept=b.SdeptANDb.Sname='刘晨'AnIntroductiontoDatabaseSystem引出子查询的谓词带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询AnIntroductiontoDatabaseSystem一、带有IN谓词的子查询[例]查询选修了课程学生学号和姓名SELECTSno,SnameFROMStudentWHERESNOIN(SELECTSNOFROMSC)[例]查询未选修任何课程学生学号和姓名SELECTSno,SnameFROMStudentWHERESNONOTIN(SELECTSNOFROMSC)AnIntroductiontoDatabaseSystem多层嵌套：[例]查询选修了课程名为“信息系统”的学生学号和姓名SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname=‘信息系统’));AnIntroductiontoDatabaseSystem二、带有比较运算符的子查询●当能确切知道内层查询返回单值时，可用比较运算符（，，=，=，=，!=或）。与ANY或ALL谓词配合使用AnIntroductiontoDatabaseSystem带有比较运算符的子查询（续）例：假设一个学生只可能在一个系学习，并且必须属于一个系，则与刘晨同一个系的学生信息查询可用“