Oracle的视图,索引,约束,事务,数据库范式

简介: 🍅程序员小王的博客:程序员小王的博客🍅 欢迎点赞 👍 收藏 ⭐留言 📝🍅 如有编辑错误联系作者,如果有比较好的文章欢迎分享给我,我会取其精华去其糟粕🍅java自学的学习路线:java自学的学习路线

一、视图

视图就是封装了一条复杂查询的语句。


1、语法 1.:CREATE VIEW 视图名称 AS 子查询

  • 范例:建立一个视图,此视图包括了 20 部门的全部员工信息
1. --范例:建立一个视图,此视图包括了 20 部门的全部员工信息
2. create view empdept20 as select * from emp where deptno=20;

视图创建完毕就可以使用视图来查询,查询出来的都是 20 部门的员工

1. --查询视图
2. select * from empdept20;

0.png


2、语法 2:CREATE OR replace VIEW 视图名称 AS 子查询

如果视图已经存在我们可以使用语法 2 来创建视图,这样已有的视图会被覆盖。


--替换视图
create or replace view empde20 as select * from emp where deptno=20; 
select * from empde20;

2.png


3、优点:

视图是给sql查询语句定义名称,方便复用,简化SQL


安全性,对开发人员屏蔽掉表和字段信息.


4、注意:

视图相当于查询语句,不存储实际数据,视图基本不会占用硬盘空间 在效率上没有得到提高


删除视图: drop view 视图名;


二、索引

1、什么是索引?

理解:类似于书前面的目录,用于提高查询效率。索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。


注意:索引里存储数据(索引列数据和rowid),并且按照索引列升序


创建索引:


CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap,创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]                                 --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING]                                  --表示创建和重建索引时允许对表做DML操作,默认情况下不应该使用
[NOLINE]
[NOSORT];                                    --表示创建索引时不进行排序,默认不适用,如果数据已经是按照该索引顺序排列的可以使用

使用: 不需要手动使用, 默认使用索引字段为条件进行查询时,数据库会自动使用索引查找数据


注意:


1. 索引不是创建的越多越好,索引占空间(有数据);增删改数据时,同时需要维护索引中的数据
2. 通常在经常被查询的列上创建索引
3. 主键和唯一约束的列,数据库会自动添加索引.
4. 删除索引:drop index 索引名;


2、单列索引

  • 单列索引是基于单个列所建立的索引,比如:
CREATE index 索引名 on 表名(列名)
  • 案例:给 person 表的 name 建立索引
create index pname_index on person(name);


3、 复合索引

复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引,但是


要求列的组合必须不同,比如:


Create index emp_idx1 on emp(ename,job);
Create index emp_idx1 on emp(job,ename);
  • 案例:给 person 表创建一个 name 和 gender 的索引
create index pname_gender_index on person(name,gender)


4、索引的使用原则:

在大表上建立索引才有意义


在 where 子句后面或者是连接条件上的字段建立索引


表中数据修改频率高时不建议建立索引


5、索引原理

若没有索引,搜索某个记录时(例如查找name='whj')需要搜索所有的记录,因为不能保证只有一个whj,必须全部搜索一遍


若在name上建立索引,oracle会对全表进行一次搜索,将每条记录的name值哪找升序排列,然后构建索引条目(name和rowid),存储到索引段中,查询name为whj时即可直接查找对应地方


创建了索引并不一定就会使用,oracle自动统计表的信息后,决定是否使用索引,表中数据很少时使用全表扫描速度已经很快,没有必要使用索引


三、约束

1、主键约束:primary key

作用:用于唯一标识表中的一行数据,如学号、工号


特点:唯一、非空


语法:字段名 数据类型 primary key


注意:开发时,通常常每一张表都会有一个主键约束


2、唯一约束:unique

作用:标识该字段的值不允许重复。如身份证号、手机号


特点:唯一、可以为空


语法:字段名 数据类型 unique


3、非空约束:not null

作用:标识该字段的必须有内容,不允许为null;如学生姓名


特点:非空、可以重复


语法:字段名 数据类型 not null


4、检查约束:check(表达式)

