《MySQL數(shù)據(jù)庫(kù)應(yīng)用案例教程》教案 第9課 多表數(shù)據(jù)記錄查詢

課題多表數(shù)據(jù)記錄直詢

課時(shí)2課時(shí)(90min)

知識(shí)技能目標(biāo)：

(1)掌握多表連接查詢的方法

(2)掌握FROM子句和WHERE子句中子查詢的使用方法

教學(xué)目標(biāo)(3)掌握使用UNION關(guān)鍵字合并查詢結(jié)果的方法

素質(zhì)目標(biāo)：

(1)了解數(shù)據(jù)庫(kù)前沿技術(shù)，緊跟時(shí)代發(fā)展

(2)了解數(shù)據(jù)庫(kù)在生活中的應(yīng)用

教學(xué)重點(diǎn)：多表連接查詢的方法

教學(xué)重難點(diǎn)教學(xué)難點(diǎn)：FROM子句和WHERE子句中子查詢的使用方法，使用UNION關(guān)鍵字合并查詢結(jié)果的方

法

教學(xué)方法案例分析法、問(wèn)答法、討論法、講授法、實(shí)踐法

教學(xué)用具電腦、投影儀、多媒體課件、教材

第1節(jié)課：課前彳珍一考勤(2min)一問(wèn)題導(dǎo)入(5min)一傳授新知(38min)

教學(xué)設(shè)計(jì)第2節(jié)課：?jiǎn)栴}導(dǎo)入(5min)一傳授新知(20min)一?課堂實(shí)訓(xùn)(15min)一課堂小結(jié)(3min)一作

業(yè)布置(2min)

教學(xué)過(guò)程主要教學(xué)內(nèi)容及步驟設(shè)計(jì)意圖

第一節(jié)課

通過(guò)課前任務(wù),

【教師】布置課前任務(wù)，和學(xué)生負(fù)責(zé)人取得聯(lián)系，讓其提醒同學(xué)通過(guò)APP或其他

使學(xué)生預(yù)習(xí)本節(jié)

學(xué)習(xí)軟件，預(yù)習(xí)本節(jié)課要學(xué)習(xí)的知識(shí)

課前任務(wù)課要學(xué)習(xí)的知識(shí)，

【學(xué)生】完成課前任務(wù)增加學(xué)生的學(xué)習(xí)

興趣

【教師】使用APP進(jìn)行簽到培養(yǎng)學(xué)生的組

考勤

織紀(jì)律性，掌握學(xué)

(2min)【學(xué)生】班干部報(bào)請(qǐng)假人員及原因

生的出勤情況

【教師】提出以下問(wèn)題：通過(guò)問(wèn)題導(dǎo)入，

問(wèn)題導(dǎo)入

如何在多張數(shù)據(jù)表中進(jìn)行查詢？引導(dǎo)學(xué)生主動(dòng)思

(5min)考，激發(fā)學(xué)生的學(xué)

【學(xué)生】聆聽(tīng)'思考、舉手回答習(xí)興趣

【教師】通過(guò)學(xué)生的回答引入要講的知識(shí)，講解連接查詢的相關(guān)知識(shí)

8.1連接查詢通過(guò)教師的講

傳授新知解和演示，使學(xué)生

中【教師】隨機(jī)邀請(qǐng)學(xué)生回答以下問(wèn)題：

(38min)了解連接查詢的

什么是連接查詢？相關(guān)知識(shí)

｝【學(xué)生】聆聽(tīng)、思考、回答

+【教師】總結(jié)學(xué)生的回答

在關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)中，通常一張表只會(huì)存儲(chǔ)一個(gè)實(shí)體的相關(guān)信息，如果

用戶需要查詢多張表中不同實(shí)體的數(shù)據(jù)可以使用關(guān)鍵字JOIN對(duì)表執(zhí)行連接查詢操

作，但前提條件是，這些表中必須存在具有相同意義的字段.連接查詢主要包括內(nèi)

連接查詢和外連接查詢，另外還可以在連接查詢中添加過(guò)濾條件，篩選符合條件的

數(shù)據(jù)，這就是復(fù)合條件連接查詢。

8.1.1內(nèi)連接查詢

★【教師】組織學(xué)生掃碼觀看”內(nèi)連接查詢“視頻（詳見(jiàn)教材），讓學(xué)生對(duì)基

本知識(shí)有一個(gè)大致的了解

內(nèi)連接查詢（INNERJOIN）是使用比較運(yùn)算符對(duì)多個(gè)表間的某些列數(shù)據(jù)進(jìn)行比

較，并列出這些表中與連接條件相匹配的數(shù)據(jù)行，組合成新的記錄。表之間的連接

條件由表中具有相同意義的字段組成。

〉【教師】通過(guò)多媒體展示"staff表結(jié)構(gòu)"和"section表結(jié)構(gòu)”表格，介紹

相關(guān)知識(shí)

為便于講解，首先需要?jiǎng)?chuàng)建一個(gè)數(shù)據(jù)庫(kù)staff,并在其中創(chuàng)建兩個(gè)數(shù)據(jù)表staff和

