oracle中数据字典、数据、序列、索引、视图知识

---------------------------------------------------------
数据字典

定义：用来描述用户对象的对象。

Oracle本质：
Oracle是一个用表（系统表）来维护表（用户表）的数据库系统，这里的系统表就是我们所说的数据字典

Oracle数据库定义的数据字典都遵循一定的命名规则，它内置了上千张表

命名规则：
前缀_数据库对象+s/es

常用的前缀：
user
all
dba
$

常用数据库对象
table,user,constraint,sequence,index,view...

常用数据字典：
user_users 存放用户的信息
user_tables 存放用户表的信息
user_constraints存放表中约束的信息
user_sequences 存放序列的信息
user_indexes 存放索引的信息
user_views 存放视图的信息
user_cons_columns存放约束与列对应关系的信息

--查看当前用户下有哪些约束
select * from user_constraints;
--查看当前用户下有哪些表格
select * from user_tables;
--查看TBL_EMP表中所有的约束名和约束类型
select constraint_name,constraint_type from user_constraints where table_name = 'TBL_USER';
注意：此表名一定要大写！

--查看当前用户（scott）下所有状态不可用的约束名字，以及该约束作用的表格名
select constraint_name,constraint_type from user_constraints where status = 'disabled';

-------------------------------------
DTL，数据事务语言

事务的定义：
就是指一组相关的SQL操作，我们所有的操作都是事务中的。

注意：在数据库中，执行业务的基本单位是【事务】，不是以某一条SQL。
数据库在默认情况下，事务是都打开的，也就是说它一种处在事务当中的，一个事务的结束，代表着下一个事务的开启。
执行commit或者rollback指令时，会结束当前事务

作用：用来保证数据的平稳性和可预测性
例如：银行转账业务
SQL1：A账号向B账号转账10000
update tbl_account set balance=balance-10000 where accountNo=A账号；
SQL2:
update tbl_account set balance=balance+10000 where accountNo=B账号；
SQL1和SQL2必须处在同一个事务中，从而保证同时成功或者同时失败。

 

事务的四大特性（ACID）：
atomic,原子性，事务是不可分割的，要么同时成功，要么同时失败；
consistency,一致性，事务一旦结束，内存中的数据和数据库中的数据是保持一种;
isolation,隔离性，事务之间互不干扰，一个事务的结束意味着下一个事务的开启；
duration，持久性，事务一旦提交，则数据持久化到数据库中，永久保存

在oracle中，操作事务的命令：
1.commit，提交事务
把事务中所有的数据持久化到磁盘中

2.rollback to 回滚点,回滚事务
把事务中所做的操作全部取消，回到初始化状态

3.savepoint 回滚点，设置回滚点
事务回滚时，回滚到起点

总结：
1.目前主流的数据库都是支持事务的，而且其中Oracle支持的最好
2.一个事务不能读取到另一个事务还没有提交的数据
3.DDL语句都会自动提交事务
4.DML语句不会自动提交事务，需要手动提交commit

-------------------------------------------

多事务的并发处理机制：
原因：多个事务同时操作一个表中的同一行数据，如果这些操作数修改操作的话，就会产生并发问题，如果不处理，则会造成数据的不一致的情况

数据库可能产生的并发问题包括：
1.脏读
是指一个事务正在访问数据，并且对这个数据进行修改，而这种修改还没有提交到数据库中，而另一个事务也访问了这个数据，并且使用了这个数据。
解决方法：一个事务在修改数据时，该数据不能被其他事务访问

2.不可重复读：
是指一个事务多次读取同一条记录，如果此时另一个事务也访问并且修改了该数据，则就会出现多次读取出现数据不一致的情况，原来的数据变成了不可重复读取的数据。
解决方法：只有在修改事务完全提交过后才可以读取数据

3.幻读
是指一个事务修改表中的多行记录，但是此时另一个事务对该表格进行了插入数据的操作，则第一个事务会发现表格中会出现没有被修改的行，就像发生了幻象一样；
解决方法：在一个事务提交数据之前，其他事务不能添加数据

Oracle中采用‘锁’来做并发处理
1.表级排它锁(X) exclusive mode
2.表级共享锁(S) share mode
3.表中行级排它锁 share row exclusive
注：这三种锁是通过专门的命令来申请的

语法：
lock table tbl_name in mode;

例如：
--以共享锁锁表
lock table tbl_emp in share mode;

--以排它锁锁表
lock table tbl_emp in exlusive mode;

