使用SQL语句创建视.ppt

第4讲视图 什么是视图 基表 basetable 独立存在的表视图 虚拟表 在SQL中只存储视图的定义 不存放视图所对应的记录 视图的概念 视图是从一个或者多个表或视图中导出的表 其结构和数据是建立在对表的查询基础上的 和真实的表一样 视图也包括几个被定义的数据列和多个数据行 但从本质上讲 这些数据列和数据行来源于其所引用的表 视图概述 视图的作用使用户只关注所关心的数据提高了数据的安全性提高客户端的查询效率 注意 创建视图时应该注意以下情况 只能在当前数据库中创建视图 如果视图引用的基表或者视图被删除 则该视图不能再被使用 直到创建新的基表或者视图 如果视图中某一列是函数 数学表达式 常量或者来自多个表的列名相同 则必须为列定义名称 不能在视图上创建索引视图的名称必须遵循标识符的规则 且对每个用户必须是唯一的 此外 该名称不得与该用户拥有的任何表的名称相同 视图应用 视图为基表的行的子集 问题 在XK数据库创建视图v Student 只显示Student表中班级编码为20000001的班级的学生信息方法1 使用企业管理器方法2 使用SQL语句方法3 使用创建视图向导 创建视图 SQL语句定义如下 usexkgocreateviewv student withencryption asselect fromstudentwhereclassno 20000001 go 练习 创建视图v Class 只显示Class表中系部编码为01的系部的班级信息创建视图v StuCouSub 只显示StuCou表中的StuNo列和CouNo列 创建视图 视图为多个表 视图连接组成的复杂查询 最常用的情况 例 创建视图v StuCou 显示学生的学号 姓名和所选的课程名称特点 多表查询 其实只需要在正确的select语句前加上CREATEVIEW AS即可 创建视图 添加表并选择要输出的行 练习 创建视图v CouByCpt 显示计算机应用工程系承担的选修课程的信息 课程名称 授课老师 上课时间 usexkgocreateviewv coubycptasselectCouName Teacher SchoolTime DepartNamefromCourse DepartmentwhereCourse DepartNo Department DepartNoandDepartName 计算机应用工程系 go 创建视图 4 视图为基表的统计汇总 问题 创建视图v CouByKind 按课程分类统计报名人数 特点 视图定义中使用了GROUPBY子句注意 在下面的情况下必须明确指明视图每一列的列名 视图中的某一列是算术表达式 函数或者常量多表连接时 有两个或两个以上的列具有相同的列名思考 能否将课本P51图2 2 26的查询结果作为视图 创建视图 创建视图 usexkgocreateviewV coubykindasselectkindas 课程分类 sum willnum as 报名总数 fromcoursegroupbykindgo 练习 创建视图v CouByDep 显示各个系部开设的选修课的数量和报名人数 创建视图 SQL语句 usexkgocreateviewv CouByDepasselectdepartname 课程名称 课程数量 count 报名人数 sum willnum fromdepartment coursewheredepartment departno course departnogroupbydepartnamego 显示视图信息 显示视图信息显示视图的定义信息sp helptext显示视图的参照对象和字段信息sp depends例 显示v coubycpt的定义信息 参照对象和字段信息 sp helptextv coubycptgosp dependsv coubycptgo 修改视图 格式 ALTERVIEWview nameASselect statement 问题 将v CouByCpt进行修改 使其显示旅游系所开设的所有课程信息 加密视图WITHENCRYPTION 修改视图 usexkgoalterviewv coubycptasselectCouName Teacher SchoolTime DepartNamefromCourse DepartmentwhereCourse DepartNo Department DepartNoandDepartName 旅游系 go 显示视图信息 显示视图信息显示视图的定义信息sp helptext显示视图的参照对象和字段信息sp depends例 显示v coubycpt的定义信息 参照对象和字段信息 sp helptextv coubycptgosp dependsv coubycptgo 重命名视图 重命名视图使用企业管理器使用系统存储过程sp rename例 将视图v coubycpt重新命名为v coucount sp renamev coubycpt v coucountgo 删除视图通过企业管理器修改应用SQL语句来删除SQL语句格式如下 DROPVIEWview name也可以同时删掉多个视图 例 删除试图v coubycpt和v stucou Dropviewv coubycpt v stucou 更新视图 INSERT操作UPDATE操作DELETE操作注意 由于视图实际上并不存储记录 因此更新视图的操作最终都转化为对基表的更新操作使用WITHCHECKOPTION 保证在进行视图的更新操作时 所插入 更新 删除的记录 满足视图定义中查询的条件表达式 更新视图 使用视图修改数据时 需要注意以下几点 修改视图中的数据时 不能同时修改两个或者多个基表 可以对基于两个或多个基表或者视图的视图进行修改 但是每次修改都只能影响一个基表 不能修改那些通过计算得到的字段 如果在创建视图时指定了WITHCHECKOPTION选项 那么所有使用视图修改数据库信息时 必须保证修改后的数据满足视图定义的范围 执行UPDATE DELETE命令时 所删除与更新的数据必须包含在视图的结果集中 如果视图引用多个表时 无法用DELETE命令删除数据 更新视图 利用视图添加新记录例 首先创建了一个新的视图v teacher 该视图基于表teacher创建 createviewv teacherasselectteacherno teachername departno salaryfromteacherWheresalary 3000goInsertintov teacherValues 006 李田 3100 02 go 更新视图 usexkgoalterviewv teacherasselectID teachername salary departnofromteacherWheresalary 3000withcheckoptiongoInsertintov teacherValues 10 李田 2900 02 go 更新视图 利用视图修改记录将视图v teacher中名为张胜利的老师改为王静西 usexkgoupdatev teachersetteachername 王静西 whereteachername 张胜利 go 更新视图 使用视图删除记录 可以删除任何基表中的记录 直接利用DELETE语句删除记录即可 但应该注意 必须指定在视图中定义过的字段来删除记录 例 利用视图v teacher删除表teacher中姓名为王静西的记录 deletefromv teacherwherename 王静西 练习 创建视图v StuSQL 显示 00电子商务 班并选修了 SQLServer实用技术 的学生信息 学号 姓名 练习 usexkgocreateviewv stusqlasselectstudent stuno stuname classnofromstudent class course stucouwherestudent stuno stucou stunoandstudent classno class classnoandcourse couno stucou counoandclassname 00电子商务 andcouname SQLServer实用技术 go 思考 问题 对于上述建立的试图V stusql 我们可否做以下操作 向该视图中插入新的一行 00000061 林思雨 20000001 如果可以插入那么在什么情况下可以插入 注 原来的student表中并不存在学号 00000061 思考 将视图定义改变如下 usexkgoalterviewv stusqlasselectstudent stuno stuname student classnofromstudent class course stucouwherestudent stuno stucou stunoandstudent classno class classnoandcourse couno stucou counoandclassname 00电子商务 andcouname SQLServer实用技术 withcheckoptiongo 思考 问题 1 此时还能否将下列一行记录插入到该视图中 00000062 林雨 20000001 2 能否通过视图将学号为 00000001 的学生的姓名改为 林小斌 3 能否将视图中下面所示的这行记录删除 00000001 林斌 小结 视图可被看成是虚拟表或存储查询使用视图可提高数据库性能和安全性对视图进行修改会影响基表数据