数据库实验报告110.docx
《数据库实验报告110.docx》由会员分享，可在线阅读，更多相关《数据库实验报告110.docx（19页珍藏版）》请在冰豆网上搜索。
数据库实验报告110
实验一数据库管理（2012.9.20）
一、实验目的
1．熟练掌握在图形界面下创建数据库的方法
2．熟练掌握用SQL语句创建数据库的方法
3．熟练掌握数据库属性的设置
4．熟练掌握数据库修改和删除的方法
二、实验内容及步骤
1．创建用户数据库
（1）在SQLServermanagementStudio管理工具下创建数据库
（2）使用T-SQL语句创建数据库
①创建一个名为jwglxt1的数据库
Createdatabasejwglxt1
②创建指定数据文件和事务日志文件的数据库
CREATEDATABASEjwglxt2
ON
（NAME=jwglxt2_dat,
FILENAME='e:
\jc\jwglxt2_data.mdf',
SIZE=10,
MAXSIZE=50,
FILEGROWTH=5）
LOGON
（NAME='jwglxt2_log',
FILENAME='e:
\jc\jwglxt2_log.ldf',
SIZE=5MB,
MAXSIZE=25MB,
FILEGROWTH=5MB）
）
③创建一个指定多个数据文件和事务日志文件创建数据库。
使用三个100MB的数据文件和两个100MB的事务日志文件创建了名为JWGLXT3的数据库
CREATEDATABASEJWGLXT3
ON
PRIMARY（NAME=JWGLXT3_DAT1,
FILENAME='e:
\jc\JWGLXTdat1.mdf',
SIZE=100MB,
MAXSIZE=200,
FILEGROWTH=20）,
（NAME=JWGLXT3_DAT2,
FILENAME='e:
\jc\JWGLXTdat2.ndf',
SIZE=100MB,
MAXSIZE=200,
FILEGROWTH=20）,
（NAME=JWGLXT3_DAT3,
FILENAME='e:
\jc\JWGLXTdat3.ndf',
SIZE=100MB,
MAXSIZE=200,
FILEGROWTH=20）
LOGON
（NAME=JWGLXT3_log1,
FILENAME='e:
\jc\JWGLXT3log1.ldf',
SIZE=100MB,
MAXSIZE=200,
FILEGROWTH=20）,
（NAME=JWGLXT3_log2,
FILENAME='e:
\jc\JWGLXT3log2.ldf',
SIZE=100MB,
MAXSIZE=200,
FILEGROWTH=20）
2．修改数据库
①为jwglxt2数据库添加一个8MB大小的新数据文件
ALTERDATABASEJWGLXT2
ADDFILE
（
NAME=JWGLXTdata2,
FILENAME='e:
\jc\JWGLXTdata2.ndf',
SIZE=8MB,
MAXSIZE=100MB,
FILEGROWTH=5MB
）
②向jwglxt2数据库中添加两个5MB大小的日志文件
ALTERDATABASEJWGLXT
ADDLOGFILE
（NAME=JWGLXTlog1,
FILENAME='e:
\jc\JWGLXTlog1.ldf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB）,
（NAME=JWGLXTlog2,
FILENAME='e:
\jc\JWGLXTlog2.ldf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB）
3．重命名数据库
（1）使用管理平台把“jwglxt1”数据库重命名为“New_jwglxt”
（2）使用T-SQL语句把“jwglxt1”数据库重命名为“New_jwglxt”
Sp_renamedb‘jwglxt1’,’new_jwglxt’
4．删除数据库
（1）使用管理平台把“jwglxt1”数据库删除
（2）使用T-SQL语句把“jwglxt1”数据库删除
实验二数据表的创建（2012..9.26）
一、实验目的
1．熟练掌握T-SQL语句创建数据表的方法
2．熟练掌握表的基本特性
3．掌握SQLServer的基本数据类型
4．熟练掌握使用管理平台创建数据表的方法
二、实验内容及步骤
1．创建用户数据表
（1）在SQLServermanagementStudio管理工具下创建数据表
（2）使用T-SQL语句创建数据表
①创建一个名为jwglxt的数据库
②在jwglxt数据库中创建如下数据表
Student表的结构与数据类型
字段名
数据类型
约束
说明
Sno
Char（8）
Primarykey
学号
Clno
Char（6）
Notnull
班级编号
Sname
Char（8）
Notnull
姓名
Ssex
Char
（2）
Notnull
性别
Sbir
Datetime
出生日期
表记录参照P84图2.46
createtablestudent
（snochar（8）primarykey,
clnochar（6）notnull,
snamechar（8）notnull,
ssexchar
（2）check（ssexin（'男','女'））default（'男'）notnull,
sbirdatetime
）
Sc表的结构与数据类型
字段名
数据类型
约束
说明
Sno
Char（8）
foreignkey
学号
Cno
Char（5）
foreignkey
课程编号
score
Numeric（5,1）
成绩
主键snocno
表记录参照P81图2.40
createtablesc
（snochar（8）,
cnochar（5）,
primarykey（sno,cno）,
foreignkey（sno）referencesstudent（sno）,
scorenumeric（5,1）
）
course表的结构与数据类型
字段名
数据类型
约束
说明
Cno
Char（8）
Primarykey
课程编号
Cname
Char（20）
Notnull
课程名称
credits
Numeric（5,1）
学分
表记录参照P99图2.66
createtablecourse
（cnochar（8）primarykey,
cnamechar（20）uniquenotnull,
creditsnumeric（5,1）
）
（请自行添加创建代码）
2．为数据表添加各类约束
①为student表的性别列指定默认值“男”，并设置检查约束为性别只能是“男”或“女”
②为course表的Cname字段指定唯一约束
③为SC表的sno字段建立外键约束（student表的sno字段）
实验三表的维护（2012.9.26）
一、实验目的
1．熟练掌握使用SQL语句对表的结构进行修改
2．熟练掌握使用管理平台删除表、重命名表的操作方法
3．熟练使用SQL语句删除表、重命名表
二、实验内容及步骤
使用实验二创建的student、sc、course三个表
1．为数据表student添加一个允许为空的列，列名为Sdept，类型为varchar（20）和一个sage列，其为计算列（通过sbir计算学生年龄）
altertablestudent
addsdeptvarchar（20）null
altertablestudent
addsageasyear（getdate（））-year（sbir）
2．将添加的sage列删除
altertablestudent
dropcolumnsage
3．为sdept列添加默认值“计算机系”
altertablestudent
addconstraintdfdefault（'计算机系'）forsdept
4．删除sdept的默认值约束
altertablestudent
dropconstraintdf
5．修改student表中sname列的长度为20
altertablestudent
altercolumnsnamechar（20）
6．将student表重命名为stu
sp_rename'student','stu'
7．删除表stu
altertablesc
dropFK__sc__sno__182C9B23
droptablestu
（分别使用管理工具和SQL语句操作，实验报告只写SQL语句）
实验四简单查询（2012.10.10）
一、实验目的
1．熟练掌握SQL语句使用方法
2．熟练使用聚合函数
3．熟练使用分组、排序
二、实验内容及步骤
使用实验二创建的student、sc、course三个表
1．查询全体学生的学号、姓名、性别和年龄
selectsno,sname,ssex,sage
fromstudent
2．查询选修了课程的学生的学号（不显示重复行）
selectdistinctsno
fromsc
3．查询全体学生的详细信息
select*
fromstudent
4．查询全体学生的学号、姓名和出生年份（出生年份使用别名）
selectsno,sname,year（sbir）as出生年份
fromstudent
5．查询年龄在21-23岁之间的学生，显示学号、姓名及年龄
selectsno,sname,year（getdate（））-year（sbir）as年龄
fromstudent
whereyear（getdate（））-year（sbir）between21and23
6．查询姓“李”的学生记录
select*fromstu
wheresnamelike'李%'
7．查询选修了课程01001、02002，成绩在80-90之间的学生
selectstu.*,o,sc.scorefromstudentinnerjoinsconstudent.sno=sc.sno
where（cno='01001'orcno='02002'）andscorebetween80and90
8．查询student表的5条记录
selecttop5*fromstudent
9．查询考试成绩排在前10%的学生的学号、姓名、成绩
selecttop10percentstu.sno,sname,score
fromstudent,sc
wherestudent.sno=sc.sno
10．查询缺少学习成绩的学生的学号和课程号（空值查询）
selectsno,cnofromsc
wherescoreisnull
11．求学生的总人数
selectcount（sno）as全班人数
fromstudent
12．求选修了课程的学生的总人数
selectcount（distinctsno）as选修课程人数
fromsc
13．求200801班的学生的平均年龄
selectclno,avg（score）as平均成绩
fromstudentinnerjoinsconstudent.sno=sc.sno
groupbyclno
havingclno='200801'
14．求选修01001课程的最高、最低、平均成绩
selectcno,max（score）as最高成绩,min（score）as最低成绩,avg（score）as平均成绩
fromsc
groupbycno
havingcno='01001'
15．求各门课程的平均成绩与总成绩（分组查询）
selectcno,avg（score）as平均成绩,sum（score）总成绩
fromsc
groupbycno
16．求学生人数不足4人的班级及相应的学生数（分组查询）
selectclno,count（sno）as班级人数
fromstudent
groupbyclnoHavingcount（sno）<4
实验五多表查询和子查询（2012.10.17）
一、实验目的
1．熟悉基本的连接查询，掌握内连接与外连接及自连接
2．掌握相关子查询的方法
3．学会嵌套子查询的方法
4．学会集合查询的方法
二、实验内容及步骤
使用实验二创建的student、sc、course三个表
1．查询选修了课程的学生姓名、学号、课程号、选课成绩
selectstudent.sno,sname,cno,score
fromstudentinnerjoinsconstudent.sno=sc.sno
2．查询选修了课程的姓“李”的学生的姓名、学号、课程号、选课成绩、并且选课成绩大于80分。
selectstudent.sno,sname,cno,score
fromstudentinnerjoinsconstudent.sno=sc.sno
wheresnamelike'李%'andscore>80
3．分别将student表与sc表进行左连接、右连接、全连接、交叉连接，观察连接结果
内连接
selectstudent.*,sc.*
fromstudentinnerjoinsconstudent.sno=sc.sno
左连接
selectstudent.*,sc.*
fromstudentleftouterjoinsconstudent.sno=sc.sno
右连接
selectstudent.*,sc.*
fromstudentrightouterjoinsconstudent.sno=sc.sno
全连接
selectstudent.*,sc.*
fromstudentfullouterjoinsconstudent.sno=sc.sno
交叉连接
selectstudent.*,sc.*
fromstudentcrossjoinsc
4．查询比莫芳芳年龄大的学生（使用自连接）
selecta.*
fromstudentainnerjoinstudentbona.sno=b.sno
whereb.sage>（selectsagefromstudentwherestudent.sname='莫芳芳'）
5．查询学生的姓名、课程名及考试成绩
selectsname,cname,score
fromstudentinnerjoinsconstudent.sno=sc.sno
joincourseono=o
6．查询选修了课程的学生信息
selectstudent.*
fromstudent
wheresnoin（selectdistinct（sno）fromsc）
7．查询与覃天同班的学生信息
selectstudent.*
fromstudent
whereclno=（selectclnofromstudentwheresname='覃天'）
8．查询比’200801’班中某一学生年龄大的学生的姓名和学号
selectsname,sno
fromstudent
wheresage>any（selectsagefromstudentwhereclno='200801'）
9．选修了02002课程的学生的姓名和学号（子查询exists）
selectsno,sname
fromstudent
whereexists（selectsnofromscwherestudent.sno=sc.snoandcno='02002'）
10．查询选修了01001或2002的学生学号（集合查询）
selectsnofromsc
wherecno='01001'
union
selectsnofromsc
wherecno='2002'
11．查询选修了全部课程的学生的姓名（相关子查询）
selectsname
fromstudent
wherenotexists（select*
fromcourse
wherenotexists（select*
fromsc
wherestudent.sno=sc.snoando=o）
）
12．查询选修了课程，但没有选修02003课程的学生的姓名、学号（相关子查询或集合查询）
selectsno,sname
fromstudent
wheresnoin（selectdistinct（sno）fromsc）
intersect
selectsno,sname
fromstudent
wheresnonotin（selectsnofromscwherecno='02003'）
13．查询成绩比所选课程平均成绩高的学生的学号、课程号、成绩（相关子查询）
selectx.*fromscx
wherescore>（selectavg（score）fromscywherey.sno=x.sno）
14．查询所有未选修课程的学生学号、姓名
selectsno,sname
fromstudent
wheresnonotin（selectdistinct（sno）fromsc）
15．查询所有选修了课程的学生的学号、姓名
selectsno,sname
fromstudent
wheresnoin（selectdistinct（sno）fromsc）
实验六数据更新和视图
一、实验目的
1．熟练掌握插入、更新、删除数据表记录的方法
2．熟练掌握视图的创建方法
3．掌握视图的维护方法
二、实验内容及步骤
使用实验二创建的student、sc、course三个表
1．向student数据表插入一条记录
20100101201001张容女1990-6-12
insertstudent
values（'20100101','201001','张容','女','1990-6-12'）
2．将200801班的学生记录追加到一个空表student1（表结构与student相同）中
insertintostudent1
select*fromstudent
whereclno='200801'
3．在sc表中，将所有选修了02002课程的学生的选课成绩增加5分
updatesc
setscore=score+5
wherecno='02002'
4．删除数据表sc中的所有记录
deletesc1或者truncatetablesc1
5．删除数据表student中所有女生记录
deletestudent1wheressex='女'
6．利用管理平台创建学生成绩表视图stuv1，包括sno，sname，cname，score
7．用SQL语句创建学生成绩表视图stuv2，包括sno，sname，cname，score
createviewstuv2
as
selectstudent.sno,sname,cname,score
fromstudentinnerjoinsconstudent.sno=sc.sno
joincourseono=o
8．为数据表student创建一个视图stuv，包括sno，clno，sname，ssex，sbir。
createviewstuv
as
selectsno,clno,sname,ssex,sbirfromstudent
①通过视图向数据表添加一条记录20100112201001张宏男1992－10－12
insertintostuv
values（'20100112','201001','张宏','男','1992-10-12'）
②通过视图将20100112的学生记录的性别改为女
updatestuv
setssex='女'
wheresno='20100112'
③通过视图删除20100112的学生记录
deletefromstuv
wheresno='20100112'
9．删除视图stuv
dropviewstuv
实验七SQL程序设计（2012.10.31）
一、实验目的
1．熟练掌握SQL语言中局部变量的定义和赋值方法
2．熟练掌握流程控制语句在程序中的使用方法
二、实验内容及步骤
使用实验二创建的student、sc、course三个表
1．声明两个整型局部变量：
@a1和@a2，并给@a1赋初值10，给@a2赋初值@a1*5，输出@a2的结果
declare@a1int,@a2int
set@a1=10
set@a2=@a1*5
print@a2
2．用exists确定student表中是否存在“赵丽丽”，如果存在则显示记录信息，不存在则返回提示信息。
ifexists（select*fromstudentwheresname='赵丽丽'）
select*fromstudentwheresname='赵丽丽'
else
print'没有赵丽丽的记录'
3．使用while语句实现计算5000减1、减2、减3……一直减到50的结果。
declare@sint,@iint
set@s=5000
set@i=1
while@i<=50
begin
set@s=@s-@i
set@i=@i+1
end
print'5000-1-2-...-50='+cast（@saschar（20））
4．用case语句实现功能：
声明@x、@y为字符型，长度为6，为@x赋初值’abc’，分情况判断：
当@x=’a’时，@y=’abc’+’1’
当@x=’b’时，@y=’abc’+’2’
当@x=’c’时，@y=’abc’+’3’
否则，@y=’no’
declare@xchar（6）,@ychar（6）
set@x='abc'
升级会员 