AI 助理
备案控制台
开发者社区 数据库 文章 正文

Oracle 10g数据库基础之基本查询语句-中-函数

2017-11-13 1396
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

 Oracle 10g数据库基础之基本查询语句-中-函数

-- 资料参考：张烈  张建中《数据库管理员培训讲义》
函数：

使用函数的目的是为了操作数据

将输入的变量处理，返回一个结果。

变量可以有好多。

传入的变量可以是列的值，也可以是表达式。

函数可以嵌套。

内层函数的结果是外层函数的变量。

单行函数：每一行都有一个返回值，但可以有多个变量。

多行函数：多行有一个返回值。

单行函数的分类：

字符操作函数

数字操作函数

日期操作函数

数据类型转换函数

综合数据类型函数

字符操作函数：
大小写操作函数

Lower,upper,initcap   小写大写首字母大写

字符串操作函数
Concat,length,substr,instr,trim,replace,lpad,rpad
实验12：操作字符串的函数
字符操作函数：

大小写操作函数

Lower,upper,initcap    小写 大写 首字母大写

字符串操作函数

Concat,length,substr,instr,trim,replace,lpad,rpad

该实验的目的是掌握常用的字符串操作的函数.

字符串的大小写操作

SQL> Select lower(ename),upper(ename),initcap(ename) from emp;
SQL>Select lower(ename)  小写字母 ,upper(ename)  大写字母 ,initcap(ename)  首字母大写  from emp;

SQL> select lower(' mf TR') 小写字母, upper('mf TR') 大写字母, initcap('mf TR') 首字母大写 from dual;

Dual是虚表，让我们用表的形式来访问函数的值。

字符串操作函数

Concat,length,substr,instr,trim,replace,lpad,rpad

其它字符串操作函数

concat 将两个字符连接到一起
select ename,job,concat(ename,job) from emp;

求字符串的长度

下面三句话是求字符串的长度，字符串要单引。

select length(' 张三 ') from dual;-- 按照字
select lengthb(' 张三 ') from dual;-- 按字节

select lengthc('张三') from dual;--unicode的长度

substr截取字符串

substr(字符串,m,n),m是从第几个字符开始，如果为负的意思是从后边的第几个开始。N是数多少个，如果不说就是一直到字符串的结尾

SQL> select ename,substr(ename,1,1) "第一个字母",substr(ename,-2) "最后两个字母"from emp;

求子串在父串中的位置 ， 0 表示没有在父串中找到该子串。
SQL> select ename,instr(ename,'A') "A 在第几位 " from emp;

trim 截断字符串和添加字符串的函数
Trim 函数是截掉头或者尾连续的字符，一般我们的用途是去掉空格。
SQL> select trim(leading 'a' from 'aaabababaaa') from dual;
截掉连续的前置的 a

SQL> select trim(trailing 'a' from 'aaabababaaa') from dual;
截掉连续的后置的 a

SQL> select trim(both 'a' from 'aaabababaaa') from dual;
截掉连续的前置和后置的 a

SQL> select trim('a' from 'aaabababaaa') from dual;
如果不说明是前置还是后置就是 both 全截断。

Lpad和rpad字符填充

SQL> select lpad(ename,20,'*') ename,rpad(ename,20,'。') ename from emp;

左填充和右填充，20是总共填充到多少位，*和。是要填充的字符串。

Lpad左填充，rpad右填充，一般的用途是美化输出的结果。

如果位数不足，按照截取后的结果显示，不报错。
select lpad(sal,18,'*') 左填充 18 位星号 ,rpad(sal,25,' 。 ') 右填充 25 位句号 ,sal from emp;

select lpad(sal,30,' ') 左填充 30 位空格 ,rpad(sal,30,' ') 右填充 30 位空格 ,sal from emp;

REPLACE替换字符

SQL> SELECT REPLACE('JACK and JUE','J','xj_') FROM DUAL;

