关系数据库与SQL Server 2008 第2版 教学课件 ppt 作者 龚小勇 第17讲 连接查询和子查询

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