·¢²¼Ê±¼ä : ÐÇÆÚ¶þ ÎÄÕÂÊý¾Ý¿âÔÀí¼°Ó¦Ó㨵Ú3°æ£© - È«²¿Ï°Ìânew¸üÐÂÍê±Ï¿ªÊ¼ÔĶÁ
WHERE X.SNO=¡¯S1¡¯ AND NOT EXISTS (SELECT * FROM SPJ Y
WHERE Y.JNO=J.JNO AND Y.PNO=X.PNO))
£¨3£©
1£©°ÑÈ«²¿ºìÉ«Áã¼þµÄÑÕÉ«¸Ä³ÉÀ¶É«;
UPDATE P SET COLOR=¡¯À¶¡¯ WHERE COLOR=¡¯ºì¡¯
2£©ÓÉS10¹©¸øJ4µÄÁã¼þP6¸ÄΪÓÉS8¹©Ó¦£¬Çë×÷±ØÒªµÄÐ޸ġ£
UPDATE SPJ SET SNO=¡¯S8¡¯ WHERE SNO=¡¯S10¡¯ AND PNO=¡¯P6¡¯ AND JNO=¡¯J4¡¯ Èô²»ÄÜÖ±½ÓÐÞ¸ÄÖ÷Â룬Ôò¿ÉÈçϲÙ×÷£ºÏÈÌí¼ÓмǼ£¬ÔÙɾ³ýÀϼǼ Insert into spj
select ¡¯S8¡¯,jno,pno,qty from spj
where SNO=¡¯S10¡¯ AND PNO=¡¯P6¡¯ AND JNO=¡¯J4¡¯
delete from spj where SNO=¡¯S10¡¯ AND PNO=¡¯P6¡¯ AND JNO=¡¯J4¡¯
3£©´Ó¹©Ó¦É̹ØϵÖÐɾ³ýS2µÄ¼Ç¼£¬²¢´Ó¹©Ó¦Áã¼þ¹ØϵÖÐɾ³ýÏàÓ¦µÄ¼Ç¼¡£ Delete from spj where sno=¡¯s2¡¯ Delete from s where sno=¡¯s2¡¯
4£©Ç뽫 (S2, J8, P4, 200) ²åÈ빩ӦÁã¼þ¹Øϵ¡£ Insert into spj values(¡¯S2¡¯,¡¯J8¡¯,¡¯P4¡¯,200) 5£©½«¹¤³ÌJ2µÄÔ¤Ëã¸ÄΪ40ÍòÔª¡£ Update J SET BG=40 WHERE JNO=¡¯J2¡¯
6£©É¾³ý¹¤³ÌJ8¶©¹ºµÄS4µÄÁã¼þ¡£ÌâÒâΪ£ºÉ¾³ý¹¤³ÌJ8¶©¹ºµÄS4ÌṩÁã¼þµÄ¹©Ó¦¹Øϵ¼Ç¼¡£
DELETE FROM SPJ WHERE JNO=¡¯J8¡¯ AND SNO=¡¯S4¡¯ £¨4£©
CREATE VIEW P_SPJ_VIEW AS SELECT P.*,SPJ.SNO,SPJ.JNO,QTY FROM P,SPJ
WHERE P.PNO=SPJ.PNO
1£©ÕÒ³ö¹¤³Ì´úÂëΪJ2µÄ¹¤³ÌʹÓõÄËùÓÐÁã¼þÃû³Æ¡¢ÊýÁ¿¡£ SELECT PNAME,QTY FROM P_SPJ_VIEW WHERE JNO=?J2?
2£©ÕÒ³öʹÓÃÉϺ£²úµÄÁã¼þµÄ¹¤³ÌºÅ¡£ SELECT JNO
FROM P_SPJ_VIEW WHERE CITY=¡¯ÉϺ£¡¯
6¡¢ÔÚǶÈëʽSQLÖÐÈçºÎÇø·ÖSQLÓï¾äºÍÖ÷Óï¾äµÄ£¿¾ÙÀý˵Ã÷¡£ ½â£º
ÔÚǶÈëʽSQLÖУ¬ÎªÁËÄܹ»Çø·ÖSQLÓï¾äÓëÖ÷ÓïÑÔÓï¾ä£¬ËùÓÐSQLÓï¾ä¶¼±ØÐë¼Óǰ׺EXEC SQL¡£SQLÓï¾äµÄ½áÊø±êÖ¾ÔòËæÖ÷ÓïÑԵIJ»Í¬¶ø²»Í¬£¬ÀýÈçÔÚPL/1ºÍCÖÐÒԷֺţ¨£»£©½áÊø£¬ÔÚCOBOLÖÐÒÔEND-EXEC½áÊø¡£ÕâÑù£¬ÒÔC»òPL/1×÷ΪÖ÷ÓïÑÔµÄǶÈëʽSQLÓï¾äµÄÒ»°ãÐÎʽΪ£ºEXEC SQL
45
Êý¾Ý¿âÔÀí¼°Ó¦Óà ¾ÙÀý£ºÈçÏÂÒ»Ìõ½»»¥ÐÎʽµÄSQLÓï¾ä£ºDROP TABLE S ǶÈëµ½C³ÌÐòÖУ¬Ó¦Ð´×÷£ºEXEC SQL DROP TABLE S
7¡¢ÔÚǶÈëʽSQLÖÐÈçºÎ½â¾öÊý¾Ý¿â¹¤×÷µ¥ÔªÓë³ÌÐò¹¤×÷µ¥ÔªÖ®¼ä¹µÍ¨µÄ£¿ ½â£º
ǶÈëʽSQLÓï¾äÖпÉÒÔʹÓÃÖ÷ÓïÑԵijÌÐò±äÁ¿À´ÊäÈë»òÊä³öÊý¾Ý¡£°ÑSQLÓï¾äÖÐʹÓõÄÖ÷ÓïÑÔ³ÌÐò±äÁ¿³ÆΪÖ÷±äÁ¿(Host Variable)£¬Ö÷±äÁ¿ÔÚÊý¾Ý¿â¹¤×÷µ¥ÔªÓë³ÌÐò¹¤×÷µ¥ÔªÖ®¼äÆðÏ໥ÐÅÏ¢»òÊý¾Ý¹µÍ¨×÷Óá£Ö÷±äÁ¿ÔÚËÞÖ÷ÓïÑÔ³ÌÐòÓëÊý¾Ý¿âÖ®¼äµÄ×÷ÓÿɲÎÔÄͼ3.5¡£
ËÞÖ÷ÓïÑÔ ÏòSQLÌṩ²ÎÊý µÃµ½SQLÓï¾äµÄ½á¹ûºÍ״̬ ´ÓËÞÖ÷ÓïÑÔÖлñÈ¡²ÎÊý ²Ù×÷Êý¾Ý¿â Ö÷±äÁ¿ SQLÓï¾ä »ñÈ¡Êý¾Ý DB ¶ÔÖ÷±äÁ¿¸³Öµ»òÉèÖÃ״̬ÐÅÏ¢
ͼ3.5 Ö÷±äÁ¿µÄͨÐÅÓë´«µÝÊý¾ÝµÄ×÷ÓÃʾÒâͼ
Ö÷±äÁ¿¸ù¾ÝÆä×÷ÓõIJ»Í¬£¬·ÖΪÊäÈëÖ÷±äÁ¿¡¢Êä³öÖ÷±äÁ¿ºÍָʾÖ÷±äÁ¿¡£ÊäÈëÖ÷±äÁ¿ÓÉÓ¦ÓóÌÐò¶ÔÆ丳ֵ£¬SQLÓï¾äÒýÓã»Êä³öÖ÷±äÁ¿ÓÉSQLÓï¾ä¶ÔÆ丳ֵ»òÉèÖÃ״̬ÐÅÏ¢£¬·µ»Ø¸øÓ¦ÓóÌÐò£»Ò»¸öÖ÷±äÁ¿¿ÉÒÔ¸½´øÒ»¸öÈÎÑ¡µÄָʾÖ÷±äÁ¿£¬Ö¸Ê¾Ö÷±äÁ¿ÊÇÒ»¸öÕûÐͱäÁ¿£¬ÓÃÀ´¡°Ö¸Ê¾¡±ËùÖ¸Ö÷±äÁ¿µÄÖµµÄÇé¿ö£¬Ö¸Ê¾Ö÷±äÁ¿¿ÉÒÔָʾÊäÈëÖ÷±äÁ¿ÊÇ·ñÏ£ÍûÉèÖÃΪ¿ÕÖµ£¬¿ÉÒÔ¼ì²âÊä³öÖ÷±äÁ¿ÊÇ·ñÊÇ¿ÕÖµ£¨Ö¸Ê¾Ö÷±äÁ¿Îª¸ºÖµÖ¸Ê¾ËùÖ¸Ö÷±äÁ¿Îª¿ÕÖµ£©¡£Ò»¸öÖ÷±äÁ¿¿ÉÄܼÈÊÇÊäÈëÖ÷±äÁ¿ÓÖÊÇÊä³öÖ÷±äÁ¿¡£ÔÚSQLÓï¾äÖÐʹÓÃÕâЩ±äÁ¿Ê±£¬ÐèÔÚÖ÷±äÁ¿ÃûÇ°¼ÓðºÅ¡°£º¡±×÷Ϊ±ê¼Ç£¬ÒÔÇø±ðÓÚ±íÖеÄ×ֶΣ¨»òÊôÐÔ£©Ãû¡£
SQLÓï¾äÔÚÓ¦ÓóÌÐòÖÐÖ´Ðкó£¬ÏµÍ³Òª·´À¡¸øÓ¦ÓóÌÐòÈô¸ÉÐÅÏ¢£¬ÕâЩÐÅÏ¢Ë͵½SQLµÄͨÐÅÇøSQLCA£¨SQL Communication Area£©¡£SQLCAÓÃÓï¾äEXEC SQL INCLUDE¼ÓÒÔ¶¨Òå¡£SQLCAÊÇÒ»¸öÊý¾Ý½á¹¹£¨¼´SQLCA½á¹¹Öк¬ÓÐÄÜ·´Ó³²»Í¬Ö´Ðкó×´¿öµÄ¶à¸ö״̬±äÁ¿È磺SQLCODE¡¢SQLERRD1¡¢SQLERRMC¡¢SQLWARN¡¢SQLERRMµÈ£©£¬SQLCAÖÐÓÐÒ»¸ö´æ·Åÿ´ÎÖ´ÐÐSQLÓï¾äºó·µ»Ø´úÂëµÄ״̬±äÁ¿SQLCODE¡£µ±SQLCODEΪÁãʱ£¬±íʾSQLÓï¾äÖ´Ðгɹ¦£¬·ñÔò·µ»ØÒ»¸ö´íÎó´úÂ루¸ºÖµ£©»ò¾¯¸æÐÅÏ¢£¨ÕýÖµ£©£¬Ò»°ã³ÌÐòÔ±Ó¦¸ÃÔÚÿ¸öSQLÓï¾äÖ®ºó²âÊÔSQLCODEµÄÖµ¡£ÒÔ±ã¸ù¾Ýµ±Ç°SQLÃüÁîÖ´ÐÐÇé¿ö¾ö¶¨ºóÐøµÄ´¦Àí¡£
8¡¢SQLµÄ¼¯ºÏ´¦Àí·½Ê½ÓëËÞÖ÷ÓïÑԵĵ¥¼Ç¼´¦Àí·½Ê½Ö®¼äÈçºÎе÷? ½â£º
ÎÒÃÇÖªµÀÒ»¸öSQLÓï¾äÒ»°ãÄÜ´¦ÀíÒ»×é¼Ç¼£¬¶øÖ÷ÓïÑÔÒ»´ÎÖ»ÄÜ´¦ÀíÒ»¸ö¼Ç¼£¬Îª´Ë±ØÐëе÷Á½ÖÖ´¦Àí·½Ê½£¬Ê¹ËüÃÇÏ໥е÷µØ´¦Àí¡£Ç¶ÈëʽSQLÖÐÊÇÒýÈëÓα꣨Cursor£©»úÖÆÀ´½â¾öÕâ¸öÎÊÌâµÄ¡£
ÓαêÊÇϵͳΪÓû§¿ªÉèµÄÒ»¸öÊý¾ÝÄڴ滺³åÇø£¬ÓÃÀ´´æ·ÅSQLÓï¾äµÄÖ´Ðнá¹û£¬Ã¿¸öÓαêÇø¶¼ÓÐÒ»¸öÃû×Ö¡£Óû§¿ÉÒÔͨ¹ýÓαêÖðÒ»»ñÈ¡¼Ç¼£¬²¢¸³¸øÖ÷±äÁ¿£¬ÔÙÓÉÖ÷ÓïÑÔ³ÌÐò×÷½øÒ»²½´¦Àí¡£ÓëÓαêÓйصÄSQLÓï¾äÓÐÏÂÁÐ4¸ö£º
£¨1£©Óα궨ÒåÓï¾äDECLARE¡£ÓαêÊÇÓëij¸ö²éѯ½á¹ûÏàÁªÏµµÄ·ûºÅÃû£¬ÓÃSQLµÄDECLAREÓï¾ä¶¨Ò壬ËüÊÇ˵Ã÷ÐÔÓï¾ä£¬¶¨ÒåʱÓα궨ÒåÖеÄSELECTÓï¾ä²¢²»ÂíÉÏÖ´ÐС££¨Çé¿öÓëÊÓͼµÄ¶¨ÒåÏàËÆ£©
£¨2£©Óαê´ò¿ªÓï¾äOPEN¡£´ËʱִÐÐÓα궨ÒåÖеÄSELECTÓï¾ä£¬Í¬Ê±Óα껺³åÇøÖк¬ÓÐSELECTÓï¾äÖ´Ðкó¶ÔÓ¦µÄËùÓмǼ£¬ÓαêÒ²´¦Óڻ״̬¡£ÓαêÖ¸ÕëÖ¸ÏòÓαêÖмǼ½á¹ûµÚÒ»ÐÐ֮ǰ¡£
£¨3£©ÓαêÍƽøÓï¾äFETCH¡£´ËʱִÐÐÓαêÏòÇ°ÍƽøÒ»ÐС£²¢°ÑÓαêÖ¸ÕëÖ¸ÏòµÄµ±Ç°¼Ç¼
46
¶Á³ö£¬·Åµ½FETCHÓï¾äÖÐÖ¸¶¨µÄ¶ÔÓ¦Ö÷±äÁ¿ÖС£FETCHÓï¾ä³£ÖÃÓÚÖ÷ÓïÑÔ³ÌÐòµÄÑ»·½á¹¹ÖУ¬Í¨¹ýÑ»·ÖðÒ»´¦ÀíÓαêÖеÄÒ»¸ö¸ö¼Ç¼¡£
£¨4£©Óαê¹Ø±ÕÓï¾äCLOSE¡£¹Ø±ÕÓα꣬ʹËü²»ÔÙºÍÔÀ´µÄ²éѯ½á¹ûÏàÁªÏµ£¬Í¬Ê±ÊÍ·ÅÓαêÕ¼ÓõÄ×ÊÔ´¡£¹Ø±ÕµÄÓαê¿ÉÒÔÔٴδò¿ª¡¢µÃµ½ÐµÄÓαê¼Ç¼ºóÔÙʹÓÃÓαꡢÔٹرա£
ÔÚÓα괦Óڻ״̬ʱ£¬¿ÉÒÔÐ޸ĺÍɾ³ýÓαêÖ¸ÕëÖ¸ÏòµÄµ±Ç°¼Ç¼¡£
9¡¢¶ÔÓÚ¼òÒ×½Ìѧ¹ÜÀíÊý¾Ý¿âÓÐÈçÏÂ3¸ö»ù±¾±í£ºS(SNO,SN,AGE,SEX)¡¢SC(SNO,CNO,SCORE)¡¢C(CNO,CN,TH)£¬Æ京ÒåΪSNO(ѧºÅ),SN(ÐÕÃû),AGE(ÄêÁä),SEX(ÐÔ±ð)£¬SCORE(³É¼¨)£¬CNO(¿Î³ÌºÅ),CN(¿Î³ÌÃû),TH(½ÌʦÃû)¡£ÊÔÓÃSQLÓïÑÔ±í´ïÈçϲéѯ¼°²Ù×÷£º
(1)¼ìË÷ÄêÁä´óÓÚ16ËêµÄŮѧÉúµÄѧºÅºÍÐÕÃû¡£ (2)¼ìË÷ÐÕÁõµÄѧÉúÑ¡ÐÞµÄËùÓпγÌÃûÓë½ÌʦÃû¡£ (3)¼ìË÷ûÓÐÑ¡ÐÞÊý¾Ý¿â¿Î³ÌµÄѧÉúµÄѧºÅÓëÐÕÃû¡£ (4)¼ìË÷ÖÁÉÙÑ¡ÐÞÁ½Ãſγ̵ÄѧÉúµÄѧºÅÓëÐÕÃû¡£
(5)¼ìË÷Ñ¡Ð޿γ̰üº¬ÐÕÕÅÀÏʦËùÊÚÈ«²¿¿Î³ÌµÄѧÉúµÄѧºÅÓëÐÕÃû¡£ (6)°ÑÍõ·ÇͬѧµÄѧÉúÐÅÏ¢¼°ÆäÑ¡¿ÎÇé¿öµÈÈ«²¿É¾³ý¡£ (7)Ôڿγ̱íÖÐÌí¼ÓÒ»ÃÅпγ̣¬ÆäÐÅϢΪ£º£¨'C8','ÐÅϢϵͳ¸ÅÂÛ','ËïÁ¦'£©¡£
(8)ÔÚÑ¡ÐÞ¹Øϵ±íSCÖÐÌí¼ÓËùÓÐѧÉú¶Ô'C8'¿Î³ÌµÄÑ¡ÐÞ¹Øϵ¼Ç¼£¬³É¼¨Ôݶ¨Îª60£¬ÇëÓÃÒ»ÌõÃüÁîÍê³É±¾ÅúÁ¿Ìí¼ÓÈÎÎñ¡£
(9)°ÑÑ¡¡°ÐÅϢϵͳ¸ÅÂÛ¡±¿Î³ÌµÄÄÐѧÉúµÄ³É¼¨ÔÝÈ«²¿³õʼ»¯ÖØÐÂÉèÖÃΪ0¡£
(1) SELECT SNO,SN
FROM S
WHERE AGE>16 AND SEX='Å®'; (2) ·½·¨Ò»(Áª½Ó²éѯ·½Ê½): SELECT CN,TH FROM S,SC,C
WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND SN like 'Áõ%'; ·½·¨¶þ(ǶÌײéѯ·½Ê½)£º SELECT CN£¬TH FROM C
WHERE CNO IN
(SELECT CNO FROM SC
WHERE SNO IN (SELECT SNO FROM S
WHERE SN like 'Áõ%'));
(3) SELECT SNO,SN
FROM S
WHERE SNO NOT IN (SELECT SNO FROM S
WHERE SNO IN (SELECT SNO FROM SC
WHERE CNO IN
47
Êý¾Ý¿âÔÀí¼°Ó¦Óà (SELECT CNO FROM C
WHERE CN='Êý¾Ý¿â'))); »ò
SELECT SNO,SN FROM S
WHERE SNO NOT IN (SELECT SNO FROM S,SC,C WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND CN='Êý¾Ý¿â');
(4) ·½·¨Ò»£ºSELECT DISTINCT X.SNO
FROM SC X WHERE SNO IN (SELECT SNO FROM SC Y
WHERE X.SNO=Y.SNO AND X.CNO<>Y.CNO);
·½·¨¶þ£ºSELECT DISTINCT X.SNO
FROM SC X,SC Y
WHERE X.SNO=Y.SNO AND X.CNO<>Y.CNO);
·½·¨Èý£ºSELECT SNO
FROM SC
GROUP BY SNO HAVING COUNT(*)>=2;
(5)SELECT DISTINCT SX.SNO,SN
FROM SC X£¬S SX WHERE NOT EXISTS (SELECT * FROM C
WHERE TH LIKE 'ÕÅ%' AND NOT EXISTS (SELECT * FROM SC Y
WHERE X.SNO=Y.SNO AND C.CNO=Y.CNO)) AND X.SNO=SX.SNO ; »ò
SELECT DISTINCT X.SNO,X.SN FROM S X
WHERE NOT EXISTS (SELECT * FROM C
WHERE TH LIKE 'ÕÅ%' AND NOT EXISTS (SELECT * FROM SC Y
WHERE X.SNO=Y.SNO AND C.CNO=Y.CNO)); (6) DELETE FROM SC
WHERE SNO =
(SELECT SNO FROM S
WHERE SN='Íõ·Ç');
DELETE FROM S WHERE SN='Íõ·Ç';
48