模塊三

使用數(shù)據(jù)庫(kù)根據(jù)評(píng)標(biāo)專家?guī)旃芾硐到y(tǒng)業(yè)務(wù)需求，在對(duì)數(shù)據(jù)進(jìn)行查詢時(shí)還常常需要對(duì)數(shù)據(jù)進(jìn)行匯總統(tǒng)計(jì)，如匯總專家總?cè)藬?shù)、計(jì)算專家平均年齡、統(tǒng)計(jì)不同技術(shù)職稱的專家人數(shù)等。本任務(wù)主要介紹評(píng)標(biāo)專家?guī)旃芾硐到y(tǒng)的數(shù)據(jù)高級(jí)查詢功能，主要包括匯總數(shù)據(jù)、分組統(tǒng)計(jì)、多表連接查詢、嵌套查詢及聯(lián)合查詢等。任務(wù)描述任務(wù)3數(shù)據(jù)高級(jí)查詢掌握聚合函數(shù)的用法12掌握分組統(tǒng)計(jì)的用法3掌握多表連接查詢、子查詢的語(yǔ)法學(xué)會(huì)將查詢結(jié)果合并的方法4掌握掌握學(xué)會(huì)掌握任務(wù)目標(biāo)聚合函數(shù)對(duì)數(shù)據(jù)表中的某一列值或一組值執(zhí)行計(jì)算，并返回單個(gè)值。聚合函數(shù)經(jīng)常與GROUPBY子句一起使用，進(jìn)行數(shù)據(jù)統(tǒng)計(jì)分析。除了COUNT()函數(shù)，聚合函數(shù)都忽略空值。聚合函數(shù)只能在以下位置作為表達(dá)式使用：

SELECT子句的目標(biāo)列表達(dá)式。

HAVING子句。知識(shí)準(zhǔn)備聚合函數(shù)知識(shí)點(diǎn)1MySQL中常用聚合函數(shù)及其功能見下表。MySQL中常用聚合函數(shù)及其功能函數(shù)名功能COUNT(*)返回檢索到的行數(shù)。COUNT([ALL|DISTINCT]expression)返回非空值的數(shù)量。SUM([ALL|DISTINCT]expression)返回表達(dá)式中所有值的和，表達(dá)式必須是數(shù)值類型。AVG([ALL|DISTINCT]expression)返回表達(dá)式中所有值的平均值，表達(dá)式必須是數(shù)值類型。MAX([ALL|DISTINCT]expression)返回表達(dá)式中的最大值。MIN([ALL|DISTINCT]expression)返回表達(dá)式中的最小值。知識(shí)準(zhǔn)備聚合函數(shù)知識(shí)點(diǎn)1默認(rèn)選項(xiàng)，表示對(duì)所有的值進(jìn)行聚合函數(shù)運(yùn)算。表示在計(jì)算時(shí)去除重復(fù)值。有時(shí)需要先將表中的數(shù)據(jù)分組，再對(duì)每組進(jìn)行統(tǒng)計(jì)計(jì)算，例如，統(tǒng)計(jì)男女專家人數(shù)、各職稱專家人數(shù)等。GROUPBY子句按照指定的列對(duì)查詢結(jié)果進(jìn)行分組統(tǒng)計(jì)，每一組返回一條統(tǒng)計(jì)記錄。其基本語(yǔ)法格式如下：GROUPBY{column_name|expr|position},…[WITHROLLUP]知識(shí)準(zhǔn)備GROUPBY子句知識(shí)點(diǎn)2指定用于分組的列。指定用于分組的表達(dá)式。指定用于分組的列在查詢結(jié)果集中的位置，通常是一個(gè)正整數(shù)。為可選項(xiàng)，指定在結(jié)果集中不僅包含由GROUPBY子句分組后的數(shù)據(jù)行，還包含匯總行。GROUPBY子句的使用規(guī)則如下：

GROUPBY子句中的列不能使用聚合函數(shù)。

除了聚合函數(shù)，SELECT子句中的目標(biāo)列都應(yīng)在GROUPBY子句中出現(xiàn)。

如果用于分組的列包含空值NULL，則所有的NULL作為一個(gè)組。知識(shí)準(zhǔn)備GROUPBY子句知識(shí)點(diǎn)2如果數(shù)據(jù)分組后還要對(duì)這些組按條件進(jìn)行篩選，輸出滿足條件的組，則要使用篩選子句HAVING。HAVING子句一定要放在GROUPBY子句后面。HAVING子句的作用與WHERE子句相似，WHERE子句中可用的通配符、運(yùn)算符等在HAVING子句中也可以使用。WHERE子句與HAVING子句的區(qū)別：WHERE子句作用于表（在分組之前對(duì)表中的數(shù)據(jù)先篩選），而HAVING子句作用于組（在分組之后對(duì)生成的組進(jìn)行篩選）；HAVING子句中可以使用聚合函數(shù)，而WHERE子句中不能。當(dāng)兩者同時(shí)出現(xiàn)時(shí)，先執(zhí)行WHERE子句過(guò)濾掉不符合條件的數(shù)據(jù)，然后用GROUPBY子句對(duì)余下的數(shù)據(jù)按照指定列分組，最后再用HAVING子句排除一些組。知識(shí)準(zhǔn)備HAVING子句知識(shí)點(diǎn)3數(shù)據(jù)庫(kù)本著精簡(jiǎn)的設(shè)計(jì)原則，通常將數(shù)據(jù)存放于不同的表中，最大限度地減少數(shù)據(jù)冗余。在實(shí)際應(yīng)用中，經(jīng)常需要從多個(gè)數(shù)據(jù)表中查詢滿足一定條件的記錄，這時(shí)就要用到連接查詢。連接查詢分為交叉連接、內(nèi)連接和外連接。知識(shí)準(zhǔn)備連接查詢知識(shí)點(diǎn)41.交叉連接（CROSSJOIN）交叉連接返回被連接表中所有數(shù)據(jù)行的笛卡爾積，查詢結(jié)果集的總行數(shù)等于被連接表行數(shù)的乘積，總列數(shù)是被連接表列數(shù)的總和。其基本語(yǔ)法格式如下：在FROM子句中也可以省略CROSSJOIN，使用逗號(hào)分隔被連接的表，其基本語(yǔ)法格式如下：交叉連接產(chǎn)生的結(jié)果集一般沒(méi)有實(shí)際應(yīng)用的意義，所以這種連接很少使用。知識(shí)準(zhǔn)備連接查詢知識(shí)點(diǎn)4SELECT*FROM表名1CROSSJOIN表名2SELECT*FROM表名1,表名22.內(nèi)連接（INNERJOIN）內(nèi)連接是最常使用的連接查詢方式，通過(guò)INNERJOIN或者JOIN連接兩個(gè)表，結(jié)果集中只包含滿足連接條件的記錄。連接條件通常采用“主鍵=外鍵”的形式。內(nèi)連接創(chuàng)建連接關(guān)系有以下兩種方式：

在WHERE子句中創(chuàng)建連接關(guān)系，兩個(gè)表名出現(xiàn)在FROM子句中，其基本語(yǔ)法格式如下：

在FROM子句中創(chuàng)建連接關(guān)系，其基本語(yǔ)法格式如下：知識(shí)準(zhǔn)備連接查詢知識(shí)點(diǎn)4SELECT列名列表FROM表名1,表名2WHERE表名1.列名=表名2.列名SELECT列名列表FROM表名1[INNER]JOIN表名2ON表名1.列名=表名2.列名使用內(nèi)連接進(jìn)行數(shù)據(jù)查詢時(shí)，需要注意以下幾點(diǎn)：

可以在FROM子句指定表的同時(shí)定義表的別名，格式為：表名[AS]表別名。

如果連接的表中有相同的列名，要求加上表名或表別名作為前綴來(lái)限定列名，即用“表名.列名”或“表別名.列名”表示，明確列名來(lái)自哪個(gè)數(shù)據(jù)表，否則系統(tǒng)將無(wú)法執(zhí)行此查詢，并提示錯(cuò)誤信息。表別名往往是一個(gè)縮短了的表名，如果定義了表別名，則不能使用表名。如果列名不重名，可以不加表名或表別名前綴。

