《MySQL數(shù)據(jù)庫應(yīng)用與管理項(xiàng)目化教程》課件-項(xiàng)目5-4子查詢

項(xiàng)目五-4子查詢?nèi)蝿?wù)目標(biāo)任務(wù)一認(rèn)識子查詢?nèi)蝿?wù)二

使用比較運(yùn)算符的子查詢?nèi)蝿?wù)三使用[NOT]IN列表運(yùn)算符的子查詢?nèi)蝿?wù)四使用[NOT]EXISTS測試存在性的子查詢?nèi)蝿?wù)五

合并查詢

任務(wù)一認(rèn)識子查詢認(rèn)識子查詢

主要內(nèi)容：子查詢的概念子查詢應(yīng)用場合重點(diǎn)難點(diǎn):子查詢應(yīng)用場合子查詢查閱語法：官網(wǎng)文檔：/doc/refman/8.0/en/subqueries.html語法：SELECT[ALL|DISTINCT]select_expr[,select_expr...]FROMtable_referencesWHERE{operandcomparison_operatorANY(subquery)|operandIN(subquery)|operandcomparison_operatorSOME(subquery)}

comparison_operatorisoneoftheseoperators:=><>=<=<>!=子查詢的概念子查詢（嵌套查詢）如果一個SELECT語句能夠返回一個單值或一列值并嵌套在另一個SELECT、INSERT、UPDATE或DELETE語句中，則稱之為子查詢或內(nèi)層查詢。包含一個或多個子查詢的語句稱為主查詢或外層查詢。子查詢應(yīng)用場合子查詢作為條件表達(dá)式部分SELECT*FROMtb_name1WHEREcol_name=(SELECTcol_nameFROMtb_name2)例：mysql>USEdb_shop;mysql>SELECT*FROMstaffer

WHEREdept_id=(SELECTdept_idFROMdepartment

WHEREdept_name='銷售部');子查詢應(yīng)用場合子查詢做為表:應(yīng)用一：用在FROM子句SELECT*FROM(SELECT*FROMtb_name)[AS]tb_alias;例：mysql>USEdb_shop;mysql>SELECT*FROM(SELECT*FROMstafferWHEREsex='F')ASA

WHEREdept_id='002';子查詢應(yīng)用場合應(yīng)用二：用在JOIN子句UPDATEtb_nameAINNERJOIN(SELECT*|select_expr)BONA.col_name=B.col_nameSETA.col_name=select_expr;例：mysql>USEdb_shop;mysql>UPDATEordersAinnerjoin(SELECTorder_id,SUM(total_price)ASsum_total_priceFROMitemGROUPBYorder_id)BONA.order_id=B.order_idSETorder_total_price=sum_total_price;子查詢應(yīng)用場合子查詢做為值：應(yīng)用一：UPDATEtb_name1SETcol_name=(SELECTcol_nameFROMtb_name2)WHEREwhere_condition例：mysql>USEdb_shop;mysql>UPDATEdepartment_bak2SETdept_phone=(SELECTdept_phoneFROMdepartmentWHEREdept_id='002')WHEREdept_id='002'子查詢應(yīng)用場合應(yīng)用二：SELECT*FROMtb_name1BETWEEN初始值A(chǔ)ND(SELECTcol_nameFROMtb_name2)例：mysql>USEdb_shop;mysql>SELECT*FROMgoodsWHEREunit_priceBETWEEN3and(SELECTMAX(unit_price)FROMgoods)；考考你1、請列出子查詢可以用在SELECT語句的應(yīng)用；2、請列出子查詢可以用在UPDATE語句的應(yīng)用；3、請列出子查詢可以用在DELETE語句的應(yīng)用；4、請列出子查詢可以用在INSERT語句的應(yīng)用；任務(wù)二使用比較運(yùn)算符的子查詢用于比較運(yùn)算符的子查詢

主要內(nèi)容：使用比較運(yùn)算符的子查詢語法使用比較運(yùn)算符的子查詢規(guī)則比較運(yùn)算符的子查詢應(yīng)用重點(diǎn)難點(diǎn):ANY與ALL的應(yīng)用場合用于比較運(yùn)算符的子查詢使用比較運(yùn)算符的子查詢SELECT<字段列表>FROM<表名>WHERE測試表達(dá)式比較運(yùn)算符[ANY|ALL](子查詢)用于比較運(yùn)算符的子查詢使用子查詢進(jìn)行單一比較測試

例1:從職員表中查詢采購部的員工信息：mysql>USEdb_shop;mysql>SELECT*FROMstafferWHEREdept_id=(SELECTdept_idFROMdepartmentWHEREdept_name='銷售部')；/*子查詢返回單一數(shù)據(jù)*/用于比較運(yùn)算符的子查詢例2：從訂單表中查詢商品編號為2的商品訂單數(shù)量高于其平均訂單數(shù)量的記錄信息。mysql>USEdb_shop;mysql>SELECT*FROMitemWHEREquantity>(SELECTAVG(quantity)FROMitem);/*子查詢返回單一數(shù)據(jù)*/用于比較運(yùn)算符的子查詢使用子查詢進(jìn)行批量比較測試使用ANY或者SOME：語法：SELECT{*|col_list}FROMtb_name WHERE

col_name比較運(yùn)算符ANY（子查詢）作用：比較運(yùn)算符將一個表達(dá)式的值與子查詢返回的一列值中的每一個進(jìn)行比較，如果在某一次比較中運(yùn)算結(jié)果為TRUE，則ANY測試返回TRUE。用于比較運(yùn)算符的子查詢例1：從商品表中獲取比最便宜商品的價(jià)格高的商品信息。mysql>USEdb_shop;mysql>SELECT*FROMgoodsWHEREunit_price>ANY(SELECTDISTINCTunit_priceFROMgoods)；用于比較運(yùn)算符的子查詢例2：從商品表中獲取比最貴商品的價(jià)格低的商品信息。mysql>USEdb_shop;mysql>SELECT*FROMgoodsWHEREunit_price<ANY(SELECTDISTINCTunit_priceFROMgoods)；用于比較運(yùn)算符的子查詢使用ALL運(yùn)算符：語法：SELECT{*|col_list}FROMtb_name WHEREcol_name比較運(yùn)算符ALL（子查詢）作用：比較運(yùn)算符將一個表達(dá)式的值與子查詢返回的一列值中的每一個進(jìn)行比較，如果在每次比較中運(yùn)算結(jié)果都為TRUE，則ALL測試返回TRUE，即有一次返回FALSE，則ALL測試返回FALSE。用于比較運(yùn)算符的子查詢例1：從商品表中獲取比最貴商品的信息。mysql>USEdb_shop;mysql>SELECT*FROMgoods

WHEREunit_price>=ALL

(SELECTDISTINCTunit_priceFROMgoods);用于比較運(yùn)算符的子查詢例2：從商品表中獲取比最便宜商品的信息。mysql>USEdb_shop;mysql>SELECT*FROMgoods

WHEREunit_price<=ALL

(SELECTDISTINCTunit_priceFROMgoods);用于比較運(yùn)算符的子查詢例2：從商品表中獲取比最便宜商品的信息。mysql>USEdb_shop;mysql>SELECT*FROMgoodsWHEREunit_price<=ALL(SELECTDISTINCTunit_priceFROMgoods);用于比較運(yùn)算符的子查詢比較運(yùn)算符修改符返回結(jié)果>、>=、!<ALL大于子查詢的最大值A(chǔ)NY大于子查詢的最小值=ANY相當(dāng)于IN<>!=ALL相當(dāng)于NOTIN<<=!>ALL小于子查詢的最小值A(chǔ)NY小于子查詢的最大值考考你查詢跟“張南”同一部門的員工查詢商品一筆訂單數(shù)量低于該商品的平均訂單數(shù)量的訂單信息；查詢所有“飲料水”的詳細(xì)訂單信息；查詢所有采購部的商品信息；查詢最便宜的商品信息；查詢最貴的商品信息；查詢比最便宜商品的價(jià)格高的商品；任務(wù)三

使用[NOT]IN列表運(yùn)算符的子查詢使用IN運(yùn)算符的子查詢

主要內(nèi)容:IN運(yùn)算符應(yīng)用語法；IN的應(yīng)用重點(diǎn)難點(diǎn):IN的應(yīng)用場合使用IN運(yùn)算符的子查詢語法：SELECT<字段列表> FROM<表名> WHERE測試表達(dá)式[NOT]IN（子查詢）

