第04章 SQL的數(shù)據(jù)查詢

第4章SQL的數(shù)據(jù)查詢StructuredQueryLanguage本章要點(diǎn)SQL的數(shù)據(jù)查詢命令

簡(jiǎn)單查詢連接查詢分組及匯總查詢嵌套查詢需要查詢支持的數(shù)據(jù)操作視圖及其操作4.1 SQL的數(shù)據(jù)查詢命令數(shù)據(jù)查詢查詢即檢索操作,是對(duì)已經(jīng)存在的基本表及視圖進(jìn)行數(shù)據(jù)檢索,不改變數(shù)據(jù)本身。基本結(jié)構(gòu)：SELECT-FROM-WHERE組成的查詢塊。查詢的結(jié)果仍然是一個(gè)關(guān)系SELECT語(yǔ)句的一般格式SELECT[ALL|DISTINCT][TOP表達(dá)式1[PERCENT]][WITHTIES]<列名或表達(dá)式>[列別名1][,<列名或表達(dá)式>[列別名2]…][INTO

新基本表]FROM<表名或視圖名>[表別名1][,<表名或視圖名>[表別名2]…][WHERE<條件表達(dá)式1>][GROUPBY<列名1>[HAVING<條件表達(dá)式2>]][ORDERBY<列名2>[ASC|DESC]]說(shuō)明：SELECT子句：指定查詢結(jié)果要顯示的結(jié)果列清單FROM子句：指定查詢結(jié)果的數(shù)據(jù)來(lái)源，即查詢所涉及的基本表或視圖WHERE子句：指定從數(shù)據(jù)來(lái)源選取元組需要滿足的條件GROUPBY子句和HAVING子句：用于分組和分組過(guò)濾處理。ORDERBY子句：決定查找出來(lái)的元組的排列順序。語(yǔ)句的執(zhí)行過(guò)程從FROM子句指定的基本表或視圖中，選取符合WHERE子句中指定的<條件表達(dá)式1>的元組，按SELECT子句中的目標(biāo)列表，選出元組中的分量值形成結(jié)果表。若有INTO子句，則創(chuàng)建新的基本表，并將查詢結(jié)果存入新建的基本表中。若有GROUPBY子句，則將符合<條件表達(dá)式1>的元組，按照指定的列名1的值分組，值相同的元組分在一組，每個(gè)組產(chǎn)生結(jié)果表中的一個(gè)元組；若有HAVING子句，則在分組結(jié)果中去掉不滿足HAVING子句<條件表達(dá)式2>的分組。若有ORDERBY子句，則結(jié)果表要根據(jù)指定的列名2的值按升序或降序排序。4.2簡(jiǎn)單查詢簡(jiǎn)單查詢查詢僅涉及一個(gè)表，相當(dāng)于關(guān)系代數(shù)的投影操作?；靖袷绞牵篠ELECT[ALL|DISTINCT][TOP表達(dá)式1[PERCENT]][WITHTIES]<列名或表達(dá)式>[列別名1][,<列名或表達(dá)式>[列別名2]…][INTO

新基本表]FROM

<表名或視圖名>[WHERE

<條件表達(dá)式>]WHERE子句常用的查詢條件：比較：<、<=、>、>=、=、!=、<>、!>、!<確定范圍：BETWEENAANDB、NOTBETWEENAANDB確定集合：IN、NOTIN字符匹配：LIKE，NOTLIKE空值：ISNULL、ISNOTNULL多重條件：AND、OR、NOT4.2.1簡(jiǎn)單無(wú)條件查詢查詢僅涉及一個(gè)表，不包含查詢條件，是一種最簡(jiǎn)單的查詢操作。相當(dāng)于關(guān)系代數(shù)的投影操作。4.2.1簡(jiǎn)單無(wú)條件查詢【例4.1】查詢所有客戶的基本情況。SELECTcustID,custName,custCity,custPhoneFROMCustomer或者SELECT*FROMCustomer說(shuō)明：*是一種缺省形式，當(dāng)所查詢的列是關(guān)系的所有屬性時(shí)，可以使用*來(lái)表示。4.2.2簡(jiǎn)單條件查詢一般形式為：

<列名>θ<列名>或者<列名>θ常量值【例4.2】查詢北京客戶的客戶編號(hào)和客戶名稱(chēng)。SELECTcustID,custNameFROMCustomerWHEREcustCity='北京'客戶編號(hào)custID客戶名稱(chēng)custName客戶所在城市custCity客戶電話custPhone901里奇北京13844472365902環(huán)宇北京18955532012903張松林上海13677712316905賈立委廣州15985612316906張小寧

