oracle索引，视图，序列 事务 锁 数据字典 动态视图 笔记，小白必看！（内有福利）

 索引，视图，序列 事务 锁 数据字典 动态视图 笔记
                                                          ——老子亲笔
                                                       
               ******************  索引 笔记 ****************
                                                               
--创建索引的原因？
数据量大的情况下 并且需要查询的时候 做索引，
给增删改操作少的字段做索引。

--索引的分类 
一。单列索引：给某个列建一个索引，叫单行索引
二。联合索引：给多个列建一个索引，叫联合索引，如果联合索引，在查询时，使用了第一个索引列作为条件，那么它会使用索引提高查询效率，
           如果使用了非第一个索引列作为条件，它不会使用索引

-- 创建索引
语法
create index 索引名字 on 表(字段)--可加多个字段

create index index_emp on emp(ename);--创建索引示例
select * from emp where ename='SMITH';--搜索示例

--创建多个索引语法：
create index index_name on table_name(col_name[,col_name[,col_name,]]);

-- 索引使用注意

注意：使用索引列作为查询条件时--不要在索引列上进行数学运算

create index ind_emp_sal on emp(sal);

select * from emp where sal*12>10000;   --这样写条件不会使用索引
     
select * from emp where sal>10000/12;    --这样它就会使用索引

-- 删除索引
drop index 索引名字
--注意！
只能删除手动创建的索引
主键约束和唯一约束自带的index 不能被删除
drop index PK_EMP;--这个就是删除上述索引 结果是失败的

-- roeid 伪列概念
注:
rowid伪列
  oracle数据库的表中的每一行数据都有一个唯一的标识符,或者称为rowid。 
  ROWID为该表行的唯一标识,该值表明了该行在oracle数据库中的物理具体位置。
        包含着数据对象编号，对应文件编号， 块编号和 行编号的信息。
使用rowid可以去除重复记录
-- 它是Oracle提供一个伪列 他是每个行数据唯一标识， 他是数据在硬盘中保存地址（物理信息）生成

select rowid,emp.* from emp;-- 查询出表中伪列的示例

                                          ************************ 视图 笔记 *********************

-- 视图概念

1.视图，就是一个虚表，我们可以从这个表中查询数据
2.视图，就是一个命名的查询语句
3.任何有效的查询语句都可以作为视图

我们之前做的子查询就是一个视图
视图也就是虚表，实际上是一个命名的查询，
用户改变数据的显示形式，简化查询，访问视图与表的访问方式一样。
          --就是两个表连接起来然后把这个作为一个 虚表 这个表就是视图
select * from emp,dept where emp.deptno=dept.deptno;--示 例

-- 视图有什么好处？

1、可以限制对数据的访问，让用户通过视图可以看到表中的一部分数据
2、可以使复杂的查询变得简单
3、提供了数据的独立性，用户不知道数据来源处
4、提供了对相同数据的不同显示

-- 创建视图语法格式1种

create view 视图名字 as 查询语句; --语法格式
create view HR_VIEW as select * from emp;--示例1
create view XM_VIEW as select empno,ename,mgr,hiredate,deptno from emp;--示例2

-- 创建视图语法格式2种 or replace （如果视图存在，则替换旧视图。）这一种常用

create or replace view HR_VIEW as select * from emp;--示例1

create or replace view XM_VIEW as select empno,ename,mgr,deptno from emp;--示例2

-- 创建视图语法格式3种
--（把两个连接的表作为一个视图 起一个名字 查询出来）

create or replace view view_empinfo
as
select e.empno,e.ename,e.job,e.mgr,e.sal,d.dname,d.loc from emp e,dept d 
where e.deptno = d.deptno;
--select后面接的表现列名必须是一个表的列，如果是e.*，d.*的话，是创建不出来的

-- 创建视图语法格式4种

-- force 基表还没有创建 要先创建视图 就必须用到这个单词，后面创建了基表之后 这个视图就可以用了，要不然这个视图 不可用
create force view HR_VIEW as select * from emp;

-- 查询视图语法

select * from HR_VIEW;
select * from XM_VIEW;

-- 添加 删除 修改 视图内容语法
创建出来的视图本身就是可以进行DML操作的 只要不违反表的约束就可以