作用:根据自定义规则限制该字段的内容。例如性别只能是男或女


语法:字段名 数据类型 check(检查约束的表达式)


常见使用场景:


性别只能是 男或女:check( sex in ('男','女')) 或是 
                 check(sex = '男' or sex='女')
手机号码必须是11位:phone char(11) check(phone like '_____')或 
                  phone char(11) check(length(pone)=11)
邮箱必须有@:check (email  like  '%@%')


5、外键约束

作用:标识该字段的值不允许随便输入,必须是另一个表中的主键或唯一键中存在的值。 (标识当前表 [从表] 和另一个表 [主表] 之间的关系)


特点:可以重复、可以是null


语法:字段名 数据类型 references 主表名(主键/唯一键的字段名)


3.png


6、默认约束

默认值:default


作用:标识该字段在不填写任意内容,系统默认分配的内容


语法:字段名 数据类型 defualt 值 约束


注意:给定的默认值的类型必须和该字段的数据类型一致


7、联合约束

联合约束:使用表的任意一个字段无法标识主键或唯一约束时,使用多个键的组合来进行约束


常见联合约束:联合主键、联合唯一约束


  • 语法:
1. 1. 联合主键:primary  key(字段名1 , 字段名2)
2. 2. 联合唯一约束:unique(字段名1 , 字段名2)

(1)创建班级表

--学生表
create table t_class(
  cls_id number(10) primary key,
cls_name varchar2(20) not null
  );

(2)创建学生表


学生学号-主键、姓名、邮箱(必须有@)、出生日期(默认当前日期)、身份证号-唯一、所在班级编号

--学生表
--学生学号-主键、姓名、邮箱(必须有@)、出生日期(默认当前日期)、身份证号-唯一、所在班级编号 
create table t_student(
  stu_id number(5) primary key,
  stu_name varchar2(20) not null,
  email varchar2(50) not null check(email like '%@%'),
  birthday date default sysdate,
  card_id char(18) unique,
  cls_id number(5) references t_class(cls_id)
);

4.png


(3)查询用户下所有的表


  • 注意:查询库中所有的表:select * from tabs;
1. --查询数据库中所有的表
2. select * from tabs;


5.png


(4)联合约束案例

6.png

7.png


-- 学生表
create table t_student(
    s_id number(5) primary key,
    s_name varchar2(30) not null
);
-- 课程表
create table t_course(
    c_id number(5) primary key,
    c_name varchar2(30) not null
);
-- 描述:一个学生可以选择多门课程,定义关系表
create table t_sc(
   s_id number(5) references t_student(s_id),
   c_id number(5) references t_course(c_id),
   primary key(s_id,c_id)
);
--  插入数据
create sequence stud_seq
insert into t_student values(stud_seq.nextval,'大宝贝')
select * from t_student
create sequence cour_seq start with 100
insert into t_course values(cour_seq.nextval,'oracle')
select * from t_course
-- 往关系表中插入数据
insert into t_sc values(2,101);
-- 查询学生选课情况
select s.s_id,s_name,c.c_name from t_student s join t_sc sc on s.s_id=sc.s_id join t_course c on sc.c_id=c.c_id
where s.s_name='佳明'


四、事务(transaction)

1、什么是事务?

概念:数据库中最小的执行单元,通常由一条或是多条sql组成,所有sql都执行成功,事务才成功,则提交事务(commit);只要有一条sql执行失败,则事务失败,则事务回滚(rollback)


事务的大小取决于业务

(1) 实际开发时,一个业务对应不同条数的sql,这多条sql的组成被称为事务
    (实现业务需要控制事务)
(2) 案例:
    转账业务:一个账户的钱转向另一个账户
      update account set balance=balance-money where id = 894893
      update account set balance=balance+money where id = 242546
      注意:转账的业务至少2条sql,构成一个事务(2条sql)
    开户: 往account表中添加信息
      insert into .....
      注意:开户的业务1条sql,构成一个事务(1条sql)

事务的边界

