Oracle学习笔记

Oracle学习笔记

一. Oracle简介

基于关系型的数据库（RDBMS）(Oracle、MySQL、SqlServer、db2)：关系型类似于二维表。非关系型：NoSQL(not only sql)，redis/mongodb：基于key-value结构。

组成结构：RAC（PGA，SGA）

​ 一个PGA对应一个客户端，一个SGA对应多个PGA。

​ 两阶段提交：

​ 第一阶段：PGA -> SGA

​ 第二阶段：SGA -> 数据库（此阶段，会将一些重复/冗余的工作进行合并，从而减少访问次数。）

二. Oracle下载安装

Oracle官方网址：https://www.oracle.com

超级管理员：sys

普通管理员：system

普通用户：scott

注意：安装需要关闭防火墙和杀毒软件。

##三. Oracle使用

###基本概念

实体

类似于java中的类。

记录

java中的对象。表中的一行。

字段

java中的属性。表中的一列。

表

运算符

操作运算符：+ - / * %
关系运算符：> >= < <= != <>
逻辑运算符：or and not
连接符：concat ||

null

判断字段是否为null: is / is not
null的计算：任何数字和null计算，结果为null。

大小写

关键字/命令：大小写不敏感。
对于真实数据严格区分大小写。

###常用操作

字符：col [列名] for a[长度]
数字：col [列名] for 999..
set linesize [数值]; --设置显示宽度。
set pagesize [数值]; --设置显示页数。
set timing on; --打卡执行时间。
set timing off; --关闭时间。
purge recyclebin; --清空回收站。
comment on table [表名] is '注释'；
-- 注释
desc 表名; --查看表的结构。
host cls; --清屏。

四. SQL

函数

####多行函数

也称为组函数或聚合函数，能一次性操作多行。

count(); --统计记录条数。
count(*); --自动排空。
distinct; --去重。
max(); --求最大值。
min(); --求最小。
avg(); --求平均值。
sum(); --求和。

####单行函数

一次性操作一行。

#####字符函数

lower(); --变小写。
upper(); --变大写。
initcap(); --首字母大写。
substr(str, begin, len); --从begin开始数截取长度为len字符串。
length(); --字符数。
lengthb(); --字节数。（英文或数字一个字节等于一个字符，中文或者特殊符号占多个字节。）
instr(a, b); --在a中找b的位置。
lpad(a, b, c);
rpad(a, b, c); --字符串a要占b个位置，位置有空，在左/右填充符号c。
trim(); --去空格。
trim(a from b); --去掉b中的字符a。
replace(a, b, c); --把a中的b字符串替换成c。

#####数值函数

round(数字，位数); --四舍五入，保留n位小数。
trunc(数字，位数); --舍尾，舍去n位数。
mod(a, b); --求余。

日期函数

sysdate; --当前时间。（关键字不用加括号）
to_char(日期，格式)：--将日期转换成指定的格式。（日期能加减数字（默认是天的意思），日期与日期之间只能减不能加）
months_between(日期1， 日期2); --相差多少月。
add_months(日期，时间); --加多少月。
last_day(); --当前时间的最后一天。
next_day(a, '星期几'); --下一个星期几是几月几号。 
round(a,b); --对a日期的b进行四舍五入。

通用函数

nvl(a,b); --如果a为空则返回b，如果a不为空则返回a.
nvl2(a,b,c); --如果a为空则返回c，如果a不为空则返回b。
nullif(a,b); --如果返回一样返回null，如果两个不一样返回a。
coalesce(); --从左往右谁第一个不为null就显示谁。
decode(字段，条件1，返回值1，条件2，返回2， ... , 最后表达式); --字段如果满足条件1则返回值1，如果都不是则返回最后表达式。
case
when ... then
when ... then
else
end; --类似于java else if。

转换函数

隐式转换

​ 字符 <-> 数字/日期

显式转换

to_char(字符，格式)：
to_number(字符，格式)：
to_date(字符，格式)：

###分组

