Oracle介绍

1、什么是Oracle
Oracle数据库是美国ORACLE公(甲骨文)提供的以分布式数据库为核心的一组软件产品，是目前最流行的
客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。
2、Oracle的特点
(1)支持多用户、大事务量的事务处理
(2)数据安全性和完整性控制
(3)支持分布式数据处理
(4)可移植性
3、Oracle体系结构介绍【重要】
数据库
Oracle数据库是数据的物理存储。这就包括(数据文件 ORA 或者 DBF、控制文件、联机日志、参数
文件)。其实 Oracle 数据库的概念和其它数据库(SQLSERVER、MYSQL)不一样，这里的数据库是一
个操作系统只有一个库。可以看作是Oracle 就只有一个大数据库。
实例
一个oracle实例有一系列的后台进程和内存结构组成，一个数据库可以有n个实例。实例之间互不
干扰。
数据文件（.DBF）
数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中，真正是在某一个或多个数据
文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦
数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删掉其所
属于的表空间才行。
表空间
表空间是Oracle对物理数据库上相关数据文件(ORA 或者 DBF 文件)的逻辑映射。一个数据库在逻
辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少
有一个表空间(称之为 system表空间)。
每个表空间由同磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能
属于一个表空间。
注：表的数据，是由用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多
个数据文件中。
由于oracle的数据库不是普通的概念，oracle是有用户和表空间对数据进行管理和存放的。但是表
不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一  个名字的
表!这里区分就是用户了!
--------简单来说Oracle中的表空间类似mysql的database数据库，可以在表空间下创建多个表

二、Oracle表空间和用户【重点】
1、oracle表空间
(1)、表空间分类
永久表空间
表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空
间中。但主要存放的是表，所以称作表空间
临时表空间
oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的
中间结果进行排序。重启数据库可以释放临时表空间
(2)、表空间操作
查看所有表空间
创建表空间
select * from sys.dba_tablespaces;

 

创建表空间
select * from sys.dba_tablespaces;
create tablespace 永久表空间名称 datafile ‘永久表空间物理文件位置【数据文件】.DBF’
size 100M -- size设置数据文件的大小
autoextend on next 10M -- autoextend 设置数据文件是否自动扩容，on是开启【开启后可以通过
“next xx”设置扩容大小】，off是关闭
maxsize 200M -- maxsize设置上限大小和autoextend配合使用，不设置就是无限
[permanent] -- 设置永久表空间，设置了就是永久表空间
[online]; -- 联机状态 ，以便数据库用户访问其中的数据。但是数据库管理员可以在对表空间进行维护，
备份或恢复操作时，令表空间脱机(offline)

 

 

表空间重命名

alter tablespace 原表空间名称 rename to 新表空间名称;

增加数据文件

ps：一个表空间可以有多个数据文件的，每个是数据文件属于一个表空间，数据文件就是在磁盘上存储数据的媒介 

alter tablespace 表空间名称 add datafile ‘数据文件路径’
size 大小
autoextend on next 大小
maxsize 大小

修改表空间的数据文件
PS1: 想要修改数据文件需要先将表空间改为离线状态“offline”，否则无法生效，因为online状态下，表空间在使用数据文件，有这个读写保护的
PS2: 修改成的文件需要存在 

alter tablespace 表空间名 rename datafile ‘原来数据文件的路径’ to ‘新的数据文件路径’;

修改数据文件大小

alter database datafile ‘数据文件路径’ resize 大小;

修改数据文件为自动增长

alter database datafile '数据文件路径' autoextend on next 大小 maxsize 大小; 

 

 

修改表空间状态
删除表空间
 
演示案例：移动数据文件
ps：在实际的开发中可能会遇到磁盘损坏，需要移动表空间文件移至安全路径
alter database datafile ‘数据文件路径’ resize 大小;
alter database datafile '数据文件路径' autoextend on next 大小 maxsize 大小;
1、联机
alter tablespace 表空间名 online；
2、脱机
alter tablespace 表空间名 offline；
3、修改为只读表空间
alter tablespace 表空间名 read only;
4、查看表空间状态
select * from DBA_TABLESPACES where TABLESPACE_NAME=’表空间名’;
drop tablespace 表空间名;
第一步：使用管理员用户操作，查看数据文件所在的表空间
select tablespace_name from dba_data_files where file_name=upper(‘数据文件路径’);
第二步：使表空间进行脱机
alter tablespace 表空间名 offline；
第三步：移动数据文件至指定的目录【如果使用linux就用mv指令，window可以用鼠标】
第四步：修改数据文件的位置
alter tablespace 表空间名 rename datafile ‘原来数据文件的路径’ to ‘新的数据文件路径’;
第五步：使用表空间联机
alter tablespace 表空间名称 online;

-- 查看全部表空间
select * from sys.dba_tablespaces;
 
 
 
2、Oracle用户--DCL【重点】
oracle用户和表空间挂钩的，每一个表空间下创建多个表，用户起到分隔数据的作用，是oracle权限的
重要组成
创建用户
-- 创建表空间---类似MySQL中创建数据库
create tablespace mytablespace datafile '/usr/local/dba/testdbf.DBF'  -- LINUX服
务器没有磁盘的直接是目录路径
size 100M -- size设置数据文件的大小
autoextend on next 10M -- autoextend 设置数据文件是否自动扩容，on是开启【开启后可以通
过“next xx”设置扩容大小】，off是关闭
maxsize 200M -- maxsize设置上限大小和autoextend配合使用，不设置就是无限
permanent -- 设置永久表空间，设置了就是永久表空间
online; -- 联机状态 ，以便数据库用户访问其中的数据。但是数据库管理员可以在对表空间进行维护，备
份或恢复操作时，令表空间脱机(offline)
-- 修改表空间名字
alter tablespace mytablespace rename to newtablespace;
--- 追加数据文件到表空间
alter tablespace newtablespace add datafile  '/usr/local/dba/testdbf_02.DBF'
size 100M -- 文件多大
autoextend on next 10M -- 每次扩容多少
maxsize 200M; -- 最大上限多少
-- 修改数据文件设置
alter database datafile '/usr/local/dba/testdbf_02.DBF' resize 500M;   -- 大小500
-- 查看某个表空间状态
select * from sys.dba_tablespaces  where TABLESPACE_NAME='NEWTABLESPACE';
-- 更改表空间状态位offline---离线状态
alter tablespace NEWTABLESPACE offline;
-- 更改表空间状态位online--联机状态
alter tablespace NEWTABLESPACE online;
create user 用户名 identified by "密码" default tablespace 表空间名; 

