sql复习（视图、Top-N分析、其他数据库对象）

一、视图view

1.视图定义

视图是一种虚表。
视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句。
视图向用户提供基表数据的另一种表现形式。

2.使用视图的好处

控制数据访问
简化查询
避免重复访问相同的数据

3.简单视图和复杂视图

没有用分组函数的都是简单视图

在 CREATE VIEW 语句中嵌入子查询

create or replace view empview3
as
select department_name dept_name,avg(salary) avg_sal
from employees e,departments d
where e.department_id = d.department_id
group by department_name

在复杂视图中进行DML（增删改）操作时会出现错误信息
ORA-01732: 此视图的数据操纵操作非法

--删除视图empview3
drop view empview3;

删除视图不会删除原表的数据

如果不希望用户通过视图修改数据，可以不给用户赋予该视图的update，delete，insert的权限。也可以在创建视图时使用with read only设置只读属性，创建视图时加在最后。

--只读视图
create or replace view employee_vu
as
select last_name,employee_id,department_id
from employees
where department_id = 80
with read only

二、Top-N分析

--查找employees表中工资排名前十的员工信息
select rownum,employee_id,last_name,salary
from(select employee_id,last_name,salaryfrom employeesorder by salary desc)
where rownum <= 10

注意:：
对 ROWNUM 只能使用 < 或 <=, 用 =、 >、 >= 都不能返回任何数据。
所以当查询中间部分的信息时，需要将rownum变成一个真实的列，不再是一个伪列

rownum “伪列”——数据表本身并没有这样的列，是 oracle 数据库为每个数据表 “加上的” 列。
可以标识行号，默认情况下 rownum 按主索引来排序. 若没有主索引则自然排序。

select rn,employee_id,last_name,salary
from (select rownum rn,employee_id,last_name,salaryfrom(select employee_id,last_name,salaryfrom employeesorder by salary desc))
where rn > 40 and rn <=50

三、其他数据库对象（序列、索引、同义词）

1.序列sequence

序列:：可供多个用户用来产生唯一数值的数据库对象
自动提供唯一的数值
共享对象
主要用于提供主键值
将序列值装入内存可以提高访问效率

<1>CREATE SEQUENCE 语句

--定义序列
CREATE SEQUENCE sequence
[INCREMENT BY n]  --每次增长的数值
[START WITH n]    --从哪个值开始
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]     --是否需要循环
[{CACHE n | NOCACHE}];  --是否缓存登录

create sequence empseq
increment by 10 --每次增长10
start with 10 --从10开始增长
maxvalue 100 --提供的最大值
cycle --需要循环
nocache --不需要缓存登录

<2>NEXTVAL 和 CURRVAL 伪列

NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用
CURRVAL 中存放序列的当前值
NEXTVAL 应在 CURRVAL 之前指定 ，否则会报CURRVAL 尚未在此会话中定义的错误。

<3>ALTER SEQUENCE 语句

用ALTER SEQUENCE 语句来修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存

<4>DROP SEQUENCE 语句

使用 DROP SEQUENCE 语句删除序列

2.索引index

索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
通过指针加速 Oracle 服务器的查询速度

--创建索引
CREATE INDEX emp_last_name_idx
ON employees(last_name)

可以使用数据字典视图 USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息

--查询索引
SELECT  ic.index_name, ic.column_name,ic.column_position col_pos,ix.uniqueness
FROM  user_indexes ix, user_ind_columns ic
WHERE  ic.index_name = ix.index_name
AND  ic.table_name = 'EMPLOYEES'

删除索引操作不可回滚

--删除索引
DROP INDEX emp_last_name_idx

3.同义词synonym

--为employees表创建同义词
CREATE SYNONYM e
FOR employees

--删除同义词
DROP SYNONYM e