使用SQL语句创建视图1

1、任务任务5：创建视图和更新视图数据：创建视图和更新视图数据使用使用management studio创建视图；创建视图；使用使用CREATE VIEW语句创建视图；语句创建视图；视图的管理：查看视图信息、修改视图、删除视图、重命视图的管理：查看视图信息、修改视图、删除视图、重命名视图等；名视图等；使用视图对表数据操作：查询、添加、修改、删除等。使用视图对表数据操作：查询、添加、修改、删除等。第一部分第一部分创建视图创建视图1.视图概述视图概述视图是一种常用的数据库对象，是从视图是一种常用的数据库对象，是从一个或多个基表中导出的表，其结构一个或多个基表中导出的表，其结构和数据是对数据表进行查询的

2、结果。和数据是对数据表进行查询的结果。视图被定义后便存储在数据库中，通视图被定义后便存储在数据库中，通过视图看到的数据只是存放在基表中过视图看到的数据只是存放在基表中的数据。的数据。视图的概念视图的概念视图不是真实存在的基础表（基表），视图不是真实存在的基础表（基表），而是一张而是一张虚表。虚表。 通过视图看到的数据只是存放在基表通过视图看到的数据只是存放在基表中的数据中的数据 对视图的操作与对表的操作一样对视图的操作与对表的操作一样可以对其进行查询、修改（有一定的可以对其进行查询、修改（有一定的限制）和删除；限制）和删除； 可以在视图的基础上继续创建视图。可以在视图的基础上继续创建视图。2.

3、视图的用途视图的用途1简化查询操作简化查询操作用户可以将多个表用户可以将多个表中感兴趣的数据集中感兴趣的数据集中在一个视图中，中在一个视图中，通过查询视图查看通过查询视图查看多个表中的数据。多个表中的数据。 2提高安全性提高安全性 为用户创建一个视为用户创建一个视图，只将允许该用图，只将允许该用户查看的数据加入户查看的数据加入视图。再通过权限视图。再通过权限的设置，使该用户的设置，使该用户允许访问视图而不允许访问视图而不允许访问表。允许访问表。3便于数据的交换便于数据的交换 先将多个表中的数先将多个表中的数据集中到一个视图据集中到一个视图中，再将视图中的中，再将视图中的数据和其他类型数数据和其

4、他类型数据库中数据交换。据库中数据交换。 3.使用使用management studio创建视图创建视图“视图视图”-”新建视图新建视图”-“添加表添加表”1创建视图，查询库存不足的商品基本信息，结果包括创建视图，查询库存不足的商品基本信息，结果包括条形码、商品名称、库存数量。库存不足指库存数量少于条形码、商品名称、库存数量。库存不足指库存数量少于30。视图名为。视图名为库存不足商品库存不足商品。4.使用使用CREATE VIEW语句创建视图语句创建视图CREATE VIEW 视图名视图名 ( 视图列名视图列名1 ,.视图列名视图列名n ) WITH ENCRYPTION AS SELECT

5、语句语句WITH CHECK OPTIONWITH ENCRYPTION：对视图的定义进行加密。对视图的定义进行加密。WITH CHECK OPTION：迫使通过视图执行的所有数据迫使通过视图执行的所有数据修改语句必须符合视图定义中设置的条件。修改语句必须符合视图定义中设置的条件。视图列名视图列名列名如未在列名如未在CREATE VIEW后指明，则由查询语句中列字后指明，则由查询语句中列字段组成。段组成。必须明确指明组成视图的所有列名的三种情况必须明确指明组成视图的所有列名的三种情况：其中某个目标列不是单纯的列名，而是列表达式或聚其中某个目标列不是单纯的列名，而是列表达式或聚合函数；合函数；多

6、表连接时，选出了几个同名的列作为视图的字段；多表连接时，选出了几个同名的列作为视图的字段；需要在视图中为某个列启用新的名字需要在视图中为某个列启用新的名字 。-1创建视图，查询库存不足的商品基本信息，结果包创建视图，查询库存不足的商品基本信息，结果包括条形码、商品名称、库存数量。库存不足指库存数量少括条形码、商品名称、库存数量。库存不足指库存数量少于于30。视图名为。视图名为库存不足商品库存不足商品。create view 库存不足商品库存不足商品asselect 条形码条形码,商品名称商品名称,库存数量库存数量from 商品商品where 库存数量库存数量30go-2创建视图，查询商品的销售

7、详细信息，结果包括销售号、销售创建视图，查询商品的销售详细信息，结果包括销售号、销售时间、是否会员、支付方式、会员号、条形码、销售数量。视图名为时间、是否会员、支付方式、会员号、条形码、销售数量。视图名为商品销售详细信息商品销售详细信息。create view 商品销售详细信息商品销售详细信息asselect 销售销售.销售号销售号,销售时间销售时间,是否会员是否会员,支付方式支付方式,会员号会员号,条形码条形码,销售销售数量数量from 销售销售,进行销售进行销售where 进行销售进行销售.销售号销售号=销售销售.销售号销售号go5.查看视图的定义脚本查看视图的定义脚本EXECUTE SP

