mysql数据库实验13

68 阅读 0 评论 45 点赞

我是靠谱客的博主勤奋夏天，最近开发中收集的这篇文章主要介绍mysql数据库实验13，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

本系列最后一篇文章已经更新完成！！！

2. 创建存储过程，实现的功能是：根据输入的员工姓名删除employees表中对应的员工信息。调用这个存储过程，输入参数为：‘李丽’，查看结果。

 delimiter $$
create procedure  delete_name(in a char(6))
begin
	delete from employees where name=a;
end $$
delimiter ;

call delete_name('李丽');

select * from employees;

3．创建存储过程，实现的功能是：比较两个输入的浮点参数，若前者大，输出’大于’，若前者小，输出’小于’，否则输出’等于’。调用这个存储过程，输入参数为10.2,22.8，查看结果。

 delimiter $$
create procedure compar(in k1 Float, in k2 Float, out k3 char(6) )
begin
	if k1>k2 then
		set k3= '大于';
	elseif k1=k2 then
		set k3= '等于';
	else 
		set k3= '小于';
	end if;
end$$
delimiter ;

call compar(10.2,22.8,@k);

select @k;
DROP PROCEDURE compar;

4．创建存储过程，实现的功能是：根据输入的员工姓名输出该员工所在的部门名称。调用这个存储过程，输入参数为：‘王林’，查看结果。

 delimiter $$
create procedure  xz(in xm char(6))
begin
select departmentname from departments 
where departmentid=(select departmentid from employees 
where name=xm);
end $$
delimiter ;

call xz('王林');

select @部门名称;

DROP PROCEDURE  xz;

5. 创建存储过程，实现的功能是：根据输入的员工姓名比较两个员工的实际收入，若前者大，输出’1’，若前者小，输出’-1’，否则输出’0’。调用这个存储过程，查看结果。

 delimiter $$
create procedure bj(in xm1 char(6),in xm2 char(6),out BJ int)
begin
declare sr1,sr2 float(8);
select inCome-outCome into sr1 from salary 
where employeeID=(select employeeid from employees where name =xm1);
select inCome-outCome into sr2 from salary 
where employeeID=(select employeeid from employees where name =xm2);
if xm1>xm2 then
set BJ=1;
elseif xm1<xm2 then
set BJ=-1;
else 
set BJ=0;
end if;
end $$
delimiter;

call bj('王林','刘明',@bjdx);

DROP PROCEDURE  bj;

6. 创建存储过程，要求当一个员工的工作时间大于6年时，将其转到经理办公室工作。调用这个存储过程，查看结果。

 delimiter $$
create procedure cg(in id char(6))
begin
declare year tinyint;
select workyear into year  from  employees where employeeid= id; 	
if year>6 then
update employees set departmentID=(select departmentID from departments
where departmentName='经理办公室') where employeeID=id;
end if;
end$$

delimiter ;

call cg('000001');

DROP PROCEDURE  cg;

7. 创建存储过程，使用游标方式计算employees表中行的数目。调用这个存储过程，查看结果。

delimiter $$
create procedure number(out pnumber int)
begin
declare a int;  
declare found bool default true;
declare c cursor for
select employeeid from employees;  
declare continue handler for not found
set found=false;  
set pnumber=0;
open c; 
fetch c into a; 
while found do
set pnumber=pnumber+1;
fetch c into a;
end while;  
close c;  
end$$

delimiter ;
call number(@pnumber);

select @pnumber;

select count(*) from employees;

DROP PROCEDURE  num;

8．创建存储过程，使用count(*)函数计算employees表中行的数目。调用这个存储过程，查看结果。

delimiter $$
create procedure  cx(out a1 int)
begin
declare a2 int;
set a2=(select count(*) from employees);
set a1=a2;
end $$
delimiter ;

call cx(@hangshu);

select @hangshu;

9. 创建存储过程，根据输入的部门名称，输出该部门收入最高的员工姓名。

 delimiter $$
create procedure max1(in a char (15),out  str char (10))
begin 
declare deid char (3) ; 
declare max2 double  ; 
declare id2 char (10);
set deid = (select departmentID  from departments where departmentName = a );
set max2= (select max(Income ) from employees a, salary b where  a.employeeID = b.employeeID and a.departmentID = deid );
Set  id2=(select employeeID from salary where Income = max2);
set str=(select name from employees where employeeID =id2);
end $$

