Oracle_Day08(视图、索引、同义词)
视图
是通过查询语句将查询出来的结果创建为一个表现形式(视图),就是满足查询语句的一个子表
作用:
为了简化开发人员对于复杂sql的编写。
提高复杂sql语句的复用
创建或修改视图:
Create or replace view 视图名称 as 查询的sql语句
--查询出10部门的员工信息,并存储在一个视图中
create or replace view emp10
as
select * from emp where deptno=10;
查询视图:
--视图的使用:查询视图
select * from EMP10;
--查询30部门员工信息,同时计算出员工的年薪
create or replace view emp30
as
select empno,ename,job,sal,sal * 12 yearSal from emp where deptno=30;
--示例:查询每个部门中最低薪资、最高薪资、平均薪资
create or replace view gourpSal (no,minSal,maxSAL,avgSal)
as
select deptno ,min(sal) ,max(sal) ,avg(sal) from emp group by deptno;
create or replace view gourpSal
as
select deptno ,min(sal) minSal,max(sal) maxSAL,avg(sal) avgSal from emp group by deptno;
当我们在创建视图的子查询中,如果出现了分组函数的使用,必须为每一个分组函数起一个别名。
删除视图:
Drop view 视图名称
--删除一个视图
drop view emp10;
Top-N 分析:
- Top-N 分析查询一个列中最大或最小的 n 个值:
- 销售量最高的十种产品是什么?
- 销售量最差的十种产品是什么?
- 最大和最小的值的集合是 Top-N 分析所关心的
- 查询最大的几个值的 Top-N 分析:
SELECT [column_list], ROWNUM FROM (SELECT [column_list] FROM table ORDER BY Top-N_column) WHERE ROWNUM <= N;
注意:对 ROWNUM 只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据。
--练习1:查询员工表中,工资前10名的员工信息。
select empno,ename,sal,hiredate,rownum as rn
from (select empno,ename,sal,hiredate from emp order by sal desc)
where rownum <=10;
--练习2:查询员工表中,工资排名在10-20之间的员工信息。
create or replace view top5
as
select empno,ename,sal,hiredate from
(select rownum rn ,empno,ename,sal,hiredate
from
(select empno,ename,sal,hiredate from emp order by sal desc )
)
where rn <=10 and rn > 5
select * from top5;
索引
- 一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中
- 索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
- 索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引
- 在删除一个表时,所有基于该表的索引会自动被删除
- 通过指针加速 Oracle 服务器的查询速度
- 通过快速定位数据的方法,减少磁盘 I/O
创建索引:
- 自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引
- 手动创建: 用户可以在其它列上创建非唯一的索引,以加速查询
CREATE INDEX index ON table (column[, column]...);
--创建索引
create index emp_name_index
on emp(ename);
什么时候创建索引:
- 列中数据值分布范围很广
- 列经常在 WHERE 子句或连接条件中出现
- 表经常被访问而且数据量很大 ,访问的数据大概占数据总量的2%到4%
什么时候不要创建索引:
- 表很小
- 列不经常作为连接条件或出现在WHERE子句中
- 查询的数据大于2%到4%
- 表经常更新
创建索引的表一定是查询的频率大于修改的频率
删除索引:
DROP INDEX index;
同义词-synonym
使用同义词访问相同的对象:
- 方便访问其它用户的对象
- 缩短对象名字的长度
在oracle 中,每个用户只能直接访问属于自己所有的表、视图、序列、索引
如果要访问其他用户的对象,则需要在对象上指明你所要访问的对象的所属用户
--system访问scott用户的emp表
select * from scott.emp;
企业的开发中,当项目经理或者数据库管理员创建好了数据表之后,但是此时数据表只属于项目经理用户或这数据库管理员所有, 那么对于其他的开发人员来说,如果要访问数据表,则在每一个sql语句上的表明上都需要添加表的所属用户。
--创建同义词
create synonym s_emp for scott.emp;
select * from s_emp;
同义词对表的修改也会影响到基表。
删除同义词:
Drop synonym 同义词的名称
--删除同义词
drop synonym s_emp;
视图也可以创建同义词
数据库对象:
- 表: 存储数据
- 视图: 表的逻辑相关的数据的集合
- 索引: 提高查询效率
- 同义词: 给对象起别名
- 序列: 提供一组有规律的数值(主要提供主键的自增)