2数据库实验指导书.docx
《2数据库实验指导书.docx》由会员分享，可在线阅读，更多相关《2数据库实验指导书.docx（101页珍藏版）》请在冰豆网上搜索。
2数据库实验指导书
数据库系统原理与应用
实验指导书
浙江农林大学环境与资源学院
2015.3
目录
实验一数据库定义3
实验二数据插入、修改、删除与简单查询4
实验三数据库的连接查询和嵌套查询12
实验四数据库的组合查询和统计查询14
实验五数据完整性和数据安全性17
实验六数据库备份和恢复34
实验七数据库前端开发工具基础41
学生选课管理系统开发（课外练习）57
实验一数据库定义
实验目的
1．熟悉SQLServer2008中SQLServerManagementStudio的环境
2．了解SQLServer2008数据库的逻辑结构和物理结构
3．掌握使用向导方式和SQL方式来创建和删除数据库及数据表的方法
4．学会数据操纵语言Insert、Update、Delete最基本的使用方法。
实验准备
1．装有SQLServer2008的PC机。
2.明确能够创建数据库的用户必须是系统管理员。
实验要求
1.熟练使用SQLServerManagementStudio（简称SSMS）中的进行数据库的创建
和删除操作，以及数据表的创建和删除操作。
2.熟练使用SQL语句创建和删除数据库数据表。
3.学会分离和附加数据库。
4.学会使用Insert、Update、Delete语句。
5.完成相应的实验报告。
实验内容
设有一学籍管理系统，其数据库名为“STUDENT”，初始大小为10MB，最大为50MB，数据库自动增长，增长方式是按5％比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。
数据库的逻辑文件名为“student_data”（如不修改则默认为数据库名STUDENT_data）,存放路径为“E:
\sql_data”（可自己选择存放路径）,物理文件名为“student_data.mdf（自动生成，无需设置），创建完成后可在存放路径下查看物理文件。
日志文件的逻辑文件名为“student_log”,存放路径为“E:
\sql_data”（可自己选择存放路径），物理文件名为“student_log.ldf”，（自动生成，无需设置），创建完成后可在存放路径下查看物理文件。
（1）分别使用向导和SQL语句两种方式创建上述描述的数据库STUDENT。
使用SQLServerManagementStudio（简称SSMS）创建数据库。
（2）创建STUDENT数据库中的三个基本表（见教材教学管理数据库三个表结构），并录入表中数据。
（3）删除上面建立的SC表。
（4）删除上面建立的数据库STUDENT。
●操作指导——使用向导创建和删除数据库数据表
（1）使用向导创建上述描述的数据库。
使用SQLServerManagementStudio（简称SSMS）创建数据库。
1）启动SSMS
在开始菜单中：
所有程序－SQLServer2008－SQLServerManagementStudio
单击“连接”按钮，便可以进入【SQLServerManagementStudio】窗口。
如果身份验证选择的是“混合模式”，则要输入sa的密码。
2）建立数据库
在“对象资源管理器”窗口，建立上述数据库STUDENT。
在数据库节点上右击选择新建。
在下图“新建数据库”窗口中，完成实验内容中STUDENT数据库的各项要求。
以上设置完成后，点击“确定”。
完成数据库创建。
即可在“对象资源管理器”中查看到STUDENT。
如下图所示。
（2）使用向导创建STUDENT数据库中的三个基本表，并录入数据。
以student表为例，表结构如下：
student表（学生信息表）
字段名称
类型
宽度
允许空值
主键
说明
sno
char
9
NOTNULL
是
学生学号
sname
char
20
NOTNULL
学生姓名
ssex
char
2
NULL
学生性别
sage
smallint
NULL
学生年龄
sdept
char
20
NULL
学生所在院系
1）打开STUDENT前的“+”，右击下层的“表”选项，右侧弹出表设计窗口，先完成student的创建，
右击sno设置主键，然后点击
保存，对话框中输入student（表名），点击“确定”，完成student表的创建。
目前，该表还只是一张空白表，里面没有任何数据。
可点击STUDENT下“表”前“+”，查
看已创建的student表，右击“dbo.student”----打开表，如下图
按行录入表中数据，数据参照教材（P82）。
录入完成后，关闭窗口，系统自动保存数据。
2）使用同样方法继续在STUDENT数据库中创建另外两个表course和sc表。
表结构如下：
注意：
sc表设置主键时，需要把sno和cno两行一起选中，再设置。
course表（课程信息表）
字段名称
类型
宽度
允许空值
主键
说明
cno
char
4
NOTNULL
是
课程编号
cname
char
40
NOTNULL
课程名称
cpno
char
4
NULL
先修课课程编号（外键）
ccredit
smallint
NULL
课程学分
sc表（学生选课成绩表）
字段名称
类型
宽度
允许空值
主键
说明
sno
char
9
NOTNULL
是
学生学号
cno
char
4
NOTNULL
课程编号
grade
smallint
NULL
学生成绩
（3）使用向导删除上面建立的SC表。
右击“dbo.sc”选择“删除”，确认删除此表。
（4）使用向导删除上面建立的数据库STUDENT。
（建议下课前再做此项，也可简单另外创建一个新数据库，再对其删除。
）
右击“STUDENT”选择“删除”，确认删除此数据库。
操作指导——使用SQL语句创建和删除数据库数据表
1.使用SQL创建数据库STUDENT。
打开SQLServerManagementStudio点击左上角“新建查询”连接数据库成功后，右侧即可出现查询分析器空白窗口，输入SQL语句，点击
分析有无语法错误，如有错误，根据提示修改，直到“命令成功完成”再点击
运行该SQL语句，“命令成功完成”后，刷新左侧数据库选项，即可看到新创建的数据库。
1）以下是创建数据库userdb1的SQL语句，
createdatabaseuserdb1
on
（name=userdb1_data,--数据文件的逻辑名称,注意不能与日志逻辑同名
filename='d:
\SQL_data\userdb1.mdf',--物理名称，注意路径必须存在
size=5,--数据初始长度为M
maxsize=10,--最大长度为M
filegrowth=1）--数据文件每次增长M
logon
（name=userdb1_log,
filename='d:
\SQL_data\userdb1.ldf',
size=2,
maxsize=5,
filegrowth=1）
运行上述语句建立数据库userdb1。
2）根据步骤1）的SQL语句，写出创建数据库STUDENT的SQL语句，数据库要求见实验一，并建立数据库STUDENT。
2.使用SQL创建数据库STUDENT中的三个基本表student，course和SC。
在SQLServer2008的查询分析器中，用sql语句创建student表。
点击“新建查询”，打开新的窗口，输入以下创建语句，注意选择自己的数据库，否则默认为系统数据库master.
CREATETABLEstudent
（snochar（9）PRIMARYKEY,
Snamechar（20）UNIQUE,
Ssexchar（10）,
Sagesmallint,
sdeptchar（20）
）;
以上方法用SQL语句创建其他表（建表SQL语句参考课本上的例子）。
数据录入方法同实验一。
3.练习使用分离附加数据库。
当数据库数据表创建好后，如想把数据库拷贝出来，必须先从SQLServerManagementStudio中分离数据库，然后可到物理文件存储路径下，复制数据文件和日志文件。
下次再需要使用该数据库数据时，再把物理文件附加进SQLServerManagementStudio。
具体操作如下：
1）首先关闭数据库中的所有表的窗口。
右击对象资源管理器中要分离的数据库名，选择“任务—分离”点击“确认”即可，分离成功后，对象资源管理器中，该数据库立刻消失了。
2）打开数据库的物理存储路径，如“d:
\SQL_data\”（以自己数据库存储路径为准），把相应数据库的数据文件和日志文件，一并拷贝，转入U盘保存。
3）“附加”操作。
打开对象资源管理器，右击“数据库”选择“附加”，弹出附加数据库窗口，点击“添加”，选择数据库物理文件存储路径，“确定”，附加成功后，对象资源管理器中，出现新附加的数据库名。
4.使用SQL删除SC表。
在查询分析器中输入SQL语句Droptablesc;执行该语句完成删除数据表操作。
5.使用SQL删除STUDENT数据库。
在查询分析器中输入SQL语句DropdatabaseSTUDENT;执行该语句完成删除数据库操作。
操作指导——使用SQL语句修改数据表和对数据操作
student表（学生信息表）
字段名称
类型
宽度
允许空值
主键
说明
sno
char
9
NOTNULL
是
学生学号
sname
char
20
NOTNULL
学生姓名
ssex
char
2
NULL
学生性别
sage
smallint
NULL
学生年龄
sdept
char
20
NULL
学生所在院系
course表（课程信息表）
字段名称
类型
宽度
允许空值
主键
说明
cno
char
4
NOTNULL
是
课程编号
cname
char
40
NOTNULL
课程名称
cpno
char
4
NULL
先修课课程编号（外键）
ccredit
smallint
NULL
课程学分
sc表（学生选课成绩表）
字段名称
类型
宽度
允许空值
主键
说明
sno
char
9
NOTNULL
是
学生学号
cno
char
4
NOTNULL
课程编号
grade
smallint
NULL
学生成绩
（3）使用向导删除上面建立的SC表。
1、CreateTable
--建立学生表
CREATETABLEstudent
（snochar（9）PRIMARYKEY,
Snamechar（20）NOTNULL,
Ssexchar（10）,
Sagesmallint,
sdeptchar（20））;
--建立课程表
CREATETABLEcourse
（cnochar（4）PRIMARYKEY,
cnamechar（40）NOTNULL,
cpnochar（4）,
ccreditsmallint）;
--建立选课表
CREATETABLESC
（snochar（9）NOTNULL,
cnochar（4）NOTNULL,
gradedec（4,1）,
Constraintc1PrimaryKey（sno,cno）,
Constraintc2ForeignKey（sno）referencesstudent（sno）,
Constraintc3ForeignKey（cno）referencescourse（cno））;
２、AlterTable
--添加一列cphone
AlterTablestudent
addcphonechar（11）;
--修改列属性
AlterTablestudent
Altercolumncphonechar（12）;
--删除一列
AlterTablestudent
Dropcolumncphone;
３、InsertInto
--插入数据
InsertIntostudent
Values（'100000001','李晓峰','男',22,'环境与资源学院'）;
４、Delete
--删除数据
DeleteFromstudent;
实验二数据插入、修改、删除与简单查询
一、实验目的
要求学生熟练掌握和使用SQL、T-SQL、SQLServer企业管理器创建数据库、表和索引,修改表结构,学会使用查询分析器接受T-SQL语句,进行结果分析。
掌握SQLServer查询分析器的使用方法，加深对SQL和TransactSQL语言的查询语句的理解。
掌握简单表的数据查询、数据排序和数据联结查询的操作方法。
二、实验内容
1.建立数据库:
学生选课
学生选课数据包含4个表，内容如下：
学生（学号，姓名，年龄，性别，学院号）；
课程（课程号，课程名，先行课）；
选课（学号，课程号，成绩）
学院（学院号,学院名）
2.基本查询操作
（1）熟悉SQLServer企业管理器和查询分析器的基本使用方法
（2）简单查询操作
该实验包括投影、选择条件表达、数据排序等。
（3）连接查询操作
该实验包括求笛卡儿积、内连接、左外连接、右外连接、等值连接和自连接。
三、实验步骤
1用企业管理器建立学生选课数据库（名称：
学生选课,初始大小10MB,增长10%）
2在企业管理器中察看学生选课数据库属性,并修改
3用查询分析器建学生、课程、学院和选课4个表，同时建立约束：
学生（学号smallint,姓名char（10）,年龄smallint,性别char
（2）,学院号tinyint）;--主码，外码
课程（课程号smallint,课程名char（20）,先行课smallint）;--主码
选课（学号smallint,课程号smallint,成绩decimal（4,1））;--主码，外码
学院（学院号tinyint,学院名char（30））;--主码
选择合适的数据类型,定义每个表的主码,是否允许空和默认值等列级数据约束。
4在企业管理器中浏览学生、课程、学院和选课4个表级数据约束：
每个表的主码约束、外码约束。
也可以尝试建立表和修改约束。
让大家明白查询分析器和企业管理器都可以进行数据定义，建议在一般情况下使用查询分析器。
5用T-SQL进行简单查询：
求环境与资源学院学生的学号和姓名；
求选修了课程的学生学号。
求选修了课程的学生学号、课程号和成绩。
求选修课程”数据库原理”且成绩在80~90分之间的学生学号和成绩。
求环境与资源学院或计算机学院姓张的学生的信息。
求缺少了成绩的学生的学号和课程号。
四、实验方法
（一）创建数据库和表
1、创建数据库
（1）用企业管理器创建数据库
1）开始---〉程序---〉SQLServer--->企业管理器
2）选中要在其上建立数据库的服务器,单击前面的”+”号,展开树形目录
3）选中“数据库”文件夹,点击鼠标右键,在弹出菜单上选“新建数据库”,随后在数据库属性中输入数据库名、数据文件名、日志文件名,并设置各自参数（参考上面的要求）；
2、查看数据库属性
选中数据库文件夹,点击鼠标右键,选择“属性”项,弹出数据库属性对话框,可以查看可修改数据库属性（参考课本）
3、删除数据库
选中要删除的数据库,点击鼠标右键,选择“删除”项,确认
4、新建表
用查询分析器—T-SQL
Createtable学生（…）
Createtable课程（…）
Createtable选课（…）
Createtable学院（…）
*注意几个表的参考和被参考关系，以及各自的主码和外码关系：
学生与学院表中的学院号：
外码与主码；
学生与选课表中的学号：
主码与外吗；
课程与选课表中的课程号：
主码与外吗；
*注意约束的先后顺序：
先建主码，后建外码。
删除的顺序相反。
详见后面的参考部分。
5、定义完整性约束
前3个表的check约束（随意建）
6、修改表结构
在学生表中添加”身份证号码”字段。
（1）在企业管理其中修改
用鼠标右击表名，在弹出菜单中选择设计表，弹出表属性窗口，可在此修改结构。
（2）用altertable修改
altertable学生addcolumn身份证号码char（16）
go
（7）修改身份证号码长度
altertable学生altercolumn身份证号码char（18）
go
8、删除列
altertable学生dropcolumn身份证号码
go
（二）插入、删除数据
1、直接插入
Insertinto学生（学号,姓名,性别,年龄,学院号）values（201218080102,'张凯烨','女',21,11）
或
Insertinto学生values（201218080102,'张凯烨','女',21,11）
或
Insertinto学生（学号,姓名）values（201218080102,'张凯烨'）
2、从查询结果插入
createtable地信专业学生（学号dec（12,0）primarykey,姓名char（10）,年龄smallint,性别char
（2）,学院号tinyint）
go
Insertinto地信专业学生（学号,姓名,性别,年龄,学院号）
Select学号,姓名,性别,年龄,学院号from学生
Go
3、删除数据
（1）删除部分数据
Deletefrom学生where学院=‘环境与资源学院’
Go
（2）删除全部数据
Deletefrom学生
Go
（三）简单查询
●将查询需求用TransactSQL语言表示。
⏹在SQLServer查询分析器的输入区中TransactSQL查询语句。
⏹设置查询分析器结果区为StandardExecute（标准执行）或ExecutetoGrid方式。
⏹发布执行命令，查看查询结果；如果结果不正确，进行修改，直到正确为止。
⏹查询分析器及使用方法。
查询分析器是在开发数据库应用系统时使用最多的工具。
查询分析器的主要作用是编辑
TransactSQL，将其发送到服务器，并将执行结果及分析显示出来（或进行存储）。
查询分
析功能主要通过测试查询成本，判断该查询是否需要增加索引以提高查询速度，并可以实现
自动建立索引的功能。
查询分析器的界面如图2—1图所示。
图2—1SQLServer2008查询分析器
Usestudent表示将student数据库作为当前操作的数据库，以后的SQL操作均在此库进行。
查询分析器启动时，默认当前数据库为Master数据库。
在查询生成器中的左边窗口是对象浏览器，其中按树结构列出了数据库对象；右上方是
SQL代码区域，用于输入SQL的查询语句；右下方为结果区，用于显示查询结果和分析结
果。
对于TSQL语句的执行结果，在结果区中可以有4种不同的输出形式：
标准执行将结果
直接显示在结果区；网格执行将结果以表格形式显示在结果区；计划执行显示执行计划：
索
引分析为在结果区中显示查询的索引情况。
上述输出形式，可以通过菜单或按钮选择。
1简单查询数据准备
将教材中用SQL描述的建立学生_课程库操作，在SQLServer企业管理器中实现。
建立数据库学生选课，其库中的学生、课程、选课、学院表的数据为：
学生（1,'刘纯',20,'女',11）
（2,'张浩',21,'男',11）
（3,'李丽',19,'女',21）
（4,'刘安元',19,'男',21）
（5,'岳不群',25,'男',null）
（6,'李东',21,'男',21）
（7,'张名',20,'男',31）
（8,'岳群',22,'男',41）
课程（1,’计算机原理’,’高等数学’）
（2,’计算机应用’,’计算机原理’）
（3,’数据库原理’,’C语言’）
（4,’信息工程’,’计算机应用’）
（5,’C语言’,’高等数学’）
（6,’数据结构’,’C语言’）
（7,’遥感概论’,’高等数学’）
（7,’高等数学’,’’）
选课（1,1,80）
（1,3,70）
（1,4,90）
（3,1,78）
（3,2,92）
（3,5,85）
（4,1,95）
（4,6,83）
（5,4,79）
（5,6,89）
（6,6,null）
（6,2,81）
学院（11,'林业生物技术'）
（21,'环境科技'）
（31,'园林'）
（41,'信息技术'）
（51,'工程'）
（61,'国际教育'）
2．基本操作实验
（1）简单查询实验
用TransactSQL语句表示下列操作，在学生选课库中实现其数据查询操作：
①求环境与资源学院学生的学号和姓名。
②求选修了课程的学生学号。
③求选修了课程的学生学号、课程号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。
④求选修课程”数据库原理”且成绩在80~90分之间的学生学号和成绩，并将成绩乘以系数0.7输出。
⑤求环境与资源学院或计算机学院姓张的学生的信息。
⑥求缺少了成绩的学生的学号和课程号。
（2）连接查询实验（扩展）
用Transact—SQL语句表示，并在学生选课库中实现下列数据连接查询操作：
①查询每个学生的情况以及他（她）所选修的课程。
②求学生的学号、姓名、选修的课程名及成绩。
③求选修”数据库原理”且成绩为90分以上的学生学号、姓名及成绩。
④查询每一门课的间接先行课（即先行课的先行课）。
（3）Transact—SQL语句表示学生和学院之间的内连接、左外部连接和右外部连接，在
学生、学院中实现其数据内连接和各种外查询操作。
（扩展）
五、注意事项
1．查询结果的几种处理方式。
2．内连接、左外部连接和右外部连接的含义及表达方法。
3．输入SQL语句时应注意，语句中均使用西文操作符号。
七、SQL参考
--1、建表和约束
usestudent
droptable学生
droptable课程
droptable选课
createt
升级会员 