SQL/Oracle——第九章 视图、索引、序列

视图

是一个虚拟表，所有看到的东西都来自于原表

--必须在system下才能授权
conn system/rjxy123456;
--给scott账户授予了一个创建视图的权限
grant create view to scott; 
--切换为scott用户
conn scott/tiger;

--创建一个视图 格式
create or replace view emp_view as 
select empno,ename,job,deptno from emp where deptno = 20;
create or replace view v1 as
 select ename,dname from emp,dept where emp.empno=dept.empno;
--插入一行纪录
insert into emp_view values(9527,'东方','MANAGER',20);
--查看视图的结构
desc emp_view;
--视图考试，员工姓名和部门名称（多表连接视图）任何前面的都能改成视图但是一般不会太难
create or replace view emp_view_complex as select deptno 部门编号 ,max(sal) 最高工资 ,
min(sal) 最低工资,avg(sal) 平均工资 from emp group by deptno;
--通过视图查询
select * from emp_view_complex order by 部门编号;
--删除视图
drop view emp_view_complex;

索引

每一行用一个ID表示，相当于全书的目录，只能决定创建它，(oracle)决定用不用,取决于是否有优化，可以跳跃式进行扫描。

create index 索引名称 on 表名（列名）;

1.B树索引

最常用的索引类型（也是默认的，默认时升序）

create index b1 on emp(deptno);--默认升序
create index emp_deptno_index on emp(deptno desc)--降序

花费时间相同BALANCED，用二分查找
相当于二叉树：根块→分支块→叶块

create index emp_deptno_index on emp(deptno)
pctfree 25 --为将来的INSERT操作预留的空闲时间；若表已经包含了大量数据，在建立索引时，应该仔细规划这个值。
tablespace users;--用于指定索引段所在表空间

2.位图索引：

就是当只有性别男女两列时，不再适合用B树索引，因为只能缩短一半时间，要全盘进行扫描，建立位图。

create bitmap index emp_sal on emp(sal);--创建位图索引
create bitmap index e2 on 产品表(颜色);

3.反向键索引：

create index e3  on emp(job) reverse;

4.基于函数的索引

select * from emp where job=upper('manager');--upper函数
create index e4 on emp(upper('manager'));

5.删除索引：

drop index 索引名;

练习：

例9.1 在scott.emp表的sal字段上创建一个名为index_sal的B树索引，按字段值的降序排列。

create index index_sal on emp(sal desc);

例9.2 在scott.dept表的dname字段上创建一个具有唯一性的B树索引，索引值按字母序排序。

create unique index index_dname on emp(dname);

例9.3 在scott.emp表的ename和deptno字段上创建一个复合索引。

create index index1 on emp(ename,deptno);

例9.4 在scott.emp表中的job字段上创建位图索引bit_index。

create bitmap index bit_index on emp(job);

例9.5 在scott.emp表中的empno字段上创建反向索引re_index。

create index re_index on emp(empno) reverse;

例9.6 在scott.emp表中的hiredate字段上创建一个基于函数的索引。

create index re_h on emp(to_char(hiredate,'yyyy-mm-dd')); 

序列

create sequence s1
start with n
increment by n;

nextval:产生下一个序列号；但是，初次使用，返回的是初始值
currval：返回当前序列号

insert into emp(empno,ename,deptno) values (empno_seq.nextval,'东方',20);
select empno_seq.currval from dual;

insert into emp(empno,ename,deptno) values (empno_seq.nextval,'西方',30);
select empno_seq.currval from dual;

练习:

创建一个新表e1(empno number(5),age number(3),addr varchar2(40),depip number(5));，并且使用上例产生的序列emp_no，插入记录给e2表中empno列产生编号。

create table emp2--建表
(
empno number(5) primary key,
name varchar2(8),
age number(3),
addr varchar2(40),
dep_id number(5)
)
segment creation immediate;  --需要在建表的同时，设置延迟段立即执行

把序列emp_no应用到表emp2中：
insert into emp2 values(emp_no.nextval,'刘丽',25,'河南开封',150);  ---1001
select * from emp2;
select emp_no.currval from dual;

insert into emp2 values(emp_no.nextval,'王英',28,'河南许昌',170);  ---1002
select * from emp2;
select emp_no.currval from dual;

执行结果如下：

SQL> show user
USER 为 "SCOTT"
SQL> create sequence emp_no--创建序列
  2  start with 1001
  3  increment by 1
  4  nomaxvalue
  5  cache 10
  6  nocycle;

序列已创建。

SQL> create table emp2--建表
  2  (
  3  empno number(5) primary key,
  4  name varchar2(8),
  5  age number(3),
  6  addr varchar2(40),
  7  dep_id number(5)
  8  )
  9  segment creation immediate;

表已创建。

SQL> insert into emp2 values(emp_no.nextval,'刘丽',25,'河南开封',150);

已创建 1 行。

SQL> select * from emp2;

     EMPNO NAME            AGE ADDR
---------- -------- ---------- ----------------------------------------
    DEP_ID
----------
      1001 刘丽             25 河南开封
       150


SQL> select emp_no.currval from dual;

   CURRVAL
----------
      1001

SQL> insert into emp2 values(emp_no.nextval,'王英',28,'河南许昌',170);

已创建 1 行。

SQL> select * from emp2;

     EMPNO NAME            AGE ADDR
---------- -------- ---------- ----------------------------------------
    DEP_ID
----------
      1001 刘丽             25 河南开封
       150

      1002 王英             28 河南许昌
       170


SQL> select emp_no.currval from dual;

   CURRVAL
----------
      1002


或者：
conn system/123456;
grant dba to scott;
conn scott/tiger;
alter system set deferred_segment_creation=FALSE;

create sequence  emp_no
start with 1001
increment by 1
nomaxvalue
cache 10
nocycle;


create table emp2
(
empno number(5) primary key,
name varchar2(8),
age number(3),
addr varchar2(40),
dep_id number(5)
);

insert into emp2 values(emp_no.nextval,'刘丽',25,'河南开封',150);  
select * from emp2;
select emp_no.currval from dual;

删除序列：

drop sequence 序列名;

同义词

公有同义词：

--给scott授予create public synonym权限，由scott来创建；或者直接system创建
grant create public synonym to scott;
--(创建公有同义词，对原对象具有权限的所有用户使用)
create or replace public synonym public_dept for scott.dept;
--使用：
select * from public_dept;
--删除：
drop public synonym public_dept;

私有同义词

--scott需要先被system授予create synonym权限
grant create synonym to scott;
--(创建私有同义词，它的创建者拥有、可以使用，其他用户需要被授权才可以使用)
create or replace  synonym private_dept for dept;
--使用：
select * from private_dept;
--删除：
drop synonym private_dept;
--私有同义词只有当前用户可以直接使用，其他用户在引用的的时候必须带上用户名。

创建一个新用户并授权

create user a1 identified by a1;--创建用户a1密码a1
grant create session to a1;--授权连接数据库
conn a1/a1;--连接
select * from scott.private_dept;--表或视图不存在
conn scott/tiger;--已连接。
grant select on private_dept to a1;--授权同义词给其他用户
conn a1/a1--已连接。
SQL> select * from scott.private_dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from public_dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON