1¡¢Ñ¡È¡×îÊÊÓõÄ×Ö¶ÎÊôÐÔ
MySQL¿ÉÒԺܺõÄÖ§³Ö´óÊý¾ÝÁ¿µÄ´æÈ¡£¬µ«ÊÇÒ»°ã˵À´£¬Êý¾Ý¿âÖеıíԽС£¬ÔÚËüÉÏÃæÖ´ÐеIJéѯҲ¾Í»áÔ½¿ì¡£Òò´Ë£¬ÔÚ´´½¨±íµÄʱºò£¬ÎªÁË»ñµÃ¸üºÃµÄÐÔÄÜ£¬ÎÒÃÇ¿ÉÒÔ½«±íÖÐ×ֶεĿí¶ÈÉèµÃ¾¡¿ÉÄÜС¡£ÀýÈ磬ÔÚ¶¨ÒåÓÊÕþ±àÂëÕâ¸ö×Ö¶Îʱ£¬Èç¹û½«ÆäÉèÖÃΪCHAR(255),ÏÔÈ»¸øÊý¾Ý¿âÔö¼ÓÁ˲»±ØÒªµÄ¿Õ¼ä£¬ÉõÖÁʹÓÃVARCHARÕâÖÖÀàÐÍÒ²ÊǶàÓàµÄ£¬ÒòΪCHAR(6)¾Í¿ÉÒԺܺõÄÍê³ÉÈÎÎñÁË¡£Í¬ÑùµÄ£¬Èç¹û¿ÉÒԵĻ°£¬ÎÒÃÇÓ¦¸ÃʹÓÃMEDIUMINT¶ø²»ÊÇBIGINÀ´¶¨ÒåÕûÐÍ×ֶΡ£
ÁíÍâÒ»¸öÌá¸ßЧÂʵķ½·¨ÊÇÔÚ¿ÉÄܵÄÇé¿öÏ£¬Ó¦¸Ã¾¡Á¿°Ñ×Ö¶ÎÉèÖÃΪNOT NULL£¬ÕâÑùÔÚ½«À´Ö´ÐвéѯµÄʱºò£¬Êý¾Ý¿â²»ÓÃÈ¥±È½ÏNULLÖµ¡£
¶ÔÓÚijЩÎı¾×ֶΣ¬ÀýÈç¡°Ê¡·Ý¡±»òÕß¡°ÐԱ𡱣¬ÎÒÃÇ¿ÉÒÔ½«ËüÃǶ¨ÒåΪENUMÀàÐÍ¡£ÒòΪÔÚMySQLÖУ¬ENUMÀàÐͱ»µ±×÷ÊýÖµÐÍÊý¾ÝÀ´´¦Àí£¬¶øÊýÖµÐÍÊý¾Ý±»´¦ÀíÆðÀ´µÄËÙ¶ÈÒª±ÈÎı¾ÀàÐÍ¿ìµÃ¶à¡£ÕâÑù£¬ÎÒÃÇÓÖ¿ÉÒÔÌá¸ßÊý¾Ý¿âµÄÐÔÄÜ¡£
2¡¢Ê¹ÓÃÁ¬½Ó£¨JOIN£©À´´úÌæ×Ó²éѯ(Sub-Queries)
MySQL´Ó4.1¿ªÊ¼Ö§³ÖSQLµÄ×Ó²éѯ¡£Õâ¸ö¼¼Êõ¿ÉÒÔʹÓÃSELECTÓï¾äÀ´´´½¨Ò»¸öµ¥ÁеIJéѯ½á¹û£¬È»ºó°ÑÕâ¸ö½á¹û×÷Ϊ¹ýÂËÌõ¼þÓÃÔÚÁíÒ»¸ö²éѯÖС£ÀýÈ磬ÎÒÃÇÒª½«¿Í»§»ù±¾ÐÅÏ¢±íÖÐûÓÐÈκζ©µ¥µÄ¿Í»§É¾³ýµô£¬¾Í¿ÉÒÔÀûÓÃ×Ó²éѯÏÈ´ÓÏúÊÛÐÅÏ¢±íÖн«ËùÓз¢³ö¶©µ¥µÄ¿Í»§IDÈ¡³öÀ´£¬È»ºó½«½á¹û´«µÝ¸øÖ÷²éѯ£¬ÈçÏÂËùʾ£º
DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
|
ʹÓÃ×Ó²éѯ¿ÉÒÔÒ»´ÎÐÔµÄÍê³ÉºÜ¶àÂß¼ÉÏÐèÒª¶à¸ö²½Öè²ÅÄÜÍê³ÉµÄSQL²Ù×÷£¬Í¬Ê±Ò²¿ÉÒÔ±ÜÃâÊÂÎñ»òÕß±íËøËÀ£¬²¢ÇÒдÆðÀ´Ò²ºÜÈÝÒס£µ«ÊÇ£¬ÓÐЩÇé¿öÏ£¬×Ó²éѯ¿ÉÒÔ±»¸üÓÐЧÂʵÄÁ¬½Ó£¨JOIN£©.. Ìæ´ú¡£ÀýÈ磬¼ÙÉèÎÒÃÇÒª½«ËùÓÐûÓж©µ¥¼Ç¼µÄÓû§È¡³öÀ´£¬¿ÉÒÔÓÃÏÂÃæÕâ¸ö²éѯÍê³É£º
SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
|
Èç¹ûʹÓÃÁ¬½Ó£¨JOIN£©.. À´Íê³ÉÕâ¸ö²éѯ¹¤×÷£¬ËٶȽ«»á¿ìºÜ¶à¡£ÓÈÆäÊǵ±salesinfo±íÖжÔCustomerID½¨ÓÐË÷ÒýµÄ»°£¬ÐÔÄܽ«»á¸üºÃ£¬²éѯÈçÏ£º
SELECT * FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.
CustomerID
WHERE salesinfo.CustomerID IS NULL
|
Á¬½Ó£¨JOIN£©.. Ö®ËùÒÔ¸üÓÐЧÂÊһЩ£¬ÊÇÒòΪ MySQL²»ÐèÒªÔÚÄÚ´æÖд´½¨ÁÙʱ±íÀ´Íê³ÉÕâ¸öÂß¼ÉϵÄÐèÒªÁ½¸ö²½ÖèµÄ²éѯ¹¤×÷¡£
¹²5Ò³: ÉÏÒ»Ò³ 1 [2] [3] [4] [5] ÏÂÒ»Ò³ |