insert into XM_VIEW values(8888,'aaa','7782','10');-- 添加

insert into XM_VIEW(ename,empno,sal) values('aaa','10','3000');--添加 2 种

update  view_name set empno=7369 where empno=7360;-- 修改

delete view_name where empno=7369;                -- 删除

--通过cmd界面登录sys系统空间 对用户进行赋予权限

sqlplus / as sysdba -- 这是登录 系统空间的语法（写完敲回车）
grant create view scott -- 然后写上这个 再回车
-这就是赋予了这个用户view视图权限，如果需要赋予别的权限
也可以用这个方式在cmd界面登录系统空间进行赋值

-- 给需要view 视图的用出赋予权限
-也可以直接在sys空间里面 打上下面这段话来进行赋值，如下

grant create view scott -- 这是给scott用户赋予view 视图权限
  

-- 删除视图  drop view 视图名字；

drop view XM_VIEW;

-- 视图注意事项 易错点（重点，重点，重点）

--*******创建的视图本身就是可以进行DML操作的*********

-我们平常在做表连接的时候，会出现相同的列，但是不影响我们查询。
-但是在创建视图的时候，不能出现相同列，这个时候我们就不能创建视图了。
-比如

create or replace view view_empinfo
as                                    
select e.*,d.* from emp e,dept d where e.deptno = d.deptno;
              --这条查询语句是没有问题的，但是在创建视图的时候，就会报错
  
               --这个时候，我们就需要用别名，将每个列都查询出来 或者相同的列只显示一个

create or replace view view_empinfo
as
select e.empno,e.ename,e.job,e.mgr,e.sal,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno;

-- 视图实用示例

 -假如我们在计算员工年收入的时候，我们每次都需要查询，
 -非常麻烦，那么我们就可以把年收入计算出来做成视图，以后就只要在视图中查询就可以了
 
create or replace view view_name
as
select e.empno,e.ename,e.job,e.mgr,e.sal,d.dname,d.loc,sal+nvl(comm,0) 年薪 from emp e,dept d where e.deptno = d.deptno;
        -- 那么我以后查询年薪的时候就可以从视图中查询了
        
select e.*,rowid from view_name e;--用伪列查询视图

-- 视图一般用于查询，不建议DML操作
-- 如果我需要创建一个视图，并且不能进行DML操作，那么我就要用到下面的写法
-- with read only 创建只读视图，不能执行DML操作
create or replace view view_name
as
select emp.*,rownum r from emp
with read only;

delete view_name where empno=7369;

-- 同样的，如果我们需要对视图进行DML操作时，那么我们可以省略 with read only 
--或者 改为with check option

--*******也就是说创建的视图本身就是可以进行DML操作的*********
create or replace view view_name
as
select * from emp
[with check option;]

-- 如果对视图做 DML操作时，需要注意什么

-1、如果违反基表的约束条件时，则不能更新。
-2、如果视图中包含链接操作符、distnct关键字、
-聚合函数、集合操作符或group by 子查询的时候，不能更新
-3、如果视图存在伪劣的时候，则不能更新

--对视图做 DML操作 使用示例

select * from view_name ;
update  view_name set empno=7369 where empno=7360;

                                      
                                  ************************** 序列 笔记 ***********************************

-- 序列

-- 序列的概念
-序列是Oracle自动给我们生成的一个序列的对象

-- 序列有什么用？
- 序列一般用于生成主键或者达到主键自增的效果

-- 序列的作用 我自己的理解
-一般来讲 表格里的主键约束 就是约束这个列是唯一的 而且不能是空值对吧。那这个样子，我们在给表输入数据的时候，正常也就是该怎莫输入就怎么输入
大不了 主键约束的这一列 注意点 别是空的 别是重复的就是了 对吧。一般来讲 主键约束的列都是数字编号之类的，这样就是每次输入不一样的编号
可是一旦给这个主键列 赋予了序列 那就不一样了 。创建的序列 可以自己设定数字，设定数字的自增或者自减的规律，并且还能给这样的序列起一个名字，
这样 在给表格主键列提添加数据的时候就不用跟之前一样，一个一个的输入了，直接 insert into 后面在主键列对应的内容下面 输入序列的名字就行，后面再加
序列的属性 nextval （自动下一个值）或者是 currval（取当前值），就行了，然后后面就直接复制粘贴 主键列的序号就会自动增加1.2.3....6
就折磨点作用，主键列要不是数字的那种 那就当我没说