(1) 开始:从第一天sql开始执行,事务开始
(2) 结束:
       a. DML语句(insert/delete/update)需要明确指定结束的边界:
          I. 遇到:commit,提交事务,事务成功
          II.遇到: rollback,回滚事务,事务失败
       b. DDL语句:创建表的语句(create table/create sequence),自动提交事务


2、回滚段:

数据库服务器DBServer会为每一个连接上的client,开辟一小块内存空间(称为回滚段),用于暂时存储sql语句的执行结果,所有的sql都执行成功,则提交事务(commit),会把自己回滚的数据真正的写入DB中;如果有一条sql执行失败,则事务回滚(rollback)时,则会清空自己回滚段里的数据,恢复到事务之前的结果。


8.png


3、锁

(1) 事务的底层采用锁的机制保证数据的安全性


(2) 数据库对每条数据分配一个锁,如果一个客户端执行为insert/delete/update操作时,获取当前操作数据锁标记,只要执行到commit或是rollback时,才释放锁标记,其他的客户端才能操作当前条数据


注意:select 查询操作无需考虑事务。


4、事务的特点:(ACID)

(1)事务的原子性(Atomic):事务中的多个sql语句是一个整体, 要么全部成功,要么全部失
                   败.
(2)事务的一致性(Consistency):(数据的合理性),事务执行前后(无论失败还是成功),最
            终数据是合理的.
(3) 事务的隔离型(Isolation):多个事务并发时,事务之间相互独立,互不影响
(4) 事务的持久性(Durability):事务结束(成功或失败),对数据库的数据修改时永久的.


五、数据库范式

1、什么是范式?

简单的讲,范式就是在设计数据库时应当遵循的一些规范,可以在建表时起一些指导性作用。设计数据库表时遵循范式,能够减少冗余数据,使得对数据的管理更为科学。


2、第一范式

第一范式的要点是字段不可再分


案例:对于爱好属性,实际存储的数据可能为:

image.png


比如现在有需求需要修改所有电影爱好为影视,再有需求希望删除所有跳舞爱好,这些操作势必转换为对字符串的拼接、查询操作,非常不方便。


解决方法是将属性再次分割,分离到另外一张表中,变为这样

image.png

image.png


3、第二范式

第二范式是指属性不能部分依赖主属性


例如:

学号    学生姓名  课程编号    课程名称   成绩    
s001      张三    c001      java      90
s001      张三    c002      oracle    80
s002      李四    c002      oracle    60
s003      李四    c001      java      50
s003      王五    c002      oracle    100

如果这样设计一个表示学员选课的成绩表,表中能够起唯一标识的属性称之为主要属性,这张表中学号不能单独作为主属性,同样课程编号也不能单独作为主属性。因此有两个主要属性学号和课程编号。


成绩属性同时依赖于这两个主要属性,而学生姓名和课程名称只能部分依赖于主要属性。称学生姓名和课程名称属性为部分依赖,应当从本表分离出去。


现有表结构存在这样一些问题,例如,添加新学员会存在问题新加学员如果还没有选修课程,那么一些列必须留空;修改学员一样会有问题,张三改名张四,需要修改多条记录…


这些问题分别被成为添加异常、修改异常、删除异常,如果将这些部分依赖的属性分离出去,这些问题都可以避免,如:


学号    学生姓名  
s001      张三    
s002      李四    
s003      王五    
课程编号  课程名称
c001      java      
c002      oracle    
学号    课程编号  成绩    
s001      c001      90
s001      c002      80
s002      c002      60
s003      c001      50
s003      c002      100

首先去除了学员和课程的一些冗余信息,其次,添加修改删除学员也不会产生异常,对于课程也是同样道理。


4、第三范式

第三范式是指属性不能间接依赖于主属性


例如:


empno    ename       deptno    dname
7369     Smith         10      财务部
7499     Scott         10      财务部
7869     King          10      财务部

如果存在如上表结构,dname直接依赖于deptno属性,deptno直接依赖于empno主属性,这种情况称dname间接依赖于empno。


这种依赖带来的问题是,要添加一个新部门,这个部门添加到哪里呢,类似的也存在修改异常与删除异常,解决办法还是将间接依赖于主属性(empno)的属性(dname)从此表分离出去。如下所示:


