1. 视图

视图中并不会存放数据, 只会存放视图的定义语句. 视图是基于表或另一个视图的逻辑表，一个视图并不包含它自己的数据，它象一个窗口，通过该窗口可以查看或改变表中的数据。它是从若干基本表和（或）其他视图构造出来的表.

在用户使用视图时, 才去动态检索数据.

视图基于其上的表称为基表

视图的优越性 ：

1. 视图限制数据的访问 因为视图能够选择性的显示表中的列

2. 将复杂的查询简单化 如：视图能用于从多表中查询信息，而用户不必知道怎样写连接语句

创建视图：

create view dept_name(name,minsal,maxsal,avgsal) as select 
             d.department_name,
             min(salary),
             max(salary),
             avg(salary) from employees e,departments d 
           where e.department_id = d.department_id group by d.department_name;

查询视图：

select * from dept_name;

3. 视图对特别的用户和应用程序提供数据独立性，一个视图可以从几个表中取回数据

视图类型:

简单视图和复杂视图：

----创建简单视图

语法结构：

示例：

创建一个视图，视图中包含部门 id 为 80 的员工的 id，名字以及薪水

create view emp80 as select e.employee_id,e.last_name,e.salary from employees e;

--用子查询中的列别名创建视图

如果在创建视图的查询语句中含有列别名，列别名将作为视图的列名

示例：

创建一个视图，包含部门 id 为 50 的员工 id 使用 ID_NUMBER 命名该列，包含员工名字使用 NAME 命名该列，包含员工的年薪使用 ANN_SALARY 命名该列

create view emp50 as 
    select e.employee_id id_number,e.last_name name,e.salary ann_salary from employees e;

视图中 DML 操作的执行规则 ：

如果视图中包含下面的部分就不能修改数据：

• 组函数

• GROUP BY 子句

• DISTINCT 关键字

• 用表达式定义的列

对视图进行DQL和DML操作：

--创建简单视图
create or replace view v_student as (select * from student);
-- 查询视图（从视图中读取数据）
select * from v_student;
-- 新增
insert into v_student values (seq_student.nextval, '小刚', '男', 20, sysdate, 'gang@sxt.com', 102);
-- 修改
update v_student set age=21 where sno=2;
-- 删除
delete from v_student where sno=2;

拒绝 DML 操作（只读视图） ：

(with read only)只读视图, 只能对视图进行查询(DQL)操作, 不能执行增删改(DML)操作

create view dept as (select * from employees) with read only;

----创建复杂视图

语法结构：

示例：

创建一个视图，包含每个部门的部门名称，部门低薪水、部门高薪水以及部门的平 均薪水

create view dept_name as select d.department_name,
             min(salary) min_salary,
             max(salary) max_salary,
             avg(salary) avg_salary from employees e,departments d 
           where e.department_id = d.department_id group by d.department_name;

--在定义视图时指定列名

create view dept_name(name,minsal,maxsal,avgsal) as select 
             d.department_name,
             min(salary),
             max(salary),
             avg(salary) from employees e,departments d 
           where e.department_id = d.department_id group by d.department_name;

----删除视图

语法结构：

drop view 视图名;

----内建视图（将子查询的结果看做一张表/视图）

概念：

内建视图是一个带有别名 (或相关名) 的可以在 SQL 语句中使用的子查询

一个主查询的在 FROM 子句中指定的子查询就是一个内建视图

内建视图：内建视图由位于 FROM 子句中命名了别名的子查询创建。该子查询定义一个可以在主查询中引用数据源（伪基表）

示例：

显示那些雇员低于他们部门高薪水的雇员的名字、薪水、部门号和他们部门高的薪水

select distinct e.last_name,e.salary,e.department_id from employees e,
    (select d1.department_id,max(e1.salary) max_mal from departments d1,employees e1 
      where e1.department_id = d1.department_id group by d1.department_id
    )t 
      where e.salary < t.max_mal order by e.salary desc;

----Top-N 分析

Top-N 查询在需要基于一个条件，从表中显示前面的 n 条记录或后面的 n 条记录时是有用的。

----执行“Top-N”分析

Top-N 查询使用一个带有下面描述的元素的一致的嵌套查询结构：

•子查询或者内建视图产生数据的排序列表，该子查询或者内建视图包含 ORDER BY 子句来确保排序以想要的顺序排列。为了取回大值，需要用 DESC 参数。

• 在后的结果集中用外查询限制行数。外查询包括下面的组成部分：

− ROWNUM 伪列，它为从子查询返回的每一行指定一个从 1 开始的连续的值

− 一个 WHERE 子句，它指定被返回的 n 行，外 WHERE 子句必须用一个<或者<=操作。

示例1：

从 EMPLOYEES 表中显示挣钱多的 3 个人的名字及其薪水