-- 创建序列的语法框架

-- 一.创建序列简单语法框架  最大值 缓存 是否循环 可以省略
create sequence pk
start with 1       
increment by 1

-- 二.创建序列全面语法框架

create sequence( ) 括号里是序列名字
        start with 1 --  自定义一个开始的值 --表示从1开始 也可以是负数 
    increment by 1 -- 表示每次执行的时候，增加多少。（自增）一般用1，这样后面就必须跟maxvalue 最大值
                   -- 也可以用负数 那这样就是自减 后面跟minvalue 最小值
    minvalue 1 -- 序列最小值，
    maxvalue 10 -- 序列最大值，表示序列到最大值是停止（可省略）
    cache|nocache -- 缓存，可省略，默认为 20 , 省略不写就是 nocache 意思为 没有缓存
    cycle|nocycle -- 是否生成循环，一边和maxvalue使用，表示到maxvalue后从start whit 循环使用，可省略，省略不写就代表 nocycle 意思为不循环

-- 创建序列 示例

create sequence myseq   -- 就是序列名字为 myseq   
start with 1            -- 开始的值是 1
increment by 1          -- 后面是自增 1 
maxvalue 20             -- 最大值是20，自增到20的话就停止了
cache 10                -- 缓存为 10个数

-- 序列里面有俩个属性

-nextval:取序列的值，并且序列的值增加。
-currval:取序列当前的值。不能直接去当前值，需要执行nextval后才可以

-- 查询序列属性 使用示例

create sequence myseq3
start with 1
increment by 1
maxvalue 5
cache 2  
cycle  -- 循环的意思 表示从1-5一直循环 ，只要用下面一号语句输出，输出一次 涨一个数 涨到5再输出，就会循环到1 再到5

select myseq3.nextval from dual;-- 一号
select myseq3.currval from dual;

-- 修改序列

alter sequence 序列名字 maxvalue 5000;、
alter sequence myseq3 cache 15 ;

——注意，不能修改序列的 start whit 值，
——但是能修改 maxvalue、cache、cycle、increment by

-- 删除序列
drop sequence 序列名字;

drop sequence myseq4;

--序列，序列属性 使用示例 （目的为实现主键自增）*******

create sequence pk
start with 1       --这是创建序列 最大值 缓存 是否循环 可以省略
increment by 1
---------------------------
create table b1(
 id number(3) primary key,--创建一个表 给id列主键约束
 name varchar2(50))
--------------------------------
select * from b1;
select pk.currval from dual;-- 查询表 查询 序列
--------------------------------------
insert into b1 values(pk.nextval,'aa');
insert into b1 values(pk.nextval,'bb');--表b1里面的 主键列id 就会1.2.3....这样自增的输入下去
insert into b1 values(pk.nextval,'cc');-- 咱们只去要不断地复制粘贴就行
  
                                        ***********************关闭延迟技术***********************                       
/*
-- 关闭延迟技术                                
ALTER SYSTEM SET deferred_segment_creation=FALSE; 这个我也不知道干啥的 再说吧
*/

                                     *********************** 同义词 笔记 ********************************

-- 同义词：

--概念 作用
就是给表 视图啥的 起一个自己感觉好记的名字，而且是整个数据库都好用，永久好用
不是在这个语句查完了就完事了 永久好用 就这么个逼作用
-- 专业概念
同义词，就是数据库对象的一个别名，
可以简化访问其他用户的数据库对象。缩短了对象名称的长度，
同时屏蔽了对象的名称，使用户不知道最终的数据来源与那个对象。

-- 同义词的分类

1.共有同义词 需要加 public-- 公有就是 全数据库都可以使用
2.私有同义词 不加 public-- 私有就是只有你这个用户可以使用

-- 创建 私有 同义词 语法框架

create synonym 同义词名字 for 表（对象）;

