第5章 多表操作

學(xué)習(xí)目標(biāo)/Target掌握多表查詢，能夠使用交叉連接、內(nèi)連接、外連接及復(fù)合條件連接進(jìn)行多表查詢掌握子查詢，能夠使用子查詢結(jié)合IN、EXISTS、ANY、ALL及比較運(yùn)算符進(jìn)行查詢掌握外鍵約束的使用，能夠?yàn)楸硖砑油怄I約束和刪除外鍵約束掌握關(guān)聯(lián)表的三種關(guān)聯(lián)關(guān)系，能夠向關(guān)聯(lián)表中添加和刪除數(shù)據(jù)章節(jié)概述/Summary之前章節(jié)對(duì)數(shù)據(jù)的操作都是基于一張數(shù)據(jù)表完成的，即單表操作，然而實(shí)際應(yīng)用中業(yè)務(wù)邏輯較為復(fù)雜，表與表之間可能存在業(yè)務(wù)聯(lián)系，有時(shí)候需要基于兩張或者兩張以上的數(shù)據(jù)表進(jìn)行操作，即多表操作。本章將針對(duì)多表操作的相關(guān)知識(shí)進(jìn)行講解。目錄/Contents01020304多表查詢子查詢外鍵約束上機(jī)實(shí)踐：圖書管理系統(tǒng)的多表操作多表查詢5.1在關(guān)系型數(shù)據(jù)庫中，一張數(shù)據(jù)表通常存儲(chǔ)一個(gè)實(shí)體的信息。當(dāng)兩張或多張數(shù)據(jù)表中存在相同意義的字段時(shí)，如果需要同時(shí)顯示多張數(shù)據(jù)表中的數(shù)據(jù)，便可以通過這些意義相同的字段將不同的數(shù)據(jù)表進(jìn)行連接，并對(duì)連接后的數(shù)據(jù)表進(jìn)行查詢，這樣的查詢通常稱為連接查詢。在MySQL中，連接查詢包括交叉連接查詢、內(nèi)連接查詢、外連接查詢、復(fù)合條件連接查詢，本節(jié)將對(duì)這些連接查詢進(jìn)行講解。5.1.1交叉連接查詢交叉連接（CROSSJOIN）查詢返回的結(jié)果是被連接的兩張數(shù)據(jù)表中所有數(shù)據(jù)行的笛卡爾積。交叉連接的語法格式如下所示。SELECT<字段名>FROM<數(shù)據(jù)表名1>CROSSJOIN<數(shù)據(jù)表名2>;或SELECT<字段名>FROM<數(shù)據(jù)表名1>,<數(shù)據(jù)表名2>;

