sql>selectvsize(user),userfromdual;
vsize(user)user
-----------------------------------------
6system
60.avg(distinct|all)
all表示对所有的值求平均值,distinct只对不同的值求平均值
sqlwks>createtabletable3(xmvarchar(8),salnumber(7,2));
语句已处理。
sqlwks>insertintotable3values(‘gao‘,1111.11);
sqlwks>insertintotable3values(‘gao‘,1111.11);
sqlwks>insertintotable3values(‘zhu‘,5555.55);
sqlwks> mit;
sql>selectavg(distinctsal)fromgao.table3;
avg(distinctsal)
----------------
3333.33
sql>selectavg(allsal)fromgao.table3;
avg(allsal)
-----------
2592.59
61.max(distinct|all)
求最大值,all表示对所有的值求最大值,distinct表示对不同的值求最大值,相同的只取一次
sql>selectmax(distinctsal)fromscott.emp;
max(distinctsal)
----------------
5000
62.min(distinct|all)
求最小值,all表示对所有的值求最小值,distinct表示对不同的值求最小值,相同的只取一次
sql>selectmin(allsal)fromgao.table3;
min(allsal)
-----------
1111.11
63.stddev(distinct|all)
求标准差,all表示对所有的值求标准差,distinct表示只对不同的值求标准差
sql>selectstddev(sal)fromscott.emp;
stddev(sal)
-----------
1182.5032
sql>selectstddev(distinctsal)fromscott.emp;
stddev(distinctsal)
-------------------
1229.951
64.variance(distinct|all)
求协方差
sql>selectvariance(sal)fromscott.emp;
variance(sal)
-------------
1398313.9
65.groupby
主要用来对一组数进行统计
sql>selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptno;
deptnocount(*)sum(sal)
---------------------------
1038750
20510875
3069400
66.having
对分组统计再加限制条件
sql>selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptnohavingcount(*)>=5;
deptnocount(*)sum(sal)
---------------------------
20510875
3069400
sql>selectdeptno,count(*),sum(sal)fromscott.emphavingcount(*)>=5groupbydeptno;
deptnocount(*)sum(sal)
---------------------------
20510875
3069400
67.orderby
用于对查询到的结果进行排序输出
sql>selectdeptno,ename,salfromscott.emporderbydeptno,saldesc;
deptnoenamesal
----------------------------
10king5000
10clark2450
10miller1300
20scott3000
20ford3000
20jones2975
20adams1100
20smith800
30blake2850
30allen1600
30turner1500
30ward1250
30martin1250
30james950
oracle最常用功能函数经典汇总
来源：chinaitlab收集整理
2004-6-1410:58:00
*sqlgroupfunction
*
s(numcanbeacolumnorex
pression)
(nullvaluesareign
*
ored,defaultbetweendistin
ctandallisall)
*
avg([distinctorall]num)
--averagevalue
count(distinctorall]num)
--numberofvalues
max([distinctorall
]num)--maximumvalue
max([distinctorall]num)
--minimumvalue
stddev([distinctor
all]num)--standarddevi
ation
sum([distinctorall
]num)--sumofvalues
variance([distincto
rall]num)--varianceofv
alues
*
*miscellaneaousfunctions:
*
*
decode(expr,srch1,
return1[,srch2,return2...]
,default]
--ifnosearchmatchest
heexpressionthenthedefaultisreturned,
--otherwise,
thefirstsearchthatmatch
eswillcause
--thecorres
pondingreturnvaluetober
eturned
dump(column_name[,fmt[,start_p
os[,length]]])
--returnsan
column
internaloracleformat,used
forgettinginfoabouta
--formatoptions:8=oc
tal,10=decimel,16=hex,17=characters
--returntype
codes:1=varchar2,2=n
umber,8=long,12=date,
--23=raw,
24=longraw,69=rowid,
96=char,106=mlslabel
greatest(expr[,expr2[,expr3...]]
--returnsthelargestval
ueofallexpressions
least(expr[,expr2[,expr3...]]
--returnsthe
smallestvalueofallexpre
ssions
nvl(expr1,expr2
--ifexpr1isnotnull,i
tisreturned,otherwiseexpr2isreturned
sqlcode
--returnssqlerrorcode
query,
oflasterror.cannotbeuseddirectlyin
--valuemust
besettolocalvariablefir
st
sqlerrm
--returnssql
inquery,
errormessageoflasterror
.cannotbeuseddirectly
--valuemustbesettolo
calvariablefirst
uid
--returnstheuseridof
theuseryouareloggedonas
--usefulins
electinginformationfromlo
wlevelsystables
user
--returnsthe
usernameoftheuseryoua
reloggedonas
userenv(‘option‘)
--returnsinf
ormationabouttheuseryou
areloggedonas
--options:e
ntryid,sessionid,terminal,
language,label,osdba
--(
alloptionsnotavailablein
alloracleversions)
vsize(expr)
--returnsthenumberofb
ytesusedbytheexpression
--usefulins
electinginformationaboutt
ablespacerequirements
*
*sqldatefunctions(dtreprese
*
ntsoracledateandtime)
*(functionsreturn
*
anoracledateunlessotherw
isespecified)
*
add_months(dt,num)
--addsnummonthsto
dt(numcanbenegative)
last_day(dt)
--lastdayofmonthin
monthcontainingdt
months_between(dt1,dt2)--retu
dt2
rnsfractionalvalueofmonthsbetweendt1,
new_time(dt,tz1,tz
zone2
2)--dt=dateintimezo
ne1,returnsdateintime
next_day(dt,str)--date
etc..)
offirst(str)afterdt(str=‘monday‘,
sysdate--presentsystemdate
round(dt[,fmt]--roun
dsdtasspecifiedbyformatfmt
trunc(dt[,fmt]
--truncatesdtasspe
cifiedbyformatfmt
*
*numberfunctions:
*
*
abs(num)--absolute
valueofnum
ceil(num)--smallestinteger>or=num
cos(num)--cosine(n
um),numinradians
cosh(num)
--hyperboliccosine(num)
exp(num)
--eraisedtothenumpowe
r
floor(num)--largest
integer<or=num
ln(num)--natural
logarithmofnum
log(num2,num1)--logarith
mbasenum2ofnum1
mod(num2,num1)--remainde
rofnum2/num1
power(num2,num1)
--num2raisedtothenum1
power
round(num1[,num2]--num1rou
ndedtonum2decimelplaces(default0)
sign(num)--signof
num*1,0ifnum=0
sin(num)
--sin(num),numinradians
sinh(num)--hyperbolicsine(num)
sqrt(num)--squarerootofnum
tan(num)--tangent(
num),numinradians
tanh(num)
--hyperbolictangent(num)
trunc(num1[,num2]--truncate
num1tonum2decimelplaces(default0)
*
*stringfunctions,
*
stringresult:
*
(num)--ascii
characterfornum
chr(num)
--asciicharacterforn
um
concat(str1,str2)--str1
concatenatedwithstr2(sameasstr1||str2)
initcap(str)
--capitalizefirstlett
erofeachwordinstr
lower(str)--strw
ithalllettersinlowercase
lpad(str1,num[,str2])--left
spaces)
padstr1tolengthnumwithstr2(default
ltrim(str[,set])
--removesetfromleft
sideofstr(defaultspaces)
nls_initcap(str[,nl
s_val])--sameasinitcapf
ordifferentlanguages
nls_lower(str[,nls_
val])--sameaslowerfor
differentlanguages
replace(str1,str2[,str3])--r
eplacesstr2withstr3instr1
--
deletesstr2fromstr1ifstr3isomitted
rpad(str1,num[,str
(defaultspaces)
2])--rightpadstr1to
lengthnumwithstr2
rtrim(str[,set])
spaces)
--removesetfrom
rightsideofstr(default
soundex(str)
--phicrepresen
tationofstr
substr(str,num2[,n
um1])--substringofstr,
startingwithnum2,
--
omitted)
num1characters(toendofstrifnum1is
substrb(str,num2[,
bytes
num1])--sameassubstrbu
tnum1,num2expressedin
translate(str,set1,
set2)--replacesset1in
strwithset2
--
truncated
ifset2islongerthanset1,itwillbe
upper(str)
--strwithalllett
ersinuppercase
*
*stringfunctions,
*
numericresult:
*
ascii(str)
--asciivalueofstr
instr(str1,str2[,num1[,num2]]
)--positionofnum2thoccurrenceof
--str2instr1,startingatnum1
--(num1,num2defaultto1)
instrb(str1,str2[,num1[num2]]
)--sameasinstr,bytevaluesfornum1,num2
length(str)
--numberof
charactersinstr
lengthb(str)
--numberofbytesinstr
nlssort(str[,nls_val])
--nls_valbytevalueofstr
*
*sqlconversionfunctions
*
*
chartorowid(str)
--convertsstrtorowid
convert(str,chr_set2[,chr_set1
])--convertsstrtochr_set2
characterset
--chr_set1
defaultisthedatbase
hextoraw(str)
--convertshexstringva
luetointernalrawvalues
rawtohex(raw_val)--convert
srawhexvaluetohexstringvalue
rowidtochar(rowid)
--convertsrowidto18ch
aracterstringformat
to_char(expr[,fmt])
fmt
--convertsexpr(dateorn
umber)toformatspecifiedby
to_date(str[,fmt])
--convertsstringtodat
e
to_multi_byte(str)--convert
ssinglebytestringtomultibytestring
to_number(str[,fmt])--convert
sstrtoanumberformattedbyfmt
to_single_byte(str)
--convertsmultibytest
ringtosinglebytestring
*
*sqldateformats
*
*
bc,b.c.bcindicator
ad,a.d.adindicator
cc,scccent
urycode(sccincludesspace
or-sign)
yyyy,syyyy4digityear(sy
yyyincludesspaceor-sign)
iyyy4digitisoyear
y,yyy4digityearwith ma
yyy,yy,orylast3,2,or1
digitofyear
year,syearyearspelledout
(syearincludesspaceor-sign)
rrlast2digitsof
yearinpriorornextcentury
qquarteroryear,1to4
mmmonth-from01to12
monthmonthspelledout
monmonth3letterabbreviation
rmromannumeralformonth
eekofyear,1to53
iwisoweekofyear
,1to52or1to53
wweekofmonth,1
to5(week1begins1stdayofthemonth)
ddayofweek,1to7
dddayofmonth,1to31
ddddayofyear,1to366
daydayofweekspel
ledout,ninecharactersrightpadded
dydayabbreviation
j#of
dayssincejan1,4712bc
hh,hh12hourofday,1to12
hh24hourofday,0to23
miminuteofhour,0to59
sssecondofminute,0to59
sssssseco
ndspastmidnight,0to8639
9
am,a.m.amindicator
pm,p.m.pmindicator
anypuctuationpunc
tuationbetweenformatitems
,asin‘dd/mm/yy‘
anytexttextbetweenformatitems
thconv
erts1to‘1st‘,2to‘2nd‘,
andsoon
spconverts1to‘o
ne‘,2to‘two‘,andsoon
spthconverts1to‘f
irst‘,2to‘second‘,andsoon
fxfill
exact:usesexactpattern
matching
fmfillmode:tog
glessuppressionofblanksinoutput