数据库原理及应用mysql版陈业斌实验五

🏝️专栏:Mysql_猫咪-9527的博客-CSDN博客
🌅主页:猫咪-9527-CSDN博客 

“欲穷千里目,更上一层楼。会当凌绝顶,一览众山小。”

目录

实验五存储程序

1.实验数据如下

2. 插入数据

3.实验内容

5-1 创建存储过程 pro_fndname

5-2 设计函数 count_credit

5-3 创建存储过程p_count_credit

5-4 创建触发器 sum_credit

5-5 创建级联删除触发器 delstudent_score


实验五存储程序

【实验目的】

掌握存储函数、存储过程与触发器等存储程序的相关操作方法,理解存储函数、存储过程与触发器的作用。

1.实验数据如下

student 表(学生表)

CREATE TABLE student (
    sno CHAR(5) PRIMARY KEY,
    snme VARCHAR(20) NOT NULL,        
    sdept VARCHAR(20) NOT NULL,       
    sclass CHAR(2) NOT NULL,          
    ssex CHAR(1),                     
    birthday DATE,                  
    totalcredit DECIMAL(4,1)          
);

course 表(课程表)

CREATE TABLE course (
    cno CHAR(3) PRIMARY KEY,
    cname VARCHAR(50),       
    ctime DECIMAL(3,0),          
    credit DECIMAL(3,1) 
);

teacher 表(教师表)

CREATE TABLE teacher (
    tno CHAR(6) PRIMARY KEY,          
    tname VARCHAR(20),               
    tsex CHAR(1),                    
    tdept VARCHAR(20)                 
);

score 表(成绩表)

CREATE TABLE score (
    sno CHAR(5),                      
    cno CHAR(3),                      
    tno CHAR(6),                     
    grade DECIMAL(5,1),              
    PRIMARY KEY (sno, cno, tno),      
    CONSTRAINT fk_sno FOREIGN KEY(sno) REFERENCES student(sno),
    CONSTRAINT fk_cno FOREIGN KEY(cno) REFERENCES course(cno),
    CONSTRAINT fk_tno FOREIGN KEY(tno) REFERENCES teacher(tno)
);

2. 插入数据

student 表中的数据

INSERT INTO student VALUES('96001', '马小燕', '计算机', '01', '女', '2000/01/02', 0);
INSERT INTO student VALUES('96002', '黎明', '计算机', '01', '男', '2000/03/05', 0);
INSERT INTO student VALUES('96003', '刘东明', '数学', '01', '男', '2000/10/05', 0);
INSERT INTO student VALUES('96004', '赵志勇', '信息', '02', '男', '2000/08/08', 0);
INSERT INTO student VALUES('97001', '马蓉', '数学', '02', '女', '2001/03/04', 0);
INSERT INTO student VALUES('97002', '李成功', '计算机', '01', '男', '2001/09/10', 0);
INSERT INTO student VALUES('97003', '黎明', '信息', '03', '女', '2002/02/08', 0);
INSERT INTO student VALUES('97004', '李丽', '计算机', '02', '女', '2002/01/05', 0);
INSERT INTO student VALUES('96005', '司马志明', '计算机', '02', '男', '2001/11/23', 0);

course 表中的数据

INSERT INTO course VALUES('001', '数学分析', 64, 4);
INSERT INTO course VALUES('002', '普通物理', 64, 4);
INSERT INTO course VALUES('003', '微机原理', 56, 3.5);
INSERT INTO course VALUES('004', '数据结构', 64, 4);
INSERT INTO course VALUES('005', '操作系统', 56, 3.5);
INSERT INTO course VALUES('006', '数据库原理', 56, 3.5);
INSERT INTO course VALUES('007', '编译原理', 48, 3);
INSERT INTO course VALUES('008', '程序设计', 32, 2);

teacher 表中的数据