section,兩個(gè)表的結(jié)構(gòu)分別如表8-1和表8-2所不。

表8-1staff表結(jié)構(gòu)

字段數(shù)據(jù)類(lèi)型約束注釋

無(wú)符號(hào)、主鍵、自增、

stafflidINT(IO)員工ID

非空

section_idINT(IO)無(wú)符號(hào)、非空部門(mén)ID

positions_idINT(IO)非空職位ID

nameVARCHAR(IO)非空姓名

sexENUMC男；女）非空性別

phone_numberCHAR(ll)非空手機(jī)號(hào)

無(wú)符號(hào)、非空，默認(rèn)值

moneyDECIMAL(10,2)薪資

0

entry_dateDATETIME非空入職時(shí)間

表8-2section表結(jié)構(gòu)

字段數(shù)據(jù)類(lèi)型約束注釋

無(wú)符號(hào)、主鍵、自增、

sectionedINT(IO)部門(mén)ID

非空

section_titleVARCHAR(20)非空部門(mén)名稱

【實(shí)例8-1］本例首先創(chuàng)建數(shù)據(jù)庫(kù)staff,然后參照表8-1和表8-2創(chuàng)建數(shù)據(jù)表

staff和section,并在其中插入數(shù)據(jù)。

步驟1登錄MySQL后執(zhí)行以下語(yǔ)句，創(chuàng)建數(shù)據(jù)庫(kù)staff。

CREATEDATABASEstaff;

步驟2選擇數(shù)據(jù)庫(kù)staff,并執(zhí)行以下語(yǔ)句創(chuàng)建數(shù)據(jù)表staff。

CREATETABLEstaff

(

staff_idINT(IO)UNSIGNEDNOTNULLAUTOJNCREMENT,

section_idINT(IO)UNSIGNEDNOTNULL,

positionsJdINT(IO)NOTNULL,

nameVARCHAR(IO)NOTNULL,

sexENUMC男;女)NOTNULL,

phone.numberCHAR(ll)NOTNULL,

moneydecimal(10,2)UNSIGNEDNOTNULLDEFAULT'O.OO1,

entry_dateDATETIMENOTNULL,

PRIMARYKEY(staff_id)

)；

步驟3執(zhí)行以下SQL語(yǔ)句，向staff表中插入數(shù)據(jù)。

INSERTINTO

staff(stafLid,seclion_id,positions_id,name,sex,phone_number,money,entry_date)

