实验五 视图与索引

实验五指导5.1 实体完整性1）在数据库School中建立表Stu_Union，进行主键约束，在没有违反实体完整性的前提下插入并更新一条记录use Schoolcreate table Stu_Union(Sno char(8)not null unique,Sname char(8),Ssex char(1),Sage int,constraint PK_Stu_Union primary key(Sno))insert Stu_Union values('456','小二','M','22')update Stu_Union set Sno=''WHERE Sage='22'update Stu_Union set Sno='789'where Sname='小二'select*from Stu_Union2）演示违反实体完整性的插入操作use Schoolinsert Stu_Union values('789','小三','M','23')3）演示违反实体完整性的更新操作use Schoolupdate Stu_Union set Sno=NULL where Sno='789'4）演示事务的处理，包括事务的建立，处理以及出错时的事务回滚,演示事务处理和批处理的区别。

提示：SQL2005相关语句为BEGIN TRANROLLBACK TRANCOMMIT TRAN可以这样演示：新建一个包含两条语句的事务，使第一条成功而第二条失败，然后查看整个事务是否回滚。

重要提示：SQL默认只回滚出错的语句，要回滚整个事务，需要预先执行以下语句：SET XACT_ABORT ON事务的建立use Schoolset xact_abort onbegin transaction t1insert into Stu_Union values('001','张三','M','22')insert into Stu_Union values('002','李四','F','24')insert into Stu_Union values('003','李四','F','24')select*from Stu_Unioncommit transaction t1处理以及出错时的回滚use Schoolset xact_abort onbegin transaction t2insert into Stu_Union values('004','王五','M','23')select*from Stu_Unioninsert into Stu_Union values('789','钱六','F','21')commit transaction t2use Schoolselect*from Stu_Union5）通过建立Scholarship表，插入一些数据。

实验六：关系图、索引和视图的定义及维护一、实验目的1.掌握创建关系图的方法。

2.掌握创建、查看和删除索引的操作和方法。

3.理解视图的含义，能使用SQL Server企业管理器创建视图并对其进行管理。

二、实验环境已安装SQL Server 企业版的计算机(120台)；具有局域网环境,有固定IP;三、实验学时2学时四、实验要求1.索引和视图的创建和管理也可以用查询分析器实现，参照课本上的相关Transact-SQL命令，写出对应的Transact-SQL实现代码。

2.将视图的创建、修改、删除过程与实验二中表的创建、修改、和删除进行对照，看看有什么相同点和不同点。

3.完成实验报告；五、实验内容及步骤以系统管理员身份登录到SQL Server服务器，在SQL Server2005界面中实现第1题操作，其余题目用SQL Server2005界面及T-SQL 语句实现：1.使用创建数据库关系图向导为学生选课数据库中的Student表，Course表，SC表创建关系图。

2.为Student表创建一个基于Sname（姓名）的按降序排列的聚簇索引Stusname。

CREATE CLUSTERED INDEX Stusname ONStudent(Sname DESC)3.为学生—课程数据库中的Student,Course,SC 3 个表建立索引。

其中student按学号升序建唯一索引，course按课程号升序建唯一索引，SC按学号升序和课程号降序建唯一索引，索引名称分别为Stusno,Coucno,Scno。

CREATE UNIQUE INDEX Stusno ON Student(Sno)；CREATE UNIQUE INDEX Coucno ON Course(Cno)；CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC)；4.删除 Student表中的Stusname索引。

实验五视图的创建和使用5.1概述5.1.1任务一理解视图的概念视图是一个虚拟表，其内容由查询定义。

同真实的表一样，视图包含一系列带有名称的列和行数据。

但是，视图并不在数据库中以存储的数据集合形式存在。

行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

对其中所引用的基表来说，视图的作用类似于筛选。

定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。

视图被定义后便存放在数据库中，对视图中的数据的操作与对表的操作一样，可以对其进行查询、修改和删除，但对数据的操作要满足一定的条件。

当对视图所看到的数据进行修改时，相应的基表的数据也会发生变化，同时，若基表的数据发生变化，这种变化也会自动地反映到视图中。

