Oracle总结第二篇【视图、索引、事务、用户权限、批量操作】-优快云博客

前言

在Oracle总结的第一篇中，我们已经总结了一些常用的SQL相关的知识点了…那么本篇主要总结关于Oralce视图、序列、事务的一些内容…

在数据库中，我们可以把各种的SQL语句分为四大类…

（1）DML（数据操纵语言）：select，insert，update，delete
（2）DDL（数据定义语言）：create table，alter table，drop table，truncate table
（3）DCL（数据控制语言）：grant select any table to scott/revoke select any table from scott
（4）TCL（事务控制语言）：commit，rollback，savepoint to 回滚点

批量操作

何为批量操作，就是一次性插入多条数据….在SQL中，我们查询出来的数据可看成是一张表，那么我们在插入数据的时候，可以根据查询出来的数据进行插入…这就可以看成是批量操作…

值得注意的是，如果没有指定插入哪些字段的话，那么查询出来的全部字段均会插入表中..


将xxx_emp表中所有20号部门的员工，复制到emp表中，批量插入，insert into 表名 select ...语法
insert into emp
select * 
from xxx_emp
where deptno=20;

删除操作对比

我们的删除语法有三种：

delete from
truncate from
drop from

我们来对比一下他们的区别：

drop table

1)属于DDL
2)不可回滚
3)不可带where
4)表内容和结构删除
5)删除速度快

truncate table

1)属于DDL
2)不可回滚
3)不可带where
4)表内容删除
5)删除速度快

delete from

1)属于DML
2)可回滚
3)可带where
4)表结构在，表内容要看where执行的情况
5)删除速度慢,需要逐行删除

事务

事务其实我们在JDBC章节中已经讲解过了：http://blog.youkuaiyun.com/hon_3y/article/details/53760782

再次明确一下：事务就是让一个不可分割的子操作形成一个整体，该整体要么全部执行成功，要么全部执行失败

我们值得注意的是Oracle中的事务与Mysql中的事务操作是有些不同的：

Oracle的事务开始：

第一条DML操作做为事务开始【并不需要手动开启事务】

Oracle的提交事务

（1）显示提交：commit
（2）隐藏提交：DDL/DCL/exit(sqlplus工具)【注意】

Oracle的回滚事务

（1）显示回滚：rollback
（2）隐藏回滚：关闭窗口(sqlplus工具)，死机，掉电

因为Oracle有实例池这个概念，所以Oracle支持回滚…

这里写图片描述

Oracle默认支持的隔离级别是：read commited

Mysql默认支持的隔离级别是：reapatable read


Oracle中设置事务隔离级别为serializable
set transaction isolation level serializable;

访问其他用户的对象

在上一篇博文已经说了，Oracle将表/用户都看成是对象…那么我们怎么在scott用户下访问hr用户下的表呢？？？

其实，我们只要在访问表的时候，指定具体的用户.数据库表就行了，但是呢，还要看看该用户有没有权限查询别的用户的数据表，于是就需要赋予权限了…

练习：

声明：scott或hr叫用户名/方案名/空间名
      scott--tiger
      hr-----lion

查询当前用户是谁
show user;

查询scott自己表空间下的所有对象时，可加，或不加用户名select * from emp;
select * from emp;
或
select * from scott.emp;

以sysdba身份解锁hr普通帐户
alter user hr account unlock;

以sysdba身份设置hr普通帐户的密码
alter user hr identified by lion;

当scott查询hr表空间下的所有表时，必须得加用户名
select * from hr.jobs;

在默认情况下，每个用户只能查询自已空间下的对象的权限，不能查询其它用户空间下的对象

以sysdba身份角色，授予scott用户查询所有用户空间下的对象权限
grant select any table to scott;

以sysdba身份，撤销scott用户查询所有用户空间下的对象权限
revoke select any table from scott;

scott自已查看自己所拥有的权限
select * from user_sys_privs;

从scott用户空间导航到sysdba用户空间
conn / as sysdba;

