数据库与表的基本操作实验报告

《数据库与表的基本操作》 实验报告 项目名称 数据库与表的基本操作 实验成绩： 批阅教师： 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 emp_name sex dept title date_hired birthday salary .

char(5) char(10) char(1) char(4) char(6) datetime datetime int Not null primary key 员工编号 员工姓名 性别 所属部门 职称 到职日 生日 薪水 .

addr

char(50) null 住址 （2）/*客户表customer */ cust_id cust_name addr tel_no zip

（3）/*销售主表sales */ order_no cust_id sale_id tot_amt order_date ship_date invoice_no

（4）/*销货明细表sale_item */ order_no prod_id qty .

char(5) char(20) char(40) char(10) char(6) Not null primary key 客户号 客户名称 客户住址 客户电话 邮政编码 int char(5) char(5) numeric(9,2) datetime datetime char(10) Not null primary key 订单编号 客户号 业务员编号 订单金额 订货日期 出货日期 发票号码 int char(5) int Not null, Not null, primary key primary key 订单编号 产品编号 销售数量 .

unit_price order_date

numeric(7,2) datetime null 单价 订单日期 （5）/*产品名称表product */ pro_id prod_name char(5) char(20) Not null Not null primary key 产品编号 产品名称 2、建立表的同时创建表的约束。

（1） 为每张表建立主键约束。 （2） 通过拖放操作加入外键。

（3） 在表employee加入CHECK约束：输入的员工编号必须以E开头的

5位数编号，性别只能为M/F。

（4） 为销售主表sales中的发票编号字段建立UNIQUE约束。

3、利用存储过程，给employee表添加一条业务部门员工的信息。

4、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。

5、利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。 6、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。

7、利用存储过程计算出订单编号为10003的订单的销售金额。

三、实验要求：

1．熟悉SQL SERVER 工作环境； 2．建立销售数据库

3．复习有关约束与存储过程的SQL语言命令。 4．备份数据库，作为实验5 的操作数据库。

.

.

四、实验步骤

1．创建销售数据库，并建表、修改，要求将自己的信息包含其中； 2、利用存储过程，给employee表添加一条业务部门员工的信息。

3、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。

4、利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。 5、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金。

五、实验结果

1、建立一个新的销售数据库，包含五张表，每张表至少需要10条记录。 （1）/*员工人事表employee */ emp_no emp_name sex dept title date_hired birthday salary addr

（2）/*客户表customer */ cust_id char(5) Not null primary key 客户号 char(5) char(10) char(1) char(4) char(6) datetime datetime int char(50) Not null null primary key 员工编号 员工姓名 性别 所属部门 职称 到职日 生日 薪水 住址 .

.

cust_name addr tel_no zip

char(20) char(40) char(10) char(6) 客户名称 客户住址 客户电话 邮政编码 （3）/*销售主表sales */ order_no cust_id sale_id tot_amt order_date ship_date invoice_no

（4）/*销货明细表sale_item */ order_no prod_id qty unit_price order_date

（5）/*产品名称表product */

.

int char(5) char(5) numeric(9,2) datetime datetime char(10) Not null primary key 订单编号 客户号 业务员编号 订单金额 订货日期 出货日期 发票号码 int char(5) int numeric(7,2) datetime Not null, Not null, null primary key primary key 订单编号 产品编号 销售数量 单价 订单日期 .

pro_id prod_name char(5) char(20) Not null Not null primary key 产品编号 产品名称

解：

首先写出每一个表的创建语句 CREATE TABLE employee (emp_no char(5)not null, emp_name char(10), sex char(1),

dept char(4), title char(6),

date_hired datetime, birthday datetime, salary int,

addr CHAR (50),

primary key (emp_no) )

CREATE TABLE customer (cust_id char(5)not null, cust_name char(20), addr char(40), tel_no char(10), zip char(6),

primary key (cust_id) )

