项目05SQL连接查询和子查询(补3)

SQLServer2008项目数据查询——连接查询和子查询SQL2第5章数据查询—连接查询和子查询SQL复习:1.数据库的投影运算(选择列)用什么子句实现？2.数据库的选择运算(选择行)用什么子句实现？3.分类汇总用什么子句实现？4.对分类汇总生成的组进行条件筛选用什么子句？5.明细汇总用什么子句实现？6.compute…by子句必须与什么子句连用？select子句where子句groupby子句computeby子句orderby子句having子句3第5章数据查询—连接查询和子查询SQL数据汇总查询的语法格式用聚合函数、groupby子句、compute…by子句select列名表from表名[,……n][where条件][groupby列名][having逻辑表达式][orderby列名][compute聚集函数[by列名]]投影运算选择运算分类汇总连接运算排序明细汇总4第5章数据查询—连接查询和子查询SQL认知目标：了解数据库的连接运算了解子查询的基本含义能力目标:掌握连接查询的方法(重点、难点)理解子查询的方法(难点)5第5章数据查询—连接查询和子查询SQL一、连接查询(多表查询）连接查询的含义：从多个相关的表中查询数据（用from子句实现）。连接运算：把多个表中的行按给定的条件进行连接从而形成新表。连接运算的类型：内连接(掌握）外连接(左外连接、右外连接、全外连接）(理解)自连接(理解)无限制连接(了解，略)6第5章数据查询—连接查询和子查询SQL1.内连接含义：将多个表中的共享列值进行比较，把表中满足连接条件的行组合起来，作为查询结果。例如：请将xs和cj表进行连接。内连接查询结果思考：•这两张表为什么可以进行连接？•连接的条件应该是什么？7第5章数据查询—连接查询和子查询SQL语法格式有两种:ANSI语法形式：select列名表from表1[inner]join表2on连接条件where查询条件SQLServer语法形式：select列名表from表1,表2where查询条件连接条件表示内连接(可省略)连接条件：当两个表中的列名相同时，需加上表名修饰；否则，可省去表名。连接条件8第5章数据查询—连接查询和子查询SQL实例1：查询不及格学生的学号、姓名、课程号、成绩信息。分析：确定要查询内容的来源。确定连接条件(要查询的表通过什么列名相关联)。select学号,姓名,课程号,成绩fromxs,cjwherexs.学号=cj.学号and成绩60检查有没有多张表的共有字段，需要加表名前缀？9第5章数据查询—连接查询和子查询SQL课堂练习1：查询不及格学生的学号、课程名、授课教师、开课学期的信息。select学号,cj.课程号,授课教师,开课学期,成绩fromkc,cjwherecj.课程号=kc.课程号and成绩60select学号,cj.课程号,授课教师,开课学期,成绩fromkcjoincjoncj.课程号=kc.课程号where成绩60请思考：xs、kc、cj三张表应如何连接？10第5章数据查询—连接查询和子查询SQL2.外连接含义：在内连接中，只把两个表中满足条件的行显示出来。而在外连接中，可以将不满足条件的行也显示出来。外连接的类型左外连接：保留左边表中不匹配的行。右外连接：保留右边表中不匹配的行。全外连接：保留两表中不匹配的行。11第5章数据查询—连接查询和子查询SQL左外连接的查询结果:右外连接的查询结果:左外连接查询结果右外连接查询结果12第5章数据查询—连接查询和子查询SQL全外连接的查询结果:全外连接查询结果13第5章数据查询—连接查询和子查询SQL2.外连接—(1)左外连接1.ANSI语法格式select列名表from表1left[outer]join表2on连接条件where查询条件2.SQLServer语法格式select列名表from表1,表2where查询条件连接条件实例：阅读教材例5-37。14第5章数据查询—连接查询和子查询SQL2.外连接—(2)右外连接1.ANSI语法格式：select列名表from表1right[outer]join表2on连接条件where查询条件2.SQLServer语法格式：select列名表from表1,表2where查询条件连接条件实例：阅读教材例5-38。15第5章数据查询—连接查询和子查询SQL2.外连接—(3)全外连接语法格式(只有一种)：select列名表from表1full[outer]join表2on连接条件where查询条件说明：只有ANSI语法格式SQLServer无全外连接语句。实例：阅读教材例5-39。16第5章数据查询—连接查询和子查询SQL3.自连接含义：将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间进行的连接。要求：在自连接中，必须为表指定两个别名，使之在逻辑上成为两张表。实例：阅读教材例5-40。17第5章数据查询—连接查询和子查询SQL二、子查询含义：是指在一个select语句中再包含另一个select语句，外层的select语句被称为外部查询，内层的select语句被称为内部查询(或子查询)。使用注意事项：子查询出现的形式：多数情况下，子查询出现在外部查询的where子句中，并与比较运算符、列表运算符in、存在运算符exists等一起构成查询条件，完成有关操作。在新增列中的子查询。18第5章数据查询—连接查询和子查询SQL语法格式：select列名from表名where(select…from…where…)运算符表达式该子查询用来作为外部查询的查询条件19第5章数据查询—连接查询和子查询SQL类型1.相关子查询含义：内部查询的执行依赖于外部查询。执行过程：将外部查询到的第1条记录中被引用列的列值，传递给子查询，然后执行子查询。根据子查询的执行结果判断外部查询条件是否满足要求，若是则显示结果行，直到外部查询处理完外表的每一行。2.嵌套子查询含义：内部查询的执行不依赖于外部查询。执行过程：先执行子查询，子查询得到的结果集不被显示出来，而是传递给外部查询，作为外部查询的条件来使用。执行外部查询，如果外部查询条件成立则显示查询结果。20第5章数据查询—连接查询和子查询SQL说明：不管是相关子查询还是嵌套子查询，其外部查询用于显示查询结果集，而内部查询的结果用来作为外部查询的查询条件。21第5章数据查询—连接查询和子查询SQL1.使用比较运算的子查询例(5-41)：查询平均分低于60的学生学号和姓名。select学号,姓名fromxswhere(selectavg(成绩)fromcjwherecj.学号=xs.学号)60该子查询属于相关子查询，每执行一次，只返回单列单个值。子查询，其结果作为外查询的条件外查询，用于显示结果22第5章数据查询—连接查询和子查询SQL课堂练习2：查询最高分在80分以上的学号、姓名、联系电话（xs,cj）select学号,姓名,联系电话fromxsawhere(selectmax(成绩)fromcjbwhereb.学号=a.学号)=8023第5章数据查询—连接查询和子查询SQL2.使用all、any关键字的子查询例5-42：查询有不及格学生的课程的授课教师select课程号,授课教师fromkcwhere课程号=any(select课程号fromcjwhere成绩60)该子查询属于嵌套子查询，执行后，返回单列多个值(列表)表示等于列表中的任一个值子查询，其结果作为外查询的条件外查询，用于显示结果24第5章数据查询—连接查询和子查询SQL例5-43：查询每门课程的最低分。select*fromcjawhere成绩=all(select成绩fromcjbwhereb.课程号=a.课程号)25第5章数据查询—连接查询和子查询SQL3.使用列表运算符的子查询例5-44：查询有不及格学生的课程的授课教师select课程号,授课教师fromkcwhere课程号in(select课程号fromcjwhere成绩60)26第5章数据查询—连接查询和子查询SQL4.使用exists运算符的子查询例5-45：查询至少有一门课程不及格的学生信息selectdistinct学号,姓名fromxsawhereexists(select*fromcjbwhereb.学号=a.学号and成绩60)exists子查询的功能是：判断子查询结果集中是否有数据行返回，若有则返回true，否则返回false。（注意：不产生其他任何实际值）由于不需要在这种查询中返回具体值，所以这种子查询的选择列表常用“SELECT*”格式。27第5章数据查询—连接查询和子查询SQL课堂练习3：查询至少有2门课程的成绩在80分以上的学生基本信息（xs,cj）。select学号,姓名fromxswhere(selectcount(课程号)fromcjwherexs.学号=cj.学号and成绩80)=228第5章数据查询—连接查询和子查询SQL本课小结连接查询(多表查询）内连接：只显示条件匹配的连接行。外连接：既显示条件匹配的连接行，还可显示条件不匹配的行。左外连接：保留左表中不匹配的行。右外连接：保留右表中不匹配的行。全外连接：保留两边表中不匹配的行。自连接：将同一张表中条件匹配的行进行连接。子查询相关子查询嵌套子查询29第5章数据查询—连接查询和子查询SQL课余作业布置课外练习：教材课外实践任务3任务4上机准备：连接查询和子查询预习：第6章视图与索引