當(dāng)一個(gè)表與它自身進(jìn)行連接時(shí)，稱為表的自連接。自連接可以理解為一個(gè)表的兩個(gè)副本之間的連接。使用自連接時(shí)，必須為表指定別名，且列的引用需要加上表別名前綴。知識(shí)準(zhǔn)備連接查詢知識(shí)點(diǎn)4說(shuō)明：在FROM子句中創(chuàng)建連接關(guān)系，是ANSISQL92的標(biāo)準(zhǔn)語(yǔ)法，有助于將連接條件與WHERE子句中的查詢條件區(qū)分開，推薦大家在實(shí)際應(yīng)用中使用這種方式。3.外連接（OUTERJOIN）在內(nèi)連接查詢中，結(jié)果集中只包括滿足連接條件的數(shù)據(jù)行，但有時(shí)用戶也希望在結(jié)果集中能顯示那些不滿足連接條件的數(shù)據(jù)，這就需要使用外連接查詢。外連接操作的類型可分為左外連接和右外連接。在創(chuàng)建外連接時(shí)，表在SQL語(yǔ)句中出現(xiàn)的順序非常重要。出現(xiàn)在JOIN左邊的表是“左表”，出現(xiàn)在JOIN右邊的表是“右表”。知識(shí)準(zhǔn)備連接查詢知識(shí)點(diǎn)4（1）左外連接（LEFT[OUTER]JOIN）左外連接指定在結(jié)果集中除了包括由內(nèi)連接返回的所有行之外，還包括左表中所有不滿足連接條件的行，并將結(jié)果集中右表的輸出列設(shè)置為NULL。因此，左外連接可以使得左表中的所有記錄都顯示在結(jié)果集中。左外連接是對(duì)連接條件中左邊表不加限制，其基本語(yǔ)法格式如下：知識(shí)準(zhǔn)備連接查詢知識(shí)點(diǎn)4SELECT列名列表FROM表名1LEFT[OUTER]JOIN表名2ON表名1.列名=表名2.列名（2）右外連接（RIGHT[OUTER]JOIN）右外連接指定結(jié)果集中除了包括由內(nèi)連接返回的所有行之外，還包括右表中所有不滿足連接條件的行，并將結(jié)果集中左表的輸出列設(shè)置為NULL。因此，右外連接可以使得右表中的所有記錄都顯示在結(jié)果集中。右外連接是對(duì)連接條件中右邊表不加限制，其基本語(yǔ)法格式如下：知識(shí)準(zhǔn)備連接查詢知識(shí)點(diǎn)4注意：連接查詢，需要根據(jù)要解決的問(wèn)題進(jìn)行分析——結(jié)果來(lái)自哪幾個(gè)表（確定連接表）？表之間建立怎樣的連接（確定連接類型）？選取怎樣的數(shù)據(jù)？只有將這些問(wèn)題搞清楚，才可能寫出正確的語(yǔ)句。SELECT列名列表FROM表名1RIGHT[OUTER]JOIN表名2ON表名1.列名=表名2.列名在SQL語(yǔ)言中，一個(gè)SELECT…FROM…WHERE語(yǔ)句稱為一個(gè)查詢塊，將一個(gè)查詢塊嵌套在另一個(gè)查詢塊中的查詢稱為嵌套查詢。在嵌套查詢中，上層查詢塊稱為外層查詢或父查詢，下層查詢塊稱為內(nèi)層查詢或子查詢。SQL語(yǔ)言允許多層嵌套查詢，即一個(gè)子查詢中還可以嵌套其他子查詢。嵌套查詢一般按照由里向外的順序處理，即先處理最內(nèi)層的子查詢，然后一層一層向外處理，直到最外層查詢塊。需要注意以下幾點(diǎn)：

子查詢語(yǔ)句必須用圓括號(hào)括起來(lái)。

子查詢中不能使用ORDERBY子句，ORDERBY子句只能對(duì)最終查詢結(jié)果進(jìn)行排序。知識(shí)準(zhǔn)備嵌套查詢知識(shí)點(diǎn)5聯(lián)合查詢就是使用UNION關(guān)鍵字將多個(gè)查詢的結(jié)果集合并為單個(gè)結(jié)果集，其基本語(yǔ)法格式如下：知識(shí)準(zhǔn)備聯(lián)合查詢知識(shí)點(diǎn)6重要提示：在SELECT語(yǔ)句中，ALL是默認(rèn)選項(xiàng)；而在聯(lián)合查詢中，DISTINCT是默認(rèn)選項(xiàng)。select_statementUNION[ALL|DISTINCT]select_statement[UNION[ALL|DISTINCT]select_statement…]指定SELECT查詢語(yǔ)句。指定合并多個(gè)結(jié)果集并將其作為單個(gè)結(jié)果集返回。表示將所有行合并到結(jié)果集中，包括重復(fù)行。默認(rèn)選項(xiàng)，可以省略，表示去除查詢結(jié)果集中的重復(fù)行。使用聯(lián)合查詢時(shí)需要注意以下幾點(diǎn)：

