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 )°´²¿ÃÅ·Ö×é¶Ô¹¤×ʽøÐÐÅÅÐò
Ïà¹ØÎĵµ£º
ÏîÄ¿ÖÐÓõ½µÄpartition£¬×ܽáÕûÀíÒ»ÏÂÏà¹ØÖªÊ¶¡££¨ÍøÉϲɼ¯£¬ÈçÓÐÇÖȨ£¬Ç뼰ʱÁªÏµ¡££©
Ò»¡¢Oracle·ÖÇø¼ò½é
ORACLEµÄ·ÖÇøÊÇÒ»ÖÖ´¦Àí³¬´óÐÍ±í¡¢Ë÷ÒýµÈµÄ¼¼Êõ¡£·ÖÇøÊÇÒ»ÖÖ“·Ö¶øÖÎÖ®”µÄ¼¼Êõ£¬Í¨¹ý½«´ó±íºÍË÷Òý·Ö³É¿ÉÒÔ¹ÜÀíµÄС¿é£¬´Ó¶ø±ÜÃâÁ˶Ôÿ¸ö±í×÷Ϊһ¸ö´óµÄ¡¢µ¥¶ÀµÄ¶ÔÏó½øÐйÜÀí£¬Îª´óÁ¿Êý¾ÝÌṩ ......
ÓÃdistinct¹Ø¼ü×ÖÖ»ÄܹýÂ˲éѯ×Ö¶ÎÖÐËùÓмǼÏàͬµÄ£¨¼Ç¼¼¯Ïàͬ£©£¬¶øÈç¹ûÒªÖ¸¶¨Ò»¸ö×Ö¶ÎȴûÓÐЧ¹û£¬ÁíÍâdistinct¹Ø¼ü×Ö»áÅÅÐò£¬Ð§Âʺܵ͡£
select distinct name from t1 ÄÜÏû³ýÖØ¸´¼Ç¼£¬µ«Ö»ÄÜȡһ¸ö×ֶΣ¬ÏÖÔÚҪͬʱȡid,nameÕâ2¸ö×ֶεÄÖµ¡£
select distinct id,name from t1 ¿ÉÒÔÈ¡¶à¸ö×ֶΣ¬µ«Ö»ÄÜÏû³ýÕâ2¸ö×Ö¶ ......
1.»ù±¾½á¹¹
CREATE OR REPLACE PROCEDURE ´æ´¢¹ý³ÌÃû×Ö
(
²ÎÊý1 IN NUMBER,
²ÎÊý2 IN NUMBER
) IS
±äÁ¿1 INTEGER :=0;
±äÁ¿2 DATE;
BEGIN
END ´æ´¢¹ý³ÌÃû×Ö
2.SELECT INTO STATEMENT
½«select²éѯµÄ½á¹û´æÈëµ½±äÁ¿ÖУ¬¿ÉÒÔͬʱ½«¶à¸öÁд洢¶à¸ö±äÁ¿ÖУ¬±ØÐëÓÐ ......
---------------oracle ÈëÃÅ
´ò¿ª·þÎñÆ÷
net start oracleserviceORCL
´ò¿ª¼àÌýÆ÷
lsnrctl start
¹Ø±Õ·þÎñÆ÷
net stop oracleserviceORCL
¹Ø±Õ¼àÌýÆ÷
lsnrctl stop
-----------------------------
1.´´½¨±í
Create table ±íÃû
(×Ö¶ÎÃû Êý¾Ý ......