数据库实验4

1、流程控制
（1）“学生表”中若存在学号为“0538”的学生，则显示已存在的信息，否则插入该学生的记录。然后从student表中删除学号为“0538”的学生记录，重新执行该程序，观察与上次有何不同。

①：输入如下语句，然后执行，用于创建存储过程
注：
这里开头的delimiter //的作用是将分隔符变成//。因为默认情况下，分隔符是分号。那么，一般就是一条命令以分号结束，然后执行，MySQL遇到该分号去执行该条命令。而在此处定义存储过程时，要输入的语句比较多，且语句中包含有分号，这就意味着，如果不把分隔符改变为其它符号的话，那么MySQL一遇到分号就要自动执行了，这样就会报错，存储过程就无法创建完成。倒数第二行里的//就是上面改过之后的分隔符，这样一来，MySQL在这里直到遇到//时才会去执行这段命令。然后最后一行delimiter ;是将分隔符再变回分号，因为后面还要执行其它语句，肯定还是要变回分号为分隔符更方便的。本实验中的所有类似这里改变分隔符的写法和原因都同这里一样，后面就不再赘述了。

delimiter //
create procedure test1()
begin
   if exists (select * from student where 学号 = '0538') then
	  select * from student where 学号 = '0538';
   else
	  insert into student(学号,姓名,性别,专业班级,出生日期,联系电话) values('0538','于兰兰','女','生物05','1984-2-20','1331200xxxx');
   end if;
end; //
delimiter ;

注：
我所指的执行都是点击workbench上面的第二个形似闪电的标记，即【Execute the statement under the keyboard cursor】，本文中后面所指的执行也是点击这个，后面就不再赘述了。

②：输入如下语句，然后执行，用于调用该存储过程，显示已存在的信息。

call test1();

③：从student表中删除学号为“0538”的学生记录。
此时，为了防止报Cannot delete or update a parent row: a foreign key constraint fails的错误（报该错误的原因：student表中的学号是sc表的外键，在进行删除 *** 作时会自动检查外键约束），先输入如下语句，然后执行，用来关闭外键约束检查

set foreign_key_checks = 0;

然后，输入如下语句，执行，用于删除该条学生记录，在学生表中，该条学生记录确实已被删除

delete from student where 学号 = '0538';

然后，输入如下语句，执行，用于打开外键约束检查，保持表结构的完整性

set foreign_key_checks = 1;

④：输入如下语句，然后执行，此步骤为重新执行该程序。执行完后，观察到该学生的记录已被插入

call test1();

（2）将“课程表”中所有课程的学时数都加16（提示：使用流程控制语句while）
①：为了体现出 *** 作执行前后学时数的变化，先打开课程表，发现原来课程表中的学时数如下

②：输入如下语句，然后执行，用于创建该存储过程

delimiter //
create procedure test2()
begin
   declare i int default 1;
   while i > 0 do
	  update course set 学时数 = 学时数 + 16;
      set i = i - 1;
   end while;
end; //
delimiter ;

③：输入如下语句，然后执行，用于调用该存储过程，执行完毕后发现课程表中的学时数相较原来都加了16

call test2();

（3）计算“学生作业表”中每位同学的各门课程三项作业的平均成绩，输出课程号、学号和平均成绩（若某项作业的成绩未给出，按0计算）。
①：（此处考虑到sc表里只有一个人的作业2成绩是没有的，于是偷了个懒，直接就在sc表里把那个空着的成绩先改成0）
进入sc表，将最后一行中值为NULL的作业2成绩修改为0，然后点击两次Apply，点击Finish。

②：输入如下语句，然后执行，用于创建该存储过程

delimiter //
create procedure score()
begin
   select 课程号, 学号, ( (作业1成绩 + 作业2成绩 + 作业3成绩) / 3 ) as 三项作业的平均成绩 
   from sc
   group by 学号, 课程号;
end; //
delimiter ;

