tbwshc

六、子查詢的用法

子查詢是一個 SELECT 查詢，它嵌套在 SELECT、INSERT、UPDATE、DELETE 語句或其它子查詢中。

任何允許使用表達式的地方都可以使用子查詢，子查詢可以使我們的編程靈活多樣，可以用來實現(xiàn)一些特殊的功能。但是在性能上，

往往一個不合適的子查詢用法會形成一個性能瓶頸。如果子查詢的條件中使用了其外層的表的字段，這種子查詢就叫作相關(guān)子查詢。

相關(guān)子查詢可以用IN、NOT IN、EXISTS、NOT EXISTS引入。 關(guān)于相關(guān)子查詢，應(yīng)該注意：

(1)

A、NOT IN、NOT EXISTS的相關(guān)子查詢可以改用LEFT JOIN代替寫法。

比如： SELECT PUB_NAME FROM PUBLISHERS WHERE PUB_ID NOT IN (SELECT PUB_ID FROM TITLES WHERE TYPE = 'BUSINESS') 可以改寫成： SELECT A.PUB_NAME FROM PUBLISHERS A LEFT JOIN TITLES B ON B.TYPE = 'BUSINESS' AND A.PUB_ID=B. PUB_ID WHERE B.PUB_ID IS NULL

(2)

SELECT TITLE FROM TITLES  
WHERE NOT EXISTS  
(SELECT TITLE_ID FROM SALES  
WHERE TITLE_ID = TITLES.TITLE_ID) 

可以改寫成：

SELECT TITLE  
FROM TITLES LEFT JOIN SALES  
ON SALES.TITLE_ID = TITLES.TITLE_ID  
WHERE SALES.TITLE_ID IS NULL 

B、 如果保證子查詢沒有重復(fù) ，IN、EXISTS的相關(guān)子查詢可以用INNER JOIN 代替。比如：

SELECT PUB_NAME  
FROM PUBLISHERS  
WHERE PUB_ID IN 
(SELECT PUB_ID  
FROM TITLES  
WHERE TYPE = 'BUSINESS') 

可以改寫成：

SELECT A.PUB_NAME --SELECT DISTINCT A.PUB_NAME  
FROM PUBLISHERS A INNER JOIN TITLES B  
ON B.TYPE = 'BUSINESS' AND 
A.PUB_ID=B. PUB_ID 

(3)

C、 IN的相關(guān)子查詢用EXISTS代替，比如

SELECT PUB_NAME FROM PUBLISHERS  
WHERE PUB_ID IN 
(SELECT PUB_ID FROM TITLES WHERE TYPE = 'BUSINESS') 

可以用下面語句代替：

SELECT PUB_NAME FROM PUBLISHERS WHERE EXISTS  
(SELECT 1 FROM TITLES WHERE TYPE = 'BUSINESS' AND 
PUB_ID= PUBLISHERS.PUB_ID) 

D、不要用COUNT(*)的子查詢判斷是否存在記錄，最好用LEFT JOIN或者EXISTS，比如有人寫這樣的語句：

SELECT JOB_DESC FROM JOBS  
WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0 

應(yīng)該改成：

SELECT JOBS.JOB_DESC FROM JOBS LEFT JOIN EMPLOYEE  
ON EMPLOYEE.JOB_ID=JOBS.JOB_ID  
WHERE EMPLOYEE.EMP_ID IS NULL 
SELECT JOB_DESC FROM JOBS  
WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)<>0 

應(yīng)該改成：

SELECT JOB_DESC FROM JOBS  
WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID) 

七：盡量使用索引

建立索引后，并不是每個查詢都會使用索引，在使用索引的情況下，索引的使用效率也會有很大的差別。只要我們在查詢語句中沒有強制指定索引，

索引的選擇和使用方法是SQLSERVER的優(yōu)化器自動作的選擇，而它選擇的根據(jù)是查詢語句的條件以及相關(guān)表的統(tǒng)計信息，這就要求我們在寫SQL

語句的時候盡量使得優(yōu)化器可以使用索引。為了使得優(yōu)化器能高效使用索引，寫語句的時候應(yīng)該注意：

A、不要對索引字段進行運算，而要想辦法做變換，比如

SELECT ID FROM T WHERE NUM/2=100

應(yīng)改為:

SELECT ID FROM T WHERE NUM=100*2

-------------------------------------------------------

SELECT ID FROM T WHERE NUM/2=NUM1

如果NUM有索引應(yīng)改為:

SELECT ID FROM T WHERE NUM=NUM1*2

如果NUM1有索引則不應(yīng)該改。

--------------------------------------------------------------------

發(fā)現(xiàn)過這樣的語句：

SELECT 年,月,金額 FROM 結(jié)余表 WHERE 100*年+月=2010*100+10

應(yīng)該改為：

SELECT 年,月,金額 FROM 結(jié)余表 WHERE 年=2010 AND月=10

B、 不要對索引字段進行格式轉(zhuǎn)換

日期字段的例子：

WHERE CONVERT(VARCHAR(10), 日期字段,120)='2010-07-15'

應(yīng)該改為

