Oracle学习(二)

20181229 update:

查询一组数据中最新的一条,注意不能直接在where条件中and rownum=1.要结合子查询适用,这样才能得到正确的结果

---

删除数据

DELETE FROM student;  

删除所有记录，表结构还在，写日志，可以恢复的，速度慢。  Delete 的数据可以恢复。

savepoint a; --创建保存点  
DELETE FROM student;  
rollback to a;  --恢复到保存点  

一个好的数据库管理员,在确保完成无误的情况下要定期创建还原点。

DROP TABLE student; --删除表的结构和数据；  
delete from student WHERE xh = 'A001'; --删除一条记录；  
truncate TABLE student; --删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。

truncate 删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。

分页查询 :

rownum格式:

1.无ORDER BY排序的写法(效率最高)
–(经过测试，此方法成本最低，只嵌套一层，速度最快！即使查询的数据量再大，也几乎不受影响，速度依然！)

SELECT *
  FROM (SELECT ROW_.*, ROWNUM ROWNUM_
          FROM (SELECT *
                  FROM TABLE1
                 WHERE TABLE1_ID = XX
                 ) ROW_
         WHERE ROWNUM <= 20)
 WHERE ROWNUM_ >= 10;

2.有ORDER BY排序的写法。(效率最高)
–(经过测试，此方法随着查询范围的扩大，速度也会越来越慢哦！)

SELECT *
  FROM (SELECT ROW_.*, ROWNUM ROWNUM_
          FROM (SELECT *
                  FROM TABLE1
                 WHERE TABLE1_ID = XX
                 ORDER BY GMT_CREATE DESC) ROW_
         WHERE ROWNUM <= 20)
 WHERE ROWNUM_ >= 10;

对于 Oracle 的 rownum 问题，很多资料都说不支持>,>=,=,between…and，只能用以上符号(<、<=、!=)，并非说用>,>=,=,between…and 时会提示SQL语法错误，而是经常是查出的结果与预期不同.

rownum是伪列，是在获取查询结果集后再加上去的 （获取一条记录加一个rownum）。对符合条件的结果添加一个从1开始的序列号.
rownum是动态的，必有查询结果，然后再给查询的结果集添加上这个列。

由于这个特性 ,所以一下这个sql查询的结果就是空集:

select rownum, phone_no from ur_user_info where rownum > 5 and rownum < 10;  ---查询结果为空集

当产生结果集时，oracle会产生一条rownum为1的记录，显然不符合条件；那么就会产生第二条记录，同样rownum=1,也不符合记录； 一直下去，导致最后上述sql产生的结果集时空集。
如果修改为:

SELECT * FROM (SELECT ROWNUM nums,phone_no FROM ur_user_info) WHERE nums > 5 AND nums <= 10 ;   ---

可以理解为:子查询的时候产生了从1开始的记录,这时候在子查询的基础上,使用rownum > 5 ,这个是时候就可以查出结果了.
同样的:如果使用了子查询,哪怕子查询中没有限定rownum的范围,在子查询的基础上,依然可以使用如> ,>=这些连接符来查询,

任何时候想把 rownum = 1 这条记录抛弃是不对的，它在结果集中是不可或缺的，少了rownum=1 就像空中楼阁一般不能存在，所以你的 rownum 条件要包含到 1 .

---

rowid格式

rowid和rownum都是Oracle的伪列;
rowid是物理存在的，实际存在的一个列，是一种数据类型。 基于64为编码的18个字符来唯一标识的一条记录的物理位置的一个ID。
唯一标识出对应的存储的物理位置, 类似hashcode值。
rowid并未存储在表中，所以不支持增删改操作，只能用户查询。
可以看到ROWID确实由18个字符组成，组成结构如下：

数据对象编号	文件编号	块编号	行编号
OOOOOO	FFF	BBBBBB	RRR

比较:

-	rownum	rowid
伪列	是	是
格式	从1开始,依次递增	18个字符
是否实际存在	不是实际存在	是物理存在的,但是也不是实际存在于表中的
应用	不能直接使用>,>=,=,between…and等连接符	可以where rowid > 5 and rowid < 10;并且可以用来去重

ROWNUM与ROWID不同，ROWID是插入记录时生成，ROWNUM是查询数据时生成。ROWID标识的是行的物理地址。ROWNUM标识的是查询结果中的行的次序。

补充:还有一种with as的写法,可以学习一下:

