oracle学习(6)

2023-12-30 13:26:50

其他数据库对象

视图:

常见数据库对象——视图:从表中抽出的逻辑上相关的数据集合。

所以:1. 视图基于表。2. 视图是逻辑概念。3. 视图本身没有数据。

创建视图

创建语法与创建表类似,只需要将table → view即可:

SQL> create view?empincomeview ?

as

select e.empno, e.ename, e.sal, e.sal*12 annualsal, e.sal*12+nvl(comm, 0) income, d.dname

from emp e, dept d

where e.deptno = d.deptno ??

出错提示:权限不足。因为创建视图需要“create view”的权限。默认scott用户没有该种权限。加之!

添加步骤:

1. 使用管理员登陆:sqlplus / as sysdba

2. 给scott用户增加权限: SQL> ?grant create view to scott; ?

3. 执行“/”可成功创建视图empincomeview。 ?

4. 视图的操作和表的操作完全一样。 SQL> ?select * from empincomeview;

视图的优点:

视图的优点 ?

1. 简化复杂查询: 原来分组、多表、子查询等可以用一条select * from xxxview代替。

视图可以看做是表的复杂的SQL一种封装。

2. 限制数据访问: 只看视图的结构和数据是无法清楚视图是怎样得来的。可以限制数据的访问。例如:

银行项目,所谓的各个“表”都是“视图”,并有可能只是“只读视图”

注意:1. 视图不能提高性能 2. 不建议通过视图对表进行修改。

创建视图细节:

使用下面的语法格式创建视图:

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

?? [(alias[, alias]...)]

? AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]];

1. 视图只能创建、删除、替换。(不能修改,修改即替换replace)

如:刚刚创建的empincomeview,其他语句不变,将create一行改写成:

SQL> create or replace view empincomeview 视图不存在则创建、存在则替换。

??as

??????select…… from…..where…..

??????with read only? ??????? 可以将视图设为只读视图。

2. 别名:可以写在子查询select各个列的后面,也可以写在视图的名字后面。

3. with read only ?表示该视图为只读视图。

4. with check option 了解即可, 举例:

SQL> ?create view testview

as

select * from emp where deptno=10

with check option; ??

SQL> insert into testview values(******, 10); 不建议向视图插入,但可以做。向视图插入10号员工。

SQL> insert into testview values(******, 20); 因为创建视图时加了“with check option”,所以失败。

视图中使用DML的规定: ????

一:

当视图定义中包含以下元素之一时不能使用delete:

  1. 组函数
  2. GROUP BY 子句
  3. DISTINCT 关键字
  4. ROWNUM 伪列

二:

当视图定义中包含以下元素之一时不能使用update :

  1. 组函数
  2. GROUP BY子句
  3. DISTINCT 关键字
  4. ROWNUM 伪列
  5. 列的定义为表达式

三:

当视图定义中包含以下元素之一时不能使用insert :

  1. 组函数
  2. GROUP BY 子句
  3. DISTINCT 关键字
  4. ROWNUM 伪列
  5. 列的定义为表达式
  6. 表中非空的列在视图定义中未包括

总结一句话:不通过视图做insert、update、delete操作因为视图提供的目的就是为了简化查询。

删除视图:SQL> drop view testview? 不加“purge”关键字。

    1. 序列:

可以理解成数组:默认,从[1]开始,长度[20] [1, 2, 3, 4, 5, 6, …, 20] 在内存中。

??????????*

由于序列是被保存在内存中,访问内存的速率要高于访问硬盘的速率。所以序列可以提高效率。

???????序列的使用:

1. 初始状态下:指针*指向1前面的位置。欲取出第一个值,应该将*向后移动。每取出一个值指针都向后移。

2. 常常用序列来指定表中的主键。

3. 创建序列:create sequence myseq??来创建一个序列。

创建序列:

CREATE SEQUENCE sequence

???????[INCREMENT BY n]

???????[START WITH n]

???????[{MAXVALUE n?| NOMAXVALUE}]

???????[{MINVALUE n?| NOMINVALUE}]

???????[{CYCLE | NOCYCLE}]

???????[{CACHE n?| NOCACHE}];

NOCACHE表示没有缓存,一次不产生20个,而只产生一个。

???????创建序列

创建序列、表,以备后续测试使用:

SQL> create sequence myseq? 按默认属性创建一个序列。

SQL> create table tableA ????

??(tid number, tname varchar2(20))?? tid作为主键,准备使用序列来向表中插入值。

???????序列的属性:

每个序列都有两个属性 ?

NextVal 必须在CurrVal之前被指定。因为初始状态下,CurrVal指向1前面的位置,无值

对于新创建的序列使用SQL> ?select myseq.currval from dual??? 得到出错。

??但SQL> ?select myseq.nextval from dual??? 可以得到序列的第一值1.

??此时再执行SQL> ?select myseq.currval from dual??????? currval的值也得到1

使用序列给tableA表创建主键tid:

SQL> ?insert into tableA values(myseq.nextval, ‘aaa’)?????????

只有nextval取完会向后移动,使用currval不会移动。

SQL> ?insert into tableA values(myseq.nextval, ‘bbb’)?????????

继续使用nextval向表中添加主键tid

……

SQL> ?insert into tableA values(myseq.nextval, &name)???

可以使用“&”和“/”来指定名字。

SQL> select * from tableA; ?

由于前面测试currval和nextval关系时调用过nextval,所以tableA的tid起始从2开始。

查询序列的属性:SQL> select * from user_sequences; ??user_sequences为数据字典视图。

修改序列:

  1. 必须是序列的拥有者或对序列有?ALTER 权限
  2. 只有将来的序列值会被改变
  3. 改变序列的初始值只能通过删除序列之后重建序列的方法实现

删除序列:SQL> drop sequence myseq; ?

???????使用序列需要注意的问题:

1. 序列是公有对象,所以多张表同时使用序列,会造成主键不连续。 如:[1, 2, 3, 4, 5, …, 20]

tableA: 1 2 4

tableB: 3 5 A、B表有可能主键不连续。

2. 回滚也可能造成主键不连续。 如:多次调用insert操作使用序列创建主键。但是当执行了rollback后再次使用insert借助序列创建主键的时候,nextval不会随着回滚操作回退。

3. 掉电等原因,也可能造成不连续。由于代表序列的数组保存在内存中,断电的时候内存的内容丢失。恢复供电时候,序列直接从21开始。

???????索引:

索引,相当于书的目录,提高数据检索速度。提高效率(视图不可以提高效率)

  1. 一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中
  2. 索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
  3. 索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引. 用户不用在查询语句中指定使用哪个索引
  4. 在删除一个表时, 所有基于该表的索引会自动被删除
  5. 通过指针加速 Oracle 服务器的查询速度

通过快速定位数据的方法,减少磁盘 I/O。

上图中:

  1. emp表中保存数据,其中包含部门号列。有10号部门,有20部门员工

2. 当 select * from emp where deptno=10?的时候。由于10号部门员工不连续,没规律。

为了提高访问速度,可以在数据库中,依照rowid给deptno列建立索引

SQL> create index myindex?on?emp(deptno) ??

这样就建立了“索引表”可以通过rowid保存的行地址快速的找到表中数据。即使表中数据不连续。

3. 建立了索引以后,如果再执行select语句的时候,会先检查表上是否有索引表。如果有,可以通过有规律 的rowid找到不连续的数据。

4. Oracle的数据库中,索引有 B树索引(默认)和 位图索引两种。

5. 使用create index 索引表名 on 表名(列名1, 列名2…);来创建索引表。由数据库自动进行维护。 使用主键查询数据最快速,因为主键本身就是“索引”,所以检索比较快。 ??

索引使用的场景:

以下情况可以创建索引:

  1. 列中数据值分布范围很广
  2. 列经常在 WHERE 子句或连接条件中出现
  3. 表经常被访问而且数据量很大?,访问的数据大概占数据总量的2%到4%

下列情况不要创建索引:

  1. 表很小
  2. 列不经常作为连接条件或出现在WHERE子句中
  3. 查询的数据大于2%到4%
  4. 表经常更新

删除索引:SQL> drop index myindex;

    1. synonym同义词:

就是指表的别名。

如:scott用户想访问hr用户下的表employees。默认是不能访问的。需要hr用户为scott用户授权:

SQL> ?sqplus hr/11 或 conn hr/11(已登录界面, 切换登陆)

SQL> ?grant?select on?employees to scott?? ???hr用户为scott用户开放了employees表的查询权限。

? 这时scott用户就可以使用select语句,来查询hr用户下的employees表的信息了。

SQL> ?select count(*) from hr. employees???(若用户名叫zhangsanfeng则zhangsanfeng.employees)

hr.employees名字过长,为了方便操作,scott用户为它重设别名:

SQL> ?create synonym hremp for hr.employees; ? 为hr.employees创建了同义词。

如有权限限制,那么切换管理员登录,给scott用户添加设置同义词权限

SQL> ?conn / as sysdba

SQL> ?grant create synonym to scott ???

SQL> ?select count(*) from hremp??? 使用同义词进行表查询操作。

?——同义词、视图 等用法在数据保密要求较高的机构使用广泛,如银行机构。好处是既不影响对数据的操作,同时又能保证数据的安全。

OCA

OCP

OCM 认证?

文章来源:https://blog.csdn.net/cat_fish_rain/article/details/135303964
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。