客戶編號(hào)custID客戶名稱(chēng)custName901里奇902環(huán)宇【例4.3】查詢訂單總金額超過(guò)5000元的訂單信息。SELECT*FROMOrdersWHEREorderAmount>5000訂單編號(hào)orderID訂單日期orderDate客戶編號(hào)custID送貨地址orderAddress訂單總金額orderAmountD2010051616280012010-05-16901北京市朝陽(yáng)區(qū)聯(lián)盟小區(qū)9號(hào)樓6750.00D2010051716300022010-05-17903上海市虹梅路201號(hào)1232.00D2010060609230032010-06-06905廣州市江南大道中82號(hào)2500.00D2010061016280042010-06-10901北京市海淀區(qū)光華路55號(hào)6200.00D2010070109160052010-07-01902北京市北四環(huán)中路35號(hào)1425.00訂單編號(hào)orderID訂單日期orderDate客戶編號(hào)custID送貨地址orderAddress訂單總金額orderAmountD2010051616280012010-05-16901北京市朝陽(yáng)區(qū)聯(lián)盟小區(qū)9號(hào)樓6750.00D2010061016280042010-06-10901北京市海淀區(qū)光華路55號(hào)6200.004.2.3使用BETWEENAND的查詢謂詞BETWEEN…AND…可以用來(lái)判定表達(dá)式值在不在指定范圍內(nèi)的元組。形如：<表達(dá)式>[NOT]BETWEENAANDBA是范圍的下限，B是范圍的上限?！纠?.4】查詢價(jià)格在100-500（包含100和500）之間的商品編號(hào)SELECTpdIDFROMProductWHEREpdPriceBETWEEN100AND500相當(dāng)于SELECTpdIDFROMProductWHEREpdPrice>=100ANDpdPrice<=500【例4.5】查詢價(jià)格不在100-500之間的商品編號(hào)。SELECTpdIDFROMProductWHEREpdPriceNOTBETWEEN100AND500相當(dāng)于SELECTpdIDFROMProductWHEREpdPrice<100ANDpdPrice>5004.2.4字符串匹配查詢用于字符串比較，來(lái)實(shí)現(xiàn)模糊查詢。形如：<列名>[NOT]LIKE<字符串常數(shù)>[ESCAPE<轉(zhuǎn)義字符>]<字符串常數(shù)>可以包含通配符

%(百分號(hào))代表任意長(zhǎng)度（長(zhǎng)度可以為0）的字符串例：a%b表示以a開(kāi)頭，以b結(jié)尾的任意長(zhǎng)度的字符串。如acb，addgb，ab等都滿足該匹配串。_(下劃線)代表任意單個(gè)字符例：a_b表示以a開(kāi)頭，以b結(jié)尾的長(zhǎng)度為3的任意字符串。如acb，afb等都滿足該匹配串?！纠?.6】查詢客戶“里奇”的聯(lián)系電話。SELECTcustPboneFROMCustomerWHEREcustNameLIKE‘里奇’等價(jià)于：SELECTcustPboneFROMCustomerWHEREcustName=‘里奇’當(dāng)‘<字符串常數(shù)>’不含有通配符時(shí)，可以用=運(yùn)算符取代LIKE

謂詞，用!=或<>運(yùn)算符取代NOTLIKE

謂詞。【例4.7】查詢所有姓張的客戶的基本情況。SELECT*FROMCustomerWHEREcustNameLIKE‘張%’客戶編號(hào)custID客戶名稱(chēng)custName客戶所在城市custCity客戶電話custPhone901里奇北京13844472365902環(huán)宇北京18955532012903張松林上海13677712316905賈立委廣州15985612316906張小寧

客戶編號(hào)custID客戶名稱(chēng)custName客戶所在城市custCity客戶電話custPhone903張松林上海13677712316906張小寧

【例4.8】查詢客戶名稱(chēng)第2個(gè)字為“立”的客戶的基本情況。SELECT*FROMCustomerWHEREcustNameLIKE‘_立%’客戶編號(hào)custID客戶名稱(chēng)custName客戶所在城市custCity客戶電話custPhone901里奇北京13844472365902環(huán)宇北京18955532012903張松林上海13677712316905賈立委廣州15985612316906張小寧

客戶編號(hào)custID客戶名稱(chēng)custName客戶所在城市custCity客戶電話custPhone905賈立委廣例4.9】查詢所有不姓賈的客戶的基本情況。SELECT*FROMCustomerWHEREcustNameNOTLIKE‘賈%’客戶編號(hào)custID客戶名稱(chēng)custName客戶所在城市custCity客戶電話custPhone901里奇北京13844472365902環(huán)宇北京18955532012903張松林上海13677712316905賈立委廣州15985612316906張小寧

客戶編號(hào)custID客戶名稱(chēng)custName客戶所在城市custCity客戶電話custPhone901里奇北京13844472365902環(huán)宇北京18955532012903張松林上海13677712316906張小寧

【例4.10】查詢所有客戶名稱(chēng)以“張_”開(kāi)頭的客戶的基本情況。當(dāng)用戶要查詢的字符串本身就含有%或_時(shí)，要使用ESCAPE'<轉(zhuǎn)義字符>'短語(yǔ)對(duì)通配符進(jìn)行轉(zhuǎn)義。SELECT*FROMCustomerWHEREcustNameLIKE‘張\_%’ESCAPE‘\’4.2.5空值查詢形如：<表達(dá)式>IS[NOT]NULL【例4.11】查詢?nèi)鄙俾?lián)系電話的客戶名單。SELECTcustNameFROMCustomerWHEREcustPhoneISNULL【例4.12】查詢所有有聯(lián)系電話的客戶名單。SELECTcustNameFROMCustomerWHEREcustPhoneISNOTNULL4.2.6使用IN的查詢謂詞IN可以用來(lái)查找表達(dá)式值屬于不屬于指定集合的元組。形如：<表達(dá)式>[NOT]IN<集合><集合>：用逗號(hào)分隔的一組取值【例4.13】查詢北京和廣州的客戶的基本情況。SELECT*FROMCustomerWHEREcustCityIN(‘北京’,’廣州’)等價(jià)于：SELECT*FROMCustomerWHEREcustCity=‘北京’ORcustCity=’廣州’【例4.14】查詢除了北京和廣州之外，其余城市的客戶的基本情況SELECT*FROMCustomerWHEREcustCityNOTIN(‘北京’,’廣州’)4.2.7DISTINCT短語(yǔ)的使用帶DISTINCT的語(yǔ)句：SELECTDISTINCTcustCityFROMCustomer注意：

