Oracle All And Any
ת£ºOracle All And Any
ÕâÊÇÔÚOracleÖбȽÏÈÝÒ×±»ºöÊÓµÄÁ½¸öÌõ¼þº¯Êý£¬µ«ÕâÁ½¸öº¯ÊýÆäʵ¶Ô¼ò»¯SqlÓï¾äÊǷdz£ÖØÒªµÄ×÷Óõġ£
¡¡¡¡ÀýÈç
scott@eddev> select ename, sal from emp where sal > any (1600, 2999);
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000
6 rows selected.
ʵ¼ÊÉÏÒÔÉÏÓï¾ä¿ÉÒÔµÈÓÚ£º
select ename, sal from emp where sal > 1600 or sal > 2999;
ËùÒÔAnyÊǵÈÓÚN¸öorÓï¾ä¡£
ÓÖÈç
select ename from emp where substr(ename,1,1) = any ('A', 'W', 'J');
µÈͬÓÚ£º
select ename from emp where substr(ename,1,1) = 'A' or substr(ename,1,1) ='W' or substr(ename,1,1) ='J';
ÁíÍâ
scott@eddev> select ename, sal from emp where sal > all (1600, 2999);
ENAME SAL
---------- ----------
SCOTT 3000
KING 5000
FORD 3000
ʵ¼ÊÉÏÒÔÉÏÓï¾ä¿ÉÒÔµÈÓÚ£º
select ename, sal from emp where sal > 1600 and sal > 2999;
ËùÒÔAllÊǵÈÓÚN¸öAndÓï¾ä¡£
Ïà¹ØÎĵµ£º
OracleÊý¾Ýµ¼Èëµ¼³öimp/exp¾ÍÏ൱ÓÚoracleÊý¾Ý»¹ÔÓ뱸·Ý¡£expÃüÁî¿ÉÒÔ°ÑÊý¾Ý´ÓÔ¶³ÌÊý¾Ý¿â·þÎñÆ÷µ¼³öµ½±¾µØµÄdmpÎļþ£¬impÃüÁî¿ÉÒÔ°ÑdmpÎļþ´Ó±¾µØµ¼Èëµ½Ô¶´¦µÄÊý¾Ý¿â·þÎñÆ÷ÖС£ ÀûÓÃÕâ¸ö¹¦ÄÜ¿ÉÒÔ¹¹½¨Á½¸öÏàͬµÄÊý¾Ý¿â£¬Ò»¸öÓÃÀ´²âÊÔ£¬Ò»¸öÓÃÀ´ÕýʽʹÓá£
Ö´Ðл·¾³£º¿ÉÒÔÔÚSQLPLUS.EXE»òÕßDOS£¨ÃüÁîÐУ©ÖÐÖ´Ð ......
Ò»°ãÊý¾Ý¿âÖÐʱ¼äµÄ¸ñʽΪDATEÀàÐÍ£¬¶øÎÒÃÇ´ÓÒ³ÃæÖлñÈ¡µÄʱ¼äÍùÍùΪStringÀàÐÍ£¬Õâ¸ö¾ÍÐèÒªÀàÐ͵Äת»»¡£Ò»°ãÎÒÃÇ»áͨ¹ýµ÷Óà Java.text.SimpleDateFormat JAVAÀàÀ´¶ÔÆä½øÐÐת»»¡£Õâ¸öJAVAÀàÖÐÎÒÃǾ³£Óõ½µÄ·½·¨ÓÐÁ½¸ö£¬Ò»¸öÊÇformat(½«Ê±¼ä¸ñʽµÄÊý¾Ýת»»³ÉStringÀàÐÍ)£¬ÁíÒ»¸ö¾ÍÊÇ parse(½«StringÀàÐÍת»»³ÉDATEÀàÐÍ)¡£ÎÒ ......
OracleÉ󼯹¦ÄÜ
Éó¼ÆÊǶÔÑ¡¶¨µÄÓû§¶¯×÷µÄ¼à¿ØºÍ¼Ç¼£¬Í¨³£ÓÃÓÚ£º
u Éó²é¿ÉÒɵĻ¡£ÀýÈ磺Êý¾Ý±»·ÇÊÚȨÓû§Ëùɾ³ý£¬´Ëʱ°²È«¹ÜÀíÔ±¿É¾ö¶¨¶Ô¸Ã Êý¾Ý¿âµÄËùÓÐÁ¬½Ó½øÐÐÉ󼯣¬ÒÔ¼°¶ÔÊý¾Ý¿âµÄËùÓбíµÄ³É¹¦µØ»ò²»³É¹¦µØÉ¾³ý½øÐÐÉ󼯡£
u &n ......
ÔÚOracleÖУ¬Òª°´Ìض¨Ìõ¼þ²éѯǰNÌõ¼Ç¼£¬Óøörownum¾Í¸ã¶¨ÁË¡£
select * from emp where rownum <= 5
¶øÇÒÊéÉÏÒ²¸æ½ë£¬²»ÄܶÔrownumÓÃ">"£¬ÕâÒ²¾ÍÒâζ×Å£¬Èç¹ûÄãÏëÓÃ
select * from emp where rownum > 5
ÔòÊÇʧ°ÜµÄ¡£ÒªÖªµÀΪʲô»áʧ°Ü£¬ÔòÐèÒªÁ˽ârownum±³ºóµÄ»úÖÆ£º
1 Oracle executes your quer ......
ÈÕǰÓöµ½Ò»¸öÎÊÌ⣬ÐèÒª¶ÔÔÚOracleÊý¾Ý¿âÖвéѯ³öÀ´µÄÊýÖµ½øÐбÈÂʵļÆËã¡£ÀàËÆÓÚÈçϵÄSQL£º
Select Round((discount/Amount),2) from dual;
ÕâÑùËã³öÀ´µÄÊý×Ö¿ÉÒÔÂú×ã¾ø´ó²¿·ÖµÄÐèÇó¡£µ«ÊǶÔÓÚ²»µ½1µÄÊý×Ö£¬ÎÊÌâ³öÀ´ÁË......ÏÔʾµÄÀàËÆÓÚ .45Ñù×ÓµÄÊý¾Ý¡£0.45Ç°ÃæµÄ0²»¼ûÁË¡£
ÒÔÏÂ×ܽáÁËÁ½ÖÖ·½·¨À´½â¾öȱ0µÄÎÊÌâ¡£
µÚ ......