- 浏览: 131055 次
- 性别:
- 来自: 杭州
文章分类
- 全部博客 (84)
- Java (30)
- C/C++ (3)
- Linux (1)
- 数据库 (19)
- 搜索引擎 (1)
- 数据结构 (2)
- 杂七杂八 (4)
- WEB前端 (6)
- Spring (2)
- JavaScript (9)
- DHTMLX (3)
- 正则表达式 (1)
- JQuery (1)
- Report (2)
- PDF (2)
- Word (0)
- Excel (0)
- CSV (0)
- Test & QA (1)
- BI (Business Intelligence) (10)
- SQL (18)
- SAP Business Object (0)
- DataStage (3)
- CI (Continuous Integration) (1)
- Algorith (3)
- Portal (1)
- Struts (1)
- Oracle (9)
- MS SQL Server (3)
- Teradata (3)
- MySql (1)
- Data Warehouse (5)
- ETL (8)
oracle的函数
单行函数 返回值只有一个
分组函数 返回值是多条记录
group by
sum
avg
单行函数
字符函数
concat 连接 ||
<1>显示dname和loc中间用-分隔
select deptno,dname||'----'||loc from dept;
dual哑元表 没有表需要查询的时候 可以用它
select 'Hello World' from dual;
select 1+1 from dual;
查询系统时间
select sysdate from dual;
<2> initcap 首字母大写
select ename,initcap(ename) from emp;
<3> lower 转换为小写字符
select ename,lower(ename) from emp;
<4> upper 转换为大写
update dept set loc=lower(loc);
update dept set loc=upper(loc);
<5> LPAD 左填充
select deptno,lpad(dname,10,' '),loc from dept;
<6> RPAD 右填充
<7> LTRIM 去除左边的空格
RTRIM 去除右边的空格
ALLTRIM 去除两边的空格
<8>replace 替换
translate 转换
select ename,replace(ename,'S','s') from emp;
用's'去替换ename中的'S'
select ename,translate(ename,'S','a') from emp;
<9> ASCII 求ASC码
chr asc码变字符
select ascii('A') from dual;
select chr(97) from dual;
select 'Hello'||chr(9)||'World' from dual;
'\t' ascii码是 9
'\n' ascii码是 10
select 'Hello'||'\t'||'World' from dual;
<10> substr 字符截取函数
select ename,substr(ename,1,3) from emp;
从第1个位置开始 显示3个字符
select ename,substr(ename,4) from emp;
从第4个位置开始显示后面所有的字符
<11> instr 测试字符串出现的位置
select ename,instr(ename,'S') from emp;
'S'第1次出现的位置
select ename,instr(ename,'T',1,2) from emp;
从第1个位置开始 测试'T'第2次出现的位置
<12> length 字符串的长度
select ename,length(ename) from emp;
日期和 时间函数
<1> sysdate 系统时间
select sysdate from dual;
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(sysdate,'DDD') from dual
select to_char(sysdate,'D') from dual
select to_char(sysdate,'DAY') from dual
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;
select '''' from dual;
select to_char(sysdate,'SSSSS') from dual;
--从今天零点以后的秒数
<2> ADD_MONTHS 添加月份 得到一个新的日期
select add_months(sysdate,1) from dual;
select add_months(sysdate,-1) from dual;
select trunc(sysdate)-to_date('20050101','yyyymmdd') from dual;
select add_months(sysdate,12) from dual;
一年以后的今天
select add_months(sysdate,-12) from dual;
一年以前的今天
trunc(sysdate) 截取年月日
select sysdate+2 from dual;
数字代表的是天数
两个日期之间的差值代表天数
<3> last_day 某月的最后一天
select last_day(sysdate) from dual;
select add_months(last_day(sysdate)+3,-1) from dual;
本月第3天的日期
<4> months_between 两个日期之间的月数
select months_between(sysdate,'2005-02-01') from dual;
方向 sysdate - '2005-02-01'
select months_between('2005-02-01',sysdate) from dual;
转换函数
to_char 把日期或数字类型变为字符串
select to_char(sysdate,'hh24:mi:ss') from dual;
select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;
select sal,to_char(sal,'L9,999') from emp;
L本地货币
to_number 把字符串变成数字
select to_number('19990801') from dual;
to_date 把字符串变成日期
select to_date('19800101','yyyymmdd') from dual;
select to_char(to_date('19800101','yyyymmdd'),
'yyyy"年"mm"月"dd"日"') from dual;
数学函数
ceil(x) 不小于x的最小整数
ceil(12.4) 13
ceil(-12.4) -12
floor(x) 不大于x的最大整数
floor(12.5) 12
floor(-12.4) -13
round(x) 四舍五入
round(12.5) 13
round(12.456,2) 12.46
trunc(x) 舍去尾数
trunc(12.5) 12
trunc(12.456,2) 12.45
舍去日期的小时部分
select to_char(trunc(sysdate),'yyyymmdd hh24:mi:ss') from dual;
mod(x,n) x除以n以后的余数
mod(5,2) 1
mod(4,2) 0
power(x,y) x的y次方
select power(3,3) from dual;
混合函数
求最大值
select greatest(100,90,80,101,01,19) from dual;
求最小值
select least(100,0,-9,10) from dual;
空值转换函数 nvl(comm,0) 字段为空值 那么就返回0 否则返回本身
select comm,nvl(comm,0) from emp;
comm 类型和 值的类型是 一致的
复杂的函数
decode 选择结构 (if ... elseif .... elesif ... else结构)
要求:
sal=800 显示低工资
sal=3000 正常工资
sal=5000 高工资
只能做等值比较
select sal,decode(sal,800,'低工资',3000,'正常工资',5000,'高工资','没判断')
from emp;
表示如下的if else 结构
if sal=800 then
'低工资'
else if sal =3000 then
'正常工资'
else if sal = 5000 then
'高工资'
else
'没判断'
end if
sal > 800 sal -800 > 0
判断正负
sign(x) x是正 1
x是负 -1
x是0 0
select sign(-5) from dual;
如何做大于小于的比较????
sal<1000 显示低工资 sal-1000<0 sign(sal-1000) = -1
1000<=sal<=3000 正常工资
3000<sal><=5000 高工资
select sal,decode(
sign(sal-1000),-1,'低工资',
decode(sign(sal-3000),-1,'正常工资',
0,'正常工资',1,
decode(sign(sal-5000),-1,'高工资','高工资')
)) as 工资状态 from emp;
一般的情况 decode(x,y1,z1,y2,z2,z3)
if x= y1 then
z1
else if x = y2 then
z2
else
z3
end if
分组函数 返回值是多条记录 或计算后的结果
group by
sum
avg
<1> 计算记录的条数 count
select count(*) from emp;
select count(1) from emp;
select count(comm) from emp; 字段上count 会忽略空值
comm不为空值的记录的条数
统计emp表中不同工作的个数 ????
select count(distinct job) from emp;
select distinct job from emp;
select distinct job,empno from emp;
select job,empno from emp;
得到的效果是一样的,distinct 是消去重复行
不是消去重复的列
<2>group by 分组统计
--在没有分组函数的时候
--相当于distinct 的功能
select job from emp group by job;
select distinct job from emp;
--有分组函数的时候
--分组统计的功能
统计每种工作的工资总额是多少??
select job,sum(sal) from emp
group by job; --行之间的数据相加
select sum(sal) from emp; --公司的工资总额
统计每种工作的平均工资是多少??
select job,avg(sal) from emp
group by job;
select avg(saL) from emp; --整个公司的平均工资
显示平均工资>2000的工作???
<1>统计每种工作的平均工资是多少
<2>塞选出平均工资>2000的工作
从分组的结果中筛选 having
select job,avg(sal) from emp
group by job
having avg(sal) > 2000;
group by 经常和having搭配来筛选
计算工资在2000以上的各种工作的平均工资????
select job,avg(sal) from emp
where sal > 2000
group by job
having avg(sal) > 3000;
一般group by 和 having搭配
表示对分组后的结果的筛选
where子句 --- 用于对表中数据的筛选
<3> max min
select max(sal) from emp;
公司的最高工资
select min(sal) from emp ;
公司的最低工资
找每个部门的最高和最低的工资??
select deptno,max(sal),min(sal) from emp
group by deptno;
找每个工作的最高和最低的工资??
select job,max(sal),min(sal) from emp
group by job;
找每个部门中每种工作的最高和最低的工资??
select deptno,job,max(sal),min(sal)
from emp
group by deptno,job;
select max(sal),min(sal)
from emp
group by deptno,job;
单个字段如果没有被分组函数所包含,
而其他字段又是分组函数的话
一定要把这个字段放到group by中去
单行函数 返回值只有一个
分组函数 返回值是多条记录
group by
sum
avg
单行函数
字符函数
concat 连接 ||
<1>显示dname和loc中间用-分隔
select deptno,dname||'----'||loc from dept;
dual哑元表 没有表需要查询的时候 可以用它
select 'Hello World' from dual;
select 1+1 from dual;
查询系统时间
select sysdate from dual;
<2> initcap 首字母大写
select ename,initcap(ename) from emp;
<3> lower 转换为小写字符
select ename,lower(ename) from emp;
<4> upper 转换为大写
update dept set loc=lower(loc);
update dept set loc=upper(loc);
<5> LPAD 左填充
select deptno,lpad(dname,10,' '),loc from dept;
<6> RPAD 右填充
<7> LTRIM 去除左边的空格
RTRIM 去除右边的空格
ALLTRIM 去除两边的空格
<8>replace 替换
translate 转换
select ename,replace(ename,'S','s') from emp;
用's'去替换ename中的'S'
select ename,translate(ename,'S','a') from emp;
<9> ASCII 求ASC码
chr asc码变字符
select ascii('A') from dual;
select chr(97) from dual;
select 'Hello'||chr(9)||'World' from dual;
'\t' ascii码是 9
'\n' ascii码是 10
select 'Hello'||'\t'||'World' from dual;
<10> substr 字符截取函数
select ename,substr(ename,1,3) from emp;
从第1个位置开始 显示3个字符
select ename,substr(ename,4) from emp;
从第4个位置开始显示后面所有的字符
<11> instr 测试字符串出现的位置
select ename,instr(ename,'S') from emp;
'S'第1次出现的位置
select ename,instr(ename,'T',1,2) from emp;
从第1个位置开始 测试'T'第2次出现的位置
<12> length 字符串的长度
select ename,length(ename) from emp;
日期和 时间函数
<1> sysdate 系统时间
select sysdate from dual;
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(sysdate,'DDD') from dual
select to_char(sysdate,'D') from dual
select to_char(sysdate,'DAY') from dual
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;
select '''' from dual;
select to_char(sysdate,'SSSSS') from dual;
--从今天零点以后的秒数
<2> ADD_MONTHS 添加月份 得到一个新的日期
select add_months(sysdate,1) from dual;
select add_months(sysdate,-1) from dual;
select trunc(sysdate)-to_date('20050101','yyyymmdd') from dual;
select add_months(sysdate,12) from dual;
一年以后的今天
select add_months(sysdate,-12) from dual;
一年以前的今天
trunc(sysdate) 截取年月日
select sysdate+2 from dual;
数字代表的是天数
两个日期之间的差值代表天数
<3> last_day 某月的最后一天
select last_day(sysdate) from dual;
select add_months(last_day(sysdate)+3,-1) from dual;
本月第3天的日期
<4> months_between 两个日期之间的月数
select months_between(sysdate,'2005-02-01') from dual;
方向 sysdate - '2005-02-01'
select months_between('2005-02-01',sysdate) from dual;
转换函数
to_char 把日期或数字类型变为字符串
select to_char(sysdate,'hh24:mi:ss') from dual;
select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;
select sal,to_char(sal,'L9,999') from emp;
L本地货币
to_number 把字符串变成数字
select to_number('19990801') from dual;
to_date 把字符串变成日期
select to_date('19800101','yyyymmdd') from dual;
select to_char(to_date('19800101','yyyymmdd'),
'yyyy"年"mm"月"dd"日"') from dual;
数学函数
ceil(x) 不小于x的最小整数
ceil(12.4) 13
ceil(-12.4) -12
floor(x) 不大于x的最大整数
floor(12.5) 12
floor(-12.4) -13
round(x) 四舍五入
round(12.5) 13
round(12.456,2) 12.46
trunc(x) 舍去尾数
trunc(12.5) 12
trunc(12.456,2) 12.45
舍去日期的小时部分
select to_char(trunc(sysdate),'yyyymmdd hh24:mi:ss') from dual;
mod(x,n) x除以n以后的余数
mod(5,2) 1
mod(4,2) 0
power(x,y) x的y次方
select power(3,3) from dual;
混合函数
求最大值
select greatest(100,90,80,101,01,19) from dual;
求最小值
select least(100,0,-9,10) from dual;
空值转换函数 nvl(comm,0) 字段为空值 那么就返回0 否则返回本身
select comm,nvl(comm,0) from emp;
comm 类型和 值的类型是 一致的
复杂的函数
decode 选择结构 (if ... elseif .... elesif ... else结构)
要求:
sal=800 显示低工资
sal=3000 正常工资
sal=5000 高工资
只能做等值比较
select sal,decode(sal,800,'低工资',3000,'正常工资',5000,'高工资','没判断')
from emp;
表示如下的if else 结构
if sal=800 then
'低工资'
else if sal =3000 then
'正常工资'
else if sal = 5000 then
'高工资'
else
'没判断'
end if
sal > 800 sal -800 > 0
判断正负
sign(x) x是正 1
x是负 -1
x是0 0
select sign(-5) from dual;
如何做大于小于的比较????
sal<1000 显示低工资 sal-1000<0 sign(sal-1000) = -1
1000<=sal<=3000 正常工资
3000<sal><=5000 高工资
select sal,decode(
sign(sal-1000),-1,'低工资',
decode(sign(sal-3000),-1,'正常工资',
0,'正常工资',1,
decode(sign(sal-5000),-1,'高工资','高工资')
)) as 工资状态 from emp;
一般的情况 decode(x,y1,z1,y2,z2,z3)
if x= y1 then
z1
else if x = y2 then
z2
else
z3
end if
分组函数 返回值是多条记录 或计算后的结果
group by
sum
avg
<1> 计算记录的条数 count
select count(*) from emp;
select count(1) from emp;
select count(comm) from emp; 字段上count 会忽略空值
comm不为空值的记录的条数
统计emp表中不同工作的个数 ????
select count(distinct job) from emp;
select distinct job from emp;
select distinct job,empno from emp;
select job,empno from emp;
得到的效果是一样的,distinct 是消去重复行
不是消去重复的列
<2>group by 分组统计
--在没有分组函数的时候
--相当于distinct 的功能
select job from emp group by job;
select distinct job from emp;
--有分组函数的时候
--分组统计的功能
统计每种工作的工资总额是多少??
select job,sum(sal) from emp
group by job; --行之间的数据相加
select sum(sal) from emp; --公司的工资总额
统计每种工作的平均工资是多少??
select job,avg(sal) from emp
group by job;
select avg(saL) from emp; --整个公司的平均工资
显示平均工资>2000的工作???
<1>统计每种工作的平均工资是多少
<2>塞选出平均工资>2000的工作
从分组的结果中筛选 having
select job,avg(sal) from emp
group by job
having avg(sal) > 2000;
group by 经常和having搭配来筛选
计算工资在2000以上的各种工作的平均工资????
select job,avg(sal) from emp
where sal > 2000
group by job
having avg(sal) > 3000;
一般group by 和 having搭配
表示对分组后的结果的筛选
where子句 --- 用于对表中数据的筛选
<3> max min
select max(sal) from emp;
公司的最高工资
select min(sal) from emp ;
公司的最低工资
找每个部门的最高和最低的工资??
select deptno,max(sal),min(sal) from emp
group by deptno;
找每个工作的最高和最低的工资??
select job,max(sal),min(sal) from emp
group by job;
找每个部门中每种工作的最高和最低的工资??
select deptno,job,max(sal),min(sal)
from emp
group by deptno,job;
select max(sal),min(sal)
from emp
group by deptno,job;
单个字段如果没有被分组函数所包含,
而其他字段又是分组函数的话
一定要把这个字段放到group by中去
发表评论
-
ORACLE ROLLUP和CUBE的使用
2014-09-12 21:29 613ROLLUP,是GROUP BY子句的一种扩展,可以为每个分 ... -
用Oracle的分析函数删除重复的数据
2014-08-13 15:52 712用Oracle的分析函数删 ... -
Oracle分析函数
2014-08-13 15:45 648Oracle分析函数 本文讲述Oracle分析 ... -
Oracle外连接中对非连接条件使用(+)需要注意的地方
2014-03-24 16:33 1005Oracle外连接中对非连接条件使用(+)需要注意的地方 ... -
oracle表分区详解【转】
2014-02-12 17:43 638此文从以下几个方面来整理关于分区表的概念及操作: ... -
shell执行oracle存储过程,获得存储过程返回值【转】
2013-05-08 14:53 4840一个小的etl调度,需要将存储过程执行情况进行返回并控制其后 ... -
SQL server 与Oracle开发比较【转】
2013-04-03 22:03 1554SQL server 与Oracle开发比较 本文档主 ... -
SQL/Oracle取出第 m 条到第 n 条记录的方法
2013-03-17 21:22 1813用一句SQL取出第 m 条到第 n 条记录的方法从Table ... -
SQL Server中的行列转换问题
2013-03-04 14:16 1366SQL Server中的行列转换问题 -
TERADATA解决锁表的方法
2013-02-04 16:16 2412TERADATA的锁表问题一直很头疼,经常早上过来发现任务 ... -
Teradata数据库SQL命令
2013-01-30 15:11 1366HELP 帮助用户了解数据库中各种对象的结构 SHOW 帮 ... -
ETL的一些概念和问题(转)
2012-11-21 11:30 10551. What is a logical data mappi ... -
Teradata数据库中的求字段值的长度函数(即对应SQL中的Length()函数)
2012-06-28 12:57 3512公司的新项目用的是Teradata数据库,之前从未用过这个,所 ... -
用 IBM WebSphere DataStage 进行数据整合:第 3 部分: 实时数据整合
2012-05-15 14:08 933【转载】用 IBM WebSphere DataSta ... -
用 IBM WebSphere DataStage 进行数据整合:第 2 部分
2012-05-15 14:05 1024【转载】用 IBM WebSphere DataSta ... -
用 IBM WebSphere DataStage 进行数据整合:第 1 部分
2012-05-15 14:02 899【转载】用 IBM WebSphere DataSta ... -
(转载)SQL函数说明大全
2012-04-06 15:25 1850觉得该作者总结的很好,所以就转载过来了,想看原文的朋友请点击此 ... -
SQL Server CONVERT() 函数
2012-01-31 15:42 747SQL Server CONVERT() 函数 ... -
inner join on, left join on, right join on
2012-01-29 12:14 884inner join on, left join ... -
SELECT INTO FROM 与 INSERT INTO SELECT
2011-12-06 15:49 12581.INSERT INTO SELECT语句 ...
相关推荐
oracle函数介绍 1 著名函数之单值函数 pdf oracle函数介绍 2 非著名函数之单值函数 pdf oracle函数介绍 3 著名函数之聚合函数 pdf oracle函数介绍 4 非著名函数之聚合函数 pdf oracle函数介绍 5 分析函数简述 ...
oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全
oracle函数大全 oracle函数大全 oracle函数大全
postgresql 兼容 oracle 函数, postgresql 兼容 oracle 函数,postgresql 兼容 oracle 函数, postgresql 兼容 oracle 函数
oracle 函数大全 参考函数 手册 速查 chm格式。。。
Oracle函数.chm
oracle函数大全,oracle函数大全,oracle函数大全,oracle函数大全,
Oracle函数.txt Oracle函数.txt Oracle函数.txt
oracle函数大全.chm oracle函数大全.chm
ORACLE函数.pdfORACLE函数ORACLE函数.pdf
ORACLE函数及其用法ORACLE函数及其用法ORACLE函数及其用法ORACLE函数及其用法ORACLE函数及其用法
oracle函数大全,介绍各自oracle函数1111111111111111111
Oracle自定义函数返回一张表。主要用于生成一张等时间间隔的表数据。
Oracle函数大全 Oracle函数大全 Oracle函数大全Oracle函数大全
Oracle函数学习资料,包含各种常用Oracle函数。
oracle函数大全.doc oracle函数大全.doc oracle函数大全.doc oracle函数大全.doc
Ora9iSQL参考手册 oracle函数大全 分类显示 Oracle函数大全 Oracle函数手册 ORACLE九阴真经 oracle知识库 SQLCodes Oracle错误代码与消息解释 SQL语言参考大全
Ora9iSQL参考手册 oracle函数大全 分类显示 Oracle函数大全 Oracle函数手册 ORACLE九阴真经 oracle知识库 SQLCodes Oracle错误代码与消息解释 SQL语言参考大全
DBA日记(第一部),oracle函数介绍(1) 著名函数之单值函数,oracle函数介绍(2) 非著名函数之单值函数,oracle函数介绍(6) 著名函数之分析函数等
oracle函数大全.pdf oracle函数大全.pdf