最近要對(duì)數(shù)據(jù)庫(kù)進(jìn)行優(yōu)化，但由于工作項(xiàng)目中已經(jīng)很少親自寫(xiě)SQL而且用的都不是很復(fù)雜的語(yǔ)句，所以有些生疏了，于是翻翻N年前的筆記資料，想以此來(lái)記錄回顧總結(jié)一些實(shí)用的SQL干貨讓大家來(lái)學(xué)習(xí)，若有不對(duì)之處可提出。
記得剛出來(lái)行走江湖的時(shí)候也是只會(huì)增、刪、改、查四大法寶，一般公司沒(méi)有多少?gòu)?fù)雜的業(yè)務(wù)，所以就夠用了。但后來(lái)看著大神會(huì)寫(xiě)個(gè)幾百行的SQL存儲(chǔ)過(guò)程就感覺(jué)自己是不是弱爆了。
如今是大數(shù)據(jù)的時(shí)代，對(duì)數(shù)據(jù)的處理要求越來(lái)越重視，要出各種數(shù)據(jù)報(bào)表，因此百萬(wàn)數(shù)據(jù)處理速度，數(shù)據(jù)庫(kù)明顯比后臺(tái)邏輯處理的優(yōu)勢(shì)不是一個(gè)別。
下面進(jìn)入正題，寫(xiě)了多年的SQL，你真的玩會(huì)了SQL嗎？
在此我想再次提示一個(gè)數(shù)據(jù)處理的中心思想，SQL數(shù)據(jù)處理是集合思維，不要用邏輯思維來(lái)思考。
文中的示例來(lái)自自己的積累和TSQL2008技術(shù)內(nèi)幕。
對(duì)于教條式的定義請(qǐng)自己去查，此處不會(huì)涉及到文鄒鄒的知識(shí)，但還是強(qiáng)調(diào)一下基礎(chǔ)的重要性，即使你理解了所有的概念，但當(dāng)組合起來(lái)用時(shí)也會(huì)一頭霧水。
邏輯查詢(xún)處理階段
在以上的10個(gè)處理步驟中, 每一步的處理都生成一個(gè)虛擬表來(lái)作為下一步的輸入. 虛擬表對(duì)于調(diào)用者或輸出查詢(xún)來(lái)說(shuō)是不存在的, 僅在最后步驟生成的表才會(huì)返回給調(diào)用者或者輸出查詢(xún). 如果某一子句沒(méi)有出現(xiàn)在SQL語(yǔ)句中, 這一步就被簡(jiǎn)單跳過(guò)..
這10個(gè)具體步驟是:
1．FROM: from子句中的兩個(gè)表首先進(jìn)行交叉連接(笛卡爾積), 生成虛擬表VT1。
2．ON: on條件作用在VT1上, 將條件為T(mén)rue的行生成VT2。
3．OUTER: 如果outer join被指定, 則根據(jù)外連接條件, 將左表or右表or多表的未出現(xiàn)在VT2查詢(xún)結(jié)果中的行加入到VT2后生成VT3。
4．WHERE: VT3表中應(yīng)用Where條件, 結(jié)果為真的行用來(lái)生成VT4。
5．GROUP BY: 根據(jù)Group by指定的列, 將VT4的行組織到不同的組中, 生成VT5。
6．CLUB|ROLLUP: 超級(jí)組(分組之后的分組)被添加到VT5中, 生成VT6。
7．HAVING: Having用來(lái)篩選組, VT6上符合條件的組將用來(lái)生成VT7。
8．SELECT: select子句用來(lái)選擇指定的列, 并生成VT8。
9．DISTINCT: 從VT8中刪除重復(fù)的行后, VT9被生成。
10．ORDER BY: 根據(jù)Order by子句, VT9中的行被排序, 生成游標(biāo)10。
注意事項(xiàng):
第一步中FROM: 需要對(duì)兩表同時(shí)存在的列添加前綴, 以免混淆.
第二步中ON: 在SQL特有的三值邏輯(true,false,unknown)中, unkown的值也是確定的, 只是在不同情況下有時(shí)為true, 有時(shí)為false. 一個(gè)總的原則是: unknown的值非真即假, 非假即真. 也就是時(shí)說(shuō), unknown只能取true和false里面的一個(gè)值, 但是unknown的相反還是unknown.如:
在ON、WHERE和HAVING中做過(guò)濾條件時(shí), unknown看做false;
在CHECK約束中, unknown被看做是true;
在條件中, 兩個(gè)NULL的比較結(jié)果還是Unknown.
在UNIQUE和PRIMARY KEY約束、排序和分組中, NULL被看做是相等的. 例如Group by 將null分為一組, 而order by將所有null排在一起.
第三步中OUTER: 如果多余兩張表, 則將VT3和FROM中的下一張表再次執(zhí)行從第一步到第三步的過(guò)程.
第四步中WHERE: 由于此刻沒(méi)有分組, 也沒(méi)有執(zhí)行select所以, where子句中不能寫(xiě)分組函數(shù), 也不能使用表的別名. 并且, 只有在外連接時(shí), on和where的邏輯才是不同的, 因此建議連接條件放在on中.
第五步中GROUP BY: 如果查詢(xún)中包含Group by 子句, 那么所有的后續(xù)操作(having, select等)都是對(duì)每一組的結(jié)果進(jìn)行操作.
Group by子句中可以使用組函數(shù), 在Sql 2000中一旦使用組函數(shù), 其后面的步驟將都不能處理, 而在Sql2005中沒(méi)有這個(gè)限制.
第六步不常用, 略過(guò).
第七步中HAVING: having表達(dá)式是僅有的分組條件. 注意: count(*)不會(huì)忽略掉null, 而count(field)會(huì); 此外分組函數(shù)中不支持子查詢(xún)做輸入.
第八步中SELECT: 如果包含Group By子句, 那么在第5步后將只能使用Group By子句中出現(xiàn)的列, 如果要使用其他原始列則, 只能使用組函數(shù).
另外, select在第八步才執(zhí)行, 因此別名只能第八步之后才能使用, 并且只能在order by中使用.
第九步中DISTINCT: 當(dāng)使用Group By子句時(shí), 使用Distinct是多余的, 他不會(huì)刪除任何記錄.
第十步中ORDER BY: 按Order by子句指定的列排序后, 返回游標(biāo)VC10.
別名只能在Order by子句中使用.
如果定義了Distinct子句, 則只能排序上一步中返回的表VT9, 如果沒(méi)有指定Distinct子句, 則可以排序不再最終結(jié)果集中的列. 例如: 如果不加Distinct則Order by可以訪問(wèn)VT7和VT8中的內(nèi)容.
這一步最不同的是它返回的是游標(biāo)而不是表, Sql是基于集合論的, 集合中的元素師沒(méi)有順序的, 一個(gè)在表上引用Order by排序的查詢(xún)返回一個(gè)按照特定特定物理順序組織的對(duì)象—游標(biāo). 所以對(duì)于視圖、子查詢(xún)、派生表等均不能將order by結(jié)果作為其數(shù)據(jù)來(lái)源.
建議: 使用表的表達(dá)式時(shí), 不允許使用order by子句的查詢(xún), 因此除非你真的要對(duì)行排序, 否則不要使用order by 子句.
內(nèi)容為 RJ 寫(xiě)的，邏輯非常清楚，值得花點(diǎn)時(shí)間理解，再次強(qiáng)調(diào)是因?yàn)閺?fù)雜的集合數(shù)據(jù)處理過(guò)程中會(huì)得到不是你想要的結(jié)果，這時(shí)就要你自己腦袋當(dāng)SQL處理器來(lái)推出結(jié)果查出問(wèn)題，可能大多數(shù)寫(xiě)了幾年的SQL都還沒(méi)弄明白，但到了用時(shí)還是提前理解下，非常重要。
練習(xí)
此后用到的用例數(shù)據(jù)庫(kù)是SQL2008里面的
用例數(shù)據(jù)庫(kù)文件：鏈接：http://pan.baidu.com/s/1qW1QxA0 密碼：dqxx
/*1.返回來(lái)自美國(guó)的客戶(hù)，并為每個(gè)客戶(hù)返回其訂單總數(shù)和商品交易總數(shù)量。涉及到表：Sales.Customers表、Sales.Orders表，以及Sales.OrderDetails表。期望的輸出：*/custid numorders totalqty----------- ----------- -----------32 11 34536 5 12243 2 2045 4 18148 8 13455 10 60365 18 138371 31 495875 9 32777 4 4678 3 5982 3 8989 14 10631參考SQL：
--answer:select c.custid,count(distinct o.orderid) as 'numorders',sum(od.qty) as 'totalqty'from Sales.Customers as cjoin Sales.Orders as oon c.custid=o.custidjoin Sales.OrderDetails as odon o.orderid=od.orderidwhere c.country='USA'group by c.custid/*1.將表Sales.Customers別名為c和表Sales.Orders別名為o應(yīng)用ON篩選器以custid為條件內(nèi)連接，生成虛擬表VT1，2.將虛擬表VT1和表Sales.OrderDetails應(yīng)用ON篩選器以orderid為條件內(nèi)連接，生成虛擬表VT2，3.對(duì)上一步返回的虛擬表中的所有行應(yīng)用where篩選器返回滿(mǎn)足條件c.country='USA'的虛擬表VT3，4.應(yīng)用group by子句將數(shù)據(jù)以c.custid列分組5.處理select列表，去掉重復(fù)o.orderid再用count統(tǒng)計(jì)個(gè)數(shù)返回別名為numorders的列，統(tǒng)計(jì)od.qty列別名totalqty*//*2：返回客戶(hù)及其訂單信息，包括沒(méi)有下過(guò)任何訂單的客戶(hù)。涉及到表：Sales.Customers和Sales.Orders表。期望的輸出（按簡(jiǎn)略的格式顯示）：*/custid companyname orderid orderdate----------- --------------- ----------- ------------------------85 Customer ENQZT 10248 2006-07-04 00:00:00.00079 Customer FAPSM 10249 2006-07-05 00:00:00.00034 Customer IBVRG 10250 2006-07-08 00:00:00.00084 Customer NRCSK 10251 2006-07-08 00:00:00.000...73 Customer JMIKW 11074 2008-05-06 00:00:00.00068 Customer CCKOT 11075 2008-05-06 00:00:00.0009 Customer RTXGC 11076 2008-05-06 00:00:00.00065 Customer NYUHS 11077 2008-05-06 00:00:00.00022 Customer DTDMN NULL NULL57 Customer WVAXS NULL NULL2參考SQL：
--answer:select c.custid,c.companyname,o.orderid,o.orderdatefrom Sales.Customers as cleft join Sales.Orders as oon c.custid=o.custid/*1.將表Sales.Customers別名為c和表Sales.Orders別名為o應(yīng)用ON篩選器以custid為條件左外連接，生成虛擬表VT1，2.添加外部行，外部行中非保留表中的屬性被賦值為NULL，生成虛擬表VT23.處理select列表，查找出c.custid,c.companyname,o.orderid,o.orderdate生成虛擬表VT3*//*3：返回值2007年2月12日下過(guò)訂單的客戶(hù)，以及他們的訂單。同時(shí)也返回在2007年2月12日沒(méi)有下過(guò)訂單的客戶(hù)。涉及到表：Sales.Customers表和Sales.Orders表。期望的輸出（按簡(jiǎn)略格式顯示）：*/custid companyname orderid orderdate----------- --------------- ----------- -----------------------72 Customer AHPOP NULL NULL58 Customer AHXHT NULL NULL25 Customer AZJED NULL NULL18 Customer BSVAR NULL NULL91 Customer CCFIZ NULL NULL...33 Customer FVXPQ NULL NULL53 Customer GCJSG NULL NULL39 Customer GLLAG NULL NULL16 Customer GYBBY NULL NULL4 Customer HFBZG NULL NULL5 Customer HGVLZ 10444 2007-02-12 00:00:00.00042 Customer IAIJK NULL NULL34 Customer IBVRG NULL NULL63 Customer IRRVL NULL NULL73 Customer JMIKW NULL NULL15 Customer JUWXK NULL NULL...21 Customer KIDPX NULL NULL30 Customer KSLQF NULL NULL55 Customer KZQZT NULL NULL71 Customer LCOUJ NULL NULL77 Customer LCYBZ NULL NULL66 Customer LHANT 10443 2007-02-12 00:00:00.00038 Customer LJUCA NULL NULL59 Customer LOLJO NULL NULL36 Customer LVJSO NULL NULL64 Customer LWGMD NULL NULL29 Customer MDLWA NULL NULL...3參考SQL：
--answer:select c.custid,c.companyname,o.orderid,o.orderdatefrom Sales.Customers as cleft join Sales.Orders as oon c.custid=o.custid and o.orderdate='2007-2-12'/*1.將表Sales.Customers別名為c和表Sales.Orders別名為o應(yīng)用ON篩選器以custid和o.orderdate='2007-2-12'為條件左外連接，生成虛擬表VT1，2.添加外部行，外部行中非保留表中的屬性被賦值為NULL，生成虛擬表VT23.處理select列表，從虛擬表VT2中查找出c.custid,c.companyname,o.orderid,o.orderdate生成虛擬表VT3*/聯(lián)系客服
