ORACLE---Unit05： 视图、序列、索引 、 约束

---数据库基础 

---其他数据库对象，还有约束的知识。
---数据库对象，第一天有介绍，有表、视图、序列和索引。
---DDL是帮我们增删改数据库对象的，之前是对表进行操作。

---视图（VIEW）：
---视图在SQL中，所体现出的角色是和表一样的。看着和表一样，但是实际上并不是一张真实存在的表。
---对应的于一条SELECT语句的查询结果集
---相当于把SELECT查询结果集当成一张表看而已，然后再基于它做某些操作。
---多列子查询就当做一张表看待，内视图。Java内部类。（重用子查询）

---视图、序列、索引
---一、视图：
--- 1.什么是视图
--- 1.1)视图（VIEW）也被称作虚表，即虚拟的表，是一组数据的逻辑表示
--- 1.2）视图对应于一条SELECT语句，结果集被赋予一个名字，即视图名字
--- 1.3）视图本身并不包含任何数据，它只包含映射在基表的一个查询语句，当基表数据发生变化，视图数据也随之变化

--- 1.4）GREATE[OR REPLACE]VIEW view_name[(alias[,alias...])]AS subquery;
--- 1.5)视图创建后，可以像操作表一样操作视图，主要是查询
--- 1.6）Subquery是SELECT查询语句，对应的表被称作基表
--- 1.7）根据视图所对应的子查询子类分为几种类型
--- -SELECT语句是基于单表建立的，且不包含任何函数运算、表达式或分组函数、去重，叫做【简单视图】，此时视图是基表的子集
--- -SELECT语句同样是基于单表，但包含了单行函数、表达式、分组函数或GROUP BY子句，叫做【复杂视图】
--- -SELECT语句是基于多个表的，叫做【连接视图】，属于复杂视图的一种。

--- 2.视图的作用
--- 2.1）如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询此视图即可，简化复杂查询。
--- 2.2）视图本质上就是一条SELECT语句，所以当访问视图时，只能访问到所对应的SELECT语句中涉及到的列，
--- 对基表中其他列起到安全和保密的作用，限制数据访问。（工资保密制度，防君子不防小人）

--- 3.授权创建视图
--- 3.1）创建视图的语句是CREATE VIEW
--- 3.2)用户必须有CREATE VIEW系统权限，才能创建视图
--- （用超级用户，去申明这个权限。工作上可以找DBA去开，或找项目经理找管数据库的这个人。
---- 在家里创键：主机名：localhost、端口号：1521（不变）、SID：输之前装的那个。
--- 用户名：sys（超级用户，刚装好时输入，密码是装数据库是熟的那个口令）
--- 注意：角色：SYSDBA(选)
--- 进去之后---其他用户---创建用户---在家再自己创建个普通用户即可（全部授权）
--- 3.3）如果没有权限，会提示：权限不足
--- 3.4）管理员可以通过DCL语句授权用户创建视图的权限：
--- 3.5）GRANT CREATE VIEW TO tarena;
--- GRANT...TO... :DCL命令，授予权限
--- GREATE VIEW : 授予何种权限
--- tarena：权限赋予给谁

--- 4.创建简单视图（单表）
--- 4.1）创建一个简单视图V_EMP_10来显示部门10中的员工的编码、姓名和薪水
CREATE VIEW v_emp_RR_10
---自定义试图名称(试图本身不是一张真实存在的表，它自己没有结构，它就是对应的一条SQL语句而已。)
AS
SELECT empno,ename,sal,deptno
FROM emp_RR
WHERE deptno=10;
---查询语句（10号部门的四个信息）
--->view V_EMP_RR_10 已创建。

---创建表名：在数据库中，所有数据库对象的名字是不可以重复的，表、视图、序列和索引（数据库对象）的名字是不可以重复的。
---例如：创建一张表叫做emp，创建一个视图就不能叫做emp。不光是两张表不能重名，表和视图也不能重名。
SELECT * FROM v_emp_RR_10;
---等同于(上节课所学的子查询)
SELECT * FROM
(SELECT empno,ename,sal,deptno
FROM emp_RR
WHERE deptno=10);

--- 4.2）查看视图结构：
DESC v_emp_RR_10

/*DESC v_emp_RR_10
名称 空值 类型
------ -- ------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
SAL NUMBER(7,2)
DEPTNO NUMBER(2)
*/

--- 4.3）创建视图时，给列赋予别名
--- 4.4）可以用 OR REPLACE短语修改视图对应的SQL查询语句
CREATE OR REPLACE VIEW v_emp_RR_10
AS
SELECT empno id,ename name,sal salary,deptno
---设置列的别名
FROM emp_RR
WHERE deptno=10;
--->view V_EMP_RR_10 已创建。

