数据库之单行函数：字符函数，数字函数，日期函数，转换函数，通用函数，case表达式，decode函数

来源：互联网发布：2016年双十一城市数据编辑：程序博客网时间：2024/05/17 04:23

字符函数

--substr(a, b)从a中,第b位开始取

--substr(a, b, c)从a中,第b位开始取,取5个字符

--length字符数--lengthb字节数

--一个中文字符,两个字节

--instr('母串', '子串') ,在母串中查找字串,返回子串在母串中的位置;找不到返回0;

-lpad rpad

--lpad('abcd', 10, '*' ) rpad('abcd', 10, '*' )

--trim去掉前后指定的字符,字符可以是空格,也可以不是空格.

--trim('H' from 'Hellowrold')

--replace替换

SQL> select replace('hello world', 'l','*') from dual;

DUAL是一个‘伪表’，可以用来测试函数和表达式

数字函数

l ROUND:四舍五入

ROUND(45.926, 2) 45.93

l TRUNC: 截断

TRUNC(45.926, 2) 45.92

l MOD:求余

MOD(1600, 300) 100

日期函数：

Oracle中的日期型数据实际含有两个值:日期和时间。

默认的日期格式是DD-MON-RR.

日期的数学运算

SELECT last_name, (SYSDATE-hire_date)/7AS WEEKS

FROM employees

WHERE department_id = 90;

转换函数

TO_CHAR函数对数字的转换

使用 TO_NUMBER函数将字符转换成数字:
使用 TO_DATE函数将字符转换成日期:

通用函数：

这些函数适用于任何数据类型，同时也适用于空值：

NVL (expr1, expr2)
NVL2 (expr1, expr2, expr3)
NULLIF (expr1, expr2)
COALESCE (expr1, expr2, ..., exprn)
----------------------------------------------------------------------
nvl2(a, b, c) 当a=null返回c,否则返回b
SQL> select sal,sal*12+nvl2(comm, comm, 0)年收入from emp;
nullif(a,b) 当a=b时,返回null;否则返回a
select nullif('abc', 'abc')from dual;
coalesce(a, b, c, ...) ;从左到右,返回第一个不为空的....
SQL> select comm,sal,COALESCE(sal, comm) from emp;
General Functions
These functions work with any data type and pertain to the use ofnull values in the expression list.

条件表达式

SQL语句中使用IF-THEN-ELSE逻辑

使用两种方法:

CASE 表达式：SQL99的语法，类似Basic，比较繁琐
DECODE 函数：Oracle自己的语法，类似Java，比较简介

例子：

-字符函数

selectlower('Hello') 转小写, Upper('Hello')转大写, initcap('hello world')首字母大写 from dual;

select'aaa' || 'bbb' 连接1, concat('1111', 2222) from dual;

--substr(a, b)从a中,第b位开始取

--substr(a, b, c)从a中,第b位开始取,取5个字符

selectsubstr('abcdefg', 3) 第一次提取, substr('abcdefg', 3,5) 第2次提取 fromdual;

--length字符数 --lengthb字节数

--一个中文字符,两个字节

selectlength('abcde中国') , lengthb('abcde') , lengthb('中国') from dual;

selectlpad('abcd', 10, '*' ), rpad('abcd', 10, '*' ) from dual;

SQL>select lpad('abcd', 10, '*' ) , rpad('abcd', 10, '*' ) from dual;

