Oracle 学习笔记课件

Oracle笔记

曲Oracle部分

第一章数据库介绍3

第二章Oracle简介4

第三章用户、权限6

第四章Oracle数据类型8

第五章SQL语句概述9

第六章表空间11

第七章表12

第八章函数14

第九章约束17

第十章单查询查询22

第十一章多表查询-内连接23

第十二章多表查询-外连接25

第十三章多表查询-差不多26

第十四章多表查询-连接29

第十五章子查询32

第十七章组函数及分组统计34

第十八章数据库对象-视图37

第十九章数据库对象-序列40

第二十章数据库对象-同义词43

第二十一章数据库对象-索引44

第二十二章网络配置47

第二十三章嵌套表、可变数组48

曲PL/SQL部分

第一章PL/SQL简介51

第二章PL/SQL数据类型54

第三章LP/SQL操纵语句58

第四章动态SQL63

第五章错误处理64

第六章游标-隐式游标66

第七章游标-显式游标68

第八章游标-REF游标72

第九章子程序-过程74

第十章子程序-函数77

第H-一章自主事物处理80

第十二章程序包82

第十三章触发器讲解85

第十四章触发器实例89

第十五章数据库设计和三大范式92

第十六章数据库的备份与复原简介97

第十七章导入导出工具99

第十八章数据库归档方式102

能JDBC部分

JDBC部分104

第一章jdbc简介104

第二章连接数据库106

第三章常用数据库的驱动程序和JDBCURL108

第四章连接池109

第五章数据操作-创建表111

第六章数据操作-查询112

第七章预处理113

第八章批处理114

第九章数据的数据115

第十章调用函数116

第十一章调用过程119

第十二章DAO封装122

第一章数据库介绍

一、数据的储存方法：

第一种方法：用大脑来记住数据

第二种方法：写在纸上

第三种方法：写在运算机的内存中

第四种方法:写成磁盘文件

二、数据库能做什么？

1.储备大量数据，方便检索和访问

2.保持数据的一致、完整

3.共享和安全

4.通过组合分析，产生新的有用信息

三、数据库的进展史

萌芽时期-文件系统

★使用磁盘文件储存数据

初级时期-第一代数据库

★显现了网状模型、层次模型的数据库

中级时期-第一代数据数据库

★关系型数据库和结构化查询语言

高级时期-新一代数据库

★”关系-对象"型数据库

四、当前的数据库产品

Oracle——甲骨文

BD2IBM

SQLServer——微软

Sybase赛贝思

MySqlSUN

五、数据库和应用程序

向数据库清求数据、存储数据

并显示结果

六、数据库相关的差不多概念

概念模型：基于客户的方法和观点所形成的认识和对象

实体(Entiy):客观存在的、能够被描述的事物。如职员、部门

属性(Attribute):用于描述实体所具有的特点或特性，如使用编号、姓名、部门、工资等属性

职员的特点。

关系(Relationship):实体之间的联系。如部门和职员之间有一对多的关系。

数据模型：也叫关系模型，是实体、属性、关系在数据库中的具体表现。

关系数据库：用于储存各种类型的''仓库"，是二维表的集合

表：实体的映射

行和列：行代表一个具体的实体数据。也叫一条记录。列是属性的映射，用于描述实体的

主键和外键

七、数据库治理系统(DBMS)

数据库产品

检索数据插入数据一更新数据删除数据

数据库管理系统-DBMS

第二章Oracle简介

快速把握Oracle

课程目标：

•Oracle安装及配置

•有关数据库的DDL操作

•有关数据表的DDL操作

•有关数据表的CRUD操作

•事物操纵

•索引视图

・储备过程

•触发器

•权限治理

・数据库的备份与复原

・数据库设计

Oracle是一个生产中间件和数据库的较大生产商，Oracel的原本含义是''神喻"，指的是神

说的话。在中国的商朝的时代，把一些刻在龟壳上的文字当成了上天的指示，因此在中国也

将Oracle翻译成''甲骨文"。

Oracle的进展实际上依靠了IMB公司。

Oracle的创始人是：LarryEllision仓|办了Oracle公司。

Oracle的版本分为：

Oracle8

Oracle8i:Internet表示现在Oracle公司开始开始正式进军互联网.

Oracle9i:Oracle8i和Oracle9i相比是专门相似的

Oracle10g:g表示网格技术

网格技术：如我们在百度上下载一个软件，那么那个软件在离我们的远处有一个，在我们的

近处也有一个，有可能我们通过搜索引擎下载的是远的那个。忽略了近处的资源，如此就造

成了资源的白费。因此就产生了网格技术。确实是将网络划为了多个小格。通过网络表示区

域。

OOracle是由甲骨文公司生产的以高级结构化查询语言(SQL)为基础的大型关系数据库，通

俗地讲它是用方便逻辑治理的语言操纵大量有规律数据的集合。是目前最流行的客户/服务

器(CLIENT/SERVER)体系结构的数据库之一。

◊是目前市场占用率极高的一款数据库产品

特点：

♦提供了基于角色(ROLE)分工的安全保密治理。在数据库治理功能、完整性检查、安全性、

一致性方面都有良好的表现。