从sysdba用户空间导航到scott用户空间
conn scott/tiger;

从scott用户空间导航到hr用户空间
conn hr/lion;

查询hr用户空间中的所有对象
select * from tab;

从hr用户空间导航到scott用户空间
conn scott/tiger;

在scott用户空间下，查询hr用户空间下的jobs表，必须加上hr用户空间名
select * from hr.jobs;

视图

视图是一种基于数据表的一种虚表

（1）视图是一种虚表
（2）视图建立在已有表的基础上, 视图赖以建立的这些表称为基表
（3）向视图提供数据内容的语句为 SELECT 语句,可以将视图理解为存储起来的 SELECT 语句
（4）视图向用户提供基表数据的另一种表现形式
（5）视图没有存储真正的数据，真正的数据还是存储在基表中
（6）程序员虽然操作的是视图，但最终视图还会转成操作基表
（7）一个基表可以有0个或多个视图

为什么要用到视图？

有的时候，我们可能只关系一张数据表中的某些字段，而另外的一些人只关系同一张数据表的某些字段…

那么把全部的字段都都显示给他们看，这是不合理的。我们应该做到：他们想看到什么样的数据，我们就给他们什么样的数据…一方面就能够让他们只关注自己的数据，另一方面，我们也保证数据表一些保密的数据不会泄露出来…

这里写图片描述

还有另外一个原因：

我们在查询数据的时候，常常需要编写非常长的SQL语句，几乎每次都要写很长很长….上面已经说了，视图就是基于查询的一种虚表，也就是说，视图可以将查询出来的数据进行封装。。。那么我们在使用的时候就会变得非常方便…

小总结：

（1）如果你不想让用户看到所有数据（字段，记录），只想让用户看到某些的数据时，此时可以使用视图
（2）当你需要减化SQL查询语句的编写时，可以使用视图，但不提高查询效率

练习



基于emp表所有列，创建视图emp_view_1，create view 视图名 as select对一张或多张基表的查询
create view emp_view_1
as
select * from emp;

默认情况下，普通用户无权创建视图，得让sysdba为你分配creare view的权限 

以sysdba身份，授权scott用户create view权限
grant create view to scott;

以sysdba身份，撤销scott用户create view权限
revoke create view from scott;

基于emp表指定列，创建视图emp_view_2，该视图包含编号/姓名/工资/年薪/年收入（查询中使用列别名）
create view emp_view_2
as
select empno "编号",ename "姓名",sal "工资",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;

基于emp表指定列，创建视图emp_view_3(a,b,c,d,e)，包含编号/姓名/工资/年薪/年收入（视图中使用列名）
create view emp_view_3(a,b,c,d,e)
as
select empno "编号",ename "姓名",sal "工资",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;

查询emp_view_3创建视图的结构
desc emp_view_3;

修改emp_view_3(id,name,salary,annual,income)视图，create or replace view 视图名 as 子查询
create or replace view emp_view_3(id,name,salary,annual,income)
as
select empno "编号",ename "姓名",sal "工资",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;

查询emp表，求出各部门的最低工资，最高工资，平均工资
select min(sal),max(sal),round(avg(sal),0),deptno
from emp
group by deptno;

创建视图emp_view_4，视图中包含各部门的最低工资，最高工资，平均工资
create or replace view emp_view_4
as
select deptno "部门号",min(sal) "最低工资",max(sal) "最高工资",round(avg(sal),0) "平均工资"
from emp
group by deptno;

创建视图emp_view_5，视图中包含员工编号，姓名，工资，部门名，工资等级
create or replace view emp_view_5
as
select e.empno "编号",e.ename "姓名",e.sal "工资",d.dname "部门名",s.grade "工资等级"
from emp e,dept d,salgrade s
where (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal);

删除视图emp_view_1中的7788号员工的记录，使用delete操作，会影响基表吗
delete from emp_view_1 where empno=7788;写法正确，会影响基表