---检查视图结构
DESC v_emp_RR_10

---视图
---视图是数据库对象之一
---视图在SQL语句中体现的角色与表一致
---但并非真实存在的表，它是对应一个SELECT语句的查询结果集。
---使用视图的主要目的：
---1.重用子查询，简化SQL复杂度
---2.限制数据访问

---删除视图
DROP VIEW v_emp_RR_10;
--->view V_EMP_RR_10已删除。

---创建视图对应的子查询若含有函数或者表达式，那么该函数或者表达式必须给别名。
CREATE VIEW v_emp_RR_10
AS
SELECT empno,ename,sal*10,deptno
FROM emp_RR
WHERE deptno=10;
--->SQL 错误: ORA-00998: 必须使用列别名命名此表达式
CREATE VIEW v_emp_RR_10
AS
SELECT empno,ename,sal*12 sal_year,deptno
FROM emp_RR
WHERE deptno=10;

SELECT * FROM v_emp_RR_10;

---修改试图就是替换视图中对应的子查询。
CREATE OR REPLACE VIEW v_emp_RR_10
AS
SELECT empno,ename,sal,job,deptno
FROM emp_RR
WHERE deptno=10;
--->SQL 错误: ORA-00955: 名称已由现有对象使用
---CREATE OR REPLACE VIEW 创建或替换
---如果这个视图不存在，就创建；如果存在，就替换。
--->view V_EMP_RR_10 已创建。

--- 5.查询视图
--- 5.1)查询视图和查询表的操作相同：
SELECT * FROM v_emp_RR_10;
--- 5.2)此时视图的别名，和创建视图时的别名一致，不一定是原列名：
SELECT id,name,salary FROM v_emp_RR_10;

--- 6.对视图进行DML操作
--- 6.1)视图本身并不包含数据，只是基表数据的逻辑映射
--- 6.2）当对视图执行DML操作时，实际上是对基表的DML操作（基表：基础表---数据来源的表）
--- 6.3）对视图执行DML操作的基本原则：
--- --简单视图能够执行DML操作，下列情况除外：在基表中定义了非空列，导致这个非空列对视图不可见，这时无法对视图执行INSERT操作
--- --DELETE操作时，只能删除现有视图里能查到的记录
--- --如果视图定义中包含了函数、表达式、分组语句、DISTINCT关键字或ROWNUM伪列，不允许执行DML操作
--- --DML操作不能违反基表的约束条件
--- 6.4）对简单视图执行INSERT操作，成功插入数据到基表中
INSERT INTO v_emp_RR_10
VALUES(1234,'DOCTOR',4000,10);
--->1 行已插入。
--- 6.5)简单视图可以通过DML操作影响到基表查询

---对视图进行DML操作：
---对视图进行DML就是对视图数据来源的基础表进行的操作。
---只能对简单视图进行DML操作，复杂视图不允许执行DML操作。
---并且对简单视图进行DML操作不能违反视图数据来源的基础表的约束条件。
INSERT INTO v_emp_RR_10
(empno,ename,sal,job,deptno)
VALUES
(1001,'JACK',3000,'CLERK',10);
--->1 行已插入。
SELECT * FROM v_emp_RR_10;
SELECT * FROM emp_RR;

UPDATE v_emp_RR_10
SET sal=5000
WHERE empno=1001;
--->1 行已更新。

DELETE FROM v_emp_RR_10
WHERE empno=1001;

DELETE FROM v_emp_RR_10
WHERE empno=1234;

---对视图的不当DML操作会污染基础表数据
---所谓污染，指的是对视图进行DML操作后，视图对基础表做相应DML操作，但是操作后视图对这些数据不可见。

INSERT INTO v_emp_RR_10
(empno,ename,sal,job,deptno)
VALUES
(1001,'JACK',5000,'MANAGER',20);

SELECT * FROM v_emp_RR_10;
--->视图里面看不见。？？？
SELECT * FROM emp_RR;
--->基础表上有记录。（偷渡进去的）

UPDATE v_emp_RR_10 SET deptno=20;
--->一样的。视图里面看不见，基础表的信息有更新，没有10号部门了。
ROLLBACK;

DELETE v_emp_RR_10 WHERE deptno=20;
--->0 行已删除。
---因为视图里面没有20号部门的信息。只有删除是没有数据污染情况的。但是，增加、修改都会有污染。

---为视图添加检查选项，这样做可以避免对视图进行DML操作后对基表数据污染，因为检查选项的意义在于
---视图对进行DML操作后的数据要检查是否可以见，不可见则不允许相关操作。

CREATE OR REPLACE VIEW v_emp_RR_10
AS
SELECT empno,ename,sal,job,deptno FROM emp_RR
WHERE deptno=10
WITH CHECK OPTION;
--->view V_EMP_RR_10 已创建。
SELECT * FROM v_emp_RR_10;

