oracl数据库应用.docx

oracl数据库应用.docx

《oracl数据库应用.docx》由会员分享，可在线阅读，更多相关《oracl数据库应用.docx（19页珍藏版）》请在冰豆网上搜索。

oracl数据库应用

DISTINCT,COUNT

oracle中的distinc关键字和count函数需要经常组合起来使用，例如,如果我们拿到一个仅有员工基本信息的列表，我们希望得到这个公司共有多少个部门。

我们可以这样做：

selectcount（a.deptno）from（selectdistinctdeptnofromscott.emp）a;

但这样做太复杂了,我们可以将discint和count函数用在一起

例如：

selectcount（distinctdeptno）fromscott.emp;

二者效果是一样的。

NVL,NLV2,NULLIF,Coalesce

Oracle中函数以前介绍的字符串处理，日期函数，数学函数,以及转换函数等等，还有一类函数是通用函数。

1.NVL函数

NVL函数的格式如下：

NVL（expr1,expr2）

含义是：

如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

例如：

SQL>selectename,NVL（comm,-1）fromemp;

ENAMENVL（COMM,-1）

——————–————

SMITH-1

ALLEN300

WARD500

JONES-1

MARTIN1400

BLAKE-1

FORD-1

MILLER-1

其中显示-1的本来的值全部都是空值的

2NVL2函数

NVL2函数的格式如下：

NVL2（expr1,expr2,expr3）

含义是：

如果该函数的第一个参数为空那么显示expr3，如果第一个参数的值不为空，则显示expr2。

SQL>selectename,NVL2（comm,-1,1）fromemp;

ENAMENVL2（COMM,-1,1）

——————–—————

SMITH1

ALLEN-1

WARD-1

JONES1

MARTIN-1

BLAKE1

CLARK1

SCOTT1

上面的例子中。

凡是结果是-1的原来都不为空，而结果是1的原来的值就是空。

3.NULLIF函数

NULLIF（exp1,expr2）函数的作用是如果exp1和exp2相等则返回空（NULL），否则返回第一个值。

下面是一个例子。

使用的是oracle中HRschema，如果HR处于锁定，请启用

这里的作用是显示出那些换过工作的人员原工作，现工作。

SQL>SELECTe.last_name,e.job_id,j.job_id,NULLIF（e.job_id,j.job_id）“OldJobID”

FROMemployeese,job_historyj

WHEREe.employee_id=j.employee_id

ORDERBYlast_name;

LAST_NAMEJOB_IDJOB_IDOldJobID

————————————————–——————–——————–——————–

DeHaanAD_VPIT_PROGAD_VP

HartsteinMK_MANMK_REPMK_MAN

KauflingST_MANST_CLERKST_MAN

KochharAD_VPAC_MGRAD_VP

KochharAD_VPAC_ACCOUNTAD_VP

RaphaelyPU_MANST_CLERKPU_MAN

TaylorSA_REPSA_MANSA_REP

TaylorSA_REPSA_REP

WhalenAD_ASSTAC_ACCOUNTAD_ASST

WhalenAD_ASSTAD_ASST

可以看到凡是employee。

job_id和job_histroy.job_id相等的，都会在结果中输出NULL即为空，否则显示的是employee。

job_id

4.Coalesce函数

Coalese函数的作用是的NVL的函数有点相似，其优势是有更多的选项。

格式如下：

Coalesce（expr1,expr2,expr3…..exprn）

Coalesce是这样来处理这些参数的。

如果第一个参数为空，则看第二个参数是否是空，否则则显示第一个参数，如果第二个参数是空再看第三个参数是否为空，否则显示第二个参数，依次类推。

这个函数实际上是NVL的循环使用，在此就不举例子了。

正则表达式,TO_CHAR

正则表达式是很多编程语言中都有的。

可惜oracle8i、oracle9i中一直迟迟不肯加入，好在oracle10g中终于增加了期盼已久的正则表达式功能。

