自连接和视图

20.16.12.3

十三.自连接和视图

之前的内容，我们总是基于所查找的数据物理地存在于数据库的表中这一假设。内连接和外连接使用了不同的方法组合多个表的数据。其实，还有以虚拟方式使用和定义表的方式。

13.1 自连接

自连接允许两次引用同一个表，该表就像是两个独立的表一样。实质上，自连接创建了一个表的虚拟视图，允许多次使用这个虚拟视图。自连接最常见的用途是处理那些本质上是自引用的表。这类表中的一列指向了同一表中的另一列。对于一个职工表EmployeeI：

EmployeeID    NAME   ManagerID

1             A      NULL

2             B      1

3             C      1

4             D      1

5             E       2

6             F       2

7             G       3

8             H       4

9             J       4

10            K       5

该表有员工编号，和员工姓名以及对应的上司。假设我们要列出所有的员工，并显示其对应的上司，要用到自连接。

SELECT

EmployeeI.NAME AS ‘employeename’

Managers.NAME AS ‘mannagename’

FROM EmployeeI

INNER JOIN EmployeeI AS ‘Managers’

ON EmployeeI.EmployeeID =Managers.ManagerID

ORDER BY EmployeeI.EmployeeID

结果：

employeename       mannagename

B A

C A

D A

E B

F B

G C

H D

J D

K E

发现没有显示A。因为EmployeeI表中没有A对应的匹配。要显示A，可以将内连接换为左连接。

SELECT

EmployeeI.NAME AS ‘employeename’

Managers.NAME AS ‘mannagename’

FROM EmployeeI

LEFT JOIN EmployeeI AS Managers

ON EmployeeI.EmployeeID =Managers.ManagerID

ORDER BY EmployeeI.EmployeeID

13.2 创建视图

视图只是保存在数据库中的SELECT语句，一旦保存了，就可以像引用数据库中的表一样来引用视图。数据库的表保存了物理的数据，视图不包含数据，但是允许我们像处理真实数据一样来处理视图。

视图增加了访问数据的灵活性。当访问数据的需求发生变化时，很难重新组织数据库中的数据。视图的最大优势就是可以对数据库中已经存在的数据创建新的视图，使得我们无需物理的重新组织数据，就可以创建等价的新表。视图为我么增加了始终能保持数据库设计不断更新的能力。

数据库都是以许多不同的对象类型组成的，最重要的类型就是表。除了表以外，还有视图，存储过程，触发器等。

CREATE VIEW viewname AS selectstatement

视图创建之后，用viewname表示从selectstatement返回到视图的数据。

CREATE VIEW EmployeeView AS

SELECT

EmployeeI.NAME AS ‘employeename’

Managers.NAME AS ‘mannagename’

FROM EmployeeI

INNER JOIN EmployeeI AS ‘Managers’

ON EmployeeI.EmployeeID =Managers.ManagerID

视图不会保存物理数据，所以不能包含ORDER BY子句。

13.3 引用视图

SELECT

employeename

Mannagename

FROM  EmployeeView

WHERE employeename=’B’

结果：

employeename       mannagename

B A

13.4 视图的优点

视图一旦创建，可以像引用表一样引用视图。即使创建的视图来自于多个关联起来的表，它在逻辑上就像是一个表一样。

视图可以减少复杂性。

视图可以增加可复用性。

视图可以正确地格式化数据。

视图可以创建计算的列。

视图可以用来对列重新命名。

视图可以创建数据子集。

视图可以用来加强安全性限制。

13.5 修改和删除视图

修改视图：ALTER VIEW viewname AS selectstatement

 

在ORACLE中需要先执行删除视图DROP VIEW，然后执行新定义的视图CREATE VIEW。

删除：

DROP VIEW viewname