Oracle （3）事物、序列、权限、视图、索取
大纲：
1．数据库事务
2．数据库对象—表,约束,序列
3．用户和权限相关（普通用户只需要两个角色）
4．视图,同义词
5．索引：工作原理、操作、执行计划、强制索引、应用场景
一、数据库事务
a.什么是数据库事务？
事务是保持数据的一致性，它由相关的DDL或者DML语句做为载体，这组语句执行的结果要么一起成功，要么一起失败。
b.事务的特性
原子性 (Atomicity) :一个事务里面所有包含的SQL语句是一个执行整体，不可分割，要么都做，要么都不做.
一致性 (Consistency) :事务开始时，数据库中的数据是一致的，事务结束时，数据库的数据也应该是一致的
隔离性 (Isolation): 多个事务并发的独立运行，而不能互相干扰，一个事务修改，新增，删除数据在根据当前事务的事务隔离级别基础上，其余事务能看到相应的结果.
持久性 (Durability) : 事务被提交后，数据会被永久保存.
c.事务的开始和结束
Oracle的默认事务开启和结束是跟mysql不一样的。
回顾：mysql的事务是如何开启的？
MySQL默认采用autocommit模式运行。这意味着，当您执行一个用于更新（修改）表的语句之后，MySQL立刻把更新存储到磁盘中，不需要手动提交。如果需要手动管理事务，需要显式的关闭自动事务：Set autocommit false，然后显式的手动开启事务：START TRANSACTION，直到手动COMMIT或ROLLBACK结束事务为止。
那么，Oracle的事务是如何开启的？
Oracle的事务默认是手动管理事务，事务是自动开启（不需要显式的开启，隐式开启），但一般需要手动提交关闭。
Oracle事务的开始和结束的触发条件：
事务的开始：以第一个DML语句(insert update delete)的执行作为开始，即是自动开启的事务。
事务的结束（以下条件之一）：
- 显式结束：commit， rollback（还是隐式commit）
- 隐式结束（自动提交）：DDL（create table…）和DCL（所以不能回滚 ），exit(事务正常退出)
- 隐式回滚（系统异常终止）：关闭窗口，死机，掉电
工具上的事务按钮
控制事务-保留点SAVEPOINT
SAVEPOINT的作用：
使用 SAVEPOINT 语句在当前事务中创建保存点，语法：SAVEPOINT 保留点名称。
使用 ROLLBACK 语句回滚到创建的保存点。语法：ROLLBACK TO 保留点名称。
【示例演示】
SELECT * FROM TEST;
INSERT INTO TEST VALUES(85,NULL);
SELECT * FROM TEST;
SAVEPOINT aa;--保留点
INSERT INTO TEST VALUES(86,NULL);--后悔了，不插入了
SELECT * FROM TEST;
--回滚
ROLLBACK TO aa;
SELECT * FROM TEST;
INSERT INTO TEST VALUES(87,NULL);
SELECT * FROM TEST;
--提交
COMMIT;
SELECT * FROM TEST;
注：当前事务提交后，事务中所有的保存点将被释放。
二、数据库对象-表（TABLE）
a.什么是数据库对象？
数据库对象，是数据库的组成部分.有表（Table ）、索引（Index）、视图（View）、用户（User）、触发器（Trigger）、存储过程（Stored Procedure）、图表（Diagram）、缺省值（Default）、规则（Rule）等。
b.表的命名规则和命名规范
c.创建表CREATE TABLE
d.复制表
Create table 新表 as select from 旧表 条件(如果条件不成立,则只复制结构,如果条件成立,复制结构和值).
e.修改表
1.修改表的列：
2.通过工具来修改表
f.删除表
三、约束
约束是可以更好的保证数据库数据的完整性和一致性的一套机制。
约束可以限制加入表的数据的类型。
如果存在依赖关系，约束可以防止错误的删除数据，也可以级联删除数据。
a.常见的约束类型
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- DEFAULT
- CHECK—用来检查一个字段的值是否符合某表达式,表达式的结果必须是布尔值。
工具快速添加约束
四、序列-sequence
序列：可供多个用户来产生唯一数值的数据库对象。
-
自动提供唯一的数值。
-
共享对象
-
主要用于提供主键值
-
将序列值装入内存可以提高访问效率
注：Mysql中主键有自增长的特性.Oracle中,主键没有自增长这个特性。常用序列高效的生成主键值。
语法：
将序列提前装入内存，可以提高效率。
a.简单的序列
b.序列的使用
ORACLE中为序列提供了两个伪列：
1，NEXTVAL 获取序列对象的下一个值（指针向前移动一个后获得该值。）
2，CURRVAL 获取序列对象当前的值
【示例】
原因是：序列初始化之后指针在第一个数之前。必须先向前移动才可以查询的到。数组的指针默认在1之前，并没有指向第一个值，要想使用必须向前移动一下。（指针只能向前不能向后）
【示例】在插入数据的时候插入序列主键.
--在插入数据的时候插入序列主键.
INSERT INTO TEST VALUES(seq_test.nextval,'Jack');
c.序列的裂缝
1，序列是一个共有对象，多个表都可以调用。但实际开发中，可以避免多个表用一个序列（创建多个序列）。序列是独立的对象。*任意表都可以使用，*但是编号就不能保证有序。
2，当插入记录时报错，序列对象值也被使用，下一次再使用时，序列的值就会+1.
【示例】序列的裂缝
INSERT INTO T_TESTSEQ VALUES(seq_test.nextval,'张三1');
ROLLBACK;
INSERT INTO T_TESTSEQ VALUES(seq_test.nextval,'张三2');
COMMIT;
SELECT * FROM T_TESTSEQ;
故：用序列插入数据库的值不一定是连续的。
序列出现裂缝的条件：
- 事务回滚。
- 系统异常。
- 多个表同时使用同一个序列。
五、用户和权限
a.预定义用户（内置账户）
Oracle预定义有很多用户，用于不同的用途。这些用户大都默认是禁用的（如scott，hr等），但有两个最重要的用户是默认开启的，这两个用户就是SYS和SYSTEM。
SYS 帐户(数据库拥有者):
- 拥有 DBA 角色权限
- 拥有 ADMIN OPTION 的所有权限
- 拥有 startup, shutdown, 以及若干维护命令
- 拥有数据字典
SYSTEM 帐户
- 拥有 DBA 角色权限.
Sys和system账户的区别：
sys用户是数据库的拥有者，是系统内置的、权限最大的超级管理员帐号。
system用户只是拥有DBA角色权限的一个管理员帐号，其实它还是归属于普通用户。
b.操作用户
创建用户的语句
create user 用户名
identified by 密码(不要加引号)
default tablespace 默认表空间名 quota 5M on 默认表空间名
[temporary tablespace 临时表空间名]
[profile 配置文件名] //配置文件
[default role 角色名] //默认角色
[password expire] //密码失效
修改用户
alter user 用户名 identified by 密码 quota 10M on 表空间名
alter user 用户名 account lock/unlock
删除用户
drop user 用户名 [cascade].如果要删除的用户中有模式对象，必须使用cascade.
【示例】最简方式创建一个用户
1.切换到sys用户下：
2.注：未指定的参数都采用默认值。
创建用户的时候指定的表空间，会成为以后在该用户下建立对象（表）的默认存储表空间。
注意：
企业开发中，建表要手动指定表空间，可以让不同模块、不同功能的对象存储在不同的数据文件中，可以提高性能。
【示例】删除用户
提示，每个数据库用户帐户具备：
- 一个唯一的用户名
- 一个验证方法
- 一个默认的表空间
- 一个临时表空间
- 权限和角色
d.配置角色和权限
常见权限：
例：将create session权限赋权给新建的用户.新建的用户就可以登录了.
一个用户会拥有很多权限，这样一个个添加权限非常的麻烦。
如何简单的方式将普通用户的权限赋予给一个用户呢？
通过预定义内置角色就可以实现。
注意需要分配 unlimited tablespaces 权限。
如何选择预定义的角色呢？
普通用户就选择：connect和Resource角色即可。
管理员用户选择：connect和Resource、dba角色。
grant connect,resource to username;
如果遇到这个错误：
说明当前用户**没有操作该表空间的权限，**需要手动加入这个权限：
e.跨域访问对象
跨域访问也称之为跨用户访问、跨方案访问，访问的方式为：用户名.对象名.
如在icbc用户下访问scott用户下的emp表的数据：
Select * from scott.emp;
原因：没有对象访问权限。
Oracle用户的权限分为两种:
-
系统权限(System Privilege): 允许用户执行对于数据库的特定行为，例如：创建表、创建用户等
-
对象权限(Object Privilege): 允许用户访问和操作一个特定的对象，例如：对其他方案下的表的查询
需求：icbc用户要读取scott用户中emp表的数据。
–scott用户登录赋权：
-Sql语句：grant select on EMP to icbc;
–icbc用户登录测试：
注意：赋权的时候，只能是自己拥有的权限、或者该权限是可以传递的，才可以将其赋予别人。
六、视图VIEW
问题：
icbc用户现在只需要查询10部门的员工数据就行了，scott也不想将所有数据都开放给icbc用户。
a.视图的概念和作用
- 视图是一种虚表.
- 视图建立在已有表的基础上, 视图赖已建立的这些表称为基表。
- 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
- 视图向用户提供基表数据的另一种表现形式
作用：
b.语法
c. 操作视图
视图只能创建、替换和删除，不能修改。
创建视图：
【示例】创建10号部门的视图
--sql语句建立视图
CREATE VIEW v_emp10
AS
SELECT * FROM emp WHERE deptno=10
查询视图
视图的真实内容查看
替换视图
视图没有修改功能。
【示例】要将视图改为可以查询10号部门的员工信息且工资要大于2000；
CREATE OR REPLACE VIEW v_emp10
AS
SELECT * FROM emp WHERE deptno=10 AND sal >2000;
编写建立视图的语句时候，一般直接把replace加上，即直接CREATE OR REPLACE。
删除视图
【示例】删除10好部门的这个视图
DROP VIEW v_emp10;
SELECT * FROM v_emp10;
d.几个参数说明
1.一般我们都是先有表再创建视图。
2.视图默认情况下和表一样，拥有表类似的功能，可以crud
3.视图，我们主要用来查询的，一般不维护它。
e. 只读视图
视图主要用来提供查询的，并不希望用户去修改它，因此，我们可以创建只读视图。
创建只读视图只需要添加with read only 选项即可，这样就可以屏蔽对视图的DML操作。
【示例】将已有的视图修改为只读视图
CREATE OR REPLACE VIEW v_emp_dept10
AS
SELECT * FROM emp WHERE deptno=10 AND sal >2000
WITH READ ONLY ;
f.跨域访问视图
【示例】只放开scott下的emp表的部分数据给icbc14用户查询，开放的数据要求为：20号部门的员工，字段只显示员工号和姓名，且要求这两个字段的标题显示为中文。（要求本例使用工具来操作）
–将该视图赋予icbc用户：在scott用户下操作：
grant select on v_emp_dept20 to icbc;
–切换到icbc用户下进行查询验证
Select * from scott.v_emp_dept20;
视图可以屏蔽筛选不同字段、字段名称等，因此，你看到的时候的字段也未必是真实表中存在的！
g.视图小结
视图和表的区别：
视图是实体表的映射，视图和实体表区别就是于视图中没有真实的数据存在。
什么时候使用视图：
1，在开发中，有一些表结构是不希望过多的人去接触，就把实体表映射为一个视图。
2，在项目过程中，程序人员主要关注编码的性能、业务分析这方面。对于一些复杂的SQL语句，设计人员会提前把这些语句封装到一个视图中，供程序人员去调用
七.同义词SYNONYM
同义词就是（对象的）别名，可以对表、视图等对象起个别名，然后通过别名就可以访问原来的对象了。
【需求】在icbc用户下为视图scott.v_emp_dept20创建一个同义词emp20;
–查询验证：
八、索引INDEX
a.索引的概念特性和作用
简单的说，相当于一本书的目录。(数据库中的索引相当于字典的目录(索引)),它的作用就是提升查询效率。
特性：
- 一种独立于表的模式（数据库）对象, 可以存储在与表不同的磁盘或表空间中。
- 索引被删除或损坏, 不会对表（数据）产生影响, 其影响的只是查询的速度。
- 索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引.
用户不用在查询语句中指定使用哪个索引。 - 在删除一个表时, 所有基于该表的索引会自动被删除
- 如果建立索引的时候,没有指定表空间,那么默认索引会存储在所属用户默认的表空间.
作用：
1.通过指针（地址）加速Oracle 服务器的查询速度。
2.提升服务器的i/o性能（减少了查询的次数）；
b.索引的工作原理
c.操作索引
索引的常见操作可以创建、删除。
c1. 创建索引
索引有两种创建方式:
自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引。
手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询。
唯一索引的数据不重复，一般用于主键或唯一约束上创建。
其他普通的列的数据，一般可以重复，那么就不要创建唯一索引，创建一个非唯一、普通索引。
【示例】主键自动创建索引
在emp表的empno上增加主键PK_EMPc2 .手动创建
可以在一个列或多个列上同时创建索引。
如果在多个列上创建索引，也称之为联合索引。（类似联合主键的概念）
【示例】手动创建索引
生成的sql：
-- Create/Recreate indexes
create index idx_emp_ename on EMP (ename);
--缺点：没有指定表空间，生产环境下一般要将索引单独指定表空间。
create index idx_emp_ename on EMP (ename) tablespace USERS;
如果在生产环境下，创建表空间的脚本最好不要指定表空间的具体参数，例如将下图的深蓝色的地方去掉。
c3 删除索引
d.执行计划Explain Plan
采用执行计划,查看索引是否生效以及预估索引性能效果.
–补充知识：脚本的执行
执行脚本有两种方式：
1.使用command窗口的editor子窗口执行。
将脚本直接粘贴到editor窗口，然后点击执行按钮或按F8：
2.在命令行使用@方式执行。
在命令行下执行：@sql脚本路径，如：
@E:\oracle\bigdatatest.sql
第二种方法适合大量脚本的批量执行。推荐
d2. 打开执行计划窗口有两种方式：
- 直接打开执行计划的空窗口，然后输入需要执行的SQL查询语句。
- 在SQL查询窗口中的语句上，按F5，可以自动打开执行计划窗口，并且会将选中的SQL语句自动填入执行计划窗口中自动执行。
【示例】演示索引的效率查看
–在testname字段上创建索引
创建索引前后的结果对比：
e.强制索引
查询字段上增加索引后，查询操作时索引一定会生效么?上面的语句修改一下，执行后发现索引没有生效.
解决方案是：使用强制索引。
强制索引的语法：
/+INDEX(表名,索引名字)/
SELECT /*+index(bigdatatest,idx_bdt_testname)*/ * FROM bigdatatest WHERE testname <> 'name0';
强制索引对于超大的数据量的表来说是有一定的作用的，虽然它是全索引扫描，但扫描索引比扫描表速度还是快.
强制索引的使用注意：
1．尽量少用强制索引，如何避免使用，条件上尽量不要用<>, like ‘%aa%’
2．如果要用强制索引，在非常大的数据量的情况下使用。
f. 索引的创建场景
索引不是万能！以下情况可以创建索引:
- 列中数据值分布范围很广
- 列经常在 WHERE 子句或连接条件中出现
- 表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%
下列情况不要创建索引:
- 表很小
- 列不经常作为连接条件或出现在WHERE子句中
- 表经常频繁更新
索引的详细使用：
请百度 oracle的降龙十八掌