你可以在oracle10g中使用正则表达式肆意地匹配你想匹配的任何字符串了。

所谓正则表达式是对于字符串进行匹配的一种模式。

举个例子来说字符串’^198[0-9]$’可以匹配‘1980-1989’，也即80后出生的年份。

如果希望统计出公司那些员工是80后的，就可以使用如下的SQL语句：

select*fromempwhereregexp_like（to_char（birthdate,’yyyy’）,’^198[0-9]$’）;

这里用到了regexp_like和to_char函数。

这里在正则表达式中用到的^、$、[0-9]都被称为元数据（metacharacter），正则表达式都是由多元表达式组成的。

在这里，^表示一个字符串的开头，$表示一个字符换的结尾，因此^198表示以198开头的字符串，而[0-9]$则表示以0-9的数字结尾的字符串。

因此整体上’^198[0-9]$’就能匹配所有1980-1989的字符串。

正则表达式中常用到的元数据（metacharacter）如下：

^匹配字符串的开头位置。

$匹配字符串的结尾位置。

*匹配该字符前面的一个字符0次，1次或者多次出现。

例如52*oracle可以匹配5oracle,52oracle,522oracle,5222oracle等等。

+匹配该字符前面的一个字符1次或者多次出现。

例如52+oracle可以匹配52oracle,522oracle,5222oracle等等

?

匹配该字符前面的一个字符0次或1次或者多次出现。

例如52?

oracle只能匹配5oracle,52oracle等等

{n}匹配一个字符串n次，n为正整数。

例如：

hel{2}o所匹配的是hello

{n,m}匹配一个字符串至少n次，至多m次。

其中n和m都是整数。

.匹配除了null之外的任何单个字符串

（pattern）这个是用来匹配指定模式的一个子表达式

x|y匹配x或者y，其中x和y是一个或者多个字符

[abc]匹配括号中的任意一个字符。

例如：

[ab]bc可以匹配abc和bbc

[a-z]匹配指定范围内的任意字符串。

例如[A-G]hi可以匹配Ahi至Ghi

[:

:

]指定一个字符类，可以匹配该类中的任意字符这里的字符类包括：

[:

alphanum:

]可以匹配字符0-9、A-Z、a-z

[:

alpha:

]可以匹配字符A-Z、a-z

[:

blank:

]可以匹配空格或者tab键

[:

digit:

]可以匹配数字0-9

[:

gragh:

]可以匹配非空字符

[:

punct:

]可以匹配.,”‘等标点符号。

[:

upper:

]可以匹配字符A-Z

[:

lower:

]可以匹配字符a-z

这里列出的是一些常见的正则表达式中的元数据。

更多的正则表达式的内容请参照oracle官网上的正则表达式的相关内容

关于orace中的正则表达式只能通过oracle特意为正则表达式设计的4个函数来使用。

这4个函数分别是：

regexp_like,regexp_instr,regexp_replace,regexp_substr。

关于这4个函数的具体用法，会在稍后介绍，这里简单说一下：

regexp_like（x,pattern）当x能正确匹配字符串时返回true。

regexp_instr（x,pattern）在x中尝试匹配pattern，并返回匹配的位置。

regexp_replace（x,pattern,replacestring）在x中尝试匹配pattern,并将其替换成replacestring。

regexp_substr（x,pattern）返回x中匹配pattern的一个字符串。

前面介绍了oracle中正则表达式构成的元数据，但仅仅知道元数据是不够的，oracle中的正则表达式是结合其特定的4个函数使用的，其中用的最多的就是现在要介绍的的这个函数：

regexp_like

regexp_like函数的基本构成是：

regexp_like（x,pattern[,match_option]）

其基本功能是在x中查找pattern，如果能找到返回true否则返回false,这里的可选参数match_option可以有如下几种形式：

‘c’表明进行匹配时区分大小写（这也是默认选项）。

