數(shù)據(jù)庫應(yīng)用技術(shù)（MySQL）（第二版） 課件 模塊三 使用數(shù)據(jù)庫

模塊三

使用數(shù)據(jù)庫在開發(fā)使用評(píng)標(biāo)專家?guī)旃芾硐到y(tǒng)之前，項(xiàng)目信息、專家信息等數(shù)據(jù)是以Excel表格的形式保存的，這些數(shù)據(jù)不必一條一條地輸入到數(shù)據(jù)庫beems中。首先可以使用數(shù)據(jù)導(dǎo)入功能將這些基礎(chǔ)數(shù)據(jù)從Excel文件中導(dǎo)入到數(shù)據(jù)庫beems的相應(yīng)數(shù)據(jù)表中，然后完成數(shù)據(jù)添加、修改和刪除操作；接著可以利用SQL語句按照用戶要求從數(shù)據(jù)庫中檢索特定信息，還可以為查詢結(jié)果排序、分組和統(tǒng)計(jì)運(yùn)算。為了更有效地使用數(shù)據(jù)庫，還需要?jiǎng)?chuàng)建視圖、索引、存儲(chǔ)過程和觸發(fā)器等數(shù)據(jù)庫對(duì)象。本模塊主要包括以下5個(gè)學(xué)習(xí)任務(wù)：任務(wù)1數(shù)據(jù)操作任務(wù)2數(shù)據(jù)簡單查詢?nèi)蝿?wù)3數(shù)據(jù)高級(jí)查詢?nèi)蝿?wù)4視圖及索引的應(yīng)用任務(wù)5存儲(chǔ)過程及觸發(fā)器的應(yīng)用評(píng)標(biāo)專家?guī)旃芾硐到y(tǒng)的數(shù)據(jù)庫和數(shù)據(jù)表已經(jīng)創(chuàng)建好了，但是數(shù)據(jù)表只是一個(gè)空表，表中還沒有數(shù)據(jù)。本任務(wù)主要學(xué)習(xí)如何將基礎(chǔ)數(shù)據(jù)導(dǎo)入數(shù)據(jù)表中，并學(xué)習(xí)如何使用SQL語句添加、修改和刪除表中的數(shù)據(jù)。任務(wù)描述任務(wù)1數(shù)據(jù)操作學(xué)會(huì)數(shù)據(jù)導(dǎo)入與導(dǎo)出方法12掌握INSERT語句的語法及使用方法3掌握UPDATE語句的語法及使用方法掌握DELETE語句的語法及使用方法4任務(wù)目標(biāo)學(xué)會(huì)掌握掌握掌握INSERT語句用于將一行或多行數(shù)據(jù)添加到表中，有3種語法形式。1.INSERT…VALUES語句使用INSERT…VALUES語句添加數(shù)據(jù)的基本語法格式如下：INSERT[INTO]table_name[(column_list)]{VALUES|VALUE}({expr|DEFAULT},…),(…),…INSERT語句知識(shí)點(diǎn)1知識(shí)準(zhǔn)備可選的關(guān)鍵字指定要添加數(shù)據(jù)的表名可以任選一種，通常使用VALUES指定列值為該列的默認(rèn)值指定要添加數(shù)據(jù)的列名列表，必須用圓括號(hào)括起來，列名之間用逗號(hào)分隔。column_list是可選項(xiàng)，如果省略，表示向表中的所有列按順序添加數(shù)據(jù)。指定列值是常量、變量或表達(dá)式，也可以是空值NULL2.INSERT…SET語句INSERT…SET語句可以為表中指定列添加數(shù)據(jù)，其基本語法格式如下：INSERT[INTO]table_nameSETcolumn_name={expr|DEFAULT},…INSERT語句知識(shí)點(diǎn)1知識(shí)準(zhǔn)備指定要添加數(shù)據(jù)的表名指定要添加數(shù)據(jù)的列名3.INSERT…SELECT語句INSERT…SELECT語句可以將SELECT語句查詢出的結(jié)果集插入到INSERT后指定的表中，其基本語法格式如下：說明：SELECT語句查詢出的結(jié)果集的列數(shù)、列的數(shù)據(jù)類型必須與INSERT后指定的表一致。INSERT[INTO]table_name[(column_list)]SELECT…INSERT語句知識(shí)點(diǎn)1知識(shí)準(zhǔn)備UPDATE語句用于修改更新表中的數(shù)據(jù)，其基本語法格式如下：UPDATE語句知識(shí)點(diǎn)2知識(shí)準(zhǔn)備UPDATEtable_nameSETcolumn_name1={expr1|DEFAULT}[,column_name2={expr2|DEFAULT}]…[WHEREwhere_condition]指定要修改數(shù)據(jù)的表的名稱指定表中要修改的列名及其列值，其中列值可以是表達(dá)式，也可以是該列對(duì)應(yīng)的默認(rèn)值。指定要修改的列名指定列值是常量、變量或表達(dá)式指定列值是該列的默認(rèn)值指定條件來限定表中要修改的行。如果沒有WHERE子句，則會(huì)修改表中所有行的指定列。DELETE語句用于從表中刪除一行或多行數(shù)據(jù)，其基本語法格式如下：DELETE語句知識(shí)點(diǎn)3知識(shí)準(zhǔn)備DELETEFROMtable_name[WHEREwhere_condition]指定要?jiǎng)h除數(shù)據(jù)的表的名稱指定條件來限制表中要?jiǎng)h除的行。如果沒有WHERE子句，則刪除表中的所有行。注意：DELETE語句是刪除表中的數(shù)據(jù)，而不是刪除表的結(jié)構(gòu)。任務(wù)實(shí)施1數(shù)據(jù)導(dǎo)入/導(dǎo)出2添加數(shù)據(jù)3修改數(shù)據(jù)4刪除數(shù)據(jù)【例1】將beems.xlsx文件中的數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫beems的相應(yīng)數(shù)據(jù)表中。任務(wù)實(shí)施數(shù)據(jù)導(dǎo)入/導(dǎo)出1注意：為了確保成功向數(shù)據(jù)庫beems中導(dǎo)入數(shù)據(jù)，在導(dǎo)入數(shù)據(jù)前，先將數(shù)據(jù)表中設(shè)置的外鍵約束刪除，數(shù)據(jù)成功導(dǎo)入后，再重新進(jìn)行設(shè)置。1.使用INSERT…VALUES語句添加數(shù)據(jù)INSERT…VALUES語句的語法比較復(fù)雜，可以歸納如下：功能：將“VALUES”后面的值插入到表中新記錄的對(duì)應(yīng)列中。其中，“值1”插入到新記錄的“列名1”，“值2”插入到“列名2”中……“VALUES”后面值的順序要與表名后面列的順序一一對(duì)應(yīng)。任務(wù)實(shí)施添加數(shù)據(jù)2INSERT[INTO]表名[(列名1,列名2,…)]VALUES(值1,值2,…)【例2】在部門信息表tdeptinfo中，新增一個(gè)部門，已知部門名稱sDeptName為“工會(huì)”。任務(wù)實(shí)施添加數(shù)據(jù)2mysql>USEbeems;Databasechangedmysql>INSERTtdeptinfo(sDeptName)VALUES('工會(huì)');QueryOK,1rowaffected(0.00sec)說明：本例省略了關(guān)鍵字INTO。