所有SELECT查詢語(yǔ)句中的列數(shù)必須相同，并且對(duì)應(yīng)列的數(shù)據(jù)類型必須兼容。

結(jié)果集的列標(biāo)題為第一個(gè)SELECT查詢語(yǔ)句的列標(biāo)題。知識(shí)準(zhǔn)備聯(lián)合查詢知識(shí)點(diǎn)6任務(wù)實(shí)施1數(shù)據(jù)匯總2分組統(tǒng)計(jì)3連接查詢4嵌套查詢5聯(lián)合查詢表中數(shù)據(jù)經(jīng)常需要進(jìn)行統(tǒng)計(jì)計(jì)算，如統(tǒng)計(jì)個(gè)數(shù)、計(jì)算總和、求平均值等。這些統(tǒng)計(jì)可使用聚合函數(shù)來(lái)實(shí)現(xiàn)。【例1】統(tǒng)計(jì)專家的總?cè)藬?shù)。任務(wù)實(shí)施數(shù)據(jù)匯總1【例2】統(tǒng)計(jì)被抽取專家的人數(shù)。分析：專家被抽取一次，在抽取專家列表textractionexpert中就有一條相應(yīng)的記錄。一般情況下，一個(gè)專家會(huì)多次被抽取到。為了避免重復(fù)計(jì)算專家的人數(shù)，需要在COUNT()函數(shù)中加入DISTINCT。任務(wù)實(shí)施數(shù)據(jù)匯總1【例3】查詢專家的平均年齡、最大年齡和最小年齡。任務(wù)實(shí)施數(shù)據(jù)匯總11.按單列分組【例4】統(tǒng)計(jì)男、女專家人數(shù)。分析：本例要求按性別統(tǒng)計(jì)專家人數(shù)，所以需要對(duì)專家按性別進(jìn)行分組，然后計(jì)算各組的人數(shù)。重要提示：如果使用GROUPBY子句，則SELECT語(yǔ)句的目標(biāo)列表達(dá)式中必須是GROUPBY子句中的分組列，或者聚合函數(shù)。任務(wù)實(shí)施分組統(tǒng)計(jì)2【例5】統(tǒng)計(jì)不同學(xué)歷的專家人數(shù)，并按照人數(shù)降序排序。分析：本例要求按學(xué)歷統(tǒng)計(jì)專家人數(shù)，所以需要對(duì)專家按學(xué)歷進(jìn)行分組，然后計(jì)算各組的人數(shù)，最后再按人數(shù)降序排序。任務(wù)實(shí)施分組統(tǒng)計(jì)22.按多列分組【例6】統(tǒng)計(jì)各學(xué)歷男、女專家人數(shù)。分析：本例要求統(tǒng)計(jì)各學(xué)歷男、女專家人數(shù)，需要對(duì)專家先按照學(xué)歷sDiploma進(jìn)行分組，如果學(xué)歷sDiploma相同，但性別sSex不同，再按照性別sSex進(jìn)行分組。任務(wù)實(shí)施分組統(tǒng)計(jì)2【例7】查詢各技術(shù)職稱的男、女專家人數(shù)，并顯示匯總行。分析：GROUPBY子句中使用WITHROLLUP后，會(huì)顯示各分組的匯總行，以及所有分組的整體匯總行。任務(wù)實(shí)施分組統(tǒng)計(jì)23.過(guò)濾分組【例8】查詢女專家人數(shù)不少于5人的出生年份。分析：本例要求查詢女專家人數(shù)不少于5人的出生年份，首先要知道各出生年份的女專家人數(shù)，所以先按出生年份YEAR(dBirthDate)對(duì)專家信息表texpertinfo進(jìn)行分組。分組后統(tǒng)計(jì)人數(shù)，再篩選出滿足人數(shù)不少于5人的出生年份。任務(wù)實(shí)施分組統(tǒng)計(jì)21.內(nèi)連接【例9】查詢啟用狀態(tài)的用戶的用戶編號(hào)iUserID、姓名sUserName和所在部門的部門名稱sDeptName。

