oracle学习课件 计算机技术第05章

第5章使用SQL语言操作管理Oracle数据库

课程描述

SQL语言

SQL*Plus

数据库查询

数据类型

常用函数

DML命令

5.1SQL语言概述

□SQL是一种面向数据库的通用数据处理语言

规范，能完成以下几类功能：提取查询数据,

插入修改删除数据，生成、修改和删除数据

库对象，数据库安全控制，数据库完整性及

数据保护控制。

口数据库对象包括表、视图、索引、同义词、

簇、触发器、函数、过程、包、数据库链、

快照等（表空间、回滚段、角色、用户）。

数据库通过对表的操作来管理存储在其中的

数据。

SQL体系结构

□SQL数据库的体系结构也是三级结构，但术语与传统关

系模型术语不同，在SQL中，关系携式称为“基本表”，

存储模式称为“存储文件”，子模式称为“视图”，

元组称“行”，属性称“列”。

□SQL数据库体系的结构要点如下：

(1)一个SQL数据库是表的汇集。

(2)一个SQL表由行集构成，行是列的序列，每列对应

一个数据项。

(3)表或者是基本表，或者是视图。基本表是实际存

储在数据库中的表，视图由是由若干基本表或其他视

图构成的表的定义。

(4)一个基本表可以跨一个或多个存储文件,一个存

储文件也可存放一个或多个基本表。存储文件与物理

文件对应。

(5)用户可以用SQL语句对表进行操作，包括视图和基

术表。

(6)SQL的用户可以是应用程序，也可以是终端用户。

SQL组成

1、数据定义(SQLDDL)用于定义SQL模式、基

本表、视图和索引的创建和撤消操作；

2、数据操纵(SQLDML)数据操纵分成数据查询

和数据更新两类。数据更新又分成插入、删除、

和修改三种操作；

3、数据控制(SQLDCL)包括对基本表和视图的

授权，完整性规则的描述，事务控制等内容；

4、嵌入式SQL的使用规定，涉及到SQL语句嵌入

在宿主语言程序中使用的规则。

SQL*Plus编辑器的编辑命令

在SQL*Plus中键入SQL命令，将在缓冲区中保留最后一次

执行的命令（一行或多行）。

SQL*PLUS关键字（以下命令以“CR”结束）

@、#、/、AcceptsAppendsBreak、

Btitle.Change、Clear、Column>

Compute、ConnectsCODV>Define、

Del.Describe,DisconnectsDocuments

Edit、Exit、Get、Help、

Host、Input、List、Newpacie、

Pause>Quit、Remark、Run、

SaveySet,Show、Spool、

Start、Timina,Ttitle.Undefine、

SQL*Plus编辑器的编辑命令

命令缩写解释

/执行语句

RUNR运行SQL缓冲区命令

ListL列出缓冲区内容

APPENDtextAtext行尾增力口text

CHANGEC/old/new在当前行中将old换为/new

CHANGEC/text/在当前行中删除text

CLEARBufferCLBuff清除缓冲区

SAVE将SQL命令存入文件

DELDEL(DELn)删除当前行（第n行）

INPUT1增加一行

INPUTtext/text增加有text组成的行

GET将文件装入缓冲区

S77U?7或@执行命令文件

SQL*Plus编辑器的编辑命令

通过SQLPLUS发送的最后一次SQL语句都会保存在SQL

PLUS的一个数据缓冲区内，而SQLPLUS命令不包括在内,

可以通过编辑命令进行编辑。

(1)/(R)

重新执行一下sql语句。

SQL<selectcount(*)fromstudents

SQL</

SQL*Plus编辑器的编辑命令

(2)list

列出缓冲区中的内容。

SQL>1

1select*

2fromstudents

3*whereSex=f

1,2,3代表行号，带*号代表当前行

SQL>12/列出第二行缓冲区内容，同时第二行变成当前行

SQL>112/列出第一到第二行缓冲区里的内容

SQL>1*/列出当前行，*表示当前行

SQL>12*/列出从第二行到当前行缓冲区的内容

SQL>12last/列出从第二行到最后一行

SQL*Plus编辑器的编辑命令

(3)input(4)APPEND

在当前行后插入新行在当前行行尾增加内容

SQL>iandage<20

SQL>12

SQL>1

SQL>atableb

1select*2*fromstudentstableb

2fromstudents

3whereSex=f

4*andage<20

(5)deln

n命令用于删除第n行指令，不指定为当前行

SQL>del

SQL>del2

SQL*Plus编辑器的编辑命令

(6)change

用于修改替换当前语句中的字符

SQL>1SQL>C/<20/<19

1select*SQL>1

2fromstudents

3whereSex二f1select*

4*andage<202fromstudents

3whereSex='f'

4*andage<19

SQL*Plus编辑器的编辑命令

(7)save

□SQL命令存盘

SQL>save文件名

□若文件存在，可以替换

SQL>save文件名replace

□追加在文件后面，保留原有文件名

SQL>save文件名append

□不指定文件名，为命令文件，文件名为sql

SQL*Plus编辑器的编辑命令

(8)start

执行命令文件，默认文件名为sql

SQL>start文件名

SQL>@文件名

(9)clear

清空当前屏幕

SQL>clearscreen

SQL*Plus环境参数

(1)set

环境项设置命令

SQL>set环境项设置内容

SQL>setlinesize1000/设置屏幕的现实宽度为1000,