為部分列賦值，不能省略表名后的列名。列值“工會(huì)”為字符型，需要用引號(hào)括起來（MySQL中可以用單引號(hào)，也可以用雙引號(hào)，建議使用單引號(hào)）?！纠?】新增一個(gè)部門，部門名稱sDeptName為“團(tuán)委”，部門主管sDeptManager為“趙美麗”，部門狀態(tài)iStatus取默認(rèn)值。任務(wù)實(shí)施添加數(shù)據(jù)2mysql>USEbeems;Databasechangedmysql>INSERTINTOtdeptinfo->VALUES(NULL,'團(tuán)委','趙美麗',DEFAULT);QueryOK,1rowaffected(0.01sec)INSERTINTOtdeptinfo(iDeptID,sDeptName,sDeptManager,iStatus)VALUES(NULL,'團(tuán)委','趙美麗',DEFAULT);等同于iDeptID列是自動(dòng)遞增列，可以設(shè)置為NULL，也可以設(shè)置為數(shù)字0，推薦使用NULL，該列的值是由系統(tǒng)自動(dòng)生成。iStatus列設(shè)置了默認(rèn)值為“1”，所以可以用“DEFAULT”表示該列取默認(rèn)值。重要提示：本例向tdeptinfo表的所有列按順序添加數(shù)據(jù)，因此可以省略表名后的列名列表，此時(shí)要求給出的值的順序與數(shù)據(jù)表的結(jié)構(gòu)相對(duì)應(yīng)。盡管這種方式的語法比較簡單，但它高度依賴于表中所有列的定義順序，在表的結(jié)構(gòu)發(fā)生改變時(shí)，INSERT語句就會(huì)出錯(cuò)，所以應(yīng)盡量在表名后給出列名列表，這樣即使表的結(jié)構(gòu)發(fā)生改變，INSERT語句仍能正確執(zhí)行。【例4】在部門信息表tdeptinfo中，添加以下部門：馬克思主義學(xué)院、執(zhí)紀(jì)審查處、海外教育學(xué)院。任務(wù)實(shí)施添加數(shù)據(jù)2mysql>INSERTINTOtdeptinfo(sDeptName)->VALUES('馬克思主義學(xué)院'),->('執(zhí)紀(jì)審查處'),->('海外教育學(xué)院');QueryOK,3rowsaffected(0.01sec)Records:3Duplicates:0Warnings:02.使用INSERT…SET語句添加數(shù)據(jù)【例5】新增一個(gè)部門，部門名稱sDeptName為“創(chuàng)新創(chuàng)業(yè)學(xué)院”，部門主管sDeptManager為“吳偉”。任務(wù)實(shí)施添加數(shù)據(jù)2mysql>INSERTINTOtdeptinfo->SETsDeptName='創(chuàng)新創(chuàng)業(yè)學(xué)院',sDeptManager='吳偉';QueryOK,1rowaffected(0.01sec)3.使用INSERT…SELECT語句添加數(shù)據(jù)【例6】將tprojectinfo表中項(xiàng)目狀態(tài)iProjectStatus為2的項(xiàng)目信息插入到新建的表“tprojecttemp”中。任務(wù)實(shí)施添加數(shù)據(jù)2mysql>CREATETABLEtprojecttemp->(sProjectNamevarchar(100),->iProjectStatustinyint,->sLeaderUservarchar(50)->);QueryOK,0rowsaffected(0.03sec)

mysql>INSERTINTOtprojecttemp->SELECTsProjectName,iProjectStatus,sLeaderUser->FROMtprojectinfo->WHEREiProjectStatus=2;QueryOK,2rowsaffected(0.01sec)Records:2Duplicates:0Warnings:0可以將UPDATE語句歸納如下：功能：對(duì)表中滿足WHERE條件的數(shù)據(jù)行進(jìn)行修改，由SET子句將表達(dá)式的值替換相應(yīng)列的值。如果沒有WHERE子句，則修改表中所有行的指定列。任務(wù)實(shí)施修改數(shù)據(jù)3UPDATE表名SET列名1=表達(dá)式1[,列名2=表達(dá)式2]…[WHERE條件]1.更新一列【例7】在專家信息表texpertinfo中，將專家編碼sExpertCode為“252081”專家的出生日期dBirthDate改成當(dāng)前系統(tǒng)日期。任務(wù)實(shí)施修改數(shù)據(jù)3mysql>UPDATEtexpertinfoSETdBirthDate=CURDATE()->WHEREsExpertCode='252081';QueryOK,1rowaffected(0.01sec)Rowsmatched:1Changed:1Warnings:0【例8】將專家編碼sExpertCode為“252081”專家的專家星級(jí)iExpertLevel加1級(jí)。任務(wù)實(shí)施修改數(shù)據(jù)3mysql>UPDATEtexpertinfoSETiExpertLevel=iExpertLevel+1->WHEREsExpertCode='252081';QueryOK,1rowaffected(0.01sec)Rowsmatched:1Changed:1Warnings:02.更新多列更新多列時(shí)，各列之間要用逗號(hào)分隔?！纠?】將專家編碼sExpertCode為“252081”專家的性別sSex改成“女”，學(xué)歷sDiploma改成“碩士研究生”。任務(wù)實(shí)施修改數(shù)據(jù)3mysql>UPDATEtexpertinfoSETsSex='女',sDiploma='碩士研究生'->WHEREsExpertCode='252081';QueryOK,1rowaffected(0.01sec)Rowsmatched:1Changed:1Warnings:0【例10】刪除專家編碼sExpertCode為“252081”的專家記錄。任務(wù)實(shí)施刪除數(shù)據(jù)4mysql>DELETEFROMtexpertinfo->WHEREsExpertCode='252081';QueryOK,1rowaffected(0.01sec)經(jīng)驗(yàn)分享本任務(wù)主要學(xué)習(xí)如何管理數(shù)據(jù)表中的數(shù)據(jù)，包括導(dǎo)入、添加、修改和刪除數(shù)據(jù)。使用INSERT語句添加數(shù)據(jù)時(shí)，要符合數(shù)據(jù)類型、完整性約束、列與值對(duì)應(yīng)關(guān)系的要求。使用UPDATE和DELETE語句修改和刪除數(shù)據(jù)的操作是不可逆的，應(yīng)該謹(jǐn)慎使用。模塊三

