Oracle数据基础(三)

慧9建

　　一. 视图，序列，索引
　　视图
　　1. 什么是视图
　　视图（VIEW）也被称作虚表，是一组数据的逻辑表示
　　视图对应于一条SELECT语句，结果集被赋予一个名字，即视图名字
　　视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基于数据发生变化，视图数据页随之变化
　　CREATE [OR REPLACE] VIEW view_name[alias[,alias…]] AS subquery;
　　视图创建后，可以像操作表一样操作视图，主要是查询
　　SUBQUERY是SELECT查询语句，对应的表被称为基表
　　— SELECT语句是基于单表建立的，且不包含任何函数运算，表达式或分组函数，叫简单视图，这时视图是基表的子集
　　— SELECT语句基于单表，但包含了单行函数，表达式，分组函数或GROUP BY子句，叫做复杂视图
　　— SELECT语句基于多个表，叫做连接视图
　　2. 视图的作用
　　简化复杂查询：若经常需要执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询次视图即可
　　限制数据访问：当访问视图时只能访问到对应的SELECT语句中涉及的列，对基表中其它列起到安全和保密的作用
　　3. 授权创建简单视图（单表）
　　创建视图的语句是：CREATE VIEWER
　　用户必须有CREATE VIEWER系统权限，才能创建视图
　　管理员通过DCL语句授予用户创建视图的权限：GRANT CREATE VIEWER TO user;
　　eg： CREATE VIEWER v_emp_10 AS SELECT empno,name,sal,deptno FROM emp WHERE deptno = 10;
　　4. 查询视图
　　desc v_emp_10;
　　5. 对视图进行INSERT操作
　　视图本身并不包含数据，只是基表数据的逻辑映射
　　当对视图执行DML操作时，实际上是对基表的DML操作
　　对视图执行DML操作的基本原则：
　　— 简单视图能够执行DML操作，下列情况除外：在基表中定义了非空列，但简单视图对应的SELECT语句并没有包含这个非空列，导致这个非空列队视图不可见，这时无法对视图执行INSERT操作
　　— 如果视图定义中包含了函数，表达式，分组语句，DISTINCT关键字或ROWNUM伪列，不允许执行DML操作
　　— DML操作不能违反基表的约束条件
　　— 简单视图可以通过DML操作影响到基表数据
　　— 视图进行DELETE操作时只能删除基表中视图中看得到的数据，不能基表中存在而视图中看不到的数据
　　6. 创建具有CHECK OPTION约束的视图
　　CREATE [OR REPLACE] VIEW view_name[(alias[, alias...])] AS subquery [WITH CHECK OPTION];
　　WITH CHECK OPTION短语表示，通过视图所做的修改，必须在视图的可见范围
　　— 假设INSERT，新增的记录在视图仍可查看
　　— 假设UPDATE，修改后的结果必须能通过视图查看到
　　7. 创建具有READ ONLY约束的视图
　　CREATE [OR REPLACE] VIEW view_name[(alias[, alias...])] AS subquery [WITH READ ONLY];
　　如果没有在视图上执行DML操作的必要，在建立视图时声明为只读来避免这种情况，保证视图对应的基表数据不会被非法修改
　　8. 通过查询user_viewers获取相关信息
　　和视图相关的数据字典：
　　— USER_OBJECTS
　　— USER_VIEWS
　　— USER_UPDATE_COLUMNS  (查看哪些列允许增删改)
　　eg：在数据字典USER_OBJECTS中查询所有视图名称
　　SELECT object_name FROM user_objects WHERE object_type = ‘VIEW’;
　　9. 创建复杂视图（多表关联）
　　复杂视图：在子查询中包含了表达式，单行函数或分组函数的视图
　　必须为子查询中的表达式或函数定义别名
　　复杂视图不允许DML操作（即INSERT，UPDATE，DELETE）
　　10. 删除视图
　　当不再需要视图的定义，可以使用DROP VIEW语句删除视图
　　eg：DROP VIEW view_name;
　　视图虽然时存放在数据字典中的独立对象，但视图仅仅是基于表的一个查询定义，所以对视图的删除不会导致基表数据的丢失，不影响基表数据
　　SELECT sys_guid() FROM DUAL;//可以得到32位的uuid
　　JAVA中：
　　String uuid = UUID.randomUUID().toString();
　　
　　序列
　　1. 什么是序列
　　序列（SEQUENCE）是一种用来生成唯一数字值的数据库对象
　　序列的值由Oracle程序按递增或递减顺序自动生成，通常用来自动生成表的主键值，是一种高效率获得唯一键值的途径
　　序列是独立的数据库对象，和表是对立的对象，序列并不依附于表
　　通常一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键
　　2. 创建序列
　　CREATE SEQUENCE [schema.]sequence_name
　　[START WITH i] [INCREMENT BY j ]
　　[MAXVALUE m | NOMAXVALUE ]
　　[MINVALUE n | NOMINVALUE ]
　　[CYCLE | NOCYCLE ][ CACHE p | NOCACHE]
　　sequence_name是序列名，将创建在schema方案下，schema是用户名，给自己用户创建时可不写
　　序列的第一个序列值是i，步进（即步长）是j
　　如果j是整数，表示递增，如果是负数，表示递减
　　序列可生成的最大值是m，最小值n
　　如果没有设置任何可选参数，序列的第一个值是1，步进是1
　　CYCLE表示在递增至最大值或递减至最小值之后是否重用序列。若是递减并有最大值，从最大值开始。若是递增并有最小值，从最小值开始。若没有从START WITH指定的值开始。默认是NOCYCLE
　　CACHE用来指定先预取p个数据在缓存中，以提高序列值的生成效率，默认是20
　　3. 使用序列
　　eg：序列起始数据：100，步进10，则序列号分别是：100，110，120，130...
　　CREATE SEQUENCE emp_seq START WITH 100 INCREMENT BY 10;
　　序列中有2个伪列：
　　— NEXTVAL：获取序列的下个值
　　— CURRENT：获取序列的当前值
　　当序列创建以后，必须先执行一次NEXTVAL，之后才能使用CURRENT
　　获取序列的第一个值，并使用序列值为EMP表插入新的记录
　　SELECT emp_seq.NEXTVAL FROM DUAL;//第一次执行：100；第二次执行：110
　　INSERT INTO emp(epmno, ename) VALUES(emp_seq.NEXTVAL, ‘DONNA’);
　　4. 删除序列
　　DROP SEQUENCE sequence_name;
　　
　　索引
　　1. 索引的原理
　　索引是一种允许值接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是独立于表的对象，可以存放在与表不同的表空间（TABLESPACE）中
　　索引记录中存有索引关键字和指向表中数据的指针（地址）
　　对索引进行的I/O操作比对表进行操作要少很多
　　索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引
　　索引是一种提高查询效率的机制
　　2. 创建索引
　　CREATE [UNIQUE] INDEX index_name ON table(column[, column…]);
　　— index_name表示索引名称
　　— table表示表明
　　— column表示列名，可以建立单列索引或复合索引
　　— UNIQUE表示唯一索引
　　— 索引指定的列是在查询时WHERE子句中经常出现的列
　　复合索引也叫多列索引，是基于多个列的索引
　　如果经常在ORDER BY子句中使用job，sal作为排序依据，则可以建立复合索引：