select rownum ,last_name,salary 
    from (select last_name, salary from employees order by salary desc) where rownum <=3;

示例2：

显示公司中 4 个资格老的雇员显示他们的入职时间与名字

select rownum, e.last_name,e.hire_date 
     from (select last_name, hire_date from employees order by hire_date) e where rownum <= 4;

rowid和rownum：

--->rowid在记录创建时生成，而且是不变，直接指向硬件上的存储位置;通过rowid查询是效率高的, 但是rowid是由Oracle维护的, 人力无法做到

--->rownum是一个伪列, 查询的时候除非特别指定，否则不会显示. 表示行号, 常用于控制查询返回的行数

----Oracle 的分页查询

当查询的结果集数据量过大时，可能会导致各种各样的问题发生，例如：服务器资源被耗尽，因数据传输量过大而使处理超时，等等。终都会导致查询无法完成。

解决这个问题的一个策略就是“分页查询”，也就是说不要一次性查询所有的数据，每次只查询一部分数据。这样分批次地进行处理，可以呈现出很好的用户体验，对服务器资源的消耗也不大。

通过rownum进行分页查询------>

分页查询原则：

在内建视图中通过 rownum 伪列值的判断来指定获取数据的数量

通用的分页查询语句结构：

select t.* from 
   (select t1.*,rownum rn from 表 t1 where rownum <= 当前页*分页单位) t 
         where t.rn >= (当前页-1)*分页单位+1 and t.rn <= 当前页*分页单位;

示例：

查询雇员表中数据，每次只返回 3 条数据

--第三页
select e1.* from 
     (select e.*,rownum rn from emp e where rownum <= 9) e1 
        where e1.rn>=7 and e1.rn<=9;

2. 序列

序列是用户创建的数据库对象，序列会产生的整数。序列的一个典型的用途是创建一

个主键的值，它对于每一行必须是的。序列由一个 Oracle 内部程序产生并增加或减少。

序列是一个节省时间的对象，因为它可以减少应用程序中产生序列程序的代码量。序列号独

立于表被存储和产生，因此，相同的序列可以被多个表使用。

序列是Oracle中特有的对象, 用于生成一个自动递增的数列. 通常被用来作为主键的值.

----创建序列

--通过 DDL 语句创建序列

语法结构：

在语法中：

sequence 是序列发生器的名字

INCREMENT BY n ：指定序列号之间的间隔，在这儿 n 是一个整数 (如果该子句被省略，序列增量为 1) -----------序列号递增间隔

START WITH n ：指定产生的个序列数 (如果该子句被省略，序列从 1 开始)

MAXVALUE n ：指定序列能产生的大值

NOMAXVALUE ：(默认选项) 对于升序序列指定 10^27 为大值，对于降序序列指定-1 为大值

MINVALUE n ：指定小序列值

NOMINVALUE ：(默认选项) 对于升序序列指定 1 为小值，对于降序序列指定-(10^26)为小值

CYCLE|NOCYCLE ：指定序列在达到它的大或小值之后，是否继续产生(NOCYCLE

是默认选项)

CACHE n|NOCACHE ：指定 Oracle 服务器预先分配多少值，并且保持在内存中(默认

情况下，Oracle 服务器缓冲 20 个值 nocache表示没有缓存)

示例：

创建一个序列名称为：dept_seq,增长间隔为 10，从 120 开始，大值为 9999，不缓存。不循环使用

create sequence dept_seq increment by 10 start with 120 maxvalue 9999 nocycle nocache;

----查询序列

示例：

select sequence_name,min_value,max_value,increment_by,last_number from user_sequences;

----使用序列

NEXTVAL 和 CURRVAL 伪列 ：

• NEXTVAL 返回下一个可用的序列值，它每次返回一个的被引用值，即使对于不同的用户也是如此

• CURRVAL 获得当前的序列值

注意：在 CURRVAL 获得一个值以前，NEXTVAL 对该序列必须发布

--查看序列的下一个值

select dept_seq.nextval from dual;

--查看序列的当前值

select dept_seq.currval from dual

--插入数据时使用序列

示例：

在 location ID 2500 中插入一个新部门名称 Support

insert into departments(department_id,department_name,location_id) 
          values(dept_seq.nextval,'Support',2500);

----修改和删除序列

--修改序列

修改序列的原则:

必须是被修改序列的所有者，或者有 ALTER 权限。

用 ALTER SEQUENCE 语句，只有修改后的序列数会受影响。

用 ALTER SEQUENCE 语句，START WITH 选项不能被改变。为了以不同的数重新开始一个序列，该序列必须被删除和重新创建。

示例：

将 dept_seq2序列中的增长量修改 20，大值修改为 999999。