SELECT * FROM emp_RR;

DELETE v_emp_RR_10 WHERE deptno=20;
--->0 行已删除。
---WITH CHECK OPTION:避免表操作对数据进行污染。
---工作中，几乎不会对视图进行DML操作。

 

--- 7.创建具有CHECK OPTION约束的视图
CREATE OR REPLACE VIEW v_emp_RR_10
AS
SELECT empno id,ename name,sal salary,deptno FROM emp_RR
WHERE deptno=10
WITH CHECK OPTION;
--->view V_EMP_RR_10 已创建。

--- DML操作失败，部门20不在视图可见范围内
INSERT INTO v_emp_RR_10
VALUES(1008,'donna',5500,20);
--->SQL 错误: ORA-00947: 没有足够的值
UPDATE v_emp_RR_10 SET deptno=20
WHERE id=7782;
--->SQL 错误: ORA-00904: "ID": 标识符无效

--- 8.创建具有READ ONLY约束的视图
CREATE OR REPLACE VIEW v_emp_RR_10
AS
SELECT empno,ename,sal,deptno FROM emp_RR
WHERE deptno=10
WITH READ ONLY;
--- 对只读视图执行DML操作，失败
INSERT INTO v_emp_RR_10 VALUES(1258,'DONNA',3000,10);
---ERROR 位于第1行
---ORA-01733：此处不允许虚拟列
---或：ORA-42399：无法对只读视图执行DML操作

---为视图添加只读选项后，该视图不允许进行DML操作。
CREATE OR REPLACE VIEW v_emp_RR_10
AS
SELECT empno,ename,sal,job,deptno FROM emp_RR
WHERE deptno=10
WITH READ ONLY;
--->SQL 错误: ORA-42399: 无法对只读视图执行 DML 操作

---数据字典（也是表），只不过这个表里面的数据不是我们增删改，上面的数据是由数据库自行维护。
---可以把这些表当清单来使用。曾经创建的表，记录在数据字典上。

---数据字典
---数据字典也是一堆表，这些表示由数据库自行维护，作为“清单”使用，可以查看如：
---创建过的表，视图等信息。
---USER_OBJECTS:记录用户创建的所有数据库对象

SELECT object_name FROM user_objects;
--->又有表，又有视图。
SELECT object_name,object_type FROM user_objects;

SELECT object_name FROM user_objects
WHERE object_type='VIEW';
--->view写大写。

---查看自己的视图。
SELECT object_name FROM user_objects
WHERE object_type='VIEW'
AND object_name LIKE '%_RR';
--->V_EMP_10_RR
---LIKE：像，模糊查询。
---%:百分号，任意字符。
---RR:自己名字。名字大写。

SELECT * FROM user_objects
WHERE object_type='VIEW'
AND object_name LIKE '%_RR';
--->object_name:V_EMP_10_RR
--->suboject_name:null
--->object_id:171640
--->data_object_id:null
--->object_type:view
--->created:27-8月 -17
--->last_ddl_time:27-8月 -17
--->timestamp:2017-08-27:09:35:11
--->status:valid
--->tempopary:n
--->generated:n
--->secondary:n
--->banespace:1
--->edition_name:null

--- 9.通过查询USER_VIEW获取相关信息
--- 9.1）和视图相关的数据字典：
--- USER_OBJECTS
--- USER_VIEWS
--- USER_UPDATE_COLUMNS
--- 9.2)在数据字典USER_OBJECTS中查询所有视图名称
SELECT object_name FROM user_objects
WHERE object_type='VIEW';
--- 9.3）在数据字典USER_VIEWS中查询指定视图
SELECT text FROM user_views
WHERE view_name='V_EMP_RR_10';
/*
"SELECT empno,ename,sal,deptno FROM emp_RR
WHERE deptno=10
WITH READ ONLY"
*/

---USER_VIEWS:记录用户创建的所有视图
SELECT view_name,text FROM user_views;

---USER_TABLES:记录用户创建的所有表（记住）
SELECT table_name FROM user_tables;

SELECT * FROM user_tables;
--->所有字段。

---记住这几个数据字典，将来工作的时候，需要查询表名，有两种办法：
---1.直接看数据字典；
---2.项目经理，会给你一个ER图，有一个软件可以打开，里面有数据库字段与哪张表有关联关系。

--- 10.创建复杂视图（多表关联）
--- 10.1）复杂视图指在子查询中包含了表达式、单行函数或分组函数的视图
--- 10.2）必须为子查询中的表达式或函数定义别名
--- 创建一个视图V_EMP_SALARY,把职员表的数据按部门分组，获得每个部门的平均薪水、薪水总和、
--- 最高薪水和最低薪水
CREATE VIEW v_emp_RR_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_RR e join dept_RR d
ON e.deptno=d.deptno
GROUP BY d.dname;
--- 查询复杂视图
SELECT * FROM v_emp_RR_salary;
--- 复杂视图不允许DML操作

