ORACLE数据库

一、oracle数据库简介

（一）什么是oracle数据库

oracle数据库系统是美国ORACLE公司（甲骨文）提供的以分布式数据为核心的一组软件产品，是目前最流行的客户/服务器（CLIENT/SERVER）或B/S体系数据之一

分布式：举个简单例子，开学打扫卫生，如果一个人搞那这个人会比较累，但是给他十个人搞，分给每个人不同的任务，那每个人就会轻松很多；那么对一个服务器也是一样的，如果把所有的应用都架构在一个服务器上，那么这个服务器负担会很重，那么分布式的概念就是把这个应用程序进行拆解，然后拆解成很多功能模块，把不同的功能模块分别部署在不同的服务器上，那么他的运算压力是不是就被分担到很多服务器上，这就是分布式。（为什么oracle服务器可以承载很大压力呢，就是因为他可以架构成分布式的，也就是说一台服务器计算能力有限，但是我可以把oracle分别部署在不同的服务器上来分担压力）。

oracle通常应用于大型系统的数据库产品。

oracle数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。
ORACLE 数据库具有以下特点：

• 支持多用户、大事务量的事务处理
• 数据安全性和完整性控制
• 支持分布式数据处理
• 可移植性（在不同的数据库都可以安装数据库，并且格式都是通用的，也叫跨平台）

常用的行业：金融，因为这些行业对数据安全要求特别高，不能有差错，oracle卖的是服务，你数据库出问题了，他派人给你解决

（二）oracle数据库的体系结构

1.数据库

Oracle 数据库是数据的物理存储。这就包括（数据文件 ORA 或者 DBF、控制文件、联机日志、参数文件）。其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是 Oracle 就只有一个大数据库。

2.实例

一个Oracle实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。一个数据库可以有 n 个实例。

3.数据文件（dbf）

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

4.表空间

表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻
辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在
逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为 system 表空
间)。
每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

注：表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。由于 oracle 的数据库不是普通的概念，oracle 是有用户和表空间对数据进行
管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用
户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！

5.用户

用户是在表空间下建立的。用户登陆后只能看到和操作自己的表, ORACLE的用户与 MYSQL 的数据库类似，每建立一个应用需要创建一个用户。

二、ORACLE的安装

（一）材料准备

（二）安装

（三）常见报错以及解决方案

（四）远程连接oracle

sqlplus 123456@127.0.0.1"">system/123456@127.0.0.1:1521/orcl

sqlplus 用户名/密@ip:端口/实例名

（五）可视化工具的安装与配置

三、项目实例

（一）项目介绍与需求分析

某某某市某某小学为了更好的管理学生的信息档案，决定委托我公司开发“学生信息档案管理系统”，考虑到学生信息数量庞大，数据开发并发量高，决定采用oracle数据库主要功能包括：

1.学生信息管理

（1）学生姓名设置

（2）学生出生日期设置

（3）学生性别设置

（4）学生班级设置

2.学生成绩管理

（1）学生成绩查询

3.班级信息管理

（1）班级名称设置

（2）班级班主任名称设置

4.授课信息管理

（1）授课名称设置

（2）授课老师设置

5.教师信息管理

（1）教师名称设置

（二）表结构设计

1.学生信息表（STUDENT）

2.学生成绩表（SCORE）

3.课程记录表（COURSE）

4.老师信息表（TEACHER）

5.班级信息表（CLASS）

（三）创建表空间

create tablespace waterboss
datafile 'c:\waterboss.dbf'
size 100m
autoextend on
next 10m

解释：
waterboss：为表空间名称
datafile： 用于设置物理文件名称
size： 用于设置表空间的初始大小
autoextend on： 用于设置自动增长，如果存储量超过初始大小，则开始自动扩容
next： 用于设置扩容的空间大小

（四）创建用户

create user dalian01
identified by 123456
default tablespace waterboss

wateruser： 为创建的用户名
identified by： 用于设置用户的密码
default tablesapce： 用于指定默认表空间名称

（五）用户赋权

grant dba to dalian01

给用户 wateruser 赋予 DBA 权限后即可登陆

四、表的创建、删除与修改

（一）创建表

CREATE TABLE 表名称 (
字段名 类型(长度) primary key,
字段名 类型(长度),
.......
);

1.数据类型

（1）字符型

CHAR : 固定长度的字符类型，最多存储 2000 个字节

VARCHAR2 :可变长度的字符类型，最多存储 4000 个字节

LONG : 大文本类型。最大可以存储 2 个 G

（2）数值型

NUMBER : 数值类型
例如：NUMBER(5) 最大可以存的数为 99999
NUMBER(5,2) 最大可以存的数为 999.99

（3）文本型

DATE：日期时间型，精确到秒
TIMESTAMP：精确到秒的小数点后 9 位

（4）二进制型（大数据类型）

CLOB : 存储字符,最大可以存 4 个 G
BLOB：存储图像、声音、视频等二进制数据,最多可以存 4 个 G

（二）修改表

1.增加字段语法:

ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型
[DEFAULT 默认值]...)

2.修改字段语法

ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型
[DEFAULT 默认值]...)

3.修改字段名语法：

ALTER TABLE 表名称 RENAME COLUMN 原列名 TO 新列名

4.删除字段名

-- 删除一个字段
ALTER TABLE 表名称 DROP COLUMN 列名
-- 删除多个字段
ALTER TABLE 表名称 DROP (列名 1,列名 2...)

（三）删除表

DROP TABLE 表名称

五、数据的增删改

（一）插入数据

语法1：

INSERT INTO 表名[(列名 1，列名 2，...)]VALUES(值 1，值 2，...)

语法2：

显式插入，需要把插入的值写全，若为空需要明确写出

INSERT INTO 表名VALUES(值 1，值 2，...)

语法3：

子查询批量插入

INSERT INTO 表名[(列名 1，列名 2，...)] SELECT .. FROM ..

