数据库与表的基本操作实验报告Word文档下载推荐.docx
- 文档编号:7166626
- 上传时间:2023-05-08
- 格式:DOCX
- 页数:38
- 大小:827.26KB
数据库与表的基本操作实验报告Word文档下载推荐.docx
《数据库与表的基本操作实验报告Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《数据库与表的基本操作实验报告Word文档下载推荐.docx(38页珍藏版)》请在冰点文库上搜索。
primarykey
员工编号
emp_name
char(10)
员工姓名
sex
char
(1)
性别
dept
char(4)
所属部门
title
char(6)
职称
date_hired
datetime
到职日
birthday
生日
salary
int
薪水
addr
char(50)
null
住址
(2)/*客户表customer*/
cust_id
客户号
cust_name
char(20)
客户名称
char(40)
客户住址
tel_no
客户电话
zip
邮政编码
(3)/*销售主表sales*/
order_no
订单编号
sale_id
业务员编号
tot_amt
numeric(9,2)
订单金额
order_date
订货日期
ship_date
出货日期
invoice_no
发票号码
(4)/*销货明细表sale_item*/
Notnull,
prod_id
产品编号
qty
销售数量
unit_price
numeric(7,2)
单价
订单日期
(5)/*产品名称表product*/
pro_id
prod_name
产品名称
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、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金。
五、实验结果
解:
首先写出每一个表的创建语句
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
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)
以上建表结果:
为每一个表输入数据:
已建立主键约束
步骤如下:
首先点击数据库,可以看见,数据库下方有数据库关系表:
右键其,创建一个数据库关系图
接着可以看见
选择添加以上五个数据库,拖动键后便可以·
设立建立
(1)在表employee加入CHECK约束:
步骤:
第一步右键
第二步:
又按着鼠标右键:
第三步:
点击添加
接着再表达式里面写着约束条件
(2)为销售主表sales中的发票编号字段建立UNIQUE约束。
altertablesalesaddconstraintorder_no——uniqueunique(order_no);
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,'
都匀市'
执行后:
刷新表格后,查看表格,可以发现,表中写入了信息
结果为:
createprocedurefind
selectemployee.emp_name,customer.cust_name,sales.tot_amt
fromemployee,customer,sales
wheresales.sale_id=employee.emp_noandsales.cust_id=customer.cust_id
execfind
执行后结果为:
刷新后,旁边的存储过程的显示为:
执行结果:
刷新后,可看见:
此结果采用的数据为:
Employee表:
Sales表:
createprocedurefindLi
@emp_namevarchar(10)
selectemployee.emp_no,sales.order_no,sales.tot_amt
fromemployee,sales
whereemployee.emp_no=sales.sale_idandemployee.title='
职员'
and(employee.emp_namelike@emp_name)
execfindLi'
李%'
结果:
刷新后,在旁边可看见:
结果的数据中所查询的表的内容为:
CREATEPROCEDUREPRO_ORDER
@order_novarchar(6)
selectsales.tot_amt
fromsales
wheresales.order_no=@order_no
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
execsp_empname'
陈%'
2、利用存储过程计算出’E0014’业务员的销售总金额。
createproceduresp_saletot@E_nochar(5),@p_totintoutputas
select@p_tot=sum(tot_amt)
wheresale_id=@E_no
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
@[参数名][类型],@[参数名][类型]
begin
.........
end
以上格式还可以简写成:
createprocsp_name
/*注:
“sp_name”为需要创建的存储过程的名字,该名字不可以以阿拉伯数字开头*/
调用存储过程
1.基本语法:
execsp_name[参数名]
删除存储过程
dropproceduresp_name
2.注意事项
(1)不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程
其他常用命令
1.showprocedurestatus
显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等
2.showcreateproceduresp_name
显示某一个mysql存储过程的详细信息
3、execsp_helptextsp_name
显示你这个sp_name这个对象创建文本
七、实验小结
在本次实验中,我学会了存储过程的参数的传递,以及参数的输入和参数的创建,以及使用存储过程去实现功能的查询,和打印输出某些东西。
实验5《触发器与游标》
进一步熟悉SQL语句对数据库进行完整性控制的方法;
理解触发器的概念、定义方法和触发条件。
理解游标的定义、打开、使用、关闭与释放的方法。
针对实验4所建销售数据库:
1、设置一个触发器,该触发器仅允许“dbo”用户可以删除employee表内数据,否则出错。
2、针对employee表写一个DELETE触发器。
3、针对employee表写一个UPDATE触发器。
4、统计employee表中员工的平均薪水,输出低于平均薪水的员工的人数以及这些员工的姓名与薪水(使用游标)。
2.恢复实验4所建销售数据库
3.复习有关SQL语句对数据库进行完整性控制的方法;
复习触发器的概念、定义方法和触发条件。
复习游标的定义、打开、使用、关闭与释放的方法约束与存储过程的SQL语言命令。
1.设置一个触发器,该触发器仅允许“dbo”用户可以删除employee表内数据,否则出错。
2.针对employee表写一个DELETE触发器。
3.针对employee表写一个UPDATE触发器。
4.统计employee表中员工的平均薪水,输出低于平均薪水的员工的人数以及这些员工的姓名与薪水(使用游标)。
创建命令为:
createtriggerEmploteeDeleteonemployee
fordelete
ifexists(select*fromdeleted)--要删除的是有数据的
begin
ifuser!
='
dbo'
/*如果不是dbo用户*/
rollbacktransaction
end
创建命令:
createtriggerdelete_disploy
onemployee
fordelete/*默认after*/
as
/*操作*/
SELECT*fromemployee
BEGIN
PRINT'
已触发触发器了'
END
测试:
发现输出来的表已无E00001,说明删除成功
再看输出来的信息:
说明触发器成功执行
命令:
createtriggerUPDATE_DISPLOY
ONEMPLOYEE
FORUPDATE
AS
select*fromemployee
触发了更新操作的触发器'
更新前的数据为:
更新后:
消息处显示为:
原先备份过数据库,所以我先把数据后还原
还原后,employee表的数据为:
命令为:
DECLARECUR_EMPLOEE_NAME_SALARYSCROLLCURSORFOR--定义游标名为CUR_EMPLOEE_NAME_SALARY
SELECTEMPLOYEE.emp_no,EMPLOYEE.salary
FROMEMPLOYEEWHEREEMPLOYEE.salary<
(SELECTAVG(E
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 基本 操作 实验 报告