|
马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。oracle|函数
PL/SQL单行函数和组函数详解
函数是一种有零个或多个参数而且有一个前往值的程序。在SQL中Oracle内建了一系列函数,这些函数都可被称为SQL或PL/SQL语句,函数次要分为两年夜类:
单行函数
组函数
本文将会商怎样使用单行函数和利用划定规矩。
SQL中的单行函数
SQL和PL/SQL中自带良多范例的函数,有字符、数字、日期、转换、和夹杂型等多种函数用于处置单行数据,因而这些都可被统称为单行函数。这些函数都可用于SELECT,WHERE、ORDERBY等子句中,比方上面的例子中就包括了TO_CHAR,UPPER,SOUNDEX等单行函数。
SELECTename,TO_CHAR(hiredate,day,DD-Mon-YYYY)FROMempWhereUPPER(ename)LikeAL%ORDERBYSOUNDEX(ename)
单行函数也能够在其他语句中利用,如update的SET子句,INSERT的VALUES子句,DELET的WHERE子句,认证测验出格注重在SELECT语句中利用这些函数,以是我们的注重力也会合在SELECT语句中。
NULL和单行函数
在怎样了解NULL上入手下手是很坚苦的,就算是一个很有履历的人仍然对此感应狐疑。NULL值暗示一个未知数据大概一个空值,算术操纵符的任何一个操纵数为NULL值,了局均为提个NULL值,这个划定规矩也合适良多函数,只要CONCAT,DECODE,DUMP,NVL,REPLACE在挪用了NULL参数时可以前往非NULL值。在这些中NVL函数时最主要的,由于他能间接处置NULL值,NVL有两个参数:NVL(x1,x2),x1和x2都式表达式,当x1为null时前往X2,不然前往x1。
上面我们看看emp数据表它包括了薪水、奖金两项,必要盘算总的抵偿
columnnameemp_idsalarybonuskeytypepknulls/uniquenn,unnfktabledatatypenumbernumbernumberlength11.211.2
不是复杂的将薪水和奖金加起来就能够了,假如某一行是null值那末了局就将是null,好比上面的例子:
updateempsetsalary=(salary+bonus)*1.1
这个语句中,雇员的人为和奖金都将更新为一个新的值,可是假如没有奖金,即salary+null,那末就会得堕落误的结论,这个时分就要利用nvl函数来扫除null值的影响。
以是准确的语句是:
updateempsetsalary=(salary+nvl(bonus,0)*1.1
单行字符串函数
单行字符串函数用于操纵字符串数据,他们年夜多半有一个或多个参数,个中尽年夜多半前往字符串
ASCII()
c1是一字符串,前往c1第一个字母的ASCII码,他的逆函数是CHR()
SELECTASCII(A)BIG_A,ASCII(z)BIG_zFROMempBIG_ABIG_z65122
CHR(<i>)[NCHAR_CS]
i是一个数字,函数前往十进制暗示的字符
selectCHR(65),CHR(122),CHR(223)FROMempCHR65CHR122CHR223AzB
CONCAT(,)
c1,c2均为字符串,函数将c2毗连到c1的前面,假如c1为null,将前往c2.假如c2为null,则前往c1,假如c1、c2都为null,则前往null。他和操纵符||前往的了局不异
selectconcat(slobo,Svoboda)usernamefromdualusernamesloboSyoboda
INITCAP()
c1为一字符串。函数将每一个单词的第一个字母年夜写别的字母小写前往。单词由空格,把持字符,标点标记限定。
selectINITCAP(veni,vedi,vici)CeasarfromdualCeasarVeni,Vedi,Vici
INSTR(,[,<i>[,]])
c1,c2均为字符串,i,j为整数。函数前往c2在c1中第j次呈现的地位,搜刮从c1的第i个字符入手下手。当没有发明必要的字符时前往0,假如i为正数,那末搜刮将从右到左举行,可是地位的盘算仍是从左到右,i和j的缺省值为1.
selectINSTR(Mississippi,i,3,3)fromdualINSTR(MISSISSIPPI,I,3,3)11selectINSTR(Mississippi,i,-2,3)fromdualINSTR(MISSISSIPPI,I,3,3)2
INSTRB(,[,i[,j])
与INSTR()函数一样,只是他前往的是字节,关于单字节INSTRB()即是INSTR()
LENGTH()
c1为字符串,前往c1的长度,假如c1为null,那末将前往null值。
selectLENGTH(IpsoFacto)ergofromdualergo10
LENGTHb()
与LENGTH()一样,前往字节。
lower()
前往c的小写字符,常常呈现在where子串中
selectLOWER(colorname)fromitemdetailWHERELOWER(colorname)LIKE%white%COLORNAMEWinterwhite
LPAD(,<i>[,])
c1,c2均为字符串,i为整数。在c1的左边用c2字符串补足致长度i,可屡次反复,假如i小于c1的长度,那末只前往i那末长的c1字符,其他的将被截往。c2的缺省值为单空格,拜见RPAD。
selectLPAD(answer,7,)padded,answerunpaddedfromquestion;PADDEDUNPADDEDYesYesNONOMaybemaybe
LTRIM(,)
把c1中最右边的字符往失落,使其第一个字符不在c2中,假如没有c2,那末c1就不会改动。
selectLTRIM(Mississippi,Mis)fromdualLTRppi
RPAD(,<i>[,])
在c1的右边用c2字符串补足致长度i,可屡次反复,假如i小于c1的长度,那末只前往i那末长的c1字符,其他的将被截往。c2的缺省值为单空格,其他与LPAD类似
RTRIM(,)
把c1中最右侧的字符往失落,使其第后一个字符不在c2中,假如没有c2,那末c1就不会改动。
REPLACE(,[,])
c1,c2,c3都是字符串,函数用c3取代呈现在c1中的c2后前往。
selectREPLACE(uptown,up,down)fromdualREPLACEdowntown
STBSTR(,<i>[,])
c1为一字符串,i,j为整数,从c1的第i位入手下手前往长度为j的子字符串,假如j为空,则直到串的尾部。
selectSUBSTR(Message,1,4)fromdualSUBSMess
SUBSTRB(,<i>[,])
与SUBSTR大抵不异,只是I,J是以字节盘算。
SOUNDEX()
前往与c1发音类似的词
selectSOUNDEX(dawes)DawesSOUNDEX(daws)Daws,SOUNDEX(dawson)fromdualDawesDawsDawsonD200D200D250
TRANSLATE(,,)
将c1中与c2不异的字符以c3取代
selectTRANSLATE(fumble,uf,ar)testfromdualTEXTramble
TRIM([[]]fromc3)
将c3串中的第一个,最初一个,大概都删除。
selectTRIM(spacepadded)trimfromdualTRIMspacepadded
UPPER()
前往c1的年夜写,常呈现where子串中
selectnamefromdualwhereUPPER(name)LIKEKI%NAMEKING
单行数字函数
单行数字函数操纵数字数据,实行数学和算术运算。一切函数都无数字参数并前往数字值。一切三角函数的操纵数和值都是弧度而不是角度,oracle没有供应内建的弧度和角度的转换函数。
ABS()
前往n的相对值
ACOS()
反余玄函数,前往-1到1之间的数。n暗示弧度
selectACOS(-1)pi,ACOS(1)ZEROFROMdualPIZERO3.141592650
ASIN()
归正玄函数,前往-1到1,n暗示弧度
ATAN()
归正切函数,前往n的归正切值,n暗示弧度。
CEIL()
前往年夜于或即是n的最小整数。
COS()
前往n的余玄值,n为弧度
COSH()
前往n的双曲余玄值,n为数字。
selectCOSH(<1.4>)FROMdualCOSH(1.4)2.15089847
EXP()
前往e的n次幂,e=2.71828183.
FLOOR()
前往小于即是N的最年夜整数。
LN()
前往N的天然对数,N必需年夜于0
LOG(,)
前往以n1为底n2的对数
MOD()
前往n1除以n2的余数,
POWER(,)
前往n1的n2次方
ROUND(,)
前往舍进小数点右侧n2位的n1的值,n2的缺省值为0,这回将小数点最靠近的整数,假如n2为正数就舍进到小数点右边响应的位上,n2必需是整数。
selectROUND(12345,-2),ROUND(12345.54321,2)FROMdualROUND(12345,-2)ROUND(12345.54321,2)1230012345.54
SIGN()
假如n为正数,前往-1,假如n为负数,前往1,假如n=0前往0.
SIN()
前往n的正玄值,n为弧度。
SINH()
前往n的双曲正玄值,n为弧度。
SQRT()
前往n的平方根,n为弧度
TAN()
前往n的正切值,n为弧度
TANH()
前往n的双曲正切值,n为弧度
TRUNC(,)
前往截尾到n2位小数的n1的值,n2缺省设置为0,当n2为缺省设置时会将n1截尾为整数,假如n2为负值,就截尾在小数点右边响应的位上。
单行日期函数
单行日期函数操纵DATA数据范例,尽年夜多半都有DATA数据范例的参数,尽年夜多半前往的也是DATA数据范例的值。
ADD_MONTHS(,<i>)
前往日期d加上i个月后的了局。i可使恣意整数。假如i是一个小数,那末数据库将隐式的他转换成整数,将会截往小数点前面的部分。
LAST_DAY()
函数前往包括日期d的月份的最初一天
MONTHS_BETWEEN(,)
前往d1和d2之间月的数量,假如d1和d2的日的日期都不异,大概都使该月的最初一天,那末将前往一个整数,不然会前往的了局将包括一个分数。
NEW_TIME(,,)
d1是一个日期数据范例,事先区tz1中的日期和工夫是d时,前往时区tz2中的日期和工夫。tz1和tz2时字符串。
NEXT_DAY(,)
前往日期d后由dow给出的前提的第一天,dow利用以后会话中给出的言语指定了一周中的某一天,前往的工夫份量与d的工夫份量不异。
selectNEXT_DAY(01-Jan-2000,Monday)"1stMonday",NEXT_DAY(01-Nov-2004,Tuesday)+7"2ndTuesday")fromdual;1stMonday2ndTuesday03-Jan-200009-Nov-2004
ROUND([,])
将日期d依照fmt指定的格局舍进,fmt为字符串。
SYADATE
函数没有参数,前往以后日期和工夫。
TRUNC([,])
前往由fmt指定的单元的日期d.
单行转换函数
单行转换函数用于操纵多半据范例,在数据范例之间举行转换。
CHARTORWID()
c使一个字符串,函数将c转换为RWID数据范例。
SELECTtest_idfromtest_casewhererowid=CHARTORWID(AAAA0SAACAAAALiAAA)
CONVERT(,[,])
c尾字符串,dset、sset是两个字符集,函数将字符串c由sset字符集转换为dset字符集,sset的缺省设置为数据库的字符集。
HEXTORAW()
x为16进制的字符串,函数将16进制的x转换为RAW数据范例。
RAWTOHEX()
x是RAW数据范例字符串,函数将RAW数据类转换为16进制的数据范例。
ROWIDTOCHAR()
函数将ROWID数据范例转换为CHAR数据范例。
TO_CHAR([[,)
x是一个data或number数据范例,函数将x转换成fmt指定格局的char数据范例,假如x为日期nlsparm=NLS_DATE_LANGUAGE把持前往的月份和日份所利用的言语。假如x为数字nlsparm=NLS_NUMERIC_CHARACTERS用来指定小数位和千分位的分开符,和泉币标记。
NLS_NUMERIC_CHARACTERS="dg",NLS_CURRENCY="string"
TO_DATE([,[,)
c暗示字符串,fmt暗示一种特别格局的字符串。前往依照fmt格局显现的c,nlsparm暗示利用的言语。函数将字符串c转换成date数据范例。
TO_MULTI_BYTE()
c暗示一个字符串,函数将c的担子截字符转换成多字节字符。
TO_NUMBER([,[,)
c暗示字符串,fmt暗示一个特别格局的字符串,函数前往值依照fmt指定的格局显现。nlsparm暗示言语,函数将前往c代表的数字。
TO_SINGLE_BYTE()
将字符串c中很多字节字符转化成等价的单字节字符。该函数仅当数据库字符集同时包括单字节和多字节字符时才利用
别的单行函数
BFILENAME(
,)
dir是一个directory范例的工具,file为一文件名。函数前往一个空的BFILE地位值唆使符,函数用于初始化BFILE变量大概是BFILE列。
DECODE(,,[,,,[])
x是一个表达式,m1是一个婚配表达式,x与m1对照,假如m1即是x,那末前往r1,不然,x与m2对照,顺次类推m3,m4,m5....直到有前往了局。
DUMP(,[,[,[,]]])
x是一个表达式或字符,fmt暗示8进制、10进制、16进制、或则单字符。函数前往包括了有关x的外部暗示信息的VARCHAR2范例的值。假如指定了n1,n2那末从n1入手下手的长度为n2的字节将被前往。
EMPTY_BLOB()
该函数没有参数,函数前往一个空的BLOB地位唆使符。函数用于初始化一个BLOB变量或BLOB列。
EMPTY_CLOB()
该函数没有参数,函数前往一个空的CLOB地位唆使符。函数用于初始化一个CLOB变量或CLOB列。
GREATEST()
exp_list是一列表达式,前往个中最年夜的表达式,每一个表达式都被隐含的转换第一个表达式的数据范例,假如第一个表达式是字符串数据范例中的任何一个,那末前往的了局是varchar2数据范例,同时利用的对照长短添补空格范例的对照。
LEAST()
exp_list是一列表达式,前往个中最小的表达式,每一个表达式都被隐含的转换第一个表达式的数据范例,假如第一个表达式是字符串数据范例中的任何一个,将前往的了局是varchar2数据范例,同时利用的对照长短添补空格范例的对照。
UID
该函数没有参数,前往独一标示以后数据库用户的整数。
USER
前往以后用户的用户名
USERENV()
基于opt前往包括以后会话信息。opt的可选值为:
ISDBA 会话中SYSDBA角色呼应,前往TRUE
SESSIONID 前往审计会话标示符
ENTRYID 前往可用的审计项标示符
INSTANCE 在会话毗连后,前往实例标示符。该值只用于运转Parallel服务器而且有多个实例的情形下利用。
LANGUAGE 前往言语、地区、数据库设置的字符集。
LANG 前往言语称号的ISO缩写。
TERMINAL 为以后会话利用的终端或盘算机前往操纵体系的标示符。
VSIZE()
x是一个表达式。前往x外部暗示的字节数。
SQL中的组函数
组函数也叫汇合函数,前往基于多个行的单一了局,行的正确数目没法断定,除非查询被实行而且一切的了局都被包括在内。与单行函数分歧的是,在剖析时一切的行都是已知的。因为这类不同使组函数与单行函数有在请求和举动上有巨大的差别.
组(多行)函数
与单行函数比拟,oracle供应了丰厚的基于组的,多行的函数。这些函数能够在select或select的having子句中利用,当用于select子串经常常都和GROUPBY一同利用。
AVG([{DISYINCT|ALL}])
前往数值的均匀值。缺省设置为ALL.
SELECTAVG(sal),AVG(ALLsal),AVG(DISTINCTsal)FROMscott.empAVG(SAL)AVG(ALLSAL)AVG(DISTINCTSAL)1877.941181877.941181916.071413
COUNT({*|DISTINCT|ALL})
前往查询中行的数量,缺省设置是ALL,*暗示前往一切的行。
MAX([{DISTINCT|ALL}])
前往选择列表项目标最年夜值,假如x是字符串数据范例,他前往一个VARCHAR2数据范例,假如X是一个DATA数据范例,前往一个日期,假如X是numeric数据范例,前往一个数字。注重distinct和all不起感化,应为最年夜值与这两种设置是不异的。
MIN([{DISTINCT|ALL}])
前往选择列表项目标最小值。
STDDEV([{DISTINCT|ALL}])
前往选者的列表项目标尺度差,所谓尺度差是方差的平方根。
SUM([{DISTINCT|ALL}])
前往选择列表项目标数值的总和。
VARIANCE([{DISTINCT|ALL}])
前往选择列表项目标统计方差。
用GROUPBY给数据分组
正如标题表示的那样组函数就是操纵那些已分好组的数据,我们告知数据库用GROUPBY如何给数据分组大概分类,当我们在SELECT语句的SELECT子句中利用组函数时,我们必需把为分组或十分数列安排在GROUPBY子句中,假如没有效groupby举行专门处置,那末缺省的分类是将全部了局设为一类。
selectstat,counter(*)zip_countfromzip_codesGROUPBYstate;STZIP_COUNT-----------AK360AL1212AR1309AZ768CA3982
在这个例子中,我们用state字段分类;假如我们要将了局依照zip_codes排序,能够用ORDERBY语句,ORDERBY子句可使用列或组函数。
selectstat,counter(*)zip_countfromzip_codesGROUPBYstateORDERBYCOUNT(*)DESC;STCOUNT(*)----------NY4312PA4297TX4123CA3982
用HAVING子句限定分组数据
如今你已晓得了在查询的SELECT语句和ORDERBY子句中利用主函数,组函数只能用于两个子串中,组函数不克不及用于WHERE子串中,比方上面的查询是毛病的:
毛病SELECTsales_clerk,SUN(sale_amount)FROMgross_salesWHEREsales_dept=OUTSIDEANDSUM(sale_amount)>10000GROUPBYsales_clerk
这个语句中数据库不晓得SUM()是甚么,当我们必要唆使数据库对行分组,然后限定分组后的行的输入时,准确的办法是利用HAVING语句:
SELECTsales_clerk,SUN(sale_amount)FROMgross_salesWHEREsales_dept=OUTSIDEGROUPBYsales_clerkHAVINGSUM(sale_amount)>10000;
嵌套函数
函数能够嵌套。一个函数的输入能够是另外一个函数的输出。操纵数有一个可承继的实行历程。但函数的优先权只是基于地位,函数遵守由内到外,由左到右的准绳。嵌套手艺一样平常用于象DECODE如许的能被用于逻辑判别语句IF....THEN...ELSE的函数。
嵌套函数能够包含在组函数中嵌套单行函数,大概组函数嵌套进单行函数或组函数中。好比上面的例子:
SELECTdeptno,GREATEST(COUNT(DISTINCTjob),COUNT(DISTINCTmgr)cnt,COUNT(DISTINCTjob)jobs,COUNT(DISTINCTmgr)mgrsFROMempGROUPBYdeptno;DEPTNOCNTJOBSMGRS-----------------104422043430332
MySQL数据库归MySQLAB公司所有,但是这个软件是开源的,有一个MySQL学习教程可以免费下载。稍俱常识的新入门者都可以轻松实现在一个常见硬件上安装和配置MySQL。 |
|