• 一次插入一行
• 可以使用函数
• 插入行中没有列出的列，会被插入null，如果有默认值，那么就会插入默认值
• 插入的数据大小写敏感
• 日期格式敏感，当插入日期格式时，最好强制转换成日期类型，默认转换在环境变化汇总出错

（二）修改数据

UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，....WHERE 修改条件；

使用update 语句的时候，在事务没有结束之前该条数据会被锁住，其他用户无法修改此条数据

事务结束之后，该条数据的锁才会被打开，其他用户才可以修改这条数据

（三）删除数据

语法1：

DELETE FROM 表名 WHERE 删除条件;

语法 2：

TRUNCATE TABLE 表名称

比较 truncat 与 delete 实现数据删除？

• delete 删除的数据可以 rollback
• delete 删除可能产生碎片，并且不释放空间
• truncate 是先摧毁表结构，再重构表结构

六、ORACLE 查询

（一）单表查询

1.简单查询

（1）查询一张表的字段

select 字段1,字段2,字段3..... from  表

注意：字段与字段之间用 英文格式的, 隔开

（2）查询一张表中的所有字段

select * from 表

2.带条件查询

（1）带基本比较符的条件查询

基本比较符号：<,>,≤,≥,<>,≠,=

select * from 表  where  条件语句 （字段 基本比较符  数据）

（2）and 运算符

用来判断两个或者两个以上条件需要同时满足时使用

select * from  表 where 条件语句1 and  条件语句2

（3）or 运算符

用来判断两个或者两个以上条件需要同时满足一个时使用

如果我们用 or 运算符编写，SQL 非常繁琐，所以我们用 in 来进行查询

select * from  表 where 条件语句1 or  条件语句2

（4）and 与 or 运算符混合使用

用来判断两个或者两个以上条件逻辑复杂判断时使用

select * from  表 where 条件语句1 and  条件语句2 or 条件语句3

注：因为 and 的优先级比 or 大，所以我们需要用 ( ) 来改变优先级

（5）范围查询

可以用≤，≥，或者between .. and .. 来实现

select * from  表 where  字段 ≥ 数据1 and 字段 ≤ 数据2

或者

select * from  表 where  字段 between 数据1 and 数据2
select * from  表 where  字段 between 数据1 and 数据2

注：在oracle使用between .. and ..时，小的数字需要在前，大的数字在后，否则查询为null 能取到数据1和数据2两个离散的点

（6）模糊查询

select * from  表 where  字段 like '_%'
select * from  表 where  字段 not like '_%'

_：表示有且只有一个字符

%：表示0-n个字符

（7）区间 查询

select * from  表 where  字段 in （区间）
select * from  表 where  字段  not in （区间）

（8）空值查询

select * from  表 where  字段 is null 
select * from  表 where  字段 is not null 

2.去除重复记录

select distinct 字段1 .. from 表

只能加在第一个字段前，若加在聚合函数中可用于计算不同记录

3.排序查询

（1）升序排序

select * from 表  order by 字段 （asc）

系统默认升序，asc默认省略不写

（2）降序排序

select * from 表  order by 字段 desc

（3）多字段排序

select * from 表  order by 字段1 desc,字段2 

多字段排序时，越靠近order by 优先级越高，当前面字段相同时才会按照靠后字段排序

4.基于伪列的查询

在 Oracle 的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就
像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。
接下来学习两个伪列：ROWID 和 ROWNUM。

（1）ROWID

表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的
物理地址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的
标识表中的一行。由于 ROWID 返回的是该行的物理地址，因此使用 ROWID 可
以显示行是如何存储的。

select rowid,t1.* from t1 

结果如下：

（2）ROWNUM

在查询的结果集中，ROWNUM 为结果集中每一行标识一个行号，第一行返回 1，
第二行返回 2，以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行数。

select rowid,t1.* from t1 

我们的分页查询需要用到此伪列，后续小节详细讲解

5.聚合统计

ORACLE 的聚合统计是通过分组函数来实现的，与 MYSQL 一致。

（1）聚合函数

（1.1）求和 sum

需求：查询所有学生的成绩总和

select sum(score) as sum from score

旧字段 as 新字段，可以给字段重命名，其中as可以省略用空格代替

（1.2）求平均 avg

需求：查询所有学生的平均成绩

select avg(score) avg from score

（1.3）求最大值 max

需求：查询所有学生的最高成绩

select max(score) max from score

（1.4）求最小值 min

需求：查询所有学生的最低成绩

select min(score) min from score

（1.5）统计记录个数 count

需求：查询所有学生的最高成绩

select count(score) count from score

（2）分组聚合 GROUP BY

（2.1）什么是分组？分组就是按照某个字段进行分组，系统会把把相同的记录自动归为一组

（2.2）分组的目的？分组是为了做计算，计算用到的函数就叫聚合函数

（2.3）聚合函数的特点？组内运算：所有的聚合函数只在本小组内进行运算；不计算null值：当使用分组函数时，除了函数 count(*) 之外，其他分组函数都会忽略 null 行，当然 count(字段名) 也会忽略 null 行。

（2.4）分组函数的特性？压缩行：group by语句具有去重功能，每个小组只会显示一条记录；压缩列：在系统执行group by后，未出现在group by 后面的字段都将呗压缩掉，即：出现在group by 后面的字段才能出现在select、having、order by 之后；，但是where不受影响，因为where语句运行在group by之前

（2.5） 分组后条件查询 having

where是执行分组前的筛选，having是执行分组后的筛选

having必须配合group by的使用

当一条语句中筛选时where与having通用的时候，优先使用where，因为使用where筛选效率更高

where后面不能跟组函数，having后面可以跟组函

（二）连接查询

TA表：

TB表:

1.多表内连接查询

语法：

FROM 表1 INNER JOIN 表2 on 关联条件