CREATE TABLE sales (order_no int not null, cust_id char(5), sale_id char(5),

tot_amt numeric(9,2), order_date datetime, ship_date datetime, invoice_no char(10), primary key (order_no) )

CREATE TABLE sale_item (order_no int not null,

.

.

prod_id char(5) not null,

qty int,

unit_price numeric(7,2),

order_date datetime not null, primary key (order_no,prod_id) )

CREATE TABLE product (pro_id char(5) NOT NULL,

prod_name char(20) NOT NULL, primary key (pro_id) )

以上建表结果：

为每一个表输入数据：

2、建立表的同时创建表的约束。

（1） 为每张表建立主键约束。

已建立主键约束 （2） 通过拖放操作加入外键。

.

.

.

步骤如下：

首先点击数据库，可以看见，数据库下方有数据库关系表：

右键其，创建一个数据库关系图

接着可以看见

.

.

选择添加以上五个数据库，拖动键后便可以·设立建立

.

（1） 在表employee加入CHECK约束：输入的员工编号必须以E开头的

5位数编号，性别只能为M/F。

步骤：

第一步右键

第二步： 又按着鼠标右键：

.

.

第三步： 点击添加

.

.

接着再表达式里面写着约束条件

（2） 为销售主表sales中的发票编号字段建立UNIQUE约束。

alter table sales add constraint order_no——unique unique (order_no);

3、利用存储过程，给employee表添加一条业务部门员工的信息。 create procedure proAddEmployee

.

.

(@emp_no char(5), @emp_name char(10), @sex char(1), @dept char(10), @title char(6),

@date_hired datetime, @birthday datetime, @salary int,

@addr char(50)) as

insert into employee values

(@emp_no,@emp_name,@sex,@dept,@title,@date_hired,@birthday,@salary,@addr)

go

exec proAddEmployee 'E0022','罗刚','M','业务','经理','2009-07-08','1988-02-03',13000,'都匀市'

执行后：

刷新表格后，查看表格，可以发现，表中写入了信息 结果为：

4、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。

create procedure find

.

.

as

select employee.emp_name,customer.cust_name,sales.tot_amt from employee,customer,sales

where sales.sale_id = employee.emp_no and sales.cust_id customer.cust_id

go

exec find

执行后结果为：

=

刷新后，旁边的存储过程的显示为：

.

.

5、利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。

create procedure find as

select employee.emp_name,customer.cust_name,sales.tot_amt from employee,customer,sales

where sales.sale_id = employee.emp_no and sales.cust_id customer.cust_id

go

exec find

执行结果：

.

=

.

刷新后，可看见：

此结果采用的数据为：

.

.

Employee表：

Sales表：

6、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。

create procedure findLi @emp_name varchar(10) as

select employee.emp_no,sales.order_no,sales.tot_amt from employee,sales

where employee.emp_no = sales.sale_id and employee.title = '职员' and (employee.emp_name like @emp_name)

go

.

.

exec findLi '李%' 结果：

刷新后，在旁边可看见：

结果的数据中所查询的表的内容为：

.

.

Employee表：

Sales表：

7、利用存储过程计算出订单编号为10003的订单的销售金额。

CREATE PROCEDURE PRO_ORDER @order_no varchar(6) as

select sales.tot_amt from sales

where sales.order_no = @order_no go

exec PRO_ORDER '10003'

.

.

实验结果：

刷新后发现左边更新：

.

.

附录：实验示例 1、模糊查询

create procedure sp_empname @E_name varchar(10) as select a.emp_name,a.dept,b.tot_amt from employee a inner join sales b on a.emp_no=b.sale_id

where a.emp_name like @E_name go

exec sp_empname '陈%'

2、 利用存储过程计算出’E0014’业务员的销售总金额。 create procedure sp_saletot @E_no char(5),@p_tot int output as select @p_tot=sum(tot_amt) from sales

where sale_id=@E_no go

declare @tot_amt int

exec sp_saletot E0014, @tot_amt output select @tot_amt