使用數(shù)據(jù)庫數(shù)據(jù)表中存儲(chǔ)了數(shù)據(jù)后，根據(jù)系統(tǒng)功能需求按照一定條件進(jìn)行簡單的數(shù)據(jù)查詢是最基本、最重要的操作，如查詢專家信息、查詢項(xiàng)目信息等。本任務(wù)主要介紹評(píng)標(biāo)專家?guī)旃芾硐到y(tǒng)數(shù)據(jù)簡單查詢功能。任務(wù)描述任務(wù)2數(shù)據(jù)簡單查詢掌握SELECT基本語法12掌握使用SELECT語句進(jìn)行簡單查詢的方法3掌握使用WHERE子句進(jìn)行條件查詢的方法學(xué)會(huì)使用LIMIT子句限制返回行數(shù)5任務(wù)目標(biāo)掌握掌握熟練運(yùn)用掌握學(xué)會(huì)4熟練運(yùn)用ORDERBY子句實(shí)現(xiàn)數(shù)據(jù)排序

SELECT語句的基本語法格式：SELECT[ALL|DISTINCT|DISTINCTROW]select_expr[,select_expr…]FROMtable_references[WHEREwhere_condition][GROUPBY{column_name|expr|position},…[WITHROLLUP]][HAVINGwhere_condition][ORDERBY{column_name|expr|position}[ASC|DESC],…][LIMIT{[offset,]row_count|row_countOFFSEToffset}]SELECT語句知識(shí)點(diǎn)1知識(shí)準(zhǔn)備用于指定查詢返回的列。用于指定數(shù)據(jù)的來源。用于指定數(shù)據(jù)的查詢條件。用于對(duì)查詢結(jié)果進(jìn)行分組。用于指定組的選擇條件。用于對(duì)查詢結(jié)果進(jìn)行排序。用于限制返回的行數(shù)。是可選項(xiàng)，指定結(jié)果集中是否包含重復(fù)行。ALL是默認(rèn)值，表示包含重復(fù)行。DISTINCT與DISTINCTROW為同義詞，表示去掉結(jié)果集中的重復(fù)行。select_expr可以是表中的列名，也可以是根據(jù)表中列計(jì)算的表達(dá)式。SELECT語句知識(shí)點(diǎn)1知識(shí)準(zhǔn)備

SELECT語句的完整語法比較復(fù)雜，其主要子句可歸納如下：SELECT[ALL|DISTINCT|DISTINCTROW]目標(biāo)列表達(dá)式FROM表名[WHERE條件表達(dá)式][GROUPBY分組依據(jù)][HAVING條件表達(dá)式][ORDERBY排序依據(jù)[ASC|DESC]][LIMIT行數(shù)]提示：SELECT語句中，子句的順序非常重要，可以省略可選子句，但這些子句必須按照語法格式規(guī)定的順序來使用。運(yùn)算符分類運(yùn)算符作用比較運(yùn)算符>、>=、=、<、<=、<>、!=、<=>比較大小邏輯運(yùn)算符AND或&&、OR或||、NOT或！用于多條件的邏輯連接范圍運(yùn)算符BETWEEN…AND、NOTBETWEEN…AND判斷列值是否在指定范圍內(nèi)列表運(yùn)算符IN、NOTIN判斷列值是否為列表中的值字符匹配符LIKE、NOTLIKE判斷列值是否與指定的字符匹配格式相符空值判斷符ISNULL、ISNOTNULL判斷列值是否為空值WHERE子句知識(shí)點(diǎn)2WHERE子句用于指定查詢條件，過濾掉不符合條件的數(shù)據(jù)行。WHERE子句必須緊跟在FROM子句的后面。查詢條件中常用運(yùn)算符見表1。知識(shí)準(zhǔn)備表1查詢條件中常用運(yùn)算符LIMIT子句知識(shí)點(diǎn)3知識(shí)準(zhǔn)備查詢結(jié)果集的行數(shù)很多時(shí)，可以使用LIMIT子句來限制返回的行數(shù)。LIMIT子句的基本語法格式如下：例如，“LIMIT3”表示返回前3行記錄，“LIMIT2,6”表示返回從第3行開始的6行記錄?！癓IMIT2,6”等價(jià)于“LIMIT6OFFSET2”。提示：查詢結(jié)果集中第一行記錄的偏移量為0，而不是1。LIMIT{[offset,]row_count|row_countOFFSEToffset}指定返回的行數(shù)，必須是非負(fù)的整數(shù)常量。指定從offset+1行開始，返回row_count行。為可選項(xiàng)，默認(rèn)為“0”，指定要返回的第一行在查詢結(jié)果集中的偏移量，必須是非負(fù)的整數(shù)常量。ORDERBY子句知識(shí)點(diǎn)4知識(shí)準(zhǔn)備可以使用ORDERBY子句將查詢結(jié)果按一列或多列進(jìn)行排序。其基本語法格式如下：ORDERBY{column_name|expr|position}[ASC|DESC],…指定用于排序的列。指定用于排序的表達(dá)式。指定用于排序的列在查詢結(jié)果集中的位置，通常是一個(gè)正整數(shù)。指定排序方式，“ASC”表示按升序排列，“DESC”表示按降序排列。默認(rèn)值為“ASC”。ORDERBY子句知識(shí)點(diǎn)4知識(shí)準(zhǔn)備使用ORDERBY子句時(shí)需要注意以下幾點(diǎn)：

ORDERBY子句中可以包含子查詢。

MySQL中排序時(shí)空值（NULL）被認(rèn)為是最小值。