VALUES(1,T,T,‘劉長(zhǎng)生男'.,20000,'2018-04-0214:35:52'),

(2,T,'2',‘趙霞，,10000,,2018-04-0314:40:52'),

(3,'2','3','季慶奇','女',,15000,'2018-04-0314:43:52'),

(4,'3','3','李星宇.',15000,,2018-04-0314:45:52,),

(5,'4；3',‘張向陽(yáng),15000,12018-04-0314:47:24'),

(6,'4','8','張旭','男',,10000；2018-04-0314:50:52');

……(詳見(jiàn)教材)

1.普通內(nèi)連接查詢

內(nèi)連接查詢的語(yǔ)法形式為：

SELECT{*|col_list)FROMiable_namel

INNERJOINtable_name2ONcondition;

上述語(yǔ)句中,condition表示連接條件。接下來(lái)通過(guò)實(shí)例具體說(shuō)明內(nèi)連接查詢的

用法。

【實(shí)例8-2]在staff表和section表之間使用內(nèi)連接查詢，從staff表中查詢

staffjd(員工ID)、name(姓名)、sex(悵！])和phone_number(手機(jī)號(hào)),從

section表中查詢section_title(部門(mén)名稱),

由表8-1和表8-2的表結(jié)構(gòu)可知，兩個(gè)表都有相同數(shù)據(jù)類(lèi)型的字段sectionjd,

可以通過(guò)該字段建立聯(lián)系。為此，首先選擇數(shù)據(jù)庫(kù)staff,然后執(zhí)行內(nèi)連接查詢語(yǔ)句，

結(jié)果如下：

mysql>SELECTstaff_id,name,sex,section_title,phonc_number

->FROMstaffINNERJOINsection

->ONstaff.section_id=section.section_id;

+--------------+------------------4------------------4-------------------------+--------------------------------------+

|staffLid|name|sex|section_title|phone_number|

+---------+-----------+-----------+---------------+------------------------+

11I劉長(zhǎng)生I男I總經(jīng)辦113753697300|

12|趙霞|女|總經(jīng)辦

131季慶奇|女|財(cái)務(wù)部113753697302|

14|李星宇|男|銷(xiāo)售部

151張向陽(yáng)|男|研發(fā)部113753697304|

16|張旭|男I研發(fā)部

+----------+-----------+----------+----------------+------------------------+

6rowsinset（0.01sec）

【提示】

上述SQL語(yǔ)句中，使用符號(hào)將表名和字段名拼接起來(lái)，作用是明確指定字

段所屬的數(shù)據(jù)表，不會(huì)因?yàn)樽侄蚊孛斐上到y(tǒng)無(wú)法識(shí)別。

此處，SELECT語(yǔ)句與前面最大的區(qū)別在于：SELECT后面指定的列分別屬于

兩個(gè)不同的表伺時(shí),FROM子句列出了兩個(gè)表兩個(gè)表之間的關(guān)系通過(guò)INNERJOIN

指定；連接條件使用ON子句給出。

另外，使用WHERE子句也可以給出連接條件，以下語(yǔ)句將返回與前面完全相

同的結(jié)果。

SELECTstafflid,name,sex,section_title,phone_numberFROMstaff,section

WHEREstaff.section_id=section.section_id;

使用WHERE子句定義連接條件簡(jiǎn)單明了，但在某些時(shí)候會(huì)影響查詢性能，而

使用INNERJOIN語(yǔ)法能夠確保不會(huì)忘記連接條件。

2.自連接查詢

內(nèi)連接查詢中有一種特殊的查詢，稱為自連接查詢，它是指連接查詢中涉及的

兩張表在物理上是同一張表，但邏輯上可以看成兩張表，語(yǔ)法形式如下：

SELECTtable_alias1.*,table_alias2.*FROMtablc_nameAStable_aliasl

INNERJOINtablc_nameAStable_alias2ONcondition;

上述語(yǔ)句的意義是，將一張表分別命名為table_aliasl和tablc_alias2,然后使用

這兩個(gè)表名進(jìn)行自連接查詢。

【實(shí)例8-3］使用內(nèi)連接查詢語(yǔ)句，從staff表中查詢薪資低于15000的員工

的staffjd（員工ID）、name（姓名）和money（薪資）。

首先選擇數(shù)據(jù)庫(kù)staff,然后執(zhí)行內(nèi)連接查詢語(yǔ)句，結(jié)果如下：

mysql>SELECTs1.staffjd,s1.name,s2.moneyFROMstaffASsi

->INNERJOINstaffASs2ONsl.staff_id=s2.staffjdANDs2.money<15000;

+---------+----------+------------------+

|staffLid|name|money|

+---------+----------+------------------+

I21趙霞|10000.00|

I61張旭|10000.00|

+----------+----------+-----------------+

2rowsinset(0.00sec)

此處查詢的兩個(gè)表是同一個(gè)表staff,為防止產(chǎn)生二義性，使用AS關(guān)鍵字為表

起了別名，staff表第1次以si為別名出現(xiàn)，第2次以s2為別名出現(xiàn)。INNERJOIN

連接兩個(gè)表，并按照第2個(gè)表的money值對(duì)數(shù)據(jù)進(jìn)行過(guò)濾.

8.1.2外連接直詢

〉【教師】組織學(xué)生掃碼觀看”外連接和復(fù)合條件連接查詢“視頻(詳見(jiàn)教材),

讓學(xué)生對(duì)基本知識(shí)有一個(gè)大致的了解

外連接查詢(OUTERJOIN)是以一張表為基表，根據(jù)連接條件，與另外一張

表的每一行進(jìn)行匹配，如果沒(méi)有匹配上，則在相關(guān)聯(lián)的結(jié)果行中，另一張表的所有

選擇列均返回空值。

外連接查詢通常分為兩種：左連接查詢(LEFTJOIN)和右連接查詢(RIGHT

JOIN).其基本語(yǔ)法形式如下：

SELECT(*|col_list)FROMtablc_namel

{LEFTIRIGHT)[OUTER]JOINtabie_name2ONcondition;

本節(jié)以第5章創(chuàng)建的goods表和orders表為操作對(duì)象，講解外連接查詢的應(yīng)用，

在前面的操作中已經(jīng)為goods表插入數(shù)據(jù)，接下來(lái)為orders表插入數(shù)據(jù)。

【實(shí)例8-4]執(zhí)行SQL語(yǔ)句，向orders表中插入數(shù)據(jù)。

步驟1登錄MySQL,并執(zhí)行以下語(yǔ)句，選擇db_sh叩數(shù)據(jù)庫(kù)。

USEdb_shop;

步驟2執(zhí)行以下語(yǔ)句，關(guān)閉orders表的外鍵約束。

SETFOREIGN_KEY_CHECKS=0;

【提示】

對(duì)于先創(chuàng)建表關(guān)系，之后才插入數(shù)據(jù)的表，在其中插入數(shù)據(jù)時(shí)需要先關(guān)閉外鍵

約束，否則會(huì)插入不成功。由于前面在創(chuàng)建orders表時(shí)為其設(shè)置了外鍵約束，此處

先將其關(guān)閉，待插入數(shù)據(jù)后再開(kāi)啟。開(kāi)啟表外鍵約束的語(yǔ)句為SET

FOREIGN_KEY_CHECKS=1;.

步驟3執(zhí)行SQL語(yǔ)句，向orders表中插入數(shù)據(jù)，結(jié)果如下所示。