LPAD('ABCDRPAD('ABCD

--------------------

******abcdabcd******

------instr('母串', '子串') ,在母串中查找字串,返回子串在母串中的位置;找不到返回0;

selectinstr('abcd', 'cd' ) from dual;

--------trim去掉前后指定的字符,字符可以是空格,也可以不是空格 .

--trim('H' from 'Hellowrold')

SQL>select trim('l' from 'Hellowrold') from dual;

TRIM('L'FR

----------

Hellowrold

SQL>select trim('H' from 'Hellowrold') from dual;

TRIM('H'F

---------

ellowrold

SQL>select trim('H' from 'HellowroldH') from dual;

-----replace('aaaa','b', 'c') --从母串中检索符合条件的字串b,替换成c

SQL>select replace('hello world', 'l','*') from dual;

REPLACE('HE

-----------

he**o wor*d

--- 2保留小说点后2位 1保留小说点后1位 0保留小说点后个位

-1保留小说点十位(要看个位数,要看个位数,根据十位数,进行四舍五入)

-2保留百位(要看十位数,根据十位数,进行四舍五入)

select round(45.926, 2) AA, round(45.926,1) BB, round(45.926, 0) CC, round(45.926) DD,

round(44.926, -1) EE, round(45.926, -2) FFfrom dual;

---round(45.926, -2) FF from dual

因为4没有超过5,所以四舍五入,0

=====日期函数

select sysdate-1昨天, sysdate今天, sysdate+1明天 from dual;

---to_char('日期/数字',format) --

select to_char(sysdate-1, 'yyyy-mm-ddhh24:mi:ss')昨天, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')今天,

to_char(sysdate+1, 'yyyy-mm-dd hh24:mi:ss')明天 from dual;

---查询员工的入职时间,按照天周月年方式显示

select ename, hiredate-sysdate天, (hiredate-sysdate)/7 周, (hiredate-sysdate)/30月,

(hiredate-sysdate)/365年 from emp;

--精确计算员工的入职月数

select ename, ( sysdate-hiredate)/30月1,MONTHS_BETWEEN(sysdate,hiredate) 月2 from emp;

--显示当前日期的下一个周一

NEXT_DAY ('01-SEP-95','FRIDAY')

select next_day(sysdate, '星期一') fromdual;

--日期的四舍五入

select round(sysdate, 'MONTH') from dual;

====================

================类型转换

=================================

---比这个日期都要大的08-9月 -81 所有员工信息

selct * from emp

where hiredate > '08-9月 -81' --隐式类型转换

selct * from emp

where hiredate > to_data('1981-02-02','yyyy-mm-dd') --把字符串转成日期

select to_char(sysdate, 'yyyy-mm-dd') fromdual --把日期转成字符串

oracle自动转换

char===>number

number====>char

select3+2 || 'aaaa' from dual;

SQL>select 3+2 || 'aaaa' from dual;

3+2||

-----

5aaaa

SQL> \

data====>char

char====data

====转93行

--查询员工的薪水:两位小数本地货币代码千位符

selectename, to_char(sal, 'L9,999.99') from emp; -=-把数字转成字符串(格式化字符串)

--把这个字符串￥1,250.00转成数字

selectto_number('￥1,250.00' , 'L9,999.99') from dual;

ENAME TO_CHAR(SAL,'L9,999

-----------------------------

tom_abc ￥8,000.00

SMITH ￥800.00

ALLEN ￥1,600.00

WARD ￥1,250.00

JONES ￥2,975.00

MARTIN ￥1,250.00

BLAKE ￥2,850.00

CLARK ￥2,450.00

SCOTT ￥3,000.00

KING ￥5,000.00

TURNER ￥1,500.00

ADAMS ￥1,100.00

JAMES ￥950.00

FORD ￥3,000.00

MILLER ￥1,300.00

select sal, sal*12+nvl2(comm, comm, 0) 年收入 from emp;

通用函数============

nvl2(a,b, c) 当a=null返回c,否则返回b

SQL>select sal, sal*12+nvl2(comm, comm, 0) 年收入 from emp;

nullif(a,b)当a=b时,返回null;否则返回a

selectnullif('abc', 'abcd') from dual;

coalesce(a,b, c, ...) ;从左到右,返回第一个不为空的....

SQL>select comm,sal, COALESCE(sal, comm) from emp;

select nullif('abc', 'abc') AA, nullif('abc', 'abcd') BB from dual;

=====CASE表达式 =====做报表

-给员工涨工资:总裁 1000 经理:800 其他涨500,

前后工资给列出来

JOB

--------- -

CLERK

SALESMAN

MANAGER

SALESMAN

MANAGER

ANALYST

PRESIDENT

SALESMAN

CLERK

ANALYST

CLERK

检索JOB列

if 'PRESIDENT' SAL+1000

elseif 'MANAGER' SAL+800

else SAL+500

CASEexpr WHEN comparison_expr1 THEN return_expr1

[WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn

ELSE else_expr]

END

CASEjob WHEN 'PRESIDENT' THEN SAL+1000

WHEN 'MANAGER' THENSAL+800

ELSE SAL+500

END

select ename, job, sal涨前工资,

(CASEjob WHEN 'PRESIDENT' THEN SAL+1000

WHEN'MANAGER' THEN SAL+800

ELSESAL+500

END)涨后工资

from emp

DECODE(job, 'PRESIDENT', SAL+1000 ,'MANAGER', SAL+800, SAL+500)

[, search2, result2,...,]

[, default])

select ename, job, sal涨前工资,

(DECODE(job,'PRESIDENT', SAL+1000 , 'MANAGER', SAL+800, SAL+500)) 涨后工资

from emp

0 0