WHERE日期字段〉='2010-07-15' AND 日期字段<'2010-07-16'

ISNULL轉(zhuǎn)換的例子：

WHERE ISNULL(字段,'')<>''應(yīng)改為:WHERE字段<>''

WHERE ISNULL(字段,'')=''不應(yīng)修改

WHERE ISNULL(字段,'F') ='T'應(yīng)改為: WHERE字段='T'

WHERE ISNULL(字段,'F')<>'T'不應(yīng)修改

C、 不要對索引字段使用函數(shù)

WHERE LEFT(NAME, 3)='ABC' 或者WHERE SUBSTRING(NAME,1, 3)='ABC'

應(yīng)改為: WHERE NAME LIKE 'ABC%'

日期查詢的例子：

WHERE DATEDIFF(DAY, 日期,'2010-06-30')=0

應(yīng)改為:WHERE 日期>='2010-06-30' AND 日期 <'2010-07-01'

WHERE DATEDIFF(DAY, 日期,'2010-06-30')>0

應(yīng)改為:WHERE 日期 <'2010-06-30'

WHERE DATEDIFF(DAY, 日期,'2010-06-30')>=0

應(yīng)改為:WHERE 日期 <'2010-07-01'

WHERE DATEDIFF(DAY, 日期,'2010-06-30')<0

應(yīng)改為:WHERE 日期>='2010-07-01'

WHERE DATEDIFF(DAY, 日期,'2010-06-30')<=0

應(yīng)改為:WHERE 日期>='2010-06-30'

D、不要對索引字段進行多字段連接

比如：

WHERE FAME+ '. '+LNAME='HAIWEI.YANG'

應(yīng)改為:

WHERE FNAME='HAIWEI' AND LNAME='YANG'

八：多表連接的連接條件對索引的選擇有著重要的意義，所以我們在寫連接條件條件的時候需要特別注意。

A、多表連接的時候，連接條件必須寫全，寧可重復(fù)，不要缺漏。

B、連接條件盡量使用聚集索引

C、注意ON、WHERE和HAVING部分條件的區(qū)別

ON是最先執(zhí)行， WHERE次之，HAVING最后，因為ON是先把不符合條件的記錄過濾后才進行統(tǒng)計，它就可以減少中間運算要處理的數(shù)據(jù)，按理說應(yīng)該速度是最快的，WHERE也應(yīng)該比 HAVING快點的，因為它過濾數(shù)據(jù)后才進行SUM，在兩個表聯(lián)接時才用ON的，所以在一個表的時候，就剩下WHERE跟HAVING比較了

1考慮聯(lián)接優(yōu)先順序：

2INNER JOIN

3LEFT JOIN (注：RIGHT JOIN 用 LEFT JOIN 替代)

4CROSS JOIN

其它注意和了解的地方有：

A、在IN后面值的列表中，將出現(xiàn)最頻繁的值放在最前面，出現(xiàn)得最少的放在最后面，減少判斷的次數(shù)

B、注意UNION和UNION ALL的區(qū)別。--允許重復(fù)數(shù)據(jù)用UNION ALL好

C、注意使用DISTINCT，在沒有必要時不要用

D、TRUNCATE TABLE 與 DELETE 區(qū)別

E、減少訪問數(shù)據(jù)庫的次數(shù)

還有就是我們寫存儲過程，如果比較長的話，最后用標記符標開，因為這樣可讀性很好，即使語句寫的不怎么樣但是語句工整，C# 有region

sql我比較喜歡用的就是

--startof 查詢在職人數(shù)  
          sql語句  
--end of 

正式機器上我們一般不能隨便調(diào)試程序，但是很多時候程序在我們本機上沒問題，但是進正式系統(tǒng)就有問題，但是我們又不能隨便在正式機器上操作，那么怎么辦呢?我們可以用回滾來調(diào)試我們的存儲過程或者是sql語句，從而排錯。

BEGIN TRAN  
   UPDATE a SET 字段='' 
ROLLBACK 

作業(yè)存儲過程我一般會加上下面這段，這樣檢查錯誤可以放在存儲過程，如果執(zhí)行錯誤回滾操作，但是如果程序里面已經(jīng)有了事務(wù)回滾，那么存儲過程就不要寫事務(wù)了，這樣會導(dǎo)致事務(wù)回滾嵌套降低執(zhí)行效率，但是我們很多時候可以把檢查放在存儲過程里，這樣有利于我們解讀這個存儲過程，和排錯。

BEGIN TRANSACTION 
--事務(wù)回滾開始  
--檢查報錯  
IF ( @@ERROR > 0 )  
        BEGIN 
        --回滾操作  
                ROLLBACK TRANSACTION 
                RAISERROR('刪除工作報告錯誤', 16, 3)  
                RETURN 
        END 
--結(jié)束事務(wù)  
COMMIT TRANSACTION 

好久沒有寫博文了，工作項目一個接一個，再加上公司人員流動，新人很多事情接不下來，加班成了家常便飯，倉促寫下這些希望對大家有幫助，不對的也歡迎指點，交流互相提高。

有錯誤的地方歡迎大家拍磚,希望交流和共享。