5.1.1交叉連接查詢上述語法格式中，兩種語法格式的返回結(jié)果相同，其中<字段名>指的是需要查詢的字段名稱；<數(shù)據(jù)表名1>和<數(shù)據(jù)表名2>指的是需要交叉連接的數(shù)據(jù)表的名稱；CROSSJOIN用于連接兩個(gè)要查詢的數(shù)據(jù)表，通過CROSSJOIN語句可以查詢兩個(gè)表中所有的數(shù)據(jù)組合。5.1.1交叉連接查詢例如，對(duì)數(shù)據(jù)庫ems中員工表emp和部門表dept進(jìn)行交叉連接查詢，具體如下。部門表dept用于存儲(chǔ)部門信息，由于在第2章的講解中將部門表刪除了，所以查詢之前需要先創(chuàng)建一個(gè)部門表，并完善部門表中的數(shù)據(jù)，具體SQL語句如下。#創(chuàng)建部門表CREATETABLEdept( deptno INTPRIMARYKEY, dname VARCHAR(20)UNIQUE);#插入部門數(shù)據(jù)INSERTINTOdeptVALUES(10,'總裁辦'),(20,'研究院'),(30,'銷售部'),(40,'運(yùn)營部');5.1.1交叉連接查詢對(duì)員工表和部門表進(jìn)行交叉連接查詢，具體SQL語句如下所示。mysql>SELECT*FROMemp,dept;+-------+--------+--------+------+---------+---------+--------+--------+--------+|empno|ename|job|mgr|sal|comm|deptno|deptno|dname|+-------+--------+--------+------+---------+---------+--------+--------+--------+|9369|張三|保潔|9902|900.00|NULL|20|30|銷售部||9369|張三|保潔|9902|900.00|NULL|20|40|運(yùn)營部||9369|張三|保潔|9902|900.00|NULL|20|20|研究院||9369|張三|保潔|9902|900.00|NULL|20|10|總裁辦|…因篇幅有限，此處省略了其他的記錄+-------+--------+--------+------+---------+---------+--------+--------+--------+48rowsinset(0.00sec)5.1.1交叉連接查詢內(nèi)連接（INNERJOIN）查詢又稱簡單連接查詢或自然連接查詢，是常見的連接查詢。內(nèi)連接根據(jù)連接條件，可以對(duì)交叉連接查詢的部分結(jié)果進(jìn)行篩選，僅篩選出兩張表中相互匹配的記錄。內(nèi)連接查詢的語法格式如下所示。SELECT查詢字段FROM數(shù)據(jù)表1[INNER]JOIN數(shù)據(jù)表2ON匹配條件;5.1.2內(nèi)連接查詢上述語法格式中，INNERJOIN用于連接2張數(shù)據(jù)表，其中INNER可以省略；ON用于指定查詢的匹配條件，即同時(shí)匹配2張數(shù)據(jù)表的條件。由于內(nèi)連接查詢是對(duì)2張數(shù)據(jù)表進(jìn)行操作，所以需要在匹配條件中指定所操作的字段來源于哪一張數(shù)據(jù)表，如果給數(shù)據(jù)表設(shè)置了別名，也可以通過別名指定數(shù)據(jù)表。5.1.2內(nèi)連接查詢例如，查詢已經(jīng)分配了部門（部門號(hào)不為NULL）的員工的信息，員工信息只需要顯示員工姓名和對(duì)應(yīng)部門的名稱，具體SQL語句如下所示。SELECTename,dnameFROMempeJOINdeptdONe.deptno=d.deptno;5.1.2內(nèi)連接查詢執(zhí)行CONCAT()函數(shù)會(huì)返回函數(shù)參數(shù)連接之后的字符串。如執(zhí)行CONCAT('a','_'，'b')，會(huì)返回字符串'a_b'。如果在一個(gè)連接查詢中，涉及到的兩張數(shù)據(jù)表是同一張數(shù)據(jù)表，這種查詢稱為自連接查詢。自連接是一種特殊的內(nèi)連接，它是指相互連接的數(shù)據(jù)表在物理上為同一張數(shù)據(jù)表，但邏輯上分為兩張數(shù)據(jù)表。自連接查詢5.1.2內(nèi)連接查詢例如，查詢員工王五所在部門的所有員工信息。查詢時(shí)可以使用自連接查詢實(shí)現(xiàn)，具體SQL語句如下所示。SELECTe1.*FROMempe1JOINempe2ONe1.deptno=e2.deptnoWHEREe2.ename='王五';5.1.2內(nèi)連接查詢內(nèi)連接的查詢結(jié)果是符合連接條件的記錄，然而有時(shí)在查詢時(shí)，除了要查詢出符合條件的數(shù)據(jù)外，還需要查詢出其中一張數(shù)據(jù)表中符合條件之外的其他數(shù)據(jù)，此時(shí)就需要使用外連接查詢。外連接查詢的語法格式如下。SELECT所查字段FROM數(shù)據(jù)表1LEFT|RIGHT[OUTER]JOIN數(shù)據(jù)表2ON匹配條件5.1.3外連接查詢外連接查詢分為左連接（LEFTJOIN）查詢和右連接（RIGHTJOIN）查詢，一般稱呼上述語法格式中的數(shù)據(jù)表1被稱為左表，數(shù)據(jù)表2被稱為右表。使用左連接查詢和右連接查詢的區(qū)別如下。(1)LEFTJOIN：返回左表中的所有記錄和右表中符合連接條件的記錄。(2)RIGHTJOIN：返回右表中的所有記錄和左表中符合連接條件的記錄。5.1.3外連接查詢左連接查詢左連接查詢的結(jié)果包括LEFTJOIN子句中左表的所有記錄，以及右表中滿足連接條件的記錄。如果左表的某條記錄在右表中不存在，則右表中對(duì)應(yīng)字段的值顯示為NULL。右連接查詢右連接查詢的結(jié)果包括RIGHTJOIN子句中右表的所有記錄，以及左表中滿足連接條件的記錄。如果右表的某條記錄在左表中沒有匹配，則左表中對(duì)應(yīng)字段的值顯示為NULL。5.1.3外連接查詢例如，查詢所有部門名稱及部門對(duì)應(yīng)員工的姓名。因?yàn)樾枰樵兂鏊胁块T的名稱，查詢時(shí)可以使用左連接查詢，將部門表作為查詢中的左表，具體SQL語句如下所示。SELECTd.dname,e.enameFROMdeptdLEFTJOINempeONe.deptno=d.deptno;例如，查詢所有員工姓名及對(duì)應(yīng)部門的名稱，沒有分配部門的員工也需要查詢出來。因?yàn)樾枰樵兂鏊袉T工的名稱，查詢時(shí)可以使用右連接查詢，將員工表作為查詢中的右表，具體SQL語句如下所示。SELECTd.dname,e.enameFROMdeptdRIGHTJOINempeONe.deptno=d.deptno;5.1.3外連接查詢復(fù)合條件連接查詢是指在連接查詢的過程中，通過添加過濾條件限制執(zhí)行結(jié)果，使執(zhí)行結(jié)果更加精確。5.1.4復(fù)合條件連接查詢例如，查詢所有員工信息，員工信息包含員工所在部門的名稱，并且按員工的工資降序排序。在查詢時(shí)，可以根據(jù)deptno字段，使用左連接將部門表和員工表進(jìn)行關(guān)聯(lián)查詢，并使用ORDERBY根據(jù)sal字段的值對(duì)查詢結(jié)果進(jìn)行排序，具體SQL語句如下所示。SELECTe.*,d.dnameFROMempeLEFTJOINdeptdONe.deptno=d.deptnoORDERBYe.salDESC;子查詢5.2子查詢是指一個(gè)查詢語句嵌套在另一個(gè)語句內(nèi)部的查詢，當(dāng)某個(gè)語句執(zhí)行所需要的過濾條件是另外一個(gè)SELECT語句的結(jié)果時(shí)，可以使用子查詢。子查詢通常在WHERE子句中結(jié)合操作符一起使用，操作符可以是IN、EXISTS、ANY、ALL、比較運(yùn)算符，本節(jié)將結(jié)合這幾種操作符的子查詢進(jìn)行講解。5.2.1IN關(guān)鍵字結(jié)合子查詢例如，查詢工資大于2900的員工所屬部門。查詢時(shí)可以先通過子查詢返回工資大于2900的員工所在部門的編號(hào)，接著使用IN關(guān)鍵字根據(jù)部門編號(hào)查詢部門信息，具體SQL語句如下所示。mysql>SELECT*FROMdeptWHEREdeptnoIN(SELECTdeptnoFROMempWHEREsal>2900);例如，查詢工資小于2900的員工所在的部門信息，具體SQL語句如下所示。mysql>SELECT*FROMdeptWHEREdeptnoNOTIN(SELECTdeptnoFROMempWHEREsal>2900);5.2.1IN關(guān)鍵字結(jié)合子查詢IN關(guān)鍵字結(jié)合子查詢使用時(shí)，需要內(nèi)層子查詢語句返回的結(jié)果是一個(gè)數(shù)據(jù)列，這個(gè)數(shù)據(jù)列中的值供外層語句進(jìn)行比較操作。EXISTS關(guān)鍵字用于判斷子查詢的結(jié)果集是否為空，若子查詢的結(jié)果集不為空，返回TRUE，否則返回FALSE。使用EXISTS關(guān)鍵字結(jié)合子查詢進(jìn)行查詢時(shí)，會(huì)先執(zhí)行外層查詢語句，再根據(jù)EXISTS關(guān)鍵字后面子查詢的查詢結(jié)果，判斷是否保留外層語句查詢出的記錄，EXISTS的判斷結(jié)果為TRUE時(shí)，保留對(duì)應(yīng)的記錄，否則去除記錄。5.2.2EXISTS關(guān)鍵字結(jié)合子查詢例如，查詢工資大于2900的員工所在的部門信息。首先查詢出部門的所有信息，然后通過子查詢篩選出工資大于2900的員工信息，接著使用EXISTS關(guān)鍵字符合子查詢結(jié)果的記錄返回；具體SQL語句如下所示。SELECT*FROMdeptWHEREEXISTS(SELECT*FROMempWHEREemp.deptno=dept.deptnoANDemp.sal>2900);5.2.2EXISTS關(guān)鍵字結(jié)合子查詢使用EXISTS關(guān)鍵字結(jié)合子查詢，和使用IN關(guān)鍵字結(jié)合子查詢的結(jié)果一致，但在表數(shù)據(jù)不同時(shí)，這兩種方式的性能也不同。當(dāng)外表數(shù)據(jù)量比較大，內(nèi)表數(shù)據(jù)量比較小的時(shí)候，適合使用IN關(guān)鍵字結(jié)合子查詢進(jìn)行查詢。當(dāng)外表數(shù)據(jù)量比較小，內(nèi)表數(shù)據(jù)量比較大的情況，適合使用EXISTS關(guān)鍵字結(jié)合子查詢進(jìn)行查詢。5.2.2EXISTS關(guān)鍵字結(jié)合子查詢ANY關(guān)鍵字表示“任意一個(gè)”的意思，必須和比較操作符一起使用，例如ANY和>結(jié)合起來使用，表示大于任意一個(gè)。ANY關(guān)鍵字結(jié)合子查詢時(shí)，表示子查詢的查詢結(jié)果集中的任一查詢結(jié)果，例如“值1>ANY(子查詢)”，比較值1是否大于子查詢返回的結(jié)果集中任意一個(gè)結(jié)果。5.2.3ANY關(guān)鍵字結(jié)合子查詢例如，查詢部門編號(hào)為10的員工信息，要求查詢到的員工信息中，工資都高于部門編號(hào)為20的部門中的最低工資。查詢時(shí)可以先使用子查詢語句查詢出部門編號(hào)為20的部門中所有員工工資，接著查詢部門編號(hào)為10的部門中所有員工信息，最后使用ANY連接兩者的工資進(jìn)行比較。具體SQL語句如下所示。SELECT*FROMempWHEREdeptno=10ANDsal>ANY(SELECTsalFROMempWHEREdeptno=20);5.2.3ANY關(guān)鍵字結(jié)合子查詢ANY關(guān)鍵字表示“所有”的意思，ALL關(guān)鍵字結(jié)合子查詢時(shí)，表示子查詢的所有查詢集中是所有結(jié)果，例如“值1>ALL(子查詢)”，比較值1是否大于子查詢返回的結(jié)果集中所有結(jié)果。5.2.4ALL關(guān)鍵字結(jié)合子查詢例如，查詢部門編號(hào)為10的員工信息，要求查詢到的員工信息中，工資都高于部門編號(hào)為20的部門中的最高工資。查詢時(shí)可以使用子查詢將部門編號(hào)為20的所有員工工資查詢出來，然后將部門編號(hào)為10的所有員工工資與子查詢的結(jié)果進(jìn)行比較，只要大于子查詢中的任意一個(gè)值，就是符合查詢條件的記錄，具體SQL語句如下所示。SELECT*FROMempWHEREdeptno=10ANDsal>ALL(SELECTsalFROMempWHEREdeptno=20);5.2.4ALL關(guān)鍵字結(jié)合子查詢前面講解的ANY關(guān)鍵字和ALL關(guān)鍵字的子查詢中使用了比較運(yùn)算符>，除了>運(yùn)算符，子查詢中還可以使用其他的比較運(yùn)算符，如<、=、!=等。5.2.5比較運(yùn)算符結(jié)合子查詢例如，查詢與王五職位相同的員工信息。查詢時(shí)可以先使用子查詢獲取王五的職位，接著根據(jù)子查詢的結(jié)果篩選出職位和王五相同的員工信息，具體SQL語句如下所示。SELECT*FROMempWHEREjob=(SELECTjobFROMempWHEREename='王五')ANDename!='王五';5.2.5比較運(yùn)算符結(jié)合子查詢一般情況下，表連接查詢都可以用子查詢替換，但反過來卻不一定適用。子查詢相對(duì)比較靈活、方便、形式多樣，適合作為查詢的篩選條件，而表連接查詢更適合查看連接表的數(shù)據(jù)。5.2.5比較運(yùn)算符結(jié)合子查詢外鍵約束5.3實(shí)際開發(fā)的項(xiàng)目中，一個(gè)健壯數(shù)據(jù)庫中的數(shù)據(jù)一定有很好的參照完整性。例如，員工管理系統(tǒng)中有員工表和部門表，如果員工表的部門編號(hào)字段使用了20的部門編號(hào)，部門表中的20編號(hào)卻被刪除了，這樣就會(huì)產(chǎn)生垃圾數(shù)據(jù)或者錯(cuò)誤數(shù)據(jù)。為了保證數(shù)據(jù)的完整性，可以在員工表中添加外鍵約束。本節(jié)將對(duì)外鍵約束進(jìn)行講解。5.3.1添加外鍵約束

