ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[Âà¶K] ORACLE ¨ç¼Æ¤j¥þ

[Âà¶K] ORACLE ¨ç¼Æ¤j¥þ

SQL¤¤ªº³æ°O¿ý¨ç¼Æ
1.ASCII
ªð¦^»P«ü©wªº¦r²Å¹ïÀ³ªº¤Q¶i¨î¼Æ;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;

¡@¡@¡@¡@A¡@¡@¡@¡@ A¡@¡@¡@ZERO¡@¡@ SPACE
--------- --------- --------- ---------
¡@¡@¡@ 65¡@¡@¡@¡@97¡@¡@¡@¡@48¡@¡@¡@¡@32


2.CHR
µ¹¥X¾ã¼Æ,ªð¦^¹ïÀ³ªº¦r²Å;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;

ZH C
-- -
»¯ A

3.CONCAT
³s±µ¨â­Ó¦r²Å¦ê;
SQL> select concat('010-','88888888')||'Âà23'¡@°ª°®Äv¹q¸Ü from dual;

°ª°®Äv¹q¸Ü
----------------
010-88888888Âà23

4.INITCAP
ªð¦^¦r²Å¦ê¨Ã±N¦r²Å¦êªº²Ä¤@­Ó¦r¥ÀÅܬ°¤j¼g;
SQL> select initcap('smith') upp from dual;

UPP
-----
Smith


