《MySQL數(shù)據(jù)庫(kù)應(yīng)用實(shí)戰(zhàn)教程（微課版）》第04章-使用數(shù)據(jù)庫(kù)圖書借閱數(shù)據(jù)庫(kù)

項(xiàng)目4使用數(shù)據(jù)庫(kù)

——圖書借閱數(shù)據(jù)庫(kù)任務(wù)1數(shù)據(jù)操縱任務(wù)1數(shù)據(jù)操縱4.1.1【實(shí)訓(xùn)4–1】數(shù)據(jù)插入4.1.2【實(shí)訓(xùn)4–2】數(shù)據(jù)更新4.1.3【實(shí)訓(xùn)4–3】數(shù)據(jù)刪除4.1.4數(shù)據(jù)操縱與數(shù)據(jù)約束任務(wù)2理解數(shù)據(jù)完整性約束任務(wù)3簡(jiǎn)單數(shù)據(jù)查詢?nèi)蝿?wù)4復(fù)雜數(shù)據(jù)查詢4.1.1【實(shí)訓(xùn)4–1】數(shù)據(jù)插入根據(jù)Jitor校驗(yàn)器的要求，在dbForge上完成“【實(shí)訓(xùn)4–1】數(shù)據(jù)插入”省略列名列表，指定主鍵值省略列名列表，主鍵值為null，自動(dòng)增量列出所有列，主鍵值為null，自動(dòng)增量Insertinto表名values(1,值1,值2…);Insertinto

表名values(null,值1,值2…);Insert

into(主鍵,列1,列2…)表名values(null,值1,值2…);Insertintolib_uservalues(1,'2249739484','管理員','admin','123456',2,'M',);Insertintolib_uservalues(null,'2249733829','杰克','jack','123456',1,'M',);Insertintolib_user(id_lib_user,col_rfid,col_name,col_account,col_password,col_type,col_sex,col_mobile)values(null,'2249731839','艾米','amy','123456',1,'F',);【實(shí)訓(xùn)4-1】4.1.1【實(shí)訓(xùn)4–1】數(shù)據(jù)插入（續(xù)）列出所有列，不含自動(dòng)增量的主鍵僅列出所有非空列，不含自動(dòng)增量的主鍵下述3類列可以省略自增量的列應(yīng)該省略，其值由程序根據(jù)規(guī)則自動(dòng)填入?；蛴糜胣ull作為它的值允許為空的列，省略時(shí)，其值為空。有默認(rèn)約束的列，省略時(shí)，其值為默認(rèn)值Insert

into(列1,列2…)表名values(值1,值2…);Insert

into(列1,…)表名values(值1,…);Insertintolib_user(col_rfid,col_name,col_account,col_password,col_type,col_sex,col_mobile)values('2249738325','張三','zhangs','123456',0,'M',);Insertintolib_user(col_rfid,col_name,col_account,col_password)values('2249738303','李四','lisi','123456');4.1.2【實(shí)訓(xùn)4–2】數(shù)據(jù)更新根據(jù)Jitor校驗(yàn)器的要求，在dbForge上完成“【實(shí)訓(xùn)4–2】數(shù)據(jù)更新”更新指定行的一列數(shù)據(jù)更新指定行的多列數(shù)據(jù)Update表名set列名=值whereid=主鍵;Update表名set列名1=值1,列名2=值2…

whereid=主鍵;Updatelib_usersetcol_name='王五'whereid_lib_user=5;Updatelib_usersetcol_account='wangwu',col_password='abcde'whereid_lib_user=5;【實(shí)訓(xùn)4-2】4.1.2【實(shí)訓(xùn)4–2】數(shù)據(jù)更新（續(xù)）更新多行數(shù)據(jù)更新所有行的數(shù)據(jù)這時(shí)沒有where子句，將更新表中所有行的數(shù)據(jù)，因此操作時(shí)需要特別謹(jǐn)慎。Update表名set列名1=值1,列名2=值2…

where條件;Update表名set列名1=值1,列名2=值2…;Updatelib_usersetcol_password=col_accountwherecol_type=0;Updatelib_usersetcol_password='abc123';4.1.3【實(shí)訓(xùn)4–3】數(shù)據(jù)刪除根據(jù)Jitor校驗(yàn)器的要求，在dbForge上完成“【實(shí)訓(xùn)4–3】數(shù)據(jù)刪除”刪除指定的一行刪除部分行刪除所有行——Delete語(yǔ)句刪除所有行——Truncate語(yǔ)句Deletefrom表名whereid=主鍵;Deletefrom表名where條件;Deletefrom表名;Truncate表名;Deletefromlib_userwhereid_lib_user=2;Deletefromlib_lendingwherecol_returned_date<'2021-12-30';Deletefromlib_lending;Truncate表名;【實(shí)訓(xùn)4-3】4.1.4數(shù)據(jù)操縱與數(shù)據(jù)約束任務(wù)2理解數(shù)據(jù)完整性約束任務(wù)1數(shù)據(jù)操縱任務(wù)2理解數(shù)據(jù)完整性約束4.2.1實(shí)體完整性約束（主鍵約束）4.2.2參照完整性約束（外鍵約束）4.2.3其他完整性約束任務(wù)3簡(jiǎn)單數(shù)據(jù)查詢?nèi)蝿?wù)4復(fù)雜數(shù)據(jù)查詢4.2.1實(shí)體完整性約束（主鍵約束）主鍵應(yīng)當(dāng)是對(duì)用戶沒有意義的，在理論上，只要是具有唯一性的列都可以作為主鍵，例如“身份證號(hào)”“學(xué)號(hào)”等屬性，但實(shí)際上通行的做法是采用無(wú)業(yè)務(wù)含義的主鍵主鍵應(yīng)該是單屬性的，以便提高查詢和連接操作的效率。在理論上，允許多屬性的組合作為主鍵，但實(shí)際上通行的做法是采用單屬性的主鍵主鍵的值應(yīng)當(dāng)由計(jì)算機(jī)自動(dòng)生成，手動(dòng)輸入主鍵的值既不能保證它的唯一性，也極大地增加大地增加了輸入工作的難度永遠(yuǎn)也不要更新主鍵，主鍵的作用只是標(biāo)識(shí)一行，沒有其他的用途，所以也就沒有理由，更沒有必要去更新它。如果更新主鍵，那么還要更新相應(yīng)的外鍵，這樣容易導(dǎo)致錯(cuò)誤4.2.2參照完整性約束（外鍵約束）外鍵的值只能取被參照的表的主鍵的值外鍵的值允許為空外鍵的值允許為空，表示主從表之間的聯(lián)系不是非常緊密。例如學(xué)生的籍貫外鍵，在輸入學(xué)生的入學(xué)數(shù)據(jù)時(shí)，可以暫時(shí)不輸入，留待日后補(bǔ)錄外鍵的值非空外鍵的值非空，表示主從表之間的聯(lián)系非常緊密。例如學(xué)生的班級(jí)，在輸入學(xué)生的入學(xué)數(shù)據(jù)時(shí)是必須輸入的，因?yàn)橐粋€(gè)學(xué)生不可能在入學(xué)時(shí)是沒有班級(jí)的外鍵的值不允許重復(fù)如果外鍵的值不允許重復(fù)，表示主從表是一對(duì)一的聯(lián)系。就是在一對(duì)多的聯(lián)系中，將“多”的一方限制為不允許重復(fù)，從而成為一對(duì)一的聯(lián)系多個(gè)外鍵一張表可能與多張表有聯(lián)系，這時(shí)每個(gè)外鍵表示與一張表的聯(lián)系。有多少個(gè)外鍵，就表示與多少?gòu)埍碛新?lián)系一張表也可以有多個(gè)外鍵參照同一張表，這時(shí)每個(gè)外鍵都有不同的含義，例如借閱表有3個(gè)外鍵，都是參照用戶表的，它們分別表示借閱人、借出經(jīng)手人和歸還經(jīng)手人4.2.3其他完整性約束1．非空約束非空約束是很容易理解的一種約束，即指定該列的值是否允許取空值。需要注意的是，取空值和值為0或值為空字符串是不同的2．唯一性約束具有唯一性約束的屬性不允許出現(xiàn)重復(fù)的值。唯一性約束可以是單屬性的，也可以是多屬性的屬性組主鍵默認(rèn)具有唯一性約束。唯一性約束與主鍵的區(qū)別如下唯一性約束根據(jù)業(yè)務(wù)需求，不允許出現(xiàn)空值或允許出現(xiàn)一次空值，主鍵則不允許出現(xiàn)空值一張表允許有多個(gè)唯一性約束，但只能有一個(gè)主鍵約束3．默認(rèn)約束默認(rèn)約束用于當(dāng)插入行時(shí)，如果沒有為該列提供值，該列的值將被賦給默認(rèn)約束指定的值。更新數(shù)據(jù)時(shí)，默認(rèn)約束不起作用任務(wù)3簡(jiǎn)單數(shù)據(jù)查詢?nèi)蝿?wù)1數(shù)據(jù)操縱任務(wù)2理解數(shù)據(jù)完整性約束任務(wù)3簡(jiǎn)單數(shù)據(jù)查詢4.3.1【實(shí)訓(xùn)4–4】單表查詢4.3.2【實(shí)訓(xùn)4–5】理解查詢條件4.3.3【實(shí)訓(xùn)4–6】排序和分頁(yè)4.3.4【實(shí)訓(xùn)4–7】聯(lián)合查詢?nèi)蝿?wù)4復(fù)雜數(shù)據(jù)查詢4.3.1【實(shí)訓(xùn)4–4】單表查詢根據(jù)Jitor校驗(yàn)器的要求，在dbForge上完成“【實(shí)訓(xùn)4–4】單表查詢”1.查詢所有數(shù)據(jù)Select*from表名;Select*fromlib_user;微課：4-1單表查詢【實(shí)訓(xùn)4-4】4.3.1【實(shí)訓(xùn)4–4】單表查詢（續(xù)）選擇列1)選擇列（無(wú)標(biāo)題）2)列的別名（列標(biāo)題）Select列名1AS別名1,列名2AS別名2…from表名;Selectcol_nameas姓名,col_accountas賬號(hào),col_mobileas手機(jī)fromlib_user;Select列名1,列名2…from表名;Selectcol_name,col_account,col_mobilefromlib_user;4.3.1【實(shí)訓(xùn)4–4】單表查詢（續(xù)）選擇列（續(xù)）3)消除重復(fù)數(shù)據(jù)Selectdistinct

