sqlserver2008数据库应用与开发教学课件作者李新德第四章

第四章 视图和游标,项目五 创建并使用视图 项目六 声明并使用游标,返回,项目五 创建并使用视图,一、视图的概念 视图是从一个或多个表（或视图）中映射出的虚表。其包括三个含义：其一，它是虚表，不是具有物理存在的表，即在数据库中只存储其定义，没有实际的存储空间；其二，它来自一个或多个表（或视图），这些表称为视图的基本表；其三，视图是映射出的，意思是从基本表中以某种特殊的视角“看到”的“风景”，非常形象，故称视图，如何“看”，则是形成视图的关键。 对于相同的基本表，不同的视图实际上就是不同的“视角”定义。,下一页,返回,项目五 创建并使用视图,视图一经定义，就可以像表一样被查询、修改、删除和更新。 视图有很多优点，主要表现在： （1） 屏蔽了数据库的复杂性。定义视图简化了信息量，使得对数据的处理更加简单和快捷。 （2） 便于数据的集中。视图可以将原先分散在几处的数据集中在一起，使原来几个表的组合查询等处理变成了对一个视图的处理。 （3） 简化了用户权限管理。将用户有权限的不同表中的数据列归在一个视图中，简化了数据安全管理。 （4） 便于数据的共享。,上一页,下一页,返回,项目五 创建并使用视图,将各用户可共享的数据放在一起，便于共享和存储。 （5） 便于重组数据表。实际的表已经被固定了，但需要是各种各样的，用视图可以重组数据以解决各种需要。 使用视图的限制： （1） 只能在当前数据库中才能创建视图。视图的命名必须遵循标识符命名规则，不能与表同名。 （2） 在视图上不能定义规则、默认值和触发器。 现实中使用视图的意义主要在于集中数据和简化查询，因此通过本项目主要掌握视图的创建和视图的查询，而视图的其他功能如：视图数据的插入、修改、删除等一般情况下较少使用，且有不少限制，有时不如直接对基本表操作，对此，只要熟悉和了解即可。,上一页,下一页,返回,项目五 创建并使用视图,任务 视图的创建和查询 【知识准备】 创建视图： 使用CREATE VIEW 语句可以创建视图，例如： use STUDY GO CREATE VIEW View_student as,上一页,下一页,返回,项目五 创建并使用视图,select studentid,name,sex,birthday,specialty from tb_student where specialty=计算机软件 创建视图的简单语法格式为： CREATE VIEW() as select 语句可以针对一个或多个表或视图，对创建视图中的SELECT 语句还有如下限制： （1） select 语句中不能使用compute 或compute by 子句；,上一页,下一页,返回,项目五 创建并使用视图,（2） select 语句中不能使用order by 子句； （3） select 语句中不能使用into 子句； （4） select 语句中不能使用临时表或表的变量。 用语法来创建视图时可以通过在“CREATE VIEW视图名”后面定义别名来替换来自基本表的列名。 【任务实施】 一、视图的界面操作 视图的界面操作包括视图的创建、更新和删除，以及对视图中数据的查询、插入和删除等，这些操作均可在SQL Server 2008 数据库管理系统的“对象资源管理器”中完成。,上一页,下一页,返回,项目五 创建并使用视图,下面是使用界面方式创建视图的主要步骤： （1） 启动SQL Server management Studio，在“对象资源管理器”中展开“数据库”，展开“STUDY”，用鼠标右键单击“视图”下的“新建视图”，如图41 所示。 （2） 在弹出的“添加表”窗口中，添加所需要的基本表或视图等，这里选择“表”选项卡，选择表“tb_student”，单击“添加”按钮，如图42 所示，如果还需添加其他表，可继续选中表并按“添加”按钮，若不需要可单击“关闭”按钮，关闭此弹出窗口。,上一页,下一页,返回,项目五 创建并使用视图,（3） 在添加完基本表后，视图窗口中显示基本表的所有列，如图43 所示，可以用鼠标选择所需要的列，可以看到随着用鼠标选择列，其他子窗口中也在同步组织。 （4） 在中间子窗口中，“别名”栏可以在视图中不用原名，“排序类型”栏可以指定列的排序方式，而“筛选器”栏则指示了创建本视图的映射规则，也就是构成条件，在专业列“specialty”对应的“筛选器”栏中输入“计算机软件”，按回车键，可看到中间子窗口下面的子窗口中构建的语句，为一条普通的带条件的select 语句。 （5） 完成构建后，单击工具栏中的“保存”按钮，输入创建的视图名“view_student”，单击“确定”按钮，便完成了视图的创建。,上一页,下一页,返回,项目五 创建并使用视图,视图创建完后，可在“对象资源管理器”中的“STUDY”“视图”中看到刚刚创建的视图“dbo.View_student”，用鼠标右键单击选择“设计”（图44）可回到图43 所示的构建视图窗口进行修改更新，选择“打开视图”可看到视图这个虚表中的数据，如图45 所示，选择“删除”可删除本视图，非常方便。 对视图的数据查询、插入和删除的界面操作，同对待通常的表是一样的。当对视图的数据进行插入时，基本表中的数据也同时得到了插入（但其他字段的属性必须是可空），反之亦然；当对视图的数据进行删除时，基本表中的数据也同时被删除了，反过来也是。,上一页,下一页,返回,项目五 创建并使用视图,这是因为视图不是一个真实的表，而仅是一条select 语句，一个语法定义，在对视图进行操作时，实际上是在对基本表进行操作。 二、视图的语法使用 1. 创建视图 【例4.1】 创建视图View_student_male，构建条件是选择计算机软件专业的男学生的学号、姓名，还要求包含其选修的课程号及成绩。 use STUDY GO CREATE VIEW View_student_male as,上一页,下一页,返回,项目五 创建并使用视图,select tb_student.studentid,name,courseid,score from tb_student,tb_score where specialty='计算机软件' and sex=1 and tb_student.studentid=tb_score.studentid 2. 查询视图数据 创建视图后，就可以如同查询真实表一样对视图数据进行查询了。 3. 插入视图数据 向视图中插入数据类似于对表数据的插入，使用insert into 语句。 向视图中插入数据后，基本表中的数据也同时得到了插入（但基本表的其他字段的属性必须是可空）。,上一页,下一页,返回,项目五 创建并使用视图,另外，当视图的映射来自多个表时，不能向该视图中插入数据，因为这将会影响多个表。 4. 修改视图数据 与真实表一样，使用update 语句可以对视图数据进行修改，基本表也同时被修改。 若视图的映射来自多个表，则修改该视图一次只能变动一个基本表的数据。 5. 删除视图数据 使用delete 语句可以删除视图和基本表中的记录。,上一页,下一页,返回,项目五 创建并使用视图,若视图的映射来自多个表，不能使用delete 语句来删除数据，因为删除操作会影响多个基表。 6. 修改视图、删除视图 修改视图、删除视图使用的语句与对待真实表是类似的，修改视图用ALTER VIEW 语句，删除视图用DROP VIEW 语句，平时用不到，这里省去不讲，有兴趣的同学可自己去查阅。,上一页,返回,项目六 声明并使用游标,游标的概念 在关系数据库中，由select 语句返回的结果通常是包含多个记录的结果集，但是，在有嵌入select 语句的应用程序开发过程中，并不总是能将整个结果集作为一个单元来有效地处理，而常常需要一种机制，以便每次处理结果集中的一行或一部分行。游标就提供了这种处理机制。 可以将游标简单地理解为一种“整取零花”机制。游标还是一种特殊的指针，它可以指向结果集中的任何位置，以便逐条处理指向位置的数据。,下一页,返回,项目六 声明并使用游标,使用游标所遵循的一般步骤如下： （1） 声明游标； （2） 打开游标； （3） 读取游标； （4） 处理游标指向的数据； （5） 关闭游标； （6） 释放游标。 游标主要用于存储过程、触发器和嵌入TSQL 脚本。,上一页,下一页,返回,项目六 声明并使用游标,任务 游标的使用 【知识准备】 一、游标的声明 语法： DECLARECURSOR STATIC|DYNAMIC FOR select 语句 其中： STATIC静态游标；,上一页,下一页,返回,项目六 声明并使用游标,DYNAMIC动态游标。 通常，在无参数的情况下声明的游标称为只进游标，在具有以上两种参数的情况下声明的游标称为可进退游标。 （1） 静态游标：select 语句所在的表若数据发生变化，游标结果集的内容不变。 （2） 动态游标：select 语句所在的表若数据发生变化，游标结果集的内容同步变化。 不同的游标类型，其游标指针可移动的方向及select 语句变化引起的数据变化也不同。游标类型与游标移动方向见表41。,上一页,下一页,返回,项目六 声明并使用游标,二、游标的使用 1. 打开游标 语法： OPEN 2. 读取游标 语法： FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE n |RELATIVE n from,上一页,下一页,返回,项目六 声明并使用游标,INTO 变量,n 其中： NEXT返回结果集中当前行之后的行，并将其作为当前行，若FETCH NEXT 为对游标的第一次取数，则返回结果集中的第一行。 PRIOR返回结果集中当前行之前的行，并将其作为当前行，若FETCH PRIOR 为对游标的第一次取数，则没有行返回，且游标指针置于第一行之前。 FIRST返回结果集中的第一行，并将其作为当前行。 LAST返回结果集中的最后一行，并将其作为当前行。,上一页,下一页,返回,项目六 声明并使用游标,ABSOLUTE n返回结果集中从第一行数起的第n 行，并将其作为当前行，如果n 为负数，则返回结果集中从末尾行数起的第n 行，并将其作为当前行。 RELATIVE n返回结果集中从当前行向前或向后数起的第n 行，并将其作为当前行。 INTO将游标取出的数据赋给后面的变量。 不同类型的游标在读取游标数据时允许使用的参数也不同，表42 列出了具体的允许参数。 3. 关闭游标,上一页,下一页,返回,项目六 声明并使用游标,语法： CLOSE 4. 释放游标 语法： DEALLOCATE 【任务实施】 1. 游标的声明 【例4.7】为学生表声明一个游标。 DECLARE Cur_1 CURSOR,上一页,下一页,返回,项目六 声明并使用游标,FOR select * from tb_student 本例没有参数，是一个只进游标。 2. 游标的使用 下面通过几个例子说明如何读取游标数据。 【例4.9】从游标Cur_1 中读取数据。 OPEN Cur_1 FETCH NEXT from Cur_1 本例打开游标Cur_1，读取结果集中的第一行，执行的结果如图47 所示。,上一页,下一页,返回,项目六 声明并使用游标,【例4.10】从游标Cur_2 中读取最后一行和当前行的上三行。 OPEN Cur_2 FETCH LAST from Cur_2 打开游标Cur_2，读取结果集中的最后一行，执行的结果如图48 所示。 FETCH RELATIVE3 from Cur_2 读取结果集中当前行的上三行，执行的结果如图49 所示。,上一页,返回,图41 新建视图,返回,图42 添加基本表,返回,图43 构建视图,返回,图44 视图对应的其他界面操作功能,返回,图45 视图这个虚表中的数据,返回,表41 游标类型与游标移动方向,返回,表4