综合实验 索引与视图管理.doc

综合实验2 索引与视图管理create database XXXgo XXXusego/*2、参照表格数据，创建如下数据表：（）学生表：Student ( Sno, Sname, sex, Sbirthday, Sdept )SnoSnamesexSbirthdaySdept2010010505李阳光男1991-5-15软件工程系2010010508张小明男1991-5-15数学系2010010515李丽女1992-6-2软件工程系2010010525王刚男1989-12-10信息系（）课程表：Course ( Cno, Cname, Ccredit )CnoCnameCcreditB000000016数据库4B000000019网络设备互联4A000000006Windows Server 20036（）成绩表：Score ( Sno, Cno, Grade )SnoCnoGrade2010010505B000000016912010010508B000000019852010010515B000000019922010010505B000000019532010010505A000000006562010010508B000000016822010010515B00000001695*/create table Student ( Sno char(10) primary key, Sname varchar(8) not null, sex char(2) default男 check(sex=男 or sex=女), Sbirthday datetime, Sdept varchar(20)create table Course ( Cno char(10) primary key, Cname varchar(30) unique, Ccredit tinyint)create table Score ( Sno char(10), Cno char(10), Grade tinyint check(grade between 0 and 100)-3、分别向数据表中插入以上表格中的数据。insert dbo.Student values(2010010505,李阳光,男,1991-5-15,软件工程系)insert dbo.Student values(2010010508,张小明,男,1991-5-15,数学系)insert dbo.Student values(2010010515,李丽,女,1992-6-2,软件工程系)insert dbo.Student values(2010010525,王刚,男,1989-12-10,信息系)insert dbo.Course values(B000000016,数据库,4)insert dbo.Course values(B000000019,网络设备互联,4)insert dbo.Course values(A000000006,Windows Server 2003,6)insert dbo.Score values(2010010505,B000000016,91)insert dbo.Score values(2010010508,B000000019,85)insert dbo.Score values(2010010515,B000000019,92)insert dbo.Score values(2010010505,B000000019,53)insert dbo.Score values(2010010505,A000000006,56)insert dbo.Score values(2010010508,B000000016,82)insert dbo.Score values(2010010515,B000000016,95)-1、创建一张没有主键的数据表Stu，字段名分别为sNo、sName、sSex、sBirthday、SClass；create table Stu( Sno char(10), Sname varchar(8) not null, sex char(2) default男 check(sex=男 or sex=女), Sbirthday datetime, Sclass varchar(20)-2、给sNo字段创建聚族索引，索引名为ix_1；CREATE UNIQUE CLUSTERED INDEX ix_1ON dbo.Stu (Sno)-3、给sName字段创建非聚族索引，索引名为ix_2；CREATE INDEX ix_2ON dbo.Stu (Sname)-4、把索引ix_1重命名为ix_No、ix_2重命名为ix_Name；use sunyafeigosp_rename dbo.Stu.ix_1,ix_No,indexsp_rename dbo.Stu.ix_2,ix_Name,index-5、删除以上所创建的索引；DROP INDEX dbo.Stu.ix_Name,dbo.Stu.ix_No-6、给sNo字段创建聚族索引结构的唯一索引，索引名为u_ix_No；CREATE UNIQUE INDEX u_ix_NoON dbo.Stu (Sno)-7、给sClass和sName字段创建复合索引，索引名为ix_Class_Name；CREATE UNIQUE INDEX ix_Class_Nameon dbo.Stu(Sclass,Sname)-8、创建视图V_view1：列出Student表中年之前出生的学生名单；create view V_view1as select Snamefrom dbo.Studentwhere Sbirthday1990-9、创建视图V_view2：查询“B000000016”课程的所有学生成绩情况，要求使用“WITH CHECK OPTION”子句；create view V_view2as select*from dbo.Scorewhere Cno=B000000016WITH CHECK OPTION-10、创建视图V_view3：统计“B000000016”课程的选修人数、以及平均分，要求视图加密；create view V_view3 with encryptionas select count(B000000016)选修人数,avg(Grade)平均分from dbo.Scorewhere Cno=B000000016-11、在视图V_view1中查询所有男学生的名单；select Snamefrom dbo.V_view1where Sname in (select Sname from dbo.Student where sex=男)-12、向视图V_view2中插入如下两条记录：-2010010525 B000000016 72-2010010525 A000000006 86insert V_view2 values(2010010525,B000000016,72)insert V_view2 values(2010010525,B000000006,86)-13、利用视图V_view2更改“”学生的成绩为；update V_view2set grade=97w