DISTINCT短語(yǔ)的作用范圍是所有目標(biāo)列，在SELECT子句中只出現(xiàn)一次。查詢所有客戶所在城市。不帶DISTINCT的語(yǔ)句：SELECTcustCityFROMCustomer客戶編號(hào)custID客戶名稱(chēng)custName客戶所在城市custCity客戶電話custPhone901里奇北京13844472365902環(huán)宇北京18955532012903張松林上海13677712316905賈立委廣州15985612316906張小寧

客戶所在城市custCity北京北京上海廣州

帶DISTINCT的語(yǔ)句：SELECTDISTINCTcustCityFROMCustomer客戶所在城市custCity北京上海廣州

【例4.15】注意：DISTINCT短語(yǔ)的作用范圍是所有目標(biāo)列，在SELECT子句中只出現(xiàn)一次。4.2.8存儲(chǔ)查詢結(jié)果INTO子句將查詢結(jié)果存儲(chǔ)到指定的新基本表中【例4.16】查詢所有北京客戶的客戶編號(hào)、客戶名稱(chēng)和客戶電話，并將其存入新基本表BJ_Customer中。SELECTcustD,custName,custPhoneINTOBJ_CustomerFROMCustomer4.2.9帶排序的查詢希望查詢結(jié)果中的元組按某種順序排列基本格式是：ORDERBY<列名1>[ASC|DESC][,<列名2>[ASC|DESC]…]

作用將SELECT語(yǔ)句的查詢結(jié)果中所有元組按照要求的順序排列首先按第一個(gè)屬性列值排列；第一個(gè)屬性列值相同者，再按下一個(gè)屬性列值排列，依次類(lèi)推。ASC，則表示該列值以升序排列；DESC，則表示該列值以降序排列。省略不寫(xiě)，默認(rèn)為升序排列?！纠?.17】

查詢所有商品的基本信息，并首先按照生產(chǎn)廠商升序排列，同一個(gè)生產(chǎn)廠商的商品按照價(jià)格降序排列。SELECT*FROMProductORDERBYpdSupplier,pdPriceDESC【例4.17】查詢結(jié)果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短語(yǔ)的作用指定返回查詢結(jié)果的“前”一組結(jié)果，該短語(yǔ)必須和ORDERBY一起使用。格式如下:[TOP表達(dá)式[PERCENT]][WITHTIES]說(shuō)明：表達(dá)式指出返回多少結(jié)果如果同時(shí)選用了PERCENT則按百分比進(jìn)行計(jì)算如果選用WITHTIES則返回與規(guī)定的最后一個(gè)元組具有相同排序值的其他所有行。【例4.18】查詢所有商品中庫(kù)存數(shù)量最少的兩種商品的基本信息。SELECTTOP

2

*FROMProductORDERBYpdStockSize如果使用WITHTIES，即語(yǔ)句為：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集合運(yùn)算集合操作種類(lèi)并操作(UNION)交操作(INTERSECT)差操作(EXCEPT)注意：參與集合操作的各查詢結(jié)果必須是相容的，即列數(shù)必須相同，且對(duì)應(yīng)屬性列的數(shù)據(jù)類(lèi)型也必須相同。1．并使用UNION可以把查詢的結(jié)果合并起來(lái)并且去掉重復(fù)的元組，如果要保留所有重復(fù)的元組，則必須使用UNIONALL?！纠?.19】查詢北京和上海的客戶的基本信息。SELECT*FROMCustomerWHEREcustCity='北京'UNIONSELECT*FROMCustomerWHEREcustCity='上海'【例4.20】查詢包含了編號(hào)為“11203”的商品或編號(hào)為“11206”的商品的訂單編號(hào)。SELECTorderIDFROMOrderDetailWHEREpdID='11203'UNIONSELECTorderIDFROMOrderDetailWHEREpdID='11206'等價(jià)于：SELECTDISTINCTorderIDFROMOrderDetailWHEREpdIDIN('11203','11206')2．交【例4.21】查詢“上海雙喜”生產(chǎn)的價(jià)格在100元以上的商品信息。SELECT*FROMProductWHEREpdSupplier='上海雙喜'INTERSECTSELECT*FROMProductWHEREpdPrice>100等價(jià)于：SELECT*FROMProductWHEREpdSupplier='上海雙喜'ANDpdPrice>1003．差【例4.22】查詢包含編號(hào)為“11203”的商品但不包含編號(hào)為“11206”的商品的訂單編號(hào)。SELECTorderIDFROMOrderDetailWHEREpdID='11203'EXCEPTSELECTorderIDFROMOrderDetailWHEREpdID='11206'【例4.23】查詢“上海雙喜”生產(chǎn)的價(jià)格不低于200元的商品信息。SELECT*FROMProductWHEREpdSupplier='上海雙喜'EXCEPTSELECT*FROMProductWHEREpdPrice<=200等價(jià)于：SELECT*FROMProductWHEREpdSupplier='上海雙喜'ANDpdPrice>200對(duì)集合操作結(jié)果的排序ORDERBY子句只能用于對(duì)最終查詢結(jié)果排序，不能對(duì)中間結(jié)果排序任何情況下，ORDERBY子句只能出現(xiàn)在最后

