第04章 SQL的数据查询_第1页
第04章 SQL的数据查询_第2页
第04章 SQL的数据查询_第3页
第04章 SQL的数据查询_第4页
第04章 SQL的数据查询_第5页
已阅读5页,还剩119页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

第4章SQL的数据查询StructuredQueryLanguage本章要点SQL的数据查询命令

简单查询连接查询分组及汇总查询嵌套查询需要查询支持的数据操作视图及其操作4.1 SQL的数据查询命令数据查询查询即检索操作,是对已经存在的基本表及视图进行数据检索,不改变数据本身。基本结构:SELECT-FROM-WHERE组成的查询块。查询的结果仍然是一个关系SELECT语句的一般格式SELECT[ALL|DISTINCT][TOP表达式1[PERCENT]][WITHTIES]<列名或表达式>[列别名1][,<列名或表达式>[列别名2]…][INTO

新基本表]FROM<表名或视图名>[表别名1][,<表名或视图名>[表别名2]…][WHERE<条件表达式1>][GROUPBY<列名1>[HAVING<条件表达式2>]][ORDERBY<列名2>[ASC|DESC]]说明:SELECT子句:指定查询结果要显示的结果列清单FROM子句:指定查询结果的数据来源,即查询所涉及的基本表或视图WHERE子句:指定从数据来源选取元组需要满足的条件GROUPBY子句和HAVING子句:用于分组和分组过滤处理。ORDERBY子句:决定查找出来的元组的排列顺序。语句的执行过程从FROM子句指定的基本表或视图中,选取符合WHERE子句中指定的<条件表达式1>的元组,按SELECT子句中的目标列表,选出元组中的分量值形成结果表。若有INTO子句,则创建新的基本表,并将查询结果存入新建的基本表中。若有GROUPBY子句,则将符合<条件表达式1>的元组,按照指定的列名1的值分组,值相同的元组分在一组,每个组产生结果表中的一个元组;若有HAVING子句,则在分组结果中去掉不满足HAVING子句<条件表达式2>的分组。若有ORDERBY子句,则结果表要根据指定的列名2的值按升序或降序排序。4.2简单查询简单查询查询仅涉及一个表,相当于关系代数的投影操作。基本格式是:SELECT[ALL|DISTINCT][TOP表达式1[PERCENT]][WITHTIES]<列名或表达式>[列别名1][,<列名或表达式>[列别名2]…][INTO

新基本表]FROM

<表名或视图名>[WHERE

<条件表达式>]WHERE子句常用的查询条件:比较:<、<=、>、>=、=、!=、<>、!>、!<确定范围:BETWEENAANDB、NOTBETWEENAANDB确定集合:IN、NOTIN字符匹配:LIKE,NOTLIKE空值:ISNULL、ISNOTNULL多重条件:AND、OR、NOT4.2.1简单无条件查询查询仅涉及一个表,不包含查询条件,是一种最简单的查询操作。相当于关系代数的投影操作。4.2.1简单无条件查询【例4.1】查询所有客户的基本情况。SELECTcustID,custName,custCity,custPhoneFROMCustomer或者SELECT*FROMCustomer说明:*是一种缺省形式,当所查询的列是关系的所有属性时,可以使用*来表示。4.2.2简单条件查询一般形式为:

<列名>θ<列名>或者<列名>θ常量值【例4.2】查询北京客户的客户编号和客户名称。SELECTcustID,custNameFROMCustomerWHEREcustCity='北京'客户编号custID客户名称custName客户所在城市custCity客户电话custPhone901里奇北京13844472365902环宇北京18955532012903张松林上海13677712316905贾立委广州15985612316906张小宁