‘i’表明在匹配时不区分大小写。

‘n’表明允许使用匹配任何字符串的元数据，即’.'。

‘m’将x作为一个包含多行的字符串。

以下是两个关于regexp_like的例子：

这个例子是寻找员工中的80后的员工。

select*fromempwhereregexp_like（to_char（birthdate,’yyyy’）,’^198[0-9]$’）;

这个例子是寻找名字是以’M'或者’m'打头的那些员工的名字

select*fromempwhereregexp_like（ename,’^j’,'i’）

前面我们介绍了oracle中正则表达式的基本元字符和常用的两个函数regexp_like和regexp_instr。

这里我们再继续介绍剩下的两个正则表达式函数regexp_replace和regexp_substr。

regexp_replace函数和regexp_substr函数的功能类似与字符函数replace和substr,只是这里的应用更加灵活，可以一下子匹配一系列的结果，而原来的函数只能一下子匹配一个固定的字符换。

具体来讲：

regexp_replace的意义是找到于给定模式匹配的字符串并用其他的字符串来替代。

其原型是：

regexp_replace（x,pattern[,replace_string[,start[,occurence[match_option]]]]）

每个参数的意思分别是：

x待匹配的函数

pattern正则表达式元字符构成的匹配模式

replace_string替换字符串

start开始位置

occurence　匹配次数

match_option　匹配参数，这里的匹配参数和regexp_like是完全一样的，可参考前面的一篇文章。

举例来讲：

selectregexp_replace（’helloeverybody,mayIhaveyourattentionplease?

’,'b[[:

alpha:

]]{3}’,'one’）fromdual将会返回结果：

helloeveryone,mayIhaveyourattentionplease?

而regexp_substr函数的意义找出与给定模式匹配的字符串并返回，

其原型是：

regexp_substr（x,pattern[,start[,occurence[match_option]]]）

这里各参数的意义与前面的函数regexp_replace的含义是一样做的。

regexp_substr的例子如下：

selectregexp_substr（’Iloveoracleverymuch’,'o[[:

alpha:

]]{5}’）fromdual;

这里将会匹配出结果：

oracle来。

这也是这个函数的返回结果。

和其他的关系型数据库一样，oracle中也能进行一些隐式的数据转换，这对我们写SQL语句有非常用，我们可以不必麻烦地手动转化很多类型的字符。

虽然前面我们介绍了一些使用例如to_char,to_date的函数进行强制转换的方法，但是隐式转换也还是不错的。

Oracle可以隐式地进行一些变量类别之间转化，例如从字符串转换到数值,看下面的例子。

SQL>selectename,salfromempwheresal=‘1100′;

ENAMESAL

——————–———-

SMITH1100

ADAMS1100

这里用了对员工的工资进行了选择，我们明知道员工的工资是数值型的，但我们故意把他写成了字符串型的。

结果oracle仍然得到了正确的结果。

这说明oracle进行了隐式的从字符串到数值直接的转换。

再比如下面的例子。

SQL>Selectlast_day（’26-4月-08′）fromdual;

LAST_DAY（’

———-

30-4月-08

这里我们的last_day函数本来是需要提供一个日期类型的参数，我们故意提供了一个字符串类型的参数。

但oracle仍然给我们返回了正确的结果，这说明oracle内部进行了从字符串到日期类型的隐式转换。

学要说明的是，如果这个例子在你的机器上没有成功的执行，那很又能是你的默认日期格式和这里的不同，如果你不知道你的日期格式的话，你可以用selectsysdatefromdual这条SQL语句返回的结果来查看你的机器到底是什么日期格式。

一般这个和NLS_lang参数的值有关

这两个例子都说明了oracle内部确实能进行某些隐式的函数转换。

下面是oracle中隐式转换的一般情况。

从

到

Varchar2orChar

Number

Varchar2orChar

Date

Number

Varchar2

Date

Varchar2

