×÷ÕߣºØýÃû ÎÄÕÂÀ´Ô´£º»¥ÁªÍø µã»÷Êý£º ÔÚÏßͶ¸å Ͷ¸åÖ¸ÄÏ  |
ÔÚÕâÆªÎÄÕÂÀïÎÒÒªÃèÊöÒ»ÏÂÈçºÎ´Ó±í¸ñÀïɾ³ýÁУ¬ÒªÉ¾³ýµÄÕâЩÁÐͬʱ»¹ÒªÒÀÀµÓÚÆäËû±í¸ñµÄ±ê×¼¡£Òª½â¾öÕâ¸öÎÊÌâ¾ÍÐèÒªÒ»¸öºÜ´ÏÃ÷¶øÇÒÍêÈ«×ñÊØSQL92×Ó²éѯÉùÃ÷µÄÓ¦ÓóÌÐò¡£ ÎÒ±ØÐëÌáÐѶÁÕßµÄÊÇ£¬¾¡¹Ü²éѯ¿ÉÄÜ»á×ñÊØSQLµÄ±ê×¼£¬µ«ÊÇÖÚ¶àµÄÊý¾Ý¿âÉý²úÉÌ»áÒÔ²»Í¬µÄ¾ä·¨Ö§³ÖʵÏÖSQL¡£ÒÔÏÂÕâ¸ö½â¾ö·½°¸Ó¦¸ÃÊʺÍÓÚ´ó¶àÊýÊý¾Ý¿â£»µ«ÊÇ£¬Èç¹ûÄãµÄ½á¹ûÓгöÈ룬¾Í»¹ÊÇÓ¦¸Ã²é¿´Ò»ÏÂÎĵµ¡£Í¬Ê±£¬ÓÉÓÚÕâ¸ö²éѯҪ´¦ÀíDELETEÉùÃ÷£¬ËùÒÔÄãÓ¦¸ÃÔÚ½«ÆäÓ¦ÓÃÓÚÕæÊµµÄÉý²ú»·¾³ÒÔǰÔÚʵÑéÊý¾ÝÉϽøÐвâÊÔ¡£
ÐèÒª¸ü¶àµÄ±³¾°ÐÅÏ¢£¿ ²é¿´ÕâЩÎÄÕ¾ÍÄÜ¿ìËÙÉÏ·£º ¡¶SQL»ù´¡I²éÊý¾Ý²éѯ¡·Éæ¼°µ½ÁËDELETE²éѯµÄʹÓᣠ¡¶Ê¹ÓÃSQL×ÓÑ¡ÏîÀ´ºÍ²¢²éѯ¡·ËµÃ÷×ÓÑ¡Ïî²éѯÄܹ»¼õÉÙ¶ÔÊý¾Ý¿âÇëÇóµÄÊýÁ¿£¬²¢ÌṩÁËÀý×Ó¡£ ¡¶SQL»ù´¡£º²éѯ¶à¸ö±í¡·ÌṩÁ˸ü¶à¹ØÓÚ×ÓÑ¡ÏîµÄÐÅÏ¢£¬»¹½²µ½ÁËʹÓõ¥¸ö²éѯ¾ÍÄÜ·ÃÎʶà¸ö±í¸ñµÄ¶àÖÖÆäËû·½·¨¡£
³èÎïµêµÄÀý×Ó Òª½âÊÍÈçºÎ½øÐÐÕâÖÖÀàÐ͵ÄÁÐɾ³ý£¬ÎÒ»áʹÓÃÈçÏÂÕâ¸öÊý¾Ý¿âµÄ±í¸ñ£¬¸ÃÊý¾Ý¿â½Ð×öPetStore£¬²¢°üº¬ÓÐÇåµ¥£¨inventory£©ÐÅÏ¢¡£ÔÚ½Ð×ö¡°Æ·ÖÖ£¨breed£©¡±µÄ±íAÀÎÒ´æ´¢ÓÐÿÖÖ¶¯ÎïµÄÐÅÏ¢ºÍ³èÎïµê¿â´æµÄÐÅÏ¢¡£ÔÚ½Ð×ö¡°Çåµ¥¡±µÄ±íBÀ°üº¬ÓÐÉ̵êÀïÌØ¶¨¶¯ÎïµÄÐÅÏ¢¡£
ÔÚÕâ¸öÀý×ÓÀÎÒÃÇÏȼÙÉèÉ̵ê°ÑÕûÎÑShitzuС¹·¶¼ÂôÍêÁË¡£ÎÒ¿ÉÒÔʹÓÃbreed±í¸ñÀïµÄbreed_id×Ö¶ÎÀ´É¾³ýShitzuÇåµ¥ÀïµÄËùÓÐÏîÄ¿£¬¾ÍÏñÕâÑù£º
DELETE FROM inventory WHERE breed_id IN (SELECT breed_id FROM breed WHERE breed_name = ¡®Shitzu¡¯);
Ê×ÏÈ£¬ÎÒÒªÖ¸¶¨ÐèҪɾ³ý¼Ç¼µÄ±í¸ñ£¬ÔÚÕâÀïÊÇÇåµ¥±í¸ñ¡£È»ºóÔÙ½«Ê¶±ð×Ö¶Îbreed_idͬ×ÓÑ¡Ïî×Ó¾äµÄ½á¹û·´¸´±È¶Ô¡£ÎÒÖªµÀÒªÕÒµÄÊÇShitzus£¬ËùÒÔ¾ÍÄÜÖ±½ÓɾµôËûÃÇ£¬¶ø²»ÓÃÔÙÔÚµ¥¶ÀµÄÇëÇóÀï²éѯbreed_id¡£
ÎÒ±ØÐëÒª¾¯¸æÄãµÄÊÇ£¬ÒÔÕâÖÖ·½Ê½Ê¹ÓÃDELETEÉùÃ÷ÊÇΣÏյģ¬Ö»ÓÐÔÚÄã¶ÔÊý¾Ý¿âµÄ½á¹¹ºÜÊìϤµÄÇé¿öϲÅÄÜʹÓÃÕâЩÉùÃ÷¡£DELETE²éѯ»á´ÓÊÜÓ°ÏìµÄ±í¸ñÀïɾ³ýµôÈ«²¿ÁУ¬ÄãÓ¦¸ÃÖªµÀÕâ¶ÔÄãËù¹ÜÀí×ŵÄÊý¾ÝÒâζ×Åʲô¡£ÓиöºÃ°ì·¨ÊÇʹÓÃSELETE *Õâ¸ö¶ÌÓïÌæ´úDELETE¹Ø¼ü×ÖÀ´¶ÔDELETEÉùÃ÷µÄ×Ó²éѯ½á¹û½øÐвâÊÔ£¬ÕâÑù¾ÍÄܱ£Ö¤½á¹ûÀﺬÓÐÄãҪɾ³ýµÄËùÓж«Î÷£¬¾ÍÏñÕâÑù£º
SELECT * FROM inventory WHERE breed_id IN (SELECT breed_id FROM breed WHERE breed_name = ¡®Shitzu¡¯);
DELETEºÍJOINÁªÓà ÓÐÈËÎʵ½Á˽â¾öÕâ¸öÎÊÌâÁíÒ»¸ö¿ÉÄܵİ취£º°ÑJOIN×Ó¾äºÍDELETEÉùÃ÷ÁªºÍʹÓá£ÓÉÓÚÒÔǰûÓÐʹÓùýÕâÖÖ·½·¨£¬ÎÒ¾ÍÑо¿ÁËһϣ¬·¢ÏÖSQL ServerµÄÎĵµÉùÃ÷Ö§³ÖÕâ¸ö·½·¨£¬¾¡¹ÜËü²»·ûºÍSQL92¡£ÔÚ¾¹ý²âÊÔºÍѯÎʸ÷ÖÖÊý¾Ý¿âƽ̨µÄÀÏÊÖÖ®ºó£¬ÎÒ·¢ÏÖ°ÑDELETEºÍJOINÉùÃ÷ÁªºÍʹÓÃÔÚÎÒ²âÊÔ¹ýµÄÈÎºÎÆ½Ì¨É϶¼Ðв»Í¨¡£
´Ó¶à¸ö±í¸ñÀïÒ»´Îɾ³ý ÒÔÉϵĽâ¾ö·½°¸»¹Ã»ÓнâÊÍÈçºÎʹÓø¸±í´Ó¶à¸ö×Ó±íÀïɾ³ýÐÅÏ¢¡£µ«ÊÇSQL92¹æ·¶ÀïûÓÐÌṩÍê³ÉÕâÏîÈÎÎñµÄ±ê×¼½â¾ö·½°¸¡£
DELETEµÄÉùÃ÷²»ÄܰѶà¸ö±í¸ñ×÷Ϊһ¸ö²ÎÊý½ÓÊÜ¡£×÷Ϊһ¸ö¾ßÓÐÆÆ»µÐԵIJéѯ£¬ÕâÄܱ£Ö¤ÔÚÃüÁîÒª±»Ö´Ðеĵط½²»»á³öÏÖÆçÒå¡£´ËÍ⣬Õâ¸öÏÞÖÆ·ÀÖ¹ÁËÔÚµ¥¸öÉùÃ÷ÄÚ½«ANDºÍ¶à¸ö×Ó²éѯÁªÓá£Èç¹û²âÊÔSELECTÉùÃ÷µÄ½á¹ûÓÃÒÔ¼ì²éDELETE²éѯ½«ÒªÓ°Ïìµ½µÄÊÇÄÄЩÊý¾Ý£¬Äã»á·¢ÏÖSELECT»á·µ»Ø¶à¸ö±í¸ñµÄÇåµ¥£¬DELETE²»»áÓ°Ïìµ½µÄ¶à¸ö×Ó²éѯ²»ÔÚÆäÖС£
Óкܶà¿ÉÄܵķ½·¨Äܹ»Âú×ãÄãµÄÐèÇó£¬ÀýÈçÔÚ±í¸ñÀï´´½¨Ò»¸ö×ֶΣ¬ÓÃÒÔÖ¸Ã÷¸ÃÏîÄ¿ÊÇ·ñΪ»î¶¯µÄ¡£»òÕߣ¬Äã¿ÉÒÔʹÓÃһЩÊý¾Ý¿âÀïµÄÔ¤´æ³ÌÐòÔÚÿ¸öËùÐèµÄDELETE²éѯÀïµü´ú¡£
|
ÐÅÑôµçÄÔÍøÉêÃ÷:±¾Õ¾×ÊÔ´À´Ô´ÓÚ»¥ÁªÍø,°æÈ¨¹éÔ×÷ÕßËùÓУ¬ÈçÓÐÇÖȨÇëÁªÏµÎÒÃÇ,ÎÒÃǽ«ÔÚÊÕµ½ÏûÏ¢ºó24СʱÄÚɾ³ý.
ÉÏһƪÎÄÕ£º ¸úÎÒѧSQL£º(°Ë)ÊýÖµÊý¾ÝÀàÐÍ |
ÏÂһƪÎÄÕ£º ´®ÐÐÊý¾ÝÀàÐÍ |
¡¾×ÖÌ壺С ´ó¡¿¡¾·¢±íÆÀÂÛ¡¿¡¾¼ÓÈëÊղء¿¡¾¸æËߺÃÓÑ¡¿¡¾´òÓ¡´ËÎÄ¡¿¡¾¹Ø±Õ´°¿Ú¡¿ |
¡¡ ¡¡ÍøÓÑÆÀÂÛ£º£¨Ö»ÏÔʾ×îÐÂ10Ìõ¡£ÆÀÂÛÄÚÈÝÖ»´ú±íÍøÓѹ۵㣬Óë±¾Õ¾Á¢³¡Î޹أ¡£© |
|
|