ä¸ã ç®åæ¥è¯¢
ç®åçTransact-SQLæ¥è¯¢åªå
æ¬éæ©å表ãFROMåå¥åWHEREåå¥ãå®ä»¬åå«è¯´æææ¥è¯¢åãæ¥è¯¢ç
表æè§å¾ã以åæç´¢æ¡ä»¶çãä¾å¦ï¼ä¸é¢çè¯å¥æ¥è¯¢testtable表ä¸å§å为âå¼ ä¸âçnicknameå段åemailå段ã
SELECT nickname,email
FROM testtable
WHERE name='å¼ ä¸'
(ä¸) éæ©å表
éæ©å表(select_list)æåºææ¥è¯¢åï¼å®å¯ä»¥æ¯ä¸ç»ååå表ãæå·ã表达å¼ãåé(å
æ¬å±é¨åéåå
¨å±åé)çææã
1ãéæ©ææå
ä¾å¦ï¼ä¸é¢è¯å¥æ¾ç¤ºtesttable表ä¸ææåçæ°æ®ï¼
SELECT *
FROM testtable
2ãéæ©é¨åå并æå®å®ä»¬çæ¾ç¤ºæ¬¡åºæ¥è¯¢ç»æéåä¸æ°æ®çæå顺åºä¸éæ©å表ä¸ææå®çååæå顺åºç¸åã
ä¾å¦ï¼
SELECT nickname,email
FROM testtable
3ãæ´æ¹åæ é¢
å¨éæ©å表ä¸ï¼å¯éæ°æå®åæ é¢ãå®ä¹æ ¼å¼ä¸ºï¼
åæ é¢=åå
åå åæ é¢å¦ææå®çåæ é¢ä¸æ¯æ åçæ è¯ç¬¦æ ¼å¼æ¶ï¼åºä½¿ç¨å¼å·å®ç符ï¼ä¾å¦ï¼ä¸åè¯å¥ä½¿ç¨æ±åæ¾ç¤ºåæ é¢ï¼
SELECT æµç§°=nickname,çµåé®ä»¶=email
FROM testtable
4ãå é¤éå¤è¡
SELECTè¯å¥ä¸ä½¿ç¨ALLæDISTINCTé项æ¥æ¾ç¤ºè¡¨ä¸ç¬¦åæ¡ä»¶çææè¡æå é¤å
¶ä¸éå¤çæ°æ®è¡ï¼é»è®¤
为ALLã使ç¨DISTINCTé项æ¶ï¼å¯¹äºææéå¤çæ°æ®è¡å¨SELECTè¿åçç»æéåä¸åªä¿çä¸è¡ã
5ãéå¶è¿åçè¡æ°
使ç¨TOP n [PERCENT]é项éå¶è¿åçæ°æ®è¡æ°ï¼TOP n说æè¿ånè¡ï¼èTOP n PERCENTæ¶ï¼è¯´ænæ¯
表示ä¸ç¾åæ°ï¼æå®è¿åçè¡æ°çäºæ»è¡æ°çç¾åä¹å ã
ä¾å¦ï¼
SELECT TOP 2 *
FROM testtable
SELECT TOP 20 PERCENT *
FROM testtable
(äº)FROMåå¥
FROMåå¥æå®SELECTè¯å¥æ¥è¯¢åä¸æ¥è¯¢ç¸å
³ç表æè§å¾ãå¨FROMåå¥ä¸æå¤å¯æå®256个表æè§å¾ï¼å®ä»¬ä¹é´ç¨éå·åéãå¨FROMåå¥åæ¶æå®å¤ä¸ªè¡¨æè§å¾æ¶ï¼å¦æéæ©å表ä¸åå¨åååï¼è¿æ¶åºä½¿ç¨å¯¹è±¡åéå®è¿äºå
æå±ç表æè§å¾ãä¾å¦å¨usertableåcitytable表ä¸åæ¶åå¨cityidåï¼å¨æ¥è¯¢ä¸¤ä¸ªè¡¨ä¸çcityidæ¶åº
使ç¨ä¸é¢è¯å¥æ ¼å¼å 以éå®ï¼
SELECT username,citytable.cityid
FROM usertable,citytable
WHERE usertable.cityid=citytable.cityid
å¨FROMåå¥ä¸å¯ç¨ä»¥ä¸ä¸¤ç§æ ¼å¼ä¸ºè¡¨æè§å¾æå®å«åï¼
表å as å«å
表å å«å
(äº) FROMåå¥
FROMåå¥æå®SELECTè¯å¥æ¥è¯¢åä¸æ¥è¯¢ç¸å
³ç表æè§å¾ãå¨FROMåå¥ä¸æå¤å¯æå®256个表æè§å¾ï¼å®ä»¬ä¹é´ç¨éå·åéãå¨FROMåå¥åæ¶æå®å¤ä¸ªè¡¨æè§å¾æ¶ï¼å¦æéæ©å表ä¸åå¨åååï¼è¿æ¶åºä½¿ç¨å¯¹è±¡åéå®è¿äºå
æå±ç表æè§å¾ãä¾å¦å¨usertableåcitytable表ä¸åæ¶åå¨cityidåï¼å¨æ¥è¯¢ä¸¤ä¸ªè¡¨ä¸çcityidæ¶åºä½¿ç¨ä¸é¢è¯å¥æ ¼å¼å 以éå®ï¼
SELECT username,citytable.cityid
FROM usertable,citytable
WHERE usertable.cityid=citytable.cityid
å¨FROMåå¥ä¸å¯ç¨ä»¥ä¸ä¸¤ç§æ ¼å¼ä¸ºè¡¨æè§å¾æå®å«åï¼
表å as å«å
表å å«å
ä¾å¦ä¸é¢è¯å¥å¯ç¨è¡¨çå«åæ ¼å¼è¡¨ç¤ºä¸ºï¼
SELECT username,b.cityid
FROM usertable a,citytable b
WHERE a.cityid=b.cityid
SELECTä¸ä»
è½ä»è¡¨æè§å¾ä¸æ£ç´¢æ°æ®ï¼å®è¿è½å¤ä»å
¶å®æ¥è¯¢è¯å¥æè¿åçç»æéåä¸æ¥è¯¢æ°æ®ã
ä¾å¦ï¼
SELECT a.au_fname+a.au_lname
FROM authors a,titleauthor ta
(SELECT title_id,title
FROM titles
WHERE ytd_sales>10000
) AS t
WHERE a.au_id=ta.au_id
AND ta.title_id=t.title_id
æ¤ä¾ä¸ï¼å°SELECTè¿åçç»æéåç»äºä¸å«åtï¼ç¶ååä»ä¸æ£ç´¢æ°æ®ã
(ä¸) 使ç¨WHEREåå¥è®¾ç½®æ¥è¯¢æ¡ä»¶
WHEREåå¥è®¾ç½®æ¥è¯¢æ¡ä»¶ï¼è¿æ»¤æä¸éè¦çæ°æ®è¡ãä¾å¦ä¸é¢è¯å¥æ¥è¯¢å¹´é¾å¤§äº20çæ°æ®ï¼
SELECT *
FROM usertable
WHERE age>20
WHEREåå¥å¯å
æ¬åç§æ¡ä»¶è¿ç®ç¬¦ï¼
æ¯è¾è¿ç®ç¬¦(大å°æ¯è¾)ï¼>ã>=ã=ã<ã<=ã<>ã!>ã!<
èå´è¿ç®ç¬¦(表达å¼å¼æ¯å¦å¨æå®çèå´)ï¼BETWEENâ¦ANDâ¦
NOT BETWEENâ¦ANDâ¦
å表è¿ç®ç¬¦(å¤æ表达å¼æ¯å¦ä¸ºå表ä¸çæå®é¡¹)ï¼IN (项1,项2â¦â¦)
NOT IN (项1,项2â¦â¦)
模å¼å¹é
符(å¤æå¼æ¯å¦ä¸æå®çå符éé
æ ¼å¼ç¸ç¬¦):LIKEãNOT LIKE
空å¼å¤æ符(å¤æ表达å¼æ¯å¦ä¸ºç©º)ï¼IS NULLãNOT IS NULL
é»è¾è¿ç®ç¬¦(ç¨äºå¤æ¡ä»¶çé»è¾è¿æ¥)ï¼NOTãANDãOR
1ãèå´è¿ç®ç¬¦ä¾ï¼age BETWEEN 10 AND 30ç¸å½äºage>=10 AND age<=30
2ãå表è¿ç®ç¬¦ä¾ï¼country IN ('Germany','China')
3ã模å¼å¹é
符ä¾ï¼å¸¸ç¨äºæ¨¡ç³æ¥æ¾ï¼å®å¤æåå¼æ¯å¦ä¸æå®çåç¬¦ä¸²æ ¼å¼ç¸å¹é
ãå¯ç¨äºcharã
varcharãtextãntextãdatetimeåsmalldatetimeçç±»åæ¥è¯¢ã
å¯ä½¿ç¨ä»¥ä¸éé
å符ï¼
ç¾åå·%ï¼å¯å¹é
ä»»æç±»ååé¿åº¦çå符ï¼å¦ææ¯ä¸æï¼è¯·ä½¿ç¨ä¸¤ä¸ªç¾åå·å³%%ã
ä¸å线_ï¼å¹é
å个任æå符ï¼å®å¸¸ç¨æ¥éå¶è¡¨è¾¾å¼çå符é¿åº¦ã
æ¹æ¬å·[]ï¼æå®ä¸ä¸ªå符ãå符串æèå´ï¼è¦æ±æå¹é
对象为å®ä»¬ä¸çä»»ä¸ä¸ªã
[^]ï¼å
¶åå¼ä¹[] ç¸åï¼ä½å®è¦æ±æå¹é
对象为æå®å符以å¤çä»»ä¸ä¸ªå符ã
ä¾å¦ï¼
éå¶ä»¥Publishingç»å°¾ï¼ä½¿ç¨LIKE '%Publishing'
éå¶ä»¥Aå¼å¤´ï¼LIKE '[A]%'
éå¶ä»¥Aå¼å¤´å¤ï¼LIKE '[^A]%'
4ã空å¼å¤æ符ä¾WHERE age IS NULL
5ãé»è¾è¿ç®ç¬¦ï¼ä¼å
级为NOTãANDãOR
(å)æ¥è¯¢ç»ææåº
使ç¨ORDER BYåå¥å¯¹æ¥è¯¢è¿åçç»ææä¸åæå¤åæåºãORDER BYåå¥çè¯æ³æ ¼å¼ä¸ºï¼
ORDER BY {column_name [ASC|DESC]} [,â¦n]
å
¶ä¸ASC表示ååºï¼ä¸ºé»è®¤å¼ï¼DESC为éåºãORDER BYä¸è½æntextãtextåimageæ°æ®ç±»åè¿è¡æ
åºã
ä¾å¦ï¼
SELECT *
FROM usertable
ORDER BY age desc,userid ASC
å¦å¤ï¼å¯ä»¥æ ¹æ®è¡¨è¾¾å¼è¿è¡æåºã
äºã èåæ¥è¯¢
UNIONè¿ç®ç¬¦å¯ä»¥å°ä¸¤ä¸ªæ两个以ä¸ä¸SELECTè¯å¥çæ¥è¯¢ç»æéåå并æä¸ä¸ªç»æéåæ¾ç¤ºï¼å³æ§è¡è
åæ¥è¯¢ãUNIONçè¯æ³æ ¼å¼ä¸ºï¼
select_statement
UNION [ALL] selectstatement
[UNION [ALL] selectstatement][â¦n]
å
¶ä¸selectstatement为å¾
èåçSELECTæ¥è¯¢è¯å¥ã
ALLé项表示å°ææè¡å并å°ç»æéåä¸ãä¸æå®è¯¥é¡¹æ¶ï¼è¢«èåæ¥è¯¢ç»æéåä¸çéå¤è¡å°åªä¿çä¸è¡ãèåæ¥è¯¢æ¶ï¼æ¥è¯¢ç»æçåæ é¢ä¸ºç¬¬ä¸ä¸ªæ¥è¯¢è¯å¥çåæ é¢ãå æ¤ï¼è¦å®ä¹åæ é¢å¿
é¡»å¨ç¬¬ä¸ä¸ªæ¥è¯¢è¯å¥ä¸å®ä¹ãè¦å¯¹èåæ¥è¯¢ç»ææåºæ¶ï¼ä¹å¿
须使ç¨ç¬¬ä¸æ¥è¯¢è¯å¥ä¸çååãåæ é¢æè
ååºå·ãå¨ä½¿ç¨UNION è¿ç®ç¬¦æ¶ï¼åºä¿è¯æ¯ä¸ªèåæ¥è¯¢è¯å¥çéæ©å表ä¸æç¸åæ°éç表达å¼ï¼å¹¶ä¸æ¯ä¸ªæ¥è¯¢éæ©è¡¨è¾¾å¼åºå
·æç¸åçæ°æ®ç±»åï¼ææ¯å¯ä»¥èªå¨å°å®ä»¬è½¬æ¢ä¸ºç¸åçæ°æ®ç±»åãå¨èªå¨è½¬æ¢æ¶ï¼å¯¹äºæ°å¼ç±»
åï¼ç³»ç»å°ä½ç²¾åº¦çæ°æ®ç±»å转æ¢ä¸ºé«ç²¾åº¦çæ°æ®ç±»åãå¨å
æ¬å¤ä¸ªæ¥è¯¢çUNIONè¯å¥ä¸ï¼å
¶æ§è¡é¡ºåºæ¯èªå·¦è³å³ï¼ä½¿ç¨æ¬å·å¯ä»¥æ¹åè¿ä¸æ§è¡é¡ºåºãä¾å¦ï¼
æ¥è¯¢1 UNION (æ¥è¯¢2 UNION æ¥è¯¢3)
ä¸ãè¿æ¥æ¥è¯¢éè¿è¿æ¥è¿ç®ç¬¦å¯ä»¥å®ç°å¤ä¸ªè¡¨æ¥è¯¢ãè¿æ¥æ¯å
³ç³»æ°æ®åºæ¨¡åç主è¦ç¹ç¹ï¼ä¹æ¯å®åºå«äºå
¶å®ç±»å
æ°æ®åºç®¡çç³»ç»çä¸ä¸ªæ å¿ã
å¨å
³ç³»æ°æ®åºç®¡çç³»ç»ä¸ï¼è¡¨å»ºç«æ¶åæ°æ®ä¹é´çå
³ç³»ä¸å¿
ç¡®å®ï¼å¸¸æä¸ä¸ªå®ä½çææä¿¡æ¯åæ¾å¨ä¸ä¸ªè¡¨ä¸ãå½æ£ç´¢æ°æ®æ¶ï¼éè¿è¿æ¥æä½æ¥è¯¢åºåæ¾å¨å¤ä¸ªè¡¨ä¸çä¸åå®ä½çä¿¡æ¯ãè¿æ¥æä½ç»ç¨æ·å¸¦
æ¥å¾å¤§ççµæ´»æ§ï¼ä»ä»¬å¯ä»¥å¨ä»»ä½æ¶åå¢å æ°çæ°æ®ç±»åã为ä¸åå®ä½å建æ°ç表ï¼å°åéè¿è¿æ¥è¿è¡
æ¥è¯¢ã
è¿æ¥å¯ä»¥å¨SELECT è¯å¥çFROMåå¥æWHEREåå¥ä¸å»ºç«ï¼ä¼¼æ¯èéå¨FROMåå¥ä¸æåºè¿æ¥æ¶æå©äº
å°è¿æ¥æä½ä¸WHEREåå¥ä¸çæç´¢æ¡ä»¶åºåå¼æ¥ãæ以ï¼å¨Transact-SQLä¸æ¨è使ç¨è¿ç§æ¹æ³ã
SQL-92æ åæå®ä¹çFROMåå¥çè¿æ¥è¯æ³æ ¼å¼ä¸ºï¼
FROM join_table join_type join_table
[ON (join_condition)]
å
¶ä¸join_tableæåºåä¸è¿æ¥æä½ç表åï¼è¿æ¥å¯ä»¥å¯¹åä¸ä¸ªè¡¨æä½ï¼ä¹å¯ä»¥å¯¹å¤è¡¨æä½ï¼å¯¹åä¸
个表æä½çè¿æ¥å称åèªè¿æ¥ã
join_type æåºè¿æ¥ç±»åï¼å¯å为ä¸ç§ï¼å
è¿æ¥ãå¤è¿æ¥å交åè¿æ¥ãå
è¿æ¥(INNER JOIN)使ç¨æ¯
è¾è¿ç®ç¬¦è¿è¡è¡¨é´æ(äº)åæ°æ®çæ¯è¾æä½ï¼å¹¶ååºè¿äºè¡¨ä¸ä¸è¿æ¥æ¡ä»¶ç¸å¹é
çæ°æ®è¡ãæ ¹æ®æ使ç¨
çæ¯è¾æ¹å¼ä¸åï¼å
è¿æ¥åå为çå¼è¿æ¥ãèªç¶è¿æ¥åä¸çè¿æ¥ä¸ç§ã
å¤è¿æ¥å为左å¤è¿æ¥(LEFT OUTER JOINæLEFT JOIN)ãå³å¤è¿æ¥(RIGHT OUTER JOINæRIGHT JOIN)
åå
¨å¤è¿æ¥(FULL OUTER JOINæFULL JOIN)ä¸ç§ãä¸å
è¿æ¥ä¸åçæ¯ï¼å¤è¿æ¥ä¸åªååºä¸è¿æ¥æ¡ä»¶ç¸å¹
é
çè¡ï¼èæ¯ååºå·¦è¡¨(å·¦å¤è¿æ¥æ¶)ãå³è¡¨(å³å¤è¿æ¥æ¶)æ两个表(å
¨å¤è¿æ¥æ¶)ä¸ææ符åæç´¢æ¡ä»¶ç
æ°æ®è¡ã
交åè¿æ¥(CROSS JOIN)没æWHERE åå¥ï¼å®è¿åè¿æ¥è¡¨ä¸æææ°æ®è¡çç¬å¡å°ç§¯ï¼å
¶ç»æéåä¸ç
æ°æ®è¡æ°çäºç¬¬ä¸ä¸ªè¡¨ä¸ç¬¦åæ¥è¯¢æ¡ä»¶çæ°æ®è¡æ°ä¹ä»¥ç¬¬äºä¸ªè¡¨ä¸ç¬¦åæ¥è¯¢æ¡ä»¶çæ°æ®è¡æ°ã
è¿æ¥æä½ä¸çON (join_condition) åå¥æåºè¿æ¥æ¡ä»¶ï¼å®ç±è¢«è¿æ¥è¡¨ä¸çååæ¯è¾è¿ç®ç¬¦ãé»è¾
è¿ç®ç¬¦çææã
æ 论åªç§è¿æ¥é½ä¸è½å¯¹textãntextåimageæ°æ®ç±»ååè¿è¡ç´æ¥è¿æ¥ï¼ä½å¯ä»¥å¯¹è¿ä¸ç§åè¿è¡é´æ¥
è¿æ¥ãä¾å¦ï¼
SELECT p1.pub_id,p2.pub_id,p1.pr_info
FROM pub_info AS p1 INNER JOIN pub_info AS p2
ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)
(ä¸)å
è¿æ¥
å
è¿æ¥æ¥è¯¢æä½ååºä¸è¿æ¥æ¡ä»¶å¹é
çæ°æ®è¡ï¼å®ä½¿ç¨æ¯è¾è¿ç®ç¬¦æ¯è¾è¢«è¿æ¥åçåå¼ãå
è¿æ¥å
ä¸ç§ï¼
1ãçå¼è¿æ¥ï¼å¨è¿æ¥æ¡ä»¶ä¸ä½¿ç¨çäºå·(=)è¿ç®ç¬¦æ¯è¾è¢«è¿æ¥åçåå¼ï¼å
¶æ¥è¯¢ç»æä¸ååºè¢«è¿æ¥
表ä¸çææåï¼å
æ¬å
¶ä¸çéå¤åã
2ãä¸çè¿æ¥ï¼ å¨è¿æ¥æ¡ä»¶ä½¿ç¨é¤çäºè¿ç®ç¬¦ä»¥å¤çå
¶å®æ¯è¾è¿ç®ç¬¦æ¯è¾è¢«è¿æ¥çåçåå¼ãè¿äº
è¿ç®ç¬¦å
æ¬>ã>=ã<=ã<ã!>ã!<å<>ã
3ãèªç¶è¿æ¥ï¼å¨è¿æ¥æ¡ä»¶ä¸ä½¿ç¨çäº(=)è¿ç®ç¬¦æ¯è¾è¢«è¿æ¥åçåå¼ï¼ä½å®ä½¿ç¨éæ©å表æåºæ¥è¯¢
ç»æéåä¸æå
æ¬çåï¼å¹¶å é¤è¿æ¥è¡¨ä¸çéå¤åã
ä¾ï¼ä¸é¢ä½¿ç¨çå¼è¿æ¥ååºauthorsåpublishers表ä¸ä½äºåä¸åå¸çä½è
ååºç社ï¼
SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city=p.city
åå¦ä½¿ç¨èªç¶è¿æ¥ï¼å¨éæ©å表ä¸å é¤authors åpublishers 表ä¸éå¤å(cityåstate)ï¼
SELECT a.*,p.pub_id,p.pub_name,p.country
FROM authors AS a INNER JOIN publishers AS p
ON a.city=p.city
(äº)å¤è¿æ¥
å
è¿æ¥æ¶ï¼è¿åæ¥è¯¢ç»æéåä¸çä»
æ¯ç¬¦åæ¥è¯¢æ¡ä»¶( WHERE æç´¢æ¡ä»¶æ HAVING æ¡ä»¶)åè¿æ¥æ¡ä»¶
çè¡ãèéç¨å¤è¿æ¥æ¶ï¼å®è¿åå°æ¥è¯¢ç»æéåä¸çä¸ä»
å
å«ç¬¦åè¿æ¥æ¡ä»¶çè¡ï¼èä¸è¿å
æ¬å·¦è¡¨(å·¦å¤
è¿æ¥æ¶)ãå³è¡¨(å³å¤è¿æ¥æ¶)æ两个边æ¥è¡¨(å
¨å¤è¿æ¥)ä¸çæææ°æ®è¡ã
å¦ä¸é¢ä½¿ç¨å·¦å¤è¿æ¥å°è®ºåå
容åä½è
ä¿¡æ¯è¿æ¥èµ·æ¥ï¼
SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b
ON a.username=b.username
ä¸é¢ä½¿ç¨å
¨å¤è¿æ¥å°city表ä¸çææä½è
以åuser表ä¸çææä½è
ï¼ä»¥åä»ä»¬æå¨çåå¸ï¼
SELECT a.*,b.*
FROM city as a FULL OUTER JOIN user as b
ON a.username=b.username
(ä¸)交åè¿æ¥
交åè¿æ¥ä¸å¸¦WHERE åå¥ï¼å®è¿å被è¿æ¥ç两个表æææ°æ®è¡çç¬å¡å°ç§¯ï¼è¿åå°ç»æéåä¸çæ°
æ®è¡æ°çäºç¬¬ä¸ä¸ªè¡¨ä¸ç¬¦åæ¥è¯¢æ¡ä»¶çæ°æ®è¡æ°ä¹ä»¥ç¬¬äºä¸ªè¡¨ä¸ç¬¦åæ¥è¯¢æ¡ä»¶çæ°æ®è¡æ°ã
ä¾ï¼titles表ä¸æ6ç±»å¾ä¹¦ï¼èpublishers表ä¸æ8家åºç社ï¼åä¸å交åè¿æ¥æ£ç´¢å°çè®°å½æ°å°ç
äº6*8=48è¡ã
SELECT type,pub_name
FROM titles CROSS JOIN publishers
ORDER BY type
温馨提示:答案为网友推荐,仅供参考