特点：

保留两表中满足关联条件的所有记录

需求：查询学生的学号、姓名、成绩

select * from ta  inner join tb on ta.tid = tb.tid

旧表名 新表名：为给表重命名，此处省略as

2.左外连接查询

表1 LEFT JOIN 表2 on 关联条件

特点：

不但保留两表中满足关联条件的所有记录，并且补全左表（join 之后的永远为右表，另外一张为左表）

需求：查询学生的学号、姓名、成绩，如果没有成绩成绩的学生也要列出

select * from ta  left join tb on ta.tid = tb.tid

3.右外连接查询

表1 RIGHT JOIN 表2 on 关联条件

特点：

保留两表中满足关联条件的所有记录，并且补全右表

需求：查询学生的学号、姓名、成绩，如果没有姓名的也要列出

select * from ta  right join tb on ta.tid = tb.tid

4.全连接查询

表1 FULL JOIN 表2 on 关联条件

特点：

保留两表中满足关联条件的所有记录，并且分别补全左右表

需求：查询学生的学号、姓名、成绩，不论有没有成绩和姓名都要列出

select * from ta  full join tb on ta.tid = tb.tid

5.笛卡尔积

表1 INNER JOIN 表2

特点：

将表1的所有记录都去关联一遍表2的所有记录，即没有关联条件，列出两表可能出现的所有排列组合，慎用！！

需求：查询学生的学号、姓名、成绩，列出所有可能出现的组合

select * from ta  cross join tb 

（三）子查询

1.where和having语句中的子查询

（1）单行子查询

• 只返回一条记录
• 单行操作符

需求：查询01科目中大于01科目平均分的记录

select * from score t1 
where cid = 01 and  score > ( select round(avg(score))  from score where cid =01 )

需求：查询科目数比01学过的科目数多的学生的学号

select sid from score
group by sid
having count(cid) >(select round(count(cid)) from score where sid = 01)

（2）多行子查询

返回多条记录

多行操作符

（2.1）in 和 not in运算符

需求：查询（没）学过01科目的学生信息

select * from student
where sid in (select sid from score where cid =01 )

select * from student
where sid not in (select sid from score where cid =01 )

（2.2）exists 和 not exists

需求：查询（没）学过01和02这两门科目的学生学号

select sid from score t1 
where t1.cid = 01 exists (select 1 from score t2 where t2.cid = 02 and t2.sid = t1.sid )

exists：

• 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”
• 使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值否则返回假值
• 由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值
• 给出列名无实际意义

3.from语句中的子查询

from 子句的子查询为多行子查询

需求：查询学过01学生的学号、姓名、科目、成绩，要求使用子查询

select * from (select t1.sid,sname,cid,score from student t1
inner join score t2 on t1.sid =t2.sid 
)
where cid =01

3.select 字句中的子查询

select中的子查询必须为单行子查询

需求：查询学生的的学号，科目，成绩，出生年月

select score.*
,(select sname   from student where student.sid=score.sid) sname
,(select sage   from student where student.sid=score.sid) sage  from score

score.*：代表score表中的所有字段

（四）分页查询

1.简单分页

需求： 分页查询学生表，每页十条记录

分析：我们在 ORACLE 进行分页查询，需要用到伪列 ROWNUM 和嵌套查询
我们首先显示前 10 条记录，语句如下：

select rownum,ta.* from  score ta 
where rownum≤10

分析：我们在 ORACLE 进行分页查询，需要用到伪列 ROWNUM 和嵌套查询
我们首先显示前 10 条记录，语句如下：

select rownum,ta.* from  score ta 
where rownum>10 and rownum ≤ 20

嗯？怎么没有结果？
这是因为 rownum 是在查询语句扫描每条记录时产生的，所以不能使用“大于”符号，只能使用“小于”或“小于等于” ，只用“等于”也不行。那怎么办呢？我们可以使用子查询来实现

select * from (
select rownum as rn,ta.* from  score ta 
)
where rn>10 and rn ≤ 20

2.基于排序的分页

需求：分页查询学生表，每页十条记录，要去按照分数降序排序

select * from (
select rownum as rn,tb.* from (
select * from  score ta 
order by score
) tb
)
where rn > 10

（五）单行函数

1.字符函数

常用字符函数讲解：

（1）取长度函数

length(string)

语句：

select length('ABCD') from dual;

（2）截取函数

substr(string,start, len)
string 表示源字符串，即要截取的字符串,
start 开始位置，从1开始查找。如果start是负数，则从string字符串末尾开始算起
len 表示截取字符串长度

select substr('Hello SQL!', 1) from dual;     --截取所有字符串，返回'Hello SQL!'
select substr('Hello SQL!', 2) from dual;   --从第2个字符开始，截取到末尾。返回'ello SQL!'
select substr('Hello SQL!', -4) from dual;    --从倒数第4个字符开始，截取到末尾。返回'SQL!'
select substr('Hello SQL!', 3, 6) from dual;  --从第3个字符开始，截取6个字符。返回'llo SQ'
select substr('Hello SQL!', -4, 3) from dual; --从倒数第4个字符开始，截取3个字符。返回'SQL'

（3）去除开头或者结尾的字符串

trim

ltrim

rtrim

select trim('S' from 'SXXS') from dual;    --去除首尾字符串
select trim ( '  sxsx  ' ) from dual;      --去除首尾空格
select ltrim ( '  sxsx  ' ) from dual;     --去除开头空格
select rtrim( ' sxsx   ' ) from dual;      --去除结尾空格
select ltrim('SSSXXS' , 'S') from dual;    --去除开头空格
select Rtrim('SSSXXX' , 'X') from dual;    --去除结尾空格

（4）替换函数
replace(源字符串，要被替换的字符串,要替换成的字符串)
translate (源字符串，要被替换的字符串,要替换成的字符串)