delimiter ;

call max1('财务部',@max);

select @max;

DROP PROCEDURE  max1;

10. 创建存储过程，根据输入的整数，输出1到该整数的累加结果。

delimiter $$
create procedure plus(in k int(4),out sum1 int )
begin
declare i int;
set i=1;
set sum1=0;
while i<=k do
set sum1=sum1+i;
set i=i+1;
end while;
end $$

delimiter ;


call plus(5,@sum1);

select @sum1;

11. 创建一个存储函数，返回员工的平均实际收入。调用该存储函数。

 delimiter $$
create function num()
returns integer
begin
return (select count(*) from employees);
end $$

delimiter ;

select num();

drop function num;

12．创建一个存储函数，根据输入的员工编号，若该员工收入大于3000元，则在employees表和salary表删除该员工信息，返回’大于3000元已删除’，否则返回’不大于3000元保留’。调用该存储函数，查看结果。

 delimiter $$
create function delete1(id char (10))
returns char(15)
begin 
declare b  int ;
declare  str char (15);
set b = (select Income from salary where employeeID = id);
if b>3000  then 
set str = '大于3000元已删除';
delete from employees where employeeID =id;
delete from salary where employeeID=id;
else 
set str  ='不大于3000元保留';
Return str;
end if ;
end $$

delimiter ;

select delete1('000001');

drop function delete1;

** 13. 创建存储函数，根据输入的员工姓名，判断员工是否在研发部工作，若是则返回其学历，若不是则返回’不是研发部员工’。**

 delimiter $$
create function panduan(nm char (10))
returns char(15)
begin 
declare  b  char(15) ;
declare  str char (15);
set b=(select departmentname from departments where departmentid = (select departmentid from employees where name=nm));
if b='研发部'  then 
set str= (select education from employees where name=nm);
else 
set str='不是研发部员工';
end if ;
return str;
end $$

delimiter ;

select panduan('叶凡');

drop function panduan;

14. 创建一个存储函数，将工作时间满4年的员工收入增加500元。

 delimiter $$
create function sj()
begin 
update salary set income=income+500 where salary.employeeid=employees.employeeid and workyear>4;
end $$

delimiter ;

select sj();

drop function panduan;



delimiter $$
create function add3()
returns char(10)
begin declare done int default 0;
declare id char (8);
declare  nb  cursor for select employeeID from employees where workyear>=4;
declare  continue  handler  for  not  found
set done =1;
open nb ;
xh:loop
fetch nb into id;
If done =1 then leave xh;
End if ;
update salary set Income= Income +500 where employeeID= id;
End loop;
close nb ;
return '执行完成';
end$$
delimiter ;

select add3();

drop function add1;

15. 创建触发器，在employees表中删除员工信息的同时将salary表中该员工的信息删除，以确保数据完整性。创建完后删除employees表中的一行数据，然后查看salary表中的变化情况。

 delimiter $$
create trigger delete2 after delete
on employees for each row
delete from salary where employeeid=old.employeeid;
end$$
delimiter ;

delete from employees where employeeid='000001';

select * from salary;


drop trigger delete2;

16. 创建update_employees触发器，当departments表中的部门号发生变化时，employees表中员工所属的部门号也将改变。创建完后修改departments表中的一个部门号，然后查看employees表中的变化情况。

 delimiter $$
create trigger update_employees after update
on departments for each row
begin
update employees set departmentid=new.departmentid 
where departmentID= old.departmentID;
end$$
delimiter ;

select * from employees;

update departments set departmentid =8 where departmentname='研发部';

select * from employees;

drop trigger update_employees;

17．创建update_salary_outCome触发器，当salary表中的收入增加500元时，支出也增加500元。创建完后修改salary表中的收入，然后查看表的变化情况。

delimiter $$
create trigger update_salary_outCome before update 
on salary for each row 
begin
declare a int ;
set a=(new.Income-old.Income);
set new.Outcome =old.Outcome +a ;
end$$
delimiter ;

select * from salary;

update salary set income =income+500 where employeeid='010008';

select * from salary;