修改用户密码

1、传统sql语法（11g 可以使用）：alter user 用户名 identified by 新密码;
2、专用语法：password 用户名

 删除用户

drop user 用户名 cascade;
--- cascades删除时删除该用户所有表

完整演示

问题：用创建的用户登录发现无法登录，原因是当前用户创建了却没有赋予权限，无权访问

 

3、权限管理--DCL【重点】
权限管理是Oracle实现安全管理的一部分。通过授予不同用户的系统权限与对象权限，可以控制用户对
系统功能的数据库对象的操作
oracle权限可分为三种：系统权限，对象权限，角色权限
（1）、系统权限：
是指在系统级控制数据库的存取和使用的机制，即执行某种SQL语句的能力。例如启动/停止数据库，修
改数据库参数，连接数据库等权限。-----系统权限是针对于用户设置的，用户必须被授予相应的系统权
限，才能连接到数据库中进行想要的操作。
ps: 系统权限很多，此处说一下常见的权限，其余的自行百度
CREATE SESSION —————————— 连接数据库
CREATE TABLESPACE —————————— 创建表空间
ALTER TABLESPACE —————————— 修改表空间
DROP TABLESPACE ——————————   删除表空间
CREATE USER ——————————   创建用户
DROP USER —————————— 修改用户
CREATE TABLE ——————————   创建表
语句：
grant 系统权限1，系统全选2 ... to 用户 --- 赋予
revoke 系统权限1，系统全选2 ... from 用户 --- 收回

2）、对象权限：
是指用户对数据库对象的操作权限。数据库对象包括表，视图，序列和存储过程等。Oracle的对象权限
包括ALTER【更改】，DELETE【删除】，EXECUTE【执行】，INDEX【索引】，INSERT【添加】，
SELECT【查询】和UPDATE【刷新】。这些对象权限适用于不同的对象。
--- 表权限【用于赋予对其他用户【不同表空间下的】表操作的权限】
grant 权限1，权限2 on 用户B.表名 to 用户A；--- 赋予用户A户用于对于用户Bxxx表的xxx权限
revoke 权限1,权限2 on 用户B.表名 from 用户A; --- 收回用户A用于对于用户Bxxx表的xxx权限

--- 表空间权限： 新建的用户针对自己所属表空间的对象默认是无权操作的，想要操作就要使用下列指令赋
予其对于自己所属表空间对象的操作权限
grant unlimited tablespace to 用户名; --- 给用户授予全部的表空间的权限
revoke unlimited tablespace from 用户名; --- 收回户授予全部的表空间的权限

（3）、角色权限：

角色是可以授予用户的相关权限的组，该方法使权限的授予，撤回更加容易管理

常用角色【内置角色】：
connect ----- 登陆权限
resource ----- 开发人员权限
dba ----- 管理员权限[全部权限]
语法：
grant 角色名称1,角色名称2 to 用户;

（4）、权限传递

ps：这个用于给其他账户赋权的，只有允许的传递的权限才可以赋予其他账户，只有系统权限和对象权限可以设置权限传递

权限传递有两种方式with admin option 和 with grant option，这两种方式的特点是
with admin option: 用户A可以将拥有权限赋予用户B，如果用户A的该权限被收回了，用户B是不会受到
影响，该方式用于系统权限
with grant option：用户A可以将拥有权限赋予用户B，如果用户A的该权限被收回了，用户B也会被收回，
该方式用于对象权限
with admin option:
ps：用户A可以将拥有权限赋予用户B，如果用户A的该权限被收回了，用户B是不会受到影响，该方式
用于系统权限
with grant option：
ps：用户A可以将拥有权限赋予用户B，如果用户A的该权限被收回了，用户B也会被收回，该方式用于
对象权限
（5）、角色定义
角色在oracle中是一组权限的结合体，角色中可以包含很多权限。当把角色赋予用户时，用户就可以具
有角色内的所有权限。Oracle中角色分为两类
<1> 系统预定义角色
<2> 管理员自建角色
1、新建两个用户
create user tim1 identified by "tim1" default tablespace test_ts;
create user tim2 identified by "tim2" default tablespace test_ts;
2、system用户赋予权限create session 给tim1用户
grant create session to tim1 with admin option;
3、切换用户至tim1，然后tim1用户赋予权限create session 给tim2用户；
grant create session to tim2 ;
4、此时tim1和tim2都可以登录，然后system用户回收tim1的create session权限，tim1肯定不能登
录，但是tim2还是可以登录的。
revoke create session from tim1;
1、scott用户赋予select权限给tim1
grant select on scott.emp to tim1 with grant option;
2、切换用户至tim1，然后tim1用户赋予权限给tim2用户；
grant select on scott.emp to tim2;
3、scott用户回收tim1用户的select权限，tim1没有对scott.emp查询的权限,tim2用户也会消失掉对
scott.emp表的查询权限
revoke select on scott.emp from tim1;
connect ----- 登陆权限
resource ----- 开发人员权限
dba ----- 管理员权限
1、创建角色语法：create role 角色名;
2、给角色授权：grant 权限 to 角色名;
ps：角色与角色之间也可以互相授权。例如：grant resource,dba to r1
3、把权限从角色中移除：revoke 权限 from 角色名；
4、删除角色：drop role 角色名;

