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

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,3.3.2,连接查询,查询同时涉及两个以上的表,连接查询分为：,等值连接查询,非等值连接查询,外连接查询,自身连接查询,复合条件连接查询,连接条件：,格式：,.,当比较运算符为,=,时，称,等值连接,；使用其它运算符为,非等值连接,连接谓词中的列名，称,连接字段,连接字段的类型必须是可比的，但连接字段名可以不同,1.,等值连接,例,1,查询每个学生及其选修课程的情况,等值连接,SELECT,Student.*,SC.*,FROM Student,SC,WHERE Student.Sno=SC.Sno,自然连接,SELECT,Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade,FROM Student,SC,WHERE Student.Sno=SC.Sno,在多表连接查询中，应综合使用在单表查询中所学的各种查询方法，把多表连接的结果看作是一个新表，在其基础上应用字符匹配、条件,分组查询等方法。,例,查询选修,2,号课程且成绩在,90,分以上的所有学生。,SELECT Student.Sno,Sname,FROM Student,SC,WHERE Student.Sno=SC.Sno AND,SC.Cno=2 AND SC.Grade=90,例,查询每个学生选修的课程及其成绩。,SELECT Student.Sno,Sname,Cname,Grade,FROM Student,SC,Course,WHERE Student.Sno=SC.Sno AND,SC.Cno=Course.Cno,示例：,等值连接练习：,1.,查询李勇,同学选修的课程号及成绩,按成绩升序排列,2.,查询李勇,同学选修的课程名及成绩,3.,查询选修数据库课程的学生姓名和成绩,4.,查询各系选修课程的学生人次数,5.,查询各系选修课程的学生人数,6.,查询,CS,系学生的平均选课成绩,7.,查询,CS,系学生各门课程的平均成绩,外连接,SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade,FROM Student,SC,WHERE Student.Sno=SC.Sno,(*),*为外连接符，,相当于在表中增加了一个全部为空值的万能匹配行,它可以将那些不能与另一个表连接的元组进行连接,笛卡儿积连接,SELECT Student.*,SC.*,FROM Student,SC,SQL SERVER,中的多表连接,内部连接（,inner join,）,外部连接（,outer join,）,交叉联接（,cross join,）,内连接,内连接,是使用比较运算符比较要联接列中的值的联接，在,SQL-99,标准中，可以在,FROM,子句或,WHERE,子句中指定内部联接。这是,WHERE,子句中唯一一种,SQL-99,支持的联接类型。,WHERE,子句中指定的内部联接称为旧式内部联接。,例,查询选修,2,号课程且成绩在,90,分以上的所有学生。,SELECT Student.Sno,Sname,FROM Student inner join SC on,Student.Sno=SC.Sno,WHERE SC.Cno=2 AND SC.Grade=90,例,查询每个学生选修的课程及其成绩。,SELECT Student.Sno,Sname,Cname,Grade,FROM Student inner join,(,SC inner join course on SC.Cno=Course.Cno)on Student.Sno=SC.Sno,外连接,在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。如果我们查找所有学生的选课情况，没有选课学生的记录就不会出现在查询结果当中。但是有时我们想以,Student,表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，则只输出其基本情况信息，其选课信息为空值,(NULL),即可，这时就需要使用外连接,(Outer Join),。内部联接消除了与另一个表中的行不匹配的行。而外部联接会返回,FROM,子句中提到的至少一个表或视图中的所有行，只要这些行符合任何,WHERE,或,HAVING,搜索条件。将检索通过左外部联接引用的左表中的所有行，以及通过右外部联接引用的右表中的所有行。在完全外部联接中，将返回两个表的所有行。,Microsoft SQL Server 2005,对,FROM,子句中指定的外部联接使用下列,SQL-92,关键字：,LEFT OUTER JOIN,或,LEFT JOIN,RIGHT OUTER JOIN,或,RIGHT JOIN,FULL OUTER JOIN,或,FULL JOIN,例：查看所有学生的选课情况,SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade,FROM Student,left outer join,SC,on student.sno=sc.sno,或：,SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade,FROM SC,right outer join,Student,on student.sno=sc.sno,练习：查看所有课程的选修情况,表的别名,进行多表连接查询时，为了列名避免混肴，,SELECT,子句与,WHERE,子句中的属性名前都加上表名前缀。这样限制列名不仅费，而且容易出错，特别是在当连接的表具有较长名称时更是如此。为了简化任务，可以用表别名代替表名。表别名写在,FROM,子句的表名后面。一旦给表赋予了别名，该别名的使用就必须贯穿整个,SELECT,命令。,别名的使用,例：,SELECT,A,*，,B,*,FROM Student,A,，,SC,B,WHERE,A,.Sno=,B,Sno,；,在内外连接中均可以使用别名，以减少长表名带来的拼写麻烦。,例：,SELECT S.Sno,Sname,FROM Student S inner join SC C on,S.Sno=C.Sno,WHERE C.Cno=2 AND C.Grade=90,自身连接（特殊的多表连接）,连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自身连接。,例,查询每一门课的间接先修课,(,先修课的先修课,),Cno,Cname,Cpno,Ccredit,1,数据库,5,4,2,数学,2,3,信息系统,1,4,4,操作系统,6,3,5,数据结构,7,4,6,数据处理,2,7,C,语言,6,4,Cno,Cname,Cpno,Ccredit,1,数据库,5,4,2,数学,2,3,信息系统,1,4,4,操作系统,6,3,5,数据结构,7,4,6,数据处理,2,7,C,语言,6,4,FIRST,(Course),SECOND(Course),查询语句：,SELECT FIRST.Cno,SECOND.Cpno,FROM Crouse,FIRST,Crouse,SECOND,WHERE FIRST.Cpno=SECOND.Cno,子查询（嵌套查询）,定义：,子查询（嵌套查询）,：将一个查询块嵌套在另一个查询块的,WHERE,子句或,HAVING,短语的条件中的查询。,查询块：,一个,SELECT-FROM-WHERE,语句,上层查询块称,主查询,（外层查询、父查询）,下层查询块称,子查询,（内层查询）,SQL,允许多层嵌套查询,子查询的,SELECT,语句中不能使用,ORDER BY,子句,ORDER BY,子句只对最终查询结果排序。,求解方法：,由里向外,从一个表中返回单一值的子查询称为单一行子查询。,例 查询年龄大于学生平均年龄的学生的信息，,SELECT Sno,，,Sname,，,Ssex,Sage,Sdept,FROM Student,WHERE Sage,(SELECT AVG(Sage),FROM Student),单一行比较运算符：,=,=,=,练习：查询与刘晨同一个系的其他学生学号与姓名,1.,返回单一行的子查询,2.,多行子查询,多行子查询返回一行或者多行数据。,多行比较运算符,符号,意义,IN,等于子查询返回值中的任一值，则为,TRUE,NOT IN,不等于或不同于子查询返回值中的任一值，则为,TRUE,ANY,一个值与子查询返回值中的每一个值比较。只要有一个成立，则为,TRUE,ALL,一个值与子查询返回值中的每一个值比较，都成立时，则为,TRUE,查询所有被学生选修的课程的信息。,SELECT Cno,Cname,Cpno,Ccredit,FROM Course,WHERE Cno IN,(SELECT DISTINCT(Cno),FROM SC),说明：,(1),利用子查询查找,SC,表当中，被学生选修的课程,(2),外查询从,Course,表中查找课程号（,Cno,）在子查询中查找出的课程号范围的课程信息。,练习：查询所有未被学生学修的课程信息,查询选修了“数据结构”课程的同学的信息,例,.,查询其他系中比信息系（,IS,）任意一学生年龄小的学生姓名和年龄,带有,ANY,谓词的子查询,SELECT Sname,Sage FROM Student,WHERE Sage,ANY,(SELECT Sage FROM Student,WHERE Sdept=IS),AND SdeptIS,效果等同于：,带有集函数,MAX,的子查询,SELECT Sname,Sage FROM Student,WHERE Sage(SELECT,MAX,(Sage)FROM Student,WHERE Sdept=IS),AND SdeptIS,例,2.,查询其他系中比信息系（,IS,）所有学生年龄小的学生姓名和年龄,带有,ALL,谓词的子查询,SELECT Sname,Sage FROM Student,WHERE Sage,ALL,(SELECT Sage FROM Student,WHERE Sdept=IS),AND SdeptIS,效果等同于,：,带有集函数,MIN,的子查询,SELECT Sname,Sage FROM Student,WHERE Sage (SELECT,MIN,(Sage)FROM Student,WHERE Sdept=IS),AND SdeptIS,比较不同查询方式实现同一查询,例,查询选修了课程号,C2,的学生姓名,带有,ANY,谓词的子查询,1,）,SELECT Sname FROM Student,WHERE Sno=,ANY,(SELECT Sno FROM SC WHERE Cno=C2),带有,IN,谓词的子查询,2,）,SELECT Sname FROM Student,WHERE Sno,IN,(SELECT Sno FROM SC WHERE Cno=C2),连接查询,3,）,SELECT Sname FROM Student,SC,WHERE,Student.Sno=SC.Sno,AND,SC.Cno=C2,4.,带有,EXISTS,谓词的子查询,EXISTS,代表存在量词 。,带有,EXISTS,谓词的子查询不返回任何实际数据；,它只产生逻辑真值“,true”,或逻辑假值“,false”,。,由,EXISTS,引出的子查询，其目标列表达式通常都用*，因为带,EXISTS,的子查询只返回真值或假值，给出列名也无实际意义,与,EXISTS,谓词相对应的是,NOT EXISTS,谓词,例,.,查询所有选修了,1,号课程的学生姓名。,查询所有选修了,1,号课程的学生姓名涉及,Student,关系和,SC,关系，可以在,Student,关系中依次取每个元组的,Sno,值，用此,Student.Sno,值去检查,SC,关系，若,SC,中存在,(EXITS),这样的元组,(,其,SC,Sno,值等于用来检查的,Student,Sno,值,),并且其,SC,Cno,1,，则取此,Student,Sname,送入结果关系。,带有,EXISTS,短语的查询,SELECT Sname FROM Student,WHERE,EXISTS,(SELECT*FROM SC,WHERE Sno,Student.Sno,AND Cno,1),；,例：查询所有未修,1,号课程的学生姓名。,带有,EXISTS,短语的查询,SELECT Sname FROM Student,WHERE,NOT EXISTS,(SELECT*FROM SC,WHERE Sno,Student.Sno,AND Cno,1),；,相关子查询：,这类查询与前面的不相关子查询有一个明显区别，即子查询的查询条件依赖于外层父查询的某个属性值,(,在本例中是依赖于,Student,表的,Sno,值,),，我们称这类查询为相关子查询,(correlatedsubquery),。求解相关子查询不能像求解不相关子查询那样，一次将子查询求解出来，然后求解父查询。相关子查询的内层查询由于与外层查询有关，因此必须反复求值。,从概念上讲，相关子查询的一般处理过程如下。首先取外层查询中,Student,表的第一个元组，根据它与内层查询相关的属性值,(,即,Sno,值,),处理内层查询，若,WHERE,子句返回值为真,(,即内层查询结果非空,),，则取此元组放入结果表；然后再检查,Student,表的下一个元组；重复这一过程，直至,Student,表全部检查完毕为止。,利用子查询进行数据更新,利用子查询插入数据,例,.,将计算机系学生的学号加入选课表中,INSERT INTO SC(Sno),SELECT Sno FROM Student,WHERE Sdept=,计算机,利用子查询更新数据,UPDATE SC SET Grade=0,WHERE CS=,(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno),利用子查询删除数据,删除计算机系所有学生的选课记录,DELETE FROM SC WHERE CS=,(SELECT Sdept FROM Student,WHERE Student.Sno=SC.Sno),集合查询,若要把多个,SELECT,语句的结果合并为一个结果，可用集合操作来完成。,集合操作主要包括并操作,UNION,、交操作,INTERSECT,和差操作,EXCEPT,。,标准,SQL,中,没有直接提供集合交操作和集合差操作,，但可以用其他方法（,NOT,、,AND,、,OR,）来实现，会依查询不同而不同。,(ACCESS,不支持,INTERSECT,和,EXCEPT),例,.,查询选修了课程,1,或者选修了课程,2,的学生。,SELECT Sno FROM SC,WHERE Cno,1,UNION,SELECT Sno FROM SC,WHERE Cno,2,；,例,.,查询,CS,系的学生与年龄不大于,19,岁的学生的交集。,SELECT*FROM Student,WHERE Sdept,CS,AND,Sage19,；,例,.,查询选修课程,1,的学生集合与选修课程,2,的学生集合的差集。,实际上是查询选修了课程,1,但没选课程,2,的学生。,SELECT Sno FROM SC,WHERE Cno,1 AND Sno,NOT IN,(SELECT Sno FROM SC,WHERE Cno,2),；,