INSERT INTO teacher VALUES('052501', '王成刚', '男', '计算机');
INSERT INTO teacher VALUES('052502', '李正科', '男', '计算机');
INSERT INTO teacher VALUES('052503', '严敏', '女', '数学');
INSERT INTO teacher VALUES('052504', '赵高', '男', '数学');
INSERT INTO teacher VALUES('052505', '刘玉兰', '女', '计算机');
INSERT INTO teacher VALUES('052506', '王成刚', '男', '信息');
INSERT INTO teacher VALUES('052507', '马悦', '女', '计算机');

score 表中的数据

INSERT INTO score VALUES('96001', '001', '052503', 77.5);
INSERT INTO score VALUES('96001', '003', '052501', 89);
INSERT INTO score VALUES('96001', '004', '052502', 86);
INSERT INTO score VALUES('96001', '005', '052505', 82);
INSERT INTO score VALUES('96002', '001', '052504', 88);
INSERT INTO score VALUES('96002', '003', '052502', 92.5);
INSERT INTO score VALUES('96002', '006', '052507', 90);
INSERT INTO score VALUES('96005', '004', '052502', 92);
INSERT INTO score VALUES('96005', '005', '052505', 90);
INSERT INTO score VALUES('96005', '006', '052505', 89);
INSERT INTO score VALUES('96005', '007', '052507', 78);
INSERT INTO score VALUES('96003', '001', '052504', 69);
INSERT INTO score VALUES('97001', '001', '052504', 96);
INSERT INTO score VALUES('97001', '008', '052505', 95);
INSERT INTO score VALUES('96004', '001', '052503', 87);
INSERT INTO score VALUES('96003', '003', '052501', 91);
INSERT INTO score VALUES('97002', '003', '052502', 91);
INSERT INTO score VALUES('97002', '004', '052505', NULL);
INSERT INTO score VALUES('97002', '006', '052507', 92);
INSERT INTO score VALUES('97004', '005', '052502', 90);
INSERT INTO score VALUES('97004', '006', '052501', 85);

 注:把上面的实验数据添加上再开始实验。

3.实验内容

5-1 创建存储过程 pro_fndname

存储过程 profndname 通过模糊查询学生姓名,输入一个汉字,返回所有包含该汉字的学生记录。

delimiter ##
create procedure pro_fndname(in seach_char char(1))
begin
    select *from student where snme like concat('%',seach_char,'%');
end ##
delimiter ;

5-2 设计函数 count_credit

函数 countcredit 根据学号计算学生的总学分,前提是成绩大于或等于60分时才能获得课程学分。

delimiter ##
  create Function count_credit(v_sno char(6))
  returns int reads sql data
  Begin
  declare sums float ;
  select ifnull(sum(credit),0) into sums from course natural join score where grade >=60 and v_sno=sno;
    return sums;
  end ##
  delimiter ;

5-3 创建存储过程p_count_credit

存储过程 p_count_credit 使用 countcredit 函数更新 student 表中的总学分。

delimiter ##
  create procedure  p_count_credit()
  begin
    declare v_sno varchar(20);
    declare v_credit int default 0;
    declare stucur cursor for select sno from student;
    declare exit handler for not found close stucur ;
    open stucur;
    while true do
    fetch stucur into v_sno;
    update student set totalcredit =count_credit(v_sno) where sno=v_sno;
    end while;
    close stucur;
    end ##
  delimiter ;

 

5-4 创建触发器 sum_credit

触发器 sum_credit 在向 score 表插入记录时,自动更新 student 表中的总学分。只有成绩大于或等于60分时,才会增加学分。

delimiter ##
  create trigger sum_credit
  after insert on score for each row
  Begin
  if(new.grade>=60) then
        update student set totalcredit =totalcredit+(select credit from course where cno=new.cno)
        where student.sno=new.sno;
    end if;
    end ##
    delimiter ;
 

5-5 创建级联删除触发器 delstudent_score

触发器 delstudent_score 实现级联删除,当删除 student 表中的学生记录时,会同时删除 score 表中对应学号的成绩记录。

delimiter ##
  create trigger del_studnet_score
  before delete on student for each row
  begin
    delete from score where sno=old.sno;
    end ##
  delimiter ;

删除前:

 删除后:

评论 90
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值