empno    ename       deptno
7369    Smith          10
7499    Scott          10
7869    King           10
deptno    dname
10      财务部


相关文章
|
6天前
|
存储 Oracle 关系型数据库
Oracle数据库的应用场景有哪些?
【10月更文挑战第15天】Oracle数据库的应用场景有哪些?
109 64
|
11天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
45 3
Mysql(4)—数据库索引
|
3天前
|
存储 Oracle 关系型数据库
数据库数据恢复—Oracle ASM磁盘组故障数据恢复案例
Oracle数据库数据恢复环境&故障: Oracle ASM磁盘组由4块磁盘组成。Oracle ASM磁盘组掉线 ,ASM实例不能mount。 Oracle数据库故障分析&恢复方案: 数据库数据恢复工程师对组成ASM磁盘组的磁盘进行分析。对ASM元数据进行分析发现ASM存储元数据损坏,导致磁盘组无法挂载。
|
5天前
|
监控 Oracle 关系型数据库
Oracle数据库性能优化
【10月更文挑战第16天】Oracle数据库性能优化是
13 1
|
12天前
|
存储 Oracle 关系型数据库
【数据库-Oracle】《Oracle 数据库探秘:基础知识点全攻略》
《Oracle 数据库探秘:基础知识点全攻略》深入介绍 Oracle 数据库的基础知识点,包括数据类型、表结构、查询语句等。通过详细讲解、代码示例和流程图,帮助读者快速掌握 Oracle 数据库的基本操作,为数据库开发和管理打下坚实基础。
20 0
|
13天前
|
关系型数据库 MySQL 数据库
深入浅出MySQL索引优化:提升数据库性能的关键
在这个数据驱动的时代,数据库性能的优劣直接关系到应用的响应速度和用户体验。MySQL作为广泛使用的数据库之一,其索引优化是提升查询性能的关键。本文将带你一探MySQL索引的内部机制,分析索引的类型及其适用场景,并通过实际案例演示如何诊断和优化索引,以实现数据库性能的飞跃。
|
14天前
|
Oracle 关系型数据库 数据库
oracle数据恢复—Oracle数据库文件损坏导致数据库打不开的数据恢复案例
打开oracle数据库时报错,报错信息:“system01.dbf需要更多的恢复来保持一致性,数据库无法打开”。急需恢复zxfg用户下的数据。 出现上述报错的原因有:控制文件损坏、数据文件损坏、数据文件与控制文件的SCN不一致等。数据恢复工程师对数据库文件做进一步检测分析后发现sysaux01.dbf文件有坏块。修复sysaux01.dbf文件,启动数据库依然有许多查询报错。export和data pump工具无法使用,查询告警日志并分析报错,确认发生上述错误的原因就是sysaux01.dbf文件损坏。由于该文件损坏,从数据库层面无法修复数据库。由于system和用户表空间的数据文件是正常的,
|
17天前
|
存储 SQL 数据库
深入理解数据库索引:提升查询性能的关键
数据库索引是优化查询性能的重要工具。本文将带你深入探索索引的内部结构和工作原理,揭示如何通过合理使用索引来加速数据库查询,同时避免常见的索引陷阱。
|
3月前
|
监控 Oracle 关系型数据库
"深度剖析:Oracle SGA大小调整策略——从组件解析到动态优化,打造高效数据库性能"
【8月更文挑战第9天】在Oracle数据库性能优化中,系统全局区(SGA)的大小调整至关重要。SGA作为一组共享内存区域,直接影响数据库处理能力和响应速度。本文通过问答形式介绍SGA调整策略:包括SGA的组成(如数据缓冲区、共享池等),如何根据负载与物理内存确定初始大小,手动调整SGA的方法(如使用`ALTER SYSTEM`命令),以及利用自动内存管理(AMM)特性实现智能调整。调整过程中需注意监控与测试,确保稳定性和性能。
233 2
|
4月前
|
存储 缓存 Oracle
Oracle数据库可扩展性和性能
【7月更文挑战第6天】
80 7

推荐镜像

更多