实验6 视图的应用.doc

实验6 视图的应用实验目的学会使用MS SQL SERVER 2000企业管理器和SQL语句创建、修改和删除视图，掌握通过视图向基本表插入、删除、更新数据的方法和步骤。知识点拨：1. 视图是一张虚表2. 视图的作用3. 视图的种类4. 创建视图的SQL语句5. 更新视图的SQL语句6. 删除视图的SQL语句7. 通过视图修改数据库中数据的限制实验内容1. 行列子视图的创建2. 基于多个基本表的视图的创建3. 基于基本表和视图的视图的创建4. 带表达式的视图的创建5. 分组视图的创建6. 删除视图7. 修改视图8. 通过视图修改数据库数据实验步骤1. 选择数据库JX；注意：以下操作均在查询分析器定义，在企业管理器中查看和修改，并请验证结果！2. 创建行列子视图1） 创建视图WY_Student，用于查询外语系学生的基本信息；CREATE VIEW WY_StudentASSELECT *FROM StudentWHERE Sdept=外国语2） 创建视图SS_Student，用于查询学生的姓名、学号和总学分信息，要求属性列中文显示；CREATE VIEW SS_StudentASSELECT Sname 姓名,SC.Sno 学号,Total 总学分FROM Student3. 创建基于多个基本表的视图1） 创建视图XSXK，用于查询每个同学的选课信息，包括学号、姓名、课程号、课程名和成绩；CREATE VIEW XSXKASSELECT SC.Sno,Sname,SC.Cno,Cname,GradeFROM Student,SC,CourseWHERE Student.Sno=SC.Sno AND SC.Cno=Course.CnoGROUP BY SC.Sno,Sname,SC.Cno,Cname,Grade2） 创建视图JSSK，用于查询每个教师的任课情况，包括教师号、教师名、职称、所讲授课程的课程号信息，并用中文列出各属性名；CREATE VIEW JSSKASSELECT Teacher.Tno 教师号,Tname 教师名,Tprof 职称,SC.Cno 课程号,Cname 课程名FROM Teacher,SC,CourseWHERE Teacher.Tno=SC.Tno AND SC.Cno=Course.CnoGROUP BY Teacher.Tno ,Tname ,Tprof ,SC.Cno ,Cname 3） 基于视图WY_Student和SC，创建视图SSS，用于查询外语系学生的选课情况；CREATE VIEW SSSASSELECT WY_Student.Sno,Sname,CnoFROM WY_Student,SCWHERE SC.Sno=WY_Student.Sno4. 带表达式的视图的创建1） 创建视图XSNL，用于查询学生的学号、姓名和年龄；CREATE VIEW XSNL(Sno,Sname,Sage)ASSELECT Sno,Sname,year(getdate()-year(Sbirth)FROM Student2） 创建视图XSZCJ，用于查询选修了课程的学生的总评成绩，包括学号、姓名、课程号，每门课程的总评成绩计算公式为：成绩*70%+（成绩-10）*20%+10；CREATE VIEW XSZCJ(Sno,Sname,Cno,总评成绩)ASSELECT Student.Sno,Sname,Cno,Grade*0.7+(Grade-10)*0.2+10FROM Student,SCWHERE Student.Sno=SC.Sno3） 创建视图JSGZ，用于显示教师的预发工资，包括教工号、姓名、职称和按95%预发的工资额；（含有%的列名字可以用【】）CREATE VIEW JSGZ(Tno,Tname,Tprof,预发95%工资)ASSELECT Tno,Tname,Tprof,Tpay*0.95 FROM Teacher5. 分组视图的创建1） 创建视图XSPJCJ，用于查询选修了课程的学生的成绩，包括学号、姓名和所选课程的平均成绩；CREATE VIEW XSPJCJ(Sno,Sname,平均成绩)ASSELECT SC.Sno,Sname,AVG(Grade)FROM SC,StudentWHERE SC.Sno=Student.SnoGROUP BY SC.Sno,Sname2） 创建视图JSRKMS，用于查询教师的任课门数，包括教工号、教师名和任课门数；CREATE VIEW JSRKMS(Tno,Tname,任课门数)ASSELECT Teacher.Tno,Tname,COUNT(DISTINCT(Cno)FROM Teacher,SCWHERE Teacher.Tno=SC.TnoGROUP BY Teacher.Tno,Tname3） 创建视图KCPJCJ，用于查询各门课程的平均成绩，包括课程号、课程名、选课人数、平均成绩；CREATE VIEW KCPJCJ(Cno,Cname,选课人数,平均成绩)ASSELECT Course.Cno,Cname,COUNT(*),AVG(Grade)FROM Course,SCWHERE Course.Cno=SC.CnoGROUP BY Course.Cno,Cname4） 创建视图YXXS，用于查询各门成绩和平均成绩都在85分以上的学生的学号、姓名及平均成绩；CREATE VIEW YXXSASSELECT DISTINCT SC.Sno,Sname,AVG(Grade) 平均成绩FROM Student,SCWHERE Student.Sno=SC.Sno AND SC.Sno NOT IN(SELECT SnoFROM SCWHERE Grade85)GROUP BY SC.Sno,Sname6. 删除视图1） 删除视图XSNL；DROP VIEW XSNL7. 通过视图修改数据库数据1） 查询视图WY_Student，然后使用INSERTINTO语句更新WY_Student视图，再次查询该视图以及相关基表，检查执行结果；SELECT *FROM WY_StudentINSERTINTO WY_Student(Sno,Sname)VALUES (06122,王五)SELECT *FROM WY_StudentSELECT *FROM Student再次查询的结果为，在基本表Student中插入了该记录，但是在视图WY_Student中并没有插入该记录，因为插入的记录不满足视图WY_Student子查询的条件。若是改为INSERT INTO WY_Student(Sno,Sname,Sdept)VALUES (06122,王五,外国语)则视图WY_Student和基本表Student都插入了该记录。2） 使用UPDATE 更新视图WY_Student中的某个或某些属性值，然后再次查询该视图以并查询相关基表检查更新结果。UPDATE WY_StudentSET Sname=王士武WHERE Sno=06003SELECT *FROM WY_StudentSELECT *FROM Student再次查询的结果为，视图WY_Student和基本表Student中的记录都被修改了，即如果修改视图中的记录，那么基本表中的记录相应的也被修改。同样的，若是修改了基本表中的记录，那么由基本表导出的视图中的记录相应也会被修改。3） 使用DELETE删除视图WY_Student中刚插入的记录，然后查询该视图和相关基本表数据；DELETE FROM WY_StudentWHERE Sname=王五SELECT * FROM WY_StudentSELECT * FROM Student基本表和视图中的记录都被删掉4） 修改视图JSGZ，把职称为副教授的预发工资改为该视图预发工资的90%，查看执行结果；UPDATE