大家é½å¨è®¨è®ºå
³äºæ°æ®åºä¼åæ¹é¢çä¸ä¸ï¼å好åä¸å¼åäºä¸ä¸ªæ°æ®ä»åºæ¹é¢ç项ç®ï¼ä»¥ä¸çä¸ç¹ä¸è¥¿ç®æ¯æ°æ®åºä¼åæ¹é¢çå¦ä¹ å®æçä¸äºå¿å¾ä½ä¼äºï¼æ¿åºæ¥å¤§å®¶å
±äº«ã欢è¿æ¹è¯ææ£é¿ï¼
SQLè¯å¥ï¼
æ¯å¯¹æ°æ®åº(æ°æ®)è¿è¡æä½çæä¸éå¾ï¼
æ¶èäº70%~90%çæ°æ®åºèµæºï¼ç¬ç«äºç¨åºè®¾è®¡é»è¾ï¼ç¸å¯¹äºå¯¹ç¨åºæºä»£ç çä¼åï¼å¯¹SQLè¯å¥çä¼åå¨æ¶é´ææ¬åé£é©ä¸ç代价é½å¾ä½ï¼
å¯ä»¥æä¸åçåæ³ï¼æå¦ï¼é¾ç²¾éã
SQLä¼åï¼
åºå®çSQL书åä¹ æ¯ï¼ç¸åçæ¥è¯¢å°½éä¿æç¸åï¼åå¨è¿ç¨çæçè¾é«ã
åºè¯¥ç¼åä¸å
¶æ ¼å¼ä¸è´çè¯å¥ï¼å
æ¬åæ¯ç大å°åãæ ç¹ç¬¦å·ãæ¢è¡çä½ç½®çé½è¦ä¸è´
ORACLEä¼åå¨ï¼
å¨ä»»ä½å¯è½çæ¶åé½ä¼å¯¹è¡¨è¾¾å¼è¿è¡è¯ä¼°ï¼å¹¶ä¸æç¹å®çè¯æ³ç»æ转æ¢æçä»·çç»æï¼è¿ä¹åçåå æ¯
è¦ä¹ç»æ表达å¼è½å¤æ¯æºè¡¨è¾¾å¼å
·ææ´å¿«çé度
è¦ä¹æºè¡¨è¾¾å¼åªæ¯ç»æ表达å¼çä¸ä¸ªçä»·è¯ä¹ç»æ
ä¸åçSQLç»æææ¶å
·æåæ ·çæä½ï¼ä¾å¦ï¼= ANY (subquery) and IN (subquery)ï¼ï¼ORACLEä¼æä»ä»¬æ å°å°ä¸ä¸ªåä¸çè¯ä¹ç»æã
1 常éä¼åï¼
常éç计ç®æ¯å¨è¯å¥è¢«ä¼åæ¶ä¸æ¬¡æ§å®æï¼èä¸æ¯å¨æ¯æ¬¡æ§è¡æ¶ãä¸é¢æ¯æ£ç´¢æèªå¤§äº2000çç表达å¼ï¼
sal > 24000/12
sal > 2000
sal*12 > 24000
å¦æSQLè¯å¥å
æ¬ç¬¬ä¸ç§æ
åµï¼ä¼åå¨ä¼ç®åå°æå®è½¬åæ第äºç§ã
ä¼åå¨ä¸ä¼ç®åè·¨è¶æ¯è¾ç¬¦ç表达å¼ï¼ä¾å¦ç¬¬ä¸æ¡è¯å¥ï¼é´äºæ¤ï¼åºå°½éåç¨å¸¸éè·å段æ¯è¾æ£ç´¢ç表达å¼ï¼èä¸è¦å°å段置äºè¡¨è¾¾å¼å½ä¸ãå¦å没æåæ³ä¼åï¼æ¯å¦å¦æsalä¸æç´¢å¼ï¼ç¬¬ä¸å第äºå°±å¯ä»¥ä½¿ç¨ï¼ç¬¬ä¸å°±é¾ä»¥ä½¿ç¨ã
2 æä½ç¬¦ä¼åï¼
ä¼åå¨æ使ç¨LIKEæä½ç¬¦åä¸ä¸ªæ²¡æéé
符ç表达å¼ç»æçæ£ç´¢è¡¨è¾¾å¼è½¬æ¢ä¸ºä¸ä¸ªâ=âæä½ç¬¦è¡¨è¾¾å¼ã
ä¾å¦ï¼ä¼åå¨ä¼æ表达å¼ename LIKE 'SMITH'转æ¢ä¸ºename = 'SMITH'
ä¼åå¨åªè½è½¬æ¢æ¶åå°å¯åé¿æ°æ®ç±»åç表达å¼ï¼åä¸ä¸ªä¾åä¸ï¼å¦æENAMEå段çç±»åæ¯CHAR(10)ï¼ é£ä¹ä¼åå¨å°ä¸åä»»ä½è½¬æ¢ã
ä¸è¬æ¥è®²LIKEæ¯è¾é¾ä»¥ä¼åã
å
¶ä¸ï¼
~~ IN æä½ç¬¦ä¼åï¼
ä¼åå¨æ使ç¨INæ¯è¾ç¬¦çæ£ç´¢è¡¨è¾¾å¼æ¿æ¢ä¸ºçä»·ç使ç¨â=âåâORâæä½ç¬¦çæ£ç´¢è¡¨è¾¾å¼ã
ä¾å¦ï¼ä¼åå¨ä¼æ表达å¼ename IN ('SMITH','KING','JONES')æ¿æ¢ä¸º
ename = 'SMITH' OR ename = 'KING' OR ename = 'JONESâ
~~ ANYåSOME æä½ç¬¦ä¼å:
ä¼åå¨å°è·éå¼å表çANYåSOMEæ£ç´¢æ¡ä»¶ç¨çä»·çåçæä½ç¬¦åâORâç»æç表达å¼æ¿æ¢ã
ä¾å¦ï¼ä¼åå¨å°å¦ä¸æ示ç第ä¸æ¡è¯å¥ç¨ç¬¬äºæ¡è¯å¥æ¿æ¢ï¼
sal > ANY (:first_sal, :second_sal)
sal > :first_sal OR sal > :second_sal
ä¼åå¨å°è·éåæ¥è¯¢çANYåSOMEæ£ç´¢æ¡ä»¶è½¬æ¢æç±âEXISTSâåä¸ä¸ªç¸åºçåæ¥è¯¢ç»æçæ£ç´¢è¡¨è¾¾å¼ã
ä¾å¦ï¼ä¼åå¨å°å¦ä¸æ示ç第ä¸æ¡è¯å¥ç¨ç¬¬äºæ¡è¯å¥æ¿æ¢ï¼
x > ANY (SELECT sal FROM emp WHERE job = 'ANALYST')
EXISTS (SELECT sal FROM emp WHERE job = 'ANALYST' AND x > sal)
~~ ALLæä½ç¬¦ä¼å:
ä¼åå¨å°è·éå¼å表çALLæä½ç¬¦ç¨çä»·çâ=âåâANDâç»æç表达å¼æ¿æ¢ãä¾å¦ï¼
sal > ALL (:first_sal, :second_sal)表达å¼ä¼è¢«æ¿æ¢ä¸ºï¼
sal > :first_sal AND sal > :second_sal
对äºè·éåæ¥è¯¢çALL表达å¼ï¼ä¼åå¨ç¨ANYåå¦å¤ä¸ä¸ªåéçæ¯è¾ç¬¦ç»æç表达å¼æ¿æ¢ãä¾å¦
x > ALL (SELECT sal FROM emp WHERE deptno = 10) æ¿æ¢ä¸ºï¼
NOT (x <= ANY (SELECT sal FROM emp WHERE deptno = 10))
æ¥ä¸æ¥ä¼åå¨ä¼æ第äºä¸ªè¡¨è¾¾å¼éç¨ANY表达å¼ç转æ¢è§å转æ¢ä¸ºä¸é¢ç表达å¼ï¼
NOT EXISTS (SELECT sal FROM emp WHERE deptno = 10 AND x <= sal)
~~ BETWEEN æä½ç¬¦ä¼å:
ä¼åå¨æ»æ¯ç¨â>=âåâ<=âæ¯è¾ç¬¦æ¥çä»·ç代æ¿BETWEENæä½ç¬¦ã
ä¾å¦ï¼ä¼åå¨ä¼æ表达å¼sal BETWEEN 2000 AND 3000ç¨sal >= 2000 AND sal <= 3000æ¥ä»£æ¿ã
~~ NOT æä½ç¬¦ä¼å:
ä¼åå¨æ»æ¯è¯å¾ç®åæ£ç´¢æ¡ä»¶ä»¥æ¶é¤âNOTâé»è¾æä½ç¬¦çå½±åï¼è¿å°æ¶åå°âNOTâæä½ç¬¦çæ¶é¤ä»¥å代以ç¸åºçæ¯è¾è¿ç®ç¬¦ã
ä¾å¦ï¼ä¼åå¨å°ä¸é¢ç第ä¸æ¡è¯å¥ç¨ç¬¬äºæ¡è¯å¥ä»£æ¿ï¼
NOT deptno = (SELECT deptno FROM emp WHERE ename = 'TAYLOR')
deptno <> (SELECT deptno FROM emp WHERE ename = 'TAYLOR')
é常æ
åµä¸ä¸ä¸ªå«æNOTæä½ç¬¦çè¯å¥æå¾å¤ä¸åçåæ³ï¼ä¼åå¨ç转æ¢ååæ¯ä½¿âNOTâæä½ç¬¦åè¾¹çåå¥å°½å¯è½çç®åï¼å³ä½¿å¯è½ä¼ä½¿ç»æ表达å¼å
å«äºæ´å¤çâNOTâæä½ç¬¦ã
ä¾å¦ï¼ä¼åå¨å°å¦ä¸æ示ç第ä¸æ¡è¯å¥ç¨ç¬¬äºæ¡è¯å¥ä»£æ¿ï¼
NOT (sal < 1000 OR comm IS NULL)
NOT sal < 1000 AND comm IS NOT NULL sal >= 1000 AND comm IS NOT NULL
å¦ä½ç¼åé«æçSQL:
å½ç¶è¦èèsql常éçä¼ååæä½ç¬¦çä¼åå¦ï¼å¦å¤ï¼è¿éè¦ï¼
1 åççç´¢å¼è®¾è®¡ï¼
ä¾ï¼è¡¨recordæ620000è¡ï¼è¯çå¨ä¸åçç´¢å¼ä¸ï¼ä¸é¢å 个SQLçè¿è¡æ
åµï¼
è¯å¥A
SELECT count(*) FROM record
WHERE date >'19991201' and date < '19991214â and amount >2000
è¯å¥B
SELECT count(*) FROM record
WHERE date >'19990901' and place IN ('BJ','SH')
è¯å¥C
SELECT date,sum(amount) FROM record
group by date
1 å¨dateä¸å»ºæä¸ä¸ªéèéç´¢å¼
Aï¼(25ç§)
Bï¼(27ç§)
Cï¼(55ç§)
åæï¼
dateä¸æ大éçéå¤å¼ï¼å¨éèéç´¢å¼ä¸ï¼æ°æ®å¨ç©çä¸éæºåæ¾å¨æ°æ®é¡µä¸ï¼å¨èå´æ¥æ¾æ¶ï¼å¿
é¡»æ§è¡ä¸æ¬¡è¡¨æ«ææè½æ¾å°è¿ä¸èå´å
çå
¨é¨è¡ã
2 å¨dateä¸çä¸ä¸ªèéç´¢å¼
Aï¼ï¼14ç§ï¼
Bï¼ï¼14ç§ï¼
Cï¼ï¼28ç§ï¼
åæï¼
å¨èéç´¢å¼ä¸ï¼æ°æ®å¨ç©çä¸æ顺åºå¨æ°æ®é¡µä¸ï¼éå¤å¼ä¹æåå¨ä¸èµ·ï¼å èå¨èå´æ¥æ¾æ¶ï¼å¯ä»¥å
æ¾å°è¿ä¸ªèå´çèµ·æ«ç¹ï¼ä¸åªå¨è¿ä¸ªèå´å
æ«ææ°æ®é¡µï¼é¿å
äºå¤§èå´æ«æï¼æé«äºæ¥è¯¢é度ã
3 å¨placeï¼dateï¼amountä¸çç»åç´¢å¼
Aï¼ï¼26ç§ï¼
Cï¼ï¼27ç§ï¼
Bï¼ï¼< 1ç§ï¼
åæï¼
è¿æ¯ä¸ä¸ªä¸å¾åççç»åç´¢å¼ï¼å 为å®çå导åæ¯placeï¼ç¬¬ä¸å第äºæ¡SQL没æå¼ç¨placeï¼å æ¤ä¹æ²¡æå©ç¨ä¸ç´¢å¼ï¼ç¬¬ä¸ä¸ªSQL使ç¨äºplaceï¼ä¸å¼ç¨çææåé½å
å«å¨ç»åç´¢å¼ä¸ï¼å½¢æäºç´¢å¼è¦çï¼æ以å®çé度æ¯é常快çã
4 å¨dateï¼placeï¼amountä¸çç»åç´¢å¼
Aï¼ (< 1ç§)
Bï¼ï¼< 1ç§ï¼
Cï¼ï¼11ç§ï¼
åæï¼
è¿æ¯ä¸ä¸ªåççç»åç´¢å¼ãå®å°dateä½ä¸ºå导åï¼ä½¿æ¯ä¸ªSQLé½å¯ä»¥å©ç¨ç´¢å¼ï¼å¹¶ä¸å¨ç¬¬ä¸å第ä¸ä¸ªSQLä¸å½¢æäºç´¢å¼è¦çï¼å èæ§è½è¾¾å°äºæä¼ã
æ»ç»1
缺çæ
åµä¸å»ºç«çç´¢å¼æ¯éèéç´¢å¼ï¼ä½ææ¶å®å¹¶ä¸æ¯æä½³çï¼åççç´¢å¼è®¾è®¡è¦å»ºç«å¨å¯¹åç§æ¥è¯¢çåæåé¢æµä¸ãä¸è¬æ¥è¯´ï¼
æ大ééå¤å¼ãä¸ç»å¸¸æèå´æ¥è¯¢ï¼between, >,< ï¼>=,< =ï¼åorder byãgroup byåççåï¼èè建ç«èéç´¢å¼ï¼
ç» å¸¸åæ¶ååå¤åï¼ä¸æ¯åé½å«æéå¤å¼å¯èè建ç«ç»åç´¢å¼ï¼å¨æ¡ä»¶è¡¨è¾¾å¼ä¸ç»å¸¸ç¨å°çä¸åå¼è¾å¤çåä¸å»ºç«æ£ç´¢ï¼å¨ä¸åå¼å°çåä¸ä¸è¦å»ºç«ç´¢å¼ãæ¯å¦å¨éå 表çâæ§å«âåä¸åªæâç·âä¸â女â两个ä¸åå¼ï¼å æ¤å°±æ å¿
è¦å»ºç«ç´¢å¼ãå¦æ建ç«ç´¢å¼ä¸ä½ä¸ä¼æé«æ¥è¯¢æçï¼åèä¼ä¸¥ééä½æ´æ°é度ã
ç»åç´¢å¼è¦å°½é使å
³é®æ¥è¯¢å½¢æç´¢å¼è¦çï¼å
¶å导åä¸å®æ¯ä½¿ç¨æé¢ç¹çåã
2 é¿å
使ç¨ä¸å
¼å®¹çæ°æ®ç±»åï¼
ä¾å¦floatåINtãcharåvarcharãbINaryåvarbINaryæ¯ä¸å
¼å®¹çãæ°æ®ç±»åçä¸å
¼å®¹å¯è½ä½¿ä¼åå¨æ æ³æ§è¡ä¸äºæ¬æ¥å¯ä»¥è¿è¡çä¼åæä½ãä¾å¦:
SELECT name FROM employee WHERE salary ï¼ 60000
å¨è¿æ¡è¯å¥ä¸,å¦salaryå段æ¯moneyåç,åä¼åå¨å¾é¾å¯¹å
¶è¿è¡ä¼å,å 为60000æ¯ä¸ªæ´åæ°ãæ们åºå½å¨ç¼ç¨æ¶å°æ´å转åæ为é±å¸å,èä¸è¦çå°è¿è¡æ¶è½¬åã
3 IS NULL ä¸IS NOT NULLï¼
ä¸ è½ç¨nullä½ç´¢å¼ï¼ä»»ä½å
å«nullå¼çåé½å°ä¸ä¼è¢«å
å«å¨ç´¢å¼ä¸ãå³ä½¿ç´¢å¼æå¤åè¿æ ·çæ
åµä¸ï¼åªè¦è¿äºåä¸æä¸åå«ænullï¼è¯¥åå°±ä¼ä»ç´¢å¼ä¸æ é¤ãä¹å°±æ¯è¯´å¦ææååå¨ç©ºå¼ï¼å³ä½¿å¯¹è¯¥å建索å¼ä¹ä¸ä¼æé«æ§è½ãä»»ä½å¨WHEREåå¥ä¸ä½¿ç¨is nullæis not nullçè¯å¥ä¼åå¨æ¯ä¸å
许使ç¨ç´¢å¼çã
4 INåEXISTSï¼
EXISTSè¦è¿æ¯INçæçé«ãéé¢å
³ç³»å°full table scanårange scanãå ä¹å°ææçINæä½ç¬¦åæ¥è¯¢æ¹å为使ç¨EXISTSçåæ¥è¯¢ã
ä¾åï¼
è¯å¥1
SELECT dname, deptno FROM dept
WHERE deptno NOT IN
(SELECT deptno FROM emp);
è¯å¥2
SELECT dname, deptno FROM dept
WHERE NOT EXISTS
(SELECT deptno FROM emp
WHERE dept.deptno = emp.deptno);
ææ¾çï¼2è¦æ¯1çæ§è¡æ§è½å¥½å¾å¤
å 为1ä¸å¯¹empè¿è¡äºfull table scan,è¿æ¯å¾æµªè´¹æ¶é´çæä½ãèä¸1ä¸æ²¡æç¨å°empçINdexï¼
å 为没æWHEREåå¥ãè2ä¸çè¯å¥å¯¹empè¿è¡çæ¯range scanã
5 INãORåå¥å¸¸ä¼ä½¿ç¨å·¥ä½è¡¨ï¼ä½¿ç´¢å¼å¤±æï¼
å¦æä¸äº§ç大ééå¤å¼ï¼å¯ä»¥èèæåå¥æå¼ãæå¼çåå¥ä¸åºè¯¥å
å«ç´¢å¼ã
6 é¿å
æç®åæåºï¼
åºå½ç®åæé¿å
对大å表è¿è¡éå¤çæåºãå½è½å¤å©ç¨ç´¢å¼èªå¨ä»¥éå½ç次åºäº§çè¾åºæ¶ï¼ä¼åå¨å°±é¿å
äºæåºçæ¥éª¤ã以ä¸æ¯ä¸äºå½±åå ç´ ï¼
ç´¢å¼ä¸ä¸å
æ¬ä¸ä¸ªæå 个å¾
æåºçåï¼
group byæorder byåå¥ä¸åç次åºä¸ç´¢å¼ç次åºä¸ä¸æ ·ï¼
æåºçåæ¥èªä¸åç表ã
为äºé¿å
ä¸å¿
è¦çæåºï¼å°±è¦æ£ç¡®å°å¢å»ºç´¢å¼ï¼åçå°å并æ°æ®åºè¡¨ï¼å°½ç®¡ææ¶å¯è½å½±å表çè§èåï¼ä½ç¸å¯¹äºæççæé«æ¯å¼å¾çï¼ãå¦ææåºä¸å¯é¿å
ï¼é£ä¹åºå½è¯å¾ç®åå®ï¼å¦ç¼©å°æåºçåçèå´çã
7 æ¶é¤å¯¹å¤§å表è¡æ°æ®ç顺åºååï¼
å¨ åµå¥æ¥è¯¢ä¸ï¼å¯¹è¡¨ç顺åºåå对æ¥è¯¢æçå¯è½äº§çè´å½çå½±åãæ¯å¦éç¨é¡ºåºååçç¥ï¼ä¸ä¸ªåµå¥3å±çæ¥è¯¢ï¼å¦ææ¯å±é½æ¥è¯¢1000è¡ï¼é£ä¹è¿ä¸ªæ¥è¯¢å°±è¦æ¥è¯¢ 10亿è¡æ°æ®ãé¿å
è¿ç§æ
åµç主è¦æ¹æ³å°±æ¯å¯¹è¿æ¥çåè¿è¡ç´¢å¼ãä¾å¦ï¼ä¸¤ä¸ªè¡¨ï¼å¦ç表ï¼å¦å·ãå§åãå¹´é¾??ï¼åé课表ï¼å¦å·ã课ç¨å·ãæ绩ï¼ãå¦æ两个 表è¦åè¿æ¥ï¼å°±è¦å¨âå¦å·âè¿ä¸ªè¿æ¥å段ä¸å»ºç«ç´¢å¼ã
è¿å¯ä»¥ä½¿ç¨å¹¶éæ¥é¿å
顺åºååã尽管å¨ææçæ£æ¥åä¸é½æç´¢å¼ï¼ä½æäºå½¢å¼çWHEREåå¥å¼ºè¿«ä¼åå¨ä½¿ç¨é¡ºåºååãä¸é¢çæ¥è¯¢å°å¼ºè¿«å¯¹orders表æ§è¡é¡ºåºæä½ï¼
SELECT ï¼ FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
è½ç¶å¨customer_numåorder_numä¸å»ºæç´¢å¼ï¼ä½æ¯å¨ä¸é¢çè¯å¥ä¸ä¼åå¨è¿æ¯ä½¿ç¨é¡ºåºååè·¯å¾æ«ææ´ä¸ªè¡¨ãå 为è¿ä¸ªè¯å¥è¦æ£ç´¢çæ¯å离çè¡çéåï¼æ以åºè¯¥æ¹ä¸ºå¦ä¸è¯å¥ï¼
SELECT ï¼ FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT ï¼ FROM orders WHERE order_num=1008
è¿æ ·å°±è½å©ç¨ç´¢å¼è·¯å¾å¤çæ¥è¯¢ã
8 é¿å
ç¸å
³åæ¥è¯¢ï¼
ä¸ä¸ªåçæ ç¾åæ¶å¨ä¸»æ¥è¯¢åWHEREåå¥ä¸çæ¥è¯¢ä¸åºç°ï¼é£ä¹å¾å¯è½å½ä¸»æ¥è¯¢ä¸çåå¼æ¹åä¹åï¼åæ¥è¯¢å¿
é¡»éæ°æ¥è¯¢ä¸æ¬¡ãæ¥è¯¢åµå¥å±æ¬¡è¶å¤ï¼æçè¶ä½ï¼å æ¤åºå½å°½éé¿å
åæ¥è¯¢ãå¦æåæ¥è¯¢ä¸å¯é¿å
ï¼é£ä¹è¦å¨åæ¥è¯¢ä¸è¿æ»¤æå°½å¯è½å¤çè¡ã
9 é¿å
å°é¾çæ£è§è¡¨è¾¾å¼ï¼
MATCHESåLIKEå
³é®åæ¯æéé
符å¹é
ï¼ææ¯ä¸å«æ£è§è¡¨è¾¾å¼ãä½è¿ç§å¹é
ç¹å«èè´¹æ¶é´ãä¾å¦ï¼SELECT ï¼ FROM customer WHERE zipcode LIKE â98_ _ _â
å³ä½¿å¨zipcodeå段ä¸å»ºç«äºç´¢å¼ï¼å¨è¿ç§æ
åµä¸ä¹è¿æ¯éç¨é¡ºåºæ«æçæ¹å¼ãå¦ææè¯å¥æ¹ä¸ºSELECT ï¼ FROM customer WHERE zipcode >â98000âï¼å¨æ§è¡æ¥è¯¢æ¶å°±ä¼å©ç¨ç´¢å¼æ¥æ¥è¯¢ï¼æ¾ç¶ä¼å¤§å¤§æé«é度ã
å¦å¤ï¼è¿è¦é¿å
éå¼å§çå串ãä¾å¦è¯å¥ï¼SELECT ï¼ FROM customer WHERE zipcode[2ï¼3] >â80âï¼å¨WHEREåå¥ä¸éç¨äºéå¼å§å串ï¼å èè¿ä¸ªè¯å¥ä¹ä¸ä¼ä½¿ç¨ç´¢å¼ã
10 ä¸å
份çè¿æ¥æ¡ä»¶ï¼
ä¾ï¼è¡¨cardæ7896è¡ï¼å¨card_noä¸æä¸ä¸ªéèéç´¢å¼ï¼è¡¨accountæ191122è¡ï¼å¨account_noä¸æä¸ä¸ªéèéç´¢å¼ï¼è¯çå¨ä¸åç表è¿æ¥æ¡ä»¶ä¸ï¼ä¸¤ä¸ªSQLçæ§è¡æ
åµï¼
SELECT sum(a.amount) FROM account a,card b WHERE a.card_no = b.card_no
ï¼20ç§ï¼
å°SQLæ¹ä¸ºï¼
SELECT sum(a.amount) FROM account a,card b WHERE a.card_no = b.card_no and a.account_no=b.account_no
ï¼< 1ç§ï¼
åæï¼
å¨ç¬¬ä¸ä¸ªè¿æ¥æ¡ä»¶ä¸ï¼æä½³æ¥è¯¢æ¹æ¡æ¯å°accountä½å¤å±è¡¨ï¼cardä½å
å±è¡¨ï¼å©ç¨cardä¸çç´¢å¼ï¼å
¶I/O次æ°å¯ç±ä»¥ä¸å
¬å¼ä¼°ç®ä¸ºï¼
å¤å±è¡¨accountä¸ç22541页 ï¼å¤å±è¡¨accountç191122è¡*å
å±è¡¨cardä¸å¯¹åºå¤å±è¡¨ç¬¬ä¸è¡æè¦æ¥æ¾ç3页ï¼=595907次I/O
å¨ç¬¬äºä¸ªè¿æ¥æ¡ä»¶ä¸ï¼æä½³æ¥è¯¢æ¹æ¡æ¯å°cardä½å¤å±è¡¨ï¼accountä½å
å±è¡¨ï¼å©ç¨accountä¸çç´¢å¼ï¼å
¶I/O次æ°å¯ç±ä»¥ä¸å
¬å¼ä¼°ç®ä¸ºï¼
å¤å±è¡¨cardä¸ç1944页 ï¼å¤å±è¡¨cardç7896è¡*å
å±è¡¨accountä¸å¯¹åºå¤å±è¡¨æ¯ä¸è¡æè¦æ¥æ¾ç4页ï¼= 33528次I/O
å¯è§ï¼åªæå
份çè¿æ¥æ¡ä»¶ï¼çæ£çæä½³æ¹æ¡æä¼è¢«æ§è¡ã
å¤è¡¨æä½å¨è¢«å®é
æ§è¡åï¼æ¥è¯¢ä¼åå¨ä¼æ ¹æ®è¿æ¥æ¡ä»¶ï¼ååºå ç»å¯è½çè¿æ¥æ¹æ¡å¹¶ä»ä¸æ¾åºç³»ç»å¼éæå°çæä½³æ¹æ¡ãè¿æ¥æ¡ä»¶è¦å
份èè带æç´¢å¼ç表ãè¡æ°å¤ç表ï¼å
å¤è¡¨çéæ©å¯ç±å
¬å¼ï¼å¤å±è¡¨ä¸çå¹é
è¡æ°*å
å±è¡¨ä¸æ¯ä¸æ¬¡æ¥æ¾ç次æ°ç¡®å®ï¼ä¹ç§¯æå°ä¸ºæä½³æ¹æ¡ã
ä¸å¯ä¼åçWHEREåå¥
ä¾1
ä¸åSQLæ¡ä»¶è¯å¥ä¸çåé½å»ºææ°å½çç´¢å¼ï¼ä½æ§è¡é度å´é常æ
¢ï¼
SELECT * FROM record WHERE substrINg(card_no,1,4)='5378'
(13ç§)
SELECT * FROM record WHERE amount/30< 1000
ï¼11ç§ï¼
SELECT * FROM record WHERE convert(char(10),date,112)='19991201'
ï¼10ç§ï¼
åæï¼
WHEREåå¥ä¸å¯¹åçä»»ä½æä½ç»æé½æ¯å¨SQLè¿è¡æ¶éå计ç®å¾å°çï¼å æ¤å®ä¸å¾ä¸è¿è¡è¡¨æç´¢ï¼è没æ使ç¨è¯¥åä¸é¢çç´¢å¼ï¼å¦æè¿äºç»æå¨æ¥è¯¢ç¼è¯æ¶å°±è½å¾å°ï¼é£ä¹å°±å¯ä»¥è¢«SQLä¼åå¨ä¼åï¼ä½¿ç¨ç´¢å¼ï¼é¿å
表æç´¢ï¼å æ¤å°SQLéåæä¸é¢è¿æ ·ï¼
SELECT * FROM record WHERE card_no like '5378%'
ï¼< 1ç§ï¼
SELECT * FROM record WHERE amount< 1000*30
ï¼< 1ç§ï¼
SELECT * FROM record WHERE date= '1999/12/01'
ï¼< 1ç§ï¼
11 åå¨è¿ç¨ä¸ï¼éç¨ä¸´æ¶è¡¨ä¼åæ¥è¯¢ï¼
ä¾
1ï¼ä»parven表ä¸ævendor_numç次åºè¯»æ°æ®ï¼
SELECT part_numï¼vendor_numï¼price FROM parven ORDER BY vendor_num
INTO temp pv_by_vn
è¿ä¸ªè¯å¥é¡ºåºè¯»parvenï¼50页ï¼ï¼åä¸ä¸ªä¸´æ¶è¡¨ï¼50页ï¼ï¼å¹¶æåºãåå®æåºçå¼é为200页ï¼æ»å
±æ¯300页ã
2ï¼æ临æ¶è¡¨åvendor表è¿æ¥ï¼æç»æè¾åºå°ä¸ä¸ªä¸´æ¶è¡¨ï¼å¹¶æpart_numæåºï¼
SELECT pv_by_vnï¼ï¼ vendor.vendor_num FROM pv_by_vnï¼vendor
WHERE pv_by_vn.vendor_num=vendor.vendor_num
ORDER BY pv_by_vn.part_num
INTO TMP pvvn_by_pn
DROP TABLE pv_by_vn
è¿ ä¸ªæ¥è¯¢è¯»åpv_by_vn(50页)ï¼å®éè¿ç´¢å¼ååvendor表1.5ä¸æ¬¡ï¼ä½ç±äºævendor_num次åºæåï¼å®é
ä¸åªæ¯éè¿ç´¢å¼é¡ºåºå°è¯» vendor表ï¼40ï¼2=42页ï¼ï¼è¾åºç表æ¯é¡µçº¦95è¡ï¼å
±160页ãå并ååè¿äºé¡µå¼å5ï¼160=800次ç读åï¼ç´¢å¼å
±è¯»å892页ã
3ï¼æè¾åºåpartè¿æ¥å¾å°æåçç»æï¼
SELECT pvvn_by_pn.ï¼ï¼part.part_desc FROM pvvn_by_pnï¼part
WHERE pvvn_by_pn.part_num=part.part_num
DROP TABLE pvvn_by_pn
è¿æ ·ï¼æ¥è¯¢é¡ºåºå°è¯»pvvn_by_pn(160页)ï¼éè¿ç´¢å¼è¯»part表1.5ä¸æ¬¡ï¼ç±äºå»ºæç´¢å¼ï¼æ以å®é
ä¸è¿è¡1772次ç£ç读åï¼ä¼åæ¯ä¾ä¸º30â¶1ã
温馨提示:答案为网友推荐,仅供参考