---复杂视图：
---视图对应的子查询中含有函数，表达式，分组，去重，关联查询等操作时，该视图为复杂视图
---复杂视图不允许进行DML操作。

---你对视图进行操作，视图就对基表进行操作。复杂视图，没有办法反推的。
---假设视图有分组，有统计，视图看的是每个部门的平均工资。此时要插入60号部门，平均工资3500.怎么插入？
---做不到。没有办法帮你反推。
---创建一个部门工资情况的视图，包含信息：
---部门编号，部门名称，平均，最高，最低工资和工资总和
CREATE VIEW v_emp_RR_dept_SalInfo
AS
SELECT d.deptno,d.dname, AVG(e.sal),MAX(e.sal),MIN(e.sal),SUM(e.sal)
FROM emp_RR e,dept_RR d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,d.dname;
--->SQL 错误: ORA-00998: 必须使用列别名命名此表达式

CREATE VIEW v_emp_RR_SalInfo
AS
SELECT d.deptno,d.dname, AVG(e.sal)avg_sal,MAX(e.sal)max_sal,MIN(e.sal)min_sal,SUM(e.sal) sum_sal
FROM emp_RR e,dept_RR d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,d.dname;
--->view V_EMP_RR_SALINFO 已创建。
SELECT * FROM v_emp_RR_SalInfo;

---查看比自己所在部门平均工资高的员工？
SELECT e.ename,e.deptno,e.sal
FROM emp_RR e,v_emp_RR_SalInfo v
WHERE e.deptno=v.deptno
AND e.sal>v.avg_sal;

--- 11.删除视图
--- 11.1)当不再需要视图的定义，可以使用DROP VIEW 语句删除视图
--- DROP VIEW view_name;
--- 11.2)删除视图v_emp_10:
--- DROP VIEW v_emp_10;
--- 11.3)视图虽然是存放在数据字典中的独立对象，但视图仅仅是基于表的一个查询定义，
--- 所以对视图的删除不会导致基表数据的丢失，不会影响基表数据。

---二、序列：（排号机，ATM排号机，主键就是ID，唯一标识，非空且唯一）
--- 1.什么是序列？
--- 1.1）系列（SEQUENCE）是一种用来生成唯一数字值得数据库对象
--- 1.2）序列的值由Oracle程序按递增或递减顺序自动生成，通常用来自动产生表的主键值，
--- 是一种高效率获得唯一键值的途径
--- 1.3）序列是独立的数据库对象，和表示独立的对象，序列并不依附于表
--- 1.4）通常情况下，一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键值

--- 2.创建序列
CREATE SEQUENCE [schema.] sequence_name
[START WITH i][INCREMENT BY j]
[MAXVALUE m][NOMAXVALUE]
[MINVALUE n][NOMINVALUE]
[CYCLE|NOCYCLE][CACHE p|NOCACHE]

--- 2.1）sequence_name是序列名，将创建在schema方案下
--- 2.2）序列的第一个序列值是i，步进是j
--- 2.3）如果j是整数，表示递增，如果是负数，表示递减

--- 2.4）序列可生成的最大值是m，最小值是n
--- 2.5）如果没有设置任何可选参数，序列的第一个值是1，步进是1
--- 2.6）CYCLE表示在递增至最大值或递减至最小值之后是否重用序列。若是递减并有最大值，从最大值开始。
--- 若是递减并有最小值，从最小值开始。若没有从START WITH指定的值开始。默认是NOCYCLE
--- 2.7)CACHE用来指定先预取p个数据在缓存中，以提高序列值的生成效率，默认是20
--- 序列 缓存：
--- cache默认情况下，就是有的，默认是20。也有数据字典，user_sequence，去看你创建的所有序列。
--- 当你调序列下一个数NEXTVAL,如果没有缓存的话...断裂(服务器重启维护)...断电内存的数据就没有了。开机的时候，序列就直接生成20...
--- 断裂：断电、回退事务、两张表使用同一个序列。
--- SELECT MAX(id)+1 FROM emp_RR;

--- 删除序列
DROP SEQUENCE seq_emp_RR_id;
--->sequence SEQ_EMP_RR_ID已删除。

 

--- 3.使用序列
--- 3.1）创建一个序列，起始数据是100，步进是10
--- 3.2）当序列被创建后，第一个序列值将是100，将要生成的序列号分别是110、120、130等
CREATE SEQUENCE emp_RR_seq
START WITH 100
INCREMENT BY 10;
--->sequence EMP_RR_SEQ 已创建。
SELECT * FROM EMP_RR_SEQ ;
---ORA-02201: 此处不允许序列

