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


相关文章
|
7天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库(一):装上去,飞起来!
【Oracle】玩转Oracle数据库(一):装上去,飞起来!
44 7
|
15天前
|
关系型数据库 MySQL 数据库
轻松入门MySQL:精准查询,巧用WHERE与HAVING,数据库查询如虎添翼(7)
轻松入门MySQL:精准查询,巧用WHERE与HAVING,数据库查询如虎添翼(7)
|
7天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库(七):RMAN恢复管理器
【Oracle】玩转Oracle数据库(七):RMAN恢复管理器
35 5
|
2天前
|
SQL 存储 Oracle
关系型数据库查询数据的语句
本文介绍了关系型数据库中的基本SQL查询语句,包括选择所有或特定列、带条件查询、排序、分组、过滤分组、表连接、限制记录数及子查询。SQL还支持窗口函数、存储过程等高级功能,是高效管理数据库的关键。建议深入学习SQL及相应数据库系统文档。
6 2
|
7天前
|
存储 SQL Oracle
【Oracle】玩转Oracle数据库(二):体系结构、存储结构与各类参数
【Oracle】玩转Oracle数据库(二):体系结构、存储结构与各类参数
32 7
|
16天前
|
缓存 监控 数据库
优化数据库查询性能的八大技巧
在今天的互联网时代,数据库是许多应用程序的核心组件之一。优化数据库查询性能是提升应用程序整体性能的关键。本文介绍了八种有效的技巧,帮助开发人员提高数据库查询性能,从而提升应用程序的响应速度和用户体验。
|
15天前
|
SQL 数据可视化 关系型数据库
轻松入门MySQL:深入探究MySQL的ER模型,数据库设计的利器与挑战(22)
轻松入门MySQL:深入探究MySQL的ER模型,数据库设计的利器与挑战(22)
|
15天前
|
存储 关系型数据库 MySQL
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
|
17天前
|
存储 关系型数据库 MySQL
数据库字符编码MySQL中使用UTF-8还是UTFB4
数据库字符编码MySQL中使用UTF-8还是UTFB4
20 0
|
20天前
|
SQL 关系型数据库 MySQL
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(8.0版本升级篇)
【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南(8.0版本升级篇)
94 0