数据库实验报告.docx
《数据库实验报告.docx》由会员分享，可在线阅读，更多相关《数据库实验报告.docx（20页珍藏版）》请在冰豆网上搜索。
数据库实验报告
实验一 创建数据库及关系表
一、实验目的
1. 掌握SQLServer数据库管理系统的使用，能够中该环境中进行日常数据库操作；
2. 掌握在SQLServer中使用图形化工具创建数据库的方法；
3.掌握建立关系表的语句，掌握定义主码约束及外码约束的语句；
4.掌握修改表结构的语句。
二、实验要求
1．了解SQLServer数据库的组成，会使用图形化工具创建数据库。
2．编写建立表及主、外码约束的T-SQL语句，并执行这些语句，在数据库中建立符合要求的关系表。
3．编写修改表结构的语句。
三、实验内容和步骤
1．创建符合如下条件的数据库：
数据库的名字为：
Students
数据文件的逻辑文件名为：
Students_dat，存放在D:
\Test录下（若D:
盘中无此子目录，可先建立此目录，然后再创建数据库。
）；
文件的初始大小为：
5MB；
增长方式为自动增长，每次增加1MB。
日志文件的逻辑文件名字为：
Students_log，也存放在D:
\Test目录下；
日志文件的初始大小为：
2MB；
日志文件的增长方式为自动增长，每次增加10%。
2．在已建立的Students数据库中，写出创建满足下述条件的四张表的SQL语句，并查看执行结果。
Student表结构
列名
说明
数据类型
约束
Sno
学号
普通编码定长字符串，长度为7
主码
Sname
姓名
普通编码定长字符串，长度为10
非空
Ssex
性别
普通编码定长字符串，长度为2
Sage
年龄
微整型（tinyint）
Sdept
所在系
普通编码不定长字符串，长度为20
Spec
专业
普通编码定长字符串，长度为10
Course表结构
列名
说明
数据类型
约束
Cno
课程号
普通编码定长字符串，长度为10
主码
Cname
课程名
普通编码不定长字符串，长度为20
非空
Credit
学时数
整型
Semester
学分
小整型
SC表结构
列名
说明
数据类型
约束
Sno
学号
普通编码定长字符串，长度为7
主码，引用Student的外码
Cno
课程号
普通编码定长字符串，长度为10
主码，引用Course的外码
Grade
成绩
小整型
Teacher表结构
列名
说明
数据类型
约束
Tno
教师号
普通编码定长字符串，长度为8
非空
Tname
教师名
普通编码定长字符串，长度为10
非空
Dept
所在部门
普通编码不定长字符串，长度为20
Salary
工资
定点小数，小数点前4位，小数点后2位
Birthday
出生日期
小日期时间型（smallldatetime）
Createtablestudent（
Snochar（7）primarykey,
Snamenchar（10）notnull,
Ssexnchar
（2）,
Sagetinyint,
Sdeptnvarchar（20）,
Specchar（10）
）
createtablecourse（
Cnochar（10）,
Cnamenvarchar（20）notnull,
Creditint,
Semestertinyint,
Primarykey（Cno）
）
createtablesc（
Snochar（7）notnull,
Cnochar（10）notnull,
Gradetinyint,
primarykey（Sno,Cno）,
foreignkey（Sno）referencesStudent（Sno）,
foreignkey（Cno）referencesCourse（Cno）,
）
createtableteacher（
Tnochar（8）notnull,
Tnamechar（10）notnull,
Deptnvarchar（20）,
Salarynumeric（6,2）,
Birtherysmalldatetime
）
执行结果：
2.写出实现如下操作的SQL语句，并查看执行结果：
（1）在SC表中添加一个新的修课类别列：
列名为：
XKLB，类型为char（4）。
altertablesc
addXKLBchar（4）
（2）将Course表中的Credit列的类型改为：
tinyint（微整型，取值范围在0～255）。
altertablecourse
altercolumnCredittinyint
（3）删除Student表的spec列。
altertablestudent
dropcolumnspec
（4）为Teacher表添加主码约束，其主码列为：
Tno。
altertableteacher
addconstrainty_s
primarykey（Tno）
3.执行下列语句，能否成功为什么
INSERTINTOSCVALUES（‘S001’,’C01’,88,’必修’）
答：
不成功，因为错误显示sc表的sno学号列有外键约束，它是引用的student学生表里面的学号，所以执行前面的语句时’s001’不存在于学生表中，所以执行不成功。
若在student学生表中插入学号’s001’的学生信息，如：
insertintostudentvalues（'s001','王盼','女',19,'数学系'）
则再执行该语句时成功。
四．实验小结：
通过这次实验，学习了SQLServer数据库管理系统的使用的方法，通过自己创建数据库和表，熟悉了上课中讲到的相关知识，在完成实验的同时对自己所编写的代码进行调试和查错，并且自己发现错误并改正，对知识的理解和掌握都有很大的帮助。
实验二 数据操作语句
一、实验目的
理解数据操作语句的功能。
二、实验要求
1．根据实验一建立的数据库和关系表，导入部分数据；
2．编写实现数据操作功能的SQL语句。
三、实验内容及步骤
1.查询计算机系年龄最大的三个学生的姓名和年龄。
selecttop3sname,sage,sdeptfromstudent
wheresdept='计算机系'
orderbysagedesc
2.统计每个学生的选课门数，并按选课门数的递增顺序显示结果。
selectsno,count（*）fromsc
groupbysno
orderbycount（*）
3.列出总成绩超过200分的学生，要求列出学号、总成绩。
selectsno,sum（grade）总成绩fromsc
groupbysno
havingsum（grade）>200
4.查询成绩80分以上的学生的姓名、课程号和成绩，并将结果按成绩的降序排列。
selectSname,cno,Gradefromsc
joinstudentson=
wheregrade>80
orderbygradedesc
5.查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的学生），并列出学生的学号、姓名、课程号和考试成绩。
select,Sname,Cno,Grade
fromstudentleftouterjoinsc
on=
6.列出“VB”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。
selecttop3,sname,sdept,gradefromstudents
joinscon=
joincoursecon=
wherecname='VB'
orderbygradedesc
7.查询哪些课程没有人选，要求列出课程号和课程名。
select,CnamefromCoursecleftjoinsc
on=
whereisnull
8．查询计算机系学生考试成绩高于全体学生的总平均成绩的学生的姓名、考试的课程名和考试成绩。
selectsname,cname,gradefromstudents
joinscon=
joincoursecon=
wheregrade>all
（selectavg（grade）fromsc）
andsdept='计算机系'
9．查询VB考试成绩最低的学生的姓名、所在系和VB成绩。
selecttop1sname,sdept,gradefromstudents
joinscon=
joincoursecon=
wherecname='VB'
orderbygrade
10.查询VB课程考试成绩高于VB平均成绩的学生的姓名。
selectsnamefromstudents
joinscon=
joincoursecon=
wherecname='VB'
andgrade>all
（selectavg（grade）fromsc
joincoursecon=
wherecname='VB'
）
11.查询平均成绩大于80分的课程的名字和学分。
selectcname,semesterfromcoursec
wherecnoin（
selectfromsc
groupby
havingavg（grade）>80）
12.统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列出课程号，选课人数及选课情况，其中选课情况为：
如果此门课程的选课人数超过100人，则显示“人多”；如果此门课程的选课人数在40～100，则显示“一般”；如果此门课程的选课人数在1～40，则显示“人少”；如果此门课程没有人选，则显示“无人选”。
select,count选课人数,
case
whencount（*）>100then'人多'
whencount（*）between40and100then'一般'
whencount（*）between1and40then'人少'
whencount（*）=0then'无人选'
endas选课情况
fromsc
groupby
13．查询至少选修了“9512102”号学生选修的全部课程的学生，列出学号和所选的课程号。
selectsno,cnofromscsc1
wherenotexists（
select*fromcoursecjoinscon=
wheresno='9512102'
andnotexists（
select*fromsc
where=and=）
14．查询至少选修了第2学期开设的全部课程的学生的姓名和所在系。
selectsname,sdeptfromstudents
wherenotexists（
select*fromcoursec
where=2
andnotexists（
select*fromsc
where=and=））
15．修改“VB”课程的考试成绩，如果是计算机系的学生，则增加4分；如果是信息系的学生则增加8分，其他系的学生增加10分。
updatescsetgrade=grade+
casesdept
when'计算机系'then4
when'信息系'then8
else10
end
fromstudentsjoinscon=
joincoursecon=
16.删除修课成绩小于50分的学生的修课记录
deletefromsc
wheregrade<50
17.删除信息系修课成绩小于50分的学生的修课纪录，分别用子查询和多表连接形式实现。
--子查询
deletefromsc
wheresnoin（
selectsnofromstudent
wheresdept='信息系'）
andgrade<50
--多表连接
deletefromsc
fromscjoinstudentson=
wheresdept='信息系'andgrade<50
18.将所有选修了“c01”课程的学生的成绩加10分。
updatescsetgrade=grade+10
fromsc
wherecno='c01'
19.将计算机系所有选修了‘计算机文化学’课程的学生的成绩加10分，分别用子查询和多表连接形式实现。
--多表连接
updatescsetgrade=grade+10
fromscjoinstudentson=
joincoursecon=
wherecname='计算机文化学'
andsdept='计算机系'
--子查询
updatescsetgrade=grade+10
wheresnoin（
selectsnofromstudent
wheresdept='计算机系'
andcnoin（
selectcnofromcourse
wherecname='计算机文化学'））
20．删除“VB”考试成绩最低的学生的VB修课记录。
Deletefromsc
wherecno=（selectcnofromcoursewherecname='VB'）
And
grade=（selectmin（grade）fromsc
wherecno=
（selectcnofromcoursewherecname='VB'）
）
21．修改高等数学的考试成绩，修改规则如下：
如果是计算机系学生，则加10分，如果是信息系学生则加5分，如果是数学系学生则分数不变。
updatescsetgrade=grade+
casesdept
when'计算机系'then10
when'信息系'then5
else0
end
fromstudentsjoinscon=
joincoursecon=
wherecname='高等数学'
四．实验小结
这次上机实验，主要对数据操作语句的基本运用和理解，在实验中从简单到复杂的问题中，一点点的掌握了SQL语言的功能，在实验中通过自己对语句的检验分析，还有老师的指导，基本掌握了数据库语言的运用，课后会多多巩固。
实验三数据完整性约束
一、实验目的
理解数据完整性约束的功能。
二、实验要求
1．掌握建立声明性数据完整性约束的SQL语句；
2．掌握建立过程完整性约束的SQL语句。
三、实验内容
1．在实验一建立的Students数据库中，编写建立满足完整性要求的定义表的SQL语句，执行并观察执行结果。
（说明：
表名、列名均用给出的中文）
（1）图书表，结构如下：
书号：
统一字符编码定长类型，长度为6，主码，
书名：
统一字符编码可变长类型，长度为30，非空，
第一作者：
普通编码定长字符类型，长度为10，非空，
出版日期：
小日期时间型，小于等于当前系统日期，
印刷数量：
小整型，取值范围：
1000～5000，默认为4000，
价格：
定点小数，小数部分一位，整数部分3位。
（2）书店表，结构如下：
书店编号：
统一字符编码定长类型，长度为6，主码，
店名：
统一字符编码可变长类型，长度为30，非空，
电话：
普通编码定长字符类型，12位长，取值形式：
010-8位数字
地址：
普通编码可变长字符类型，40位长。
前两个字符必须是：
北京。
（3）图书销售表（XSB），结构如下：
书号：
统一字符编码定长类型，长度为6，非空，
书店编号：
统一字符编码定长类型，长度为6，非空，
销售日期：
小日期时间型，非空，默认值为系统当前日期，
销售数量：
微整型，取值大于0，
主码（书号，书店编号，销售日期）；
书号为引用“图书表”的外码；
书店编号为引用“书店表”的外码。
createtable图书表（
书号nchar（6）PRIMARYKEY,
书名nvarchar（30）notnull,
第一作者char（10）notnull,
出版日期smalldatetimecheck（出版日期<=getdate（））,
印刷数量smallintdefault4000,
价格numeric（4,1）,
check（印刷数量between'1000'and'5000'）
createtable书店表（
书店编号nchar（6）primarykey,
店名nvarchar（30）notnull,
电话char（12）check（电话like'010-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'）,
地址nvarchar（40）check（地址like'北京%'）
）
createtable图书销售表（
书号nchar（6）notnull,
书店编号nchar（6）notnull,
销售日期smalldatetimedefaultgetdate（）notnull,
销售数量tinyintcheck（销售数量>0）,
primarykey（书号,书店编号,销售日期）,
foreignkey（书号）references图书表（书号）,
foreignkey（书店编号）references书店表（书店编号）
）
2.执行下列两条数据插入语句，是否都能执行成功为什么
INSERTINTO图书表
VALUES（‘T0001’,‘数据库系统基础’,‘张三’,‘2010-2-1’,3000,
成功。
INSERTINTO图书表
VALUES（‘T0002’,‘计算机网络’,‘张三’,‘2010-1-1’,500,
不成功，因为印刷数量是500，不满足规定的1000到5000。
2.执行下列两条数据插入语句，是否执行成功为什么
INSERTINTO书店表
VALUES（‘S001’,’新华书店’,’12345678’,’北京市海淀区’）
不成功，电话不满足格式。
修改后的电话：
3.执行下列两条数据插入语句，是否执行成功为什么
INSERTINTO图书销售表（书号，书店编号，销售数量）VALUES（‘T0001’,’S001’,20）
不成功，因为书号为引用“图书表”的外码。
4.编写实现如下功能的触发器：
（1）限制考试成绩必须在0~100分之间。
createtriggertri_a
onscafterinsert,update
as
ifexists（select*fromsc
wheregradenotbetween0and100）
rollback
（2）限制不能将不及格的考试成绩改为及格。
createtriggertri_b
onscafterupdate
as
ifexists（select*
frominsertedjoindeletedon=and=
where>=60and<=60）
rollback
（3）限制不能删除选修课程考试成绩不及格学生的该门课程的选课及考试记录。
createtriggertri_c
onscafterdelete
as
ifexists（select*fromdeletedwheregrade<60）
rollback
（4）限制每个学生总的选课门数不能超过20门。
createtriggertri_d
onscafterinsert,update
as
ifexists（selectcount（cno）
fromsc
groupbysno
havingcount（cno）>20）
rollback
（5）限制每个学期开设的课程总学分必须在20~25之间。
createtriggertri_e
oncourseafterinsert,update,delete
as
ifexists（selectsum（credit）
fromcourse
groupbysemester
havingsum（credit）notbetween20and25）
rollback
五．实验小结
通过这次上机实验，把上课中所讲的数据完整性约束的相关知识得到了复习，通过上机更直观的理解了完整性约束的功能，通过添加删除约束的操作更好的掌握了有关知识，收获很大。
实验四 安全管理
一、实验目的
1.掌握安全管理的过程。
2.掌握在SQLServer环境中实现安全管理的操作。
二、实验要求
1.能够在SQLServer环境中建立SQLServer身份验证的登录账户。
2.能够建立数据库用户。
3.编写给用户授权的SQL语句。
4.验证安全管理的效果。
三、实验内容及步骤
1．建立SQLServer认证的登录帐户：
u1、u2、u3，并将u1、u2、u3映射为实验一建立的Students数据库的用户。
2．用u1建立一个新的连接，并在students数据库中执行下述语句，是否能成功为什么SELECT*FROMCourse
不成功，因为u1没有被授予对象'Course'的查询权限。
3．用系统管理员登录建立一个连接，并授予u1具有对course和SC的查询权限，授予u2具有对course的插入权限。
GRANTSELECTONCOURSETOu1
GRANTSELECTONSCTOu1
GRANTINSERTONCOURSETOu2
4．用u1建立一个新的连接，并在students数据库中执行下述语句，能否成功为什么
INSERTINTOcourseVALUES（'C011'，'数据仓库技术'，2，7）
能成功，已经授予u2对course表的插入权限
再执行下述语句，能
升级会员 