SQLServer教案第09周-连接查询和子查询

数据库原理与应用——SQLServer2005教案邹竞授课日期年月日第6周授课形式讲课授课时数4章节名称第08章简单查询教学目的与要求①掌握使用Join…On…子句进行多表自然连接查询②掌握左外连接、右外连接和完全外连接③掌握自连接查询④掌握子查询的分类（单行子查询、多行子查询）⑤掌握在Where子句中使用子查询作为查询条件⑥掌握在From子句中使用子查询作为数据源⑦掌握使用子查询在InsertInto语句中为数据表插入另一表中的已有数据。⑧掌握使用子查询在update语句中进行基于外表条件的数据修改。⑨掌握使用子查询在deletefrom语句中进行基于外表条件的数据删除。教学重点连接查询、自查询教学难点自连接查询教学方法和手段讲授法结合课堂实例分析讨论教学过程与组织导入新课我们已经学习了简单的数据查询。前面所讲的数据查询，数据的来源都在一张表中。更多的情况下，需要查询的数据往往分布在多张表，这就需要进行关系代数的连接运算，在连接的结果上进行查询。讲授新课第09章连接查询和子查询第01节多表连接查询多表查询又称作连接查询，或称作联合查询。连接查询又可分成内连接查询、外连接查询等等。内连接查询中，又可分为等值连接、非等值连接、自连接等子类。外连接中，又可分为左外连接、右外连接等。9.1.1内连接（自然连接）两个表的内连接查询是指，从两个表中的相关字段中提取信息作为查询的条件，如果满足查询的条件，就从两个表中的相关记录中，选择需要的信息，连接成一个元组，置于查询结果集之中，这就是内连接的主要作用。内连接语句的格式和语法如下：FROM表1[INNER]JOIN表2ON连接条件表达式连接查询的语句，主要是在单表查询的语句中，对数据源部分进行了语法成份的扩展。用以申明是哪两个表联合查询。ON之后的条件表达式，说明了连接的条件。连接条件表达式的格式有特殊要求。其一般格式为：[表名1.]列名1比较运算符[表名2.]列名2如果某个列所表示的数据在查询语句涉及到的多张表中存在，且列名相同，使用时就应该在其前面冠上表名，并以圆点（.）隔开。当确认某个列名只出现在一张表时，它前面的表名才可以省略。连接查询语句中，SELECT之后的列名列表也应这样表示：SELECT[表名.]列名1,…,[表名.]列名nFROM…连接条件中，两个表中的比较列，必须是语义相同的列，才可以构成有意义比较条件。例：查询每个学生的情况和选课情况Select*FromStudentInnerJoinSCOnStudent.SNo=SC.SNo数据库原理与应用——SQLServer2005教案邹竞例：在上例中，只选取学号、姓名、课程号和成绩SelectSC.SNo,Student.SName,SC.CNo,SC.GradeFromStudentInnerJoinSCOnStudent.SNo=SC.SNo例：查询系编号为g001和g005各学生的选课情况和每门课的成绩，将每个系的名单排在一起SelectStudent.SName,SC.CNo,SC.GradeFromStudentInnerJoinSCOnStudent.SNo=SC.SNoWhereStudent.Depart='g001'OrStudent.Depart='g005'OrderByStudent.Depart;例：在上例中，使用表的别名SelectS.SName,SC.CNo,SC.GradeFromStudentSInnerJoinSCOnS.SNo=SC.SNoWhereS.Depart='g001'OrS.Depart='g005'OrderByS.Depart;例：查询计算机系中选修了课程名为VB的课程的学生姓名和成绩SelectS.SName,C.CName,SC.GradeFromStudentSJoinSCOnS.SNo=SC.SNoJoinCourseCOnC.CNo=SC.CNoWhereS.Depart='g001'AndC.CName='VB'例：查询所有选修了VB课程的学生姓名和所在系编号SelectS.SName,S.DepartFromStudentSJoinSCOnS.SNo=SC.SNoJoinCourseCOnC.CNo=SC.CNoWhereC.CName='VB'9.1.2自连接连接操作不仅可以在不同的表上进行，而且在同一张表内可以进行自身连接，即将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。在自连接中，必须为表指定两个别名，使之在逻辑上成为两张表。自连接的处理思想是，将物理上的一个表，从逻辑上视同两个表。使用自连接必须为同一张表取两个别名，让一个表变成表名不同的两个表，仅此而已。其余的工作与两表连接查询无任何区别。例：查询与冯巩在同一个系学习的学生的姓名和系编号SelectS2.SName,S2.DepartFromStudentS1JoinStudentS2OnS1.Depart=S2.DepartWhereS1.SName='冯巩'AndS2.SName'冯巩'9.1.3外连接外连接的思想是，当一个表中的元组，如果在另一表中找不到与其连接条件相匹配的元组时，并不将此元组的数据抛弃，而是将该元组的相关信息（结果中需要的列值）与连接的另一个表的空值列（全部取空值），也在形式上连接成新的元组，并将它置于查询结果中。这样一来，就达到了用户想保留某些信息的目的。外连接是左外连接和右外连接的统称。语句结构和语法规则如下：FROM表1LEFT|RIGHT[OUTER]JOIN表2ON连接条件LEFT[OUTER]JOIN称作左外连接，RIGHT[OUTER]JOIN称作右外连接。左外连接将在查询结果中，除了输出满足自然连接条件的结果外，还输出表1里面那些在表2找不到对应匹配的数据行的相关信息。右外连接将在查询结果中，除了输出满足自然连接条件的结果外，还输出表2里面那些在表1找不到对应匹配的数据行的相关信息。例：查询每个学生的学号、姓名、所选课程、成绩，即使有学生没有选课，也要列出他的信息SelectS.No,S.SName,SC.CNo,SC.GradeFromStudentSLeftJoinSCOnS.SNo=SC.SNo例：将例5-42的左外连接改成右外连接，观察查询结果有什么不同SelectS.No,S.SName,SC.CNo,SC.GradeFromStudentSRightJoinSCOnS.SNo=SC.SNo9.1.4交叉连接数据库原理与应用——SQLServer2005教案邹竞交叉连接也叫非限制连接，它将两个表不加任何约束地组合起来。在数学上，就是两个表的笛卡尔积。交叉连接后得到的结果集的行数是两个被连接表的行数的乘积。语法如下：SELECT字段列表FROM表1CROSSJOIN表2或SELECT字段列表FROM表1，表2例：列出所有学生的有可能的选课组合SELECTS.SNO,S.SNAME,C.CNO,C.CNAMEFROMSTUDENTSCROSSJOINCOURSEC在实际应用中使用交叉连接产生的结果集一般没有什么意义，但在数据库的数学模式上有重要的作用。第02节子查询8.2.1子查询的概念和分类在SQL语言中，将一个SELECTFROMWHERE语句称作一个查询块。如果将一个SELECT语句嵌套在下列语句之中，则称这样一类语句为子查询语句或内层查询语句，而位于外面（包含子查询语句）的语句则称为主查询语句或外层查询语句。可以作为主查询的语句的有以下几个：（1）SELECT语句；（2）INSERT语句；（3）UPDATE语句；（4）DELETE语句。子查询可以嵌套。子查询往往作为在某一查询的Where条件句或Having子句的一部分，或者作为查询的某个数据源。子查询分为两种：单行子查询（子查询的返回结果只有一行）和多行子查询（字查询的结果在两行或者两行以上）。8.2.2使用子查询的结果作比较当确认一个子查询语句的查询结果是一个单一值时，这样的子查询语句可以置于任何带比较运算符的条件表达式的一端，构成一个条件表达式。比较运算符可以是=、、、=、=、、!=等中的一个。例：查询选修了C02号课程且成绩比此课程的平均分高的学生的学号、成绩SelectSNo,GradeFromSCWhereCNo='C02'AndGrade(SelectAvg(Grade)fromSCWhereCNo='C02')该语句的执行过程如下（1）先执行括号内的子查询；（2）将子查询的结果代入外层查询中；（3）执行外层查询语句。需要注意的是，当子查询的结果作为外层查询语句的条件的一部分，且外层查询语句的条件使用到了“=、、、=、=、、!=”这样的运算符，必须使用单行子查询。8.2.3使用子查询的结果作集合当确认一个子查询语句的结果集中的值的行数大于一（多行子查询）的情况下，它不能与比较运算符一起使用，而只能使用集合运算符IN或NOTIN，将一个表达式的值与查询返回的结果集进行比较。如果表达式的值在子查询的结果集合中，条件为真，反之，条件为假。注意，子查询语句中只能返回一组类型相同的值，且要求其返回值的数据类型与测试表达式的数据类型一致。数据库原理与应用——SQLServer2005教案邹竞例：查询与冯巩在同一个系学习的学生的学号、姓名和系编号SelectSNo,SName,DepartFromStudentWhereDepartIn(SelectDepartFromStudentWhereSName='冯巩')例：查询C06号课程的成绩90分以上的学号和姓名SelectSNo,SNameFromStudentWhereSNoIn(SelectSNoFromSCWhereGrade=90AndCNo='C06')或者SelectS.SNo,S.SNameFromStudentSJoinSCOnS.SNo=SC.SNoWhereSC.Grade90AndSC.CNo='C06'例：查询选修了'数据库基础'课程的学生学号和姓名SelectS.SNo,S.SNameFromStudentSJoinSCOnS.SNo=SC.SNoJoinCourseCOnC.CNo=SC.CNoWhereCName='数据库基础'或者SelectSno,SNameFromStudentWhereSNoIn(SelectSNoFromSCWhereCNo=(SelectCNoFromCourseWhereCName='数据库基础'))8.2.3使用子查询进行逻辑测试使用子查询进行逻辑测试，是使用存在性谓词EXISTS。在这个谓词后面带一个子查询语句，这一语句执行后并不返回具体数据，而只返回一个逻辑值，或者返回“真”，或者返回“假”。例：查询选修了课程C20的学生姓名。SelectSNameFromStudentWhereExist(Select*FromSCWhereSNo=Student.SNoAndCNo='C20')该语句的处理过程如下：（1）找到外层Student表的第1行，根据其SNo值处理内层查询；（2）用外层的值执行内层查询，如果有符合条件的数据，则Exist返回True，否则返回False。如果Exist返回True，则外层结果中的当前数据为符合条件的结果；否则，是不符合条件的结果；（3）顺序处理外层Student表的第2行、第3行……直到处理完所有的行。如果在Exist前加上Not，则表示，当子查询语句中不存在任何满足条件的记录时，返回Ture，当子查询语句存在满足条件的记录时，返回False。例：查询没有选修C01课程的学生姓名和所在系编号SelectSName,DepartFromStudentSWhereNotExist(Select*FromSCWhereSC.SNo=S.SNoAndCNo='C01')第03节多个相同模式的查询结果的并、交、差我们在关系代数中提到了两个相同关系模式的关系，能够进行交、并、差运算。在