第14章T-SQL高级查询.

第14章T-SQL高级查询本章内容使用聚合函数（AggregateFunctions）使用groupby分组使用having筛选结果连接多个表使用子查询使用聚合函数什么是聚合函数？聚合函数对一组值执行计算并返回单一的值。有哪些聚合函数？AVG返回组中值的平均值。空值将被忽略。COUNT返回组中项目的数量。COUNT(*)返回符合搜索条件的行的数目，包括含有空值的行MAX返回表达式的最大值。MIN返回表达式的最小值。SUM返回表达式中所有值的和。使用聚合函数举例（一）Count语法：COUNT({[ALL|DISTINCT]expression]|*})查询titles表中书名的总数USEpubsGOSELECTCOUNT(title)FROMtitlesGO使用聚合函数举例（二）AVG语法：AVG([ALL|DISTINCT]expression)计算出titles表中所有商业类书籍的平均价格USEpubsGOSELECTAVG(price)FROMtitlesWHEREtype='business'使用聚合函数注意除COUNT(*)函数之外，聚合函数忽略空值。举例：SELECTCOUNT(*)FROMtitlesSELECTCOUNT(price)FROMtitlesSelectavg(price)fromtitlesSelectsum(price)/count(price)fromtitlesSelectsum(price)/count(*)fromtitles课堂练习1.找出pubs数据库titles表中计算机类图书中价格最高的图书的价格。2.查询titles表中有几类图书。使用groupby分组什么时候使用groupby子句？当需要按某一列数据的值进行分类，在分类的基础上对数据进行统计，就需要使用groupby子句。使用groupby举例要求：查询pub数据库的titles表，按书的种类分类，求出每种类型的书的平均价格。USEpubsGOSELECTtype,avg(price)FROMtitlesGROUPBYtypeGO使用groupby注意：指定GROUPBY时，选择列表中任一非聚合表达式内的所有列都应包含在GROUPBY列表中。选择列表中的列如果不在聚合函数里面，就必须在GROUPBY列表里面。否则语法上虽然不会出错，但是执行起来会出错。使用groupby举例（二）USEpubsGOSELECTtype,pub_id,avg(price)FROMtitlesgroupbytypeGOERROR!使用groupby举例（三）USEpubsGOSELECTtype,pub_id,avg(price)FROMtitlesgroupbytype,pub_idorderby1,2GOOK!课堂练习3.查询pubs数据库authors表，要求按照州进行分类，查找每个州有几名作者。4.查询pubs数据库titles表，要求按照出版商id进行分类，查找每个出版商的书到目前为止的销售额总和（ytd_sales）使用having筛选结果当完成对数据结果的查询和统计后，可以使用having关键字对查询和计算的结果进行进一步的筛选。注意having和where的区别WHERE子句搜索条件在进行分组操作之前应用；而HAVING搜索条件在进行分组操作之后应用。HAVING语法与WHERE语法类似，但HAVING可以包含聚合函数。HAVING子句可以引用选择列表中出现的任意项。使用having举例（一）查询得到本年度截止到目前的销售额超过$40,000的出版商。USEpubsSELECTpub_id,total=SUM(ytd_sales)FROMtitlesGROUPBYpub_idHAVINGSUM(ytd_sales)40000使用having举例（二）查询价格超过10美元的书的种类，以及他们的平均价格。USEpubsGOSELECTtype,avg(price)'平均价格'FROMtitlesWHEREprice10GROUPBYtypeGO使用having举例（三）在前一个查询的结果的基础上，找出平均价格大于18美元的书的种类。USEpubsGOSELECTtype,avg(price)'平均价格'FROMtitlesWHEREprice10GROUPBYtypeHAVINGavg(price)18GO课堂练习5.在pubs数据库的titles表中，找出平均价格大于18美元的书的出版商id。6.在pubs数据库的titles表中，找出最高价格大于20美元的书的种类。连接多个表连接查询多个表中的数据使用表的别名连接多个结果集连接查询多个表中的数据什么是连接内连接（重点掌握）外连接交叉连接什么是连接连接通过扩展select语句的from子句，可以从多个表中选择字段。语法：FROMfirst_tablejoin_typesecond_table[ON(join_condition)]连接的类型内连接：INNERJOIN（默认类型）外连接：OUTERJOIN左外连接：LEFTOUTERJOIN右外连接：RIGHTOUTERJOIN全外连接：FULLOUTERJOIN交叉连接：CROSSJOIN内连接仅显示两个联接表中的匹配行的联接。注意事项：内连接是SQLServer缺省的连接方式，可以把INNERJOIN简写成JOIN；在连接的条件中不要指定空值（NULL），因为空值和其他值都不会相等。内连接举例（一）查找pubs数据库中title和au_id的对应关系。分析：含有title列的表：titles，titleauthor；含有au_id列的表：titleauthor，authors。假设我们这样写UsepubsgoSelecttitle,au_idFromtitles,authorsGo想不想看看什么结果？应该怎么考虑在两个表之间建立一种连接路径title_idtitletype…au_idau_lnameau_fnameau_idtitle_idau_ordTitles表authors表Titleauthor表应该怎么写方法一：Selecttitles.title,titleauthor.au_idFromtitles,titleauthorWheretitles.title_id=titleauthor.title_id方法二：Selecttitles.title,titleauthor.au_idFromtitlesINNERJOINtitleauthorONtitles.title_id=titleauthor.title_id内连接连接举例（二）可以联接titles表和publishers表以创建显示每个书名的出版商名称的结果集。注意：在内联接中，结果集内不包含没有出版商信息的书名，也不包含没有书名的出版商。SELECTtitle,pub_nameFROMtitlesINNERJOINpublishersONtitles.pub_id=publishers.pub_id使用表的别名为什么使用表的别名？增加语句的可读性；有利于编写复杂的表连接操作语句；语法：table_nameAStable_aliastable_nametable_alias使用表的别名举例Selecttitles.title_id,publishers.pub_nameFromtitlesJOINpublishersONtitles.pub_id=publishers.pub_idSelectt.title_id,p.pub_nameFromtitlestJOINpublisherspONt.pub_id=p.pub_idSelectt.title_id,p.pub_nameFromtitlesAStJOINpublishersASpONt.pub_id=p.pub_id课堂练习7.找出title_id和pub_name的对应关系。（查询每一本书编号对应的出版商）8.找出title和pub_name的对应关系。9.查询每个作者的编号，姓名，所出的书的编号，并对结果排序。(注意使用表的别名)外连接为什么使用外连接仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。外联接会返回FROM子句中提到的至少一个表或视图的所有行，只要这些行符合任何WHERE或HAVING搜索条件。外连接举例（一）假设在city列上联接authors表和publishers表。结果只显示在出版商所在城市居住的作者。USEpubsSELECTa.au_fname,a.au_lname,p.pub_nameFROMauthorsaINNERJOINpublisherspONa.city=p.city外连接举例（二）但是如果使用左外连接…USEpubsSELECTa.au_fname,a.au_lname,p.pub_nameFROMauthorsaLEFTOUTERJOINpublisherspONa.city=p.cityORDERBYp.pub_nameASC,a.au_lnameASC,a.au_fnameASC交叉连接（crossjoin）交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。如果表A有N条记录，表B有M条记录，那么交叉连接产生的结果集将会有N*M条记录。连接多个结果集使用UNION运算符，把多个select语句返回的结果集合并到一个结果集中。每一个查询必须：类似的数据类型（兼容的数据类型）相同的列数选择列表中的列具有相同的顺序连接多个结果集举例（一）usepubsgoselectpub_id,pub_namefrompublishersunionselecttitle_id,titlefromtitleswhereprice20连接多个结果集并排序如果要对UNION操作的结果集进行排序，必须把orderby子句写在最后的select子句后面。选择排序的列必须是第一个select子句的列。观察多个结果集合并后的结果集的列标题。连接多个结果集举例（二）usepubsgoselectpub_id,pub_namefrompublishersunionselecttitle_id,titlefromtitleswhereprice20orderbypub_name保留合并后结果集重复的行UNION子句会自动删除结果集中重复的行。使用ALL关键字保留所有的行。连接多个结果集举例（二）UsepubsgoSelectstatefromauthorsUNIONALLSelectstatefrompublishersOrderbystatego课堂练习10.从authors表中选择state,city列，从publisher表中选择state,city列，并把两个查询的结果合并为一个结果集，并对结果集按city列、state列进行排序。11.对上面的查询语句作修改，保留所有重复的记录。使用子查询（嵌套查询）子查询介绍（subquery）把子查询用作派生的表把子查询用作表达式使用IN的子查询用子查询关联数据使用EXISTS和NOTEXISTS的子查询子查询介绍什么是子查询？子查询是一个SELECT查询，它返回单个值且嵌套在SELECT、INSERT、UPDATE、DELETE语句或其它子查询中。为什么使用子查询？把一个复杂的查询分解成一系列的逻辑步骤；当一个查询依赖于另一个查询时，子查询会很有用。把子查询用作派生的表子查询产生一个结果集，我们可以把它看成一个表（派生表），用来代替FROM子句中的表。把子查询用作派生的表举例USEnorthwindSELECTT.order