关键字：group by [列名]
对组筛选用having。
增强: group by rollup()
group by rollup(a, b)相当于：
	group by a, b
	group by a
	group by null

DQL

​ 数据查询语言select。

select [*]/[字段名 ...] 
[as] [别名] 
from [表名] 
[where] [列名] 
[between] [小（数字/日期）] [and] [大（数字/日期）] 
[like] [通配符_/%(支持字符\数字\日期)] 
[escape ’\‘] 
[not in (10, 20, 30)] 
[order by] [字段名|表达式|序号] [asc|desc] [,hiredate asc] 
[nulls last]；
--（别名可以加双引号也可以不加，如果不加则不能使用特殊符号和关键字，否则报错。not in括号中不能写null，如果写了null结果为空。排序默认为升序。升序asc，降序desc。排序时null是最大的）
--(where执行顺序从右往左查询。)

DML

​ 数据操作语言，insert、delete、update

####增加数据

insert into 表名（字段名1，字段名2，....）
--（动态插入字段名）[&表名（&xxx, &xxxx）]
values (字段值1，字段值2，...)
--(动态输入插入的值)[values(&empno, &xxxx, &xxxx)] (注意：字符和日期任然需要单引号。)

#####批量插入数据

创建新表（批量插入之前不存在的表）

create table [新表] 
as
select * /（列名 ）from [已存在的表] [where 判断条件]；

不创建新表

insert into [表名1](字段名1,字段名2)
select 字段名3，字段名4 from 表名2；

删除数据

delete from [表名] where [判断条件]; --把表中所有数据都删除了。可以回退。
truncate table 表名; --不可以回退。

回退问题

​ DML: insert update delete 可以回退。（可以进行事务操作）

时间问题

​ 对于少量数据：delete效率高，一行一行删除。

​ 对于海量数据：truncate效率高，先丢弃整张表，再创建一张新表。

闪回问题

​ delete支持闪回，truncate不支持闪回。

内存问题

​ delete不会释放空间（换两个地方存储数据[undo]）, truncate会。

​ delete会产生碎片，truncate不会。

​ 如果碎片需要整理碎片：

q. alter table [表名] move； b. 导出数据。

更新数据

update [表名] 
set 字段名1=字段值1，字段名2=字段值2，... 
where [判断条件]。

DDL

​ 数据定义语言 create/drop/truncate/alter table

创建表

create table [表名] （
	[字段名] [字段类型] [约束]，
	...
	[表级约束]
）

注意事项

权限和空间问题

表名的规定

​ 表名必须以字母开头

​ 表名只能包含大小写字母、数字、_、$、#

​ 表名的长度只能1-30个字符

​ 不能与数据库中其他对象重名（表、视图、索引、触发器、存储过程）。

​ 不能与保留字重名

​ 查看保留字：

​ 登录DBA账户。

修改表

增加新列

alter table [表名] add [列名] 类型；

修改列

alter table [表名] modify [列名] 类型;
--注意：blob/clob不能修改。

删除列

alter table [表名] drop column [列名];

重命名列

alter table [表名] rename column [旧列名] to [新列名];

删除表

drop table 表名 [purge];
--删除表只是把表放在了回收站。

回收站操作

show recyclebin; --查看回收站。
purge recyclebin; --清空回收站。
--还原回收站：闪回。

DCL

数据控制语言 grant，revoke

内连接\外连接\自连接\层次连接

多表连接查询

交叉连接（笛卡尔积）

所有情况的组合，不推荐使用。

select * from [表1], [表2];

内连接

select * from [表1],[表2] where [表1.列名 = 表2.列名];
select * from [表1] [别名] inner join  [表2] [别名] on [表1.列名 = 表2.列名];

外连接

左外连接

以左表为基准，去匹配右表数据。如果匹配成功，则全部显示。匹配不成功，显示部分。

select * from [表1] [别名] left join  [表2] [别名] on [表1.列名 = 表2.列名];
select * from [表1],[表2] where [表1.列名 = 表2.列名] [(+)]; --oracle独有。