③：输入如下语句，然后执行，用于调用该存储过程，输出了相应的课程号、学号和平均成绩

call score();

2、函数
（1）定义一个名为Sage_func的函数，按出生年月计算年龄。然后从“学生表”中检索出含有年龄的学生信息。
①：输入如下语句，然后执行，用于创建该函数
注1：
这里如果没写第三行deterministic，则会报Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS的错误，因为create procedure, create function, alter procedure,alter function,call, drop procedure, drop function等语句都会被写进二进制日志,然后在从服务器上执行。但是，一个执行更新的不确定子程序(存储过程、函数、触发器)是不可重复的，在从服务器上执行(相对于主服务器是重复执行)可能会造成恢复的数据与原始数据不同，从服务器不同于主服务器的情况。因此，MySQL强制要求：在主服务器上，除非子程序被声明为确定性的或者不更改数据的，否则创建或者替换子程序将被拒绝。这就意味着，当创建一个子程序的时候，必须要么声明它是确定性的，要么声明它是不改变数据的。因此，共有两种声明方法：
第一种:声明是否是确定性的。deterministic和not deterministic指出一个子程序是否对给定的输入总是产生同样的结果。如果没有明确指定，则默认是not deterministic，所以，必须明确指定deterministic来声明一个子程序是确定性的。
第二种:声明是否会改变数据。contains sql, no sql, reads sql data, modifies sql用来指出子程序是读还是写数据的。无论no sql还是reads sql data都指出，子程序没有改变数据，但是必须明确地指定其中一个，因为如果没有明确指定，则其默认的指定是contains sql。在默认情况下，如果允许create procedure或create function语句被接受，就必须明确地指定deterministic或no sql与reads sql data中的一个，否则就会产生1418错误。
因而，解决办法自然也有两种，此处我选择的是第一种方法，在创建子程序(存储过程、函数、触发器)时，声明为deterministic或no sql与reads sql data中的一个，我是选择的声明deterministic。另一种方法就不在此处介绍了。
注2：
在本题中，curdate()函数用于返回当前的日期，如“2022-4-29”，year()函数用于返回日期的年份，如“2022-4-29”里的2022，month()函数用于返回日期的月份，如“2022-4-29”里的4，day()函数用于返回具体的日，如“2022-4-29”的29

delimiter //
create function Sage_func(birthday varchar(15)) returns int
deterministic
begin
	declare age int;
	set age = year(curdate()) - year(birthday) - 1;
	if month(curdate()) = month(birthday) then
	   if day(curdate()) >= day(birthday) then 
		  set age = age + 1;
	   end if;
	elseif month(curdate()) > month(birthday) then
		  set age = age + 1;
	end if;
    return age; 
end //
delimiter ;

②：输入如下语句，然后执行，检索成功

select 姓名, Sage_func(出生日期) as 年龄 from student;

（2）定义一个名为Isprime_func的函数，用于判断一个正整数是否为素数，如果是，则返回TRUE，否则返回FALSE。
①：输入如下语句，然后执行，用于创建该函数
注：
此处写第三行的原因同上一题，不再赘述。

delimiter //
create function Isprime_func(n int) returns varchar(10)
deterministic
begin
     declare i int;
     set i = 2;
	 if n = 1 then return 'FALSE';
	 end if;
     while i <= sqrt(n) do
		   if n % i = 0 then return 'FALSE';
		   end if;
           set i = i + 1;
     end while;
     return 'TRUE';
end //
delimiter ;

②：输入如下语句，然后执行，返回TRUE，结果正确

select Isprime_func(7);

③：输入如下语句，然后执行，返回FALSE，结果正确

select Isprime_func(9);

3、存储过程
（1）创建一个名称为proc_1的不带参数的存储过程，该存储过程的功能是从学生表中查询所有男同学的信息；并执行proc_1存储过程。
①：输入如下语句，然后执行，用于创建该存储过程

delimiter //
create procedure proc_1()
begin
     select * from student where 性别 = '男';
