ORACLEÖÐin ºÍ existsÇø±ð
INºÍEXISTSÇø±ð
in ÊÇ°ÑÍâ±íºÍÄÚ±í×÷hash join£¬¶øexistsÊǶÔÍâ±í×÷loop£¬Ã¿´ÎloopÔÙ¶ÔÄÚ±í½øÐвéѯ¡£
Ò»Ö±ÒÔÀ´ÈÏΪexists±ÈinЧÂʸߵÄ˵·¨ÊDz»×¼È·µÄ¡£
Èç¹û²éѯµÄÁ½¸ö±í´óСÏ൱£¬ÄÇôÓÃinºÍexists²î±ð²»´ó¡£
Èç¹ûÁ½¸ö±íÖÐÒ»¸ö½ÏС£¬Ò»¸öÊÇ´ó±í£¬Ôò×Ó²éѯ±í´óµÄÓÃexists£¬×Ó²éѯ±íСµÄÓÃin£º
ÀýÈ磺±íA£¨Ð¡±í£©£¬±íB£¨´ó±í£©
1£º
select * from A where cc in (select cc from B)
ЧÂʵͣ¬Óõ½ÁËA±íÉÏccÁеÄË÷Òý£»
select * from A where exists(select cc from B where cc=A.cc)
ЧÂʸߣ¬Óõ½ÁËB±íÉÏccÁеÄË÷Òý¡£
Ïà·´µÄ
2£º
select * from B where cc in (select cc from A)
ЧÂʸߣ¬Óõ½ÁËB±íÉÏccÁеÄË÷Òý£»
select * from B where exists(select cc from A where cc=B.cc)
ЧÂʵͣ¬Óõ½ÁËA±íÉÏccÁеÄË÷Òý¡£
´øinµÄ¹ØÁª×Ó²éѯÊǶàÓàµÄ£¬ÒòΪin×Ó¾äºÍ×Ó²éѯÖÐÏà¹ØµÄ²Ù×÷µÄ¹¦ÄÜÊÇÒ»ÑùµÄ¡£È磺
select staff_name from staff_member where staff_id in
(select staff_id from staff_func where staff_member.staff_id=staff_func.staff_id);
Ϊ·Ç¹ØÁª×Ó²éѯָ¶¨exists×Ó¾äÊDz»Êʵ±µÄ£¬ÒòΪÕâÑù»á²úÉúµÑ¿¨³Ë»ý¡£È磺
select staff_name from staff_member where staff_id
exists (select staff_id from staff_func);
not in ºÍnot exists
Èç¹û²éѯÓï¾äʹÓÃÁËnot in ÄÇôÄÚÍâ±í¶¼½øÐÐÈ«±íɨÃ裬ûÓÐÓõ½Ë÷Òý£»
¶ønot extsts µÄ×Ó²éѯÒÀÈ»ÄÜÓõ½±íÉϵÄË÷Òý¡£
ËùÒÔÎÞÂÛÄĸö±í´ó£¬ÓÃnot exists¶¼±Ènot inÒª¿ì¡£
¾¡Á¿²»ÒªÊ¹ÓÃnot in×Ӿ䡣ʹÓÃminus ×Ӿ䶼±Ènot in ×Ó¾ä¿ì£¬ËäȻʹÓÃminus×Ó¾äÒª½øÐÐÁ½´Î²éѯ£º
select staff_name from staff_member where staff_id in (select staff_id from staff_member minus select staff_id from staff_func where func_id like '81%');
in Óë "=" µÄÇø±ð
select name from student where name in ('zhang','wang','li','zhao');
Óë
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'
µÄ½á¹ûÊÇÏàͬµÄ¡£
Ïà¹ØÎĵµ£º
oracleÊý¾Ý¿â²åÈëÈÕÆÚÐÍÊý¾Ý
ÍùOracleÊý¾Ý¿âÖвåÈëÈÕÆÚÐÍÊý¾Ý£¨to_dateµÄÓ÷¨£©
INSERT INTO FLOOR VALUES ( to_date ( '2007-12-20 18:31:34' , 'YYYY-MM-DD HH24:MI:SS' ) ) ;
²éѯÏÔʾ£º2007-12-20 18:31:34.0
-------------------
INSERT INTO FLOOR VALUES ......
ÔÚOracleÊý¾Ý¿âÖÐ''ÓëNULLÊǵȼ۵ġ£¾ù±íʾ¿ÕÖµ£¬¶ø²»ÊÇÀàËÆÆäËûÊý¾Ý¿âÉÏ''±íʾ¿Õ´®£¬NULL±íʾ¿ÕÖµ¡£
ORACLEÔÊÐíÈκÎÒ»ÖÖÊý¾ÝÀàÐ͵Ä×Ö¶ÎΪ¿Õ£¬³ýÁËÒÔÏÂÁ½ÖÖÇé¿ö£º
1¡¢Ö÷¼ü×ֶΣ¨primary key£©£¬
2¡¢¶¨ÒåʱÒѾ¼ÓÁËNOT NULLÏÞÖÆÌõ¼þµÄ×Ö¶Î
˵Ã÷£º
1¡¢NULLµÈ¼ÛÓÚûÓÐÈκÎÖµ¡¢ÊÇδ֪Êý¡£
2¡¢NULLÓë ......
Ò»£ºsql loader µÄÌصã
oracle×Ô¼º´øÁ˺ܶàµÄ¹¤¾ß¿ÉÒÔÓÃÀ´½øÐÐÊý¾ÝµÄǨÒÆ¡¢±¸·ÝºÍ»Ö¸´µÈ¹¤×÷¡£µ«ÊÇÿ¸ö¹¤¾ß¶¼ÓÐ×Ô¼ºµÄÌص㡣
±ÈÈç˵expºÍimp¿ÉÒÔ¶ÔÊý¾Ý¿âÖеÄÊý¾Ý½øÐе¼³öºÍµ¼³öµÄ¹¤×÷£¬ÊÇÒ»ÖֺܺõÄÊý¾Ý¿â±¸·ÝºÍ»Ö¸´µÄ¹¤¾ß£¬Òò´ËÖ÷ÒªÓÃÔÚÊý¾Ý¿âµÄÈȱ¸·ÝºÍ»Ö¸´·½Ãæ¡£ÓÐ×ÅËٶȿ죬ʹÓüòµ¥£¬¿ì½ÝµÄÓŵ㣻ͬʱҲÓÐһР......
select 'create sequence '||sequence_name||
' minvalue '||min_value||
' maxvalue '||max_value||
' start with '||last_number||
&n ......