视图

视图

视图是数据库系统中一种非常常用的数据库对象,它往往是和外模式联系在一起的概念。外模式是数据库用户(包括应用程序员和最终用户)能够看见和使用的局部数据时逻辑结构和特征的描述,通常通过数据库用户的数据视图来实现,用于对与某一应用有关的数据进行逻辑表示。

视图概述

视图是一个或多个表或者视图中导出的表,它也包含一系列带有名称的数据列和若干条数据行。然而,视图不同于数据库中真实的表,其区别在于:

  1. 视图不是数据库中真实的表,是一张虚拟的表,其结构和数据是建立在对数据库中真实表的查询基础上的。
  2. 视图的内容是由存储在数据库中进行查询操作的SQL语句来定义的,它的列数据与行数据均来自于定义视图的查询所引用的真实表(也称为基础表、基表或源表)或者是基于真实表的计算值,并且这些数据是在引用视图时动态生成的。
  3. 视图不是以数据集的形式储存在数据库中,它所对应的数据实际上是存储在视图所引用的真实表(基础表)中。
  4. 视图是用来查看存储在别处的数据的一种设施,其本身并不存储数据。

尽管视图与数据库中的表存在着本质的不同,但视图一经定义后,可以如同使用表一样,对视图进行查询以及受限的修改、删除和更新等操作。并且,使用视图具有如下一些优点:

  1. 集中分散数据。当用户所需的数据分散在数据库的多个表中时,通过定义视图可以将这些数据集中在一起,以方便用户对分散数据的集中查询与处理。
  2. 简化查询语句。定义视图可为用户屏蔽数据库的复杂性,使其不必详细了解数据库中复杂的表结构和表连接,因而能够简化用户对数据库的查询语句。例如,即便是底层数据库表发生来更改,也不会影响上层用户对数据库的正常使用,只需数据库编程人员重新定义视图的内容即可。这也正是外模式,以及模式和外模式之间映射的目的。实际上,视图的查询定义就是模式和外模式之间的映射关系。这样一来,依据数据外模式编写的应用程序就不需要更改,从而保证了数据和程序的逻辑独立性。
  3. 重用SQL语句。视图提供的是一种对查询操作的封装,它本身不包含数据,其所呈现的数据是根据视图的定义从基础表中检索出来的,若基础表中的数据被新增或更改,视图所呈现的就是更新后的数据。因此,通过定义视图,编写完所需要的查询后,可以方便地重用该视图,而不必了解它的具体查询细节。
  4. 保护数据安全。通过只授予用户使用视图的权限,而不具体指定使用表的权限,来保护基础数据的安全性。因为,通过视图用户只能查询和修改他们所能看到的数据,数据库中的其他数据则是既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上,但不能授权到数据库的特定行上。通过视图,用户可以被限制在数据的不同子集上:使用权可以被限制在基表的行的子集上。
  5. 共享所需的数据。通过视图,每个用户不必都定义和存储自己所需的数据,可以共享数据库中的数据,从而同样的数据只需存储一次。
  6. 更改数据格式。通过视图,可以重新格式化检索出的数据,并组织输出到其他应用中去。

创建视图

其语法格式为:

1
2
3
4
create [or replace]
view view_name [(col_list)]
as select_statement
[with [cascaded | local] check option]

