《数据库应用基础-SQL Server 》课件-CORE-06

第六章SQLServer2008数据库高级查询目标深入掌握SQL语句在SQLServer2008数据库的高级应用：分组查询、连接查询、子查询、合并查询等概述、专业术语本章将在基本的SQL语句使用的基础上学习高级SQL语句的使用，包括分组查询、连接查询、子查询、合并查询等。分组查询当执行数据统计时，需要将表中的数据划分成几个组，最终统计每个组的数据结果数据分组统计是通过GROUPBY子句、聚合函数以及HAVING子句共同实现商品号商品数量支付总金额110100000.002202000.00330300.00例：在EBuy系统，通过输入客户号及订单日期的上下限，获得指定时间内该客户所下订单中每种商品的购买数量及支付总金额：使用GROUPBY简单分组查询聚合函数：对一组数据返回单一的值常用的聚合函数函数名功能AVG求平均值COUNT计数函数，计算组中成员个数(组中记录行数)MAX求最大值MIN求最小值SUM求和说明：除COUNT函数外，聚合函数忽略空值(NULL)

使用GROUPBY简单分组查询例：查询EBuy系统中orders(订单表)中目前的订单总数说明：把整个表的记录作为一个组，求记录总数select

count(*)fromordersGROUPBY<分组表达式>GROUPBY子句及单列分组例：查询每种商品的平均订购数量ordidcomidamount…1110…2220…3110…4230…按商品种类分组groupbycomid求每组中商品的平均数量avg(amount)selectcomid,avg(amount)as

平均商品数fromordersgroup

bycomid注意：SELECT选择列表中的列或表达式必须出现在groupby子句中使用GROUPBY简单分组查询多列分组：是指在GROUPBY子句中使用多个列生成分组统计数据例：查询每个客户、每种商品的平均订购商品数。selectCusID,ComID,avg(Amount)fromordersgroup

byCusID,ComID在分组中使用HAVING子句HAVING子句用于限制分组显示结果HAVING子句必须跟在GROUPBY后头例：查询每个客户、每种商品的平均订购商品数，而且平均商品数要大于10。selectCusID,ComID,avg(Amount)fromordersgroup

byCusID,ComIDhaving

avg(Amount)>10连接查询连接查询是指基于两个或两个以上的基表或视图的查询连接方式主要有：内连接，外连接连接查询的语法规则：SELECTtable1.column,table2.columnFROMtable1[INNER|LEFT|RIGHT|FULL|CROSS]JOINtable2ONtable1.column=table2.column;INNERJOIN：表示内连接LEFTJOIN：表示左联接RIGHTJOIN：表示右连接FULLJOIN：表示完全连接CROSSJOIN：交叉连接ON：后跟连接条件内连接查询内连接是指：当且仅当连接条件成立时，才在结果集中产生一条连接记录。当左表中某记录根据连接条件在右表中没有匹配的记录时，该记录便被忽略内连接可分为：等值连接、不等连接、自连接等内连接查询等值连接：使用等于“=”运算符catidcatname1办公用品2笔记本电脑3电脑外围设备4家用电器comidcatidcomname12DELL笔记本电脑23IBM电脑外围设备31办公用品14nulltianjin-棉毛织品catnamecomname笔记本电脑DELL笔记本电脑电脑外围设备IBM电脑外围设备办公用品办公用品1selecta.catname,namefromcommdity_categoryasainnerjoincommditysascona.catid=c.catidcommodity_category表commoditys表查询结果表selecta.catname,namefromcommdity_categoryasa,commditysascwherea.catid=c.catid内连接查询不等连接：使用除了等于运算符外的比较运算符selecto.cusid,a.cusnamefromcustomerasa,ordersasowherea.cusid<>o.cusid内连接查询自连接：使用同一个表的列进行比较查询EBuy应用系统中，orders（订单表）中商品单价为100的订单部分信息cusidcomidamountpayamount1002110100000.0010012202000.001001110100000.0010012303000.00表orders中的部分字段cusidcomidamountpayamount1002110100000.0010012202000.001001110100000.0010012303000.00selecta.cusid,id,a.amount,a.payamountfromordersasa,ordersasbwhereb.payamount=a.amount*100外连接查询外连接：不仅包含符合连接条件的记录，而且还包含左表或右表连接中的所有行外连接一般分为左连接（LEFTJOIN）右连接（RIGHTJOIN）完整连接（FULLJOIN）外连接查询左连接：LEFT[OUTER]JOINcatidcatname1办公用品2笔记本电脑3电脑外围设备4家用电器comidcatidcomname12DELL笔记本电脑23IBM电脑外围设备31办公用品14NULLtianjin-棉毛织品catnamecomname笔记本电脑DELL笔记本电脑电脑外围设备IBM电脑外围设备办公用品办公用品1家用电器NULLcommodity_category表commoditys表查询结果表selecta.catname,namefromcommdity_categoryaleftjoincommditysbona.catid=b.catid外连接查询右连接：RIGHT[OUTER]JOINcatidcatname1办公用品2笔记本电脑3电脑外围设备4家用电器comidcatidcomname12DELL笔记本电脑23IBM电脑外围设备31办公用品14NULLtianjin-棉毛织品catnamecomname笔记本电脑DELL笔记本电脑电脑外围设备IBM电脑外围设备办公用品办公用品1NULLshanghai-棉毛织品commodity_category表commoditys表查询结果表selecta.catname,namefromcommdity_categoryarightjoincommditysbona.catid=b.catid外连接查询完全连接：FULL[OUTER]JOINcatidcatname1办公用品2笔记本电脑3电脑外围设备4家用电器comidcatidcomname12DELL笔记本电脑23IBM电脑外围设备31办公用品14NULLtianjin-棉毛织品catnamecomname笔记本电脑DELL笔记本电脑电脑外围设备IBM电脑外围设备办公用品办公用品1家用电器NULLNULLtianjin-棉毛织品commodity_category表commoditys表查询结果表selecta.catname,namefromcommdity_categoryfulljoincommditysbona.catid=b.catid子查询子查询也称为内部查询和内部选择，是一个SELECT查询，包含子查询的语句也称为外部查询或外部选择。子查询类型

