SQLÖÐEXISTSµÄÓ÷¨
±ÈÈçÔÚNorthwindÊý¾Ý¿âÖÐ
ÓÐÒ»¸ö²éѯΪ
SELECT c.CustomerId, CompanyName
from Customers c
WHERE EXISTS(
SELECT OrderID from Orders o
WHERE o.CustomerID = cu.CustomerID)
ÕâÀïÃæµÄEXISTSÊÇÈçºÎÔË×÷ÄØ£¿×Ó²éѯ·µ»ØµÄÊÇOrderId×ֶΣ¬¿ÉÊÇÍâÃæµÄ²éѯҪÕÒµÄÊÇCustomerIDºÍCompanyName×ֶΣ¬ÕâÁ½¸ö×ֶο϶¨²»ÔÚOrderIDÀïÃæ°¡£¬ÕâÊÇÈçºÎÆ¥ÅäµÄÄØ£¿
EXISTSÓÃÓÚ¼ì²é×Ó²éѯÊÇ·ñÖÁÉٻ᷵»ØÒ»ÐÐÊý¾Ý£¬¸Ã×Ó²éѯʵ¼ÊÉϲ¢²»·µ»ØÈκÎÊý¾Ý£¬¶øÊÇ·µ»ØÖµTrue»òFalse
EXISTS
Ö¸¶¨Ò»¸ö×Ó²éѯ£¬¼ì²âÐеĴæÔÚ¡£
Óï·¨
EXISTS subquery
²ÎÊý
subquery
ÊÇÒ»¸öÊÜÏÞµÄ SELECT Óï¾ä (²»ÔÊÐíÓÐ COMPUTE ×Ó¾äºÍ INTO ¹Ø¼ü×Ö)¡£Óйظü¶àÐÅÏ¢£¬Çë²Î¼û SELECT ÖÐÓйØ×Ó²éѯµÄÌÖÂÛ¡£
½á¹ûÀàÐÍ
Boolean
½á¹ûÖµ
Èç¹û×Ó²éѯ°üº¬ÐУ¬Ôò·µ»Ø TRUE¡£
ʾÀý
A. ÔÚ×Ó²éѯÖÐʹÓà NULL ÈÔÈ»·µ»Ø½á¹û¼¯
Õâ¸öÀý×ÓÔÚ×Ó²éѯÖÐÖ¸¶¨ NULL£¬²¢·µ»Ø½á¹û¼¯£¬Í¨¹ýʹÓà EXISTS ÈÔȡֵΪ TRUE¡£
USE Northwind
GO
SELECT CategoryName
from Categories
WHERE EXISTS (SELECT NULL)
ORDER BY CategoryName ASC
GO
B. ±È½ÏʹÓà EXISTS ºÍ IN µÄ²éѯ
Õâ¸öÀý×ӱȽÏÁËÁ½¸öÓïÒåÀàËÆµÄ²éѯ¡£µÚÒ»¸ö²éѯʹÓà EXISTS ¶øµÚ¶þ¸ö²éѯʹÓà IN¡£×¢ÒâÁ½¸ö²éѯ·µ»ØÏàͬµÄÐÅÏ¢¡£
USE pubs
GO
SELECT DISTINCT pub_name
from publishers
WHERE EXISTS
(SELECT *
from titles
WHERE pub_id = publishers.pub_id
AND type = \'business\')
GO
-- Or, using the IN clause:
USE pubs
GO
SELECT distinct pub_name
from publishers
WHERE pub_id IN
(SELECT pub_id
from titles
WHERE type = \'business\')
GO
ÏÂÃæÊÇÈÎÒ»²éѯµÄ½á¹û¼¯£º
pub_name &nbs
Ïà¹ØÎĵµ£º
±¾ÈËдÁËÒ»¸öSQL²éѯ·ÖÎöÆ÷À©Õ¹¹¤¾ß£¬¹¦ÄÜÀàËÆDelphiµÄToDo¡£¿ÉÒÔ·½±ã¿ª·¢ÕßÔÚ´óÁ¿µÄSQL½Å±¾ÖпìËÙÕÒµ½Ã¿¸öÄ£¿é¡¢To-Do£¬´Ó¶øÌá¸ß¿ª·¢¸ßÀûÂÊ¡£
ʹÓõÄʱºòÏÈÔËÐÐSQL²éѯ·ÖÎöÆ÷£¬È»ºóÔËÐб¾³ÌÐò¼´¿É£¨¿ÉÒÔ¹¤¾ß|×Ô¶¨ÒåÖÐÅäÖã©¡£°´F2ÏÔʾTo-Do List´°¿Ú£¬°´Alt+F2ÔÚµ±Ç°Î»ÖòåÈëTo-Do¡£
ÓÉÓÚʱ¼ä¹ØÏµ£¬±¾³ÌÐò²¢Î´ÍêÉÆ£¬ÔÚË« ......
OracleµÄsql*plusÊÇÓëoracle½øÐн»»¥µÄ¿Í»§¶Ë¹¤¾ß¡£ÔÚsql*plusÖУ¬¿ÉÒÔÔËÐÐsql*plusÃüÁîÓësqlÓï¾ä¡£
ÎÒÃÇͨ³£Ëù˵µÄDML¡¢DDL¡¢DCLÓï¾ä¶¼ÊÇsqlÓï¾ä£¬ËüÃÇÖ´ÐÐÍêºó£¬¶¼¿ÉÒÔ±£´æÔÚÒ»¸ö±»³ÆÎªsql bufferµÄÄÚ´æÇøÓòÖУ¬²¢ÇÒÖ»Äܱ£´æÒ»Ìõ×î½üÖ´ÐеÄsqlÓï¾ä£¬ÎÒÃÇ¿ÉÒÔ¶Ô±£´æÔÚsql bufferÖеÄsql Óï¾ä½øÐÐÐ޸ģ¬È»ºóÔÙ´ÎÖ´ÐС£
³ý ......
ËäÈ»²»ÄÜÍêÈ«±ÜÃâËÀËø£¬µ«¿ÉÒÔʹËÀËøµÄÊýÁ¿¼õÖÁ×îÉÙ¡£½«ËÀËø¼õÖÁ×îÉÙ¿ÉÒÔÔö¼ÓÊÂÎñµÄÍÌÍÂÁ¿²¢¼õÉÙϵͳ¿ªÏú£¬ÒòΪֻÓкÜÉÙµÄÊÂÎñ£º
»Ø¹ö£¬¶ø»Ø¹ö»áÈ¡ÏûÊÂÎñÖ´ÐеÄËùÓй¤×÷¡£
ÓÉÓÚËÀËøÊ±»Ø¹ö¶øÓÉÓ¦ÓóÌÐòÖØÐÂÌá½»¡£
ÏÂÁз½·¨ÓÐÖúÓÚ×î´óÏ޶ȵؽµµÍËÀËø£º
°´Í¬Ò»Ë³Ðò·ÃÎʶÔÏó¡£
±ÜÃâÊÂÎñÖеÄÓû§½»»¥¡£
±£³ÖÊÂÎñ¼ò¶Ì ......
½ñÌ칤×÷時ºò輸Èë數據庫ÖÐÓÐÒ»條數據×Ö¶Î記錄ÊÇ"¿Í戶·ñ¾ö"
ʹÓÃ
select * from TelephoneStatusCategory where CategoryName like '%¿Í戶·ñ%'
select * from TelephoneStatusCategory where CategoryName like '%¿Í戶·ñ¾ö% ......
ISNULL
²éѯÊý¾ÝʱÈô×Ö¶ÎÖµÊÇNULL£¬ÔòʹÓÃʹÓÃÖ¸¶¨µÄÌæ»»ÖµÌæ»» NULL¡£
Óï·¨
ISNULL ( check_expression , replacement_value )
²ÎÊý
check_expression
½«±»¼ì²éÊÇ·ñΪ NULLµÄ±í´ïʽ¡£check_expression ¿ÉÒÔÊÇÈκÎÀàÐ͵ġ£
replacement_value
ÔÚ check_expression Ϊ NULLʱ½«·µ»ØµÄ±í´ïʽ¡£replacement_value ± ......