右外连接

​ （等同于左外连接。）

全外连接

​ 左外 + 右外 - 去重

自连接

将一张表通过别名视为不同的表。

select * from [表] [别名1]，[表] [别名2] where [];

缺点：费性能。

解决方法 层次连接。

层次连接(***)

select level [字段名] from [表名]connect by prior [下层] = [上层] start with 当层节点上层节点的值 = x order by level;

子查询

​ 子查询可出现的位置：where、select、having、from。(不能出现再group by后面。)

​ select后只能出现单行值。

​ 主查询和子查询可以是也可以不是同一张表。

​ 子查询可以使用单行操作符（=，<）, 多行操作符（in()）

​ 子查询中的null

​ 如果！=null查不出任何数据。

​ =null 或者 != null会出现很多问题，子查询中不能有null。

​ 一般不在子查询排序，除非Top-N问题。

伪列

​ rowid：物理序列（18位）。

​ rownum：逻辑序列。

​ :new.列名：修改之后的数据。

​ :old.列名：修改之前的数据。

思考如何利用rowid删除重复数据。

rownum

​ 不同sql执行时，rownum的值不一致。

​ 相同sql执行时，rownum的值保持一致。

rowid

​ 一共18位：数据对象编号（前6位）+ 数据文件编号（依次3位） + 数据块编号（依次6位） + 行号（依次3位）

注意

在Oracle中，如果是更新操作是针对单表，可以批量更新。但如果是多表联合更新，则必须明确，不能出现歧义。

交并补

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

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

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

MINUS(补集)：返回包含在第一个查询中，但不包含在第二个查询中的记录。

集合运算的细节

各个集合的列数、类型必须保持一致。

四. 约束

对数据的限制条件：数据类型。

常见约束

检查约束（check）

唯一约束（Unique）

主键约束（Primary key）

主键和唯一约束的区别:
a.【核心区别】主键不能为Null。
b.主键可以是复合主键，也可以是单值主键。
c.一张表中只有一个主键或复合主键。但唯一键可以有多个，设置多次。

外键约束（Foreign Key）

非空约束（Not null）

默认约束（Default）

分类

列级约束

​ 作用于一个列。

​ 在列的后面。

​ 可以有多个约束（用空格分隔）。

​ 全部的六个约束都可以作用于列级约束。

表级约束

​ 作用于一个列/多列。

​ 表的后面。

​ 多个表级约束用逗号分割。

​ 主键、外键、唯一、检查可作用于表级约束。

注意事项

​ a. 报错：违反唯一约束条件，可能主键报错，也可能唯一约束报错。

​ b. 如果有多个约束，default必须放在第一位。

​ c. check约束和使用where一致。

​ d. 唯一约束可以是null，但不适用于null。可以有多个null。

约束命名

规范

​ 主键：PK_字段名

​ 检查约束：CK_字段名

​ 唯一约束：UQ_字段名

​ 非空约束：NN_字段名

​ 外键约束：FK_ 子表_父表

​ 默认约束：一般不需要命名。

注意事项：

​ 约束名是多表公用的， 多个表中的约束不能重名。

外键

​ A表中的a列对应B表中的b列，则a称为外键。a中的数据必须来自于b中。

​ 直接创建外键会报错，外键所指向的列必须是主键或者唯一约束的键。

级联删除与级联置空

​ 如果删除父表中外键所指向的列，2个策略：级联删除 | 级联置空。

​ 级联删除：当删除父表中的数据时，子表会跟着删除对应的数据。

​ 级联置空：当删除父表中的数据时，子表会跟着删除对应的置空，其它字段不受影响。

constraint FK_[表1] _[表2]  foreign key(表1.列名) references sub(sid)  [on delete cascade] (级联删除) / [set null] （级联置空）;

外键使用建议：

​ 当父表中没有相对应数据时，不要向子表中添加数据。

​ 不要更改父表数据数据，孤立子表中的数据。

​ 建议：在创建外键时，直接设置成联级删除或者联级置空。

