1 视图(View)
1.1 什么是视图
可以通过创建表的视图来表现数据的逻辑子集或数据的组合。视图是基于表或另一个视图的逻辑表，一个视图并不包含它自己的数据，它象一个窗口，通过该窗口可以查看或改变表中的数据。视图基于其上的表称为基表。
1.2 视图的优越性
• 视图限制数据的访问，因为视图能够选择性的显示表中的列。
• 视图可以用来构成简单的查询以取回复杂查询的结果。例如，视图能用于从多表中
查询信息，而用户不必知道怎样写连接语句。
• 视图对特别的用户和应用程序提供数据独立性，一个视图可以从几个表中取回数据。
1.3 视图类型
简单视图和复杂视图
| 特性 | 简单视图 | 复杂视图 |
|---|---|---|
| 表的数目 | 一个 | 一个或多个 |
| 包含函数 | 无 | 有 |
| 包含数据分组 | 无 | 有 |
| 通过视图进行DML操作 | 是 | 不允许 |
1.4 简单视图 VS 复杂视图
视图有两种分类：简单和复杂，基本区别涉及 DML (INSERT、UPDATE 和 DELETE)操作。
简单视图：
− 数据仅来自一个表
− 不包含函数或数据分组
− 能通过视图执行 DML 操作
复杂视图：
− 数据来自多个表
− 包含函数或数据分组
− 不允许通过视图进行 DML 操作
1.5 创建简单视图
1.5.1 示例
创建一个视图，视图中包含部门 id 为 80 的员工的 id，名字以及薪水。
create view emp80 as select e.employee_id,e.last_name,e.salary
from employees e ;
1.6 用子查询中的列别名创建视图
如果在创建视图的查询语句中含有列别名，那么列别名将作为视图的列名。
1.6.1 示例
创建一个视图，包含部门 id 为 50 的员工 id 使用 ID_NUMBER 命名该列，包含员工名字使用 NAME 命名该列，包含员工的年薪使用 ANN_SALARY 命名该列。
create view emp50 as select e.employee_id id_number,e.last_name name,12*e.salary ann_salary
from employees e where e.employee_id = 50;
1.8 创建复杂视图
1.8.1 示例
创建一个视图，包含每个部门的部门名称，部门最低薪水、部门最高薪水以及部门的
平均薪水。
create view dept_name as select d.department_name,min(e.salary) min ,max(e.salary) max ,avg(e.salary) avg
from employees e, departments d where e.department_id = d.department_id group by d.department_name;
1.9 在定义视图时指定列名
1.9.1 示例
创建一个视图，包含每个部门的部门名称、部门最低薪水、部门最高薪水以及部门的平均薪水。将部门名称命名为 name、最低薪水命名为 minsal、最高薪水命名为 maxsal、平均薪水命名为 avgsal。
create view dept_name1(name,minsal,maxsal,avgsal) as
select d.department_name,min(e.salary) ,max(e.salary),avg(e.salary)avg
from employees e,departments d
where e.department_id = d.department_id group by d.department_name;
1.10 视图中 DML 操作的执行规则
如果视图中包含下面的部分就不能修改数据：
• 组函数
• GROUP BY 子句
• DISTINCT 关键字
• 用表达式定义的列
1.10.1 示例
删除 emp80 视图中雇员 ID 为 190 的雇员。
delete from emp80 e where e.employee_id = 190;
1.11 拒绝 DML 操作
1.11.1 示例
创建一个简单视图，包含 employees 表中的所有数据，单该视图拒绝 DML 操作。
create view v_emp as
select * from employees with read only;
1.12 通过工具创建视图
| 名称 | 说明 |
|---|---|
| name | 视图名称 |
| ltem list | 当前视图需要投影的列 |
| table list | 投影的列来源于那些表 |
| where clause | 创建视图的条件 |
1.13 删除视图
删除视图不会丢失数据，因为视图是基于数据库中的基本表的。
1.13.1 示例
删除名称为 emp80 的视图。
drop view emp80;
1.14 内建视图
1.14.1 是什么内建视图
• 内建视图是一个带有别名 (或相关名) 的可以在 SQL 语句中使用的子查询。
• 一个主查询的在 FROM 子句中指定的子查询就是一个内建视图。
内建视图：内建视图由位于 FROM 子句中命名了别名的子查询创建。该子查询定义一个可以在主查询中引用数据源。
1.14.1.1 示例
创建显示那些雇员低于他们部门最高薪水的雇员的名字、薪水、部门号和他们部门最高的薪水视图。
create view emp90 as select em.last_name,em.salary,em.department_id, e.maxsal
from employees em ,(select e.department_id, max(e.salary) maxsal
from employees e group by e.department_id)e
where em.department_id = e.department_id and em.salary < e.maxsal;
1.15Top-N 分析
1.15.1 什么是“Top-N”分析
Top-N 查询在需要基于一个条件，从表中显示最前面的 n 条记录或最后面的 n 条记录时是有用的。该结果可以用于进一步分析，例如，用 Top-N 分析你可以执行下面的查询类型：
• 在中挣钱最多的三个人
• 公司中最新的四个成员
• 销售产品最多的两个销售代表
• 过去 6 个月中销售最好的 3 种产品
1.15.2 执行“Top-N”分析
Top-N 查询使用一个带有下面描述的元素的一致的嵌套查询结构：
•子查询或者内建视图产生数据的排序列表，该子查询或者内建视图包含 ORDER BY子句来确保排序以想要的顺序排列。为了取回最大值，需要用 DESC 参数。
• 在最后的结果集中用外查询限制行数。外查询包括下面的组成部分：
− ROWNUM 伪列，它为从子查询返回的每一行指定一个从 1 开始的连续的值
− 一个 WHERE 子句，它指定被返回的 n 行，外 WHERE 子句必须用一个<或者<=操作。
1.15.3 示例一
从 EMPLOYEES 表中显示挣钱最多的 3 个人的名字及其薪水。
select rownum ,last_name,salary from (select last_name, salary
from employees order by salary desc) where rownum <=3;
1.15.4 示例二
显示公司中 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;
1.16Oracle 的分页查询
1.16.1 什么是分页查询
当查询的结果集数据量过大时，可能会导致各种各样的问题发生，例如：服务器资源被耗尽，因数据传输量过大而使处理超时等。最终都会导致查询无法完成。解决这个问题的一个策略就是“分页查询”，也就是说不要一次性查询所有的数据，每次只查询一部分数据。这样分批次地进行处理，可以呈现出很好的用户体验，对服务器资源的消耗也不大。
分页查询原则：
在内建视图中通过 rownum 伪劣值的判断来指定获取数据的数量。
1.16.2 示例
查询雇员表中数据，每次只返回 10 条数据。
select * from (select rownum rn,e.* from employees e) em where em.rn between 11 and 20;
2 序列(Sequence)
2.1 什么是序列
序列是用户创建的数据库对象，序列会产生唯一的整数。序列的一个典型的用途是创建一个主键的值，它对于每一行必须是唯一的。序列由一个 Oracle 内部程序产生并增加或减少。
序列是一个节省时间的对象，因为它可以减少应用程序中产生序列程序的代码量。序列号独立于表被存储和产生，因此，相同的序列可以被多个表使用。
2.2 创建序列
2.2.1 通过 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 个值)
2.2.1.1 示例
创建一个序列名称为：dept_seq,增长间隔为 10，从 120 开始，最大值为 9999，不缓存。不循环使用。
create sequence dept_seq increment by 10
start with 120 maxvalue 9999 nocache nocycle;
2.2.2 通过工具创建序列
2.3 操作序列
2.3.1 查询序列
2.3.1.1 示例
select sequence_name,increment_by,max_value,min_va lue,last_number
from user_sequences;
2.3.2 使用序列
NEXTVAL 和 CURRVAL 伪列
• NEXTVAL 返回下一个可用的序列值，它每次返回一个唯一的被引用值，即使对于
不同的用户也是如此
• CURRVAL 获得当前的序列值
• 在 CURRVAL 获得一个值以前，NEXTVAL 对该序列必须发布
2.3.2.1 示例
在 location ID 2500 中插入一个新部门名称 Support。
insert into departments(department_id,department_name,location_id)
values(dept_seq.nextval,'Support',2500);
2.4 修改与删 除序列
2.4.1 修改序列
2.4.2 修改序列的原则
必须是被修改序列的所有者，或者有 ALTER 权限。
用 ALTER SEQUENCE 语句，只有以后的序列数会受影响。
用 ALTER SEQUENCE 语句，START WITH 选项不能被改变。为了以不同的数重新开始一个序列，该序列必须被删除和重新创建。
2.4.2.1 示例
将 dept_seq 序列中的增长量修改 20，最大值修改为 999999。
alter sequence dept_seq increment by 20 maxvalue 999999 nocache nocycle;
2.4.3 删除序列
2.4.3.1 示例
删除 dept_seq 序列
drop sequence dept_seq;
3 索引(Index)
3.1 什么是索引
在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引提供对表中行的直接和快速访问，它的目的是用已索引的路径快速定位数据以减少磁盘 I/O。索引由 Oracle 服务器自动使用和维护，索引逻辑地和物理地独立于他们索引的表，这意味者索引可以在任何时候被创建或删除，并且不影响基表或其它的索引。当删除表时，相应的索引也被删除。
3.2 索引的类型
唯一性索引：当你在一个表中定义一个列为主键，或者定义一个唯一键约束时 Oracle服务器自动创建该索引，索引的名字习惯上是约束的名字。
非唯一索引：由用户创建，例如，可以创建一个 FOREIGN KEY 列索引用于一个查询中的连接来改进数据取回的速度。
3.3 创建索引的方式
• 自动：在一个表的定义中，当定义一个 PRIMARY KEY 或 UNIQUE 约束时，一个唯一索引被自动创建
• 手动：用户能够在列上创建非唯一的索引来加速对行的访问。
3.4 使用索引
在表上建立更多的索引并不意味者更快地查询，在带索引的表上被提交的每个 DML操作意味者索引必须更新；与表联系的索引越多，对 Oracle 数据库的影响越大，Oracle数据库在每次 DML 操作之后必须更新所有的索引。
3.4.1 什么时候创建索引
• 一个列包含一个大范围的值
• 一个列包含很多的空值
• 一个或多个列经常同时在一个 WHERE 子句中或一个连接条件中被使用
• 表很大，并且经常的查询期望取回少于百分之 2 到 4 的行。
3.4.2 什么时候不创建索引
• 表很小
• 不经常在查询中作为条件被使用的列
• 大多数查询期望取回多于表中百分之 2 到 4 的行
• 表经常被更新
• 被索引的列作为表达式的的一部分被引用
3.5 操作索引
3.5.1 非唯一性索引的类型
oracle 的非唯一性索引：单行索引，复合索引(组合索引)，函数索引。
3.5.2 创建索引语法
3.5.2.1 创建单行索引
3.5.2.1.1 示例
为 employees 表中的 last_name 创建一个索引并命名为 emp_index。
create index emp_index on employees(last_name);
3.5.2.2 创建复合索引
3.5.2.2.1 示例
为 departments 表 创 建 一 个 包 括 manager_id 与 location_id 复 合 索 引 并 命 名 为dept_man_loc。
create index dept_man_loc on departments(manager_id,location_id);
3.5.2.3 创建函数索引
3.5.2.3.1 示例
为 departments 表中的 department_name 创建一个带有大写函数的索引 dept_upper2。
create index dept_upper2 on departments(upper(department_name));
3.5.3 查询索引
user_indexs数据字典视图包含索引和他的唯一的名字
user_ind_columns 视图包含索引名，表明和列名
3.5.3.1 示例
select IC.INDEX_NAME ,ic.COLUMN_NAME,ic.COLUMN_POS ITION,ix.uniqueness
from user_indexes ix ,user_ind_columns ic where ix.index_name = ic.INDEX_NAME and ic.TABLE_NAME = 'DEPARTMENTS';
3.5.4 删除索引
3.5.4.1 示例
删除名称为 dept_upper 的索引。
drop index dept_upper;
4 同义词(Synonym)
4.1 什么是同义词
同义词可以除去对象名必须带的方案限制，并提供给你一个可替换表名、视图名、序列名和存储过程名或其它对象名。该方法对具有特别长的对象的名字很有用。
4.2 创建和删除同义词
4.2.1 创建同义词
create synonym d_sum for dept_sum_vn;
4.2.1.1 示例
4.2.2 删除同义词
drop synonym d_sum;
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