5.1.2任务二理解视图的优点用户可以根据自己的实际需要创建视图，使用视图有很多优点，主要有以下几点：1、简单性视图可以屏蔽数据的复杂性，简化用户对数据库的操作。

使用视图，用户可以不必了解数据库的结构，就可以方便地使用和管理数据。

那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2、逻辑数据独立性视图可以使应用程序和数据库表在一定程度上独立。

如果没有视图，应用一定是建立在表上的。

有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。

3、安全性通过视图用户只能查询和修改他们所能见到的数据。

数据库中的其他数据则既看不见也取不到。

5.2创建视图创建视图的方法有三种:在创建视图前请考虑如下原则:只能在当前数据库中创建视图.视图名称必须遵循标识符的规则,且对每个用户必须为唯一.此外,该名称不得与该用户拥有的任何表的名称相同.可在其他视图和引用视图的过程之上建立视图.SQLServer 2000允许嵌套多达32级视图若要创建视图,数据库所有者必须授予用户创建视图的权限,并且用户对视图定义中所引用的表或视图要有适当的权限5.2.1任务一使用企业管理器创建视图使用企业管理器创建视图的具体操作步骤如下:1、打开企业管理器窗口，打开“新建视图”对话框。

实验五索引与视图定义1 实验目的与要求(1) 掌握索引的建立和删除操作。

(2) 掌握视图的创建和查询操作。

2 实验内容(1) 根据基本表创建以下索引：①在图书表中按出版社号建立一个非聚集索引PublishingnoIdx。

②在读者表中按身份证号建立一个非聚集索引IdentifycardIdx。

③在读者表中，首先按工作单位的升序，然后按借书数量降序建立一个非聚集索引WorkunitCountIdx。

(2) 创建一个只含清华大学出版社信息的图书视图BookView。

(3) 创建一个包含读者编号、读者姓名、图书编号、图书名称、借阅日期、归还日期的视图BorrowView。

(4) 创建一个视图，要求显示至少借阅了3本书的读者信息ReaderView。

(5) 在视图BorrowView中查询2010年4月22日以前被借阅的图书。

(6) 在视图ReaderView中查询姓张的读者信息。

(7) 在视图BorrowView基础上再建一个只包含合生元有限公司读者所借图书信息的视图BorrowView1。

(8) 删除视图BorrowView。

参考答案(1) 根据基本表创建以下索引：①在图书表中按出版社号建立一个非聚集索引PublishingnoIdx。

create nonclustered index PublishingnoIdx on book(Publishingno)②在读者表中按身份证号建立一个非聚集索引IdentifycardIdx。

create nonclustered index IdentifycardIdx on reader(Identifycard)③在读者表中，首先按工作单位的升序，然后按借书数量降序建立一个非聚集索引WorkunitCountIdx。

create nonclustered index WorkunitCountIdx onreader(Workunit,borrowCount desc)(2) 创建一个只含清华大学出版社信息的图书视图BookView。

7-1编程在teaching 数据库中的student 表的sname 列上创建唯一索引IDX_sname。

USE teachingGOCREATE UNIQUE INDEX IDX_sname ON student(sname)7-2 编程在teaching数据库中的student表的studentno和classno列上创建组合索引IDX_sc。

USE teachingIF EXISTS(SELECT name FROM sysindexes WHERE name='IDX_sc')DROP INDEX student.scGOCREATE INDEX IDX_sc ON student(studentno,classno)7-3 重新组织teaching 数据库中student 表上的IDX_sc 索引。

USE teachingALTER INDEX IDX_sc ON dbo.student REORGANIZE7-4 重新生成teaching 数据库中student 表上的IDX_sname 索引，设置填充索引，将填充因子设置为80%，设置将中间排序结果存储在tempdb中。

USE teachingALTER INDEX IDX_sname ON dbo.studentREBUILD WITH(PAD_INDEX =ON,FILLFACTOR= 80,SORT_IN_TEMPDB =ON)7-5 重新生成teaching 数据库中student 表上的C_studentno 索引，将其修改为聚集唯一索引，并指定该索引的叶级页的填充度为70%。

USE teachingGOCREATE UNIQUE CLUSTERED INDEX C_studentnoON dbo.student(studentno)WITH(PAD_INDEX =ON,FILLFACTOR= 70,DROP_EXISTING =ON)7-6 删除teaching 数据库中student 表上的聚集索引C_studentno 和非聚集索引UC_classno。

实验5 视图的创建和使用【实验目的】掌握创建与删除视图的方法掌握更新视图的方法掌握视图的查询操作【实验内容】一、创建视图1、用企业管理器创建视图此实验中以mydb1数据库中的test01、student、course、sc表为基础创建视图。

打开企业管理器，展开左侧窗口树形结构上的数据库节点，选中要创建视图的数据库（这里是mydb1），右击数据库中的“视图”对象，选择“新建视图”命令，如图1所示，就会弹出如图2所示的视图设计器窗口。

图【1】新建视图图【2】视图设计器在视图设计器窗口中右击选择“添加表”或单击工具栏上“添加表”按钮，添加所需要的表。

如图3所示。

图【3】添加表在表窗口中各字段名的前面方框里单击，选择相应的字段，或是在下面“列”的下拉列表框中选择字段，并可在“准则”列中输入提取记录时的过滤条件，在“或”列中输入提取记录所用的附加条件，最后在上面窗口中右击，选择“运行”，则在最下面的窗口中就能看到视图的结果。

如图4所示。

图【4】选择视图内容视图设计完后，点击保存图标，在弹出的“另存为”对话框中输入视图名，此实验为score_view ，最后点击“确定”按钮。

如图5所示。

图【5】保存视图2、使用向导创建视图（略）3、用T-SQL语句创建视图格式：CREATE VIEW view_name[(column1,column2,……)] Asselect_statement[with check option]例：创建所有选课学生的信息视图，如图6所示。

（3）重命名表test001为test01。

当发现表名不恰当的时候，需要为表重新命名。

使用系统存储过程sp_rename 可以为表重新命名，语法如下：sp_rename old_table_name , new_table_name请在查询管理器中输入如下的语句：Use mydb1Gosp_rename test001 , test01 /*EXEC sp_rename test001 , test01 也可以*/Go系统执行，将返回如图6所示的结果。

学号：20164477 姓名：陈家凤实验五SQL语言一、目的与要求1.掌握SQL语言的查询功能；2.掌握SQL语言的数据操作功能；3.掌握对象资源管理器建立查询、索引和视图的方法；二、实验准备1.了解SQL语言的查改增删四大操作的语法；2.了解查询、索引和视图的概念；3.了解各类常用函数的含义。

三、实验内容(一)SQL查询功能使用提供的studentdb数据库文件，先附加到目录树中，再完成下列题目，SQL命令请保存到脚本文件中。

1．基本查询(1)查询所有姓王的学生的姓名、学号和性别Select St_Name,St_Sex,St_IDFrom st_infoWhere St_Name like'王%'图5-1(2)查询全体学生的情况，查询结构按班级降序排列，同一班级再按学号升序，并将结果存入新表new中select*into newfrom st_infoorder by Cl_Name desc,st_ID asc图5-2(3)对S_C_info表中选修了“体育”课的学生的平均成绩生成汇总行和明细行。

(提示：用compute汇总计算)因2014版本已不支持compute关键字，所以选择用其他方式。

Select c_no,scoreFrom s_c_infoWhere c_no=group by c_no,score图5-32．嵌套查询(1)查询其他班级中比“材料科学0601班”的学生年龄都大的学生姓名和年龄select st_name,born_datefrom st_infowhere cl_name!='材料科学0601班'and born_date<(select min(born_date) from st_info where cl_name='材料科学0601班')图5-4(2)用exists查询选修了“”课程的学生姓名select st_namefrom st_infowhere exists(select*from s_c_info where c_no=9710041 andst_id=st_info.st_id)图5-5(3)用in查询找出没有选修“”课程的学生的姓名和所在班级。