Oracle学习——第六讲（视图、序列和索引）

Oracle 通用表

/*

emp 表                             dept  部门表                 salgrade 表

empno 员工编号               deptno  部门编号          grade  工资等级

ename  员工姓名             dname  部门名称          losal    工资范围下限

job       职位                      loc 工作地点                 hisal   工资范围上限

mgr     经理编号

hiredate  入职日期

sal        薪金

comm  奖金

deptno  部门编号

*/

Oracle实例演练

1、视图

--是个命名的查询 用于改变基表数据的显示 通过视图能简化查询；访问方式与表相同。

--视图不存数据,为查询命名

--语法:

--  create  view  视图名   as  子查询

1.1 创建视图

--：创建一个视图v_emp10，通过该视图只能查看10号部门的员工编号，员工姓名，职位。

CREATE VIEW V_emp2

AS

SELECT empno, ename, job

FROM emp

WHERE deptno = 10

--创建一个视图，通过该视图可以查询到工资在

--2000-5000内并且姓名中包含有A的员工编号，姓名，工资。

CREATE VIEW V_emp3

AS

SELECT empno, ename, sal

FROM emp

WHERE sal BETWEEN 2000 AND 5000

AND ename like '%A%';

--创建视图时，在子查询中使用列的别名

CREATE VIEW v_sal

AS

SELECT empno EMPLORYEE_NUMBER, ename ee_NAME, sal SALARY

FROM emp

WHERE deptno = 30;

--创建一个视图，通过该视图可以查看每个部门

--的名称，最低工资，最高工资，平均工资

CREATE VIEW v_dept(deptname, lowsal, highsal, avgsal)

AS

SELECT dname, MIN(sal), MAX(sal), AVG(sal)

FROM emp e, dept d

WHERE e.deptno = d.deptno

GROUP BY dname

--从视图中检索数据

select *

FROM v_dept;

--创建一个视图，通过该视图可以查询到工作在

--NEW YORK和CHICAGO的员工编号，姓名，部门编号，入职日期。

CREATE VIEW v_dept1

AS

SELECT empno ee_number, ename ee_name, d.deptno dd_Number, hiredate ee_date

FROM emp e, dept d

WHERE e.deptno = d.deptno

AND d.loc IN('NEW YORK', 'CHICAGO');

1.2 创建复杂视图

--创建一个视图，通过该视图可以查看每个部门的名称，最低工资，最高工资，平均工资

CREATE VIEW V_emp4(name, minsal, maxsal,  avgsal)

AS

SELECT d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)

FROM emp e, dept d

WHERE e.deptno = d.deptno

GROUP BY d.dname;

1.3 查询视图

SELECT *

FROM V_emp4;

--创建一个视图，通过该视图可以查询到工作在

--NEW YORK和CHICAGO的员工编号，姓名，部门编号，入职日期。

CREATE VIEW V_emp5(loc, empno, ename, deptno, hiredate)

AS

SELECT d.loc, e.empno, e.ename, d.deptno, e.hiredate

FROM emp e, dept d

WHERE e.deptno = d.deptno

AND d.loc IN('NEW YORK', 'CHICAGO');

1.4 修改视图

--CREATE OR REPLACE VIEW子句修改视图

--修改v_emp10，为每个列添加别名

CREATE OR REPLACE VIEW v_emp10(ee_NUMBER, ee_name, ee_job)

AS

SELECT empno, ename, job

FROM emp

WHERE deptno = 10

--备注;CREATE VIEW子句中别名的顺序必须和内部查询中的列的顺序一一对应。

1.5 删除视图

--删除视图并不会删除数据，因为视图是基于数据库中的基表的虚表。

DROP VIEW view;