select replace('(1996)',')', '--') from dual;   --将')'替换成'--'
select translate('(1996)',')', '--') from dual; --将')'替换成'-'
select translate('(1996)','()', '--') from dual; -- 将'('替换成'-',将')'替换成'-'
select replace('(1996)','()','--') from dual;  -- 将'()'替换成'--'

replace 和 translate 都是替换，只不过，translate 是逐一替换对应的字符串，而replace则是寻找并替换整个字符串

（5）填充函数

lpad (s,len,char)

rpad(s,len,char)

s： 是要填充的字符串

len： 是填充后字符串的长度

char： 是用于填充的字符

如果 s 的长度小于 len，则在左侧填充 char 直到长度达到 len。如果 s 的长度已经大于等于 len，则返回原字符串 s。注意中文s和char位置若为中文，则一个中文占两个长度

select rpad('s',4,'*') from dual;
*select rpad('中',4,'*') from dual
select rpad('中',4,'国') from dual;

（6）字符查找函数

instr(string,child_string,start,show_time)

string：表示源字符串
child_string：子字符串，即要查找的字符串
start：可选项，开始位置，默认从1开始。如果为负数，则从右向左检索
show_time：可选项，表示子字符串第几次出现在源字符串当中，默认第1次，负数则报错

select INSTR('city_company_staff', '_') from dual    --返回5
--表示从源字符串'city_company_staff'中第1个字符开始查找子字符串'_'第1次出现的位置

select INSTR('city_company_staff', '_', 5) from dual    --返回5
--表示从源字符串'city_company_staff'中第5个字符开始查找子字符串'_'第1次出现的位置

  select INSTR('city_company_staff', '_', 5, 1) from dual    --返回5
--表示从源字符串'city_company_staff'中第5个字符开始查找子字符串'_'第1次出现的位置

  select INSTR('city_company_staff', '_', 3, 2) from dual    --返回13
--表示从源字符串'city_company_staff'中第3个字符开始查找子字符串'_'第2次出现的位置

  select INSTR('city_company_staff', '_', -1, 1) from dual    --返回13
-- start参数为-1，从右向左检索，查找'_'字符串在源字符串中第1次出现的位置

  select INSTR('city_company_staff', '_', -6, 2) from dual    --返回5
--start参数为-6，从右向左检索，查找'_'字符串在源字符串中第2次出现的位置

（7）连接函数

concat (string1,string2)

select concat('hello ','word!') from dual

（8）取ASCII码

ASCII(string)

select ascii('A') from dual

（9）大小写转换函数

upper(string)

lower(string)

initcap(string)

select upper('app ss') from  dual
select initcap('app ss') from  dual  -- 将每个字母的首字母大写
select lower('APP SS') from  dual

2.数值函数

常见数值函数讲解：

（1）四舍五入

ROUND(n,i)

• 四舍五入
• n是任意数值，是必需的。i是整数，是可选的，忽略则默认为0
• 若i为0，则取整数位四舍五入；若 i < 0, 则对n四舍五入取整到小数点左侧第i位；若 i > 0, 则对n四舍五入取整到小数点右侧第i位

select round(3.141592653) from dual
select round(3.141592653 , 0) from dual
select round(3.141592653 , -2) from dual
select round(53.141592653 , -2) from dual
select round(35.41592653 , -1) from dual
select round(3.141592653 , 4) from dual
select round(-5.6) from dual

（2）截断函数

TRUNC(n,i)

n是任意数值，是必需的。i是整数，是可选的，忽略则默认为0

select trunc(3.141592653) from dual
select trunc(3.141592653 , 0) from dual
select trunc(3.141592653 , -2) from dual
select trunc(53.141592653 , -2) from dual
select trunc(35.41592653 , -1) from dual
select trunc(3.141592653 , 4) from dual
select trunc(-5.6) from dual

（3）向上取整

ceil(n)

select ceil(3.1415926) from dual

（4）向下取整

floor(n)

select floor(3.1415926) from dual

（5）求余函数

mod(n1 , n2)

select  mod(9 , 3) from dual

（6）平方根函数

sqrt(n)

select sqrt(121) from dual

（7）幂函数

power(x,y) ，求 x 的 y 次幂

select power(11,2) from dual
select power(8 , 1/3) from dual

3.日期函数

常用日期函数讲解：

（1）我们用 sysdate 这个系统变量来获取当前日期和时间

select sysdate from dual  -- 系统的当前时间
select systimestamp  from dual -- 可以得到系统的当前时间，该时间包含时区信息，精确到微秒
select dbtimezone  from dual  -- 返回数据库时区

（2）加月函数

add_months(r,n)

r是指定日期，n是要增加的月份数，如果n为负数，则表示减去的月份数

如果指定的日期是月份的最后一天，返回的也是新的月份的最后一天，如果新的月份比指定的月份日期少，将会自动调回有效日期

select to_char(add_months(to_date('2018-11-12', 'yyyy-mm-dd'), 1),
'yyyy-mm-dd'),
to_char(add_months(to_date('2018-10-31', 'yyyy-mm-dd'), 1),
  'yyyy-mm-dd'),
to_char(add_months(to_date('2018-09-30', 'yyyy-mm-dd'), 1),
  'yyyy-mm-dd')
from dual

（3）月份最后一天

last_day(r)

select last_day(sysdate) from dual

（4）两个日期间相差的月份数

months_between(r1,r2)

函数返回r1日期和r2日期直接的月份。当r1>r2时，返回的是正数，假如r1和r2是不同月的同一天，则返回的是整数，否则返回的小数。当r1<r2时，返回的是负数

select months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
to_date('2017-11-12', 'yyyy-mm-dd')) as zs, -- 整数
months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
  to_date('2017-10-11', 'yyyy-mm-dd')) as xs, -- 小数
months_between(to_date('2017-11-12', 'yyyy-mm-dd'),
  to_date('2018-10-12', 'yyyy-mm-dd')) as fs -- 负数