如果不使用ORDERBY子句，查詢結(jié)果集中數(shù)據(jù)行的順序是不可預(yù)料的。任務(wù)實(shí)施1使用SELECT語句選擇列2使用WHERE子句過濾數(shù)據(jù)3使用LIMIT子句限制返回行數(shù)4使用ORDERBY子句排序任務(wù)實(shí)施使用SELECT語句選擇列11.查詢部分列在很多情況下，用戶只對(duì)數(shù)據(jù)表中的部分列感興趣，通過SELECT語句可以“過濾”掉某些列，只顯示用戶需要的列。【例1】在部門信息表tdeptinfo中，查詢部門名稱sDeptName和部門主管sDeptManager。說明：SELECT后列名的先后順序可以與數(shù)據(jù)表中列的順序不一致，查詢結(jié)果集中列的順序與SELECT后列名的順序相同。任務(wù)實(shí)施使用SELECT語句選擇列2.查詢?nèi)苛袑?shù)據(jù)表中所有列都顯示出來有兩種方法：一種方法是在SELECT后面列出所有列名，當(dāng)列較多時(shí)，這種方法比較繁瑣；另一種方法是使用通配符“*”，采用這種方法列出所有列，查詢結(jié)果集中列的順序與數(shù)據(jù)表中列的順序完全一致?！纠?】在部門信息表tdeptinfo中，查詢部門信息。注意：一般而言，除非確實(shí)需要數(shù)據(jù)表中所有的列，否則最好不要使用通配符“*”。雖然使用通配符能省事，不用明確列出所需列，但檢索不需要的列通常會(huì)降低檢索和應(yīng)用程序的性能。1使用SELECT語句選擇列3.更改列標(biāo)題數(shù)據(jù)表中列的名稱可能只是一個(gè)簡單的代碼，如部門信息表tdeptinfo中的部門主管采用sDeptManager作為列名，有時(shí)用戶無法理解這類列名的具體含義。為了增加查詢結(jié)果的可讀性，可以通過指定列別名的方式改變查詢結(jié)果的列標(biāo)題。指定列別名的語法格式如下：列別名改變的只是查詢結(jié)果的列標(biāo)題，并沒有改變數(shù)據(jù)表中的列名。colunm_name[AS]column_alias指定列名。指定列別名。1任務(wù)實(shí)施任務(wù)實(shí)施使用SELECT語句選擇列【例3】在部門信息表tdeptinfo中，查詢部門名稱sDeptName和部門主管sDeptManager，并要求查詢結(jié)果集中列標(biāo)題顯示為“部門名”和“部門主管”。1使用SELECT語句選擇列4.查詢經(jīng)過計(jì)算的列值SELECT子句中的select_expr不僅可以是數(shù)據(jù)表中的列，也可以是表達(dá)式，包括算術(shù)表達(dá)式、字符串常量或函數(shù)等。【例4】

在專家信息表texpertinfo中，查詢專家姓名sName、性別sSex和年齡。分析：專家信息表texpertinfo中存儲(chǔ)的是專家的出生日期dBirthDate，專家的年齡可以通過計(jì)算得到，計(jì)算專家的年齡用到了兩個(gè)函數(shù)：YEAR(date)：用于得到date日期中的年份。CURDATE()：用于得到當(dāng)前系統(tǒng)日期。使用表達(dá)式Y(jié)EAR(CURDATE())-YEAR(dBirthDate)，計(jì)算當(dāng)前系統(tǒng)日期中的年份減去專家出生日期中的年份，結(jié)果就是專家的年齡。1任務(wù)實(shí)施使用SELECT語句選擇列5.去掉重復(fù)行【例5】在專家信息表texpertinfo中，查詢專家的學(xué)歷sDiploma。1執(zhí)行結(jié)果包含重復(fù)的學(xué)歷，如果想去掉查詢結(jié)果中的重復(fù)行，必須添加DISTINCT選項(xiàng)。任務(wù)實(shí)施任務(wù)實(shí)施使用SELECT語句選擇列說明：在一個(gè)SELECT語句中，DISTINCT只能出現(xiàn)一次，并且DISTINCT必須在所有的列名之前，否則會(huì)發(fā)生語法錯(cuò)誤。對(duì)于DISTINCT選項(xiàng)來說，空值（NULL）被認(rèn)為是相等的，不論有多少個(gè)空值（NULL），結(jié)果只返回一個(gè)。1執(zhí)行結(jié)果只有3行記錄，不再包含重復(fù)的行。使用SELECT語句選擇列6.替換查詢結(jié)果中的數(shù)據(jù)查詢時(shí)，如果希望得到某些列的查詢分析結(jié)果，而不是原始數(shù)據(jù)，例如，專家信息表中專家狀態(tài)iExpertStatus列中保存的是“1”或“0”，如果想知道專家狀態(tài)是“啟用”還是“停用”，就可以在SELECT語句中替換iExpertStatus列中的數(shù)據(jù)。1任務(wù)實(shí)施使用SELECT語句選擇列替換查詢結(jié)果中的數(shù)據(jù)，可以使用CASE表達(dá)式，其語法格式如下：執(zhí)行過程：逐條執(zhí)行WHEN語句，如果search_condition條件成立，則執(zhí)行相應(yīng)的THEN后的statement_list語句。如果條件都不成立，則執(zhí)行ELSE后的statement_list語句。CASEWHENsearch_conditionTHENstatement_list[WHENsearch_conditionTHENstatement_list]…[ELSEstatement_list]END[AS]column_alias指定判斷條件。指定不同條件的執(zhí)行語句。指定列別名。1任務(wù)實(shí)施任務(wù)實(shí)施使用SELECT語句選擇列【例6】在專家信息表texpertinfo中，查詢專家姓名sName和專家狀態(tài)iExpertStatus，要求判斷查詢結(jié)果中專家狀態(tài)的值，如果專家狀態(tài)的值為“1”，則替換為“啟用”，否則替換為“停用”，同時(shí)結(jié)果集中列標(biāo)題顯示為“姓名”和“專家狀態(tài)”。1使用WHERE子句過濾數(shù)據(jù)1.使用比較運(yùn)算符構(gòu)成查詢條件【例7】在專家信息表texpertinfo中，查詢專家劉貂蟬的專家編碼sExpertCode、姓名sName和技術(shù)職稱sTechnicalTitle。2任務(wù)實(shí)施使用WHERE子句過濾數(shù)據(jù)【例8】在專家信息表texpertinfo中，查詢年齡超過58歲的專家的專家編碼sExpertCode和姓名sName。2任務(wù)實(shí)施使用WHERE子句過濾數(shù)據(jù)2.使用邏輯運(yùn)算符構(gòu)成查詢條件WHERE子句中可以使用邏輯運(yùn)算符把多個(gè)查詢條件連接起來，從而實(shí)現(xiàn)比較復(fù)雜的選擇查詢?！纠?】在專家信息表texpertinfo中，查詢80后女專家的姓名sName和年齡。2任務(wù)實(shí)施使用WHERE子句過濾數(shù)據(jù)【例10】在專家信息表texpertinfo中，查詢學(xué)歷sDiploma為“碩士研究生”或“博士研究生”的專家的專家編碼sExpertCode和姓名sName。注意：邏輯運(yùn)算符AND和OR可用來連接多個(gè)查詢條件。如果這兩個(gè)運(yùn)算符同時(shí)出現(xiàn)在一個(gè)WHERE子句中，AND的優(yōu)先級(jí)高于OR，可以用圓括號(hào)改變優(yōu)先級(jí)。2任務(wù)實(shí)施使用WHERE子句過濾數(shù)據(jù)3.使用范圍運(yùn)算符構(gòu)成查詢條件如果過濾數(shù)據(jù)使其列值在某個(gè)范圍內(nèi)（包括邊界），就可以使用BETWEEN…AND；如果過濾數(shù)據(jù)使其列值不在某個(gè)范圍內(nèi)，可以在BETWEEN前面加NOT。使用范圍運(yùn)算符構(gòu)成查詢條件的一般形式如下：[NOT]BETWEENexpression1ANDexpression2說明：表達(dá)式“expression1”的值不能大于“expression2”的值。2任務(wù)實(shí)施使用WHERE子句過濾數(shù)據(jù)【例11】在專家信息表texpertinfo中，查詢年齡在30~40歲之間的專家的專家編碼sExpertCode和姓名sName。2任務(wù)實(shí)施使用WHERE子句過濾數(shù)據(jù)4.使用列表運(yùn)算符構(gòu)成查詢條件如果過濾數(shù)據(jù)使其列值在某個(gè)集合內(nèi)，就可以使用IN；如果過濾數(shù)據(jù)使其列值不在某個(gè)集合中，可在IN前面加NOT。使用列表運(yùn)算符構(gòu)成查詢條件的一般形式如下：[NOT]IN(expression[,…n])2任務(wù)實(shí)施使用WHERE子句過濾數(shù)據(jù)【例12】查詢技術(shù)職稱sTechnicalTitle為“教授”或“副教授”的專家的專家編碼sExpertCode和姓名sName。說明：IN運(yùn)算符的作用類似于“邏輯或”，但比“邏輯或”更加方便靈活。2任務(wù)實(shí)施使用WHERE子句過濾數(shù)據(jù)5.使用字符匹配符構(gòu)成查詢條件有時(shí)用戶對(duì)數(shù)據(jù)表中的數(shù)據(jù)了解不全面，如不能確定所要查詢專家的姓名，只知道他姓“王”，這時(shí)需要使用LIKE以及通配符來實(shí)現(xiàn)模糊匹配查詢。使用字符匹配符構(gòu)成查詢條件的一般形式如下：SQL語言提供的通配符有“%”和“_”。

