视图
什么是视图
· 视图（view）也被称作虚表，即虚拟的表，是一组数据的逻辑表示；
· 视图对应于一条SELECT语句，结果集被赋予一个名字，即视图名字；
· 视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化；类似于一个引用，指向一个具体的对象；
· 创建语法
CREATE [OR REPLACE]VIEW view_name[(alias[,alias...])] AS subquery;· 视图创建后，可以像操作表一样操作视图，主要是查询；
· Subquery是SELECT 查询语句，对应的表被称作基表；
· 根据视图所对应的子查询种类分为集中类型；
- SELECT 语句是基于单表建立的，且不包含任何函数运算；表达式或分组函数，叫做简单视图，此时视图是基表的子表；
- SELECT 语句同样是基于单表，但包含了单行函数，表达式、分组函数或GROUP BY 子句，叫做复杂视图；
- SELECT 语句是基于多个表的，叫做连接视图；
视图的作用
· 如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询此视图即可，简化复杂查询；
· 视图本质上就是一条SELECT 语句，所以当访问视图时，只能访问到所对应的SELECT 语句中涉及到的列，对基表中的其他列起到安全和保密的作用，限制数据访问；
授权创建视图
· 创建视图的语句是CREATE VIEW;
· 用户必须CREATE VIEW 系统权限，才能创建视图，如果没有就会提示：权限不足；
· 管理员可以通过DCL（数据控制语言）语句授予用户创建视图权限；
-- GRANT...TO:DCL命令，授予权限
-- user表示权限赋予给谁
GRANT CREATE VIEW TO user创建简单视图（单表）
-- 创建一个简单视图V_EMP_10来显示部门10中的员工的编码、姓名和薪水
CREATE VIEW v_emp_10
AS
SELECT empno,ename,sal,deptno
FROM emp
WHERE deptno=10;· 可以用OR REPLACE短语修改视图对应的SQL查询语句
CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp
WHERE deptno=10;· 查看视图结构：
DESC v_emp_10;查询视图
· 查询视图和查询表的操作相同
SELECT * FROM v_emp_10· 此时视图的列名，和创建视图时的列名一致，不一定是原列名
SELECT id,name,salary FROM v_emp_10;对视图进行DML（数据操作语言）操作
· 视图本身并不包含数据，只是基表数据的逻辑映射
· 当对视图执行DML操作时，实际上是对基表的DML操作
· 对视图执行DML操作的基本原则：
- 简单视图能够执行DML操作，下列情况除外：在基表中定义了非空列，但简单视图对应的SELECT 语句并没有包含这个非空列，导致这个非空列队视图不可见，这时无法对视图执行INSERT操作；
- DELETE操作时，只能删除现有视图里能查到的记录；
- 如果视图定义中包含了函数、表达式、分组语句、DISTINCT关键字或ROWNUM伪列，不允许执行DML操作；
· 对简单视图执行INSERT操作，成功插入数据到其中
INSERT INTO v_emp_10
VALUES(1234,'DOCTOR',4000,10);· 简单视图可以通过DML操作影响到基表数据；
创建check option约束的视图
CREATE [OR REPLACE] VIEW view_name[(alias[,akias...])] AS subquery
[WITH CHECK OPTION]· WITH CHECK OPTION 短语表示，通过视图所做的修改，必须在视图的可见范围内；
· 假设INSERT，新增的记录在视图仍可查看；
· 假设UODATE，修改后的结果必须能通过视图查看到；
创建复杂视图（多表关联）
· 复杂视图指在子查询包含了表达式、单行函数或分组函数的视图；
· 必须为子查询中的表达式或函数定义别名；
-- 创建一个视图V_EMP_SALARY,把职员表的数据按部门分组，获得每个部门的平均薪水、薪水总和、最高薪水和最低薪水
CREATE VIEW v_emp_salary AS
SELECT d.dname,avg(e.sal) avg_sal,sum(e.sal)sum_sal,max(e.sal)max_sal,min(e.sal)min_sal
FROM emp e join dept d
ON e.deptno = d.deptno
GROUP BY d.dname;删除视图
· 当不再需要视图的定义，可以使用DROP VIEW 语句删除视图
DROP VIEW view_name;· 视图虽然是存放在数据字典中的独立对象，但视图不仅仅是基于表的一个查询定义，所以对视图的删除不会导致基表数据的丢失，不会影响基表数据；
序列
什么是序列
· 序列（SEQUENCE,sequence）是一种用来生成唯一数字值的数据库对象；
· 序列的值是由Oracle程序按递增或递减顺序自动生成，通常用来自动产生表的主键值，是一种高效率的唯一键值的途径；
· 序列是独立的数据库对象，和表是独立的对象，序列并不依附与表；
· 通常情况下，一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键值；
创建序列
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方案下；
· 序列的第一个序列值是i，步进是 j；
· 如果 j 是正数，表示递增，如果是负数，表示递减；
· 序列可生成的最大值是m，最小值是n；
· 如果没有设置任何可选参数，序列的第一个值是1，步进是1；
· CYCLE表示在递增至最大值或递减至最小值之后是否重用序列，若是递减并有最大值，从最大值开始。若是递增并有最小值，从最小值开始。若没有从STATR WITH 指定的值开始，默认是NOCYCLE;
· CACHE用来指定先预取p个数据在缓存中，以提高序列的生成效率，默认是20；
使用序列
-- 创建一个序列，起始数据是100，步进是10
-- 当序列被创建之后，第一个序列值将是100，将要生成的序列号是110,120,130等
CREATE SEQUENCE emp_seq
START WITH 100
INCREMENT BY 10;· 序列中有两个伪列
- NEXTVAL：获取序列的下一个值；
- CURRVAL：获取序列的当前值；
· 当序列创建成功之后，必须先执行一次NEXTVAL，之后才能使用CURRVAL；
-- 获取序列的第一个值，并且使用序列值为EMP表插入新的记录
SELECT emp_seq.NEXTVAL FROM DUAL;
INSERT INTO emp(empno,ename) VALUES(emp_seq.NEXTVAL,'donna');· 在序列的使用过程中，比如执行一条SELECT emp_seq.NEXTVAL FROM DUAL语句，则浪费了一个序列值，会导致表的主键值不连续，而CURRVAL的使用不会导致序列值的递进；
删除序列
DROP SEQUENCE sequence_name;索引
索引的原理
· 对索引进行I/O操作比对表进行操作要少很多；
· 索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用那个索引；
· 索引是一种提高查询效率的机制；
· Oracle B-tree索引的结构如下图：
· ROWID：伪列，唯一标识一条记录，可理解为行地址；
创建索引
· 语法
CREATE [UNIQUE] INDEX index_name ON table(column[,column...]);- index_name 表示索引名称；
- table 表示表名；
- column 表示列名，可以建立单列索引或复合索引；
- UNIQUE 表示唯一索引；
· 举例
-- 在EMP表的ENAME列上建立索引
CREATE INDEX idx_emp_ename ON emp(ename);· 复合索引也叫多列索引，是基于多个列的索引；
· 如果经常在ORDER BY 子句中使用job和sal作为排序依据，可以建立复合索引
CREATE INDEX idx_name_job_sal ON emp(job,sal);· 当执行下面查询时，会自动应用索引idx_emp_job_sal
SELECT empno,ename,sal,job FROM emp ORDER BY job,sal;创建基于函数的索引
· 如果需要在emp表的ename列上执行大小写无关搜索，可以在此列上建立一个基于UPPER函数的索引；
CREATE INDEX emp_ename_upper_idx ON emp(UPPER(ename));· 当做下面的查询时，会自动应用刚刚建立的索引；
SELECT * FROM emp WHERE UPPER(ename) = 'KING';修改和删除索引
· 如果经常在索引列上执行DML操作，需要定期重建索引，提高索引的空间利用率；
ALTER INDEX index_name REBUILD;· 举例：重建索引idx_emp_ename
ALTER INDEX idx_emp_ename REBUILD;· 当一个表上有不合理的索引，会导致操作性能下降，删除索引的语法；
DROP INDEX index_name;· 举例：删除索引idx_emp_ename
DROP INDEX idx_emp_ename;合理使用索引提升效率
· 为经常出现在WHERE子句中的列创建索引；
· 为经常出现在ORDER BY、DISTINCT后面的字段建立索引，如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致；
· 为经常作为表的连接条件的列上创建索引；
· 不要在经常做DML操作的表上建立索引；
· 不要在小表上建立索引；
· 限制表上的索引数目，索引并不是越多越好；
· 删除很少被使用的，不合理的索引；
约束
约束概述
约束的作用
· 约束（CONSTRAINT）的全称是约束条件，也称作完整性约束条件；
· 约束是在数据表上强制执行的一些数据校验规则，当执行DML操作时，数据必须符合这些规则，如果不符合则无法执行；
· 约束条件可以保证表中数据的完整性，保证数据间的商业逻辑；
约束的类型
- 非空约束（Not Null），简称NN;
- 唯一性约束（Unique），简称UK;
- 主键约束（Primary Key），简称PK;
- 外键约束（Foreign Key），简称FK；
- 检查约束（Check），简称CK;
非空约束（Not Null）
建表时添加非空约束
· 非空约束用于确保字段值不为空；
· 默认情况下，任何列都允许有空值，但业务逻辑可能会要求某些列不能去空值；
· 当某个字段被设置了非空约束条件，这个字段中必须存在有效值；即：
- 当执行INSERT操作时，必须提供这个列的数据；
- 当执行UPDATE操作时，不能给这个列的值设置为NULL;
CREATE TABLE emp(
eid NUMBER(6),
name VARCHAR2(30) NOT NULL
)修改表时添加非空约束
· 可以在建表之后，通过修改表的定义，添加非空约束
ALTER TABLE emp MODIFY(eid NUMBER(6) NOT NULL);取消非空约束
· 如果业务要求取消某列的非空约束，可以采用重建表或者修改表的方式
ALTER TABLE emp MODIFY(eid NUMBER(6) null);唯一约束（Unique）
什么是唯一约束
· 唯一性（Unique）约束条件用于保证字段或者字段的组合不出现重复值；
· 当给表的某个列定义了唯一约束条件，该列的值不允许重复，但允许是NULL值；
· 唯一性约束条件可以在建表同事建立，也可以在建表以后在建立；
添加唯一约束
· 在建表是添加唯一约束
CREATE TABLE emp(
eid NUMBER(6) UNIQUE,
name varchar2(30),
CONSTRAINT emp_email_uk UNIQUE(email)
)· 在建表以后增加唯一性约束条件
ALTER TABLE emp ADD CONSTRAINT name UNIQUE(name);主键约束
主键的意义
· 主键（Primary Key）约束条件从功能上看相当于非空（NOT NULL）且唯一（UNIQUE）的组合；
· 主键字段可以是单字段或多字段，即：在主键约束下的单字段或者多字段组合上不允许有空值，也不允许有重复值；
· 主键可以用来在表中唯一的确定一行数据；
· 一个表上只允许建立一个主键，而其他约束条件则没有明确的个数限制；
主键选取原则
· 主键应是对系统无意义的数据；
· 永远也不要更新主键，让主键除了唯一标识一行之外，而无其他的用途；
· 主键不应包含动态变化的数据，如时间戳；
· 主键应自动生成，不要人为干预，以免使它带有除了唯一标识一行以外的意义；
· 主键尽量建立在单列上；
添加主键约束
· 在建表时添加主键约束
CREATE TABLE emp(
eid NUMBER(6) PRIMARY KEY,
name VARCHAR2(30),
hiredate DATE
)· 建表后添加主键约束
ALTER TABLE emp ADD CONSTRAINT emp_eid_pk PRIMARY KEY(eid);外键约束
外键约束的意义
· 外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证相关两个字段的关系
· dept表：主表或父表；
· emp表：从表或子表；
添加外键约束
· 先建表，在建表后建立外键约束条件
CREATE TABLE emp(
eid NUMBER(6),
name VARCHAR2(30),
deptno NUMBER(4)
)
ALTER TABLE emp ADD CONSTRAINT emp_deptno_fk
FOREIGN KEY(deptno) REFERENCES dept(deptno)外键约束对性能的降低
· 如果在一个频繁DML操作的表上建立外键，每次DML操作，都将导致数据库自动对外键所关联的对应表做检查，产生开销，如果已在程序中控制逻辑，这些判断将增加额外负担，可以省去；
· 外键确定了主从表的先后生成关系，有时会影响业务逻辑；
检查约束
什么是检查约束
· 检查（Check）约束条件用来强制在字段上的每个值都要满注Check中定义条件；
· 当定义了Check约束的列新增或者修改数据时，数据必须符合Check约束中定义的条件；
添加检查约束
· 员工的薪水必须大于2000元
ALTER TABLE emp ADD CONSTRAINT emp_salary_check CHECK(salary>2000);· 试图修改职员薪水为1500元，更新会失败
UPDATE emp SET salary=1500 WHERE eid='条件'
411
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
