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、自动转换,都会使索引失效。
一般在架构设计时,都需要进行权衡。到底使用哪种,必须根据经验、性能、测试。