外鍵是數(shù)據(jù)表中的一個(gè)特殊字段，它引用另一張數(shù)據(jù)表中的一列或多列，被引用的列應(yīng)該具有主鍵約束或唯一性約束。對(duì)于兩個(gè)具有關(guān)聯(lián)關(guān)系的數(shù)據(jù)表來說，相關(guān)聯(lián)字段中主鍵所在的數(shù)據(jù)表就是主表，外鍵所在的數(shù)據(jù)表就是從表。5.3.1添加外鍵約束上述語法格式中，ADDCONSTRAINT表示添加約束；外鍵名稱是可選參數(shù)，用來指定添加的外鍵約束的名稱；FOREIGNKEY表示外鍵約束；使用REFERENCES指定創(chuàng)建的外鍵引用哪個(gè)表的主鍵。ALTERTABLE從表名ADDCONSTRAINT[外鍵名稱]FOREIGNKEY(外鍵字段名)REFERENCES主表名(主鍵字段名);在MySQL中為從表添加外鍵約束的語法格式如下。根據(jù)添加外鍵約束的語法格式，給員工表emp添加外鍵約束，具體SQL語句如下所示。ALTERTABLEempADDCONSTRAINTfk_deptnoFOREIGNKEY(deptno)REFERENCESdept(deptno);添加外鍵成功之后，可以使用SHOWCREATETABLE語句查看員工表emp的創(chuàng)建語句，查詢語句如下所示。5.3.1添加外鍵約束SHOWCREATETABLEemp;在為表添加外鍵約束時(shí)，需要注意以下情況：（1）建立外鍵的表必須使用InnoDB引擎（默認(rèn)的存儲(chǔ)引擎），不能是臨時(shí)表，因?yàn)樵贛ySQL中只有InnoDB引擎才允許使用外鍵。（2）定義的外鍵名稱不能加引號(hào)，如CONSTRAINT'FK_ID'或CONSTRAINT"FK_ID"都是錯(cuò)誤的。（3）外鍵所在列的數(shù)據(jù)類型必須和主表中主鍵對(duì)應(yīng)列的數(shù)據(jù)類型相同。5.3.1添加外鍵約束多學(xué)一招建立外鍵是為了保證數(shù)據(jù)的完整性和統(tǒng)一性，但如果主表中的數(shù)據(jù)被刪除或被修改，從表中對(duì)應(yīng)的數(shù)據(jù)也應(yīng)該被刪除或被修改，否則數(shù)據(jù)庫中會(huì)存在很多無意義的垃圾數(shù)據(jù)。MySQL可以在建立外鍵時(shí)添加ONDELETE或ONUPDATE子句來告訴數(shù)據(jù)庫，怎樣避免垃圾數(shù)據(jù)的產(chǎn)生。多學(xué)一招參數(shù)名稱功能描述CASCADE主表中刪除或更新記錄時(shí)，同時(shí)自動(dòng)刪除或更新從表中對(duì)應(yīng)的記錄SETNULL主表中刪除或更新記錄時(shí)，使用NULL值替換從表中對(duì)應(yīng)的記錄（不適用于已標(biāo)記為NOTNULL的字段）NOACTION拒絕主表刪除或修改外鍵關(guān)聯(lián)列RESTRICT拒絕主表刪除或修改外鍵關(guān)聯(lián)列。（在不定義ONDELETE和ONUPDATE子句時(shí)，這是默認(rèn)設(shè)置，也是最安全的設(shè)置）添加外鍵約束的參數(shù)說明建立外鍵時(shí)避免垃圾數(shù)據(jù)的語法格式如下。ALTERTABLE從表名ADDCONSTRAINT[外鍵名稱]FOREIGNKEY(外鍵字段名)REFERENCES外表表名(主鍵字段名);[ONDELETE{CASCADE|SETNULL|NOACTION|RESTRICT}][ONUPDATE{CASCADE|SETNULL|NOACTION|RESTRICT}]實(shí)際開發(fā)中，需要根據(jù)實(shí)體的內(nèi)容設(shè)計(jì)數(shù)據(jù)表，實(shí)體間會(huì)有各種關(guān)聯(lián)關(guān)系，因此數(shù)據(jù)表之間也存在著各種關(guān)聯(lián)關(guān)系。下面對(duì)數(shù)據(jù)表的關(guān)聯(lián)關(guān)系、關(guān)聯(lián)表添加數(shù)據(jù)、關(guān)聯(lián)表刪除數(shù)據(jù)進(jìn)行講解。5.3.2操作關(guān)聯(lián)表213一對(duì)一關(guān)系的兩張數(shù)據(jù)表建立外鍵時(shí)，要分清主從關(guān)系。在數(shù)據(jù)表的主從關(guān)系中，從表需要主表的存在才有意義。一對(duì)一表之間的關(guān)系是通過外鍵建立的。在多對(duì)一的表關(guān)系中，應(yīng)該將外鍵添加在“多”的一方，否則會(huì)造成數(shù)據(jù)的冗余。多對(duì)一通常情況下，為了實(shí)現(xiàn)多對(duì)多關(guān)聯(lián)關(guān)系需要定義一張中間表（稱為連接表），中間表會(huì)存在兩個(gè)外鍵。多對(duì)多5.3.2操作關(guān)聯(lián)表數(shù)據(jù)表的關(guān)聯(lián)關(guān)系需要注意的是，一對(duì)一關(guān)聯(lián)關(guān)系在數(shù)據(jù)庫中并不常見，因?yàn)橐赃@種方式存儲(chǔ)的信息通常會(huì)放在一個(gè)表中。在實(shí)際開發(fā)中，一對(duì)一關(guān)聯(lián)關(guān)系可以應(yīng)用于如下場景。（1）分割具有很多列的表。（2）由于安全原因而隔離表的一部分。（3）保存臨時(shí)數(shù)據(jù)，并且可以毫不費(fèi)力地通過刪除保存臨時(shí)數(shù)據(jù)的表，而刪除這些數(shù)據(jù)。5.3.2操作關(guān)聯(lián)表

