数据库中的视图

发表于 更新于 分类于

视图 (VIEW)

视图的特点 :

  • 虚表, 是从一个或几个基本表 (或视图) 导出的表
  • 只存放视图的定义, 不存放视图对应的数据
  • 基表中的数据发生变化, 从视图中查询出的数据也随之改变

视图相当于建一个方便用户查看的表, 这个表在用户要看的时候临时生成, 平时只记录这个表的构造方法.

定义视图

以建立的方式分类,视图有以下几种 :

  1. 基于单个表的视图
  2. 基于多个表的视图
  3. 基于视图的视图
  4. 带表达式的视图
  5. 分组视图

语句格式

CREATE VIEW <视图名> [(<列名> [,<列名>])]
AS <子查询>
[WITH CHECK OPTION];

子查询 :
子查询可以是任意的SELECT语句, 是否可以含有 ORDER BY 子句和 DISTINCT 短语, 决定具体系统的实现.

WITH CHECK OPTION :
对视图进行 UPDATE, INSERT, DELETE操作时要保证更新, 插入, 删除的行满足视图定义中的谓词条件.
(满足子查询中的条件表达式)

组成视图的属性列名 : 全部省略或全部指定

  • 全部省略:
    • 由子查询中的SELECT目标列中的字段组成
  • 全部指定:
    • 某个目标列是聚集函数或列表达式
    • 多表链接时选出了几个同名列作为视图的字段
    • 需要在视图中为某个列启用新定更适合的名字

执行CREATE VIEW语句时, 只是把视图定义存入数据字典. 并不执行其中的SELECT语句.
在对视图查询时候, 按视图的定义从基本表中将数据查出.

基于单个表的视图

例子1-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#建立信息系学生的视图
CREATE VIEW IS_Student #行列子集视图
AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept = 'IS';

#建立信息系学生的视图, 同时呀求进行修改和插入操作时要保证该视图只有信息系学生
CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept = 'IS'
WITH CHECK OPTION;  #该子句确保只能有信息系学生
#加上WITH CHECK OPTION子句后, 对视图进行增删改操作时, 自动加上Sdept = 'IS'的条件

#将Student中所有女生记录定义为一个视图
CREATE VIEW F_Student (F_Sno, name, sex, age, dept)
AS
SELECT *    # 不指定属性列
FROM Student
WHERE Ssex = '女';
#缺点 : 修改基表Student后, 基表与视图的映像关系被破坏, 导致视图不能正常工作

行列子集视图 :
若一个视图是从单基本表导出的, 并且只是去掉了部分行列, 但保留主键. 我们称这类视图为行列子集视图

基于多个表的视图

例子1-2

1
2
3
4
5
6
7
8
9
#建立信息系选修了1号课程的学生的视图 (包括学号, 姓名, 成绩)
CREATE VIEW IS_S1 (Sno, Sname, Grade)
AS
SELECT Student.Sno, Sname, Grade
FROM Student, SC
WHERE Sdept = 'IS'
  AND Student.Sno = SC.Sno
  AND SC.Cno = '1'
);

基于视图的视图

例子1-3

1
2
3
4
5
6
#建立信息系选修了1号课程且成绩在90以上的学生的视图
CREATE VIEW IS_S2
AS
SELECT Sno, Sname, Grade
FROM IS_S1
WHERE Grade >= 90;

带表达式的视图

例子1-4

1
2
3
4
5
#定义一个反映学生出生年份的视图
CREATE VIEW BT_S (Sno, Sname, Sbirth)
AS
SELECT Sno, Sname, 2020-Sage
FROM Student;

分组视图

例子1-5

1
2
3
4
5
6
#将学生的学号及平均成绩定义为一个视图
CREATE VIEW S_G (Sno, Gavg)
AS
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno;

删除视图

从数据字典中删除指定的视图定义

语句格式:

DROP VIEW <视图名> [CASCADE];
CASCADE : 使用该关键字可以级联删除这个视图导出的其他视图, 不使用时则只能删除没有导出视图的视图.
删除基表时 : 由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除.

例子2-1

1
2
3
4
#删除视图BT_S和IS_S1 (前者由后者导出)
DROP VIEW IS_S1;         #拒绝执行 : 有其他视图由其导出
DROP VIEW BT_S;          #成功执行 : 无其他视图由其导出
DROP VIEW IS_S1 CASCADE; #成功执行 : 级联删除BT_S和IS_S1

查询视图

从用户的角度来看 : 查询视图与查询基本表的方式相同

从实现的角度来看: 实现视图查询的方法如下 :

  • 视图消解法 (View Resoltion)
    • 进行有效性检查
    • 转换成等价的对基本表的查询
    • 执行修正后的查询

例子3-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#在信息系学生的视图中找出年龄小于20岁的学生
  #查询语句如下   (操作者视角)
  SELECT Sno, Sage
  FROM IS_Student
  WHERE Sage < 20;
  #视图消解转换后的查询语句如下:    (系统视角)
  SELECT Sno, Sage
  FROM Student
  WHERE Sdept = 'IS'
    AND Sage < 20;

#查询选修了1号课程的信息系学生
  SELECT IS_Student.Sno, Sname
  FROM IS_Student, SC
  WHERE IS_Student.Sno = SC.Sno
    AND SC.Cno = '1';

视图消解法的局限 : 有时视图消解法不能生成正确的查询