[NOT]IN子查询[NOT]EXISTS子查询由比较运算符引出的子查询子查询[NOT]IN子查询返回零个或多个值步骤：先执行内部查询，再执行外部查询例：查询商品信息表中价格大于1000的商品类别catidcatname1办公用品2笔记本电脑3电脑外围设备4家用电器catidcomnamecomprice2DELL笔记本电脑10000.003IBM电脑外围设备100.001办公用品110.00NULLtianjin-棉毛织品50.00commodity_category表commoditys表selectcatid,catnamefromcommodity_categorywherecatidin( selectcatidfromcommodityswherecomprice>1000)[NOT]EXISTS子查询判断子查询结果中是否有数据存在子查询的选择列表常使用SELECT*格式例：查询商品类别表中有哪些商品类别在商品信息表中有具体的商品存在catidCatname1办公用品2笔记本电脑3电脑外围设备4家用电器catidcomnamecomprice2DELL笔记本电脑10000.003IBM电脑外围设备100.001办公用品110.00NULLtianjin-棉毛织品50.00commodity_category表commoditys表selectcatid,catnamefromcommodity_categoryawhereexists(select*fromcommoditysb

whereb.catid=a.catid )子查询由比较运算符引出的子查询外层查询与子查询之间用比较运算符进行连接例：查询价格低于平均价格的商品编号和名称comidcatidcomnamecomprice12DELL笔记本电脑10000.0023IBM电脑外围设备100.0031办公用品110.004NULLtianjin-棉毛织品50.00commoditysselectcomid,comnamefromcommodityswherecomprice<(select

avg(comprice)fromcommoditys)可以使用谓词ANY和ALL与比较运算符一同构成子查询>ANY：大于子查询结果中的任意一个值（即大于最小值）>ALL：大于子查询结果中的所有值（即大于最大值）catidcatname1办公用品2笔记本电脑3电脑外围设备4家用电器catidcomnamecompricesalpricestoamount2DELL笔记本电脑10000.0010000.00203IBM电脑外围设备100.0090.0010001办公用品110.009.0050NULLtianjin-棉毛织品50.0045.0010000commodity_categorycommoditys表selectcatid,catnamefromcommodity_categorywherecatid>any(selectcatidfromcommodityswhere(comprice-salprice)>5andstoamount>100)集合查询集合查询：将多个查询结果合并成一个结果集可采用的集合查询操作符UNIONUNIONALLINTERSECT集合查询UNION：合并两个结果集，自动过滤掉重复数据行，并按输出结果的第一列排序例：从商品信息表commoditys中查询商品编号大于1及商品单价大于5的商品代码和商品名称selectcomid,comnamefromcommodityswherecomid>1unionselectcomid,comnamefromcommodityswherecomprice>5comidcatidcomnamecomprice12DELL笔记本电脑10000.0023IBM电脑外围设备100.0031办公用品110.004NULLtianjin-棉毛织品50.00commoditys集合查询UNIONALL：合并两个结果集，不会过滤掉重复数据行，也不作任何排序selectcomid,comnamefromcommodityswherecomid>1unionallselectcomid,comnamefromcommodityswherecomprice>5comidcatidcomnam