修改emp_view_1为只读视图【with read only】，再执行上述delete操作，还行吗？
create or replace view emp_view_1
as
select * from emp
with read only;
不能进行delete操作了

删除视图中的【某条】记录会影响基表吗？
会影响基表

将【整个】视图删除，会影响表吗？
不会影响基表

删除视图，会进入回收站吗？
不会进入回收站

删除基表会影响视图吗？
会影响视图

闪回基表后,视图有影响吗？
视图又可以正常工作了

同义词

我们在使用多表查询，或者查询出来的表字段意义不清晰的时候，我们就使用别名来替代….当然了，别名只针对列名或表名

现在，我们已经知道的对象有用户/视图/表等等其他对象了，Oracle也提供了同义词【类似于别名】给我们进行使用

同义词的作用

（1）缩短对象名字的长度
（2）方便访问其它用户的对象

练习：

创建与salgrade表对应的同义词，create synonym 同义词 for 表名/视图/其它对象
create synonym e for salgrade;
create synonym ev5 for emp_view_5;

以sys身份授予scott普通用户create synonym权限
grant create synonym to scott;

以sys身份从scott普通用户撤销create synonym权限
revoke create synonym from scott;

使用同义词操作salgrade表
select * from s;

删除同义词
drop synonym ev5;

删除同义词，会影响基表吗？
不会影响基表

删除基表，会影响同义词吗？
会影响同义词

序列

Mysql的自动增长可以直接在创建表的时候，在字段后面跟上auto increament关键字就行了。那Oracle 有没有自动增长策略呢？？？

Oracle使用的是序列这么一个对象….

（1）类似于MySQL中的auto_increment自动增长机制，但Oracle中无auto_increment机制
（2）是oracle提供的一个产生唯一数值型值的机制
（3）通常用于表的主健值
（4）序列只能保证唯一，不能保证连续
- 声明：oracle中，只有rownum永远保持从1开始，且继续
（5）序列值，可放于内存，取之较快

那oralce中的序列和Mysql中的自动增长有啥区别？？？

Mysql每张表都会维护一个自动增长的程序…
Oralce会把序列存放在内存中，可以供几张表使用…

这里写图片描述

有的同学可能会疑问，我们在分页的时候用到了rownum这么一个伪列，为啥不用它来做自动增长的呢？？？

rownum的值虽然是唯一和连续的，但是不能一直唯一标识该记录…也就是说，一旦该记录删除了，那么rownum的值是会变的。

为什么要用序列

（1）以前我们为主健设置值，需要人工设置值，容易出错
（2）以前每张表的主健值，是独立的，不能共享

练习


为emp表的empno字段，创建序列emp_empno_seq，create sequence 序列名
create sequence emp_empno_seq;

删除序列emp_empno_seq，drop sequence 序列名
drop sequence emp_empno_seq;

查询emp_empno_seq序列的当前值currval和下一个值nextval，第一次使用序列时，必须选用：序列名.nextval
select emp_empno_seq.nextval from dual;
select emp_empno_seq.currval from dual;

使用序列，向emp表插入记录，empno字段使用序列值
insert into emp(empno) values(emp_empno_seq.nextval);
insert into emp(empno) values(emp_empno_seq.nextval);
insert into emp(empno) values(emp_empno_seq.nextval);

修改emp_empno_seq序列的increment by属性为20，默认start with是1，alter sequence 序列名
alter sequence emp_empno_seq
increment by 20;

修改修改emp_empno_seq序列的的increment by属性为5
alter sequence emp_empno_seq
increment by 5;

修改emp_empno_seq序列的start with属性，行吗
alter sequence emp_empno_seq
start with 100;

有了序列后，还能为主健手工设置值吗？
insert into emp(empno) values(9999);
insert into emp(empno) values(7900);

删除表，会影响序列吗？
你无法做insert操作,表真正亡，序列亡

删除序列，会影响表吗？

不会


在hibernate中，如果是访问oracle数据库服务器，那么User.hbm.xml映射文件中关于<id>标签如何配置呢？
<id name="id" column="id">
   <generator class="increment/identity/uuid/【sequence】/【native】"/>
