数据库实验三《数据查询与操作》实验一、实验目的与要求1、掌握多表查询：嵌套查询的使用。
2、掌握INSERT、UPDATE和DELETE语句的一般格式与使用方法。
3、掌握视图的创建和删除、视图的NSERT、UPDATE和DELETE 的应用二、实验平台1、操作系统：Windows XP或Windows 20032、数据库管理系统：SQL Server 2005三、实验内容实验前，先附加“教务管理系统”，在“教务管理系统”的数据库中进行一下查询：嵌套查询1、在“教务管理系统”数据库中，根据相关表的内容，查询平均成绩大于70的课程信息select课程名称from课程信息where课程编号in(select课程编号from选课表group by课程编号having avg(成绩)>70)2、从“教务管理系统”数据库中，查询人数超过45人所在班级中的学生的学号、姓名和班级名称。
select学号,姓名,b.班级编号,班级名称from学生信息x,班级信息bwhere x.班级编号= b.班级编号and x.班级编号in(select班级编号from班级信息where人数>45)3、在“教务管理系统”数据库中，查询学生姓名为“朱文娟”所在班级的学生信息，要求返回学生的学号、姓名、性别、班级编号、班级名称和年级信息select学号,姓名,性别,x.年级,b.班级编号,班级名称from学生信息x,班级信息bwhere x.班级编号= b.班级编号and x.班级编号=(select班级编号from学生信息where姓名='朱文娟')4、在“教务管理系统”数据库中，查询出所有非计算机系的学生信息，并显示出这些学生的考试成绩，再按成绩进行降序排列显示，要求返回学生的学号、姓名、班级编号、课程编号和成绩信息。
select学生信息.学号,姓名,班级编号,课程编号,成绩from学生信息left join选课表on学生信息.学号=选课表.学号where学生信息.班级编号<>(select班级编号from班级信息where班级名称 LIKE '计算机%')数据更新（一）添加教务信息1、在2003年级中创建一个新班级，编号为20031340000200、班级名称为“环境与科学”、班级人数为60、班主任为“张浩”。
编写INSERT语句完成这操作。
insertinto班级信息(年级,班级编号,班级名称,班级简称,人数,班主任)values('2003','20031340000200','环境与科学','环境与科学','60','张浩')2、添加一个来自“上海”、“汉族”、姓名为“苏娜”的女生信息，并设置该生的学号为“200330000201”、班级编号为“20031340000200”。
编写INSERT语句完成这操作。
insert into学生信息(学号,姓名,班级编号,性别,民族,籍贯)values('200330000201','苏娜','20031340000200','女','汉','上海')3、添加一条学号为20030000041的学生的选课记录，选修的课程的编号为201，考试成绩为94.insert into选课表(学号,课程编号,成绩)values('2003000041','201','94')消息547，级别16，状态0，第2 行INSERT 语句与FOREIGN KEY 约束"FK_成绩表_学生信息"冲突。
该冲突发生于数据库"教务管理系统"，表"dbo.学生信息", column '学号'。
语句已终止。
4、在“课程信息”表中，添加一条课程记录，其中课程编号为201，名称为“数据库系统原理”。
insert into课程信息(课程编号,课程名称)values('201','数据库系统原理')消息515，级别16，状态2，第1 行不能将值NULL 插入列'课程简称'，表'教务管理系统.dbo.课程信息'；列不允许有空值。
INSERT 失败。
语句已终止。
4、创建一个学生补考表，表中数据来自成绩表，条件为成绩小于60分，要求补考表中仅显示学生的学号和补考课程的编号。
编写INSERT语句完成这操作。
create table补考表(学号varchar(14),补考课程编号int)insertinto补考表(学号,补考课程编号)select学号,课程编号from选课表where成绩<60（二）更新教务信息5、学生“胡少华”选择了新的专业，并调换到了编号为“20021340000104”的班级。
编写UPDATE语句完成这操作。
update学生信息set班级编号=20021340000104where姓名='胡少华'6、调整课程“中外美术名作鉴赏”到班级“美术01”开设。
update课程信息set班级编号=(select班级编号from班级信息where班级名称='美术01')where课程名称='中外美术名作鉴赏'（三）删除教务信息7、将学生“胡少华”所在班级的成绩合格（大于60）的学生信息删除。
编写DELETE 语句完成这操作。
delete from学生信息where学号in(select xs1.学号from学生信息xs1,学生信息xs2,选课表xkwhere xs1.班级编号= xs2.班级编号and xs1.学号=xk.学号and xs2.姓名='胡少华'and xk.成绩>=60)或者：delete from学生信息where学号in(select学生信息.学号from学生信息,选课表where学生信息.学号=选课表.学号and成绩>60 and班级编号in(select班级编号from学生信息where姓名='胡少华'))8、学生“胡少华”已经学完所有课程并顺利毕业，删除他的学生信息。
编写DELETE 语句完成这操作。
deletefrom学生信息where姓名='胡少华'消息547，级别16，状态0，第2 行DELETE 语句与REFERENCE 约束"FK_成绩表_学生信息"冲突。
该冲突发生于数据库"教务管理系统"，表"dbo.选课表", column '学号'。
语句已终止。
9、删除前面创建的学生补考表。
drop table补考表数据更新，并观察是否有违反数据的完整性约束。
视图1、建立学生党员的视图，并要求数据操作时仍需保证该视图只有党员学生create view vi_xdxasselect*from学生信息where政治面貌='党员'with check option2、创建一个学生成绩统计视图，包括学号，姓名，选课门数，平均分，最高分，最低分。
create view vi_gradestuasselect xs.学号,姓名,count(课程编号)课程数,avg(成绩)平均成绩,max(成绩)最高分,min(成绩)最低分from学生信息xs,选课表xkwhere xs.学号= xk.学号group by xs.学号,姓名3、建立需要重考课程的学生视图（包括学号，姓名，重修的课程编号）create view vi_kstudentasselect学生信息.学号,姓名,课程编号from学生信息,选课表where学生信息.学号=选课表.学号and选课表.是否重考='是'4、建立课程及课程平均成绩的视图create view vi_courceAvg(课程名称,平均成绩)asselect课程名称,avg(成绩)from课程信息k,选课表xwhere k.课程编号= x.课程编号group by课程名称5、建立计算机专业学生的视图create view vi_jsjstuasselect学生信息.*from学生信息,班级信息where学生信息.班级编号=班级信息.班级编号and班级名称like'计算机%'6、查询需要重考的学生姓名及相应重考课程名称。
create view vi_kstudentasselect学生信息.学号,姓名,课程编号from学生信息,选课表where学生信息.学号=选课表.学号and选课表.是否重考='是'7、查询计算机专业学生的选课情况select选课表.*from选课表,vi_jsjstuwhere vi_jsjstu.学号=选课表.学号8、向学生党员视图中插入一个新的学生记录，其中学号为20030000045，姓名为李流，性别为男。
insert into vi_xdx(学号,姓名,性别,政治面貌)values('20030000045','李流','男','党员')9、通过计算机专业学生的视图，实现计算机专业学号为200230000347的学生郑辉转专业到信息管理01。
update vi_jsjstuset班级编号=(select班级编号from班级信息where班级名称='信息管理')where姓名='郑辉'10、删除计算机专业视图中学号为200230000347的记录。
deletefrom vi_jsjstuwhere学号='200230000347'消息4405，级别16，状态1，第1 行视图或函数'vi_jsjstu' 不可更新，因为修改会影响多个基表。
11、删除计算机专业学生的视图drop view vi_jsjstu。