from dual

（5）日期四舍五入

round(r,f)

日期r按f的格式进行四舍五入。如果f不填，则四舍五入到最近的一天

select sysdate, -- 当前时间
round(sysdate, 'yyyy') as year, -- 按年
round(sysdate, 'mm') as month, -- 按月
round(sysdate, 'dd') as day, -- 按天
round(sysdate) as mr_day, -- 不填默认按天
round(sysdate, 'hh24') as hour -- 按小时
from dual

（6）日期截取

将日期r按f的格式进行截取。如果f不填，则截取到当前的日期

select sysdate, -- 当前时间
trunc(sysdate, 'yyyy') as year, -- 按年
trunc(sysdate, 'mm') as month, -- 按月
trunc(sysdate, 'dd') as day, -- 按天
trunc(sysdate) as mr_day, -- 不填默认按天
trunc(sysdate, 'hh24') as hour -- 按小时
from dual

4.转换函数

常用转换函数讲解：

（1）数字转字符串

select TO_CHAR(1024) from dual

（2）日期转字符串

select TO_CHAR(sysdate,'yyyy-mm-dd') from dual

（3）字符串转日期

select TO_DATE('2017-01-01','yyyy-mm-dd') from dual

（4）字符串转数字

select to_number('100') from dual

5.其他函数

（1）空值处理函数

nvl(string1,string2)

nvl函数的括号中可以填两个数据，数据的数据类型必须相同，如果第一个数据为空，则取第二个数据的值，第一个数据不为空，则取第一个数据的原值。

select NVL(NULL,0) from dual

nvl2(string1,string2,string3)

nvl2函数的括号中可以填三个数据，如果数据1为空，返回数据3的值，如果数据1不为空，返回数据2的值

nullif(string1,string2)

如果数据1和数据2相等的话，返回null，如果不相等，返回数据1

coalesce(string1,string2,string3,stringn)

作用是返回第一个不为null的数据，如果都为null，则返回null，本质上类似于nvl函数的循环使用

（2）decode 函数

decode(条件,值 1,翻译值 1,值 2,翻译值 2,…值 n,翻译值 n,缺省值)
根据条件返回相应值

select  decode ('hl','hh','le','el') from dual

（六）CASE WHEN 语句

1.语句

case when  条件 then 结果
[when .. then ..]
else 结果
end

如果省略了ELSE子句，则返回NULL

2.应用场景

（1）场景1：当分数大于60分时视为及格，其他情况视为不及格

select score.*
,case when score>60 then '及格' else '不及格' end  pj
from score

case when score= 'A' THEN '优' ELSE 0 END

‘优’和0数据类型不一致则报错：
[Err] ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER

这是因为THEN后边的值与ELSE后边的值类型应一致，否则会报错

（2）场景2：查询不各科及格与不及格的人数

select cid
,count(case when score>60 then '及格' end)  pj_high
,count(case when score<60 then '不及格'  end)  pj_low
,sum(case when score>60 then THEN 1  end)  pj_high2
,sum(case when score<60 then THEN 1 end)  pj_low2
from score
group by cid

（3）场景3：要求查询不同科目的成绩情况，结果按照学号，01,02,03的字段顺序显示

select sid
,max(case when cid=01 then score end ) "01"
,max(case when cid=02 then score end ) "02"
,max(case when cid=03 then score end ) "03"
from score
group by sid
order by sid

（七）分析函数

function ()over(partition by column order by column rows .. )

rows 默认值：rows between unbounded preceding and current row

rows between

unbounded preceding / X preceding /x following /current row /

and

unbounded following / x preceding /x following /current row

1.聚合类分析函数

聚合函数：

sum()

avg()

max()

min()

count()

SELECT score.*,round(avg(score)over()) FROM score
-- over()分组范围默认整张表 再求平均

SELECT score.*,round(avg(score)over(PARTITION BY cid),2) FROM SCORE
--  over(PARTITION BY cid) 表示按照cid进行分页之后再求平均

SELECT score.*,sum(score)over( ORDER BY  sid) FROM SCORE 
-- over( ORDER BY  sid) 表示 按照sid进行升序排序，并进行累加求分数总和

SELECT score.*,sum(score)over( PARTITION BY cid ORDER BY  score) FROM SCORE 
-- over( PARTITION BY cid ORDER BY  score) 表示表示按照cid进行分页之后，
再按照sid进行升序排序，并进行累加求分数总和

2.排序类分析函数

rank() – 跳跃排名

dense_rank() – 非跳跃排名

row_number() – 序号

SELECT score.*,rank()over(PARTITION BY  cid ORDER BY score desc)
FROM  score 

SELECT score.*,dense_rank()over(PARTITION BY  cid ORDER BY score desc)
FROM  score 

SELECT score.*,row_number()over(PARTITION BY  cid ORDER BY score desc)
FROM  score 

3.偏移类分析函数

lag(string,n,df_val) 向上偏移

lead(string,n,df_val) 向下偏移

string:需要偏移的字段

n:向上或者向下偏移的行数

df_val：偏移后未找到任何记录后的默认值

SELECT score.*,lag(score,1,null)over(PARTITION BY  cid ORDER BY score desc)
FROM  score

SELECT score.*,lead(score,1,null)over(PARTITION BY  cid ORDER BY score desc)
FROM  score 

1.什么是集合运算

集合运算，集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算
包括：

UNION ALL(并集)：返回各个查询的所有记录，包括重复记录。

UNION(并集)：返回各个查询的所有记录，不包括重复记录。

INTERSECT(交集)：返回两个查询共有的记录。

MINUS(差集)：返回第一个查询检索出的记录减去第二个查询检索出的记录之
后剩余的记录。

2.并集运算

3.交集运算

4.差集运算

七、ORACLE数据对象

（一）视图

1.什么是视图

