MySQL(挑战3):简化数据操作提升性能

心已赠人 2022-05-17 10:14 201阅读 0赞

## 介绍 ##

在挑战实验1中构建的成绩管理系统中，当有同学的分数被修改时，需要通过触发器将成绩表中的这条更新记录存到新的表 modifymark 中。同时需要创建一个存储过程用来显示数学课的全部学生的成绩，并按分数降序排列。另外，现需要为数据库新增一个用户，并为他分配在数据库 gradesystem 下所有表的 select 权限。

创建成绩管理系统数据库的代码在 createdb2.sql 中，请打开终端输入下载代码：

wget http://labfile.oss.aliyuncs.com/courses/9/createdb2.sql

下载的文件将保存在/home/shiyanlou/目录下。

数据库 gradesystem 中有三张表分别用于记录学生信息、课程信息和成绩信息。

## 数据库表的数据如下： ##

1.学生表(student)：学生 id 、学生姓名和性别

2.课程表(course)：课程 id 和课程名

3.成绩表(mark)：成绩 id 、学生 id 、课程 id 和分数

4.成绩更新表（modifymark）：成绩 id (m\_mid)、学生 id (m\_sid)、课程 id(m\_cid) 、分数(m\_score)和更新时间(m\_time)，表结构与 mark 表大致类似。

## 目标 ##

1.MySQL 服务处于运行状态

2.下载运行 createdb2.sql 文件，完成数据库的建立

3.创建触发器 trigger\_modify 实现当 mark 表有分数被修改时，将更新后的整条数据插入到 modifymark 表中。测试将 Tom 的化学成绩在原来的基础上加3分，查看 modifymark 中是否插入了数据

4.创建存储过程 math\_proc 将数学课的全部学生的成绩按降序排列显示。包括课程名(cname) math、学生姓名(sname)以及分数(score)

5.创建用户 testuser，密码为 123456，并为其分配在数据库 gradesystem 下所有表的 select 权限

## 提示 ##

执行 .sql 文件  
触发器在更新操作后触发事件  
order by 排序  
grant 创建用户

## 过程 ##

MySQL服务处于运行状态并登录:

sudo service mysql start
    mysql -u root -p

下载运行 createdb2.sql 文件，完成数据库的建立:

source /home/shiyanlou #sql文件地址

### 1.创建触发器 trigger\_modify 实现当 mark 表有分数被修改时，将更新后的整条数据插入到 modifymark 表中: ###

create trigger trigger_modify before update on mark
    for each row
    insert into modifymark value (NULL,new.sid, new.cid, new.score,now());

测试将 Tom 的化学成绩在原来的基础上加3分

update mark set score = score + 3 
    where sid = (select sid from student where sname = 'Tom') 
    and   cid = (select cid from course where cname = 'chemistry');

查看 modifymark 中是否插入了数据

select * from modifymark ;

结果：

![70][]

### 2.创建存储过程 math\_proc 将数学课的全部学生的成绩按降序排列显示。包括课程名(cname) math、学生姓名(sname)以及分数(score): ###

创建存储过程 :(用mysql客户端 **delimiter** 命令来把语句定界符从 **;** 变为 **//** 。这就允许用在程序体中的;定界符被传递到服务器而不是被mysql自己来解释。)

delimiter //
    
    create procedure math_proc () 
    begin
     select 'math', sname, score 
     from  student, mark 
     where mark.cid = (select cid from course where cname = 'math') 
     and student.sid = mark.sid
     order by score desc;
    end
    //
    
    delimiter ;

调用过程程序:

call math_proc();

结果：

![70 1][]

### 3.创建用户 testuser，密码为 123456，并为其分配在数据库 gradesystem 下所有表的 select 权限: ###

grant select on *.* to 'testuser'@'localhost' identified by '123456';

[70]: /images/20220517/c96d9423d1284487b1a76715d7918978.png
[70 1]: /images/20220517/d3e9138df75f43c8870c2cea31c293f8.png