主要语法说明如下:

  1. or replace:可选项。用于指定or replace子句。该语句用于替换数据库中已有的同名视图,但需要在该视图上具有drop权限。
  2. view_name:指定视图的名称。该名称在数据库中必须是唯一的,不能与其他表或视图同名。
  3. col_list:这个可选子句可以为视图中的每个列指定明确的名称。其中,列名的数目必须等于select语句检索出的结果数据集的列数,并且每个列名之间使用逗号分隔。若省略了col_list子句,则新建视图使用与基础表或源视图中相同的列名。
  4. select_statement:用于指定创建视图的select语句。这个select语句给出了视图的定义,它可以用于查询多个基础表或源视图。对于select语句的指定,存在以下一些限制:
    • 定义视图的用户除了要求被授予create view的权限,还必须授予可以操作视图所设计的基础表或其他视图的相关权限,例如,由select语句选择的每一列上的某些权限。
    • select语句不能包含from子句中的子查询(我电脑上的MySQL支持from子句)。
    • select语句不能引用系统变量或用户变量。
    • select语句不能引用预处理语句参数。
    • 在select语句中引用的表或视图必须存在。但是,创建完视图之后,可以删除视图定义中所引用的基础表或源视图。若想检查视图定义是否存在这类问题,可以使用check table语句。
    • 若select语句中所引用的不是当前数据库的基础表或源视图时,需要在该表或视图前加上数据库的名称作为限定前缀。
    • 在由select语句构造的视图定义中,允许使用order by子句。但是,如果从特定视图中进行了选择,而该视图使用了自己的order by语句,则视图定义中的order by子句将被忽略。
    • 对于select语句中的其他选项或子句,若所创建的视图中包含了这些选项,则语句执行效果未定义。例如,如果在视图定义中包含了limit子句,而select语句也使用了自己的limit子句,那么MySQL对于使用哪一个limit语句未作定义。
  5. with check option:这个可选子句用于指定在可更新视图上所进行的修改都需要符合select_statement中所指定的限定条件,这样可以确保数据修改后仍可以通过视图看到修改后的数据。但视图是根据另一个视图定义时,with check option给出两个参数,即cascaded和local,它们决定检查测试的范围。其中,关键字cascaded为默认值,它会对所有视图进行检查,而关键字local则表示只对定义的视图进行检查。

例如,在数据库db_school中创建视图v_student,要求该视图包含学生信息表tb_student中所有男生的信息,并且保证今后对该视图数据的修改都必须符合学生性别为男性的这个条件:

1
2
3
4
5
use db_school;
create or replace view db_school.v_student
as
select * from db_school.tb_student where sex = '男'
with check option;

例如,在数据库db_school中创建视图db_school.v_score_avg,要求该视图包含表tb_score中所有学生的的学号和平均成绩,并按学号studentNo进行排序:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
insert into db_school.tb_score
values
('2013110101', '11003', 90),
('2013110101', '21001', 86),
('2013110103', '11003', 89),
('2013110103', '21001', 88),
('2013110201', '11003', 78),
('2013110201', '21001', 92),
('2013110202', '11003', 82),
('2013110202', '21001', 85),
('2013310101', '21004', 83),
('2013310101', '31002', 68),
('2013310103', '21004', 80),
('2013310103', '31002', 76),
('2014210101', '21002', 93),
('2014210101', '21004', 89),
('2014210102', '21002', 95),
('2014210102', '21004', 88),
('2014310101', '21001', 79),
('2014310101', '21004', 80),
('2014310102', '21001', 91),
('2014310102', '21004', 87);
create or replace view db_school.v_score_avg(学号, 平均分)
as
select studentNo, avg(score) from tb_score
group by studentNo;

我的MySQL版本支持from子句:

1
2
3
4
5
6
create or replace view v_score_avgs(学号, 平均分)
as
select distinct t1.studentNo, t2.平均分 from tb_score as t1,
(select studentNo, avg(score) as 平均分 from tb_score
group by studentNo) as t2
where t1.studentNo = t2.studentNo;

下面的例子反映了with check option的内容:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create view v_score
as
select * from db_school.tb_score where score < 50
with check option;

create view v_score_local
as
select * from db_school.v_score where score > 80
with local check option;

create view v_score_cascaded
as
select * from db_school.v_score where score > 80
with cascaded check option;

后面两个视图是根据第一个视图定义的。local检查只会针对其自身检查插入项进行测试;cascaded检查则不仅会针对它自己的检查对插入项进行测试;也会针对基本视图进行测试。通过下面的语句可以清楚得分辨彼此之间的差异:

1
2
3
4
5
6
7
insert into db_school.v_score_local
values
('2013110101', '21005', 90);

--结果

[2018-09-01 15:04:05] [HY000][1369] CHECK OPTION failed 'db_school.v_score_local'

删除视图

1
drop view if exists db_school.v_student;

修改视图定义

例如:

1
2
3
4
5
6
7
8
9
10
11
-- 因为刚刚删除了,所以再重新定义一个
create or replace view db_school.v_student
as
select * from db_school.tb_student where sex = '男'
with check option;
-- 修改
alter view db_school.v_student(studentNo, studentName, classNo)
as
select studentNo, studentName, classNo from db_school.tb_student
where sex = '男' and nation = '汉'
with check option;

