Oracle视图,序列,索引,约束

视图

什么是视图

视图(VIEW)也被称作虚表,即虚拟的表,是一组数据的逻辑表示,其本质是对应于一条SELECT语句,结果集被赋予一个名字,即视图名字。
视图本身并不包含任何数据,它只包含映射到基表的一个查询语句,当基表数据发生变化视图数据也随之变化

  1. CREATE[ORREPLACE]VIEW view_name[(alias[, alias…])]
  2. ASsubquery ;
视图创建后,可以像操作表一样操作视图,主要是查询操作。
语法中的Subquery是SELECT查询语句,对应的表被称作基表。
根据视图所对应的子查询种类分为几种类型:

SELECT语句是基于单表建立的,且不包含任何函数运算、表达式或分组函数,叫做简单视图,此时视图是基表的子集;
SELECT语句同样是基于单表,但包含了单行函数、表达式、分组函数或GROUP BY子句,叫做复杂视图;
SELECT语句是基于多个表的,叫做连接视图。

视图的作用

如果需要经常执行某项复杂查询,可以基于这个复杂查询建立视图,此后查询此视图即可, 简化复杂查询
视图本质上就是一条SELECT语句,所以当访问视图时,只能访问到所对应的SELECT语句中涉及到的列,对 基表中的其它列起到安全和保密的作用,可以限制数据访问

创建简单视图(单表)

创建一个简单视图V_EMP_10,来显示部门10中的员工的编码、姓名和薪水:

  1. CREATEVIEW v_emp_10
  2. AS
  3. SELECTempno, ename, sal, deptno
  4. FROM emp
  5. WHEREdeptno = 10;
查看视图结构:

  1. DESCv_emp_10;

对视图进行INSERT操作

视图本身并不包含数据,只是基表数据的逻辑映射。所以当对视图执行DML操作时,实际上是对基表的DML操作。对视图执行DML操作的基本原则:

简单视图能够执行DML操作,下列情况除外:在基表中定义了非空列,但简单视图对应的SELECT语句并没有包含这个非空列,导致这个非空列对视图不可见,这时无法对视图执行INSERT操作;
如果视图定义中包含了函数、表达式、分组语句、DISTINCT关键字或ROWNUM伪列,不允许执行DML操作;
DML操作不能违反基表的约束条件。

 创建具有CHECK OPTION约束的视图

WITH CHECK OPTION短语表示,通过视图所做的修改,必须在视图的可见范围内:

假设INSERT,新增的记录在视图仍可查看
假设UPDATE,修改后的结果必须能通过视图查看到
假设DELETE,只能删除现有视图里能查到的记录

  1. CREATE OR REPLACEVIEW v_emp_10
  2. AS
  3. SELECTempno id,ename name,sal salary, deptno
  4. FROM emp
  5. WHEREdeptno = 10
  6. WITHCHECK OPTION;

创建具有READ ONLY约束的视图

对简单视图进行DML操作是合法的,但是不安全的。如果没有在视图上执行 DML 操作的必要,在建立视图时声明为只读来避免这种情况,保证视图对应的基表数据不会被非法修改。加入READ ONLY约束的视图语法如下:

  1. CREATE OR REPLACEVIEW v_emp_10
  2. AS
  3. SELECTempno, ename, sal, deptno FROM emp
  4. WHEREdeptno = 10
  5. WITHREAD ONLY;

通过查询USER_VIEWS获取相关信息

和视图相关的数据字典有:
USER_OBJECTS
USER_VIEWS
USER_UPDATABLE_COLUMNS

例一:在数据字典USER_OBJECTS中查询所有视图名称:

  1. SELECTobject_name FROM user_objects
  2. WHEREobject_type = 'VIEW';
例二:在数据字典USER_VIEWS中查询指定视图:

  1. SELECT textFROM user_views
  2. WHEREview_name = 'V_EMP_10';
例三:在数据字典USER_UPDATABLE_COLUMNS中查询视图:

  1. SELECTcolumn_name, insertable, updatable, deletable
  2. FROMuser_updatable_columns
  3. WHEREtable_name = 'V_EMP_10';

创建复杂视图(多表关联)

复杂视图指在子查询中包含了表达式、单行函数或分组函数的视图。此时必须为子查询中的表达式或函数定义别名。
例如,创建一个视图V_EMP_SALARY,把职员表的数据按部门分组,获得每个部门的平均薪水、薪水总和、最高薪水和最低薪水:

  1. CREATEVIEW v_emp_salary
  2. AS
  3. SELECTd.dname,avg(e.sal) avg_sal,sum(e.sal) sum_sal,
  4. max(e.sal) max_sal,min(e.sal) min_sal
  5. FROM emp e join dept d
  6. ONe.deptno= d.deptno
  7. GROUPBY d.dname;

序列

什么是序列

序列(SEQUENCE)是一种用来生成唯一数字值的数据库对象。序列的值由Oracle程序按递增或递减顺序自动生成,通常用来自动产生表的主键值,是一种高效率获得唯一键值的途径。

序列是独立的数据库对象,和表是独立的对象,序列并不依附于表。

通常情况下,一个序列为一个表提供主键值,但一个序列也可以为多个表提供主键值。

创建序列


  1. CREATE SEQUENCE [schema.]sequence_name
  2.     [ START WITH i ] [ INCREMENT BY j ]
  3.     [ MAXVALUE m | NOMAXVALUE ]
  4.     [ MINVALUE n | NOMINVALUE ]
  5.     [ CYCLE | NOCYCLE ][ CACHE p | NOCACHE ]
其中:
sequence_name是序列名,将创建在schema方案下
序列的第一个序列值是i,步进是j
如果j是正数,表示递增,如果是负数,表示递减
序列可生成的最大值是m,最小值是n
如果没有设置任何可选参数,序列的第一个值是1,步进是1
CYCLE表示在递增至最大值或递减至最小值之后是否继续生成序列号,默认是NOCYCLE
CACHE用来指定先预取p个数据在缓存中,以提高序列值的生成效率,默认是20

使用序列

创建一个序列,起始数据是100,步进是10:
  1. CREATE SEQUENCE emp_seq
  2. START WITH 100
  3. INCREMENT BY 10;
当序列被创建后,第一个序列值将是100,将要生成的序列号分别是110、120、130等。
序列中有两个伪列:
NEXTVAL:获取序列的下个值
CURRVAL:获取序列的当前值
当序列创建以后,必须先执行一次NEXTVAL,之后才能使用CURRVAL。
获取序列的第一个值,并且使用序列值为EMP表插入新的记录:
  1. SELECT emp_seq.NEXTVAL FROM DUAL;
  2. INSERT INTO emp(empno, ename)
  3. VALUES(emp_seq.NEXTVAL, 'donna');

索引

索引的原理

索引是一种允许直接访问数据表中某一数据行的树型结构,为了提高查询效率而引入,是独立于表的对象,可以存放在与表不同的表空间(TABLESPACE)中。索引记录中存有索引关键字和指向表中数据的指针(地址)。对索引进行的I/O操作比对表进行操作要少很多。
索引一旦被建立就将被Oracle系统自动维护,查询语句中不用指定使用哪个索引,是一种提高查询效率的机制。
ROWID: 伪列,唯一标识一条数据记录,可理解为行地址。
  1. CREATE [UNIQUE] INDEX index_name
  2.         ON table(column[, column…]);
其中:
index_name表示索引名称
table表示表名
column表示列名,可以建立单列索引或复合索引
UNIQUE表示唯一索引
在EMP表的ENAME列上建立索引:
  1. CREATE INDEX idx_emp_ename ON emp(ename);
复合索引也叫多列索引,是基于多个列的索引。如果经常在ORDER BY子句中使用job和salary作为排序依据,可以建立复合索引:
  1. CREATE INDEX idx_emp_job_sal ON emp(job, sal);
