SQL语言-数据查询PPT学习课件

3.3.2连接查询,查询同时涉及两个以上的表连接查询分为：等值连接查询非等值连接查询外连接查询自身连接查询复合条件连接查询,连接条件：格式：.当比较运算符为=时，称等值连接；使用其它运算符为非等值连接连接谓词中的列名，称连接字段连接字段的类型必须是可比的，但连接字段名可以不同,1.等值连接,例1查询每个学生及其选修课程的情况等值连接SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno自然连接SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno,在多表连接查询中，应综合使用在单表查询中所学的各种查询方法，把多表连接的结果看作是一个新表，在其基础上应用字符匹配、条件分组查询等方法。例查询选修2号课程且成绩在90分以上的所有学生。SELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=2ANDSC.Grade=90例查询每个学生选修的课程及其成绩。SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno,示例：,等值连接练习：1.查询李勇同学选修的课程号及成绩,按成绩升序排列2.查询李勇同学选修的课程名及成绩3.查询选修数据库课程的学生姓名和成绩4.查询各系选修课程的学生人次数5.查询各系选修课程的学生人数6.查询CS系学生的平均选课成绩7.查询CS系学生各门课程的平均成绩,外连接SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno(*)*为外连接符，相当于在表中增加了一个全部为空值的万能匹配行它可以将那些不能与另一个表连接的元组进行连接笛卡儿积连接SELECTStudent.*,SC.*FROMStudent,SC,SQLSERVER中的多表连接,内部连接（innerjoin）外部连接（outerjoin）交叉联接（crossjoin）,内连接,内连接是使用比较运算符比较要联接列中的值的联接，在SQL-99标准中，可以在FROM子句或WHERE子句中指定内部联接。这是WHERE子句中唯一一种SQL-99支持的联接类型。WHERE子句中指定的内部联接称为旧式内部联接。,例查询选修2号课程且成绩在90分以上的所有学生。SELECTStudent.Sno,SnameFROMStudentinnerjoinSConStudent.Sno=SC.SnoWHERESC.Cno=2ANDSC.Grade=90例查询每个学生选修的课程及其成绩。SELECTStudent.Sno,Sname,Cname,GradeFROMStudentinnerjoin(SCinnerjoincourseonSC.Cno=Course.Cno)onStudent.Sno=SC.Sno,外连接,在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。如果我们查找所有学生的选课情况，没有选课学生的记录就不会出现在查询结果当中。但是有时我们想以Student表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，则只输出其基本情况信息，其选课信息为空值(NULL)即可，这时就需要使用外连接(OuterJoin)。内部联接消除了与另一个表中的行不匹配的行。而外部联接会返回FROM子句中提到的至少一个表或视图中的所有行，只要这些行符合任何WHERE或HAVING搜索条件。将检索通过左外部联接引用的左表中的所有行，以及通过右外部联接引用的右表中的所有行。在完全外部联接中，将返回两个表的所有行。MicrosoftSQLServer2005对FROM子句中指定的外部联接使用下列SQL-92关键字：LEFTOUTERJOIN或LEFTJOINRIGHTOUTERJOIN或RIGHTJOINFULLOUTERJOIN或FULLJOIN,例：查看所有学生的选课情况SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentleftouterjoinSConstudent.sno=sc.sno或：SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMSCrightouterjoinStudentonstudent.sno=sc.sno练习：查看所有课程的选修情况,表的别名,进行多表连接查询时，为了列名避免混肴，SELECT子句与WHERE子句中的属性名前都加上表名前缀。这样限制列名不仅费，而且容易出错，特别是在当连接的表具有较长名称时更是如此。为了简化任务，可以用表别名代替表名。表别名写在FROM子句的表名后面。一旦给表赋予了别名，该别名的使用就必须贯穿整个SELECT命令。,别名的使用,例：SELECTA*，B*FROMStudentA，SCBWHEREA.Sno=BSno；在内外连接中均可以使用别名，以减少长表名带来的拼写麻烦。例：SELECTS.Sno,SnameFROMStudentSinnerjoinSCConS.Sno=C.SnoWHEREC.Cno=2ANDC.Grade=90,自身连接（特殊的多表连接）,连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自身连接。例查询每一门课的间接先修课(先修课的先修课),FIRST(Course),SECOND(Course),查询语句：SELECTFIRST.Cno,SECOND.CpnoFROMCrouseFIRST,CrouseSECONDWHEREFIRST.Cpno=SECOND.Cno,子查询（嵌套查询）,定义：子查询（嵌套查询）：将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询。查询块：一个SELECT-FROM-WHERE语句上层查询块称主查询（外层查询、父查询）下层查询块称子查询（内层查询）SQL允许多层嵌套查询子查询的SELECT语句中不能使用ORDERBY子句ORDERBY子句只对最终查询结果排序。求解方法：由里向外,从一个表中返回单一值的子查询称为单一行子查询。例查询年龄大于学生平均年龄的学生的信息，SELECTSno，Sname，Ssex,Sage,SdeptFROMStudentWHERESage(SELECTAVG(Sage)FROMStudent)单一行比较运算符：=,=,=练习：查询与刘晨同一个系的其他学生学号与姓名,1.返回单一行的子查询,2.多行子查询,多行子查询返回一行或者多行数据。多行比较运算符,查询所有被学生选修的课程的信息。SELECTCno,Cname,Cpno,CcreditFROMCourseWHERECnoIN(SELECTDISTINCT(Cno)FROMSC)说明：(1)利用子查询查找SC表当中，被学生选修的课程(2)外查询从Course表中查找课程号（Cno）在子查询中查找出的课程号范围的课程信息。练习：查询所有未被学生学修的课程信息查询选修了“数据结构”课程的同学的信息,例.查询其他系中比信息系（IS）任意一学生年龄小的学生姓名和年龄带有ANY谓词的子查询SELECTSname,SageFROMStudentWHERESageIS效果等同于：带有集函数MAX的子查询SELECTSname,SageFROMStudentWHERESageIS,例2.查询其他系中比信息系（IS）所有学生年龄小的学生姓名和年龄带有ALL谓词的子查询SELECTSname,SageFROMStudentWHERESageIS效果等同于：带有集函数MIN的子查询SELECTSname,SageFROMStudentWHERESageIS,比较不同查询方式实现同一查询例查询选修了课程号C2的学生姓名带有ANY谓词的子查询1）SELECTSnameFROMStudentWHERESno=ANY(SELECTSnoFROMSCWHERECno=C2)带有IN谓词的子查询2）SELECTSnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno=C2)连接查询3）SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=C2,4.带有EXISTS谓词的子查询,EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何实际数据；它只产生逻辑真值“true”或逻辑假值“false”。由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名也无实际意义与EXISTS谓词相对应的是NOTEXISTS谓词,例.查询所有选修了1号课程的学生姓名。查询所有选修了1号课程的学生姓名涉及Student关系和SC关系，可以在Student关系中依次取每个元组的Sno值，用此Student.Sno值去检查SC关系，若SC中存在(EXITS)这样的元组(其SCSno值等于用来检查的StudentSno值),并且其SCCno1，则取此StudentSname送入结果关系。带有EXISTS短语的查询SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESnoStudent.SnoANDCno1)；,例：查询所有未修1号课程的学生姓名。带有EXISTS短语的查询SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESnoStudent.SnoANDCno1)；,相关子查询：这类查询与前面的不相关子查询有一个明显区别，即子查询的查询条件依赖于外层父查询的某个属性值(在本例中是依赖于Student表的Sno值)，我们称这类查询为相关子查询(correlatedsubquery)。求解相关子查询不能像求解不相关子查询那样，一次将子查询求解出来，然后求解父查询。相关子查询的内层查询由于与外层查询有关，因此必须反复求值。从概念上讲，相关子查询的一般处理过程如下。首先取外层查询中Student表的第一个元组，根据它与内层查询相关的属性值(即Sno值)处理内层查询，若WHERE子句返回值为真(即内层查询结果非空)，则取此元组放入结果表；然后再检查Student表的下一个元组；重复这一过程，直至Student表全部检查完毕为止。,利用子查询进行数据更新,利用子查询插入数据例.将计算机系学生的学号加入选课表中INSERTINTOSC(Sno)SELECTSnoFROMStudentWHERESdept=计算机,利用子查询更新数据UPDATESCSETGrade=0WHERECS=(SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno),利用子查询删除数据删除计算机系所有学生的选课记录DELETEFROMSCWHERECS=(SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno),集合查询,若要把多个SELECT语句的结果合并为一个结果，可用集合操作来完成。集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。标准SQL中没有直接提供集合交操作和集合差操作，但可以用其他方法（NOT、AND、OR）来实现，会依查询不同而不同。(ACCESS不支持INTERSECT和EXCEP