WITH ChildData AS
     (SELECT ROW_.*, ROWNUM ROWNUM_
          FROM (SELECT *
                  FROM TABLE1
                 WHERE TABLE1_ID = XX
                 ORDER BY GMT_CREATE DESC) ROW_
         WHERE ROWNUM <= 20)

SELECT * FROM ChildData  WHERE ROWNUM_>= 10;

---

用户权限

sys和system用户区别
1）最重要的区别，存储的数据的重要性不同
sys所有oracle的数据字典的基表和视图都存放在sys用户中，这些基表和视图对于oracle的运行是至关重要的，由数据库自己维护，任何用户都不能手动更改。sys用户拥有dba，sysdba，sysoper等角色或权限，是oracle权限最高的用户。

system用户用于存放次一级的内部数据，如oracle的一些特性或工具的管理信息。system用户拥有普通dba角色权限。

2）其次的区别，权限的不同
system用户只能用normal身份登陆em，除非你对它授予了sysdba的系统权限或者syspoer系统权限。
sys用户具有“SYSDBA”或者“SYSOPER”系统权限，登陆em也只能用这两个身份，不能用normal。

Sysdba和sysoper两个系统权限区别
normal 、sysdba、 sysoper有什么区别
normal 是普通用户
另外两个，你考察他们所具有的权限就知道了
sysdba拥有最高的系统权限，登陆后是 sys
sysoper主要用来启动、关闭数据库，sysoper 登陆后用户是 public
sysdba和sysoper属于system privilege，也称为administrative privilege，拥有例如数据库开启关闭之类一些系统管理级别的权限sysdba和sysoper具体的权限可以看下表：

系统权限区别:

sysdba	sysoper
Startup(启动数据库)	startup
Shutdown(关闭数据库)	shutdown
alter database open/mount/backup	alter database open/mount/backup
改变字符集	none
create database(创建数据库)	None不能创建数据库
drop database(删除数据库)	none
create spfile	create spfile
alter database archivelog(归档日志)	alter database archivelog
alter database recover(恢复数据库)	只能完全恢复，不能执行不完全恢复
拥有restricted session(会话限制)权限	拥有restricted session权限
可以让用户作为sys用户连接	可以进行一些基本的操作，但不能查看用户数据
登录之后用户是sys	登录之后用户是public

---

约束

在oracle中，约束包括：not null、 unique， primary key， foreign key，和check五种。

约束类型	特点
not null	如果在列上定义了not null，那么当插入数据时，必须为列提供数据。
unique	当定义了唯一约束后，该列值是不能重复的，但是可以为null。
primary key	用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null。一张表最多只能有一个主键，但是可以有多个unqiue约束
foreign key	用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。
check	用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间如果不在1000-2000之间就会提示出错。

---

数据库对象:

同义词：就是给数据库对象一个别名。 
序列：Oracle中实现增长的对象。 
视图：预定义的查询，作为表一样的查询使用，是一张虚拟表。 
索引：对数据库表中的某些列进行排序，便于提高查询效率。

同义词

语法结构：同义词

CREATE [OR REPLACE] [PUBLIC] SYSNONYM [schema.]synonym_name  FOR [schema.]object_name 

语法解析：

① CREATE [OR REPLACE：]表示在创建同义词时，如果该同义词已经存在，那么就用新
创建的同义词代替旧同义词。 
② PULBIC：创建公有同义词时使用的关键字，一般情况下不需要创建公有同义词。 
③ Oracle中一个用户可以创建表、视图等多种数据库对象，一个用户和该用户下的所
有数据库对象的集合称为Schema（中文称为模式或者方案），用户名就是Schema名。一个数据库对象的全称是：用户名.对象名，即schema.object_name。

---

序列

序列(Sequence)是用来生成连续的整数数据的对象。序列常常用来作为主键中增长列，序列中的可以升序生成，也可以降序生成。创建序列的语法是：

CREATE SEQUENCE sequence_name
[START WITH num]
[INCREMENT BY increment]
[MAXVALUE num|NOMAXVALUE]
[MINVALUE num|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE num|NOCACHE]

语法解析：

① START WITH：从某一个整数开始，升序默认值是1，降序默认值是-1。 
② INCREMENT BY：增长数。如果是正数则升序生成，如果是负数则降序生成。升序默
认值是1，降序默认值是-1。 
③ MAXVALUE：指最大值。 
④ NOMAXVALUE：这是最大值的默认选项，升序的最大值是：1027，降序默认值是-1。 
⑤ MINVALUE：指最小值。
⑥ NOMINVALUE：这是默认值选项，升序默认值是1，降序默认值是-1026。
⑦ CYCLE：表示如果升序达到最大值后，从最小值重新开始;如果是降序序列，达到最小值后，从最大值重新开始。
⑧ NOCYCLE：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。默认NOCYCLE。
⑨ CACHE：使用CACHE选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率。Oracle默认会生产20个序列号。
⑩ NOCACHE：不预先在内存中生成序列号。