分析：用戶編號(hào)、姓名和用戶狀態(tài)存放在用戶信息表tuserinfo中，用戶所在部門的部門名稱存放在部門信息表tdeptinfo中，所以本例查詢涉及tuserinfo和tdeptinfo兩個(gè)表。這兩個(gè)表之間的連接是通過(guò)部門編號(hào)iDeptID來(lái)實(shí)現(xiàn)的。任務(wù)實(shí)施連接查詢3說(shuō)明：本例查詢語(yǔ)句中t1為用戶信息表tuserinfo的別名，t2為部門信息表tdeptinfo的別名。iDeptID在兩個(gè)表中重名，需要加上表別名前綴。iUserID、sUserName、sDeptName和iUserStatus在兩個(gè)表中不重名，可以不加前綴。2.外連接（1）左外連接【例10】查詢技術(shù)職稱sTechnicalTitle為“助教”的專家的被抽取情況。要求顯示專家編碼sExpertCode、專家姓名sName和項(xiàng)目編號(hào)iProjectID。分析：抽取專家列表textractionexpert中存放被抽取的專家編號(hào)和項(xiàng)目編號(hào)，專家編碼、專家姓名、技術(shù)職稱存放在專家信息表texpertinfo中，所以本例查詢涉及textractionexpert和texpertinfo兩個(gè)表。這兩個(gè)表之間的連接是通過(guò)專家編號(hào)iExpertID來(lái)實(shí)現(xiàn)的。由于不是所有專家都被抽取過(guò)，為了讓沒(méi)有被抽取的專家的信息也顯示在結(jié)果集中，本例采用左外連接方式進(jìn)行查詢。任務(wù)實(shí)施連接查詢3深度理解：本例查詢結(jié)果列出了左表（texpertinfo）中所有滿足條件（sTechnicalTitle='助教'）的記錄。如果左表（texpertinfo）中的記錄不滿足連接條件，將查詢結(jié)果中來(lái)自右表（textractionexpert）的項(xiàng)目編號(hào)iProjectID列設(shè)置為NULL。任務(wù)實(shí)施連接查詢3（2）

右外部連接右外連接和左外連接是對(duì)稱的，因此“表名1LEFTJOIN表名2”和“表名2RIGHTJOIN表名1”的結(jié)果相同?！纠?1】以右外連接方式查詢技術(shù)職稱sTechnicalTitle為“助教”的專家的被抽取情況。任務(wù)實(shí)施連接查詢3多學(xué)一招：在連接查詢時(shí)，若數(shù)據(jù)表連接的字段同名，則連接時(shí)的匹配條件可以使用using代替on。基本語(yǔ)法格式如下：任務(wù)實(shí)施連接查詢3SELECT列名列表FROM表名1[inner|left|right]join

