sqlÖÐ in ¡¢not in ¡¢exists¡¢not exists Ó÷¨ºÍ²î±ð
exists £¨sql ·µ»Ø½á¹û¼¯ÎªÕ棩
not exists (sql ²»·µ»Ø½á¹û¼¯ÎªÕ棩
ÈçÏ£º
±íA
ID NAME
1 A1
2 A2
3 A3
±íB
ID AID NAME
1 1 B1
2 2 B2
3 2 B3
±íAºÍ±íBÊÇ£±¶Ô¶àµÄ¹ØÏµ A.ID => B.AID
SELECT ID,NAME from A WHERE EXIST (SELECT * from B WHERE A.ID=B.AID)
Ö´Ðнá¹ûΪ
1 A1
2 A2
ÔÒò¿ÉÒÔ°´ÕÕÈçÏ·ÖÎö
SELECT ID,NAME from A WHERE EXISTS (SELECT * from B WHERE B.AID=£±)
--->SELECT * from B WHERE B.AID=£±ÓÐÖµ·µ»ØÕæËùÒÔÓÐÊý¾Ý
SELECT ID,NAME from A WHERE EXISTS (SELECT * from B WHERE B.AID=2)
--->SELECT * from B WHERE B.AID=£²ÓÐÖµ·µ»ØÕæËùÒÔÓÐÊý¾Ý
SELECT ID,NAME from A WHERE EXISTS (SELECT * from B WHERE B.AID=3)
--->SELECT * from B WHERE B.AID=£³ÎÞÖµ·µ»ØÕæËùÒÔûÓÐÊý¾Ý
NOT EXISTS ¾ÍÊÇ·´¹ýÀ´
SELECT ID,NAME from A WHERE¡¡NOT EXIST (SELECT * from B WHERE A.ID=B.AID)
Ö´Ðнá¹ûΪ
3 A3
===========================================================================
EXISTS = IN,Òâ˼Ïàͬ²»¹ýÓï·¨ÉÏÓеãµãÇø±ð£¬ºÃÏñʹÓÃINЧÂÊÒª²îµã£¬Ó¦¸ÃÊDz»»áÖ´ÐÐË÷ÒýµÄÔÒò
SELECT ID,NAME from A¡¡ WHERE¡¡ID IN (SELECT AID from B)
NOT EXISTS = NOT IN ,Òâ˼Ïàͬ²»¹ýÓï·¨ÉÏÓеãµãÇø±ð
SELECT ID,NAME from A WHERE¡¡ID¡¡NOT IN (SELECT AID from B)
ÏÂÃæÊÇÆÕͨµÄÓ÷¨£º
SQLÖÐIN,NOT IN,EXISTS,NOT EXISTSµÄÓ÷¨ºÍ²î±ð:
¡¡¡¡IN:È·¶¨¸ø¶¨µÄÖµÊÇ·ñÓë×Ó²éѯ»òÁбíÖеÄÖµÏàÆ¥Åä¡£
¡¡¡¡IN ¹Ø¼ü×ÖʹÄúµÃÒÔÑ¡ÔñÓëÁбíÖеÄÈÎÒâÒ»¸öֵƥÅäµÄÐС£
¡¡¡¡µ±Òª»ñµÃ¾ÓסÔÚ California¡¢Indiana »ò Maryland ÖݵÄËùÓÐ×÷ÕßµÄÐÕÃûºÍÖݵÄÁбíʱ£¬¾ÍÐèÒªÏÂÁвéѯ£º
¡¡¡¡SELECT ProductID, ProductName from Northwind.dbo.Products WHERE CategoryID = 1 OR CategoryID = 4 OR CategoryID = 5
¡¡¡¡È»¶ø£¬Èç¹ûʹÓà IN£¬ÉÙ¼üÈëһЩ×Ö·ûÒ²¿ÉÒԵõ½Í¬ÑùµÄ½á¹û£º
¡¡¡¡SELECT ProductID, ProductName from Northwind.dbo.Products WHERE CategoryID IN (1, 4, 5)
¡¡¡¡IN ¹Ø¼ü×ÖÖ®ºóµÄÏîÄ¿±ØÐëÓöººÅ¸ô¿ª£¬²¢ÇÒÀ¨ÔÚÀ¨ºÅÖС£
¡¡¡¡ÏÂÁвéѯÔÚ titleauthor ±íÖвéÕÒÔÚÈÎÒ»ÖÖÊéÖеõ½µÄ°æË°ÉÙÓÚ 50% µÄËùÓÐ×÷ÕßµÄ au_id£¬È»ºó´Ó authors ±íÖÐÑ¡Ôñ
Ïà¹ØÎĵµ£º
×öÒ»¸öϵͳµÄºǫ́£¬»ù±¾É϶¼ÉÙ²»ÁËÔöɾ¸Ä²é£¬×÷Ϊһ¸öÐÂÊÖÈëÃÅ£¬ÎÒÃDZØÐëÒªÕÆÎÕSQLËÄÌõ×î»ù±¾µÄÊý¾Ý²Ù×÷Óï¾ä£ºInsert£¬Select£¬UpdateºÍDelete£¡ ÏÂÃæ¶ÔÕâËĸöÓï¾ä½øÐÐÏêϸµÄÆÊÎö£º
¡¡¡¡ ÊìÁ·ÕÆÎÕSQLÊÇÊý¾Ý¿âÓû§µÄ±¦¹ó²Æ¸»¡£ÔÚ±¾ÎÄÖУ¬ÎÒÃǽ«Òýµ¼ÄãÕÆÎÕËÄÌõ×î»ù±¾µÄÊý¾Ý²Ù×÷Óï¾ä—SQLµÄºËÐŦÄÜ—À´ÒÀ´Î½éÉܱȽ ......
½ñÌì´ÓÊý¾Ý¿âÖвéѯ³öxml£¬Í¬Ê±Ìí¼ÓÒ»¸ö¸ù½Úµã
×öÁËÈçϲâÊÔ£º
create table TestXmlQuery(
ID int identity(1,1) not null,
Name varchar(10)
)
go
insert into [TestXmlQuery] (Name) values('²âÊÔ1')
insert into [TestXmlQuery] (Name) values('²âÊÔ2')
insert into [TestXmlQuery] (Name) values('²âÊÔ3')
......
ÏÂÔØ½âѹÁËOracle SQL Developer¹¤¾ß£¬ÔËÐÐʱ£¬Æô¶¯²»ÁË£¬±¨´íÐÅÏ¢ÈçÏ£º
---------------------------
Unable to create an instance of the Java Virtual Machine
Located at path:
<SQLDEVELOPER>\jdk\jre\bin\client\jvm.dll
---------------------------
ÊÇJVM²ÎÊýÉèÖõÄÎÊÌ⣬ÎҵĽâ¾ö·½°¸ÈçÏ£º
<SQ ......
1 Âß¼Êý¾Ý¿âºÍ±íµÄÉè¼Æ
Êý¾Ý¿âµÄÂß¼Éè¼Æ¡¢°üÀ¨±íÓë±íÖ®¼äµÄ¹ØÏµÊÇÓÅ»¯¹ØÏµÐÍÊý¾Ý¿âÐÔÄܵĺËÐÄ¡£Ò»¸öºÃµÄÂß¼Êý¾Ý¿âÉè¼Æ¿ÉÒÔΪ
ÓÅ»¯Êý¾Ý¿âºÍÓ¦ÓóÌÐò´òÏÂÁ¼ºÃµÄ»ù´¡¡£
±ê×¼»¯µÄÊý¾Ý¿âÂß¼Éè¼Æ°üÀ¨ÓöàµÄ¡¢ÓÐÏ໥¹ØÏµµÄÕ±íÀ´´úÌæºÜ¶àÁеij¤Êý¾Ý±í¡£ÏÂÃæÊÇһЩʹÓñê×¼»¯
±íµÄһЩºÃ´¦¡£
A:ÓÉÓÚ±íÕ£¬Òò´Ë¿ÉÒÔʹŠ......
SQLÓï¾ä¼¯½õ
--Óï ¾ä ¹¦ ÄÜ
--Êý¾Ý²Ù×÷
SELECT --´ÓÊý¾Ý¿â±íÖмìË÷Êý¾ÝÐкÍÁÐ
INSERT& ......