---

视图

视图（View）实际上是一张或者多张表上的预定义查询，这些表称为基表。从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的SELECT…FROM即可。 
 
视图具有以下优点： 
① 可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基表。 
② 可以将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。 
③ 限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定的安全性。 
④ 从多张基表中按一定的业务逻辑抽出用户关心的部分，形成一张虚拟表。

语法结构：创建视图

CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
AS
SELECT查询
[WITH READ ONLY CONSTRAINT] 

语法解析：

① OR REPLACE：如果视图已经存在，则替换旧视图。 
② FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。 
③ NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。 
④WITH READ ONLY：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制（比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行insert操作），WITH READ ONLY说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。

---

索引

创建索引

单列索引  
单列索引是基于单个列所建立的索引，比如：

create index 索引名 on 表名(列名);   

复合索引
复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同，比如：

create index emp_idx1 on emp (ename, job);  
create index emp_idx1 on emp (job, ename);

索引的缺点

索引有一些先天不足：  
1. 建立索引，系统要占用大约为表1.2倍的硬盘和内存空间来保存索引。  
2. 更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。  
实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。   
比如在如下字段建立索引应该是不恰当的：  
1. 很少或从不引用的字段；  
2. 逻辑型的字段，如男或女（是或否）等。

综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立.

其它索引

介绍

按照数据存储方式，可以分为B*树、反向索引、位图索引；  
按照索引列的个数分类，可以分为单列索引、复合索引；  
按照索引列值的唯一性，可以分为唯一索引和非唯一索引。  
此外还有函数索引，全局索引，分区索引…

1.B树索引（默认索引，保存讲过排序过的索引列和对应的rowid值）

1）说明：
　　1.oracle中最常用的索引；B树索引就是一颗二叉树；叶子节点（双向链表）包含索引列和指向表中每个匹配行的ROWID值
　　2.所有叶子节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同
　　3.能够适应精确查询、模糊查询和比较查询
2）分类：
　　 UNIQUE,NON-UNIQUE(默认),REVERSE KEY（数据列中的数据是反向存储的）
3）创建例子

craete index index_sno on student(sno);

4）适合使用场景：
列基数（列不重复值的个数）大时适合使用B数索引

2.位图索引

1）说明：
1.创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建立一个位图（位图中，对表中每一行 使用一位（bit，0或者1）来标识该行是否包含该位图的索引列的取值，如果为1，表示对应的rowid所在的记录包含该位图索引列值），最后通过位图索 引中的映射函数完成位到行的ROWID的转换

2)创建例子

create bitmap index index_sno on student(sno);

3)适合场景：
对于基数小的列适合简历位图索引（例如性别等）

3.单列索引和复合索引（基于多个列创建）

1)注意：
　　即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引，即至少要包含组合索引的第一列
多列索引:

craete index index_sno on student(sno,sname);

4.函数索引

1)说明：
　　1. 当经常要访问一些函数或者表达式时，可以将其存储在索引中，这样下次访问时，该值已经计算出来了，可以加快查询速度
　　2. 函数索引既可以使用B数索引，也可以使用位图索引；当函数结果不确定时采用B树索引，结果是固定的某几个值时使用位图索引
　　3. 函数索引中可以水泥用len、trim、substr、upper（每行返回独立结果），不能使用如sum、max、min、avg等
2）例子：

create index fbi  on student (upper(name));
select * from student where upper(name) ='WISH';

---

---

权限

常用的系统权限

create session   连接数据库         
create table  建表  
create view  建视图             
create public synonym   建同义词  
create procedure 建过程、函数、包   
create trigger  建触发器  
create cluster 建簇

---

更多:
https://www.cnblogs.com/szlbm/p/5806070.html(ORACLE 中ROWNUM用法总结!)
https://blog.csdn.net/Honey_Potter/article/details/53014284 (ORACLE分页查询SQL语法)
https://www.cnblogs.com/xqzt/p/4449184.html(Oracle rowid)

https://wenku.baidu.com/view/2cd673d080eb6294dd886cc2.html?sxts=1543128412564 (面试题)

http://www.dedecms.com/knowledge/data-base/oracle/2012/0810/4489.html (用户)

https://www.cnblogs.com/wishyouhappy/p/3681771.html (索引)