SQLÀïµÄEXISTSÓëin¡¢not existsÓënot in
SQLÀïµÄEXISTSÓëin¡¢not existsÓënot in
ϵͳҪÇó½øÐÐSQLÓÅ»¯£¬¶ÔЧÂʱȽϵ͵ÄSQL½øÐÐÓÅ»¯£¬Ê¹ÆäÔËÐÐЧÂʸü¸ß£¬ÆäÖÐÒªÇó¶ÔSQLÖеIJ¿·Öin/not inÐÞ¸ÄΪexists/not exists
Ð޸ķ½·¨ÈçÏ£º
inµÄSQLÓï¾ä
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime
from tab_oa_pub WHERE is_check=1 and
category_id in (select id from tab_oa_pub_cate where no='1')
order by begintime desc
ÐÞ¸ÄΪexistsµÄSQLÓï¾ä
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime
from tab_oa_pub WHERE is_check=1 and
exists (select id from tab_oa_pub_cate where tab_oa_pub.category_id=convert(int,no) and no='1')
order by begintime desc
·ÖÎöÒ»ÏÂexistsÕæµÄ¾Í±ÈinµÄЧÂʸßÂð£¿
ÎÒÃÇÏÈÌÖÂÛINºÍEXISTS¡£
select * from t1 where x in ( select y from t2 )
ÊÂʵÉÏ¿ÉÒÔÀí½âΪ£º
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
--Èç¹ûÄãÓÐÒ»¶¨µÄSQLÓÅ»¯¾Ñ飬´ÓÕâ¾äºÜ×ÔÈ»µÄ¿ÉÒÔÏëµ½t2¾ø¶Ô²»ÄÜÊǸö´ó±í£¬ÒòΪÐèÒª¶Ôt2½øÐÐÈ«±íµÄ“ΨһÅÅÐò”£¬Èç¹ût2ºÜ´óÕâ¸öÅÅÐòµÄÐÔÄÜÊDz»¿ÉÈÌÊܵġ£µ«ÊÇt1¿ÉÒԺܴó£¬ÎªÊ²Ã´ÄØ£¿×îͨË×µÄÀí½â¾ÍÊÇÒòΪt1.x=t2.y¿ÉÒÔ×ßË÷Òý¡£µ«Õâ²¢²»ÊÇÒ»¸öºÜºÃµÄ½âÊÍ¡£ÊÔÏ룬Èç¹ût1.xºÍt2.y¶¼ÓÐË÷Òý£¬ÎÒÃÇÖªµÀË÷ÒýÊÇÖÖÓÐÐòµÄ½á¹¹£¬Òò´Ët1ºÍt2Ö®¼ä×î¼ÑµÄ·½°¸ÊÇ×ßmerge join¡£ÁíÍ⣬Èç¹ût2.yÉÏÓÐË÷Òý£¬¶Ôt2µÄÅÅÐòÐÔÄÜÒ²ÓкܴóÌá¸ß¡£
select * from t1 where exists ( select null from t2 where y = x )
¿ÉÒÔÀí½âΪ£º
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD!
end if
end loop
--Õâ¸ö¸üÈÝÒ×Àí½â£¬t1ÓÀÔ¶ÊǸö±íɨÃ裡Òò´Ët1¾ø¶Ô²»ÄÜÊǸö´ó±í£¬¶øt2¿ÉÒÔº
Ïà¹ØÎĵµ£º
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(Create£¬Alter£¬Drop£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(Select£¬Delete£¬Update£¬Insert)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
Create&nb ......
ת×Ô£ºhttp://topic.csdn.net/u/20100109/09/70f82fb7-eabc-4140-9573-5f8395e0f62e.html
²éѯËÙ¶ÈÂýµÄÔÒòºÜ¶à£¬³£¼ûÈçϼ¸ÖÖ£º
¡¡¡¡1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
¡¡¡¡2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
¡¡¡¡3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
¡¡¡¡4¡¢ÄÚ´æ²»×ã
¡¡¡ ......
¼òµ¥µØ˵£¬¾ÍÊÇ´ÓCDMÉú³ÉPDMµÄʱºò£¬releationshipÔ¼ÊøÎÞЧ£¬²»ÄÜÉú³ÉÍâ¼üÔ¼ÊøµÄ´úÂë¡£Éú³É»ùÓÚoracleµÄpdmûÎÊÌ⣬ms sql serverÓÐÎÊÌâ
ÕâÀïµÄÊÇsql server 2005
Power DesignerÊÇ15.0.0.2613
¿´Æð來ÊÇPDµÄbug,½â決辦·¨ÊÇÔÚPDM畫ÃæÖÐ點選Model->References...
會Ìø³öÒ ......
a.Êý¾Ý¿â»ù´¡
i.¸ÅÄ±í¼°Ïà¹Ø²Ù×÷¶ÔÏóµÄ¼¯ºÏ£¨Ö÷Êý¾ÝÎļþmdf£¬¶þ¼¶Êý¾ÝÎļþndf£¬ÈÕÖ¾ldf£©
ii.×é³É£ºÏµÍ³£º master(¼Ç¼ÁËËùÓеķþÎñÆ÷ϵͳÐÅÏ¢)
,model£¨¼Ç¼ÁËËùÓÐÁÙʱ¼Ç¼£¬Á´½Ó¶Ï¿ªÊ±£¬Êý¾Ý¶ªÊ§£©
Tempdb£¨Êý¾ÝÄ£°å¿â£¬º¬´´½¨¿âʱµÄȱʡÊý¾Ý£©
, ......
Ò»¡¢»ù´¡
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý¿â
drop database dbname
3¡¢ËµÃ÷£º±¸·Ýsql server
--- ´´½¨±¸·ÝÊý¾ÝµÄ device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'
--- ¿ªÊ¼±¸·Ý
BACKUP DATABASE pubs TO testBack
......