9.索引+视图+序列（SQL基础）

1. rownum 虚拟列

定义：是针对查询结果，自动生成的一个从1开始的自然数序号

select * from emp;

2. rowid 伪列

定义：实际存在的，标识一条数据的物理位置的18位字符串，除非特意查询才能看到

select t.* ,rowid from emp t;

select * from emp where rowid='AAAYKuAAEAAAAisAAQ';

select t.*,rowid from emp t where deptno=30;

--去重：

select * from emp43;

delete from emp43;

insert into emp43 select * from emp;

select t.*,rowid from emp43 t;

1.在查询时实现去重

select distinct * from emp43;

select t.empno,
       t.ename,
       t.job,
       t.mgr,
       t.hiredate,
       t.sal,
       t.comm,
       t.deptno from emp43 t group by t.empno,
       t.ename,
       t.job,
       t.mgr,
       t.hiredate,
       t.sal,
       t.comm,
       t.deptno;

select * from emp43 t group by t.empno,
       t.ename,
       t.job,
       t.mgr,
       t.hiredate,
       t.sal,
       t.comm,
       t.deptno;

select * from emp43 where rowid in(select max(rowid) from emp43 group  by empno);

2.删除重复数据，只保留一条

delete from emp43 where rowid not in (select max(rowid) from emp43 group  by empno);

索引 index

索引是什么：

(1)一种供服务器在表中快速查找的一行的 数据库结构

(2)可以理解成：一本书的目录

定义：

指向表中的数据，通过建立索引，提高我们的查询效率，依赖于表，添加在列（一般会添加在唯一列上）

oracle 自动使用和维护索引

全盘扫描： 按照一定的条件 在对应的数据列，从第一条数据开始检索到最后一条数据，一旦条件在某数据上成立，

将其rowid取出，然后按照rowid取出整行数据

索引扫描： 按照一定的条件 在对应的索引列，从大概位置开始检索到最后一条符合条件的数据，一旦条件在某数据上成立，

--将其rowid取出，然后按照rowid取出整行数据

select * from emp where deptno=10;

select * from emp where empno=7782;

注意点：

1-没有索引的时候，查询数据会是全表扫描

2-建立索引后，只要查询到索引数据，就会停止查询

创建索引：

语法：

create [unique|bitmap] index 索引名 on 表名(列1，列2...);

索引的分类：

1.按照存储形式分类

• 1.B-tree 索引

使用场景：

列基数比较大的列

列基数： 该字段不重复的个数 --唯一列： 学号、订单号 、身份证号

• 1.1默认的普通索引 B-tree (索引列原始数据+rowid)

create index 索引名 on 表名(列1，列2...);

• 1.2反向键索引 B-tree索引的一种特殊形式 （索引列原始数据反向存储+rowid）

create index 索引名 on 表名(列1，列2...) reverse;

• 1.3 基于函数的索引 B-tree 索引的一种特殊形式（索引列原始数据经过函数处理+rowid）

create index 索引名 on 表名(函数（列1），列2...);

create index ID_comm on emp (nvl(comm,0));

唯一索引 unique --索引列中的数据不能重复的

create unique index 索引名 on 表名(列1，列2...);

非唯一索引 normal

• 2.位图索引 bitmap (位图+rowid)

create bitmap index 索引名 on 表名(列1，列2...);

使用场景：

列基数比较小的列

列基数： 该字段不重复的个数 --性别 婚姻

3.按照列的个数分类

• 单列索引
• 多列索引（复合索引）

（二）. 索引的优点

(1) 加快数据的 '检索速度'

(2) 可以保证列值的 '唯一性'（unique、主键）

(3) 实现表与表之间的 '参照完整性'（外键）

(4) 在使用 order by、group by 子句时，可以减少排序和分组的时间

（三）. 索引的缺点

(1) 当对表数据进行 DML 操作的时候，索引自动维护，'降低 DML 操作的速度'

(2) 索引需要占 '物理空间'，同数据表的 '表空间' tablespace 一样

(3) 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

（四）. 索引的使用原则

(1) '主键' 和 '外键' 会自动创建索引，无需人工操作

(2) 选择正确的表：经常检索包含大量数据的表中小于 15% 的行

(3) 选择正确的列：多个表之间的关联关系列

(4) 合理安排索引列：（A，B，C） A 最常用，B 次之，C 最末 A建立索引

面试常问：

索引的缺点

• 1-索引并不一定加快所有表的查询速度，尤其对于比较小的表
• 2-索引是用空间换时间 来实现的快速查询，因此会影响 DML的效率，因为操作后索引也需要同步更新

注意

sql优化：

• 1.如果对某大表进行筛选时，某列或某几列频繁出现在WHERE子句中，并且检索出的数据低于总行数的15% ，应考虑在这些列上建立索引。
• 2.如果对某大表进行排序时，某列或某几列频繁出现在ORDER BY 子句中，应考虑在这些列上建立索引。
• 3.小表不建议建立索引。
• 4.对于含有空值的列，如果经常在查询时查询非空值，建议在该列上建立索引；如果经常在查询时查询空值，建议在该列上建立基于函数的索引。
• 5.为了提高表连接的性能，应在连接列上建立索引(建立一般普通的索引即可)
• 6.索引是数据库的一种实体对象，级别类似于表，会占用内存空间，ORACLE会自动进行索引维护和使用，表和索引可以建立在不同的表空间。
• 7.通过索引可以提升数据的查询速度，但是会相对地降低DML语句的操作速度，尤其是插和改的速度，ORACLE会花费时间在索引维护上，所以说要把握好索引的数量
• 8.对于列基数比较大的列，适合B-TREE索引，列基数比较小的列，适合位图索引。
• 9.某列可以出现在多个索引中，但相同的某列或某几列无法多次建立索引。(就是可以有多个组合，但不能有重复组合，不同顺序的相同几列视为不同组合)（empno,ename） （empno,ename,deptno） （ename，empno）empno ename
• 10.索引建立后并不一定会被引用，ORACLE 会分析整个SQL后做出最优的执行方式。
• 11.ORACLE会自动在主键约束和唯一约束列上建立唯一索引。

索引失效的情况

• 1.对于一般的B-TREE索引，通配符出现在搜索词的首位时不会引用索引 ename like'%M%'
• 2.在索引列上使用<> !=号时，或对空值进行判断时，索引不会生效 ename<>'张三' ename is not null
• 3.使用索引不能使用 or 连接，否则索引会失效 ename='张三' or ename='李四'
• 4.查询条件有多个列的时候务必要索引列开头 deptno=10 and job=''; ---deptno列上有索引，则deptno要放在条件的开头

修改索引名

alter index 旧索引名 rename to 新索引名

禁用索引

alter index 索引名 unusable;

激活索引

alter index 索引名 rebuild;

all_indexes;--索引表

select * from all_indexes where table_name='CLASS43';

视图 view

定义：视图是将sql查询结果 动态的保存在数据库中，作为一个临时的虚拟表，不占内存空间

创建视图基于的表 叫做基表

作用：在不依赖数据库基表的前提下，将需要的数据通过多个数据库基表联合查询后，得到目标数据集合，创建虚拟表。

创建视图

create view 视图名 as select 查询;

create [or replace] view 视图名 as select 查询 [with read only];----只读视图

创建一个视图包含员工信息表中的20号部门的员工编号，姓名，工资，部门编号

create or replace view V_test805 as select empno,ename,sal,deptno from emp where deptno=20 with read only ;
select * from v_test805;
select * from v_qwer;

练习：

创建视图 包含部门名称，工作地点，员工的小写名字，工资，工资和奖金的合计

create view v_qwer as 
select dname, loc, lower(ename) mz, sal, sal + nvl(comm, 0) xz
  from emp t1
  join dept t2
    on t1.deptno = t2.deptno;

创建视图 包含部门名称，工作地点，员工的小写名字，工资，工资和奖金的合计

create view v_test3 as 
select (select dname from dept where deptno = t.deptno) as 部门名称,
       (select loc from dept where deptno = t.deptno) as 工作地点,
       lower(ename),
       sal,
       sal + nvl(comm, 0) as 合计
  from emp t;

通过视图插入数据

insert into v_test805 values(9990,'yu',9000,10);

删除视图中的数据

delete from v_test805 where empno=7369;通过视图删除的数据，基表中的数据也会被删除

修改视图中的数据

update v_test805 set sal=10;

通过视图修改的数据，基表中的数据也会被修改

