第12章 视图和游标

第12章视图和游标Page2本章要点视图概述创建、修改、删除和重命名视图使用视图修改数据SQLServer编程基础游标概述使用游标管理数据Page3视图概述视图并不是表，只是由查询语句所定义的一个虚拟表。Page4视图概述视图的应用主要是基于以下考虑：着重于特定的数据简化查询安全性简化对切分数据的访问注意视图中没有存储任何数据，视图仅仅是一种访问数据库表中数据的方式。Page5创建视图基本语法CREATEVIEW[schema_name.]view_name[(column[,...])][WITHview_option[,...]]ASselect_statement[WITHCHECKOPTION]Page6创建视图基本语法view_option::={ENCRYPTION|SCHEMABINDING|VIEW_METADATA}Page7创建视图基本语法：view_option参数说明ENCRYPTION对创建视图的SQL语句进行加密若不设置该参数，可以使用sp_helptext查看视图的创建语句SCHEMABINDING将视图绑定到架构（schema）上必须指定select_statement中引用表或视图的架构名，即：schema_name.table_or_view_namePage8创建视图基本语法：view_option参数说明VIEW_METADATA当引用视图的查询请求浏览模式的元数据（METADATA）时，SQLServer将返回有关视图的元数据，而不返回基表的元数据描述性的数据Page9创建视图基本语法WITHCHECKOPTION强制要求通过视图修改的数据记录依然满足select_statement中的条件，依然出现在视图中思考创建一个视图，其select_statement如下，能不能通过修改该视图使得：stuAge=18SELECTstuNo,stuName,stuAgeFROMStudentWHEREstuAge18Page10创建视图实例分析提出问题BigCollege选课管理人员需要经常了解学生选课情况，主要包括学生学号、姓名、专业名称、所选课程名和任课老师信息，在不创建新表的前提下，提出一种解决方案，满足管理人员的日常查询要求。Page11创建视图实例分析分析问题管理人员需要查询的信息来源于不同的数据表，需要进行数据表连接，属于有一定复杂度的查询动作该查询属于日常行为，会频繁地进行不允许创建新表Page12创建视图实例分析问题求解编写查询语句Page13创建视图实例分析问题求解编写视图创建语句CREATEVIEWvSelectCourseInfo(学号,学生姓名,专业,所选课程,任课教师)ASSELECTs.stuNo,s.stuName,majorName,classCourseName,classTeacherNameFROMRegisterrJOINClassescONr.classNo=c.classNoJOINStudentsONs.stuNo=r.stuNoJOINMajormONs.stuMajorNo=m.MajorNoPage14创建视图实例分析结果验证查询视图使用sp_helptext查看创建视图的语句Page15思考创建视图vOldStudentClass，查询所有年龄在19岁以上学生的选课情况，包括字段学号，姓名，年龄，所选课程和总学分字段。Page16修改视图基本语法ALTERVIEW[schema_name.]view_name[(column[,...])][WITHview_option[,...]]ASselect_statement[WITHCHECKOPTION]Page17修改视图实例分析提出问题选课管理员希望修改视图vSelectCourseInfo--加入学生总学分字段--去掉教师名称字段--对创建视图语句进行加密Page18修改视图实例分析分析问题添加Student表的stuTotalCredit字段到视图中删除视图中的classTeacherName字段增加ENCRYPTION参数Page19修改视图实例分析问题求解修改查询语句Page20修改视图实例分析问题求解修改视图创建语句ALTERVIEWvSelectCourseInfo(学生编号,学生姓名,总学分,专业,所选课程)WITHENCRYPTIONASSELECTs.stuNo,stuName,stuTotalCredit,majorName,classCourseNameFROMRegisterrJOINClassescONr.classNo=c.classNoJOINStudentsONs.stuNo=r.stuNoJOINMajormONs.stuMajorNo=m.MajorNoPage21修改视图实例分析结果验证查询视图使用sp_helptext验证加密Page22思考修改视图vOldStudentClass，添加学生所属院系字段和CHECK选项，并举例验证该选项效果。Page23重命名视图基本语法sp_rename‘old_name’,‘new_name’[,‘object_type’]Page24重命名视图具体实例重命名视图sp_rename‘vSelectCourseInfo’,’vSelCourInfo’重命名表中的列sp_rename‘Student.stuMajorNo’,’majorNo’,’COLUMN’Page25删除视图基本语法DROPVIEWview_name[,…]具体实例DROPVIEWvSelectCourInfoDROPVIEWvDepartmentClass,vDepMajor,vHighCreditStudentPage26思考删除视图vOldStudentClass。Page27通过视图更新数据可更新视图在定义视图的SELECT列表中不能包含聚合函数。在定义视图的SELECT语句中不能使用GROUPBY或HAVING子句。单个INSERT、UPDATE或DELETE语句更新视图时，发生变化的数据只能来自一个源表。Page28通过视图更新数据实例分析提出问题创建一个查看学生所选课程的视图vStudentSelectCourse：现需要通过视图将学号为S004的学生田洋的专业修改为M008，而且其计算机网络课程成绩有误，应修改为85。Page29通过视图更新数据实例分析分析问题分析后得出，需要修改的stuMajorNo字段来自于源表Student，score字段来自于源表Register，所以，必须分别编写UPDATE语句更新这两个字段的内容。Page30通过视图更新数据实例分析问题求解编写两条UPDATE语句如下：UPDATEvStudentSelectCourseSETstuMajorNo='M008'WHEREstuNo='S004'UPDATEvStudentSelectCourseSETscore=85WHEREstuNo='S004'Page31通过视图更新数据实例分析结果验证查询视图Page32思考更新视图vOldStudentClass，将学号为“S024”的学生年龄增加1，所选课程改为“C012”。Page33Transact-SQL编程基础常量字符串常量二进制常量Datetime常量Integer常量Decimal常量Float和Real常量Money常量Page34Transact-SQL编程基础局部变量声明语法DECLARE@local_variabledata_type[,…n]实例DECLARE@stuNamevarchar(16)Page35Transact-SQL编程基础局部变量赋值语法SET@local_variable_name=expression实例SET@stuName=‘田洋’Page36Transact-SQL编程基础局部变量应用SELECT*FROMStudentWHEREstuName=@stuNamePage37Transact-SQL编程基础消息打印和注释消息打印DECLARE@addressvarchar(50)SET@address=‘东城区牌楼西街216号’PRINT‘地址是：’+@address注释单行注释：--注释内容多行注释：/*注释内容*/Page38流程控制IF语句IFgetdate()='2009-01-01'BEGINUPDATEStudentSETstuAge=stuAge+1PRINT'又过了一年，所有学生年龄增加一岁'ENDELSEPRINT'学生年龄暂时不需要修改'Page39流程控制WHILE语句DECLARE@countintSELECT@count=11WHILE@count0BEGINSET@count=@count-1IF@count=4BREAKIF@count=6CONTINUEPRINT@countENDPage40游标概述游标提供了以结果集中单条记录为单位进行数据操作的机制，也就是说，是一个可控的指向单条记录的指针。Page41使用游标的步骤使用游标包括以下五个步骤声明游标打开游标提取数据关闭游标释放游标Page42使用游标管理数据基本语法声明游标DECLAREcursor_name[INSENSITIVE][SCROLL]CURSORFORselect_statement[FORREADONLY|UPDATE[OFcolumn_name[,...]]]Page43使用游标管理数据参数说明INSENSITIVE当使用游标时，对源表数据的更新不敏感同时，也不允许通过游标修改源表数据SCROLL设置FETCH语句中的参数FIRST、LAST、PRIOR、NEXT、RELATIVE和ABSOLUTE均可用READONLY设定游标为只读的UPDATE[OFcolumn_name[,...]]定义可通过游标更新的列Page44使用游标管理数据基本语法打开游标OPENcursor_namePage45使用游标管理数据基本语法提取数据FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{cursor_name}[INTO@variable_name[,...]]Page46使用游标管理数据参数说明NEXTPRIORFIRSTLASTABSOLUTE3RELATIVE2当前位置Page47使用游标管理数据基本语法关闭游标CLOSEcursor_name释放游标DEALLOCATEcursor_namePage48使用游标管理数据实例分析提出问题BigCollege教务处老师需要查看学生年龄和学分的情况，生成下图所示报表：Page49使用游标管理数据实例分析分析问题报表表头信息无法通过查询语句得出，准备采用PRINT语句输出。表头中的日期是实时日期，通过getdate()函数得到，且需要对年月日进行分割。因为需要计算学生应修学分与已修学分的差，所以报表需要使用Student表和Major表。因为要比较学生年龄是否大于平均年龄，所以需要事先计算学生平均年龄。由于要对每个学生情况进行判断和处理，所以采用游标解决该问题。Page50使用游标管理数据实例分析解决问题声明变量和游标，计算学生平均年龄打开游标生成表头提取数据关闭游标释放游标Page51使用游标管理数据实例分析结果验证Page52思考打印学生课程成绩报表，要求包括学号、姓名、课程名和成绩情况（0-59分为不及格，60-69分为及格，7