Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

Oracle×Ó²éѯ

×Ó²éѯ
 µ¥ÐÐ×Ó²éѯ(single-row subqueries)
    Ê¹ÓõÄÔËËã·ûºÅ(=,>,<,>=,<=,<>)
  ¶àÐÐ×Ó²éѯ(multiple-row subqueries)
    Ê¹ÓõÄÔËËã·ûºÅ(in,not in,exists,not exits,all,any)
   Ïà¹Ø×Ó²éѯ(correlated subqueries)
    ¸ñʽ select ÁÐÃû,(select Óï¾ä) from ±íÃû
   ±êÁ¿×Ó²éѯ(scalar subqueries)
    ×Ó²éѯÊÇ·µ»Øµ¥Ðе¥ÁÐ,¸ñʽͬÉÏ
   ¶àÁÐ×Ó²éѯ(multiple-column subqueries)
   ÔÚDDLÓï¾äÖÐʹÓÃ×Ó²éѯ
   ÔÚDMLÓï¾äÖÐʹÓÃ×Ó²éѯ
--------
µ¥ÐÐ×Ó²éѯ
 --ÏÔʾ¹¤×Ê×î¸ßµÄ¹ÍÔ±ÐÅÏ¢
 Select ename,deptno,sal from emp
 Where sal=(select max(sal) from emp);
--------
¶àÐÐ×Ó²éѯ
 --ÏÔʾÓ벿ÃűàºÅΪ20µÄ¸ÚλÏàͬµÄ¹ÍÔ±ÐÅÏ¢
 Select ename,deptno,sal,job from emp
 Where job in (select distinct job from emp where deptno=20);
 --ÏÔʾ²»Ó벿ÃűàºÅΪ20µÄ¸ÚλÏàͬµÄ¹ÍÔ±ÐÅÏ¢
 Select ename,deptno,sal,job from emp where job not in (select distinct job from emp where deptno=20);
 --ÏÔʾ¸ßÓÚ²¿ÃűàºÅΪ20µÄËùÓйÍÔ±µÄ¹¤×ʵĹÍÔ±ÐÅÏ¢
 select ename,deptno,sal ,job from emp
 where sal>all(select sal from emp where deptno=20);
 --ÏÔʾ¸ßÓÚ²¿ÃűàºÅΪ20µÄÈκιÍÔ±µÄ¹¤×ʵĹÍÔ±ÐÅÏ¢
 select ename,deptno,sal ,job from emp
 where sal>any(select sal from emp where deptno=20); 
---------
Ïà¹Ø×Ó²éѯ
 --ÏÔʾÿ¸ö²¿ÃŵÄ×î¸ß¹¤×ʵĹÍÔ±ÐÅÏ¢
 select deptno,(select max(sal) from emp b where b.deptno=a.deptno) maxsal
 from emp a order by deptno;
 --Ôö¼Ódistinct
 select distinct deptno,(select max(sal) from emp b where b.deptno=a.deptno) maxsal
 from emp a order by deptno;
 --ÏÔʾ¹¤×÷ÔÚNEW YORKµÄ¹ÍÔ±ÐÅÏ¢
 select ename,deptno,sal,job from emp
 where exists (select 'x' from dept where dept.deptno=emp.deptno and dept.loc='NEW YORK');
---------
±êÁ¿×Ó²éѯ
 --·µ»Øµ¥Ðе¥ÁÐ
 Select count(*) from emp;
 Select sum(sal)


Ïà¹ØÎĵµ£º

¹ØÓÚoracleѧϰ,¶ÔÕÒ×ÊÁϽ׶εÄÓÐÒâÒå

ƽʱ½Ó´¥mysql½Ï¶à£¬ÒòΪmysqlÔÚÒ»°ãµÄСÏîÄ¿Öй»ÓÃÁË ¿ªÔ´¶øÇÒÃâ·Ñ¡£
ºÜ¶à¹«Ë¾ÐèÒªoracleά»¤µÄ£¬¿ª·¢oracleÉõÖÁÊǶþ´Î¿ª·¢ÔÚ¹úÄÚÒ²±È½ÏÉٵģ¬Óм׹ÇÎĹ«Ë¾£¬Ë­¸ÒºÍËûÃÇÇÀ·¹Íë°¡ ¹þ¹þ ²»¹ýÈ˼ҵÄÊÕ·ÑÂù¹óµÄ£¬ÄãÈç¹ûÓÐˮƽ£¬³Ôµã²Ð¸þµÄ»ú»áÂù¶à¡£ oracleµÄÅàѵÐÅÏ¢±í´ïÁËÊг¡ÐèÇó¡£
£¨ÎÒÔÚѧУ»·¾³µÄ²ÂÏ룬רҵÈËÊ¿À´ÅÄש ......