表名2using(列名)【例12】使用USING關(guān)鍵字實(shí)現(xiàn)查詢啟用狀態(tài)的用戶的用戶編號(hào)iUserID、姓名sUserName和所在部門的部門名稱sDeptName。1.使用比較運(yùn)算符連接的子查詢使用比較運(yùn)算符連接子查詢，就是將列或列表達(dá)式的值與子查詢的結(jié)果進(jìn)行比較，如果比較結(jié)果為真則返回該行，否則無(wú)返回值。子查詢的結(jié)果集只能是單列、單值，否則會(huì)報(bào)錯(cuò)?！纠?3】使用子查詢列出所在部門為“信息工程系”的用戶的姓名和手機(jī)號(hào)碼。分析：用戶信息表tuserinfo中只保存了部門編號(hào)iDeptID，部門名稱保存在部門信息表tdeptinfo中，要查詢所在部門為“信息工程系”的用戶信息，首先要查詢部門“信息工程系”的部門編號(hào)，再把部門編號(hào)作為條件，在用戶信息表中查詢用戶的姓名和手機(jī)號(hào)碼。任務(wù)實(shí)施嵌套查詢4下面先分步完成查詢，然后再構(gòu)造嵌套查詢。步驟1：在部門信息表tdeptinfo中查詢部門“信息工程系”的部門編號(hào)iDeptID。具體SQL語(yǔ)句與執(zhí)行結(jié)果如下：從以上執(zhí)行結(jié)果可知“信息工程系”的部門編號(hào)為“1”。任務(wù)實(shí)施嵌套查詢4步驟2：在用戶信息表tuserinfo中查詢部門編號(hào)為“1”的用戶的姓名和手機(jī)號(hào)碼。具體SQL語(yǔ)句與執(zhí)行結(jié)果如下：任務(wù)實(shí)施嵌套查詢4步驟3：將步驟1嵌入到步驟2的查詢條件中，構(gòu)造嵌套查詢。具體SQL語(yǔ)句與執(zhí)行結(jié)果如下：任務(wù)實(shí)施嵌套查詢4【例14】使用子查詢列出年齡大于平均年齡的專家的專家編碼sExpertCode、專家姓名sName和聯(lián)系電話sLinkTel。分析：要查詢年齡大于平均年齡的專家的信息，首先要查詢專家的平均年齡，然后將平均年齡作為條件再查詢出專家的編碼、姓名和聯(lián)系電話。任務(wù)實(shí)施嵌套查詢42.使用IN連接的子查詢對(duì)于結(jié)果集為單列、單值的子查詢，可以使用比較運(yùn)算符進(jìn)行連接。但是對(duì)結(jié)果集為單列、多值的子查詢，則不能使用比較運(yùn)算符，可以使用IN或NOTIN進(jìn)行連接。IN用于子查詢，用來(lái)確定指定的值是否與子查詢結(jié)果集中的值相匹配。任務(wù)實(shí)施嵌套查詢4

【例15】使用子查詢列出2號(hào)項(xiàng)目抽取到的專家的編碼sExpertCode和姓名sName。

分析：可以分兩步來(lái)構(gòu)造子查詢。第一步：從抽取專家列表textractionexpert中查詢出2號(hào)項(xiàng)目抽取到的專家編號(hào)。第二步：從專家信息表texpertinfo中查詢抽取到的專家信息。說(shuō)明：本例執(zhí)行的順序是：首先執(zhí)行圓括號(hào)里的子查詢，返回的結(jié)果集是2號(hào)項(xiàng)目抽取到的專家編號(hào)的集合；然后對(duì)專家信息表texpertinfo從第一行起逐行掃描，每一行的專家編號(hào)列iExpertID都與集合中的值進(jìn)行比較，判斷是否屬于這個(gè)集合，如果是就返回該行，否則無(wú)返回值。任務(wù)實(shí)施嵌套查詢43.使用ALL或ANY連接的子查詢使用比較運(yùn)算符連接子查詢時(shí)，通常要用到操作符ALL、ANY、SOME。其基本語(yǔ)法格式如下：任務(wù)實(shí)施嵌套查詢4expression{>|>=|=|<|<=|<>|!=|<=>}{ALL|ANY|SOME}(subquery)指定任何有效的表達(dá)式。指定返回單列結(jié)果集的子查詢。指定expression需要與子查詢結(jié)果集中的每個(gè)值進(jìn)行比較，當(dāng)expression與每個(gè)值都滿足比較條件時(shí)，才返回TRUE。指定expression只要與子查詢結(jié)果集中的某個(gè)值滿足比較條件時(shí)，就返回TRUE。和ANY是等效的。【例16】使用子查詢列出比博士研究生專家年齡都小的專家的編碼sExpertCode和姓名sName。任務(wù)實(shí)施嵌套查詢4也可以先使用聚合函數(shù)MIN()查詢博士研究生專家的最小年齡，然后將最小年齡作為條件再查詢出專家的編碼和姓名。說(shuō)明：用聚合函數(shù)實(shí)現(xiàn)子查詢通常比用ALL或ANY的查詢效率高。任務(wù)實(shí)施嵌套查詢44.使用EXISTS連接的子查詢EXISTS和NOTEXISTS關(guān)鍵字用來(lái)確定數(shù)據(jù)是否在子查詢結(jié)