</id>

索引

什么是索引

什么是索引【Index】

（1）是一种快速查询表中内容的机制，类似于新华字典的目录
（2）运用在表中某个/些字段上，但存储时，独立于表之外

为什么要用索引

（1）通过指针加速Oracle服务器的查询速度
（2）通过rowid快速定位数据的方法，减少磁盘I/O
- rowid是oracle中唯一确定每张表不同记录的唯一身份证

索引表把数据变成是有序的….
这里写图片描述

快速定位到硬盘中的数据文件…

这里写图片描述

rowid特点

rowid的特点

（1）位于每个表中，但表面上看不见，例如：desc emp是看不见的
（2）只有在select中，显示写出rowid，方可看见
（3）它与每个表绑定在一起，表亡，该表的rowid亡，二张表rownum可以相同，但rowid必须是唯一的
（4）rowid是18位大小写加数字混杂体，唯一表代该条记录在DBF文件中的位置
（5）rowid可以参与=/like比较时，用”单引号将rowid的值包起来，且区分大小写
（6）rowid是联系表与DBF文件的桥梁

索引特点

索引的特点

（1）索引一旦建立,* Oracle管理系统会对其进行自动维护*, 而且由Oracle管理系统决定何时使用索引
（2）用户不用在查询语句中指定使用哪个索引
（3）在定义primary key或unique约束后系统自动在相应的列上创建索引
（4）用户也能按自己的需求，对指定单个字段或多个字段，添加索引

需要注意的是：Oracle是自动帮我们管理索引的，并且如果我们指定了primary key或者unique约束，系统会自动在对应的列上创建索引..

什么时候【要】创建索引

（1）表经常进行 SELECT 操作
（2）表很大(记录超多)，记录内容分布范围很广
（3）列名经常在 WHERE 子句或连接条件中出现

什么时候【不要】创建索引

（1）表经常进行 INSERT/UPDATE/DELETE 操作
（2）表很小(记录超少)
（3）列名不经常作为连接条件或出现在 WHERE 子句中

练习




为emp表的empno单个字段，创建索引emp_empno_idx，叫单列索引，create index 索引名 on 表名(字段,...)
create index emp_empno_idx
on emp(empno);

为emp表的ename,job多个字段，创建索引emp_ename_job_idx，多列索引/联合索引
create index emp_ename_job 
on emp(ename,job);
如果在where中只出现job不使用索引
如果在where中只出现ename使用索引
我们提倡同时出现ename和job

注意：索引创建后，只有查询表有关，和其它（insert/update/delete）无关,解决速度问题

删除emp_empno_idx和emp_ename_job_idx索引，drop index 索引名
drop index emp_empno_idx;
drop index emp_ename_job_idx;

权限与用户

这里写图片描述

练习



一）用户
Oracle中的用户分为二大类
1）Oracle数据库服务器创建时，由系统自动创建的用户，叫系统用户，如sys。
2）利用系统用户创建的用户，叫普通用户，如scott,hr，c##tiger,zhaojun,...

》用sys登录，查询当前Oracle数据库服务器中已有用户的名字和状态
  username表示登录名
  expired&locked表示帐号过期和锁定
  open表示帐号现在可用
  sqlplus / as sysdba;
  col username for a30;
  col account_status for a30;
  set pagesize 100;
  select username,account_status from dba_users;

  查询Oracle中有哪些用户
  select * from all_users;



二）创建与删除普通用户
可以在Oracle中创建新的普通用户，创建普通用户命令是：create user，在创建普通用户的同时，应该为其分配一个具体的表空间，通常叫users。

》用sys登录，查询Oracle中有哪些可用存储空间，所有普通用户默认为users存储空间
  select * from v$tablespace;

》用sys登录，创建普通用户c##tiger，密码为abc，默认使用users存储空间，即对应硬盘上的一个DBF二进制文件
  sqlplus / as sysdba;
  create user c##tiger identified by abc default tablespace users;