或者使用create or replace语句也可以直接修改。

1
2
3
4
5
create or replace view db_school.v_student
as
select studentNo, studentName, classNo from db_school.tb_student
where sex = '男' and nation = '汉'
with check option;

查看视图定义

1
show create view db_school.v_student;

或者在终端:

1
2
3
4
5
6
7
mysql> show create view db_school.v_student \G;
*************************** 1. row ***************************
View: v_student
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db_school`.`v_student` AS select `db_school`.`tb_student`.`studentNo` AS `studentNo`,`db_school`.`tb_student`.`studentName` AS `studentName`,`db_school`.`tb_student`.`classNo` AS `classNo` from `db_school`.`tb_student` where ((`db_school`.`tb_student`.`sex` = '男') and (`db_school`.`tb_student`.`nation` = '汉')) WITH CASCADED CHECK OPTION
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

更新视图数据

由于视图是个虚拟表,所以通过插入、修改和删除等操作方式来更新视图中的数据,实质上是在更新视图所引用的基础表中的数据。然而,视图的更新操作是受一定限制的,并非所有的视图都可以进行insert、update或delete等更新操作,只有满足可更新条件的视图才能进行更新,否则可能会导致系统出现不可预期的结果。对于可更新的视图,需要该视图中的行和基础表中的行具有一对一的关系。另外,倘若视图中包含了下述任何一种SQL语句结构,那么该视图就是不可更新的:

  1. 聚合函数;
  2. distinct关键字;
  3. group by子句;
  4. order by子句;
  5. having子句;
  6. union运算符;
  7. 位于选择列表中的子查询;
  8. from子句中包含多个表;
  9. select语句中引用了不可更新的视图;
  10. where子句中的子查询引用了from子句中的表。

使用insert语句通过视图向基础表中插入数据

1
2
3
4
5
6
7
8
9
10
11
create or replace view db_school.v_student
as
select * from db_school.tb_student
with check option ;

insert into db_school.v_student
values
('2014310108', '周明', '男', '1997-08-16', '辽宁', '汉', 'IS1401');

select *
from db_school.v_student;

with check option子句会在更新数据时检查新数据是否符合视图定义中where子句的条件,并且with check option子句只能和可更新视图一起使用。若插入的新数据不符合where子句的条件,则数据插入操作不会成功,因而此时视图的数据插入操作受限。另外,但视图所依赖的的基础表有多个时,也不能向该视图插入数据,这是因为MySQL不能正确地确定要被更新的基础表。

使用update语句通过视图修改基础表的数据

1
2
3
# 将视图v_student中所有学生的native都更新为"河南"
update db_school.v_student
set native = '河南';

使用delete语句通过视图删除基础表的数据

1
2
delete from db_school.v_student
where studentName = '周明';

查询视图数据

视图一经定义后,就可以如同查询数据库中的表一样,对数据库进行数据查询,这也是对视图使用最多的一种操作。视图用于查询检索,主要体现在这样一些应用:

  1. 利用视图简化复杂的表连接;
  2. 使用视图重新格式化检索出的数据;
  3. 使用视图过滤不想要的数据。

例如,在视图v_student中查找classNo为“CS1401”的学生学号和姓名:

1
2
3
select studentNo, studentName
from db_school.v_student
where classNo = 'CS1401';

对视图的进一步说明

在MySQL中,对视图的使用还需要注意以下几点:

  1. 创建视图必须有足够的权限;
  2. 对于可以创建的视图数目没有限制;
  3. 视图可以嵌套,既可以利用从其他视图中检索数据的查询来构造一个视图。
  4. order by子句可以用在视图中。但如果从该视图检索数据的select语句也包含order by子句,那么该视图中的order by子句将被覆盖;
  5. 视图不能被索引,也不能有关联的触发器、默认值。
  6. 视图可以和表一起使用。例如,编写一条连接表和视图的select语句;
  7. 由于视图不包含数据,所以每次使用视图时都必须处理查询执行是所需的任何一个检索操作。倘若用多个连接和过滤条件创建来复杂的视图或者嵌套来视图,可能会发现系统运行性能下降得非常严重。因此,在部署使用了大量视图的应用前,应该进行性能测试。
# MySQL

评论

程振兴

程振兴 @czxa.top
截止今天,我已经在本博客上写了604.4k个字了!

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×