create synonym stu for student;
select * from stu;            --创建了同义词之后 查表就可以这样子查 直接查同义词名字就行 

-- 创建 公有 同义词 语法框架 需要加 public

create public synonym 同义词名字 for 表（对象）;

create public synonym aaa for dept;
select * from aaa; -- 查的时候就可以这样查

-- 同义词 使用 是需要赋予权限的 通过sys 给scott授权

grant create synonym to 用户；       --给用户私有同义词创建权
grant create public synonym to 用户；-- 给用户公有同义词创建权
grant drop public  synonym to 用户； -- 给用户 删除 公有同义词的权利
grant drop synonym to 用户；         -- 给用户 删除 同义词的权利

-- 同义词权限 通过cmd界面登录sys系统空间 对用户进行赋予权限

sqlplus / as sysdba -- 这是登录 系统空间的语法（写完敲回车）

grant create synonym to 用户；       --给用户私有同义词创建权
grant create public synonym to 用户；-- 给用户公有同义词创建权
grant drop public  synonym to 用户； -- 给用户 删除 公有同义词的权利
grant drop synonym to 用户；         -- 给用户 删除 同义词的权利
       -- 然后写上这个 再回车
       
-这就是赋予了这个用户synonym同义词权限，如果需要赋予别的权限
也可以用这个方式在cmd界面登录系统空间进行赋值

-- 同义词 对基表做 DML操作 使用示例

create synonym st for shitu;--创建同义词 给这个表，此时表就是词，词就是表

select * from st;-- 搜索一下 同义词

update stu set sname='王八' where sno='s001';--给表修改数据 就直接给同义词修改数据就行了

-- 删除同义词
        --删除同义词 也是要 赋予权限的 如下
 grant drop public  synonym to 用户；

drop synonym 名字          --删除私用
drop public synonym 名字   --删除公用
    
drop synonym aaa;
drop public synonym aaa;--删除 实例

                            
                                       ************************* 事务 笔记 *****************************

-- 事务：
--概念

一组相关的数据改变SQL语句，这组SQL语句对数据的改变要么同时成功，
要么同时失败。

-- 自我理解：如果理解事务这个词呢？
    在这个数据库中 最基层还有一个模块 就是保持了这个数据库数据最基本形态的一个模块，咱们平常不论在数据库种 增删改也好 运行别的指令也罢
它们确确实实 是在数据库中 被我们改变了，但是 我们这些表 啥的数据 在数据库最基层的模板里面 还是保持着原有的样子。所以 我们要是改砸了
或者是 /*想变回之前的样子*/了，我们就可以/*点 左上角那个 红色 的向下指的箭头图标 来回滚，*/点了之后 我们的数据表 不论改成了啥样子，啪！的一声
就会变回 最初的 在基层模板的 样子。同理 我们要是改完了表数据之后，/*点了左上角的 绿色下指按钮 那么就等同于 提交了事务*/，这样的话 原本保留在
基层模块里的/*最初的表数据的样子 就会变成我们当前提交的表数据的样子*/ 这就基层模块跟个保险一样，万一哪天 改炸了 我们还有个回滚的余地不是！！
这里面 还有 这种 声明保存点:savepoint 的语句 /*savepoint 名字;*/可以用这个单词 做一个临时保存点 相当于 游戏的临时存档 一旦你搞错了
可以用回滚 +临时点名字 回到临时点的那个状态

   主要作用 就是写表的时候 害怕出错导致全删 就每一句添加表数据的语句后面 设置一个临时保存点 到时候 那一句运行错了
直接 事务回滚：rollback +临时点名字 就可以 回到咱们设置的那个点 的存档 类似于 游戏的临时存档 飞车的临时旗帜 

--事务必须具备以下四个属性，简称ACID 属性：

原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行
一致性（Consistency）：当事务完成时，数据必须处于一致状态
隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性

-- 这就是左上角那两个提交跟回滚键 而已 纯属鸡肋
事务提交：commit
事务回滚：rollback
声明保存点:savepoint-- 可以用这个单词 做一个临时点 相当于 游戏的临时存档 一旦你搞错了 可以用回滚 +临时点名字 回到临时点的那个状态

