ORACLE数据库总结05

 

视图

数据库对象之一

视图在SQL语句中体现的角色与表一致

但是视图不是表，只是对应一条SELECT语句查询结果集。

 

CREATE VIEW v_emp_b_10

AS

SELECT empno,ename,sal,deptno

FROM emp_brown

WHERE deptno = 10;

 

DESC v_emp_b_10;

 

查看视图数据

SELECT * FROM v_emp_b_10;

 

 

视图对应的子查询可以为字段添加别名，

那么视图对应的该字段就以别名命名。

当对应的子查询的某个字段是函数或表达式时

那么该字段必须使用别名。

 

CREATE OR REPLACE VIEW v_emp_brown_10

AS

SELECT empno id,ename name,

       sal salary,deptno

FROM emp_brown

WHERE deptno = 10;

 

desc v_emp_b_10;

 

drop VIEW v_emp_brown_10;

SELECT * FROM v_emp_brown_10;

SELECT * FROM emp_brown;

 

视图根据对应的查询语句不同分为

简单视图:查询单一的一张表，且查询的字段

      不包含函数，表达式，去重，分组等对数据进行加工的操作

复杂视图:与简单视图相反的就是复杂视图

连接视图:查询的数据来自多张表，连接视图算作复杂视图的一种

 

可以对视图进行DML操作，但是仅能对简单视图进行。

对视图进行DML操作就是对视图数据来源的基础表进行的，

所以还不能违反基础表的约束条件   

 

INSERT INTO v_emp_brown_10

(id,name,salary,deptno)

VALUES

(1001,'JACK',5000,10);

 

对视图进行DML操作后，银行的数据

若视图看不到，则会对基表数据污染！

 

UPDATE v_emp_brown_10

SET deptno=20;

 

为视图添加检查选项后可以避免对视图进行DML操作后污染基表

 

CREATE OR REPLACE VIEW v_emp_brown_10

AS

SELECT empno id,ename name,

       sal salary,deptno

FROM emp_brown

WHERE deptno = 10

WITH CHECK OPTION;

 

为视图添加只读选项后，该视图仅能查询不能进行任何的DML操作。

CREATE OR REPLACE VIEW v_emp_brown_10

AS

SELECT empno id,ename name,

       sal salary,deptno

FROM emp_brown

WHERE deptno = 10

WITH READ ONLY;

 

在数据字典USER_OBJECTS中查询所有视图名称：

SELECT object_name FROM user_objects

WHERE object_type = 'VIEW';

 

在数据字典USER_VIEWS中查询指定视图：

SELECT text FROM user_views 

WHERE view_name = 'V_EMP_BROWN_10';

 

SELECT table_name

FROM user_tables;

 

 

复杂视图

创建一个记录每个部门的部门号，部门名称，平均工资，工资总和，

最高工资，最低工资的视图v_emp_dept_sal_info

 

CREATE VIEW v_emp_dept_sal_info_brown

AS

SELECT d.deptno,d.dname,avg(e.sal) avg_sal,sum(e.sal) sum_sal, 

max(e.sal) max_sal,min(e.sal) min_sal 

FROM emp_brown e ,dept_brown d

WHERE e.deptno = d.deptno

GROUP BY d.deptno,d.dname;

 

SELECT * FROM v_emp_dept_sal_info_brown;

 

查看高于自己所在部门平均工资？

SELECT e.ename,e.sal,e.deptno

FROM emp_brown e,(SELECT AVG(sal) avg_sal,deptno

                  FROM emp

                  GROUP BY deptno) t

WHERE e.deptno=t.deptno

AND e.sal>avg_sal

 

简单写法:

SELECT e.ename,e.sal,e.deptno

FROM emp_brown e JOIN v_emp_dept_sal_info_brown v

ON e.deptno=v.deptno

WHERE e.sal>v.avg_sal

 

复杂视图不允许DML操作，会报错。

SELECT * FROM v_emp_dept_sal_info_brown

 

删除视图v_emp_10：

DROP VIEW v_emp_b_10;

 

删除视图不会影响基表数据，但是删除视图中的数据(DML操作)

会影响基表数据。

 

 

序列

数据库对象之一

序列是根据制定的规则生成一系列数字

通常使用序列生成的数字为表的主键

字段提供值使用。、

 

CREATE SEQUENCE seq_emp_brown_id

START WITH 1

INCREMENT BY 1

 

序列提供了两个伪列:

NEXTVAL:获取序列下一个数字

若是刚创建的序列，从START WITH开始获取，