列名1,列名2…from表名;Selectcol_sexfromlib_user;Selectdistinctcol_sexfromlib_user;4.3.1【實(shí)訓(xùn)4–4】單表查詢（續(xù)）3.選擇行（下一小節(jié)詳細(xì)講解）4.計(jì)算列1)常量Select*from表名

where條件;Select常量,列名1,列名2…from表名;Select'圖書',col_titlefromlib_book;Select*fromlib_userwherecol_sex='F';4.3.1【實(shí)訓(xùn)4–4】單表查詢（續(xù)）2)表達(dá)式3)If表達(dá)式Select表達(dá)式,列名1AS別名1,列名2AS別名2…from表名;Selectif(條件,值1,值2),列名1,列名2…from表名;Selectconcat(col_author,'著：',col_title)fromlib_book;Selectcol_nameas姓名,col_sex,if(col_sex='M','男','女')性別fromlib_user;4.3.2【實(shí)訓(xùn)4–5】理解查詢條件根據(jù)Jitor校驗(yàn)器的要求，在dbForge上完成“【實(shí)訓(xùn)4–5】理解查詢條件”Select*from表名

where條件;【實(shí)訓(xùn)4-5】1．關(guān)系表達(dá)式1．關(guān)系表達(dá)式關(guān)系運(yùn)算符可以連接列名和常量（或表達(dá)式），從而形成關(guān)系表達(dá)式，用于查詢條件。等于、大于、小于、大于等于、小于等于、不等于：=、>、<、>=、<=、<>可以比較數(shù)字、字符串、日期時(shí)間例如如查詢用戶表中所有男性的姓名、電話和性別的代碼如下。查詢價(jià)格小于50元的圖書的代碼如下。Selectcol_name,col_mobile,col_sexfromlib_userwherecol_sex='M';Select*fromlib_bookwherecol_price<50

