1、ascii : 将输入字符转换为相应ascii码
select ascii('a') a,ascii('A') A,ascii('0') zero from dual;
--97 65 48
2、CHR :返回给出整数对应的字符
select chr(90) ,chr(61) from dual;
--Z =
3、CONCAT:连接两个字符
select concat('hello ',' world!')||'zhuqi' say from dual;
-- hello world!zhuqi
4、initcap :将首字母大写
select initcap('test') from dual;
--Test
5、instr(str1,str2,[p1,[p2]]):从指定的字符串中搜索字符串,返回首次搜到的位置(位置从1开始)str1:被搜索的字符串str2:搜索的字符串p1;搜索开始的位置,默认:1p2:搜索第几次出现的位置,默认:1
select instr('hello world','o',7,1) pos from dual;
--8
6、length:返回字符串长度
select length('hello world') len from dual;
--11
7、lower:返回字符串小写
select lower('HeLLo') lower from dual;
--hello
8、upper:返回字符串大写
select upper('hello') upper from dual;
--HELLO
9、rpad(src,len,[str]):右补齐字符src : 源字符串len : 补齐后的长度,多则裁剪str : 不足时填充的字符
select rpad(level,2,0) from dual connect by level < 13;
---------
10、lpad(src,len,[str]):左补齐字符
src : 源字符串
len : 补齐后的长度,多则裁剪
str : 不足时填充的字符
select lpad(level,2,0) month from dual connect by level < 13;
---------
11、ltrim(str,[ch]):从左侧删除空格 或 删除其他预定义字符串中包含的字符直到遇到非删除字符为止
str : 源字符串
ch : 要删除的字符集
select ltrim('heehllo','he') lstr from dual;
--llo
---------12、rtrim(str,[ch]):从右侧删除空格 或 删除其他预定义字符串中包含的字符直到遇到非删除字符为止str : 源字符串ch : 要删除的字符集
select rtrim('hellooollo','lo') rstr from dual;
--he
---------13、substr(str,start,[cnt]):截取字符串,从start开始,取cnt长度str: 源字符串start: 开始位置,正方向从1开始,倒数方向从-1开始,0则取全部cnt:截取的长度
select substr('helloworld',-2,1) s from dual;
-- l
14、replace(str,s1,s2):替换字符str: 源字符串s1: 被替换的字符s2:要替换的字符
select replace('hello ,{0}','{0}','friend') str from dual;
--hello ,friend
15、soundex(str):返回给定字符串的语音表示形式规则:a e h i o u w y -> 0b f p v -> 1c g j k q s x z -> 2d t -> 3l -> 4m n -> 5r -> 61、提取字符串的首字母作为soundex的第一个值。2、按照上面的字母对应规则,将后面的字母逐个替换为数字。如果有连续的相等的数字,只保留一个,其余的都删除掉。并去除所有的0。3、如果结果超过4位,取前四位;如果结果不足4位向后补0。
select soundex('too') too,soundex('two') two,soundex('cap') from dual;
-- T000 T000C100
15、trim一般都是用在删除字符串两边的空格。实际上,trim也可以用来删除字符串两边的指定字符。并且trim指定删除的字符串只能是单个字符。如 trim('字符1' from '字符串2') ,字符1只能是单个字符。1. trim()删除字符串两边的空格。2. ltrim()删除字符串左边的空格。3. rtrim()删除字符串右边的空格。4. trim('字符1' from '字符串2') 分别从字符2串的两边开始,删除指定的字符1。5. trim([leading | trailing | both] trim_char from string) 从字符串String中删除指定的字符trim_char。 leading:从字符串的头开始删除。 trailing:从字符串的尾部开始删除。 borth:从字符串的两边删除。6. tim()只能删除半角空格。16、abs(num): 返回绝对值
select abs(-100.4) from dual;
--100.4
17、COS:返回一个给定数字的余弦
select cos(-3.1415927653) from dual;
---0.999999999999994
acos(n):给出反余弦值
select acos(-1) from dual;
--3.14159265358979
COSH:返回双曲余弦值
select cosh(10) from dual;
--0.523598775598299
18、asin:给出反正弦的值
select asin(0.5) from dual;
SIN:返回一个数字的正弦值
select sin(1.57079) from dual;
--0.999999999979986
SIGH:返回双曲正弦的值
select sin(20),sinh(20) from dual;
--0.912945250727628242582597.704895
19、ATAN:返回一个数字的反正切值
select atan(1) from dual;
--0.785398163397448
TAN:返回数字的正切值
select tan(20),tan(10) from dual;
--2.237160944224740.648360827459087
TANH:返回数字n的双曲正切值
select tanh(20),tan(20) from dual;
--12.23716094422474
20、CEIL:返回大于或等于给出数字的最小整数
select ceil(-5.12) from dual;
---5
21、FLOOR:返回不大于本身的整数
select floor(-3.1415) from dual;
-- -4
22、EXP:返回一个数字e的n次方根
select exp(0),exp(1) from dual;
--12.71828182845905
23、LN:返回一个数字的对数值
select ln(1),ln(2),ln(2.7182818) from dual;
--00.6931471805599450.999999989530502
24、LOG(n1,n2):返回一个以n1为底n2的对数
select LOG(2,1), LOG(2,2) from dual;
--01
25、MOD(n1,n2):返回一个n1除以n2的余数
select mod(10.2,3.1),mod(2,3) from dual;
--0.92
26、POWER(n1,n2):返回n1的n2次方根
select power(2,10),power(1.1,10) from dual;
--10242.5937424601
27、ROUND( number, [ decimal_places ] ):按精度取值,四舍五入decimal_places: 保留小数位数,四舍五入,可以为负数
select round(123.456, 2),round(123.456, 4),round(123.456, -1),round(sysdate,'dd') from dual;
--123.46123.4561202014-06-26
28、TRUNC(number,num_digits):截取,不四舍五入number: 需要截尾取整的数字。num_digits 用于指定取整精度的数字。num_digits 的默认值为 0。
select trunc(123.458,4), trunc(sysdate,'yyyy') from dual;
/**************日期********************/
1.select trunc(sysdate) from dual --2014-06-25 今天的日期为2014-06-25
2.select trunc(sysdate, 'mm') from dual --2014-06-01 返回当月第一天.
3.select trunc(sysdate,'yy') from dual --2014-01-01 返回当年第一天
4.select trunc(sysdate,'dd') from dual --2014-06-25 返回当前年月日
5.select trunc(sysdate,'yyyy') from dual --2014-01-01 返回当年第一天
6.select trunc(sysdate,'d') from dual --2014-06-22 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual --2014-06-25 14:00:00 当前时间为14:56
8.select trunc(sysdate, 'mi') from dual --2014-06-25 14:56:00 TRUNC()函数没有秒的精确
29、SIGN(n):取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
select sign(12),sign(-90),sign(0) from dual; -- 1 -1 0
30、SQRT(n):返回数字n的平方根
select sqrt(9),sqrt(3) from dual; -- 3 1.73205080756888
31、ADD_MONTHS(date,add_month):增加或减去月份
select to_char(add_months(sysdate,2),'yyyy-MM-dd') from dual; -- 2014-08-25
32、LAST_DAY:返回当月的最后一天
select last_day(sysdate) from dual;
-- 2014-06-30 16:48:22
33、MONTHS_BETWEEN(date2,date1):给出date2-date1的月份数
select months_between(to_date('2014.06.20','yyyy.mm.dd'),to_date('2013.06.20','yyyy.mm.dd')) mon_betw from dual;
-- 12
34、NEW_TIME(date,this,that):给出在this时区=other时区的日期和时间
select new_time(sysdate,'PDT','GMT') from dual; -- 2014-06-25 23:51:42
35、NEXT_DAY(date,y):用于计算date时间后第一个星期y的时间
date:日期
y:y星期一至星期日中的一个。也可能用数字1 - 7代替,但是用数字的时候1是代表的周日2才是代表的周一
select next_day(sysdate,'星期一') from dual;
-- 2014-06-30 16:55:31
36、SYSDATE : 用来得到系统的当前日期
select to_char(sysdate,'yyyy-MM-dd') from dual; -- 2014-06-25
37、CHARTOROWID:将字符数据类型转换为ROWID类型
select rowid,rowidtochar(rowid),ename from scott.emp;
38、CONVERT(char, dest_char_set[, source_char_set ]):将源字符串char从一个语言字符集source_char_set转换到另一个目的dest_char_set字符集
SELECT CONVERT('Ä Ê Í Ó Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL; -- ? ?? ?? ?? ? A B C D E
39、HEXTORAW:将一个十六进制构成的字符串转换为二进制
40、RAWTOHEXT:将一个二进制构成的字符串转换为十六进制
41、ROWIDTOCHAR:将ROWID数据类型转换为字符类型
42、TO_CHAR(d,format):把日期或数字转换为字符串
select to_char(12345,'999999.999') from dual; -- 12345.000
43、TO_DATE(string,format):将字符串转化为ORACLE中的一个日期
select to_date('2014.05.06 16:50:01','yyyy-MM-dd hh24:mi:ss') from dual; -- 2014-05-06 16:50:01
44、TO_MULTI_BYTE:将字符串中的单字节字符转化为多字节字符,(转换成全角)
select to_multi_byte('123') from dual;-- 123 而不是:123
45、to_single_byte(c):转换成半角
select to_single_byte('123') from dual; -- 123
46、TO_NUMBER:将给出的字符转换为数字
select to_number('123.7') from dual; -- 123.7,非数字报错
47、BFILENAME(dir,file):用于返回一个BFILE定位器,这个定位器指向一个物理的LOB二进制文件。
dir:是指一个目录对象,该目录对象对应该文件的全路径,该文件位于文件服务器上。
file:是指该文件在文件服务器上文件名。
应用:
首先 我们需要创建一个叫exampleDir目录对象,该目录对象指向文件服务器的路径/example/totn.
CREATE DIRECTORY exampleDir AS '/example/totn';
然后我们可以在bfilename函数中使用exampleDir目录对象:
SELECT bfilename('exampleDir', 'totn_logo.jpg')
FROM dual;
48、decode(value,if1,then1,if2,then2,if3,then3,...,else):表示如果value 等于if1时,DECODE函数的结果返回then1,...,
如果不等于任何一个if值,则返回else。
该函数的含义如下:
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
49、DUMP(expr[,return_fmt[,start_position][,length]]):以return_fmt指定的内部数字格式返回一个VARCHAR2类型的值
expr:这个参数是要进行分析的表达式(数字或字符串等,可以是各个类型的值)
return_fmt:指返回参数的格式,这个参数有5种用法
1) 8:以8进制返回结果的值
2) 10:以10进制返回结果的值(默认)
3) 16:以16进制返回结果的值
4) 17:以单字符的形式返回结果的值
5) 1000:以上4种加上1000,表示在返回值中加上当前字符集
start_position:开始进行返回的字符位置
length:需要返回的字符长度
select dump('abc') from dual;-- Typ=96 Len=3: 97,98,99
SELECT DUMP('abc',17,2,2) FROM DUAL; -- Typ=96 Len=3: b,c
50、EMPTY_BLOB()和EMPTY_CLOB():这两个函数都是用来对大数据类型字段进行初始化操作的函数
51、GREATEST:通过比较字符的编码大小,返回一组表达式中的最大值.
select greatest('1','AB','AC') from dual; -- AC
52、LEAST:返回一组表达式中的最小值
select least('下','午','好','!') from dual; -- !
53、UID:返回标识当前用户的唯一整数
select username,user_id from dba_users where user_id=uid; -- test 61
select uid from dual; -- 61
54、USER:返回当前用户的名字
select USER from dual; -- test
55、USERENV(opt):返回当前用户环境的信息
opt:ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE,其中:
ISDBA 查看当前用户是否是DBA如果是则返回true
SESSIONID 返回会话标志
ENTRYID 返回会话人口标志
INSTANCE 返回当前INSTANCE的标志
LANGUAGE 返回当前环境变量
LANG 返回当前环境的语言的缩写
TERMINAL 返回用户的终端或机器的标志
VSIZE(X) 返回X的大小(字节)数
select userenv('isdba'),userenv('SESSIONID'),userenv('ENTRYID'),userenv('INSTANCE') ,
userenv('LANGUAGE'),userenv('LANG'),userenv('TERMINAL'),VSIZE(user) from dual;
-- FALSE 3776 0 1 SIMPLIFIED CHINESE_CHINA.ZHS16GBK ZHS ZHAOZB 6
56、AVG(DISTINCT|ALL):
all表示对所有的值求平均值,distinct只对不同的值求平均值
57、MAX(DISTINCT|ALL):
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
58、MIN(DISTINCT|ALL):
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
59、STDDEV(distinct|all):
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
60、VARIANCE(DISTINCT|ALL):
求协方差
61、GROUP BY :主要用来对一组数进行分组统计
62、HAVING :对分组统计再加限制条件
63、ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2):
row_number()从1开始,为每一条分组记录返回一个数字,
表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)
select name,flag,age, row_number() over (partition by flag order by age) rank from person t
--1 taiy 1 23 1
--2 zhaozb 1 24 2
--3 cm 1 24 3
--4 wuyk 2 26 1
和 RANK() OVER(PARTITION BY COL1 ORDER BY COL2)、DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY COL2)类似,
区别:
1,2,2,4,5,6 。。。。这是rank()的形式
1,2,2,3,4,5 。。。。这是dense_rank()的形式
1,2,3,4,5,6 。。。。这是row_number()涵数形式
64、over(PARTITION BY COL1 ORDER BY COL2):
Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:
对于每个组返回多行,而聚合函数对于每个组只返回一行。
开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化
例子:
over(order by salary range between 5 preceding and 5 following):窗口范围为当前行数据幅度减5加5后的范围内的
over(order by salary rows between 5 preceding and 5 following):窗口范围为当前行前后各移动5行。
sum() over(partition by ... order by ...):分组求和
select name,age,sum(age) over (order by age range between 1 preceding and 1 following) s from person;
-- 年龄升序后,年龄在+-1范围内的总和
--1 taiy 23 71 71= 23+24+24
--2 zhaozb 24 71
--3 cm 24 71
--4 wuyk 26 26
count() over(partition by ... order by ...):分组统计
select name,age,count(age) over (order by age range between 1 preceding and 1 following) s from person;
-- 年龄升序后,年龄在+-1范围内的数量
--1 taiy 23 3
--2 zhaozb 24 3
--3 cm 24 3
--4 wuyk 26 1
max() over(partition by ... order by ...):组内求最大
select name,age,max(age) over (order by age range between 1 preceding and 1 following) s from person;
-- 年龄升序后,年龄在+-1范围内中的最大年龄
--1 taiy 23 24
--2 zhaozb 24 24
--3 cm 24 24
--4 wuyk 26 26
min() over(partition by ... order by ...):组内最小
select name,age,min(age) over (order by age range between 1 preceding and 1 following) s from person;
-- 年龄升序后,年龄在+-1范围内中的最小年龄
--1 taiy 23 23
--2 zhaozb 24 23
--3 cm 24 23
--4 wuyk 26 26
avg() over(partition by ... order by ...):组内求平均值
select name,age,avg(age) over (order by age rows between 1 preceding and 1 following) s from person;
-- 年龄升序后,当前行+-1范围内中的平均值
--1 taiy 23 23.5
--2 zhaozb 24 23.6666666666667 val = (23+24+24)/3
--3 cm 24 24.6666666666667 val = (24+24+26)/3
--4 wuyk 26 25
first_value() over(partition by ... order by ...):组内第一个值
select name,age,first_value(age) over (order by age rows between 1 preceding and 1 following) s from person;
--年龄升序后,当前行+-1范围内中的第一个值
--1 taiy 23 23
--2 zhaozb 24 23
--3 cm 24 24
--4 wuyk 26 24
last_value() over(partition by ... order by ...):组内最后一个值
select name,age,last_value(age) over (order by age rows between 1 preceding and 1 following) s from person;
--年龄升序后,当前行+-1范围内中的最后一个值
--1 taiy 23 24
--2 zhaozb 24 24
--3 cm 24 26
--4 wuyk 26 26
lag(expresstion,<offset>,<default>) over(partition by ... order by ...):取出前n行的那条数据
select name,age,lag(age,2,0) over(order by age) s from person;
-- 年龄升序后,当前行前2行的age,默认为0
--1 taiy 23 0
--2 zhaozb 24 0
--3 cm 24 23
--4 wuyk 26 24
lead(expresstion,<offset>,<default>) over(partition by ... order by ...):取出后n行的那条数据
select name,age,lead(age,1,0) over(order by age) s from person;
-- 年龄升序后,当前行下一行的age,默认为0
--1 taiy 23 24
--2 zhaozb 24 24
--3 cm 24 26
--4 wuyk 26 0
65、CUME_DIST() over(partition by .. order by ..):
计算某个值在 SQL Server 中的一组值内的累积分布。也即,CUME_DIST 计算某指定值在一组值中的相对位置。
对于行 r,假定采用升序,r 的 CUME_DIST 是值低于或等于 r 的值的行数除以在分区或查询结果集中求出的行数。
select name,age,CUME_DIST() over(order by age) s from person;
--1 taiy 23 0.25
--2 zhaozb 24 0.75
--3 cm 24 0.75
--4 wuyk 26 1
66、percent_rank() over(partition by .. order by ..):
计算方法:所在组排名序号-1除以该组所有的行数-1。用rank排序
select name,age,percent_rank() over(order by age) s from person;
-- rank 方式排序后(例子排名:1 2 2 4),计算
--1 taiy 23 0
--2 zhaozb 24 0.333333333333333
--3 cm 24 0.333333333333333
--4 wuyk 26 1
还有些其他的,以后遇到了再添加。。