--- 3.3）序列中有两个伪列
--- --NEXTVAL:获取序列的下个值
--- --CURRVAL:获取序列的当前值
--- 3.4）当序列创建以后，必须先执行一次NEXTVAL,之后才能使用CURRVAL
--- 3.5)获取序列的第一个值，并且使用序列值为EMP表插入新的记录。
SELECT emp_seq.NEXTVAL FROM DUAL;
--->261
INSERT INTO emp_RR(empno,ename)
VALUES(emp_seq.NEXTVAL,'donna');
--->1 行已插入。
--->263 why???????????????

--- 3.6）查询刚刚生成的记录，主键值将是110
SELECT empno,ename FROM emp_RR
WHERE ename='DONNA';
--- 3.7)此时查询序列的当前值，会得到110的数字。
SELECT emp_seq.CURRVAL FROM DUAL;
--- 3.8)在序列的使用过程中，比如执行了一条SELECT emp_seq.CURRVAL FROM DUAL语句，
--- 则浪费了一个序列值，会导致表的主键值不连续。而CURRVAL的使用不会导致序列值的递进。

--- 4.删除序列
--- 4.1）删除系列的语法如下：
-- DROP SEQUENCE sequence_name;
--- 4.2)删除系列emp_seq.
--- DROP SEQUENCE emp_seq;

---序列
---序列也是数据库对象之一。序列是用来根据指定的规律生成一系列数字使用的。
---通常使用序列为表的主键字段生成值。
CREATE SEQUENCE seq_emp_RR_id
START WITH 1000
INCREMENT BY 10;
--->sequence SEQ_EMP_RR_ID 已创建。

---序列提供了两个伪列：
---NEXTVAL:获取序列下一个数字（序列最后生成的数字加步长得到）
---CURRVAL：获取序列最后生成的数字
---需要注意！新创建的序列至少使用NEXTVAL生成一个数字以后才可以开始使用CURRVAL.
---NEXTVAL会导致序列发生步进，且序列是不能回退的，不受事务影响。
SELECT seq_emp_RR_id.CURRVAL FROM dual;
--->ORA-08002: 序列 SEQ_EMP_RR_ID.CURRVAL 尚未在此会话中定义
---NEXTVAL会导致序列发生步进，且序列是不能回退的，不受事务影响。
SELECT seq_emp_RR_id.NEXTVAL FROM dual;
--->1000
--->1010
--->1020
--->... ...
SELECT seq_emp_RR_id.CURRVAL FROM dual;
--->1030一直是1030（过号作废）

INSERT INTO emp_RR
(empno,ename,sal,job,deptno)
VALUES
(1001,'JACK',3000,'CLERK',10);
---empno:1001,每次都要记得这个数，对其增加

INSERT INTO emp_RR
(empno,ename,sal,job,deptno)
VALUES
(seq_emp_RR_id.NEXTVAL,'JACK',3000,'CLERK',10);
--->1 行已插入。
SELECT * FROM emp_RR;
--->1040 JACK CLERK 3000 10
ROLLBACK;
--->新插入行没了。INSERT---DML操作是受事务控制的。序列是不受事务控制的。

 

---三、索引：（功能，加快查詢效率的。好马配好鞍）
--- TO BE CONTINUED
--- 1.索引的原理
--- 1.1）索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是独立于表的对象，
--- 可以存放在与表不同的表空间（TABLESPACE）中
--- 1.2）索引记录中存有索引关键字和指向表中数据的指针（地址）
--- 1.3）对索引进行的I/O操作比对表进行操作要少很多
--- 1.4）索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引
--- 1.5）索引是一种提高查询效率的机制
--- 1.6）Oracle B-tree索引的结构
--- 1.7）ROWID:伪列，唯一标识一条数据记录，可理解为行地址
--- 2.创建索引
--- CREATE[UNIQUE]INDEX index_name ON table(column,[,column...]);
--- --index_name:表示索引名称
--- --table：表示表名
--- --column：表示列名，可以建立单列索引或复合索引
--- --UNIQUE：表示唯一索引

--- 在EMP表的ENMAE列上建立索引
--- CREATE INDEX idx_emp_ename ON emp(ename);

---- 复合索引也叫多列索引，是基于多个列的索引
--- 如果经常在ORDER BY子句中使用job和sal作为排序依据，可以建立复合索引：
--- CREATE INDEX idx_emp_job_sal ON emp(job,sal);
--- 当做下面的查询时，会自动应用索引idx_emp_job_sal
--- SELECT empno,ename,sal,job FROM emp ORDER BY job,sal;

--- 3.创建基于函数的索引
--- 3.1)如果需要在emp表的ename列上执行大小写无关搜索，可以在此列上建立一个基于UPPER函数的索引：
--- CREATE INDEX emp_ename_upper_idx ON emp(UPPER(ename));

