数据库的视图
视图概念
前面已经提到过视图(View),这一节专门讨论视图的概念、定义和操作。
视图是从一个或多个表(或视图)导出的表。视图是数据库的用户使用数据库的观点。例如,对于一个学校,其学生的情况存于数据库的一个或多个表中,而作为学校的不同职能部门,所关心的学生数据的内容是不同的。即使是同样的数据,也可能有不同的操作要求,于是就可以根据他们的不同需求,在物理的数据库上定义他们对数据库所要求的数据结构,这种根据用户观点所定义的数据结构就是视图。
视图与表(有时为与视图区别,也称表为基本表——Base Table)不同,视图是一个虚表,即视图所对应的数据不进行实际存储,数据库中只存储视图的定义,对视图的数据进行操作时,系统根据视图的定义去操作与视图相关联的基本表。
视图一经定义以后,就可以像表一样被查询、修改、删除和更新。使用视图有下列优点:
(1)为用户集中数据,简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中,定义视图可将它们集中在一起,从而方便用户的数据查询和处理。
(2)屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构,并且数据库表的更改也不影响用户对数据库的使用。
(3)简化用户权限的管理。只需授予用户使用视图的权限,而不必指定用户只能使用表的特定列,也增加了安全性。
(4)便于数据共享。各用户不必都定义和存储自己所需的数据,可共享数据库的数据,这样同样的数据只需存储一次。
(5)可以重新组织数据以便输出到其他应用程序中。
MySQL 在5.0版中就已经实现了视图功能(包括可更新视图)。
1. 使用CREATE VIEW语句创建视图
语法格式:
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
说明:
● view_name:视图名。
● column_list:要想为视图的列定义明确的名称,可使用可选的column_list子句,列出由逗号隔开的列名。column_list中的名称数目必须等于SELECT语句检索的列数。若使用与源表或视图中相同的列名时可以省略column_list。
● OR REPLACE:给定了OR REPLACE子句,语句能够替换已有的同名视图。
● ALGORITHM子句:可选的ALGORITHM子句是对标准SQL的MySQL扩展,规定了MySQL的算法,算法会影响MySQL处理视图的方式。ALGORITHM可取3个值:MERGE、TEMPTABLE或UNDEFINED。如果没有ALGORITHM子句,默认算法是UNDEFINED(未定义的)。指定了MERGE选项,会将引用视图的语句的文本与视图定义合并起来,使得视图定义的某一部分取代语句的对应部分。MERGE算法要求视图中的行和基表中的行具有一对一的关系,如果不具有该关系,必须使用临时表取而代之。指定了TEMPTABLE选项,视图的结果将被置于临时表中,然后使用它执行语句。
● select_statement:用来创建视图的SELECT语句,可在SELECT语句中查询多个表或视图。但对SELECT语句有以下的限制:
(1)定义视图的用户必须对所参照的表或视图有查询(即可执行SELECT语句)权限;
(2)不能包含FROM子句中的子查询;
(3)不能引用系统或用户变量;
(4)不能引用预处理语句参数;
(5)在定义中引用的表或视图必须存在;
(6)若引用不是当前数据库的表或视图时,要在表或视图前加上数据库的名称;
(7)在视图定义中允许使用ORDER BY,但是,如果从特定视图进行了选择,而该视图使用了具有自己ORDER BY的语句,则视图定义中的ORDER
BY将被忽略。
(8)对于SELECT语句中的其他选项或子句,若视图中也包含了这些选项,则效果未定义。例如,如果在视图定义中包含LIMIT子句,而SELECT语句使用了自己的LIMIT子句,MySQL对使用哪个LIMIT未做定义。
● WITH CHECK OPTION:指出在可更新视图上所进行的修改都要符合select_statement所指定的限制条件,这样可以确保数据修改后,仍可通过视图看到修改的数据。当视图是根据另一个视图定义的时,WITH
CHECK OPTION给出两个参数:LOCAL和CASCADED。它们决定了检查测试的范围。LOCAL关键字使CHECK
OPTION只对定义的视图进行检查,CASCADED则会对所有视图进行检查。如果未给定任一关键字,默认值为CASCADED。
注意,使用视图时,要注意下列事项:
(1)在默认情况下,将在当前数据库创建新视图。要想在给定数据库中明确创建视图,创建时,应将名称指定为db_name.view_name。
(2)视图的命名必须遵循标志符命名规则,不能与表同名,且对每个用户视图名必须是唯一的,即对不同用户,即使是定义相同的视图,也必须使用不同的名字。
(3)不能把规则、默认值或触发器与视图相关联。
(4)不能在视图上建立任何索引,包括全文索引。
假设当前数据库是TEST,创建XSCJ数据库上的CS_KC视图,包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都要符合专业名为计算机这个条件。
CREATE OR REPLACE VIEW XSCJ.CS_KC
AS
SELECT XS.学号,课程号,成绩
FROM XSCJ.XS, XSCJ.XS_KC
WHERE XS.学号 = XS_KC.学号 AND XS.专业名 = '计算机'
WITH CHECK OPTION;
创建XSCJ数据库上的计算机专业学生的平均成绩视图CS_KC_AVG,包括学号(在视图中列名为num)和平均成绩(在视图中列名为score_avg)。
USE XSCJ
CREATE VIEW CS_KC_AVG(num, score_avg)
AS
SELECT 学号,AVG(成绩)
FROM CS_KC
GROUP BY
学号;
说明:这里SELECT语句直接从CS_KC视图中查询出结果。
视图定义后,就可以如同查询基本表那样对视图进行查询。
在视图CS_KC中查找计算机专业的学生学号和选修的课程号。
SELECT 学号,
课程号
FROM CS_KC
查找平均成绩在80分以上的学生的学号和平均成绩。
本例首先创建学生平均成绩视图XS_KC_AVG,包括学号(在视图中列名为num)和平均成绩(在视图中列名为score_avg)。
创建学生平均成绩视图XS_KC_AVG:
CREATE VIEW XS_KC_AVG ( num,score_avg )
AS
SELECT 学号, AVG(成绩)
FROM XS_KC
GROUP BY
学号;
再对XS_KC_AVG视图进行查询。
SELECT *
FROM XS_KC_AVG
WHERE score_avg>=80;
执行结果为:
从以上两例可以看出,创建视图可以向最终用户隐藏复杂的表连接,简化了用户的SQL程序设计。
注意:使用视图查询时,若其关联的基本表中添加了新字段,则该视图将不包含新字段。例如,视图CS_XS中的列关联了XS表中所有列,若XS表新增了“籍贯”字段,那么CS_XS视图中将查询不到“籍贯”字段的数据。
如果与视图相关联的表或视图被删除,则该视图将不能再使用。
查询视图也可以在MySQL Query Browser查询工具中进行,方法与查询表数据类似。
1. 可更新视图
要通过视图更新基本表数据,必须保证视图是可更新视图,即可以在INSET、UPDATE或DELETE等语句当中使用它们。对于可更新的视图,在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构,这类结构会使得视图不可更新。如果视图包含下述结构中的任何一种,那么它就是不可更新的:
(1)聚合函数;
(2)DISTINCT关键字;
(3)GROUP BY子句;
(4)ORDER BY子句;
(5)HAVING子句;
(6)UNION运算符;
(7)位于选择列表中的子查询;
(8)FROM子句中包含多个表;
(9)SELECT语句中引用了不可更新视图;
(10)WHERE子句中的子查询,引用FROM子句中的表;
(11)ALGORITHM
选项指定为TEMPTABLE(使用临时表总会使视图成为不可更新的)。
2. 插入数据
使用INSERT语句通过视图向基本表插入数据,有关INSERT语句的语法介绍见第 3章。
创建视图CS_XS,视图中包含计算机专业的学生信息,并向CS_XS视图中插入一条记录:('081255','李牧','计算机',1,'1990-10-21',50,NULL,NULL)。
首先创建视图CS_XS:
CREATE OR REPLACE VIEW CS_XS
AS
SELECT *
FROM XS
WHERE
专业名 = '计算机'
WITH CHECK OPTION;
注意:在创建视图的时候加上WITH CHECK OPTION子句,是因为WITH CHECK OPTION子句会在更新数据的时候检查新数据是否符合视图定义中WHERE子句的条件。WITH
CHECK OPTION子句只能和可更新视图一起使用。
接下来插入记录:
INSERT INTO CS_XS
VALUES('081255', '李牧', '计算机', 1, '1990-10-14', 50, NULL, NULL);
注意:这里插入记录时专业名只能为“计算机”。
这时,使用SELECT语句查询CS_XS视图和基本表XS,就可发现XS表中该记录已经被添加。
当视图所依赖的基本表有多个时,不能向该视图插入数据,因为这将会影响多个基本表。例如,不能向视图CS_KC插入数据,因为CS_KC依赖两个基本表:XS和XS_KC。
对INSERT语句还有一个限制:SELECT语句中必须包含FROM子句中指定表的所有不能为空的列。例如,若CS_XS视图定义的时候不加上“姓名”字段,则插入数据的时候会出错。
3. 修改数据
使用UPDATE语句可以通过视图修改基本表的数据,有关UPDATE语句的语法介绍见第3章。
将CS_XS视图中所有学生的总学分增加8。
UPDATE CS_XS
SET 总学分 =
总学分+ 8;
该语句实际上是将CS_XS视图所依赖的基本表XS中所有记录的总学分字段值在原来基础上增加8。
若一个视图依赖于多个基本表,则一次修改该视图只能变动一个基本表的数据。
将CS_KC视图中学号为081101的学生的101课程成绩改为90。
UPDATE CS_KC
SET 成绩=90
WHERE 学号='081101' AND
课程号='101';
本例中,视图CS_KC依赖于两个基本表:XS和XS_KC,对CS_KC视图的一次修改只能改变学号(源于XS表)或者课程号和成绩(源于XS_KC表)。
以下的修改是错误的:
UPDATE CS_KC
SET 学号='081120',课程号='208'
WHERE 成绩=90;
4. 删除数据
使用DELETE语句可以通过视图删除基本表的数据,有关DELETE语句的语法介绍见第3章。
删除CS_XS中女同学的记录。
DELETE FROM CS_XS
WHERE 性别 = 0;
注意:对依赖于多个基本表的视图,不能使用DELETE语句。例如,不能通过对CS_KC视图执行DELETE语句而删除与之相关的基本表XS及XS_KC表的数据。
使用ALTER语句可以对已有视图的定义进行修改。
语法格式:
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
ALTER VIEW语句的语法和CREATE VIEW类似,这里不过多叙述。
将CS_XS视图修改为只包含计算机专业学生的学号、姓名和总学分。
USE XSCJ
ALTER VIEW CS_XS
AS
SELECT 学号,姓名,总学分
FROM XS
WHERE
专业名 = '计算机';
1. 使用SQL语句删除视图
语法格式:
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
其中,view_name是视图名,声明了IF EXISTS,若视图不存在的话,也不会出现错误信息。也可以声明RESTRICT和CASCADE,但它们没什么影响。
使用DROP VIEW一次可删除多个视图。例如:
DROP VIEW CS_KC, CS_XS;
将删除视图CS_KC和CS_XS。
2. 使用MySQL Administrator删除视图
MySQL Administrator中删除视图的操作方法是:展开数据库和视图,单击需要删除的视图,选择下方的选项“Drop View”,出现如图4.34所示的对话框,在其中单击“Drop
View(s)”按钮即可删除指定的视图。
分享到:
相关推荐
那些年,与你同分同位次的同学都去了哪里?全国各大学在四川2020-2024年各专业最低录取分数及录取位次数据,高考志愿必备参考数据
麒麟win10双系统重新安装win10后麒麟启动菜单看不到解决方法
多邻国Duolingo v6.0.3 高级版.apk
QT网络编程: 实现TCP通讯设置(客户端)
减少重复造轮子,开源微信小程序商城(前后端开源:uniapp+Java)。快速搭建一个属于自己的微信小程序商城。
彩虹云商城 最新彩虹代刷V6.9.0免授权纯净完整版 直接上传源码解压缩后访问域名安装即可,亲测可用 彩虹自助下单系统 安装说明: 上传到空间后直接访问即可根据提示安装。 PHP推荐使用7.0及以上版本 V6.9 1.修复SQL注入漏洞 2.修复后台微信QQ扫码登录 V6.8.5 1.修复亿乐对接 2.新增支持倍数输入框 V6.8 1.更新全新的faka模板 2.新增微信快捷登录 3.新增批量下单功能 4.防CC配置新增滑动验证码模式 5.修复部分地区后台加载错误 6.修复https网站对接http支付接口 7.后台登录支持微信QQ扫码登录
MyBatis-Plus学习思维导图
那些年,与你同分同位次的同学都去了哪里?全国各大学在四川2020-2024年各专业最低录取分数及录取位次数据,高考志愿必备参考数据
那些年,与你同分同位次的同学都去了哪里?全国各大学在四川2020-2024年各专业最低录取分数及录取位次数据,高考志愿必备参考数据
分布式搜索引擎ElasticSearch思维导图
网鼎杯
网络安全入门教程(工具版)
科普里控制器调试软件工具使用 win64环境安装
内容概要:本文档详细介绍了GC9503V单片机a-Si TFT LCD驱动器的技术规格,包括主要特点、内部结构图、引脚定义以及系统接口等。GC9503V支持480x864分辨率,16.7百万色显示,无内置GRAM。文章还提供了详细的引脚尺寸、对齐标记尺寸、芯片信息以及接口模式控制的序列实例,如DCS写入命令及其参数。 适合人群:LCD显示屏设计人员、嵌入式系统工程师、电子硬件开发者和技术研究人员。 使用场景及目标:帮助开发者快速理解和应用GC9503V在实际产品中的具体使用方法,掌握LCM与MCU之间的数据交互方式,实现高效的屏幕驱动设计。 其他说明:GalaxyCore公司保留在不事先通知的情况下更改文档内容的权利。
那些年,与你同分同位次的同学都去了哪里?全国各大学在四川2020-2024年各专业最低录取分数及录取位次数据,高考志愿必备参考数据
yolo系列算法目标检测数据集,包含标签,可以直接训练模型和验证测试,数据集已经划分好,适用yolov5,yolov8,yolov9,yolov7,yolov10,yolo11算法; 包含两种标签格:yolo格式(txt文件)和voc格式(xml文件),分别保存在两个文件夹中; yolo格式:<class> <x_center> <y_center> <width> <height>, 其中: <class> 是目标的类别索引(从0开始)。 <x_center> 和 <y_center> 是目标框中心点的x和y坐标,这些坐标是相对于图像宽度和高度的比例值,范围在0到1之间。 <width> 和 <height> 是目标框的宽度和高度,也是相对于图像宽度和高度的比例值
那些年,与你同分同位次的同学都去了哪里?全国各大学在四川2020-2024年各专业最低录取分数及录取位次数据,高考志愿必备参考数据
某酒店排水课程设计计算书.doc
那些年,与你同分同位次的同学都去了哪里?全国各大学在四川2020-2024年各专业最低录取分数及录取位次数据,高考志愿必备参考数据
那些年,与你同分同位次的同学都去了哪里?全国各大学在四川2020-2024年各专业最低录取分数及录取位次数据,高考志愿必备参考数据