SQL Server2005分组统计与多表关联查询.ppt

第五章 分组统计与多表关联查询 回顾 编写SQL语句查询一个月前的订单信息 查询作者表中姓名以 李 开头的作者信息 查询前20 的图书信息 预习检查 什么是分组统计 多表联接查询分为哪几种 为什么要使用多表联接查询 提问 本章任务 使用使用T SQL语句对Authors和Books表进汇总统计 对多表进行关联查询 本章目标 掌握聚合函数的使用 掌握GroupBy进行分组统计 掌握多表联接查询 聚合函数 会员表中存储了所有会员的积分 如果想计算出会员的最高分 平均积分 积分大于300的会员个数 怎么办 会员表Customers 聚合函数 SUM AVG MAX MIN 1 SUM 2 AVG 3 MAX和MIN 聚合函数 COUNT 4 COUNT 表达式 返回结果集的非空行数 MAX和MIN除了计算数值列 还可以用于计算字符型以及日期时间类型数据列 其中 表达式 可以是 列名 1 COUNT 返回表中所有数据行的记录数 2 COUNT 列名 返回指定列非空值个数 示例 聚合函数 如果想统计居住在不同城市的人员个数 编写下面代码正确吗 执行出现错误 解决这样的问题需要使用GROUPBY分组统计 什么是分组统计 一家水果店的一天销售情况 要统计各类水果的销售额 示例 按水果的种类分组 然后分别汇总 分组统计 分组统计牢记 SELECT语句中除聚合函数外所有列 应写在GroupBy语句后面 否则将出现错误 语法 SELECT 聚合函数 FROM表 WHERE 条件GROUPBY 其中可以有多个字段 各字段用逗号隔开 它表示按哪些列进行分组 分组统计应用 统计会员表中居住在不同城市的人员个数 提问 分析 应该按照City列进行分组分组后使用Count 聚合函数汇总行数 统计结果 多列分组统计 统计不同城市不同性别的会员个数 提问 分析 统计结果集应包含 城市 性别和人数3列信息 很明显应该按照City和Sex两列进行分组 分组后仍使用Count 聚合函数汇总行数 统计结果 多列分组统计 如果代码写成这样 结果会怎样 所以牢记 SELECT语句中除聚合函数外所有列 应写在GroupBy语句后面 执行错误 Sex 使用HAVING子句对分组结果再次过滤 继续考虑上面的查询 如果 要统计不同城市不同性别的会员个数 并且只显示会员个数大于2个的记录 如何处理呢 需要对分组后的数据进行筛选 筛选条件Count 2 对分组后的数据进行筛选需要使用Having子句 统计结果 使用HAVING子句对分组结果再次过滤 WHERE子句 从数据源中去掉不符合其搜索条件的数据GROUPBY子句 搜集数据行到各个组中 统计函数为各个组计算统计值HAVING子句 去掉不符合其组搜索条件的各组数据行 小节 如下表A 查询各学生的平均成绩 并显示学生成绩出现2次以上的信息 提问 多表关联查询 为什么要用多表关联查询 这是从图书表中查询出的信息 如果时间长了你还能记得作者编号1 4 11 12都代表是哪个作者吗 实际上最好显示作者姓名 而作者姓名是存储在作者表中 如何同时从这两个表中取得数据呢 多表关联查询 内联接 INNERJOIN 外联接 左外联接 LEFTJOIN 右外联接 RIGHTJOIN 完整外联接 FULLJOIN 交叉联接 CROSSJOIN Authors 内连接 Books 内连接查询出的是两个表公共列共有的记录 内连接 语法 SELECTFROM表1INNERJOIN表2ON表1 列名条件运算符 表2 列名 WHERE条件 ORDERBY排序列 having 其中 ON表1 列名条件运算符表2 列名 中 条件运算符常用的是 表1 列名和表2 列名 分别是两个表的公共列 示例 Books与Authors内连接SQL语句 内连接另外一种写法 SQLServer2005还兼容以前版本的内连接语法 即使用WHERE子句进行内连接 多表内连接 语法 SELECTFROM 表1INNERJOIN表2ON表1 列名条件运算符表2 列名 INNERJOIN表3ON表1 列名条件运算符表3 列名 WHERE条件 ORDERBY排序列 示例 左外连接 语法 SELECTFROM左表LEFT OUTER JOIN右表ON左表 列名条件运算符右表 列名 WHERE条件 ORDERBY排序列 示例 使用左外连接查询Books与Authors表中的数据 右表Authors 从 左外连接 左表 Books 主 左外连接是以左表为主表 去关联右表 从表 结果集中包含主表所有数据行 如果主表的某行在从表中没有匹配行时 则从表的选择列为NULL值 左外连接 如果把上面的语句改如下代码 查询结果一样吗 为什么 右外连接 语法 SELECTFROM左表RIGHT OUTER JOIN右表ON左表 列名条件运算符右表 列名 WHERE条件 ORDERBY排序列 示例 使用右外连接查询Books与Authors表中的数据 右表Authors 主 右外连接 左表 Books 从 右外连接是以右表为主表 去关联左表 从表 结果集中包含主表所有数据行 如果主表的某行在从表中没有匹配行时 则从表的选择列为NULL值 完全连接 语法 SELECTFROM左表FULL OUTER JOIN右表ON左表 列名条件运算符右表 列名 WHERE条件 ORDERBY排序列 示例 使用完全连接查询Books与Authors表中的数据 完全连接左表和右表中所有行 当某行数据在另一个表中没有匹配时 则另一个表的选择列值为NULL 案例分析 在查找作者表中哪些作者还没有出过书 并将这些作者信息保存到新表TempAuthors中 分析 先查找内容 然后插入到新表中 可以使用SELECT INTO 语句 关键是能查询出哪些作者还没有出过书 这涉及到2个表 一个是作者表 另一个是图书表 查询出作者表中作者编号在图书表中没有出现过的 以作者表为主表 图书表为从表 使用左外连