mysql>INSERTINTOorders(o_id,add_time,goods_id)

->VALUES(1,2018-04-0214:35:52\6),

->(272018-04-0314:40:52；1),

->(3/2018-04-0314:43:57,5),

->(4/2018-04-0314:45:521,1),

->(5/2018-04-0314:47:24,,15),

->(6/2018-04-0314:50:52,,4);

QueryOK,6rowsaffected(0.06sec)

Records:6Duplicates:0Warnings:0

1.左連接查詢

在外連接查詢語(yǔ)句中，LEFTJOIN關(guān)鍵字之前的表稱為左表，左連接查詢會(huì)以

左表為基表，與另外一張表的每一行進(jìn)行匹配，如果符合連接條件，則返回兩張表

相對(duì)應(yīng)的行；如果不符合，則只返回左表中的行，并且其對(duì)應(yīng)的行為一個(gè)空值。

【實(shí)例8-5]執(zhí)行左連接查詢語(yǔ)句，查詢goods表中哪些商品有訂單，哪些商

品沒(méi)有訂單。

由于goods表中的id字段對(duì)應(yīng)orders表中的goods_id字段，此處將這兩個(gè)字段

進(jìn)行比較，如果goods表中的id字段在orders表中有對(duì)應(yīng)的goods_id值，則返回其

對(duì)應(yīng)的。一id值；否則就表示該商品沒(méi)有對(duì)應(yīng)訂單，返回空值。執(zhí)行結(jié)果如下：

mysql>SELECTgoods.id,,orders.o_idFROMgoods

->LEFTJOINordersONgoods.id=orders.goods_id;

+--+----------+------+

|id|name|o_id|

+——+----------+------+

14|休閑西服|6|

151果汁|3|

131水果糖INULL|

19|牛仔褲|NULL|

12|牛奶糖|NULL|

|10|紅樓夢(mèng)|NULL|

18|蘋(píng)果|NULL|

111西游記12|

111西游記14|

171西瓜INULL|

161論語(yǔ)|1|

+---+---------+-------+

11rowsinset(0.04sec)

由結(jié)果可以看出，goods表中id值為2,3,7,8,9和10的商品所對(duì)應(yīng)的訂單

信息為空值，說(shuō)明這幾種商品沒(méi)有客戶下訂單。

2.右連接查詢

在外連接查詢語(yǔ)句中，RIGHTJOIN關(guān)鍵字之后的表稱為右表，右連接查詢會(huì)

以右表為基表，與另外一張表的每一行進(jìn)行匹配，如果符合連接條件，則返回兩張

表相對(duì)應(yīng)的行；如果不符合，則只返回右表中的行，并且其對(duì)應(yīng)的行為一個(gè)空值。

【實(shí)例8-6]執(zhí)行右連接查詢語(yǔ)句，查詢訂單中所對(duì)應(yīng)的商品哪些仍然存在，

哪些已經(jīng)刪除。

與實(shí)例8-5類(lèi)似，依然將商品編號(hào)進(jìn)行比較，所不同的是,此處是將orders表

中的goodsjd與goods表中的id字段逐行進(jìn)行比較，如果在goods表中有對(duì)應(yīng)的記

錄，則返回相應(yīng)的name值，如果沒(méi)有則返回空值。執(zhí)行結(jié)果如下：

mysql>SELECTorders.o_id,orders.add_time,FROMgoods

->RIGHTJOINordersONorders.goods_id=goods.id;

+-----+-------------------+----------+

|o_id|add_time|name|

+------+----------------------+------------+

I112018-04-0214:35:52|論語(yǔ)|

|2|2018-04-0314:40:52|西游記|

|312018-04-0314:43:52|果汁|

|412018-04-0314:45:52|西游記|

|512018-04-0314:47:24|NULL|

I612018-04-0314:50:52|休閑西服|

+-------+----------------------+------------+

6rowsinset(0.00sec)

由結(jié)果可以看出，orders表中o_id值為5的訂單所對(duì)應(yīng)的商品信息為空值，說(shuō)

明該商品已被刪除。

8.1.3復(fù)合條件連接查詢

復(fù)合條件連接杳詢是通過(guò)在連接查詢中添加過(guò)濾條件，以達(dá)到限制查詢結(jié)果和

篩選數(shù)據(jù)的目的，下面通過(guò)實(shí)例進(jìn)行介紹。

【實(shí)例8-7】在staff表和section表中，執(zhí)行內(nèi)連接查詢語(yǔ)句，查詢section表

中部門(mén)名稱為“總經(jīng)辦"的員工ID、姓名、性別和電話號(hào)碼等信息。

步驟1登錄MySQL,并執(zhí)行以下語(yǔ)句，選擇staff數(shù)據(jù)庫(kù)。

USEstaff;

步驟2執(zhí)行以下SQL語(yǔ)句，在staff表和section表中查詢部門(mén)名稱為"總經(jīng)

辦"的員工ID、姓名、性別和電話號(hào)碼，執(zhí)行結(jié)果如下：