end //
delimiter ;

②：输入如下语句，然后执行，用于调用该存储过程，查询到了所有男同学的信息

call proc_1();

（2）创建一个名称为proc_2的存储过程，该存储过程的功能是向学生表中插入一条记录，新记录的值由参数提供；并执行proc_2存储过程，调用时向存储过程传递6个参数，组成一条新记录插入到学生表中，如下图。

①：输入如下语句，然后执行，用于创建该存储过程

delimiter //
create procedure proc_2(
       Sno varchar(20),
       Sname varchar(20),
       Ssex char(1),
       Sclass varchar(20),
       Sbirthday varchar(20),
	   Stel varchar(20)
)
begin
       insert into student(学号, 姓名, 性别, 专业班级, 出生日期, 联系电话) values (Sno, Sname, Ssex, Sclass, Sbirthday, Stel);
end //
delimiter ;

②：输入如下语句，然后执行，用于调用该存储过程，插入了一条新记录

call proc_2('0595', '张小兰', '女', '生物05', '1990-12-21', '13312341234');

（3）创建一个名称为proc_3的存储过程，该存储过程的功能是从学生表中根据学号查询某一同学的姓名和性别；并执行proc_3存储过程，查询学号为0595的同学的姓名和性别。
①：输入如下语句，然后执行，用于创建该存储过程

delimiter //
create procedure proc_3(Sno varchar(20))
begin
       select 姓名, 性别 from student where 学号 = Sno;
end //
delimiter ;

②：输入如下语句，然后执行，用于调用该存储过程，查询学号为0595的同学的姓名和性别

call proc_3('0595');

4、触发器
（1）创建触发器trigger_1，其功能是当向student表中插入某个学生的出生日期为空时，就把出生日期设置为“1999-9-9”，如果不为空就按照设定值插入。
①：输入如下语句，然后执行，用于创建该触发器
注：
第5、6行的new是在触发器中⽤于获取insert *** 作添加的数据、update *** 作修改后的记录。

delimiter //
create trigger trigger_1 before insert on student
for each row
begin
   if new.出生日期 is null then
      set new.出生日期 = '1999-9-9';
   end if;
end //
delimiter ;

②：进入student表，在其最后一行输入如图所示的学生记录，其出生日期为空。连续点击两次Apply，点击Finish，然后发现student表中该学生的出生日期被设置为“1999-9-9”，符合要求



③：在student表的最后一行输入如图所示的学生记录，其出生日期不为空。连续点击两次Apply，点击Finish，然后发现student表中该学生的出生日期与设定值一样，符合要求

（2）创建触发器trigger_2，实现当删除“学生表”中的某个学生的记录时，对应“学生作业表”中的该学生的记录一并删除。
①：输入如下语句，然后执行，用于创建该触发器
注：
第5行的old是在触发器中⽤于获取delete *** 作删除前的数据、update *** 作修改前的数据。

delimiter //
create trigger trigger_2 after delete on student 
for each row
begin
     delete from sc where sc.学号 = old.学号;
end //
delimiter ;

②：为了观察触发器的作用，此时先进入student表和sc表。在student表中随机选择一名想要删除的学生（此处我选择的是学号为’0531’的张志国），在sc表中发现他有K001、K002、K006、S001这四门课的记录。


③：从student表中删除张志国的记录。
此时，为了防止报Cannot delete or update a parent row: a foreign key constraint fails的错误（报该错误的原因：student表中的学号是sc表的外键，在进行删除 *** 作时会自动检查外键约束），先输入如下语句，然后执行，用来关闭外键约束检查

set foreign_key_checks = 0;

然后，在student表中右键张志国那一行，点击Delete Row(s)，删除其记录。连续点击两次Apply，点击Finish，然后再次进入sc表，发现sc表中已没有张志国那四门课的选课记录，满足题目要求



然后，输入如下语句，执行，用于打开外键约束检查，保持表结构的完整性

set foreign_key_checks = 1;