​ 删除表，先删子表，后删父表

追加约束

​ 在创建时忘了加约束，后续可以追加约束。

第一类

唯一、主键、检查、外键约束

alter table 表名 add constraint 约束名 约束类型(约束字段);

第二类

默认（建议默认约束不起名字，constraint）、非空

alter table 表名 modify  字段名 constraint 约束名 约束类型;

删除约束

第一类

适用于唯一、主键、检查、外键、非空。

alter table 表名 drop constraint 约束名;

第二类语法

alter table [表名] modify 列名 default null; --本质是默认约束置为null。

完整性约束

站在保证数据的正确性、相容性，防止数据冗余等。

​ 域完整性： 对列的约束。数据类型、非空、检查、外键等。

​ 实体完整性： 对行的约束。主键约束、唯一约束等。

​ 引用完整性：不同表之间。外键。

​ 自定义完整性： 触发器（当执行一个操作时，会自动触发另一个操作）。自定义需求。

三大范式

1NF：确保每列的原子性（不可再分）。

2NF：

​ 宏观：每张表只描述一件事。

​ 微观：除了主键以外的其它字段，都依赖于主键。

​ 在做第二范式时将一个表的主键，设置为另一个表的外键，用于将两张表关联起来。

3NF：

​ 除了主键以外的其它字段，都不传递依赖于主键。

注意

​ 三大范式可以帮助我们规范数据的设计，好处是防止数据混乱、数据冗余（重复）。但是，很难严格排除出所有不满足的表，并且难以拆分。会一定程度影响性能。实际使用时需要在规范性与性能之间考量。

​ 要满足第N范式，必须先满足第N-1范式。

Power Designer

Power Designer设计好后可以自动生成sql语句。

五. 视图

属于数据库对象（表、视图、序列、同义词、触发器）之一

视图是一个虚表。建立在表的基础之上。

注意

​ 字段可以来自一张表或者多张表。

​ 修改的视图会影响原表。

​ 一般来说对视图仅仅进行查询操作，不要进行dml；

语法

create view 视图名 as select语句
[with check option] --限制对试图操作时，必须满足where子句。
/[with ready only] --视图只能读取。
--视图只建议查看，不建议DML，视图会删除原表。

优点

​ 简化查询

​ 增加数据的安全性

操作视图

​ DML + Query 和表完全一致。

​ scott用户权限不足以创建视图。

​ 通过sys授予scott创建视图。

​ 尝试修改。

​ 单表可以任意更新，一次性更新多张表，则必须唯一。

​ 修改的视图会影响原表。

删除视图

drop view 视图名;

简单视图与复杂视图

视图的增删改

如果非要对视图进行增删改还需要遵循一些严格的苛刻条件

​ 当视图存在以下之一时，不能insert/update

​ group by、distinct、组函数、列的定义为表达式。

​ 当视图中存在以下之一时，不能delete

​ group by、distinct、rownum伪列。

六. 录屏

oracle支持文字录屏

语法

spool [路径]; --开启。
spool off; --关闭。

七. 事务

作为的单个逻辑工作单元执行的一系列操作。

四大特性

ACID

Atomicity:原子性

​ 要么都成功，要么都失败。

Consistency:一致性

​ 事务执行前后总量保持一致。

Isolation:隔离性

​ 各个事务并发执行时互不干涉。

Durability:持久性

​ 持久化操作。

生命周期

​ MySQl：自动提交。

​ Oracle：手动提交。

Oracle:手动提交

​ 事务的开始标识：第一条DML。

​ 事务的中间过程：各种DML操作。

​ 结束：

​ 提交

​ 显式提交：commit

​ 隐式提交：正常退出exit、DCL(grant … to …、revoke … from …)、DDL(create…，drop…)

​ 回滚

​ 显式回滚：rollback

​ 隐式回滚：异常退出（宕机，断电）。

保存点

语法

savepoint 保存点名;
rollback to savepoint 保存点名; --还原

隔离级别

多个事务会产生很多问题：脏读、不可重复读、幻读。