--显式事务  跟 隐式事务  需要在cmd界面 登录在scott用户界面 在输入以下代码 才行
显式事务：
   1.set autocommit off --关闭事务的自动提交
   2.手动调用commit和rollback
隐式事务:                    
   1.set autocommit on 
   2.create、drop、grant等操作事务是自动提交
   3.每一个insert、update、delete执行后都会提交到数据库

-- 用代码的方式 显式事务 隐式事务 开启关闭 提交事务功能 的方法 （很鸡肋）

-- 一.用cmd 界面登录用户账号

    sqlplus scott/123 -- 这个123 是密码 然后就可以再用户里 查询 修改 各种命令了
          
-- 二.cmd 登录后 可以在scott里面 开启和关闭 事务提交功能 （其实没啥用）

   在cmd界面输入这个 set autocommit off --关闭事务的自动提交 （显式事务）
   在cmd界面输入这个 set autocommit on -- 操作事务是自动提交
 

-- 事务的 语法 及 用法*  ********** 重点 ****************
 
savepoint 名字;
-- 可以用这个单词 做一个临时保存点 相当于 游戏的临时存档 一旦你搞错了 可以用回滚 +临时点名字 回到临时点的那个状态
主要作用 就是写表的时候 害怕出错导致全删 就每一句添加表数据的语句后面 设置一个临时保存点 到时候 那一句运行错了
直接 事务回滚：rollback +临时点名字 就可以 回到咱们设置的那个点 的存档

-- 用法 实例
insert into stu values(1,'aa');
savepoint s1;
insert into stu values(2,'aa');
savepoint s2;
insert into stu values(3,'aa');
savepoint s3;
insert into stu values(4,'aa');
savepoint s4;
-- 假如在第三条语句出错了，我单纯的用回滚的话，那么我之前的语句就需要再次添加了。那么我就可以这么回滚
rollback to s3;  -- 这样的话 我就可以 继续从第三条开始写了 类似于 游戏的临时存档 飞车的临时旗帜 

-- 事务 回滚 rollback 设置临时保存点 savepoint 配合 使用示例**********重点*************
insert into student values('s120','大哥',18,'男');
savepoint s1;
insert into student values('s220','大哥',18,'男');
savepoint s2;
insert into student values('s320','大哥',18,'男');
savepoint s3;
insert into student values('s420','大哥',18,'男');
savepoint s4;
insert into student values('s520','大哥',18,'男');
savepoint s5;
insert into student values('s620','大哥',18,'男');
savepoint s6;
insert into student values('s720','大哥',18,'男');
savepoint s7;
select * from student;
s5的这句出错了 那我之前设置了一个 临时保存点 那就可以用一下语句 回滚到这个存档 就不用把之前的都删了
rollback to s5;

                                ********************************** 锁 *******************************

-- 3.锁

--锁的概念

锁是数据库用来控制共享资源并发访问的机制。
锁用于保护正在被修改的数据
直到提交或回滚了事务之后，其他用户才可以更新数据

--事务提交 回滚代码
事务提交：commit
事务回滚：rollback

-- 锁的类型

——行级锁、表级锁

--行级锁概念

行级锁是一种排他锁，防止其他事务修改此行
在使用以下语句时，Oracle会自动应用行级锁：
UPDATE、
DELETE、--这个好理解 就是进行增删改操作的时候 就会自动给这个表上一个锁 当前用户确实是在数据库中对这个表增删改完了
        --但你 你不进行提交 或者回滚的话（就是在程序后面 或者在cmd界面 打上提交/回滚的代码）别的用户依然无法对你这个表进行操作 
        --直到你提交/回滚之后 这个锁才会解除（提交/回滚就是直接改变数据库后面的基层模块里 表的样子，提交完之后这个表在基层模块
        --就彻底变成了当前的样子 再回滚也滚不回来了）
SELECT … FOR UPDATE

--SELECT … FOR UPDATE 的用法 解析

SELECT … FOR UPDATE

-这个的意思就是当前用户对一个表进行select查询的时候，别的用户也可以对这个表同时进行查询，但是我只要在select
-查询的后面加上了for update这句话之后 这个表就上锁了，即使我不进行增删改操作，别的用户也无法进行增删改操作
-比如 我查询的时候这样写 select * from emp for update; 那么这个表就上锁了 别用户只能查询 不能操作 除非我提交/回滚 

