ORACLE学习（三）

--数值函数

1、abs(值) 绝对值

正数的绝对值是它本身 负数是他的相反数 0是0

SELECT ABS(6),ABS(-5),ABS(0)

FROM DUAL;

2、MOD(数1,数2) 取余

数1÷数2 。。。。。。取余

数2可以为0 结果是数1

结果的正负只和数1有关

FROM DUAL;

--假设员工编号奇数为男 偶数为女 囚所有的男员工的信息

SELECT *

FROM EMP

WHERE MOD(EMPNO,2)=1;

3、ceil(数) 向上取整

4、floor(数) 向下取整

SELECT CEIL(4.5),CEIL(-4.5),FLOOR(4.5),FLOOR(-4.5)

FROM DUAL;

5、round(数1[,数2]) 四舍五入

数2 保留的小数位数 不写默认保留到整数

小数位数不够 不会补0

数2为负数 是保留到小数点前几位

SELECT ROUND(3.14,1),ROUND(3.14),ROUND(3.14,3),

ROUND(1354,-2)

FROM DUAL;

6、trunc(数1[,数2]) 截断 ---不进位

数2 保留的小数位数 不写默认保留到整数

小数位数不够 不会补0

数2为负数 是保留到小数点前几位

SELECT trunc(3.14,1),trunc(3.14),trunc(3.14,3),

trunc(1354,-2)

FROM DUAL;

7、sign(数) 判断正负零

正数返回1 负数返回-1 0返回0

SELECT SIGN(6),SIGN(-6),SIGN(0)

FROM DUAL;

8、POWER(数1,数2) 次方和开方

数1的数2次方

数2为分数表示开方

SELECT POWER(2,3),POWER(16,1/4),POWER(2,-3),POWER(32,-1/5)

FROM DUAL;

--字符函数

1、upper(str) 转大写

2、lower(str) 转小写

3、initcap(str) 首字母大写

如果有多个字母 那么每个字母的首字母都会变成大写

英文字母中间有非英文字母就认为是不同的单词

SELECT UPPER('I love eat banana'),lower('I LOVE EATING BANANA'),

INITCAP('i我love爱eat吃banana香蕉')

FROM DUAL;

--查询员工姓名 转首字母大写 职位转小写

SELECT INITCAP(ENAME),

LOWER(JOB)

FROM EMP;

3、length(str) 字符长度

4、lengthb(str) 字节长度（占内存的大小）

注：字符长度不一定等于字节长度

1byte=8bit

1KB=1024byte

1MB=1024KB

1GB=1024MB

SELECT LENGTH('ABC'),LENGTHB('ABC'),

LENGTH('中国'),LENGTHB('中国')

FROM DUAL;

--查询员工姓名的字符长度和字节长度

select ename,length(ename),lengthb(ename)

from emp;

6、trim(str) 去两端的空格

7、ltrim(str1[,STR2]) 去除str1左边的str2

8、rtrim(str1[,str2]) 去除str1右边的str2

不写str2默认去空格

SELECT TRIM(' ABC ')||'D',LTRIM(' ABC'),

RTRIM('ABC CC','C')||'D'

FROM DUAL;

--查询员工编号 先去掉左边的7在去掉右边的8

SELECT EMPNO, RTRIM(LTRIM(EMPNO,7),8)

FROM EMP;

9、lpad(str1,数,str2) 在str1的左边填str2 填充之后的总长度是数

10、rpad(str1,数,str2) 在str1的右边填充str2 。。。。。。数

str1 源字符串

str2 要填充的字符串

数 填充之后整体的字节长度，如果比原长度还要小就变成了截取

--在abc的左右两边各填充两个*

SELECT 'ABC'RPAD(LPAD('ABC',5,''),7,'')

FROM DUAL;

SELECT 'ABCDEFG',LPAD('ABCDEFG',4,'6'),

RPAD('ABCDEFG',3,'8')

FROM DUAL;

--在abc的左右两边各填充两个 '星'

SELECT RPAD(LPAD('ABC',7,'星'),11,'星')

FROM DUAL;

--在ename的两边各填充两个*

SELECT ENAME,RPAD(LPAD(ENAME,LENGTHB(ENAME)+21,''),

LENGTHB(ENAME)+4,'*')

from emp;

SELECT ENAME,''||ENAME||''

FROM EMP;

11、instr(str1,str2[,数1[,数2]]) 查找字符串出现的位置

str1 原字符串

str2 要找的字符串

数1 从第几位开始找 不写默认第一为找