将字符串中的J全部替换位xj_

实验13：操作数字的函数

该实验的目的是掌握常用的关于数字操作的函数.

 ROUND，TRUNC数字操作函数

以小数点位核心，2是小数点后两位，0可以不写，表示取整，-1表示小数点前一位

ROUND是四舍五入：

SQL> SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL;

TRUNC是截断，全部舍弃：

SQL> SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM DUAL;

ceil 取整，上进位，和 trunc 全部去掉正好相反
SQL> select ceil(45.001) from dual;

取绝对值
SQL> select abs(-23.00) from dual;

取余数
SQL> select mod(8.88,2) from dual;

实验14：操作日期的函数

该实验的目的是掌握常用的关于日期操作的函数.

系统日期的操作

日期是很特殊的数据类型，用好了可以提高数据库的性能，而使用不当往往是错误的根源，如果你使用字符型数据来存储日期，就放弃日期特有的计算功能。

函数SYSDATE求当前数据库的时间。

SQL> select sysdate from dual;

日期的显示格式和客户端的配置相关。

查看当前的日期显示格式

SQL> select * from nls_session_parameters

where parameter='NLS_DATE_FORMAT';

col value for a20

代表的含义是凡是列的名称是value的，都按照20个宽度来显示，你想取消该列的定义

col value clear，其中col是column的缩写。你想查看帮助help column即可

alter session set NLS_DATE_FORMAT='yyyy/mm/dd:hh24:mi:ss';

重新设定为我们想要的格式。

select sysdate from dual;
SYSDATE
-------------------
2012/5/16007/05/01:16:32:54

查看系统时间，数据库本身没有时间，它有scn号，和我们的时间不同。

alter session set NLS_DATE_FORMAT='DD-MON-RR';

设定为默认的显示格式

select sysdate from dual;

再次查看，我们发现日期的显示随着客户端的格式变化而变化。

日期的内部存储都是以yyyymmddhh24miss存在数据库中

日期的操作函数
SQL> select round(sysdate-hiredate) days,sysdate,hiredate from emp;

两个日期相减的结果单位为天，往往是带小数点。我们通过函数可以取整。

取两个日期的月间隔

SQL> select months_between(sysdate,hiredate) 两个日期的月间隔,sysdate,hiredate from emp;

六个月过后是哪天 ?

SQL> select sysdate 今天, add_months(sysdate,6) 六个月后  from dual;

SQL> select next_day(hiredate,5) ,hiredate from emp;

当前的日期算起，下一个星期五是哪一天， 这句话你可能运行失败，因为日期和客户端的字符集设置有关系，如果你是英文的客户端，就的用 Friday 来表达，日期是格式和字符集敏感的。如果你是中文的客户端，就的用‘星期五’来表达。

该日期的月底是哪一天。

日期的进位和截取
select hiredate,round(hiredate,'mm') ,round(hiredate,'month') from emp;

select hiredate,round(hiredate,'yyyy') ,round(hiredate,'year') from emp;

select hiredate,trunc(hiredate,'mm') ,trunc(hiredate,'month') from emp;

select hiredate,trunc(hiredate,'yyyy') ,trunc(hiredate,'year') from emp;

数字的进位和截取是以小数点为中心，我们取小数点前或后的值，而日期的进位和截取是以年，月，日，时，分，秒为中心。

数据类型的隐式转换

字符串可以转化为数字和日期。

数字要合法，日期要格式匹配。

select ename,empno from emp where empno='7900';

数字和日期在赋值的时候也可以转为字符串，但在表达式的时候不可以转换。

select ename,empno from emp where ename='123';
select ename,empno from emp where ename=123;
数据类型的显式转换
To_char,to_date,to_number
日期转化为字符串，请说明字符串的格式。

SQL> select ename,to_char(hiredate,'yyyy/mm/dd') from emp;

FM 消除前置的零和空格。

SQL> select ename,to_char(hiredate,'fmyyyy/mm/dd') from emp;