mysql>SELECTstaff_id,name,sex,section_title,phone_number

->FROMstaffINNERJOINsection

->ONstaff.section_id=section.section_id

->WHEREsection.section_title='總經(jīng)辦'；

+------+-------+----+----------+-------------+

|staff_id1name|sex|section_title|phone_number|

+------+--------+----+------------4----------------+

11I劉長(zhǎng)生I男|總經(jīng)辦113753697300|

12|趙霞|女|總經(jīng)辦

+-----+-------+----+----------+-------------+

2rowsinset(0.00sec)

可以看出，在限制了部門(mén)名稱為總經(jīng)辦后，此處只返回了在總經(jīng)辦任職的員工

信息?？梢詫⒃摻Y(jié)果與實(shí)例8-2的查詢結(jié)果進(jìn)行匕匕較.

【實(shí)例8-8］執(zhí)行左連接查詢語(yǔ)句，查詢goods表中哪些商品有訂單，哪些商

品沒(méi)有訂單，并使用商品1D號(hào)對(duì)查詢結(jié)果進(jìn)行排序。

步驟1登錄MySQL,并執(zhí)行以下語(yǔ)句，選擇db_shop數(shù)據(jù)庫(kù)。

USEdb_shop;

步驟2執(zhí)行以下SQL語(yǔ)句，在goods表和orders表中查詢哪些商品有訂單，

哪些商品沒(méi)有訂單，并使用商品ID號(hào)對(duì)查詢結(jié)果進(jìn)行排序，執(zhí)行結(jié)果如下：

mysql>SELECTgoods.id,,orders.o_idFROMgoodsLEFTJOINorders

->ONgoods.id=orders.goods_idORDERBYgoods.id;

+---+----------+-------+

|id|name|o_id|

+--+----------+-------+

111西游記12|

111西游記14|

12|牛奶糖|NULL|

13|水果糖INULL|

14|休閑西服|6|

15|果汁13|

16|論語(yǔ)111

17|西瓜|NULL|

I8|蘋(píng)果|NULL|

19|牛仔褲|NULL|

|10I紅樓夢(mèng)INULL|

+----+----------+-------+

11rowsinset(0.01sec)

與實(shí)例8-5的查詢結(jié)果進(jìn)行比較，可以看出，在對(duì)商品信息進(jìn)行排序后，可以

更加方便地查看信息。

【學(xué)生】聆聽(tīng)、思考、記錄

第二節(jié)課

【教師】提出以下問(wèn)題：通過(guò)問(wèn)題導(dǎo)入，

問(wèn)題導(dǎo)入什么是主查詢和子查詢？引導(dǎo)學(xué)生主動(dòng)思

(5min)考，激發(fā)學(xué)生的學(xué)

【學(xué)生】思考、舉手回答

習(xí)興趣

【教師】通過(guò)學(xué)生的回答引入新知，介紹子查詢和合并查詢結(jié)果的相關(guān)知識(shí)

8.2子查詢通過(guò)教師的講

+【教師】組織學(xué)生掃碼觀看“子查詢"視頻(詳見(jiàn)教材)，讓學(xué)生對(duì)基本知解和演示，使學(xué)生

傳授新知了解子查詢和合

識(shí)有一個(gè)大致的了解

(20min)并查詢結(jié)果的相

如果一個(gè)查詢語(yǔ)句中嵌套了一個(gè)或若干個(gè)其他的查詢語(yǔ)句，那么在整個(gè)語(yǔ)句中，關(guān)知識(shí)

外層查詢稱為主查詢，內(nèi)層查詢稱為子查詢或者嵌套查詢。該類(lèi)查詢可以基于一個(gè)

表或多個(gè)表。在此類(lèi)查詢中，系統(tǒng)會(huì)先執(zhí)行子查詢，將子查詢的結(jié)果作為主查詢的

過(guò)濾條件。

子查詢可以應(yīng)用在SELECT,UPDATE和DELETE語(yǔ)句中，并且大多數(shù)子查詢

會(huì)包含在FROM子句或WHERE子句中，在WHERE子句中通常與IN,ANY,ALL

和EXISTS關(guān)鍵字搭配使用，也可以使用條件判斷符。本節(jié)主要講解SELECT語(yǔ)句

中的子查詢。

8.2.1FROM子句中的子查詢

FROM子句中的子查詢會(huì)生成一個(gè)臨時(shí)表，由于FROM子句中的每個(gè)表都必須

有一個(gè)名稱，因此應(yīng)該為臨時(shí)表取一個(gè)別名，語(yǔ)法形式如下：

SELECT(*|col_list)FROM(SELECT*FROMtable_name)AStable_alias

[WHEREcondition];

上述語(yǔ)句中，table_alias表示表別名，下面通過(guò)實(shí)例介紹上述語(yǔ)句的應(yīng)用.

【實(shí)例8-9]執(zhí)行查詢語(yǔ)句，查詢goods表中id值大于3,且num值大于10

的商品的id和name值。

