实验5 索引和视图

学号：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=29000011group 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查询选修了“9710041”课程的学生姓名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查询找出没有选修“9710041”课程的学生的姓名和所在班级。

上机实验五1.如何通过企业管理器和Transact_SQL语句对视图进行创建、修改和删除？通过企业管理器：创建步骤：最后保存该视图即可。

修改步骤：进入该界面重新修改该视图即可。

删除步骤：通过Transact_SQL语句：创建：create view 视图名[with encryption]as select_statement[with check option]修改：利用alter view 视图名删除：利用drop view 视图名1．通过Transact_SQL语句创建一个视图，计算各个班级的各门课程的平均分。

Create view v_avggradeAsSelect substring(pupil.sno,1,len(pupil.sno)-2) as 班级，Cname as 课程名称，Avg(grade) as 平均分From pupil,sc,gradeWhere pupil.sno=sc.sno and o=oGroup by substring(pupil.sno,1,len(pupil.sno)-2), Cname 3. 通过Transact_SQL语句创建一个视图，显示‘高等数学’未过的学生的信息。

Create view v_gradenotpassAsSelect pupil.sno,sname,cname,gradeFrom pupil,sc,courseWhere pupil.sno=sc.sno and o=o and grade<60 And cname=’高等数学’3.通过Transact_SQL语句创建一个视图，查询的数据为99521班学生的考试成绩。

Create view v_grade99521AsSelect pupil.sno,sname,cname,gradeFrom pupil,sc,courseWhere pupil.sno=sc.sno and o=o andleft(pupil.sno,5)=’99521’4.如何通过企业管理器创建索引？步骤：点击‘新建’按钮点击‘确定’点击‘关闭’5.如何删除索引？选中该索引点击‘删除’即可或利用drop index 表名. 索引名完成删除。

实验六：关系图、索引和视图的定义及维护一、实验目的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索引。

实验报告
课程名称：数据库技术项目名称：索引和视图
姓名：专业：班级：学号同组成员
1注：1、实验预习部分包括实验环境准备和实验所需知识点准备。

2、若是单人单组实验，同组成员填无。

5.分别使用企业管理器和系统存储过程sp_helpindex查看grade
6.使用企业管理器的“向导”工具对grade表创建一个聚集索引和唯一索引。

10.在studentsdb数据库中，使用Transact-SQL
12.分别使用企业管理器和Transact-SQL语句修改视图每个学生所学课程数目。

15.利用视图v_stu_i为student_info表添加一行数据：学号为
16.利用视图v_stu_i删除学号为
实验报告成绩（百分制）__________ 实验指导教师签字：__________
3注：1、实验小结应包含实验所需知识点和实验方法的总结，实验心得体会等。

2、分组实验需包含同组讨论内容。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

实验训练4：视图和索引的构建与使用1. 简介在数据库中，视图是一种虚拟表，它是基于查询结果动态生成的。

视图可以看作是一个预定义的查询，通过视图可以方便地访问和组织数据。

本文将介绍在数据库中如何创建和使用视图，以及如何构建索引来提高查询性能。

2. 创建视图在数据库中，可以使用CREATE VIEW语句来创建一个视图。

视图的创建需要满足以下条件：•视图的名字必须唯一。

•视图的定义必须是一个有效的SELECT语句。

下面是一个创建视图的示例：CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;其中，view_name是视图的名称，column1和column2是视图所包含的列名，table_name是数据来自的表名，condition是过滤条件。

创建视图之后，可以使用SELECT语句来查询视图的内容，就像查询表一样。

3. 修改视图在数据库中，可以使用ALTER VIEW语句来修改一个已经存在的视图。

修改视图可以包括添加、删除和修改列。

下面是一个修改视图的示例：ALTER VIEW view_name ADD column_name datatype;其中，view_name是要修改的视图的名称，column_name 是要添加的列名，datatype是列的数据类型。

4. 删除视图在数据库中，可以使用DROP VIEW语句来删除一个已经存在的视图。

下面是一个删除视图的示例：DROP VIEW view_name;其中，view_name是要删除的视图的名称。

5. 索引的构建与使用索引是一种数据结构，可以提高数据库的查询性能。

数据库中的索引可以分为以下几种类型：•B-Tree索引：适用于等值查询和范围查询。

•哈希索引：适用于等值查询。

•全文索引：适用于关键词搜索。

在数据库中，可以使用CREATE INDEX语句来创建索引。

实验五视图和索引的创建及使用实验目的：1、理解视图和索引的概念2、学会使用企业管理器和T-SQL语句创建视图和索引3、理解视图和索引的优缺点，掌握视图和索引的管理和维护。

实验内容：一视图1、使用企业管理器建立视图，进入“添加表”对话框，选择表student，在“视图设计器”界面的表选择区中选择所有列选型，在SQL语句区，编辑并执行，查看视图显示结果。

2、保存视图名为view_student.3、建立学生学号、姓名、性别、课程号、成绩的视图v_sc，查看v_sc中的数据。

4、建立学生学号、姓名、出生年份的视图v_age查看V_age中的数据。

5、建立…JSJ‟ 系的学生学号、姓名、性别、年龄的视图v_JSJ6、建立每门课程的平均分的视图v_avggrade二索引1、使用企业管理器为学生表student创建一个以stud_id 为索引关键字的惟一聚簇索引。

2、将上一步所建立的索引名称修改为new_index。

3、将前述所建立的new_index 索引删除。

4、使用T-SQL 语句为课程表Course 创建一个名为Course_Index 的惟一非聚簇索引，索引关键字为教师编号Course_id，降序，填充因子为80%。

5、使用T-SQL 语句将课程表中的Course_Index 删除。

6、为student表创建一个基于“年龄，学号”组合列的非聚集、复合索引cj_xh_index。

7、为lesson_info 创建一个基于“课程号course_id，课程类型course_type” 组合列的惟一、聚集、复合索引kc_lx_index。

实验结果中记录结果，不能省略。

附：建立实验相关数据库与表CREATE DATABASE TESTDBON PRIMARY(NAME='TESTDB',FILENAME='d:\TESTDB.mdf',SIZE=3072KB,MAXSIZE='UNLIMITED',FILEGROWTH=1024KB)LOG ON(NAME='TESTDB_LOG',FILENAME='d:\TESTDB_LOG.ldf',SIZE=1024KB,MAXSIZE=2048GB,FILEGROWTH=10%)COLLATE Chinese_PRC_CI_ASCREATE TABLE Student(Stu_ID CHAR(12) NOT NULL PRIMARY KEY,Stu_Name VARCHAR(50),Stu_Passwd V ARCHAR(50),Ssex CHAR(2),Sage INT,Sdept V ARCHAR(50))CREATE TABLE Course(Course_ID [char](9) NOT NULL,Course_Name [varchar](50) NULL,Course_Credit [int] NULL,Course_Pro_ID [char](9) NULL,Teacher_ID [char](8) NULL,Books_ID [char](12) NULL,)use TESTDB1CREATE TABLE Stu_Course(Stu_ID CHAR(12) NOT NULL,Course_ID CHAR(9) NOT NULL,Term CHAR(6),Score DECIMAL(3,2),Credit INT,CONSTRAINT PK_Stu_Course PRIMARY KEY(Stu_ID,Course_ID))USE [TESTDB]GOINSERT INTO student V ALUES('11012901','杜月梅','147258','女',21,'物理系'); INSERT INTO student V ALUES('11012902','李建丽','258369','女',22,'物理系'); INSERT INTO student V ALUES('11012903','高勇','369789','女',23,'物理系'); INSERT INTO student V ALUES('12023001','王军雅','123456','男',20,'计算机'); INSERT INTO student V ALUES('12023002','王晓玲','456789','女',21,'计算机');INSERT INTO course V ALUES('1','高等数学',6,null,'1','1');INSERT INTO course V ALUES('2','C语言',3,null,'2','2');INSERT INTO course V ALUES('3','数据结构',2,2,'3','3');INSERT INTO Stu_Course V ALUES('11012901','1','1',82,6);INSERT INTO Stu_Course V ALUES('11012902','1','1',90,6); INSERT INTO Stu_Course V ALUES('11012903','1','1',75,6); INSERT INTO Stu_Course V ALUES('12023001','1','1',96,6); INSERT INTO Stu_Course V ALUES('12023002','1','1',90,6); INSERT INTO Stu_Course V ALUES('12023001','2','1',86,3); INSERT INTO Stu_Course V ALUES('12023002','2','1',70,3);。

