å
³äºmysqlå¤çç¾ä¸çº§ä»¥ä¸çæ°æ®æ¶å¦ä½æé«å
¶æ¥è¯¢é度çæ¹æ³
æè¿ä¸æ®µæ¶é´ç±äºå·¥ä½éè¦ï¼å¼å§å
³æ³¨é对Mysqlæ°æ®åºçselectæ¥è¯¢è¯å¥çç¸å
³ä¼åæ¹æ³ã
ç±äºå¨åä¸çå®é
项ç®ä¸åç°å½mysql表çæ°æ®éè¾¾å°ç¾ä¸çº§æ¶ï¼æ®éSQLæ¥è¯¢æçåç´çº¿ä¸éï¼èä¸å¦æwhereä¸çæ¥è¯¢æ¡ä»¶è¾å¤æ¶ï¼å
¶æ¥è¯¢é度ç®ç´æ æ³å®¹å¿ãæ¾ç»æµè¯å¯¹ä¸ä¸ªå
å«400å¤ä¸æ¡è®°å½ï¼æç´¢å¼ï¼ç表æ§è¡ä¸æ¡æ¡ä»¶æ¥è¯¢ï¼å
¶æ¥è¯¢æ¶é´ç«ç¶é«è¾¾40å ç§ï¼ç¸ä¿¡è¿ä¹é«çæ¥è¯¢å»¶æ¶ï¼ä»»ä½ç¨æ·é½ä¼æçãå æ¤å¦ä½æé«
sqlè¯å¥æ¥è¯¢æçï¼æ¾å¾ååéè¦ã以ä¸æ¯ç½ä¸æµä¼ æ¯è¾å¹¿æ³ç30ç§SQLæ¥è¯¢è¯å¥ä¼åæ¹æ³ï¼
1ãåºå°½éé¿å
å¨ where åå¥ä¸ä½¿ç¨!=æ<>æä½ç¬¦ï¼å¦åå°å¼ææ¾å¼ä½¿ç¨ç´¢å¼èè¿è¡å
¨è¡¨æ«æã
2ã对æ¥è¯¢è¿è¡ä¼åï¼åºå°½éé¿å
å
¨è¡¨æ«æï¼é¦å
åºèèå¨ where å
order by æ¶åçåä¸å»ºç«ç´¢å¼ã
3ãåºå°½éé¿å
å¨ where åå¥ä¸å¯¹å段è¿è¡ null å¼å¤æï¼å¦åå°å¯¼è´å¼ææ¾å¼ä½¿ç¨ç´¢å¼èè¿è¡å
¨è¡¨æ«æï¼å¦ï¼
select id from t where num is null
å¯ä»¥å¨numä¸è®¾ç½®é»è®¤å¼0ï¼ç¡®ä¿è¡¨ä¸numå没ænullå¼ï¼ç¶åè¿æ ·æ¥è¯¢ï¼
select id from t where num=0
4ãå°½éé¿å
å¨ where åå¥ä¸ä½¿ç¨ or æ¥è¿æ¥æ¡ä»¶ï¼å¦åå°å¯¼è´å¼ææ¾å¼ä½¿ç¨ç´¢å¼èè¿è¡å
¨è¡¨æ«æï¼å¦ï¼
select id from t where num=10 or num=20
å¯ä»¥è¿æ ·æ¥è¯¢ï¼
select id from t where num=10
union all
select id from t where num=20
5ãä¸é¢çæ¥è¯¢ä¹å°å¯¼è´å
¨è¡¨æ«æï¼(ä¸è½åç½®
ç¾åå·)
select id from t where name like â%c%â
è¥è¦æé«æçï¼å¯ä»¥èèå
¨ææ£ç´¢ã
6ãin å not in ä¹è¦æ
ç¨ï¼å¦åä¼å¯¼è´å
¨è¡¨æ«æï¼å¦ï¼
select id from t where num in(1,2,3)
对äºè¿ç»çæ°å¼ï¼è½ç¨ between å°±ä¸è¦ç¨ in äºï¼
select id from t where num between 1 and 3
7ãå¦æå¨ where åå¥ä¸ä½¿ç¨åæ°ï¼ä¹ä¼å¯¼è´å
¨è¡¨æ«æãå 为SQLåªæå¨è¿è¡æ¶æä¼è§£æå±é¨åéï¼ä½ä¼åç¨åºä¸è½å°è®¿é®è®¡åçéæ©æ¨è¿å°è¿è¡æ¶ï¼å®å¿
é¡»å¨ç¼è¯æ¶è¿è¡éæ©ãç¶ èï¼å¦æå¨ç¼è¯æ¶å»ºç«è®¿é®è®¡åï¼åéçå¼è¿æ¯æªç¥çï¼å èæ æ³ä½ä¸ºç´¢å¼éæ©çè¾å
¥é¡¹ãå¦ä¸é¢è¯å¥å°è¿è¡å
¨è¡¨æ«æï¼
select id from t where num=@num
å¯ä»¥æ¹ä¸ºå¼ºå¶æ¥è¯¢ä½¿ç¨ç´¢å¼ï¼
select id from t with(index(ç´¢å¼å)) where num=@num
8ãåºå°½éé¿å
å¨ where åå¥ä¸å¯¹å段è¿è¡
表达å¼æä½ï¼è¿å°å¯¼è´å¼ææ¾å¼ä½¿ç¨ç´¢å¼èè¿è¡å
¨è¡¨æ«æãå¦ï¼
select id from t where num/2=100
åºæ¹ä¸º:
select id from t where num=100*2
9ãåºå°½éé¿å
å¨whereåå¥ä¸å¯¹å段è¿è¡å½æ°æä½ï¼è¿å°å¯¼è´å¼ææ¾å¼ä½¿ç¨ç´¢å¼èè¿è¡å
¨è¡¨æ«æãå¦ï¼
select id from t where substring(name,1,3)=âabcââname以abcå¼å¤´çid
select id from t where datediff(day,createdate,â2005-11-30â²)=0ââ2005-11-30â²çæçid
åºæ¹ä¸º:
select id from t where name like âabc%â
select id from t where createdate>=â2005-11-30â² and createdate<â2005-12-1â²
10ãä¸è¦å¨ where åå¥ä¸çâ=â左边è¿è¡å½æ°ãç®æ¯è¿ç®æå
¶ä»è¡¨è¾¾å¼è¿ç®ï¼å¦åç³»ç»å°å¯è½æ æ³æ£ç¡®ä½¿ç¨ç´¢å¼ã
11ãå¨ä½¿ç¨ç´¢å¼å段ä½ä¸ºæ¡ä»¶æ¶ï¼å¦æ该索å¼æ¯å¤åç´¢å¼ï¼é£ä¹å¿
须使ç¨å°è¯¥ç´¢å¼ä¸ç第ä¸ä¸ªå段ä½ä¸ºæ¡ä»¶æ¶æè½ä¿è¯ç³»ç»ä½¿ç¨è¯¥ç´¢å¼ï¼å¦å该索å¼å°ä¸ä¼è¢«ä½¿ ç¨ï¼å¹¶ä¸åºå°½å¯è½ç让å段顺åºä¸ç´¢å¼é¡ºåºç¸ä¸è´ã
12ãä¸è¦åä¸äºæ²¡ææä¹çæ¥è¯¢ï¼å¦éè¦çæä¸ä¸ªç©ºè¡¨ç»æï¼
select col1,col2 into #t from t where 1=0
è¿ç±»ä»£ç ä¸ä¼è¿åä»»ä½ç»æéï¼ä½æ¯ä¼æ¶èç³»ç»èµæºçï¼åºæ¹æè¿æ ·ï¼
create table #t(â¦)
13ãå¾å¤æ¶åç¨ exists ä»£æ¿ in æ¯ä¸ä¸ªå¥½çéæ©ï¼
select num from a where num in(select num from b)
ç¨ä¸é¢çè¯å¥æ¿æ¢ï¼
select num from a where exists(select 1 from b where num=a.num)
14ã并ä¸æ¯ææç´¢å¼å¯¹æ¥è¯¢é½ææï¼SQLæ¯æ ¹æ®è¡¨ä¸æ°æ®æ¥è¿è¡æ¥è¯¢ä¼åçï¼å½ç´¢å¼åæ大éæ°æ®éå¤æ¶ï¼SQLæ¥è¯¢å¯è½ä¸ä¼å»å©ç¨ç´¢å¼ï¼å¦ä¸è¡¨ä¸æå段 sexï¼maleãfemaleå ä¹åä¸åï¼é£ä¹å³ä½¿å¨sexä¸å»ºäºç´¢å¼ä¹å¯¹æ¥è¯¢æçèµ·ä¸äºä½ç¨ã
15ãç´¢å¼å¹¶ä¸æ¯è¶å¤è¶å¥½ï¼ç´¢å¼åºç¶å¯ä»¥æé«ç¸åºç select çæçï¼ä½åæ¶ä¹éä½äº insert å update çæçï¼å 为 insert æ update æ¶æå¯è½ä¼é建索å¼ï¼æ以ææ ·å»ºç´¢å¼éè¦æ
éèèï¼è§å
·ä½æ
åµèå®ãä¸ä¸ªè¡¨çç´¢å¼æ°æ好ä¸è¦è¶
è¿6个ï¼è¥å¤ªå¤ååºèèä¸äºä¸å¸¸ä½¿ç¨å°çåä¸å»ºçç´¢å¼æ¯å¦æ å¿
è¦ã
16.åºå°½å¯è½çé¿å
æ´æ° clustered ç´¢å¼æ°æ®åï¼å 为 clustered ç´¢å¼æ°æ®åç顺åºå°±æ¯è¡¨è®°å½çç©çåå¨é¡ºåºï¼ä¸æ¦è¯¥åå¼æ¹åå°å¯¼è´æ´ä¸ªè¡¨è®°å½ç顺åºçè°æ´ï¼ä¼èè´¹ç¸å½å¤§çèµæºãè¥åºç¨ç³»ç»éè¦é¢ç¹æ´æ° clustered ç´¢å¼æ°æ®åï¼é£ä¹éè¦èèæ¯å¦åºå°è¯¥ç´¢å¼å»ºä¸º clustered ç´¢å¼ã
17ãå°½é使ç¨æ°ååå段ï¼è¥åªå«æ°å¼ä¿¡æ¯çå段尽éä¸è¦è®¾è®¡ä¸ºå符åï¼è¿ä¼éä½æ¥è¯¢åè¿æ¥çæ§è½ï¼å¹¶ä¼å¢å åå¨å¼éãè¿æ¯å 为å¼æå¨å¤çæ¥è¯¢åè¿æ¥æ¶ä¼ é个æ¯è¾
å符串ä¸æ¯ä¸ä¸ªå符ï¼è对äºæ°ååèè¨åªéè¦æ¯è¾ä¸æ¬¡å°±å¤äºã
18ãå°½å¯è½çä½¿ç¨ varchar/nvarchar ä»£æ¿ char/nchar ï¼å 为é¦å
åé¿å段åå¨ç©ºé´å°ï¼å¯ä»¥èçåå¨ç©ºé´ï¼å
¶æ¬¡å¯¹äºæ¥è¯¢æ¥è¯´ï¼å¨ä¸ä¸ªç¸å¯¹è¾å°çå段å
æç´¢æçæ¾ç¶è¦é«äºã
19ãä»»ä½å°æ¹é½ä¸è¦ä½¿ç¨ select * from t ï¼ç¨å
·ä½çå段å表代æ¿â*âï¼ä¸è¦è¿åç¨ä¸å°çä»»ä½å段ã
20ãå°½é使ç¨è¡¨åéæ¥ä»£æ¿ä¸´æ¶è¡¨ãå¦æ表åéå
å«å¤§éæ°æ®ï¼è¯·æ³¨æç´¢å¼é常æéï¼åªæ
主é®ç´¢å¼ï¼ã
21ãé¿å
é¢ç¹å建åå é¤ä¸´æ¶è¡¨ï¼ä»¥åå°ç³»ç»è¡¨èµæºçæ¶èã
22ã临æ¶è¡¨å¹¶ä¸æ¯ä¸å¯ä½¿ç¨ï¼éå½å°ä½¿ç¨å®ä»¬å¯ä»¥ä½¿æäºä¾ç¨æ´ææï¼ä¾å¦ï¼å½éè¦éå¤å¼ç¨å¤§å表æ常ç¨è¡¨ä¸çæ个
æ°æ®éæ¶ãä½æ¯ï¼å¯¹äºä¸æ¬¡æ§äºä»¶ï¼æ好使 ç¨å¯¼åºè¡¨ã
23ãå¨æ°å»ºä¸´æ¶è¡¨æ¶ï¼å¦æä¸æ¬¡æ§æå
¥æ°æ®éå¾å¤§ï¼é£ä¹å¯ä»¥ä½¿ç¨ select into ä»£æ¿ create tableï¼é¿å
é æ大é log ï¼ä»¥æé«é度ï¼å¦ææ°æ®éä¸å¤§ï¼ä¸ºäºç¼åç³»ç»è¡¨çèµæºï¼åºå
create tableï¼ç¶åinsertã
24ãå¦æ使ç¨å°äºä¸´æ¶è¡¨ï¼å¨
åå¨è¿ç¨çæåå¡å¿
å°ææç临æ¶è¡¨æ¾å¼å é¤ï¼å
truncate table ï¼ç¶å drop table ï¼è¿æ ·å¯ä»¥é¿å
ç³»ç»è¡¨çè¾é¿æ¶é´éå®ã
25ãå°½éé¿å
使ç¨
游æ ï¼å 为游æ çæçè¾å·®ï¼å¦æ游æ æä½çæ°æ®è¶
è¿1ä¸è¡ï¼é£ä¹å°±åºè¯¥èèæ¹åã
26ã使ç¨åºäºæ¸¸æ çæ¹æ³æ临æ¶è¡¨æ¹æ³ä¹åï¼åºå
寻æ¾åºäºéç解å³æ¹æ¡æ¥è§£å³é®é¢ï¼åºäºéçæ¹æ³é常æ´ææã
27ãä¸ä¸´æ¶è¡¨ä¸æ ·ï¼æ¸¸æ 并ä¸æ¯ä¸å¯ä½¿ç¨ã对å°åæ°æ®éä½¿ç¨ FAST_FORWARD 游æ é常è¦ä¼äºå
¶ä»éè¡å¤çæ¹æ³ï¼å°¤å
¶æ¯å¨å¿
é¡»å¼ç¨å 个表æè½è·å¾æéçæ°æ®æ¶ãå¨ç»æéä¸å
æ¬âå计âçä¾ç¨é常è¦æ¯ä½¿ç¨æ¸¸æ æ§è¡çé度快ãå¦æå¼åæ¶ é´å
许ï¼åºäºæ¸¸æ çæ¹æ³ååºäºéçæ¹æ³é½å¯ä»¥å°è¯ä¸ä¸ï¼çåªä¸ç§æ¹æ³çæææ´å¥½ã
28ãå¨ææçåå¨è¿ç¨å
触åå¨çå¼å§å¤è®¾ç½® SET NOCOUNT ON ï¼å¨ç»ææ¶è®¾ç½® SET NOCOUNT OFF ãæ éå¨æ§è¡åå¨è¿ç¨å触åå¨çæ¯ä¸ªè¯å¥åå客æ·ç«¯åé DONE_IN_PROC æ¶æ¯ã
29ãå°½éé¿å
å客æ·ç«¯è¿å大æ°æ®éï¼è¥æ°æ®éè¿å¤§ï¼åºè¯¥èèç¸åºéæ±æ¯å¦åçã
30ãå°½éé¿å
大äºå¡æä½ï¼æé«ç³»ç»å¹¶åè½åã