--- 3.2)当做下面的查询时，会自动应用刚刚建立的索引：
--- SELECT * FROM emp_RR WHERE UPPER(ename)='KING';

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

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

---索引
---索引是数据库对象之一
---索引的意义在于加快查询效率

---索引的实际创建与使用是数据库自行完成的，我们的操作在于是否对表创建索引。
CREATE INDEX idx_emp_RR_ename ON emp_RR(ename);
--->index IDX_EMP_RR_ENAME 已创建。
SELECT * FROM emp_RR WHERE ename='SMITH' ORDER BY ename;
--->7369 SMITH CLERK 7902 17-12月-80 800 20

---LIKE是不会加索引的。模糊查询是不会有索引的
SELECT * FROM emp_RR WHERE ename LIKE 'XXX' ORDER BY ename;
---经常出现在WHERE中，ORDER BY中或DISTINCT中的字段可以建立索引，
---需要注意的是，对于字符串类型字段，在WHERE中若是LIKE判断是不会使用到索引的。

CREATE INDEX idx_emp_RR_job_Sal ON emp_RR(job,sal);

SELECT empno,ename,sal,job FROM emp_RR ORDER BY job,sal;

CREATE INDEX emp_ename_upper_idx ON emp_RR(UPPER(ename));

SELECT * FROM emp_RR WHERE UPPER(ename)='KING';

ALTER INDEX idx_EMP_RR_ename REBUILD;
--->index IDX_EMP_RR_ENAME已变更。
ALTER INDEX idx_emp_ename REBUILD;
--->index IDX_EMP_ENAME已变更。
DROP INDEX idx_emp_ename;
--->index IDX_EMP_ENAME已删除。

---注意：不要在小表上建立索引。例如我们的emp_RR,总共才14条数据，没有必要建立索引。

--- 四、约束
--- 1.约束的概述
--- 1.1）约束的作用
--- 约束（CONSTRAINT）的全称是约束条件，也称作完整性约束条件
--- 约束是在数据表上强制执行的一些数据校验规则，当执行DML操作时，数据必须符合这些规则，如果不符合则无法执行
--- 约束条件可以保证表中数据的完整性，保证数据间的商业逻辑
--- 1.2）约束的类型
--- 约束条件包括：
--- 非空约束（NOT NULL）,简称NN
--- 唯一性约束（Unique）,简称UK
--- 主键约束（Primary Key）,简称PK
--- 外键约束（Foreign Key）,简称FK
--- 检查约束（Check）,简称CK
--- 2.非空约束
--- 2.1）建表时添加非空约束
--- 非空约束用于确保字段不为空
--- 默认情况下，任何列都允许有空值，但业务逻辑可能会要求列不能取控制
--- 当某个字段被设置了非空约束条件，这个字段中必须存在有效值，即：
--- ---当执行INSERT操作时，必须提供这个列的数据
--- ---当执行UPDATE操作时，不能给这个列的值设置为NULL
CREATE TABLE employees_rr(
eid NUMBER(6),
name VARCHAR2(30) NOT NULL,
salary NUMBER(7,2),
hiredate DATE CONSTRAINT employees_rr_hiredate_nn NOT NULL
);
--->table EMPLOYEES_RR 已创建。
DESC employees_rr;
/*
DESC employees_rr
名称 空值 类型
-------- -------- ------------
EID NUMBER(6)
NAME NOT NULL VARCHAR2(30)
SALARY NUMBER(7,2)
HIREDATE NOT NULL DATE
*/

--- 2.2）修改表时添加非空约束
--- 可以在建表之后，通过修改表的定义，添加非空约束：
ALTER TABLE employees_rr MODIFY (eid NUMBER(6) NOT NULL);
--->table EMPLOYEES_RR已变更。
---modify:修改字段的。
DESC employees_rr;
/*
DESC employees_rr
名称 空值 类型
-------- -------- ------------
EID NOT NULL NUMBER(6)
NAME NOT NULL VARCHAR2(30)
SALARY NUMBER(7,2)
HIREDATE NOT NULL DATE
*/

--- 2.3)取消非空约束
--- 如果业务要求取消某列的非空约束，可以采用重建表或者修改表的方式：
ALTER TABLE employees_rr MODIFY(eid NUMBER(6)NULL);
--->table EMPLOYEES_RR已变更。
DESC employees_rr;
/*
DESC employees_rr
名称 空值 类型
-------- -------- ------------
EID NUMBER(6)
NAME NOT NULL VARCHAR2(30)
SALARY NUMBER(7,2)
HIREDATE NOT NULL DATE
*/

