Oracle数据库

Oracle数据库

特点与优势

适合于大型项目的开发；银行、电信、电商、金融等各领域都大量使用Oracle数据库。

Oracle数据库是一种对象关系型数据库，在关系型数据库的基础上，引入了一些面向对象的特性。

Oracle数据库的产品可以免费下载，其服务是收费的，也就是该数据库用于商业目的时，必须取得Oracle的授权。

对比MySQL

和MySQL数据库的基本区别

• Oracle是大型的数据库而Mysql是中小型数据库；MySQL是开源的，Oracle是收费的，且价格昂贵。
• Oracle支持大并发，大访问量，是OLTP的最好的工具。
• 安装占用的内存也是有差别，MySQL安装完成之后占用的内存远远小于Oracle所占用的内存，并且Oracle越用所占内存也会变多。

数据库，用户，表之间的联系

mysql 数据库 用户–数据库–表

mysql数据库中一个用户被管理员DBA赋予权限之后可以创建多个数据库，每个数据库可以有多张表

oracle 数据库 数据库and实例–用户–表

oracle数据库中，只有一个数据库，然后有多个实例给这个数据库分配进程和内存区来存储数据到硬盘，在实例和数据库的联合之下建立有不同权限的用户，不同的实例可以创造不同的用户，不同实例创造的用户可以以相同的名字命名，可以把实例当做类的不同构造，名字相同参数不同但是产生结果不同，用户被授予权限可以创建表。

实例和数据库

实例和数据库之间的关系是：数据库可以由多个实例装载和打开，而实例可以在任何时间点装载和打开一个数据库

语法差别

有关大小写

MySQL在windows环境下大小写不敏感 在unix,linux环境下区分大小写，Oracle不区分

数据库字段

整数类型：MySQL的int 在Oracle为number

字符串类型 ：MySQL的varchar 在Oracle为varchar2

字段的属性约束

操作表的关键字大概相同

但是在字段的属性约束上Oracle与MySQL有区别

Oracle去除了MySQL的自增以及无符号和零填充

并且在对字段属性约束的定义上有所变化

MySQL中字段+约束的定义在Oracle中变成了字段+ constraint 关键字+约束命名+ 约束

这里的约束命名规范格式为约束名两位英文简写_数据库表名_列名中间以下划线分隔

关于事务

MySQL默认开启自动提交，需要手动开启事务

-- 使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/

-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交

-- 开始一个事务,标记事务的起始点
START TRANSACTION  

-- 提交一个事务给数据库
COMMIT

-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK

-- 还原MySQL数据库的自动提交
SET autocommit =1;

-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

SET autocommit = 1MySQL中事务默认开启自动提交

START TRANSACTION需要手动开启事务

Oracle默认关闭自动提交，默认自动开启事务

这就导致我们需要提交事务的时候手动开启提交事务

commit

需要回滚事务的时候手动开启回滚

rollback

关于DML

INSERT

对于MySQL，赋值时可以用value也可以用values

value和values的区别是

插入一行数据时values更快

插入多行数据时value更快

而Oracle只可以用values

关于DQL

联表查询

Oracle比MySQL多了FULL JOIN ON

查询的内容是左表右表全部的数据和满足ON条件的所有数据

实现字段自增

MySQL 字段自增可以添加 anto_increment

Oracle 不能直接提供自增约束，需要借助序列

序列的声明

CREATE SEQUENCE 序列名 序列名最好命名为se_+要引入序列实现自增的表，以便区分

序列可以在声明的时候添加一些属性，比如

[START WITH num] 起始值

[INCREMENT BY num] 增量数 一次以几个池容量增加

[MAXVALUE num|NOMAXVALUE] 最大容量

[MINVALUE num|NOMINVALUE] 最小容量

[CYCLE|NOCYCLE] 周期性 达到最大容量时报错还是回到起始值

[CACHE num|NOCACHE] 池化容量 在内存中预生成序列号，能快捷提供序列号

序列实现字段自增

如何拿到序列中的当前序列号和下一个序列号

nextval :取得序列的下一个内容

currval :取得序列的当前内容

select seqpersonid.nextval from dual;

select seqpersonid.currval from dual;

这里千万注意序列第一次使用必须先调用nextval获取一个序列值才能使用currval查看当前值

拿到序列当前值和下一个值就可以直接在insert语句中把这个值作为传递参数插到需要实现自增的字段上

对于序列的个人理解

• 可以把序列看做一个外部只为提供一个整数并且可以随调用而实现自增的方法
• 只需要在需要实现自增时引入方法来添加自增数即可
• 使用序列千万要一一对应，表引入的序列一定只为该表使用，否则如果被其他表使用，则会导致表的自增无规律