♦支持大量多媒体数据，如二进制图形、声音、动画以及多维数据结构等。

♦提供了与第三代高级语言的接口

♦提供了新的分布式数据库能力。可通过网络较方便地读写远端数据库里的数据，并有对称

复制的技术。

一、储备结构：

物理结构：

ORACLE数据库在物理上是储备于硬盘的各种文件。它是活动的，可扩充的，随着数据的

添加和应用程序的增大而变化。

逻辑结构：

ORACLE数据库在逻辑上是由许多表空间构成。要紧分为系统表空间和非系统表空间。非

系统表空间内储备着各项应用的数据、索引、程序等相关信息。

二、启动Oracle

在Window平台下必须启动的Oracle服务有：

1.OracleServiceSID

数据库服务，那个服务会自动地启动和停止数据库。假如安装了一个数据库，它的缺省启动

类型为自动。服务进程为ORACLE.EXE.

2.OracleHOME_NAMETNSListener

监听器服务。

3.sqlplus工具

sqlplus工具

登录数据库有以下几种方式：

(1).sqlplus:以命令的方式进入数据库连接

(2).sqlplusw：以窗口的形式启动命令行

在使用此命令时，会提示一个主机的字符串，假如一台电脑上有多个数据库的话，那么要在

此处输入数据库的名称，假如不输入，会进行默认的，一样默认的是最后一个数据库。

那么登录帐户以后，就能够在数据库中进行增、册h改、查等操作。

如我们能够查看表：SELECT*FROMemp;(emp表是数据库自带的表)

当我们对表进行查看时，有时候显示的表并不规范，如本来是一行的内容，但是有一部分被

补到了下一行。如此看下来表就专门纷乱，不规范。因此我们要对其环境进行一下设置。

如设置每行显示的长度：setlinesize300;

有时候标题行还会重复显现，在这因为在Oracle中数据是一页一页的显示方式进行输出的。

所我们可修改每页显示行数。

如setpagesize20;

4.命令

我们通常使用的sqlplusw

在sqlplusw中存在着大量的命令。

在sqlplusw下编辑代码时，显现了错误，不承诺我们使用向左方向键向右移动到相应的位

置上进行修改，专门不方便，因此通常我们使用记事本进行代码的编辑，直截了当在命令行

中输入、'ed文件名名称即可"。如edtest,输入之后会提示找不到test.sql文件，要创建新

文件吗？我们选择''是〃，那么就创建了一个test文件，我们就能够在test文本中写相应的

代码。创建完成之后，能够通过@文件名称的方式执行命令。如@1€$3就会执行在test中

写的代码。

除了在sqlplus中创建文件之外，也能够通过@符找到磁盘上的文件，如我们在D盘上建立

一个demo.txt的文件，里边写上查询指令。执行的时候，要指定文件的路径：@路径，如

@D:\demo.txt,也会执行demo中的指令。成效也完全一样的。

、'\"能够省略。如@口也010其士假如文件的后缀是.sql,那么不写后缀名称也能够找到。如：

@D:demoo因此默认找到的后缀是、'*.sql"。

在sqlplusw中能够使用''/〃表示重复执行上一句命令的操作。

第三章用户、权限

一、用SQLPLUS登录Oracle

1.Sqlplus用户/密码［as身份］

如登录系统帐户：connsys/systemassysdba;

conn:连接到数据库的关键字

sys:系统用户名

system:是验证密码

assysdba:是身份验证

2.Oracle内置帐户

sys具有最大的权限。

Oracle数据库服务器启动后，一样至少有以下几个用户:

Internal,它不是一个真实的用户名，而是具有SYSDBA优先级的Sys用户的别名，它由DBA

用户使用来完成数据库的治理任务，包括启动和关闭数据库；

sys:它是一个DBA用户名，具有最大的数据库操作权限；

system:它也是一个DBA用户名，权限仅次于Sys用户

scott:它是一个oracle例如/学习帐户

3.停止和启动Oracle

启动/停止windows服务

Sqlplus/nolog利用那个命令能够在DOS下不利用任何身份进入到SQL的状态。之后再利

用身份登录

Connect/assysdba

Shutdown/startup

/是以操作系统认证进行登录

Nolog不创建初始联接

4.创建、删除用户

△创建帐户：

CREATEUSERusernameIDENTIFIEDBYpassword

[DEFAULTTABLESPACEtablespace1]——>默认表空间

[TEMPORARYTABLESPACEtablespace2]——>临时表空间

[QUOTAnKONtablespacel];——>不足时自动增加nK或nM

M

UNLIMITED>没有限制

如创建一个hellen的帐户：createuserhellenidentifiedbyabed;

△删除帐户：

差不多语法：DROPUSERuserName[cascade];

假如加上cascade关键字可删除该用户所创建的对象。

如：删除用户hellen:dropuserhellen;

△用户修改密码：

差不多语法：ALTERUSERuserNameIDENTIFIEDBYpassword;

如：alteruserhellenidentifiedbyabcl23;

△用户解锁：

差不多语法：ALTERUSERuserNameACCOUNTUNLOCK;

如：alteruserhellenaccountunlock;

△查看当前登录用户

SHOWUSER;

△查看系统有哪些用户：

SELECTUSERNAMEFROMDBA_USERS;

如：

第一步：descdba_users;

第二步：selectusername,USERJD,ACCOUNT_STATUSFROMDBA_USERS;

△查看用户的默认表空间

selectusername,default_tablespacefromdba_users;

△查看用户有哪些表空间

selectdistincttablespace_namefromdbatableswhereowner='USER';

△确定用户帐户所授予的权限

select*fromDBA_tab_privs;直截了当授予用户帐户的对象权限select*from

DBA_role_privs;授予用户帐户的角色select*fromDBA_sys_privsWHEREGRANTEE=?;

授予用户帐户的系统权限

5.授于用户连接Oracle数据库的权限

格式：grant权限to帐户名。

如授予能够连接到数据库的权限：granthellentoscott;

常用的权限有：

connect(8)连上Oracle,做最差不多操作

resource(5)具有程序开发最差不多的权限

dba(77)数据库治理员所有权限

exp-full-database可把数据库整个备份输出的权限

imp-full-datsabase可把数据库整个备份复原输入的权限

6.回收权限

差不多语法：

REVOKE权限FROM用户名；

REVOKE实体权限|ALLON表空间FROM用户名|角色名［PUBLIC;

如授回连接到数据库的权限：revokeconnectfromhellen;

常见的实体权限：见附录

第四章Oracle数据类型

Oracle提供了22种不同的SQL数据类型供我们使用：

★CHAR：这是一个定长字符串，会用空格填充来达到其最大长度。非null的CHARQ2.)

总是包含12字节信息。CHAR字段最多能够储备2,000字节的信息。

★NCHAR：这是一个包含UNICODE格式数据的定长字符串。最多能够储备2,000字节

的信息。

★VARCHAR2：这是一个变长字符串，与CHAR类型不同，它可不能用空格填充至最大

长度。VARCHAR2Q2)可能包含0〜12字节的信息。VARCHAR2最多能够储备4,000字节

的信息。

★NVARCHAR2：这是一个包含UNICODE格式数据的变长字符串。NVARCHAR2(12)能

够包含0〜12字符的信息。NVARCHAR2最多能够储备4,000字节的信息。

★NUMBER：这种数据类型能储备精度最多达38位的数字。这些数介于12X12(-130)-1

12X12(126)之间。

用法:number(p,s);p和s是能够选的，用于表示整数部分和小数部分的精度

★BINARY_FLOAT：这是Oracle10gRelease1及以后版本中才有的一种新类型。它是一

个32位单精度浮点数，能够支持至少6位精度，占用磁盘上5字节的储备空间。

★BINARY_DOUBLE:这也是10g中新的一种类型

★CLOB：在Oracle分及往常的版本中，这种数据类型承诺储备最多4GB的数据，在Oracle

10g及以后的版本中承诺储备最多14GB)X(数据库块大小)字节的数据。这种数据类型

专门适合储备纯文本信息。

★BLOB：在Oracle为及往常的版本中，这种数据类型承诺储备最多4GB的数据，在Oracle

10g及以后的版本中承诺储备最多14GB)X(数据库块大小)字节的数据。适合于储备图

片/文档

★LONG：这种类型能储备最多2G的字符数据--建议使用CLOB代替

★DATE：这是一个7字节的定宽日期/时刻数据类型。其中总包含7个属性，包括：世纪、

世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。

★TIMESTAMP：这是一个7字节或12.字节的定宽日期/时刻数据类型。它与DATE数据

类型不同，因为TIMESTAMP能够包含小数秒(fractionalsecond)；带小数秒的TIMESTAMP

在小数点右边最多能够保留9位。

★TIMESTAMPWITHTIMEZONE：与前一种类型类似，这是一个12.字节的定宽

TIMESTAMP,只是它还提供了时区(TIMEZONE)支持。数据中会随TIMESTAMP储备有

关时区的额外信息，因此原先插入的TIMEZONE会与数据一同保留。

★ROWID：ROWID实际上是数据库中一行的12字节地址。ROWID中编码有足够的信

息，足以在磁盘上定位这一行，以及标识ROWID指向的对象。

(l)Oracle中伪列就像一个表列，然而它并没有储备在表中

(2)伪列能够从表中查询，但不能插入、更新和删除它们的值

(3)常用的伪列有ROWID和ROWNUM

ROWID是表中行的储备地址，该地址能够唯独地标识数据库中的一行，能够使用

ROWID伪列快速地定位表中的一行。

ROWNUM是查询返回的结果集中行的序号，能够使用它来限制查询返回的行数

例：

列的类型要紧有如下几种：

NUMBER(4):表示是数字，长度为4

VARCAHR2Q0):表示的是字符串，只能10的个的长度

DATE:表示日期

NUMBER(7,2):表示是数字，其中小数位占2位，整数位占5位，总共是7位。

第五章SQL语句概述

SQL语句概述

OSQL结构化查询语言(StructuredQueryLanguage)

一样读作：［si:kju:］或者是字母SQL的发音。

O目前数据库厂商实现的差不多上SQL92标准，还没有任何一家厂商通过SQL99标准认证

OOracle对SQL92做了扩展,因此称自己为加强版SQL(SQLPLUS)□

关于不同的数据库来讲，重点差不多上把握SQL语句，因为现在的数据库全部是以SQL操

作的标准，在实际中，各个数据库确实是提供的函数不同。

SQL语言要紧用于与数据库的通讯。SQL语言功能强大,要紧分为以下同种：DMLDDL

DCL事物操纵语言。

一、SQL语句分类

1.DDL(DataDefinitionLanguage)数据定义语言:定义数据库对象(表空间，表，歹!J,索引等)

如：CREATE,DROP,ALTER,TRUNCATE等

2.DML(DataManipulationLanguage)数据操纵语言:完成对数据记录的操作

如：INSERT,DELETE,UPDATE,SELECT等。

3.DCL(DataControlLanguage)数据操纵语言:定义用户的访问权限和安全级别

如：GRANT,REVOKE

4.事物操纵(TransactionControl)事物操纵:如：COMMIT,ROLLBACK

(1)事务是最小的工作单元，作为一个整体进行工作

(2)保证事务的整体成功或失败，称为事务操纵

用于事务操纵的语句有：

(4)C0MMIT-提交并终止事务处理

当向表插入一个新值的时候，该事物并没有被永久的写到磁盘上去，重新打开窗口再次查

询该表中的数据时，发觉并没有刚才插入的记录，这是因为那个事物还没有终止，当遇到

commit或rollback才认为是终止了。

假如要永久性的提交能够执行：commit命令，再次打开新的窗口时该记录已被写到表中了。

(5)ROLLBACK-撤销事务中已完成的工作

当我们做了和系列的操作以后，都没有执行commit命令，也确实是没有提交，我们执行了

rollback就可要回到原点了，也确实是刚才所做的都等于没做，所有rollback回退是将所有

的回退。

(6)SAVEPOINT-标记事务中能够回滚的点

因为rollback回退是将所有的都回退了，这明显不太好，那么我们能够设置几个回退点，使

再次回退的时候，不让其回退到原点，而是回退到我们固定的位置上去。

如：

UPDATE表名setid=2WHEREid=3——＞将id=3改为id=2

SAVEPOINTmarkl——＞设置一个还原点markl

DELETEFROM表名WHEREid=5一一＞册|除id=5

SAVEPOINTmark2;--一＞再设置一个还原点mark2

二、Oracle支持的SQL操作符的分类：算术操作符比较操作符逻辑操作符集合操作符连

接操作符

1.算术操作符：

算术操作符用于执行数值运算

能够在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接

它们的算术操作符组成

算术操作符包括加(+)、减(-)、乘(*)、除⑺。

2.连接操作符用于将多个字符串或数据值合并成一个字符串

例：要求查出雇员的编号、姓名、工作然而显示的格式是：

编号是：7369的雇员，姓名是：SMITH,工作是:CLERK

SELECT编号是：［empno『的雇员，姓名是：Rename(工作是30比一一＞使用了连接操作符

在查询中也能够使用四那么运算功能。如我们要查每个雇员的姓名及年薪。

SELECTename,sal*12FROMemp;-一＞月薪*12表示年薪——＞使用了算术操作符

3.比较操作符用于比较两个表达式的值

比较操作符包括=、！=、＜、＞、＜=、＞=、BETWEEN—AND,IN、LIKE和ISNULL等

例：

SQL＞SELECTitemdesc,re_level

FROMitemfile

WHEREqty_hand＜max_level/2;

SQL＞SELECTordernoFROMorder_master

WHEREdel_dateIN('06-1月-05','05-2月-05');

SQL＞SELECTvencode,venname,tel_no

FROMvendormaster

WHEREvennameLIKE*j___s';

4.逻辑操作符

逻辑操作符用于组合多个计较运算的结果以生成一个或真或假的结果。

逻辑操作符包括与(AND)、或(OR)和非(NOT)。

例:显示2005-5月-10至2005-5月-26的订单信息

SQL>SELECT*FROMorder_master

WHEREodate>'10-5月-05,

ANDdel_date<'26-5月-05';

5.集合操作符将两个查询的结果组合成一个结果，集合操作符有：UNION,UNIONALL,

INTERSECT>MINUS

(l)UNION:将两个表中的所有的记录合到一起，但重复的只合一遍

如：用法有两个表(id和name)

SELECT*FROM表A

UNION

SELECT*FROM表B;

结果：A与B重复的行只拿一次

(2)UNIONALL:重复的行复取，也确实是取出所有的(A+B)

SELECT*FROMA

UNIONALL

SELECT*FROMB;

(3)INTERSECT:返回公有的(A与B的交集)

(4)MINUS差积(A-B或B-A)

用法都与UNION相同。

注一：在求差积时，假如A放前，去掉公共的行时，剩余的差不多上A中的，而B中剩余

了什么并不管。假如B放前去掉公共行时，剩余差不多上B中的。

注二、在使用集合操作符时，两个表的类型一定要相同。

三、SQL操作符的优先级从高到低的顺序是：

算术操作符——最高优先级

连接操作符

比较操作符

NOT逻辑操作符

AND逻辑操作符

OR逻辑操作符——最低优先级

第六章表空间

表空间

1.创建表空间

差不多语法：

CREATETABLESPACEspacename

[LOGGING]|NOLOGGING

DATAFILE'd:\javasky.dbf'

SIZE200M

AUTOEXTENDONNEXT200M;

如：

createtablespacejavasky——＞表空间名

datafile'd:\javasky.dbf＞名目地址

size20M＞大小为20M

autoextendonnext5M;＞当空间不足时自动增加5M;

2.删除表空间

差不多语法：

DROPTABLESPACE'、TABLESPANCENAME”

注意表空间的名字需要使用双引号包围，同时表空间的名称需要大写。

如：droptablespace"JAVASKY";

3.查看表空间的名称和状态

selecttablespace_name,statusfromdba_tablespaces;

表空间的状态属性要紧有在线（ONLINEk离线（OFFLINEK只读（READONLY）和

读写（READWRITE）4种

4.修改表空间的状态

altertablespace表空间名状态;能够修改表空间的状态

第七章表

查看该帐户下所有的表：

第一种、select*fromcat;

第二种、select*fromtab;

•建表

CREATETABLE表名（列名列类型,…）；

如：createtablestudent（idint,namevarchar2（5）,addressvarchar2（10））;

一创建一个具有IDNAMEADDRESS的学生信息表

创建完表以后能够利用:desc表名来查看

如deststudent;如名称、类型、是否为空。

•修改表

1.增加新列

ALTERTABLE表名ADD列名列类型［ADD列类型］;

如新一个的列：altertablestudentaddtelnumber;

增加一个新的列后给其赋值：updatestudentsettelhereid=2;

2.删除旧列

ALTERTABLE表名DROPCOLUMN列名；

3.修改列类型（要求，列中许多据）

ALTERTABLE表名MODIFY列名列类型

4.修改列名

ALTERTABLE表名RENAMECOLUMN列名TO新列名；

5.修改表名

RENAME表名TO新表名；

•插入（记录）数据，也确实是给列赋值

1.给其相应的字段赋值：INSERTINTO表名［（列名1,,,,）］VALUES（值1,,,）;注：口中的内容

可写可不写

如：INSERTINTOstudent（id,name,addredd）VALUESQ,'李小龙':河北省'）；

2.赋全值

INSERTINTOstudentVALUES。,'李小龙？河北省'）；

3.赋值以后查看该表的内容：select*fromstudent;

4.插入日期格式的值：

INSERTINTO列名（列名）

VALUES（TO_DATE（'2005-10-18','YYYY-MM-DD'））;

・修改记录

UPDATE表名SET列名=值，….

［WHERE条件］;

如update表名settname='李小龙，

wheretname='李建龙>这是一个条件限制

•删除（记录）数据

第一种方法：DELETEFROM表名

［WHERE条件］;

第二种方法：TRUNCATETABLE表名；

•利用现有的表创建表

语法：

CREATETABLE<new_table_name>AS

SELECTcolumn_namesFROM<old_table_name>;

如：

SQL>CREATETABLEnewitemfile

ASSELECT*FROMitemfile;——>所有的歹U

SQL>CREATETABLEnewitemfile1

ASSELECTitemcode,itemdesc,qty_hand>选择特定的列

FROMitemfile;

SQL>CREATETABLEnewitemfile2

ASSELECT*FROMitemfile

WHERE1=2;——>建表时的条件，那个地点是1=2,明显为假，然而但是以建表，然

而空的内容为空。

•不带条件的DELETE和TRUNCATETABLE的区别：

*在功能上，TRUNCATETABLE是清空一个表的内容，它相当于DELETEFROM

table_name。

*DELETE是dml操作,truncatetable是ddl操作；因此DELETE能够回滚,TRUNCATETABLE

不可回滚。

*TRUNCATETABLE调整highwatermark而DELETE不；TRUNCATETABLE之后，

TABLE的HWM退回到INITIAL和

NEXT的位置［默认）delete那么不能够。

*TRUNCATETABLE只能对TABLE进行操作，DELETE能够是table,view,synonym。

"TRUNCATETABLE可不能触发DELETE触发器

*日志记录方式不同,DELETE逐行记录删除日志,TRUNCATETABLE只记录在磁盘上某一

第八章函数

函数

数据库系统中，每个数据库之中唯独最大的区别确实是函数的支持上，使用函数能够完成一

系列的操作能。Oracle提供一系列用于执行特定操作的函数。

SQL函数带有一个或多个参数并返回一个值

以下是SQL函数的分类：单行函数、分组函数、分析函数

一、单行函数

单行函数关于从表中查询的每一行只返回一个值

能够显现在SELECT子句中和WHERE子句中

单行函数能够大致划分为：

字符函数：同意字符输入同时返回字符或数值

数值函数：同意数值输入并返回数值

日期函数：对日期型数据进行操作

转换函数：从一种数据类型转换为另一种数据类型

通用函数：NVL函数、DECODE函数单行函数的的语法：

function_name(columnleexpression,[arg1,arg2.….])

function_name:函数的名称

culumnle:数据库列名

expression:字符串或运算表达式

argl,arg2:函数中使用的参数

时刻类型函数

SYSDATE返回当前的系统时刻

ADD_MONTHS(date,x)返回加上x月后的日期DATE的值，X能够是任意的整数，假如结果的月份

中所包含的日份量不于DATE的月份的日份量，那么返回结果月份的最后一天，假如不小于,

那么结果与DATE的日份量相同。

LAST_DAY(日期)指定日期所在月份的最后一天的日期，

TRUNC(日期，'MONTH\YEAR，)返回指定月份的第一天。

日期运算：

日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果

日期函数包括：ADD_MONTHS、MONTHS_BETWEEN>LAST_DAY,ROUND,NEXTJDAY、TRUNC

范例一：

返回datel与date2之间相差的天数。该值是一个数值，其小数部分代表一天的几分之几。

SQL>selectto_dateC2010-01-31*,'yy-mm-dd，)-to_date('2010-01-01",'yy-mm-dd')as

相差天数fromdual;1相差天数是一个别名)

相差天数

30

范例二：

返回date1与date2之间的时刻间隔

selectto_date(,2020-01-0110:30*,'yy-mm-ddhh:mi')

-to_date(,2020-01-3109:31",'yy-mm-ddhh:mi')fromdual;

T0_DATE('2020-01-0110:30',"YY-MM-DDHH:MI')T0_DATE('2020-01-3109:31',"YY-MM-DDHH:MI')

-29.959028

2、字符函数

Initcap(char)：将首字母转换为大写

eg：SelectinitcapChello^fromdual;>Hello

Lower(char)：转化为小写

eg：Selectlower('FUN')fromdual;>fun

Upper(char)：转化为大这与

eg：Selectupper('sun')fromdual;>SUN

Ltrim(char,set)：从左边开始截取字符(一样用来截取空格)

eg：Selectltrim('xyzadams'/xyz')fromdual;>adams

Trim():从两端截取

eg：SQL>SELECTTRIM。"FROM'abcda')FROMdual;--->abc

Rtrim(char,set)：从右边开始截取字符(右截空格)

eg：Selectrtrim('xyzadams'/ams')fromdual;>xyzad

Translate(char,from,to)：替换单个字符

eg：Selecttranslate('jack'j'b')fromdual;>back

Replace(char,searchstring,[repstring])：替换多个字符

eg：Selectreplace('jackandjue'/j':bl')fromdual;>blackandblue

Instr(char,m,n)：返回一个数值型，标识截取的字符的位置，从1开始计，第一次显现的

位置

eg：Selectinstr('worldwide'd')fromdual;>5

Substr(char,m,n)：从第m开始截取n个字符

eg：Selectsubstr(abcdefg,3,2)fromdual;>cd

Concat(expr1,expr2)：合并

eg：Selectconcat('Hello'/world')fromdual;>Helloworld

作用：以UPPER。为例

1.UPPER。强制大写

如：SELECTUPPER('hellen')FROMdual;

范例：一样用户在查询一个人的姓名的时候可能考虑到那个人的姓名是以大写字母储存的依

旧以小写字母储存的呢？

SELECT*FROMempWHEREename=UPPER(,smith');

3、字符函数

以下是一些其它的字符函数：CHR和ASCII、LPAD和RPAD、TRIM、LENGTH、DECODE

1、CHR和ASCII：通过一个字符求ASCII码

SQL>selectchr(67)fromdual;

C

C

2、LPAD和RPAD：左填充、右填充

SQL>SELECTLPAD(，abc；10,田)FROMdual;

LPAD('ABC

*******abc

，abc不足10个字符，在其左边填加7个补齐10个，右填充同理

3.LENGTH():求给定的字符的长度如：

SQL>SELECTLENGTH('abcd')FROMdual;

LENGTH('ABCD')

4

假如是''中国"结果也是2,因为这是按字符算的

LENGTH。与LENGTHC0是等同的。

还有一个LENGTHB()L中国，的结果是4,是按字节算的。

LENGTH2。和LENTH4。差不多上按字符算的，我们常用的是LENGTH。和LENGTHB()

4.DECODE:相当于if语句如：

SQL>SELECTDECODE(id,l,'tom',2,'jack',3,'hellen')FROMstudent;

DECODE

tom

jack

hellen

假如id是123分别返回tomjackhelen相当于做了一个多行的判定

5.数字函数

函数范例结果

Abs(n)Selectabs(-15)fromdual;15

Ceil(n)Selectceil(44.778)fromdual;45

Cos(n)Selectcos(180)fromdual;-.5984601

Cosh(n)Selectcosh(O)fromdual;1

Floor(n)Selectfloor(100.2)fromdual;100

Power(m,n)Selectpower(4,2)fromdual;16

Mod(m,n)Selectmod(10,3)fromdual;1

Round(m,n)Selectround(100.256,2)fromdual;100.26

Trunc(m,n)Selecttrunc(100.256,2)fromdual;100.25

Sqrt(n)Selectsqrt(4)fromdual;2

Mod(m,n)Selectmod(4,2)fromdual;0

Sign(n)Selectsign(-30)fromdual;-1

dbms_random.value(x,y)Selectdbms_random.value(2,4)fromdual;3.980765

6.转换函数

转换函数将值从一种数据类型转换为另一种数据类型

常用的转换函数有：TO_CHAR、TCLDATE、TO_NUMBER

1、TO_DATE

SQL>SELECTTO_DATE(,2005-12-06','yyyy-mm-dd,)FROMdual;

TO_DATEC2005-

06-12月-05

2、TO_NUMBER

SQL>SELECTTO_NUMBER('100')FROMdual;

TO_NUMBER('100')

100

3、TO_CHAR

SQL>SELECTTO_CHAR(sysdate,'YYYY"年"fmMMn月nfmDDH日nHH24:MI:SS')FROM

dual;

TO_CHAR(SYSDATE,'YYYY”

2010年n月13日11:01:45

7.其它函数:NVL、NVL2、NULLIF

1、NVL

SELECTcomm,NVL(comm,0)FROM表

假如为空就用。来代替，不为空自己。

2、NVL2

有3个参数，假如第1个为空，就走第3个表达式，假设不为空，走第2个表达式

SELECTcomm,NVL(comm,comm,0)FROM表

3.NULLIF

假如两个表达式的值相等返回空，不相等返回第一个表达式

SQL>SELECTNULLIF(22,23)FROMdual;

NULLIF(22,23)

22

SQL>SELECTNULLIF(22,23)FROMdual;

NULLIF(22,23)

22

二、组函数

详细见组函数及分组统计一章

第九章约束

CREATETABLE深入

・为表增加约束

NOTNULL非空约束要求值不能为空

UNIQUE唯独约束要求值不能重复

PRIMARYKEY主键约束关于整个表表中的记录不能重复

FOREIGNKEY外键约束当多个表建立关联时设置的一个引用约束

CHECK检查检查某一个列的值要符合某一个规范，如年龄(age>18)

•设置表所在的表空间

•使用序列实现自动增长

一、什么是约束？

约束是在表上强制执行的一些数据校验规那么，被插入/修改/删除的数据必须符合在相关字

段上设置的这些约束条件

二、约束定义的语法

列级约束：在定义列的同时定义约束

语法：列定义约束类型

表级约束：在定义了所有的列之后定义的约束

语法：

列定义

[CONSTRAINT约束名]约束类型(列名)

约束名的命名规那么：

举荐采纳：表名一列名.约束类型简写

约束能够在创建表时就定义，也能够在创建完表后再添加

语法：ALTERTABLE表名

ADDCONSTRAINT约束名约束类型(要约束的列名)

三、各约束介绍

1.NOTNULL

(1)该列的值不能为空

(2)列级约束

如名字不能为空

createtabledepts(dept_idint,namevarchar(20)NOTNULL,descriptionvarchar(lOO));

假如创建了以后再加不能为空的约束能够以修改列的形式：如

altertableemployeemodifyempnamevarchar2(6)notnull;

2.UNIQUE

(1)要求该列的值唯独，承诺为空。注：Oracle承诺有多个空什和null

(2)列级约束、表级约束

(3)取名：表名一列名.uk

如：名字不能为空且唯独

createtabledepts(dept_idint,namevarchar(20)NOTNULLUNIQUE,descriptionvarchar(100));

假如是先创建了表来要增加约束能够写成：

altertabledeptsaddconstraintdepts_name_ukunique(name);--将名字加上唯独约束

3.PRIMARYKEY

(1)用来唯独标识这一行记录，一个表中只能有一个主键

⑵功能上相当于非空且唯独

(3)列给约束、表级约束

(4)取名：表名—列名_pk

例1：将id号设置主键(在列级上定义)

createtabledepts(

idintprimarykey,——＞将id设置为主键

namevarchar(lO)uniquenotnull,——＞名字不为空且唯独

descriptionvarchar(lOO));

例2：在表级上定义

CREATETABLEstudent(

firstnameVARCHAR(20),

lastnameVARCHAR(20),

descriptionVARCHAR(IOO),

[CONSTRAINTstudent_name_pk]PRIMARYKEY(firstname,lastname));

那个例子是联合主键，所谓联合主键确实是firstname和lastname他们的组合不重复，就认

为是有效。如AB和AC这不确实是重复。

⑸主键有一个最要紧的作用是：当两个表关联时，主外键时，要求引用的主表中的字段一

定要为主键。

4.FOREIGNKEY

(1)用于两表间建立关系，需要指定引用主表的那列

(2)列级约束表级约束

(3)命名:主表名一子表名一FK

⑷语法：

[CONSTRAINT约束名]FOREIGNKEY(子表外键列名)

REFERENCES主表名(主键列名)

如我们把depts表和employees表的通过dept_id关联起来。

一部门表注表

createtabledepts(

dept_idintprimarykey,

descriptionvarchar(20));

--职员表子表

createtableemployees(

emp_idintprimarykey,

addressvarchar(20),

dept_idint,

constraintdepts_employees_fk

foreignkey(dept_id)

referencesdepts(dept_id)一主键

)；

表之间的关联关系，建议创建好表之后通过ALTERTABLE语句来添加：

语法：

ALTERTABLE表名

ADDCONSTRAINT外键约束名

FOREIGNKEY(本表外键列名)

REFERENCES主表名(主表主键列名)

[ONDELETE[RESTRICT|CASCADE|SETNULL|NOACTION]]

例：

altertableemployees

addconstraintdepts_employees_fkforeignkey(depts_id)

referencesdepts(depts_id);

ONDELETE:用来指定在删除主表中的数据时，对关联表(从表)所做的相关操作。

RESTRICT(限制):跟NOACTION(没有动作)，成效一样，不采取动作，即当主表中的主键在

子表中被使用，那么不承诺修改此主键值。

CASCADE(级联)：级联更新子表

SETNULL(设置为空)：主表删除(更新)行，那么设置子表中的外键列为NULL。

例如：图

如上表employees表和depts表本来没有关系的，然而通过dept_id能够将他们联系起来。

注：depts表中的dept_id必须为主键。才能通过dept_id与employees表联合起来。

录depts表与employees表进行联合的时候，他们之间便有了联系，如：在depts表中一共有

四个部门部,部门号代号分别是1、2、3、4。

然而假如我们在employeess表中插记录时，就不能给某个人所在部门号设置为5,因为全然

没有那个部门。

假如我们在employees表中加了两个人差不多上2号部门，那么两个表关联下来以后就不

承诺我们删除depts表的2号部门了。别的部门只要是在employees表中没有被引用的都能

够被删除。

当子表中引用了主表中的某一个记录时，那么那个主记录不承诺删除。也不承诺更换。

我们能够在删除以后做一些操作如：

例：

SQL>altertableemployees

2addconstraintdepts_employees_id

3foreignkey(dept_id)

4referencesdepts(dept_id)

5ondeletesetnull;-一>删除后设置为空

也确实是主表的部门被删除后，子表的记录走向为空。也确实是原先引用该部门的记录现在

部门不存在了，该记录的引用就就变为空了。

5.CHECK

(1)对某列的值进行范畴限制、格式限制等

(2)列级约束、表级约束

(3)取名：表名一列名一ck

例：

SQL>createtablestudent(

2idintprimarykey,

3namevarchar(lO)uniquenotnull,

4ageintcheck(age>18)—能够使用like等

5)；

6.删除约束

先找到表的约束名称，执行：

selectTABLENAME,CONSTRAINTNAMEfromuserconstraintswhereowner=c,

其中CONSTRAINT_NAME为表的约束名称

然后删除约束：

altertableTABLE_NAMEdropconstraintCONSTRAINT_NAME[cascade];

使用CASCADE关键字使相关约束也失效.

如：删除sal上的约束

altertableemployeedropconstraintemployee_sal_ck;

例：

SQL>descuser_constraints;

名称是否为空？类型

OWNERNOTNULLVARCHAR2(30)

CONSTRAINT_NAMENOTNULLVARCHAR2(30)

CONSTRAINT_TYPEVARCHAR2(1)

TABLE_NAMENOTNULLVARCHAR2(30)

SEARCH_CONDITIONLONG

SQL>selectCONSTRAINT_NAME,TABLE_NAME,CONSTRAINT_TYPEfrom

user_constraintswhe

retable_name=,employees,;

能够看到employees表的表名、约束名字和类型等。

7.指定表空间

CREATETABLE()TABLESPACEspacename

如：

descuser_tablespace;>查看所有的表空间

createtablestudent(idint)tablespacesystem;

8.创建序列

序列：是Oracle的一个对象，表也是一个对象。所谓序列是指生成了一个有顺序的数字对象。

序号生成器

创建语法：

CREATESEQUENCE序列名MINVALUEnumberlSTARTWITHnumber2INCREMENT

BYnumber3nocache|cachenumber3;

MINVALUE：最小的值

INCREMENTBY：从那开始

STARTWITH：增量、步长

是否可被缓存

是否能够循环

从序列中取值:Select序列名.nextvalfromdual;—nextval序列中的下一个值

读取下一个值：Selectseq.nextvalfromdual;

读取当前值：Selectseq.currvalfromdual;

如创建一个序列：

SQL>createsequenceseq_dept

2startwith1

3incrementby2

4maxvalue10;

查看：

SQL>selectseq_dept.nextvalfromdual;

NEXTVAL

1

第执行一次就+2.

SQL>selectseq_dept.currvalfromdual;

NEXTVAL

1

currval不论执行多少次差不多上1.

往部口表中插：

insertintodeptsvalues（seq_dept.nextval,“贝才务部”）；

seq_dept.nextval是一个数字对象，与部门号对应。

修改序列：如将最大值改为100.

altersequenceseq_deptmaxvalue100;

删除序列：dropsequenceseq_dept;

第十章单查询查询

SQL查询的差不多语法结构:

SELECTselection_list选择哪些列

FROMtable_list从何处选择

WHEREprimary_constraint行必须满足的条件

GROUPBYgrouping_columns结果如何样分组

OREDERBYsort_columns如何样对结果排序

ROWNUMoffset结果限定

以emp表和部门表（dept）为例：

一、一般查询

1.查询所有记录的所有列

SELECT*FROMemp

2.查询特定行

SELECT*FROMempWHEREname=，纪小岚

3.查询特定列

SELECTname,sarlaryFROMemp;

4.去掉重复的值

SELECTDISTINCTdept_idFROMemp;

5.给列取别名

SELECTnameAS姓名