åå¸å¼ç³»ç»å°±æ¯å°ç³»ç»çåºç¨å±ï¼æ°æ®å±æå
¶å®é¨åææ¶æåå¸ï¼ç©çåé»è¾ä¸çé½å¯ä»¥ï¼ç¶ï¼é常æ¯ç½ç¶ï¼ãåå¸å¼ç³»ç»é常æ¯ä¸ºäºå¢å¼ºç³»ç»çå¯æ©å±æ§ã稳å®æ§åæ§è¡æçãæ¯å¦å¨çº¿æ¸¸æé常就æ¯åå¸ç³»ç»ï¼éé¢æè°çâåºâå°±æ¯åå¸ç³»ç»éåä¾ç¨ãèåå¸å¼æ°æ®åºå
¶å®ä¹å¯ä»¥ç§°ä½åå¸å¼ç³»ç»ï¼æ°æ®æä¹
åå±æ¯åå¸çï¼æ°æ®åå¨ä¸åçæ°æ®åºä¸ï¼å¯äº¤äºï¼æä¸å¥ç»¼ç®¡ç³»ç»æ¥ç»´æ¤æ°æ®çå®æ´æ§ååç¡®æ§ï¼ãæ以说åå¸å¼ç³»ç»æ´åç¡®å°è¯´æ¯ä¸ç§ç³»ç»ææ¶æ¦å¿µï¼ä¸æ¯ä¸ç§ææ¯ã
æé«æ¥è¯¢é度ã
1ãç¨ç¨åºä¸ï¼
ä¿è¯å¨å®ç°åè½çåºç¡ä¸ï¼å°½éåå°å¯¹æ°æ®åºç访é®æ¬¡æ°ï¼
éè¿æç´¢åæ°ï¼å°½éåå°å¯¹è¡¨ç访é®è¡æ°
æå°åç»æéï¼ä»èåè½»ç½ç»è´æ
ï¼
å¤åå¼çæä½å°½éåå¼å¤çï¼æé«æ¯æ¬¡çååºé度ï¼
å¨æ°æ®çªå£ä½¿ç¨
SQL æ¶ï¼å°½éæ使ç¨çç´¢å¼æ¾å¨éæ©çé¦åï¼
ç®æ³çç»æå°½éç®åï¼
å¨æ¥è¯¢æ¶ï¼ä¸è¦è¿å¤å°ä½¿ç¨éé
符å¦
SELECT * FROM T1 è¯å¥ï¼è¦ç¨å°å åå°±éæ©å åå¦ï¼
SELECT COL1,COL2 FROM T1 ï¼
å¨å¯è½çæ
åµä¸å°½ééå¶å°½éç»æéè¡æ°å¦ï¼
SELECT TOP 300 COL1,COL2,COL3 FROM T1,
å 为æäºæ
åµä¸ç¨æ·æ¯ä¸éè¦é£ä¹å¤çæ°æ®çã
ä¸è¦å¨åºç¨ä¸ä½¿ç¨æ°æ®åºæ¸¸æ ï¼æ¸¸æ æ¯é常æç¨çå·¥å
·ï¼ä½æ¯ä½¿ç¨å¸¸è§çãé¢åéçSQLè¯å¥éè¦æ´å¤§çå¼éï¼
æç
§ç¹å®é¡ºåºæåæ°æ®çæ¥æ¾ã
2ãé¿å
使ç¨ä¸å
¼å®¹çæ°æ®ç±»åãä¾å¦
floatåintãcharåvarcharãbinaryåvarbinaryæ¯ä¸å
¼å®¹çãæ°æ®ç±»åçä¸å
¼å®¹å¯è½ä½¿ä¼åå¨æ æ³æ§è¡ä¸äºæ¬æ¥å¯ä»¥è¿è¡çä¼åæä½ã
ä¾å¦:
SELECT name FROM employee WHERE salary ï¼60000
å¨è¿æ¡è¯å¥ä¸,å¦salaryå段æ¯moneyåç,åä¼åå¨å¾é¾å¯¹å
¶è¿è¡ä¼å,å 为60000æ¯ä¸ªæ´åæ°ã
æ们åºå½å¨ç¼ç¨æ¶å°æ´å转åæ为é±å¸å,èä¸è¦çå°è¿è¡æ¶è½¬åã
3ãå°½éé¿å
å¨WHEREåå¥ä¸å¯¹å段è¿è¡å½æ°æ表达å¼æä½ï¼è¿å°å¯¼è´å¼ææ¾å¼ä½¿ç¨ç´¢å¼èè¿è¡å
¨è¡¨æ«æãå¦ï¼
SELECT * FROM T1 WHERE F1/2=100 åºæ¹ä¸º
SELECT * FROM T1 WHERE F1=100*2
SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=â5378â
åºæ¹ä¸º:
SELECT * FROM RECORD WHERE CARD_NO LIKE â5378%â
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())
å³ï¼ä»»ä½å¯¹åçæä½é½å°å¯¼è´è¡¨æ«æï¼å®å
æ¬æ°æ®åºå½æ°ã计ç®è¡¨è¾¾å¼ççï¼æ¥è¯¢æ¶è¦å°½å¯è½å°æä½ç§»è³çå·å³è¾¹ã
4ãé¿å
使ç¨!=æï¼ï¼ãIS NULLæIS NOT NULLãIN ï¼NOT INçè¿æ ·çæä½ç¬¦å 为è¿ä¼ä½¿ç³»ç»æ æ³ä½¿ç¨ç´¢å¼,
èåªè½ç´æ¥æ索表ä¸çæ°æ®ãä¾å¦:
SELECT id FROM employee WHERE id != 'B%'
ä¼åå¨å°æ æ³éè¿ç´¢å¼æ¥ç¡®å®å°è¦å½ä¸çè¡æ°,å æ¤éè¦æ索该表çææè¡ã
5ãå°½é使ç¨æ°ååå段ï¼ä¸é¨åå¼å人ååæ°æ®åºç®¡ç人åå欢æå
å«æ°å¼ä¿¡æ¯çå段设计为å符åï¼
è¿ä¼éä½æ¥è¯¢åè¿æ¥çæ§è½ï¼å¹¶ä¼å¢å åå¨å¼éãè¿æ¯å 为å¼æå¨å¤çæ¥è¯¢åè¿æ¥åé个æ¯è¾å符串ä¸æ¯ä¸ä¸ªå符ï¼
è对äºæ°ååèè¨åªéè¦æ¯è¾ä¸æ¬¡å°±å¤äºã
6ãåç使ç¨EXISTS,NOT EXISTSåå¥ãå¦ä¸æ示ï¼
1.SELECT SUM(T1.C1)FROM T1
WHERE( (SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0)
2.SELECT SUM(T1.C1) FROM T1 WHERE EXISTS(
SELECT * FROM T2 WHERE T2.C2=T1.C2)
两è
产çç¸åçç»æï¼ä½æ¯åè
çæçæ¾ç¶è¦é«äºåè
ãå 为åè
ä¸ä¼äº§ç大ééå®ç表æ«ææ
æ¯ç´¢å¼æ«æã
å¦æä½ æ³æ ¡éªè¡¨éæ¯å¦åå¨ææ¡çºªå½ï¼ä¸è¦ç¨count(*)é£æ ·æçå¾ä½ï¼èä¸æµªè´¹æå¡å¨èµæºã
å¯ä»¥ç¨EXISTS代æ¿ãå¦ï¼
IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')
å¯ä»¥åæï¼
IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')
ç»å¸¸éè¦åä¸ä¸ªT_SQLè¯å¥æ¯è¾ä¸ä¸ªç¶ç»æéååç»æéï¼
ä»èæ¾å°æ¯å¦åå¨å¨ç¶ç»æéä¸æèå¨åç»æéä¸æ²¡æçè®°å½ï¼å¦ï¼
1.SELECT a.hdr_key FROM hdr_tbl a
---- tbl a 表示 tbl ç¨å«åa代æ¿
WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)
2.SELECTa.hdr_key FROM hdr_tbl a LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key
WHERE b.hdr_key IS NULL
3.SELECT hdr_key FROM hdr_tbl WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)
ä¸ç§åæ³é½å¯ä»¥å¾å°åæ ·æ£ç¡®çç»æï¼ä½æ¯æçä¾æ¬¡éä½ã
7ãå°½éé¿å
å¨ç´¢å¼è¿çå符æ°æ®ä¸ï¼ä½¿ç¨éæ头åæ¯æç´¢ãè¿ä¹ä½¿å¾å¼ææ æ³å©ç¨ç´¢å¼ã
è§å¦ä¸ä¾åï¼SELECT * FROM T1 WHERE NAME LIKE â%L%â
SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=âLâ
SELECT * FROM T1 WHERE NAME LIKE âL%â
å³ä½¿NAMEå段建æç´¢å¼ï¼å两个æ¥è¯¢ä¾ç¶æ æ³å©ç¨ç´¢å¼å®æå å¿«æä½ï¼å¼æä¸å¾ä¸å¯¹å
¨è¡¨æ
ææ°æ®éæ¡æä½æ¥å®æä»»å¡ãè第ä¸ä¸ªæ¥è¯¢è½å¤ä½¿ç¨ç´¢å¼æ¥å å¿«æä½ã
8ãå
åå©ç¨è¿æ¥æ¡ä»¶ï¼å¨æç§æ
åµä¸ï¼ä¸¤ä¸ªè¡¨ä¹é´å¯è½ä¸åªä¸ä¸ªçè¿æ¥æ¡ä»¶ï¼è¿æ¶å¨
WHERE åå¥ä¸å°è¿æ¥æ¡ä»¶å®æ´çåä¸ï¼æå¯è½å¤§å¤§æé«æ¥è¯¢é度ãä¾ï¼
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO
AND A.ACCOUNT_NO=B.ACCOUNT_NO
第äºå¥å°æ¯ç¬¬ä¸å¥æ§è¡å¿«å¾å¤ã
9ãæ¶é¤å¯¹å¤§å表è¡æ°æ®ç顺åºååï¼å°½ç®¡å¨ææçæ£æ¥åä¸é½æç´¢å¼ï¼ä½æäºå½¢å¼ç
WHEREåå¥å¼ºè¿«ä¼åå¨ä½¿ç¨é¡ºåºååãå¦ï¼
SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR
order_num=1008 解å³åæ³å¯ä»¥ä½¿ç¨å¹¶éæ¥é¿å
顺åºååï¼
SELECT ï¼FROM orders WHERE customer_num=104 AND order_num>1001
UNION SELECT ï¼FROM orders WHERE order_num=1008 è¿æ ·å°±è½å©ç¨ç´¢å¼è·¯å¾å¤çæ¥è¯¢ã
10ãé¿å
å°é¾çæ£è§è¡¨è¾¾å¼ã
LIKEå
³é®åæ¯æéé
符å¹é
ï¼ææ¯
温馨提示:答案为网友推荐,仅供参考