视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图所对应的数据并不真正地存储在视图中，而是存储在所引用的数据表中，视图的结构和数据是对数据表进行查询的结果。根据创建视图时给定的条件，视图可以是一个数据表的一部分，也可以是多个基表的联合，它存储了要执行检索的查询语句的定义，以便在引用该视图时使用。

使用视图的优点：

• 简化数据操作：视图可以简化用户处理数据的方式。

• 着重于特定数据：不必要的数据或敏感数据可以不出现在视图中。

• 视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问
  权限。

• 提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接
  口。

2.创建或修改视图语法

CREATE [OR REPLACE] [FORCE] VIEW view_name
AS subquery
[WITH CHECK OPTION ]
[WITH READ ONLY]

选项解释：

OR REPLACE ：若所创建的试图已经存在，ORACLE 自动重建该视图；
FORCE ：不管基表是否存在 ORACLE 都会自动创建该视图；
subquery ：一条完整的 SELECT 语句，可以在该语句中定义别名；
WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；
WITH READ ONLY ：该视图上不能进行任何 DML 操作。

3.删除视图语法

DROP VIEW view_name

4.案例

（1）简单视图的创建与使用

什么是简单视图？如果视图中的语句只是单表查询，并且没有聚合函数，我们就称之为简单视图。
需求：创建视图 ：学号01的学生信息

create or replace view view_stu as
select * from student where sid = ‘01’ 

利用该视图进行查询

select * from view_stu where sid = '01'

就像使用表一样去使用视图就可以了。对于简单视图，我们不仅可以用查询，还可以增删改记录。
我们下面写一条更新的语句，试一下：

update view_stu set sname='王刚' where sid = '02'

再次查询：

select * from view_owners1

结果已经更改成功

我们再次查看表的数据，发现表的数据也跟着更改了。由此我们得出结论：视图其实是一个虚拟的表，它
的数据其实来自于表。如果更改了视图的数据，表的数据也自然会变化，更改了表的数据，视图也自然会变化。一个视图所存储的并不是数据，而是一条 SQL语句。

（2）带检查约束的视图

需求：根据成绩表创建一个视图，科目cid=02的记录

create or replace view view_sco as
select * from score where cid = '02'
with check option

执行下列更新语句：

update view_scoo set cid = '01' where sid = '02'

系统提示如下错误信息：

（3）只读视图的创建与使用

如果我们创建一个视图，并不希望用户能对视图进行修改，那我们就需要创建视图时指定 WITH READ ONLY 选项，这样创建的视图就是一个只读视图。

需求：将上边的视图修改为只读视图

create or replace view view_sco as
select * from score where cid = '02'
with read only

修改后，再次执行 update 语句，会出现如下错误提示

（4）创建带错误的视图

我们创建一个视图，如果视图的 SQL 语句所设计的表并不存在，如下

create or replace view view_temp as
select * from scost

scost 表并不存在，此时系统会给出错误提示

有的时候，我们创建视图时的表可能并不存在，但是以后可能会存在，我们如果此时需要创建这样的视图，需要添加 FORCE 选项，SQL 语句如下：

create or replace  force view view_temp as
select * from scost

此视图创建成功

（5）复杂视图的创建与使用

所谓复杂视图，就是视图的 SQL 语句中，有聚合函数或多表关联查询。

我们看下面的例子：

（5.1）多表关联查询的例子

查询学生学号，姓名，科目，成绩

create or replace view view_stusco as
select t1.sid,sname,cid,score from student t1
inner join score t2 on t1.sid = t2.sid

使用该视图进行查询

select * from view_stusco

尝试修改该视图的数据

update view_stusco set sname = ‘zhangsan’ where sid = ‘01’

可以修改

update view_stusco set cid = ‘05’ where sid = ‘01’

这次会发现，系统弹出

这个是什么意思？是说我们所需改的列不属于键保留表的列。

什么叫键保留表呢？

键保留表是理解连接视图修改限制的一个基本概念。该表的主键列全部显示在视图中,并且它们的值在视图中都是唯一且非空的。也就是说，表的键值在一个连接视图中也是键值，那么就称这个表为键保留表。
在我们这个例子中，视图中存在两个表，学生表（STUDENT）和成绩表（SCORE）, 其中 STUDENT 表就是键保留表，因为 STUDENT 的主键也是作为视图的主键。键保留表的字段是可以更新的，而非键保留表是不能更新的

（5.2）分组聚合统计查询的例子

需求：按照学号统计各个学生的平均分

create or replace view view_avg as
select sid,avg(score) from score 
group by sid
order by sid

此例用到聚合函数，没有键保留表，所以无法执行 update

（二）物化视图

1.什么是物化视图

视图是一个虚拟表（也可以认为是一条语句），基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化视图）。

物化视图与普通的视图相比的区别是物化视图是建立的副本，它类似于一张表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样

2.创建物化视图

CREATE METERIALIZED VIEW view_name
[BUILD IMMEDIATE | BUILD DEFERRED ]
REFRESH [FAST|COMPLETE|FORCE]
[
ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT
(next_time)
]
AS
subquery

BUILD IMMEDIATE： 是在创建物化视图的时候就生成数据

BUILD DEFERRED： 则在创建时不生成数据，以后根据需要再生成数据。默认为 BUILD IMMEDIATE。

刷新（REFRESH）：指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行同步。

REFRESH 后跟着指定的刷新方法有三种：FAST、COMPLETE、FORCE。FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE 刷新对整个物化视图进行完全的刷新。

如果选择 FORCE 方式，则 Oracle 在刷新时会去判断是否可以进行快速刷新，如果可以则采用 FAST 方式，否则采用 COMPLETE的方式。FORCE 是默认的方式。

刷新的模式有两种：ON DEMAND 和 ON COMMIT。ON DEMAND 指需要手动刷新物化视图（默认）；ON COMMIT 指在基表发生 COMMIT 操作时自动刷新。