例子3-2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#在S_G视图中查询平均成绩在90以上的学生学号和平均成绩
  SELECT *            # S_G的定义如下:
  FROM S_G            # CREATE VIEW S_G (Sno, Gavg) AS
  WHERE Gavg >= 90;   # SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno;
  #视图消解法的两种情况
    #错误方式 : WHERE的条件这里不能用聚集函数
    SELECT Sno, AVG(Grade) FROM SC
    WHERE AVG(Grade) >= 90 GROUP BY Sno;
    #正确方式 : 聚集函数针对分组操作, 要先分组
    SELECT Sno, AVG(Grade) FROM SC
    GROUP BY Sno HAVING AVG(Grade) >= 90;
  #还有一个正确方法 : 直接将定义嵌入查询语句 ---- 先把这个表构造出来,再直接在表中查询
    SELECT * FROM (
      SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno # S_G的定义
    ) AS S_G(Sno, Gavg)
    WHERE Gavg >= 90;

更新视图

从用户的角度来看 : 更新视图与查询基本表的方式相同

例子4-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#将信息系学生视图IS_Student中学号201215122的学生名字改为刘晨
  UPDATE IS_Student
  SET Sname = '刘晨'
  WHERE Sno = '201215122';
  #转换后
  UPDATE Student
  SET Sname = '刘晨'
  WHERE Sno = '201215122' AND Sdept = 'IS';

#向信息系学生视图IS_Student中插入一个新学生记录, 其中学号为201215129, 姓名赵新, 年龄20岁
  INSERT INTO IS_Student
  VALUES ('201215129', '赵新', 20);
  #转换后
  INSERT INTO Student(Sno, Sname, Sage, Sdept)
  VALUES ('201215129', '赵新', 20, 'IS');

# 删除信息系学生视图IS_Student中学号为201215129的记录
  DELETE FROM IS_Student
  WHERE Sno = '201215129';
  #转换后
  DELETE FROM Student
  WHERE Sno = '201215129' AND Sdept = 'IS';

更新视图的限制 :
一些视图是不可更新的, 因为对这些视图的更新不能唯一地有意义地转换为对相应的基本表的更新

例子4-2

1
2
3
4
5
6
7
8
9
10
#例1-5中的视图及为一个不可更新的视图
  #例1-5 : 将学生的学号及平均成绩定义为一个视图
  CREATE VIEW S_G (Sno, Gavg) AS
  SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno;
  #对其的更新语句为:
  UPDATE S_G SET Gavg = 90 WHERE Sno = '201215121';
  #这个对视图的更新无法转换成对基本表SC的更新
  #(平均成绩是由SC中成绩计算而成, 逻辑上应该是平均成绩随着成绩更新而更新, 而不是相反.)


视图更新的规则 :

  • 允许对行列子集视图进行更新 (依赖于单表构造的视图)
  • 对其他类型视图的更新不同系统有不同限制
  • 基于一个不可更新的视图之上 所建立的视图 也是不可更新的

视图的作用

  • 简化了用户的操作***
  • 使用户能够以多种角度看待同一数据
  • 视图对重构数据库提供了一定程度的逻辑独立性
  • 视图能够对机密数据提供安全保护
  • 适当的利用视图可以更清晰的表达查询

简化用户操作

当视图中的数据不是直接来自一张基本表, 定义视图能够简化用户的操作.

  • 基于多张表连接成的视图
  • 基于复杂嵌套查询的视图
  • 含导出属性的视图

使用户能够以多种角度看待同一数据

  • 视图机制能使不同用户以不同方式看待统一数据, 适应数据库共享需要

视图对重构数据库提供了一定程度的逻辑独立性

  • 数据库重构
  • 视图只能在一定程度上提供数据的逻辑独立性
    • 由于视图的更新是有条件的, 因此应用程序中修改数据的语句可能会因为基本表结构的改变而改变

例子5-1

1
2
3
4
5
6
7
8
9
# 学生关系Student(Sno, Sname, Ssex, Sage, Sdept)"垂直地"分成两个基本表:
# SX(Sno, Sname, Sage) 与SY(Sno, Ssex, Sdept)
  # 通过创建一个Student视图
  CREATE VIEW Student(Sno, Sname, Ssex, Sage, Sdept)
  AS
    SELECT SX.Sno, SX.Sname, SY.Ssex, SX.Sage, SY.Sdept
    FROM SX, SY
    WHERE SX.Sno = SY.Sno
  # 使得用户的外模式不变, 用户的应用程序通过视图仍然能够查找数据

视图能够对机密数据提供安全保护

  • 对不同用户定义不同视图, 使得么个用户只能看到他有权看到的数据

适当的利用视图可以更清晰的表达查询

  • 经常要查询同一个语句时, 可以针对该语句先建立一个视图, 使得查询语句更清晰

例子5-2

1
2
3
4
5
6
7
8
9
# 对每个同学找出他获得最高成绩的课程号 (经常要查询该语句)
  # 先定义一个视图, 得出每个学生的最高成就
  CREATE VIEW VMGrade AS
  SELECT Sno, MAX(Grade) Mgrade FROM Student
  GROUP BY Sno;
  # 再查询
  SELECT SC.Sno, Cno FROM SC, VMGrade
  WHERE SC.Sno = VMGrade.Sno
    AND SC.Grade = VMGrade.Mgrade;