alter sequence dept_seq2 increment by 20 maxvalue 999999 nocache nocycle;

--删除序列

示例：

删除 dept_seq 序列

drop sequence dept_seq;

3. 索引

3.1 概念：

在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的

一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据

页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内

容。

索引提供对表中行的直接和快速访问，它的目的是用已索引的路径快速定位数据以减少磁

盘 I/O。索引由 Oracle 服务器自动使用和维护，索引逻辑地和物理地独立于他们索引的表，这

意味者索引可以在任何时候被创建或删除，并且不影响基表或其它的索引。当删除表时，相应

的索引也被删除。

为了提高查询效率, 可以建立类似目录的数据库对象, 实现数据快速查询, 这就是索引(Index)

3.2 索引的类型 ：

性索引：当你在一个表中定义一个列为主键，或者定义一个键约束时 Oracle

服务器自动创建该索引，索引的名字习惯上是约束的名字。

非索引：由用户创建，例如，可以创建一个 FOREIGN KEY 列索引用于一个查询

中的连接来改进数据取回的速度。

3.3 创建索引的方式

• 自动：在一个表的定义中，当定义一个 PRIMARY KEY 或 UNIQUE 约束时，一个索引被自动创建

• 手动：对于不是primary key和unique约束的列, 如果经常会被查询或用于排序，用户能够在列上创建非的索引来加速对行的访问。

3.4 使用索引

过多建立索引也坏事

在表上建立更多的索引并不意味者更快地查询，在带索引的表上被提交的每个 DML 操作意味者索引必须更新；与表联系的索引越多，对 Oracle 数据库的影响越大，Oracle 数据库在每次 DML 操作之后必须更新所有的索引。

3.4.1什么时候创建索引

• 一个列包含一个大范围的值

• 一个列包含很多的空值

• 一个或多个列经常同时在一个 WHERE 子句中或一个连接条件中被使用

• 表很大，并且经常的查询期望取回少于百分之 2 到 4 的行。

3.4.2什么时候不创建索引

• 表很小

• 不经常在查询中作为条件被使用的列

• 大多数查询期望取回多于表中百分之 2 到 4 的行

• 表经常被更新

• 被索引的列作为表达式的的一部分被引用

索引被创建后, 查询时会自动生效, 提高查询效率

3.5 非性索引（手动创建）

非性索引的类型 ：

oracle 的非性索引：单行索引，复合索引(组合索引)，函数索引

----创建索引

--创建单行索引

示例：

为 employees 表中的 last_name 创建一个索引并命名为 emp_index

create index emp_index on employees(last_name);

--创建复合索引

示例：

为 departments 表 创 建 一 个 包 括 manager_id 与 location_id 复 合 索 引 并 命 名 为 dept_man_loc

create index dept_man_loc on departments(manager_id,location_id);

--创建函数索引

示例：

为 departments 表中的 department_name 创建一个带有大写函数的索引 dept_upper

create index dept_upper on departments(upper(department_name));

----查询索引

示例：

select ic.index_name,ic.column_name,ic.column_position,ix.uniqueness 
      from user_indexes ix,user_ind_columns ic 
      where ix.INDEX_NAME = ic.INDEX_NAME and ic.TABLE_NAME = 'DEPARTMENTS';

----删除索引

示例：

删除名称为 dept_upper 的索引

drop index dept_upper;

索引的优缺点：

优点：

当数据量比较庞大时, 索引可以大大提高查询的效率

缺点：

a) 索引会单独存放, 索引过多会占用大量的存储空间;

b) 索引会降低DML的效率, 因为数据发生变化时, 还需要重新维护索引;

c) 对于性不好的数据, 不适合创建索引

4. 同义词(Synonym)

4.1 概念

同义词可以除去对象名必须带的方案限制，并提供给你一个可替换表名、视图名、序列名和

存储过程名或其它对象名。该方法对具有特别长的对象的名字很有用。

4.2创建和删除同义词

----创建同义词

示例：

create synonym em for employees;

----删除同义词

示例：

drop synonym em;

5. 创建用户（User）

用户概念：Oracle 用户是用来连接数据库和访问数据库对象的

----创建用户

条件：需要具备创建用户的权限可以使用 sys 或者 system 用户来创建新用户

语法结构：

--使用默认表空间

示例：

创建一个用户名称为 u_test，表空间使用 Oracle 默认的表空间

create user u_test identified by 0100;

--使用指定表空间

示例：

创建一个用户名为 u_bjsxt,使用 bjsxt 表空间作为它的表空间

create user u_bjsxt identified by 0100 default tablespace bjsxt temporary tablespace temp;

----删除用户

示例：

删除 u_test 用户

drop user u_test;

--删除用户的同时将该用户下的其他对象一并删掉

示例：

drop user u_test cascaed;