第17讲数据库的连接查询和子查询

第5章数据查询 连接查询和子查询 主讲人 段利文 第17讲 复习 数据库的投影运算 选择列 用什么子句实现 数据库的选择运算 选择行 用什么子句实现 分类汇总用什么子句实现 对分类汇总生成的组进行条件筛选用什么子句 明细汇总用什么子句实现 compute by子句必须与什么子句连用 select子句 where子句 groupby子句 compute子句 orderby子句 having子句 复习 数据汇总查询的语法格式用聚合函数 groupby子句 compute by子句select列名表from表名 n where条件 groupby列名 having逻辑表达式 orderby列名 compute聚集函数 by列名 投影运算 选择运算 连接运算 排序 第5章数据查询 连接查询和子查询学习目标 认知目标 了解数据库的连接运算了解子查询的基本含义能力目标 掌握连接查询的方法 重点 难点 理解子查询的方法 难点 一 连接查询 多表查询 连接查询的含义 从多个相关的表中查询数据 用from子句实现 连接运算 把多个表中的行按给定的条件进行拼接从而形成新表 连接运算的类型 内连接 掌握 外连接 左外连接 右外连接 全外连接 理解 自连接 理解 无限制连接 了解 略 一 连接查询 多表查询 1 内连接 自然连接 含义 将多个表中的共享列值进行比较 把表中满足连接条件的行组合起来 作为查询结果 例如 请将xsqk和xs kc表进行连接 思考 这两张表为什么可以进行连接 连接的条件应该是什么 一 连接查询 多表查询 1 内连接 自然连接 语法格式有两种 ANSI语法形式 select列名表from表1 inner join表2on表1 列名1 表2 列名2SQLServer语法形式 select列名表from表1 表2where表1 列名1 表2 列名2 表示内连接 可省略 连接条件 当两个表中的列名相同时 需加上表名修饰 否则 可省去表名 连接条件 一 连接查询 多表查询 1 内连接 自然连接 实例1 查询不及格学生的学号 姓名 课程号 成绩信息 教材P113 分析 确定要查询内容的来源 确定连接条件 要查询的表通过什么列名相关联 selectxsqk 学号 姓名 课程号 成绩fromxsqk xs kcwherexsqk 学号 xs kc 学号and成绩 60 为什么要加上表名前缀 而其它字段前不要 因学号在两个表中都有 所以需指明所属的表名 一 连接查询 多表查询 1 内连接 自然连接 课堂练习1 查询不及格学生的学号 课程名 授课教师 开课学期的信息 select学号 xs kc 课程号 授课教师 开课学期 成绩fromkc xs kcwherexs kc 课程号 kc 课程号and成绩 60 select学号 xs kc 课程号 授课教师 开课学期 成绩fromkcjoinxs kconxs kc 课程号 kc 课程号and成绩 60 请思考 xsqk kc xs kc三张表应如何连接 一 连接查询 多表查询 2 外连接 含义 在内连接中 只把两个表中满足条件的行显示出来 而在外连接中 可以将不满足条件的行也显示出来 外连接的类型左外连接 保留左边表中不匹配的行 右外连接 保留右边表中不匹配的行 全外连接 保留两表中不匹配的行 一 连接查询 多表查询 2 外连接 左外连接的查询结果 右外连接的查询结果 一 连接查询 多表查询 2 外连接 全外连接的查询结果 一 连接查询 多表查询 2 外连接 1 左外连接 ANSI语法格式select列名表from表1left outer join表2on表1 列名1 表2 列名2SQLServer语法格式select列名表from表1 表2where表1 列名1 表2 列名2实例 阅读教材P128例5 36 一 连接查询 多表查询 2 外连接 2 右外连接 ANSI语法格式 select列名表from表1right outer join表2on表1 列名1 表2 列名2SQLServer语法格式 select列名表from表1 表2where表1 列名1 表2 列名2实例 阅读教材P128例5 37 一 连接查询 多表查询 2 外连接 3 全外连接 语法格式 只有一种 select列名表from表1full outer join表2on表1 列名1 表2 列名2说明 只有ANSI语法格式SQLServer无全外连接语句 实例 阅读教材P130例5 38 一 连接查询 多表查询 3 自连接 含义 将同一个表的不同行连接起来 自连接可以看作一张表的两个副本之间进行的连接 要求 在自连接中 必须为表指定两个别名 使之在逻辑上成为两张表 实例 阅读教材P130例5 39 二 子查询 含义 是指一条select语句作为另一条select语句的一部分 外层的select语句被称为外部查询 内层的select语句被称为内部查询 或子查询 使用注意事项 P119 子查询出现的形式 多数情况下 子查询出现在外部查询的where子句中 并与比较运算符 列表运算符in 存在运算符exists等一起构成查询条件 完成有关操作 在新增列中的子查询 二 子查询 类型 相关子查询含义 内查询的执行依赖于外查询 执行过程 将外部查询到的第1条记录中被引用列的列值 传递给子查询 然后执行子查询 根据子查询的执行结果判断外部查询条件是否满足要求 若是则显示结果行 直到外部查询处理完外表的每一行 嵌套子查询含义 内查询的执行不依赖于外查询 执行过程 先执行子查询 子查询得到的结果集不被显示出来 而是传递给外部查询 作为外部查询的条件来使用 执行外部查询 并显示查询结果 二 子查询 说明 不管是相关子查询还是嵌套子查询 其外查询用于显示查询结果集 而内查询的结果用来作为外查询的查询条件 语法格式 select字段列表from表名where select from where 运算符表达式 二 子查询1 使用比较运算的子查询 例 5 56 查询平均分低于60的学生学号和姓名 select学号 姓名fromxsqkwhere selectavg 成绩 fromxs kcwherexs kc 学号 xsqk 学号 60 该子查询属于相关子查询 每执行一次 只返回单列单个值 二 子查询1 使用比较运算的子查询 课堂练习2 查询最高分在80分以上的学号 姓名 联系电话 xsqk xs kc select学号 姓名 联系电话fromxsqkawhere selectmax 成绩 fromxs kcbwhereb 学号 a 学号 80 二 子查询2 使用all any关键字的子查询 例5 41 查询有不及格学生的课程的授课教师select课程号 授课教师fromkcwhere课程号 any select课程号fromxs kcwhere成绩 60 该子查询属于嵌套子查询 执行后 返回单列多个值 列表 表示等于列表中的任一个值 二 子查询2 使用all any关键字的子查询 例5 42 查询每门课程的最低分 select fromxs kcawhere成绩 all select成绩fromxs kcbwhereb 课程号 a 课程号 二 子查询3 使用列表运算符的子查询 例5 43 查询无不及格学生的课程的授课教师select课程号 授课教师fromkcwhere课程号notin select课程号fromxs kcwhere成绩 60 二 子查询4 使用exists运算符的子查询 例5 44 至少一门课程不及格的学生信息 selectdistinct学号 姓名fromxsqkawhereexists select fromxs kcbwhereb 学号 a 学号and成绩 60 exists子查询的功能是 判断子查询结果集中是否有数据行返回 若有则返回true 否则返回false 注意 不产生其他任何实际值 由于不需要在这种查询中返回具体值 所以这种子查询的选择列表常用 SELECT 格式 二 子查询 课堂练习 查询至少有2门课程的成绩在80分以上的学生基本信息 xsqk xs kc select学号 姓名fromxsqkwhere selectcount 课程号 fromxs kcwherexsqk 学号 xs kc 学号and成绩 80 2 本课小结