Oracleº¯Êý
º¯Êý:
×Ö·ûº¯Êý
ת»¯³ÉСдLOWER(<C>) ת»¯³É´óдUPPER(<C>) select lower('aAbBcC') from dual;
--------
ÈÕÆÚº¯Êý
add_months(D,<I>)·µ»ØÈÕÆÚD¼ÓÉÏi¸öÔºóµÄ½á¹û
select add_month(sysdate,3)from dual;
last_day(D)º¯Êý·µ»Ø°üº¬ÈÕÆÚDµÄÔ·ݵÄ×îºóÒ»Ìì
select last_day(sysdate)from dual;
.........
-------
·Ö×麯Êý
AVG()·µ»ØÆ½¾ùÖµ
count(*)·µ»Ø²éѯÖÐÐеÄÊýÄ¿
max()·µ»ØÑ¡ÔñÁбíÏîÄ¿µÄ×î´óÖµ
min()·µ»ØÑ¡ÔñÁбíÏîÄ¿µÄ×îСֵ
Select avg(sal),avg(distinct sal),max(sal),min(sal),
sum(sal),count(*),count(sal),count(distinct sal),
count(distinct comm),count(comm)
from emp where deptno=30;
--µ¥ÁзÖ×é
--ÏÔʾÿ¸ö²¿Ãŵį½¾ù¹¤×ʺÍ×î¸ß¹¤×Ê
Select deptno,avg(sal),max(sal) from emp
group by deptno;
--¶àÁзÖ×é
--ÏÔʾÿ¸ö²¿ÃÅ¡¢Ã¿ÖÖ¸ÚλµÄƽ¾ù¹¤×ʺÍ×î¸ß¹¤×Ê
Select deptno,job,avg(sal),max(sal) from emp
group by deptno,job;
having×Ó¾ä
--ÏÔʾƽ¾ù¹¤×ʸßÓÚ2000ÔªµÄ²¿ÃűàºÅ¡¢Æ½¾ù¹¤×ʺÍ×î¸ß¹¤×Ê
select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)>2000;
--×¢Òâ·Ö×麯Êý²»ÄܳöÏÖÔÚwhere×Ó¾äÖÐ
select deptno,avg(sal),max(sal) from emp where avg(sal)>2000 group by deptno;
-----
ÅÅÐòº¯Êý
row_numberº¯Êý(˳ÐòÅÅÐòº¯Êý)
rankº¯Êý(ÅÅÐò´æÔÚÏàֵͬ¸ø³öÏàͬÐòºÅ£¬Í¬Ê±¿Õ³öÔ¤ÁôÐòºÅ)
dense_rankº¯Êý(ÅÅÐò´æÔÚÏàֵͬ¸ø³öÏàͬÐòºÅ£¬µ«²»¿Õ³öÔ¤ÁôÐòºÅ)
------
´°¿Úº¯Êý
over(partition by ·Ö×é×Ö¶ÎÃû order by ÅÅÐò×Ö¶ÎÃû |[desc])
over(partition by deptno order by sal )°´²¿ÃÅ·Ö×é¶Ô¹¤×ʽøÐÐÅÅÐò
Ïà¹ØÎĵµ£º
±íÊÇOracleÊý¾Ý¿â×î»ù±¾¶ÔÏñÖ®Ò»£¬Ëü´æ´¢Êµ¼ÊÊý¾Ý£¬ÓÉÐкÍÁÐ×é³ÉÒ»¸ö¶þά±í¡£
±íºÍÁеÄÃüÃûÒªÇó:
1.³¤¶È±ØÐëÔÚ30¸ö×Ö½ÚÖ®ÄÚ£¬×î¶Ì³¤¶È²»ÄÜÉÙ1¸ö×Ö½Ú
2.±ØÐëÊÇ×Öĸ¿ªÍ·
3.ÄܰüÀ¨×Öĸ¡¢ÊýÖµ¡¢Ï»®Ïß¡¢»õ±Ò·ûºÅ¡£½¨Òé×îºÃ²»ÒªÓûõ±Ò·ûºÅ
&n ......
ÊÕ²Ø×ÔijЩ¸ßÊÖµÄ×ܽᣬ²»¸Ò¶ÀÏí£¬ÓëÓÐÔµÀ´´ËÕß¹²Ïí¡£
-----------------------------------------------------------------------------------
Oracle×Ö·û¼¯ÎÊÌâ×ܽá
¾³£ÓÐͬÊÂ×ÉѯoracleÊý¾Ý¿â×Ö·û¼¯Ïà¹ØµÄÎÊÌ⣬ÈçÔÚ²»Í¬Êý¾Ý¿â×öÊý¾ÝÇ¨ÒÆ¡¢Í¬ÆäËüϵͳ½»»»Êý¾ÝµÈ£¬³£³£ÒòΪ×Ö·û¼¯²»Í¬¶øµ¼ÖÂÇ¨ÒÆÊ§°Ü»òÊý¾Ý¿âÄÚÊý¾Ý±ä³ ......
ÔµÆðÒ»¸ö±í¿Õ¼äÌ«´ó,ɾ³ýÊý¾ÝºóÓÉÓÚÎļþβ±»ÓÃ,ÎÞ·¨resize,´òËã°ÑËùÓбí¿Õ¼äÉϵĶÔÏómoveµ½Ò»¸öÁÙʱ´æ´¢µÄ±í¿Õ¼ä×öÕûÀí¡£
moveÒ»¸ö±íµ½ÁíÍâÒ»¸ö±í¿Õ¼äʱ,Ë÷Òý²»»á¸ú×ÅÒ»Æðmove£¬¶øÇÒ»áʧЧ¡££¨LOBÀàÐÍÀýÍ⣩±ímove£¬ÎÒÃÇ·ÖΪ£º
*ÆÕͨ±ímove
*·ÖÇø±ímove
*LONG,LOB´ó×Ö¶ÎÀàÐÍmoveÀ´½øÐвâÊÔºÍ˵Ã÷¡£
Ë÷ÒýµÄmove£¬ÎÒÃÇÍ ......
oracle·ÖÎöº¯ÊýÊ®·ÖÇ¿´ó£¬ÎÒÃÇÖ»ÒªÕÆÎÕÕâЩ·½·¨£¬¸üÖ±½ÓµÄ˵·¨¾ÍÊÇÖªµÀÕâЩ·ÖÎöº¯ÊýµÄ×÷ÓþÍÄÜÍê³ÉºÜ¶à¹¤×÷¡£
ϱßÌù³öÕâЩº¯Êý£¬¼°¼òµ¥Ó¦Óá£
ÆäÖÐÎÒÏë¶Ôlag£¨£©ºÍlead£¨£©º¯Êý×øÏÂ˵Ã÷£ºlag£¨£©±¾ÉíÊÇÑÓºóµÄÒâ˼Ҳ¾ÍÊÇÑÓºó³öÏÖijÁеÄÊý£¬¶ølead£¨£©ÓÐÒýÁì¡¢ÁìÏȵÄÒâ˼Ҳ¾ÍÊÇÌáǰ¼¸ÐÐÏÔʾijÁÐÊý¾Ý
RANK()
dense_rank() ......
Oracle´¥·¢Æ÷Óï·¨(Ò»)
Ò» Oracle´¥·¢Æ÷Óï·¨
´¥·¢Æ÷ÊÇÌØ¶¨Ê¼þ³öÏÖµÄʱºò£¬×Ô¶¯Ö´ÐеĴúÂë¿é¡£ÀàËÆÓÚ´æ´¢¹ý³Ì£¬´¥·¢Æ÷Óë´æ´¢¹ý³ÌµÄÇø±ðÔÚÓÚ:´æ´¢¹ý³ÌÊÇÓÉÓû§»òÓ¦ÓóÌÐòÏÔʽµ÷ÓõÄ,¶ø´¥·¢Æ÷ÊDz»Äܱ»Ö±½Óµ÷Óõġ£
¹¦ÄÜ£º
1¡¢ ÔÊÐí/ÏÞÖÆ¶Ô±íµÄÐÞ¸Ä
2¡¢ ×Ô¶¯Éú³ÉÅÉÉúÁУ¬±ÈÈç×ÔÔö×Ö¶Î
3¡¢ Ç¿ÖÆÊý¾ÝÒ»ÖÂÐÔ
4¡¢ ÌṩÉó¼Æº ......