默认为100

(2)show

查看当前系统变量

SQL>showuser

SQL>showall

SQL>showerror

SQL*Plus环境参数

对环境设置后，退出后再一次进入时，这些环境设置又回到了默认值，想要

进次入先进前设入置好后的环，再境，进就入必此须文使牖用文彝件翳将置设好置的好环的环境境保保存存起起来来,，下

(3)store

保存环境设置

SQL>storesetenvironment,cmd

注意：environment是可以改变的，可以是其它名字只要后缀名不变即可。

当再次登录进入时，只要运行这个文件即可

environment,cmd或者startenvironment,cmd

例如：

SQL>showpause

PAUSE为OFF

SQL>setpauseon

SQL>showpause

PAUSE为ON并设为〃“

SQL>storeset2.cmd

已创建文件2.cmd

SQL>exit

假脱机输出

所谓假脱机输出，就是将sql*plus的输出信息保存到一个数据文件

中。可以采用菜单项，也可以采用命令spool来实现

(1)假脱机输出

□使用sql*plus中的“文件”■»“假脱机”

□使用spool命令

SQL>spool文件名

SQL>spoole:/LmdOra.1is或

SQL>spoole:/LmdOra

(2)停止假脱机

停止假脱机可以采用命令spooloff或者spoolout

□spooloff:关闭输出文件

□spoolout:关闭输出文件并打印输出。

联机帮助

(1)help

获取命令帮助

□SQL>help/显示所有命令的帮助信息

□SQL>help命令名/显示某个具体命令的帮助信息

SQL>helplist

(2)describe(desc)

快速查询表及所有表列的概要，功能如下：

□列出表结构

SQL>desc表名

□获取有关函数、过程及包的描述

SQL>desc函数名(过程名&包名)

注：使用describe获取信息，所查找的对象必须存在

数据库查询

查询语句的基本结构是由SELECT子句、FROM子句、

WHERE子句德组成的查询块。

数据查询语句的格式

SELECT{*|table.*|[table.]field[Jtable.]field2[9...]]}[AS

aliasl[,alias2[,.・・]]]

FROMtableexpression

[WHERE...]

[GROUPBY...]

[HAVING...]

[ORDERBY...

数据库查询

基本SELECT语句

SELECT'{[[DISTINCT]column\expression{alias\^

在语法中：

*选择所有的列

DISTINCT禁止重复

这择指定的字段或表达式

column\expression

alias给所选择的列不同的标题

table指定包含列的表

简单查询

□SELECT子句

查询语句的主关键字，在该子句中，通过指定列名来确定

表的哪些列值出现在查询结果中。

SELECTsno9sname9dno

FROMstudent

WHEREsex=，女';

select子句后的查询输出列表项可以出现列名，常量，函数

以及四则运算。

简单查询

□WHERE子句

从表中找出满足条件的记录。

例：SELECTsname,birthdayFROMstudent

WHEREsex=6男'andbirthday

BETWEEN，01-1月-19805AND'3L12月-1983，；

WHERE子句后的条件表达式，参与运算的值可以包括常

量、系统函数及FROM子句中所声明表中的列名。

在查询条件中可以使用集合运算符。

伊J:SELECTsname,telFROMDEP

WHEREdnameINC计算机系'J外语系'J法律系'）；

简单查询

□ORDERBY条件子句

排序输出。将查询结果以排序的方式输出。

SELECTfieldlistFROMtable

WHEREselectcriteria

ORDERBYfield[ASC|DESC][,field![ASC|DESC][,

默认顺序是升序ASC,如果降序，指定DESC。

排序项可以是列名，表达式，别名，或输出项的序号。

可以指定多个排序项，用逗号隔开。

ORDERBY的排序项可以不出现在SELECT后，但如果包

含DISTINCT时，ORDERBY后的排序项必须是SELECT

后的列名。

简单查询

例，

在职工表中emp,查询职工的姓名、年龄、工资和部门,

按部门依次输电，同一部门的职工按年龄由大到小输出，

年龄相同按工资由低到高输出。

SELECTdeptno,ename,age,sal

FROMemp

ORDERBYdeptno,ageDESC,sal;

简单查询

□GROUPBY条件子句

该子句指定用于分组的表达式，该表达式的值相同的记录

被分为一组。用于在执行查询语句的统计工作。分组后，

组南薮再对每一小组进行统并。

SELECTfieldlist

FROMtable

WHEREcriteria

GROUPBYgroupfieldlist

例：查询每个系的学生人数，输出系号，人数

SELECTdno,count(*)

FROMstudent

GROUPBYdno;

*只有出现在group㈢子句中的列名才能与组函数并列出现

在select子句输出项中。

简单查询

□GROUPBY子句中可以指定多个用于分组的表达式项，

按第一项分组后，每组再按第二项分，…，组函数以最小

的组为单位进行统计。

例：查询201课程最高成绩和最低成绩，但系中男，女分开

统计

SELECTdno,sex,MAX(grade),MIN(grade)

FROMstudent,sc

WHEREcno=201andsc.sno=student.sno

GROUPBYdno,sex;

简单查询

□HAVING条件子句

与GROUPBY子句配合使用。当选择是基于统计的结果

时，使用HAVING.

SELECTfieldlist

FROMtable例：查询学分不到10的同学

WHEREselectcriteriaselectsno,sum(credit)

fromsc,course

GROUPBYgroupfieldlist

wheregrade>60and

HAVINGgroupcriteriao=o

groupbysno

havingsum(credit)<10;

简单查询

□WHERE和HAVING的区别

WHERE是查询开始时从表中选择记录的条件

HAVING是依据分组统计的结果所进行的组的选择

□出现在查询语句中的6个基本子句（顺序）:

必须：SELECT

FROM在一个查询语句

可选：WHERE中，子句出现的

顺序

GROUPBY

HAVING

ORDERBY

部分函数

□AVG(n)返回n的平均值。

例，查询部门20职工的平均工资

SelectAVG(sal)fromemp

Wheredeptno=6205;

□COUNTC|expr})返回查询到的行数。

参数为*,返回所有的行，包括空行。

象数为expr,返回expr为非空的行数。

例，查询计算机系的学生总数

selectCOUNT(*)fromstudent,dep

wheredname=，并算机系'AND

dep.dno=student.dno;

部分函数

□MAX(expr)返回expr最大值。

表达式是字符型，则返回VARCHAR2,数据库字

符集中排序最高的最大；

表达式是日期型，则返回日期，最近的日期最大;

表达式是数字型，则返回数字。

□MIN(expr)返回expr最小值，与MAX函数相反。

运算符与表达式

口SQL语句中的表达式由一个或多个值、运算符和SQL函数组

成。表达式值的类型取决于参与运算的值的类型。

□在SQL语句中可以使用表达式的地方有：

•SELECT语句的查询列表中。

•WHERE和HAVING子句的条件中。

•ORDERBY子句中。

•INSERT语句的VALUES子句中。

•UPDATE语句的SET子句中。

运算符与表达式

ALL,ANY

用一个值与集合中的所有元素进行比较，比较条件都

满足时为真。

(ALL,ANY的前面必须带有一个比较运算符作为比较

条件)

例：select*fromempwheresal>ALL(

selectsalfromempwheredeptno=30);

注意：！=ALL相当于NOTIN,=ANY相当于IN

运算符与表达式

BETWEENaANDb

判断一个值是否在a和b之间

例：SELECT*FROMemp

WHEREsalBETWEEN1000AND2000;

NOTBETWEENaANDb义词。

EXISTS

用来判断子查询的返回结果，如果至少有一行返回，就为

真。

例:select*fromdepwhereexists(

select*fromstudentwheredno=dep.dno);

运算符与表达式

ISNULL

用于测试列值是否为空，列值为空时为真

例:SELECT*FROMempWHEREMGRISNULL;

ISNOTNULL测试空值，列值不为空时为真。

LIKE

用于包含匹配字符的字符串比较，实现模糊匹配。

Oracle提供了两个字符匹配符号：%,_

%:代表任意多个字符，不能匹配一个空值。

代表任意一'个字符。

例：SELECT*FROMempWHEREenameLIKE'SM%';

匹配符只能出现在LIKE之后的字符串中，如果在LIKE之

前，则被看作普通字符

运算符与表达式

□逻辑运算符

AND（与）、OR（或）、NOT（非）

例，

SELECT*FROMemp

WHEREjob—CLERK'ANDdeptno=20;

SELECT*FROMemp

WHEREjob=6CLERK5ORjob=6MANAGER5;

SELECT*FROMemp

WHERENOT（jobISNULL）;

运算符与表达式

□集合运算符

SQL语句中可以出现集合运算符，是对两个查询结果关系进行

集合操作。在两个查询中取的列数与数据类型必须相同。

UNION并

INTERSECT交

MINUS差

运算符与表达式

□UNION并

将两个或两个以上SELECT语句的查询结果集合合并成一个

结果集合显示。（不包括重复值）

UNION的语法格式为：

Selectstatement

UNION[ALL]

selectstatement

[UNION[ALL]selectstatement][...n]

例，查询至少选修102和105其中一门课的学生，输出学号

selectsnofromscwherecno=f102f

union

selectsnofromscwherecno=f1051;

UNIONALL包括重复值

运算符与表达式

□INTERSECT交

将既属于一个查询结果又属于另一个查询结果的记录作为结

臬。

例，查询同时选修了102和105两门课的学生

selectsnofromscwherecno='102'

intersect

selectsnofromscwherecno='105';

运算符与表达式

MINUS差

从一个查询结果的记录中去掉属于另一个查询结果的记录。

例，查询选修了302但没有选修301的学生

selectsnofromscwherecno='302'

minus

selectsnofromscwherecno=l301l;

优先规则

求值顺序

1算木运算

2连字操作

3比较操作

4IS[NOT]NULL,LIKE,[NOT]IN

5[NOT]BETWEEN

6NOT逻辑条件

7AND逻辑条件

8OR逻辑条件

使用圆括号改变优先规则

高级查询

口在数据库中会有许多表存储不同的数据，并且这些表

之间有一定的关联，同时从这多张表中抽取数据，就

是高级查询。涉及到如何将多个查询结果进行集合运

算，从多个表中取数据时如何进行连接，以及如何用

子查询解决一些实际问题。

多表连接

□在多表连接查询中，一般带有WHERE子句，指定连接条件,

否则得到的查询结果将是各表的笛卡尔积（即一表中的每条

记录其另一个表中的每条记亲做连显）O

口笛卡尔连接是一种无条件连接，产生大量的行。如果第一张

表中有M条记录，第二张表中有N条记录，查询结果是M*

N条记录。

口避免产生笛卡尔连接，如果有N张表连接，至少有N・1个连

接条件。

多表连接

□在一条查询语句中涉及多张表，需要将这些表连接起来进

行查询。连接用于从不同的表中得到列的组合。它们之间

通常存在一定的联系，必须识别连接多张表的公共列。

□在多表连接查询中，在连接条件中使用等号“=”将被连接

的多张表中的列作相等比较，为等值连接，也叫做内连接。

内连接查询结果只显示完全满足连接条件的记录。

□在多表连接查询中，在连接条件中使用等号“=”之外的其

它运算符连接多张表，为非等值连接。

注意：查询涉及多表时，先分析查询所涉及到几张表，在

FROM后列出相应的袤名;然后在WHERE子句后写上相应

的连接条件。

多表连接

例1,查询计算机系的学生信息

SELECTsno,sname,sexFROMstudent,dep

WHEREstudent.dno=dep.dno

ANDdname=，计算机系，;

例2,查询哪些雇员的工资级别属于第三级别？（工资级别表为

SALGRADE）

SQL>selectemp9salfromemp,salgrade

wheregrade=3andsal<2200andsal>1601;

多表连接

①当查询多张表时，FROM子句后的表名用“，”隔开；

②在WHERE子句后写上相应的连接条件；

③在SELECT和WHERE后涉及表中的相同的列名时，需要

加上相应的表名，“表名.列名”。

④表名可以取别名，这样在后面参考表名时可以使用简短的

别名。一旦定义了表的别名，展木条SELECT语句中就不

能用表名去限制列名，应该用别名去限制列名。

多表连接

例，查询英语口语不及格学生，输出学生学号，姓名

及成绩

SELECTstudent.sno,sname,grade

FROMstudent,sc,course

WHEREcname='英语口语'ANDgrade<60AND

o=oANDsc.sno=student.sno;

外连接

口一般连接，没有被连接的记录是不会出现在查询结果中的，

外连接可以返回两种记录，一种是满足连接条件的记录，另

外一种是来自其中一个表的记录，这些记录因不满足连接条

件而不能与另一个表的记录连接.

□在ORACLE中，外连接可以通过在WHERE子句中：(+)

的使用来使用，例如：

表A与表B的左连接A.FIELD1=B.FIELD1(+),

右连接A.FIELD1(+尸B.FIELD1。

也可以使用LEFT|RIGHT|FULL实现左连接|右连接|全连接

外连接

□左连接A・FIELD1=B.FIELD1(+)

对于表A中所有的行，如果表B中没有匹配的行，则表B中的

列返回NULL。

例，左连接

selectsname,sex,dnamefromstudent,dep

wherestudent.dno=dep.dno(+);

等价于

selectsname,sex,dnamefromstudentleftouterjoindep

on(student.dno=dep.dno);

外连接

□右连接A・FIELD1(+尸B.FIELD1

对于表B中所有的行，如果表A中没有匹配的行，则表A中

的列返回NULL。

例，右连接

selectsname9sex,dnamefromstudent,dep

wherestudent.dno(+)=dep.dno;

等价于

selectsname9sex9dnamefromstudent

rightouterjoindep

on(student.dno=dep.dno);

外连接

全连接

selectsname,sex9dname

fromstudent

fullouterjoindep

on(student.dno=dep.dno);

自连接

在查询语句中涉及到一张表中的不同行的列值，自连接

查询仅涉及到该表与其自身的连接。为了表示不同的行，在

FROM子句中为一张表定义不同的别名，可以通过在列前加

上别名来引用同一张表中不同行的列值。

例，查询EMP表中职工SMITH的上级领导信息，输出该领导

的职工号和建加。

自连接查询

selecte2.eno,e2.enamefromempel,empe2

whereel.ename=,SMITHlANDel.mgr=e2.eno;

子查询

口在查询语句中，可以通过调用一个查询获得并使用查询结果。

把出现在查询语句（或其他语句）中的查询称为，子查询，，

也称嵌套查询。一般情况下，子查询出现在SELECT语句的

WHERE子句中，也出现在FROM子句和HAVING子句中。

口子查询比主查询先执行，子查询的结果作为主查询的条件使

用。

口一个子查询可以返回一个简单数据，一条记录或一个包含多

行的夹系。

子查询

口子查询使用的原则：

>一个子查询必须用括号引起来。

»在子查询中可以使用两类比较运算符：单行和多行。

>一个子查询必须出现在运算符的右边，

>子查询可以在SELECT、INSERT.UPDATE.DELETE

等语句中使用。

»子查询中不能包含一个ORDERBY子句。在SELECT语句

中只能有一个ORDERBY子句，如果要指定，该ORDER

BY子句必须是主查询语句中的最后一个子句。

子查询

在主查询中可以使用子查询的结果

例，

selectsnamefromstudent

wheredno=（selectdnofromdep

wheredname=，计算机系5）;

对于子查询返回的结果是一个关系的情况（包括若干行和

歹U）,主查询必须使用集合运算符进行处理

WHERE子句中的子查询

□单行子查询

单行子查询只返回一行结果。单行子查询使用一个单

行的运算符，常用等于运算符“二”。

例：查找与SMITH不在同一部门工作的所有雇员，输出姓名

和部门号。

SQL>selectename,deptnofromemp

wheredeptno!=(selectdeptnofromemp

whereename=6SMITH5);

WHERE子句中的子查询

□多行子查询

多行子查询即子查询返回多行结果。可使用多行运算符：

IN、EXISTS.ANY、ALL,都可使用NOT运算符，对其结

果进行求反。

例：当EMP表中有多个雇员叫SMITH时，查找与SMITH不在

同一部门工作的所有雇员，输出姓名和部门号。

SQL>selectename,deptnofromemp

wheredeptnonotin(selectdeptnofromemp

whereename=6SMITH5);

注意：当用户不能确定结果集是否是多行时，在子查询中应该

使用多行运算符。

WHERE子句中的子查询

口相互关联的子查询

在子查询中要使用主查询的当前记录值，这种嵌套的子查

询称为相互关联的子查询。在这类查询语句中，主查询处理

每一行时，相互关联的子查询执行一次。

例，求没有选修C2课程的学生姓名。

SELECT姓名

FROM学生

WHERENOTEXISTS

（SELECT*

FROM选课

WHERE学生.学号=学号AND课程号=£2，）；

注意：①相关子查询是不能独立执行的。

②列的作用范围只能在当前查询层及子查询。

子查询在其他语句中的应用

(1)在CREATETABLE语句中应用

CREATETABLE表名AS子查询

创建一个新表时，如果该表的结构与已存在的表相同时，可以

通过使用子查询的方式建表，同时可插入初始数据。

例，建立计算机系学生表studl,并将计算机系的学生记录从

student表中复刷到新表中。

Createtablestudl

Asselectsno,sname,sex,birthday,student.dno

Fromstudent,dep

Wheredname='计算机系'andstudent.dno=dep.dno;

子查询在其他语句中的应用

(2)还可在INSERT语句中使用子查询，可以通过子查询将查

询结果插入到已存在的表中。选择插入的数据要与插入的表

结构相符。

例：将emp中目前没有单位的雇员记录复制到new_emp表中

SQL>insertintonewemp

select*fromemp

wheredeptnoisnull;

子查询在其他语句中的应用

(3)还可在UPDATE,DELETE语句中使用子查询

例1：将所有雇员的工资改成相应部门的最大工资。

SQL>updateempsetsal=(selectmax(sal)fromemp,e

wheree.deptno=emp.deptno);

例2:删除工资低于该部门平均工资的雇员记录。

SQL>deletefromemp

wheresal<(selectavg(sal)fromemp,e

whereemp.deptno=e.deptno);

FROM子句中的子查询

□在SELECT语句的FROM子句中出现的子查询相当于一张表,

与定义和使用视图类似。FROM子句市的子查询用括号引起

来，并且可以给其取一个别名。

例：查询每个部门的平均工资，并且找出每个部门中的每个

雇员的工资与其所在部门的平均工资的差。

SQL>selecte.deptno,e.ename,e.salsalary,a.average,

e.sal-a.averagedifffromemp,e,(select

deptno,avg(sal)averagefromemp

groupbydeptno)a

wheree.deptno=a.deptno

orderby1,2;

HAVING-f句中的子查询

□在HAVING子句中使用子查询，Oracle服务器先执行子查询,

然后将子查询的结果返回各主查询的HAVING子句。

例：查询EMP表中平均工资超过30号部门平均工资的部门。

SQL>selectdeptno,avg(sal)fromemp

groupbydeptno

havingavg(sal)>

(selectavg(sal)fromemp

wheredeptno=30);

5.4数据基本类型

口字符型数据类型

包括CHAR、VARCHAR2(VARCHAR)、LONG、NCHAR和

NVARCHAR2几种类型。

(DCHAR,描述定长的字符串，如果实际值不够定

义的长度，系统将以空格填充。如果长度大于定

义长度将会触发错误信息。声明方式如下:CHAR

(L),

L为字符串长度，缺省为1,作为变量最大32767个

字符，作为数据存储在ORACLE最大为2000个字节。

字符型数据类型

(2)VARCHAR2(VARCHAR),描述变长字符串。它的声明方式

如下：

VARCHAR2(L)

L为字符串长度，没有缺省值，作为变量最大32767个字节，作

为数据存储在ORACLE中最大为4000个字节。在多字节语言环

境中，实际存储的字符个数可能小于L值，

例如：当语言环境为中文(SIMPLIFIED

CHINESE_CHINA.ZHS16GBK)时，一个VARCHAR2(200)的数

据列可以保存200个英文字符或者100个汉字字符。

□因为VARCHAR2数据类型只存储为该列所赋的字符(不加空

格)，所以VARCHAR2需要的存储空间比CHAR数据类型要小。

□Oracle推荐使用VARCHAR2

字符型数据类型

(3)NCHAR、NVARCHAR2,国家字符集，与

环境变量NLS(NLS文件二多国语言资源文件)

指定的语言集密切相关，使用方法和CHAR、

VARCHAR2相同。

NUMBER数据类型

NUMBER,是以十进制格式进行存储的，它便于存储，但是

在计算上，系统会自动的将它转换成为二进制进行运算。

它的定义方式为：

NUMBER(P,S),

P是精度，最大38位，S是刻度范围，可在-84.・.127间取值。

例如：NUMBER(5,2)可以用来存储表示

-999.99...999.99间的数值。

若在一行数据中的这个字段输入575.316,则真正保存到字

段中的数值是575.32o

如：number(3,0),输入575.316,真正保存的数据是

575o

P、S可以在定义时省略，例如：NUMBER(5)、NUMBER等；

DATE数据类型

从公元前4712年1月1日到公元4712年12月31日的所有合法

日期，默认为公元后。

Oracle其实在内部是按7个字节来保存日期数据，在定

义中还包括小时、分、秒。

缺省格式为DD-MON-YY,如07-11月-00表示2000年11月7

日。

□Century

□Year

□Month

□Day

□Hour

□Minute

□Second

LONG数据类型

LONG：LONG数据类型可以存放2GB的字符数

据，它是从早期版本中继承来的。现在如

果想存储大容量的数据，Oracle推荐使用

CLOB和NCLOB数据类型。在表和SQL语句中

使用LONG类型有许多限制。

二进制数据类型

用来存储二进制数据，不会在字符集间转换。

(1)RA肌类似于CHAR,声明方式RAW(L),L为长度，

以字节为单位，作为数据库列最大2000,作为变量最

大32767字节。Oracle用这种格式来保存较小的图形文

彳牛或带格式的文本文件，如MiceosoftWord文档。

是一种较老的数据类型，将来会逐渐被BLOB、CLOB、

NCL0B等大的对象数据类型所取代。

(2)LONGRAW,类似于VARCHAR2,可变长二进制数据，

最大长度是2GB。Oracle用这种格式来保存较大的图

形文件或带格式的文本文禅，加MiceosoftWord支档,

以及音频、视频等非文本文件。

在同一源表申木能向时宥long类型和longraw类型，

也是一种较老的数据类型，将来会逐渐被BLOB、CLOB、

NCLOB等大的对象数据类型所取代。

ROWID数据类型

□ROWID是一种特殊的列类型，称之为伪列(逻辑列)

(pseudocolumn)。在创建基表时，自动创建。不能改

变。

□ROWID伪列在SQLSELECT语句中可以像普通列那样被访

问。Oracle数据库中每行都有一个伪列。ROWID表示行

的地址，ROWID伪列用ROWID数据类型定义。

□ROWID与磁盘驱动的特定位置有关，因此，ROWID是获得

行的最快方法。但是，行的ROWID会随着卸载和重载数

据库而发生变化，因此建议不要在事务中使用ROWID伪

列的值。例如，一旦当前应用已经使用完记录，就没有

理由保存行的ROWID.不能通过任何SQL语句来设置标准

的ROWID伪列的值。

□列或变量可以定义成ROWID数据类型，但是Oracle不能

保证该列或变量的值是一个有效的ROWID.

大对象数据类型

LOB变量主要是用来存储大量数据的数据库字段，

最大可以存储4G字节的内容。用来保存较大的图

形文件或带格式的文本文件，如MiceosoftWord

文档，以及香频、视频等非文本文件。主要

有：

□CL0B：和LONG类型相似，存储单字节字符数据

□NCL0B：用来存储定宽多字节字符数据。

□BLOB：和LONGRAW相似，用来存储无结构的二进

制数据。

□BFILE：它用来允许ORACLE对数据库外存储的大

型二进制文本进行只读形式的访问。

5.5函数

输入输出

函数

参数11..函数(Function)

■参数2,执行作用

函数

□Oracle提供了大量的函数，可以在SQL和PL/SQL语句中调

用。

口函数有0个或多个参数，并且返回单个值。

口函数的一般格式：函数名（参数1,参数2,…，参数n）

□函数功能：

♦计算数值

♦修改单个数据项

♦对行进行分组输出

♦修改日期的显示格式

♦转换列的数据类型

数

函数按操作对象可分为两种类型:

□单行函数

□聚组函数（多行函数）

函数

函数

单行函数多行函数

函数

口单行函数

单行函数只操作一行，并且每一行返回一个结果。

单行函数在从表中取出数据之前就知道要处理参数

的个数。

□分组函数

分组函数是对一组被查询的行返回一个单一的值。

分组函数直到所有的数据从表中抽取出来并且组成

一个分类时，才知道要处理的参数的个数。

单行函数

单行函数用于操纵数据项，他们接受一个或多个

参数，并且对查询的每个返回行返回一个值。

一个参数可以是下列数据之一：

□用户提供的常数

□变量值

□列名

□表达式

单行函数

单行函数的特性包括：

□作用于查询中返回的每一行

□每行返回一个结果

□可能返回一个与参数不同类型的数据值

□可能需要一个或多个参数

□能够用在SELECT、WHERE和ORDERBY子

句中；可以嵌套

单行函数

□function_name\_{argl,arg2,...)]

在语法中：

functionname是函数的名字。

argl,arg2是由函数使用的任意参数，可以由

一个列名或者一个表达式提供。

单行函数

单行函数可以出现在查询列表（但查询语句

不能包含GROUPBY子句）、WHERE子句、

ORDERBY子句的表达式中，可以出现在UPDATE

语句的SET子句中，也可以出现在INSERT语句的

VALUES子句中。

单行函数

字符

通用HS数字

单行函数

转换0期

单行函数

SQL和PL/SQL提供了很多种单行函数，根据函

数值的数据类型可划分为：数值函数、字符函数、

日期函数、转换函数、通用函数、其他函数、对象

引用函数、以及程序员编写的存储函数。

单行函数

口字符函数：接受字符输入，可以返回字符或者数字值

口数值函数：接受数字输入，返回数字值

□日期函数：对DATE数据类型的值进行运算

口转换函数：从一个数据类型到另一个数据类型转换一个

值

□通用函数：NVL、NVL2、NULLIF、

COALSECE.CASE.DECODE

单行函数

字符型函数：

□CONCAT(sl,s2)字符串连接，对字符串si和字符串s2进行连接。返回

连接后的字符串。如果si是NULL,则返回§2;如果§2是NULL,贝”返回

si；如果§1、s2都是NULL,则返回NULL。

如：selectconcat("micro',‘soft')fromdual;

□LOWER(s)变字母为小写

□UPPER(s)变字母为大写

□REPLACE(sl,s2[,s3])字符串查找/替换，该函数在字符串仃中查找所有

出现的§2串，并将找到的串用§3串替换。

小口:selectreplace(6thisisanapple4apple9,'example')fromdual;

□INSTR(sl,s2,[,n[,m])该函数在si串中查找s2串出现的位置，返回函数值。

参数n指示从si串的第n个位置开始找；参数m指示找出第几次出现的那

个s2串。

小口:selectinstr(6thisisnotagoodexample,isit?,''is',4,2)29

单行函数

□SUBSTR(S,M[,N])取子串函数。该函数从s串中第m个字符起，取长度为

n的一个字串。

如：selectsubstr(athisisanexample9,12,4)fromdual;exam

□LENGTH(s)返回字符串s的长度，如果s为CHAR型，则长度包括所有尾

部空格。

如：selectlength(6microsoff)fromdual;9

□ASCII(c)返回字符串c中的第一个字符的ASCII码。

□CHR(n)返回与n相等的二进制数(ascii码)所代表的字符。

□INITCAP⑸该函数是将包含若干单词的字符串s中的每一个单词的首字

母大写，其他字母小写。

□TRANSLATE(s,from,to)该函数是将字符串s中出现的from字符列表中的

字符替换成to字符列表中的相应字符。

如：Selecttranslate(6thisisanexample','myis，；*****，)fromdual;

th******an*exa*ple

单行函数

□LPAD(sl,nJs2])左填充函数。该函数是在字符串si的左边填

充字符s2,使字符串总长度达到参数n所规定的长度。如果

si的长度比n长，那么不填充，并只输出si串的前一部分。

S2省略，就填充空格。

如：selectlpad(6verygood!',30,'ok!')

ok!ok!ok!ok!verygood!

□RPAD(sl,n,[s2])右填充函数。

□LTRIM(cl,[c2])该函数是删除cl串中位于最左边的、且出现

在c2串中的字符。如果c2参数省略，要删除的字符就是空格

字符。

□RTRIM(cl,[,c2])该函数是删除cl串中位于最右边的、且出现

在c2串中的字符。

单行函数

数值函数：

□ABS(n)返回n的绝对值

如:Selectabs(-3.2)fromdual;3.2

口三角函数，SIN(n),COS(n),TAN(n),ASIN(n),ACOS(n),

ATAN(n)等

□CEIL(n)返回大于或等于n的最小整数

如：selectceil(-3.2)fromdual;3

□FLOOR(n)返回小于或等于n的最大整数

□EXP(n)返回基于n自然对数的平方

□LN(n)返回以e为底的n的自然对数[e(2.7182818)J

□LOG(nl,n2)返回以nl为底的n2的对数

□MOD(m,n)返回m除以n的余数

单行函数

□POWER(nl,n2)返回nl的n2次方

□ROUND(m,n)取小数点前后位置n四舍五入，n默认为0,

n>0,则四A五入为n位小数，n<0,四舍五人为小数点向左

第n位。

如：selectround(5678.1234<2)fromdual;5700

口TRUNC(m,n)与ROUND类似，只是不采用四舍五入，强行

截去不需要的部分。

□SIGN(n)n>0返回1,nvO返回n=0返回0

□SQRT(n)返回n的平方根

单行函数

日期函数

□SYSDATE返回当前日期，该函数没有参数。

□ADD_MONTHS(d,n)该函数是在d所代表的日期上加n个月，

并将结果日期返回。

如：selectadd_months(sysdate,3)?fromdual;

□LAST_DAY(d)返回日期d所在的那个月份的最后一天。

□MONTHS_BETWEEN(dl,d2)返回dl和d2两个日期之间相隔

几个月。一

□ROUND(d[,fmt])该函数按照fmt指定的日期、时间单位精度

返回d所袤示的日期第一天。

如：Selectround(sysdate,year')fromdual;

1-1-2010

单行函数

转换函数：

□TO_CHAR:将日期型或数值型数据转换成字符串。

>TO_CHAR(d,fmt)将d所表示的日期型数据转换成fmt所指定

的丙容及格式。该函数值为VARCHAR2类型。

例,TO_CHAR(SYSDATE,YYYY-MONTH-DD)

>TO_CHAR(n,fmt)将NUMBER类型的数据n按fmt格式转换

为VXRCHAR2类型数据。

□TO_DATE(s,fmt)将CHAR或VARCHAR2类型的字符串数据

s转换成DATE类型的数据。参数fmt说明s串所袤示的日期格

□TO_NUMBER(s,fmt)将CHAR或VARCHAR2类型的数据s转

换成对应的数值型数据。

单行函数

通用函数：

□NVL(exprl,expr2)exprl和expr2为两个表达式。当

exprl的值不空时，函数返回exprl的值；当exprl的值

为空时，函数返回expr2的值。

□NULLIF(exprl,expr2)o比较两个表达式，如果相等，

函数返回空，如果不相等，函数返回第一个表达式。

第一个表达式不能为NULLo

□COALSECE(exprl,exprl,...exprn)。函数返回列表中

的第一个非空表达式。

组函数

□组函数返回基于一组记录的值。组函数不处理NULL值，

也不返回NULL值。大部分组函数可以接受

DISTINCT和ALL两个选项。

DISTINCT,该选项使得组函数只考虑不同值，（即去掉重

复值）。

ALL,该选项使得组函数考虑参数表达式的所有值（包括重

复值），默认值。

□组函数可以出现在查询列表和HAVING子句中。在SELECT

子句中使用时，通常要求该语句带GROUPBY子句。

组函数

口组函数的特点：

♦一般对一组行进行操作，而不是对单一行。每一组只给

出一个结臬。

♦用于返回一组数据的汇总信息(求和，求平均值)。

♦组函数的参数的数据类型可以是数字型，字符型，日期型。

♦如果将单行函数和组函数一起使用，必须有相应的

GROUPBY子打o

♦多数组函数可以使用DISTINCT和ALL两个选项。

♦除了COUNT(*)以夕卜，所有的组函数都忽略NULL值。

可以使用NVL函数以别的值参代NULL。

组函数

□AVG(DISTINCT|ALLn)返回n的平均值。

例，查询部门20职工的平均工资

SelectAVG(sal)fromemp

Wheredeptno=6209;

□COUNT({*|[DISTINCT|ALL]expr})返回查询到的行数。

参数为*,返回所有的行，包括空行。

参数为expr,返回expr为非空的行数。

例，查询计算机系的学生总数

selectCOUNT(*)fromstudent,dep

wherednanie=，计算机系1ANDdep.dno=student.dno;

组函数

□SUM({DISTINCT|ALL}n)返回n的总和。

例，统计职工工资总额

SelectSUM(sal)fromemp;

□MAX({DISTINCT|ALL}expr)返回expr最大值。

表达式是字符型，则返回VARCHAR2,数据库字符集中排

序最高的最大；

表达式是日期型，则返回日期，最近的日期最大；

表达式是数字型，则返回数字。

□MIN({DISTINCT|ALL}expr)返回expr最小值，与MAN函数

相反。

组函数

例，查询部门20职工的最高工资，最低工资

selectMAX(sal)fromempwheredeptno=20;

selectMin(sal)fromempwheredeptno=20;

组函数

□VARIANCE({DISTINCT|ALL}x)返回x的方差。

□STDDEV({DISTINCT|ALL}x)返回x的标准方差。是根据方

差的平方根得到的。

例，

selectvariance(sal)fromemp;

selectstddev(sal)fromemp;

函数的嵌套

口函数可以嵌套，以便一个函数的输出作为另一个函数的输入。

嵌套函数的优先顺序基于它的位置，从最里层开始执行直到

最外层，并且从左到右。

□嵌套函数可以在组函数内包含单行函数，也可以在单行函数

内包含组函数，可以在单行函数内包含单行函数，也可以在

组函数内包含组函数。

查询结果的报表输出

□使用SQL*PLUS的格式命令使得查询结果按报表方式输出。

□通过SQL*PLUS的格式命令进行：

>定义一个列的宽度

>显示有意义的列标题

>对数字型和日期型数值进行格式化

>隐藏字符列

>显示页标题、页号。

查询结果的报表输出

口格式化列，使用COLUMN命令格式化列标题，格式化列

的显示格式；设置列的格式参照其他列的格式；列出或清

除列的格式。

①格式化列标题

SQL>COLUMN列名HEADING列标题

例：SQL>columnSNAMEHEADING姓名

SQL>columnSNAMEHEADING'STUDENT|姓

名，

使用T将列标题分隔上下两行

②格式化列的显示格式

SQL>COLUMN列名FORMAT格式描述

查询结果的报表输出

③设置列的格式参照其他列的格式，包括列标题和格

式

SQL>COLUMN歹4名1LIKE列名2

[HEADING列标题]

其中，列名2是已经格式化的列。

④列出或清除列的格式设置

COLUMN列名——列出某一列的格式设置

COLUMN列名CLEAR——清除某一列的格式设

置

CLEARCOLUMNS——清除所有列的格式设置

⑤隐藏列的输出

SQL>COLUMN列名NOPRINT

查询结果的报表输出

□用空行和总计使报表简洁

报表在输出时分组，对组内的数据进行组函数运算，则必

须使用BREAK命令先设置分组，分组后组内的数据重复值只

显示一次。然后使用COMPUTE命令对组内的数据进行运算。

①设置分组列

BREAKON分组列名一按某一列分组

BREAKON分组列名SKIPn—按某一列分组，两组之间空n行

BREAKON分组列名SKIPPAGE^按某一列分组，两组之间

换页

BREAKONREPORT—按报表分组，整个报表为一组

查询结果的报表输出

注意：

①每次只有一个BREAK命令起作用，但一次可以在

多个列上使用BREAK命令

SQL>BREAKON歹4名1ON歹4名2

②在SELECT语句中带有ORDERBY子句与BREAK

对应，如果BREAK设置使用BREAKON分组列名,

贝帕ELECT语句中必须按分组列排序。如果BREAK语

句使用RE