SELECTorderIDFROMOrderDetailWHEREpdID='11203'ORDERBYorderIDUNIONSELECTorderIDFROMOrderDetailWHEREpdID='11206'ORDERBYorderID例－－錯(cuò)誤寫(xiě)法

SELECTorderIDFROMOrderDetailWHEREpdID='11203'UNIONSELECTorderIDFROMOrderDetailWHEREpdID='11206'ORDERBYorderID正確寫(xiě)法4.3連接查詢連接查詢涉及到多個(gè)表的查詢是關(guān)系數(shù)據(jù)庫(kù)中最主要的查詢功能基本格式中：FROM子句要指明進(jìn)行連接的表名；SELECT子句中要指明多表檢索的結(jié)果表中的屬性名列。WHERE子句要指明連接的列名及其連接條件；一般格式：[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>

[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>連接字段連接謂詞中的列名稱(chēng)為連接字段連接條件中的各連接字段類(lèi)型必須是可比的，但不必是相同的4.3.1一般連接【例4.24】查詢所有客戶訂單的客戶編號(hào)、客戶名稱(chēng)、訂單編號(hào)和送貨地址。SELECTCustomer.custID,custName,orderID,orderAdressFROMCustomer,OrderWHERECustomer.custID=Order.custID連接操作的執(zhí)行過(guò)程首先在表1中找到第一個(gè)元組，然后從頭開(kāi)始掃描表2，逐一查找滿足連接件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。表2全部查找完后，再找表1中第二個(gè)元組，然后再?gòu)念^開(kāi)始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。重復(fù)上述操作，直到表1中的全部元組都處理完畢?！纠?.24】可能的執(zhí)行過(guò)程客戶編號(hào)custID客戶名稱(chēng)custName客戶所在城市custCity客戶電話custPhone901里奇北京13844472365902環(huán)宇北京18955532012903張松林上海13677712316905賈立委廣州15985612316906張小寧

訂單編號(hào)orderID訂單日期orderDate客戶編號(hào)custID送貨地址orderAddress訂單總金額orderAmountD2010051616280012010-5-16901北京市朝陽(yáng)區(qū)聯(lián)盟小區(qū)9號(hào)樓6750D2010051716300022010-5-17903上海市虹梅路201號(hào)1540D2010060609230032010-6-6905廣州市江南大道中82號(hào)2500D2010061016280042010-6-10901北京市海淀區(qū)光華路55號(hào)6200D2010070109160052010-7-1902北京市北四環(huán)中路35號(hào)1425訂單編號(hào)orderID訂單日期

orderDate客戶編號(hào)custID送貨地址orderAddress訂單總金額

orderAmount客戶名稱(chēng)custName客戶所在城市custCity客戶電話custPhoneD2010051616280012010-5-16901北京市朝陽(yáng)區(qū)聯(lián)盟小區(qū)9號(hào)樓6750里奇北2010051716300022010-5-17903上海市虹梅路201號(hào)1540張松林上2010060609230032010-6-6905廣州市江南大道中82號(hào)2500賈立委廣2010061016280042010-6-10901北京市海淀區(qū)光華路55號(hào)6200里奇北2010070109160052010-7-1902北京市北四環(huán)中路35號(hào)1425環(huán)宇北戶編號(hào)custID客戶名稱(chēng)custName訂單編號(hào)orderID送貨地址orderAddress901里奇D201005161628001北京市朝陽(yáng)區(qū)聯(lián)盟小區(qū)9號(hào)樓903張松林D201005171630002上海市虹梅路201號(hào)905賈立委D201006060923003廣州市江南大道中82號(hào)901里奇D201006101628004北京市海淀區(qū)光華路55號(hào)902環(huán)宇D201007010916005北京市北四環(huán)中路35號(hào)【例4.25】查詢北京客戶的訂單編號(hào)、客戶名稱(chēng)和送貨地址。SELECTorderID,custName,orderAdressFROMCustomer,OrderWHERECustomer.custID=Order.custIDANDcustCity='北京'【例4.26】查詢客戶張松林訂購(gòu)商品的訂單編號(hào)、送貨地址、商品編號(hào)和訂購(gòu)數(shù)量。SELECTOrder.orderID,orderAddress,pdID,quantityFROMCustomer,Order,OrderDetailWHERECustomer.custID=Order.custIDANDOrder.orderID=OrderDetail.orderIDANDcustName='張松林'【例4.27】查詢北京客戶的訂單編號(hào)、客戶名稱(chēng)、商品編號(hào)、商品名稱(chēng)和訂購(gòu)數(shù)量。SELECTOrder.orderID,custName,Product.pdID,pdName,quantityFROMCustomer,Order,Product,OrderDetailWHERECustomer.custID=Order.custIDANDOrder.orderID=OrderDetail.orderIDANDOrderDetail.pdID=Product.pdIDANDcustCity='北京'內(nèi)連接形如：INNERJOIN…ON…【例4.28】用INNERJOIN…ON…重新實(shí)現(xiàn)例4.25:查詢北京客戶的訂單編號(hào)、客戶名稱(chēng)和送貨地址。SELECTorderID,custName,orderAdressFROMCustomerINNERJOINOrderONCustomer.custID=Order.custIDWHEREcustCity='北京'【例4.29】用INNERJOIN…ON…重新實(shí)現(xiàn)例4.26:查詢客戶張松林訂購(gòu)商品的訂單編號(hào)、送貨地址、商品編號(hào)和訂購(gòu)數(shù)量SELECTOrder.orderID,orderAddress,pdID,quantityFROMCustomerINNERJOINOrderONCustomer.custID=Order.custIDINNERJOINOrderDetailONOrder.orderID=OrderDetail.orderIDWHEREcustName='張松林'4.3.2別名和自連接查詢一個(gè)表與自身進(jìn)行的連接【例4.30】查詢和客戶“環(huán)宇”在同一城市的客戶的基本信息。

SELECTCA.*FROMCustomerCA,CustomerCBWHERECA.custCity=CB.custCityAND CB.custName='環(huán)宇'4.3.3外連接查詢外連接與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出形如：左外連接(LEFTJOIN…ON…)右外連接(RIGHTJOIN…ON…)?！纠?.31】用外連接改寫(xiě)例4.24:查詢所有客戶訂單的客戶編號(hào)、客戶名稱(chēng)、訂單編號(hào)和送貨地址。SELECTCustomer.custID,custName,orderID,orderAddressFROMCustomerLEFTJOINOrderONCustomer.custID=Order.custID

客戶編號(hào)custID客戶名稱(chēng)custName訂單編號(hào)orderID送貨地址orderAddress901里奇D201005161628001北京市朝陽(yáng)區(qū)聯(lián)盟小區(qū)9號(hào)樓901里奇D201006101628004北京市海淀區(qū)光華路55號(hào)902環(huán)宇D201007010916005北京市北四環(huán)中路35號(hào)903張松林D201005171630002上海市虹梅路201號(hào)905賈立委D201006060923003廣州市江南大道中82號(hào)906張小寧4.4分組及匯總查詢分組及匯總查詢對(duì)查詢結(jié)果進(jìn)行分組計(jì)算和匯總計(jì)算分組查詢用GROUPBY子句完成計(jì)算的函數(shù)稱(chēng)為聚合函數(shù)COUNT(*)：統(tǒng)計(jì)元組個(gè)數(shù)COUNT(<列名>)：統(tǒng)計(jì)一列中值的個(gè)數(shù)SUM(<列名>)：計(jì)算一列值的總和AVG(<列名>)：計(jì)算一列值的平均值MAX(<列名>)：求一列中的最大值MIN(<列名>)：求一列值的最小值說(shuō)明：除COUNT（*）外，其它聚集函數(shù)在計(jì)算時(shí)會(huì)忽略空值。在<列名>前加入DISTINCT保留字，會(huì)將查詢結(jié)果的列去掉重復(fù)值在計(jì)算。4.4.1一般匯總查詢【例4.32】統(tǒng)計(jì)客戶所在城市的個(gè)數(shù)。SELECTCOUNT（DISTINCTcustCity）FROMCustomer

3客戶編號(hào)custID客戶名稱(chēng)custName客戶所在城市custCity客戶電話custPhone901里奇北京13844472365902環(huán)宇北京18955532012903張松林上海13677712316905賈立委廣州15985612316906張小寧

【例4.32】可以使用下列語(yǔ)句為屬性列進(jìn)行命名。SELECTCOUNT（DISTINCTcustCity）AS

custCityNumber

FROMCustomer

custCityNumber3客戶編號(hào)custID客戶名稱(chēng)custName客戶所在城市custCity客戶電話custPhone901里奇北京13844472365902環(huán)宇北京18955532012903張松林上海13677712316905賈立委廣州15985612316906張小寧

【例4.33】【例4.33】查詢訂單的個(gè)數(shù)和總金額之和。SELECTCOUNT(*)ASorderNumber,SUM(orderAmount)ASSumAmountFROMOrders4.4.2使用GROUPBY的分組匯總查詢GROUPBY子句把元組按某一指定列（或一些列）上的值相等的原則分組，然后再對(duì)每組數(shù)據(jù)進(jìn)行規(guī)定的操作。對(duì)分組進(jìn)行匯總可通過(guò)聚合函數(shù)實(shí)現(xiàn)【例4.34】查詢每一種商品的銷(xiāo)售總量。SELECTpdID,SUM(quantity)ASTotalFROMorderDetailGROUPBYpdID訂單編號(hào)

orderID商品編號(hào)

pdID訂購(gòu)數(shù)量

quantityD2010051616280011120310D2010051616280011120640D2010051616280011220115D201005171630002112034D201005171630002122025D2010060609230031120610D2010060609230031120910D2010061016280041120510D2010061016280041120820D2010061016280041121010D2010061016280041220210D201007010916005112075D201007010916005112105pdIDTotal11203451120520112062511207511208101120910112101512201101220214GROUPBY子句的說(shuō)明GROUPBY子句分組方法：按指定的一列或多列值分組，值相等的為一組使用GROUPBY子句后，SELECT子句的列名列表中只能出現(xiàn)分組屬性和集函數(shù)

【例4.35】【例4.35】查詢每個(gè)城市的客戶總數(shù)。SELECTcustCity,COUNT(*)AScustCountFROMCustomerGROUPBYcustCity

【例4.36】查詢被購(gòu)買(mǎi)3次以上的商品的商品編號(hào)和銷(xiāo)售數(shù)量的最大值和最小值。SELECTpdID,MAX(quantity)ASMAX_quantity,MIN(quantity)ASMIN_quantityFROMOrderDetailGROUPBYpdIDHAVINGCOUNT(*)>=3HAVING短語(yǔ)與WHERE子句的區(qū)別：作用對(duì)象不同WHERE子句作用于基表或視圖，從中選擇滿足條件的元組。HAVING短語(yǔ)作用于組，從中選擇滿足條件的組。集函數(shù)的條件關(guān)系必須用Having，Where中不應(yīng)出現(xiàn)集函數(shù)4.4.3帶明細(xì)的分組匯總查詢GROUPINGSETS短語(yǔ)在顯示匯總的同時(shí)也顯示明細(xì)信息【例4.37】查詢所有商品信息的訂單明細(xì)，并分類(lèi)匯總各種商品的銷(xiāo)售數(shù)量。SELECTpdID,orderID,SUM(quantity)asTotalFROMOrderDetailGROUPBYGROUPINGSETS(pdID,(pdID,orderID,quantity))pdIDorderIDTotalpdIDorderIDTotal11203D2010051616280014011208NULL1011203D201005171630002511209D2010060609230031011203NULL4511209NULL1011205D2010061016280042011210D2010061016280041011205NULL2011210D201007010916005511206D2010051616280011511210NULL1511206D2010060609230031012201D2010051616280011011206NULL2512201NULL1011207D201007010916005512202D201005171630002411207NULL512202D2010061016280041011208D2010061016280041012202NULL144.5嵌套查詢嵌套查詢一個(gè)SELECT-FROM-WHERE語(yǔ)句稱(chēng)為一個(gè)查詢塊。將一個(gè)查詢塊(SELECT-FROM-WHERE)嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語(yǔ)的條件表達(dá)式中的查詢，稱(chēng)為嵌套查詢。其中外層查詢稱(chēng)為父查詢，內(nèi)層查詢稱(chēng)為子查詢。執(zhí)行過(guò)程:是由里至外的，每一個(gè)子查詢是在上一級(jí)查詢處理之前完成的。注意子查詢中不能用ORDERBY語(yǔ)句4.5.1內(nèi)外層不相關(guān)

的嵌套查詢1．返回單值的子查詢確定知道子查詢返回的檢索信息是單一的值。這類(lèi)子查詢的結(jié)果可以看做一個(gè)常量值，參與=、!=(<>)、>、>=、<、<=等關(guān)系比較運(yùn)算?！纠?.38】查詢編號(hào)為“D201005161628001”的訂單的客戶名稱(chēng)和聯(lián)系電話。

SELECTcustName,custPhoneFROMCustomerWHEREcustID=(SELECTcustIDFROMOrdersWHEREorderID='D201005161628001')

可能的執(zhí)行過(guò)程：首先執(zhí)行內(nèi)層子查詢，找到orderID='D201005161628001'的custID，即901然后再執(zhí)行外層父查詢，找到custID=901的custName,custPhone。等價(jià)于SELECTcustName,custPhoneFROMCustomerWHEREcustIDIN(SELECTcustIDFROMOrdersWHEREorderID='D201005161628001')【例4.39】查詢比編號(hào)為“D201006101628004”訂單的訂單金額還高的訂單編號(hào)和客戶編號(hào)。

SELECTorderID,custIDFROMOrdersWHEREorderAmount>(SELECTorderAmountFROMOrderWHEREorderID='D201006101628004')不相關(guān)子查詢子查詢的查詢條件不依賴(lài)于父查詢。求解方法：由里向外逐層處理。即每個(gè)子查詢?cè)谏弦患?jí)查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。SELECTcustName,custPhone外層查詢/父查詢FROMCustomerWHEREcustIDIN