然后每次获取时是根据步长进行计算得到。

NEXTVAL会导致序列步进，且序列不能回退!

 

CURRVAL:获取序列当前值(最后一次使用NEXTVAL生成的的数字)

新创建的序列至少调用一次NEXTVAL后才可以使用CURRVAL

 

SELECT seq_emp_brown_id.NEXTVAL

FROM dual;

 

为EMP表主键字段提供值

INSERT INTO emp_brown

(empno,ename,sal,job,deptno)

VALUES

(seq_emp_b.NEXTVAL,'JACK',3000,'CLARK',20)

 

INSERT INTO emp_brown

(empno,ename,sal,job,deptno)

VALUES

(seq_emp_brown_id.NEXTVAL,'ROSE',8000,'MANAGER',20)

 

select * from emp_brown 

 

删除序列

DROP SEQUENCE sql_emp_id;

 

 

索引

数据库对象之一

索引用于提高查询效率

索引的内建工作对用户是透明的，由数据库自行维护，

我们只需要指定是否添加索引。

索引是为表中字段添加的。当一个字段经常出现在WHERE中作为过滤条件，

或ORDER BY或DISTINCT中时可以为其添加索引提高查询效率

 

CREATE INDEX idx_emp_brown_ename

ON emp_brown(ename)

 

SELECT ename,job,deptno

FROM emp_brown

WHERE ename='SCOTT'

 

多列索引

CREATE INDEX idx_emp_brown_job_sal ON emp(job, sal);

 

SELECT empno, ename, sal, job FROM emp_brown

ORDER BY job, sal;

 

如果需要在emp表的ename列上执行大小写无关搜索，可以在此列上建立一个基于UPPER函数的索引:

CREATE INDEX emp_ename_upper_idx 

ON emp(UPPER(ename));

 

 

修改和删除索引

如果经常在索引列上执行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操作的表上建立索引

不要在小表上建立索引

限制表上的索引数目，索引并不是越多越好

删除很少被使用的、不合理的索引

 

 

约束

NOT NULL

NOT NULL约束是一个列级约束，即:为某个字段

添加该约束时候必须是在定义该字段的同时进行。

表创建后再为某字段添加非空约束时，也要在修改字段的时候进行。

CREATE TABLE employees_brown(

eid NUMBER(6),

name VARCHAR2(30) NOT NULL,

salary NUMBER(7,2),

hiredate DATE CONSTRAINT employees_brown_hiredate_nn NOT NULL);

 

desc employees_brown;

 

修改表时添加非空约束

ALTER TABLE employees_brown

MODIFY(eid NUMBER(6) NOT NULL);

 

取消非空约束

如果业务要求取消某列的非空约束，可以采用重建表或者修改表的方式：

ALTER TABLE employees_brown 

MODIFY (eid NUMBER(6) null);

 

唯一性约束

唯一性约束可以要求某个字段在表中任何

集中的值不能相同，NULL值除外。

CREATE TABLE employees1_brown (

  eid NUMBER(6) UNIQUE,

  name VARCHAR2(30),

  email VARCHAR2(50),

  salary NUMBER(7, 2),

  hiredate DATE,

  CONSTRAINT employees_brown_email_uk UNIQUE(email)

);

 

desc employees1_brown;

 

INSERT INTO employees1_brown

(eid,name,email)

VALUES

(1,'JACK','[e-mail]')

 

SELECT * FROM employees1_brown

delete FROM employees1_brown

 

ALTER TABLE employees1_brown

ADD CONSTRAINT employees1_brown_name_uk UNIQUE(name);

 

 

主键约束

主键约束只能建立在单列上，并且一张表

只能有一个主键约束，主键约束可以保证

该字段非空且唯一

CREATE TABLE employees2_brown (

  eid NUMBER(6) PRIMARY KEY,

  name VARCHAR2(30),

  email VARCHAR2(50),

  salary NUMBER(7, 2),

  hiredate DATE

)

 

INSERT INTO employees2_brown

(eid,name)

VALUES

(01,'JACK');

 

SELECT * FROM employees2_brown;

 

ALTER TABLE employees3_brown

ADD CONSTRAINT employees1_brown_eid_pk PRIMARY KEY(eid);

 

ALTER TABLE employees2_brown

ADD CONSTRAINT employees2_brown_salary_check

CHECK(salary > 2000);

 

INSERT INTO employees2_brown(eid,name,salary)

VALUES(1236,'donna noble',2500);

 

UPDATE employees2_brown SET salary = 1500

WHERE eid = 1236;