步驟1登錄MySQL,并執(zhí)行以下語(yǔ)句，選擇db_shop數(shù)據(jù)庫(kù)。

USEdb_shop;

步驟2執(zhí)行以下語(yǔ)句，查詢goods表中id值大于3的數(shù)據(jù)記錄，結(jié)果如下：

mysql>SELECT*FROMgoodsWHEREid>3;

+——+-----------4------------------------+---------------4----------------+------------------------------------------------+

|id|type|name|price|num|add_time|

+——+----+---------+------+------+-------------------+

I4|服飾|休閑西服1800.00|10|2018-04-0413:40:40|

I5|飲品I果汁I2.50|7012018-05-0513:40:40|

|6|書(shū)籍|論語(yǔ)|109.00|5012018-01-0313:40:40|

|7|水果|西瓜|1.50|NULL|2018-02-0513:40:40|

|8|水果|蘋(píng)果|3.00|10012018-03-0513:40:40|

|9I服飾I牛仔褲|120.00|1012018-05-0413:40:40|

|10|書(shū)籍|紅樓夢(mèng)|50.50|1512018-05-0613:40:40|

+一一+----+---------+------+------+-------------------+

7rowsinset(0.04sec)

步驟3執(zhí)行嵌套笥旬，將上述語(yǔ)句作為子查詢，在其生成的臨時(shí)表gl中查詢

num值大于10的商品的id和name值，查詢結(jié)果如下：

mysql>SELECTid,namefrom(SELECT*FROMgoodsWHEREid>3)ASgl

->WHEREnum>10;

+_…+-----------------4-

|id|name|

+——+------+

15|果汁I

161論語(yǔ)I

18I蘋(píng)果I

110|紅樓夢(mèng)|

++-------+

4rowsinset(0.00sec)

8.2.2WHERE子句中的子查詢

包含在WHERE子句中的子查詢，其查詢結(jié)果通常是單列數(shù)據(jù)，系統(tǒng)執(zhí)行子查

詢后，子查詢的結(jié)果會(huì)作為主查詢的篩選條件。

1.使用IN關(guān)鍵字的子查詢

當(dāng)子查詢返回的是一個(gè)數(shù)據(jù)集合，主查詢需要返回符合集合中條件的記錄時(shí)，

可以使用1N關(guān)鍵字，語(yǔ)法形式如下：

SELECT{*|col_list}FROMtablc_namcl

WHEREcol_namelIN

(SELECTcol_name2FROMtable_name2[WHEREcondition]);

上述語(yǔ)句中col_namcl為表1中的字段，col_name2為表2中的字段。

【實(shí)例8-10]執(zhí)行查詢語(yǔ)句，查詢orders表中字段goods_id的所有值，然后

使用子查詢結(jié)果拼接WHERE條件，查詢goods_id值對(duì)應(yīng)的所有商品的詳細(xì)信息。

步驟1登錄MySQL,并執(zhí)行以下語(yǔ)句，選擇db_shop數(shù)據(jù)庫(kù)。

USEdb_shop;

步驟2執(zhí)行SQL語(yǔ)句，查詢orders表中字段goods_id的所有值，執(zhí)行結(jié)果如

下：

mysql>SELECTgoods_idFROMorders;

+----------+

|goods_id|

+......................+

|1|

|1|

|4|

|5|

|6|

|15|

++

6rowsinset(0.00sec)

步驟3執(zhí)行SQL語(yǔ)句，使用上述查詢結(jié)果作為子查詢，在goods表中查詢

goods_id值對(duì)應(yīng)的商品的詳細(xì)信息，執(zhí)行結(jié)果如下：

mysql>SELECT*FROMgoodsWHEREidIN(SELECTgoods.idFROMorders);

+--+----+---------+------+------+-------------------+

|id|type|name|price|num|add」ime|

+——+----+---------+------+------+-------------------+

|1I書(shū)籍I西游記I50.40|2012018-01-0113:40:40|

|4|服飾|休閑西服|800.00|1012018-04-0413:40:40|

|5|飲品|果汁|2.50|7012018-05-0513:40:40|

|6|書(shū)籍|論語(yǔ)|109.0015012018-01-0313:40:40|

+----+----+----------+-----+------+-------------------+

4rowsinset(0.04sec)

執(zhí)行結(jié)果成功顯示出訂單中商品的詳細(xì)信息，但由于goods_id為15的商品不

在goods表中，所以沒(méi)有顯示出該商品的信息。

另外，子查詢還可以和NOTIN配合使用，下面通過(guò)實(shí)例進(jìn)行介紹。

【實(shí)例8-11】執(zhí)行SQL語(yǔ)句，依然以前面的查詢結(jié)果作為子查詢，在goods

表中查詢沒(méi)有訂單的商品的詳細(xì)信息，執(zhí)行結(jié)果如下：

mysql>SELECT*FROMgoodsWHEREidNOTIN(SELECTgoods_idFROM

orders);

+----+----+-------+--------+------+-------------------+

|id|type|name|price|num|add_time|