;2．范圍查詢2．范圍查詢用于查詢表達(dá)式的值是否在（不在）一個(gè)連續(xù)的范圍在…范圍之間、不在…范圍之間：between…and…、notbetween…and…可用于數(shù)字、字符串、日期時(shí)間例如查詢價(jià)格是40～50元的圖書（包括40和50）的代碼如下查詢?cè)?020/04/08與2020-04-12之間借出的圖書的代碼如下，日期要用單引號(hào)引起來(lái)，年、月、日的分隔符可以用斜線，也可以用減號(hào)Select*fromlib_bookwherecol_pricebetween40and50;Select*fromlib_lendingwherecol_lending_datebetween'2020/04/08'and'2020-04-12';3．集合查詢3．集合查詢用于查詢表達(dá)式的值是否在（不在）一個(gè)不連續(xù)的集合中。在…集合之內(nèi)、不在…集合之內(nèi)：in(…)、notin(…)可用于數(shù)字、字符串、日期時(shí)間例如查詢圖書ID是1或3的圖書副本的代碼如下，由于不是連續(xù)的數(shù)字，所以應(yīng)該使用集合查詢，而不是用范圍查詢Select*fromlib_copywhereid_lib_bookin(1,3);4．模糊查詢4．模糊查詢模糊查詢利用通配符來(lái)達(dá)到不精確匹配的查詢要求類似于、不類似于：like、notlike，并且配合通配符使用，通配符有兩種，如表4.4所示。僅用于字符串，不用于數(shù)字和日期時(shí)間例如查詢書名中，以“SQLServer”開始的圖書（只有一本）的代碼如下。查詢書名中，包含“SQLServer”的圖書（共有兩本）的代碼如下。Selectcol_author,col_titlefromlib_bookwherecol_titlelike'sqlserver%';Selectcol_author,col_titlefromlib_bookwherecol_titlelike'%sqlserver%';5．邏輯表達(dá)式5．邏輯表達(dá)式需要使用多個(gè)查詢條件時(shí)，可以使用and、or等將查詢條件連接起來(lái)，形成邏輯表達(dá)式。并且、或者、不：and、or、not僅用于連接其他查詢條件，一般不用直接于數(shù)字、字符串和日期例如例如查詢用戶表中姓“李”（條件一）的女性（條件二）的代碼如下。例如查詢用戶表中姓“李”（條件一）或者女性（條件二）的代碼如下。Selectcol_nameas姓名,col_sexas性別fromlib_userwherecol_namelike'李%'andcol_sex='F';Selectcol_nameas姓名,col_sexas性別fromlib_userwherecol_namelike'李%'or

col_sex='F';6．空值判斷6．空值判斷判斷值是否為空為空、不為空：isnull、isnotnull，不能用等于號(hào)“=”，用英語(yǔ)的語(yǔ)法isnull或者isnotnull可用于任何數(shù)字類型例如查詢用戶表中“手機(jī)”值為空的數(shù)據(jù)，代碼如下。查詢用戶表中“手機(jī)”值不為空的數(shù)據(jù)，代碼如下。Select*fromlib_userwherecol_mobileisnull;Select*fromlib_userwherecol_mobileisNOTnull;4.3.3【實(shí)訓(xùn)4–6】排序和分頁(yè)根據(jù)Jitor校驗(yàn)器的要求，在dbForge上完成“【實(shí)訓(xùn)4–6】排序和分頁(yè)”1．升序排序?qū)?shù)據(jù)從小到大進(jìn)行排序，用asc表示，這是默認(rèn)的排序方式，因此可以省略asc。例如對(duì)用戶表以“賬號(hào)”列為升序排序的代碼如下。2．降序排序降序排序是從大到小進(jìn)行排序，用desc表示。例如對(duì)相同的數(shù)據(jù)根據(jù)“賬號(hào)”列進(jìn)行降序排序的代碼如下Selectcol_rfid,col_name,col_sex,col_account,col_mobilefromlib_userorderbycol_accountasc;Selectcol_rfid,col_name,col_sex,col_account,col_mobilefromlib_userorderbycol_accountdesc