脏读

​ 官方：脏读是读到了别的事务回滚前的脏数据。比如事务B执行过程中修改了数据X,在未提交前,事务A读取了X,而事务B却回滚了,这样事务A就形成了脏读。

​ oracle：当一个事务正在访问数据，并对此数据进行了修改（1 -> 2）,但是这种修改还没有提交到数据库（commit），此时，另一个事务也在访问这个数据。

​ 本质：某个事务读取到的事务是过时的。

不可重复读

​ 在一个事务内，多次读取同一个数据，但结果不同。

​ 本质：就是事务A拿到了，被其他事务B修改并提交后的数据。

幻读（虚读）

​ 在一个事务内，多次读取同一批数据，但结果不同。

​ 不可重复读和幻读的区别：

​ a. 不可重复读指的是对于“同一条”数据的查询操作 a 。

​ 幻读对于“多条数据” 的查询操作，数据量数。

​ b.不可重复读针对于update

​ 幻读针对于insert | delete。

四种隔离级别

SQL99标准定义的四种隔离级别。

读未提交（Read uncommitted）

​ 允许事务读取未被其他事务提交的变更 脏读（√） 不可重复读（√） 幻读（√）

读已提交（Read committed）

​ 只允许事务读取已经被其他事务提交的变更

可重复读（Repeatable read）

​ 确保事务可以多次从同一个字段中读取相同的值。并且在这个事务期间，禁止其他事务对这个字段进行更新。脏读（×） 不可重复读（×） 幻读（√）

序列化（Serializable）

​ 确保事务可以从一个表中读取相同的行。并且在这个事务期间，禁止其他事务对该表执行DML操作。脏读（×） 不可重复读（×） 幻读（×）

四种隔离级别的程度依次递进，但是效率逐渐降低。并发性和可用性本身就是矛盾的。

Oracle只支持其中两种：Read Committed(默认)，Serializable。（三种：read only，不在标准里面，oracle自身扩充的，实际上隶属于Serializable级别。）

切换隔离级别

set trasaction isolation level [隔离级别]；
set trasaction read only; --切换到read only

八. 序列

模拟自增

绷直就是内存中的数组（20）

语法

create sequence 序列名 
[increment by 步长] 
[start with 起始值]
[maxvalue | nomaxvalue 最大值]
[minvalue | nominvalue 最小值]
[cycle | nocycle]
[cache | noche]

属性

nextval:下一个值。
currval：当前值。

序列会接着上一次的值自动使用，不会归零。

cache元素个数，要小于循环元素个数。

修改序列

create sequence 序列名

[increment by 步长]

[start with 起始值]

[maxvalue | nomaxvalue 最大值]

[minvalue | nominvalue 最小值]

[cycle | nocycle]

[cache | noche]

修改序列对以前不影响。

删除序列

drop sequence 序列名；

九. 索引

​ 索引类似于书的目录。

​ 通过伪列rowid来找。

类型

​ B数索引

​ 位图索引

创建索引

create index 索引名 on 表名（列名，）;

什么时候、适合建立索引：

​ 数据集中的列，经常在where中使用的列数据量大。

删除索引

drop index 索引名;

十. 同义词（别名）

数据库对象（表 视图 索引…）起别名

如果查看其它用户的表，报错“表或视图不存在”，可能是权限不足。

别名默认是私有的。

创建别名

create synonym [别名] for [对象名]; --私有别名。
create public synonym [别名] for [对象名]; --公有别名。

删除别名

drop [public] synonym [别名];

共有同义词的操作（创建、删除）一般建议由管理员来操作。

十一. PLSQL

可以对SQL进行编程。

开发工具

plsql developer

oracle sql developer

语法

declare 
	--变量、常量、光标（游标）、例外（自定义异常）
begin
	...
	exception --(捕获异常)
	...
end;

赋值

:= --静态赋值：
select [字段名] into 变量 from 表名; --动态赋值。

语句

定义变量

​ 变量名 类型 := ‘变量值’；