--select … for update[wait n | nowait]用法解析

select … for update[wait n | nowait]语句允许用户一次锁定多条记录进行更新
使用COMMIT或ROLLBACK语句释放锁

-- 表级锁

--概念
锁定整个表，限制其他用户对表的访问
使用命令显示的锁定表

--表级锁的语法是:

 LOCK TABLE 表的名字 IN 属性名子 MODE;
 
 --用法实例
 LOCK TABLE emp IN ROW SHARE MODE;
rollback

     -- 以下是属性名字    
行共享 (ROW SHARE) – 禁止排他锁定表
行排他(ROW EXCLUSIVE) – 禁止使用排他锁和共享锁
共享锁(SHARE)
锁定表，仅允许其他用户查询表中的行
禁止其他用户插入、更新和删除行
多个用户可以同时在同一个表上应用此锁
              --这是最牛逼的一个锁
共享行排他(SHARE ROW EXCLUSIVE) – 比共享锁更多的限制，禁止使用共享锁及更高的锁
排他(EXCLUSIVE) – 限制最强的表锁，仅允许其他用户查询该表的行。禁止修改和锁定表

-- 1.数据字典

--概念
数据字典：记录了数据库的最基本的信息。
数据字典包括数据字典基本表和数据字典视图

--数据字典的分类

数据字典视图分为三种类型：USER_、ALL_、DBA_。
     
USER_类型的视图表示当前登录用户拥有的信息。
     
ALL_类型的视图表示当前登录用户有权限看到的信息。 
     
DBA_类型的视图表示数据库管理员能够看到的信息，也
就是整个数据库范围内的信息。

-- 三种类型的实际写法
 user_tables    all_tables      dba_tables
 
select * from user_tables;-- 查看当前用户 所有的表

select * from all_tables;-- 查看当前用户有权限看到的表

select * from dba_tables;-- （使用sys用户输入）查看数据库管理员（sys）能看到的所有表

-- 用户表字典视图 
-- user_tables （用用户登录输入就可以）

select * from user_tables;
select table_name from user_tables;--可以查看这个大表的字段 这里查的是 显示所有表的表名

-- 表字段字典视图 （只显示可以查看当前用户所有表的所有字段字段跟字段类型）
--user_tab_cols

select * from user_tab_cols;
select column_name from user_tab_cols where table_name='EMP';--（EMP要大写）这是查看这里面EMP表的全部字段跟字段类型

-- 用户对象字典视图
-- user_objects

select * from user_objects-- 用户对象字典视图 查出来 有表 有序列 有序列 视图 等等
 
SELECT * FROM  USER_TABLESPACES;-- 查看用户表空间

SELECT * FROM DBA_TABLESPACES;--（用sys'登录）查看的是sys 账户的全部表空间

-- 2.动态性能视图

存储数据库中每时每刻都在变化的信息，主要是数据库的活动状态信息，它把存储在内存里的信息、和存储在控制文件里的信息，以视图的形式展现出来。
常见的动态性能视图：
v$parameter：初始化参数文件中所有项的值，
v$process：当前进程的信息，
v$session:有关会话的信息，
v$sysstat：基于当前操作会话进行的系统统计，
v$log：从控制文件中提取有关重做日志组的信息，
v$logfile有关实例重置日志组文件名及其位置的信息，
v$lock：当前进程已获得的和正在请求的锁定信息，
v$transaction：数据库的事务信息。
v$fixed_view_definition中记录了所有的动态性能视图的定义信息。 

-- 查询以上动态视图的 语句

select * from v$sysstat;-- 这是查询这种表的 方式 以此类推

-- 动态性能视图 用法*************小知识点*************

如果 在scott 用户下，执行查询语句是出现“Oracle动态执行表不可访问，从 什么什么 表里获得权限”那么就执行以下操作
--- 一定要在 sys 用户里面 运行

grant select on V_$session to scott;
grant select on V_$sesstat to scott;
grant select on V_$statname to scott;

-- 这是给与用户 scott 这些动态视图表的权限的语句