　　CREATE INDEX>　　当做下面查询时会自动应用索引[idx_emp_job_sal]
　　SELECT empno, name, sal, job FROM amp ORDER BY job,sal;
　　3. 创建基于函数的索引
　　可以在列上建立一个基于UPPER函数的索引：
　　CREATE INDEX emp_name_upper_idx ON emp(UPPER(name));
　　当做下面查询时会自动应用索引[emp_name_upper_idx]
　　SELECT empno FROM emp WHERE UPPER(ename) = ‘KING'
　　4. 修改和删除索引
　　如果经常在索引列上执行DML操作，需要定期重建索引，提高索引的空间利用率：ALTER INDEX index_name REBUILD;
　　当一个表上有不合理的索引，会导致操作性能下降，删除索引：DROP INDEX index_name;
　　5. 合理使用索引提升查询效率
　　为经常出现在WHERE子句中的列创建索引
　　为经常出现在ORDER BY，DISTINCT后面的字段建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致
　　为经常作为表的连接条件的列上创建索引
　　不要在经常做DML操作的表上建立索引
　　不要在小表上建立索引
　　限制表上的索引数目，索引并不是越多越好
　　删除很少被使用的，不合理的索引
　　二. 约束
　　约束概述
　　1. 约束的作用
　　约束（CONSTRAINT）的全称是约束条件，也称为完整性约束条件
　　约束条件可以保证表中数据的完整性，保证数据间的商业逻辑
　　2. 约束的类型
　　— 非空约束（Not Null），简称NN
　　— 唯一性约束（Unique），简称UK
　　— 主键约束（Primary Key），简称PK
　　— 外键约束（Foreign Key），简称FK
　　— 检查约束（Check），简称CK
　　非空约束
　　1. 建表时添加非空约束
　　列级约束：在创建表指定列的同时声明的约束
　　CREATE TABLE employees(id NUMBER(6),
　　name VARCHAR2(30) NOT NULL,//列级约束1
　　salary NUMBER(7,2),
　　hiredate DATE CONSTRAINT employees_hiredate_nn NOT NULL//列级约束2
　　);
　　NOT NULL只能是列级约束
　　2. 修改表时添加非空约束

