一、单行函数(single row function):单行函数同时只能对一行进行操作,并且对输入的每一行返回一行输出结果;
二、聚合函数(aggregate function):聚合函数可以同时对多行进行操作,并返回一行输出结果;
三、使用单行函数:字符函数,数字函数,转换函数,日期函数,正则表达式函数;
1、字符函数:
⑴ASCII(x),返回x的ASCII码;
⑵CHR(x),返回ASCII码x对应的字符;
⑶CONCAT(x,y),将字符串y增加到x后面,并将结果返回;
⑷INITCAP(x),将x的首字母大写;
⑸INSTR(x,find_string[,start][,occurrence]),在x中从sart位置,查找第occurrence次出现的find_string的位置;
⑹LENGTH(x),返回字符串x中的字符数;
⑺LOWER(x),将x中的所有字符小写;
⑻LPAD(x,width[,pad_string]),在字符串x左边补齐空格,或者pad_string,得到总长为width的字符串;
⑼LTRIM(x[,trim_string]),从字符串x的左边截去空格或者trim_string;
⑽NANVL(x,value),如果x匹配NAN这个特殊的字符,就返回value,否则返回x;
⑾NVL(x,value),如果x为空,返回value,否则返回value;
⑿NVL2(x,vlaue1,value2),如果x为非空,返回value1。否则返回value2;
⒀REPLACE(x,search_string,replace_string),替换;
⒁RPAD(x,width[,pad_string]),参照LPAD,右边;
⒂RTRIM(x[,trim_string]),参照LTRIM,右边;
⒃SOUNDEX(x),返回一个包含x的发音的字符串,该函数用于对英文发音不同但却比较接近的单词进行比较;
⒄SUBSTR(x,start[,length]),从x的start开始,返回一个长度为length的子字符串;
⒅TRIM([trim_char FROM ]x),从x的左边或者右边同时截去一些字符串,可以用trim_char来指定要截去的字符;如果不指定,就为空;
⒆UPPER(x);将X中的字符全部转换为大写;
⑴
SQL> select ascii('a') , ascii('A') , ascii('z'), ascii('Z') ,ascii(0), ascii(9), chr(97),chr(65),chr(122),chr(90),chr(48),chr(57) from dual;
ASCII('A') ASCII('A') ASCII('Z') ASCII('Z') ASCII(0) ASCII(9) C C C C C C
---------- ---------- ---------- ---------- ---------- ---------- - - - - - -
97 65 122 90 48 57 a A z Z 0 9
⑵
SQL> select concat(first_name,last_name) from customers;
--CONCAT函数和||操作符功能完全相同;
CONCAT(FIRST_NAME,LA
--------------------
JohnBrown
CynthiaGreen
SteveWhite
GailBlack
DoreenBlue
⑶
SQL> SELECT product_id , INITCAP(description) FROM products where product_id <4;
PRODUCT_ID INITCAP(DESCRIPTION)
---------- --------------------------------------------------
1 A Description Of Modern Science
2 Introduction To Chemistry
3 A Star Explodes
⑷
SQL> select name,instr(name,'Science') from products where product_id=1;
NAME INSTR(NAME,'SCIENCE')
------------------------------ ---------------------
Modern Science 8
⑸
SQL> select name,instr(name,'e',1,2) from products where product_id=1;
NAME INSTR(NAME,'E',1,2)
------------------------------ -------------------
Modern Science 11
⑹
SQL> select name,length(name) from products where product_id=1;
NAME LENGTH(NAME)
------------------------------ ------------
Modern Science 14
⑺
SQL> SELECT RPAD(name,15,'*') ,LPAD(price,8,'+') from products where product_id<3;
RPAD(NAME,15,'*') LPAD(PRICE,8,'+')
------------------------------------------------------------ ----------------------------
Modern Science* +++19.95
Chemistry****** ++++++30
⑻
SQL> SELECT
2 LTRIM( ' Hello Gail '),
3 RTRIM(' HI , Doreem!abcabcabc' , 'abc'),
4 TRIM('o' FROM ' ooooHey , Button! ooooo')
5 FROM DUAL;
LTRIM('HELLOG RTRIM('HI,DOREEM!ABCABCABC' TRIM('O'FROM'OOOOHE
------------- --------------------------- -------------------
Hello Gail HI , Doreem! ooooHey , Button!
SQL> SELECT
2 LTRIM( ' Hello Gail '),
3 RTRIM(' HI , Doreem!abcabc' , 'abc'),
4 TRIM('o' FROM 'ooooHey , Button! ooooo')
5 FROM DUAL;
LTRIM('HELLOG RTRIM('HI,DOREEM!ABCABCABC' TRIM('O'FROM'O
------------- --------------------------- --------------
Hello Gail HI , Doreem! Hey , Button!
⑼
SQL> SELECT customer_id , NVL(phone,'Unkown Phone Number') ,
2 NVL2(phone,'Known','Unknown') From customers;
3 --NVL2(phone,'Known','Unknown'):如果phone为非空,返回Known,否则返回Unkonwn;
CUSTOMER_ID NVL(PHONE,'UNKOWNPH NVL2(PH
----------- ------------------- -------
1 800-555-1211 Known
2 800-555-1212 Known
3 800-555-1213 Known
4 800-555-1214 Known
5 Unkown Phone Number Unknown
⑽
SQL> SELECT REPLACE(name,'Science','Physics') FROM products where product_id=1;
REPLACE(NAME,'SCIENCE','PHYSICS')
----------------------------------------------------------------------------------
Modern Physics
⑾
SQL> select last_name from customers where SOUNDEX(last_name)=SOUNDEX('whyte');
LAST_NAME
----------
White
⑿
SQL> select substr(name ,2,7) from products where product_id < 4;
SUBSTR(NAME,2,7)
----------------------------
odern S
hemistr
upernov
⒀
在函数中使用表达式:
SQL> select substr('Mary had a little lamb' , 12 ,6) from dual;
SUBSTR
------
little
⒁
SQL> select name , upper(substr(name , 2 , 8)) from products where product_id < 4;
NAME UPPER(SUBSTR(NAME,2,8))
------------------------------ --------------------------------
Modern Science ODERN SC
Chemistry HEMISTRY
Supernova UPERNOVA
2、数字函数:
ABS(x)、返回x的绝对值;
ACOS(x);返回x的余弦;
ASIN(x):返回x的饭余弦;
ATAN(x);返回x的反正切;
ATAN2(x,y);返回x和y的反正切;
BITAND(x,y):返回对x和y进行与操作的结果;
COS(x):返回x的余弦,其中x是弧度;
COSH(x):返回x的双曲余弦函数;
CEIL(x):返回大于或等于x的最小整数;
EXP(x);返回e的x次幂;
FLOOR(x):返回小于或等于x的最大整数;
LOG(x,y):返回一x为底y的对数;
LN(x):返回x的自然对数;
MOD(x,y):返回x除以y的余数;
POWER(x,y):反回x的y次幂;
ROUND(x[ ,y]):返回对x取整的结果,y可选,说明对第几位小数取整,如果没有指定y,则对x在0位小数取整,如果y是负数,则对x小数点左边的第|y|位处取整;round(5.75) = 6;round(5.75,1)=5.8;round(5.75,-1)=10;
SIGN(x):x为负数,返回-1,x为正数返回1,x为0返回0;
SIN(x):x的正弦值;
SINH(x):x的双曲正弦函数;
SQRT(x):返回x的平方根;
TAN(x):返回x的正切;
TANH(x):返回x的双曲正切函数;
TRUNC(x[ ,y]):对x截断的结果:y可选,说明对第几位小数截断,说明对第几位小数取整,如果没有指定y,则对x在0位小数截断,如果y是负数,则对x小数点左边的第|y|位处截断;TRUNC(5.75)=5;TRUNC(5.75,1)=5.7,TRUNC(5.75,-1)=0;
3、转换函数:
ASCIISTR(x):将x转换为一个ASCII字符串,其中x可以是有字符集中的任意字符组成的字符串;
BIN_TO_NUM(x):将二进制数字x转换为number类型;
CAST(x AS TYPE):将x转换为TYPE所指定的兼容数据类型;
CHARTOROWID(x):将x转换为ROWID类型;
COMPOSE(x):将x转换为Unicode编码的字符串,字符串使用与x完全相同的字符集;
CONVERT(x,source_data_set,dest_data_set):将x从source_data_set转换为dest_data_set;
DECODE(x,serch,result,default):将x与search中的值进行比较,如果想的,返回result,否则返回default;
DECOMPOSE(x):先对x进行分解,在将其转化为一个Unicode字符串,字符串使用与x完全相同的字符集;
HEXTORAW(x):将包含十六进制的字符x转化为一个二进制数字(RAW),这个函数返回RAW类型的数字;
NUMTODSINTERVAL(x):将数字x转换为一个INTERVAL DAY TO SECOND类型;
NUMTOYMINTERVAL(x):将数字转换为一个INTERVAL YEAR TO MONTH类型;
RAWTOHEX(x):将二进制数字(RAW)x转换为一个VARCHAR2类型的字符串,值为等价的十六进制数字;
RAWRONHEX(x):将二进制数字(RAW)x转换为一个NVARCHAR2类型的字符串,值为等价的十六进制数字;NVARCHAR2类型用于以国家字符集格式存储字符串;
ROWIDTOCHAR(x):将ROWID x转换为一个VARCHAR2字符串;
ROWIDTONCHAR2(x):将ROWID x转换为一个NVARCHAR2字符串;
TO_BINARY_DOUBLE(x):将x转换为一个BINARY_DOUBLE类型;
TO_BINARY_FLOAT(x):
TO_BLOB(x):将x转换为一个二进制大对象数据;
TO_CHAR(x[,format]):将x转换为一个VARCHAR2字符串,可以指定format格式;
TO_CLOB(x):将x转换为字符大对象;
TO_DATE(x[,format]):将x转换为一个DATE类型;
TO_DSINTERVAL(x):将x转换为一个INTERVAL DAY TO SECOND类型;
TO_MULTI_BYTE(x):将x中的单字节字符转化为对应的多字节字符,返回类型与x相同;
TO_NCHAR(x):将数据库字符集中的x转换为一个NVARCHAR2字符串;
TO_NCLOB(x):将x转换为NCLOB类型,NCLOB用于保存大量的国家语言字符数据;
TO_NUMBER(x[,format]):将x转换为NUMBER类型;
TO_SINGLE_BYTE(x):将x中多字节字符转化为对应的单字节字符,返回类型与x相同;
TO_TIMESTAMP(x):将字符串c转换为TIMESTAMP类型;
TO_TIMESTAMP_TZ(x):将字符串c转换为TIMESTAMP WITH TIME ZONE类型;
TO_YMINTERVAL(x):将字符串c转换为INTERVAL YEAR TO MONTH类型;
TRANSLATE(x,from_string,to_string):将x中所有的from_string转换为to_string;
UNISTR(x):将x中的字符转换为国家语言字符集(NCHAR);
二、聚合函数(aggregate function):聚合函数可以同时对多行进行操作,并返回一行输出结果;
三、使用单行函数:字符函数,数字函数,转换函数,日期函数,正则表达式函数;
1、字符函数:
⑴ASCII(x),返回x的ASCII码;
⑵CHR(x),返回ASCII码x对应的字符;
⑶CONCAT(x,y),将字符串y增加到x后面,并将结果返回;
⑷INITCAP(x),将x的首字母大写;
⑸INSTR(x,find_string[,start][,occurrence]),在x中从sart位置,查找第occurrence次出现的find_string的位置;
⑹LENGTH(x),返回字符串x中的字符数;
⑺LOWER(x),将x中的所有字符小写;
⑻LPAD(x,width[,pad_string]),在字符串x左边补齐空格,或者pad_string,得到总长为width的字符串;
⑼LTRIM(x[,trim_string]),从字符串x的左边截去空格或者trim_string;
⑽NANVL(x,value),如果x匹配NAN这个特殊的字符,就返回value,否则返回x;
⑾NVL(x,value),如果x为空,返回value,否则返回value;
⑿NVL2(x,vlaue1,value2),如果x为非空,返回value1。否则返回value2;
⒀REPLACE(x,search_string,replace_string),替换;
⒁RPAD(x,width[,pad_string]),参照LPAD,右边;
⒂RTRIM(x[,trim_string]),参照LTRIM,右边;
⒃SOUNDEX(x),返回一个包含x的发音的字符串,该函数用于对英文发音不同但却比较接近的单词进行比较;
⒄SUBSTR(x,start[,length]),从x的start开始,返回一个长度为length的子字符串;
⒅TRIM([trim_char FROM ]x),从x的左边或者右边同时截去一些字符串,可以用trim_char来指定要截去的字符;如果不指定,就为空;
⒆UPPER(x);将X中的字符全部转换为大写;
⑴
SQL> select ascii('a') , ascii('A') , ascii('z'), ascii('Z') ,ascii(0), ascii(9), chr(97),chr(65),chr(122),chr(90),chr(48),chr(57) from dual;
ASCII('A') ASCII('A') ASCII('Z') ASCII('Z') ASCII(0) ASCII(9) C C C C C C
---------- ---------- ---------- ---------- ---------- ---------- - - - - - -
97 65 122 90 48 57 a A z Z 0 9
⑵
SQL> select concat(first_name,last_name) from customers;
--CONCAT函数和||操作符功能完全相同;
CONCAT(FIRST_NAME,LA
--------------------
JohnBrown
CynthiaGreen
SteveWhite
GailBlack
DoreenBlue
⑶
SQL> SELECT product_id , INITCAP(description) FROM products where product_id <4;
PRODUCT_ID INITCAP(DESCRIPTION)
---------- --------------------------------------------------
1 A Description Of Modern Science
2 Introduction To Chemistry
3 A Star Explodes
⑷
SQL> select name,instr(name,'Science') from products where product_id=1;
NAME INSTR(NAME,'SCIENCE')
------------------------------ ---------------------
Modern Science 8
⑸
SQL> select name,instr(name,'e',1,2) from products where product_id=1;
NAME INSTR(NAME,'E',1,2)
------------------------------ -------------------
Modern Science 11
⑹
SQL> select name,length(name) from products where product_id=1;
NAME LENGTH(NAME)
------------------------------ ------------
Modern Science 14
⑺
SQL> SELECT RPAD(name,15,'*') ,LPAD(price,8,'+') from products where product_id<3;
RPAD(NAME,15,'*') LPAD(PRICE,8,'+')
------------------------------------------------------------ ----------------------------
Modern Science* +++19.95
Chemistry****** ++++++30
⑻
SQL> SELECT
2 LTRIM( ' Hello Gail '),
3 RTRIM(' HI , Doreem!abcabcabc' , 'abc'),
4 TRIM('o' FROM ' ooooHey , Button! ooooo')
5 FROM DUAL;
LTRIM('HELLOG RTRIM('HI,DOREEM!ABCABCABC' TRIM('O'FROM'OOOOHE
------------- --------------------------- -------------------
Hello Gail HI , Doreem! ooooHey , Button!
SQL> SELECT
2 LTRIM( ' Hello Gail '),
3 RTRIM(' HI , Doreem!abcabc' , 'abc'),
4 TRIM('o' FROM 'ooooHey , Button! ooooo')
5 FROM DUAL;
LTRIM('HELLOG RTRIM('HI,DOREEM!ABCABCABC' TRIM('O'FROM'O
------------- --------------------------- --------------
Hello Gail HI , Doreem! Hey , Button!
⑼
SQL> SELECT customer_id , NVL(phone,'Unkown Phone Number') ,
2 NVL2(phone,'Known','Unknown') From customers;
3 --NVL2(phone,'Known','Unknown'):如果phone为非空,返回Known,否则返回Unkonwn;
CUSTOMER_ID NVL(PHONE,'UNKOWNPH NVL2(PH
----------- ------------------- -------
1 800-555-1211 Known
2 800-555-1212 Known
3 800-555-1213 Known
4 800-555-1214 Known
5 Unkown Phone Number Unknown
⑽
SQL> SELECT REPLACE(name,'Science','Physics') FROM products where product_id=1;
REPLACE(NAME,'SCIENCE','PHYSICS')
----------------------------------------------------------------------------------
Modern Physics
⑾
SQL> select last_name from customers where SOUNDEX(last_name)=SOUNDEX('whyte');
LAST_NAME
----------
White
⑿
SQL> select substr(name ,2,7) from products where product_id < 4;
SUBSTR(NAME,2,7)
----------------------------
odern S
hemistr
upernov
⒀
在函数中使用表达式:
SQL> select substr('Mary had a little lamb' , 12 ,6) from dual;
SUBSTR
------
little
⒁
SQL> select name , upper(substr(name , 2 , 8)) from products where product_id < 4;
NAME UPPER(SUBSTR(NAME,2,8))
------------------------------ --------------------------------
Modern Science ODERN SC
Chemistry HEMISTRY
Supernova UPERNOVA
2、数字函数:
ABS(x)、返回x的绝对值;
ACOS(x);返回x的余弦;
ASIN(x):返回x的饭余弦;
ATAN(x);返回x的反正切;
ATAN2(x,y);返回x和y的反正切;
BITAND(x,y):返回对x和y进行与操作的结果;
COS(x):返回x的余弦,其中x是弧度;
COSH(x):返回x的双曲余弦函数;
CEIL(x):返回大于或等于x的最小整数;
EXP(x);返回e的x次幂;
FLOOR(x):返回小于或等于x的最大整数;
LOG(x,y):返回一x为底y的对数;
LN(x):返回x的自然对数;
MOD(x,y):返回x除以y的余数;
POWER(x,y):反回x的y次幂;
ROUND(x[ ,y]):返回对x取整的结果,y可选,说明对第几位小数取整,如果没有指定y,则对x在0位小数取整,如果y是负数,则对x小数点左边的第|y|位处取整;round(5.75) = 6;round(5.75,1)=5.8;round(5.75,-1)=10;
SIGN(x):x为负数,返回-1,x为正数返回1,x为0返回0;
SIN(x):x的正弦值;
SINH(x):x的双曲正弦函数;
SQRT(x):返回x的平方根;
TAN(x):返回x的正切;
TANH(x):返回x的双曲正切函数;
TRUNC(x[ ,y]):对x截断的结果:y可选,说明对第几位小数截断,说明对第几位小数取整,如果没有指定y,则对x在0位小数截断,如果y是负数,则对x小数点左边的第|y|位处截断;TRUNC(5.75)=5;TRUNC(5.75,1)=5.7,TRUNC(5.75,-1)=0;
3、转换函数:
ASCIISTR(x):将x转换为一个ASCII字符串,其中x可以是有字符集中的任意字符组成的字符串;
BIN_TO_NUM(x):将二进制数字x转换为number类型;
CAST(x AS TYPE):将x转换为TYPE所指定的兼容数据类型;
CHARTOROWID(x):将x转换为ROWID类型;
COMPOSE(x):将x转换为Unicode编码的字符串,字符串使用与x完全相同的字符集;
CONVERT(x,source_data_set,dest_data_set):将x从source_data_set转换为dest_data_set;
DECODE(x,serch,result,default):将x与search中的值进行比较,如果想的,返回result,否则返回default;
DECOMPOSE(x):先对x进行分解,在将其转化为一个Unicode字符串,字符串使用与x完全相同的字符集;
HEXTORAW(x):将包含十六进制的字符x转化为一个二进制数字(RAW),这个函数返回RAW类型的数字;
NUMTODSINTERVAL(x):将数字x转换为一个INTERVAL DAY TO SECOND类型;
NUMTOYMINTERVAL(x):将数字转换为一个INTERVAL YEAR TO MONTH类型;
RAWTOHEX(x):将二进制数字(RAW)x转换为一个VARCHAR2类型的字符串,值为等价的十六进制数字;
RAWRONHEX(x):将二进制数字(RAW)x转换为一个NVARCHAR2类型的字符串,值为等价的十六进制数字;NVARCHAR2类型用于以国家字符集格式存储字符串;
ROWIDTOCHAR(x):将ROWID x转换为一个VARCHAR2字符串;
ROWIDTONCHAR2(x):将ROWID x转换为一个NVARCHAR2字符串;
TO_BINARY_DOUBLE(x):将x转换为一个BINARY_DOUBLE类型;
TO_BINARY_FLOAT(x):
TO_BLOB(x):将x转换为一个二进制大对象数据;
TO_CHAR(x[,format]):将x转换为一个VARCHAR2字符串,可以指定format格式;
TO_CLOB(x):将x转换为字符大对象;
TO_DATE(x[,format]):将x转换为一个DATE类型;
TO_DSINTERVAL(x):将x转换为一个INTERVAL DAY TO SECOND类型;
TO_MULTI_BYTE(x):将x中的单字节字符转化为对应的多字节字符,返回类型与x相同;
TO_NCHAR(x):将数据库字符集中的x转换为一个NVARCHAR2字符串;
TO_NCLOB(x):将x转换为NCLOB类型,NCLOB用于保存大量的国家语言字符数据;
TO_NUMBER(x[,format]):将x转换为NUMBER类型;
TO_SINGLE_BYTE(x):将x中多字节字符转化为对应的单字节字符,返回类型与x相同;
TO_TIMESTAMP(x):将字符串c转换为TIMESTAMP类型;
TO_TIMESTAMP_TZ(x):将字符串c转换为TIMESTAMP WITH TIME ZONE类型;
TO_YMINTERVAL(x):将字符串c转换为INTERVAL YEAR TO MONTH类型;
TRANSLATE(x,from_string,to_string):将x中所有的from_string转换为to_string;
UNISTR(x):将x中的字符转换为国家语言字符集(NCHAR);