当做下面的查询时,会自动应用索引idx_emp_job_sal
  1. SELECT empno, ename, sal, job FROM emp
  2. ORDER BY job, sal;

创建基于函数的索引

如果需要在emp表的ename列上执行大小写无关搜索,可以在此列上建立一个基于UPPER函数的索引:
  1. CREATE INDEX emp_ename_upper_idx
  2. ON emp(UPPER(ename));
当做下面的查询时,会自动应用刚刚建立的索引:
  1. SELECT * FROM emp
  2. WHERE UPPER(ename) = 'KING';

 修改和删除索引

如果经常在索引列上执行DML操作,需要定期重建索引,提高索引的空间利用率,语法如下:
ALTER INDEX index_name REBUILD;
重建索引idx_emp_ename:
ALTER INDEX idx_emp_ename REBUILD;
当一个表上有不合理的索引,会导致操作性能下降,删除索引的语法:
DROP INDEX index_name;

合理使用索引提升查询效率

为提升查询效率,创建和使用索引的原则:
为经常出现在WHERE子句中的列创建索引
为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致
为经常作为表的连接条件的列上创建索引
不要在经常做DML操作的表上建立索引
不要在小表上建立索引
限制表上的索引数目,索引并不是越多越好
删除很少被使用的、不合理的索引

约束

约束概述

约束的作用

约束(CONSTRAINT)的全称是约束条件,也称作完整性约束条件。约束是在数据表上 强制执行的一些数据校验规则,当执行DML操作时,数据必须符合这些规则,如果不符合则无法执行。
约束条件可以 保证表中数据的完整性,保证数据间的商业逻辑

约束的类型

约束条件包括:

  • 非空约束(Not Null),简称NN
  • 唯一性约束(Unique),简称UK
  • 主键约束(Primary Key),简称PK
  • 外键约束(Foreign Key),简称FK
  • 检查约束(Check),简称CK

非空约束

建表时添加非空约束

非空约束用于确保字段值不为空。默认情况下,任何列都允许有空值,但业务逻辑可能会要求某些列不能取空值。当某个字段被设置了非空约束条件,这个字段中必须存在有效值,即:
当执行INSERT操作时,必须提供这个列的数据
当执行UPDATE操作时,不能给这个列的值设置为NULL
建表时添加非空约束:

  1. CREATE TABLE employees (
  2. eid NUMBER(6),
  3. name VARCHAR2(30) NOT NULL,
  4. salary NUMBER(7, 2),
  5. hiredate DATE
  6. CONSTRAINT employees_hiredate_nn NOT NULL
  7. );
可以在建表之后,通过修改表的定义,添加非空约束:
  1. ALTER TABLE employees
  2. MODIFY (eid NUMBER(6) NOT NULL);
如果业务要求取消某列的非空约束,可以采用重建表或者修改表的方式:
  1. ALTER TABLE employees
  2. MODIFY (eid NUMBER(6));

唯一性约束

唯一性(Unique)约束条件用于 保证字段或者字段的组合不出现重复值。当给表的某个列定义了唯一约束条件,该列的值不允许重复,但允许是NULL值
唯一性约束条件可以在建表同时建立,也可以在建表以后再建立。
在建表employees的同时,在eid、email列上创建唯一约束条件,并在建表后在name列上建立一个名为employees_name_uk的唯一约束条件:
  1. DROP TABLE employees ; --将表删掉重新创建
  2. CREATE TABLE employees (
  3. eid NUMBER(6) UNIQUE,
  4. name VARCHAR2(30),
  5. email VARCHAR2(50),
  6. salary NUMBER(7, 2),
  7. hiredate DATE,
  8. CONSTRAINT employees_email_uk UNIQUE(email)
  9. );
在建表之后增加唯一性约束条件:
  1. ALTER TABLE employees
  2. ADD CONSTRAINT employees_name_uk UNIQUE(name);

主键约束

主键(Primary Key)约束条件从功能上看 相当于非空(NOT NULL)且唯一(UNIQUE)的组合。主键字段可以是单字段或多字段组合,即:在主键约束下的单字段或者多字段组合上不允许有空值,也不允许有重复值
主键可以用来在表中唯一的确定一行数据。 一个表上只允许建立一个主键,而其它约束条件则没有明确的个数限制

主键选取的原则

主键应是对系统无意义的数据
永远也不要更新主键,让主键除了唯一标识一行之外,再无其他的用途
主键不应包含动态变化的数据,如时间戳
主键应自动生成,不要人为干预,以免使它带有除了唯一标识一行以外的意义
主键尽量建立在单列上

添加主键约束

在建表时添加主键约束条件:
  1. CREATE TABLE employees2 (
  2. name VARCHAR2(30),
  3. email VARCHAR2(50),
  4. salary NUMBER(7, 2),
  5. hiredate DATE
  6. );
建表后创建主键约束条件,并自定义约束条件名称:
  1. CREATE TABLE employees3 (
  2. eid NUMBER(6),
  3. name VARCHAR2(30),
  4. email VARCHAR2(50),
  5. salary NUMBER(7, 2),
  6. hiredate DATE
  7. );
  8. ALTER TABLE employees3
  9. ADD CONSTRAINT
  10. employees3_eid_pk PRIMARY KEY (eid);

外键约束

外键约束条件定义在 两个表的字段或一个表的两个字段上,用于保证相关两个字段的关系。比如 emp表的deptno列参照dept表的deptno列,则dept称作主表或父表,emp表称作从表或子表
先建表,在建表后建立外键约束条件:
  1. CREATE TABLE employees4 (
  2. eid NUMBER(6),
  3. name VARCHAR2(30),
  4. salary NUMBER(7, 2),
  5. deptno NUMBER(4)
  6. );
  7. ALTER TABLE employees4
  8. ADD CONSTRAINT employees4_deptno_fk
  9. FOREIGN KEY (deptno) REFERENCES dept(deptno);

外键约束对一致性的维护


外键约束条件包括两个方面的数据约束:
从表上定义的外键的列值,必须从主表被参照的列值中选取,或者为NULL;
当主表参照列的值被从表参照时,主表的该行记录不允许被删除。
  1. --成功DML语句:
  2. INSERT INTO employees4(eid, name, deptno)
  3. VALUES(1234,rose tyler’, 40);--成功
  4. INSERT INTO employees4(eid, name, deptno)
  5. VALUES(1235,martha jones’, NULL); --成功
  6. --失败DML语句:
  7. INSERT INTO employees4(eid, name, deptno)
  8. VALUES(1236, 'donna noble', 50);
  9. --失败,不存在部门50
  10. DELETE FROM dept WHERE deptno = 40;
  11. --失败,40被参照,不允许删除

外键约束对性能的降低


如果在一个频繁DML操作的表上建立外键,每次DML操作,都将导致数据库自动对外键所关联的对应表做检查,产生开销,如果已在程序中控制逻辑,这些判断将增加额外负担,可以省去。
另外外键确定了主从表的先后生成关系,有时会影响业务逻辑。

关联不一定需要外键约束

如果业务逻辑要求保证数据完整性,可由程序或触发器控制,不一定需要外键约束。

另外为了简化开发,维护数据时不用考虑外键约束,以及大量数据DML操作时不需考虑外键耗费时间。

检查约束

检查(Check)约束条件用来强制在字段上的 每个值都要满足Check中定义的条件。当 定义了Check约束的列新增或修改数据时,数据必须符合Check约束中定义的条件

添加检查约束

员工的薪水必须大于2000元,增加检查约束:
  1. ALTER TABLE employees4
  2. ADD CONSTRAINT employees4_salary_check
  3. CHECK (salary > 2000);
当插入大于2000的数据,操作成功:
  1. INSERT INTO employees4(eid, name, salary, deptno)
  2. VALUES(1236, 'donna noble', 2500, 40);

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值