3.案例

（1）创建手动刷新的物化视图

查询学生学号，姓名，科目，成绩

create or replace view mv_stusco1 as
select t1.sid,sname,cid,score from student t1
inner join score t2 on t1.sid = t2.sid

执行上面语句后查询

select * from mv_stusco1

这时我们向学生表（student）插入一条记录

insert into student (sid,sname) values ( '66','麻子')

再次执行上边的语句进行查询，会发现新插入的语句并没有出现在物化视图中。
我们需要通过下面的语句（PL/SQL），手动刷新物化视图：

begin
DBMS_MVIEW.refresh('MV_STUSCO1','C');
end;

或者通过下面的命令手动刷新物化视图：

EXEC DBMS_MVIEW.refresh('MV_STUSCO1','C');

注意：此语句需要在命令窗口中执行

执行此命令后再次查询物化视图，就可以查询到最新的数据了。DBMS_MVIEW.refresh 实际上是系统内置的存储过程，后续课程会讲到

（2）创建会自动刷新的物化视图，和上例一样的结果集

create materialized view mv_stusco2
refresh
on commit
 as
select t1.sid,sname,cid,score from student t1
inner join score t2 on t1.sid = t2.sid

创建此物化视图后，当 STUDENT 表发生变化时，MV_STUSCO2 自动跟着改变

（3）创建时不生成数据的物化视图

create materialized view mv_stusco3
build deferred
refresh
on commit
 as
select t1.sid,sname,cid,score from student t1
inner join score t2 on t1.sid = t2.sid

创建后查询物化视图，发现并没有数据

执行下列语句生成数据

begin
DBMS_MVIEW.refresh('MV_STUSCO3','C');
end;

再次查询得到上例结果

由于我们创建时指定的 on commit ,所以在修改数据后能立刻看到最新数据，无须再次执行 refres

（4）创建增量刷新的物化视图

如果创建增量刷新的物化视图，必须首先创建物化视图日志

create materialized view log on student with rowid；
create materialized view log on score with rowid

创建的物化视图日志名称为 MLOG$_表名称

创建物化视图

create materialized view mv_stusco2
refresh fast
 as
select t1.sid,sname,cid,score from student t1
inner join score t2 on t1.sid = t2.sid

注意：创建增量刷新的物化视图，必须：

• 创建物化视图中涉及表的物化视图日志
• 在查询语句中，必须包含所有表的 rowid ( 以 rowid 方式建立物化视图日志 )

当我们向地址表插入数据后，物化视图日志的内容：

SNAPTIME$$：用于表示刷新时间

DMLTYPE$$：用于表示 DML 操作类型，I 表示 INSERT，D 表示 DELETE，U表示 UPDATE

OLD_NEW$$：用于表示这个值是新值还是旧值。N（EW）表示新值，O（LD）表示旧值，U 表示

UPDATE 操作

CHANGE_VECTOR$$：表示修改矢量，用来表示被修改的是哪个或哪几个字段此列是 RAW 类型，其实

Oracle 采用的方式就是用每个 BIT 位去映射一个列。插入操作显示为：FE, 删除显示为：OO 更新操作则根据更新字段的位置而显示不同的值。

当我们手动刷新物化视图后，物化视图日志被清空，物化视图更新。

begin
DBMS_MVIEW.refresh('MV_STUSCO4','C');
end;

（三）序列

1.什么是序列

序列是 ORACLE 提供的用于产生一系列唯一数字的数据库对象

2.创建与使用简单序列

创建序列语法：

create sequence 序列名称

通过序列的伪列来访问序列的值

NEXTVAL 返回序列的下一个值

CURRVAL 返回序列的当前值

注意：我们在刚建立序列后，无法提取当前值，只有先提取下一个值时才能再次
提取当前值。

提取下一个值

select 序列名称.nextval from dua

提取当前值

select 序列名称.currval from dual

3.创建复杂序列

语法

CREATE SEQUENCE sequence //创建序列名称

[INCREMENT BY n] //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是 1