(SELECTcustID內(nèi)層查詢/子查詢FROMOrderWHEREorderID='D201005161628001')2．返回多值的子查詢使用ALL、ANY等謂詞ANY：任意一個(gè)值A(chǔ)LL：所有值需要配合使用比較運(yùn)算符>ANY 大于子查詢結(jié)果中的某個(gè)值>ALL 大于子查詢結(jié)果中的所有值<ANY 小于子查詢結(jié)果中的某個(gè)值<ALL 小于子查詢結(jié)果中的所有值>=ANY 大于等于子查詢結(jié)果中的某個(gè)值>=ALL 大于等于子查詢結(jié)果中的所有值<=ANY 小于等于子查詢結(jié)果中的某個(gè)值<=ALL 小于等于子查詢結(jié)果中的所有值=ANY 等于子查詢結(jié)果中的某個(gè)值=ALL 等于子查詢結(jié)果中的所有值（通常沒(méi)有實(shí)際意義）!=（或<>）ANY 不等于子查詢結(jié)果中的某個(gè)值!=（或<>）ALL 不等于子查詢結(jié)果中的任何一個(gè)值【例4.40】查詢比所有“天津南生”的商品價(jià)格都高的商品信息。SELECT*FROMProductWHEREpdPrice>ALL(SELECTpdPriceFROMProductWHEREpdSupplier='天津南生')等價(jià)于SELECT*FROMProductWHEREpdPrice>(SELECTMAX(pdPrice)FROMProductWHEREpdSupplier='天津南生')【例4.41】查詢至少比“上海雙喜”一種商品價(jià)格低的商品信息。SELECT*FROMProductWHEREpdPrice<ANY(SELECTpdPriceFROMProductWHEREpdSupplier='上海雙喜')等價(jià)于：SELECT*FROMProductWHEREpdPrice<(SELECTMAX(pdPrice)FROMProductWHEREpdSupplier='上海雙喜')【例4.42】查詢至少訂購(gòu)了“上海雙喜”一種商品的訂單編號(hào)。SELECTDISTINCTorderIDFROMOrderDetailWHEREpdID=ANY(SELECTpdIDFROMProductWHEREpdSupplier='上海雙喜')等價(jià)于SELECTDISTINCTorderIDFROMOrderDetailWHEREpdIDIN(SELECTpdIDFROMProductWHEREpdSupplier='上海雙喜')ANY和ALL謂詞有時(shí)可以用聚集函數(shù)實(shí)現(xiàn)用聚集函數(shù)實(shí)現(xiàn)子查詢通常比直接用ANY或ALL查詢效率要高，因?yàn)榍罢咄ǔＤ軌驕p少比較次數(shù)ANY與ALL和聚集函數(shù)的對(duì)應(yīng)關(guān)系

