数据库原理与应用教程－第五章(第七课） .ppt

第5章视图 5 1视图概念5 2定义视图5 3通过视图查询数据5 4删除视图5 5视图的作用 5 1视图概念 视图是由从数据库的基本表中选取出来的数据组成的逻辑窗口 视图是一个虚表 在数据库中只存放视图的定义 不存放视图包含的数据 这些数据仍存放在原来的基本表中 视图可以建立在基本表上 也可以建立在其他的视图上 即可以在一个视图之上再定义视图 但对视图数据的操作最终都会转换为对基本表的操作 5 2定义视图 定义视图的sql语句为createview 其一般格式为 createview 视图列名表 as子查询语句其中子查询可以是任意的select语句 但要注意以下几点 1 子查询中通常不包含orderby和distinct子句2 在定义视图时要么指定全部视图列 要么全部省略不写 如果省略了视图的属性列名 则视图的列名与子查询列名相同 但在如下三种情况下必须明确指定组成视图的所有列名 某个目标列是计算函数或列表达式 多表连接时选出了几个同名列作为视图的字段 需要在视图中为某个列选用新的更合适的列名 1 定义单源表视图单源表视图指的是数据取自一个基本表的部分行 列 这样定义的视图可以进行查询和修改数据操作 例1 建立信息系学生的视图 createviewis studentasselectsno sname sagefromstudentwheresdept 信息系 dbms执行createview语句的结果只是保存视图的定义 只有在对视图执行查询时 才按视图的定义从相应基本表中查询数据 视图的名称存储在系统表sysobjects中创建视图过程的文本存储在syscomments中 2 定义多源表视图多源表视图指的是定义视图的子查询的源表可以有多个 这样定义的视图一般只用于查询 不用于修改数据 例2 建立信息系选修了 c02 号课程的学生的视图 列出学生的学号 姓名和成绩 createviewv is s1 sno sname grade asselectstudent sno sname gradefromstudentjoinsconstudent sno sc snowheresdept 信息系 andsc cno c01 3 在已有视图上定义新视图在视图上建立视图表示视图的数据源中有视图 作为数据源的视图必须是已经建立好的 例3 建立信息系选修了 c02 号课程且成绩在90分以上的学生的视图 createviewv is s2asselectsno sname gradefromv is s1wheregrade 90 例4 利用例1所建的视图 建立查询信息系vb考试成绩大于等于80分的学生的姓名和成绩的视图 createviewv is vbasselectsname gradefromis studentvjoinscv sno o owheregrade 80andcname vb 4 定义带表达式的视图在定义基本表时 为减少数据库中的冗余数据 表中只存放基本数据 由基本数据经过各种计算派生出的数据一般是不存储的 但由于视图中的数据并不实际存储 所以定义视图时可以根据需要设置一些派生属性列 在这些派生属性列中保存经过计算的值 例5 定义一个反映学生出生年份的视图 createviewbt s sno sname birthyear asselectsno sname 2011 sagefromstudent 5 含分组统计信息的视图含分组统计信息的视图是指视图的子查询中含有groupby子句 这样的视图只能用于查询 不能用于修改数据 例6 定义一个存放每个学生的学号及平均成绩的视图 createviews g sno averagegrade asselectsno avg grade fromscgroupbysno注意 如果子查询的选择列表包含表达式或统计函数 而且在子查询中也没有为这样的列指定列标题 则在定义视图的语句中必须要指定视图属性列的名字 从student表 sc表和course表中产生一个视图grade table 包括学生姓名 课程名和成绩 createviewgrade tableasselectsname cname gradefromstudent course scwherestudent sno sc snoandco sc cno 5 3通过视图查询数据 例7利用例1建立的视图 查询信息系年龄小于20岁的学生的学号 姓名和年龄select fromis studentwheresage 20例8 查询信息系选了 c02 号课程的学生的学号号 姓名和年龄selects sno sname sagefromv is s1joinstudentsons sno v is s1 sno 例9 查询信息系学生的学号 姓名 所选课程名selectv sno v sname cnamefromis studentvjoinsconv sno o o例10 利用例6建立的视图 查询考试成绩80分以上的学生的学号和平均成绩select froms gwhereavggrade 80 5 4修改和删除视图 1 修改视图alterview视图名 列名 n as查询语句例11 修改例6定义的视图 使其统计每个学生的考试平均成绩和修课总门数alterviews g sno avggrade count cno asselectsno avg grade count fromscgroupbysno 2 删除视图的sql语句的格式为 dropview例12删除is student视图 dropviewis student删除视图时需要注意的是 如果被删除的视图是作为其他视图的数据源 则导出视图将无法再使用了 同样 如果作为视图的基本表被删除了 则视图也将无法使用 1 简化数据查询语句可以使用户将注意力集中在所关心的数据上定义视图可以将表与表之间的复杂的连接操作和搜索条件对用户隐藏起来 当多次执行相同的数据查询操作时使用视图尤为有用 2 使用户能从多角度看到同一数据使不同的用户以不同的方式看待同一数据 当许多用户共享同一个数据库时 这种灵活性非常重要 3 提高了数据的安全性可以定制用户能查看哪些数据并屏蔽掉敏感的数据 4 提供了一定程度的逻辑独立性与数据库的外模式对应 5 5视图的作用 补充知识 存储过程1 什么是存储过程 存储过程是存储在服务器上的一组预编译的sql语句 它可以接受参数 返回状态和参数值 并能嵌套 使用存储过程能够改变sql语句的运行性能 提高其执行效率 使用存储过程的优点 1 由于存储过程在第一次执行后 其执行规划就驻存在高速缓存中 在以后的操作中 只需从高速缓存中调用 提高了系统性能 2 提供一种安全机制 存储过程作为一种安全机制 是用户通过它访问未直接授权的表或视图 当创建存储过程时 系统检查其中的语句的正确性存储过程的名称存储在系统表sysobjects中创建存储过程的文本存储在syscomments中在存储过程的创建中 允许参考还不存在的对象 但是在执行存储过程时 这些对象必须存在 存储过程的类型系统存储过程 系统提供 作为各种命令使用 存储在master数据库中 前缀为sp 系统存储过程可分为九类 本地存储过程 创建在每个用户数据库中的存储过程 5 创建存储过程createprocedureprocedure name number parameterdata type varying default output with recompile enryption recompile encryption assql statement n 参数 procedure name 符合标识符规则 对于数据库及其所有者必须唯一 要创建临时存储过程 需在procedure name前加 number整数 标识同名存储过程的不同对象 例如myproc 1 myproc 2等 用一条dropproceduremyproc语句可将同名存储过程删除 varying 指定作为输出参数支持的结果集 default 参数的默认值 如果定义了默认值 不必指定该参数的值即可执行过程 可以使用通配符 和 parameter 过程中的参数默认情况下 参数只能代替常量 而不能用于代替表名 列名或其它数据库对象的名称 recomplile 表明系统执行存储过程时重新编译该存储过程 encryption 表示系统加密syscommengs表中包含createprocedure语句文本的条目 as 指定过程要执行的操作存储过程的最大大小为128mb a 使用带有复杂select语句的简单过程下面的存储过程从四个表的连接中返回所有作者 出版的书籍以及出版社 usepubsifexists selectnamefromsysobjectswherename au info all andtype p dropprocedureau info allgocreateprocedureau info allasselectau lname au fname title pub namefromauthorsainnerjointitleauthortaona au id ta au idinnerjointitlestont title id ta title idinnerjoinpublisherspont pub id p pub id执行au info all存储过程的方法execau info all 例如 从stu数据库的三个查询表中 返回学生学号 姓名 课程名 成绩 该存储过程不使用任何参数 usestuifexists selectnamefromsysobjectswherename student info andtype p dropprocedurestudent infogocreateprocedurestudent infoasselects sno sname cname gradefromstudentsjoinscons sno o ogo执行execstudent info b 使用带参数的简单存储过程下面的存储过过程从四个表的连接中只返回指定的作者 出版的书籍以及出版社 该存储过程接受与传递的参数精确匹配的值createprocedureau info lastnamevarchar 40 firstnamevarchar 20 asselectau lname au fname title pub namefromauthorsainnerjointitleauthortaona au id ta au idinnerjointitlestont title id ta title idinnerjoinpublisherspont pub id p pub idwhereau fname firstnameandau lname lastnameexecau info dull ann execau info lastname dull firstname ann execau info firstname ann lastname dull 例如 从stu数据库中的三个表中查询某人指定课程的成绩 usestuifexists selectnamefromsysobjectswherename student info1 andtype p dropprocedurestudent info1gocreateprocedurestudent info1 snamechar 8 cnamechar 20 asselects sno s sname ame sc gradefromstudentsjoinscons sno o owheres sname ame cnamego执行存储过程的方法 executestudent info1 李勇 计算机文化学 executestudent info1 sname 李勇 cname 计算机文化学 c 使用带有通配符参数的存储过程下面的存储过过程从四个表的连接中只返回指定的作者 出版的书籍以及出版社 该存储过程对传递的参数进行模式匹配 如果没有提供参数 则使用预设的默认值createprocedureau info2 lastnamevarchar 30 d firstnamevarchar 20 asselectau lname au fname title pub namefromauthorsainnerjointitleauthortaona au id ta au idinnerjointitlestont title id ta title idinnerjoinpublisherspont pub id p pub idwhereau fnamelike firstnameandau lnamelike lastnameexecau info2execau info2 wh execau info2 firstname a execau info2 hunter shery1 execau info2 h s 例如 从三个表中的连接表中返回指定学生的学号 姓名 所选课程名称几该课程的成绩 该存储过程在参数中使用了模式匹配 如果没有提供参数 则使用预设的默认值 usestuifexists selectnamefromsysobjectswherename st info andtype p dropprocedurest infogocreateprocedurest info snamevarchar 10 刘 asselects sno s sname ame sc gradefromstudentsjoinscons sno o owheresnamelike snamego 执行存储过程的方法exectuest infoexectuest info 王 exectuest info 王张 d 使用output参数使用一个可选的输入参数和一个输出参数usepubsgocreateproceduretitles sum titlevarchar 40 summoneyoutputasselect sum sum price fromtitleswheretitlelike title执行存储过程方法如下 declare totalmoneyexecutetitles sum t totaloutputselect t totalzong 例如 用于计算指定学生的总成绩 存储过程中使用一个输入参数和一个输出参数usestugoifexists selectnamefromsysobjectswherename totalg andtype p dropproceduretotalggocreateproceduretotalg snamechar 10 totalintoutputasselect total sum grade fromsc studentwheresname snameandsc sno student snogroupbystudent sno执行存储过程的方法declare totalintexectotalg 李勇 totaloutputselect 李勇 total 使用带扩展存储过程的execute语句下列使用xp cmdshell扩展存储过程列出文件扩展名为 exe的所有文件的目录 usemasteexecutexp cmdshell dir exe 触发器是一种特殊类型的存储过程 用于保护表中的数据 当有操作影响到触发器保护的数据时 触发器自动执行 通过触发器实现多个表间的一致性 触发器可分为三类 insert类型 update类型 delete类型 一个表可以有多种类型的多个触发器 例如 对于stu数据库中的student sc 和course表 当插入某一学号的学生的某一课程的成绩时 该学号应该是student表中已经存在的 课程号应该是course表中已存在的 可以定义insert触发器实现上述功能2 使用触发器的优点触发器可以一连串地修改数据库相关表中的数据 触发器可以比约束强制更加复杂的数据完整性这些约束比用check约束所定义的更复杂 与check约束不同的是 触发器可以引用其它表中的列 触发器可以实现数据参考的完整性 后触发器createtriggertrigger nameontable for after insert update delete as ifupdate column and or update column n if columns updated bitwise operator update bitmask comparison operator column bitmask n sql statement n after 指定触发器在触发sql语句所有操作成功后才激发 if子句进一步限制触发器被触发的条件 ifupdate column 测试在指定的列上进行的insert或update操作 不能用于delete类型 可以指定多列 if columns update 测试是否插入或更新了提及的列 仅用于insert或update触发器中 columns update 返回varbinary位模 表示插入或更新了表中的哪些列 columns update函数以从左到右的顺序返回位 最左边的位表示表中的第一列 向右的下一位表示第二列 依此类推 updated bitmask 是整型位掩码 表示实际更新或插入的列 例如 表t1包含列c1 c2 c3 c4和c5 假定表t1上有update触发器 若要检查c2 c3 和c4是否都有更新 指定14 2 4 8 其测试的if子句为 if columns update 2 4 8 0 例如 创建一个触发器 在插入 修改和删除记录时 都会显示表中的内容usetest1gocreatetabletable1 c1int c2char 30 gocreatetiggertrig1ontable1forinsert update deleteasselect fromtablego执行下列语句时 inserttable1values 1 zhangsan inserttable1values 2 lisi insert表和delete表执行触发器时 系统创建了两个特殊的逻辑表 inserted表和deleted表inserted逻辑表 向表中插入数据时 insert触发器触发执行 新的记录插入到触发器表和inserted表中deleted逻辑表 用于保存已从表中删除的记录 当触发一个delete触发器时 被删除的记录存放到deleted表中 修改一条记录等于插入一个新记录 同时删除旧记录 对定义了update触发器的表记录修改时 表中原记录移到deleted表中 修改过的记录插入到inserted表中 触发器可检查deleted表 inserted表及被修改的表 1 usetestgoifexists selectnamefromsysobjectswherename trig1 andtype tr droptriggertrig1gocreatetriggertrig1ontable1forinsert update deteleasprint insert表 select frominsertedprint deleted表 select fromdeletedgoinserttable1values 2 张三 updatetable1setc2 李四 wherec1 2deletetable1wherec1 2 2 向worker表中添加一记录时 该记录的bmh值在depart表中是否存在 若不存在 则取消插入或修改usefactorygocreatetriggerworkerinsonworkerforinsert updateasbeginif selectins bmhfrominsertins notin selectbmhfromdepart rollbackend 3 修改depart表的bmh字段时 该字段在worker表中的对应值也应做相应的修改usefactorygocreatetriggerdepartupdateondepartforupdateasbeg