1、数据库与表的基本操作实验报告项目名称 数据库与表的基本操作 专业班级 软件工程 1307 班 学 号 3901130721 姓 名 谭淇蔚 实验成绩：批阅教师：2015 年 5 月 11 日实验 4存储过程实验学时： 4 每组人数： 1 实验类型： 2 （1：基础性 2：综合性 3：设计性 4：研究性）实验要求： 1 （1：必修 2：选修 3：其它）实验类别： 3 （1：基础 2：专业基础 3：专业 4：其它）一、实验目的理解存储过程的概念、建立和调用方法。进一步熟悉 SQL 语句对数据库进行完整性控制的方法。二、实验内容1、建立一个新的销售数据库，包含五张表，每张表至少需要 10 条记录。（
2、1）/*员工人事表employee */emp_no char(5) Not null primary key 员工编号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) Not null primary key 客户号cust_name char(20) 客户名称addr char(40
3、) 客户住址tel_no char(10) 客户电话zip char(6) 邮政编码（3）/*销售主表sales */order_no int Not null primary key 订单编号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 Not null, primary key订单编号prod_id
4、 char(5) Not null, primary 产品编号keyqty int 销售数量unit_price numeric(7,2) 单价order_date datetime null 订单日期（5）/*产品名称表product */pro_id char(5) Not null primary key 产品编号prod_name char(20) Not null 产品名称2、建立表的同时创建表的约束。（1） 为每张表建立主键约束。（2） 通过拖放操作加入外键。（3） 在表employee 加入 CHECK约束：输入的员工编号必须以E 开头的5位数编号，性别只能为M/F。（4） 为销
5、售主表sales中的发票编号字段建立UNIQUE约束。3、利用存储过程，给 employee 表添加一条业务部门员工的信息。4、利用存储过程从 employee、sales 、customer 表的连接中返回所有业务员的姓名、客户姓名、销售金额。5、利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。6、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。7、利用存储过程计算出订单编号为 10003 的订单的销售金额。三、实验要求：1熟悉 SQL SERVER 工作环境；2建立销售数据库3复习有关约束与存储过程的 SQL 语言命令。 4备份数据库，作为实验 5
6、 的操作数据库。四、实验步骤1创建销售数据库，并建表、修改，要求将自己的信息包含其中；2、利用存储过程，给 employee 表添加一条业务部门员工的信息。3、利用存储过程从 employee、sales 、customer 表的连接中返回所有业务员的姓名、客户姓名、销售金额。4、利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。5、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金。五、实验结果1、建立一个新的销售数据库，包含五张表，每张表至少需要 10 条记录。（1）/*员工人事表employee */emp_no char(5) Not null pri
7、mary key 员工编号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) Not null primary key 客户号cust_name char(20) 客户名称addr char(40) 客户住址tel_no char(10) 客户电话zip char(6) 邮政编码（3）/*
8、销售主表sales */order_no int Not null primary key 订单编号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 Not null, primary key订单编号prod_id char(5) Not null, primary key产品编号qty int 销售数量u
9、nit_price numeric(7,2) 单价order_date datetime null 订单日期（5）/*产品名称表product */pro_id char(5) Not null primary key 产品编号prod_name char(20) Not null 产品名称解：首先写出每一个表的创建语句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
10、,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 d
11、atetime,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 (pr
12、o_id)以上建表结果：为每一个表输入数据：2、建立表的同时创建表的约束。（1） 为每张表建立主键约束。已建立主键约束（2） 通过拖放操作加入外键。步骤如下：首先点击数据库，可以看见，数据库下方有数据库关系表：右键其，创建一个数据库关系图接着可以看见选择添加以上五个数据库，拖动键后便可以设立建立（1） 在表employee 加入 CHECK约束：输入的员工编号必须以E 开头的5位数编号，性别只能为M/F。步骤：第一步右键第二步：又按着鼠标右键：第三步：点击添加接着再表达式里面写着约束条件（2） 为销售主表sales中的发票编号字段建立UNIQUE约束。alter table sales add
13、 constraint order_nounique 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)asinsert into employee values(emp_no,emp_name,sex,dep
14、t,title,date_hired,birthday,salary,addr)goexec proAddEmployee E0022,罗刚, M,业务,经理,2009-07-08,1988-02-03,13000,都匀市执行后：刷新表格后，查看表格，可以发现，表中写入了信息结果为：4、利用存储过程从 employee、sales 、customer 表的连接中返回所有业务员的姓名、客户姓名、销售金额。create procedure findasselect employee.emp_name,customer.cust_name,sales.tot_amtfrom employee,cus
15、tomer,saleswhere sales.sale_id = employee.emp_no and sales.cust_id = customer.cust_idgoexec find执行后结果为：刷新后，旁边的存储过程的显示为：5、利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。create procedure findasselect employee.emp_name,customer.cust_name,sales.tot_amtfrom employee,customer,saleswhere sales.sale_id = employee.emp_no and
16、sales.cust_id = customer.cust_idgoexec find执行结果：刷新后，可看见：此结果采用的数据为：Employee 表：Sales 表：6、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。create procedure findLiemp_name varchar(10)asselect employee.emp_no,sales.order_no,sales.tot_amtfrom employee,saleswhere employee.emp_no = sales.sale_id and employee.title =
17、 职员 and (employee.emp_name like emp_name)goexec findLi 李%结果：刷新后，在旁边可看见：结果的数据中所查询的表的内容为：Employee 表：Sales 表：7、利用存储过程计算出订单编号为 10003 的订单的销售金额。CREATE PROCEDURE PRO_ORDERorder_no varchar(6)asselect sales.tot_amtfrom saleswhere sales.order_no = order_nogoexec PRO_ORDER 10003实验结果：刷新后发现左边更新：附录：实验示例1、模糊查询crea
18、te procedure sp_empname E_name varchar(10) asselect a.emp_name,a.dept,b.tot_amtfrom employee a inner join sales bon a.emp_no=b.sale_id where a.emp_name like E_namegoexec sp_empname 陈%2、 利用存储过程计算出E0014业务员的销售总金额。create procedure sp_saletot E_no char(5),p_tot int output asselect p_tot=sum(tot_amt)from
19、saleswhere sale_id=E_no godeclare tot_amt intexec sp_saletot E0014, tot_amt outputselect tot_amt六、实验结论存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。sql 中的存储过程：CREATE PROCEDURE 拥有者.存储过程名;程
20、序编号(参数#1,参数#1024)WITHRECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTIONFOR REPLICATIONAS 程序行其中存储过程名不能超过 128 个字。每个存储过程中最多设定 1024个参数(SQL Server 7.0 以上版本),参数的使用方法如下：参数名数据类型VARYING =内定值 OUTPUT每个参数名前要有一个“”符号,每一个存储过程的参数仅为该程序内部使用,参数的类型除了 IMAGE 外，其他 SQL Server 所支持的数据类型都可使用。内定值 相当于我们在建立数据库时设定一个字段的默认值，这里是为这个参数设定默
21、认值。OUTPUT是用来指定该参数是既有输入又有输出值的，也就是在调用了这个存储过程时，如果所指定的参数值是我们需要输入的参数，同时也需要在结果中输出的，则该项必须为 OUTPUT，而如果只是做输出参数用，可以用 CURSOR，同时在使用该参数时，必须指定VARYING 和 OUTPUT 这两个语句。分类：1 系统存储过程以 sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。2 本地存储过程用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。3 临时存储过程分为两种存储过程：一是本地临时存储过程，以井字号(#)作为其名称的第一个字符
22、，则该存储过程将成为一个存放在 tempdb 数据库中的本地临时存储过程，且只有创建它的用户才能执行它;二是全局临时存储过程，以两个井字号(#)号开始，则该存储过程将成为一个存储在 tempdb 数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。4 远程存储过程在 SQL Server2005 中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和 EXECUTE命令执行一个远程存储过程。5 扩展存储过程扩展存储过程(Extended Stored Proced
23、ures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以 xp_开头。格式：创建存储过程create procedure sp_name参数名 类型,参数名 类型asbegin.end以上格式还可以简写成：create proc sp_name参数名 类型,参数名 类型asbegin.end/*注：“sp_name”为需要创建的存储过程的名字，该名字不可以以阿拉伯数字开头*/调用存储过程1.基本语法：exec sp_name 参数名删除存储过程1.基本语法：drop procedure sp_name2.注意事项(1)不能在一个存储过程中删除另一个存储过程，只能调用另一
24、个存储过程其他常用命令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：研究
25、性）实验要求： 1 （1：必修 2：选修 3：其它）实验类别： 3 （1：基础 2：专业基础 3：专业 4：其它）一、实验目的进一步熟悉 SQL 语句对数据库进行完整性控制的方法；理解触发器的概念、定义方法和触发条件。理解游标的定义、打开、使用、关闭与释放的方法。二、实验内容针对实验 4 所建销售数据库：1、 设置一个触发器，该触发器仅允许“dbo”用户可以删除 employee 表内数据，否则出错。2、 针对 employee 表写一个 DELETE 触发器。3、 针对 employee 表写一个 UPDATE 触发器。4、 统计 employee 表中员工的平均薪水，输出低于平均薪水的员工
26、的人数以及这些员工的姓名与薪水（使用游标） 。三、实验要求：1熟悉 SQL SERVER 工作环境；2恢复实验 4 所建销售数据库3复习有关 SQL 语句对数据库进行完整性控制的方法；复习触发器的概念、定义方法和触发条件。复习游标的定义、打开、使用、关闭与释放的方法约束与存储过程的 SQL 语言命令。 四、实验步骤1.设置一个触发器，该触发器仅允许“dbo”用户可以删除 employee 表内数据，否则出错。2.针对 employee 表写一个 DELETE 触发器。3.针对 employee 表写一个 UPDATE 触发器。4.统计 employee 表中员工的平均薪水，输出低于平均薪水的员
27、工的人数以及这些员工的姓名与薪水（使用游标） 。五、实验结果1. 设置一个触发器，该触发器仅允许“dbo”用户可以删除 employee 表内数据，否则出错。创建命令为：create trigger EmploteeDelete on employeefor delete asif exists (select * from deleted) -要删除的是有数据的beginif user != dbo/*如果不是 dbo 用户*/rollback transactionend结果：2. 针对 employee 表写一个 DELETE 触发器。创建命令：create trigger delete_disployon employeefor delete/*默认 after*/as /*操作*/SELECT * from employeeBEGINPRINT 已触发触发器了END结果：测试：发现输出来的表已无 E00001，说明删除成功再看输出来的信息：