负数表示从倒数第几位开始找

数2 找第几次出现 不写默认第一次

SELECT INSTR('HELLOWORLD','O') A, --第一位开始 找第一次

INSTR('HELLOWORLD','O',6) B, --第6位开始第一次

INSTR('HELLOWORLD','O',1,2) C,--第一位开始 第二次出现

INSTR('HELLOWORLD','O',-1) D, --倒数第一位 第一次

INSTR('HELLOWORLD','O',-5) E,--倒数第五位第一次

INSTR('HELLOWORLD','O',-1,2) F --倒数第一位 第二次

FROM DUAL;

注：当找不到的时候 返回0

--查询名字中包含A的员工信息

SELECT *

FROM EMP

WHERE INSTR(ENAME,'A')!=0;

--查询名字的第二个字母是L的员工信息

SELECT *

FROM EMP

WHERE INSTR(ENAME,'L',2)=2;

--查询员工姓名中A第一次和第二次出现的位置

SELECT ENAME,INSTR(ENAME,'A') 第一次,INSTR(ENAME,'A',1,2) 第二次

FROM EMP;

12、substr(str,数1[,数2]) 截取字符串

str 源字符串

数1 从哪儿开始截取 负数表示从倒数第几位开始截

数2 截取几位（没写默认截取到最后）

SELECT SUBSTR('HELLOWORLD',2),SUBSTR('HELLOWORLD',2,3),

SUBSTR('HELLOWORLD',-2),SUBSTR('HELLOWORLD',-5,2)

FROM DUAL;

--截取员工姓名中A的位置到最后（名字没有A的不显示）

SELECT ENAME,SUBSTR(ENAME,INSTR(ENAME,'A'))

FROM EMP

WHERE INSTR(ENAME,'A')!=0;

--I LOVE EAT RICE AND FRUIT 截取 E EAT RICE

SELECT SUBSTR('I LOVE EAT RICE AND FRUIT',

INSTR('I LOVE EAT RICE AND FRUIT','E'),

INSTR('I LOVE EAT RICE AND FRUIT','E',-1)-

INSTR('I LOVE EAT RICE AND FRUIT','E')+1

) AAAAAAAA

FROM DUAL;

13、replace（str1,str2[,str3]）整体替换

把str1中的str2整体替换成str3

str3不写，默认替换成空

SELECT REPLACE('HELLOWORLD','LO','***'),

REPLACE('HELLOWORLD','LO')

FROM DUAL;

14、translate（str1,str2,str3）逐一替换

把str1里面的str2逐一替换成str3

SELECT TRANSLATE('HELLOWORLD','LO','!%')

FROM DUAL;

SELECT TRANSLATE('HELLOWORLD','LO','!%')A,

TRANSLATE('HELLOWORLD','LOE','!%')B,

TRANSLATE('HELLOWORLD','LO','!%@')C,

TRANSLATE('HELLOWORLD','LL','!%')D

FROM DUAL;

--参数3的位数比参数2多的时候，参数3多出来的位数无效

参数2的位数比参数3多的时候，参数2中多出来的位数被替换成空

同一参数多个替换，只有第一次有效

参数2为空或者参数3为空，最后结果就为空

SELECT *

FROM EMP;

--把helloworld中的lo替换成空

SELECT REPLACE('HELLOWORLD','LO')

FROM DUAL;

SELECT TRANSLATE('HELLOWORLD','#LO','#')

FROM DUAL;

--把员工姓名中的AMS三个字母替换成空

SELECT ENAME,TRANSLATE(ENAME,'AMS','')姓名

FROM EMP;

--把员工编号中的7替换成@，8换成$ 显示替换前后的编号

SELECT EMPNO,TRANSLATE(EMPNO,78,'@$')

FROM EMP;

15、concat(str1,str2)链接字符串

SELECT CONCAT(CONCAT('HELLO','WORLD'),'CHINA')

FROM DUAL;

16、to_single_byte(str)转半宽 半角

17、to_multi_byte(str)转全宽 全角

--查询emp中姓名和职位有相同字母的员工信息

str1 str2 str3

zhangdan dagong （参数3的字节要多于参数2）SELECT TRANSLATE('zhangsan','dagong','*')

FROM DUAL;

SELECT E.,ENAME,JOB,TRANSLATE(ENAME,JOB,LPAD('',LENGTHB(JOB),''))FROM EMP EWHERE ENAME!=TRANSLATE(ENAME,JOB,LPAD('',LENGTHB(JOB),'*'));