数据库实验.docx
- 文档编号:7975210
- 上传时间:2023-01-27
- 格式:DOCX
- 页数:26
- 大小:1.27MB
数据库实验.docx
《数据库实验.docx》由会员分享,可在线阅读,更多相关《数据库实验.docx(26页珍藏版)》请在冰豆网上搜索。
数据库实验
实验一通过SQLServerManagementStudio创建及管理数据库
一、实验目的
(1)熟悉SQLServerManagementStudio
(2)掌握通过SQLServerManagementStudio管理数据库的方法。
(3)掌握数据库及其物理文件的结构关系。
(4)掌握通过SQLServerManagementStudio管理数据表的方法
二、实验内容
1、通过SQLServerManagementStudio创建数据库
创建一个名称为学生管理信息Student_info数据库,创建数据库Student_info的具体参数如表1。
表B.1数据库参数
参数名称
参考参数
数据库名称
数据库逻辑文件名称
数据物理文件名称
数据文件初始大小
数据文件大小最大值
数据文件增长增量
日志逻辑文件名称
日志物理文件名称
日志文件初始大小
日志文件大小最大值
日志文件增长增量
Student_info
Student_info_data
Student_info_data.mdf
20MB
300MB
5MB
Student_info_log
Student_info_log.ldf
5MB
50MB
1MB
2、查看、验证创建的数据库
方法1:
执行sp_helpdb系统存储过程查看Student_info数据库的信息。
方法2:
在企业管理器中查看
3、修改数据库的属性
通过SQLServerManagementStudio查看数据库,将数据库Student_info数据文件的初始大小改为30MB,最大值改为500MB,数据增长改为5%,日志文件的初始大小改为20MB,最大值改为60MB,数据增长改为6%。
4、数据库的分离及附加
1)将Student_info数据库从数据库服务器分离
2)将Student_info数据库再次附加到服务器中
5、通过SQLServerManagementStudio在Student_info数据库中创建表
Student_info数据库包含3张表:
Student表、Course表及SC表,分别代表学生信息、课程信息及学生选课信息。
3张表的结构及其约束见表2、表3、表4.
表2Student表结构和约束
列名称
类型
宽度
允许空值
缺省值
约束
主键
说明
Sno
char
8
否
是
学号
Sname
varchar
8
否
学生姓名
Sex
char
2
否
男
性别
Birth
smalldatetime
否
出生年月
Classno
char
3
否
班级号
Entrance_date
smalldatetime
否
入学时间
Home_addr
varchar
40
是
家庭地址
表3Course表结构和约束
列名称
类型
宽度
允许空值
缺省值
约束
主键
说明
Cno
char
3
否
是
课程号
Cname
varchar
20
否
课程名称
Total_perior
smallint
是
大于0
总学时
Credit
tinyint
是
大于0,小于等于6
学分
表4SC表结构和约束
列名称
类型
宽度
允许空值
缺省值
约束
主键
外键
说明
Sno
char
8
否
是
是
学号
Cno
char
3
否
是
课程号
Grade
tinyint
是
大于等于0,小于等于100
否
成绩
6、通过SQLServerManagementStudio管理表结构
(1)添加和删除列
a.给Student表增加身高(以米单位)Stature列,类型为numeric(4,2),允许为空值,且身高值需小于3.0米。
b.给Student表增加所在系Sdept列,字符型,长度2,不允许为空值。
c.给Student表增加邮政篇码Postcode列,字符型,长度为6,可以为空,若不为空时,则要求其值只能出现数字,不能是其它字符。
d.删除Student表中身高Stature列。
(2)添加和删除约束
a.在Student表添加约束:
入学时间必须在出生年月之后。
b.给SC表的成绩Grade列增加默认值约束,默认值为0.
c.删除Grade列的默认值约束
7、通过SQLServerManagementStudio对表添加、修改、删除数据
(1)插入数据,Student表、Course表、SC表的记录见表5、表6、表7。
表5Student表
Sno
Sname
Sex
Birth
Classno
Entrance_date
Home_addr
Sdept
Postcode
20110001
张虹
男
1992/09/011
051
2011/09/01
南京
计算机系
200413
20110002
林红
女
1991/11/12
051
2011/09/01
北京
计算机系
100010
20110103
赵青
男
1993/05/11
061
2011/09/01
上海
软件工程
200013
表6Course表
Cno
Cname
Total_perior
Credit
001
高数
96
6
002
C语言程序设计
80
5
003
JAVA语言程序设计
48
3
004
Visual_Basic
48
4
表7SC表
Sno
Cno
Grade
20110001
001
89
20110001
002
78
20110001
003
89
20110002
002
60
20110103
001
80
其它数据可自行添加。
要求Student表和SC表中数据包括了每位同学自己的学号。
(2)修改数据
a.将Student表中的学号为’20050103’的同学的出生年月改为1993年10月1日。
b.将Course表中的课程号为’002’的学分改为4,总学时改为64。
(3)删除数据(请注意约束的限制)
a.删除SC表中20110103同学的选课记录
b.删除Course表中课程号为002的记录。
如果不能成功删除该记录,请分析原因。
无法删除:
原因是Course表的Cno是Student表的Cno的外键,必须先解除关系方可删除。
实验二通过SQL语句创建与管理数据表
一、实验目的
(1)掌握查询分析器的使用。
(2)掌握通过SQL语句创建表的方法。
(3)掌握通过SQL语句修改表结构的方法。
(4)掌握通过SQL语句添加、修改、删除表数据的方法。
二、实验内容
1、通过SQL语句删除表
用SQL语句在数据库Student_info中删除实验一创建的Student表、Course表、SC表。
2、通过SQL语句创建表
用SQL语句在数据库Student_info中创建实验一中的Student表、Course表、SC表,结构如实验一中表2、表3、表4所示
3、通过SQL语句管理表结构
(1)添加和删除列
a.给Student表增加身高(以米单位)Stature列,类型为numeric(4,2),允许为空值,且身高值需小于3.0米。
b.给Student表增加所在系Sdept列,字符型,长度2,不允许为空值。
c.给Student表增加邮政篇码Postcode列,字符型,长度为6,可以为空,若不为空时,则要求其值只能出现数字,不能是其它字符。
d.删除Student表中身高Stature列。
(2)添加和删除约束
a.在Student表添加约束:
入学时间必须在出生年月之后。
b.给SC表的成绩Grade列增加默认值约束,默认值为0.
c.删除Grade列的默认值约束
4、通过SQL语句添加、修改、删除表中数据
(1)插入数据
a.Student表、Course表、SC表的记录见实验一的表5、表6、表7,其它数据可自行添加。
要求Student表和SC表中数据包括了每位同学自己的学号。
b.执行如下语句:
insertintoStudent(Sno,Sname,Sex)values(‘20101101’,’赵青’,’男’),该语句能成功执行吗?
为什么?
c.执行如下语句:
insertintoscvalues(‘20110103’,’005’,80),该语句能成功执行吗?
为什么?
(2)修改数据
a.使用T-SQL语句,将Course表中的课程号为’002’的学分改为4,总学时改为64。
b.使用T-SQL语句,将SC表中的选修了‘002’课程的同学的成绩*80%。
(3)删除数据
a.使用T-SQL语句,删除选修了“C语言程序设计”的学生的选课记录。
b.使用T-SQL语句,删除所有的学生选课记录。
说明:
删除后,请重新插入SC表中的记录。
实验三单表查询
一、实验目的
掌握简单SQL查询语句的应用,包括like、top、orderby、compute、聚集函数的应用。
二、实验内容
1、基本查询
(1)查询学生表中全体学生的全部信息。
(2)检索全体学生的学号、姓名。
2、查询时改变列标题的显示
检索全体学生的学号、姓名、家庭地址信息,并分别加上“学生”、“学号”、“家庭地址”的别名信息。
3、条件查询
(1)查询成绩大于80分的学生的学号及课程号、成绩。
(2)查询成绩介于75~80分的学生的学号及课程号、成绩。
(3)查询选修了课程号为“002”,且成绩大于80的学生的学号。
(4)某些学生选修某门课程后没有参加考试,所以有选课记录,但没有考试成绩,下面我们来查一下缺少成绩的学生的学号和相应的课程号。
4、基于IN子句的数据查询
从课程表中查询出“高数”、“C语言程序设计”的所有信息。
5、基于BETWEEN。
。
。
。
AND子句的数据查询
查询所有成绩在70-80之间的学生选课信息。
6、基于Like子句的查询
(1)从学生表中分别检索出姓张的所有同学的资料;检索名字的第二个字是“红”或“虹”的所有同学的资料。
(2)查询课程名为Visual_Basic的课程的学分。
(提示:
使用ESCAPE短语)
7、使用top关键字查询
(1)从选课表中检索出前3个课程信息。
(2)从选课表中检索出前面20%的课程信息。
8、消除重复行
检索出学生已选课程的课程号,要求显示的课程号不重复。
9、查询经过计算的值。
查询全体学生的姓名及其年龄(提示:
利用系统函数getdate())
10、使用ORDERBY语句对查询的结果进行排序
(1)显示所有学生的基本信息,按班号排列,班号相同则再按学号排列。
(2)查询全体学生的姓名及其年龄,并按学生的年龄的降序排列。
11、使用聚合函数
(1)查询学生总人数。
(2)计算“002”号课程的学生平均成绩、最高分、最低分。
12、使用Group子句进行查询
(1)查询各班级学生总人数。
(2)汇总总分大于150分的学生的学号及总成绩。
(3)查询各个课程号相应的选课人数。
13、使用Compute和computeby子句进行查询
(1)汇总每个学生的学号及总成绩。
(2)按学号汇总出每个学生的学号及总成绩、最高分、最低分,及所有学生总成绩。
观察使用Compute子句和Computeby子句执行结果有何不同?
实验四复杂查询
一、实验目的
掌握两个表以上的连接查询的应用,包括嵌套查询。
二、实验内容
(1)查询比“林红”年纪大的男学生信息。
(2)检索所有学生的选课信息,包括学号、姓名、课号、课程名、成绩。
(3)查询已选课学生的学号、姓名、课程名、成绩。
(4)查询选修了“C语言程序设计”的学生的学号和姓名。
(5)查询与“张虹”在同一个班级的学生学号、姓名、家庭住址。
a.用子查询
b.用连接查询
(6)查询其他班级中比“051”班所有学生年龄大的学生的学号、姓名。
(7)(选作)查询选修了全部课程的学生姓名。
(8)(选作)查询至少选修了学生“20050002”选修的全部课程的学生的学号,姓名。
(9)检索学生的学号、姓名、学习课程名及课程成绩。
(10)检索选修了“高数”课且成绩至少高于选修课程号为“002”课程的学生的学号、课程号、成绩,并按成绩从高到低次序排列。
(11)检索选修3门以上课程的学生的学号、总成绩(不统计不及格的课程),并要求按总成绩的降序排列出来。
(12)检索多于3名学生选修的并以3结尾的课程号的平均成绩。
(13)检索最高分与最低分之差大于5分的学生的学号、姓名、最高分、最底分。
(14)外连接
对实验二中的表6和表7做一个外连接查询,显示每门课程的课号、课名、选修该门课的学号、成绩,没有同学选修的课程(如Visual_Basic)也要在查询结果中。
(15)创建一个表Student_other,结构同Student,输入若干记录,部分记录和Student表中的相同。
a.查询同时出现在Student表和Student_other表中的记录
b.查询Student表和Student_other表中的全部记录
(16)(选作)创建一个数据库Student_info_other,参数自定。
a.当前数据库为Student_info,将Student_info数据库中的表Student_other复制到Student_info_other中。
b.查询同时出现在Student表和tudent_info_other数据库Student_other表中的记录。
实验五视图的创建与使用
一、实验目的
(1)理解视图的概念。
(2)掌握创建视图、测试、加密视图的方法。
(3)掌握更改视图的方法。
(4)掌握用视图管理数据的方法。
二、实验内容
1.创建视图
(1)创建一个名为stuview2的水平视图,从数据库Student_info的Student表中查询出性别为“男”的所有学生的资料。
并在创建视图时使用withcheckoption。
(注:
该子句用于强制视图上执行的所有修改语句必须符合由Select语句where中的条件。
)
(2)创建一个名为stuview3的投影视图,从数据库Student_info的Course表中查询学分大于3的所有课程的课程号、课程名、总学时,并在创建时对该视图加密。
(提示:
用withENCRYPTION关键子句)
(3)创建一个名为stuview4的视图,能检索出“051”班所有女生的学号、课程号及相应的成绩。
(4)创建一个名为stuview5的视图,能检索出每位选课学生的学号、姓名、总成绩。
2.查询视图的创建信息及视图中的数据
(1)查看视图stuview2的创建信息。
a.通过系统存储过程sp_help查看
b.通过查询表sysobjects
(2)通过查看视图的定义脚本。
a.通过系统存储过程sp_helptext
b.通过查询表sysobjects和表syscomments
(提示:
视图的名称保存在表sysobjects的name列,定义脚本保存在表syscomments的text列)
(
3)查看加密视图stuview3的定义脚本。
3.修改视图的定义
(1)修改视图stuview3使其从数据库Student_info的Student表中查询总学时大于60的所有课程的课程号、课程名、学分。
(提示:
若视图原具有加密保护,修改视图时若未加withencryption子句,则修改后的视图不再加密。
)
4.视图的更名与删除
1)用系统存储过程sp_rename将视图stuview4更名为stuv4。
2)将视图stuv4删除。
5.管理视图中的数据
1)从视图stuview1查询出班级为“051”、姓名为“张虹”的资料。
2)向视图stuview1中插入一行数据,内容为:
学号姓名班级性别家庭住址入学时间出生年月
20110005赵小林054男南京2011/09/011993/01/09
3)查询student,查看表中的内容有何变化。
4)向视图stuview1中插入一行数据,内容为:
学号姓名班级性别家庭住址入学时间出生年月
20110006赵静054女南京2011/09/011993/11/09
能成功插入吗?
原因何在?
5)修改视图stuview1中的数据。
a.将stuview1中054班、姓名为“赵小林”同学的家庭地址改为“扬州市”。
b.查询student,查看表中的内容有何变化
6)从视图stuview1中将班级为054、姓名为“赵小林”同学删除。
3)
实验六存储过程
一、实验目的
(1)掌握T-SQL流控制语句。
(2)掌握创建存储过程的方法。
(3)掌握存储过程的执行方法。
(4)掌握存储过程的管理和维护。
二、实验内容
1、创建简单存储过程
(1)创建一个名为stu_pr的存储过程,该存储过程能查询出051班学生的所有资料,包括学生的基本信息、学生的选课信息(含未选课同学的信息)。
要求在创建存储过程前请判断该存储过程是否已创建,若已创建则先删除,并给出“已删除!
”信息,否则就给出“不存在,可创建!
”的信息。
2、创建带参数的存储过程
(1)创建一个名为stu_proc1的存储过程,查询某系、某姓名的学生的学号、姓名、年龄,选修课程名、成绩。
系名和姓名在调用该存储过程时输入,其默认值分别为“%”与“林%”。
执行该存储过程,用多种参数加以测试。
(2)创建一个名为Student_sc的存储过程,可查询出某段学号的同学的学号、姓名、总成绩。
(学号起始号与终止号在调用时输入,可设默认值)。
执行该存储过程。
3、创建带输出参数的存储过程
(1)创建一个名为Course_sum的存储过程,可查询某门课程考试的总成绩。
总成绩可以输出,以便进一步调用。
(2)创建一执行该存储过程的批处理,要求当总成绩小于100时,显示信息为:
“XX课程的总成绩为:
XX,其总分未达100分”。
超过100时,显示信息为:
“XX课程的总成绩为:
XX”。
4、创建带重编译及加密选项的存储过程
创建一个名为update_sc、并带重编译及加密选项的存储过程,可更新指定学号、指定课程号的学生的课程成绩。
(学号、课程号由调用时输入)
5、使用T-SQL语句管理和维护存储过程
(1)使用sp_helptext查看存储过程Student_sc的定义脚本
(2)使用select语句查看Student_sc存储过程的定义脚本(提示:
通过查询表sysobjects和表syscomments)
(3)将存储过程stu_pr改为查询学号为2011001的学生的详细资料。
(4)删除存储过程stu_pr。
6、使用SQLServerManagementStudio管理存储过程
(1)在SQLServerManagementStudio中重新创建刚删除的存储过程stu_pr
(2)查看存储过程stu_pr,并将该过程修改为查询051班女生的所有资料。
(3)删除存储过程stu_pr
实验七触发器
一、实验目的
(1)理解触发器的用途、类型和工作原理
(2)掌握利用T-SQL语句创建和维护触发器的方法
(3)掌握利用企业管理器创建、维护触发器的方法
二、实验内容
1.创建after触发器
(1)创建一个在插入时触发的触发器sc_insert,当向sc表插入数据时,须确保插入的学号已在Student表中存在,并且还须确保保插入的课程号在Course表中存在;若不存在,则给出相应的提示信息,并取消插入操作,提示信息要求指明插入信息是学号不满足条件还是课程号不满足条件(注:
Student表与sc表的外键约束要先取消)。
(2)为Course表创建一个触发器Course_del,当删除了Course表中的一条课程信息时,同时将表sc表中相应的学生选课记录删除掉。
(3)在Course表中添加一个平均成绩avg_Grade字段(记录每门课程的平均成绩),创建一个触发器Grade_modify,当SC表中的某学生的成绩发生变化时,则Course表中的平均成绩也能及时相应的发生改变。
(4)测试上述三个触发器。
2.创建insteadof触发器
(1)创建一视图Student_view,包含学号、姓名、课程号、课程名、成绩等属性,在Student_view上创建一个触发器Grade_moidfy,当对Student_view中的学生的成绩进行修改时,实际修改的是sc中的相应记录。
(2)在SC表中插入一个getcredit字段(记录某学生,所选课程所获学分的情况),创建一个触发器ins_credit,当更改(注:
含插入时)SC表中的学生成绩时,如果新成绩大于等于60分,则该生可获得这门课的学分,且该学分须与Course表中的值一致;如果新成绩小于60分,则该生未能获得学分,修改值为0。
(3)测试上述两个触发器。
3.使用T-SQL语句管理和维护
(1)用系统存储过程sp_helptrigger查看触发器Grade_modify的相关信息
(2)使用系统存储过程sp_helptext查看触发器Grade_modify中的定义内容。
(3)使用select语句查看触发器Grade_modify的定义内容。
(4)用系统存储过程sp_depends查看触发器Grade_modify的相关性。
(5)
将sc_insert触发器改为insteadof触发器,实现的功能不变。
(6)
将触发器sc_insert删除。
4.使用SQLServerManagementStudio管理存储过程
(1)在SQLServerManagementStudio中重新创建刚删除的触发器sc_insert
(2)查看触发器sc_insert的内容。
(3)删除触发器sc_insert
实验八实现数据完整性
一、实验目的
(1)实现数据完整性的概念及实施数据完整性的重要性。
(2)掌握数据完整性的分类。
(3)掌握完整性约束的添加、删除方法。
(4)掌握通用默认值的创建、实施与删除方法。
(5)掌握规则的创建、实施与删除方法。
(6)掌握级联删除、级联修改方法。
二、实验内容
1、完整性约束的添加、删除
(1)通过SQLServerManagementStudio实施约束
a.为表Student的Birth字段创建检查约束,使输入的生日日期小于系统日期。
b.为
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实验