当前位置:首页 > 数据库 > ORACLE 知识汇总

ORACLE 知识汇总

2022年11月08日 17:27:17数据库8

oracle知识汇总

一、数据库三大范式:

第一范式:每个列都不可再拆分;

第二范式:在第一范式的基础上,非主键字段完全依赖于主键字段,而不是依赖部分主键字段;

第三范式:在第二范式的基础上,非主键字段只依赖于主键字段,不依赖其他非主键字段。

二、数据库索引

1、索引:建立在表一列或多列的辅助对象,目的是加快访问表的数据。

2、索引的优点:

(1)、创建唯一性索引,可以确保数据的唯一性;

(2)、大大加快数据检索速度;

(3)、加速表与表之间的连接;

(4)、在查询过程中,使用优化隐藏器,提高系统性能。

3、索引的缺点:

(1)、创建和维护索引需要耗费时间,随数据量增加而增加;

(2)、索引占用物理空间;

(3)、对表的数据进行增删改时,索引需要动态维护,降低了数据的维护速度。

4、常用索引sql语句

建立索引:create index index_name on table_name([column1 [asc|desc]) 重命名索引:alter index index_name rename to index_new; 合并索引:alter index index_name coalesce; 重建索引:alter index index_name rebuild; 删除索引:drop index index_name; 查看索引:select * from all_indexes where table_name='表名';

5、应该建立索引的列

(1)、经常搜索的列,可加快搜索的速度;

(2)、主键列,确保数据唯一;

(3)、经常用于连接的列,加快连接速度;

(4)、经常排序的列,索引已经排序,可节省排序时间;

(5)、经常使用在where子句的列,加速判断时间;

6、不应该建立索引的列

(1)、查询中很少使用的列;

(2)、单一值的列;

(3)、blob数据类型的列;

7、索引限制

(1)、索引列使用不等于操作符<>、!=;

(2)、使用 is null 或is not null;

(3)、where子句中使用函数;

(4)、比较不匹配的数据类型(不匹配的数据类型之间比较会让Oracle自动限制索引的使用);

8、索引分类

B-树索引:默认索引类型,可以是单列索引,也可以是多列索引,索引列的值都储存在索引中,

特点: 

适合与大量的增、删、改(OLTP)

不能用包含OR操作符的查询;

适合高基数的列(唯一值多)

典型的树状结构;

每个结点都是数据块;

大多都是物理上一层、两层或三层不定,逻辑上三层;

叶子块数据是排序的,从左向右递增;

在分支块和根块中放的是索引的范围;  

位图索引 :适合于决策支持系统(Decision Support System,DSS)和数据仓库

特点:

适合与决策支持系统;

做UPDATE代价非常高;

非常适合OR操作符的查询;

基数比较少的时候才能建位图索引;  

HASH索引:必须使用HASH集群,HASH索引可能是访问数据库中数据的最快方法

特点:HASH对于一些包含有序值的静态数据非常有效。

索引编排表 

反转键索引 

基于函数的索引

分区索引:本地分区索引和全局分区索引:

本地分区索引通常使用的索引

全局分区索引在一个索引分区中包含来自多个表分区的键

三、存储过程

oracle存储过程procedure:是一个为了完成特定功能的sql语句块,经编译后存放在数据库中,第一次编译后不需要再进行编译,用户可以通过指定名称和传递参数来执行。

优点:

1.高效,编译一次后保存到数据库,以后调用不需要编译,直接执行,减少编译时间;

2.降低网络流量,存放于数据库中,远程调用不会传输大量字符串的sql语句;

3.复用性高,存储过程是针对某一特定功能创建的,当再需要完成特定功能时调用该存储即可;

4.可维护性高,功能发生细微变化时,只需修改小部分代码即可;

5.安全性高,完成某一特定功能的存储过程是特定用户创建的,具有身份限制。

结构:过程声明,执行过程部分,存储异常

分类:1、无参数存储过程:

create or replace procedure 存过名 as/is 变量2 number; begin --处理的业务功能块 exception --异常 end

2、有参数存储过程(声明参数与student表id字段类型一致)

create or replace procedure 存过名 (pal student.id%type) as/is name student.name%type --声明变量与student表的name字段类型一致 age number:=20 --声明变量age为number类型,默认值为20 begin --处理的业务功能板块 end

3.有输入参数和输出参数的存储过程

CREATE OR REPLACE PROCEDURE 存储过程名称(        s_no in varchar,        s_name out varchar,        s_age number) AS total NUMBER := 0; BEGIN   SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;   dbms_output.put_line('符合该年龄的学生有'||total||'人');   EXCEPTION     WHEN too_many_rows THEN      DBMS_OUTPUT.PUT_LINE('返回值多于1行');  END

in 代表输入参数,out代表返回值参数,只能在过程内部赋值,表示可以将参数传递给调用过程,

调用存储过程:

call 存储过程名 (参数);

/

运算符:

:= 代表赋值,

.. 范围,

|| 拼接字符

select into 将多个字段值赋予一个或多个变量

嵌套语句:

1.选择语句

1、if ..end if

IF s_sex=1 THEN   dbms_output.put_line('这个学生是男生'); END IF

2、if ..else end if

IF s_sex=1 THEN   dbms_output.put_line('这个学生是男生'); ELSE   dbms_output.put_line('这个学生是女生'); END IF

2.循环语句

1、loop循环

LOOP   IF 表达式 THEN     EXIT;   END IF END LOOP;

2、while循环

WHILE 表达式 LOOP   dbms_output.put_line('haha'); END LOOP;

3、for循环

FOR a in 10 .. 20 LOOP   dbms_output.put_line('value of a: ' || a); END LOOP;

四、游标

用来存储多条查询语句的结果集

优点:加快对数据的操作;

缺点:占用内存;

分类:

1、静态游标:显式游标和隐式游标

隐式游标:在 PL/SQL 中使用 DML(insert、delete、update) 和 select into 时,会 '自动创建隐式游标',隐式游标自动声明、打开和关闭

显式游标:由cursor声明,可带参数,也可不带参数

2、动态游标

语法

DECLARE v_sql_select VARCHAR(200); v_stu_info_rows scott.stu_info%ROWTYPE; TYPE cur_stu_type IS REF CURSOR RETURN scott.stu_info%ROWTYPE; -- 有 return cur_stu_info cur_stu_type; BEGIN -- 测试 /* v_id := 1; v_sql_select := 'SELECT t.id, t.name FROM stu_info t WHERE t.id <= :b1';*/ OPEN cur_stu_info FOR SELECT t.id, t.name, t.sex FROM stu_info t; FETCH cur_stu_info INTO v_stu_info_rows; dbms_output.put_line(v_stu_info_rows.id || ' :' || v_stu_info_rows.name); CLOSE cur_stu_info; END;

游标的属性:

属性 返回值类型 作用 sql%isopen 布尔型 判断游标是否 '开启' sql%found 布尔型 判断游标是否 '获取' 到值 sql%notfound 布尔型 判断游标是否 '没有获取' 到值(常用于 "退出循环") sql%rowcount 整型 '当前' 成功执行的数据行数(非 "总记录数")

存储过程和函数区别:

存储过程针对于特定功能,函数针对数据;

存储过程可以单独调用执行,函数只能作为表达式一部分使用;

存储过程声明不需要描述返回类型。函数要描述返回类型,至少包括一个有效的return语句

存储过程可以返回0或者多个值,函数返回一个值;

五、触发器与视图

触发器是一种特殊的存储过程,触发器一般由事件触发并且不能接受参数,由语句块去调用

分类:

1.DML(INSERT, UPDATE, DELETE)触发器: 创建在表上,由DML事件引发

2.instead of触发器: 创建在视图上并且只能在行级上触发,用于替代insert,delete等操作(由于oracle中不能直接对有两个以上的表建立的视图进行DML操作,所以给出替代触发器,它是专门为进行视图操作的一种处理方法)

3.DDL(CREATE、ALTER、DROP)触发器: 触发事件时数据库对象的创建和修改

4.数据库事件触发器:定义在数据库或者模式上,由数据库事件触发

  CREATE [OR REPLACE] TIGGER触发器名 触发时间 触发事件   ON表名/视图名   [FOR EACH ROW] //加上FOR EACH ROW 即为行级触发器,不加时为语句级触发器   BEGIN   pl/sql语句   END

视图

一个虚拟表,

CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name AS SELECT查询 [WITH READ ONLY CONSTRAINT]

优点:当表结构过于复杂时,请使用视图。

视图可以简化操作(可以将多表中的字段合并在一张视图中)

六、事务和锁

事务:是用户定义的一个操作序列

特点:

1.原子性,要么不发生,要么全发生;

2.一致性,数据库是从一个一致性状态变成另一个一致性状态;

3.隔离性,同时发生的多个事务互不干扰

4.持久性,事务一旦提交,对数据库造成的影响是永久的;

  savepoint aa;    delete from emp where empid = 9999;  //正常操作    savepoint bb;    delete from emp;  //异常操作    rollback to bb;  //回滚到异常操作之前

结论:事务支持多个保存点,可以在返回最近的保存点后另外再返回其他的保存点,但一旦

     保存点返回,保存立即回收。事务提交之后,保存点失效。

隔离级别:

各个事务的隔离程度。

隔离级别 脏读 不可重复读 幻读
读未提交(Read uncommited)
读已提交(Read commited) ×
可重复读(Repeatable read) × ×
可串行化(Serializable) × × ×

oracle的事务隔离级别是:读已提交

mysql的事务隔离级别是:可重复读

脏读:一个事务读取到另外一个事务未提交的动作

不可重复读:在同一事务中多次查询,由于其他事务所做的修改和删除,导致每次

             返回不同的结果集,此时发生非重复读

幻读:在同一事务中多次查询,由于其他事务所做的插入,导致每次返回不同的结果集,

此时发生幻读

如何在控制台设置事务的隔离级别:

set transaction isolation level committed;(默认) set transaction isolation level serializable; set transaction read only;

锁:

oracle数据库的一种轻量性锁定机制。

分类:排它锁(X锁),共享锁(S锁)

排他锁也叫写锁.这种模式的锁防止资源的共享,用做数据的修改.

共享锁也可以叫读锁.该模式锁下的数据只能被读取,不能被修改.

按照作用对象又可分为DML锁,DDL锁和内部闩锁.

DML锁:该类型的锁被称为数据锁,用于保护数据.主要保证了并发访问时数据的完整性.再细分,又可分为行级锁(TX,也可叫事务锁)和表级锁(TM).

DDL锁:可以保护模式中的对象的结构.

内部闩锁:保护数据库的内部结构,完全自动调用.

1.TX:当修改表中的某行记录时,需要对将要修改的记录加行级锁,防止两个事务同时修改相同记录,事务结束,该锁也会释放,是粒度最细的锁.该锁只能属于排他锁(X锁).

2.TM:主要用用是防止在修改表的数据时,表结构发生变化.例如,会话S在修改表A的数据时它会得到表A的TM锁,而此时将不允许其他会话对该表进行变更或删除操作.表级锁包含如下几种模式:

RS(行级共享锁,ROW SHARE):该模式下不允许其他的并行会话对同一张表使用排它锁,但允许其利用DML语名或Lock命令锁定同一张表中的其他记录.select ...from for update语名就是给记录加上了RS锁.

RX(行级排他锁,ROW EXCLUSIVE):该模式下允许并行会话对同一张表的其他数据进行修改,但不允许并行会话对同一张表使用排他锁.

S(共享锁,SHARE):该模式下,不允许会话更新表,但允许对表添加RS锁.

SRX(共享行级排他锁,SHARE ROW EXCLUSIVE):该模式下,不能对同一张表进行DML操作,也不能添加S锁.

X(排他锁,EXCLUSIVE):该模式下,其他的并行会话不能对表DML和DDL操作,该表只能读.

七、数据库优化

见oracle优化

八、数据库备份分类

主要分为逻辑备份和物理备份;

物理备份-又分为俩种:冷备份和热备份

1、逻辑备份:

指利用exp命令进行备份;

优点:简单操作,不会影响正常的数据库操作,经常用于日常备份;

(1)exp命令详解

exp可以添加多个参数,以实现不同导出策略,可以通过exp-?查看,参数包括owner、tables、inctype

exp 用户名/密码 file=d:/exp.dmp

d:/exp.dmp指导出后文件位置,用户名/密码指登录数据库时的用户名和密码。

owner参数:可以指定某一用户,只导出该用户下所有的对象

exp 用户名/密码 owner=( dm_user) file=d:/exp.dmp

tables参数:指定某一张表

exp 用户名/密码 tables=( emp) file=d:/exp.dmp

inctype参数:只针对oracle9i之前的版本,现有版本已废除参数,用于指定增量导出,

(2)imp命令详解

imp命令用于导入数据文件以恢复数据库内容,

imp 用户名/密码 file=d:/exp.dmp

也可以指定参数owner参数、tables参数,用于恢复某一用户或者某一表的数据。

2、物理备份:指直接恢复数据库的文件

(1)热备份:数据库在归档模式下的进行的数据文件、控制文件、归档日志文件的备份;热备份数据库时,首先确保数据库开启归档模式,然后备份表空间的数据文件,最后备份控制文件。

1、查看当前数据库是否开启归档模式:

archive log list

2、开启归档模式的命令:

alter database archivelog

3、查看表空间和文件路径:

select tablespace_name,file_name from dba_data_files order by tablespace_name;

查询结果会有sysaux、system、undotbs1、users、以及其他表空间文件(.dbf)

4、对表空间备份,修改表空间为备份状态

alter tablespace user begin backup; 
alter tablespace sysaux begin backup;
 alter tablespace system begin backup; 
alter tablespace undotbs1 begin backup;
 alter tablespace 其他表空间 begin backup;

5、将表空间文件复制到磁盘文件:

host copy 源文件名称 目标路径名称

6、结束表空间的备份状态

alter tablespace user end backup; 
alter tablespace sysaux end backup; 
alter tablespace system end backup; 
alter tablespace undotbs1 end backup; 
alter tablespace 其他表空间 end backup;

7、备份控制文件

通常应该在数据库物理结构做出修改之后,如添加、删除或重命名数据文件,添加、删除或修改表空间,添加或删除重做日志文件和重做日志文件组等,都需要重新备份控制文件。

将控制文件转换为二进制文件

alter database backup controlfile to ‘目标路径.bkp’

将控制文件转换为文本文件

alter database backup controlfile to trace;

8、备份其他物理文件

归档当前联机重做日志文件

alter system archive log current;

--切换日志

alter system switch logfile;

9、备份归档重做日志文件,将所有的归档重做日志文件复制到备份磁盘中。

10、备份初始化参数文件,将初始化参数文件复制到备份磁盘中。

(2)冷备份:数据库在关闭状态下的备份,备份所有的数据库文件。包括:所有数据文件、所有控制文件、所有联机redo_log文件和Init.ora文件(可选择)

ex:进入服务器,以管理员身份连接数据库:sqlplus/ as sysdba;

关闭数据库:shutdown normal;

冷备份数据库文件:host copy 源文件名称 目标路径名称

备份完成后重启数据库:startup

优点:

  • 简单、快捷,只需拷贝文件。
  • 只需将文件再拷贝回去,就可以恢复到某一时间点上。
  • 与数据库归档的模式相结合可以使数据库很好地恢复。
  • 维护量较少,但安全性确相对较高。

数据库面试题

1、新建用户后需要赋予什么权限才能连接数据库?

connect权限

2、oracle_base和oracle_home的区别

oracle_base是oracle的根目录,oracle_home是oracle产品的目录

3、数据库正常启动的几种状态

startup nomount-数据库实例启动

startup mount-数据库装载

startup open-数据库打开

4、数据库归档模式和非归档模式优缺点

归档模式可以执行热备,同时支持增量备份,可以执行时间点恢复;归档日志文件需要占用额外的磁盘空间

非归档模式:

非归档模式只能进行冷备,只能还原到备份点,之后的数据无法恢复;一般在开发或者测试环境使用;

作者:cooleer
来源链接:https://blog.csdn.net/qq_41968327/article/details/119763251

版权声明:
1、JavaClub(https://www.javaclub.cn)以学习交流为目的,由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容,包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网,本站不享有版权,版权归原作者所有。

2、本站提供的内容仅用于个人学习、研究或欣赏,以及其他非商业性或非盈利性用途,但同时应遵守著作权法及其他相关法律的规定,不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容,请及时通知本站(javaclubcn@163.com),我们将第一时间核实后及时予以删除。


本文链接:https://www.javaclub.cn/database/68508.html

标签: Oracle
分享给朋友:

“ORACLE 知识汇总” 的相关文章

Oracle SQL

start with....connect by 递归查询树状SQL Select * from t_tree where UPPER(is_display) = 'ON' start with p_node_id =1 --当前节点ID conne...

Java使用多线程导入数据到Oracle中

Oracle中的设置 多线程导入数据到Oracle中,如果是自己设置主键的值,那么肯定会遇到主键冲突的问题。例如线程A计算出的id为10(max(id) + 1),在A线程还没有完成导入时线程B用相同办法得到的id也是10,这时两个线程都请求插入数...

使用Maven管理Oracle驱动包

使用Maven管理Oracle驱动包

  由于Oracle授权问题,Maven3不提供Oracle JDBC driver,为了在Maven项目中应用Oracle JDBC driver,必须手动添加到本地仓库。 手动添加到本地仓库需要本地有oracle驱动包,有两种方式获取oracle的驱动包:一种是去oracle...

mysql中sql语句中常见的group_concat()函数意思以及用法,oracle中与其一样的功能函数是wmsys.wm_concat()

mysql中sql语句中常见的group_concat()函数意思以及用法,oracle中与其一样的功能函数是wmsys.wm_concat()

1.group_concat(),手册上说明:该函数返回带有来自一个组的连接的非NULL值的字符串结果。比较抽象,难以理解。 通俗点理解,其实是这样的:group_concat()会计算哪些行属于同一组,将属于同一组的列显示出来。要返回哪些列,由函数参数(就是字段名)决定。分组必...

python3.X链接oracle配置

第一步: 下载并安装python3.X 第二步: pid install cx_Oracle 第三步(重点): 下载Oracle Instant Client版本,可以去官网下载:https://www.oracle.com/technetwor...

docker安装oracle19c

docker安装oracle19c

1、拉取镜像 docker pull registry.cn-hangzhou.aliyuncs.com/zhuyijun/oracle:19c 2、启动 linux系统下启动容器命令 docker run -p 1521:1521 -p 5500:550...

Java添加系统当前时间到Oracle数据库

Java添加系统当前时间到Oracle数据库

Java添加系统当前时间到Oracle数据库 代码示例: // 获取系统当前时间 java.util.Date sysDate = new java.util.Date(); // 添加到Oracl...

PLSQL developer 连接不上64位Oracle 的解决方法

PLSQL developer 连接不上64位Oracle 的解决方法

Windows 64位下装Oracle 11g 64位,PLSQL Developer使用出现以下问题: 1、Database下拉框为空: 2、强制输入用户名、密码及Database,登录弹出: ** 报错: Initi...

Oracle数据库基本操作(四) —— PLSQL编程

本文主要介绍Oracle中PLSQL的基本语法——if条件判断、三种循环、游标、例外、存储过程、存储函数以及触发器。   Procedure Language 实际上是Oracle对SQL语言的能力扩展,让SQL语言拥有了if条件判断,for循环等处理。 一、P...

Oracle学习(十五)PLSQL安装

Oracle学习(十五)PLSQL安装

PS:由于原来一直用的旧版本的PLSQL客户端,查看执行计划有些数据无法展示,所以今天换一波新版本的使用,记录下安装和使用流程。 PLSQL(oracle数据可视化工具) 一、下载    我用的13的版本,把...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。