其他格式：year,month,mon,day,dy,am,ddsp,ddspth

格式内加入字符串请双引。

SQL> select to_char(hiredate,'fmyyyy "年" mm "月" dd "日"') from emp;

当前距离零点的秒数 .

select sysdate,to_char(sysdate,'sssss') ss from dual;

数据类型的显式转换

数字转为字符串

格式为9,0,$,l,.

col salary for a30

SQL> select ename,to_char(sal,'9999.000') salary from emp;

SQL> select ename,to_char(sal,'$00099999000.00' ) salary from emp;

SQL> select ename,to_char(sal,'l99,999.000') salary from emp;

SQL> select ename,TO_char(sal,'9G999D99') salary from emp;

9是代表有多少宽度，如果不足会显示成######,0代表强制显示0，但不会改变你的结果。G是千分符，D是小数点。

在数据库中16进制的表达是按照字符串来描述的，所以你想将十进制的数转换为十六进制的数使用to_char函数。

SQL> select to_char(321,'xxxxx') from dual;

其中xxxxx的位数要足够，不然报错，你就多写几个，足够大就可以。

数据类型的显式转换
 To_number,to_date

如果你想将十六进制的数转换为十进制的数请使用to_number函数。

SQL> select to_number('abc32','xxxxxxxx') from dual;

日期是格式和语言敏感的，切记！

SQL> select TO_NUMBER('100.00', '9G999D99') from dual;

G为千分符，D为小数点

RR和yy日期数据类型

select to_char(sysdate,'yyyy') "当前",

to_char(to_date('98','yy'),'yyyy') "yy98",
to_char(to_date('08','yy'),'yyyy') "yy08",
to_char(to_date('98','rr'),'yyyy') "rr98",
to_char(to_date('08','rr'),'yyyy') "rr08" from dual;

结果为

yy是两位来表示年，世纪永远和说话者的当前世纪相同。

RR比较灵活，它将世纪分为上半世纪和下半世纪。如果你处于上半世纪，描述的是0-49，那么就和当前世纪相同，描述的是50-99就是上世纪。如果你处于下半世纪，描述的是0-49，那么是下个世纪，描述的是50-99就是当前世纪。从而可以看出，RR的设计完全为了1990年到2010之间我们的思维习惯而设计的。当我们时间到2050前后，使用起来就非常的别扭。

实验15：操作数据为null的函数

该实验的目的是掌握常用的关于NULL值操作的函数.

综合数据类型函数
NVL (expr1, expr2)

如果expr1为非空，就返回expr1, 如果expr1为空返回expr2，两个表达式的数据类型一定要相同。

NVL2 (expr1, expr2, expr3)

如果expr1为非空，就返回expr2, 如果expr1为空返回expr3

NULLIF (expr1, expr2)

如果expr1和 expr2相同就返回空，否则返回expr1

COALESCE (expr1, expr2, ..., exprn)

返回括号内第一个非空的值。

SQL> select ename,comm,nvl(comm,0) from emp;

有奖金就返回奖金，奖金为空就返回0。

select ename 姓名,sal 工资,comm 奖金,sal+nvl(comm,0) 总工资,nvl2(comm,'工资加奖金','纯工资') "收入类别" from emp;

有奖金就返回‘工资加奖金‘，奖金为空就返回‘纯工资’。

SQL> select ename,nullif(ename,'KING') from emp;
如果员工的名称为 king 就返回空，否则返回自己的名字。

SQL> select ename 姓名,COALESCE(comm,0) "奖金" from emp;

如果有奖金就返回奖金，如果没有奖金就返回0，起个别名叫做"奖金"。

实验16：分支的函数

该实验的目的是掌握分支操作的函数.

 Case语句

9I以后才支持的新特性，说叫语句其实是函数。目的是为了分支。

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

例题：判别job,不同工作的人赋予不同的工资，除了CLERK，SALESMAN，ANALYST以外，其它的人工资不变，将函数的值起一个别名为"处理后数据"。