　　>　　3. 取消非空约束
　　ALTER TABLE employees MODIFY (id NUMBER(6) null);
　　唯一性约束
　　1. 什么是唯一性约束
　　唯一性（Unique）约束条件用于保证字段或者字段的组合不出现重复值
　　当给表的某个列定义了唯一约束条件，该列的值不允许重复，但允许是NULL
　　2. 添加唯一性约束
　　CREATE TABLE employees(id NUMBER(6)  UNIQUE,//列级约束
　　name VARCHAR2(30),
　　email VARCHAR2(50),
　　salary NUMBER(7,2),
　　hiredate DATE,
　　CONSTRAINT employees_email_uk UNIQUE(email)//表级约束
　　);
　　列级约束是在建表声明某一列的同时指定约束条件，表级约束是所有列都声明完毕后单独加约束，括号里指定约束对象是哪一列。
　　唯一性约束即可以是列级约束也可以是表级约束
　　在建表之后增加唯一性约束条件：ALTER TABLE employees ADD CONSTRAINT employees_name_uk UNIQUE(name);//新增的唯一性约束条件只会对该增加之后的数据起作用
　　主键约束
　　1. 主键的意义
　　主键（Primary Key）约束条件从功能上看相当于非空且唯一的组合
　　主键字段可以是单字段或多字段组合
　　主键可用来确定表中唯一一行数据
　　一个表中只允许建立一个主键，而其它约束条件则没有明确的个数限制
　　2. 主键选取的原则
　　主键应是对系统无意义的数据
　　永远不要更新主键，让主键除了唯一标识一行之外，再无其它用处
　　主键不应包含动态变化的数据，如时间戳
　　主键应自动生成，不要人为干预，以免它带有了唯一标识以外的意义
　　主键尽量建立在单列上
　　3. 添加主键约束
　　在建表时添加主键约束条件：
　　CREATE TABLE employees (

　　>　　name VARCHAR2(30),
　　email VARCHAR2(50),
　　salary NUMBER(7,2),
　　hiredate DATE
　　);
　　建表后创建主键约束条件，并自定义约束条件名称：
　　CREATE TABLE employees (

　　>　　name VARCHAR2(30),
　　email VARCHAR2(50),
　　salary NUMBER(7,2),
　　hiredate DATE
　　);
　　ALTER TABLE employees ADD CONSTRAINT employees ADD CONSTRAINT employees_id_pk PRIMARY KEY (id);
　　外键约束
　　1. 外键约束的意义
　　外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证相关两个字段的关系
　　dept表：主表或父表
　　emp表：从表或子表
deptno（PK）nameloc10研发部北京20销售部上海empno（PK）namedeptno（FK）1001刘心101002李苏海10　　1. 添加外键约束
　　先建表，在建表后建立外键约束条件
　　CREATE TABLE employees (

　　>　　name VARCHAR2(30),
　　email VARCHAR2(50),
　　salary NUMBER(7,2),
　　deptno NUMBER(4)
　　);

　　>　　2. 外键约束对一致性的维护
　　外键约束条件包含两个方面的数据约束：
　　— 从表上定义的外键的列值，必须从主表被参照的列值中选取，或者为NULL
　　— 当主表参照列的值被从表参照时，主表的该行记录不允许被删除
　　3. 外键约束对性能的降低
　　若在一个频繁DML操作的表上建立外键，每次DML操作都将导致数据库自动对外键所关联的对应表做检查，产生开销，如果已在程序中控制逻辑，这些判断将增加额外负担，可以省去
　　外键确定了主从表的先后生成关系，有时会影响业务逻辑
　　4. 关联不一定需要外键约束
　　保证数据完整行可由程序或触发器控制
　　简化开发，维护数据时不用考虑外键约束
　　大量数据DML操作时不需要考虑外键耗费时间
　　检查约束
　　1. 什么是检查约束
　　检查（Check）约束条件用来强制在字段上的每个值都要满足Check中定义的条件
　　当定义了Check约束的列新增或修改数据时，数据必须符合Check约束中定义的条件
　　2. 添加检查约束
　　eg：员工薪水必须大于2000
　　ALTER TABLE employees4 ADD CONSTRAINT employees4_salary_check CHECK (salary > 2000);
　　
　　3. 删除一个约束

　　>　　查看约束的数据字典
　　SELECT constraint_name,constraint_type FROM user_constraints WHERE table_name = ‘EMPLOYEES’;
　　— constraint_type：约束类型
　　— constraint_name：约束名
　　SELECT * FROM user_constraints WHERE table_name = ‘EMPLOYEES’;