Oracle 数据库
Oracle是一个强大的关系型数据库产品，功能丰富，产品安全性有保障，是Oracle公司的主打产品
本系列笔记可能会遇到的专业词汇有：
- DB,
DataBase, 数据库 - DBMS,
DataBase Management System, 数据库管理系统 - RDBMS,
Relationship DataBase Management System, 关系型数据库管理系统 - SQL,
Standard Query Language, 标准化查询语言
本系列学习笔记包含如下的课程内容：
- 数据库的基本概念
- DDL 命令
- Create
- ALTER
- DROP
- DML 命令
- insert
- update
- delete
- DQL 命令
- select 命令
- 关联查询
- 分组查询
- 子查询
- DCL 命令
- DTL 命令
- commit
- rollback
- savepoint
- 数据字典
- 数据库对象
- 序列
- 索引
- 视图
- 同义词
数据字典
数据字典也叫系统表，它由ORACLE数据库产生，并且可以供用户进行查询。
Oracle是采用表[系统表]来管理表[用户表]的数据库。我们要讲的第一张数据字典的表名：dictionary
通过查询 dictionary 表，可以得知所有的数据字典名。Oracle中数据字典一般按照
‘前缀_数据库对象名e|es’的规则进行命名- 前缀有：
- dba 有关整个数据库中对象的信息
- all 当前用户有权限可以访问的所有对象
- user 当前用户拥有的所有对象
常用数据库对象有：
- user 代表用户
- table 代表表
- constraint 代表约束
- sequence 代表序列
- index 代表索引
- view 代表视图
我们把上述前缀和数据库对象组合之后大致可以猜到常用的数据字典名字：
- user_users 可以查看用户信息
- user_tables 可以查看用户拥有的表
- user_constraints 可以查看表中的约束信息
- user_cons_columns 可以查看约束的列值
- user_sequences 可以查看用户拥有的所有序列
- user_indexes 可以查看用户拥有的所有索引
- user_views 可以查看用户拥有的所有视图
练习
1
2
3
4
5
6-- 查看所有表的所有约束信息
-- 查看当前用户下有哪些表格
-- 查看TBL_EMP表中所有的约束名和约束类型
-- 查看当前用户的角色及角色对应的操作权限
-- 可以利用数据字典生成SQL脚本
select 'DROP TABLE '||table_name||' CASCADE CONSTRAINTS;' from user_tables;
#DTL语句
关键字|作用
-|-
commit|用来提交数据库事务
rollback|用来回滚数据库事务
savepoint|在操作中，用来保存事务点，一个事务中可以划分成很多的事务点
什么是数据库事务呢？
简单的一句话来概括：一组相关的SQL操作。
对于Oracle数据库来讲，从你利用客户端与服务端建立连接/会话开始，
我们的所有操作都处在
事务之中。所以，我们在会话中结束一个事务之时，也表示下一个事务开始。
作用:用来保证数据的平稳性和可预测性
例如:银行转账业务1
2update tbl_account set balance - 10000 where id = 1;
update tbl_account set balance + 10000 where id = 2;事务的4大特性[ACID]
1
2
3
4
5
6
7
8
9a.原子性 [Atomic]
是指事务不可分割，处在同一事务的操作要么一起成功，要么一起失败。
b.一致性 [Consistency]
当事务结束时，内存状态与数据库一定是一致的。
c.隔离性 [Isolation]
多个事务之间互不干扰。
注：如果多个事务同时操作同一表中的同一些记录时，会产生并发。
d.持久性 [Durability]
事务一旦正确提交，则数据将固下到数据库，绝不会丢失。
目前大部分的数据库产品都支持事务。
DML如何结束事务？
利用commit命令或rollback命令;
commit 表示提务事务，就是把内存中的改变持久化到数据库[数据文件]。
rollback 表示回滚事务，就是把内存中的改变撤消到事务开始时的状态。注意: 当SQL异常终止或者系统发生故障时会自动回滚
Oracle中操作事务的命令：
1.commit，提交事务
把事务中所有的数据持久化到磁盘中
2.rollback [to 回滚点]，回滚事务
把事务中所做的操作全部取消，回到初始状态
3.savepoint 回滚点，设置回滚点
事务回滚时，回到起点- 总结：
- 目前主流的数据库都是支持事务的，而且其中Oracle支持的最好
- 一个事务不能读取到另一个事务还没有提交的数据
- DDL语句都会自动提交事务
- DML语句不会自动提交事务,需要手动commit
##数据库并发处理机制
- 为什么需要进行并发处理？
当多个事务同时对同一个表的同一些行做增、删、改操作时，就会存在
并发访问的问题，如果不对这些情况做出处理，则无法保证数据的一致性。 数据库可能产生的并发问题包括：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
171.脏读
一个事务正在访问数据，并且对这个数据进行修改，而这种修改还没有提交到
数据库中，而另一个事务也访问了这个数据，并且使用了这个数据。
解决方法：一个事务在修改数据时，该数据不能被其他事务访问
2.不可重复读
一个事务多次读取同一条记录，如果此时另一个事务也访问并且修改了该数据，
则就会出现多次读取出现数据不一致的情况，原来的数据变成了不可重复读取的数据
解决方法：只有在修改事务完全提交后才可以读取到数据
3.幻读
一个事务修改表中的多行记录，但是此时另一个事务对该表格进行了插入数据的操作，
则第一个事务会发现表格中会出现没有被修改的行，就像发生了幻觉一样。
解决方法：在一个事务提交数据之前，其他事务不能添加数据
Oracle中利用“锁”机制来做多事务并发访问控制。
锁定能够保证当某个用户正在更新表里的一行数据时，
其他用户不能同时更新相同的数据行，而且也不能删除或修改被更新的表。
锁定分为两种级别：行级别（TX锁）和表级别（TM锁）。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24行级别(TX锁)
对于Oracle数据库来说，行级锁只有X锁定模式，没有S锁定模式。
表级别(TM锁)
1.表级排他锁（X） exclusive mode
2.表级共享锁（S） share mode
3.共享行级排他锁（SRX） share row exclusive
注：这三种锁是通过专门的命令来申请的
语法：
lock table 表名 in mode;
例如：
-- 以共享锁锁表
lock table tbl_emp in share mode;
-- 以排他锁锁表
lock table tbl_emp in exclusive mode;
4.行级共享锁（RS） row share
5.行级排他锁（RX） row exclusive
注：这两种锁无需通过专门命令来申请，而是通过DDL和DML来自动申请
注意：
1. 所有DML语句默认情况下都会自动申请RX锁，每一行记录都会有唯一的RX锁
2. DQL不会主动申请锁,但在项目中为了满足业务要求，有时select语句需要申请RX锁
如：
1 | select * from s_emp for update; |
nowait 选项表示如果申请不到资源[锁]，则直接抛出异常
wait n 选项表示等待n秒钟后，还申请不到资源，则抛出异常;如果在等待期间，申请到了，则正常执行。
注意：事务的隔级级别越高，则并发性就越低。
所以，不要轻易申请X锁。当然，SELECT命令也不要轻易申请RX锁。
#其他数据库对象：
##序列 [sequence]
作用：此对象是用来生成唯一性的整数值的，所以，它很适合用来产生主键。
- 创建序列语法:
>CREATE SEQUENCE 序列名
[START WITH n] – 从几开始，默认1
[INCREMENT BY n] – 每次自增几，默认1
[MAXVALUE n|NOMAXVALUE] – 序列最高峰值n
[MINVALUE n|NOMINVALUE] – 序列最低峰值n
[CYCLE|NOCYCLE] – 是否循环
[CACHE n | NOCACHE] – 提供n个预分配的序列，保存在内存中
[order | noorder] – 有序还是无序序列
- 创建序列会后怎么用呢？
序列提供了两个伪列供我们使用
NEXTVAL 每次取序列的下一个值
CURRVAL 每次都是从当前会话中取出此序列的当前值。
也就是说，在一个会话中，一定要先取到NEXTVAL后，才能取到CURRVAL
注意：序列的值一旦生成，是不能回滚的。多个表的主键值可以来自于同一个序列。
- 例如
1 | 创建一个序列： |
- 删除序列语法:
> DROP SEQUENCE 序列名;
##索引 [Index]
作用：是一种为了快速找到目标数据记录的一种机制，它由列值与目标
数据的物理存储位置组成。
- 索引的创建有两种方式：
1.自动创建
当表中的某些列添加了主键或唯一性约束时，ORACLE会自动为这些列。
创建唯一性索引，索引名就是约束名
2.
手动创建语法：
>create index 索引名 on table_name(列名[,列名][,…]);
如：
create index index_tbl_emp_fname on s_emp(first_name);注意：
1.索引有自己独立的存储空间和命名空间
2.创建索引也会相对牺牲一些数据库性能
- 思考，当我们查询记录时，数据库管理系统是如何帮助我们定位到目标记录的？
索引的原理：
1.默认情况下，索引是采用BTres（二叉树）的数据结构
2.伪劣（rowid），存放的数据行记录真正的“物理地址”
1 | select * form s_emp where id = 1; |
3.索引建立的原理：
把创建索引的列值与rowid合成一个键值对，这个键值对就是索引，
然后把他们存放到指定的数据结构（二叉树，位图）中，并且是独立的索引空间
4.索引的查询原理
当我们的查询语句中的where条件的列建立了索引，查询就会分为2步：
a.先查索引，在句列中的值直接找到rowid
b.根据第一步得到的rowid直接定位到相应的行记录结束查询
5.建立索引的策略：
1 | a.主键列和唯一性列 适合 |
- 删除索引语法:
>DROP INDEX 索引名;
##视图[View]
视图是数据库的一种高级对象，同表一样。它作为表的另一种表现形式与表共享存储空间
或者更为直接地说，视图本质是一条合法的SELECT命令。
创建视图语法：
>CREATE [OR REPLACE] VIEW 视图名[(
列名 DATATYPE
[,列名 DATATYPE]
[,…]
) ]
AS 子查询语句
[WITH CHECK OPTION [CONSTRAINT 约束名]]
[WITH READ ONLY]
注意：
WITH READ ONLY 表示这个视图只能做SELECT操作，而不能做DML操作
WITH CHECK OPTION 表示视图中的数据绝对不能更新条件中的列值。
例如：
1
2
3
4create or replace view v_emp as
select id,first_name "fname",start_date,salary,dept_id from s_emp
where dept_id = 41
with check option constraints v_emp_ck_dept_id41;因为视图属于高级对象仅仅赋予resource角色不能满足需求
所以创建视图前需要给用户授予创建视图的权限- 视图与表是共享数据存储的，所以用户通过视图去更新了列的值，
则相应的，表中的对应记录也就更新了。 - 视图的作用：
1). 减少复杂性，增强数据的安全性
2). 为了配合对象权限做分级管理。 演示案例：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24首先，创建两个用户
create user csj identified by csj;
grant connect,resource to csj;
Create user zsj identified by zsj;
grant connect,resource to zsj;
其次，利用jsd1611这个用户，依据s_emp表来创建两个不同的视图：
CREATE or replace view v1 as
select id,first_name,salary from s_emp where dept_id = 42
with check option;
CREATE or replace view v2 as
select id,first_name,salary from s_emp where dept_id = 31
with check option;
最后，授权
把v1视图的读权限给到 csj 用户
grant select,update,insert on v1 to csj;
把v2视图的读、写权限给到zsj用户
grant select,update,insert on v2 to zsj;
最终，利用 zsj与csj 登录后，可以查看不同的对象两种视图：
1).简单视图
是指这个查询只能是单表，并且不能使用函数、表达式。
简单视图可以做DML操作，当然，如果你加了WITH READ ONLY选项，
那就不能做DML操作。
2).复杂视图
是指这个查询可以跨多表做关联，当然，也可以使用函数、表达式等。
复杂视图是不能做DML操作的，不管你有没有加WITH READ ONLY选项。删除视图语法:
DROP VIEW view_name;
补充：
1
2
3
4
5
6-- 查询当前用户可以执行的主要操作
select * from session_privs;
-- 查询某个权限可执行的所有操作
select * from DBA_SYS_PRIVS where grantee = 'DBA';(需要DBA)
-- 查询当前用户被赋予的系统角色
select * from SESSION_ROLES order by role;视图的分类：关系视图，内嵌视图，对象视图，物化视图
什么是Schema，以下内容来自官方文档:
1
2
3
4A Schema is a colleacion of database objects(used by user), schema objects are the
logical structures that directly refer to the database's data. A user is is a name
defined in the database that can connect to and access objects. schema and users help
database administartors manage database security.