广东海洋大学 - 计算机科学与技术 - 数据库实验3

GDOU-B-11-112

广东海洋大学学生实验报告书（学生用表）

实验名称 实验3 索引和视图 学院(系) 学生姓名

课程名称 数据库系统原理

专业

计科

实验地点 科技楼

课程号 1620072

信息学院

学号

班级 1113 实验日期 04-26

实验三 索引和视图

一、 实验目的

1． 掌握利用SSMS和T—SQL语句创建和删除索引的两种方法。 2． 掌握利用SSMS和T—SQL语句创建、查询、更新及删除视图的方法。 二、 实验要求

1． 能认真独立完成实验内容；

2． 实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；

3． 验后做好实验总结，根据实验情况完成实验报告。情况完成总结报告。 三、 实验学时 2学时 四、 实验内容

1、 用T—SQL建立一个“学生选课数据库”，在此基础上用SQL语句建立该数据库包含的学生

表，课程表，学生选修表：

CREATE DATABASE 学生选课数据库 ON PRIMARY ( ) LOG ON (

NAME=Student_log,

FILENAME='D:\\学生选课数据库.ldf', SIZE=5MB, ) GO

FILEGROWTH=10% NAME=Student_dat,

FILENAME='D:\\学生选课数据库.mdf',

SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=10%

学生表:Student

CREATE TABLE Student(

Sno CHAR(10) PRIMARY KEY NOT NULL,

Sname CHAR(10) ,

Ssex CHAR(2) CHECK(Ssex='男' OR Ssex='女'), Sage SMALLINT CHECK(Sage BETWEEN 15 AND 30), Sdept CHAR(20)

) ;

INSERT INTO Student( Sno

,Sname,Ssex,Sage,Sdept )

VALUES ('S01','王建平', '男', 21, '自动化')

INSERT INTO Student( Sno ,Sname,Ssex,Sage,Sdept ) VALUES ('S02', '刘华', '女', 19, '自动化')

INSERT INTO Student( Sno ,Sname,Ssex,Sage,Sdept ) VALUES ('S03', '范林军', '女', 18, '计算机')

INSERT INTO Student( Sno ,Sname,Ssex,Sage,Sdept ) VALUES ('S04', '李伟', '男', 19 , '数学' );

INSERT INTO Student( Sno ,Sname,Ssex,Sage,Sdept ) VALUES ('S05', '黄河', '男', 18, '计算机')

INSERT INTO Student( Sno ,Sname,Ssex,Sage,Sdept ) VALUES ('S06',

'长江', '男', 20 , '数学' );

课程表:Course

CREATE TABLE Course( Cno CHAR(4) PRIMARY KEY , Cname CHAR(10) NOT NULL, Cpno CHAR(4),

Credit INT CHECK(Credit>=0 AND Credit<=100), Teacher NCHAR(4) );

INSERT INTO Course ( Cno,Cname,Cpno,Credit ) VALUES ('C01','英语',NULL, 4 )

INSERT INTO Course ( Cno,Cname,Cpno,Credit ) VALUES ('C02','数据结构','C05',2)

INSERT INTO Course ( Cno,Cname,Cpno,Credit ) VALUES ('C03','数据库','C02',2)

INSERT INTO Course ( Cno,Cname,Cpno,Credit ) VALUES ('C04','DB_设计','C03',3)

INSERT INTO Course ( Cno,Cname,Cpno,Credit ) VALUES ('C05','C++',NULL,3)

INSERT INTO Course ( Cno,Cname,Cpno,Credit ) VALUES ('C06','网络原理','C07',3)

INSERT INTO Course ( Cno,Cname,Cpno,Credit ) VALUES ('C07','操作系统','C05',3)

学生选修表:SC

CREATE TABLE SC(

Sno char(10) FOREIGN KEY REFERENCES Student(Sno), Cno CHAR(4) FOREIGN KEY REFERENCES Course(Cno), Grade INT CHECK( Grade>=0 AND Grade<=100 ), PRIMARY KEY(Sno,Cno)

);

INSERT INTO SC ( Sno, INSERT INTO SC ( Sno, INSERT INTO SC ( Sno, INSERT INTO SC ( Sno, INSERT INTO SC ( Sno, INSERT INTO SC ( Sno, INSERT INTO SC ( Sno, INSERT INTO SC ( Sno,

Cno, Cno, Cno, Cno, Cno, Cno, Cno, Cno,

Grade) Grade) Grade) Grade) Grade) Grade) Grade) Grade)

VALUES ('S01','C01',92) VALUES ('S01','C03',84) VALUES ('S02','C01',90) VALUES ('S02','C02',94) VALUES ('S02','C03',82) VALUES ('S03','C01',72) VALUES ('S03','C02',90) VALUES ('S03','C03',75)

2.索引的建立、删除

①用SSMS的方式为Student表按Sno(学号)升序建唯一索引