--- 3.唯一性约束
--- 3.1)什么是唯一性约束
--- 唯一性（Unique）约束条件用于保证字段或者字段的足额不出现重复值
--- 当给表的某个列定义了唯一约束条件，该列的值不允许重复，但不允许是NULL值
--- 唯一性约束条件可以在建表同时建立，也可以在建表以后再建立
--- 3.2）添加唯一性约束
--- 在建表employees的同时，在eid、email列上创建唯一约束条件，并在建表后在name列上建立一个名为employees_name_uk的唯一约束条件
DROP TABLE employees_rr;
---将表删掉重新创建
CREATE TABLE employees_rr(
eid NUMBER(6)UNIQUE,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE,
CONSTRAINT employees_rr_email_uk UNIQUE(email)
);
--->table EMPLOYEES_RR 已创建。
INSERT INTO employees_rr(eid,name,email)VALUES(1,'ROSE','ROSE@TEDU.CN');
--->1 行已插入。
--->再次执行：
INSERT INTO employees_rr(eid,name,email)VALUES(1,'ROSE','ROSE@TEDU.CN');
--->SQL 错误: ORA-00001: 违反唯一约束条件 (JSD1701.SYS_C0054051)
INSERT INTO employees_rr(eid,name,email)VALUES(2,'ROSE','ROSE2@TEDU.CN');
--->1 行已插入。
INSERT INTO employees_rr(eid,name,email)VALUES(NULL,'ROSE',NULL);
--->1 行已插入。
INSERT INTO employees_rr(eid,name,email)VALUES(NULL,'ROSE',NULL);
--->1 行已插入。
INSERT INTO employees_rr(eid,name,email)VALUES(NULL,'ROSE',NULL);
--->1 行已插入。
SELECT* FROM employees_rr;
/*
1 ROSE ROSE@TEDU.CN
2 ROSE ROSE2@TEDU.CN
ROSE
ROSE
ROSE
*/

---列级：定义列的约束，一般叫做列级约束，定义列的同时，将其加了列。

--- 在建表之后增加唯一性约束条件：
ALTER TABLE employees_rr ADD CONSTRAINT employees_rr_name_uk UNIQUE(name);
--->SQL 错误: ORA-02299: 无法验证 (JSD1701.EMPLOYEES_RR_NAME_UK) - 找到重复关键字
---套路太深！！！
---上面已经违反了重复性的名字，再加上肯定会报错！
DROP TABLE employees_rr;
--->table EMPLOYEES_RR已删除。

---再来做个试验：
INSERT INTO employees_rr(eid,name,email)VALUES(1,'ROSE','ROSE@TEDU.CN');
ALTER TABLE employees_rr ADD CONSTRAINT employees_rr_name_uk UNIQUE(name);
INSERT INTO employees_rr(eid,name,email)VALUES(NULL,'ROSE',NULL);
--->SQL 错误: ORA-00001: 违反唯一约束条件 (JSD1701.EMPLOYEES_RR_NAME_UK)
INSERT INTO employees_rr(eid,name,email)VALUES(NULL,'JACK',NULL);
INSERT INTO employees_rr(eid,name,email)VALUES(2,'RONGR','RONGR@LOVE.COM');
INSERT INTO employees_rr(eid,name,email)VALUES(3,'DIDI','DIDI@LOVE.COM');
SELECT* FROM employees_rr;
/*
1 ROSE ROSE@TEDU.CN
JACK
2 RONGR RONGR@LOVE.COM
3 DIDI DIDI@LOVE.COM
*/
---约束
---唯一性约束
---当表中有某个字段使用了唯一性约束后，该字段的值不允许有重复的，NULL除外。

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

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

--- 4.3）添加主键约束（非空且唯一）
--- 在建表时添加主键约束条件：
CREATE TABLE employees2_rr(
eid NUMBER(6)PRIMARY KEY,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE
);
--->table EMPLOYEES2_RR 已创建。
DESC TABLE employees2_rr;
/*
DESC TABLE employees2_rr
名称 空值 类型
-------- -------- ------------
EID NOT NULL NUMBER(6)
NAME VARCHAR2(30)
EMAIL VARCHAR2(50)
SALARY NUMBER(7,2)
HIREDATE DATE
*/
SELECT * FROM employees2_rr;
INSERT INTO employees2_rr(eid,name,email,salary)VALUES(900913,'XIAORONGR','RONGR@LOVE.COM',8000);
--->1 行已插入。
--->再执行。
--->SQL 错误: ORA-00001: 违反唯一约束条件 (JSD1701.SYS_C0069164)
INSERT INTO employees2_rr(eid,name,email,salary)VALUES(901021,'XIAODIDI','DIDI@LOVE.COM',9000);
--->1 行已插入。
INSERT INTO employees2_rr(eid,name,email,salary)VALUES(901021,'XIAODIDI','DIDI@LOVE.COM',9000);
--->1 行已插入。
INSERT INTO employees2_rr(eid,name,email,salary)VALUES(901018,'XIAODIDI','DIDI@LOVE.COM',9000);
--->1 行已插入。
INSERT INTO employees2_rr(eid,name,email,salary)VALUES(NULL,'XIAODIDI','DIDI@LOVE.COM',9000);
--->SQL 错误: ORA-01400: 无法将 NULL 插入 ("JSD1701"."EMPLOYEES2_RR"."EID")
/*
900913 XIAORONGR RONGR@LOVE.COM 8000
901021 XIAODIDI DIDI@LOVE.COM 9000
901018 XIAODIDI DIDI@LOVE.COM 9000
*/
---主键约束
---主键约束要求非空且唯一