5.INSTR(C1,C2,I,J)
¦b¤@­Ó¦r²Å¦ê¤¤·j¯Á«ü©wªº¦r²Å,ªð¦^µo²{«ü©wªº¦r²Åªº¦ì¸m;
C1¡@¡@³Q·j¯Áªº¦r²Å¦ê
C2¡@¡@§Æ±æ·j¯Áªº¦r²Å¦ê
I¡@¡@ ·j¯Áªº¶}©l¦ì¸m,Àq»{¬°1
J¡@¡@ ¥X²{ªº¦ì¸m,Àq»{¬°1
SQL> select instr('oracle traning','ra',1,2) instring from dual;

INSTRING
---------
¡@¡@¡@¡@9


6.LENGTH
ªð¦^¦r²Å¦êªºªø«×;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;

NAME¡@ LENGTH(NAME) ADDR¡@¡@¡@¡@¡@¡@ LENGTH(ADDR)¡@¡@¡@ SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
°ª°®Äv¡@¡@¡@¡@¡@¡@3 ¥_¨Ê¥«®ü¿õ°Ï¡@¡@¡@¡@¡@¡@¡@¡@6¡@ 9999.99¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@7



7.LOWER
ªð¦^¦r²Å¦ê,¨Ã±N©Ò¦³ªº¦r²Å¤p¼g
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;

AABBCCDD
--------
aabbccdd


8.UPPER
ªð¦^¦r²Å¦ê,¨Ã±N©Ò¦³ªº¦r²Å¤j¼g
SQL> select upper('AaBbCcDd') upper from dual;

UPPER
--------
AABBCCDD



9.RPAD©MLPAD(Ö߶K¦r²Å)
RPAD¡@¦b¦Cªº¥kÃäÖ߶K¦r²Å
LPAD¡@¦b¦Cªº¥ªÃäÖ߶K¦r²Å
SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;

LPAD(RPAD('GAO',1
-----------------
*******gao*******
¤£°÷¦r²Å«h¥Î*¨Ó¶ñº¡


10.LTRIM©MRTRIM
LTRIM¡@§R°£¥ªÃä¥X²{ªº¦r²Å¦ê
RTRIM¡@§R°£¥kÃä¥X²{ªº¦r²Å¦ê
SQL> select ltrim(rtrim('¡@ gao qian jing¡@ ',' '),' ') from dual;

LTRIM(RTRIM('
-------------
gao qian jing


11.SUBSTR(string,start,count)
¨ú¤l¦r²Å¦ê,±qstart¶}©l,¨úcount­Ó
SQL> select substr('13088888888',3,8) from dual;

SUBSTR('
--------
08888888


12.REPLACE('string','s1','s2')
string ¡@ §Æ±æ³Q´À´«ªº¦r²Å©ÎÅܶq
s1¡@¡@¡@ ³Q´À´«ªº¦r²Å¦ê
s2¡@¡@¡@ ­n´À´«ªº¦r²Å¦ê
SQL> select replace('he love you','he','i') from dual;

REPLACE('H
----------
i love you


13.SOUNDEX
ªð¦^¤@­Ó»Pµ¹©wªº¦r²Å¦êŪ­µ¬Û¦Pªº¦r²Å¦ê
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');

SQL> select xm from table1 where soundex(xm)=soundex('weather');

XM
--------
weather
wether


14.TRIM('s' from 'string')
LEADING¡@ °Å±¼«e­±ªº¦r²Å
TRAILING¡@°Å±¼«á­±ªº¦r²Å
¦pªG¤£«ü©w,Àq»{¬°ªÅ®æ²Å

15.ABS
ªð¦^«ü©w­Èªºµ´¹ï­È
SQL> select abs(100),abs(-100) from dual;

ABS(100) ABS(-100)
--------- ---------
¡@¡@¡@100¡@¡@¡@ 100


16.ACOS
µ¹¥X¤Ï¾l©¶ªº­È
SQL> select acos(-1) from dual;

ACOS(-1)
---------
3.1415927


17.ASIN
µ¹¥X¤Ï¥¿©¶ªº­È
SQL> select asin(0.5) from dual;

ASIN(0.5)
---------
.52359878


18.ATAN
ªð¦^¤@­Ó¼Æ¦rªº¤Ï¥¿¤Á­È
SQL> select atan(1) from dual;

¡@ATAN(1)
---------
.78539816


19.CEIL
ªð¦^¤j©ó©Îµ¥©óµ¹¥X¼Æ¦rªº³Ì¤p¾ã¼Æ
SQL> select ceil(3.1415927) from dual;

CEIL(3.1415927)
---------------
¡@¡@¡@¡@¡@¡@¡@4


20.COS
ªð¦^¤@­Óµ¹©w¼Æ¦rªº¾l©¶
SQL> select cos(-3.1415927) from dual;

COS(-3.1415927)
---------------
¡@¡@¡@¡@¡@¡@ -1


21.COSH
ªð¦^¤@­Ó¼Æ¦r¤Ï¾l©¶­È
SQL> select cosh(20) from dual;

COSH(20)
---------
242582598


22.EXP
ªð¦^¤@­Ó¼Æ¦reªºn¦¸¤è®Ú
SQL> select exp(2),exp(1) from dual;

¡@ EXP(2)¡@¡@EXP(1)
--------- ---------
7.3890561 2.7182818


23.FLOOR
¹ïµ¹©wªº¼Æ¦r¨ú¾ã¼Æ
SQL> select floor(2345.67) from dual;

FLOOR(2345.67)
--------------
¡@¡@¡@¡@¡@2345


24.LN
ªð¦^¤@­Ó¼Æ¦rªº¹ï¼Æ­È
SQL> select ln(1),ln(2),ln(2.7182818) from dual;

¡@¡@LN(1)¡@¡@ LN(2) LN(2.7182818)
--------- --------- -------------
¡@¡@¡@¡@0 .69314718¡@¡@ .99999999


25.LOG(n1,n2)
ªð¦^¤@­Ó¥Hn1¬°©³n2ªº¹ï¼Æ
SQL> select log(2,1),log(2,4) from dual;

LOG(2,1)¡@LOG(2,4)
--------- ---------
¡@¡@¡@¡@0¡@¡@¡@¡@ 2


26.MOD(n1,n2)
ªð¦^¤@­Ón1°£¥Hn2ªº¾l¼Æ
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;

MOD(10,3)¡@MOD(3,3) ¡@MOD(2,3)
--------- --------- ---------
¡@¡@¡@¡@1¡@¡@¡@¡@ 0¡@¡@¡@¡@ 2


27.POWER
ªð¦^n1ªºn2¦¸¤è®Ú
SQL> select power(2,10),power(3,3) from dual;

POWER(2,10) POWER(3,3)
----------- ----------
¡@¡@¡@ 1024¡@¡@¡@¡@ 27


28.ROUND ©MTRUNC
«ö·Ó«ü©wªººë«×¶i¦æ±Ë¤J
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;

ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
¡@¡@¡@¡@ 56¡@¡@¡@¡@¡@-55¡@¡@¡@¡@¡@55¡@¡@¡@¡@¡@-55


29.SIGN
¨ú¼Æ¦rnªº²Å¸¹,¤j©ó0ªð¦^1,¤p©ó0ªð¦^-1,µ¥©ó0ªð¦^0
SQL> select sign(123),sign(-100),sign(0) from dual;

SIGN(123) SIGN(-100)¡@ SIGN(0)
--------- ---------- ---------
¡@¡@¡@¡@1¡@¡@¡@¡@ -1¡@¡@¡@¡@ 0


30.SIN
ªð¦^¤@­Ó¼Æ¦rªº¥¿©¶­È
SQL> select sin(1.57079) from dual;

SIN(1.57079)
------------
¡@¡@¡@¡@¡@ 1


31.SIGH
ªð¦^Âù¦±¥¿©¶ªº­È
SQL> select sin(20),sinh(20) from dual;

¡@SIN(20)¡@SINH(20)
--------- ---------
.91294525 242582598


32.SQRT
ªð¦^¼Æ¦rnªº®Ú
SQL> select sqrt(64),sqrt(10) from dual;

SQRT(64)¡@SQRT(10)
--------- ---------
¡@¡@¡@¡@8 3.1622777


33.TAN
ªð¦^¼Æ¦rªº¥¿¤Á­È
SQL> select tan(20),tan(10) from dual;

¡@TAN(20)¡@ TAN(10)
--------- ---------
2.2371609 .64836083


34.TANH
ªð¦^¼Æ¦rnªºÂù¦±¥¿¤Á­È
SQL> select tanh(20),tan(20) from dual;

TANH(20)¡@ TAN(20)
--------- ---------
¡@¡@¡@¡@1 2.2371609



35.TRUNC
«ö·Ó«ü©wªººë«×ºI¨ú¤@­Ó¼Æ
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;

¡@ TRUNC1 TRUNC(124.16666,2)
--------- ------------------
¡@¡@¡@100¡@¡@¡@¡@¡@¡@ 124.16



36.ADD_MONTHS
¼W¥[©Î´î¥h¤ë¥÷
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;

TO_CHA
------
200002
SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;

TO_CHA
------
199910


37.LAST_DAY
ªð¦^¤é´Áªº³Ì«á¤@¤Ñ
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;

TO_CHAR(SY TO_CHAR((S
---------- ----------
2004.05.09 2004.05.10
SQL> select last_day(sysdate) from dual;

LAST_DAY(S
----------
31-5 ¤ë -04


38.MONTHS_BETWEEN(date2,date1)
µ¹¥Xdate2-date1ªº¤ë¥÷
SQL> select months_between('19-12¤ë-1999','19-3¤ë-1999') mon_between from dual;

MON_BETWEEN
-----------
¡@¡@¡@¡@¡@9
SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;

MON_BETW
---------
¡@¡@¡@-60


39.NEW_TIME(date,'this','that')
µ¹¥X¦bthis®É°Ï=other®É°Ïªº¤é´Á©M®É¶¡
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
¡@2¡@ (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;

BJ_TIME ¡@¡@¡@¡@¡@¡@ LOS_ANGLES
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32


40.NEXT_DAY(date,'day')
µ¹¥X¤é´Á date©M¬P´Áx¤§«á­pºâ¤U¤@­Ó¬P´Áªº¤é´Á
SQL> select next_day('18-5¤ë-2001','¬P´Á¤­') next_day from dual;

NEXT_DAY
----------
25-5¤ë -01



41.SYSDATE
¥Î¨Ó±o¨ì¨t²Îªº·í«e¤é´Á
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;

TO_CHAR(SYSDATE,'
-----------------
09-05-2004 ¬P´Á¤é
trunc(date,fmt) «ö·Óµ¹¥Xªº­n¨D±N¤é´ÁºIÂ_,¦pªGfmt='mi'ªí¥Ü«O¯d¤À,ºIÂ_¬í
SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
¡@2¡@ to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;

HH ¡@¡@¡@¡@¡@¡@¡@¡@¡@HHMM
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00



42.CHARTOROWID
±N¦r²Å¼Æ¾ÚÃþ«¬Âà´«¬°ROWIDÃþ«¬
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;

ROWID¡@¡@¡@¡@¡@¡@¡@ROWIDTOCHAR(ROWID) ENAME
------------------ ------------------ ----------
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES


43.CONVERT(c,dset,sset)
±N·½¦r²Å¦ê sset±q¤@­Ó»y¨¥¦r²Å¶°Âà´«¨ì¥t¤@­Ó¥Øªºdset¦r²Å¶°
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;

conver
------
strutz


44.HEXTORAW
±N¤@­Ó¤Q¤»¶i¨îºc¦¨ªº¦r²Å¦êÂà´«¬°¤G¶i¨î


45.RAWTOHEXT
±N¤@­Ó¤G¶i¨îºc¦¨ªº¦r²Å¦êÂà´«¬°¤Q¤»¶i¨î



46.ROWIDTOCHAR
±NROWID¼Æ¾ÚÃþ«¬Âà´«¬°¦r²ÅÃþ«¬



47.TO_CHAR(date,'format')
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2004/05/09 21:14:41



48.TO_DATE(string,'format')
±N¦r²Å¦êÂà¤Æ¬°ORACLE¤¤ªº¤@­Ó¤é´Á


49.TO_MULTI_BYTE
±N¦r²Å¦ê¤¤ªº³æ¦r¸`¦r²ÅÂà¤Æ¬°¦h¦r¸`¦r²Å
SQL>¡@ select to_multi_byte('°ª') from dual;

TO
--
°ª


50.TO_NUMBER
±Nµ¹¥Xªº¦r²ÅÂà´«¬°¼Æ¦r
SQL> select to_number('1999') year from dual;

¡@¡@ YEAR
---------
¡@¡@ 1999


51.BFILENAME(dir,file)
«ü©w¤@­Ó¥~³¡¤G¶i¨î¤å¥ó
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));


52.CONVERT('x','desc','source')
±N x¦r¬q©ÎÅܶqªº·½sourceÂà´«¬°desc
SQL> select sid,serial#,username,decode(command,
¡@2¡@0,'none',
¡@3¡@2,'insert',
¡@ 4¡@3,
¡@5¡@'select',
¡@6¡@6,'update',
¡@7¡@7,'delete',
¡@8¡@8,'drop',
¡@ 9¡@'other') cmd¡@from v$session where type!='background';

¡@¡@¡@SID¡@ SERIAL# USERNAME¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@ CMD
--------- --------- ------------------------------ ------
¡@¡@¡@¡@1¡@¡@¡@¡@ 1¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@none
¡@¡@¡@¡@2¡@¡@¡@¡@ 1¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@none
¡@¡@¡@¡@3¡@¡@¡@¡@ 1¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@none
¡@¡@¡@¡@4¡@¡@¡@¡@ 1¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@none
¡@¡@¡@¡@5¡@¡@¡@¡@ 1¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@none
¡@¡@¡@¡@6 ¡@¡@¡@¡@ 1¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@none
¡@¡@¡@¡@7¡@¡@¡@1275¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@none
¡@¡@¡@¡@8¡@¡@¡@1275¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@none
¡@¡@¡@¡@9¡@¡@¡@¡@20 GAO¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@select
¡@¡@¡@ 10¡@¡@¡@¡@40 GAO¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@none


53.DUMP(s,fmt,start,length)
DUMP ¨ç¼Æ¥Hfmt«ü©wªº¤º³¡¼Æ¦r®æ¦¡ªð¦^¤@­ÓVARCHAR2Ãþ«¬ªº­È
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;

GLOBAL_NAME ¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@DUMP_STRING
------------------------------ --------------------------------------------------
ORACLE.WORLD¡@¡@¡@¡@¡@¡@¡@¡@¡@ Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D


54.EMPTY_BLOB() ©MEMPTY_CLOB()
³o¨â­Ó¨ç¼Æ³£¬O¥Î¨Ó¹ï¤j¼Æ¾ÚÃþ«¬¦r¬q¶i¦æªì©l¤Æ¾Þ§@ªº¨ç¼Æ


55.GREATEST
ªð¦^¤@²Õªí¹F¦¡¤¤ªº³Ì¤j­È,§Y¤ñ¸û¦r²Åªº½s½X¤j¤p.
SQL> select greatest('AA','AB','AC') from dual;

GR
--
AC
SQL> select greatest('°Ú','¦w','¤Ñ') from dual;

GR
--
¤Ñ


56.LEAST
ªð¦^¤@²Õªí¹F¦¡¤¤ªº³Ì¤p­È
SQL> select least('°Ú','¦w','¤Ñ') from dual;

LE
--
°Ú


57.UID
ªð¦^¼ÐÃÑ·í«e¥Î¤áªº°ß¤@¾ã¼Æ
SQL> show user
USER ¬°"GAO"
SQL> select username,user_id from dba_users where user_id=uid;

USERNAME¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@ USER_ID
------------------------------ ---------
GAO¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@¡@ 25



58.USER
ªð¦^·í«e¥Î¤áªº¦W¦r
SQL> select user from¡@dual;

USER
------------------------------
GAO


59.USEREVN
ªð¦^·í«e¥Î¤áÀô¹Òªº«H®§,opt¥i¥H¬O:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA ¡@¬d¬Ý·í«e¥Î¤á¬O§_¬ODBA¦pªG¬O«hªð¦^true
SQL> select userenv('isdba') from dual;

USEREN
------
FALSE
SQL> select userenv('isdba') from dual;

USEREN
------
TRUE
SESSION
ªð¦^·|¸Ü¼Ð»x
SQL> select userenv('sessionid') from dual;

USERENV('SESSIONID')
--------------------
¡@¡@¡@¡@¡@¡@¡@¡@ 152
ENTRYID
ªð¦^·|¸Ü¤H¤f¼Ð»x
SQL> select userenv('entryid') from dual;

USERENV('ENTRYID')
------------------
¡@¡@¡@¡@¡@¡@¡@¡@ 0
INSTANCE
ªð¦^·í«eINSTANCEªº¼Ð»x
SQL> select userenv('instance') from dual;

USERENV('INSTANCE')
-------------------
¡@¡@¡@¡@¡@¡@¡@¡@¡@1
LANGUAGE
ªð¦^·í«eÀô¹ÒÅܶq
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
ªð¦^·í«eÀô¹Òªº»y¨¥ªºÁY¼g
SQL> select userenv('lang') from dual;

USERENV('LANG')
----------------------------------------------------
ZHS
TERMINAL
ªð¦^¥Î¤áªº²×ºÝ©Î¾÷¾¹ªº¼Ð»x
SQL> select userenv('terminal') from dual;

USERENV('TERMINA
----------------
GAO
VSIZE(X)
ªð¦^Xªº¤j¤p(¦r¸`)¼Æ
SQL> select vsize(user),user from dual;

VSIZE(USER) USER
----------- ------------------------------
¡@¡@¡@¡@¡@6 SYSTEM



60.AVG(DISTINCT|ALL)
allªí¥Ü¹ï©Ò¦³ªº­È¨D¥­§¡­È,distinct¥u¹ï¤£¦Pªº­È¨D¥­§¡­È
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
»y¥y¤w³B²z¡C
SQLWKS> ¡@insert into table3 values('gao',1111.11);
SQLWKS>¡@insert into table3 values('gao',1111.11);
SQLWKS>¡@insert into table3 values('zhu',5555.55);
SQLWKS> commit;

SQL> select avg(distinct sal) from gao.table3;

AVG(DISTINCTSAL)
----------------
¡@¡@¡@¡@ 3333.33

SQL> select avg(all sal) from gao.table3;

AVG(ALLSAL)
-----------
¡@¡@2592.59


61.MAX(DISTINCT|ALL)
¨D³Ì¤j­È,ALLªí¥Ü¹ï©Ò¦³ªº­È¨D³Ì¤j­È,DISTINCT ªí¥Ü¹ï¤£¦Pªº­È¨D³Ì¤j­È,¬Û¦Pªº¥u¨ú¤@¦¸
SQL> select max(distinct sal) from scott.emp;

MAX(DISTINCTSAL)
----------------
¡@¡@¡@¡@¡@¡@5000


62.MIN(DISTINCT|ALL)
¨D³Ì¤p­È,ALLªí¥Ü¹ï©Ò¦³ªº­È¨D³Ì¤p­È,DISTINCTªí¥Ü¹ï¤£¦Pªº­È¨D³Ì¤p­È,¬Û¦Pªº¥u¨ú¤@¦¸
SQL> select min(all sal) from gao.table3;

MIN(ALLSAL)
-----------
¡@¡@1111.11


63.STDDEV(distinct|all)
¨D¼Ð·Ç®t,ALLªí¥Ü¹ï©Ò¦³ªº­È¨D¼Ð·Ç®t,DISTINCTªí¥Ü¥u¹ï¤£¦Pªº­È¨D¼Ð·Ç®t
SQL> select stddev(sal) from scott.emp;

STDDEV(SAL)
-----------
¡@1182.5032

SQL> select stddev(distinct sal) from scott.emp;

STDDEV(DISTINCTSAL)
-------------------
¡@¡@¡@¡@¡@ 1229.951



64.VARIANCE(DISTINCT|ALL)
¨D¨ó¤è®t

SQL> select variance(sal) from scott.emp;

VARIANCE(SAL)
-------------
¡@¡@1398313.9


65.GROUP BY
¥D­n¥Î¨Ó¹ï¤@²Õ¼Æ¶i¦æ²Î­p
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;

¡@ DEPTNO¡@COUNT(*)¡@SUM(SAL)
--------- --------- ---------
¡@¡@¡@ 10¡@¡@¡@¡@ 3¡@¡@¡@8750
¡@¡@¡@ 20¡@¡@¡@¡@ 5¡@¡@ 10875
¡@¡@¡@ 30¡@¡@¡@¡@ 6¡@¡@¡@9400



66.HAVING
¹ï¤À²Õ²Î­p¦A¥[­­¨î±ø¥ó
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;

¡@ DEPTNO¡@COUNT(*)¡@SUM(SAL)
--------- --------- ---------
¡@¡@¡@ 20¡@¡@¡@¡@ 5¡@¡@ 10875
¡@¡@¡@ 30¡@¡@¡@¡@ 6¡@¡@¡@9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;

¡@ DEPTNO¡@COUNT(*)¡@SUM(SAL)
--------- --------- ---------
¡@¡@¡@ 20¡@¡@¡@¡@ 5¡@¡@ 10875
¡@¡@¡@ 30¡@¡@¡@¡@ 6¡@¡@¡@9400


67.ORDER BY
¥Î©ó¹ï¬d¸ß¨ìªºµ²ªG¶i¦æ±Æ§Ç¿é¥X
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;

¡@ DEPTNO ENAME¡@¡@¡@¡@¡@¡@SAL
--------- ---------- ---------
¡@¡@¡@ 10 KING¡@¡@¡@¡@¡@¡@5000
¡@¡@¡@ 10 CLARK¡@¡@¡@¡@¡@ 2450
¡@¡@¡@ 10 MILLER¡@¡@¡@¡@¡@1300
¡@¡@¡@ 20 SCOTT¡@¡@¡@¡@¡@ 3000
¡@¡@¡@ 20 FORD¡@¡@¡@¡@¡@¡@3000
¡@¡@¡@ 20 JONES¡@¡@¡@¡@¡@ 2975
¡@¡@¡@ 20 ADAMS¡@¡@¡@¡@¡@ 1100
¡@¡@¡@ 20 SMITH¡@¡@¡@¡@¡@¡@800
¡@¡@¡@ 30 BLAKE¡@¡@¡@¡@¡@ 2850
¡@¡@¡@ 30 ALLEN¡@¡@¡@¡@¡@ 1600
¡@¡@¡@ 30 TURNER¡@¡@¡@¡@¡@1500
¡@¡@¡@ 30 WARD¡@¡@¡@¡@¡@¡@1250
¡@¡@¡@ 30 MARTIN¡@¡@¡@¡@¡@1250
¡@¡@¡@ 30 JAMES¡@¡@¡@¡@¡@¡@950

Âà¦Ü©ó:
http://big5.webasp.net/article/21/20246.htm

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD