oracle数据库从入门到精通之四

时间:2021-10-20 12:36:10

序列
是oracle中较为重要的概念
事务对于ddl是不起作用的
查询,更新,数据表,约束这些个概念要掌握。

在许多数据库之中都会存在一种数据类型--自动增长列,它能够
创建流水号
12c之前并没有提供这样一个自动增长的列,如果想要使用自动增长的列
可以用序列来完成。
序列属于数据库对象的创建过程,属于ddl的分类范畴,
对于序列而言,会在数据字典中保存
select * from user_sequences;
时间戳+序列

如果要想使用序列则可以使用如下两个伪列
nextval:取得序列下一个内容值。每一次调用序列的值都会增长。
currval:取得序列的当前内容,每一次调用不会增长。

#################################

视图
而且查询还和具体的开发要求有关,那么在开发过程中程序员完成的
并不是数据库的所有内容,而更多的是应该考虑到程序的设计结构,可
是每个项目里都会包含复杂查询,那么程序员如何从复杂查询中解脱出来呢?
所以在这种情况下就提出了视图的概念。利用视图可以实现复杂sql语句的封装操作。从实际的开
发来讲,一个优秀的数据库设计人员,除了要给出合理的数据表结构之外,还应该将所有可以使用
到的查询封装为视图,一并交给开发者。
程序任务分隔的一种手段,
数据库设计人员     视图(这就是两类人的接口)    开发人员使用

而开发者可以通过视图简单地查询到所需要的数据

可以利用视图来包装一个复杂的sql查询

如果视图名被占用,理论上应该先删除,而后再建一个新的
由于视力使用频率较高,而且直接与开发有关,
不是直接删除重新创建,而是选择进行视图的替换。
利用新的查询替换掉旧的查询才是标准流程

从实际的开发分工来讲,此部分的操作应该是由数据库开发人员进行的,但是从现实来讲,除了大的开发团队之外,
大部分的中小开发团队都会由开发人员自己来编写。

默认视图是可以直接更改的,
create or replace view myview
    as
select * from emp where deptno=20;
deptno=20是视图数据的存在依据。默认情况下这个依据是可以修改的。

update myview set deptno=30 where empno=7369;
更新视图的创建条件

为了防止上面的情况发生,加入下面子句
with check option子句
create or replace view myview
    as
select * from emp where deptno=20
with check option;

update myview set deptno=30 where empno=7369;
此时不能更新视图的创建条件

update myview set sal=80000 where empno=7369;
但是其它字段可以改,这样也是不合理的

所以在一般创建视图的时候,由于里面映射的是真实数据,那么本质上就创建一个只读视图
with read only子句

create or replace view myview
    as
select * from emp where deptno=20
with read only;
这样就避免了通过视图的临时数据修改数据表真实数据的影响。

理论上从正规的开发项目来讲,一个数据库之中应该包含有很多视图,视图的数据一定会超过表的数量,
但在实际的开发之中,有些团队不使用视图,那就得学好复杂查询了。

############################

同义词(oracle特色)

本质上来讲属于近义词的概念
dual属于sys用户,scott要想访问,
理应是这样的select sysdate from dual;
dual 是sys.dual的一个同一义词,

创建同义词
create [public] synonym 同义词名称 for 模式.表名称

create synonym semp for scott.emp;
此同义词只能被sys用户使用,所以可以将同义词创建为public.这样所有用户都可以访问了。

#########################################

索引

select * from emp where sal>1500;
通过这条语句分析一下数据库在这之中做了什么?

通过sys用户打开追踪器。
set autotrace on;
select * from scott.emp;

执行计划中的
table access full
要进行全表扫描。就属于zhu行扫描,一个个去问,而且最关键的问题emp的数据很大,
可能在第20902条之后就没有相应的数据,但上面的语句继续执行,显然是一种浪费。
所有的数据都要过一遍。性能不可能变快。

那么已经知道了问题,如何解决呢,
第一个想法是:需要知道明确的数据排序。用order by ,但order by 是最后执行的,全表
扫描已经都完了,order by 又有什么意义呢。所以在这种情况下,数据的最好排列是根据
树排列。
二叉树,选一个根节点,比此大的的放在右子树,比此小的放在左子树

所以这个时候就可以进行索引的创建以实现以上的操作结构。
create index emp_sal_ind on scott.emp(sal);

SELECT STATEMENT
 TABLE ACCESS BY INDEX ROWID
  INDEX UNIQUE SCAN

oracle总共有几十种索引

索引查询的关键在于索引树。而如果索引的字段列在不停的变化时,那么这棵树将杀死你。
树的维护操作是需要花时间的,如果数据小,可以在很短的时间内进行数的维护,
默认会在主键约束上自动追加一个索引

在现实开发中又会出现一个问题
1、保证用户的回应速度快,没有延迟
2、能够承受用户大量的更新操作

时间换空间,空间换时间
如果要想查询速度快,那么必须作用索引
如果要想保证更新速度快,那么不能使用索引。
这个时候最好的做法是牺牲实时性,等于有两个库
一个用于查询,一个用于更新

#########################################

用户管理

程序员
关注点在于程序的逻辑结构上,对开发人员而言主要目的就是进行数据的交互。
之所以讲解用户管理部分,主要是为了解释DCL,数据控制语言,grant与revoke.
这样的两个命令必须以用户对象为基础来使用。

管理员
用户权限的维护

流程为:
创建用户    创建后,默认是没有任何权限的。
分配系统权限    登录时需要create session权限。
分配角色
忘记密码
锁定账户
分配对象权限
回收权限

系统权限与对象权限

系统权限
sqlplus sys/123456 as sysdba
create user wo identified by 123456;
grant create session to wo;
还需要其它权限,难道一个一个去授予,这个时候,角色就出来了。
主要使用两个角色:connect,resource
grant connect,resource to wo;
alter user wo identified by miaomiao;
alter user wo password expire;    让用户自己修改自己的密码。
alter user wo account lock;

对象权限
普通用户要访问其它用户的对象,要用模式.对象名的形式来访问,并有权限才行。
sys用户可以访问所有用户的对象。
可以针对于一个对象下的数据表进行访问的定义:有四种权限,增删改查,insert delete update select
grant select,insert on hr.xue to wo;

后来发现wo不需要那么的权限,那么就回收。
revoke select,insert on hr.xue from wo;
revoke connect,resource,create session from wo;

用户相关联的资源太多,所以生产中尽量别考虑删除用户的操作。
drop user wo cascade

#######################################

数据导入导出及备份

导入与导出

exp与imp命令的使用

已经被(数据泵datapump)expdp与impdp和rman代替

这种方法在实际之中,使用不了,多数情况下不用。因为在其导出的过程之中,必须保证其他用户不能更新数据

数据库的冷备份

数据的完整性
在所有的事务都提交了,或回滚到原点,此时数据是完整的
数据库的冷备是常用的方式

数据库的冷备严格来讲称为归档备份,指的是数据库要关闭服务了,所有的事务都要提交了。

由管理员进行操作
备份如下内容:
参数文件
控制文件
重做日志文件
数据文件
记录好这些文件的路径
关闭oracle服务    shutdown immediate ,与超市关门要关很长时间一样,要等客户都走了,才关门。
拷贝出所有的备份文件
启动服务    startup

这种备份是允许关闭计算机的备份。

#######################################

数据库设计范式
与数据操作有关,以及数据库的对象定义。
三个设计范式,只能说是一个思路,但是实际之中不可能完全按照设计范式的要求做。
数据库设计只有一个目标,就是减少多表查询

根据业务来确定,是没有标准,只要能查询快,

第一范式就是(单表设计)

第一设计范式:数据表中每一列的内容不可再分。
现在假设定义

create table user(
    id    number,
    name    varchar2(20),
    contact    varchar2(200)
);
但是这个contact(联系方式)会有很多,可能包含:地址,电话,邮编,email,qq等,所以这个还是
可以再分的,这样的设计就不符合第一范式。

create table user(
    id    number,
    name    varchar2(20),
    address    varchar2(200),
    phone    varchar2(20),
    qq    varchar2(100),
    ...
);
第一范式的核心意义就在于常用的数据类型:number,date,varchar2,clob.

1、对于日期描述坚决不能拆分为:年一个字段,月一个字段,天一个字段。
2、对于姓名字段与国外是不同的,

第二设计范式(多对多设计)

第二设计范式:数据表中不存在非关键字段对任意一候选关键字段的部分函数依赖
对于此概念有两个层次的解释:

部分函数依赖:指的是由一些字段的内容可以推导出另一个字段的内容

同一类型的数据放在同一张表中

用伪代码来实现

此是的确符合第一范式,但此时就会存在问题了,插入数据时,会有重复数据
此设计包含有如下的问题:
主键信息重复或无法确定主键
学生,课程信息重复

此时利用第一范式没有办法解决当前的设计问题
利用第二范式解决,即多对多的,但多对多查询需要多张表一起完成,所以查询复杂的。

第三设计范式(一对多设计)

在实际开发中,这个是首选,用的最多。

三个设计范式只是一个设计之初的思考方式,但是在实际运用中,这三个设计范式一定要打破。
通过这三个肯定可以将数据库设计出来。

#############################################

powerdesigner设计工具
这个工具在所在开发中是必须用的。

主要是进行数据表的设计的,E-R图的设计
这个工具是由sybase公司出品的。

重点关注palette面板之中的表与与关联关系两人工具。

子表拉向父表松开。

生成数据库的脚本,再简单修改一下,就能用了。
general database    生成数据库脚本
general test data    但是不能用

powerdesginger 15.1

数据库设计
先建概念数据模型
再建逻辑数据模型
再建物理数据模型

是针对数据体系结构,信息体系结构,和企业体系结构的行业领先建模和元数据管理解决方案

企业流程建模bpm
数据建模
面向对象建模

ETL

文本
notepad
富文本
rtf,html,word

同类软件比较
powerdesinger,rose,visio