例如，在員工表emp的deptno字段上添加外鍵約束，引用部門表dept的主鍵字段deptno，如此就通過外鍵加強(qiáng)了員工表和部門表數(shù)據(jù)之間的關(guān)聯(lián)。5.3.2操作關(guān)聯(lián)表員工表和部門表數(shù)據(jù)之間的關(guān)聯(lián)

已經(jīng)為員工表emp添加外鍵約束。此時(shí)員工表emp和部門表dept之間是多對(duì)一的關(guān)聯(lián)關(guān)系。下面演示在這兩個(gè)關(guān)聯(lián)表中添加數(shù)據(jù)，具體如下。5.3.2操作關(guān)聯(lián)表（1）往主表dept中插入數(shù)據(jù)。因?yàn)閺谋韊mp的外鍵列只能插入所引用的列（部門表的deptno字段）中存在的值，所以如果要為兩個(gè)數(shù)據(jù)表添加數(shù)據(jù)，就需要先為主表dept添加數(shù)據(jù)，插入數(shù)據(jù)的SQL語句如下。INSERTINTOdeptVALUES(50,'人力資源部');5.3.2操作關(guān)聯(lián)表（2）往從表emp中插入數(shù)據(jù)。主表中添加的數(shù)據(jù)中，主鍵deptno的值包含10、20、30、40和50，由于員工表emp的外鍵引用部門表的主鍵deptno，因此在往員工表emp中添加數(shù)據(jù)時(shí)，其deptno字段的值只能是10、20、30、40和50，不能使用其他的值，具體語句如下。INSERTINTOempVALUES(9966,'八戒','運(yùn)營專員',9839,3000,2000,40);INSERTINTOempVALUES(9999,'悟空','人事專員',9982,3000,NULL,50);5.3.2操作關(guān)聯(lián)表數(shù)據(jù)插入成功后，如果要查詢?nèi)肆Y源部有哪些員工，可以使用連接查詢完成，也可以使用子查詢完成。例如使用內(nèi)連接查詢完成查詢需求，具體SQL語句及執(zhí)行結(jié)果如下所示。mysql>SELECTe.*,d.dnameFROMempe,deptd->WHEREe.deptno=d.deptnoANDd.dname='人力資源部';+-------+-------+----------+------+---------+------+--------+------------+|empno|ename|job|mgr|sal|comm|deptno|dname|+-------+-------+----------+------+---------+------+--------+------------+|9999|悟空|人事專員|9982|3000.00|NULL|50|人力資源部|+-------+-------+----------+------+---------+------+--------+------------+1rowinset(0.00sec)5.3.2操作關(guān)聯(lián)表從上述執(zhí)行結(jié)果可以得出，人力資源部只有1名員工。需要注意的是，外鍵約束是為了保證數(shù)據(jù)的完整性和統(tǒng)一性，主表和從表中進(jìn)行數(shù)據(jù)的新增、編輯、刪除時(shí)需要遵循外鍵約束的要求，但是對(duì)數(shù)據(jù)的查詢沒有約束性。5.3.2操作關(guān)聯(lián)表除了給關(guān)聯(lián)表添加數(shù)據(jù)，某些情況下也存在刪除關(guān)聯(lián)表中數(shù)據(jù)的需求。例如，因?yàn)楣窘M織架構(gòu)調(diào)整，需要取消人力資源部，此時(shí)就需要在數(shù)據(jù)庫中將人力資源部刪除。下面演示刪除關(guān)聯(lián)表中部門表的數(shù)據(jù)，具體如下。5.3.2操作關(guān)聯(lián)表

由于員工表emp和部門表dept之間使用外鍵進(jìn)行了關(guān)聯(lián)，主表dept中已經(jīng)被引用的值不能直接刪除。如果要?jiǎng)h除人力資源部，需要先將人力資源部中的員工刪除，或者轉(zhuǎn)移到其他部門，或者不分配部門（部門編號(hào)設(shè)置為NULL）。在此選擇先刪除人力資源部中的員工，再刪除部門表中的人力資源部。5.3.2操作關(guān)聯(lián)表（1）刪除從表emp中屬于人力資源部的員工信息，具體SQL語句如下所示。

DELETEFROMempWHEREdeptno=(SELECTdeptnoFROMdeptWHEREdname='人力資源部');5.3.2操作關(guān)聯(lián)表（2）刪除主表dept中的數(shù)據(jù)。此時(shí)從表emp中已經(jīng)沒有數(shù)據(jù)引用主表dept主鍵值為人力資源部的記錄，可以刪除主表dept中部門名稱為人力資源部的記錄，具體SQL語句及執(zhí)行結(jié)果如下所示。DELETEFROMdeptWHEREdname='人力資源部';5.3.2操作關(guān)聯(lián)表如果刪除關(guān)聯(lián)表的數(shù)據(jù)時(shí)，沒有先刪除從表中有關(guān)聯(lián)的數(shù)據(jù)，而直接刪除主表的數(shù)據(jù)，會(huì)刪除失敗。例如，直接刪除部門表dept中名稱為運(yùn)營部的記錄，具體SQL語句及執(zhí)行結(jié)果如下所示。ERROR1451(23000):Cannotdeleteorupdateaparentrow:aforeignkeyconstraintfails(`ems`.`emp`,CONSTRAINT`fk_deptno`FOREIGNKEY(`deptno`)REFERENCES`dept`(`deptno`))由此運(yùn)行結(jié)果可以看出，執(zhí)行刪除語句報(bào)錯(cuò)了。說明在兩個(gè)具有關(guān)聯(lián)關(guān)系的表中刪除數(shù)據(jù)時(shí)，需要先解除從表中外鍵對(duì)主表中主鍵值的引用，然后再刪除主表中的數(shù)據(jù)，否則會(huì)報(bào)錯(cuò)。5.3.2操作關(guān)聯(lián)表

