实验三索引和视图

实验三索引和视图徐龙琴、刘双印设计制作实验三索引和视图实验目的掌握利用SSMS和T—SQL语句创建和删除索引的两种方法。掌握利用SSMS和T—SQL语句创建、查询、更新及删除视图的方法。实验要求能认真独立完成实验内容；实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；验后做好实验总结，根据实验情况完成实验报告。情况完成总结报告。实验学时2学时实验内容用T—SQL建立一个“学生选课数据库”，在此基础上用SQL语句建立该数据库包含的学生表，课程表，学生选修表：createdatabase学生选课数据库学生表:Student(Sno,Sname,Ssex,Sage,Sdept)其中Sno为主键且Sname不能为空值，且取值唯一、Ssex只能取值男或女、Sage在15到30之间:SnoSnoSnameSsexSageSdeptS01S02S03S04S05S06王建平刘华范林军李伟黄河长江男女女男男男211918191820自动化自动化计算机数学数学数学createtableStudent(Sno char(10) primarykey, Sname char(10) notnullunique, Ssex char(2) check(Ssexin('男','女')), Sage int check(Sage>15andSage<30), Sdept char(20) )insertintoStudentvalues('S01','王建平','男','21', '自动化')insertintoStudentvalues('S02','刘华','女','19', '自动化')insertintoStudentvalues('S03','范林军','女','18', '计算机')insertintoStudentvalues('S04','李伟','男','19', '数学')insertintoStudentvalues('S05','黄河','男','18', '数学')insertintoStudentvalues('S06','长江','男','20', '数学')课程表:Course(Cno,Cname,Cpno,Credeit)其中Cno为主键CnoCnameCpnoCreditC01英语NULL4C02数据结构C052C03数据库C022C04DB_设计C033C05C++NULL3C06网络原理C073C07操作系统C053createtableCourse( Cno char(4) primarykey, Cname char(10) notnull, Cpno char(10) , Credit char(2) ,)insertintoCoursevalues('C01','英语',NULL,'4')insertintoCoursevalues('C02','数据结构','C05','2')insertintoCoursevalues('C03','数据库','C02','2')insertintoCoursevalues('C04','DB_设计','C03','3')insertintoCoursevalues('C05','C++',NULL,'3')insertintoCoursevalues('C06','网络原理','C07','3')insertintoCoursevalues('C07','操作系统','C05','3')学生选修表:SC(Sno,Cno,Grade)其中Sno,Cno为主键同时又为外键、Grade值在0到100;SnoCnoGradeS01C0192S01C0384S02C0190S02C0294S02C0382S03C0172S03C0290S04C0375createtableSC( Sno char(10) , Cno char(4) , Grade int check(Gradebetween0and100), primarykey(Sno,Cno), foreignkey(Sno) referencesStudent(Sno), foreignkey(Cno) referencesCourse(Cno))insertintoSCvalues('S01','C01','92')insertintoSCvalues('S01','C03','84')insertintoSCvalues('S02','C01','90')insertintoSCvalues('S02','C02','94')insertintoSCvalues('S02','C03','82')insertintoSCvalues('S03','C01','72')insertintoSCvalues('S03','C02','90')insertintoSCvalues('S04','C03','75')2.索引的建立、删除①用SSMS的方式为Student表按Sno(学号)升序建唯一索引②用T—SQL语句为Course表按Cno(课程号)升序建唯一索引，createuniqueindex课程号onCourse(CnoASC)③用T—SQL语句为SC表按Sno(学号)升序和Cno(课程号)号降序建唯一索引。createuniqueindexSCnoonSC(SnoASC,CnoDESC)④能否用T—SQL语句再为表Students的Sname(姓名)和Sno(学号)列上建立一个聚簇索引？若不能说明原因？答：不能，一个表最多只能一个聚集索引。⑤用T—SQL语句删除基本表SC上的唯一索引。dropindexSC.SCno3.用T—SQL完成如下视图的建立、查询、修改及删除1)建立数学系学生的视图C_Student，并要求进行修改和插入操作时仍需保证该视图只有数学系的学生，视图的属性名为Sno，Sname，Sage，Sdept。createviewC_Student(Sno,Sname,Sage,Sdept)asselectSno,Sname,Sage,SdeptfromStudentwhereSdept='数学'withcheckoption2)用SSMS的方式建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图Student_CR。3)定义一个反映学生出生年份的视图Student_birth(Sno,Sname,Sbirth)。createviewStudent_birth(Sno,Sname,Sbirth)asselectSno,Sname,2013-SagefromStudent4)建立先修课程为空的课程视图v_coursecreateviewv_courseasselect*fromCoursewhereCpno=NULL5)

建立成绩高于90分的女生成绩视图v_F_grade（包括学号，姓名，课程号及成绩列）createviewv_F_grade(Sno,Sname,Cno,Grade)asselectStudent.Sno,Sname,Course.Cno,GradefromStudent,Course,SCwhereSsex='女'andGrade>90andStudent.Sno=SC.SnoandSC.Cno=Course.Cno6)建立视图S_AVGAGE（其中包括性别SSEX与平均年龄AVG_AGE两列）用以反映男生、女生的平均年龄createviewS_AVGAGE(SSEX,AVG_AGE)asselectSsex,avg(Sage)fromStudentgroupbySsex7)对前面创建的视图S_AVGAGE执行更新操作：UPDATES_AVGAGESETAVG_AGE=85WHERE

ssex='女'上述语句能否成功执行？为什么？答：不能。对视图或函数'S_AVGAGE'的更新或插入失败，因其包含派生域或常量域。8)在数学系的学生视图C_Student中找出年龄(Sage)小于20岁的学生姓名(Sname)和年龄(Sage)。selectSname,SagefromC_StudentwhereSage<209)在Student_CR视图中查询成绩在85分以上的学生学号(Sno)、姓名(Sname)和课程名称(Cname)selectSno,Sname,CnamefromStudent_CRwhereGrade>8510)将数学系学生视图C_Student中学号为S05的学生姓名改为“黄海”。updateC_StudentsetSname='黄海'whereSno='S05'11)向数学系学生视图C_Student中插入一个新的学生记录，其中学号为“S09”，姓名为“王海”insertintoC_Studentvalues('S09','王海','20','数学')12)删除数学系学生视图C_Student中学号为“S09”deletefromC_StudentwhereSno='S09'四、思考题在一个表中可以有多个聚簇索引吗？为什么？答：聚簇索引也叫簇类索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。视图有哪些优点？答：视图是一个由SELECT语句指定，用以检索数据库表中某些行或列数据的语句存储定义。从本质上说，视图其实是一种SQL查询。使用视图的优点如下：查询的简单性：将复杂的查询(如多表的连接查询)定义为视图，保留了用户所关心的数据内容，剔除了那些不必要的冗余数据，使其数据环境更加容易控制，从而达到简化用户浏览和操作的目的；安全保护：数据库管理员可以在限制表用户的基