.

.

六、实验结论

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

sql中的存储过程：

CREATE PROCEDURE [拥有者.]存储过程名[;程序编号] [(参数#1,…参数#1024)]

[WITH{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}] [FOR REPLICATION] AS 程序行

其中存储过程名不能超过128个字。每个存储过程中最多设定1024个参数

(SQL Server 7.0以上版本),参数的使用方法如下： @参数名数据类型[VARYING] [=内定值] [OUTPUT]

每个参数名前要有一个“@”符号,每一个存储过程的参数仅为该程序内部使用,参数的类型除了IMAGE外，其他SQL Server所支持的数据类型都可使用。

[内定值]相当于我们在建立数据库时设定一个字段的默认值，这里是为这个参数设定默认值。[OUTPUT]是用来指定该参数是既有输入又有输出值的，也就是在调用了这个存储过程时，如果所指定的参数值是我们需要输入的参

.

.

数，同时也需要在结果中输出的，则该项必须为OUTPUT，而如果只是做输出参数用，可以用CURSOR，同时在使用该参数时，必须指定VARYING和OUTPUT这两个语句。

分类： 1系统存储过程

以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。 2本地存储过程

用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。

3临时存储过程 分为两种存储过程：

一是本地临时存储过程，以井字号(#)作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;

二是全局临时存储过程，以两个井字号(##)号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。

4远程存储过程

在SQL Server2005中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。

.

.

5扩展存储过程

扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。 格式：

创建存储过程

create procedure sp_name @[参数名] [类型],@[参数名] [类型] as begin ......... end

以上格式还可以简写成： create proc sp_name

@[参数名] [类型],@[参数名] [类型] as begin ......... end

/*注：“sp_name”为需要创建的存储过程的名字，该名字不可以以阿拉伯数字开头*/

调用存储过程

.

.

1.基本语法：exec sp_name [参数名] 删除存储过程

1.基本语法：drop procedure sp_name 2.注意事项

(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

其他常用命令

1.show procedure status

显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

2.show create procedure sp_name 显示某一个mysql存储过程的详细信息 3、exec sp_helptext sp_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．熟悉SQL SERVER 工作环境；

.

.

2．恢复实验4所建销售数据库

3．复习有关SQL语句对数据库进行完整性控制的方法；复习触发器的概念、定义方法和触发条件。复习游标的定义、打开、使用、关闭与释放的方法约束与存储过程的SQL语言命令。

四、实验步骤

1.设置一个触发器，该触发器仅允许“dbo”用户可以删除employee表内数据，否则出错。

2.针对employee表写一个DELETE触发器。 3.针对employee表写一个UPDATE触发器。

4.统计employee表中员工的平均薪水，输出低于平均薪水的员工的人数以及这些员工的姓名与薪水（使用游标）。

五、实验结果

1. 设置一个触发器，该触发器仅允许“dbo”用户可以删除employee表内数

据，否则出错。

创建命令为：

create trigger EmploteeDelete on employee for delete as

if exists (select * from deleted) --要删除的是有数据的 begin

if user != 'dbo'/*如果不是dbo用户*/ rollback transaction end

.

.

结果：

2. 针对employee表写一个DELETE触发器。

创建命令：

create trigger delete_disploy

on employee

for delete/*默认after*/ as

/*操作*/

SELECT * from employee BEGIN

PRINT '已触发触发器了' END 结果：

.

.

测试：

发现输出来的表已无E00001，说明删除成功 再看输出来的信息：

.

.

说明触发器成功执行

3. 针对employee表写一个UPDATE触发器。

命令:

create trigger UPDATE_DISPLOY ON EMPLOYEE FOR UPDATE AS /*操作*/

select * from employee BEGIN

PRINT '触发了更新操作的触发器' END 结果：

.

.

更新前的数据为：

更新后：

消息处显示为：

.

.

4. 统计employee表中员工的平均薪水，输出低于平均薪水的员工的人数

以及这些员工的姓名与薪水（使用游标）。 原先备份过数据库，所以我先把数据后还原

还原后，employee表的数据为:

命令为：

DECLARE CUR_EMPLOEE_NAME_SALARY SCROLL CURSOR FOR --定义游标名为 CUR_EMPLOEE_NAME_SALARY

SELECT EMPLOYEE.emp_no,EMPLOYEE.salary

FROM EMPLOYEE WHERE EMPLOYEE.salary < (SELECT AVG(EMPLOYEE.salary) FROM EMPLOYEE)

OPEN CUR_EMPLOEE_NAME_SALARY --打开游标

declare @emp_no CHAR(5) , @salary INT --定义选取出来的局部变量 DECLARE @COUNT INT,@AVG_SALARY INT

.

.

set @COUNT = 0

SET @AVG_SALARY = (SELECT AVG(EMPLOYEE.salary) FROM EMPLOYEE) PRINT '平均工资为：'+convert(char(6),@AVG_SALARY)--打印平均工资 fetch next from CUR_EMPLOEE_NAME_SALARY into @emp_no , @salary --读数据到局部变量

WHILE(@@FETCH_STATUS = 0) BEGIN

SELECT @emp_no , @salary

fetch next from CUR_EMPLOEE_NAME_SALARY into @emp_no , @salary set @COUNT = @COUNT+1

END

--select @COUNT --print str(@COUNT)

print '总共'+convert(char(2),@COUNT)+'人' CLOSE CUR_EMPLOEE_NAME_SALARY --关闭游标 DEALLOCATE CUR_EMPLOEE_NAME_SALARY --释放游标 结果：

.

.

.

.

六、实验结论

触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行

语法： DELIMITER |

CREATE TRIGGER ``.``

.

.

< [ BEFORE | AFTER ] > < [ INSERT | UPDATE | DELETE ] > ON [dbo] //dbo代表该表的所有者 FOR EACH ROW BEGIN

--do something END |

七、实验小结

学会了触发器的使用，创建和对于更新时候的做的操作的设定和对于触发器然后显示出已触犯的效果，进行了学习。

学会了创建和声明游标，学会使用游标去读取一行行数据，学会了如何把varchar类型用函数转换成char型，使得整型变量可以打印出来。

受益匪浅。

.

.

附录：实验示例

1、写一个允许用户一次只删除一条记录的触发器。

有员工人事表employee emp_no emp_name sex dept title date_hired birthday salary addr order_no prod_id qty unit_price order_date char(5) char(10) char(1) char(4) char(6) datetime datetime int char(50) int char(5) int numeric(7,2) datetime Not null Not null Not null Not null Not null Not null Null Not null null Not null, Not null, Not null Not null null primary key primary key primary key 员工编号 员工姓名 性别 所属部门 职称 到职日 生日 薪水 住址 订单编号 产品编号 销售数量 单价 订单日期 销货明细表sale_item create trigger tr_emp

on employee for delete as declare @row_cnt int

select @row_cnt=count(*) from deleted if @row_cnt>1 begin

print '此删除操作可能会删除多条人事表数据' rollback transaction end

delete from employee

where sex='女' /*结果：所影响的行数为0*/

2、显示女职员的工号，姓名

(1)声明一个游标

语法：DECLARE <游标名> CURSOR FOR ； (2)打开该游标；

语法：open <游标名> (3)将该游标的内容提取到变量中；

Fetch <游标名> Into <主变量>

declare cur_emp_gender scroll cursor for—定义游标 select emp_no ，emp_name

.

.

from employee where sex='F' order by emp_no

open cur_emp_gender—打开游标

declare @no,@name

fetch next from cur_emp_gender into @no,@name—读数据到局部变量

while(@@FETCH_STATUS=0) begin

select @no,@name

fetch next from cur_emp_gender into @no,@namer end

close cur_emp_gender—关闭

deallocate cur_emp_gender—释放

.