作用：子查詢返回的結(jié)果集是單個字段值的一個列表，該字段必須與測試表達(dá)式的數(shù)據(jù)類型相同，測試表達(dá)式的值與該列表中的任何一個值相等，則返回TRUE。使用IN運(yùn)算符的子查詢例1：查詢銷售部的全部員工信息mysql>USEdb_shop;mysql>SELECT*FROMstafferWHEREdept_idIN(SELECTDISTINCTdept_idFROMdepartmentWHEREdept_name='銷售部');使用IN運(yùn)算符的子查詢例2：查詢跟“張南”同一部門的員工mysql>USEdb_shop;mysql>SELECT*FROMstafferWHEREdept_idIN(SELECTDISTINCTdept_idFROMstafferWHEREstaff_name='張南');考考你查詢所有采購部的商品信息；查詢所有飲用水的訂單信息；任務(wù)四使用[NOT]EXISTS測試存在性的子查詢使用EXISTS存在性測試子查詢

主要內(nèi)容:EXISTS運(yùn)算符應(yīng)用語法；EXISTS的應(yīng)用；重點(diǎn)難點(diǎn):EXISTS的應(yīng)用場合；使用EXISTS存在性測試子查詢語法：SELECT<字段列表> FROM<表名> WHERE[NOT]EXITSTS（子查詢）作用：檢查子查詢所返回的結(jié)果集是否包含有記錄，若子查詢的結(jié)果集中包含有一行或多行記錄，則子查詢返回TRUE，若不包含任何記錄，則返回FALSE。有NOT，則剛好相反。使用EXISTS存在性測試子查詢例如：檢查沒有進(jìn)入訂單的商品；mysql>USEdb_shop;mysql>SELECT*FROMgoodsWHERENOTEXISTS(SELECT*FROMitemWHEREgoods.goods_id=item.goods_id);回顧EXISTS的應(yīng)用：CREATEDABASEIFNOTEXISTS數(shù)據(jù)庫名CREATETABLEIFNOTEXISTS數(shù)據(jù)表名.....考考你1、找出銷售部全體員工的信息。2、從訂單表中查詢訂單數(shù)量比商品平均銷售量低的訂單記錄。3、從商品表中查詢低于“農(nóng)夫山泉”價(jià)格的飲用水。4、找出與李強(qiáng)在同一個部門的員工信息。5、找出從未被進(jìn)入訂單的商品基本信息。6、查詢供應(yīng)1號商品的所有供應(yīng)商信息。任務(wù)五 合并查詢查閱語法：官網(wǎng)文檔：/doc/refman/8.0/en/union.html語法：SELECT...UNION[ALL|DISTINCT]SELECT...[UNION[ALL|DISTINCT]SELECT...]

任務(wù)五 合并查詢?nèi)サ糁貜?fù)記錄例如：mysql>USEdb_shop;

mysql>SELECTdept_id,staff_nameFROMstaffer

UNION

SELECTdept_id,staff_nameFROMstaffer_bak3;不去重復(fù)記錄例如：mysql>USEdb_shop; mysql>SELECTdept_id,staff_nameFROMstaffer

UNIONALL

SELECTdept_id,staff_nameFROMstaffer_bak3;考考你1、檢索出年度參會人員名單，包含職員和顧客。2、檢索出年度參會人員名單，包含女職員和消費(fèi)超過5000的顧客。3、檢索電話號碼不符合現(xiàn)有手機(jī)電話的員工電話出來。任務(wù)實(shí)施按下列操作完成db_shopping數(shù)據(jù)庫表記錄的查詢。1.選擇db_shopping數(shù)據(jù)庫，執(zhí)行語句如下：mysql>USEdb_shopping;2.查詢跟“張一楠”同一部門的職員基本資料（包括張一楠），執(zhí)行語句如下：mysql>SELECT*FROMstafferWHEREdept_idIN(SELECTDISTINCTdept_idFROMstafferWHEREstaff_name=‘張一楠’);3.查詢所有還未有訂單的商品，顯示商品編號及其基本信息，執(zhí)行語句如下：mysql>SELECT*FROMgoodsWHEREidNOTIN(SELECTDISTIN