首页 > 数据库 >Oracle函数入坑指南

Oracle函数入坑指南

时间:2022-12-22 15:14:33浏览次数:62  
标签:指南 返回 函数 SELECT dual Oracle c1 入坑 Select

 

一、oracle函数概述
Oracle 提供一系列用于执行特定操作的函数 SQL 函数带有一个或多个参数并返回一个值 以下是SQL函数的分类: 

新梦想技术分享 二、单行函数
单行函数对于从表中查询的每一行只返回一个值 可以出现在 SELECT 子句中和 WHERE 子句中 单行函数可以大致划分为:
日期函数
数字函数
字符函数
转换函数
其他函数

2.1 日期函数:可以进行算术运算

SYSDATE 返回系统日期 select sysdate from dual;
ADD_MONTHS(<d>,<i>) 返回日期d 加上i个月后的新日期(i正可负)。

Add_Months(sysdate,2)

Add_Months(sysdate,-2)

LAST_DAY(<d>) 返回日期d所在的月的最后一天。 Select Last_Day(sysdate) from dual;
MONTHS_BETWEEN(<d1>,<d2>) 返回日期d1比d2大多少月数。 Months_Between(’19-1月-2008’, ’19-1月-2009’)
NEW_TIME(<d>,<tz1>,<tz2>) 将时区tz1的时间d,转换为时区tz2里的时间。 SELECT SYSDATE,NEW_TIME(SYSDATE,'CDT','PDT') FROM DUAL;
NEXT_DAY(<d>,<dow>) 返回日期d后的第一个dow。(dow:day of week) Select Next_Day(sysdate,’星期一’) from dual;
EXTRACT 用于提取日期时间类型的特定部分 Select extract(month from sysdate) from dual;
ROUND 四舍五入 Round(sysdate,’Year’)精确到年
TRUNC 截断日期 Trunc(sysdate,’Month’)截断月份

 

2.2 字符函数

 

 

LPAD(<c1>,<i>[,<c2>]) 在字符串c1的左边添加字符串c2直到c1字符串的长度等于i。
SELECT LPAD('Hello!',8,'*') leftpad,RPAD('Hello!',8,'*') rightpad FROM DUAL;
RPAD(<c1>,<i>[,<c2>]) 在字符串c1的右边添加字符串c2直到c1字符串的长度等于i。
LOWER(<c1>) 把字符串c1转换为小写。 SELECT LOWER(ename) 小写,UPPER(ename) 大写, INITCAP(ename) 首字符 FROM EMP;
UPPER(<c1>) 把字符串c1转换为大写。
INITCAP(<c1>) 把c1字符串的每一个单词的第一个字母转换成大写字母。
LENGTH(<c1>) 返回字符串c1的长度。 SELECT LENGTH('How are you') FROM DUAL;
SUBSTR(<c1>,<i>[,<j>]) 返回字符串c1中从第i个位置开始的j个字符(向右)。如果省略j,则返回c1中从第i个位置开始的所有字符。如果j为负,则返回字符串c1中从第i个位置开始的j个字符(向左)。 SELECT SUBSTR('Hello,World',1,5) FROM DUAL; 1不是下标
INSTR(<c1>,<c2>[,<i>[,<j>]]) 在c1中从位置i开始查找c2在c1中出第j次的位置,i可以为负(此时,从c1的尾部开始)。

SELECT INSTR('Mississippi','i',3,3) FROM DUAL; 返回结果11。

SELECT INSTR('Mississippi','i',-2,3) FROM DUAL; 返回结果2。

LTRIM(<c1>,<c2>) 从c1前面开始去掉出现在c2的中任何前导字符集。

SELECT LTRIM('Mississippi','Mis') FROM DUAL; 返回结果'ppi'。

SELECT RTRIM('Mississippi','ip') FROM DUAL; 返回结果'Mississ'

RTRIM(<c1>,<c2>)

从c1后面开始去掉出现在c2的中任何前导字符集。

默认去掉空格
Trim(),去掉两端空格