②用T—SQL语句为Course表按Cno(课程号)升序建唯一索引，

CREATE UNIQUE INDEX 课程号 ON Course(Cno ASC )

③用T—SQL语句为SC表按Sno(学号)升序和Cno(课程号)号降序建唯一索引。

CREATE UNIQUE INDEX PK_SC ON SC(Sno ASC,Cno DESC)

④能否用T—SQL语句再为表Students的Sname(姓名)和Sno(学号)列上建立一个聚簇索引？若不能说明原因？

答:不能，因为一个表只能由一个聚集索引。

⑤用T—SQL语句删除基本表SC上的唯一索引。

DROP INDEX SC.PK_SC

3.用T—SQL完成如下视图的建立、查询、修改及删除

1)建立数学系学生的视图C_Student，并要求进行修改和插入操作时仍需保证该视图只有数学系的学生，视图的属性名为Sno，Sname，Sage，Sdept。

CREATE VIEW C_Student(Sno,Sname,Sage,Sdept) AS

SELECT Sno, Sname, Sage, Sdept FROM Student WHERE Sdept='数学' WITH CHECK OPTION

2) 用SSMS的方式建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图Student_CR。

3) 定义一个反映学生出生年份的视图Student_birth(Sno, Sname, Sbirth)。

CREATE VIEW Student_birth(Sno, Sname, Sbirth) AS

SELECT Sno, Sname, 2013-Sage FROM Student

4)建立先修课程为空的课程视图v_course

CREATE VIEW v_course( Cno, Cpno ) AS

SELECT Cno, Cpno FROM Course WHERE Cpno IS NULL

5) 建立成绩高于90分的女生成绩视图v_F_grade（包括学号，姓名，课程号及成绩列）

CREATE VIEW v_F_grade(学号,姓名,课程号,成绩列) AS

SELECT Student.Sno, Student.Sname, Course.Cname, SC.Grade FROM Student INNER JOIN

SC ON Student.Sno = SC.Sno INNER JOIN Course ON SC.Cno = Course.Cno

6)建立视图S_AVGAGE（其中包括性别SSEX与平均年龄AVG_AGE两列）用以反映男生、女 生的平均年龄

CREATE VIEW S_AVGAGE(SSEX, AVG_AGE)

AS

SELECT Ssex,AVG( Sage ) FROM Student GROUP BY Ssex

7)对前面创建的视图S_AVGAGE执行更新操作： UPDATE S_AVGAGE SET AVG_AGE = 85 WHERE ssex = '女'

上述语句能否成功执行？为什么？

答:不能成功.” 对视图或函数'S_AVGAGE' 的更新或插入失败，因其包含派生域或常量域。” 8) 在数学系的学生视图C_Student中找出年龄(Sage)小于20岁的学生姓名(Sname)和年龄 (Sage)。

SELECT Sname,Sage FROM C_Student WHERE Sage<20

9) 在Student_CR视图中查询成绩在85分以上的学生学号(Sno)、姓名(Sname)和课程名称 (Cname)。

SELECT Sno,Sname,Cname FROM Student_CR WHERE Grade>85

10) 将数学系学生视图C_Student中学号为S05的学生姓名改为“黄海”。

UPDATE C_Student SET Sname = '黄海' WHERE Sno ='S05'

11) 向数学系学生视图C_Student中插入一个新的学生记录，其中学号为“S09”，姓名为“王海”，年龄为20岁。

INSERT INTO C_Student( Sno ,Sname ,Sage,Sdept ) VALUES ( 'S09','王海',20 ,'数学')

12) 删除数学系学生视图C_Student中学号为“S09”的记录。

DELETE C_Student WHERE Sno = 'S09'

四、思考题

1. 在一个表中可以有多个聚簇索引吗？为什么？ 答:不能，因为一个表只能由一个聚集索引。 2. 视图有哪些优点？

答:简化用户的操作;使用户能以多种角度看待同一数据;对重构数据库提供了一定程度的逻辑独立性;对机密数据提供保护;更清晰地表达查询.

3. 总结创建视图的方法有几种，各种方法实现的步骤，各举一例实现。

答:

1)使用T-SQL方法创建视图 举例:

建立数学系学生的视图C_Student，并要求进行修改和插入操作时仍需保证该视图只有数学系的学生，视图的属性名为Sno，Sname，Sage，Sdept。

CREATE VIEW C_Student(Sno,Sname,Sage,Sdept) AS

SELECT Sno, Sname, Sage, Sdept FROM Student WHERE Sdept='数学' WITH CHECK OPTION

2)使用SSMS的方法创建视图 举例:

建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图Student_CR。

成绩

指导教师

徐龙琴

日期 04-26

注:请用A4纸书写，不够另附纸。 第 页，共

9

页

本文来源：https://www.bwwdw.com/article/oyr7.html