开发者社区> 科技小能手> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介:
+关注继续查看

 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全截断。

Lpadrpad字符填充

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:操作数字的函数

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

 ROUNDTRUNC数字操作函数

以小数点位核心,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,其中colcolumn的缩写。你想查看帮助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为小数点

RRyy日期数据类型

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,不同工作的人赋予不同的工资,除了CLERKSALESMANANALYST以外,其它的人工资不变,将函数的值起一个别名为"处理后数据"

语句:
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,不同工作的人赋予不同的工资,除了CLERKSALESMANANALYST以外,其它的人工资不变,将函数的值起一个别名为"处理后数据"

语句:
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。分组统计

3NULL值在组函数中的作用

4HAVING的过滤作用

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


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

相关文章
Oracle数据库死锁问题的查询与处理
Oracle数据库死锁问题的查询与处理http://www.bieryun.com/3139.html 近来在工作中遇到了oracle数据库死锁问题,下面是转载的问题查询与处理方法,侵删。 一、数据库死锁的现象 程序在执行的过程中,点击确定或保存按钮,程序没有响应,也没有出现报错。
2625 0
Oracle数据库之PL/SQL程序基础设计
一、PL/SQL块结构 前边我们已经介绍了PL/SQL块的结构,再来回顾一下: DECLARE /* * 声明部分——定义常量、变量、复杂数据类型、游标、用户自定义异常 */ BEGIN /* * 执行部分——PL/SQL语句和SQL语句 */ EXCEPTION /*...
675 0
ORACLE 10G中闪回汇总
1、查看当前SCN并且转换为时间 select scn_to_timestamp(dbms_flashback.get_system_change_number)  from dual; select timestamp_to_scn(to_date('20...
745 0
在 Linux x86 上安装 Oracle 数据库 10g
导读:   学习在 RHEL 2.1、RHEL 3 或 SLES 8 上从头安装 Oracle 数据库 10g的基础知识(仅用于评估)   目录   概述   第 1 部分:安装 Linux   RHEL 2.
1032 0
在 Linux x86 上安装 Oracle 数据库 10g_2
如果您按照以上说明安装了 Linux,且内核参数全部采用默认值,则只需在以 root 用户身份登录后执行下命令。 cat >> /etc/sysctl.conf /etc/sysctl.conf kernel.
820 0
在 Linux x86 上安装 Oracle 数据库 10g_1
验证您的安装 如果完成了以上步骤,您应该具备 Oracle 10g 所需的所有程序包和更新。您可以执行以下步骤验证您的安装。 所需内核版本:2.4.21-4.EL(这是 RHEL 3 的基本版本附带的内核版本。
992 0
23704
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载