%（百分號(hào)）：代表0個(gè)或任意多個(gè)字符。

_（下畫線）：代表任意單個(gè)字符。如果要匹配的字符串本身就含有通配符“%”或“_”，就要使用ESCAPE'<換碼字符>'對(duì)通配符進(jìn)行轉(zhuǎn)義。[NOT]LIKE'<匹配串>'[ESCAPE'<換碼字符>’]2任務(wù)實(shí)施使用WHERE子句過濾數(shù)據(jù)【例13】

查詢姓“王”的專家的姓名sName、性別sSex和工作單位sWorkUnit。2任務(wù)實(shí)施使用WHERE子句過濾數(shù)據(jù)【例14】

查詢姓“王”并且姓名只有兩個(gè)字的專家的姓名sName、性別sSex和工作單位sWorkUnit。2任務(wù)實(shí)施使用WHERE子句過濾數(shù)據(jù)6.使用空值判斷符構(gòu)成查詢條件空值判斷符“ISNULL”用來判斷一個(gè)表達(dá)式的值是否為空值。使用ISNULL構(gòu)成查詢條件的一般形式如下：【例15】查詢出生日期未知的專家的姓名sName、學(xué)歷sDiploma和出生日期dBirthDate。注意：這里的“IS”不能用等于號(hào)“=”代替。expressionIS[NOT]NULL2任務(wù)實(shí)施使用LIMIT子句限制返回行數(shù)【例16】

在專家信息表texpertinfo中，查詢專家姓名sName和性別sSex，只顯示5條記錄。3任務(wù)實(shí)施使用LIMIT子句限制返回行數(shù)【例17】

在專家信息表texpertinfo中，查詢專家姓名sName和性別sSex，從第5條記錄開始顯示6條記錄。3任務(wù)實(shí)施使用ORDERBY子句排序1.按一列排序【例18】

查詢年齡最大的5位專家的姓名sName和年齡。說明：在以上SQL語句中，按照專家的年齡從高到低進(jìn)行排序，然后再利用LIMIT子句限制返回的行數(shù)為5條，即可查詢到年齡最大的5位專家信息。4任務(wù)實(shí)施使用ORDERBY子句排序42.按多列排序【例19】查詢年齡最小的5位專家的姓名sName和年齡，查詢結(jié)果按照年齡的升序排列，年齡相同者按姓名降序排列。提示：由于數(shù)據(jù)表的字符集是utf8，當(dāng)排序的列為中文時(shí)，默認(rèn)不會(huì)按照中文拼音順序排序?？梢詫⒘械淖址D(zhuǎn)換為gbk：CONVERT(column_nameUSINGgbk)，強(qiáng)制讓指定的列按中文排序。MySQL中需要安裝了gbk字符集，不然會(huì)報(bào)錯(cuò)。任務(wù)實(shí)施利用SELECT語句對(duì)數(shù)據(jù)進(jìn)行簡單查詢的方法，是必須熟練掌握的內(nèi)容。可以通過SELECT子句選取列，用WHERE子句選取記錄并進(jìn)行簡單的條件查詢，用ORDERBY子句對(duì)查詢結(jié)果進(jìn)行排序。SELECT語句中的子句必須按照規(guī)定的順序書寫。要清楚SELECT語句的執(zhí)行順序，除了LIMIT和ORDERBY子句在SELECT之后執(zhí)行，其他的子句幾乎都在SELECT之前執(zhí)行。勤加練習(xí)，把所有的例子全部自己實(shí)現(xiàn)一遍，基本上就能夠全部理解透徹了，并且在做題的過程中慢慢地就會(huì)自己總結(jié)一些經(jīng)驗(yàn)。經(jīng)驗(yàn)分享模塊三

使用數(shù)據(jù)庫根據(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掌握多表連接查詢、子查詢的語法學(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ì)記錄。其基本語法格式如下：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子句過濾掉不符合條件的數(shù)據(jù)，然后用GROUPBY子句對(duì)余下的數(shù)據(jù)按照指定列分組，最后再用HAVING子句排除一些組。知識(shí)準(zhǔn)備HAVING子句知識(shí)點(diǎn)3數(shù)據(jù)庫本著精簡的設(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ù)的總和。其基本語法格式如下：在FROM子句中也可以省略CROSSJOIN，使用逗號(hào)分隔被連接的表，其基本語法格式如下：交叉連接產(chǎn)生的結(jié)果集一般沒有實(shí)際應(yīng)用的意義，所以這種連接很少使用。知識(shí)準(zhǔn)備連接查詢知識(shí)點(diǎn)4SELECT*FROM表名1CROSSJOIN表名2SELECT*FROM表名1,表名22.內(nèi)連接（INNERJOIN）內(nèi)連接是最常使用的連接查詢方式，通過INNERJOIN或者JOIN連接兩個(gè)表，結(jié)果集中只包含滿足連接條件的記錄。連接條件通常采用“主鍵=外鍵”的形式。內(nèi)連接創(chuàng)建連接關(guān)系有以下兩種方式：

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

在FROM子句中創(chuàng)建連接關(guān)系，其基本語法格式如下：知識(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]表別名。

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

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

子查詢語句必須用圓括號(hào)括起來。

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

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

結(jié)果集的列標(biāo)題為第一個(gè)SELECT查詢語句的列標(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ù)來實(shí)現(xiàn)?！纠?】統(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語句的目標(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.過濾分組【例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è)表之間的連接是通過部門編號(hào)iDeptID來實(shí)現(xiàn)的。任務(wù)實(shí)施連接查詢3說明：本例查詢語句中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è)表之間的連接是通過專家編號(hào)iExpertID來實(shí)現(xiàn)的。由于不是所有專家都被抽取過，為了讓沒有被抽取的專家的信息也顯示在結(jié)果集中，本例采用左外連接方式進(jìn)行查詢。任務(wù)實(shí)施連接查詢3深度理解：本例查詢結(jié)果列出了左表（texpertinfo）中所有滿足條件（sTechnicalTitle='助教'）的記錄。如果左表（texpertinfo）中的記錄不滿足連接條件，將查詢結(jié)果中來自右表（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?；菊Z法格式如下：任務(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é)果為真則返回該行，否則無返回值。子查詢的結(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語句與執(zhí)行結(jié)果如下：從以上執(zhí)行結(jié)果可知“信息工程系”的部門編號(hào)為“1”。任務(wù)實(shí)施嵌套查詢4步驟2：在用戶信息表tuserinfo中查詢部門編號(hào)為“1”的用戶的姓名和手機(jī)號(hào)碼。具體SQL語句與執(zhí)行結(jié)果如下：任務(wù)實(shí)施嵌套查詢4步驟3：將步驟1嵌入到步驟2的查詢條件中，構(gòu)造嵌套查詢。具體SQL語句與執(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用于子查詢，用來確定指定的值是否與子查詢結(jié)果集中的值相匹配。任務(wù)實(shí)施嵌套查詢4

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

分析：可以分兩步來構(gòu)造子查詢。第一步：從抽取專家列表textractionexpert中查詢出2號(hào)項(xiàng)目抽取到的專家編號(hào)。第二步：從專家信息表texpertinfo中查詢抽取到的專家信息。說明：本例執(zhí)行的順序是：首先執(zhí)行圓括號(hào)里的子查詢，返回的結(jié)果集是2號(hào)項(xiàng)目抽取到的專家編號(hào)的集合；然后對(duì)專家信息表texpertinfo從第一行起逐行掃描，每一行的專家編號(hào)列iExpertID都與集合中的值進(jìn)行比較，判斷是否屬于這個(gè)集合，如果是就返回該行，否則無返回值。任務(wù)實(shí)施嵌套查詢43.使用ALL或ANY連接的子查詢使用比較運(yùn)算符連接子查詢時(shí)，通常要用到操作符ALL、ANY、SOME。其基本語法格式如下：任務(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是等效的?！纠?6】使用子查詢列出比博士研究生專家年齡都小的專家的編碼sExpertCode和姓名sName。任務(wù)實(shí)施嵌套查詢4也可以先使用聚合函數(shù)MIN()查詢博士研究生專家的最小年齡，然后將最小年齡作為條件再查詢出專家的編碼和姓名。說明：用聚合函數(shù)實(shí)現(xiàn)子查詢通常比用ALL或ANY的查詢效率高。任務(wù)實(shí)施嵌套查詢44.使用EXISTS連接的子查詢EXISTS和NOTEXISTS關(guān)鍵字用來確定數(shù)據(jù)是否在子查詢結(jié)果集中存在。EXISTS表示子查詢至少返回一行時(shí)條件成立，NOTEXISTS表示子查詢中沒有任何記錄返回時(shí)條件成立。使用EXISTS連接子查詢時(shí)，相當(dāng)于對(duì)外部查詢的數(shù)據(jù)行進(jìn)行了一次存在測試。外部查詢的WHERE子句測試滿足子查詢的行是否存在。帶有EXISTS的子查詢不返回任何數(shù)據(jù)行，它只返回TRUE或FALSE。任務(wù)實(shí)施嵌套查詢4【例17】使用子查詢列出被抽取到的專家信息。深度理解：查詢被抽取到的專家的信息時(shí)，子查詢檢索滿足條件的數(shù)據(jù)是否存在，如果抽取專家列表textractionexpert中的專家編號(hào)iExpertID與專家信息表texpertinfo中任意行的iExpertID匹配，外部查詢中WHERE子句的EXISTS關(guān)鍵字將返回TRUE，即結(jié)果集中存在該行。任務(wù)實(shí)施嵌套查詢4【例18】查詢各學(xué)歷專家的平均年齡及全部專家的平均年齡。任務(wù)實(shí)施聯(lián)合查詢5【例19】查詢各學(xué)歷專家的人數(shù)及被抽取專家人數(shù)，并按人數(shù)降序排序。任務(wù)實(shí)施聯(lián)合查詢5實(shí)施查詢?nèi)蝿?wù)，可按照以下步驟進(jìn)行分析，并逐步實(shí)現(xiàn)。步驟1：分析查詢涉及的表，包括查詢條件和查詢結(jié)果涉及的表，確定是單表查詢，還是多表查詢，確定FROM子句中的表名。步驟2：如果是多表查詢，分析確定表與表之間的連接條件，即確定FROM子句中ON后面的連接條件。步驟3：分析查詢是針對(duì)所有行，還是選擇部分行。如果是選擇部分行，使用WHERE子句，確定WHERE子句中的行條件表達(dá)式。步驟4：分析查詢是否要進(jìn)行分組統(tǒng)計(jì)計(jì)算。如果需要分組統(tǒng)計(jì)，則使用GROUPBY子句，確定分組的列名。然后分析分組后是否要對(duì)組進(jìn)行篩選，如果需要，則使用HAVING子句，確定組篩選條件。步驟5：確定查詢目標(biāo)列表達(dá)式，即確定查詢結(jié)果包含的列名或列表達(dá)式，確定SELECT子句后的目標(biāo)列表達(dá)式。步驟6：分析是否要對(duì)查詢結(jié)果進(jìn)行排序，如果需要排序，則使用ORDERBY子句，確定排序的列名和排序方式。經(jīng)驗(yàn)分享模塊三

使用數(shù)據(jù)庫數(shù)據(jù)表中存儲(chǔ)了數(shù)據(jù)后，為了提高數(shù)據(jù)的查詢速度，可以為數(shù)據(jù)表創(chuàng)建索引。為了提高數(shù)據(jù)的存取性能，保障數(shù)據(jù)庫中數(shù)據(jù)的安全性，可以結(jié)合實(shí)際需求靈活地運(yùn)用視圖。本任務(wù)主要介紹索引和視圖的相關(guān)概念及基本操作。任務(wù)描述任務(wù)4視圖及索引的應(yīng)用理解索引的概念以及索引的作用12掌握使用圖形化工具和SQL語句創(chuàng)建和管理索引的方法3理解視圖的概念以及視圖的作用學(xué)會(huì)通過視圖操作基本表中的數(shù)據(jù)5任務(wù)目標(biāo)理解掌握掌握理解學(xué)會(huì)4掌握使用圖形化工具和SQL語句創(chuàng)建和管理視圖的方法1.索引的基本概念索引是對(duì)數(shù)據(jù)表中一列或多列的值進(jìn)行排序后的一種結(jié)構(gòu)。當(dāng)執(zhí)行“SELECT*FROMtable1WHEREid=10000;”語句時(shí)，如果沒有索引，必須從第1行開始遍歷，直到id等于10000的這一行被找到為止，這樣的查詢效率非常低。如果在id這一列上創(chuàng)建索引，就可以快速地找到了。數(shù)據(jù)庫索引的作用相當(dāng)于圖書的目錄，通過索引可以迅速找到表中指定的數(shù)據(jù)。索引概述知識(shí)點(diǎn)1知識(shí)準(zhǔn)備2.索引的類型MySQL中的索引主要有以下幾種類型：普通索引：是MySQL中的基本索引類型，沒有任何限制條件，通常使用關(guān)鍵字INDEX或KEY來定義。唯一索引：與普通索引基本相同，所不同的是，唯一索引所在列的值不能重復(fù)，即必須是唯一的，通常使用關(guān)鍵字UNIQUE來定義。主鍵：也是一種唯一索引，一個(gè)表只能有一個(gè)主鍵。主鍵一般是在創(chuàng)建表時(shí)指定，也可以通過修改表來設(shè)置主鍵，使用關(guān)鍵字PRIMARYKEY來定義。全文索引：只能在CHAR、VARCHAR或TEXT類型的列上創(chuàng)建全文索引，并且現(xiàn)在只有MyISAM和InnoDB存儲(chǔ)引擎支持全文索引。使用關(guān)鍵字FULLTEXT來定義?？臻g索引：只能創(chuàng)建在空間數(shù)據(jù)類型的列上，目前只有MyISAM存儲(chǔ)引擎支持空間索引，使用關(guān)鍵字SPATIAL來定義。對(duì)于初學(xué)者來說，空間索引很少會(huì)用到。索引概述知識(shí)點(diǎn)1知識(shí)準(zhǔn)備1.創(chuàng)建索引可以在創(chuàng)建數(shù)據(jù)表的同時(shí)創(chuàng)建索引，也可以在已有的數(shù)據(jù)表上添加索引。使用SQL語句創(chuàng)建索引主要有以下幾種方法：（1）使用CREATEINDEX語句創(chuàng)建索引基本語法格式如下：CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_nameONtable_name(column_name[(length)][ASC|DESC],…)

索引的基本操作知識(shí)點(diǎn)2知識(shí)準(zhǔn)備是可選項(xiàng)，UNIQUE指定創(chuàng)建唯一索引，F(xiàn)ULLTEXT指定創(chuàng)建全文索引，SPATIAL指定創(chuàng)建空間索引。指定要?jiǎng)?chuàng)建的索引名稱，一個(gè)表中索引名必須是唯一的。指定要?jiǎng)?chuàng)建索引的表名。指定要?jiǎng)?chuàng)建索引的列名。是可選項(xiàng)，指定索引按升序ASC還是降序DESC排列，默認(rèn)為ASC。是可選項(xiàng)，指定使用列的前l(fā)ength個(gè)字符來創(chuàng)建索引。說明：使用CREATEINDEX語句不能創(chuàng)建主鍵。（2）使用ALTERTABLE語句創(chuàng)建索引基本語法格式如下：ALTERTABLEtable_nameADD[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_name(column_name[(length)][ASC|DESC],…)索引的基本操作知識(shí)點(diǎn)2知識(shí)準(zhǔn)備（3）使用CREATETABLE語句創(chuàng)建索引基本語法格式如下：CREATETABLEtable_name(column_namedata_type[column_constraint],…[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[index_name](column_name[(length)][ASC|DESC],…))索引的基本操作知識(shí)點(diǎn)2知識(shí)準(zhǔn)備關(guān)鍵字KEY與INDEX是同義詞2.查看索引可以使用SHOWINDEX語句查看表中創(chuàng)建的索引。基本語法格式如下：SHOWINDEXFROMtable_name[FROMdatabase_name]索引的基本操作知識(shí)點(diǎn)2知識(shí)準(zhǔn)備指定需要查看索引的表名指定需要查看索引的數(shù)據(jù)表所在的數(shù)據(jù)庫，可省略3.刪除索引為了避免影響數(shù)據(jù)庫性能，應(yīng)及時(shí)刪除不再使用的索引。刪除索引的方式有兩種：（1）使用DROPINDEX語句刪除索引基本語法格式如下：DROPINDEXindex_nameONtable_name索引的基本操作知識(shí)點(diǎn)2知識(shí)準(zhǔn)備指定要?jiǎng)h除的索引名。指定索引所在的表的名稱。（2）使用ALTERTABLE語句刪除索引刪除表中的索引，其基本語法格式如下：刪除表中的主鍵索引，其基本語法格式如下：ALTERTABLEtable_nameDROPINDEXindex_name索引的基本操作知識(shí)點(diǎn)2知識(shí)準(zhǔn)備ALTERTABLEtable_nameDROPPRIMARYKEY視圖是從一個(gè)或多個(gè)基本表（或視圖）導(dǎo)出的表，是一個(gè)虛表。數(shù)據(jù)庫中只存儲(chǔ)視圖的定義，并不存放視圖對(duì)應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍然存放在原來的基本表中?；颈碇械臄?shù)據(jù)發(fā)生變化時(shí)，從視圖中查詢出的數(shù)據(jù)也會(huì)發(fā)生變化。與直接操作基本表相比，使用視圖具有以下優(yōu)點(diǎn)：簡化操作：視圖不僅可以簡化用戶對(duì)數(shù)據(jù)的理解，也可以簡化對(duì)數(shù)據(jù)的操作。用戶不用詳細(xì)了解數(shù)據(jù)庫中復(fù)雜的表結(jié)構(gòu)和表連接，將經(jīng)常使用的查詢定義為視圖，可以有效地避免大量重復(fù)的操作。保護(hù)數(shù)據(jù)安全：用戶通過視圖只能查詢和修改他們所能見到的數(shù)據(jù)，數(shù)據(jù)庫中的其他數(shù)據(jù)既看不見也不可以訪問，從而提高數(shù)據(jù)庫中數(shù)據(jù)的安全性。數(shù)據(jù)獨(dú)立：創(chuàng)建視圖后，應(yīng)用程序通過視圖訪問數(shù)據(jù)表，如果數(shù)據(jù)表有變化，只需要調(diào)整視圖的定義，不用修改應(yīng)用程序代碼，使應(yīng)用程序和數(shù)據(jù)表在一定程度上獨(dú)立。視圖概述知識(shí)點(diǎn)3知識(shí)準(zhǔn)備1.創(chuàng)建視圖CREATEVIEW語句用于建立視圖，其基本語法格式如下：視圖的基本操作知識(shí)點(diǎn)4知識(shí)準(zhǔn)備CREATEVIEWview_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]指定要?jiǎng)?chuàng)建的視圖名稱，該名稱在數(shù)據(jù)庫中必須是唯一的可選項(xiàng)，指定視圖的列名列表，列名之間用逗號(hào)隔開。默認(rèn)情況下，與SELECT語句中的列名相同。指定視圖要執(zhí)行的操作。定義視圖的SELECT語句，該語句可以使用多個(gè)表和其他視圖。可選項(xiàng)，指定在可更新視圖上進(jìn)行修改時(shí)要滿足所指定的限制條件。有兩種方式：CASCADED為默認(rèn)方式，表示要滿足所有相關(guān)視圖和表定義的條件；LOCAL表示只要滿足該視圖本身定義的條件。重要提示：WITHCHECKOPTION只能和可更新視圖一起使用，雖然是可選項(xiàng)，為了保證數(shù)據(jù)的安全性，建議創(chuàng)建視圖最好加上。2.查看視圖（1）用DESCRIBE語句查看視圖DESCRIBE語句不僅可以查看數(shù)據(jù)表的列信息，還可以查看視圖的列信息，其基本語法格式如下：{DESCRIBE|DESC}view_name[column_name]視圖的基本操作知識(shí)點(diǎn)4知識(shí)準(zhǔn)備DESC是DESCRIBE的簡寫，二者用法相同。指定要查看的視圖名。指定要查看的列名。（2）用SHOWTABLESTATUS語句查看視圖使用SHOWTABLESTATUS語句不僅可以查看數(shù)據(jù)表的狀態(tài)信息，還可以查看視圖的狀態(tài)信息，其基本語法格式如下：SHOWTABLESTATUSLIKE'pattern'視圖的基本操作知識(shí)點(diǎn)4知識(shí)準(zhǔn)備指定要匹配的視圖名。（3）用SHOWCREATEVIEW語句查看視圖可以使用SHOWCREATEVIEW語句來查看已有視圖的定義，其基本語法格式如下：SHOWCREATEVIEWview_name視圖的基本操作知識(shí)點(diǎn)4知識(shí)準(zhǔn)備指定要查看視圖的名稱。3.修改視圖可以使用ALTERVIEW語句對(duì)已有視圖的定義進(jìn)行修改，其基本語法格式如下：ALTERVIEWview_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]視圖的基本操作知識(shí)點(diǎn)4知識(shí)準(zhǔn)備說明：ALTERVIEW語句的各項(xiàng)參數(shù)與CREATEVIEW語句中的含義相同。4.刪除視圖可以使用DROPVIEW語句刪除視圖，其基本語法格式如下：DROPVIEW[IFEXISTS]view_name1[,view_name2…]視圖的基本操作知識(shí)點(diǎn)4知識(shí)準(zhǔn)備指定要?jiǎng)h除的視圖名。可以一次刪除多個(gè)視圖。任務(wù)實(shí)施1使用圖形化工具創(chuàng)建和管理索引2使用SQL語句創(chuàng)建和管理索引3使用圖形化工具創(chuàng)建和管理視圖4使用SQL語句創(chuàng)建、管理和應(yīng)用視圖【例1】在專家信息表texpertinfo中的專家姓名sName列上創(chuàng)建一個(gè)唯一索引“index_expert_name”。分析：在專家信息表texpertinfo中，經(jīng)常以專家姓名作為查詢條件，因此可以在專家姓名sName列上創(chuàng)建索引，以提高查詢速度。任務(wù)實(shí)施使用圖形化工具創(chuàng)建和管理索引1說明：如果表中已有數(shù)據(jù)，在創(chuàng)建唯一索引時(shí)，MySQL將自動(dòng)檢驗(yàn)索引列是否存在重復(fù)的值，若存在重復(fù)的值，則創(chuàng)建唯一索引失敗?！纠?】創(chuàng)建表的時(shí)候創(chuàng)建索引。創(chuàng)建一個(gè)新表texperttemp，在表texperttemp的sExpertCode列上創(chuàng)建一個(gè)普通索引“index_expert_code”。任務(wù)實(shí)施使用圖形化工具創(chuàng)建和管理索引1字段名稱數(shù)據(jù)類型是否允許為空說明iExp