《第7章高级查询.ppt》由会员分享，可在线阅读，更多相关《第7章高级查询.ppt（23页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第7章 高级查询0 01 11 1本章目标n n掌握聚合函数MaxMaxMinMinSumSumAvgAvgcountcountn n掌握分组查询Group by和Having的使用n n连接内联接内联接外部联接外部联接自联接自联接1 12 22 2聚合函数 n n在查询时，除了显示记录的详细信息外，经常需在查询时，除了显示记录的详细信息外，经常需要对数据进行汇总和统计，要对数据进行汇总和统计，T-SQLT-SQL提供聚合函数提供聚合函数来实现这个功能来实现这个功能 n n聚合函数聚合函数 AVGAVGn n可计算一组值的平均值，是用一组值的总和除以非空值的计数，可计算一组值的平均值，是用一组
2、值的总和除以非空值的计数，只能用于数字列。只能用于数字列。SUMSUMn n返回表达式中所有值的和，返回表达式中所有值的和，SUMSUM只能用于数字列。只能用于数字列。COUNTCOUNTn n计算所有行并返回表中所有行的总数计算所有行并返回表中所有行的总数,返回数字类型值。返回数字类型值。COUNT(COUNT(列名列名)n n对组中每一行都计算并返回非空值的数量，返回数字类型值。对组中每一行都计算并返回非空值的数量，返回数字类型值。MAXMAXn n返回表达式的最大值，表达式通常是列名，返回与表达式相同返回表达式的最大值，表达式通常是列名，返回与表达式相同的数据类型。的数据类型。MINMI
3、Nn n返回表达式的最小值，表达式通常是列名，返回与表达式相同返回表达式的最小值，表达式通常是列名，返回与表达式相同的数据类型。的数据类型。2 23 33 3聚合函数n n聚合函数对一组值执行计算，并返回单个值，除COUNT之外，聚合函数都会忽略空值 3 34 44 4聚合函数【例7-1】求所有图书的平均价格 SELECT AVG(Price)as 平均价格平均价格 FROM BookInfo4 45 55 5聚合函数【例7-2】在BookInfo表中，统计2006年出版的图书数量、图书的总价值及按供应商提供的8折批发优惠价购买的图书总金额SELECT COUNT(bookid)图书数量图书数
4、量,SUM(price)图书总价值图书总价值,SUM(Price*0.8)购书总金额购书总金额 FROM BookInfoWHERE YEAR(PublishDate)=20065 56 66 6聚合函数SELECT COUNT(DISTINCT(BookID)FROMSalesDetails【例7-3】统计销售细表SalesDetails中销售人员销售了多少不同书号的图书。6 67 77 7聚合函数n n【例7-4】统计用户表UserInfo的用户数和用户的最大年龄和最小年龄。7 78 88 8分组统计n n在SQLServer中,按一个或多个列或表达式的值将一组选定行组合成一个摘要行集，针
5、对每一组返回一行 n n语法：GROUP BY 分组表达式分组表达式【例7-5】在BookInfo中，查询每类图书的总价SELECT CategoryID 类别类别,SUM(price)总价总价FROM BookInfo GROUP BY CategoryID思考：如何查询各出版社出版的图书的数量？8 89 99 9分组统计【例7-6】在SalesDetails销售细表中，显示每个书号的销售数量并按数量降序排列。分析：如下图数据所示：按书号统计销售数量，即按书号分组，对销售数量分析：如下图数据所示：按书号统计销售数量，即按书号分组，对销售数量进行计数进行计数SELECT MAX(Quantit
6、y)销售数量销售数量,BookID FROM SalesDetails GROUP BY BookID ORDER BY 销售数量销售数量 DESC9 910101010分组统计【例7-7】统计用户表UserInfo各年出生的人数 SELECT COUNT(*)Total person,DATEPART(YYYY,hiredate)年份年份FROM UserInfo GROUP BY DATEPART(YYYY,hiredate)思考：统计思考：统计2004年出生的人数？年出生的人数？101011111111使用having【例7-8】统计用户表UserInfo 2004年出生的人数 分析：在
7、上例的基础上，使用分析：在上例的基础上，使用HavingHaving对结果集进行筛选对结果集进行筛选 SELECT COUNT(*)Total person,DATEPART(YYYY,hiredate)年份年份FROM UserInfo GROUP BY DATEPART(YYYY,hiredate)HAVING DATEPART(YYYY,hiredate)=2004 111112121212分组查询小结n nWhere用来筛选From子句中指定的操作所产生的行n nGroup by用来分组Where子句的输出n nHaving子句用来从分组的结果中筛选行。n nHaving子句后可以跟统
8、计函数，而Where子句后则不可以。121213131313多表连接查询n n提问：用户需要查看图书的有关信息以及提问：用户需要查看图书的有关信息以及出版社信息？所需要的数据分别在两个表出版社信息？所需要的数据分别在两个表中存储，如何同时访问这两个表的数据？中存储，如何同时访问这两个表的数据？131314141414多表关联分类多表关联分类n n内联接内联接内联接内联接(INNER JOIN)(INNER JOIN)n n外联接外联接外联接外联接左外联接左外联接左外联接左外联接(LEFT JOIN)(LEFT JOIN)右外联接右外联接右外联接右外联接(RIGHT JOIN)(RIGHT JO
9、IN)n n自联接自联接自联接自联接(Self JOIN)(Self JOIN)141415151515内联接内联接n n一一一一.传统语法传统语法传统语法传统语法n n二二二二.SQL.SQL联接联接联接联接SELECT select_listFROM table1,table2WHERE table1.column=table2.columnSELECT 列名FROM 表名 INNER JOIN 表名JOIN 表名.ON 连接条件WHERE 条件151516161616内连接-多表连接select a.bookid,a.author,b.CategoryName,a.categoryid,
10、a.Price from BookInfo a inner join Category b on a.CategoryID=b.CategoryID161617171717内连接-多表连接【例7-11】查询2008年各种书籍的销售总金额，并按销售数量由高到低显示。分析：销售额等于销售价格（price）*销售数量(quantity)*折扣(discount)，销售日期在另一表中，所以涉及两个表，销售主表SalesMaster和销售细表SalesDetails SELECT a.SalesDate,SUM(b.Quantity*b.Discount*b.Price)销售总金额销售总金额 FROM
11、SalesMaster a Inner Join SalesDetails bON a.SalesMasterID=b.SalesMasterID GROUP BY a.SalesDate HAVING DATEPART(yyyy,a.SalesDate)=2008ORDER BY 销售总金额销售总金额 DESC 171718181818外部联接 n n 左外部联接 左向外部联接以左边的表为主，它的结果集包左向外部联接以左边的表为主，它的结果集包括括 LEFTLEFT子句中指定的左表的所有行，而不仅子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某一行在仅是联接列所匹配的行。如
12、果左表的某一行在右表中没有匹配行，则在关联的结果集行中，右表中没有匹配行，则在关联的结果集行中，来自右表的所有选择列表列均为空值来自右表的所有选择列表列均为空值 【例7-13】查询用户的售书情况，并显示所有用户的ID、姓名及所售书名。select a.UserID,a.UserName,a.sex,b.*from UserInfo a left join SalesMaster bon a.UserID=b.UserID order by b.UserID desc 181819191919右外连接n n右外部联接以右边的表为主，它的结果集包括 RIGHT 子句中指定的右表的所有行，而不 仅仅
13、是联接列所匹配的行。如果右表的某一行在左表中没有匹配行，则在关联的结果集行中，来自左表的所有选择列表列均为空值 select a.UserID,a.UserName,a.sex,b.*from UserInfo a right join SalesMaster bon a.UserID=b.UserID order by b.UserID desc 191920202020多表连接n n外连接只能用于两个表，内连接可实现三个以上表的连接【例例7-127-12】查询住在查询住在”广州广州”的用户销售的图书的书号、书的用户销售的图书的书号、书名、销售日期和销售数量以及用户的名字和地址名、销售日期和
14、销售数量以及用户的名字和地址SELECT a.SalesDate,a.UserID,c.UserName,c.Addr,b.Bookid FROM SalesMaster a INNER JOIN SalesDetails b ONa.SalesMasterID=b.SalesMasterID INNER JOIN UserInfo c ON a.UserID=c.UserID WHERE c.Addr like%广州广州%ORDER BY b.bookID 202021212121自联接自联接n n自连接把表通过自联接与自身联接。n n表自联接其实是把一个表逻辑上看成两个表，然后根据两个逻辑
15、表之间的关系来检索数据，为避免使用相同表名而导致错误，必须为表命名别名，以示区别【例7-15】查询销售价格高于B0002图书的书号、书名、出版日期和价格等信息SELECT a.bookid 书号书号,a.bookName 书名书名,a.publishdate 出版日期出版日期,a.price 价格价格a,b.price 价格价格bFROM BookInfo a INNER JOIN BookInfo b ON a.Price b.PriceWHERE b.BookID=B0002212122222222总结总结n n对数据进行统计使用对数据进行统计使用对数据进行统计使用对数据进行统计使用SQL
16、SQLSQLSQL内置的聚合函数内置的聚合函数内置的聚合函数内置的聚合函数 MaxMax MinMin SumSum AvgAvg CountCountn n对数据进行分组用对数据进行分组用对数据进行分组用对数据进行分组用GROUP BYGROUP BYGROUP BYGROUP BY，分组条件筛选用，分组条件筛选用，分组条件筛选用，分组条件筛选用HavingHavingHavingHavingn n查询的数据来自于多个表用关联查询查询的数据来自于多个表用关联查询查询的数据来自于多个表用关联查询查询的数据来自于多个表用关联查询n n关联查询关联查询连接连接 内联接内联接n n内联接筛选多表中完全匹配的行内联接筛选多表中完全匹配的行内联接筛选多表中完全匹配的行内联接筛选多表中完全匹配的行 外部联接外部联接n n左外联接以左表为主筛选数据左外联接以左表为主筛选数据左外联接以左表为主筛选数据左外联接以左表为主筛选数据n n右外联接以右表为主筛选数据右外联接以右表为主筛选数据右外联接以右表为主筛选数据右外联接以右表为主筛选数据 自联接自联接n n自联接将表自身通过别名关联自联接将表自身通过别名关联自联接将表自身通过别名关联自联接将表自身通过别名关联2222