oracleÖÐÈ¥ÖØ¸´¼Ç¼,²»ÓÃdistinct

ÓÃdistinct¹Ø¼ü×ÖÖ»ÄܹýÂ˲éѯ×Ö¶ÎÖÐËùÓмǼÏàͬµÄ£¨¼Ç¼¼¯Ïàͬ£©£¬¶øÈç¹ûÒªÖ¸¶¨Ò»¸ö×Ö¶ÎȴûÓÐЧ¹û£¬ÁíÍâdistinct¹Ø¼ü×Ö»áÅÅÐò£¬Ð§Âʺܵ͡£
select distinct name from t1 ÄÜÏû³ýÖØ¸´¼Ç¼£¬µ«Ö»ÄÜȡһ¸ö×ֶΣ¬ÏÖÔÚҪͬʱȡid,nameÕâ2¸ö×ֶεÄÖµ¡£
select distinct id,name from t1 ¿ÉÒÔÈ¡¶à¸ö×ֶΣ¬µ«Ö»ÄÜÏû³ýÕâ2¸ö×Ö¶ ......

Oracle Date Function ½²½âºÍÊÂÀý

http://blog.csdn.net/mustbelove/archive/2007/04/17/1567836.aspx(ת)
1 year=1*12 months
1 day=24
hours=24*(1*60mins)=24*60*(1*60 seconds)
1 week =7 days
×¢Ò⣺
ºÚÉ«×ÖÌåÊÇ oracle

8i£¬9i¶¼¿ÉÒÔʹÓõĺ¯Êý£¬
À¶É«×ÖÌåÊÇ Oracle
9i
ÐÂ
ÔöµÄ²¿·Ö¡£
 
Çë´ó¼ÒÔÚʹÓÃʱ£¬×¢Òâ°æ±¾ÏÞÖÆ¡£
add_mon ......

oracle ÔÚ·Ö×éÄÚÅÅÐòµÄ·½·¨

oracle·ÖÎöº¯ÊýÊ®·ÖÇ¿´ó£¬ÎÒÃÇÖ»ÒªÕÆÎÕÕâЩ·½·¨£¬¸üÖ±½ÓµÄ˵·¨¾ÍÊÇÖªµÀÕâЩ·ÖÎöº¯ÊýµÄ×÷ÓþÍÄÜÍê³ÉºÜ¶à¹¤×÷¡£
ϱßÌù³öÕâЩº¯Êý£¬¼°¼òµ¥Ó¦Óá£
ÆäÖÐÎÒÏë¶Ôlag£¨£©ºÍlead£¨£©º¯Êý×øÏÂ˵Ã÷£ºlag£¨£©±¾ÉíÊÇÑÓºóµÄÒâ˼Ҳ¾ÍÊÇÑÓºó³öÏÖijÁеÄÊý£¬¶ølead£¨£©ÓÐÒýÁì¡¢ÁìÏȵÄÒâ˼Ҳ¾ÍÊÇÌáǰ¼¸ÐÐÏÔʾijÁÐÊý¾Ý
RANK()
dense_rank() ......

OracleË÷Òý¡¢ÊÓͼ¡¢sqlplus»ù´¡¡¢·ÖÇø±í

Ë÷Òý
 Ë÷ÒýµÄÄ¿µÄÊÇÌá¸ß´æ´¢Êý¾ÝÒ³ÃæµÄËÙ¶È
 ·þÎñÆ÷ɨÃèË÷ÒýÒÔ»ñµÃÊý¾Ý´æ´¢µ¥ÔªµÄµØÖ·
 Ë÷ÒýµÄÓŵãΪ£ºÌá¸ß²éѯµÄÖ´ÐÐËÙ¶È¡¢ÊµÊ©Êý¾ÝµÄΨһÐÔ¡¢¼ÓËÙÁ˱íÖ®¼äµÄÁ¬½Ó
 Ë÷ÒýµÄȱµãΪ£º´´½¨Ë÷ÒýÐèÒª»¨·Ñ½Ï¶àµÄʱ¼ä¡¢Ã¿¸ö´´½¨µÄË÷ÒýÁ¬Í¬Ô­ÏȵÄÊý¾ÝÔ´¶¼ÐèÒª¿Õ¼äÀ´´æ´¢Êý¾Ý¡¢Ã¿´ÎÐÞ¸ÄÊý¾ÝʱË÷Òý¶¼Òª¸üÐÂ
......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