引用型变量

​ 变量名 表名.字段名%type; --引用型变量

记录型变量

​ 变量名 表名%rowtype

输出语句

输出语句之前需要打卡显示

set serveroutput on;
dbms_output.put_line('');

输出语句中字符串拼接使用||符号。

条件语句

if语句

if 条件 then ... ;
end if;

if … else语句

if 条件 then ... ;
else ... ;
end if;

if多重选择语句

if 条件 then ... ;
elsif 条件 then ... ;
else ... ;
end if;

循环语句

while 条件

loop
...
end loop;

do … while语句

loop
...
exit when 退出条件;
end loop;

for语句

for 变量 in 最小值 .. 最大值
loop
...
end loop; 

十二. 游标（光标）

语法

定义

cursor 光标名（参数列表）
is
select ...

打开/关闭

open 光标名; --打开光标。
close 光标名；--关闭光标

取出数据

fetch 光标名 into [字段列表]；
--fetch取出当前行数据，并移动到下一行数据。

判断循环结束条件

exit when 光标名%notfound;

光标的属性

​ %isopen、%rowcount、%found、%notfound

例外（异常）

系统例外

​ No_data_found

​ Too_many_rows

​ Zero_Divide

​ Value_error：算数或转换错误。

​ Timeout_on_resource：资源等待超时。

​ raise_application_error(编号，‘错误信息’); 编号在-20000到-20999之间

自定义例外

语法

捕获异常

exception
	when 条件 then ... ;
	when 条件 then ... ;
	when others then  ... ;

定义异常

自定义异常名 exception；

抛出异常

raise 异常名；

十三. 存储过程

语法

create or replace procedure 过程名（参数列表）
as 
	plsql语句；

有参

create or replace procedure 过程名（参数名 [in\out] 参数类型, 参数名 [in\out] 参数类型） 
	--输入参数：in，输出参数：out
as
	--定义变量
begin

end;

无参

create or replace procedure 过程名
as
	--定义变量
begin

end;

调用

方式一：exec 过程名();
方式二： 
begin
	过程名()；
end;

十四. 存储函数

与存储过程最大的区别是存储函数必须有return。

语法

create [or replace] function 函数名（参数列表）

​	return 返回值类型

as

begin

​	..

​	return 返回值

end;

调用

一般使用mybatis或者jdb直接调用。

存储过程/存储函数？

只有一个返回值建议用存储函数，如果没有或者多个返回值使用存储过程。

返回值：

​ 存储函数：out \ return

​ 存储过程：out

注意：

​ 返回值/输出参数如果是光标类型，需要使用包。

十五. 包

分为：包头 + 包体

语法

--仅为包头
CREATE OR REPLACE 
PACKAGE MYPACKAGE AS 

END MYPACKAGE;

实例

CREATE OR REPLACE
PACKAGE BODY MYPACKAGE AS

  procedure queryEmpList(dno in number, emplist out empcursor) AS
  BEGIN
    -- TODO: procedure MYPACKAGE.queryEmpList所需的实施
    open emplist for
    select * from emp where deptno = dno;
  END queryEmpList;

END MYPACKAGE;

SqlDeveloper无法直接测试包。

十六. 触发器

与表相关联的plsql程序，相当于java中的事件，当我们执行DML,自动执行触发器。

语法

create or replace trigger 触发器名

before | after

delete | insert | update [of 列名]

on 表

for each row [when 条件]

​	... plsql 代码

/

语句级触发器

作用于表，只执行一次。

行级触发器

for each row [when 条件]

作用域每一行，每满足一次条件，都执行一次，可以执行多次触发器。

数据校验

web前端:onesubmit、obblur --> 控制器Servlet/SpringMVC --> 数据库 触发器。

尽量往前端拦截。

十七. 数据字典（了解）

元数据，数据库的各种描述信息，系统自带的很多表。

dictionary

user_objects

用户对象表。

user_tables

当前用户能用的表。

[表名]_columns

存放列信息。

all_tables

user_sequences

user_synonyms

