数据库实验报告4-5.doc

数据库与表的基本操作实验报告项目名称 数据库与表的基本操作 专业班级 软件工程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条记录。（1）/*员工人事表employee */emp_nochar(5)Not nullprimary key员工编号emp_namechar(10)员工姓名sexchar(1)性别deptchar(4)所属部门titlechar(6)职称date_hireddatetime到职日birthdaydatetime生日salaryint薪水addrchar(50)null住址（2）/*客户表customer */cust_idchar(5)Not nullprimary key客户号cust_namechar(20)客户名称addrchar(40)客户住址tel_nochar(10)客户电话zipchar(6)邮政编码（3）/*销售主表sales */order_nointNot nullprimary key订单编号cust_idchar(5)客户号sale_idchar(5)业务员编号tot_amtnumeric(9,2)订单金额order_datedatetime订货日期ship_datedatetime出货日期invoice_nochar(10) 发票号码（4）/*销货明细表sale_item */order_nointNot null,primary key订单编号prod_idchar(5)Not null,primary key产品编号qtyint 销售数量unit_pricenumeric(7,2) 单价order_datedatetimenull订单日期（5）/*产品名称表product */pro_idchar(5)Not nullprimary key产品编号prod_namechar(20)Not null产品名称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_nochar(5)Not nullprimary key员工编号emp_namechar(10)员工姓名sexchar(1)性别deptchar(4)所属部门titlechar(6)职称date_hireddatetime到职日birthdaydatetime生日salaryint薪水addrchar(50)null住址（2）/*客户表customer */cust_idchar(5)Not nullprimary key客户号cust_namechar(20)客户名称addrchar(40)客户住址tel_nochar(10)客户电话zipchar(6)邮政编码（3）/*销售主表sales */order_nointNot nullprimary key订单编号cust_idchar(5)客户号sale_idchar(5)业务员编号tot_amtnumeric(9,2)订单金额order_datedatetime订货日期ship_datedatetime出货日期invoice_nochar(10) 发票号码（4）/*销货明细表sale_item */order_nointNot null,primary key订单编号prod_idchar(5)Not null,primary key产品编号qtyint 销售数量unit_pricenumeric(7,2) 单价order_datedatetimenull订单日期（5）/*产品名称表product */pro_idchar(5)Not nullprimary key产品编号prod_namechar(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, 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_nochar(10), zipchar(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_datedatetime, ship_datedatetime, invoice_nochar(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_datedatetime not null, primary key (order_no,prod_id)CREATE TABLE product(pro_idchar(5) NOT NULL, prod_namechar(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_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,dept,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,customer,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 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 = 职员 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、模糊查询create 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 saleswhere sale_id=E_no godeclare tot_amt intexec sp_saletot E0014, tot_amt outputselect tot_amt六、实验结论存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。sql中的存储过程：CREATE PROCEDURE 拥有者.存储过程名;程序编号(参数#1,参数#1024)WITHRECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTIONFOR REPLICATIONAS 程序行其中存储过程名不能超过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参数名 类型,参数名 类型asbegin.end以上格式还可以简写成：create proc sp_name参数名 类型,参数名 类型asbegin.end/*注：“sp_name”为需要创建的存储过程的名字，该名字不可以以阿拉伯数字开头*/调用存储过程1.基本语法：exec sp_name 参数名删除存储过程1.基本语法：drop procedure sp_name2.注意事项(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 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，说明删除成功再看输出来的信息：说明触发器成功执行3. 针对employee表写一个UPDATE触发器。命令:create trigger UPDATE_DISPLOYON EMPLOYEEFOR UPDATEAS /*操作*/select * from employeeBEGINPRINT 触发了更新操作的触发器END结果：更新前的数据为：更新后：消息处显示为：4. 统计employee表中员工的平均薪水，输出低于平均薪水的员工的人数以及这些员工的姓名与薪水（使用游标）。原先备份过数据库，所以我先把数据后还原还原后，employee表的数据为:命令为：DECLARE CUR_EMPLOEE_NAME_SALARY SCROLL CURSOR FOR -定义游标名为 CUR_EMPLOEE_NAME_SALARY SELECT EMPLOYEE.emp_no,EMPLOYEE.salaryFROM 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 INTset COUNT = 0SET 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)BEGINSELECT emp_no , salary fetch next from CUR_EMPLOEE_NAME_SALARY into emp_no , salaryset COUNT = COUNT+1END-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 . ON dbo /dbo代表该表的所有者FOR EACH ROWBEGIN-do somethingEND |七、实验小结学会了触发器的使用，创建和对于更新时候的做的操作的设定和对于触发器然后显示出已触犯的效果，进行了学习。学会了创建和声明游标，学会使用游标去读取一行行数据，学会了如何把varchar类型用函数转换成char型，使得整型变量可以打印出来。受益匪浅。附录：实验示例1、写一个允许用户一次