+——+----+-------+--------+------+-------------------+

I2|糖類(lèi)|牛奶糖|7.50|20012018-02-0213:40:40|

|3|糖類(lèi)|水果糖|2.50|10012018-06-0911:20:55|

|7|水果|西瓜|1.50INULL12018-02-0513:40:40|

|8|水果|蘋(píng)果|3.00|10012018-03-0513:40:40|

|9|服飾|牛仔褲|120.00|1012018-05-0413:40:40|

|10I書(shū)籍I紅樓夢(mèng)I50.5011512018-05-0613:40:40|

+——+----+-------+-------+------+-------------------+

6rowsinset(0.04sec)

2.使用ANY、SOME關(guān)鍵字的子查詢

ANY和SOME是同義詞，表示滿足其中任一條件。該類(lèi)查詢會(huì)創(chuàng)建一個(gè)表達(dá)

式對(duì)子查詢的返回值列表進(jìn)行比較，只要滿足子查詢中的任一個(gè)比較條件，就返回

一個(gè)結(jié)果。其語(yǔ)法形式如下：

SELECT(*|col_list)FROMtablc_namel

WHEREcol_namel<any

(SELECTcol_name2FROMtable_name2[WHEREcondition]);

【實(shí)例8-12】執(zhí)行SQL語(yǔ)句，在goods表中查詢2018年4月2號(hào)以后訂單

中的商品信息。

步驟1登錄MySQL,并執(zhí)行以下語(yǔ)句，選擇db.shop數(shù)據(jù)庫(kù)。

USEdb_shop;

步驟2執(zhí)行SQL語(yǔ)句，查詢orders表中2018年4月2號(hào)以后的訂單中的

goods_id的所有值，結(jié)果如下：

mysql>SELECTgoods_idFROMordersWHEREadd_time>'2018-04-02,;

+--------+

Igoods_id|

+--------+

16|

111

15|

111

115|

14|

+--------+

6rowsinset(0.00sec)

步驟3執(zhí)行SQL語(yǔ)句，使用上述查詢結(jié)果作為子查詢，在goods表中查詢

goods_id值對(duì)應(yīng)的商品的詳細(xì)信息，結(jié)果如下：

mysql>SELECT*FROMgoods

->WHEREid=ANY(SELECTgoodsjdFROMordersWHERE

add_time>'2018-04-02');

+----+----+--------+------+------+-------------------+

|id|type|name|price|num|add_time|

+--+----+--------+------+------+-------------------+

|6|書(shū)籍|論語(yǔ)|109.00|5012018-01-0313:40:40|

|1I書(shū)籍I西游記I50.40|2012018-01-0113:40:40|

|5|飲品|果汁|2.50|7012018-05-0513:40:40|

|41服飾|休閑西服1800.00|10|2018-04-0413:40:40|

+--+----+---------+------+-------+------------------+

4rowsinset(0.00sec)

【提示】

由上述查詢結(jié)果可以看出，使用"=ANY"與使用關(guān)鍵字IN的效果實(shí)際上是相

同的，除"="外，ANY關(guān)鍵字前面可以使用的條件判斷符還有"<="

和"〉=",讀者可根據(jù)需要進(jìn)行選擇.

3.使用ALL關(guān)鍵字的子查詢

與ANY和SOME不同，使用關(guān)鍵字ALL的子查詢，表示當(dāng)一條記錄符合子查

詢結(jié)果中所有的條件時(shí)，才會(huì)返回該記錄。其語(yǔ)法形式如下：

SELECT{*|col_list}FROMtable_namel

WHEREcol_namel>All

(SELECTcol_name2FROMtable_name2［WHERE條件］);

【實(shí)例8-13］執(zhí)行以下語(yǔ)句，查詢goods表中id值比子查詢結(jié)果中的最大數(shù)

據(jù)還要大的記錄.

步驟1登錄MySQL,并執(zhí)行以下語(yǔ)句，選擇db_sh叩數(shù)據(jù)庫(kù)。

USEdb_shop;

步驟2執(zhí)行SQL語(yǔ)句，查詢orders表中2018年4月3號(hào)之前的訂單中的

goods_id的所有值，結(jié)果如下：

mysql>SELECTgoods.idFROMordersWHEREadd_time<'2018-04-03';

+----------+

|goods_id|

+----------+

16|

14|

+----------+

2rowsinset(0.00sec)

【提示】

為驗(yàn)證查詢結(jié)果，此處事先將orders表中第6條記錄的添加時(shí)間改為了

2018-04-0114:47:24.

步驟3執(zhí)行SQL語(yǔ)句，使用上述查詢結(jié)果作為子查詢，在goods表中查詢

goods.id值對(duì)應(yīng)的商品的詳細(xì)信息，結(jié)果如下：

mysql>SELECT*FROMgoods

