6. 索引/视图/rownum/rowid/同义词/序列

-- 索引
/*
   数据库中使用最多的是表, 其次是索引
   索引:
      方案(schema) 中的一个数据库对象
      在 oracle 数据库中用来加速对表的查询
      通过使用快速路径访问方法快速定位数据, 减少了磁盘的 I/O
      索引是独立的数据库对象, 并不与表存储在一起, 而是与表独立存储
      索引记录了表的索引字段的值, 也就是关键字, 关键字始终与表的索引字段值相同, 这种同步由Oracle 数据库自动维护
*/
-- 创建索引有两种方式
--1. 自动创建: Oracle 会自动为主键和唯一键创建索引
-- 自动创建的索引是无法手动删除的, 但是在删除主键约束, 唯一键约束的时候, 对应的索引会被自动的删除
alter table emp     -- 增加约束
  add constraints Ename_UNI unique(ename);
 
alter table emp     -- 删除约束
  drop constraints Ename_UNI;
 
--2. 可以手动创建约束, 表中什么样的字段应该添加索引?
     /*在查询的时候, 经常被用来作为查询条件的字段, 应该添加索引*/
create index ename_index on emp(ename);

--  删除索引:
drop index ename_index;


-- ========================================视图================
-- 视图, 就是一个虚表, 我们可以从这个表中查询数据
-- 视图,就是一个命名的查询语句, 取了个名字后,视图就能在数据库永久保存
-- 任何有效查询语句, 无论多么复杂, 都可以被创建成一个视图

/*
select *
from emp or (子查询, 子查询实际就是一个视图), 视图
*/

-- 给人事经理使用的视图
create view HR_VIEW
AS
SELECT * FROM EMP;

-- 给项目经理使用的视图
create view MGR_VIEW
AS
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, DEPTNO
FROM EMP;

-- 我们可以在命令行模式下查询表结构, 也可以查看视图的结构
DESC EMP
DESC HR_VIEW

     
SELECT *
FROM HR_VIEW
WHERE EMPNO = 7369;


CREATE OR REPLACE VIEW HR_VIEW
AS
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, DEPTNO
FROM EMP;

-- 提供了对相同数据的不同显示
-- 在 create view 语句后加入子查询

CREATE [OR REPLACE] VIEW HR_VIEW
[(alias[,alias]...)]
AS SUBQUERY
[WITH READ ONLY]
     
     
--  删除视图, 会影响原来的数据吗?
-- 删除视图,不会影响原来的数据
drop view hr_view;
     
    
create or replace view view_empinfo
as
select e.empno, e.ename
from emp e, dept d, locations L
where e.deptno = d.deptno and d.loc = L.loc_id


create or replace view view_emp
(员工编号, 员工姓名, 薪水, 佣金)    -- 创建视图的时候 定义参数名
as
select empno, ename, sal, comm
from emp;

-- 把使用组函数的查询创建成视图
create or replace view v_emp_sal
as
select deptno, max(sal) maxsal, min(sal) minsal, sum(sal) sumsal, avg(sal) avgsal  -- 组函数必须取别名, 不然报错字段名冲突
from emp
where deptno is not null
group by deptno

select *
from v_emp_sal
where maxsal > 200;

-- 视图就是一个(虚)表, 我们可以对表插入, 也可以对视图插入
CREATE OR REPLACE VIEW V_EMPINFO
AS
SELECT * FROM EMP WHERE SAL > 2000;

-- 对视图插入数据
-- 视图插入数据, 数据会被插入到原表中. 视图看不到被插入的数据
INSERT INTO V_EMPINFO2
VALUES(8001, '张2', '软件', 7902, '12-8月-1987', 1900, 500, 20);

-- 创建只读的视图, 不能执行 DML操作 (提示是只读视图)
CREATE OR REPLACE VIEW V_EMPINFO2
AS
SELECT * FROM EMP WHERE SAL > 2000
with read only;

-- 行内视图, 就是出现在 from 后面的子查询, 也就是一个视图,
-- 但是该视图没有命名不会在数据库中保存

-- 查询工资最高的前三个人的信息 , 这种方式被称为 top_N分析法
select rownum, a.*
from (select * from emp order by sal desc) a
where rownum <= 3;

-- rownum 只能使用与 <= 的情况 , 不能用于 >= 的情况
select rownum, a.*
from (select * from emp order by sal desc) a
where rownum >= 12;   -- 这种情况查不到,

select * from V_EMPINFO FOR UPDATE;