;【實(shí)訓(xùn)4-6】升序和降序的結(jié)果升序：從小到大降序：從大到小4.3.3【實(shí)訓(xùn)4–6】排序和分頁(yè)（續(xù)）3．多個(gè)排序列也可以指定多個(gè)排序列，這時(shí)先以第一排序列進(jìn)行排序，如果第一排序列的排序相同，再按第二排序列進(jìn)行排序，以此類推。下述代碼是對(duì)用戶表中的“性別”列和“賬號(hào)”列進(jìn)行排序，即先按“性別”列進(jìn)行排序，如果“性別”列相同，再按“賬號(hào)”列進(jìn)行排序。下述代碼將“賬號(hào)”列改為降序，其余不變Selectcol_rfid,col_name,col_sex,col_account,col_mobilefromlib_userorderbycol_sex,col_account;Selectcol_rfid,col_name,col_sex,col_account,col_mobilefromlib_userorderbycol_sex,col_accountdesc;多個(gè)排序列的結(jié)果先按第一排序進(jìn)行排序第一排序相同的，再按第二排序進(jìn)行排序如果第一排序沒有相同的，第二排序是不起作用的如果子句中的兩個(gè)列對(duì)調(diào)，成為orderbycol_account,col_sex，那么第二個(gè)列col_sex是沒有作用的，因?yàn)槊啃械馁~號(hào)都不同，按第一列col_account排序就已經(jīng)把所有行排好序了4.3.3【實(shí)訓(xùn)4–6】排序和分頁(yè)（續(xù)）4．分頁(yè)查詢?nèi)绻碇械臄?shù)據(jù)太多，例如達(dá)到100萬(wàn)行，這時(shí)顯示所有行將是不現(xiàn)實(shí)的，可以指定只顯示其中的部分行，使用limit關(guān)鍵字來(lái)實(shí)現(xiàn)。例如下述代碼是只顯示前3行l(wèi)imit關(guān)鍵字通常用于分頁(yè)，例如百度的查詢結(jié)果是分頁(yè)顯示的。分頁(yè)的語(yǔ)法格式如下假設(shè)tab_abc表有100行以上的數(shù)據(jù)，以每頁(yè)50行的要求，查詢第3頁(yè)的數(shù)據(jù)，代碼如下。其中，100=（頁(yè)號(hào)-1）*每頁(yè)行數(shù)=（3-1）*50。Select*fromtab_abclimit100,50;Select*fromlib_userlimit3;Select列名列表from表名limit(頁(yè)號(hào)-1)*每頁(yè)行數(shù),每頁(yè)行數(shù);4.3.4【實(shí)訓(xùn)4–7】聯(lián)合查詢根據(jù)Jitor校驗(yàn)器的要求，在dbForge上完成“【實(shí)訓(xùn)4–7】聯(lián)合查詢”注意以下幾點(diǎn)兩個(gè)查詢的列數(shù)相同，如果不相同，則會(huì)出錯(cuò)兩個(gè)查詢對(duì)應(yīng)列的含義相同，如果不相同，則會(huì)造成誤解列名以第一個(gè)查詢的列名為準(zhǔn)，第二個(gè)查詢的列名不顯示（被忽略）兩個(gè)查詢有各自的查詢條件等子句，只有排序子句是針對(duì)整個(gè)聯(lián)合的，并且在最后排序列名使用第一個(gè)查詢的列名，在上述例子中，如果按“性別”列排序時(shí)應(yīng)用col_sex，而不能用sex。Selectid_lib_user,col_name,col_sex,col_mobilefromlib_userunionSelectid,name,sex,mobilefromlib_staff;【實(shí)訓(xùn)4-7】聯(lián)合查詢的結(jié)果左圖：兩條查詢語(yǔ)句單獨(dú)執(zhí)行，結(jié)果是獨(dú)立的，為便于比較，將其上下放置右圖：兩條查詢語(yǔ)句改為聯(lián)合查詢，變成一條語(yǔ)句，結(jié)果就是單獨(dú)查詢時(shí)的結(jié)果的上下聯(lián)合任務(wù)4復(fù)雜數(shù)據(jù)查詢?nèi)蝿?wù)1數(shù)據(jù)操縱任務(wù)2理解數(shù)據(jù)完整性約束任務(wù)3簡(jiǎn)單數(shù)據(jù)查詢?nèi)蝿?wù)4復(fù)雜數(shù)據(jù)查詢4.4.1【實(shí)訓(xùn)4–8】?jī)?nèi)連接查詢4.4.2【實(shí)訓(xùn)4–9】外連接查詢4.4.3【實(shí)訓(xùn)4–10】自連接查詢4.4.4【實(shí)訓(xùn)4–11】統(tǒng)計(jì)與分組統(tǒng)計(jì)4.4.5【實(shí)訓(xùn)4–12】綜合練習(xí)4.4.1【實(shí)訓(xùn)4–8】?jī)?nèi)連接查詢根據(jù)Jitor校驗(yàn)器的要求，在dbForge上完成“【實(shí)訓(xùn)4–8】?jī)?nèi)連接查詢”1．兩張表的內(nèi)連接（聯(lián)系人數(shù)據(jù)庫(kù)）Selecttab_contact.id_contactas人員id,tab_contact.col_nameas姓名,tab_contact_type.col_nameas聯(lián)系人類型,tab_contact_type.id_contact_type類型idfromtab_contact