=<>或!=

<<=>>=ANY

IN--

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

NOTIN

<MIN<=MIN>MAX>=MAX4.5.2內(nèi)外層互相關(guān)的嵌套查詢子查詢的查詢條件依賴(lài)于父查詢，稱(chēng)為相關(guān)子查詢?！纠?.43】查詢每個(gè)客戶的具有最高總金額的訂單信息。SELECT*FROMOrdersAWHEREorderAmount=(SELECTMAX(orderAmount)FROMOrdersBWHEREB.custID=A.custID)ORDERBYorderID4.5.3使用EXISTS的

嵌套查詢4.5.3使用EXISTS的嵌套查詢使用[NOT]EXISTS謂詞形如：[NOT]EXISTS（子查詢）結(jié)果只與（子查詢）的結(jié)果是否為空有關(guān)若（子查詢）結(jié)果為空，則EXISTS返回“假”，NOTEXISTS返回“真”；若（子查詢）結(jié)果為不空，則EXISTS返回“真”，NOTEXISTS返回“假”【例4.44】查詢訂購(gòu)了“羽毛球”的訂單編號(hào)。SELECTDISTINCTorderIDFROMOrderDetailWHEREEXISTS(SELECT*FROMProductWHEREProduct.pdID=OrderDetail.pdIDANDpdName='羽毛球')可能的執(zhí)行過(guò)程首先取外層查詢中OrderDetail表的第一個(gè)元組，根據(jù)它的pdID值處理內(nèi)層查詢，若在Product表中找到等于外層元組pdID值且pdName值為“羽毛球”的元組，則內(nèi)層查詢結(jié)果非空，EXISTS返回“真”值，OrderDetail的元組符合條件，將其orderID輸出到結(jié)果表；若在Product表中找不到符合條件的元組，則內(nèi)層查詢結(jié)果為空，EXISTS返回“假”值，OrderDetail的元組不符合條件，跳過(guò)。取OrderDetail表的下一個(gè)元組，重復(fù)上述過(guò)程，直至OrderDetail表全部檢索完畢。演示使用IN的等價(jià)查詢本例中的查詢也可使用含IN謂詞的非相關(guān)子查詢完成：SELECTDISTINCTorderIDFROMOrderDetailWHEREpdIDIN(SELECTpdIDFROMProductWHEREpdName='羽毛球')【例4.45】檢索北京客戶的訂單編號(hào)和送貨地址。SELECTorderID,orderAddressFROMOrdersWHEREEXISTS(SELECT*FROMCustomerWHERECustomer.custID=Orders.custIDANDcustCity='北京')【例4.46】查詢沒(méi)有訂購(gòu)編號(hào)為“11206”商品的訂單編號(hào)。SELECTorderIDFROMOrdersWHERENOTEXISTS(SELECT*FROMOrderDetailWHEREOrderDetail.orderID=Orders.orderIDANDpdID='11206')使用IN的等價(jià)查詢本例的查詢也可使用含NOTIN謂詞的不相關(guān)子查詢完成：SELECTorderIDFROMOrdersWHEREorderIDNOTIN(SELECTorderIDFROMOrderDetailWHEREpdID='11206')不同形式的查詢間的替換一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價(jià)替換所有帶IN謂詞、比較運(yùn)算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價(jià)替換?！纠?.47】用EXISTS改寫(xiě)例4.42：查詢至少訂購(gòu)了“上海雙喜”一種商品的訂單編號(hào)。SELECTDISTINCTorderIDFROMOrderDetailWHEREEXISTS(SELECTpdIDFROMProductWHEREOrderDetail.pdID=Product.pdIDANDpdSupplier='上海雙喜')【例4.48】用NOTEXISTS改寫(xiě)例4.40：查詢比所有“天津南生”的商品價(jià)格都高的商品信息。SELECT*FROMProductAWHERENOTEXISTS(SELECT*FROMProductBWHEREB.pdSupplier='天津南生'ANDB.pdPrice>A.pdPrice)【例4.49】查詢訂單中包含了全部商品的訂單編號(hào)。SELECTorderIDFROMOrdersWHERENOTEXISTS(SELECT*FROMProductWHERENOTEXISTS(SELECT*FROMOrderDetailWHEREOrderDetail.orderID=Orders.orderIDANDOrderDetail.pdID=Product.pdID))