在實(shí)際開發(fā)中，根據(jù)業(yè)務(wù)邏輯的需求，需要解除兩個(gè)表之間的關(guān)聯(lián)關(guān)系時(shí)，就需要?jiǎng)h除外鍵約束。刪除外鍵約束的語法格式如下。5.3.3刪除外鍵約束ALTERTABLE表名DROPFOREIGNKEY外鍵名;演示外鍵約束的刪除，例如將員工表emp中的外鍵約束刪除，具體SQL語句如下。ALTERTABLEempDROPFOREIGNKEYfk_deptno;上機(jī)實(shí)踐：圖書管理系統(tǒng)的多表操作5.4上機(jī)實(shí)踐實(shí)踐需求2：查詢價(jià)格比《西游記》的價(jià)格高的圖書信息，圖書信息只需顯示圖書名稱和圖書價(jià)格。實(shí)踐需求1：查詢張三當(dāng)前借閱的圖書信息，圖書信息只需顯示借閱人編號(hào)、借閱人名稱、圖書名稱和借閱時(shí)間。動(dòng)手實(shí)踐1：根據(jù)圖書表book的borrower_id字段和用戶表user的name字段將表book和表user進(jìn)行關(guān)聯(lián)，并查詢出user表中name值為張三的記錄，查詢出的記錄只返回user表的id、name字段的值，以及book表的borrower、name、bookname、b.borrow_time字段的值，具體的SQL語句如下所示。SELECTu.id,borrower,bookname,b.borrow_timeFROMbookb,useruWHEREb.borrower_id=u.idAND='張三';動(dòng)手實(shí)踐2：首先查詢圖書表book中name的值為西游記的圖書價(jià)格，將該查詢作為子查詢；然后查詢價(jià)格大于子查詢結(jié)果的圖書信息，查詢出的圖書信息只返回name、price字段的值，具體的SQL語句如下所示。SELECTname,priceFROMbookWHEREprice>(SELECTpriceFROMbookWHEREname='西游記');上機(jī)實(shí)踐實(shí)踐需求3：查詢高于平均價(jià)的圖書信息。查詢價(jià)格比所有圖書的平均價(jià)格還低的圖書信息，圖書信息只需顯示圖書名稱和圖書價(jià)格。實(shí)踐需求4：根據(jù)圖書狀態(tài)查詢同類狀態(tài)的圖書。查詢圖書狀態(tài)和《三國演義》相同的圖書信息，圖書信息只需顯示圖書名稱、圖書價(jià)格和狀態(tài)。動(dòng)手實(shí)踐3：首先查詢圖書表book中price字段的平均值，將該查詢作為子查詢；然后查詢價(jià)格小于于子查詢結(jié)果的圖書信息