innerjointab_contact_type

on

tab_contact.id_contact_type=tab_contact_type.id_contact_type;from

人員表

innerjoin類型表

on

人員表的外鍵=類型表的主鍵可以省略關(guān)鍵字inner微課：4-2內(nèi)連接【實(shí)訓(xùn)4-8】?jī)?nèi)連接的查詢結(jié)果內(nèi)連接在關(guān)系數(shù)據(jù)庫(kù)查詢操作中是一項(xiàng)最重要的技能內(nèi)連接是外鍵參照（引用）主表的主鍵的概念在查詢中的體現(xiàn)內(nèi)連接是數(shù)據(jù)庫(kù)設(shè)計(jì)中，拆分表之后，再合并兩張表的數(shù)據(jù)的關(guān)鍵技術(shù)沒有內(nèi)連接查詢，就沒有關(guān)系數(shù)據(jù)庫(kù)技術(shù)參考下圖中的數(shù)據(jù)，深入理解上面的的概念4.4.1【實(shí)訓(xùn)4–8】?jī)?nèi)連接查詢（續(xù)）2．三張表的內(nèi)連接（聯(lián)系人數(shù)據(jù)庫(kù)）先連接兩張表，再與第三張連接Selecttab_contact.col_nameas姓名,

tab_contact_info.col_infoas聯(lián)系方式,tab_contact_info.col_noteas說(shuō)明,tab_contact_type.col_nameas聯(lián)系人類型fromtab_contact_infoinnerjointab_contactontab_contact_info.id_contact=tab_contact.id_contactinnerjointab_contact_typeontab_contact.id_contact_type=tab_contact_type.id_contact_type;三張表的內(nèi)連接兩張表連接之后，再連接到第三張表4.4.1【實(shí)訓(xùn)4–8】?jī)?nèi)連接查詢（續(xù)二）3．多張表的內(nèi)連接（圖書借閱數(shù)據(jù)庫(kù)）Select列名列表from表名1innerjoin表名2on表名1.外鍵1=表名2.主鍵2innerjoin…on…innerjoin表名non表名n.外鍵n=表名m.主鍵m;Selectlib_book.col_title書名,lib_book.col_author作者,lib_user.col_name借閱者姓名,lib_copy.col_bar_code副本條碼,lib_lending.col_lending_date借書時(shí)間,lib_lending.col_returned_date還書時(shí)間fromlib_lendinginnerjoinlib_useronlib_lending.id_lib_user1reader=lib_user.id_lib_userinnerjoinlib_copyonlib_lending.id_lib_copy=lib_copy.id_lib_copyinnerjoinlib_bookonlib_copy.id_lib_book=lib_book.id_lib_book;4.4.1【實(shí)訓(xùn)4–8】?jī)?nèi)連接查詢（續(xù)三）5張表的內(nèi)連接的例子注意：編寫連接查詢語(yǔ)句時(shí)，一般是從下向上連接，要一級(jí)一級(jí)地連接不論是從下向上連接，還是從上向下連接，都不能跳過(guò)某一級(jí)直接連到更高或更低的的一級(jí)。Selectlib_publisher.col_name出版社,lib_book.col_title書名,lib_book.col_author作者,lib_user.col_name借閱者姓名,lib_copy.col_bar_code副本條碼,lib_lending.col_lending_date借書時(shí)間,lib_lending.col_returned_date還書時(shí)間fromlib_lendinginnerjoinlib_useronlib_lending.id_lib_user1reader=lib_user.id_lib_userinnerjoinlib_copyonlib_lending.id_lib_copy=lib_copy.id_lib_copyinnerjoinlib_bookonlib_copy.id_lib_book=lib_book.id_lib_bookinnerjoinlib_publisheronlib_book.id_lib_publisher=lib_publisher.id_lib_publisher;4.4.1【實(shí)訓(xùn)4–8】?jī)?nèi)連接查詢（續(xù)四）4．連接查詢與其他子句（圖書借閱數(shù)據(jù)庫(kù)）from,where,orderby,limit子句的次序Selectlib_book.col_title書名,lib_book.col_author作者,lib_user.col_name借閱者姓名,lib_copy.col_bar_code副本條碼,lib_lending.col_lending_date借書時(shí)間,lib_lending.col_returned_date還書時(shí)間fromlib_lendinginnerjoinlib_useronlib_lending.id_lib_user1reader=lib_user.id_lib_userinnerjoinlib_copyonlib_lending.id_lib_copy=lib_copy.id_lib_copyinnerjoinlib_bookonlib_copy.id_lib_book=lib_book.id_lib_bookwherecol_returned_dateisnullorderbylib_user.col_name;Select列名列表from表1innerjoin表2on連接條件where選擇條件orderby排序列l(wèi)imit行數(shù);4.4.2【實(shí)訓(xùn)4–9】外連接查詢根據(jù)Jitor校驗(yàn)器的要求，在dbForge上完成“【實(shí)訓(xùn)4–9】外連接查詢”男生四位，女生三位，共七人兩位單身的男生，一位單身的女生，以及兩對(duì)夫妻微課：4-3外連接和自連接【實(shí)訓(xùn)4-9】4.4.2【實(shí)訓(xùn)4–9】外連接查詢（續(xù)一）1．內(nèi)連接作為對(duì)比，先看內(nèi)連接的結(jié)果結(jié)果是兩對(duì)夫妻問題：如果還想列出單身女生的信息，如何編寫Select語(yǔ)句？答案見下頁(yè)Select