客户编号custID客户名称custName901里奇902环宇【例4.3】查询订单总金额超过5000元的订单信息。SELECT*FROMOrdersWHEREorderAmount>5000订单编号orderID订单日期orderDate客户编号custID送货地址orderAddress订单总金额orderAmountD2010051616280012010-05-16901北京市朝阳区联盟小区9号楼6750.00D2010051716300022010-05-17903上海市虹梅路201号1232.00D2010060609230032010-06-06905广州市江南大道中82号2500.00D2010061016280042010-06-10901北京市海淀区光华路55号6200.00D2010070109160052010-07-01902北京市北四环中路35号1425.00订单编号orderID订单日期orderDate客户编号custID送货地址orderAddress订单总金额orderAmountD2010051616280012010-05-16901北京市朝阳区联盟小区9号楼6750.00D2010061016280042010-06-10901北京市海淀区光华路55号6200.004.2.3使用BETWEENAND的查询谓词BETWEEN…AND…可以用来判定表达式值在不在指定范围内的元组。形如:<表达式>[NOT]BETWEENAANDBA是范围的下限,B是范围的上限。【例4.4】查询价格在100-500(包含100和500)之间的商品编号SELECTpdIDFROMProductWHEREpdPriceBETWEEN100AND500相当于SELECTpdIDFROMProductWHEREpdPrice>=100ANDpdPrice<=500【例4.5】查询价格不在100-500之间的商品编号。SELECTpdIDFROMProductWHEREpdPriceNOTBETWEEN100AND500相当于SELECTpdIDFROMProductWHEREpdPrice<100ANDpdPrice>5004.2.4字符串匹配查询用于字符串比较,来实现模糊查询。形如:<列名>[NOT]LIKE<字符串常数>[ESCAPE<转义字符>]<字符串常数>可以包含通配符

%(百分号)代表任意长度(长度可以为0)的字符串例:a%b表示以a开头,以b结尾的任意长度的字符串。如acb,addgb,ab等都满足该匹配串。_(下划线)代表任意单个字符例:a_b表示以a开头,以b结尾的长度为3的任意字符串。如acb,afb等都满足该匹配串。【例4.6】查询客户“里奇”的联系电话。SELECTcustPboneFROMCustomerWHEREcustNameLIKE‘里奇’等价于:SELECTcustPboneFROMCustomerWHEREcustName=‘里奇’当‘<字符串常数>’不含有通配符时,可以用=运算符取代LIKE

谓词,用!=或<>运算符取代NOTLIKE

谓词。【例4.7】查询所有姓张的客户的基本情况。SELECT*FROMCustomerWHEREcustNameLIKE‘张%’客户编号custID客户名称custName客户所在城市custCity客户电话custPhone901里奇北京13844472365902环宇北京18955532012903张松林上海13677712316905贾立委广州15985612316906张小宁

客户编号custID客户名称custName客户所在城市custCity客户电话custPhone903张松林上海13677712316906张小宁

【例4.8】查询客户名称第2个字为“立”的客户的基本情况。SELECT*FROMCustomerWHEREcustNameLIKE‘_立%’客户编号custID客户名称custName客户所在城市custCity客户电话custPhone901里奇北京13844472365902环宇北京18955532012903张松林上海13677712316905贾立委广州15985612316906张小宁

客户编号custID客户名称custName客户所在城市custCity客户电话custPhone905贾立委广例4.9】查询所有不姓贾的客户的基本情况。SELECT*FROMCustomerWHEREcustNameNOTLIKE‘贾%’客户编号custID客户名称custName客户所在城市custCity客户电话custPhone901里奇北京13844472365902环宇北京18955532012903张松林上海13677712316905贾立委广州15985612316906张小宁

客户编号custID客户名称custName客户所在城市custCity客户电话custPhone901里奇北京13844472365902环宇北京18955532012903张松林上海13677712316906张小宁

【例4.10】查询所有客户名称以“张_”开头的客户的基本情况。当用户要查询的字符串本身就含有%或_时,要使用ESCAPE'<转义字符>'短语对通配符进行转义。SELECT*FROMCustomerWHEREcustNameLIKE‘张\_%’ESCAPE‘\’4.2.5空值查询形如:<表达式>IS[NOT]NULL【例4.11】查询缺少联系电话的客户名单。SELECTcustNameFROMCustomerWHEREcustPhoneISNULL【例4.12】查询所有有联系电话的客户名单。SELECTcustNameFROMCustomerWHEREcustPhoneISNOTNULL4.2.6使用IN的查询谓词IN可以用来查找表达式值属于不属于指定集合的元组。形如:<表达式>[NOT]IN<集合><集合>:用逗号分隔的一组取值【例4.13】查询北京和广州的客户的基本情况。SELECT*FROMCustomerWHEREcustCityIN(‘北京’,’广州’)等价于:SELECT*FROMCustomerWHEREcustCity=‘北京’ORcustCity=’广州’【例4.14】查询除了北京和广州之外,其余城市的客户的基本情况SELECT*FROMCustomerWHEREcustCityNOTIN(‘北京’,’广州’)4.2.7DISTINCT短语的使用带DISTINCT的语句:SELECTDISTINCTcustCityFROMCustomer注意:

DISTINCT短语的作用范围是所有目标列,在SELECT子句中只出现一次。查询所有客户所在城市。不带DISTINCT的语句:SELECTcustCityFROMCustomer客户编号custID客户名称custName客户所在城市custCity客户电话custPhone901里奇北京13844472365902环宇北京18955532012903张松林上海13677712316905贾立委广州15985612316906张小宁

客户所在城市custCity北京北京上海广州

带DISTINCT的语句:SELECTDISTINCTcustCityFROMCustomer客户所在城市custCity北京上海广州

【例4.15】注意:DISTINCT短语的作用范围是所有目标列,在SELECT子句中只出现一次。4.2.8存储查询结果INTO子句将查询结果存储到指定的新基本表中【例4.16】查询所有北京客户的客户编号、客户名称和客户电话,并将其存入新基本表BJ_Customer中。SELECTcustD,custName,custPhoneINTOBJ_CustomerFROMCustomer4.2.9带排序的查询希望查询结果中的元组按某种顺序排列基本格式是:ORDERBY<列名1>[ASC|DESC][,<列名2>[ASC|DESC]…]

作用将SELECT语句的查询结果中所有元组按照要求的顺序排列首先按第一个属性列值排列;第一个属性列值相同者,再按下一个属性列值排列,依次类推。ASC,则表示该列值以升序排列;DESC,则表示该列值以降序排列。省略不写,默认为升序排列。【例4.17】

查询所有商品的基本信息,并首先按照生产厂商升序排列,同一个生产厂商的商品按照价格降序排列。SELECT*FROMProductORDERBYpdSupplier,pdPriceDESC【例4.17】查询结果pdIDpdNamepdSpecpdModelpdSupplierpdPricepdStockSize12201羽毛球拍单只装YP-11北京胜利400.003012202羽毛球拍2只装UP-129上海双喜360.002911207篮球75-76cmTB-49x上海双喜125.001511209足球Size5TF-88x上海双喜120.001011203羽毛球10只装YM-11上海双喜20.0050011206篮球70-71cm74-492y上海斯伯丁130.002011210足球Size532-SZ天津南生160.006011208篮球75-76cm74-SL天津南生70.001511205羽毛球6只装YM-11天津南生15.00474.2.10TOP短语的作用指定返回查询结果的“前”一组结果,该短语必须和ORDERBY一起使用。格式如下:[TOP表达式[PERCENT]][WITHTIES]说明:表达式指出返回多少结果如果同时选用了PERCENT则按百分比进行计算如果选用WITHTIES则返回与规定的最后一个元组具有相同排序值的其他所有行。【例4.18】查询所有商品中库存数量最少的两种商品的基本信息。SELECTTOP

2

*FROMProductORDERBYpdStockSize如果使用WITHTIES,即语句为:SELECTTOP2WITHTIES*FROMProductORDERBYpdStockSizepdIDpdNamepdSpecpdModelpdSupplierpdPricepdStockSize11209足球Size5TF-88x上海双喜120.001011207篮球75-76cmTB-49x上海双喜125.001511208篮球75-76cm74-SL天津南生77.0015pdIDpdNamepdSpecpdModelpdSupplierpdPricepdStockSize11209足球Size5TF-88x上海双喜120.001011207篮球75-76cmTB-49x上海双喜125.00154.2.11集合运算集合操作种类并操作(UNION)交操作(INTERSECT)差操作(EXCEPT)注意:参与集合操作的各查询结果必须是相容的,即列数必须相同,且对应属性列的数据类型也必须相同。1.并使用UNION可以把查询的结果合并起来并且去掉重复的元组,如果要保留所有重复的元组,则必须使用UNIONALL。【例4.19】查询北京和上海的客户的基本信息。SELECT*FROMCustomerWHEREcustCity='北京'UNIONSELECT*FROMCustomerWHEREcustCity='上海'【例4.20】查询包含了编号为“11203”的商品或编号为“11206”的商品的订单编号。SELECTorderIDFROMOrderDetailWHEREpdID='11203'UNIONSELECTorderIDFROMOrderDetailWHEREpdID='11206'等价于:SELECTDISTINCTorderIDFROMOrderDetailWHEREpdIDIN('11203','11206')2.交【例4.21】查询“上海双喜”生产的价格在100元以上的商品信息。SELECT*FROMProductWHEREpdSupplier='上海双喜'INTERSECTSELECT*FROMProductWHEREpdPrice>100等价于:SELECT*FROMProductWHEREpdSupplier='上海双喜'ANDpdPrice>1003.差【例4.22】查询包含编号为“11203”的商品但不包含编号为“11206”的商品的订单编号。SELECTorderIDFROMOrderDetailWHEREpdID='11203'EXCEPTSELECTorderIDFROMOrderDetailWHEREpdID='11206'【例4.23】查询“上海双喜”生产的价格不低于200元的商品信息。SELECT*FROMProductWHEREpdSupplier='上海双喜'EXCEPTSELECT*FROMProductWHEREpdPrice<=200等价于:SELECT*FROMProductWHEREpdSupplier='上海双喜'ANDpdPrice>200对集合操作结果的排序ORDERBY子句只能用于对最终查询结果排序,不能对中间结果排序任何情况下,ORDERBY子句只能出现在最后