》用sys登录，为c##tiger分配users空间无限制使用，即数据库中DBF文件可以无限增加，一个DBF文件不够，会创建第二个DBF文件
  sqlplus / as sysdba;
  alter user c##tiger quota unlimited on users;

》用c##tiger登录，能进orcl数据库吗？
  sqlplus c##tiger/abc
  进不去orcl数据库

》用sys登录，删除普通用户c##tiger
  sqlplus / as sysdba;
  drop user c##tiger cascade;



三）了解系统用户
sys是Oracle中一个重要的系统用户，sys是Oracle中最高权限用户，其角色为SYSDBA（系统管理员）
sqlplus / as sysdba



四）权限
权限的最终作用于用户。即所有用户在数据库内的操作对象和可执行的动作都是受到限制的。
Oracle中权限分为二大类：
1）系统权限
2）对象权限



五）系统权限
针对数据库中特定操作的许可，例如：让c##tiger能登录到orcl数据库，能在orcl数据库中创建表

》用sys登录，获取系统权限的相关信息，例如：select any table表示针对所有表的select权限
  sqlplus / as sysdba;
  select distinct privilege from dba_sys_privs;

》用sys登录，为c##tiger分配create session与数据库建立会话的权限，即允许该用户登录
  sqlplus / as sysdba;
  grant create session to c##tiger;

》用c##tiger登录，能进orcl数据库吗？
  sqlplus c##tiger/abc
  能进去orcl数据库

》用c##tiger登录，创建一张tiger的表，能创建吗？
  sqlplus c##tiger/abc
  create table tiger(
    name varchar2(20)
  );
  这时c##tiger没有权限创建表

》用sys登录，为c##tiger分配create table权限，即允许创建表
  sqlplus / as sysdba;
  grant create table to c##tiger;

》用c##tiger登录，创建一张tiger的表，能创建吗？
  sqlplus c##tiger/abc
  create table tiger(
    name varchar2(20)
  );
  可以创建c##tiger表

》用sys登录，查询c##tiger所拥有的系统权限
  sqlplus / as sysdba;
  select grantee,privilege from dba_sys_privs where lower(grantee) = 'c##tiger';
  grantee表示普通用户名
  privilege权限名  

》用sys登录，撤销c##tiger的create table权限
  sqlplus / as sysdba;
  revoke create table from c##tiger;



六）对象权限
用户对已有对象的操作权限，包括：
1）select可用于表，视图和序列
2）insert向表或视图中插入新的记录
3）update更新表中数据
4）delete删除表中数据
5）execute函数，过程的执行
6）index为表创建索引
7）references为表创建外健
8）alter修改表或者序列的属性

》用sys登录，查询c##tiger所拥有的对象权限
  sqlplus / as sysdba;
  col grantee for a10;
  col table_name for a10;
  col privilege for a20;
  select grantee,table_name,privilege from dba_tab_privs where lower(grantee) = 'c##tiger';

》用sys登录，为c##tiger分配对tiger表的所有权限，即增删改查操作
  sqlplus / as sysdba;
  grant all on c##tiger.tiger to c##tiger;
  注意：c##tiger表示空间名
        tiger表示该空间下的表名
  C##TIGER   TIGER      FLASHBACK
  C##TIGER   TIGER      DEBUG
  C##TIGER   TIGER      QUERY REWRITE
  C##TIGER   TIGER      ON COMMIT REFRESH
  C##TIGER   TIGER      REFERENCES
  C##TIGER   TIGER      UPDATE
  C##TIGER   TIGER      SELECT
  C##TIGER   TIGER      INSERT
  C##TIGER   TIGER      INDEX
  C##TIGER   TIGER      DELETE
  C##TIGER   TIGER      ALTER

》用c##tiger登录，对tiger表进行增删改查操作
  sqlplus c##tiger/abc;
  insert into tiger(name) values('AA');
  update tiger set name = 'BB';
  delete from tiger where rownum = 1;
  select * from tiger;