user_tab_comments

命名规范

user：当前用户能够使用的。

all：系统中全部的。

dba：管理员能使用的。

v$：性能相关。

十八. DBCA

启动dbca

可以单击dbca。

也可以cmd -> dbca。

数据仓库

分析数据用，只查询不DML.

ASM

把数据库文件放在外设磁盘。

flash_recovery_area

快速恢复区，闪回区大小。

闪回区越大，可以恢复的数据越多，但性能也越低。

备份：

​ 热备份：联机备份，归档模式。

​ 冷备份：脱机备份。

示例方案

自带scott/hr/sh用户。

制定脚本

在数据库创建完毕时，自动执行脚本。

PGA

专用服务器：一个PGA只能供一个用户使用。

共享服务器：一个PGA能供多个用户使用。

监听报错

十九. 闪回

闪回可以跨越commit。

闪回必须要知道回退的时间点或者SCN。推荐将SCN闪回时间点。

表中定义更改不能闪回。

要闪回必须知道时间点：“日期” -> date -> scn

作用

将错误的DML，已经commit,进行撤销。

还原删除的表。

获取表上的历史记录。

类型

闪回表中的数据

将表中的数据，回退到历史的某一点。

查看闪回参数：

show parameter undo;
undo_retention：900； --超过闪存区的数据必须在900内闪回，否则无法闪回。
alter system set undo_retention=1200[默认立即生效] [scope = both | memory | spfile] ; 

时间 -> SCN:
select timestamp_to_scn(sysdate) scn from dual;

闪回

-- 开启移动功能
alter table [表名] enable row movement;
-- 闪回
flashback table [表名] to scn 闪回时间点;

闪回授权

--默认就有权限
grant flashback any table to scott;

闪回删除的表

还原表。

查看删除的表

show recyclebin;

还原

如果回收站中有多个重名表，默认恢复时间点最近的一个。

如果闪回的表和库中的表重名，则重命名。

如果要自定义回收哪一张表，使用回收站的名字。

flashback table [表名1] | ["回收站中的名字"] to before drop rename to [表名1冲突，修改表名为表名2];

清空回收站

purge recyclebin;
--不过回收站直接删除
drop table [表名] purge;

普通用户：

删除 -》 回收站 -》 清空或者还原。

管理员：

管理员删除数据时不过回收站直接彻底删除。

闪回事务查询

undo_sql。

区别

闪回dml + commit,依赖于时间。
闪回事务依赖于提交次数。

闪回版本查询

如果插入数据且commit太快的话可能会丢版本号。

--版本伪列：	versions_xid,versions_operation,versions_starttime,versions_endtime
select versions_xid,versions_operation,versions_starttime,versions_endtime from [表名] versions between timestamp minvalue and maxvalue;

闪回

查询undo_sql，执行即可。

undo_sql存在于flashback_transaction_query。

desc flashback_transaction_query；
--授权
grant select any transaction to scott;
--查询undo_sql前需要在sys中修改设置,日志开得太晚了。
alter database add supplemental log data;
--查询undo_sql
select undo_sql from flashback_transaction_query [where XID = ''];

BUG

dml操作数据时不要太快。（批量复制）

凡是能撤销的事务，都必须提前开日志。

--在sys中执行
alter database add supplemental log data;

闪回数据库（了解）

将数据库中的数据回退到历史的某一点。

闪回归档日志（了解）

二十. 数据库导入导出

图形界面

CMD命令

导出

表方式

exp不是sql语句，是一个工具。

多数据库需要指定orcl。

exp [账户名]/[密码]@[IP地址]/orcl file=[文件地址.dmp] log=[文件地址.log] tables=[表名]

用户方式

导出scott用户中所有的对象。

exp [账户名]/[密码]@[IP地址]/orcl file=[文件地址.dmp] log=[文件地址.log] 

全库方式

必须时DBA才能导出。

exp [账户名]/[密码]@[IP地址]/orcl file=[文件地址.dmp] log=[文件地址.log] full=y

导入