update v_qwer set sal=8888 where mz='allen';

update v_qwer set mz='abc' where sal=2975; ---更细视图时，不能修改虚拟列

-----*****通过基表创建的视图，可以通过增删改视图来达到对基表的增删改*****

注意点：

• 1.视图基于的表叫做基表
• 2.视图可以像普通表一样去查询
• 3.视图本身不占用存储空间，它是动态获取的查询结果
• 4.对于基表中没有的列，定义视图的时候必须要加列别名
• 5.基于视图创建的新视图，视图的基表数据发生变化，也会引起新视图的变化

修改视图 内容

create or replace view 视图名 as select 查询语句;

修改视图 名字

rename 旧视图名 to 新视图名;

删除视图

drop view 视图名;

序列

sequence

定义： 是oracel 提供的一组能自动增长的序列号; 序列（sequence） 是序列号生成器，可以为

表中的行自动生成序列号，产生一组等间隔的数值。 不占用磁盘空间，占用内存。

作用：主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检索当前值，或者使序列增至下一个值。

使用场景：

在设计表的时候 需要一个不用明确意义的列来作为主键，这个时候可以用序列。

序列可以是升序也可以是降序。

创建语法:

create sequence 序列名;

start with n--定义序列的初始值 默认为1

select * from emp;

create sequence sq43

start with 100; --注意：此时仅仅是创建了一个序列，它还没有任何值，

select sq43.nextval from dual; --- 序列的下一个值 使用序列第一次使用时，必须要先执行一次 nextval 取到它的初始值。

select sq43.currval from dual; ---序列当前值

insert into emp43 (empno,ename) values(sq43.nextval,'李四');

select * from emp43;

完整语法：

create sequence 序列名

start with n--定义序列的初始值 默认为1

increment by n --增长幅度 默认1 如果设置为正数 递增 如果设置为负数 递减

maxvalue n|[nomaxvalue] ---默认最大值为 10的27次方 对于递减序列 最大值 -1

minvalue n|[nominvalue] ---默认最小值为 -10的27次方 对于递增序列 最小值 1

cycle | [nocycle] ---循环|不循环

cache n| [nocache] --缓存 n |没有缓存 默认20--设置缓存时，缓存值必须小于 cycle值

cache<= ceil((maxvalue-minvalue)/abs(increment))

create sequence sq431

start with 2

increment by 2

maxvalue 57

cycle;

select sq431.nextval from dual; ---使用序列第一次使用时，必须要先执行一次 nextval 取到它的初始值。

select sq431.currval from dual;--没有最小值的循坏序列，执行到最大值后，再次取值，会取到1；有最小值时，循环后，从最小值开始

select * from emp;

create sequence sq435

start with 5

increment by 2

maxvalue 57

minvalue 2

cycle;

select sq435.nextval from dual; ---使用序列第一次使用时，必须要先执行一次 nextval 取到它的初始值。

select sq435.currval from dual;

注意：

• 1-第一次使用序列时，必须先使用下一个值nextval，第一次使用nextval，结果是初始值
• 2-一次nextval 会增加一个序列的值，所以在同一个事务中，使用多个nextval，其值是不一样的

insert into emp43(empno,ename) values(sq431.nextval ,'uuu');

insert into emp43(empno,ename) values(sq431.nextval ,'uuu');

select * from emp43;

• 3-无法修改序列的初始值，只能删除重建序列，或者设置一个新的增长幅度，通过执行nextval 执行到想要的初始值
• 4-序列的初始值不能小于最小值
• 5-创建一个循环序列，必须要设置最大值
• 6-如果创建带缓存的序列，缓存值要满足以下公式：cache <=ceil(maxvalue -minvalue)/abs(increment)
• 7-有循环时，在循环后，序列是从1开始循环，如果有最小值，会从最小值开始循环。

修改序列

修改内容

alter sequence sq431 increment by 1;

修改序列名

rename 旧序列名 to 序列名;

删除序列

drop sequence 序列名;

注释

comment --对表或者每一列添加一个说明

创建注释

列注释

comment on column 表名.列名 is '注释的内容';

comment on column class43.sno is '学号';

表注释

comment on table 表名 is '注释的内容';

comment on table class43 is '43班';

删除列注释

comment on column class31.sno is '';

comment on table class43 is '';