视图(View)

什么是视图

可以通过创建表的视图来表现数据的逻辑子集或数据的组合。视图是基于表或另一个视图的逻辑表，一个视图并不包含它自己的数据，它象一个窗口，通过该窗口可以查看或改变表中的数据。视图基于其上的表称为基表。

视图的优越性

• 视图限制数据的访问，因为视图能够选择性的显示表中的列。

• 视图可以用来构成简单的查询以取回复杂查询的结果。例如，视图能用于从多表中查询信息，而用户不必知道怎样写连接语句。

• 视图对特别的用户和应用程序提供数据独立性，一个视图可以从几个表中取回数据。

视图类型

简单视图和复杂视图

创建简单视图

--简单视图：表的数目一个，不能包含函数，不能包含数据分组，可以通过视图进行DML操作

--create view 视图名 as  select 列名1 列别名1，列名2 列别名2 …………from  表名

--或者表别名直接写在视图名后————————视图名（别名）

--也可以不设置列别名，默认列名为为查询表中的列名

create view v_emp as select e.employee_id id ,e.first_name name ,e.salary sal from employees e

创建视图 v_emp  此时视图中的列和内容为employees表中的employee_id , first_name,salary

--操作视图（查询，删除，修改，添加）

create view v_jobs as select j.job_id j,job_title from jobs j----------创建表

select * from V_jobs ----------------------------------------------------------查询视图中的内容

insert into V_jobs values('test','test')---------------------------------------插入内容

update V_jobs set j='abc',job_title='abdc' where j='test'-------------修改内容

delete from V_JOB where ,job_title='abdc'------------------------------------删除内容

--创建只读视图（不允许DML）with read only

create view v_emp2 as select e.employee_id id ,e.first_name name ,e.salary sal from employees e with read only

insert into v_emp2 values(99,'test',1000)  --------------------------此时该语句无法执行.因为该视图是只读视图.不能进行DML操作

--复杂视图：

表的数目一个或多个，可以包含函数，可以包含数据分组，不能通过视图进行DML操作

语法结构:
create view 视图名  as  select  列名1（可带函数） 列别名1，列名2（可带函数） 列别名2 …………from  表名1,表名2，where 表关联语句，条件语句，group by 分组的列名

--或者表别名直接写在视图名后————————视图名（列别名，列别名2，）

--需要注意的是，跟使用函数的列名，必须使用列别名

create view v_dept_sal as
select d.department_id,avg(salary) avgsal,min(salary) minsal,max(salary) maxsal from employees e,departments d
where e.department_id=d.department_id group by d.department_id  select * from v_dept_sal
该视图连接了两张表,并使用了函数,该视图只能进行查询,不能进行DML操作

--删除视图

drop view 视图名
drop view v_emp2----------------------------------执行该语句,就删除了e_emp视图

--内检视图：内检视图由位于FROM子句中命名了别名的子查询创建

--（将一个查询语句的结果，看做一直虚拟的表/视图进行操作）

--显示部门中，低于部门最高薪水的员工名字，薪水，和部门编号，和他们部门的最高薪水

select em.first_name,em.employee_id,em.salary,e.MAXSAL

from employees em ,(select MAX(e.salary)MAXSAL,e.department_id  from employees e group by e.department_id)e

where  em.department_id=e.department_id and em.salary<e.maxsal

上面代码中将:select MAX(e.salary)MAXSAL,e.department_id  from employees e group by e.department_id看做是一个视图作为一个表进行操作

--top-n查询在需要基于一个条件，从表中显示最前面的N记录或者最后面的N条记录

--ROWNUM伪列（行号），从子查询返回的每一行指定一个从1开始的连续数值

select e.* ,rownum from employees e

employees表中,实际并没有rownum,但oracle数据会自动创建一个隐性的伪列,rownum ,该列的值是从1开始的整数,

--从employees表中查询薪水排行前十的员工信息

首先需要查询以薪水排序显示所有员工信息--select * from employees order by salary desc

将上方查询作为一个视图进行操作(所有视图和表中,都有隐性的rownum), 所以只需要查询rownum小于等于10的即可

select * from (select * from employees order by salary desc) where rownum<=10

--查询员工，工作时间资历最久的5个人

跟上方查询员工信息操作一样,只需要按照时间进行排列然后查询rownum小于等于5的即可

select * from (select * from employees order by HIRE_date ) where rownum<=5

Oracle 的分页查询

当查询的结果集数据量过大时，可能会导致各种各样的问题发生，例如：服务器资源被耗尽，因数据传输量过大而使处理超时，等等。最终都会导致查询无法完成。解决这个问题的一个策略就是“分页查询”，也就是说不要一次性查询所有的数据，每次只查询一部分数据。这样分批次地进行处理，可以呈现出很好的用户体验，对服务器资源的消耗也不大。

oracle 中分页的实现思路：在内建视图中通过 rownum 伪劣值的判断来指定获取数据的数量。

示例----每页显示10条数据

查询.第一页，前10条数据的方法
select * from employees where rownum <=10
select * from （select e.*,rownum rn  from employees e where rownum <=10）where rn>0 and rn <=10

查询.第二页，去掉第一页后的10条数据
select * from （select e.*,rownum rn  from employees e where rownum <=20）where rn>10 and rn <=20

查询.第三页，去掉前两页后的10条数据
select * from （select e.*,rownum rn  from employees e where rownum <=30）where rn>20 and rn <=30

