数据库实验报告.docx
《数据库实验报告.docx》由会员分享，可在线阅读，更多相关《数据库实验报告.docx（44页珍藏版）》请在冰豆网上搜索。
数据库实验报告
数据库原理
实验报告
学号：
4110115
姓名：
王善斌
提交日期：
2013-06-20
成绩：
东北大学秦皇岛分校
【实验内容】
2在企业管理器中创建一个数据库，要求如下：
（1）数据库名称Test1。
（2）主要数据文件：
逻辑文件名为Test1_data1，物理文件名为Test1_data1.mdf，初始容量为1MB，最大容量为10MB，增幅为1MB。
（3）次要数据文件：
逻辑文件名为Test1_data2，物理文件名为Test1_data2.ndf，初始容量为1MB，最大容量为10MB，增幅为1MB。
（4）事务日志文件：
逻辑文件名为Test1_log1，物理文件名为Test1_log1.ldf，初始容量为1MB，最大容量为5MB，增幅为512KB。
3在查询分析器中创建一个数据库，要求如下：
（1）数据库名称Test2。
（2）主要数据文件：
逻辑文件名为Test2_data1，物理文件名为Test2_data1.mdf，初始容量为1MB，最大容量为10MB，增幅为1MB。
（3）次要数据文件：
逻辑文件名为Test2_data2，物理文件名为Test2_data2.ndf，初始容量为1MB，最大容量为10MB，增幅为1MB。
（4）事务日志文件：
逻辑文件名为Test2_log1，物理文件名为Test2_log1.ldf，初始容量为1MB，最大容量为5MB，增幅为1MB。
createdatabasetest2
onprimary（name=test2_data1,
filename='d:
\sqlex\test2_data1.mdf',
size=1,
maxsize=10,
filegrowth=1）,
（name=test2_data2,
filename='d:
\sqlex\test2_data2.ndf',
size=1,
maxsize=10,
filegrowth=1）
logon（name=test2_log,
filename='d:
\sqlex\stu_log1.ndf',
size=1,
maxsize=5,
filegrowth=2）
4.在查询分析器中按照下列要求修改第3题中创建的数据库test2
（1）主要数据文件的容量为2MB，最大容量为20MB，增幅为2MB。
（2）次要数据文件的容量为2MB，最大容量为20MB，增幅为2MB。
事务日志文件的容量为1MB，最大容量为10MB，增幅为2MB
alterdatabasetest3
modifyname=test2
alterdatabasetest2
modifyfile（name=test2_data2,
filename='d:
\sqlex\test2_data2.ndf',
size=2,
maxsize=20,
filegrowth=2）
alterdatabasetest2
modifyfile（name=test2_log,
filename='d:
\sqlex\test2_log.ldf',
size=1,
maxsize=10,
filegrowth=1）
alterdatabasetest2
modifyfile（name=test2_data1,
size=2,
maxsize=20,
filegrowth=2）
4.数据库更名：
把test1数据库更名为new_test1
alterdatabasetest1
modifyname=new_test1
5.在企业管理器中删除new_test1数据库，在查询分析器中删除test2数据库。
在企业管理器中删除new_test1前
在企业管理器中删除new_test1后
在查询分析器中删除test3前
在查询分析器中删除test3后
dropdatabasetest3
6.为sql示例数据库northwind创建一个备份：
northwindBK，并使用该备份文件恢复northwind数据库。
execsp_addumpdevice'disk','northwindBK','d:
\数据库备份\MyNwind-1.dat'
BACKUPDATABASEnorthwindTOnorthwindBK
实验二创建并管理表
1.创建数据库，包含如下表，创建这些表并按要求定义约束
usestudentinfo
go
createtablestudent（
student_idchar（10）primarykey,
student_namechar（10）notnull,
sexchar
（1）notnull,
ageintnull,
departmentchar（15）default'电子信息系'）
usestudentinfo
go
createtablecourse（
course_idchar（6）primarykey,
course_namechar（20）notnull,
precouldchar（6）null,
creditsnumeric（3,1）notnull）
usestudentinfo
go
createtablescore（
student_idchar（10）,
course_idchar（6）notnull,
gradenumeric（3,1）null,
primarykey（student_id,course_id）,
foreignkey（student_id）referencesstudent（student_id）,
foreignkey（course_id）referencescourse（course_id））
以下为各个表的数据
Students表数据
Student_id
Student_name
sex
age
department
20010101
Jone
M
19
Computer
20010102
Sue
F
20
Computer
20010103
Smith
M
19
Math
20030101
Allen
M
18
Automation
20030102
deepa
F
21
Art
Course表数据
Course_id
Course_name
PreCouId
Credits
C1
English
4
C2
Math
C5
2
C3
database
C2
2
Score表数据
Student_id
Course_id
Grade
20010101
C1
90
20010103
C1
88
20010102
C2
94
20010102
C2
62
2.增加，修改，删除字段，要求
（1）为表student增加一个memo（备注）字段，类型为varchar（200）
代码：
useyzw
go
altertablestudentaddmemovarchar（200）
（2）.将MEMO字段的数据类型更改为varchar（300）
代码：
useyzw
go
altertablestudentaltercolumnmemovarchar（300）
（3）．删除memo字段
useyzw
go
altertablestudentdropcolumnmemo
3.向表中插入数据验证约束
useyzw
go
altertablescore
addconstraintgradecheck（grade>0andgrade<100）
//建立了一个约束条件
可以知道约束条件起作用了。
三数据更新
实验内容：
在已经建立的studentinfo数据库和3个students、courses、score基础上完成下列操作。
1.向students表添加一个学生记录，学号为20010112，性别为男，姓名为stefen，年龄25岁，所在系为艺术系art。
useyzw
go
insertstudent
values（'20010112','stefen','M','25','Art'）
2.向score表添加一个选课记录，学生学好为20010112，所选课程号为C2
useyzw
go
insertscore
values（'20010112','C2',null）
3.建立临时表tempstudent,结构与students结构相同，其记录均从student表获取
useyzw
go
createtabletempstudent（
student_idchar（10）primarykey,
student_namechar（10）notnull,
sexchar
（1）notnull,
ageintnull,
departmentchar（15））
insertintotempstudent
selectstudent_id,student_name,sex,age,department
fromstudent
4，将所有学生的成绩加5分
updatescore
setgrade=grade+5
5.将姓名为sue的学生所在系改为电子信息系
updatestudent
setdepartment='电子信息系'
wherestudent_name='sue'
6.将选课的database的学生成绩加10分
updatescore
setscore.grade=score.grade+10
wherescore.course_id='C3'
7.删除所有成绩为空的选修记录
Deletescore
Wheregrade=’null’
8.删除学生姓名为deepa的学生记录
由于下表有调用因而删除不掉
四数据查询
1、查询全体学生的学号、姓名、所在系，并为结果集的各列设置中文名称。
selectstudent_id'学号',student_name'姓名',sex'性别',age'年龄',department'学院'
fromstudent
2.查询全体学生的选课情况，并为所有成绩加5分
select*
fromscore
updatescore
setgrade=grade+5
3.显示所有选课学生的学号，去掉重复行
selectdistinctstudent.student_id
fromstudent
4.查询选课成绩大于80分的学生
selectscore.student_id
fromscore
wheregrade>80
5.查询年龄在20到30之间的学生学号，姓名，所在系
selectstudent.student_id,student.student_name,student.department,student.age
fromstudent
whereagebetween20and30
6.查询数学系、电子信息系、艺术系的学生学号，姓名。
selectstudent.student_id,student.student_name
fromstudent
wheredepartmentin（'math','电子信息系','Art'）
7.查询姓名第二个字符为u并且只有3个字符的学生学号，姓名
selectstudent.student_id,student.student_name
fromstudent
wherestudent_namelike'_u_'
8.查询所有以S开头的学生。
selectstudent.student_id,student.student_name
fromstudent
wherestudent_namelike'S%'
9.查询姓名不以S、D、或J开头的学生
selectstudent.student_id,student.student_name
fromstudent
wherestudent_namenotlike'S%'andstudent_namenotlike'D%'andstudent_namenotlike'J%'
10查询没有考试成绩的学生和相应课程号（成绩值为空）
selectstudent_id,course_id
fromscore
wheregradeisnull
11.求年龄大于19岁的学生的总人数
selectcount（*）'总数'
fromstudent
whereage>19
12别求选修了c1语言课程的学生平均成绩、最高分、最低分学生。
selectAVG（grade）'平均成绩',max（grade）'最高分',min（grade）'最低分'
fromscore
wherecourse_id='c1'
13.求学号为20010101的学生总成绩
selectsum（grade）'总成绩'
fromscore
wherestudent_id='20010101'
14求每个选课学生的学号，姓名，总成绩
selectstudent.student_id,student_name,score.grade
fromstudent,score
wherestudent.student_id=score.student_id
15.查询所有姓李且全名为3个汉字的学生姓名，学号，性别
selectstudent_id,student_name,sex
fromstudent
wherestudent_name='李__'
16.求课程号及相应课程的所有的选课人数
selectcourse_id,count（*）'Sum'
fromscore,student
wherestudent.student_id=score.student_id
groupbycourse_id
17查询选修了3门以上课程的学生姓名学号
selectstudent_id,student_name
fromstudent
whereEXISTS（
select*
fromscore
wherescore.student_id=student.student_id
groupbystudent_idhavingcount（*）>=3
）
18.查询每个学生基本信息及选课情况
select*
fromstudent,course
2.查询每个学生学号姓名及选修的课程名、成绩
selectstudent.student_id,student.student_name,course_id,grade
fromstudent,score
wherestudent.student_id=score.student_id
3.求电子信息系选修课程超过2门课的学生学号姓名、平均成绩并按平均成绩降序排列
4.查询与sue在同一个系学习的所有学生的学号姓名
selectstudent.student_id,student.student_name
fromstudent
wheredepartment=（selectdepartment
fromstudent
wherestudent_name='sue'）
5.查询所有学生的选课情况，要求包括所有选修了课程的学生和没有选课的学生，显示他们的姓名学号课程号和成绩（如果有）
selectstudent.student_id,student.student_name,score.course_id,score.grade
fromstudent,score
wherestudent.student_id=score.student_id
五索引和视图
1、分别使用企业管理器和查询分析器为northwind数据库中products表建立一个聚集索引，索引字段为产品类型和产品编号。
2在已经建立的studentInfo数据库的3个表基础上，完成下列操作：
（1）建立数学系的学生视图；
createviewmathsthdentview
as
select*
fromstudent
wherestudent.department='math'
（2）建立计算机系选修了课程名为database的学生的视图，视图名为compStudentview，该视图的列名为学号、姓名、成绩
createviewcompstudentview
as
selectstudent.student_id'学号',student_name'姓名',grade'成绩'
fromstudent,score
wherestudent.department='computer'andstudent.student_id=score.student_idandscore.course_id='C3'
（3）创建一个名为studentSumview的视图，包含所有学生学号和总成绩
createviewstudentSumview
as
selectstudent.student_id,sum（grade）'sum'
fromstudent,score
wherestudent.student_id=score.student_id
groupbystudent.student_id
（4）建立一个计算机系学生选修了课程名为database并且成绩大于80分的学生视图，视图名为CompsutdentView1，视图的列为学号姓名成绩。
createviewCompstudentview1
as
selectstudent.student_id,student_name,grade
fromstudent,score
wherestudent.department='computer'andscore.course_id='C3'andgrade>80andstudent.student_id=score.student_id
（5）使用sql语句删除compsutdentview1视图。
dropviewCompstudentview1
数据库查询综合实验
1.使用查询分析器建立上述数据库和表；
图书表
读者表
借阅表
2、基于以上数据库使用sql语句完成下列对表操作：
（1）给图书表增加一列“ISBN”，数据类型为CHAR（10）；
altertable图书表
addISBNchar（10）
（2）为刚添加的ISBN列增加默认值约束，约束名为ISBNDEF，默认值为‘7111085949’；
altertable图书表
addconstraintISBNDEFdefault7111085949forISBN
（3）为读者表中“办公电话”一列增加一个CHECK约束，要求电话号码的前五位是“88320”
altertable读者表
addconstraintc1check（办公电话LIKE'88320___'）
（4）删除图书表中ISBN列增加的默认值约束
altertable图书表
dropconstraintISBNDEF
（5）删除读者表中“办公电话”列的CHECK约束
altertable读者表
dropconstraintc1
6）删除图书表中的新增ISBN列。
altertable图书表
dropcolumnISBN
3、基于以上3个表，使用sql语句完成以下数据更新操作
1）向读者表加入一个新读者，该读者的信息为，读者号：
001980，姓名：
余暇，性别：
女，办公电话：
88320564，部门：
艺术系
insertinto读者表
values（'001980','余暇','女','88320564','艺术系'）
2）向借阅表插入一个借阅记录，表示读者“王平”借阅了一本书，图书号：
TP316/ZW6，借出日期为当天日期，归还日期为空值；
insertinto借阅表
values（'001973','TP316/ZW6','2013-6-17',null）
（3）读者“王平”应在借出日期的10天之后归还该书；
update借阅表
set归还日期='2013-6-27'
where读者号='001973'and图书号='TP316/ZW6'
（4）当读者“王平”按期归还书籍后，从借阅表中删除上述借阅记录
delete
from借阅表
where读者号='001973'and图书号='TP316/ZW6'
4、针对以上3个表，完成下列单表查询
（1）查询全体图书的信息；
select*
from图书表
（2）查询全体图书信息，其中单价进行打8折操作，显示列名为“折扣价”；
select图书号,图书名,作者,出版社,0.8*单价'折扣价'
from图书表
（3）显示目前所有借阅读者信息，去掉重复行；
selectdistinct*
from借阅表
（4）显示所有单价在20-30元之间的图书信息；
select*
from图书表
where单价between20and30
（5）查询所有单价不在20-30元之间的图书信息；
select*
from图书表
where单价notbetween20and30
（6）查询机械工业出版社，科学出版社、人民邮电出版社的图书信息；
select*
from图书表
where出版社in（'机械工业出版社','科学出版社','人民邮电出版社的图书信息'）
（7）查询非人民邮电出版社的信息；
select*
from图书表
where出版社notin（'人民邮电出版社的图书信息'）
（8）查找姓名第二个字符是“建”并且只有2个字符姓名的读者信息；
select*
from读者表
where姓名like'_建'
（9）查找姓名以“王”开头的所有读者的读者号及姓名；
select读者号,姓名
from读者表
where姓名like'王%'
（10）查找以“王”、“张”、“李”开头的所有读者的读者号姓名；
升级会员 