as男性,man.wifeas妻子ID,

as女性frommanjoinwomanonman.wife=woman.id;4.4.2【實(shí)訓(xùn)4–9】外連接查詢（續(xù)二，右外連接）2．右外連接right

join可以列出右邊表的所有信息，即woman表，女生的信息問題：如果還想列出的是單身男生，而不是單身女生的信息，如何編寫Select語(yǔ)句？答案見下頁(yè)Select

as男性,man.wifeas妻子ID,

as女性fromman

rightjoinwomanonman.wife=woman.id;4.4.2【實(shí)訓(xùn)4–9】外連接查詢（續(xù)三，左外連接）3．左外連接leftjoin可以列出左邊表的所有信息，即man表，男生的信息問題：如果還想同時(shí)列出單身男生和單身女生的信息，又要如何編寫Select語(yǔ)句？答案見下頁(yè)Select

as男性,man.wifeas妻子ID,

as女性fromman

leftjoinwomanonman.wife=woman.id;4.4.2【實(shí)訓(xùn)4–9】外連接查詢（續(xù)四，全外連接）4．全外連接（full?，SQLServer使用full，但是MySQL不支持）列出兩邊表的所有數(shù)據(jù)正確的代碼是將右外連接rightjoin和左外連接leftjoin的結(jié)果合并union

到一起還要加上distinct，去除重復(fù)的已婚夫妻Select

as男性,man.wifeas妻子ID,

as女性fromman

fulljoinwomanonman.wife=woman.id;Select

as男性,man.wifeas妻子ID,as女性fromman

right

outerjoinwomanonman.wife=woman.iduniondistinctSelect

,man.wife,fromman

left