8、_HELPTEXT 视图名视图名注意：如果视图定义中使用注意：如果视图定义中使用WITH ENCRYPTION，则无，则无法查看定义脚本。法查看定义脚本。7. 重命名视图重命名视图EXECUTE SP_RENAME 旧视图名旧视图名,新视图名新视图名8.删除视图删除视图删除一个视图，就是删除视图的定义及其赋予的全部权限删除一个视图，就是删除视图的定义及其赋予的全部权限,而原先通过视图获得的而原先通过视图获得的数据并没有被删除数据并没有被删除。使用使用DROP VIEW语句删除：语句删除：DROP VIEW 视图名视图名第二部分第二部分更新视图数据更新视图数据1.通过视图查询表数据通过视图查询表

9、数据当定义视图以后，用户就当定义视图以后，用户就可以像对基础表进行查询一样对可以像对基础表进行查询一样对视图进行查询视图进行查询了。了。 可像基础表一样用在可像基础表一样用在FROM子句中作为数据来源。子句中作为数据来源。 SELECT 列名或表达式列名或表达式 FROM 表名或视图名表名或视图名 WHERE 行条件表达式行条件表达式 GROUP BY 列名列名 HAVING 组条件表达式组条件表达式 ORDER BY 列名列名 ASC|DESC1重新创建视图重新创建视图目前商品价格目前商品价格2，查询各商品目前的零，查询各商品目前的零售价和会员价，结果包括条形码、商品名称、零售价、会员售价和

10、会员价，结果包括条形码、商品名称、零售价、会员价。价。提示：通过视图提示：通过视图目前商品价格目前商品价格1和商品表创建。和商品表创建。将目前商品价格将目前商品价格1当成一张表来使用，当成一张表来使用，要查找条形码、商品名称、零售价、会员价要查找条形码、商品名称、零售价、会员价将目前商品价格将目前商品价格1和商品表连接起来和商品表连接起来select dbo.商品商品.条形码条形码,商品名称商品名称,零售价零售价,会员价会员价from dbo.目前商品价格目前商品价格,dbo.商品商品where 目前商品价格目前商品价格.条形码条形码=商品商品.条形码条形码create view 目前商品价格

11、目前商品价格2 asselect 商品商品.条形码条形码,商品名称商品名称,零售价零售价,会员价会员价from 目前商品价格目前商品价格1,商品商品where 目前商品价格目前商品价格1.条形码条形码=商品商品.条形码条形码2.通过视图更新表数据通过视图更新表数据实质实质对视图所引用的基础表执行数据的插入和更新对视图所引用的基础表执行数据的插入和更新 。视图中本没有数据，往视图中添加、删除、修改数据视图中本没有数据，往视图中添加、删除、修改数据，都要转化为表中的数据的修改，如果不能转化，就，都要转化为表中的数据的修改，如果不能转化，就不能修改。所以是受限制的。不能修改。所以是受限制的。相关限制

12、相关限制在一个语句中，一次不能修改一个以上的视图基表；在一个语句中，一次不能修改一个以上的视图基表；对视图中所有列的修改必须遵守视图基表中所定义对视图中所有列的修改必须遵守视图基表中所定义的各种数据约束条件；的各种数据约束条件；不允许对视图中的计算列进行修改，也不允许对不允许对视图中的计算列进行修改，也不允许对视图定义中包含有统计函数或视图定义中包含有统计函数或GROUP BY子句的子句的视图进行插入或修改操作。视图进行插入或修改操作。2往往水果类商品信息水果类商品信息视图中添加一条记录：条形码视图中添加一条记录：条形码为：为：907992101279，商品名称为：，商品名称为：伊利红枣酸牛奶

13、伊利红枣酸牛奶，库存数量为：，库存数量为：0，商品类型为：，商品类型为：牛奶牛奶。思考：是否能添加成功？如果出错，为什么？思考：是否能添加成功？如果出错，为什么？消息消息550，级别，级别16，状态，状态1，第，第1 行行试图进行的插入或更新已失败，原因是目标视图或者目标试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了视图所跨越的某一视图指定了WITH CHECK OPTION，而该操作的一个或多个结果行又不符合而该操作的一个或多个结果行又不符合CHECK OPTION 约束。约束。语句已终止。语句已终止。-不能添加成功，不能添加成功，因为创建视图时使用因为创建视图

14、时使用With check option,通过视图执行的所有数据修改语句通过视图执行的所有数据修改语句必须符合视图定义中设置的条件必须符合视图定义中设置的条件,即商品类型即商品类型=水果水果。如果如果水果类商品信息水果类商品信息视图定义时没有视图定义时没有With check option，能添加成功，只是加在商品表上。，能添加成功，只是加在商品表上。水果类商品信息水果类商品信息视图显示的是水果类，所以不显示伊视图显示的是水果类，所以不显示伊利红枣酸牛奶的记录利红枣酸牛奶的记录3往往商品销售详细信息商品销售详细信息视图中添加一条记录，销售号：视图中添加一条记录，销售号：101-106-45，销售时间：，销售时间：2010-4-18，是否会员：，是否会员：0，支付，支付方式：方式：现金现金 ，条形码：，条形码：2000000341316，销售数量：，销售数量：5。思考：能否添加成功？如果出错，为什么？思考：能否添加成功