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格式:

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

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;

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

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

rowid格式

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具体的权限可以看下表：

系统权限区别:

约束

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

数据库对象:

同义词：就是给数据库对象一个别名。 
序列：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值）

craete index index_sno on student(sno);

2.位图索引

create bitmap index index_sno on student(sno);

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

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 (索引)