单行函数

最新推荐文章于 2024-11-11 08:41:40 发布

ckkuc00228

最新推荐文章于 2024-11-11 08:41:40 发布

阅读量69

点赞数

文章标签：数据库

单行函数:(可以无限次嵌套)

单行函数的分类:
字符类，日期类，数字类，转换类，通用类
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