Oracle Database ：Oracle11g SQL开发指南学习笔记之（1）：使用简单函数

一、单行函数（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）；