实验三 索引和视图.docx

1、实验三 索引和视图实验三 索引和视图一、 实验目的1 掌握利用SSMS和TSQL语句创建和删除索引的两种方法。2 掌握利用SSMS和TSQL语句创建、查询、更新及删除视图的方法。二、 实验要求1 能认真独立完成实验内容；2 实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；3 验后做好实验总结，根据实验情况完成实验报告。情况完成总结报告。三、 实验学时2学时四、 实验内容1、用TSQL建立一个“学生选课数据库”，在此基础上用SQL语句建立该数据库包含的学生表，课程表，学生选修表：学生表:Student(Sno,Sname,Ssex,Sage,Sd

2、ept) 其中Sno为主键且Sname不能为空值，且取值唯一、Ssex只能取值男或女、Sage 在15到30之间:SnoSnameSsexSageSdeptS01S02S03S04S05S06王建平刘华范林军李伟黄河长江男女女男男男211918191820自动化自动化计算机数学数学数学create database 选课数据库use 选课数据库create table student(sno char(5) primary key, sname char(10) not null unique, ssex char(3) constraint ssex_ch check (ssex in(男,

3、女), sage int not null constraint sage_ch check (sage between 15 and 30), sdept char(10)课程表:Course(Cno,Cname,Cpno,Credeit) 其中Cno为主键CnoCnameCpnoCreditC01英语NULL4C02数据结构C052C03数据库C022C04DB_设计C033C05C+NULL3C06网络原理C073C07操作系统C053create table course(cno char(5) primary key, cname char(10) not null, cpno ch

4、ar(5), credit int not null)insert into coursevalues(C01,英语,null,4)insert into coursevalues(C02,数据结构,C05,2)insert into coursevalues(C03,数据库,C02,2)insert into coursevalues(C04,DB_设计,C03,3)insert into coursevalues(C05,C+,null,3)insert into coursevalues(C06,网络原理,C07,3)insert into coursevalues(C07,操作系统,C

5、05,3)学生选修表:SC(Sno,Cno,Grade) 其中Sno,Cno为主键同时又为外键、Grade值在0到100;SnoCnoGradeS01C0192S01C0384S02C0190S02C0294S02C0382S03C0172S03C0290S04C0375create table sc(sno char(5) not null, cno char(5) not null, grade int constraint grade_ch check(grade between 0 and 100), primary key(sno,cno), constraint fk_sno fo

6、reign key (sno) references student(sno), constraint fk_cno foreign key (cno) references course(cno)insert into scvalues(S01,C01,92)insert into scvalues(S01,C03,84)insert into scvalues(S02,C01,90)insert into scvalues(S02,C02,94)insert into scvalues(S02,C03,82)insert into scvalues(S03,C01,72)insert in

7、to scvalues(S03,C02,90)insert into scvalues(S04,C03,75)2.索引的建立、删除用SSMS的方式为Student表按Sno(学号)升序建唯一索引用TSQL语句为Course表按Cno(课程号)升序建唯一索引，create unique index course_cnoon course(cno asc)用TSQL语句为SC表按Sno(学号)升序和Cno(课程号)号降序建唯一索引。create unique index sc_sno_cnoon sc(sno asc,cno desc)能否用TSQL语句再为表Students的Sname(姓名)和

8、Sno(学号)列上建立一个聚簇索引？若不能说明原因？不能，因为student表中主键sno唯一，则已默认存在聚集索引，而每个表只能有一个聚集索引，故不能再建立另一个聚集索引。用TSQL语句删除基本表SC上的唯一索引。drop index sc_sno_cnoon sc3.用TSQL完成如下视图的建立、查询、修改及删除1)建立数学系学生的视图C_Student，并要求进行修改和插入操作时仍需保证该视图只有数学系的学生，视图的属性名为Sno，Sname，Sage，Sdept。create view c_studentas select sno,sname,sage,sdept from stude

9、nt where sdept=数学with check option2) 用SSMS的方式建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图Student_CR。3) 定义一个反映学生出生年份的视图Student_birth(Sno, Sname, Sbirth)。create view student_birthas select sno,sname,2013-sage as sbirth from studentgo4)建立先修课程为空的课程视图v_coursecreate view v_courseas select cno,cname fr

10、om course where cpno is nullgo5)建立成绩高于90分的女生成绩视图v_F_grade（包括学号，姓名，课程号及成绩列）create view v_f_gradeas select student.sno,sname,o,grade from student,sc,course where student.sno=sc.sno and o=o and ssex=女 and grade90go6)建立视图S_AVGAGE（其中包括性别SSEX与平均年龄AVG_AGE两列）用以反映男生、女生的平均年龄create view s_avgageas select ssex,

11、avg(sage) as avg_age from student group by ssex7)对前面创建的视图S_AVGAGE执行更新操作：UPDATE S_AVGAGESET AVG_AGE = 85WHERE ssex = 女上述语句能否成功执行？为什么？对视图或函数S_AVGAGE 的更新或插入失败，因其包含派生域或常量域。8) 在数学系的学生视图C_Student中找出年龄(Sage)小于20岁的学生姓名(Sname)和年龄(Sage)。select sname,sagefrom c_studentwhere sage8510) 将数学系学生视图C_Student中学号为S05的学

12、生姓名改为“黄海”。update c_studentset sname=黄海where sno=S0511) 向数学系学生视图C_Student中插入一个新的学生记录，其中学号为“S09”，姓名为“王海”，年龄为20岁。insert into c_student(sno,sname,sage,sdept)values(S09,王海,20,数学)12) 删除数学系学生视图C_Student中学号为“S09”的记录。deletefrom c_studentwhere sno=S09四、思考题1. 在一个表中可以有多个聚簇索引吗？为什么？聚簇索引也叫簇类索引，是一种对磁盘上实际数据重新组织以按指定的

13、一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。2. 视图有哪些优点？使用视图的优点如下：查询的简单性：将复杂的查询(如多表的连接查询)定义为视图，保留了用户所关心的数据内容，剔除了那些不必要的冗余数据，使其数据环境更加容易控制，从而达到简化用户浏览和操作的目的；安全保护：数据库管理员可以在限制表用户的基础上进一步限制视图用户，可以为各种不同的用户授予或撤销在视图上的操作权限，这样，视图用户只能查询或修改他们各自所能见到的数据，从而保证数据库中数据的安全；掩盖数据库的复杂性：使用视图可以把数据库的设计和用户的使用屏蔽开来，当基本表发生更改或重新组合时，只需要修改视图的定义即可。用户还能够通过视图获得和数据库中的表一致的数据。3. 总结创建视图的方法有几种，各种方法实现的步骤，各举一例实现。两种。一种是SSMS创建，另一种是TSQL语句创建。