---其他数据库对象,还有约束的知识。
---数据库对象,第一天有介绍,有表、视图、序列和索引。---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语句而已。)ASSELECT empno,ename,sal,deptnoFROM emp_RRWHERE deptno=10;---查询语句(10号部门的四个信息)--->view V_EMP_RR_10 已创建。---创建表名:在数据库中,所有数据库对象的名字是不可以重复的,表、视图、序列和索引(数据库对象)的名字是不可以重复的。
---例如:创建一张表叫做emp,创建一个视图就不能叫做emp。不光是两张表不能重名,表和视图也不能重名。SELECT * FROM v_emp_RR_10;---等同于(上节课所学的子查询)SELECT * FROM(SELECT empno,ename,sal,deptnoFROM emp_RRWHERE 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_10ASSELECT empno id,ename name,sal salary,deptno---设置列的别名FROM emp_RRWHERE 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_10ASSELECT empno,ename,sal*10,deptnoFROM emp_RRWHERE deptno=10;--->SQL 错误: ORA-00998: 必须使用列别名命名此表达式CREATE VIEW v_emp_RR_10ASSELECT empno,ename,sal*12 sal_year,deptnoFROM emp_RRWHERE deptno=10;SELECT * FROM v_emp_RR_10;
---修改试图就是替换视图中对应的子查询。
CREATE OR REPLACE VIEW v_emp_RR_10ASSELECT empno,ename,sal,job,deptnoFROM emp_RRWHERE 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_10VALUES(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=5000WHERE 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
ASSELECT empno,ename,sal,job,deptno FROM emp_RRWHERE deptno=10WITH 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_10ASSELECT empno id,ename name,sal salary,deptno FROM emp_RRWHERE deptno=10WITH CHECK OPTION;--->view V_EMP_RR_10 已创建。--- DML操作失败,部门20不在视图可见范围内
INSERT INTO v_emp_RR_10VALUES(1008,'donna',5500,20);--->SQL 错误: ORA-00947: 没有足够的值UPDATE v_emp_RR_10 SET deptno=20WHERE id=7782;--->SQL 错误: ORA-00904: "ID": 标识符无效 --- 8.创建具有READ ONLY约束的视图CREATE OR REPLACE VIEW v_emp_RR_10ASSELECT empno,ename,sal,deptno FROM emp_RRWHERE deptno=10WITH 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_10ASSELECT empno,ename,sal,job,deptno FROM emp_RRWHERE deptno=10WITH 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_objectsWHERE 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_objectsWHERE object_type='VIEW';--- 9.3)在数据字典USER_VIEWS中查询指定视图SELECT text FROM user_viewsWHERE view_name='V_EMP_RR_10';/*"SELECT empno,ename,sal,deptno FROM emp_RRWHERE deptno=10WITH 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_salaryASSELECT d.dname,avg(e.sal)avg_sal,sum(e.sal)sum_sal,max(e.sal)max_sal,min(e.sal)min_salFROM emp_RR e join dept_RR dON e.deptno=d.deptnoGROUP BY d.dname;--- 查询复杂视图SELECT * FROM v_emp_RR_salary;--- 复杂视图不允许DML操作 ---复杂视图:---视图对应的子查询中含有函数,表达式,分组,去重,关联查询等操作时,该视图为复杂视图---复杂视图不允许进行DML操作。---你对视图进行操作,视图就对基表进行操作。复杂视图,没有办法反推的。
---假设视图有分组,有统计,视图看的是每个部门的平均工资。此时要插入60号部门,平均工资3500.怎么插入?---做不到。没有办法帮你反推。---创建一个部门工资情况的视图,包含信息:---部门编号,部门名称,平均,最高,最低工资和工资总和CREATE VIEW v_emp_RR_dept_SalInfoASSELECT d.deptno,d.dname, AVG(e.sal),MAX(e.sal),MIN(e.sal),SUM(e.sal)FROM emp_RR e,dept_RR dWHERE e.deptno=d.deptnoGROUP BY d.deptno,d.dname;--->SQL 错误: ORA-00998: 必须使用列别名命名此表达式CREATE VIEW v_emp_RR_SalInfo
ASSELECT d.deptno,d.dname, AVG(e.sal)avg_sal,MAX(e.sal)max_sal,MIN(e.sal)min_sal,SUM(e.sal) sum_salFROM emp_RR e,dept_RR dWHERE e.deptno=d.deptnoGROUP BY d.deptno,d.dname;--->view V_EMP_RR_SALINFO 已创建。SELECT * FROM v_emp_RR_SalInfo;---查看比自己所在部门平均工资高的员工?
SELECT e.ename,e.deptno,e.salFROM emp_RR e,v_emp_RR_SalInfo vWHERE e.deptno=v.deptnoAND 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_seqSTART 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;--->261INSERT INTO emp_RR(empno,ename)VALUES(emp_seq.NEXTVAL,'donna');--->1 行已插入。--->263 why???????????????--- 3.6)查询刚刚生成的记录,主键值将是110
SELECT empno,ename FROM emp_RRWHERE 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_idSTART WITH 1000INCREMENT 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 10ROLLBACK;--->新插入行没了。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操作时,不能给这个列的值设置为NULLCREATE 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_rrADD CONSTRAINT employees4_rr_deptno_tkFOREIGN 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);--失败,不存在部门50DELETE 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_rrADD CONSTRAINT employees4_rr_salary_checkCHECK(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 401235 martha jones 1236 donna noble 501236 donna noble 2500 401237 daisy 8000 30*/