MySQL數據庫基礎與實踐4檢索表記錄第章本章內容4.1SELECT基本查詢4.2條件查詢4.3分組查詢4.4表的連接4.5子查詢4.6聯合查詢4.7案例：網上書店系統綜合查詢4.1SELECT基本查詢4.1.1SELECT…FROM查詢語句4.1.2查詢指定字段信息4.1.3關鍵字DISTINCT的使用4.1.4ORDERBY子句的使用4.1.5LIMIT子句的使用4.1SELECT基本查詢查詢數據是使用數據庫的最基本也是最重要的方式。基本查詢也稱為簡單查詢，是指在查詢的過程中只涉及到一個表的查詢。4.1.1SELECT…FROM查詢語句SELECT語句的一般格式為：SELECT[ALL|DISTINCT]<目標列表達式>[，<目標列表達式>]FROM<表名或視圖名>[，<表名或視圖名>][WHERE<條件表達式>] [GROUPBY<列名1>[HAVING<條件表達式>]][ORDERBY<列名2>[ASC|DESC]][LIMIT[start,]count]4.1.1SELECT…FROM查詢語句在SELECT語句的結構中，除了SELECT子句是必不可少的之外，其他子句都是可選的。【例4-1】計算25的平方根并輸出MySQL的版本號。SELECTSQRT(25),VERSION();4.1.2查詢指定字段信息在SELECT子句后面直接列出要顯示的字段的列名，列名之間必須以逗號分隔。【例4-2】檢索Users表，查詢所有會員的名稱、性別和電話號碼。SELECTU_Name,U_Sex,U_PhoneFROMUsers;4.1.2查詢指定字段信息如果在查詢的過程中，要檢索表或視圖中的所有字段信息，可以在SELECT子句中使用通配符“*”。【例4-3】檢索Users表，查詢所有會員的基本資料。SELECT*FROMUsers;4.1.2查詢指定字段信息定義別名可用以下方法：通過“列名列標題”形式；通過“列名AS列標題”形式。例4-1的語句可修改如下：SELECTSQRT(25)平方根,VERSION()as版本號;4.1.3關鍵字DISTINCT的使用ALL關鍵字表示將會顯示所有檢索的數據行，包括重復的數據行；（默認）DISTINCT關鍵字表示僅僅顯示不重復的數據行，對于重復的數據行，則只顯示一次。【例4-4】檢索Orders表，查詢訂購了書籍的會員號。SELECTU_IDFROMOrders;4.1.3關鍵字DISTINCT的使用如果希望在顯示結果的時候去掉重復行，可以使用DISTINCT關鍵字。SELECTDISTINCTU_IDFROMOrders;4.1.4ORDERBY子句的使用ORDERBY子句是根據查詢結果中的一個字段或多個字段對查詢結果進行排序。默認的情況下按升序排列。【例4-5】檢索BookInfo表，按圖書出版的日期進行排序。SELECTB_ID,BT_ID,B_Name,B_DateFROMBookInfoORDERBYB_DateDESC;4.1.4ORDERBY子句的使用【例4-6】檢索BookInfo表，按圖書類別的升序及圖書出版日期的降序進行排序。SELECTB_ID,BT_ID,B_Name,B_DateFROMBookInfoORDERBYBT_ID,B_DateDESC;4.1.5LIMIT子句的使用LIMIT子句的用途是從結果集中進一步選取指定數量的數據行，其基本語法格式如下：LIMIT[start,]count例如，LIMIT5表示返回結果集中的前5行記錄，LIMIT10,20表示從結果集的第11行記錄開始返回20行記錄。注意：在結果集中，第一行記錄的start值為0，而不是1。4.1.4ORDERBY子句的使用【例4-7】檢索BookInfo表，按圖書編號查詢前5本圖書的信息。SELECTB_ID,B_NameFROMBookInfoORDERBYB_IDLIMIT5;4.1.4ORDERBY子句的使用【例4-8】檢索BookInfo表，按圖書編號檢索從第3條記錄開始的2條記錄的信息。SELECTB_ID,B_NameFROMBookInfoORDERBYB_IDLIMIT2,2;4.2條件查詢4.2.1使用關系表達式查詢4.2.2使用邏輯表達式查詢4.2.3設置取值范圍的查詢4.2.4空值查詢4.2.5模糊查詢4.2.1使用關系表達式查詢關系表達式是指在表達式中含有關系運算符。常見的關系運算符有：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、!=或<>（不等于）。如果在WHERE子句中含有關系表達式，則只有滿足關系表達式的數據行才會被顯示到結果集中。4.2.1使用關系表達式查詢【例4-9】檢索BookInfo表，查詢會員價大于40元的圖書信息。SELECTB_ID,B_Name,B_SalePriceFROMBookInfoWHEREB_SalePrice>40;4.2.2使用邏輯表達式查詢常用的邏輯運算符有AND、OR和NOT。當一個WHERE子句同時包括若干個邏輯運算符時，其優先級從高到低依次為NOT、AND、OR。如果想改變優先級，可以使用括號。4.2.2使用邏輯表達式查詢【例4-10】檢索BookInfo表，查詢“機械工業出版社”出版的書名為“MySQL自學手冊”的圖書的基本信息。SELECTB_Name,B_Publisher,B_DateFROMBookInfoWHEREB_Name='MySQL自學手冊'ANDB_Publisher='機械工業出版社';4.2.2使用邏輯表達式查詢【例4-11】檢索BookInfo表，查詢圖書的會員價格在20元到40元之間的圖書信息。SELECTB_ID,B_Name,B_SalePriceFROMBookInfoWHEREB_SalePrice>=20ANDB_SalePrice<=40;4.2.3設置取值范圍的查詢謂詞BETWEEN…AND和NOTBETWEEN…AND可以用來設置查詢條件。其中，BETWEEN后面是范圍的下限，AND后是范圍的上限。例4-11使用BETWEEN…AND語句來完成。SELECTB_ID,B_Name,B_SalePriceFROMBookInfoWHEREB_SalePriceBETWEEN20AND40;4.2.4空值查詢NULL是特殊的值，代表“無值”，與0、空字符串或僅僅包含空格都不相同。在涉及空值的查詢中，可以使用ISNULL或者ISNOTNULL來設置這種查詢條件。4.2.4空值查詢【例4-12】在Users表中新增一條記錄，只輸入會員名zhangsan和密碼654321，然后檢索Users表，查詢電話號碼為空的會員編號和會員名稱。SELECTU_ID,U_NameFROMUsersWHEREU_PhoneISNULL;4.2.5模糊查詢通常在查詢字符數據時，提供的查詢條件并不是十分的精確。查詢條件僅僅是包含或類似某種樣式的字符，這種查詢稱為模糊查詢。4.2.5模糊查詢要實現模糊查詢，必須使用通配符，利用通配符可以創建和特定字符串進行比較的搜索模式。SQL支持如下通配符：%：代表任意多個字符。_（下劃線）：代表任意的一個字符。4.2.5模糊查詢如果查詢條件中使用了通配符，則操作符必須使用LIKE關鍵字。LIKE關鍵字用于搜索與特定字符串相匹配的字符數據，其基本的語法形式為：[NOT]LIKE<匹配字符串>4.2.5模糊查詢【例4-13】檢索BookInfo表，查詢所有MySQL相關書籍的名稱、出版社和會員價格。SELECTB_Name,B_Publisher,B_SalePriceFROMBookInfoWHEREB_NameLIKE'%MySQL%';4.2.5模糊查詢【例4-14】檢索BookInfo表，查詢所有的第2個字為“志”的作者所寫圖書的書名、作者和出版社信息。SELECTB_Name,B_Author,B_PublisherFROMBookInfoWHEREB_AuthorLIKE'_志%';4.2.5模糊查詢如果要查詢的字符串本身就含有通配符，此時就需要用ESCAPE關鍵字，對通配符進行轉義。例如，在Users表中添加一條記錄：會員名為yiyi_66，密碼為123456。現要查詢會員名中含有“_”的會員信息，可以使用如下的語句：SELECT*FROMUsersWHEREU_NameLIKE'%/_%'ESCAPE'/';4.3分組查詢4.3.1GROUPBY子句4.3.2HAVING子句4.3分組查詢如果要在數據檢索時對表中數據按照一定條件進行分組匯總或求平均值，就要在SELECT語句中與GROUPBY子句一起使用聚合函數。4.3分組查詢常用的聚合函數聚合函數說明SUM（）返回某列所有值的總和AVG（）返回某列的平均值MAX（）返回某列的最大值MIN（）返回某列的最小值COUNT（）返回某列的行數4.3分組查詢例如，要統計Users表中會員的數量，可以使用COUNT(*)，計算出來的結果就是查詢所選取到的行數，相關語句如下：SELECTCOUNT(*)FROMUsers;只統計填寫電話號碼的會員個數：SELECTCOUNT(U_Phone)FROMUsers;注意：聚合函數忽略列值為NULL的行。4.3.1GROUPBY子句分組是通過GROUPBY子句來實現的，其基本語法格式如下：GROUPBY<列名>4.3.1GROUPBY子句【例4-15】檢索BookInfo表，查詢每個出版社出版的圖書的數量。SELECTB_Publisher,COUNT(*)AS總數FROMBookInfoGROUPBYB_Publisher;4.3.1GROUPBY子句【例4-16】檢索BookInfo表，查詢每個出版社圖書的最高價格和最低價格。SELECTB_Publisher,MAX(B_MarketPrice)AS最高價格,MIN(B_MarketPrice)AS最低價格FROMBookInfoGROUPBYB_Publisher;4.3.2HAVING子句如果分組以后要求按一定條件對這些組進行篩選，則需要使用HAVING子句指定篩選條件。HAVING子句必須和GROUPBY子句同時使用。4.3.2HAVING子句【例4-17】檢索BookInfo表，查詢出版圖書在2本及2本以上的出版社信息。SELECTB_Publisher,COUNT(*)AS總數FROMBookInfoGROUPBYB_PublisherHAVINGCOUNT(*)>=2;4.3.2HAVING子句【例4-18】檢索BookInfo表，查詢出版了2本及2本以上并且價格大于等于32元的圖書信息。SELECTB_Publisher,COUNT(*)AS總數FROMBookInfoWHEREB_MarketPrice>=32GROUPBYB_PublisherHAVINGCOUNT(*)>=2;4.3.2HAVING子句HAVING子句和WHERE子句都是設置查詢條件，但兩個子句的作用對象不同。WHERE子句作用的對象是基本表或視圖，從中選出滿足條件的記錄；而HAVING子句的作用對象是組，從中選出滿足條件的分組。WHERE在數據分組之前進行過濾，而HAVING在數據分組之后進行過濾。4.4 表的連接4.4.1內連接4.4.2外連接4.4.3自連接4.4.4交叉連接4.4 表的連接多表連接的語法格式如下：SELECT<查詢列表>FROM<表名1>[連接類型]JOIN<表名2>ON<連接條件>WHERE<查詢條件>其中，連接類型有3種：內連接（INNERJOIN）、外連接（OUTERJOIN）和交叉連接（CROSSJOIN）。4.4 表的連接用來連接兩個表的條件稱為連接條件，通常是通過匹配多個表中的公共字段來實現的。4.4.1內連接內連接是從兩個或兩個以上的表的組合中，挑選出符合連接條件的數據。內連接是最常用的連接類型，也是默認的連接類型。在FROM子句中使用INNERJOIN（INNER關鍵字可以省略）來實現內連接。4.4.1內連接【例4-19】檢索BookInfo和BookType表，查詢每本圖書所屬的圖書類別。SELECTB_Name,BookInfo.BT_ID,BT_NameFROMBookInfoINNERJOINBookTypeONBookInfo.BT_ID=BookType.BT_IDORDERBYBT_ID;4.4.1內連接【例4-20】檢索Users和Orders表，查詢訂單總價超過100元的會員名、下單時間及訂單總價。SELECTU.U_Name,O.O_ID,O.O_Time,O.O_TotalPriceFROMUsersUINNERJOINOrdersOONU.U_ID=O.U_IDWHEREO_TotalPrice>100;4.4.1內連接【例4-21】檢索OrderDetails、Orders和BookInfo表，查詢訂單的下單時間及所購圖書名。SELECTOD.OD_ID,O.O_Time,BI.B_NameFROMOrderDetailsODINNERJOINOrdersOINNERJOINBookInfoBIONOD.O_ID=O.O_IDANDOD.B_ID=BI.B_ID;4.4.2外連接使用外連接時，以主表中每行的數據去匹配從表中的數據行，如果符合連接條件則返回到結果集中；如果沒有找到匹配行，則主表的行仍然保留，并且返回到結果集中，相應的從表中的數據行被填上NULL值后也返回到結果集中。4.4.2外連接外連接有3種類型，分別是左外連接（LEFTOUTERJOIN）、右外連接（RIGHTOUTERJOIN）和全外連接（FULLOUTERJOIN）。MySQL暫不支持全外連接。4.4.2外連接1.左外連接左外連接的結果集中包含左表（JOIN關鍵字左邊的表）中所有的記錄，如果右表中沒有滿足連接條件的記錄，則結果集中右表中的相應行數據填充為NULL。4.4.2外連接【例4-22】以左外連接方式查詢所有會員的訂書情況，在結果集中顯示會員編號、會員名稱、訂單產生時間及訂單總價，并按會員編號排序。SELECTU.U_ID,U.U_Name,O.O_Time,O.O_TotalPriceFROMUsersULEFTOUTERJOINOrdersOONU.U_ID=O.U_IDORDERBYU_ID;4.4.2外連接2.右外連接右外連接的結果集中包含滿足連接條件的所有數據和右表（JOIN關鍵字右邊的表）中不滿足條件的數據，左表中的相應行數據為NULL。4.4.2外連接【例4-23】以右外連接方式查詢所有訂單的詳細情況，在結果集中顯示訂單詳情號、購買數量、圖書編號及圖書名。SELECTOD.OD_ID,OD_Number,BI.B_ID,BI.B_NameFROMOrderDetailsODRIGHTOUTERJOINBookInfoBIONOD.B_ID=BI.B_ID;4.4.3自連接在同一個表中進行的連接被稱為自連接。對一個表使用自連接時，可以看作是這張表的兩個副本之間進行的連接，必須為該表指定兩個別名。4.4.3自連接【例4-24】在圖書類別表BookType中，查詢每種圖書類別和它的子類別。SELECTBT1.BT_NameAS父類別,BT2.BT_NameAS子類別FROMBookTypeBT1INNERJOINBookTypeBT2ONBT1.BT_ID=BT2.BT_FatherID;4.4.3自連接【例4-25】要查詢BookInfo表中高于“MySQL編程基礎”會員價格的圖書號、圖書名稱和圖書會員價格，查詢后的結果集要求按會員價格降序排列。SELECTB2.B_ID,B2.B_Name,B2.B_SalePriceFROMBookInfoB1INNERJOINBookInfoB2ONB1.B_Name=MySQL編程基礎'ANDB1.B_SalePrice<B2.B_SalePriceORDERBYB2.B_SalePriceDESC;4.4.4交叉連接使用交叉連接查詢，如果不帶WHERE子句時，則返回的結果是被連接的兩個表的笛卡爾積；如果交叉連接帶有WHERE子句時，則返回結果為連接兩個表的笛卡爾積減去WHERE子句所限定而省略的行數。交叉連接使用CROSSJOIN關鍵字。4.4.4交叉連接【例4-26】在Orders表和OrderDetails表中使用交叉連接。SELECTO.O_ID,OD.OD_IDFROMOrdersOCROSSJOINOrderDetailsOD;4.5子查詢4.5.1返回單行的子查詢4.5.2返回多行的子查詢4.5.3子查詢與數據更新4.5子查詢子查詢是指在一個外層查詢中包含另一個內層查詢，即在一個SELECT語句中的WHERE子句中，包含有另一個SELECT語句。外層的SELECT語句稱為主查詢，WHERE子句中包含的SELECT語句稱為子查詢。一般將子查詢的查詢結果作為主查詢的查詢條件。4.5.1返回單行的子查詢返回單行的子查詢是指子查詢的查詢結果只返回一個值，并將這個返回值作為父查詢的條件，在父查詢中進一步查詢。在WHERE子句中可以使用比較運算符來連接子查詢。4.5.1返回單行的子查詢【例4-27】查詢訂購了“MySQL編程基礎”圖書的訂單詳情號、訂購數量及圖書總價。SELECTOD_ID,OD_Number,OD_PriceFROMOrderDetailsWHEREB_ID=(SELECTB_IDFROMBookInfoWHEREB_Name=MySQL編程基礎');4.5.2返回多行的子查詢返回多行的子查詢就是子查詢的查詢結果中包含多行數據。返回多行的子查詢經常與IN、EXIST、ALL、ANY和SOME關鍵字一起使用。4.5.2返回多行的子查詢1.使用IN關鍵字其語法格式為：WHERE<表達式>[NOT]IN（<子查詢>）4.5.2返回多行的子查詢【例4-28】查詢訂單總價小于60元的會員信息。SELECTU_ID,U_Name,U_PhoneFROMUsersWHEREU_IDIN(SELECTU_IDFROMOrdersWHEREO_TotalPrice<60);4.5.2返回多行的子查詢2.使用EXISTS關鍵字其語法格式為：WHERE[NOT]EXISTS（<子查詢>）使用EXISTS關鍵字的子查詢并不返回任何數據，只返回邏輯真值和邏輯假值。在使用EXISTS時，子查詢通常將“*”作為輸出列表。4.5.2返回多行的子查詢【例4-29】查詢訂購了圖書的會員信息。SELECTU.U_ID,U.U_Name,U.U_SexFROMUsersUWHEREEXISTS(SELECT*FROMOrdersOWHEREO.U_ID=U.U_ID);4.5.2返回多行的子查詢3.使用ALL、ANY和SOME關鍵字其語法格式為：WHERE<表達式><比較運算符>[ALL|ANY|SOME]（<子查詢>）ANY關鍵字表示任何一個（其中之一），只要與子查詢中一個值相符合即可；ALL關鍵字表示所有（全部），要求與子查詢中的所有值相符合；SOME同ANY是同義詞。4.5.2返回多行的子查詢【例4-30】查詢訂購了圖書編號大于3的訂單編號及收貨人的姓名、地址、郵編。SELECTO_ID,O_UserName,O_Address,O_PostCodeFROMOrdersWHEREO_ID>ANY(SELECTO_IDFROMOrderDetailsWHEREB_ID>3);4.5.3子查詢與數據更新1.子查詢與INSERT語句其語法格式為：INSERTINTO<表名>[<列名>]<子查詢>要插入數據的表必須已經存在；要插入數據的表結構必須和子查詢語句的結果集結構相兼容。4.5.3子查詢與數據更新【例4-31】查詢每一類圖書會員價格的平均價格，并將結果保存到新表AvgPrice中。（1）創建新表AvgPrice。CREATETABLEAvgPrice(B_IDint,Avg_Pricefloat);（2）將查詢結果插入新表AvgPrice中。INSERTINTOAvgPriceSELECTBT_ID,AVG(B_SalePrice)FROMBookInfoGROUPBYBT_ID;（3）查看AvgPrice表中記錄。SELECT*FROMAvgPrice;4.5.3子查詢與數據更新2.子查詢與UPDATE語句【例4-32】將BookInfo表中“MySQL”類別圖書的會員價格修改為市場價格的70%。UPDATEBookInfoSETB_SalePrice=B_MarketPrice*0.7WHERE'MySQL'=(SELECTBT_NameFROMBookTypeWHEREBookInfo.BT_ID=BookType.BT_ID);4.5.3子查詢與數據更新3.子查詢與DELETE語句【例4-33】刪除BookInfo表中“.NET”類別圖書基本信息。DELETEFROMBookInfoWHERE‘.NET'=(SELECTBT_NameFROMBookTypeWHEREBookInfo.BT_ID=BookType.BT_ID);4.6聯合查詢聯合查詢是指合并兩個或多個查詢語句的結果集。其語法格式為：SELECT語句1UNION[ALL]SELECT語句2ALL選項表示保留結果集中的重復記錄，默認時系統自動刪除重復記錄。4.6聯合查詢【例4-34】查詢會員表中會員聯系方式及訂單表中的會員聯系方式。SELECTU_Name,U_PhoneFROMUsersUNIONALLSELECTO_UserName,O_PhoneFROMOrders;4.7案例：網上書店系統綜合查詢【例4-35】查詢會員表中會員編號、會員名稱及電話號碼，要求列名以漢字標題顯示。SELECTU_ID會員編號,U_Name會員名稱,U_Phone電話號碼FROMUsers;4.7案例：網上書店系統綜合查詢【例4-36】查詢價格最高的圖書信息。SELECTB_ID,B_Name,B_MarketPriceFROMBookInfoORDERBYB_MarketPriceDESCLIMIT1;4.7案例：網上書店系統綜合查詢【例4-37】統計每本圖書的銷量信息。SELECTB_ID,SUM(OD_Number)AS銷量FROMOrderDetailsGROUPBYB_IDORDERBYB_ID;4.7案例：網上書店系統綜合查詢【例4-38】查詢銷量為0的圖書信息。SELECTB_ID,B_NameFROMBookInfoBIWHERENOTEXISTS(SELECT*FROMOrderDetailsODWHEREOD.B_ID=BI.B_ID);4.7案例：網上書店系統綜合查詢【例4-39】查詢linli所購圖書的信息。SELECTBI.B_ID,BI.B_NameFROMBookInfoBIINNERJOINOrderDetailsODONBI.B_ID=OD.B_IDWHEREOD.O_IDIN(SELECTO.O_IDFROMOrdersOINNERJOINUsersUONO.U_ID=U.U_IDWHEREU.U_Name=’linli’);本章總結本章主要介紹了MySQL中T-SQL語言的各種查詢功能和用法。使用SQL查詢語句可以完成大部分的數據查詢操作，包括基本的數據查詢、條件查詢、分組查詢、連接查詢、子查詢、集合查詢等，為學習數據庫編程和數據庫操作打下基礎。實踐與練習一、選擇題1.在SELECT子句中，關鍵字________用于消除重復項。A．AS B.DISTINCT C.LIMIT D.LIKE2.要使用模糊查詢來從數據庫中查找與某一數據相關的信息，可以使用關鍵字________。A.AND B.OR C.ALL D.LIKE3.在SELECT語句中，分組時使用________子句。A.ORDERBY B.GROUPBY C.FROM D.WHERE實踐與練習4.在SELECT語句中，下列________子句用于對分組統計進一步設置條件A.ORDERBY B.GROUPBY C.HAVING D.WHERE5.在WHERE子句中，如果出現了“ageBETWEEN30AND40”，這個表達式等同于________。

A.age>=30ANDage<=40 B.age>=30ORage<=40 C.age>30ANDage<40 D.age>30ORage<406.在WHERE子句的條件表達式中，可以匹配0個到多個字符的通配符是________。A.* B.% C.- D.?實踐與練習二、填空題（1）聚合函數中求數據總和的是。（2）查詢會員信息時，結果按姓名（Name）降序排列，可使用子句。（3）查找數據表中的記錄使用關鍵