Concat (<c1>, <c2>)  用于连接c1,c2两个表达式 Select concat ('Hello',' world') from dual;
Replace(<c1>,<c2>,[<c3>] ) 用c3替换c1中所有c2字符,如果没有c3,表示去掉c2字符 Select replace('jack and jue' ,'j','bl') from dual;
CHR和ASCII 字符与ASCII值之间的转换  
DECODE(<x>,<m1>,<r1>[,<m2>,<r2…>])

功能类似于一系列的if…then…else语句。

如果deptno为10,用学术部替代
如果为20,用市场部替代
如果为30,用就业部替代

select deptno,decode(deptno,10,'学术部',20,'市场部',30,'就业部') from emp;

 

2.3 数字函数

函数 输入 输出
Abs(n)  Select abs(-15) from dual;  15
Ceil(n)  Select ceil(44.778) from dual;  45
Cos(n)  Select cos(180) from dual;  -.5984601
Cosh(n)  Select cosh(0) from dual;  1
Floor(n)  Select floor(100.2) from dual;  100
Power(m,n)  Select power(4,2) from dual;  16
Mod(m,n)  Select mod(10,3) from dual;  1
Round(m,n)  Select round(100.256,2) from dual;  100.26 
Trunc(m,n) 

Select trunc(100.256,2) from dual;  100.25 
Sqrt(n)  Select sqrt(4) from dual; 

Sign(n) Select sign(-30) from dual; -1

 

2.4 转换函数

 

TO_CHAR(<x>[,<fmt>[,<nlsparm>]]) 将x转换成字符串。

Select to_char(sysdate,’yyyy-mm-dd’) from dual;

Select to_char(123456,’$999,999’) from dual;

TO_NUMBER(<c>[,<fmt>[,<nlsparm>]]) 将字符串c转换成数字。 Select to_number(‘123’) from dual;
TO_DATE(<c>[,<fmt>[,<nlsparm>]]) 将字符串c转换成日期。

Select to_date(‘2009-7-8’,’yyyy-mm-dd’) from dual;
                    SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') FROM dual;

 

2.4  其他函数

NVL(x1,x2) 如果x1为空返回x2,否则返回x1。 SELECT NVL(comm,0) FROM EMP;
NVL2(x1,x2,x3) 如果x1为空返回x3,否则返回x2 select nvl2(comm,2000,0) from emp;
NULLIF(x1,x2) 如果x1=x2返回空,否则返回x1 select nullif(comm,300) from emp;

 

三、分组函数
分组函数基于一组行来
返回结果 为每一组行返回一个值


    3. Group by子句,having筛选
          □ GROUP BY子句
                1)用于将信息划分为更小的组
                2)每一组行返回针对该组的单个结果
            □ HAVING子句
                1)用于指定 GROUP BY 子句检索行的条件

SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category;

 

 

SELECT p_category, MAX(itemrate) FROM itemfile

GROUP BY p_category

HAVING p_category NOT IN ('accessories');

 

求每个部门中每个岗位的平均薪水大于200的员工信息

select deptno,empjob,avg(sal) from emp group by deptno,empjob having avg(sal) > 20000

 

四、分析函数

用于计算一个行在一组有序行中的排位排位从1开始。如计算聚集的累积排名、移动平均数和报表聚合值。
ROW_NUMBER 返回连续的排位,不论值是否相等
RANK 具有相等值的行排位相同,序号随后跳跃
DENSE_RANK 具有相等值的行排位相同,序号是连续的

select ename 姓名,sal 薪水,

row_number() over(order by sal) row_number,

rank() over(order by sal) rank,

DENSE_RANK() over(order by sal) dense_rank from emp;


查询公司所有员工的工资排名情况

select e.ename ,e.sal,d.dname, e.deptno,

dense_rank() over(order by e.sal desc) 

from emp e ,dept d where e.deptno = d.deptno

标签:指南,返回,函数,SELECT,dual,Oracle,c1,入坑,Select
From: https://www.cnblogs.com/xmxit/p/16998748.html

相关文章