五邑大学《数据库原理》实验指导书.docx
《五邑大学《数据库原理》实验指导书.docx》由会员分享，可在线阅读，更多相关《五邑大学《数据库原理》实验指导书.docx（19页珍藏版）》请在冰豆网上搜索。
五邑大学《数据库原理》实验指导书
《数据库原理》实验指导书
V2.0
执笔：
日期：
实验一数据库、表、索引的定义
一、实验目的：
通过本实验加深对SQLServer2008环境下数据定义语言的理解，能够熟练地建立数据库，并通过SQL语句来创建和修改基本表、创建和取消索引。
二、实验内容：
使用SQLServer2008软件，练习创建和修改数据库、表、索引。
三、实验要求：
建立一个新的SQLServer设备管理数据库，使用SQL的数据定义功能建立数据库中的各个基本表（共3个），并按要求对有关表进行修改。
每次操作后通过企业管理器查看有关对象的变化。
四、实验学时：
2学时
五、实验步骤：
1．创建数据库
1）使用企业管理器创建数据库
运行MicrosoftSQLServerManagementStudio并建立于数据库的连接后，进入如图1界面：
在“数据库”上右击，进入图2所示界面。
在图3中进行相关设置，主要包括：
1输入数据库名称（根据学生本人的学号取名），所有者为默认。
2在“数据库文件”页中输入文件逻辑名称。
3若要更改数据文件的物理存储位置，在“路径”中修改，（注意确定文件目录已经存在、并允许读写）。
4在“初始大小”项上输入希望的大小。
5可在“自动增长”中修改默认设置。
【说明一】：
数据文件和日志文件的作用
数据文件又包括：
主数据文件和辅助数据文件。
主数据文件的扩展名是.mdf，每个数据库只能包含一个主数据文件。
辅助数据文件的扩展名是.ndf。
辅助数据文件可以同主数据文件存放在相同的位置，也可以存放在不同的地方。
日志文件记录页的分配和释放以及对数据库数据的修改操作。
日志文件的扩展名为.ldf，它包含用于恢复数据库的日志信息。
每个数据库必须至少有一个日志文件。
【说明二】：
数据库文件的属性
在定义数据库的数据文件和日志文件时，可以指定如下属性：
Ø文件名及其位置。
每个数据库的数据文件和日志文件都具有一个逻辑名称以及文件的物理存放位置。
Ø文件大小。
可以指定每个数据文件和日志文件的大小，以MB为单位。
Ø增长方式。
如果需要的话，可以指定文件是否自动增长，该选项的默认配置为自动增长。
Ø最大大小。
指定文件增长的最大大小。
默认是大小无限制。
2）使用Transact-SQL语句创建数据库
1语法结构
CREATEDATABASE数据库名
[ON
[<文件格式>[,…n]]
]
[LOGON{<文件格式>[,…n]}]
<文件格式>:
:
=
（[NAME=逻辑文件名,]
FILENAME=‘操作系统下的物理路径和文件名’
[,SIZE=文件初始大小]
[,MAXSIZE=文件最大大小|UNLIMITED]
[,FILEGROWTH=增量值]）[,…n]
2有关说明
ØON关键字表示数据库是根据后面的参数来创建的；
Øn是一个占位符，表明可为新数据库指定多个文件；
ØLOGON子句用于指定该数据库的事务日志文件；
ØNAME用于指定数据库文件的逻辑文件名；
ØFILENAME用于指定数据库文件的存放位置及在磁盘上的文件名；
ØSIZE用于指定数据库文件的初始大小，可以加上MB或KB，默认为MB；
ØMAXSIZE用于指定数据库文件的最大大小，可以加上MB或KB，默认为MB。
省略此项表示最大大小无限制；
ØFILEGROWTH用于指定数据库文件的增加量，可以加上MB或KB或%，默认为MB。
省略此项表示不自动增长。
3实验内容
创建一个数据库，数据库名称为你的学号，例如：
“AP0406150”，此数据库包含一个数据文件和一个事务日志文件。
数据文件只有主数据文件，其逻辑文件名为“AP0406150_data”，其物理文件名为“AP0406150.mdf”，存放位置在默认目录下，其初始大小为10MB，最大大小为30MB，自动增长时的递增量为5MB。
事务日志文件的逻辑文件名为“AP0406150_log”，物理文件名为“AP0406150.ldf”，也存放在默认目录下，初始大小为3MB，最大大小为12MB，自动增长时的递增量为2MB。
【注意】：
上机时要根据实际情况确定存放位置，由于计算中心的大多数机器的C、D驱动器有保护卡，建议在驱动器E中建立自己的目录，有关文件将存放在该目录中。
4语句格式
CREATEDATABASEAP0406150
ON
（NAME=AP0406150_data,
FILENAME='E:
\AP0406150\AP0406150.mdf',
SIZE=10,
MAXSIZE=30,
FILEGROWTH=5）
LOGON
（NAME=AP0406150_log,
FILENAME='E:
\AP0406150\AP0406150.ldf',
SIZE=3,
MAXSIZE=12,
FILEGROWTH=2）
2．创建基本表
点击屏幕左上角的“新建查询”，选择操作数据库，在查询界面中输入以下数据定义语句后，按“执行”按钮，即建立三个基本表：
⏹学生表：
Student（Sno，Sname，Ssex，Sage，Sdept）
其中：
属性Sno，Sname，Ssex，Sage和Sdept分别表示学号、姓名、性别、年龄和所在系，Sno为主码，加下划线表示。
CREATETABLEStudent（
SnoCHAR（9）PRIMARYKEY，
SnameCHAR（20）UNIQUE，
SsexCHAR
（2），
SageCHAR（3）,
SdeptCHAR（20）
）；
⏹课程表：
Course（Cno，Cname，Cpno，Ccredit）
其中：
属性Cno，Cname，Cpno和Ccredit分别表示课程号、课程名、先修课号和学分，Cno为主码，加下划线表示。
CREATETABLECourse（
CnoCHAR（4）PRIMARYKEY，
CnameCHAR（40），
CpnoCHAR（4），
CcreditSMALLINT，
FOREIGNKEY（Cpno）REFERENCESCourse（Cno）
）；
⏹学生选课表：
SC（Sno，Cno，Grade）
其中：
属性Sno，Cno和Grade分别表示学号、课程号和成绩，主码为属性组（Sno，Cno），加下划线表示。
CREATETABLESC（
SnoCHAR（9），
CnoCHAR（4），
GradeSMALLINT，
PRIMARYKEY（Sno，Cno），
FOREIGNKEY（Sno）REFERENCESStudent（Sno），
FOREIGNKEY（Cno）REFERENCESCourse（Cno）
）；
3．修改基本表
⏹在Student表中增加“入学时间”列，数据类型为日期型
ALTERTABLEStudent
ADDS_entranceDATETIME；
⏹将年龄的数据类型由字符型改为整型，因SQLSERVER2000没有MODIFY功能，只能先删除再添加，即：
ALTERTABLEStudent//先删除
DROPCOLUMNSage;
ALTERTABLEStudent//再添加
ADDSageINT;
⏹删除Student表中的“入学时间”列
ALTERTABLEStudent
DROPCOLUMNS_entrance；
⏹在Student表中增加一个完整性约束定义，使年龄的取值只能在15到40之间
ALTERTABLEStudent
ADDCHECK（SageBETWEEN15AND40）;
4．建立索引
为学生-课程数据库中的Student、Course、SC三个表建立索引。
其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。
CREATEUNIQUEINDEXStusnoONStudent（Sno）;
CREATEUNIQUEINDEXCoucnoONCourse（Cno）;
CREATEUNIQUEINDEXSCnoONSC（SnoASC,CnoDESC）;
5．取消索引
取消按步骤4建立的索引。
DROPINDEXStudent.Stusno;//DROPINDEX表名.索引名
DROPINDEXCourse.Coucno;
DROPINDEXSC.SCno;
实验二数据更新语言的使用
一、实验目的：
通过本实验熟悉数据库的数据更新操作，加深SQL语言中对数据库进行数据插入、更新、删除操作的理解。
二、实验内容：
使用SQLServer2008软件，练习SQL语言中的插入、更新、删除命令。
三、实验要求：
使用数据插入语句INSERTINTO向实验一所建立的基本表中输入若干数据，然后再通过UPDATE或DELETE语句对已经插入的数据进行更新或删除，每执行一次操作都将通过查询命令SELECT语句查看数据变化。
四、实验学时：
2学时
五、实验步骤：
1．插入内容
学生表：
Student
Sno
Sname
Ssex
Sage
Sdept
200215121
李勇
男
19
CS
200215122
刘晨
男
20
CS
200215123
王敏
女
20
MA
200215125
张立
男
22
IS
课程表：
Course
Cno
Cname
Cpno
Ccredit
1
数据库
5
4
2
数学
2
3
信息系统
1
4
4
操作系统
6
3
5
数据结构
7
4
6
数据处理
2
7
PASCAL语言
6
4
学生选课表：
SC
Sno
Cno
Grade
200215121
1
92
200215121
2
85
200215121
3
88
200215122
2
90
200215122
3
80
2．插入数据
使用insert语句将给定数据分别插入上述基本表中：
INSERTINTOStudentVALUES（‘200215121’,’李勇’,’男’,19,’CS’）;
INSERTINTOStudentVALUES（‘200215122’,’刘晨’,’男’,20,’CS’）;
INSERTINTOStudentVALUES（‘200215123’,’王敏’,’女’,20,’MA’）;
INSERTINTOStudentVALUES（‘200215124’,’王敏乙’,’女’,20,’MA’）;
INSERTINTOStudentVALUES（‘200215125’,’张立’,’男’,22,’IS’）;
【注意】：
用SELECT命令观察结果。
INSERTINTOCourse（Cno,Cname,Ccredit）
VALUES（‘2’,’数学’,2）;
INSERTINTOCourse（Cno,Cname,Ccredit）
VALUES（‘6’,’数据处理’,2）;
INSERTINTOCourseVALUES（‘4’,‘操作系统’,’6’,3）;
INSERTINTOCourseVALUES（‘7’,’PASCAL语言’,’6’,4）;
INSERTINTOCourseVALUES（‘5’,’数据结构’,’7’,4）;
INSERTINTOCourseVALUES（‘1’,’数据库’,’5’,4）;
INSERTINTOCourseVALUES（‘3’,’信息系统’,’1’,4）;
先按课程号顺序向Course表中插入记录，再按上述顺序向Course表插入记录，体会两者的不同，并用SELECT命令观察结果。
INSERTINTOSC（Sno,Cno）VALUES（‘200215121’,’1’）;
INSERTINTOSC（Sno,Cno,Grade）VALUES（‘200215121’,’2’,85）;
INSERTINTOSCVALUES（‘200215121’,’3’,88）;
INSERTINTOSCVALUES（‘200215122’,’2’,90）;
INSERTINTOSCVALUES（‘200215122’,’3’,80）;
3．修改数据
给学号为200215121学生录入课程号为1（数据库）的成绩。
UPDATESC
SETGrade=92
WHERESno=‘200215121’andCno=‘1’;
4．删除数据
删除学号为200215124的学生记录。
DELETE
FROMStudent
WHERESno=‘200215124’;
实验三数据查询语言的使用
一、实验目的：
通过本实验加深对SQL语句中数据查询语言的理解，能够使用SQL语句对数据库进行单表查询、连接查询、嵌套查询、集合查询和统计查询。
二、实验内容：
使用SQLServer2008软件，练习SQL语言中的查询命令。
三、实验要求：
结合实验二中有关数据，按要求完成给定的查询操作。
四、实验学时：
4学时
五、实验步骤：
1．单表查询
⏹查询全体学生的学号与姓名
SELECTSno，SnameFROMStudent；
⏹查询全体学生的详细记录
SELECTSno，Sname，Ssex,Sage,Sdept
FROMStudent；
SELECT*
FROMStudent；
⏹查询全体学生的姓名及其出生年份
SELECTSname，2006-Sage
FROMStudent；
⏹将学生按年龄的升序排序。
SELECT*FROMStudentORDERBYSage
⏹查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。
SELECT*FROMStudent
ORDERBYSdept,SageDESC
⏹查询计算机系年龄在20岁以下的学生姓名。
SELECTSnameFROMStudent
WHERESdept=‘CS’ANDSage<20；
2．连接查询
⏹查询每个学生及其修课的情况。
SELECTStudent.*,SC.*
FROMStudent,SC
WHEREStudent.Sno=SC.Sno/*将Student与SC连接起来*/
⏹查询与刘晨在同一个系学习的学生的姓名和所在的系。
SELECTS2.Sname,S2.Sdept
FROMStudentS1，StudentS2
WHERES1.Sdept=S2.SdeptAND
S1.Sname='刘晨'ANDS2.Sname!
='刘晨’
⏹查询选修2号课程，且成绩在90分以上的所有学生。
SELECTStudent.Sno,Sname
FROMStudent,SC
WHEREStudent.Sno=SC.SnoANDSC.Cno=‘2’ANDSC.Grade>90;
⏹查询每个学生的学号、姓名、选修的课程及成绩。
SELECTStudent.Sno,Sname，Cname,Grade
FROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;
3．嵌套查询
⏹查询与“刘晨”在同一个系学习的学生。
SELECTSno,Sname,Sdept
FROMStudent
WHERESdeptIN
（SELECTSdeptFROMStudent
WHERESname='刘晨'）；
⏹查询选修了“数据库”课程的学生的学号、姓名。
SELECTSno,Sname
FROMStudent
WHERESnoIN
（SELECTSno
FROMSC
WHERECnoIN
（SELECTCno
FROMCourse
WHERECname='数据库'））；
4．集合查询
⏹查询选修了课程1或者选修了课程2的学生。
SELECTSnoFROMSC
WHERECno=‘1’
UNION
SELECTSnoFROMSC
WHERECno=‘2’；
5．统计查询
⏹统计学生总人数。
SELECTCOUNT（*）FROMStudent；
⏹统计选修了课程的学生的人数。
SELECTCOUNT（DISTINCTSno）FROMSC；
⏹计算200215121号学生的考试成绩之和。
SELECTSUM（Grade）
FROMSC
WHERESno='200215121'；
⏹计算1号课程学生的考试平均成绩。
SELECTAVG（Grade）FROMSCWHERECno='1'；
⏹查询选修了1号课程的学生的最高分和最低分。
SELECTMAX（Grade）,MIN（Grade）FROMSCWHERECno=‘1'；
⏹查询修了3门以上课程的学生的学号。
SELECTSno
FROMSC
GROUPBYSno
HAVINGCOUNT（*）>3；
⏹统计每门课程的选课人数，列出课程号和人数。
SELECTCnoas课程号,COUNT（Sno）as选课人数
FROMSC
GROUPBYCno；
实验四视图的定义与使用
一、实验目的：
通过本实验熟悉SQL支持的有关视图操作，能够熟练使用SQL语句来创建需要的视图、对视图进行查询和取消视图等。
二、实验内容：
使用SQLServer2008软件，练习通过SQL语言创建视图、对视图进行查询和取消视图的操作。
三、实验要求：
结合实验二中有关数据，按要求完成有关操作。
四、实验学时：
2学时
五、实验步骤：
1．建立视图
⏹建立信息系学生的视图。
CREATEVIEWIS_Student
AS
SELECTSno,Sname,Sage
FROMStudent
WHERESdept=‘IS'
⏹建立信息系学生的视图，并要求进行插入、修改操作时还需保证该视图中只有信息系学生。
CREATEVIEWIS_Student1
AS
SELECTSno,Sname,Sage
FROMStudent
WHERESdept=‘IS‘
WITHCHECKOPTION
⏹建立信息系选修了‘c01’号课程的学生的视图。
CREATEVIEWV_IS_S1（Sno,Sname,Grade）
AS
SELECTStudent.Sno,Sname,Sage
FROMStudent，SC
WHEREStudent.Sno=SC.SnoAND
Sdept=‘IS’ANDSC.Cno=‘c01’
⏹建立信息系选修了‘c01’号课程且成绩在90分以上的学生的视图。
CREATEVIEWV_IS_S2
AS
SELECTSno,Sname,Grade
FROMV_IS_S1
WHEREGrade>=90
⏹定义一个反映学生出生年份的视图。
CREATEVIEWBT_S（Sno,Sname,Sbirth）
AS
SELECTSno,Sname,2006-Sage
FROMStudent
⏹定义一个存放每个学生的学号及平均成绩的视图。
CREATEVIEWS_G（Sno,AverageGrade）
AS
SELECTSno,AVG（Grade）
FROMSC
GROUPBYSno
2．查询视图（根据视图进行查询）
⏹根据视图IS_Student，查询信息系学生的信息。
SELECT*
FROMIS_Student；
⏹根据视图V_IS_S2，查询信息系选修了‘c01’号课程且成绩在90分以上的学生。
SELECT*
FROMV_IS_S2；
⏹在信息系学生的视图中找出年龄小于20岁的学生。
SELECTSno,Sage
FROMIS_Student
WHERESage<20；
3．更新视图
⏹将信息系学生视图IS_Student中学号为200215122的学生姓名改为‘刘辰’。
UPDATEIS_Student
SETSname=‘刘辰’
WHERESno=‘200215122’；
4．删除视图
⏹删除IS_Student视图。
DROPVIEWIS_Student；
实验五数据控制语言的定义与使用
一、实验目的：
通过本实验熟悉SQL的数据控制功能，能够熟练通过企业管理器或使用SQL语句来向用户授予和收回权限。
二、实验内容：
（1）使用GRANT语句对用户授权：
对单个用户或多个用户授权、使用保留字PUBLIC对所有用户授权。
有关操作对象包括数据库、视图、基本表等。
（2）使用WITHGRANTOPTION子句授予用户传播该权限的权利。
（3）使用REVOKE子句收回授权。
【注意】：
在SQLSERVER2008中，权限分为：
（1）对象权限：
是指用户对数据库中的表、视图等对象的操作权，用来设置是否允许查询、增加、删除和修改数据等。
（2）语句权限：
用来设置是否允许执行CREATETABLE、CREATEVIEW等与创建数据库对象有关的操作。
（3）隐含权限：
是指由SQLSERVER预定义的服务器角色、数据库角色、数据库拥有者和数据库对象拥有者所具有的权限。
三、实验要求：
结合所建立的数据库和表，按要求完成向用户授予和收回权限的操作，在完成授权或收回权限操作后，再按该用户登录数据库，并进行有关操作，观察操作权限的变化。
四、实验学时：
2学时
五、实验步骤：
1．建立用户
在所建立的数据库中建立五个用户：
USER1、USER2、USER3、USER4、USER
升级会员 