（6）、profile口令文件【了解】
ps：对账户进行高级管理【有效期，密码重复次数...】
<1>、账户锁定
<2>、赋予用户口令
create profile 口令名称 limit 参数
下面这些limit的可选参数：
password_life_time (天/unlimited[无限]) ---- 有效天数
password_grace_time (天) --- 宽限天数
failed_login_attempts 次数 --- 输入错误多少次
password_lock_time (天); --- 锁定几天
....
alter user 用户名 profile 口令;

 

<3>、用户解锁

alter user 用户名 account unlock;

<4>、用户锁定

alter user 用户名 account lock; --- 永久锁定

<5>、删除口令

drop profile 口令名称 cascade;
<6>、修改口令

alter profile 口令名 limit 口令限制代码；

三、数据类型

oracle中数据类型分三类：数字型、字符型、日期型

1、数字类型

(1). number：长度不限，只要是数字就行
(2). number(x)：为整数，并且长度不超过x位
(3). number(x,y)：存在小数部分，总长度不超过x位，小数部分为y位
ps:也可以使用int和decimal之类的，number相当于int+double整合版本

2、字符型

(1). varchar2(x):长度不超过x位【oracle推荐】
(2). varchar(x):长度不超过x位
(3). char(x):固定长度是x位，不够则补空格
ps：
1、char是固定长度，如果内容无法填充满，会将剩余的用空格补充；varchar2/varchar都是可变长度，设
置只是一个最大长度，如果没有填充满也不会补空格
2、varchar是标准sql里面的；varchar2是oracle提供的独有的数据类型。
3、varchar对于汉字占两个字节，对于数字，英文字符是一个字节，占的内存小；varchar2一般情况下把所
有字符都占两字节处理。
具体要看数据库使用的字符集，比如GBK，汉字就会占两个字节，英文1个，如果是UTF-8，汉字一般占3个字
节，英文还是1个。
4、varchar对空串不处理；varchar2将空串当做null来处理。
5、varchar存放固定长度的字符串，最大长度是2000；varchar2是存放可变长度的字符串，最大长度是
4000。

3、日期型

(1)、date:只需要日期 ，占用8字节，yyyy-MM-dd
(2)、timestamp：需要日期+时间，占用12字节

四、自增主键

ps：oracle和mysql不同，不能使用auto_increment 设置主键，需要先创建一个自增序列【序列就是一个对象】

-- 1.先创建一个自增序列对象
create sequence 序列名【自定义】 属性 ;
可选属性：
increment by 1 -- 每次自增1，写负数就是自动减少
start with 1 -- 开始值
nomaxvalue -- 没有最大值
cache 20 -- 设置取值缓存数，先缓存一些数出来，效率快
end with 65535 -- 结束值 ，一般不用设置上限---和nomaxvalue二选一
nominvalue -- 没有最小值，一般用于自减的时候

-- Oracle 12 c 可以利用默认约束+自增序列实现主键自增
-- 自增序列.nextval:获取下一个值
create table tab02(
  tid number(11) default 自增序列.nextval,
  price number(11,2),
  address varchar2(111),
  addtime date,
  primary key(tid)
);

 旧版本--11g【通用写法】