--- 建表后创建主键约束条件，并自定义约束条件名称
CREATE TABLE employees3_rr(
eid NUMBER(6),
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE
);
--->table EMPLOYEES3_RR 已创建。
ALTER TABLE employees3_rr ADD CONSTRAINT employees3_rr_eid_pk PRIMARY KEY(eid);
--->table EMPLOYEES3_RR已变更。
DESC employees3_rr;
/*
DESC employees3_rr
名称 空值 类型
-------- -------- ------------
EID NOT NULL NUMBER(6)
NAME VARCHAR2(30)
EMAIL VARCHAR2(50)
SALARY NUMBER(7,2)
HIREDATE DATE
*/
SELECT * FROM employees3_rr;

---外键约束的要求：
---外键字段存的值，必须是主键里面有的，或者是null。

--- 5.外键约束(99%不用。一个字，疼。光听就行。)
--- 5.1）外键约束的意义
--- 外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证相关两个字段的关系
--- dept表：主表或父表
--- emp表：从表或子表

--- 5.2）添加外键约束
--- 先建表，在建表后建立外键约束条件
CREATE TABLE employees4_rr(
eid NUMBER(6),
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
deptno NUMBER(4)
);
ALTER TABLE employees4_rr
ADD CONSTRAINT employees4_rr_deptno_tk
FOREIGN KEY(deptno)REFERENCES dept(deptno);
--->table EMPLOYEES4_RR 已创建。

--- 5.3）外键约束对一致性的维护
--- 外键约束条件包括两个方面的数据约束：
--- --从表上定义的外键的列值，必须从主表被参照的列值中选取，或者为NULL;
--- --当主表参照列的值被从表参照时，主表的该行记录不允许被删除。
INSERT INTO employees4_rr(eid,name,deptno)VALUES(1234,'rose tyler',40);
--成功
INSERT INTO employees4_rr(eid,name,deptno)VALUES(1235,'martha jones',NULL);
--成功
INSERT INTO employees4_rr(eid,name,deptno)VALUES(1236,'donna noble',50);
--失败，不存在部门50
DELETE FROM dept WHERE deptno=40;
---失败，40被参照，不允许删除

SELECT * FROM employees4_rr;

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

--- 5.5）关联不一定需要外键约束
--- 保证数据完整性可由程序或触发器控制
--- 简化开发，维护数据时不用考虑外键约束
--- 大量数据DML操作时不需考虑外键耗费时间

--- 6.检查约束
--- 6.1）什么是检查约束
--- 检查（Check）约束条件用来强制在字段上的每个值都要满足Check中定义的条件
--- 当定义了Check约束的列新增或修改数据时，数据必须符合Check约束中定义的条件

--- 6.2）添加检查约束
--- 员工的薪水必须大于2000元
ALTER TABLE employees4_rr
ADD CONSTRAINT employees4_rr_salary_check
CHECK(salary>2000);
--->table EMPLOYEES4_RR已变更。

--- 正常插入数据
INSERT INTO employees4_rr(eid,name,salary,deptno) VALUES(1236,'donna noble',2500,40);
--->1 行已插入。
INSERT INTO employees4_rr(eid,name,salary,deptno)VALUES(1237,'daisy',8000,30);
--->1 行已插入。
INSERT INTO employees4_rr(eid,name,salary,deptno)VALUES(1237,'hellen',1000,10);
-->SQL 错误: ORA-02290: 违反检查约束条件 (JSD1701.EMPLOYEES4_RR_SALARY_CHECK)

--- 视图修改职员的薪水为1500元，更新失败
UPDATE employees4_rr SET salary=1500 WHERE eid=1236;
--->SQL 错误: ORA-02290: 违反检查约束条件 (JSD1701.EMPLOYEES4_RR_SALARY_CHECK)

SELECT * FROM employees4_rr
/*
1234 rose tyler 40
1235 martha jones
1236 donna noble 50
1236 donna noble 2500 40
1237 daisy 8000 30
*/