->WHEREid>ALL(SELECTgoodsjdFROMordersWHERE

addjime^O18-04-03');

+——+------------4------------------+--------------------4-----------------+-----------------------------------------------+

|id|type|name|price|num|add_time|

+——+----+-------+--------+------+------------------+

I7|水果|西瓜|1.50INULL12018-02-0513:40:40|

|8|水果|蘋(píng)果|3.00|100R018-03-0513:40:40|

|9|服飾|牛仔褲|120.00|1012018-05-0413:40:40|

|10I書(shū)籍I紅樓夢(mèng)|50.5011512018-05-0613:40:40|

+——+-----+------+--------+------+-------------------+

4rowsinset(0.00sec)

可以看出，查詢結(jié)果中所有記錄的id值都同時(shí)大于子查詢結(jié)果中的4和6。

【提示】

ALL關(guān)鍵字之前可以使用的條件判斷符有">""<="和">="，但一般

不會(huì)使用.

4.使用EXISTS關(guān)鍵字的子查詢

使用EXISTS關(guān)鍵字，系統(tǒng)會(huì)對(duì)子查詢的返回結(jié)果進(jìn)行判斷，如果子查詢至少

返回一行記錄，那么EXISTS的結(jié)果為T(mén)RUE,此時(shí)主查詢語(yǔ)句將會(huì)執(zhí)行；如果子

查詢沒(méi)有返回任^記錄，那么EXISTS的結(jié)果為FALSE,此時(shí)主查詢語(yǔ)句將不會(huì)執(zhí)

行。其語(yǔ)法形式如下：

SELECT{*|col_list)FROMtable_namel

WHEREEXISTS(SELECT*FROMtable_name2);

【實(shí)例8-14]查詢orders表中是否存在goodsjd值為5的記錄,如果存在，

則查詢goods表中id值小于5的記錄。

在選中數(shù)據(jù)庫(kù)db_shop后，執(zhí)行SQL語(yǔ)句，使用EXISTS關(guān)鍵字查詢goods表

中id值小于5的記錄，查詢結(jié)果如下：

mysql>SELECT*FROMgoodsWHEREid<'5'AND

->EXISTS(SELECTo_idFROMordersWHEREgoods_id=,5,);

+——+----+---------+------+------+-------------------+

|id|type|name|price|num|add_time|

+--+----+---------+------+------+-------------------+

|1I書(shū)籍|西游記I50.40|20R018-01-0I13:40:40|

|21糖類(lèi)|牛奶糖|7.50|20012018-02-0213:40:40|

|31糖類(lèi)|水果糖|2.50|10012018-06-0911:20:55|

|4|服飾|休閑西服|800.00|1012018-04-0413:40:40|

+——+----+---------+------+-------+------------------+

4rowsinset(0.02sec)

由查詢結(jié)果可知，子查詢結(jié)果表明orders表中存在goods_id=5的記錄，因此

EXISTS表達(dá)式返回true；主查詢語(yǔ)句接收true之后根據(jù)查詢條件id<5對(duì)goods表

進(jìn)行查詢，返回4條符合條件的記錄。

NOTEXISTS與EXISTS使用方法相同，返回的結(jié)果相反，讀者可自行嘗試.

5.使用條件判斷符的子查詢

在子查詢中，還可以單獨(dú)使用條件判斷符.其語(yǔ)法形式如下：

SELECT{*|col_list}FROMtable_name1

WHEREcol_nameloperators

(SELECTcol_name2FROMtable_name2);

上述語(yǔ)句中，operators表示條件判斷符。

【實(shí)例8-15】查詢goods表中ojd值等于3的商品的詳細(xì)信息。

在選中數(shù)據(jù)庫(kù)db_shop后執(zhí)行SQL語(yǔ)句使用條件判斷符查詢goods表中o_id

值等于3的商品的詳細(xì)信息。查詢結(jié)果如下：

mysql>SELECT*FROMgoods

->WHEREid=(SELECTgoods_idFROMordersWHEREo_id=3);

+--+----+----+------+------+-------------------+

|id|type|name|price|num|add_time|

+--+----+----+------+-------+-------------------+

|5|飲品|果汁|2.50|7012018-05-0513:40:40|

+---+----+----+------+-------+-------------------+

1rowinset(0.06sec)

8.3合并查詢結(jié)果

合并查詢結(jié)果就是使用UNION關(guān)鍵字，將多條查詢語(yǔ)句的結(jié)果合并在一起顯

示。UNION有兩種使用方法，一種是查詢結(jié)果不重復(fù)(過(guò)濾掉重復(fù)的記錄)，另一

種是保留所有查詢結(jié)果。

為便于講解，首先創(chuàng)建兩張表，分別為愛(ài)好音樂(lè)的學(xué)生表(music)和愛(ài)好舞蹈

的學(xué)生表(dance),具體操作見(jiàn)實(shí)例8-16.

【實(shí)例8-16]參照表8-3創(chuàng)建兩張結(jié)構(gòu)相同的表，分別命名為music和dance,

之后為各個(gè)表插入數(shù)據(jù)。

+【教師】通過(guò)多媒體展示"表結(jié)構(gòu)"表格，介紹相關(guān)