4.行级共享锁(RS) row share
5.行级排它锁(RX) row exclusive
注：这两种锁无需通过专门的命令申请，而是通过DQL和DML来自动申请的

注意：
1.所有的DQL语句默认情况下都会自动申请RS锁
2.所有的DML语句默认情况下都会自动申请RX锁，每一行记录都有唯一的RX锁
3.在项目中，为了满足业务要求，一般select语句需要申请RX锁

select语句通过for update来申请RX锁：
select * from s_emp for update;
select * from s_emp for update wait 5;等待5秒
select * from s_emp for update nowait;

其他数据库对象：
序列(SEQUENCE)
索引(INDEX)
视图(VIEW)

1.序列(SEQUENCE)
对应的数据库字典：user_sequences
作用：用来产生唯一性值的数据库特殊对象

创建序列语法：
create sequence 序列名
start with n 表示从几开始，默认值是1
increment by n 每计数一次增加多少，默认是1
maxvalue n 序列最高峰值n
minvalue n 序列最低峰值n
cache n 提供n个预分配的序列，保存在内存中
cycle | nocycle 是否循环
oredr | noorder 有序还是无序序列

例如：
--给员工创建一个序列
create sequence tbl_emp_id start with 4;

如何使用序列？
nextval:取序列的下一个值（tbl_emp_id.nextval）
currval:取序列的当的前值（tbl_emp_id.currval）

在插入数据时使用：
insert into tbl_emp values(tbl_emp_id.nextval,....)

删除序列：
drop sequence 序列名；
---------------------------------------------------
索引（INDEX）
对应的数据词典user_indexes

它是一个比较重要的数据库对象，作用是可以有效的提高数据库的查询效率（数据库性能优化）

创建索引的两种方式：
1自动创建
当表中的列添加了主键约束或者唯一性约束时，则系统会自动为此列创建唯一性的索引，索引名就是约束名

2手动创建
语法：
create index 索引名 on 表名（列名...）；

注意：
1.索引有自己独立的储存空间和命名空间
2.创建索引也会相对牺牲一些数据库性能

索引的原理：
1.默认情况下，索引是采用BTree(二叉树)的数据结构

2.伪劣（rowid）,存放的数据行记录的正真“物理地址”。

--根据某物理地址查询某一行记录
//先获取记录的rowid
select rowid from s_emp where first_name="Carmen"；

//根据行记录rowid查找相应的记录
select* from s_emp where rowid='............'

3.索引建立的原理：
把创建索引的列值与rowid合成一个键值树，这个键值对就是索引，然后把它们存放到指定的数据结构中（二叉树，位图）中，并且是独立的索引空间

4.索引查询的原理：
当我们的查询语句中where 条件的列建立了索引，则查询分为一下2步：
a.先查索引，在句列中的值直接找到rowid
b.根据第一步得到的rowid直接定位到相应的行记录结束查询

5.建立索引的策略：
a.主键和唯一性列 合适
b.不经常发生改变的列 合适
c.满足以上两个条件，经常做查询的列 合适
d.重复值太多的列 不合适
e.null值太多的列 不合适

6.删除索引

drop index 索引名；
-----------------------------------------------------
视图（VIEW）
对应的数据字典：user_view
他是一个数据库对象，它是表格的一个“窗口”，用来保存查询语句的对象，视图是依附与表的，并且与表格共享储存空间。

定义：本质就是一条合法的查询语句

作用：
1.配合权限，根据业务来做分级管理
2.减少复杂性，增加数据的安全性

创建视图的语法：
create view 视图名 as 子句;
with read only ;视图只读
with check option

例如：
--以只读方式创建s_emp表中id,salary两列的视图
create view view_name as select id,salary from s_emp with read only;
注意：操作视图必须拥有一定的权限

删除视图
drop view 视图名；

视图分类：
关系视图，内嵌视图，对象视图，物化视图
-----------------------------
补充：查询当前用户可执行什么操作
select * from session privs;

--查询某个权限可执行的所有操作
select* from DBA_SYS_PRIVS where grantee='DBA';(需要DBA)

--查询当前用户被赋予的系统角色
select *from SESSIONS_ROLES order by role;

--授予权限的两种方式：
1.grant createany view to 用户名
2.grant connect,resource,dba to 用户名

练习：
--创建41部门所有员工的简单视图(必须拥有操作视图的权限)
create view view_emp_41 as select * from s_emp where dept_id=41;

 

转载于:https://www.cnblogs.com/qianqian528/p/7804891.html