数据库上机作业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_Student AS SELECT * FROM Student WHERE Sdept=外国语2） 创建视图SS_Student，用于查询学生的姓名、学号和总学分信息，要求属性列中文显示； CREATE VIEW SS_Student AS SELECT Sname 姓名,Sno 学号,Total 总学分 FROM Student3. 创建基于多个基本表的视图1） 创建视图XSXK，用于查询每个同学的选课信息，包括学号、姓名、课程号、课程名和成绩； CREATE VIEW XSXK AS SELECT Student.Sno,Sname,SC.Cno,Cname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno2） 创建视图JSSK，用于查询每个教师的任课情况，包括教师号、教师名、职称、所讲授课程的课程号信息，并用中文列出各属性名； CREATE VIEW SSK AS SELECT Teacher.Tno 教师号,Tname 姓名,Tprof 职称,Cno 所授课课程号 FROM SC,Teacher WHERE SC.Tno=Teacher.Tno3） 基于视图WY_Student和SC，创建视图SSS，用于查询外语系学生的选课情况； CREATE VIEW SSS AS SELECT SC.Sno,Sname,Cno,Grade FROM WY_Student,SC WHERE WY_Student.Sno=SC.Sno4. 带表达式的视图的创建1） 创建视图XSNL，用于查询学生的学号、姓名和年龄 CREATE VIEW XSNL(Sno,Sname,Sage) AS SELECT Sno,Sname,year(getdate()-year(Sbirth) FROM Student2） 创建视图XSZCJ，用于查询选修了课程的学生的总评成绩，包括学号、姓名、课程号，每门课程的总评成绩计算公式为：成绩*70%+（成绩-10）*20%+10； CREATE VIEW XSZCJ(Sno,Sname,Cno,总评) AS SELECT SC.Sno,Sname,Cno,Grade*0.7+(Grade-10)*0.2+10 FROM Student,SC WHERE Student.Sno=SC.Sno3） 创建视图JSGZ，用于显示教师的预发工资，包括教工号、姓名、职称和按95%预发的工资额； CREATE VIEW JSGZ (Tno,Tname,Tprof, 预发工资) AS SELECT Tno,Tname,Tprof,Tpay*0.95 FROM Teacher5. 分组视图的创建1） 创建视图XSPJCJ，用于查询选修了课程的学生的成绩，包括学号、姓名和所选课程的平均成绩； CREATE VIEW XSPJCJ(Sno,Sname,Gavg) AS SELECT Student.Sno,Sname,AVG(Grade) FROM Student,SC WHERE Student.Sno=SC.Sno GROUP BY Student.Sno,Sname 2） 创建视图JSRKMS，用于查询教师的任课门数，包括教工号、教师名和任课门数； CREATE VIEW JSRKMS(Tno,Tname,任课门数) AS SELECT Tno,Tname,COUNT(Tno) FROM Teacher GROUP BY Tno,Tname3） 创建视图KCPJCJ，用于查询各门课程的平均成绩，包括课程号、课程名、选课人数、平均成绩； CREATE VIEW KCPJCJ(Cno,Cname,选课人数,Gavg) AS SELECT SC.Cno,Cname,COUNT(*),AVG(Grade) FROM SC,Course WHERE SC.Cno=Course.CnoGROUP BY Course.Cno,Cname,SC.Cno4） 创建视图YXXS，用于查询各门成绩和平均成绩都在85分以上的学生的学号、姓名及平均成绩； CREATE VIEW YXXS(Sno,Sname,Gavg) AS SELECT Student.Sno,Sname,AVG(Grade) FROM SC,StudentWHERE Student.Sno=SC.SnoAND NOT EXISTS(SELECT DISTINCT Sno FROM SC WHERE Grade85) GROUP BY SC.Sno,Sname 6. 删除视图1） 删除视图XSNL； DROP VIEW XSNL7. 通过视图修改数据库数据1） 查询视图WY_Student，然后使用INSERTINTO语句更新WY_Student视图，再次查询该视图以及相关基表，检查执行结果； INSERT INTO WY_Student VALUES(06011,王晶莹,男,1989/1/7 0:00:00,外国语,NULL) SELECT * FROM WY_Student SELECT * FROM Student2） 使用UPDATE 更新视图WY_Student中的某个或某些属性值，然后再次查询该视图以并查询相关基表检查更新结果。 UPDATE WY_Student SET Ssex=女 WHERE Sname=王晶莹 SELECT * FROM WY_Student SELECT * FROM Student3） 使用DELETE删除视图WY_Student中刚插入的记录，然后查询该视图和相关基本表数据； DELETE FROM WY_Student WHERE Sno=06011 SELECT * FROM WY_Student SELECT * FROM Student4） 修改视图JSGZ，把职称为副教授的预发工资改为该视图预发工资的90%，查看执行结果； UPDATE JSGZ SET 预发工资=Tpay*0.9 W