Oracle入门笔记（九）——视图、序列、索引、同义词和权限等

1.视图

    视图是，基于数据表或者另外一个视图的逻辑表，类似于一个数据表或者数据表间组合之后得到的数据窗口，通过窗口能够查看数据表或者表间组合时候得到的数据，能够避免反复将数据表做相同的表间查询。视图能够突出数据表中的数据，基于视图的权限能够将数据库管理任务或者权限细分。视图分为：简单视图和复杂视图，二者区别如下：

特性	简单视图	复杂试图
涉及表的数量	一个	一个或者多个
包含函数	无	有
包含数据分组	无	有
是否允许基于视图对表操作DML(增删改)	允许(不推荐)	不允许

（1）简单视图，视图的创建需要指定具体的视图字段，和创建数据表有点像：

create view "first_view" as select employee_id,last_name,salary from employees where employee_id=100;

使用和数据表一样：

select * from "first_view";//使用视图
drop view "first_view";//删除视图

（2）复杂视图，创建复杂视图，举例获取部门的平均薪资情况：

create view department_view as select d.department_id,round(avg(em.salary),2) avg from employees em,departments d 
where em.department_id=d.department_id group by d.department_id; 

注意：创建复杂视图的时候，分组函数处理的列需要给出别名。
另外，可以基于视图创建视图：

create view view02 as select * from department_view with read only;

创建视图的时候可以加上一些权限限制，例如：with read only。
（3）内建视图，也即通常所说的子查询，比如select 的where中的子查询和group_by 的having条件。需要注意的是内建视图的别名的使用（查询部门平均薪资等信息）：

内建视图主要会被用来进行Top-n分析(排名分析)，这里需要提到另外一个概念：rownum（伪列）：

既然提到了rownum（伪列），前面在数据查询的时候说掉了一个规则，数据查询的时候如果使用了类似于select e.* from employees e ，那么就不能在并列添加其他列了，但是rownum除外。这个rownum需要注意一下，如果使用

select employee_id,last_name,salary from employees  where 
rownum>2

那么将查不到数据，因为第一条不满足去掉的话，第二条的rownum又成了1，所以永远没有满足条件的记录。除了被用在Top-n上，还被用在分页查询上，经典分页查询语句（错误举例）：

select employee_id,last_name,salary from employees  where 
rownum>(pageSize-1)*pageLimit and rownum<pageSize*pageLimit;

原因和上面一样：因为第一条不满足去掉的话，第二条的rownum又成了1。正确写法如下(利用子查询巧妙将内部的rownum显化)：

select employee_id,last_name,salary from 
(select employee_id,last_name,salary,rownum as rn from employees)
where rn<20 and rn>10;

除了rownum，还有一个rowid,rowid是数据表中的每一行数据都有一个唯一的标识符，该值表明了该行在oracle数据库中的物理具体位置。地址详细含义解剖可以看这篇文章（简直梦回计算机组成原理）。

2.序列

    序列是数据库中的一种对象，会产生唯一整数，是辅助主键的良好工具。序列的创建需要用到sequence时间发生器，事件发生器使用时的参数如下：

参数	描述
increment by n	序列生成的整数的间隔（默认为1）
start with n	指定序列计数开始的整数值（默认为1）
nomaxvalue	序列生成的整数上限（递增10^27，递减-1）,属于默认选项
maxvalue n	指定序列生成的整数上限
nominvalue	序列生成的整数下限（递增1，递减-10^-(26)）,属于默认选项
minvalue n	指定序列生成的整数下限
cycle|nocycle	在达到上限或者下限之后是否循环
cache|nocache	指定Oracle预先分配多少值，并且保存在内存中，默认生成20个

实例：

create SEQUENCE employee_seq INCREMENT by 1 start with 120 MAXVALUE 9999 nocycle nocache;

也可以利用可视化工具配置序列：

也可以利用数据库的内部维护表user_sequences查看序列:

select * from user_sequences;

需要明白MySQL中没有序列（但是MySQL中可以直接设置主键id的自增），Orcale的序列就是用来实现这个功能的，只是需要采用插入的形式添加到指定的数据表中，使用方法如下（说白了就是Oracle中的自定义逐渐自增，防止重复）：