outerjoinwomanonman.wife=woman.id;4.4.3【實(shí)訓(xùn)4–10】自連接查詢根據(jù)Jitor校驗(yàn)器的要求，在dbForge上完成“【實(shí)訓(xùn)4–10】自連接查詢”一個(gè)家庭，6人，父母、祖父母、外祖父母，其中祖母已逝，不在數(shù)據(jù)庫(kù)中【實(shí)訓(xùn)4-10】4.4.3【實(shí)訓(xùn)4–10】自連接查詢（續(xù)一）1．查詢每個(gè)人的父親Select

姓名,

父親frommemberasmejoinmemberasfatheronme.father_id=father.id;me（自己）father（父親）物理上一張表member邏輯上兩張表asme,asfather4.4.3【實(shí)訓(xùn)4–10】自連接查詢（續(xù)二）2．查詢每個(gè)人的父親和母親Select

姓名,

父親,

母親frommemberasmejoinmemberasfatheronme.father_id=father.idjoinmemberasmotheronme.mother_id=mother.id;物理上一張表member邏輯上三張表asme,asfather,asmother4.4.3【實(shí)訓(xùn)4–10】自連接查詢（續(xù)三）3．查詢所有人的父親和母親（包括父母已去世）Select

姓名,

父親,

母親frommemberasme

left

joinmemberasfatheronme.father_id=father.id

left

joinmemberasmotheronme.mother_id=mother.id;4.4.3【實(shí)訓(xùn)4–10】自連接查詢（續(xù)四）4．查詢所有人的父親和母親（父母至少一人健在）Select

姓名,

父親,

母親frommemberasmeleftjoinmemberasfatheronme.father_id=father.idleftjoinmemberasmotheronme.mother_id=mother.idwhereme.father_idisnotnull--父親健在orme.mother_idisnotnull;--或母親健在想一想，如何查詢祖父母？如何查詢外祖父母？4.4.4【實(shí)訓(xùn)4–11】統(tǒng)計(jì)與分組統(tǒng)計(jì)根據(jù)Jitor校驗(yàn)器的要求，在dbForge上完成“【實(shí)訓(xùn)4–11】統(tǒng)計(jì)與分組統(tǒng)計(jì)”【實(shí)訓(xùn)4-11】4.4.4【實(shí)訓(xùn)4–11】統(tǒng)計(jì)與分組統(tǒng)計(jì)（續(xù)一）1．簡(jiǎn)單的統(tǒng)計(jì)Selectcount(*)圖書數(shù)量,round(avg(col_price),2)平均價(jià)格,min(col_price)最低價(jià)格,max(col_price)最高價(jià)格fromlib_book;這時(shí)，結(jié)果永遠(yuǎn)只有一行4.4.4【實(shí)訓(xùn)4–11】統(tǒng)計(jì)與分組統(tǒng)計(jì)（續(xù)二）2．分組統(tǒng)計(jì)Selectid_lib_publisher,count(*)圖書數(shù)量,round(avg(col_price),2)平均價(jià)格,min(col_price)最低價(jià)格,max(col_price)最高價(jià)格fromlib_bookgroupbyid_lib_publisher;這時(shí)，有多少個(gè)組，就有多少行例中，三個(gè)出版社就有三行4.4.4【實(shí)訓(xùn)4–11】統(tǒng)計(jì)與分組統(tǒng)計(jì)（續(xù)三）3．having子句Selectid_lib_publisher,count(*)圖書數(shù)量,round(avg(col_price),2)平均價(jià)格,min(col_price)最低價(jià)格,max(col_price)最高價(jià)格fromlib_bookgroupbyid_lib_publisher

having

平均價(jià)格<80;Having用于對(duì)統(tǒng)計(jì)結(jié)果進(jìn)行過(guò)濾例中，只列出平均價(jià)格低于80元的出版社4.4.4【實(shí)訓(xùn)4–11】統(tǒng)計(jì)與分組統(tǒng)計(jì)（續(xù)四）having和where的比較Selectid_lib_publisher,count(*)圖書數(shù)量,round(avg(col_price),2)平均價(jià)格,min(col_price)最低價(jià)格,max(col_price)最高價(jià)格fromlib_bookwherecol_price<80groupbyid_lib_publish