[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue

[{MAXVALUE n | NOMAXVALUE}] //最大值

[{MINVALUE n | NOMINVALUE}] //最小值

[{CYCLE | NOCYCLE}] //循环/不循环

[{CACHE n | NOCACHE}];//分配并存入到内存中 ，即定义存放序列的内存块的大小，默认为20
CACHE 值必须小于 CYCLE 值，CACHE < CEIL (MAXVALUE-MINVALUE) / ABS (INCREMENT)

4.案例

（1）有最大值的非循环序列

create sequence seq_test1
increment by 10
start with 10
maxvalue 300
minvalue 20

以上的错误，是由于我们的开始值小于最小值 。开始值不能小于最小值，修改以上语句

create sequence seq_test1
increment by 10
start with 10
maxvalue 300
minvalue 5

我们执行下列语句提取序列值，当序列值为 300（最大值）的时候再次提取值，系统会报异常信息

（2）有最大值的的循环序列

create sequence seq_test2
increment by 10
start with 10
maxvalue 300
minvalue 5
cycle ;

提取的值为：

由此我们得出结论，循环的序列，第一次循环是从开始值开始循环，而第二次循环是从最小值开始循环。

思考：下列语句是否会报错？为什么？

create sequence seq_test3
increment by 10
start with 10
minvalue 5
cycle ;

（3）带缓存的序列

我们执行下列语句

create sequence seq_test3
increment by 10
start with 10
maxvalue 300
minvalue 5
cycle
cache 50;

我们执行上边语句的意思是每次取出 50 个缓存值，但是执行会提示错误

上边错误提示的意思是：缓存设置的数必须小于每次循环的数。

我们缓存设定的值是 50，而最大值是 300，那么为什么还会提示这样的信息呢？其实我们的 cache 虽然是 50，但是我们每次增长值是 10。这样 50 次缓存提取出的数是 500 （50*10）

更改语句

create sequence seq_test4
increment by 10
start with 10
maxvalue 500
minvalue 10
cycle
cache 50;

下列语句依然会提示上边的错误，这是因为还存在一个 minvalue ，minvalue 和maxvalue 之间是 490 个数，也就是一次循环可以提取 490，但是我们的缓存是500。

再次修改

create sequence seq_test5
increment by 10
start with 10
maxvalue 500
minvalue 9
cycle
cache 50;

把最小值减 1，或把最大值加 1，都可以通过。

（5）修改和删除序列

修改序列：使用 ALTER SEQUENCE 语句修改序列，不能更改序列的 STARTWITH 参数

ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE

删除序列：

DROP SEQUENCE 序列名称

（四）同义词

1.什么是同义词

同义词实质上是指定方案对象的一个别名。通过屏蔽对象的名称和所有者以及对分布式数据库的远程对象提供位置透明性，同义词可以提供一定程度的安全性。同时，同义词的易用性较好，降低了数据库用户的 SQL 语句复杂度。同义词允许基对象重命名或者移动，这时，只需对同义词进行重定义，基于同义词的应用程序可以继续运行而无需修改。

你可以创建公共同义词和私有同义词。其中，公共同义词属于 PUBLIC 特殊用户组，数据库的所有用户都能访问；而私有同义词包含在特定用户的方案中，只允许特定用户或者有基对象访问权限的用户进行访问。

同义词本身不涉及安全，当你赋予一个同义词对象权限时，你实质上是在给
同义词的基对象赋予权限，同义词只是基对象的一个别名。

2.创建和使用同义词

语法：

create [public] SYONYM synooym for object

其中 synonym 表示要创建的同义词的名称，object 表示表，视图，序列等我们要
创建同义词的对象的名称

3.案例

（1）私有同义词

需求：为表STUDENT创建（私有）同义词 名称为STU

create synonym STU for STUDENT;

使用同义词

select * from STU

（2）公有同义词

需求：为表STUDENT创建（公有）同义词 名称为STU2

create synonym STU2 for STUDENT;

以另外的用户登陆，也可以使用公有同义词：

select * from OWNERS2 ;

（五）索引

1.什么是索引

索引是用于加速数据存取的数据对象，可以理解为：一本书中的 ‘目录’。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。

索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于下图的一棵“树”，而树的节点存储的就是每条记录的物理地址，也就是我们提到的伪列（ROWID）

2.普通索引

语法

create index 索引名称 on 表名(列名)

需求：我们经常根据学生姓名搜索学生信息，所以我们饿基于学生表的sname字段来建立索引

create index index_stu_sid on STUDENT(SNAME)

索引性能测试：

创建一个两个字段的表

create table T_INDEXTEST (
ID NUMBER,
NAME VARCHAR2(30)
);

编写 PL/SQL 插入 100 万条记录（后续学到）

BEGIN
FOR i in 1..1000000
loop
INSERT INTO T_INDEXTEST VALUES(i,'AA'||i);
end loop;
commit;
END;

创建完数据后，根据 name 列创建索引

CREATE INDEX INDEX_TESTINDEX on T_INDEXTEST(name)

执行下面两句 SQL 执行

SELECT * from T_INDEXTEST where ID=765432;
SELECT * from T_INDEXTEST where NAME='AA765432';

我们会发现根据 name 查询所用的时间会比根据 id 查询所用的时间要短

3.唯一索引

如果我们需要在某个表某个列创建索引，而这列的值是不会重复的。这是我们可
以创建唯一索引

create unique index 索引名称 on 表名(列名);

需求：在业主表的水表编号一列创建唯一索引

create unique index index_stu_sid on
STUDENT(sid);

4.联合索引

我们经常要对某几列进行查询，比如，我们经常要根据学历和性别对学员进行搜索，如果我们对这两列建立两个索引，因为要查两棵树，查询性能不一定高。那如何建立索引呢？我们可以建立复合索引，也就是基于两个以上的列建立一个索引

经常对几列进行查询,所以用到了组合索引，组合索引的第一个字段必须出现在查询组句中，并且不能跳跃，否则不会用到

语法

create index 索引名称 on 表名(列名,列名.....);

需求：根据姓名和学号建立索引

create index stu_index_idname
on STUDENT(sid,sname);

5.反向键索引

应用场景：当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。这样会增加查询的层数，性能会下降。建立反向键索引，可以使索引的值变得不规则，从而使索引树能够均匀分布

语法

create index 索引名称 on 表名(列名) reverse;

6.位图索引

使用场景：位图索引适合创建在低基数列上，即指指这一列出现的可能值较少
位图索引不直接存储 ROWID，而是存储字节位到 ROWID 的映射

优点：减少响应时间，节省空间占用

语法：

create bitmap index 索引名称 on 表名(列名);

需求：在STUDENT表的ssex列上建立位图索引

create bitmap index index_stu_ssex
on STUDENT(ssex)

7.优缺点

优点：

• 加快数据的 ‘检索速度’
• 可以保证列值的 ‘唯一性’（unique、主键）
• 实现表与表之间的 ‘参照完整性’（外键）
• 在使用 order by、group by 子句时，可以减少排序和分组的时间

缺点：

• 当对表数据进行 DML 操作的时候，索引自动维护，’降低 DML 操作的速度’
• 索引需要占 ‘物理空间’，同数据表的 ‘表空间’ tablespace 一样
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

使用原则：

‘主键’ 和 ‘外键’ 会自动创建索引，无需人工操作

选择正确的表：经常检索包含大量数据的表中小于 15% 的行

选择正确的列：多个表之间的关联关系

合理安排索引列：（A，B，C） A 最常用，B 次之，C 最末

8.查询表中所有索引

select * from all_indexes where table_name= ‘表名’

删除索引

drop index 索引名 on 表名