03_单行函数 oracle学习笔记

最新推荐文章于 2021-12-14 21:30:55 发布

FishYu2012

最新推荐文章于 2021-12-14 21:30:55 发布

阅读量585

点赞数

分类专栏： oracle 文章标签： oralce 单行函数

本文链接：https://blog.csdn.net/XiaoY_2012/article/details/50354725

版权

oracle 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

select ...
from ...
where ....
order by ....

多行函数

SQL> select count(*) from emp;

COUNT(*)
----------
14
字符函数

1 select Lower('Hello') 转小写, upper('hello') 转大写, initcap('hello woRld') 首字母大写
2* from dual
SQL> /

转小转大首字母大写
----- ----- -----------
hello HELLO Hello World

select concat('hello', 'world') from dual

SQL> select concat (concat('hello', 'world'), 'cccc') 函数嵌套 from dual;

函数嵌套
--------------
helloworldcccc

CONCAT('HE
----------
helloworld
SUBSTR(a,b) --- 从a中第b位去字符串
substr(a, b, c) 从a中第b位, c个字符....

SQL> select substr('abcdefg1111', 2) 从第二位取子串, substr('abcdefg1111', 2, 4) 取4个字符 from dual;

从第二位取取4
---------- ----
bcdefg1111 bcde

--length字符数 --lengthb字节数
--一个中文字符,两个字节

SQL> select length('中国abc') 字符数, lengthb('中国abc') 字节数 from dual;

字符数字节数
---------- ----------
5

SQL> \

SQL> select instr('abcdefg', 'efg') 求子串位置 from dual;

求子串位置
----------
5

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

LPAD RPAD
---------- ----------
******abcd abcd******

--trim 去掉前后指定的字符,字符可以是空格,也可以不是空格 .
SQL> select trim('A' from 'ABCDEFg') from dual;

TRIM('
------
BCDEFg

========数字函数
//2 > 0 表保留小数点2位
// 0 保留个位
//-1 保留10位
//-1 保留百位 (要看十位四舍五入)
1 select round(45.926, 2) AA, round(45.926, 1) BB, round(45.926, 0) CC, round(45.926) DD,
2* round(45.926, -1) EE, round(45.926, -2) FF from dual
3 /

AA BB CC DD EE FF
---------- ---------- ---------- ---------- ---------- ----------
45.93 45.9 46 46 50 0

=========日期
时间的计算.......
oracle数据库中 date包含日期和时间
mysql 3中数据类型 date time times.....

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual ;

SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual ;

TO_CHAR(SYSDATE,'YY
-------------------
2014-10-07 16:34:17

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

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

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

select (sysdate-hiredate)/7 周, (sysdate-hiredate)/30 月, (sysdate-hiredate)/365 年
from emp;

1 select (sysdate-hiredate)/7 周, (sysdate-hiredate)/30 月, (sysdate-hiredate)/365
2* from emp
SQL> /

周月年
---------- ---------- ----------
1763.95625 411.589791 33.8292979
1754.67053 409.423125 33.6512157
1754.38482 409.356458 33.6457363
1748.81339 408.056458 33.538887
1723.24196 402.089791 33.048476
1744.67053 407.089791 33.4594349
1739.09911 405.789791 33.3525856
1433.38482 334.456458 27.4895719
1716.09911 400.423125 32.9114897
1726.09911 402.756458 33.1032705
1428.52768 333.323125 27.3964212
1713.81339 399.889791 32.8676541
1713.81339 399.889791 32.8676541
1706.52768 398.189791 32.7279281

---查询员工的入职时间, 入职月数 ....

select ename, (sysdate-hiredate)/30 估计月, MONTHS_BETWEEN(sysdate, hiredate) 函数计算月
from emp

1 select ename, (sysdate-hiredate)/30 估计月, MONTHS_BETWEEN(sysdate, hiredate) 函数计算月
2* from emp
SQL> /

ENAME 估计月函数计算月
---------- ---------- ----------
SMITH 411.589872 405.699876
ALLEN 409.423205 403.603102
WARD 409.356539 403.538586
JONES 408.056539 402.183747
MARTIN 402.089872 396.345037
BLAKE 407.089872 401.216005
CLARK 405.789872 399.957941
SCOTT 334.456539 329.63536
KING 400.423205 394.699876
TURNER 402.756539 396.990199
ADAMS 333.323205 328.506328
JAMES 399.889872 394.151489
FORD 399.889872 394.151489
MILLER 398.189872 392.506328

已选择14行。

select next_day(sysdate, '星期六') from dual

SQL> select next_day(sysdate, '星期六') from dual ;

NEXT_DAY(SYSDA
--------------
11-10月-14

==========================数据类型转换==============

select *
2 from emp
3* where hiredate > '01-1月 -81'
日期相关类型转换
---比这个日期都要大的08-9月 -81 所有员工信息按照三种方式实现

select * from emp
where hiredate > '01-1月 -81' --隐式类型转换

select * from emp
where hiredate > to_date('1981-01-01 02:03:04', 'yyyy-mm-dd hh24:mi:ss') --字符串转成日期...显示类型转换

select * from emp
where to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') > '1981-01-01 02:03:04'

1 select * from emp
2* where hiredate > to_date('1981-01-01 02:03:04', 'yyyy-mm-dd hh24:mi:ss')
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
====================
数字和字符串转换

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

select empno, ename, to_char(sal, 'L9,999.99')
from emp;

1 select empno, ename, to_char(sal, 'L9,999.99')
2* from emp
SQL> /

EMPNO ENAME TO_CHAR(SAL,'L9,999
---------- ---------- -------------------
7369 SMITH ￥800.00
7499 ALLEN ￥1,600.00
7521 WARD ￥1,250.00
7566 JONES ￥2,975.00
7654 MARTIN ￥1,250.00
7698 BLAKE ￥2,850.00
7782 CLARK ￥2,450.00
7788 SCOTT ￥3,000.00
7839 KING ￥5,000.00
7844 TURNER ￥1,500.00
7876 ADAMS ￥1,100.00
7900 JAMES ￥950.00
7902 FORD ￥3,000.00
7934 MILLER ￥1,300.00

已选择14行。

把这个字符￥1,250.00,转成数字.....
select to_number('￥1,250.00', 'L9,999.99') from dual ;

SQL> select to_number('￥1,250.00', 'L9,999.99') from dual ;

TO_NUMBER('￥1,250.00','L9,999.99')
-----------------------------------
1250
通用函数

=======条件表达式做报表

-给员工涨工资:总裁 1000 经理:800 其他涨500,
前后工资给列出来

PRESIDENT

MANAGER

if (job == 'PRESIDENT')
SAL+1000
else if (job == 'MANAGER')
SAL+800
else
SAL+500

CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END

CASE job when 'PRESIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal + 500
END

select ename, job, sal 涨前工资,
(
CASE job when 'PRESIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal + 500
END
)

涨后工资
from emp

ENAME JOB 涨前工资涨后工资
---------- --------- ---------- ----------
SMITH CLERK 800 1300
ALLEN SALESMAN 1600 2100
WARD SALESMAN 1250 1750
JONES MANAGER 2975 3775
MARTIN SALESMAN 1250 1750
BLAKE MANAGER 2850 3650
CLARK MANAGER 2450 3250
SCOTT ANALYST 3000 3500
KING PRESIDENT 5000 6000
TURNER SALESMAN 1500 2000
ADAMS CLERK 1100 1600
JAMES CLERK 950 1450
FORD ANALYST 3000 3500
MILLER CLERK 1300 1800

已选择14行。

第二种方法

DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])

decode(job, 'PRESIDENT', sal+10000, 'MANAGER', sal+800, sal+500)
select ename, job, sal 涨前工资,
(
decode(job, 'PRESIDENT', sal+10000, 'MANAGER', sal+800, sal+500)
)

涨后工资
from emp