【例4.50】查詢訂單中包含了“天津南生”全部商品的訂單編號(hào)。SELECTorderIDFROMOrderWHERENOTEXISTS(SELECT*FROMProductWHEREpdSupplier='天津南生'ANDNOTEXISTS(SELECT*FROM OrderDetailWHEREOrderDetail.orderID=Order.orderIDANDOrderDetail.pdID=Product.pdID))練習(xí)(1)檢索選修了課程號(hào)為1或2的課程，且成績(jī)高于或等于70分的學(xué)生的學(xué)號(hào)。(2)檢索選修了“Pascal語(yǔ)言”課程的學(xué)生的學(xué)號(hào)。(3)檢索所有學(xué)生的姓名、所選課程的課程名和成績(jī)，并且按課程號(hào)的升序和成績(jī)的降序進(jìn)行排列。(4)檢索所有學(xué)生中年齡最大的學(xué)生的姓名及年齡。(5)求計(jì)算機(jī)系學(xué)生中年齡的最大值和最小值。(6)求所有學(xué)生中女生的總?cè)藬?shù)。(7)求選課在四門(mén)以上的學(xué)生所選課程的平均成績(jī)。最后按降序列出平均成績(jī)名次名單來(lái)。學(xué)生表：Student(Sno，Sname，Ssex，Sage，Sdept)課程表：Course(Cno，Cname，Cpno，Ccredit)學(xué)生選課表：SC(Sno，Cno，Grade)4.6需要查詢支持的