实训五索引5.1实训目的●理解索引的种类及用途●掌握使用企业管理器建立索引的方法。

●理解使用T-SQL语句建立索引的方法。

5.2实训要求●熟练使用企业管理建立各种索引。

●熟练使用T-SQL语句建立各种索引。

●了解维护索引。

5.3 实训内容及步骤1使用企业管理器还原“学生管理数据库”步骤略。

2使用企业管理器建立各种索引例1：在student表上按姓名建立索引。

（1）打开企业管理器，在树状目录中展开“学生管理库”节点，并选择其下一级节点“表”。

此时在右边的内容窗口将会列出数据库中包含的全部数据表；（2）选择表student，并单击鼠标右键。

在弹出菜单中选择“所有任务→管理索引…”命令，打开“管理索引”对话框如图5-1管理索引对话框。

（3）在“管理索引”对话框中从“数据库”下拉列表中选择“学生管理库”，从“表/视图”下拉列表中选择[dbo].[ student]；（4）此时在“现有索引”的列表框中会列出表student中现存的索引。

选择“新建索引”按钮，弹出“新建索引”对话框；（5）在“新建索引”对话框的索引名称文本框中输入索引的名称idx_name，然后选择字段“姓名”并设置索引属性；如下图5-2新建索引对话框所示。

图5-1管理索引对话框图5-2新建索引对话框（6）设置索引选项以后，单击“确定”按钮，返回到“管理索引”对话框。

在这个对话框中可以看到新创建的索引“idx_name”。

单击“关闭”按钮，退出“管理索引”对话框，完成索引的创建。

例2：在course表上按课程名建立唯一性索引。

操作方法同例1题，并将“唯一值”复选框选中即可，如图5-3建立索引对话框。

图5-3建立索引对话框例3：在student表上按专业名降序、姓名升序建立复合性索引。

操作方法同例1题，选中“所学专业”字段，单击“向上”按钮，将排列次序选为降序如图5-4建立复合性索引。

图5-4建立复合性索引3使用企业管理器建立以下索引（1）在course表的课程号字段创建唯一性索引，索引名称“IX_cid”。

1.视图1.1 什么是视图视图view也被称为虚表，即虚拟的表，是一组数据的逻辑表示，其本质是对应一条select语句，结果集被赋予一个名字，即视图名字。

视图本身不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

创建视图语法：create [or replace] view 视图名[(alias[,alias...])]as subquery;说明：语法中的subquery是select查询语句，对应的表被称为基表。

根据视图对应的子查询种类分为几种类型：1）select语句是基于单表建立的，且不包含任何函数运算、表达式或分组函数，叫做简单视图，此时视图是基表的子集；2）select语句同样是基于单表，但包含了单行函数、表达式、分组函数或group by 子句，叫做复杂视图；3）select语句是基于多个表的，叫做连接视图。

1.2 视图的作用1）简化复杂查询；2）视图本质上就是一条select语句，所以当访问视图时，只能访问到所对应的select语句中涉及到的列，对基表中的其他列祈祷安全和保密的作用，可以限制数据访问。

1.3 视图创建授权grant create view to 用户名;1.4 创建简单视图CREATEVIEW v_emp_10ASSELECT empno, ename, sal, deptnoFROM empWHERE deptno =10;查看视图结构：desc v_emp_10查询视图：select * from v_emp_10;select id,name,salary from v_emp_10;1.5 对视图进行insert操作当对视图进行DML操作时，实际上是对基表的DML操作。

对视图执行DML操作的基本原则：1）简单视图能够执行DML操作，下列情况除外：在基表中定义了非空列，但简单视图对应的select语句并没有包含非空列，导致这个非空列对视图不可见，这时无法对视图执行insert操作；2）如果视图定义中包含了函数、表达式、分组语句，distinct关键字或rownum伪列，不允许执行DML操作；3）DML操作不能违反基表的约束条件。