create table tab01(
  tid number(11),
  price number(11,2),
  address varchar2(111),
  addtime date,
  primary key(tid)
);
-- 在插入时，使用自增序列
insert into tab01 values(my_auto_increment.nextval,20.50,'济南',to_date('2023-01-
03','yyyy-MM-dd'));

五、DML语句
Oracle自带事务，执行DML语句后需要手动commit【提交】/rollback【回滚】
1、添加语句

基础版：
insert into 表名(字段1,字段2,字段3) values(值1,值2,值3...); --- 如果表后写字段，values
值对应表后声明字段；如果没写，就对应表字段---和mysql的规则一样
ps:
1、一次只能插入一行
2、时间类型不能直接写字符串，需要用函数to_date转换
to_date(时间,格式)： 格式有两种对应两种不同时间类型， 字段为date类型时”yyyy-MM-dd”。字段为
timestamp类型时“yyyy-MM-dd hh24:mi:ss”

2、修改语句

update 表 set 字段=值，字段=值 ... where 条件

3、删除语句

delete from 表 where 条件

where条件

1、算数运算： + ， - ，* ， /
2、比较运算：=，!= ，< , > ,>=,<=
3、介于之间：between A and B
4、包含不包含： in ， not in
5、非空不非空：is null ，is not null
6、关系运算： and ，or
7、模糊查询： like
8、子查询的全部/任意： any ， all
9、字段类型转换：
ps：有一些类型需要进转换才可以使用，比如：时间和字符串的互转
--- to_date (字符串时间，字符串格式)
字段为date类型时”yyyy-MM-dd”。字段为timestamp类型时“yyyy-MM-dd hh24:mi:ss”
--- to_char（其他类型,字符串格式）
可以将其他类型数据转为字符串类型的数据
--- to_number(字符串类型数字,数字的格式)
可以将字符串数字格式化为纯数字

六、DQL语句

1、基本结构

select 字段 from 表 where 分组前条件 group by 分组条件 having 分组后条件 order by 排序
条件;
--- 和mysql相比没有limit分页

2、Oracle伪列【独有,重点】
Oracle表的使用过程中，实际上表中除了我们设置的字段外，还有一些附加列，称为伪列；伪列可以和
正常字段一样被查询使用【不能增删改】，但是不存在于表中。

(1)、rownum（重点）
解释：rownum会在你的查询结果集中，为每一行标识一个行号【从1开始】，可以利用rownum查询行
数
注意：如果筛选条件中用rownum，那么只能用 < 或 <= 来查询，其余的都查询不到
作用：可以用于oracle分页查询

问题：如果后6行？rownum不能用除了</<=之外的运算符，怎么办

分页实现【重点】：

ps：oracle没有limit，想分页须使用rownum

(2)、rowid

解释：表中每一行数据再数据文件中有一个物理地址，rowid伪列返回的就是该物理地址

作用：

数据包准备：
-- 定义表people
create table peopletab(
pname varchar(10),
pmoney number(11,2),
pcity varchar(10)
);
-- 插入数据
insert into peopletab values('zhangsan',2500.00,'jinan');
insert into peopletab values('zhangsan',2500.00,'jinan');
insert into peopletab values('lisi',3000.00,'jinan');
insert into peopletab values('zhangsan',2500.00,'qingdao');
-- 提交
commit;

3、连接查询

(1)、传统sql写法

<1>、内连接/自然连接：将左右两个表中相关的数据查询出来
select 字段 from 表A inner join【或者简写为jion】 表B on 连表条件
<2>、外连接：
1、左外连接：左边表为驱动表，会查询出全部数据，右边是被驱动表，只出相对应的数据
select 字段 from 表A left outer join【left jion】 表B on 连表条件
2、右外连接：右边表为驱动表，会查询出全部数据，做边是被驱动表，只出相对应的数据
select 字段 from 表A right outer join【right jion】 表B on 连表条件
3、全外连接：左右连边表都是全部出来，就没有驱动被驱动了
select 字段 from 表A full outer join【full jion】 表B on 连表条件

(2)、笛卡尔积写法

<1>、内连接/自然连接的笛卡尔积写法：
ps：普通连接查询就是自然连接查询的笛卡尔积写法，这俩是一个东西，因此性能一样的
select 字段 from 表1,表2... where 连表条件 and 其他条件
<2>、外连接笛卡尔积写法：
1、左外连接笛卡尔积写法
ps： 其中这个(+) 就相当于告诉数据库以xxx表为被驱动表，另外一边全出
select 字段 from 表1,表2... where 表1的关系字段 = 表2的关系字段(+) and 其他条件
2、右外连接笛卡尔积写法
select 字段 from 表1,表2... where 表1的关系字段(+) = 表2的关系字段 and 其他条件

七、函数

1、数学函数

(1)、nvl(目标字段，默认数字【number类型】)---判断字段是否为空，如果不为空返回字段值，为空返回
默认值
(2)、abs(数字) --- 绝对值
(3)、mod(被除数，除数) --- 模运算，取余数
(4)、power(x，y) --- 幂函数，返回x的y次方
(5)、ceil(x) --- 向上取整，ceil(3.3) = 4
(6)、floor(x) --- 向下取整
(7)、round(x,y) --- 四舍五入，x被四舍五入的值，y保留几位小数

2、字符函数

(1)、add_months(d【时间类型，手写日期需要to_date转换】,n) --- 返回d这个日期完后n个月的日期

(2)、last_day(d) --- 返回d日期所属月份的最后一天

(3)、months_between(d1,d2) --- 返回两个日期的月份差

（4）、round(d,fmt) --- 对日期d根据不同维度进行四舍五入
fmt的格式有：yyyy/MM/dd/day[星期]/q[季度]/w[当月第几周]/iw[当年第几周]

3、聚合函数

(1)、sum ---- 求和
(2)、min 和 max --- 最值
(3)、avg --- 平均值
(4)、count --- 计数

4、分析函数

(1)、聚合函数分析函数

聚合函数（字段）over([partition by 字段][order by 字段]) --- 可以对聚合函数进行分析
--- partition by 字段 ：以xxx分组，是对聚合函数的结果进行分组
--- order by 字段 ： 排序

(2)、排名类分析

row_number()over([partition by 字段] order by 字段 ) --- 对每行数据进行一个排名，
row_number()类似于伪列rownum

rank()over([partition by 字段] order by 字段 ) ---对每行数据进行一个排名，如果数据相同会
标记相同的序号，后面的序号会越过

dense_rank()over([partition by 字段] order by 字段 ) ---对每行数据进行一个排名，如果数
据相同会标记相同的序号，后面的序号不会越过

5、行列转换

CREATE TABLE student(
id number(20) NOT NULL,
name varchar(50),
course varchar(50),
score number(3)
)
INSERT INTO student VALUES (1,'zhangsan', 'yuwen', 95);
INSERT INTO student VALUES (2,'lisi', 'yuwen', 99);
INSERT INTO student VALUES (3,'wangwu', 'yuwen', 80);
INSERT INTO student VALUES (4,'zhangsan', 'shuxue', 86);
INSERT INTO student VALUES (5,'lisi', 'shuxue', 96);
INSERT INTO student VALUES (6,'wangwu', 'shuxue', 81);
INSERT INTO student VALUES (7,'zhangsan', 'yingyu', 78);
INSERT INTO student VALUES (8,'lisi', 'yingyu', 88);

INSERT INTO student VALUES (9,'wangwu', 'yingyu', 87);
commit

一般显示方式

行列转换

八、存储过程语法

-- 定义
create procedur 存储过程名(参数列表【参数名 参数类型（in，out，inout） 数据类型】)
is
-- 声明部分 标量之类的
begin
-- 流程
end;
-- 调用
call 存储过程名（...）

1、if else

if 条件1 then 操作 ;
elsif 条件2 then 操作;
...
else 操作;
end if;

2 、case

case
when 条件1 then 操作;
when 条件2 then 操作;
...
else 操作;
end case;

3、loop循环

loop
-- 循环体
if 条件 then
  exit; -- mysql结束符 leave，oracle是exit;
end if;
end loop;

4、while

while 条件 loop
  -- 循环体
end loop

5、for

for 循环变量 in (select 语句) loop
-- 循环体
end loop;

6、游标

（1）、隐式游标

select 字段 into 变量 ....

（2）、显示游标

create procedure 名...
is
cursor 游标名 is select语句;
begin
  for 变量 in 游标 loop
    -- 循环体
  end loop;
end;

九、表分区

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表
进行分区。
表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到一个或多个表空间，
这样查询数据时，不至于每次都扫描整张表。

1、表分区创建
<1>、范围分区【range】
就是根据数据库表中某一字段的值的范围来划分分区

格式：

CREATE TABLE 表名 (字段 数据类型 约束...) -- 这一块是建表语句
PARTITION BY RANGE (某个字段) -- range范围分区
(
    PARTITION 分区名1 VALUES LESS THAN【LESS THAN是小于】 (值) [TABLESPACE 表
空间1],
    PARTITION 分区名2 VALUES LESS THAN (值) [TABLESPACE 表空间2],
    ....
    PARTITION 分区名n VALUES LESS THAN (MAXVALUE) [TABLESPACE 表空间n]
)
ps：表分区语法mysql和oracle基本一致，但是oracle可以将分区和tablespace进行组合

演示：

（1）、准备3个表空间

create tablespace pts01 datafile
'F:/java/oracle/product/11.2.0/tablespaces/pts01.DBF'
size 100M autoextend on next 10M maxsize 200M permanent online;
create tablespace pts02 datafile
'F:/java/oracle/product/11.2.0/tablespaces/pts02.DBF'
size 100M autoextend on next 10M maxsize 200M permanent online;
create tablespace pts03 datafile
'F:/java/oracle/product/11.2.0/tablespaces/pts03.DBF'
size 100M autoextend on next 10M maxsize 200M permanent online;

（2）、创建表分区

create table graderecord
(
sno varchar2(10), -- 编号
sname varchar2(20),-- 学生名
dormitory varchar2(3),-- 科目
grade int -- 分数
)
partition by range(grade) -- 创建范围表分区，以grade字段为范围条件，分成3分区
(
-- 例如：分区 '不及格' 当grade 值小于 60 数据存储到该分区，该分区所在表空间为pts01
partition bujige values less than(60) TABLESPACE pts01, --不及格
partition jige values less than(85) TABLESPACE pts02, --及格
-- maxvalue相当于条件分支语句的else条件，不满足上方的都会进来，如果是空值也会进入maxvalue
partition youxiu values less than(maxvalue) TABLESPACE pts03 --优秀
);

（3）、插入实验数据

insert into graderecord values('511601','魁','229',92);
insert into graderecord values('511602','凯','229',62);
insert into graderecord values('511603','东','229',26);
insert into graderecord values('511604','亮','228',77);
insert into graderecord values('511605','敬','228',47);
insert into graderecord(sno,sname,dormitory) values('511606','峰','228');-- 缺考
insert into graderecord values('511607','明','240',90);
insert into graderecord values('511608','楠','240',100);
insert into graderecord values('511609','涛','240',67);
insert into graderecord values('511610','博','240',75);
insert into graderecord values('511611','铮','240',60);
commit;

（4）、查看数据

select * from graderecord; -- 查看全部数据
select * from graderecord partition(bujige); -- 查看bujige分区数据
select * from graderecord partition(jige);-- 查看jige分区数据
select * from graderecord partition(youxiu);-- 查看youxiu分区数据

<2>、散列分区【hash】

散列分区是根据字段的hash值进行均匀分布，尽可能的实现各分区所散列的数据相等。

语法：

CREATE TABLE 表名 (字段 数据类型 约束...)
PARTITION BY HASH (某个字段) -- hash是散列
(
    PARTITION 分区名1 [TABLESPACE 表空间1],
    PARTITION 分区名2 [TABLESPACE 表空间2],
    ....
    PARTITION 分区名n [TABLESPACE 表空间n]
);

演示：

create table graderecord2
(
sno varchar2(10),
sname varchar2(20),
dormitory varchar2(3),
grade int
)
partition by hash(grade)
(
partition p1,
partition p2,
partition p3
);
insert into graderecord2 values('511601','魁','229',92);
insert into graderecord2 values('511602','凯','229',62);
insert into graderecord2 values('511603','东','229',26);
insert into graderecord2 values('511604','亮','228',77);
insert into graderecord2 values('511605','敬','228',47);
insert into graderecord2(sno,sname,dormitory) values('511606','峰','228');
insert into graderecord2 values('511607','明','240',90);
insert into graderecord2 values('511608','楠','240',100);
insert into graderecord2 values('511609','涛','240',67);
insert into graderecord2 values('511610','博','240',75);
insert into graderecord2 values('511611','铮','240',60);
insert into graderecord3 values('511611','铮','240',60);
commit;
select * from graderecord2; -- 查看全部数据
select * from graderecord2 partition(p1); -- 查看p1分区数据
select * from graderecord2 partition(p2);-- 查看p2分区数据
select * from graderecord2 partition(p3);-- 查看p3分区数据

<3>、列表分区【list】

列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。

语法：

CREATE TABLE 表名 (字段 数据类型 约束...)
PARTITION BY LIST (某个字段) -- list是列表
(
    PARTITION 分区名1 values(值,值,值...) [TABLESPACE 表空间1],
    PARTITION 分区名2 values(值,值,值...) [TABLESPACE 表空间2],
    ....
    PARTITION 分区名n values(值,值,值...) [TABLESPACE 表空间n]
);

演示：

create table graderecord3
(
sno varchar2(10),
sname varchar2(20),
dormitory varchar2(3),
grade int
)
partition by list(dormitory)
(
partition p1 values('229') ,
partition p2 values('228'),
partition p3 values('240')
);
insert into graderecord3 values('511601','魁','229',92);
insert into graderecord3 values('511602','凯','229',62);
insert into graderecord3 values('511603','东','229',26);
insert into graderecord3 values('511604','亮','228',77);
insert into graderecord3 values('511605','敬','228',47);
insert into graderecord3(sno,sname,dormitory) values('511606','峰','228');
insert into graderecord3 values('511607','明','240',90);
insert into graderecord3 values('511608','楠','240',100);
insert into graderecord3 values('511609','涛','240',67);
insert into graderecord3 values('511610','博','240',75);
insert into graderecord3 values('511611','铮','240',60);
commit;

select * from graderecord3; -- 查看全部数据
select * from graderecord3 partition(p1); -- 查看p1分区数据
select * from graderecord3 partition(p2);-- 查看p2分区数据
select * from graderecord3 partition(p3);-- 查看p3分区数据

<4>、复合分区

(1)、范围-散列分区

语法：

CREATE TABLE 表名 (字段 数据类型 约束...)
PARTITION BY RANGE (某个字段) -- 先进行范围的字段，只能是一个字段
subpartition by hash(字段1,字段2...) -- 后进行散列的字段，注意此处可以设置多个字段散列
(
partition 范围分区1 values less than(值1) [TABLESPACE 表空间]
          (
              subpartition 散列分区1 [TABLESPACE 表空间],
              subpartition 散列分区2 [TABLESPACE 表空间]
              ...
          ),
....
partition 范围分区n values less than(maxvalue) [TABLESPACE 表空间]
          (
              subpartition 散列分区3 [TABLESPACE 表空间],
              subpartition 散列分区4 [TABLESPACE 表空间]
              ...
          )
);

演示：

create table graderecord4
(
sno varchar2(10),
sname varchar2(20),
dormitory varchar2(3),
grade int
)
partition by range(grade) -- 范围
subpartition by hash(sname,dormitory) -- 散列
(
  partition p1 values less than(60) -- 成绩小于60的会被散列到sp1和sp2中
  (
      subpartition sp1,
      subpartition sp2
  ),
  partition p2 values less than(maxvalue) -- 其他成绩的会被散列到sp3和sp4中
  (
      subpartition sp3,
      subpartition sp4
  )
);

insert into graderecord4 values('511601','魁','229',92);
insert into graderecord4 values('511602','凯','229',62);
insert into graderecord4 values('511603','东','229',26);
insert into graderecord4 values('511604','亮','228',77);
insert into graderecord4 values('511605','敬','228',47);

insert into graderecord4(sno,sname,dormitory) values('511606','峰','228');
insert into graderecord4 values('511607','明','240',90);
insert into graderecord4 values('511608','楠','240',100);
insert into graderecord4 values('511609','涛','240',67);
insert into graderecord4 values('511610','博','240',75);
insert into graderecord4 values('511611','铮','240',60);
commit;
select * from graderecord4; -- 查看全部数据
select * from graderecord4 partition(p2); -- 查看60分上成绩
select * from graderecord4 subpartition(sp3); -- 查看p2下散列的分区
select * from graderecord4 subpartition(sp4);

(2)、范围-列表分区

语法：

CREATE TABLE 表名 (字段 数据类型 约束...)
PARTITION BY RANGE (某个字段) -- 先进行范围的字段，只能是一个字段
subpartition by LIST(字段1,字段2...) -- 后进行散列的字段，注意此处可以设置多个字段散列
(
partition 范围分区1 values less than(值1) [TABLESPACE 表空间]
          (
              subpartition 列表分区1 values(值,值，值...) [TABLESPACE 表空间],
              subpartition 列表分区2 values(值,值，值...) [TABLESPACE 表空间]
              ...
          ),
....
partition 范围分区n values less than(maxvalue) [TABLESPACE 表空间]
          (
              subpartition 列表分区3 values(值,值，值...) [TABLESPACE 表空间],
              subpartition 列表分区4 values(值,值，值...) [TABLESPACE 表空间]
              ...
          )
);

演示：

create table graderecord5
(
sno varchar2(10),
sname varchar2(20),
dormitory varchar2(3),
grade int
)
partition by range(grade) -- 成绩范围，分成及格/不及格
subpartition by list(dormitory) -- 列表，只按照科目分
(
  partition bujige values less than(60) -- 不及格
  (
      subpartition sp229_bjg values('229'), -- 229不及格
      subpartition sp228_bjg values('228'), -- 228不及格
      subpartition sp240_bjg values('240') -- 240不及格
  ),
2、分裂表分区
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆
分。
语法：
演示：
  partition jige values less than(maxvalue) -- 及格
  (
      subpartition sp229_jg values('229'), -- 229及格
      subpartition sp228_jg values('228'), -- 228及格
      subpartition sp240_jg values('240') -- 240及格
  )
);
insert into graderecord5 values('511601','魁','229',92);
insert into graderecord5 values('511602','凯','229',59);
insert into graderecord5 values('511603','东','229',26);
insert into graderecord5 values('511604','亮','228',77);
insert into graderecord5 values('511605','敬','228',47);
insert into graderecord5 values('511606','峰','228',99);
insert into graderecord5 values('511607','明','240',90);
insert into graderecord5 values('511608','楠','240',100);
insert into graderecord5 values('511609','涛','240',32);
insert into graderecord5 values('511610','博','240',75);
insert into graderecord5 values('511611','铮','240',60);
commit;
select * from graderecord5; -- 查看全部数据
select * from graderecord5 partition(jige); -- 及格区
select * from graderecord5 subpartition(sp228_jg);-- 228及格的
alter table 表 split partition 分区名 at【在】(值) into(partition 新分区1,partition
新分区2);  
drop table tstable;
CREATE TABLE tstable (
  tsid number(11) primary key,
  tsname varchar2(20),
  tssex varchar2(10),
  tsgrade number(11)
)  
PARTITION BY range (tsgrade) -- 基于成绩来范围分为及格不及格
(
    PARTITION p1 values less than(60), -- 不及格
    PARTITION p2 values less than(maxvalue) -- 及格
)
3、合并分区
合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，注意的是，不能将分区合
并到界限较低的分区。
语法：

ALTER TABLE 表名 MERGE PARTITIONS 分区1【低界限】,分区2【高界限】 INTO PARTITION 分区2
【高界限】;
演示：
insert into tstable values(1,'张三','男',30);
insert into tstable values(2,'张四','女',40);
insert into tstable values(3,'张五','男',50);
insert into tstable values(4,'张六','女',60);
insert into tstable values(5,'张七','男',70);
insert into tstable values(6,'张八','女',80);
insert into tstable values(7,'张九','男',90);
insert into tstable values(8,'张十','女',100);
commit;
-- 拆分分区，及格中还有良好和优秀两种分类，就要将p2【及格分区】分为LH 和YX 以80分分界
alter table tstable split partition p2 at(80) into(partition LH,partition YX);  
select * from tstable;
select * from tstable partition(p1);
select * from tstable partition(p2); -- 拆分后p2不存在
select * from tstable partition(LH); -- 新分区LH不包含80的
select * from tstable partition(YX); -- 新分区YX包含80的

演示：

drop table tstable;
CREATE TABLE tstable (
  tsid number(11) primary key,
  tsname varchar2(20),
  tssex varchar2(10),
  tsgrade number(11)
)  
PARTITION BY range (tsgrade) -- 基于成绩来范围分为及格不及格
(
    PARTITION bjg values less than(60), -- 不及格
    PARTITION jg values less than(80), -- 及格
    PARTITION yx values less than(maxvalue) -- 优秀
)
insert into tstable values(1,'张三','男',30);
insert into tstable values(2,'张四','女',40);
insert into tstable values(3,'张五','男',50);
insert into tstable values(4,'张六','女',60);
insert into tstable values(5,'张七','男',70);
insert into tstable values(6,'张八','女',80);
insert into tstable values(7,'张九','男',90);
insert into tstable values(8,'张十','女',100);
4、追加分区
注意：添加的分区界限应该高于最后一个分区界限。
语法：
演示：
5、删除分区
commit;
-- 要将及格和优秀统称为yx，就要合并分区jg和yx
ALTER TABLE tstable MERGE PARTITIONS jg,yx INTO PARTITION yx;
-- ps: 合并分区不能将内容合并到界限低的分区中；所谓界限就是分区创建时的values的范围
-- 例如： jg的values less than 80 ， yx的values less than maxvalue，所以yx界限高
-- 因此只能将jg合并到yx中
select * from tstable partition(yx);
ALTER TABLE 表名 ADD PARTITION 分区名 VALUES LESS THAN(值); --追加范围分区
ALTER TABLE 表名 ADD PARTITION 分区名; -- 追加hash分区
ALTER TABLE 表名 ADD PARTITION 分区名 values(值，值，值...); -- 追加list分区
drop table tstable;
CREATE TABLE tstable (
  tsid number(11) primary key,
  tsname varchar2(20),
  tssex varchar2(10),
  tsgrade number(11)
)  
PARTITION BY range (tsgrade) -- 基于成绩来范围分为及格不及格
(
    PARTITION bjg values less than(60),
    PARTITION jg values less than(80) -- 最大只能到80一下【79】
)
-- 正常追加80之前的都可以，因为有分区
insert into tstable values(1,'张三','男',30);
insert into tstable values(2,'张四','女',40);
insert into tstable values(3,'张五','男',50);
insert into tstable values(4,'张六','女',60);
insert into tstable values(5,'张七','男',70);
commit;
-- 追加80以上的因为没有分区，所以不行，因此要追加80以上的分区
ALTER TABLE tstable ADD PARTITION ps VALUES LESS THAN(120); -- 将范围设置到120下
【119】
insert into tstable values(6,'张八','女',80);
insert into tstable values(7,'张九','男',90);
insert into tstable values(8,'张十','女',100);
commit;
语法：
演示：
6、截断分区【重置分区】
截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即
使只有一个分区时，也可以截断该分区。
语法：
ALTER TABLE 表名 DROP PARTITION 分区名;
drop table tstable;
CREATE TABLE tstable (
  tsid number(11) primary key,
  tsname varchar2(20),
  tssex varchar2(10),
  tsgrade number(11)
)  
PARTITION BY range (tsgrade) -- 基于成绩来范围分为及格不及格
(
    PARTITION p1 values less than(60),
    PARTITION p2 values less than(80),
    PARTITION p3 values less than(maxvalue)
)
insert into tstable values(1,'张三','男',30);
insert into tstable values(2,'张四','女',40);
insert into tstable values(3,'张五','男',50);
insert into tstable values(4,'张六','女',60);
insert into tstable values(5,'张七','男',70);
insert into tstable values(6,'张八','女',80);
insert into tstable values(7,'张九','男',90);
insert into tstable values(8,'张十','女',100);
commit;
-- 先查询一下
select * from tstable;
-- 删除p2分区
alter table tstable DROP partition p2;
-- 再查询一下
select * from tstable;
select * from tstable partition(p1);
select * from tstable partition(p3);
select * from tstable partition(p2); -- 没有
ALTER TABLE 表名 TRUNCATE PARTITION 分区名;
演示：
7、分区重命名
语法：
演示：
drop table tstable;
CREATE TABLE tstable (
  tsid number(11) primary key,
  tsname varchar2(20),
  tssex varchar2(10),
  tsgrade number(11)
)  
PARTITION BY range (tsgrade) -- 基于成绩来范围分为及格不及格
(
    PARTITION p1 values less than(60),
    PARTITION p2 values less than(80),
    PARTITION p3 values less than(maxvalue)
)
insert into tstable values(1,'张三','男',30);
insert into tstable values(2,'张四','女',40);
insert into tstable values(3,'张五','男',50);
insert into tstable values(4,'张六','女',60);
insert into tstable values(5,'张七','男',70);
insert into tstable values(6,'张八','女',80);
insert into tstable values(7,'张九','男',90);
insert into tstable values(8,'张十','女',100);
commit;
-- 先查询一下
select * from tstable;
-- 截断p2分区
alter table tstable TRUNCATE partition p2;
-- 再查询一下
select * from tstable;
select * from tstable partition(p1);
select * from tstable partition(p3);
select * from tstable partition(p2); -- 存在分区，但是数据没了
ALTER TABLE 表名 RENAME PARTITION 原分区名 TO 新分区名;
drop table tstable;
CREATE TABLE tstable (
  tsid number(11) primary key,
  tsname varchar2(20),
  tssex varchar2(10),
  tsgrade number(11)
)  
PARTITION BY range (tsgrade) -- 基于成绩来范围分为及格不及格
(
    PARTITION p1 values less than(60),
十、Oracle索引
索引可以提高查询效率
1、Oracle索引注意：
（1）、索引列尽量再where子句中提取，对使用频繁的字段加入合适的索引
（2）、一个表中不是索引越多越好，索引的维护也要资源开销
（3）、将表和索引建立到相同表空间中，可以简化表空间管理；将表和索引建立到不相同表空间中，可
以提高访问性能
（4）、最好不要小表上建立索引【没必要】
（5）、当在大表上建立索引时，最好加上NOLOGGING【日志模式】选项, 可以节省重做日志的空间，
降低索引建立时间，提高效率
（6）、连表时，建议在连接列上建立索引
2、Oracle索引分类
(1)、btree索引
Btree索引是oracle默认索引类型，结构和mysql的是一样的
    PARTITION p2 values less than(80),
    PARTITION p3 values less than(maxvalue)
)
insert into tstable values(1,'张三','男',30);
insert into tstable values(2,'张四','女',40);
insert into tstable values(3,'张五','男',50);
insert into tstable values(4,'张六','女',60);
insert into tstable values(5,'张七','男',70);
insert into tstable values(6,'张八','女',80);
insert into tstable values(7,'张九','男',90);
insert into tstable values(8,'张十','女',100);
commit;
-- 将p2重命名为jg
ALTER TABLE tstable RENAME PARTITION p2 TO jg;
-- 就要用重命名的新名来查询
select * from tstable partition(jg);
oracle日志模式分为（logging，force logging，nologging）,默认情况是logging，就是会记录到
redo日志中，force logging是强制记录日志，nologging是尽量减少日志

Btree:多叉树或平衡多路查找树【概念重点】,一颗m叉的的BTREE特性如下：
1.m叉树中每个节点最多包含m个元素；
2.除根节点与叶子节点【没有子分支节点】外，每个节点至少有ceil(m/2)个孩子
3.若根节点不是叶子节点，则至少有两个孩子
4.所有的叶子节点都在同一层
5.每个非叶子节点【带有分支的节点】由n个key【上图蓝色小块】与n+1个指针【上图黄色小块】组成，其中
key范围是ceil(m/2)-1<=n<=m-

语法

创建索引：
create index 索引名 on 表名(字段,字段,....) [tablespace 表空间]
删除索引：
drop index 索引名;

演示

创建索引：
create index 索引名 on 表名(字段,字段,....) [tablespace 表空间]
删除索引：
drop index 索引名;

演示

(2)、位图索引
对于一些特殊的表，比如一个表中只有一列、数据重复度高【例如性别】，此时建立Btree索引对表进行
检索时，回返回将进一半的数据，Btree索引效率很低，此时就可以使用位图索引。具体什么时候用有一
个参考的变量“基数” oracle推荐如果一个列的“基数”小于1%适合建立位图索引【基数越小，重复率越
高】，例如一个表中10000条数据，一列中可取的值有100种，此时基数就是1%【值种类/总行数
*100%】，一个表中10000条数据，一列中可取的值有10种，此时基数就是0.1%
语法：

创建索引：
create bitmap index 索引名 on 表名(字段) [tablespace 表空间]
删除索引：
drop index 索引名;

演示

补充：oracle有个变量CREATE_BITMAP_AREA_SIZE 【位图区大小】，该变量值越大位图索引效率越
高，默认8MB

alter system set CREATE_BITMAP_AREA_SIZE=字节数【默认：8388608 （8MB）】

(3)、反向键索引
数据库会自动的表的主键创建Btree索引【主键索引】，通常的主键都是自增序列，如果使用默认的
Btree索引，当数据量巨大时会导致索引数据分布不均，叶子节点会偏向某一个方向，索引的键值会基本
分布在同一个叶块中，容易出现索引块争用问题。因此就可以使用反向键索引，将键值分散到不同叶块
中，减少索引块争用问题，提高效率
ps：反向键索引就是将索引键给倒转过来，例如：1001，1002，1003 反转为1001，2001，3001，这
样就会分散到不同的叶块中，从而降低索引块争用

语法

创建索引：
create [bitmap] index 索引名 on 表名(字段) reverse [tablespace 表空间]
删除索引：
drop index 索引名;

演示

注意

反向键索引虽然解决了索引叶争用问题，提高了效率；但是反向键索引会将索引散列到多个叶块中了，
如果进行范围查询时只能进行全表的扫描了，性能反而降低了