alias 高效率 大于 for cache order by lec 对象 san
一. 视图,序列,索引
视图 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 ON emp(job, sal);
当做下面查询时会自动应用索引 [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. 修改表时添加非空约束ALTER TABLE employees MODIFY (id NUMBER(6) NOT NULL);
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 (
id NUMBER(6) PRIMARY KEY,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE
);
建表后创建主键约束条件,并自定义约束条件名称:
CREATE TABLE employees (
id NUMBER(6),
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) | name | loc |
10 | 研发部 | 北京 |
20 | 销售部 | 上海 |
empno(PK) | name | deptno(FK) |
1001 | 刘心 | 10 |
1002 | 李苏海 | 10 |
先建表,在建表后建立外键约束条件
CREATE TABLE employees (
id NUMBER(6),
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
deptno NUMBER(4)
);
ALTER TABLE employees ADD CONSTRAINT employees_deptno_fk FOREIGN KEY (deptno) REFERENCES dept(deptno);
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. 删除一个约束
ALTER TABLE employees DROP CONSTRAINT employees_salary_check;
查看约束的数据字典SELECT constraint_name,constraint_type FROM user_constraints WHERE table_name = 'EMPLOYEES';
— constraint_type:约束类型
— constraint_name:约束名
SELECT * FROM user_constraints WHERE table_name = 'EMPLOYEES';
Oracle 数据基础 (三)
来源: http://www.bubuko.com/infodetail-2452958.html