ORACLE学习系列四

本学习笔记由本人整理而成，转载请注明出处

———————————————————————————

####【视图、序列、索引】
###【视图】
视图（VIEW）也被称作虚拟的表，视图对应于一条select语句，结果集被赋予一个名字，即
视图的名字。
视图本身不包含任何数据，只包含映射到基表的一个查询语句，当基表数据发生变化，视图数
据也随之变化
create view wenwen as 子查询
根据视图所对应的子查询种类分为几种类型：
1、select语句是基于单表建立的，且不包含任何函数运算、表达式或分组函数，叫做简单视
图，此时视图是基表的子集
2、select语句同样是基于单表，但包含了单行函数、表达式、分组函数或group by子句，叫
做复杂视图
3、select语句是基于多个表的，叫做连接视图
视图作用：
1、简化复杂查询
2、限制数据访问。视图本质上就是一条select语句，所有当访问视图时，只能访问到所对应
的select语句中涉及到的列，对基表中其他列起到安全和保密作用。

###【授权创建视图】create view
##创建视图v_emp_10来显示部门10中的员工的编码、姓名和薪水
create view v_emp_10
as 
select empno,ename,sal,deptno
from emp
where deptno=10;
#查看视图结构
desc v_emp_10;
##创建视图时给列赋予别名，可用or replace短语修改视图对应的sql查询语句
create or replace view v_emp_10
as 
select empno id,ename name,sal salary,deptno
from emp
where deptno=10;
##查询视图
select *from v_emp_10; 

###【对视图进行insert操作】
当对视图执行DML操作时，实际上是对基表的DML操作,简单视图可以通过DML操作影响到基
表数据
##对简单视图执行insert操作，成功插入数据到基表中
insert into v_emp_10
values(1234,'DOCTOR',4000,10);

###【创建具有check option约束的视图】
create [or replace] view view_name 
as subquery 
[with check option];
with check option短语表示，通过视图所做的修改，必须在视图的可见范围内

###【创建具有READ ONLY约束的视图】
create [or replace] view view_name 
as subquery 
[with READ ONLY];
如果没有在视图上执行DML操作的必要，在建立视图时声明为只读来避免这种情况，保证视
图对应的基表数据不会被非法修改
create or replace view v_emp_10
as 
select empno id,ename name,sal salary,deptno
from emp
where deptno=10
with read only;

###【通过user_views查询获取相关信息】
和视图相关的数据字典：
——user_objects
——user_views
——user_update_columns
##在数据字典user_objects中查询所有视图名称
select object_name from user_objects
where object_type ='VIEW';
##在数据字典user_views中查询指定视图
select text from user_views
where view_name = 'v_emp_10';
##在数据字典中user_update_columns中查询视图
select column_name,insertable,updatable,deletable
from user_updatable_columns
where table_name ='v_emp_10';

###创建复杂视图（多表关联）
复杂视图指在子查询中包含了表达式、单行函数或分组函数的视图
必须为子查询中的表达式或函数定义别名
##创建一个视图v_emp_salary,把职员表的数据按部门分组，获取每个部门的平均薪水、薪水
总和、最高薪水和最低薪水
create view v_emp_salary 
as
select d.dname,avg(e.sal)avg_sal,sum(e.sal)sum_sal,max(sal)max_sal,min(sal)min_sal
from emp e join dept d
on e.deptno=d.deptno
group by d.dname;
复杂视图不允许DML操作（update...set...）

##删除视图v_emp_10
drop view v_emp_10;
删除视图不会导致基表数据的丢失
————————————————————————————————————
###【视图实例】
##创建student表
create table student
(
sid number(4,0),
sname varchar2(20),
score number(4,1),
class_id number(4,0)
);
##插入数据
insert into student values(1001,'allen',89.0,101);
insert into student values(1002,'marry',80.0,101);
insert into student values(1003,'jerry',81.0,101);
insert into student values(1004,'tom',74.0,90);
insert into student values(1005,'jim',67.0,90);
insert into student(sid,sname,score) values(1234,'rose',90.0);
##定义视图v_stu,按班级分组，查询学生平均成绩、人数
select class_id,avg(score)avg_score,count(*)cnt from student group by class_id;
##删除视图v_student及v_stu
drop view v_student;
drop view v_stu;
————————————————————————————————————
————————————————————————————————————
###【序列】
序列（sequence）是一种用来生成唯一数字值的数据库对象
序列的值由oracle程序按递增或递减顺序自动生成，一个序列为一个表提供主键值
##创建一个序列，起始数据是100，步进是10
create sequence emp_seq
start with 100
increment by 10;
##序列中有两个伪列
—nextval：获取数列的下个值
—currval：获取数列的当前值
当序列创建以后，必须进行一次nextval，之后才能使用currval
#获取序列的第一个值，并且使用序列值为emp表插入新的记录
select emp_seq.nextval from dual;
#利用序列emp_seq为emp表生成主键，向emp表插入的数据为empno由序列生成、ename
为‘donna’。
insert into emp_seq(empno,ename) values (emp_seq.nextval,'donna');
#查询刚刚生成的记录，主键值将是110
select empno,ename from emp
where ename='donna';
#此时查询序列的当前值
select emp_seq.currval from dual;
#删除序列emp_seq
drop sequence emp_seq;

————————————————————————————————————
————————————————————————————————————
###【索引】
索引是一种允许直接访问数据表中某一数据行的树形结构，为了提高查询效率而引入，是独立
于表的对象
索引记录中存有索引关键字和指向表中数据的指针（地址）
##创建索引
create [unique] index index_name on table(column,...);
—index_name表示索引名称
—table表示表名
—column表示列名，可以建立单列索引或符合索引
—unique表示唯一索引
#在emp表的ename列上建立索引
create index idx_emp_ename on emp(ename);
#如果经常在order by子句中使用job和sal作为排序依据，可以建立复合索引
create index idx_emp_job_sal on emp(job,sal);
#当做下面查询时，就会自动应用索引idx_emp_job_sal
select empno,ename,sal,job from emp order by job,sal;

##创建基于函数的索引
#如果需要在emp表的ename列上执行大小写无关搜索，可以在此列上建立一个基于upper函
数的索引
create index emp_ename_upper_idx on emp(upper(ename));
#当做下面的查询时，会自动应用刚刚建立的索引
select *from emp where upper(ename)='KING';
##删除和修改索引
如果经常在索引列上执行DML操作，需要定期重建索引，提高索引的空间利用率。
#重建索引idx_emp_ename
alter index idx_emp_ename rebuild;
#当一个表上有不合理的索引，会导致操作性能下降，删除索引idx_emp_ename
drop index idx_emp_ename;
————————————————————————————————————
————————————————————————————————————
###【约束】
约束条件包括：
—非空约束(Not null),简称NN
—唯一性约束(Unique),简称UK
—主键约束(Primary Key),简称PK
—外键约束(Foreign Key),简称FK
—检查约束(Check),简称CK