序列的修改需要是创建者或者有alter权限的用户才能够修改，start with在序列创建之后不能修改，修改示例：

3.索引

    在关系型数据库中，索引是一种单独的、物理的对数据库中的一列或者多列的值进行排序的一种存储结构，它是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
    索引的作用相当于图书馆的目录 ，可以根据目录中的页码快速找到所需内容。目的是用已索引的路劲快速定位数据以减少磁盘I/O。
    索引由Orcale自动使用和维护，索引在物理和逻辑上独立于数据表，能够被创建和删除而不影响原有的数据表。数据库在更新数据表之后往往会由Oracle数据库来维护这些索引，对于数据庞大的数据库，索引的使用能够提高性能，但是对于数据比较少的数据表，强制使用索引未必能够得到性能的提升。索引使用一般有一下几种前提：
（1）一个列包含一个大范围的值；
（2）一个列包含很多的空值；
（3）一个或者多个列经常在查询中被使用；
（4）一个表数据庞大，但是一般只需要取出少于4%的列。
    索引大体上包含唯一性索引和非唯一性索引。唯一性索引是数据库根据主键约束和唯一性约束的列自动创建的，非唯一性索引包含三大类：单行索引、复合索引和函数索引。
（1）单行索引，创建单行索引（给employees的last_name创建单行索引）：

create index employee_index on employees(last_name);

（2）复合索引，即索引值有多个（给employees的manager_id,location_id创建单行索引）：

create index employee_index on employees(manager_id,location_id);

注意：复合索引被触发的前提是，查询条件种包含有manager_id,location_id，并且manager_id在前，location_id在后，当然在查询条件种二者不必紧挨着，否则索引查询不会被触发，仍然做全表扫描。
（3）函数索引，即创建索引的时候使用了函数：

create index employee_index on employees(upper(last_name));

触发条件同样是查询的时候使用相同函数进行查询，查看索引的整体情况可以使用系统内部的user_indexes表：

4.同义词

    就和长期别名一样，同义词的对象包括：表、视图、序列等。主要是简化比较长的命名。

create SYNONYM emp for employees;
select employee_id,last_name,salary from emp;
drop SYNONYM emp
//注意不能同时执行，sql异步

5.数据控制语言（DCL）

    数据控制语言通俗点来说就是权限。需要说明一点，一般数据库权限的分配都是由system级别用户来完成的，权限的种类如下：

权限名	描述
create session	连接权限
create table	创建数据表权限
create sequence	创建序列权限
create view	创建视图权限
create procedure	创建存储过程权限
unlimited tablespace	无限表空间权限

说明一下这个procedure，存储过程是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中。存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。可将DML用存储过程封装起来与数据库提供的事务处理结合一起使用。具有高效、复用和安全（主要还是权限问题，指定特定用户使用）等特点。
使用举例：

创建用户并授予权限：

create user "user01" IDENTIFIED by  "password";//设置密码
grant create session to "user01";//配置连接权限
grant create session,create table,create view,UNLIMITED TABLESPACE to "user01";//创建表和设置表空间的须同时配置，否则还是无法创建数据表

注意在创建用户时注意一下（可能不同版本不一致，不过2022/3/27版的sql developer确实有这个坑）：

权限收回：

revoke create session from "user01";

以角色为单位管理权限（角色的意义其实就是把权限封装）：

create role "user01";
grant create session to "user01";
grant "user01" to "user02";
//注意是create role,而不能是直接将用户赋予用户

6.执行计划

    执行计划是Oracle中查询语句执行的访问计划的描述，主要用于做SQL查询优化分析。由于工具报错加上隐隐感觉到Oracle在使用上的存在一些问题，不如MySQL简洁，于是决定停下来，博客现就写道这里了，最后附上大佬关于MySQL的优化文章。

给出一个总结：整体上通过Oracle的学习扩展了自己对于SQL的知识面，但是仍有少数重点等待自己深入学习，特别是执行优化。
其实，我很想把这个oracle提供的优化功能，展示出来，但是由于自己并没有实际在开发中使用Oracle数据库，所学的还是一些理论知识，因此，看到这里你可能有些失望，如果真想看看，可以参考下面的这篇文章：
使用Oracle Developer对SQL进行简单调优
最后，深表歉意。