东北大学数据库实验报告.docx
《东北大学数据库实验报告.docx》由会员分享，可在线阅读，更多相关《东北大学数据库实验报告.docx（37页珍藏版）》请在冰豆网上搜索。
东北大学数据库实验报告
数据库实验报告
班级：
计算机1105班
学号：
20113263
姓名：
张少杰
时间：
2014年6月19日
实验
（一）:
熟练掌握SQL语言
实验目的：
熟悉上机环境，创建数据库，在数据库上建立关系模式，插入数据，进行相应的查询操作。
实验内容：
具体包括如下三部分。
一、熟悉上机环境。
客户/服务器结构，数据库服务器在一台NT服务器上，同学们通过客户机（操作系统为Windows2000）上安装的SQLServer客户端程序,使用SQLServer数据库服务器。
具体包括：
1.了解SQLServer环境。
鼠标点击开始，进入“MicrosoftSQLServer企业管理器”，点击SQLServer组下的数据库服务器（服务器名称为NEUC-201S（WindowsNT））,可以看到服务器上的圆形标志变为绿色，说明客户端程序已与服务器连接成功。
点击服务器（NEUC-201S（WindowsNT））下的数据库，可以看到服务器上已建立的数据库，你可访问你有权访问的数据库，并进行相应的操作功能。
因为，数据库服务器上建有许多数据库,每个数据库都有一些合法的用户。
2.鼠标点击开始，进入“MicrosoftSQLServer查询分析器”，输入用户名和口令，进入SQL查询分析器。
如：
你登录的客户机为27号，那么请以用户名user27，口令为user27登录，登录后缺省连到数据库user27上，user27/user27是数据库user27的创建者，因此用户user27/user27具有在数据库user27上创建表等一切权力。
3.在SQL查询分析器环境下，你就可进行SQL命令语句操作了。
二、在数据库useri上创建学生选课有关关系模式，并插入相应的数据，实现有关查询。
1.描述学生、课程情况的E-R图如下，请将其转化为用关系数据模型描述的关系模式
根据E-R图按如下代码建立三个表：
CREATETABLEStudent
（SnoCHAR（9）PRIMARYKEY,
SnameCHAR（20）UNIQUE,
SsexCHAR
（2）,
SageSMALLINT,
SdeptCHAR（20）
）;
CREATETABLECourse
（CnoCHAR（4）PRIMARYKEY,
CnameCHAR（40）,
CpnoCHAR（4）,
CreditSMALLINT,
FOREIGNKEY（Cpno）REFERENCESCourse（Cno）
）;
CREATETABLESC
（SnoCHAR（9）,
CnoCHAR（4）,
GradeSMALLINT,
PRIMARYKEY（Sno,Cno）,
FOREIGNKEY（Sno）REFERENCESStudent（Sno）,
FOREIGNKEY（Cno）REFERENCESCourse（Cno）
）;
在Course表中插入学时字段
altertableCourseaddCtimesmallintNULL
运行后建表成功。
2.在数据库中定义这些关系模式，并向基本表中插入如下数据：
INSERT
INTOStudent（Sno,Sname,Ssex,Sdept,Sage）
VALUES（'201100001','张一','男','IS',18）;
INSERT
INTOStudent（Sno,Sname,Ssex,Sdept,Sage）
VALUES（'201100002','张二','女','IS',18）;
INSERT
INTOStudent（Sno,Sname,Ssex,Sdept,Sage）
VALUES（'201100003','张三','男','IS',18）;
INSERT
INTOStudent（Sno,Sname,Ssex,Sdept,Sage）
VALUES（'201100004','张四','女','IS',18）;
INSERT
INTOStudent（Sno,Sname,Ssex,Sdept,Sage）
VALUES（'201100005','张五','男','IS',18）;
INSERT
INTOStudent（Sno,Sname,Ssex,Sdept,Sage）
VALUES（'201100006','张六','女','IS',18）;
INSERT
INTOCourse（Cno,Cname,Cpno,Credit）
VALUES（'1','数据库原理',NULL,2）;
INSERT
INTOCourse（Cno,Cname,Cpno,Credit）
VALUES（'2','数据结构',NULL,2）;
INSERT
INTOCourse（Cno,Cname,Cpno,Credit）
VALUES（'3','C语言',NULL,2）;
INSERT
INTOCourse（Cno,Cname,Cpno,Credit）
VALUES（'4','算法',NULL,2）;
INSERT
INTOCourse（Cno,Cname,Cpno,Credit）
VALUES（'5','高等数学',NULL,2）;
INSERT
INTOSC（Sno,Cno）
VALUES（'201100001','1'）;
INSERT
INTOSC（Sno,Cno）
VALUES（'201100002','2'）;
INSERT
INTOSC（Sno,Cno）
VALUES（'201100003','3'）;
INSERT
INTOSC（Sno,Cno）
VALUES（'201100004','4'）;
INSERT
INTOSC（Sno,Cno）
VALUES（'201100005','5'）;
3.插入相应的数据，试着插入重复的元组，结果如何？
INSERT
INTOSC（Sno,Cno）
VALUES（'201100005','5'）;
4.在已建立的关系模式之上（已插入一些数据）建立主键约束，参照约束和用户定义的约束（要求学生年龄不小于14岁，不大于35岁），如果约束不能建立，请分析原因，修改后建立上述约束。
altertablestudentaddcheck（Sagebetween14and35）
在建立主键约束前如果存在数据不满足将要建立的主键约束条件，则不能建立主键约束。
先将相关数据修改为满足主键约束条件再建立主键约束则操作成功。
5.如果约束建立后，请试着插入重复元组，结果如何？
试着插入重复元组，无法插入：
INSERT
INTOStudent（Sno,Sname,Ssex,Sage,Sdept）
VALUES（'201100005','张五','男',18,'IS'）;
实验
（二）:
熟练掌握SQL语言
用SQL完成下列查询
1.求选修了课程的学生的学号，要求：
（1）不使用distinct语句，写出其SQL语句
SelectSnofromSC
（2）使用distinct语句，消除重复元组，写出其SQL语句
selectdistinctSnofromSC
（3）如果该查询写成：
selectStudent.SnofromStudent,SCwhereStudent.Sno=SC.Sno
请问该查询结果和上面哪个结果是相同的？
（3）和
（1）的结果相同
2.求开设的课程号、课程名、学时和学分，要求对查询结果的列重新命名。
selectCnoMyCno,CnameMyCname,CtimeMyCtime,CreditMyCredictfromCourse
3.求计算机系和数学系的学生学号、姓名和年龄。
selectSno,Sname,SagefromStudentwhereSdept='CS'orStudent='Math'
4.求不是数学系、计算机系的学生的情况，要求select后写*代替列出所有的列名。
select*fromStudentwhereStudent.Sdept!
='CS'andStudent.Sdept!
='Math'
5.求全体学生的信息，要求按年龄升序排列。
select*fromStudentorderbySageasc
6.求计算机系年龄在18~20岁之间的学生姓名和年龄。
selectSname,SagefromStudentwhereSage>=18andSage<=20andSdept='CS'
7.求姓名是以“XX”开头的计算机系学生。
select*fromStudentwhereSnamelike'张李%'andSdept='CS'
实验（三）:
熟练掌握SQL语言
1.求选修了2课程的学生的学号、姓名、成绩。
selectStudent.Sno,Student.Sname,SC.GradefromStudent,SCwhereSC.Sno=Student.SnoandSC.Cno=2
2.求学号为95开头的学生的学号和所选修的课程的学分。
selectStudent.Sno,Course.CreditfromStudent,SC,CoursewhereStudent.Snolike'2012%'andStudent.Sno=SC.SnoandSC.Cno=Course.Cno
3求选修2课程且成绩为80分以上的学生的学号、姓名和成绩。
selectStudent.Sno,Student.Sname,SC.GradefromStudent,SCwhereStudent.Sno=SC.SnoandSC.Cno=2andSC.Grade>80
4求计算机系每个学生的姓名，选修的课程名和成绩。
selectStudent.Sname,SC.Cno,SC.GradefromStudent,SCwhereStudent.Sdept='CS'andStudent.Sno=SC.Sno
5求每个学生的学号及平均成绩。
selectSno,AVG（Grade）fromSCgroupbySno
6求男学生每一年龄组中超过1人的年龄组及人数。
selectSage,Count（Sno）fromStudentgroupbySagehavingCount（Sno）>1
7求每一门课程的间接先行课号。
selectC1.Cno,C2.CpnofromCourseC1,CourseC2whereC1.Cpno=C2.Cno
8.求选修了全部课程的学生的学号。
selectSnofromStudentwherenotexists（select*fromCoursewherenotexists（select*fromSCwhereSno=Student.SnoandCno=Course.Cno））;
实验（四）:
数据库的安全性
实验目的：
了解和使用安全子系统和检查。
实验内容：
具体包括如下二部分。
一熟练掌握SQL（续）
1.修改表Students,在其中加入一个字段性别sexchar
（1）
CREATETABLEStudent
（SnoCHAR（9）,
SnameCHAR（20）,
SageSMALLINT,
SdeptCHAR（20）
）;
CREATETABLESC
（SnoCHAR（9）,
CnoCHAR（4）,
GradeSMALLINT
）;
INSERT
INTOStudent（Sno,Sname,Sdept,Sage）
VALUES（'201100003','张一','IS',18）;
INSERT
INTOStudent（Sno,Sname,Sdept,Sage）
VALUES（'201100002','张二','CS',18）;
INSERT
INTOStudent（Sno,Sname,Sdept,Sage）
VALUES（'201100001','张三','CS',18）;
INSERT
INTOStudent（Sno,Sname,Sdept,Sage）
VALUES（'201100004','张四','CS',18）;
INSERT
INTOStudent（Sno,Sname,Sdept,Sage）
VALUES（'201100005','张五','CS',18）;
INSERT
INTOStudent（Sno,Sname,Sdept,Sage）
VALUES（'201100006','张六','IS',18）;
INSERT
INTOSC（Sno,Cno,Grade）
VALUES（'201100002','2',88）;
INSERT
INTOSC（Sno,Cno，Grade）
VALUES（'201100002','1',98）;
INSERT
INTOSC（Sno,Cno,Grade）
VALUES（'201100001','2',77）;
INSERT
INTOSC（Sno,Cno,Grade）
VALUES（'201100001','1',80）;
INSERT
INTOSC（Sno,Cno,Grade）
VALUES（'201100005','2',92）;
altertableStudentaddsexchar
（1）;
2.创建索引：
在Students表的Sno上创建唯一聚簇索引,在SC表的（Sno,Cno）上创建唯一索引.查询Students表的内容,记录的顺序有变化吗?
查询SC表的内容,记录的顺序有变化吗?
CreateuniqueclusteredindexStudent_snoonStudent（Sno）;
createuniqueindexSno_cnoonSC（Sno,Cno）;
3.创建视图
▪创建计算机系学生视图CS_Students，其中包括学号、年龄和性别。
带withcheckopition子句
不带withcheckopition子句
时各插入一计算机系学生记录，通过视图查询插入的记录，结果如何？
通过表Students查询插入的记录，查看所在系字段上的值？
createviewCS_students1asselectSno,Sage,sexfromStudentwhereSdept='CS'withcheckoption
INSERTINTOCS_students1
VALUES（'201100005',18,’m’）;
createviewCS_students2asselectSno,Sage,sexfromStudentwhereSdept='CS'
INSERTINTOCS_students2
VALUES（'201100007',18,’m’）;
创建一视图，表示学生的平均成绩，其中包括Sno,avgGrade。
通过该视图插入一学生姓名和平均成绩，结果如何？
通过该视图查找平均成绩在90分以上的学生的学号和平均成绩，结果又如何？
createviewStuavggrade（sno,avggrade）
asselectSno,avg（Grade）fromSCgroupbySC.sno
InsertintoStuavggrade
values（'201100003',87）
selectsno,avggrade
fromStuavggrade
whereavggrade>=90
二了解和使用安全子系统
1、将sqlserver的一合法帐户（user62）加入到你的数据库当中来，并给其授权，使之：
SP_grantdbaccessuser62;
具有创建表的权限
grantcreatetabletouser62;
具有对Students表查询的权限
grantselectonStudenttouser62;
具有对Studetns表的年龄字段修改的权限
grantupdate（Sage）onStudenttouser62
2、用user62/user62进入系统，完成权限验证：
在Students表上，实现select*fromStudents
select*fromdb63.user63.Student
在course表上，实现select*fromcourse
select*fromdb63.user63.course
updateStudetnssetSage=Sage+1
updatedb63.user63.StudentsetSage=Sage+1
select*fromdb63.user63.Student
updateStudentssetSdept=’CS’
updatedb63.user63.StudentsetSdept='CS'
select*fromdb63.user63.Student
3、你是数据库的dbo，你来查看dept表，结果如何？
使用setuser命令使自己成为user60后，查看dept表，结果如何？
实验（五）:
数据库的完整性
实验目的：
了解和使用完整性定义和检查
1、创建表Studetns，要求学生的年龄在16和25岁之间，并且所在系的缺省取值为‘CS’
在Studetns表上插入一学生记录（‘9921’，‘cccc’,’MA’,23,’f’）
在Studetns表上插入一学生记录（‘9922’，‘ddd’,’MA’,27,’m’）
在Studetns表上插入一学生记录（‘9923’，‘eeeee’,21,’m’）
在Studetns表上插入一学生记录（‘9922’，‘ddd’,’MA’,23,’m’）
CREATETABLEStudent
（SnoCHAR（9）notnull,
SnameCHAR（20）,
Sdeptchar（20）default'CS',
SageSMALLINTcheck（Sage>=16andSage<=25）,
Ssexchar
（2）
）;
insertintoStudent（Sno,Sname,Sdept,Sage,Ssex）
values（'9921','cccc’,’MA’,23,’f’）;
insertintoStudent（Sno,Sname,Sdept,Sage,Ssex）
values（'9922','ddd’,’MA’,27,’m’）;
insertintoStudent（Sno,Sname,Sdept,Sage,Ssex）
values（'9923','eeeee’,21,’m’）;
insertintoStudent（Sno,Sname,Sdept,Sage,Ssex）
values（'9922','ddd’,’MA’,23,’m’）;
2、创建表course，要求课号作为主键
在course表上插入一课程记录（’c9’,40,’cname1’,1）
在course表上插入一课程记录（’c9’,40,’cname2’,1）
在course表上插入一课程记录（null,25,’canme3’,2）
createtablecourse
（cnochar（10）primarykey,
ctimechar（10）,
cnamechar（20）,
cscorechar（10）
）;
insertintocourse（cno,ctime,cname,cscore）
values（’c9’,40,’cname1’,1）;
insertintocourse（cno,ctime,cname,cscore）
values（’c9’,40,’cname2’,1）;
insertintocourse（cno,ctime,cname,cscore）
values（null,25,’canme3’,2）;
3、修改表Studetns，指定学号为主键，然后
插入一记录（’9908’,’shang’,’CI’,20）
插入一记录（’9908’,’shang’,’CI’,20）
插入一记录（’’,’liang’,’CS’,18）
插入一记录（’’,’an’,’CS’,19）
ALTERTABLEStudentADDCONSTRAINTpk_SnoPRIMARYKEY（Sno）
insertintoStudent（Sno,Sname,Sdept,Sage）
values（'9908','shang','CI',20）;
insertintoStudent（Sno,Sname,Sdept,Sage）
values（'9908','shang','CI',20）;
insertintoStudent（Sno,Sname,Sdept,Sage）
values（‘’,’liang’,’CS’,18）;
insertintoStudent（Sno,Sname,Sdept,Sage）
values（‘’,’an’,’CS’,19）;
4、创建sc表，要求表sc中的学号参照表Studetns中的学号，sc中的课号参照course中的课号。
参照约束创建完成后，向这三个表插入数据。
先在sc表插入一个选课记录，看看有什么情况发生？
先删除sc中的某个记录，看看有什么情况发生？
将Studetns表中的学号9906改为9907，看看有什么情况发生？
insertintoStudent（Sno,Sname,Sdept,Sage,Ssex）
values（'9906','ddd’,’MA’,23,’m’）;
createtablesc
（Snochar（9）,
Cnochar（10）,
Gradeint,
foreignkey（Sno）referencesStudent（Sno）,
foreignkey（Cno）referencescourse（cno）;
insertintoSC（Sno,Cno,Grade）
values（'9922','c9',85）;
deletefromscwhereSno='9922';
updateStudentssetSno=‘9907’
whereSno=‘9906’;
如果SC引用了9906
insertintoSC（Sno,Cno,Grade）
values（'9906','c9
升级会员 