T-SQLèæ¬ä¼åæå·§ï¼
1ï¼å¯¹äºSELECT/UPDATEè¯å¥å¿
é¡»æ¾ç¤ºçå®ä¹ææçå,é¿å
使ç¨æå·ã
2ï¼å¨æ§è¡SELECT/INSERT/UPDATE/DELETEè¯å¥æ¶,请èèæ§è¡è§åçéç¨,å°½éèèç¨SP-EXECUTESQLåå¨è¿ç¨ã
3ï¼ä¼å
ä½¿ç¨ SELECT...INTOï¼ç¶åä½¿ç¨ INSERT...SELECTï¼ä»¥é¿å
大éæ»éã
4ï¼å¦æéè¦å é¤ææçæ°æ®,ç¨TRUNCATE TABLE 代æ¿DELETE ã
5ï¼é¿å
使ç¨DISTINCT è¯å¥ã
6ï¼å¦æä½ éè¦æéçè®°å½,éè¿TOP N代æ¿SET ROWCOUNTæ¥æ§å¶æåºåå¼ã
7ï¼é¿å
使ç¨SARGABLEçè¯å¥å¨WHEREåå¥,æ¯å¦: OR, <>, !=, !<, >!, IS NULL, NOT, NOT IN, NOT LIKE åLIKE,å 为è¿äºæä½å¾é¾å©ç¨å·²ç¥çç´¢å¼ã
8ï¼é¿å
使ç¨NOT IN,å¯ä»¥éç¨IN,EXISTS NOT EXISTSåLEFT JOIN å 空å¼å¤æ
--NOT EXISTS, æçæé« SELECT a.hdr_key
FROM hdr_tbl a
WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key) --LEFT JOIN SELECT a.hdr_key
FROM hdr_tbl a
LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key
WHERE b.hdr_key IS NULL --NOT IN ,æçæä½ SELECT hdr_key
FROM hdr_tbl
WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl) 9ï¼ä½¿ç¨EXISTSå¤æè®°å½æ¯å¦åå¨ã
--ä¸å¥½çåæ³: IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx') --æ£ç¡®çåæ³: IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx') 10ï¼é¿å
å¨GROUP BYä¸ä½¿ç¨HAVING è¯å¥ã
11ï¼GROUP BYçè¯å¥è¦å°½éç®å,ä¸è¦è¿è¡GROUP BYè¯å¥çåµå¥,é¿å
å¨GROUP BYä¸å
å«å¤ä½çå
èèå¨GROUP BYçå,è¿è¡ORDER BYæåº,ç¹å«å¨å¤ç¨æ·çç¯å¢ä¸ã
12ï¼å¦æéè¦å¨ä¸ä¸ªå
å«JOINçSELECTè¯å¥è¿è¡GROUP BY,请èèç¨åæ¥è¯¢ä»£æ¿JOIN. å¦æå¿
须使ç¨GROUP BY, GROUP BY çåºè¯¥åå¨åä¸å¼ 表ã
13ï¼å¦æWHEREæ¡ä»¶è¯å¥æå¤ä¸ªANDæ¡ä»¶,请确ä¿è³å°æä¸ä¸ªåæç´¢å¼,å¦æ没æå¯ä»¥å»ºç«å¤åå¤åINDEXã
14ï¼å¯¹äºSQL æ æ³æ§è¡èªå¨ä¼åçWHEREæ¡ä»¶è¯å¥,å¯ä»¥éè¿HINTSæ¾ç¤ºçå¶å®INDEXæ¥æé«æ¥è¯¢çæçã
--å¯è½ä¸å¥½çåæ³: SELECT * FROM tblTaskProcessesWHERE nextprocess = 1 AND processid IN (8,32,45) --æ£ç¡®çåæ³: SELECT * FROM tblTaskProcesses (INDEX = IX_ProcessID)WHERE nextprocess = 1 AND processid IN (8,32,45) 15ï¼å°½å¯è½é¿å
å¨WHEREæ¡ä»¶è¯å¥ä¸ä½¿ç¨å½æ°è®¡ç®ã
--ä¸å¥½çåæ³: WHERE SUBSTRING(firstname,1,1) = 'm' --æ£ç¡®çåæ³: WHERE firstname like 'm%' 16ï¼å¨WHEREæ¡ä»¶è¯å¥ä¸,é¿å
å¨å½æ°ä¸å
å,å¦ææ æ³é¿å
,请èèå¨è¯¥å建ç«INDEXã
--ä¸å¥½çåæ³: SELECT member_number, first_name, last_name
FROM members
WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21 -- æ£ç¡®çåæ³: SELECT member_number, first_name, last_name
FROM members
WHERE dateofbirth < DATEADD(yy,-21,GETDATE()) 17ï¼å¨WHEREæ¡ä»¶è¯å¥ä¸,é¿å
使ç¨NOT ã
--ä¸å¥½çåæ³: WHERE NOT column_name > 5 --æ£ç¡®çåæ³: WHERE column_name <= 5 18ï¼å¨WHEREæ¡ä»¶è¯å¥ä¸,æ¨è使ç¨10ä½çæ¥æå½æ°ã
--æ£ç¡®çåæ³: SELECT * FROM Northwind.dbo.Orders WHERE OrderDate > '12/31/1997'--ä¸å¥½çåæ³: SELECT * FROM Northwind.dbo.Orders WHERE OrderDate > '12/31/97' 19ï¼é¿å
使ç¨UNION,èæ¯ç¨UNION ALL ã
20ï¼ä½¿ç¨ SQL-92 æ åè¿æ¥å¥æ³,为äºæé«æ§è½ï¼åºä¼å
使ç¨è¿æ¥ï¼ç¶å使ç¨åæ¥è¯¢æåµå¥æ¥è¯¢,表ä¹é´çè¿æ¥ä½¿ç¨INNER JOIN,LEFT JOIN åRIGHT JOIN,ä¸ä½¿ç¨CROSS JOINåå¤å表æ¹å¼.ã
21ï¼å¤è¡¨å
³èé¿å
è¶
è¿5个,å¯ä»¥éè¿ä¸´æ¶è¡¨(表åé),ç®åå¤æçå
³èã
åå¨è¿ç¨çå¼ååä¼åæå·§ï¼1ï¼é¿å
使ç¨è§¦åå¨TRIGGER,èèç¨åå¨è¿ç¨ä»£æ¿è§¦åå¨
ââä¸ä¸´æ¶è¡¨ä¸æ ·ï¼å
æ 并ä¸æ¯ä¸å¯ä½¿ç¨ã对å°åæ°æ®é使ç¨FAST_FORWARD å
æ é常è¦ä¼äºå
¶ä»éè¡å¤çæ¹æ³ï¼å°¤å
¶æ¯å¨å¿
é¡»å¼ç¨å 个表æè½è·å¾æéçæ°æ®æ¶ãå¨ç»æéä¸å
æ¬âå计âçä¾ç¨é常è¦æ¯ä½¿ç¨å
æ æ§è¡çé度快ãå¦æå¼åæ¶ é´å
许ï¼åºäºå
æ çæ¹æ³ååºäºéçæ¹æ³é½å¯ä»¥å°è¯ä¸ä¸ï¼çåªä¸ç§æ¹æ³çæææ´å¥½ã2ï¼èèç¨UDF代æ¿åå¨è¿ç¨
ââ使ç¨è¡¨å¼ UDF æ¶è¦å°å¿ï¼å 为å¨åéï¼èä¸æ¯å¸¸éï¼ä¸ä¼ éæ个åæ°æ¶ï¼å¦æå¨ WHERE åå¥ä¸ä½¿ç¨è¯¥åæ°ï¼ä¼å¯¼è´è¡¨æ«æãè¿è¦é¿å
å¨ä¸ä¸ªæ¥è¯¢ä¸å¤æ¬¡ä½¿ç¨ç¸åçè¡¨å¼ UDFãä½æ¯ï¼è¡¨å¼ UDF ç¡®å®å
·ææäºé常æ¹ä¾¿çå¨æç¼è¯åè½ã3ï¼å¯¹äºé¢ç¹è°ç¨çåå¨è¿ç¨,èèç¨SP_RECOMPILEéæ°ç¼è¯
4ï¼ä½¿ç¨è¾åºè¯å¥ä»£æ¿è¿åæ´ä¸ªæ°æ®é,è¾åºè¯å¥çæ§è¡æçä¼æ´å é«æ
5ï¼å¨åå¨è¿ç¨ç头é¨ä½¿ç¨SET NOCOUNT ON, éè¿@@ROWCOUNTæ¥æ§å¶,è¿æ ·å¯ä»¥åå°ç½ç»æµéåé¿å
æ½å¨çé®é¢, èå¨ç»ææ¶è®¾ç½® SET NOCOUNT OFF.
6ï¼ä¸ä½¿ç¨SP_ä½ä¸ºåå¨è¿ç¨çå称,建议ç¨USP_,è¿ä¸ªä¼å½±åæ°æ®åºçæ§è¡æ¶é´.
7ï¼å°½å¯è½ä½¿ç¨ä¸´æ¶è¡¨è使ç¨è¡¨åé,表åéå¯ä»¥åå°ä¸éåéæ°ç¼è¯ç次æ°å¹¶ä¸è¡¨åéä¸ä½¿ç¨TEMPDBç空é´,èæ¯å
¨é¨ä½¿ç¨å
åæ¥å¤çæ°æ®.
8ï¼å
å¨ä¾ç¨ä¸å建临æ¶è¡¨ï¼æååæ¾å¼å é¤ä¸´æ¶è¡¨ãå° DDL ä¸ DML è¯å¥æ··å使ç¨æå©äºå¤çé¢å¤çéæ°ç¼è¯æ´»å¨
9ï¼å°½å¯è½ä¸è¦å¨æµç¨æ§å¶è¯å¥ä¸ä½¿ç¨ä¸´æ¶è¡¨,æ¯å¦:IF .. ELSE, WHILE
10ï¼é¿å
å¨äºå¡ä¸è¿è¡èµå¼åå¤æ计ç®,
--ä¸å¥½çåæ³: Create procedure proc_1 As Begin Begin transaction -- step 1 verify the data -- step 2 perform calculations -- step 3 get default variable values (date, user info) -- update/insert records commit end --ä¸å¥½çåæ³: Create procedure proc_1 As Begin -- step 1 verify the data -- step 2 make calculations -- step 3 get default variable values (date, user info) Begin transaction -- update/insert records commit end ä¸é¢çä¸äºä¼åè§ååªæ¯ä¸è¬åå,å¨æäºç¹æ®æ
åµä¸å¯è½ä¼ææå·®å«,å¦æéè¦åæT-SQLçæ§è½,å¯ä»¥éè¿æ¥è¯¢åæå¨çCTRL+L æ¾ç¤ºæ§è¡è§åè¿è¡åæ,ä¹å¯ä»¥éè¿ SET STATISTICS PROFILE ONè¿è¡åæ.
温馨提示:答案为网友推荐,仅供参考