备案控制台
探索云世界
开发者社区 数据库 文章 正文

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

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

 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数据库
科技小能手
目录
相关文章
SarPro
|
13天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库（一）：装上去，飞起来！
【Oracle】玩转Oracle数据库（一）：装上去，飞起来！
SarPro
54 7
穆雄雄.
|
30天前
|
Oracle 关系型数据库 数据库
Oracle数据库基本概念理解（3）
Oracle数据库基本概念理解（3）
穆雄雄.
18 2
SarPro
|
13天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库（七）：RMAN恢复管理器
【Oracle】玩转Oracle数据库（七）：RMAN恢复管理器
SarPro
40 5
穆雄雄.
|
30天前
|
Oracle 关系型数据库 数据库
Oracle数据库基本概念理解（2）
Oracle数据库基本概念理解（2）
穆雄雄.
13 1
1941623231718325
|
5天前
|
Oracle 算法 关系型数据库
Oracle常用系统函数之数字类函数：数字的魔术师
【4月更文挑战第19天】Oracle数据库中的数字类函数是数字处理的魔术师，包括`ROUND`（四舍五入），`CEIL`和`FLOOR`（向上/下取整），以及`ABS`（计算绝对值）。还有`MOD`、`TRUNC`和`POWER`等函数，提供求余数、截断和计算幂的功能。熟练运用这些函数能提升数据管理效率，让处理数字变得更简单、有趣。
1941623231718325
10 1
1941623231718325
|
5天前
|
存储 Oracle 关系型数据库
Oracle的模式与模式对象：数据库的“城市规划师”
【4月更文挑战第19天】在Oracle数据库中，模式是用户对象的集合，相当于数据库的城市规划，包含表、视图、索引等模式对象。模式对象是数据存储结构，如表用于存储数据，视图提供不同查看角度，索引加速数据定位。良好的模式与模式对象设计关乎数据效率、安全和稳定性。规划时需考虑业务需求、性能、安全和可扩展性，以构建高效数据库环境，支持企业业务发展。
1941623231718325
11 0
SarPro
|
13天前
|
存储 SQL Oracle
【Oracle】玩转Oracle数据库（二）：体系结构、存储结构与各类参数
【Oracle】玩转Oracle数据库（二）：体系结构、存储结构与各类参数
SarPro
35 7
穆雄雄.
|
30天前
|
Oracle 关系型数据库 数据库
Oracle数据库基本概念理解（1）
Oracle数据库基本概念理解（1）
穆雄雄.
13 1
cuicuicuic
|
30天前
|
Oracle 关系型数据库 MySQL
Seata常见问题之oracle 数据库 报 just support mysql如何解决
Seata 是一个开源的分布式事务解决方案，旨在提供高效且简单的事务协调机制，以解决微服务架构下跨服务调用（分布式场景）的一致性问题。以下是Seata常见问题的一个合集
cuicuicuic
53 0
游客rbtl3kntj7oro
|
1月前
|
SQL 存储 关系型数据库
sql数据库查询语句大全
sql数据库查询语句大全
游客rbtl3kntj7oro
21 0

热门文章

最新文章

  • 1
    成都晨云信息技术完成阿里云PolarDB数据库产品生态集成认证
  • 2
    一文搞懂数据库中的“锁”（图文详解）
  • 3
    【C/C++ 数据库 sqlite3】SQLite C语言API返回值深入解析
  • 4
    Perl 教程 之 Perl 数据库连接 1
  • 5
    阿里云数据库服务器价格表，数据库创建、连接和使用教程
  • 6
    在业务处理分析一体化的背景下，开发者如何平衡OLTP和OLAP数据库的技术需求与选型？
  • 7
    在Python Web开发过程中：数据库与缓存，MySQL和NoSQL数据库的主要差异是什么？
  • 8
    轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
  • 9
    【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（8.0版本升级篇）
  • 10
    MySQL数据库，可以使用二进制日志（binary log）进行时间点恢复
  • 1
    Oracle数据守卫（DG）：数据的“守护者”与“时光机”
    10
  • 2
    Oracle 12c的内存列存储：数据的“闪电侠”
    14
  • 3
    Oracle 12c的多重索引：数据的“多维导航仪”
    8
  • 4
    Oracle 12c的自动数据优化（ADO）与热图：数据管理的“瘦身”与“透视”艺术
    13
  • 5
    Oracle 12c多租户架构：数据管理的“摩天大楼”
    12
  • 6
    Oracle数据泵：数据迁移的魔法棒
    5
  • 7
    Oracle中的触发器与序列：自增列的魔法组合
    10
  • 8
    Oracle的PL/SQL游标属性：数据的“导航仪”与“仪表盘”
    27
  • 9
    Oracle的PL/SQL循环语句：数据的“旋转木马”与“无限之旅”
    10
  • 10
    Oracle的PL/SQL条件控制：数据的“红绿灯”与“分岔路”
    12

    • 相关课程

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

    • 相关电子书

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

    • 相关实验场景

    更多
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 云原生HTAP数据库，让你的交易和分析一库搞定
  • Excel文件转存到RDS数据库
  • 快速体验PolarDB开源数据库
  • 使用SQL语句管理索引
  • SQLServer数据库的创建

    • 推荐镜像

    更多
  • oracle
  • mysql
  • arch4edu
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）