--创建视图v_emp_20，包含20号部门的员工编号，姓名，年薪列(年薪=12*(工资+奖金）；

CREATE VIEW v_emp_20(ee_number, ee_name, ee_sallary)

AS

SELECT empno, ename, 12 * (sal + NVL(comm, 0))

FROM emp

WHERE deptno = 20

2、序列

--序列是按照一定规则能自动增加/减少数字的一种数据库对象。

--通常可以使用序列自动地生成主键值。

2.1 创建序列

--语法

CREATE SEQUENCE [schema.] sequencename

[INCREMENT BY n]

[START WITH n]

[MAXVALUE n | NOMAXVALUE]

[MINVALUE n | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE n | NOCACHE];

--创建序列test_seq，起始值为10，每次增长2，

--最大值100，最小值9，循环序列，每次缓存10

Create SEQUENCE test_seq

START WITH  10       --序列从10开始

INCREMENT BY 2     --序列每次增加2

MAXVALUE 100       --序列最大值100

MINVALUE 9            --序列最小值为9

CYCLE                       --序列循环，每次增加2，一直到100后，到9从新开始

CACHE 10                 --缓存中序列值为10

--序列属性

--– CURRVAL：表示序列返回的当前值；

--– NEXTVAL：表示序列返回的下一个值；

--CURRVAL在被引用之前，必须先使用NEXTVAL来产生一个序列值；

--可用语句序列名.CURRVAL或序列名.NEXTVAL来访问序列;

--创建student表

CREATE TABLE student1(

id      number(6),

name varchar2(30),

tool  varchar2(20),

num  number(2)

);

--创建序列student_seq

CREATE SEQUENCE student_seq

START WITH 10000

INCREMENT BY 1;

--使用序列student_seq生成student表中sid列插入值：

INSERT INTO student1

VALUES(student_seq.NEXTVAL, 'scott', 'Computer Science', 11);

SELECt * FROM student1

SELECT  student_seq.CURRVAL FROM dual

--创建表

create table cdpt(

id number(6),

name varchar2(30),

constraint pk_id primary key(id)

);

--创建序列

Create sequence seq_cdpt

Increment by 1

Start with 1

Maxvalue 999999

Minvalue 1

Nocycle

nocache

--使用序列seq_cdpt生成cdpt表中的列插入值

insert into cdpt values(seq_cdpt.nextval,'feffefe');

--查看序列seq_cdpt当前值：

select * from cdpt;

select seq_cdpt.CURRVAL from dual;

--创建一个序列，该序列起始值从1开始，无最大值，增量是1，不循环。

CREATE SEQUENCE test_seq1

START WITH 1

INCREMENT BY 1

NOMAXVALUE

NOCYCLE

NOCACHE;

--使用第1题所建的序列，向部门表中插入两条

--记录，部门编号使用序列值，部门名称分别为：

--Education、Market，城市分别为：DALLAS、WASHTON

CREATE TABLE test_table(

deptno number(6),

dname VARCHAR2(20),

dloc    VARCHAR2(20));

INSERT INTO test_table values(test_seq1.nextval, 'Education', 'DALLAS');

INSERT INTO test_table values(test_seq1.nextval, 'Market', 'WASHTON');

--查看表中数据

select * from test_table;

2.2 修改序列

--ALTER SEQUENCE

--修改序列的语法没有START WITH子句。

ALTER SEQUENCE [schema.]sequencename

[INCREMENT BY n]

[MAXVALUE n | NOMAXVALUE]

[MINVALUE n | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE n | NOCACHE];

--修改序列

ALTER SEQUENCE test_seq2

INCREMENT BY 4  --序列每次增加4

MAXVALUE 1000   --序列最大值1000

NOCACHE;           --不设定缓存

2.3 删除序列

--DROP SEQUENCE

--删除序列student_seq

DROP SEQUENCE student_seq

--ROWID

--是一个伪列，系统自动产生。

--ROWID能唯一标示每一条数据库行记录的物理地址，通过ROWID 能快速定位到一条行记录。

--注意：快速定位记录，使用ROWID检索及操作数据，效率最快

3、索引

--INDEX

--是对数据库表中一个或多个列的值进行排序的一种数据库对象。

--在数据库中,通过索引可以加速对表的查询速度;

3.1 创建索引

--1.自动创建: （唯一键和主键）

            --当有PRIMARY KEY 或者UNIQUE 约束时，数据库会自动创建一个索引；

--2.手动创建:

            --用户使用创建索引语法来进行创建；

           

--在emp表的ename字段上创建索引.

CREATE INDEX idx_emp_ename

ON emp (ename);

--在emp表的deptno和job的组合上创建索引

CREATE INDEX idx_emp_deptnojob

ON emp (deptno,job);

--测试

--用子查询这种方式建的表，不继承约束

CREATE TABLE test_emp

AS

SELECT * FROM emp

--大数据

INSERT INTO test_emp

SELECT * FROM test_emp

SELECT * FROM test_emp

--将empno修改为rownum(按从1开始)

update test_emp

set empno = ROWNUM

--修改empno的长度

ALTER TABLE test_emp MODIFY(empno NUMBER(7))

--无索引查询

SELECT *

 FROM test_emp

WHERE empno = 111111

--为test_emp建立索引

CREATE INDEX idx_test_emp_empno

ON test_emp(empno)

--再次查询

SELECT *

 FROM test_emp

WHERE empno = 111111

--需要建索引的几种情况：

--1、经常出现在where语句中用到的

--2、数据量特别大

--3、列的值分布比较广

--不能为所有列建立索引：

1、占用内存（每建立一个索引，会自动生成一个索引表）

2、提高查询速度，但是会降低增，删，改的速度

     （每次增，删改一次数据，对应的所有数据索引表会相应增删改）

3.2 删除索引

DROP INDEX index;

--删除索引后，索引中的数据及定义被删除，索引所占的数据

--空间被释放，但表中的数据仍然存在。

4、集合运算

--联合运算：UNION (去重)

--相交运算：INTERSECT

--相减运算：MINUS

--完全联合：UNION ALL（不去重）

--用联合运算，查询出部门为10和20的所有人员编号、姓名、所在部门名称。

SELECT ename, empno, deptno FROM emp WHERE deptno = 10

UNION

SELECT ename, empno, deptno FROM emp WHERE deptno = 20

--备注：两列之间必须一一对应，如果没有对应，使用NULL代替

SELECT ename, empno, deptno FROM emp WHERE deptno = 10

UNION ALL

SELECT ename, empno, deptno FROM emp WHERE deptno = 10

--高级子查询

--子查询即为嵌套结构

--1、查询工资大于SMITH的员工信息

SELECT *

FROM emp

WHERE sal >(SELECT sal FROM emp WHERE ename = 'SMITH')

--2.查询自己部门的平均工资小于自己的工资的员工信息

SELECT ename, sal, avgsal

FROM emp e, (SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno) f

WHERE e.deptno = f.deptno

AND sal > avgsal

--使用高级查询(又称相关子查询)

--当子查询中引用了父查询表中的一个列时，Oracle服务器执行相关子查询 

SELECT ename, sal

FROM emp e

WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = e.deptno)

--查询所有部门名称和人数

SELECT dname, (SELECT count(empno)

                            FROM emp

                            WHERE deptno = d.deptno) d_number

 FROM dept d;

--查询哪些员工是经理?

SELECT *

FROM emp e

WHERE 0 < (SELECT COUNT(empno)

                      FROM emp

                      WHERE mgr = e.empno);

                     

--创建员工历史岗位表 emp_jobhistory：

CREATE TABLE emp_jobhistory(

idnum NUMBER,           --流水号

empno NUMBER(5),      --员工编号

job VARCHAR2(9),         --岗位

begindate DATE,           --开始日期

sal Number(7,2)            --在该岗位时工资

) ;      

--插入如下数据

INSERT INTO emp_jobhistory VALUES(1, 7839, 'TRAINEE', '17-11月-81', 500);             

INSERT INTO emp_jobhistory VALUES(2, 7839,  'SALESMAN', '17-2月-82', 1800); 

 INSERT INTO emp_jobhistory VALUES(3, 7839, 'CLERK', '17-2月-83', 2000); 

 INSERT INTO emp_jobhistory VALUES(4, 7839, 'SALESMAN', '17-2月-85', 1800);  

INSERT INTO emp_jobhistory VALUES(5, 7839,  'MANAGER', '17-2月-87', 3000);  

--删除数据

DELETE  FROM emp_jobhistory  WHERE sal = 500

--查询数据

SELECT *

FROm emp_jobhistory

--查询至少调过2次岗位的员工编号，姓名，岗位

SELECT e.empno, ename, e.job

FROM emp e

WHERE 2 <=(SELECT COUNT(*)

                       FROM emp_jobhistory

                       WHERE empno = e.empno)

                      

--EXISTS：存在

--EXISTS判断是否“存在”，具体操作如下：

--子查询中如果有记录找到，子查询语句不会继续执行，返回值为TRUE；

--•子查询中如果到表的末尾也没有记录找到，返回值为FALSE。

/*子查询并没有确切记录返回，只判断是否有记录存在，而且只要找到相关记录，

子查询就不需要再执行，然后再进行下面的操作。这样大大提高了语句的执行效率。*/

--NOT EXISTS：不存在   

--判断子查询是否没有返回值。

--如果没有返回值，表达式为真;

--如果找到一条返回值，则为假。

--查询哪些人是经理？               

SELECT ename, job, sal, deptno

FROM emp e

WHERE EXISTs (SELECT * FROM emp WHERE mgr = e.empno); 

--查询哪些人不是经理？

SELECT ename, job, sal, deptno

FROM emp e

WHERE NOT EXISTS(SELECT * FROM emp WHERE mgr = e.empno);

--列出至少有一个雇员的所有部门名称。

SELECT dname

FROM dept

WHERE  deptno in(SELECT deptno FROM emp)

--列出一个雇员都没有的所有部门名称。

SELECT dname

FROM dept

WHERE deptno NOT IN(SELECT deptno FROM emp)