create table student1
as
select * from student;

insert into student1 values(1000, '张0');
insert into student1 values(1001, '张1');
insert into student1 values(1002, '张2');
insert into student1 values(1003, '张3');
insert into student1 values(1004, '张4');
insert into student1 values(1005, '张5');
insert into student1 values(1006, '张6');
insert into student1 values(1007, '张7');

-- 数据插入的顺序决定了查询数据时候的顺序, 也就是决定了 rownum 的顺序
/*
   1. rownum 对于等于某值的查询条件
       如果希望找到学生表中第一条学生的信息, 可以使用 rownum=1 作为条件.
       但是想找到学生表中第二条学生的数据, 使用 rownum=2查不到数据.
       因为 Oracle认为 rownum = 1 都是从1开始, 但是 rownum = 2 oracle 则判断认为 False条件
       所以无法查到 rownum = n (n > 1 的自然数)
*/
select rownum, t.* from student1 t where rownum = 1;  -- 可以查到 rownum = 1
select rownum, t.* from student1 t where rownum = 2;  -- 查询不到数据
/*  2.如何才能找到 第二行以后的记录,
可以用子查询的方法来解决, 注意: rownum 必须有别名
*/
select * from (select rownum n, t.* from student1 t) where n = 2;

/*  3.rownum 对于小于某值的查询条件
      如果想找到第三条记录以前的记录, 当使用 rownum<3是能找到前两条的记录的.
      显然 rownum 对于  rownum<n (n>1 的自然数) 的条件认为是成立的, 所以可以找到记录
*/

/*  .可能有时候需要查询 rownum 在某区间的数据, 那如何查出来, 从上面可以看出 rownum 对小于n的查询
条件是认为true的. rownum 对于大于某值的查询条件直接认为是false的, 但是可以间接地让他转为认为是
true 的. 那就必须使用子查询.
例如: 要查询 rownum 在第二行到第三行之间的数据, 包括第二行和第三行之间的数据,
我们先让他返回小于等于三的记录行,然后再主查询中判断新的 rownum的别名列大于er的记录行*/
select rownum, t.* from student1 t where rownum <= 3;  -- 小于等于可以查
select rownum, t.* from student1 t where rownum >= 3; -- 大于等于查不到

select *
from (select rownum no, t.* from student1 t where rownum <= 7)
where no >= 2;

/*
 4. rownum 和 排序
    Oracle 中的 rownum是在取数据的时候产生的序号, 所以想对指定排序的数据去指定rownum 的行数要注意了
*/

select rownum, t.*
from
(select student.* from student order by sname)t

/*
   可以看出, rownum 并不是按照name列生成的序列, 系统是按照记录插入时的顺序给记录排的号
*/
-- 用 rowid 能获得数据的修改权(锁可以打开), (rowid 数据是在硬盘上存放的物理位置)
select rowid, e.* from emp e;  
select e.* from emp e;

--==================同义词======================================
-- 同义词(在synonyms文件夹下面), 就是数据库对象的一个别名, 可以简化访问其他用户的数据库对象
select sysdate
from sys.dual;   -- 从 sys 用户下 的 dual 表里面查询 sysdate

create or replace synonym DUAL1
  for SYS.DUAL;
-- 给 sys.dual 去个别名  dual
SELECT SYSDATE FROM DUAL1;

-- 删除同义词
drop  synonym DUAL1

--===================序列======================
-- 序列, 用来维护数据库的主要数据
drop table student2;

CREATE TABLE STUDENT2(
   SID NUMBER(4) PRIMARY KEY,
   SNAME VARCHAR2(20)
);

create sequence SEQ_STU
minvalue 1
maxvalue 9999
start with 1
increment by 1
cache 20;

-- 使用序列
insert into student2 values(SEQ_STU.NEXTVAL, '张1');

SELECT * FROM STUDENT2;

SELECT SEQ_STU.CURRVAL   -- 主键序列字段, 在 sys用户的 dual 表中保存, 自动新增按照 该字段的数字增加指定步长

FROM SYS.DUAL;


DELETE FROM STUDENT2 WHERE SID >= 10;

-- 我们自己去维护数据库的主键数据


INSERT INTO STUDENT2 VALUES(1, '张1');
INSERT INTO STUDENT2 VALUES(2, '张1');
INSERT INTO STUDENT2 VALUES(3, '张1');
INSERT INTO STUDENT2 VALUES(4, '张1');

select * from STUDENT2;