语句：
SELECT ename, job, sal,
CASE job WHEN 'CLERK' THEN 1.1111*sal
WHEN 'SALESMAN' THEN 2.2222*sal
WHEN 'ANALYST' THEN 3.3333*sal

ELSE sal END "处理后数据"

FROM emp;

Decode函数：

和CASE语句一样都是分支语句，但Decode函数是ORACLE自己定义的，其它数据库可能不支持。

语法如下：

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

例题：判别job,不同工作的人赋予不同的工资，除了CLERK，SALESMAN，ANALYST以外，其它的人工资不变，将函数的值起一个别名为"处理后数据"。

语句：
SELECT ename, job, sal,
decode(job ,'CLERK' , 1.11*sal
,'SALESMAN' , 3.33*sal
,'ANALYST' , 8.88*sal

, sal ) "处理后数据"

FROM emp;

下面的例题是求税率：

不同工资上的税率不同。每2000一个台阶，8000以上一律40%的税。

SQL> SELECT ename, sal,
DECODE (TRUNC(sal/1000, 0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30
, 0.40
)  税率
FROM emp;

不管 CASE 语句还是 DECODE 函数，他们都是单行函数，每一行都有一个返回值。从 ORACLE 角度来讲， DECODE 更好，因为各个版本的数据库都支持，横向来说， CASE 语句更好，因为它是国标，不同的数据库间都认可。
实验17：分组统计函数

该实验的目的是掌握常用的组函数.理解group by的操作.

需要掌握的知识点：

1。组函数

2。分组统计

3。NULL值在组函数中的作用

4。HAVING的过滤作用

5。组函数的嵌套

  组函数

这种函数每次处理多行，给出一个返回值

Avg平均

Sum求和

Max最大

Min最小

Count计数

所有组函数，除了count(*)以外，都忽略null值，count是计数，查看有多少行，count(列)是查看该列有多少非空的行。

例子：

Group by 子句

Having是在结果中再次筛选。Having一定得出现在group by 子句得后面。不能独立存在。

组函数的嵌套注意要使用GROUP BY子句。

巧用DECODE函数，改变排版方式

SQL>select sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",

sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987",
count(ename) " 总人数 " from emp;

《完》



本文转自xjzhujunjie 51CTO博客，原文链接:http://blog.51cto.com/xjzhujunjie/866373


文章标签：
数据库
关系型数据库
SQL
Oracle
关键词：
数据库查询
数据库oracle
Oracle数据库一键上云函数
Oracle数据库一键上云数据库
oracle数据库
科技小能手
目录
相关文章
sunrr
|
25天前
|
SQL Oracle 关系型数据库
Oracle数据库优化方法
【10月更文挑战第25天】Oracle数据库优化方法
sunrr
33 7
sunrr
|
25天前
|
Oracle 关系型数据库 数据库
oracle数据库技巧
【10月更文挑战第25天】oracle数据库技巧
sunrr
26 6
sunrr
|
25天前
|
存储 Oracle 关系型数据库
Oracle数据库优化策略
【10月更文挑战第25天】Oracle数据库优化策略
sunrr
21 5
1436047922066202
|
1月前
|
存储 Oracle 关系型数据库
数据库数据恢复—Oracle ASM磁盘组故障数据恢复案例
Oracle数据库数据恢复环境&故障： Oracle ASM磁盘组由4块磁盘组成。Oracle ASM磁盘组掉线 ，ASM实例不能mount。 Oracle数据库故障分析&恢复方案： 数据库数据恢复工程师对组成ASM磁盘组的磁盘进行分析。对ASM元数据进行分析发现ASM存储元数据损坏，导致磁盘组无法挂载。
1436047922066202
55 1
游客r426lz522voxi
|
Oracle 关系型数据库
Oracle常用函数整理
今天再给大家分享一下Oracle的常用函数。
游客r426lz522voxi
279 0
Oracle常用函数整理
技术小阿哥
|
Oracle 关系型数据库
Oracle常用函数整理
技术小阿哥
976 0
沫沫金
|
Oracle 关系型数据库
Oracle常用函数整理
wm_concat(lie[,',','|'])列转行 rount(lie,2)保留小数点
沫沫金
724 0
sunrr
|
1月前
|
存储 Oracle 关系型数据库
Oracle数据库的应用场景有哪些？
【10月更文挑战第15天】Oracle数据库的应用场景有哪些？
sunrr
171 64
sunrr
|
1月前
|
监控 Oracle 关系型数据库
Oracle数据库性能优化
【10月更文挑战第16天】Oracle数据库性能优化是
sunrr
31 1
1436047922066202
|
2月前
|
Oracle 关系型数据库 数据库
数据库数据恢复—Oracle数据库文件出现坏块的数据恢复案例
打开oracle数据库报错“system01.dbf需要更多的恢复来保持一致性，数据库无法打开”。 数据库没有备份，无法通过备份去恢复数据库。用户方联系北亚企安数据恢复中心并提供Oracle_Home目录中的所有文件，急需恢复zxfg用户下的数据。 出现“system01.dbf需要更多的恢复来保持一致性”这个报错的原因可能是控制文件损坏、数据文件损坏，数据文件与控制文件的SCN不一致等。数据库恢复工程师对数据库文件进一步检测、分析后，发现sysaux01.dbf文件损坏，有坏块。 修复并启动数据库后仍然有许多查询报错，export和data pump工具使用报错。从数据库层面无法修复数据库。
1436047922066202
77 7
数据库数据恢复—Oracle数据库文件出现坏块的数据恢复案例

热门文章

最新文章

  • 1
    oracle11g监听网卡总断（一）
  • 2
    ORACLE union order by
  • 3
    探索ORACLE之RMAN_07 参数文件丢失恢复
  • 4
    解决一例oracle archiver error
  • 5
    误删Oracle数据文件的恢复方法
  • 6
    关于Oracle表及字段的注释 转
  • 7
    oracle10g Error in invoking target 'install' of makefile
  • 8
    oracle express 11g 创建表空间
  • 9
    Oracle 用户、对象权限、系统权限
  • 10
    Script:计算Oracle Streams进程所占用的内存大小
  • 1
    实时计算 Flink版产品使用合集之用于实时同步整个数据库时，该如何配置DB2
    73
  • 2
    数据库-----范式判断
    76
  • 3
    前后端、数据库时间格式化方法
    88
  • 4
    数据库中的数据
    48
  • 5
    实时计算 Flink版产品使用合集之当 SQL Server 源数据库中的数据更新后，CDC 吐出的操作（op）是怎样的
    66
  • 6
    数据库设计规约以及MP代码生成器
    64
  • 7
    MySql数据库级别MD5加密java MD5加密解密工具包
    59
  • 8
    从 Volcano 火山模型到 Pipeline 执行模型，阿里云数据库 SelectDB 内核 Apache Doris 执行模型的迭代
    62848
  • 9
    uniapp中sqlite数据库常用操作的简单封装
    690
  • 10
    理解数据库中的模式
    100

    • 相关课程

    更多
  • 数据库的前世今生
  • 数据库核心概念
  • 从传统数据库到云数据库演进
  • 数据库常见问题排查
  • 数据库及SQL/MySQL基础
  • 高校精品课-西安交通大学 -数据库理论与技术

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 相关实验场景

    更多
  • 使用DAS实现数据库SQL优化
  • 自建数据库迁移到云数据库
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 云原生HTAP数据库，让你的交易和分析一库搞定
  • 快速体验PolarDB开源数据库
  • 使用SQL语句管理索引

    • 推荐镜像

    更多
  • oracle
  • postgresql
  • mysql
    • 下一篇
    无影云桌面