前言
在 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 章节中已经讲解过了, 详情可查看我 JDBC 的博文
再次明确一下: 事务就是让一个不可分割的子操作形成一个整体, 该整体要么全部执行成功, 要么全部执行失败
我们值得注意的是 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;
来源: https://www.cnblogs.com/Java3y/p/8513813.html