关于序列需要注意的地方

• 序列第一次必须先调用nextval获取一个序列值才能使用currval查看当前值
• 序列的起始值不能小于最小值
• 创建一个循环序列,则必须要设定最大值
• 如果创建带缓存的序列,缓存的值必须满足约束公式: 最大值-最小值>=(缓存值-1)*每次循环的值

实现查询分页

Oracle伪列定义

伪列指Oracle数据库中不显式存储，但是确实存在的不能修改，但是可以查询到表中每行特殊信息的一列

伪列分类

ROWID 返回查询行在数据库中实际的物理地址

这个物理地址不会随查询条件的改变而改变

比如在原有的查询条件上增加where判断筛选了一些行，但是查询到的行的物理地址不会随之改变

ROWNUM 返回当前行的行号

这个行号是当前查询的行信息的表示，从1到行的最后一行依次增加，会随查询条件的改变而改变

分页实现区别

MySQL 的分页通过limit关键字直接分页

语法为：limit (定位下标 ，当前页的行数)

Oracle 的分页通过伪列中的 ROWNUM 实现

ROWNUM可以随查询自动添加递增数字列，适合作为分页

想到了通过以 ROWNUM和所要查询字段联合查询出结果，再根据要求通过ROWNUM的值来筛选，发现会报错

原因是 ROWNUM不可以与> 大于号一起使用

可以通过子查询，在查询时对ROWNUM的查询加别名防止与> 使用报错

关于视图

视图是为了提高用户使用数据的安全性而引入的一个概念

视图的概念

明确视图像一张表而不同于表

他是通过表根据查询条件筛选出来的数据的组合，普通创建的视图可以在视图中完成对这些数据的增删查改

在没有设置视图只读权限时，可以在视图上加以修改，但是注意 视图上的修改会同时修改到原表数据

创建视图

创建视图需要有 DBA 或 create view权限

CREATE VIEW 视图名称 AS 子查询

创建视图时需要注意视图命名不能与用户的任何表名相同

因为操作视图和表的语句基本一致，会报错

SELECT * FROM stu_num 数据库怎么分辨stu_num是表还是视图

视图权限

设置视图权限可以让视图的功能更专注于查询和访问

CREATE VIEW 视图名称 AS 子查询 WITH READ ONLY

设置视图权限只读，这样视图就无法进行数据修改操作，只可以查询，就不会导致原表数据更改

CREATE VIEW 视图名称 AS 子查询 WITH CHECK OPTION

设置视图权限为检查，视图修改时必须满足子查询创建视图时的条件才可以操作成功，比如子查询要求stu_num > 10 ，视图就不能把这个字段的值修改到10以下

关于索引

索引为提高查询效率而生

索引分类

• 主键索引 primary key
• 唯一索引 unique key
• 普通索引 key
• 全文索引 fulltest key
• 复合索引 多个字段共同作为索引

复合索引与联合主键

一张表的主键只有一个这是原则

什么是联合主键

联合主键是指多条字段联合起来作为主键，也是一个主键，这是一种思想，且单独一条主键可以重复——便于查看相同元素

什么是复合索引

复合索引是指多条字段作为一个索引

最左前缀原则

联合主键也好，复合索引也好，他们的核心概念就是聚合多字段而产生的特殊索引

以他们为索引触发索引的查询规则必须满足最左前缀原则

查询条件必须满足索引第一个字段的内容 这就是最左前缀的要求

比如创建索引时以 (id,num) 为联合字段

where条件内可以没有nun，但是必须要有id

索引使用情况

索引不是越多越好，查询快的同时修改效率也会降低

• 索引在大数据表中效果明显
• 修改频繁的表不建议使用索引
• 表中值大多相同的列没有必要加索引

PL/SQL

PL/SQL是什么

为了实现流程控制和结构化查询的一种块结构语言

PL/SQL块

一个PL/SQL程序包含一个或多个PL/SQL块，块中可以声明变量以及异常处理

我把他看做java的类

DECLARE 
	//这部分声明变量或常量
BEGIN
	//PL/SQL块的sql语句
EXCEPTION
	//异常处理部分
END
	//结束

语法

变量常量声明

声明习惯与java相反，先写变量名后加数据类型

变量：str varchar2(10) := ‘fight’ ;

变量名 数据类型 := 初始值 初始值可有可无，赋值用:=

常量声明与变量声明不同的地方是加入constant

变量名 CONSTANT 数据类型 := 初始值

输出到控制台

dbms_output.put_line(输出内容)