表方式

imp [用户名]/[密码]@[IP地址]/orcl file=[文件地址.dmp] log=[文件地址.log] tables=emp,dept fromuser=[用户名2] touser=[用户名3] commit=y ignore=y

用户方式

需要DBA角色。

imp [用户名]/[密码]@[IP地址]/orcl file=[文件地址.dmp] log=[文件地址.log] fromuser=[用户名2] touser=[用户名3] commit=y ignore=y

全库方式

imp [用户名]/[密码]@[IP地址]/orcl file=[文件地址.dmp] log=[文件地址.log]  commit=y ignore=y destroy=y

向导模式

只需要exp和imp。

二十一. 分布式数据库

物理上分开存放，逻辑上一个整体。

刻画u但不必关心数据如何分割和存储，只需要关心数据本身即可。

本地操作

远程操作

--远程登录
sqlplus [用户名]/[密码]@[远程IP地址]/orcl

关闭远程防火墙。

远程将两个文件(tnsnames.ora、listener.ora)的Host值改成IP地址或计算机名。

链路操作（分布操作）

借助于数据库链路。

链路是单向的。

创建链路

--创建链路需要先授权
grant create database link to [用户名]；

net manager创建本地服务，用于链接远程服务。

create database link [连接名] connect to [用户名] identified by [密码] using '本地服务名'

同时操作本地和远端，关联查询。

select * from [表名]@[链路名];

创建同义词，屏蔽掉分布式访问的关键字。

create synonym [同义词] for [表名]@[链接名]

创建视图

create view [视图名] as select ...

快照

可以备份远程数据库。

快照有一定延迟。

--创建快照从当前开始同步，每个星期的今天开始同步。
create snapshot [快照名] refresh start with sysdate next next_day(sysdate, 'day') as selec * from [表名]@[链路名];

触发器

实时备份。

--如果本地数据库修改了，则同步到远程。
create trigger [触发器名] 
after update on [表名] 
for each row 
begin
	update 
end;
/

二十二. EM管理器

需要打开OracleDbConsocleorcl，关闭防火墙。

em是https。

https://[IP地址]/em

二十三. 用户权限问题

登录验证

密码校验

sqlplus [用户名]/[密码];

外部校验

自动屏蔽密码校验。

条件：当前系统用户必须是dba角色。

查看：右键计算机 -> 管理 -> 本地用户和组 -> 用户

sqlplus / as sysdba;

全局校验

生物认证、token（令牌环）

银行卡：U盾。

预定义账户

scott

sys：最高权限（一切权限）。

system：DBA权限。

权限

系统权限

允许用户执行对数据库的特定行为。

用户权限

允许用户执行一个特定的对象。

创建用户

create user [用户名] identified by [密码];

没有任何权限（包含登录权限）；

授权。

二十四. SQL优化的指导原则

数据结构、索引、SQL执行计划

在大量数据时使用。

索引类型

普通索引、复合索引、全文索引等，每个类型索引的特性是不一致的，因此使用索引前需要考虑，当前的表结构最佳适用于哪个类型索引。

缓存

数据库缓存、缓存中间件Redis。

锁机制

读锁、写锁、行锁、页锁等各种类型的锁。

不同的数据库有不同的锁，加锁前要考虑当前业务，更加适用于哪些锁。

表的设计

三大范式，范式的使用会提高规范性，但也会降低效率。因此需要权衡使用。

架构设计

读写分离、分库分表。

细节

数据类型的选择。

基于oracle

不要让oracle做太多，把一些复杂操作交给java去做。

尽量减少访问次数。

让sql更简单，尽量避免一些复杂查询。

避免使用 *，只挑选需要的字段去查询。

便面使用一些需要消耗资源的操作：distinct、union、minus、intersect、order by。尽量使用union all替代union。使用exists替代distinct。

避免索引失效。在where后面使用函数、计算、not、is null、自动转换，都会使索引失效。

一般在架构设计时，都需要进行权衡。到底使用哪种，必须根据经验、性能、测试。