SELECTorderIDFROMOrderDetailWHEREpdID='11203'ORDERBYorderIDUNIONSELECTorderIDFROMOrderDetailWHEREpdID='11206'ORDERBYorderID例--错误写法

SELECTorderIDFROMOrderDetailWHEREpdID='11203'UNIONSELECTorderIDFROMOrderDetailWHEREpdID='11206'ORDERBYorderID正确写法4.3连接查询连接查询涉及到多个表的查询是关系数据库中最主要的查询功能基本格式中:FROM子句要指明进行连接的表名;SELECT子句中要指明多表检索的结果表中的属性名列。WHERE子句要指明连接的列名及其连接条件;一般格式:[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>

[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>连接字段连接谓词中的列名称为连接字段连接条件中的各连接字段类型必须是可比的,但不必是相同的4.3.1一般连接【例4.24】查询所有客户订单的客户编号、客户名称、订单编号和送货地址。SELECTCustomer.custID,custName,orderID,orderAdressFROMCustomer,OrderWHERECustomer.custID=Order.custID连接操作的执行过程首先在表1中找到第一个元组,然后从头开始扫描表2,逐一查找满足连接件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组。表2全部查找完后,再找表1中第二个元组,然后再从头开始扫描表2,逐一查找满足连接条件的元组,找到后就将表1中的第二个元组与该元组拼接起来,形成结果表中一个元组。重复上述操作,直到表1中的全部元组都处理完毕。【例4.24】可能的执行过程客户编号custID客户名称custName客户所在城市custCity客户电话custPhone901里奇北京13844472365902环宇北京18955532012903张松林上海13677712316905贾立委广州15985612316906张小宁

订单编号orderID订单日期orderDate客户编号custID送货地址orderAddress订单总金额orderAmountD2010051616280012010-5-16901北京市朝阳区联盟小区9号楼6750D2010051716300022010-5-17903上海市虹梅路201号1540D2010060609230032010-6-6905广州市江南大道中82号2500D2010061016280042010-6-10901北京市海淀区光华路55号6200D2010070109160052010-7-1902北京市北四环中路35号1425订单编号orderID订单日期

orderDate客户编号custID送货地址orderAddress订单总金额

orderAmount客户名称custName客户所在城市custCity客户电话custPhoneD2010051616280012010-5-16901北京市朝阳区联盟小区9号楼6750里奇北2010051716300022010-5-17903上海市虹梅路201号1540张松林上2010060609230032010-6-6905广州市江南大道中82号2500贾立委广2010061016280042010-6-10901北京市海淀区光华路55号6200里奇北2010070109160052010-7-1902北京市北四环中路35号1425环宇北户编号custID客户名称custName订单编号orderID送货地址orderAddress901里奇D201005161628001北京市朝阳区联盟小区9号楼903张松林D201005171630002上海市虹梅路201号905贾立委D201006060923003广州市江南大道中82号901里奇D201006101628004北京市海淀区光华路55号902环宇D201007010916005北京市北四环中路35号【例4.25】查询北京客户的订单编号、客户名称和送货地址。SELECTorderID,custName,orderAdressFROMCustomer,OrderWHERECustomer.custID=Order.custIDANDcustCity='北京'【例4.26】查询客户张松林订购商品的订单编号、送货地址、商品编号和订购数量。SELECTOrder.orderID,orderAddress,pdID,quantityFROMCustomer,Order,OrderDetailWHERECustomer.custID=Order.custIDANDOrder.orderID=OrderDetail.orderIDANDcustName='张松林'【例4.27】查询北京客户的订单编号、客户名称、商品编号、商品名称和订购数量。SELECTOrder.orderID,custName,Product.pdID,pdName,quantityFROMCustomer,Order,Product,OrderDetailWHERECustomer.custID=Order.custIDANDOrder.orderID=OrderDetail.orderIDANDOrderDetail.pdID=Product.pdIDANDcustCity='北京'内连接形如:INNERJOIN…ON…【例4.28】用INNERJOIN…ON…重新实现例4.25:查询北京客户的订单编号、客户名称和送货地址。SELECTorderID,custName,orderAdressFROMCustomerINNERJOINOrderONCustomer.custID=Order.custIDWHEREcustCity='北京'【例4.29】用INNERJOIN…ON…重新实现例4.26:查询客户张松林订购商品的订单编号、送货地址、商品编号和订购数量SELECTOrder.orderID,orderAddress,pdID,quantityFROMCustomerINNERJOINOrderONCustomer.custID=Order.custIDINNERJOINOrderDetailONOrder.orderID=OrderDetail.orderIDWHEREcustName='张松林'4.3.2别名和自连接查询一个表与自身进行的连接【例4.30】查询和客户“环宇”在同一城市的客户的基本信息。

SELECTCA.*FROMCustomerCA,CustomerCBWHERECA.custCity=CB.custCityAND CB.custName='环宇'4.3.3外连接查询外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出形如:左外连接(LEFTJOIN…ON…)右外连接(RIGHTJOIN…ON…)。【例4.31】用外连接改写例4.24:查询所有客户订单的客户编号、客户名称、订单编号和送货地址。SELECTCustomer.custID,custName,orderID,orderAddressFROMCustomerLEFTJOINOrderONCustomer.custID=Order.custID

客户编号custID客户名称custName订单编号orderID送货地址orderAddress901里奇D201005161628001北京市朝阳区联盟小区9号楼901里奇D201006101628004北京市海淀区光华路55号902环宇D201007010916005北京市北四环中路35号903张松林D201005171630002上海市虹梅路201号905贾立委D201006060923003广州市江南大道中82号906张小宁4.4分组及汇总查询分组及汇总查询对查询结果进行分组计算和汇总计算分组查询用GROUPBY子句完成计算的函数称为聚合函数COUNT(*):统计元组个数COUNT(<列名>):统计一列中值的个数SUM(<列名>):计算一列值的总和AVG(<列名>):计算一列值的平均值MAX(<列名>):求一列中的最大值MIN(<列名>):求一列值的最小值说明:除COUNT(*)外,其它聚集函数在计算时会忽略空值。在<列名>前加入DISTINCT保留字,会将查询结果的列去掉重复值在计算。4.4.1一般汇总查询【例4.32】统计客户所在城市的个数。SELECTCOUNT(DISTINCTcustCity)FROMCustomer

3客户编号custID客户名称custName客户所在城市custCity客户电话custPhone901里奇北京13844472365902环宇北京18955532012903张松林上海13677712316905贾立委广州15985612316906张小宁

【例4.32】可以使用下列语句为属性列进行命名。SELECTCOUNT(DISTINCTcustCity)AS

custCityNumber

FROMCustomer

custCityNumber3客户编号custID客户名称custName客户所在城市custCity客户电话custPhone901里奇北京13844472365902环宇北京18955532012903张松林上海13677712316905贾立委广州15985612316906张小宁

【例4.33】【例4.33】查询订单的个数和总金额之和。SELECTCOUNT(*)ASorderNumber,SUM(orderAmount)ASSumAmountFROMOrders4.4.2使用GROUPBY的分组汇总查询GROUPBY子句把元组按某一指定列(或一些列)上的值相等的原则分组,然后再对每组数据进行规定的操作。对分组进行汇总可通过聚合函数实现【例4.34】查询每一种商品的销售总量。SELECTpdID,SUM(quantity)ASTotalFROMorderDetailGROUPBYpdID订单编号

orderID商品编号

pdID订购数量

quantityD2010051616280011120310D2010051616280011120640D2010051616280011220115D201005171630002112034D201005171630002122025D2010060609230031120610D2010060609230031120910D2010061016280041120510D2010061016280041120820D2010061016280041121010D2010061016280041220210D201007010916005112075D201007010916005112105pdIDTotal11203451120520112062511207511208101120910112101512201101220214GROUPBY子句的说明GROUPBY子句分组方法:按指定的一列或多列值分组,值相等的为一组使用GROUPBY子句后,SELECT子句的列名列表中只能出现分组属性和集函数

【例4.35】【例4.35】查询每个城市的客户总数。SELECTcustCity,COUNT(*)AScustCountFROMCustomerGROUPBYcustCity

【例4.36】查询被购买3次以上的商品的商品编号和销售数量的最大值和最小值。SELECTpdID,MAX(quantity)ASMAX_quantity,MIN(quantity)ASMIN_quantityFROMOrderDetailGROUPBYpdIDHAVINGCOUNT(*)>=3HAVING短语与WHERE子句的区别:作用对象不同WHERE子句作用于基表或视图,从中选择满足条件的元组。HAVING短语作用于组,从中选择满足条件的组。集函数的条件关系必须用Having,Where中不应出现集函数4.4.3带明细的分组汇总查询GROUPINGSETS短语在显示汇总的同时也显示明细信息【例4.37】查询所有商品信息的订单明细,并分类汇总各种商品的销售数量。SELECTpdID,orderID,SUM(quantity)asTotalFROMOrderDetailGROUPBYGROUPINGSETS(pdID,(pdID,orderID,quantity))pdIDorderIDTotalpdIDorderIDTotal11203D2010051616280014011208NULL1011203D201005171630002511209D2010060609230031011203NULL4511209NULL1011205D2010061016280042011210D2010061016280041011205NULL2011210D201007010916005511206D2010051616280011511210NULL1511206D2010060609230031012201D2010051616280011011206NULL2512201NULL1011207D201007010916005512202D201005171630002411207NULL512202D2010061016280041011208D2010061016280041012202NULL144.5嵌套查询嵌套查询一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块(SELECT-FROM-WHERE)嵌套在另一个查询块的WHERE子句或HAVING短语的条件表达式中的查询,称为嵌套查询。其中外层查询称为父查询,内层查询称为子查询。执行过程:是由里至外的,每一个子查询是在上一级查询处理之前完成的。注意子查询中不能用ORDERBY语句4.5.1内外层不相关

的嵌套查询1.返回单值的子查询确定知道子查询返回的检索信息是单一的值。这类子查询的结果可以看做一个常量值,参与=、!=(<>)、>、>=、<、<=等关系比较运算。【例4.38】查询编号为“D201005161628001”的订单的客户名称和联系电话。

SELECTcustName,custPhoneFROMCustomerWHEREcustID=(SELECTcustIDFROMOrdersWHEREorderID='D201005161628001')

可能的执行过程:首先执行内层子查询,找到orderID='D201005161628001'的custID,即901然后再执行外层父查询,找到custID=901的custName,custPhone。等价于SELECTcustName,custPhoneFROMCustomerWHEREcustIDIN(SELECTcustIDFROMOrdersWHEREorderID='D201005161628001')【例4.39】查询比编号为“D201006101628004”订单的订单金额还高的订单编号和客户编号。

SELECTorderID,custIDFROMOrdersWHEREorderAmount>(SELECTorderAmountFROMOrderWHEREorderID='D201006101628004')不相关子查询子查询的查询条件不依赖于父查询。求解方法:由里向外逐层处理。即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件。SELECTcustName,custPhone外层查询/父查询FROMCustomerWHEREcustIDIN

(SELECTcustID内层查询/子查询FROMOrderWHEREorderID='D201005161628001')2.返回多值的子查询使用ALL、ANY等谓词ANY:任意一个值ALL:所有值需要配合使用比较运算符>ANY 大于子查询结果中的某个值>ALL 大于子查询结果中的所有值<ANY 小于子查询结果中的某个值<ALL 小于子查询结果中的所有值>=ANY 大于等于子查询结果中的某个值>=ALL 大于等于子查询结果中的所有值<=ANY 小于等于子查询结果中的某个值<=ALL 小于等于子查询结果中的所有值=ANY 等于子查询结果中的某个值=ALL 等于子查询结果中的所有值(通常没有实际意义)!=(或<>)ANY 不等于子查询结果中的某个值!=(或<>)ALL 不等于子查询结果中的任何一个值【例4.40】查询比所有“天津南生”的商品价格都高的商品信息。SELECT*FROMProductWHEREpdPrice>ALL(SELECTpdPriceFROMProductWHEREpdSupplier='天津南生')等价于SELECT*FROMProductWHEREpdPrice>(SELECTMAX(pdPrice)FROMProductWHEREpdSupplier='天津南生')【例4.41】查询至少比“上海双喜”一种商品价格低的商品信息。SELECT*FROMProductWHEREpdPrice<ANY(SELECTpdPriceFROMProductWHEREpdSupplier='上海双喜')等价于:SELECT*FROMProductWHEREpdPrice<(SELECTMAX(pdPrice)FROMProductWHEREpdSupplier='上海双喜')【例4.42】查询至少订购了“上海双喜”一种商品的订单编号。SELECTDISTINCTorderIDFROMOrderDetailWHEREpdID=ANY(SELECTpdIDFROMProductWHEREpdSupplier='上海双喜')等价于SELECTDISTINCTorderIDFROMOrderDetailWHEREpdIDIN(SELECTpdIDFROMProductWHEREpdSupplier='上海双喜')ANY和ALL谓词有时可以用聚集函数实现用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高,因为前者通常能够减少比较次数ANY与ALL和聚集函数的对应关系

=<>或!=

<<=>>=ANY

IN--

<MAX<=MAX>MIN>=MINALL--

NOTIN

<MIN<=MIN>MAX>=MAX4.5.2内外层互相关的嵌套查询子查询的查询条件依赖于父查询,称为相关子查询。【例4.43】查询每个客户的具有最高总金额的订单信息。SELECT*FROMOrdersAWHEREorderAmount=(SELECTMAX(orderAmount)FROMOrdersBWHEREB.custID=A.custID)ORDERBYorderID4.5.3使用EXISTS的

嵌套查询4.5.3使用EXISTS的嵌套查询使用[NOT]EXISTS谓词形如:[NOT]EXISTS(子查询)结果只与(子查询)的结果是否为空有关若(子查询)结果为空,则EXISTS返回“假”,NOTEXISTS返回“真”;若(子查询)结果为不空,则EXISTS返回“真”,NOTEXISTS返回“假”【例4.44】查询订购了“羽毛球”的订单编号。SELECTDISTINCTorderIDFROMOrderDetailWHEREEXISTS(SELECT*FROMProductWHEREProduct.pdID=OrderDetail.pdIDANDpdName='羽毛球')可能的执行过程首先取外层查询中OrderDetail表的第一个元组,根据它的pdID值处理内层查询,若在Product表中找到等于外层元组pdID值且pdName值为“羽毛球”的元组,则内层查询结果非空,EXISTS返回“真”值,OrderDetail的元组符合条件,将其orderID输出到结果表;若在Product表中找不到符合条件的元组,则内层查询结果为空,EXISTS返回“假”值,OrderDetail的元组不符合条件,跳过。取OrderDetail表的下一个元组,重复上述过程,直至OrderDetail表全部检索完毕。演示使用IN的等价查询本例中的查询也可使用含IN谓词的非相关子查询完成:SELECTDISTINCTorderIDFROMOrderDetailWHEREpdIDIN(SELECTpdIDFROMProductWHEREpdName='羽毛球')【例4.45】检索北京客户的订单编号和送货地址。SELECTorderID,orderAddressFROMOrdersWHEREEXISTS(SELECT*FROMCustomerWHERECustomer.custID=Orders.custIDANDcustCity='北京')【例4.46】查询没有订购编号为“11206”商品的订单编号。SELECTorderIDFROMOrdersWHERENOTEXISTS(SELECT*FROMOrderDetailWHEREOrderDetail.orderID=Orders.orderIDANDpdID='11206')使用IN的等价查询本例的查询也可使用含NOTIN谓词的不相关子查询完成:SELECTorderIDFROMOrdersWHEREorderIDNOTIN(SELECTorderIDFROMOrderDetailWHEREpdID='11206')不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。【例4.47】用EXISTS改写例4.42:查询至少订购了“上海双喜”一种商品的订单编号。SELECTDISTINCTorderIDFROMOrderDetailWHEREEXISTS(SELECTpdIDFROMProductWHEREOrderDetail.pdID=Product.pdIDANDpdSupplier='上海双喜')【例4.48】用NOTEXISTS改写例4.40:查询比所有“天津南生”的商品价格都高的商品信息。SELECT*FROMProductAWHERENOTEXISTS(SELECT*FROMProductBWHEREB.pdSupplier='天津南生'ANDB.pdPrice>A.pdPrice)【例4.49】查询订单中包含了全部商品的订单编号。SELECTorderIDFROMOrdersWHERENOTEXISTS(SELECT*FROMProductWHERENOTEXISTS(SELECT*FROMOrderDetailWHEREOrderDetail.orderID=Orders.orderIDANDOrderDetail.pdID=Product.pdID))

【例4.50】查询订单中包含了“天津南生”全部商品的订单编号。SELECTorderIDFROMOrderWHERENOTEXISTS(SELECT*FROMProductWHEREpdSupplier='天津南生'ANDNOTEXISTS(SELECT*FROM OrderDetailWHEREOrderDetail.orderID=Order.orderIDANDOrderDetail.pdID=Product.pdID))练习(1)检索选修了课程号为1或2的课程,且成绩高于或等于70分的学生的学号。(2)检索选修了“Pascal语言”课程的学生的学号。(3)检索所有学生的姓名、所选课程的课程名和成绩,并且按课程号的升序和成绩的降序进行排列。(4)检索所有学生中年龄最大的学生的姓名及年龄。(5)求计算机系学生中年龄的最大值和最小值。(6)求所有学生中女生的总人数。(7)求选课在四门以上的学生所选课程的平均成绩。最后按降序列出平均成绩名次名单来。学生表:Student(Sno,Sname,Ssex,Sage,Sdept)课程表:Course(Cno,Cname,Cpno,Ccredit)学生选课表:SC(Sno,Cno,Grade)4.6需要查询支持的

数据操作插入删除修改语句格式:INSERTINTO<基本表名>[(<列名1>,<列名2>,……,<列名n>)]

子查询;功能:将子查询结果插入指定表中说明:如果列名序列省略,则子查询所得到的数据列必须和指定基本表的数据列完全一致。如果列名序列给出,则子查询结果与列名序列要一一对应。4.6.1插入操作【例4.51】假设已用如下语句建立商品销售总量表SalePd_sum(pdID,total)。CREATETABLESalePd_sum(pdIDvarchar(6)PRIMARYKEY,totalint)其中total表示每种商品的销售总量。向SalePd_sum表中插入每种商品的销售总量。INSERTINTOSalePd_sum(pdID,total)SELECTpdID,sum(quantity)FROMOrderDetailGROUPBYpdID4.6.2修改操作【例4.52】将所有销售总量少于10的商品价格降低10%。UPDATEProductSETpdPrice=pdPrice*0.9WHEREpdIDNOTIN

(SELECTpdIDFROMOrderDetailGROUPBYpdIDHAVINGSUM(quantity)>=10)【例4.53】根据订单明细中的订购数量×销售价格之和更新每个订单的总金额。UPDATEOrdersSETorderAmount=

(SELECTSUM(quantity*pdSellPrice)FROMOrderDetailWHEREorderID=Orders.orderID)【例4.54】删除905号客户的全部订单的订单细节。DELETEFROMOrderDetailWHEREorderIDIN(SELECTorderIDFROMOrdersWHEREcustID='905')4.6.3删除操作4.7视图及其操作视图的定义和特点视图(View)是外模式一级数据结构的基本单位虚表,由基本表或其它视图导出的表,其本身不存在于数据库中。只存放视图的定义,而不存放视图对应的数据。视图一经定义,就可被检索或删除,但更新操作有一定的限制,也可再定义其它视图。4.7.1视图的建立和删除1.视图的建立语法格式:CREATEVIEW

<视图名>[(<列名>[,<列名>]…)]AS<子查询>[WITHCHECKOPTION]【例4.55】建立一个所在城市为“北京”的客户信息视图BJCustomer。CREATEVIEWBJCustomerASSELECTcustID,custName,custPhoneFROMCustomerWHEREcustCity='北京'行列子集视图从单个基本表导出只是去掉了基本表的某些行和某些列保留了主码

WITHCHECKOPTION

对视图进行UPDATE,INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)对BJCustomer视图的更新操作:修改操作:RDBMS自动加上custCity='北京'的条件删除操作:RDBMS自动加上custCity='北京'的条件插入操作:RDBMS自动检查custCity='北京'如果不是,则拒绝该插入操作如果没有提供Sdept属性值,则自动定义custCity为'北京'子查询不允许含有ORDERBY子句和DISTINCT短语说明组成视图的属性列名,或者全部省略,或者全部指定。以下三种情况下必须明确指定全部属性列:子查询SELECT子句里列名中有常数、聚集函数或列表达式。子查询SELECT子句里列名中有从多个表中选出的同名属性列。需要用更合适的新列名作视图列的列名。说明(续)【例4.56】建立一个生产厂商为“天津南生”的商品视图TJ_Product,并要求进行增、删、改操作时仍需保证该视图只有“天津南生”的商品。CREATEVIEWTJ_ProductASSELECTpdID,pdName,pdPriceFROMProductWHEREpdSupplier='天津南生'WITHCHECKOPTION【例4.57】建立订购了“天津南生”的商品的订单信息视图TJ_Order,包括订单编号

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论