需要注意的就是从Varchar2、char到date的隐式转换过程中，必须保证其格式是本机的默认时间格式。

除了隐式转换以外，oracle还提供了更为灵活的数据类型的显示转换，这种转换方式更为灵活。

nvl

to_char

to_date

aa_months

to_char

你可以使用selectename,hiredate,salfromempwheredeptno=10;显示信息，可是，在某些情况下，这个并不能满足

你的需求。

问题：

日期是否可以显示时/分/秒

SQL>selectename,to_char（hiredate,'yyyy-mm-ddhh24:

mi:

ss'）fromemp;

问题：

薪水是否可以显示指定的货币符号

SQL>

yy：

两位数字的年份2004-->04

yyyy：

四位数字的年份2004年

mm：

两位数字的月份8月-->08

dd：

两位数字的天30号-->30

hh24：

8点-->20

hh12：

8点-->08

mi、ss-->显示分钟\秒

9：

显示数字，并忽略前面0

0：

显示数字，如位数不足，则用0补齐

.：

在指定位置显示小数点

：

在指定位置显示逗号

$：

在数字前加美元

L：

在数字前面加本地货币符号

C：

在数字前面加国际货币符号

G：

在指定位置显示组分隔符、

D：

在指定位置显示小数点符号（.）

分析函数

over（Partitionby...）一个超级牛皮的ORACLE特有函数。

天天都用ORACLE，用了快2年了。

最近才接触到这个功能强大而灵活的函数。

真实惭愧啊！

oracle的分析函数over及开窗函数

一：

分析函数over

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是

对于每个组返回多行，而聚合函数对于每个组只返回一行。

下面通过几个例子来说明其应用。

1：

统计某商店的营业额。

    date    sale

    1        20

    2        15

    3        14

    4        18

    5        30

  规则：

按天统计：

每天都统计前面几天的总额

  得到的结果：

  DATE  SALE    SUM

  -------------------

  1    20      20        --1天        

  2    15      35        --1天＋2天        

  3    14      49        --1天＋2天＋3天        

  4    18      67        .       

  5    30      97        .

2:

统计各班成绩第一名的同学信息

  NAME  CLASSS                 

  -------------------------------- 

  fda  1    80              

  ffd  1    78              

  dss  1    95              

  cfe  2    74              

  gds  2    92              

  gf    3    99              

  ddd  3    99              

  adf  3    45              

  asdf  3    55              

  3dd  3    78          

  通过：

  --

  select*from                                                

  （                                                   

  selectname,class,s,rank（）over（partitionbyclassorderbysdesc）mmfromt2

  ）                                                   

  wheremm=1 

  --

  得到结果：

  NAME  CLASSS                MM                                                           

  ------------------------------------------------------ 

  dss  1    95              1               

  gds  2    92              1               

  gf    3    99              1               

  ddd  3    99              1       

  注意：

  1.在求第一名成绩的时候，不能用row_number（），因为如果同班有两个并列第一，row_number（）只返回一个结果       

  2.rank（）和dense_rank（）的区别是：

    --rank（）是跳跃排序，有两个第二名时接下来就是第四名

    --dense_rank（）l是连续排序，有两个第二名时仍然跟着第三名

3.分类统计（并显示信息）

  A  B  C               

  -------------------------- 

  m  a  2               

  n  a  3               

  m  a  2               

  n  b  2               

  n  b  1               

  x  b  3               

  x  b  2               

  x  b  4               

  h  b  3 

  selecta,c,sum（c）over（partitionbya）fromt2           

  得到结果：

  A  B  C      SUM（C）OVER（PARTITIONBYA）    

  ----------------------------------- 

  h  b  3      3                

  m  a  2     4                

  m  a  2    4                

  n  a  3      6                

  n  b  2      6                

  n  b  1      6                

  x  b  3      9                

  x  b  2      9                

  x  b  4      9                

  如果用sum，groupby则只能得到