数据库原理及应用实训指导书.docx
《数据库原理及应用实训指导书.docx》由会员分享，可在线阅读，更多相关《数据库原理及应用实训指导书.docx（64页珍藏版）》请在冰点文库上搜索。
数据库原理及应用实训指导书
数据库原理及应用
实训指导书
经济管理学院信息管理教研室
2007年2月
前言
数据库技术是计算机科学技术中发展最快的领域之一，也是应用范围最广、实用性很强的技术之一，它已成为信息社会的核心技术和重要基础。
“数据库原理及应用”是经济管理学院大部分专业的必修课程，其主要目的是使学生在较好掌握数据库系统原理的基础上，熟练掌握主流数据库管理系统（如SQLServer2000）的应用技术，并利用常用的数据库应用系统开发工具（如：
VB、Delphi、PB、.NET平台等）进行数据库应用系统的设计与开发。
在Internet高速发展的信息化时代，信息资源的经济价值和社会价值越来越明显，建设以数据库为核心的各类信息系统，对提高企业的竞争力与效益、改善部门的管理能力与管理水平，均具有实实在在的重要意义。
本实训指导书能合理安排课程实训内容，引导读者逐步掌握数据库应用的各种技术，为数据库应用系统的设计与开发打好基础。
本实训指导书是经济管理学院信息管理教研室在从事数据库课程教学的基础上，为满足“数据库原理及应用”课程的教学需要，配合选用《数据库原理及应用》（陆慧娟编著，科学出版社）教材而编写的。
全书共分为十二个实训内容（其中，非信管专业只做前6个实训项目；信管专业要完成前十个实训项目；后2个为选做实训项目），适合非信管专业14学时的实训教学和信管专业30学时的教学。
本实训指导书共有20多个实训目的，基本覆盖了数据库与原理课程的主要内容。
实训内容在安排上由浅入深、从验证观查到自己着手编写程序，步步引导学生迈进数据库的大门。
每个实训内容分为【实训目的】、【实训要求】、【实训课时】、【实训内容与步骤】等环节。
个别实训还配有【实训练习】供学生在课下或实训完成较快的学生在课上做。
本书中的全部代码均在SQLServer2000个人版上调试通过。
由于时间仓促，编者水平有限，书中难免有错误、疏漏和欠妥之处，敬请广大读者与同行专家批评指正。
问题反馈与探讨：
fujl_2008@
编者
2007年2月
目录
前言
实训一数据库的创建与管理1
实训二基本表的创建与管理9
实训三数据更新操作17
实训四数据表的简单查询21
实训五数据表的高级查询26
实训六数据完整性约束30
实训七视图的应用32
实训八存储过程与触发器34
实训九数据库设计40
实训十数据库应用程序开发43
*实训十一用户管理和权限管理44
*实训十二数据的传输操作47
注：
带*号的为选做实训内容
实训一数据库的创建与管理
【实训目的】
（1）掌握使用企业管理器创建、修改与删除数据库的方法
（2）掌握使用SQL语句创建、修改与删除数据库的方法
【实训要求】
（1）在F盘新建sy1和sy2两个文件夹。
（2）注意观察企业管理器中所包含的内容
（3）注意查询分析器的启动及使用的方法
（4）每操作完一步要求正确，边运行边查看结果
【实训学时】
2学时实训教师可以根据学生实际情况进行适当的调整，也可要求学生在课下完成部分实训任务
【实训内容与步骤】
一、使用企业管理器建立数据库
1、实训内容
根据需要建立一个学生的数据库（XSDB）。
数据文件及日志文件都建立在F盘的sy1的文件夹下。
数据文件大小为2MB，最大为4MB，每次文件增大1MB；日志文件初始大小为2MB，最大为40MB，每次增加1MB。
2、实训步骤
（1）打开“企业管理器”。
操作步骤：
开始程序\MicrosoftSQLServer企业管理器。
注意观察企业管理的控制台中所包含的内容。
（2）建立数据库。
操作步骤：
在控制台中找到数据库项，右击此项后，在弹出菜单中选择“新建数据库（B）…”项。
如图1-1所示。
小知识：
一般情况下，在菜单项或按钮中如出现"…"，表明要打开一个新窗口。
（3）输入数据库的名字。
在打开的“数据库属性”对话框中，选择“常规”选项卡，设置数据库的名字为“XSDB”。
如图1-2所示。
（4）设置数据库数据文件及日志文件的属性。
选择“数据文件”选项卡，根据实训要求依次设置数据库文件的初始大小、文件增长方式和文件的最大尺寸限制。
如图1-3所示。
选择“事务日志”选项卡，根据实训要求依次设置日志文件的初始大小、文件增长方式和文件的最大尺寸限制。
如图1-4所示。
图1-1新建数据库
图1-2新建数据库属性——常规选项设置
图1-3新建数据库属性——数据文件选项设置
图1-4新建数据库属性——事务日志选项设置
（5）设置完新建数据库的所有内容后，点击“确定”按钮，一个名为“XSDB”的数据库就创建完毕了，你可以观察一下控制台数据库选项下的变化及企业管理器右侧项目列表中的内容变化情况。
二、使用企业管理器修改数据库
1、实训内容
修改刚才新建的数据库XSDB。
把数据文件的初始大小改为4M，文件增长限制改为8M；日志文件的初始大小改为2M，文件增长限制改为20M。
2、实训步骤
在企业管理器中打开服务器中的“数据库”项，右击名为"XSDB"的数据库，在弹出菜单中选择“属性”项，出现如图1-5所示的对话框。
分别选择“数据文件”和“事务日志”选项卡，按实训内容进行相应的修改。
图1-5修改数据库XKDB的属性
三、使用企业管理器删除数据库
1、实训内容
将刚才创建的数据库删除。
2、实训步骤
在企业管理器中打开服务器中的“数据库”项，右击名为"XSDB"的数据库，在弹出菜单中选择“删除”项，出现如图1-6所示的对话框。
点击“是”，则删除了名为"XSDB"的数据库。
对话框中的复选框可以选择是否同时删除数据库备份以及恢复历史。
图1-6删除数据库的确认对话框
四、使用SQL语句在查询分析器中建立数据库
1、实训内容
使用查询分析器建立名为XSDB的数据库，数据文件及日志文件都建立在F盘的sy2文件夹下。
数据文件大小为2MB，最大为4MB，每次文件增大1MB；日志文件初始大小为2MB，最大为40MB，每次增加1MB。
2、实训步骤
（1）打开“查询分析器”。
操作步骤：
开始程序\MicrosoftSQLServer查询分析器。
（2）登录服务器。
界面如图1-7所示。
使用相应的用户名和密码登录（实训室的用户名：
as，密码为空）。
图1-7登录服务器
小知识：
在图1-7中的SQLServer中填入"."或"（local）"都是连接的本地服务器（即本台机器上安装的SQLServer）。
（3）创建数据库。
创建名为XSDB的数据库。
在查询分析器中输入如下语句。
（注意：
在SQL语句中标点符号使用的是英文半角字符）。
UseMaster
GO
CREATEDATABASEXSDB
ON
（NAME=XSDB_data,
FILENAME='F:
\sy2\XSDB.mdf',
SIZE=2,
MAXSIZE=4,
FILEGROWTH=1）
LOGON
（NAME='XSDB_log',
FILENAME='F:
\sy2\XSDB.LDF',
SIZE=2MB,
MAXSIZE=40MB,
FILEGROWTH=1MB）
GO
USEXSDB--选择所使用的数据库，今后在使用前首先要选择相应的数据库
GO
（4）运行代码创建数据库。
点击绿色三角形图标运行全部代码。
检查是否有错误发生，并进行改正。
注意：
点击保存按钮，可以将输入的SQL语句保存下来，备今后使用。
五、使用SQL语句在查询分析器中修改数据库
1、实训内容
请使用SQL语句把刚才建立的XSDB数据库的数据文件的初始大小改为4M，文件增长限制改为8M；日志文件的初始大小改为2M，文件增长限制改为20M。
2、实训步骤
在查询分析器中输入如下代码，并运行：
USEmaster
GO
ALTERDATABASEXSDB
MODIFYFILE
（NAME=XSDB_data,
SIZE=4MB,
MAXSIZE=8MB）
GO
ALTERDATABASEXSDB
MODIFYFILE
（NAME=XSDB_log,
SIZE=2MB,
MAXSIZE=20MB）
GO
思考问题：
用SQL语句对数据库进行修改时，指定文件的大小是否可以小于原来的大小？
六、使用SQL语句在查询分析器中删除数据库
（1）除去单个数据库
DROPDATABASEXSDB
（2）除去多个数据库
DROPDATABASEpubs,XKDB（可以不做，只做了解）
【实训练习】
使用查询分析器建立数据库dinghuo。
并把相关数据库数据文件和日志文件放在F:
\sy1中，数据文件初始大小为3MB，每次增长5MB，最大为100MB；日志文件初始大小为4MB，每次增长2MB，最大无限制。
并将脚本文件保存在F:
\sy1\文件夹下，名称为LX1.sql
实训二基本表的创建与管理
【实训目的】
（1）掌握使用企业管理器创建、修改与删除基本表的方法
（2）掌握使用SQL语句创建、修改与删除基本表的方法
（3）掌握在两种环境下设置数据约束的方法
【实训要求】
（1）实训前进行相关知识的预习
（2）了解企业管理器及查询分析器中常用菜单及工具图标的用途
【实训学时】
2学时实训教师可以根据学生实际情况进行适当的调整，也可要求学生在课下完成部分实训任务
【实训内容与步骤】
实训内容
根据实训需要建立三个基本表，即学生表（Student），课程表（Cource），学生课程成绩表（SC），经过调查后各个表的字段如表2-1至2-3所示。
表2-1学生关系（student）结构
列名
说明
数据类型
约束
Sno
学号
字符串，长度为10
主码
Sname
姓名
字符串，长度为10
非空
Ssex
性别
字符串，长度为2
取’男’或’女’
Sage
年龄
整数
取值15-45
Sdept
所在系
字符串，长度为20
默认为“管理系”
表2-2课程关系（cource）结构
列名
说明
数据类型
约束
Cno
课程号
字符串，长度为4
主码
Cname
课程名
字符串，长度为20
非空
Cxf
学分
整数
取值大于0
Cxq
学期
整数
取值大于0
Cxs
学时
整数
取值大于0
表2-3学生课程成绩关系（SC）结构
列名
说明
数据类型
约束
Sno
学号
字符串，长度为10
主码，引用Student的SNO
Cno
课程号
字符串，长度为4
主码，引用Course的CNO
grade
成绩
整数
取值0~100
一、在企业管理器中实现
1、关系表的定义
使用SQLServer的企业管理器创建新表的步骤如下（以建立表2-1为例）：
（1）打开服务器组，然后打开服务器；
（2）打开“数据库”文件夹，再展开"XSDB"数据库；
（3）右击“表”，从弹出的快捷菜单中单击“新建表”项，进入如图2-1所示的窗口：
（4）窗口分为上下两个设置栏，上面的设置栏，可以在“列名”、“数据类型”、“长度”和“允许空”四个栏目内分别按表2-1中的说明填入相应属性值；根据数据类型的不同，在下面的设置栏，可以更改“默认值”等选项。
具体可参见图2-1。
（5）设置主键。
鼠标定位到第一行，单击“设置主键”工具图标，则给第一行的属性设置了主键，设置主键的行会在该行左侧出现一个小钥匙的图标进行标识。
具体参见图2-1。
（6）设置表的约束。
单击“设置约束”工具图标，则会弹出一个“属性”对话框，选择“Check约束”选项卡，点击“新建”，则会建立一个约束，在“约束表达式”中输入“ssex='男'orssex='女'”。
则给Ssex字段添加了一个约束，限制该字段中的内容只能取“男”和“女”两个值。
再点击“新建”按钮，并在“约束表达式”中输入“Sage>=15andSage<=45”。
则给Sage字段添加了一个约束，限制该字段中的内容只能取[15，45]之间的值。
具体参见图2-2。
图2-1使用企业管理器创建表
图2-2设置表的约束对话框
小知识：
如果想给某一字段设置主键，也可以在该行中右击鼠标，在弹出的快捷菜单中选择“设置主键”。
用同样的方法也可以设置表的约束属性。
（7）保存表。
点击“保存”工具图标，则会弹出如图2-3所示的对话框，设置表的名字为"Student"，点击“确定”，则第一个表创建完毕。
图2-3选择表名称对话框
请使用同样的方法创建表2-2和表2-3，并注意在创建表2-3时，如何创建外键约束。
2、关系表的修改
在定义完表之后，如果需要对表进行修改，如添加列，删除列或修改列定义。
在SQLServer中，表的修改方法也有两种：
一是使用T-SQL语句在查询分析器中进行操作；一是使用企业管理器。
下面先介绍使用企业管理器修改表的方法。
展开“XSDB”数据库，单击“表”项，在右侧窗口中右击“Student”表，选择“设计表”菜单（如图2-4所示），将弹出与图2-1相同的对话框，可以对其进行修改，修改完毕后，点击“保存”图标。
图2-4修改表
3、基本表的删除
选择要删除的表，右击该表，在弹出菜单中选择“删除”。
系统将弹出如图2-5所示的对话框，点击“全部移去”，则该表将在数据库中删除。
图2-5删除表确认对话框
二、使用T-SQL语句在查询分析器中实现表
1、定义基本表
选择所使用的数据库（使用语句useXSDB，也可以用图2-6所示的方法进行选择）。
在查询分析器的工作区内输入代码。
创建满足约束条件的三个基本表的SQL语句如下（注意，为了说明问题，将部分约束在列级完整性约束定义上实现，有的约束在表级完整性约束定义上实现）：
图2-6选择数据库的方法
CREATETABLEstudent
（
Snochar（10）PRIMARYKEY,
Snamechar（10）NOTNULL,
Ssexchar
（2）CHECK（ssex='男'orssex='女'）,
SagetinyintCHECK（sage>=15andsage<=45）,
Sdeptchar（20）DEFAULT'管理系'
）
CREATETABLEcourse
（
Cnochar（10）NOTNULL,
Cnamechar（20）NOTNULL,
CxftinyintCHECK（Cxf>0）,
CxqtinyintCHECK（Cxq>0）,
Cxsint,
PRIMARYKEY（Cno）
）
CREATETABLESC
（
Snochar（10）NOTNULL,
Cnochar（10）NOTNULL,
Gradetinyint,CHECK（Grade>=0ANDGrade<=100）,
PRIMARYKEY（Sno,Cno）,
FOREIGNKEY（Sno）REFERENCESStudent（Sno）,
FOREIGNKEY（Cno）REFERENCESCourse（Cno）
）
运行代码。
如有错误，检查后，请改正。
2、修改表结构
ALTERTABLE语句可以对表添加列、删除列和修改列的定义、定义主码和外码、也可以添加和删除约束。
ALTERTABLE的语句格式如下：
ALTERTABLE<表名>
[altercolumn<列名><数据类型>]
|[add[column]<列名><数据类型>[约束]
|[dropcolumn<列名>]
|[addprimarykey（列名[,…n]）]
|[addforeignkey（列名）references表名（列名）]
|[addunique（<列名,…n>）]
在查询分析器中实现以下实训项目：
a.对SC表中添加“选课类别”列，列名为XKLB字符串，长度为4。
ALTERTABLESC
AddXKLBchar（4）null
b.将新添加的XKLB的类型改为char（15）。
Altertablesc
AltercolumnXKLBchar（15）
c.删除SC中的XKLB列。
Altertablesc
DropcolumnXKLB
运行并查看结果。
在输入完每个小例子的时候都要看一下SC表结构是否相应的修改（注意：
要进行适当的刷新），如图2-7所示。
图2-7修改表结构及结果显示
小知识：
对于SQLserver2000的查询分析器，将要执行的代码选定后（如图2-7）再点击运行按钮就可以运行所选定的代码了。
3、删除表
当数据库中的某些表不再使用时，可以将其删除，以释放数据库空间，节省存储介质。
如果使用SQL语句，其格式如下：
DROPTABLE<表名>{[,<表名>]…}
如果要删除SC表，其语句格式如下：
DROPTABLESC
【实训练习】
在实训一中建立的dinghuo数据库中建立两个表，分别是顾客表和订购表。
顾客表（顾客ID，姓名，电话，地址）其中顾客ID是主码；
gk（gkid,gkxm,gkdh,gkdz）
订购表（订购ID，商品名称，顾客ID，订购数量，订货日期，交货日期），其中顾客ID参照顾客表中的顾客ID；
dh（dhid,dhmc,gkid,dhsl,dhrq,jhrq）
要求：
（1）建立以上两个表结构的SQL语句，完成相应的要求，SQL语句保存在F:
\sy\文件下名称为LX2.sql。
（2）修改表结构当顾客没有提供地址值时，使用默认值“UNKNOWN”。
（3）限制订购表中的“订购数量”必须大于0。
（4）限制订购表的“订货日期”必须早于“交货日期”。
实训三数据更新操作
【实训目的】
（1）掌握使用SQL语言向表中插入数据、修改数据和删除数据的方法
（2）掌握在SQLServer中进行数据更新的方法
【实训要求】
（1）使用查询分析器完成以下各种操作
（2）注意查询分析器所提供的其他功能
（3）对实训的SQL语句进行统一保存，以便日后使用
【实训学时】
2学时实训教师可以根据学生实际情况进行适当的调整，也可要求学生在课下完成部分实训任务
【实训内容与步骤】
说明：
本实训使用信息管理与信息系统实训室机器中已安装好的数据库XKDB，数据库中存有与实训二中相同的数据表。
一、插入数据
在SQL语言中使用INSERT语句在表中添加新数据。
INSERT语句的语法格式为：
INSERT[INTO]<表名>[（<列名列表>）]VALUES（值列表）
其中，列名列表中的列名必须是表定义中的列名，（值列表）中的值可以是常量也可以是NULL值，各值之间用逗号分隔。
动手做：
①将基本信息为（0305040235，陈冬，男，会计系，23）的学生插入到学生表中。
②新加一门课程，只知道课程编号和课程名称。
（C112，矿业运筹学）
思考问题：
如果在SC表中插入一条（0305040501,0505050505,80）的记录，是否能执行成功。
如果出现问题，说明为什么？
注意：
请接着做完本实训中的实训习题部分。
③请将你个人的信息插入到学生表中。
（请将一卡通号的前两位去掉）
④请将学号为'0405030401'，姓名为'王倩'，性别'女'，年龄21，院系为'管理学院'的学生信息插入到学生表中。
二、修改数据
在SQL语句中使用UPDATE语句对数据进行修改。
UPDATE语句的语法格式为：
UPDATE <表名> SET <列名=表达式>[,…n] [WHERE <更新条件>]
1、无条件更新
无条件更新是将所有的值进行相应的变化（即修改表中的所有数据行）。
动手做：
①将所有学生的系别都改为“信息系”。
②将所有学生的年龄都加1。
③请将所有学生的成绩提高5分。
④请将所有课程的学时增加10学时。
2、有条件更新
根据某种条件有选择性的进行修改。
在where子句中指定更改数据的条件。
（1）基于本表条件的更新
①将不及格的学生的成绩改为55。
UPDATESCSETGRADE=55WHEREGRADE<60
②将学号为“0305030128”学生的年龄改为19。
UPDATESUTDENTSETSAGE=19WHERESNO='0305030128'
③将“0305050423”的姓名改为“何洁”，同时将年龄改为23。
UPDATESTUDENTSETSNAME='何洁',SAGE=23WHERESNO='0305050423'
动手做：
④请将你个人的年龄增加1岁。
⑤请将管理系学生年龄大于24岁的学生的年龄减1岁。
⑥请将第3学期学时少于40学时的课程的学时改为45学时。
（2）基于其它表条件的更新
①将管理系全体学生成绩都加5分。
UPDATESCSETGRADE=GRADE+5
WHERESNOIN（SELECTSNOFROMSTUDENTWHERESDEPT='管理系'）
②将年龄小于21岁的学生成绩加1分。
UPDATESCSETGRADE=GRADE+1
WHERESNOIN（SELECTSNOFROMSTUDENTHERESAGE<21）
动手做：
③请将选修“数据库原理与应用”这门课程的学生成绩增加5分。
④将信息系学生姓名中含有“丁”字的学生成绩改为67。
三、删除数据
DELETE语句的语法格式为：
DELETE [FROM]<表名> [WHERE <删除条件>]
1、有条件删除
（1）基于本表条件的删除
①删除年龄大于22岁的学生。
DELETESTUDENTWHERESAGE>22
动手做：
②请将学时少于20学时的课程信息删除。
③请将选课表（SC）中学生成绩为空的信息删除。
（2）基于其它表条件的删除
①删除只要成绩小于60的学生的信息
DELETESTUDENTWHERESNOIN
（SELECTSNOFROMSCWHEREGRADE<60）
思考问题：
能不能执行成功。
为什么？
如果想删除该学生应如何操作。
动手做：
②请将学时少于20学时的学生选课信息删除。
③删除采矿系学生的选课信息。
④请将选修了会计学基础这门课程的数力系的男生信息删除。
2、无条件删除
删除SC表中所有的选课记录。
DELETEFROMSC或DELETESC
【实训练习】
因今后实训需要向表中插入以下数据（注意要保存SQL语句，名为LX3.sql）
（1）将下列数据插入到student表中。
Sno
Sname
Ssex
sage
Sdept
0305050112
李勇
男
19
管理系
0305050423
王敏
女
20
管理系
0305030128
张立
男
2
升级会员 