总结规律，select * from （select e.*,rownum rn  from 表名 e
                 where rownum <=当前页*分页单位）where rn>（当前页-1）*分页单位 and rn <=当前页*分页单位

序列(Sequence)

序列是用户创建的数据库对象，序列会产生唯一的整数。序列的一个典型的用途是创建一个主键的值，它对于每一行必须是唯一的。序列由一个 Oracle 内部程序产生并增加或减少。

序列是一个节省时间的对象，因为它可以减少应用程序中产生序列程序的代码量。序列号独立于表被存储和产生，因此，相同的序列可以被多个表使用。2.2创建序列

--创建序列 create sequence 序列名 [参数列表]

--参数列名表:increment by 增长间隔 如果不是设置.默认是1,也可是负整数,设置负整数,则表示倒序排列
--                  start with 起始值            如果不设置,.默认是1
--                  maxvalue 最大值           如果不设置,升序是10^27,倒序是-1
--                  minvalue 最小值             如果不设置,默认是1
--                  cycle/nocycle 是否循环   如果设置,默认是否
--                  cache/noche 是否缓存    如果不设置,默认是20

--1.创建一个序列名为的dept_seq,增长间隔10,从120开始,最大值9999,不缓存,不循环使用.

create sequence dept_seq increment by 10 start with 120 maxvalue 9999 nocycle nocache

---2.创建默认序列,序列从1开始,增加间隔1,最大值10^24,缓存20,不循环使用

create sequence dept_seq2

--.查询当前用户拥有的序列

select * from user_sequences

--使用序列:nextval和currval

select dept_seq4.nextval from dual -------------nextval     每次使用,序列的值,都会根据序列规则进行增加或减小

select dept_seq.currval from dual----------------currtval     使用,序列都是当前值,不会增加或减小

------------------------------------------------------------使用序列作为主键的值进行添加数据--------------------------------------------------------------------------

insert into locations  values(dept_seq.nextval,'abcdefaf',10012,'zhengzhou','henan','CN')

--------------------------需要注意的是,不管数据是否添加成功,只要序列执行了nextval语句序列的值,就会增加或减小-------------------------------------

修改序列

--alter sequence 序列名  [参数列表]

--需要注意的是,起始值无法修改.

alter sequence dept_seq increment by 10 start with 120 maxvalue 9999 nocycle nocache----------语句执行失败,因为无法修改起始值

alter sequence dept_seq2 increment by 10  maxvalue 9999 nocycle nocache----------------------------语句可以正常执行

--删除序列

drop sequence 序列名

drop sequence dept_seq2-------------------删除了序列dept_seq2

索引(Index)

在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引提供对表中行的直接和快速访问，它的目的是用已索引的路径快速定位数据以减少磁盘 I/O。索引由 Oracle 服务器自动使用和维护，索引逻辑地和物理地独立于他们索引的表，这意味者索引可以在任何时候被创建或删除，并且不影响基表或其它的索引。当删除表时，相应的索引也被删除。

索引的类型

唯一性索引：当你在一个表中定义一个列为主键，或者定义一个唯一键约束时 Oracle服务器自动创建该索引，索引的名字习惯上是约束的名字。非唯一索引：由用户创建，例如，可以创建一个 FOREIGN KEY 列索引用于一个查询中的连接来改进数据取回的速度。

创建索引的方式

• 自动：在一个表的定义中，当定义一个 PRIMARY KEY 或 UNIQUE 约束时，一个唯一索引被自动创建

• 手动：用户能够在列上创建非唯一的索引来加速对行的访问。

使用索引

过多也件坏事在表上建立更多的索引并不意味者更快地查询，在带索引的表上被提交的每个 DML 操作意味者索引必须更新；与表联系的索引越多，对 Oracle 数据库的影响越大，Oracle 数据库在每次 DML 操作之后必须更新所有的索引。

什么时候创建索引

• 一个列包含一个大范围的值
• 一个列包含很多的空值
• 一个或多个列经常同时在一个 WHERE 子句中或一个连接条件中被使用
• 表很大，并且经常的查询期望取回少于百分之 2 到 4 的行。3.4.2什么时候不创建索引
• 表很小
• 不经常在查询中作为条件被使用的列
• 大多数查询期望取回多于表中百分之 2 到 4 的行
• 表经常被更新
• 被索引的列作为表达式的的一部分被引用

操作索引

非唯一性索引的类型

oracle 的非唯一性索引：单行索引，复合索引(组合索引)，函数索引。

创建索引语法:create index 索引名 on 表名 (列名....)

create index email_index on employees (email)

--创建复合索引

create index job_salary_index on employees (job_id,salary)

--创建函数索引

create index upper_index on employees (upper(email))

--查询当前用户拥有的索引,通过数据字典查询(user_indexes)

select * from user_indexes

--删除索引:drop index 索引名

drop index upper_index-------------删除了upper_index索引

同义词(Synonym)

同义词可以除去对象名必须带的方案限制，并提供给你一个可替换表名、视图名、序列名和存储过程名或其它对象名。该方法对具有特别长的对象的名字很有用。
简单来说,就是给(表/视图.....等)起别名

语法结构:create synonym 同义词名 for 数据对象(表/视图.....)

create synonym e for employees----------------给表employees创建了一个别名e

select * from e----------------------------------------此时查询e就相对查询了employees

--删除同义词:

语法结构:drop synonym 同义词名

drop synonym e------------------------------------删除了同义词名(别名)e.此时只是删除了别名,employees表并不受任何影响