单行函数:(可以无限次嵌套)
单行函数的分类:
字符类,日期类,数字类,转换类,通用类
1.字符类
转换大小写:
lower:转换为小写
Select ENAME,LOWER(ENAME) from EMP
upper:转换为大写
Select upper('abcd') from DUAL
initcap :首字母大写
Select INITCAP(ENAME) from EMP
concat:连接两个不同的列,CONCAt只能接受两个参数(一般不用,可以用||来连接更方便)
Select CONCAT(EMPNO,ENAME) from EMP
Select CONCAT('A','B') from dual
substr:截取子串
下标从1开始
Select SUBSTR(ENAME,2) from EMP--从第2个位置截到末尾
Select SUBSTR(ENAME,2,3) from EMP--从第2个位置截取3个
Select SUBSTR(ENAME,-1,3) from EMP--从倒数第1个位置截取3个
length:提取长度
Select ENAME,length(ename) from emp
(select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER VALUE
---------------- --------
NLS_CHARACTERSET AL32UTF8
数据库字符编码为UTF-8,一个中文字符占3个字节。
select lengthb('中') from dual;
SQL> select lengthb('中') from dual;
LENGTHB('中')
-------------
3
SQL> select length('好') from dual
2 /
LENGTH('好')
------------
1
SQL> select length('a') from dual
2 /
LENGTH('A')
-----------
1
SQL> select length('阿猪') from dual;
LENGTH('阿猪')
--------------
2
SQL> select lengthb('阿猪') from dual;
LENGTHB('阿猪')
---------------
6
区别:length求得是字符长度,lengthb求得是字节长度(汉字占的字节长度与字符编码有关)。
)
INSTR:相当于String类中的indexOf,求索引
Select ENAME,instr(ename,'A') from emp--在字符ename中搜索A字符,并返回其位置
Select ENAME,instr(ename,'A','2','3') from emp--从字符ename的第二个位置还是查找,当第三次找到A字符时返回其位置
LPAD和RPAD的用法
表示补充的含义
Select LPAD(SAL,10,'*') from EMP--在字符串的左边补*,使字符串长度达到10
Select RPAD(SAL,10,'*') from EMP--在字符串的右边补*,使字符串长度达到10
trim
Select Trim(' A B C ') from DUal--去除字符串两头的空格
Select Trim('a' from 'aaaabvdaaaa') from DUal--去除字符串两边的a,知道字符串两头没有a为止
LTrime和RTrim
Select LTrim(' A B C ') from DUAL--去除字符串左边的空格
Select LTrim('a' from 'aaaabvdaaaa') from DUal--去除字符串左边的a,知道字符串两头没有a为止
Select RTrim(' A B C ') from DUAL--去除字符串右边的空格
Select RTrim('a' from 'aaaabvdaaaa') from DUal--去除字符串右边的a,知道字符串两头没有a为止
REPLACE:替换
Select ename ,Replace(ename,'A','B') from EMP--将字符串ename中的A用B代替
3.数字转换类:
ROUND:四舍五入
Select ROUND(16.336666663,3) from DUAL--保留3位小数点
Select ROUND(16.336666663,-1) from DUAL--四舍五入到十位
TRUNC:截取
Select TRUNC(16.33666666,3) from DUAL--保留3位小数点
Select TRUNC(16.33666666,3) from DUAL--截取到十位
MOD:取余
Select Mod(8,3) from DUAL
4.日期类函数
sysdate:年月日时分秒
日期+-数字,代表日期加上天数,日期-日期,得到的结果代表天数,比如:
Select TRUNC(Sysdate-365) from DUAL
Select Sysdate-1/24/60 from DUAL
5.日期运算函数
MONTHS_BETWEEN:表示两个日期的月份之差
Select EMPNO,HIREDATE,MONTHS_BETWEEN(Sysdate,HIREDATE)/12 from EMP
ADD_MONTHS:表示给指定的日期加一个月数
Select HIREDATE,ADD_MONTHS(HIREDATE,3) from EMP
NEXT_DAY:表示以当前时间为基准,下一个"星期几"的日期
Select NEXT_DAY(Sysdate,'星期二') from DUAL
LAST_DAY:计算当前日期的月份的最后一天
Select HIREDATE,LAST_DAY(HIREDATE)-HIREDATE from EMP
ROUND:对日期进行四舍五入
Select ROUND(Sysdate,'YEAR') from DUAL--对日期四舍五入到年
TRUNC:表示对日期进行截取
Select TRUNC(Sysdate,'month') from DUAL--对日期截取到月
5.TO_CHAR函数将日期转换为字符类型
Select TO_CHAR(Sysdate,'YYYY-MM-DD DAY HH24:MI:SS AM') from DUAL--将当前日期转换为'YYYY-MM-DD DAY HH24:MI:SS AM'格式的字符
特殊的日期转换描述
Select TO_CHAR(Sysdate,'"今天是"YYYY-MM-DD DAY HH24:MI:SS AM') from DUAL--可以在'"今天是"YYYY-MM-DD DAY HH24:MI:SS AM'中通过" "来加入自己添加的词语
6.TO_CHAR:可以对数字类型进行类型转换,转换的同时,你可以指定转换后的格式,比如:
Select TO_CHAR(SAL,'$999,999.00') from EMP
7.TO_NUMBER:作用是用来将一个指定的字符串类型的数字,转换为数字格式
注意:格式必须要匹配.
Select TO_NUMBER('$1,600.01','$999,999.99') from DUAL
8.TO_DATE:将字符串类型的日期,转换为日期类型
Select TO_DATE('2008-01==01','YYYY-MM==DD') from DUAL
特殊的日期格式:
Select TO_DATE('2008-01-01 星期二 10:36:31 上午','YYYY-MM-DD DAY HH:MI:SS PM') from DUAL--英语中day代表星期
(数字格式掩码:9 :代表一个数字(若9的个数不够,则结果会出现########
如:Select TO_NUMBER('$11,600.01','$9,999.99') from DUAL,则结果会出现########)
0 :强制显示0
$ :放置一个$符
L :放置一个浮动本地货币符(L显示的货币符号与nls_territory参数有关)
. :显示小数点
, :显示千位指示符
日期格式掩码:YYYY、YYY、YY 分别代表4位、3位、2位的数字年
YEAR 年的英文拼写
MM 数字月
MONTH 月的英文全拼
MON 月的英文缩写
DD 数字日
DAY 星期的全拼
DY 星期的缩写
AM 表示上午或者下午
HH24、HH12 12小时制或24小时制(HH表示12小时制)
MI 分钟
SS 秒钟
SP 数字的拼写
TH 数字的序数词
FM 去除前导0
)
10.关于通用函数:
NVL:当修饰的字段的内容为null时,运算结果为指定的值
Select COMM,NVL(COMM,200) from EMP--当COMM为空时,返回200,当COMM非空时,返回COMM
NVL2:如果第一个表达式的值不为null,显示表达式2的值,否则(为null)显示表达式3的值
Select SAL,COMM,NVL2(COMM,SAL+COMM,SAL) from EMP--当COMM为非空时,返回SAL+COMM,当COMM为空时,返回SAL
NULLIF:如果前后两个表达式的内容相等的,那就返回null,否则,返回第一个表达式的值
Select ENAME,JOB,NULLIF(LENGTH(ENAME),5) from EMP--当LENGTH(ENAME)=5时,返回null,当LENGTH(ENAME)<>5时,返回LENGTH(ENAME)的值
COALESCE函数:用来匹配多个字段的值,如果表达式1的值为null,显示表达式2的值,如果表达式2也为空,显
示表达式3的值,依次类推
Select MGR,COMM, Coalesce(MGR,COMM,EMPNO,88) from EMP
11.条件表达式:
两种方式:
1.case:表达式语句
2.decode:函数,decode()
A.case语句
简单case(简单case一定可以转换为decode())
case expre when condition then value
else value
end
例如:
Select job,
Case JOB
When 'CLERK' Then '店员'
When 'SALESMAN' Then '销售'
Else '临时工'
End
from emp(若emp表中有一行既是CLERK,又是SALESMAN,则在case和decode()中,他只参与到前面一个when的运算中)
复杂case(复杂case不一定能转换为decode())
case when expre then value
else value
end
例如:
Select job,
Case
When job='CLERK' Then '店员'
When job='SALESMAN' Then '销售'
Else '临时工'
End
from emp
B.decode函数
Select job,
decode(JOB,
'CLERK','店员'
,'SALESMAN' ,'销售',
'临时工'
) from emp