数据库与表的基本操作实验报告.docx
《数据库与表的基本操作实验报告.docx》由会员分享，可在线阅读，更多相关《数据库与表的基本操作实验报告.docx（38页珍藏版）》请在冰点文库上搜索。
数据库与表的基本操作实验报告
《数据库与表的基本操作》
实验报告
项目名称数据库与表的基本操作
实验成绩：
批阅教师：
2015年5月11日
实验4《存储过程》
实验学时：
4
每组人数：
1
实验类型：
2（1：
基础性2：
综合性3：
设计性4：
研究性）
实验要求：
1（1：
必修2：
选修3：
其它）
实验类别：
3（1：
基础2：
专业基础3：
专业4：
其它）
一、实验目的
理解存储过程的概念、建立和调用方法。
进一步熟悉SQL语句对数据库进行完整性控制的方法。
二、实验内容
1、建立一个新的销售数据库，包含五张表，每张表至少需要10条记录。
（1）/*员工人事表employee*/
emp_no
char（5）
Notnull
primarykey
员工编号
emp_name
char（10）
员工姓名
sex
char
（1）
性别
dept
char（4）
所属部门
title
char（6）
职称
date_hired
datetime
到职日
birthday
datetime
生日
salary
int
薪水
addr
char（50）
null
住址
（2）/*客户表customer*/
cust_id
char（5）
Notnull
primarykey
客户号
cust_name
char（20）
客户名称
addr
char（40）
客户住址
tel_no
char（10）
客户电话
zip
char（6）
邮政编码
（3）/*销售主表sales*/
order_no
int
Notnull
primarykey
订单编号
cust_id
char（5）
客户号
sale_id
char（5）
业务员编号
tot_amt
numeric（9,2）
订单金额
order_date
datetime
订货日期
ship_date
datetime
出货日期
invoice_no
char（10）
发票号码
（4）/*销货明细表sale_item*/
order_no
int
Notnull,
primarykey
订单编号
prod_id
char（5）
Notnull,
primarykey
产品编号
qty
int
销售数量
unit_price
numeric（7,2）
单价
order_date
datetime
null
订单日期
（5）/*产品名称表product*/
pro_id
char（5）
Notnull
primarykey
产品编号
prod_name
char（20）
Notnull
产品名称
2、建立表的同时创建表的约束。
（1）为每张表建立主键约束。
（2）通过拖放操作加入外键。
（3）在表employee加入CHECK约束：
输入的员工编号必须以E开头的5位数编号，性别只能为M/F。
（4）为销售主表sales中的发票编号字段建立UNIQUE约束。
3、利用存储过程，给employee表添加一条业务部门员工的信息。
4、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。
5、利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。
6、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。
7、利用存储过程计算出订单编号为10003的订单的销售金额。
三、实验要求：
1．熟悉SQLSERVER工作环境；
2．建立销售数据库
3．复习有关约束与存储过程的SQL语言命令。
4．备份数据库，作为实验5的操作数据库。
四、实验步骤
1．创建销售数据库，并建表、修改，要求将自己的信息包含其中；
2、利用存储过程，给employee表添加一条业务部门员工的信息。
3、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。
4、利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。
5、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金。
五、实验结果
1、建立一个新的销售数据库，包含五张表，每张表至少需要10条记录。
（1）/*员工人事表employee*/
emp_no
char（5）
Notnull
primarykey
员工编号
emp_name
char（10）
员工姓名
sex
char
（1）
性别
dept
char（4）
所属部门
title
char（6）
职称
date_hired
datetime
到职日
birthday
datetime
生日
salary
int
薪水
addr
char（50）
null
住址
（2）/*客户表customer*/
cust_id
char（5）
Notnull
primarykey
客户号
cust_name
char（20）
客户名称
addr
char（40）
客户住址
tel_no
char（10）
客户电话
zip
char（6）
邮政编码
（3）/*销售主表sales*/
order_no
int
Notnull
primarykey
订单编号
cust_id
char（5）
客户号
sale_id
char（5）
业务员编号
tot_amt
numeric（9,2）
订单金额
order_date
datetime
订货日期
ship_date
datetime
出货日期
invoice_no
char（10）
发票号码
（4）/*销货明细表sale_item*/
order_no
int
Notnull,
primarykey
订单编号
prod_id
char（5）
Notnull,
primarykey
产品编号
qty
int
销售数量
unit_price
numeric（7,2）
单价
order_date
datetime
null
订单日期
（5）/*产品名称表product*/
pro_id
char（5）
Notnull
primarykey
产品编号
prod_name
char（20）
Notnull
产品名称
解：
首先写出每一个表的创建语句
CREATETABLEemployee
（emp_nochar（5）notnull,
emp_namechar（10）,
sexchar
（1）,
deptchar（4）,
titlechar（6）,
date_hireddatetime,
birthdaydatetime,
salaryint,
addrCHAR（50）,
primarykey（emp_no）
）
CREATETABLEcustomer
（cust_idchar（5）notnull,
cust_namechar（20）,
addrchar（40）,
tel_nochar（10）,
zipchar（6）,
primarykey（cust_id）
）
CREATETABLEsales
（order_nointnotnull,
cust_idchar（5）,
sale_idchar（5）,
tot_amtnumeric（9,2）,
order_datedatetime,
ship_datedatetime,
invoice_nochar（10）,
primarykey（order_no）
）
CREATETABLEsale_item
（order_nointnotnull,
prod_idchar（5）notnull,
qtyint,
unit_pricenumeric（7,2）,
order_datedatetimenotnull,
primarykey（order_no,prod_id）
）
CREATETABLEproduct
（pro_idchar（5）NOTNULL,
prod_namechar（20）NOTNULL,
primarykey（pro_id）
）
以上建表结果：
为每一个表输入数据：
2、建立表的同时创建表的约束。
（1）为每张表建立主键约束。
已建立主键约束
（2）通过拖放操作加入外键。
步骤如下：
首先点击数据库，可以看见，数据库下方有数据库关系表：
右键其，创建一个数据库关系图
接着可以看见
选择添加以上五个数据库，拖动键后便可以·设立建立
（1）在表employee加入CHECK约束：
输入的员工编号必须以E开头的5位数编号，性别只能为M/F。
步骤：
第一步右键
第二步：
又按着鼠标右键：
第三步：
点击添加
接着再表达式里面写着约束条件
（2）为销售主表sales中的发票编号字段建立UNIQUE约束。
altertablesalesaddconstraintorder_no——uniqueunique（order_no）;
3、利用存储过程，给employee表添加一条业务部门员工的信息。
createprocedureproAddEmployee
（@emp_nochar（5）,
@emp_namechar（10）,
@sexchar
（1）,
@deptchar（10）,
@titlechar（6）,
@date_hireddatetime,
@birthdaydatetime,
@salaryint,
@addrchar（50））
as
insertintoemployeevalues
（@emp_no,@emp_name,@sex,@dept,@title,@date_hired,@birthday,@salary,@addr）
go
execproAddEmployee'E0022','罗刚','M','业务','经理','2009-07-08','1988-02-03',13000,'都匀市'
执行后：
刷新表格后，查看表格，可以发现，表中写入了信息
结果为：
4、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。
createprocedurefind
as
selectemployee.emp_name,customer.cust_name,sales.tot_amt
fromemployee,customer,sales
wheresales.sale_id=employee.emp_noandsales.cust_id=customer.cust_id
go
execfind
执行后结果为：
刷新后，旁边的存储过程的显示为：
5、利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。
createprocedurefind
as
selectemployee.emp_name,customer.cust_name,sales.tot_amt
fromemployee,customer,sales
wheresales.sale_id=employee.emp_noandsales.cust_id=customer.cust_id
go
execfind
执行结果：
刷新后，可看见：
此结果采用的数据为：
Employee表：
Sales表：
6、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。
createprocedurefindLi
@emp_namevarchar（10）
as
selectemployee.emp_no,sales.order_no,sales.tot_amt
fromemployee,sales
whereemployee.emp_no=sales.sale_idandemployee.title='职员'and（employee.emp_namelike@emp_name）
go
execfindLi'李%'
结果：
刷新后，在旁边可看见：
结果的数据中所查询的表的内容为：
Employee表：
Sales表：
7、利用存储过程计算出订单编号为10003的订单的销售金额。
CREATEPROCEDUREPRO_ORDER
@order_novarchar（6）
as
selectsales.tot_amt
fromsales
wheresales.order_no=@order_no
go
execPRO_ORDER'10003'
实验结果：
刷新后发现左边更新：
附录：
实验示例
1、模糊查询
createproceduresp_empname@E_namevarchar（10）as
selecta.emp_name,a.dept,b.tot_amt
fromemployeeainnerjoinsalesb
ona.emp_no=b.sale_id
wherea.emp_namelike@E_name
go
execsp_empname'陈%'
2、利用存储过程计算出’E0014’业务员的销售总金额。
createproceduresp_saletot@E_nochar（5）,@p_totintoutputas
select@p_tot=sum（tot_amt）
fromsales
wheresale_id=@E_no
go
declare@tot_amtint
execsp_saletotE0014,@tot_amtoutput
select@tot_amt
六、实验结论
存储过程（StoredProcedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
sql中的存储过程：
CREATEPROCEDURE[拥有者.]存储过程名[;程序编号]
[（参数#1,…参数#1024）]
[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FORREPLICATION]
AS程序行
其中存储过程名不能超过128个字。
每个存储过程中最多设定1024个参数
（SQLServer7.0以上版本）,参数的使用方法如下：
@参数名数据类型[VARYING][=内定值][OUTPUT]
每个参数名前要有一个“@”符号,每一个存储过程的参数仅为该程序内部使用,参数的类型除了IMAGE外，其他SQLServer所支持的数据类型都可使用。
[内定值]相当于我们在建立数据库时设定一个字段的默认值，这里是为这个参数设定默认值。
[OUTPUT]是用来指定该参数是既有输入又有输出值的，也就是在调用了这个存储过程时，如果所指定的参数值是我们需要输入的参数，同时也需要在结果中输出的，则该项必须为OUTPUT，而如果只是做输出参数用，可以用CURSOR，同时在使用该参数时，必须指定VARYING和OUTPUT这两个语句。
分类：
1系统存储过程
以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。
2本地存储过程
用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。
3临时存储过程
分为两种存储过程：
一是本地临时存储过程，以井字号（#）作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;
二是全局临时存储过程，以两个井字号（##）号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。
4远程存储过程
在SQLServer2005中，远程存储过程（RemoteStoredProcedures）是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。
5扩展存储过程
扩展存储过程（ExtendedStoredProcedures）是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。
格式：
创建存储过程
createproceduresp_name
@[参数名][类型],@[参数名][类型]
as
begin
.........
end
以上格式还可以简写成：
createprocsp_name
@[参数名][类型],@[参数名][类型]
as
begin
.........
end
/*注：
“sp_name”为需要创建的存储过程的名字，该名字不可以以阿拉伯数字开头*/
调用存储过程
1.基本语法：
execsp_name[参数名]
删除存储过程
1.基本语法：
dropproceduresp_name
2.注意事项
（1）不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程
其他常用命令
1.showprocedurestatus
显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等
2.showcreateproceduresp_name
显示某一个mysql存储过程的详细信息
3、execsp_helptextsp_name
显示你这个sp_name这个对象创建文本
七、实验小结
在本次实验中，我学会了存储过程的参数的传递，以及参数的输入和参数的创建，以及使用存储过程去实现功能的查询，和打印输出某些东西。
实验5《触发器与游标》
实验学时：
4
每组人数：
1
实验类型：
2（1：
基础性2：
综合性3：
设计性4：
研究性）
实验要求：
1（1：
必修2：
选修3：
其它）
实验类别：
3（1：
基础2：
专业基础3：
专业4：
其它）
一、实验目的
进一步熟悉SQL语句对数据库进行完整性控制的方法；理解触发器的概念、定义方法和触发条件。
理解游标的定义、打开、使用、关闭与释放的方法。
二、实验内容
针对实验4所建销售数据库：
1、设置一个触发器，该触发器仅允许“dbo”用户可以删除employee表内数据，否则出错。
2、针对employee表写一个DELETE触发器。
3、针对employee表写一个UPDATE触发器。
4、统计employee表中员工的平均薪水，输出低于平均薪水的员工的人数以及这些员工的姓名与薪水（使用游标）。
三、实验要求：
1．熟悉SQLSERVER工作环境；
2．恢复实验4所建销售数据库
3．复习有关SQL语句对数据库进行完整性控制的方法；复习触发器的概念、定义方法和触发条件。
复习游标的定义、打开、使用、关闭与释放的方法约束与存储过程的SQL语言命令。
四、实验步骤
1.设置一个触发器，该触发器仅允许“dbo”用户可以删除employee表内数据，否则出错。
2.针对employee表写一个DELETE触发器。
3.针对employee表写一个UPDATE触发器。
4.统计employee表中员工的平均薪水，输出低于平均薪水的员工的人数以及这些员工的姓名与薪水（使用游标）。
五、实验结果
1.设置一个触发器，该触发器仅允许“dbo”用户可以删除employee表内数据，否则出错。
创建命令为：
createtriggerEmploteeDeleteonemployee
fordelete
as
ifexists（select*fromdeleted）--要删除的是有数据的
begin
ifuser!
='dbo'/*如果不是dbo用户*/
rollbacktransaction
end
结果：
2.针对employee表写一个DELETE触发器。
创建命令：
createtriggerdelete_disploy
onemployee
fordelete/*默认after*/
as
/*操作*/
SELECT*fromemployee
BEGIN
PRINT'已触发触发器了'
END
结果：
测试：
发现输出来的表已无E00001，说明删除成功
再看输出来的信息：
说明触发器成功执行
3.针对employee表写一个UPDATE触发器。
命令:
createtriggerUPDATE_DISPLOY
ONEMPLOYEE
FORUPDATE
AS
/*操作*/
select*fromemployee
BEGIN
PRINT'触发了更新操作的触发器'
END
结果：
更新前的数据为：
更新后：
消息处显示为：
4.统计employee表中员工的平均薪水，输出低于平均薪水的员工的人数以及这些员工的姓名与薪水（使用游标）。
原先备份过数据库，所以我先把数据后还原
还原后，employee表的数据为:
命令为：
DECLARECUR_EMPLOEE_NAME_SALARYSCROLLCURSORFOR--定义游标名为CUR_EMPLOEE_NAME_SALARY
SELECTEMPLOYEE.emp_no,EMPLOYEE.salary
FROMEMPLOYEEWHEREEMPLOYEE.salary<（SELECTAVG（E
升级会员 