丨表復(fù)雜查詢?nèi)绾伪４嬷虚g結(jié)果

外部臨時表通過SQL語句創(chuàng)建，我們可以使用。首先，你要知道臨時表的創(chuàng)建語法結(jié)CREATETEMPORARYTABLE表(字段名字段類型跟普通表相比，臨時表有3個不同的特征臨時表的創(chuàng)建語法需要用到關(guān)鍵字臨時表創(chuàng)建完成之后，只有當(dāng)前連接可見，其他連接是看不到的，具有連接性臨時表在當(dāng)前連接結(jié)束之后，會被自動刪因為臨時表有連接性，不同連接創(chuàng)建相同名稱的臨時表也不會產(chǎn)生，適合并發(fā)程殘留在數(shù)據(jù)庫中。因此，我們就可以利用這些特點，用臨時表來SQL查詢的中間結(jié)剛剛提到，臨時表可以簡化復(fù)雜查詢，具體是怎么實現(xiàn)的呢？我來介紹一舉個例子，超市經(jīng)營者想要查詢2020年12月的一些特定商品銷售數(shù)量、進貨數(shù)量、返廠數(shù)量，那么，我們就要先把銷售、進貨、返廠這3個模塊分開計算，用臨時表來中間首先，我們統(tǒng)計一下在2020年12月的商品銷售數(shù)據(jù)假設(shè)我們的銷售流水表（mysales）如下所示我們可以用下面的SQL語句，查詢出每個單品的銷售數(shù)量和銷售金額，并存入臨時代代123456789mysql>CREATETEMPORARYTABLE->->--用查詢的結(jié)果直接生->ty)->SUM(salesvalue)AS->->->GROUPBY->ORDERBYQueryOK,2rowsaffected(0.01Records:2Duplicates:0Warnings:mysql>SELECT*FROM++++|itemnumberTY|salesvalue++++|1|5.000|411.18|2|5.000|24.75++++2rowsinset(0.01需要注意的是，這里我是直接用查詢結(jié)果來創(chuàng)建的臨時表。因為創(chuàng)建臨時表就是為了存放某個查詢的中間結(jié)果。直接用查詢語句創(chuàng)建臨時表比較快捷，而且連接結(jié)束后臨時表就會被自動刪除，不需要過多考慮表的結(jié)構(gòu)設(shè)計問題（比如冗余、效率等）。到這里，我們就有了一個單品銷售統(tǒng)計的臨時表。接下來，我們計算一下2020年月的進貨信息我們的進貨數(shù)據(jù)包括進貨單頭表（imorhead）和進貨單明細表（imordetails）。進貨單頭表包括進貨單編號、供貨商編號、倉庫編號、操作員編號和驗收日期：進貨單明細表包括進貨單編號、商品編號、進貨數(shù)量、進貨價格和進貨金我們用下面的SQL語句計算進貨數(shù)據(jù)，并且保存在臨時表里1mysql>CREATETEMPORARYTABLE223456789->SELECTty)ty,SUM(b.importvalue)AS->FROMdemo.importheadaJOINdemo.importdetails->ON->GROUPBYQueryOK,3rowsaffected(0.01sec)Records:3Duplicates:0Warnings:mysql>SELECT*FROM++++|itemnumberty|importvalue++++|1|5.000|290.00|2|5.000|15.00|3|8.000|40.00++++3rowsinset(0.00這樣，我們又得到了一個臨時表demo.myimport，里面保存了我們需要的進貨數(shù)據(jù)。我們的返廠數(shù)據(jù)表有2個，分別是返廠單頭表（returnhead）和返廠單明細（returndetails）返廠單頭表包括返廠單編號、供貨商編號、倉庫編號、操作員編號和驗收日返廠單明細表包括返廠單編號、商品編號、返廠數(shù)量、返廠價格和返廠金我們可以使用下面的SQL語句計算返廠信息，并且保存到臨時表代代123456789mysql>CREATETEMPORARYTABLE->SELECTty)ty,SUM(b.returnvalue)AS->FROMdemo.returnheadaJOINdemo.returndetails->ON->GROUPBYQueryOK,3rowsaffected(0.01Records:3Duplicates:0Warnings:mysql>SELECT*FROM++++|itemnumberty|returnvalue++++|1|2.000|115.00|2|1.000|3.00|3|1.000|5.00++++3rowsinset(0.00這樣，我們就獲得了單品的返廠信如果你跟著實際操作的話，你可能會有這樣一個問題：我們現(xiàn)在有3個臨時表，分別單品的銷售信息、進貨信息和返廠信息。那么，能不能把這3個表相互關(guān)聯(lián)起來信息都匯總到對應(yīng)的單品呢答案是不行，不管是用內(nèi)連接、還是用外連接，都不可以。因為無論是銷售信息、進貨信息，還是返廠信息，都存在商品信息缺失的情況。換句話說，就是在指定時間段內(nèi)，某些商品可能沒有銷售，某些商品可能沒有進貨，某些商品可能沒有返廠。如果僅僅通過這3個表之間的連接進行查詢，我們可能會丟失某些數(shù)據(jù)。為了解決這個問題，我們可以引入商品信息表。因為商品信息表包含所有的商品，因此，把商品信息表放在左邊，與其他的表進行左連接，就可以確保所有的商品都包含在結(jié)果集中。凡是不存在的數(shù)值，都設(shè)置為0，然后再篩選一下，把銷售、進貨、返廠都是0的商品去掉，這樣就能得到我們最終希望的查詢結(jié)果：2020年12月的商品銷售數(shù)量、進貨數(shù)量和返廠數(shù)量。代碼如下所示mysql>->->-> ty,0)as --如果沒有銷售記錄，銷售數(shù)量設(shè)置為-> ty,0)as --如果沒有進貨，進貨數(shù)量設(shè)為-> ty,0)as --如果沒有返廠，返廠數(shù)量設(shè)為->->demo.goodsmaster --商品信息表放在左邊進行左連接，確保->LEFTJOINdemo.mysales->ON->LEFTJOINdemo.myimport->ON->LEFTJOINdemo.myreturn->ON->HAVING ty>0OR ty>0OR ty>0;--在結(jié)果集16 17|itemnumber|goodsname| ty| ty| ty18 19|1|書|5.000|5.000|2.00020|2|筆|5.000|5.000|1.00021|3|橡皮|0.000|8.000|1.00022 233rowsinset(0.00總之，通過臨時表，我們就可以把一個復(fù)雜的問題拆分成很多個前后關(guān)聯(lián)的步驟，把中間的運行結(jié)果起來，用于之后的查詢。這樣一來，就把面向集合的SL查詢變成了面向過程的編程模式，大大降低了難度。由于采用的方式不同，臨時表也可分為內(nèi)存臨時表和磁盤臨時表，它們有著各自的優(yōu)關(guān)于內(nèi)存臨時表，有一點你要注意的是，你可以通過指定引擎類型（比ENGINE=MEMORY），來告訴MySQL臨時表在內(nèi)存中好了，現(xiàn)在我們先來創(chuàng)建一個內(nèi)存中的臨mysql>CREATETEMPORARYTABLE->->itemnumber->groupnumber->branchnumber->)ENGINE=MEMORY;（臨時表數(shù)據(jù)存在內(nèi)存中QueryOK,0rowsaffected(0.00指定引擎，MySQL會默認引擎是InnoDB，并且把表存放在磁盤上。mysql>CREATETEMPORARYTABLE->->itemnumber->groupnumber->branchnumber->QueryOK,0rowsaffected(0.00現(xiàn)在，我們向剛剛的兩張表里都插入同樣數(shù)量的記錄，然后再分別做一個查1123456789mysql>SELECTCOUNT(*)FROM代++|count(*) |4355++1rowinset(0.00mysql>SELECTCOUNT(*)FROM++|count(*)+|4355+++1rowinset(0.21可以看到，區(qū)別是比較明顯的。對于同一條查詢，內(nèi)存中的臨時表執(zhí)行時間不到10毫秒，而磁盤上的表卻用掉了210毫秒。顯然，內(nèi)存中的臨時表查詢速度更快。不過，內(nèi)存中的臨時表也有缺陷。因為數(shù)據(jù)完全在內(nèi)存中，所以，一旦斷電，數(shù)據(jù)就我畫了一張圖，匯總了內(nèi)存臨時表和磁盤臨時表的優(yōu)缺這節(jié)課，我們學(xué)習(xí)了臨時表的概念，以及使用臨時表來中間結(jié)果以拆分復(fù)雜查詢的方法。臨時表可以在磁盤中，也可以通過指定引擎的辦法在內(nèi)存中，以加快存取速其實，臨時表有很多好處，除了可以幫助我們把復(fù)雜的SQL查詢拆分成多個簡單的查詢，而且，因為臨時表是連接的，不同的連接可以使用相同的臨時表名稱，相互間不會受到影響。除此之外，臨時表會在連接結(jié)束的時候自動刪除，不會占用磁盤空當(dāng)然，臨時表也有不足，比如會擠占空間。我建議你，在使用臨時表的時候，要從簡化查詢和