數(shù)據(jù)操作插入刪除修改語(yǔ)句格式：INSERTINTO<基本表名>[(<列名1>，<列名2>，……，<列名n>)]

子查詢；功能：將子查詢結(jié)果插入指定表中說(shuō)明：如果列名序列省略，則子查詢所得到的數(shù)據(jù)列必須和指定基本表的數(shù)據(jù)列完全一致。如果列名序列給出，則子查詢結(jié)果與列名序列要一一對(duì)應(yīng)。4.6.1插入操作【例4.51】假設(shè)已用如下語(yǔ)句建立商品銷(xiāo)售總量表SalePd_sum(pdID,total)。CREATETABLESalePd_sum(pdIDvarchar(6)PRIMARYKEY,totalint)其中total表示每種商品的銷(xiāo)售總量。向SalePd_sum表中插入每種商品的銷(xiāo)售總量。INSERTINTOSalePd_sum(pdID,total)SELECTpdID,sum(quantity)FROMOrderDetailGROUPBYpdID4.6.2修改操作【例4.52】將所有銷(xiāo)售總量少于10的商品價(jià)格降低10%。UPDATEProductSETpdPrice=pdPrice*0.9WHEREpdIDNOTIN

(SELECTpdIDFROMOrderDetailGROUPBYpdIDHAVINGSUM(quantity)>=10)【例4.53】根據(jù)訂單明細(xì)中的訂購(gòu)數(shù)量×銷(xiāo)售價(jià)格之和更新每個(gè)訂單的總金額。UPDATEOrdersSETorderAmount=

(SELECTSUM（quantity*pdSellPrice)FROMOrderDetailWHEREorderID=Orders.orderID)【例4.54】刪除905號(hào)客戶的全部訂單的訂單細(xì)節(jié)。DELETEFROMOrderDetailWHEREorderIDIN(SELECTorderIDFROMOrdersWHEREcustID='905')4.6.3刪除操作4.7視圖及其操作視圖的定義和特點(diǎn)視圖(View)是外模式一級(jí)數(shù)據(jù)結(jié)構(gòu)的基本單位虛表，由基本表或其它視圖導(dǎo)出的表，其本身不存在于數(shù)據(jù)庫(kù)中。只存放視圖的定義，而不存放視圖對(duì)應(yīng)的數(shù)據(jù)。視圖一經(jīng)定義，就可被檢索或刪除，但更新操作有一定的限制，也可再定義其它視圖。4.7.1視圖的建立和刪除1．視圖的建立語(yǔ)法格式：CREATEVIEW

<視圖名>[(<列名>[，<列名>]…)]AS<子查詢>[WITHCHECKOPTION]【例4.55】建立一個(gè)所在城市為“北京”的客戶信息視圖BJCustomer。CREATEVIEWBJCustomerASSELECTcustID,custName,custPhoneFROMCustomerWHEREcustCity='北京'行列子集視圖從單個(gè)基本表導(dǎo)出只是去掉了基本表的某些行和某些列保留了主碼

WITHCHECKOPTION

對(duì)視圖進(jìn)行UPDATE，INSERT和DELETE操作時(shí)要保證更新、插入或刪除的行滿足視圖定義中的謂詞條件（即子查詢中的條件表達(dá)式）對(duì)BJCustomer視圖的更新操作：修改操作：RDBMS自動(dòng)加上custCity='北京'的條件刪除操作：RDBMS自動(dòng)加上custCity='北京'的條件插入操作：RDBMS自動(dòng)檢查custCity='北京'如果不是，則拒絕該插入操作如果沒(méi)有提供Sdept屬性值，則自動(dòng)定義custCity為'北京'子查詢不允許含有ORDERBY子句和DISTINCT短語(yǔ)說(shuō)明組成視圖的屬性列名，或者全部省略，或者全部指定。以下三種情況下必須明確指定全部屬性列：子查詢SELECT子句里列名中有常數(shù)、聚集函數(shù)或列表達(dá)式。子查詢SELECT子句里列名中有從多個(gè)表中選出的同名屬性列。需要用更合適的新列名作視圖列的列名。說(shuō)明(續(xù))【例4.56】建立一個(gè)生產(chǎn)廠商為“天津南生”的商品視圖TJ_Product，并要求進(jìn)行增、刪、改操作時(shí)仍需保證該視圖只有“天津南生”的商品。CREATEVIEWTJ_ProductASSELECTpdID,pdName,pdPriceFROMProductWHEREpdSupplier='天津南生'WITHCHECKOPTION【例4.57】建立訂購(gòu)了“天津南生”的商品的訂單信息視圖TJ_Order，包括訂單編號(hào)